MySQL SELECT from field in CSV in Python

Question

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

Why are you passing row in customers? customers(row)

Ankit Jaiswal
– Ankit Jaiswal

2014-02-19 09:24:12 +00:00
Commented Feb 19, 2014 at 9:24 — Ankit Jaiswal
– Ankit Jaiswal, Commented Feb 19, 2014 at 9:24

Burhan Khalid · Accepted Answer · 2014-02-20 09:11:15Z

1

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()

answered Feb 20, 2014 at 9:11

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

loki · Accepted Answer · 2014-02-19 09:32:07Z

0

try this,

for row in customers: print row , row[0]

row[0] should be first column value.

answered Feb 19, 2014 at 9:32

loki

3872 silver badges8 bronze badges

2 Comments

Ashley Jordan Over a year ago

Hi, Thanks for your response, the print works perfectly fine, print row prints the first as the mysql then kills the script, also row[0] works the same, both print same results as intended, but the mysql query is still having issues.

loki Over a year ago

see line no. 1598 and try to edit and save the file again. Run the script. i think rest should be fine. above code should work. remove customers(row) in for loop.

geertjanvdk · Accepted Answer · 2014-02-20 08:58:56Z

0

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].

answered Feb 20, 2014 at 8:58

geertjanvdk

3,52827 silver badges27 bronze badges

Comments

CadentOrange · Accepted Answer · 2014-02-20 09:07:34Z

0

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.

answered Feb 20, 2014 at 9:07

CadentOrange

3,3531 gold badge37 silver badges52 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT from field in CSV in Python

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related