CODEWITHSUNDEEP
pythonmysqlsqlcsv
Ashley Jordan
Ashley Jordan

Reputation: 29

MySQL SELECT from field in CSV in Python

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

Answers (4)

Burhan Khalid
Burhan Khalid

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

CadentOrange
CadentOrange

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

geertjanvdk
geertjanvdk

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

loki
loki

Reputation: 387

try this,

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

row[0] should be first column value.

Upvotes: 0

Related Questions