Reputation: 29
I am currently having issues with performing a MySQL SELECT Query based on rows in a .csv file inside a python script;
#!/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()
I am getting this;
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")
The first row in the .csv file is 1598.
For some reason it is encasing this entry with '['1598']'
hence the mySQL query failing,
Any ideas why it is encasing it and how to stop it?
Thanks in advance!
I have renamed all mysql details to defaults for DPA Reasons
Ashley
Upvotes: 1
Views: 396
Reputation: 174758
First, you need to fix your csv reader logic. Then you need to pass the value as an argument to execute
, commit()
is done on the connection not the cursor and finally - you are closing your connection in the loop itself.
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()
Upvotes: 1
Reputation: 3343
cur.execute("select field from database.table where customernumber = %s;" % row[0])
You need to pass row[0]
instead of row
. row
is a list and row[0]
is the first element of that list.
Upvotes: 0
Reputation: 3520
Change executing the SELECT to something like this:
cur.execute("SELECT field FROM database.table WHERE customernumber = %s", (row[0],))
If row[0] has the number, otherwise which ever index has it, like row[4].
Upvotes: 0
Reputation: 387
try this,
for row in customers: print row , row[0]
row[0] should be first column value.
Upvotes: 0