python - MySQL 从 Python 中的 CSV 字段中选择

Question

我目前在根据 Python 脚本中 .csv 文件中的行执行 MySQL SELECT 查询时遇到问题；

#!/usr/bin/env python

import MySQLdb, csv, sys

db = MySQLdb.connect(host="hostname",
                    user="user",
                    passwd="password",
                    db="database")


cur = db.cursor()

customers=csv.reader(file("customers.csv"))

for row in customers(row):
    cur.execute("select field from database.table where customernumber = %s;" % row)
    cur.commit()
    cur.close()

我明白了；

    Traceback (most recent call last): 
    File "script.py", line 17, in <module>
    cur.execute("select field from database.table where field = %s;" % row)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
    File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in        defaulterrorhandler
    raise errorclass, errorvalue
   _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check   the manual that corresponds to your MySQL server version for the right syntax to use near  '['1598']' at line 1")

.csv 文件的第一行是 1598。

出于某种原因，它用 '['1598']' 封装了这个条目，因此 mySQL 查询失败了，

知道为什么要封装它以及如何阻止它吗？

提前致谢!

出于 DPA 原因，我已将所有 mysql 详细信息重命名为默认值

阿什莉

Answer 1

首先，您需要修复您的 csv 阅读器逻辑。然后您需要将该值作为参数传递给 execute，commit() 是在连接上完成的，而不是光标，最后 - 您将在循环本身中关闭连接.

import csv

with open('customers.csv') as f:
    reader = csv.reader(f)
    rows = list(reader)

for row in rows:
    cur.execute("select field from database.table where customernumber = %s;", (row[0],))
    con.commit()

con.close()