MySQL syntax error?

Question 1

I have searched on forums for the error I keep getting when running my code but it seems to be situation specific. My program connects to a database, and takes a line from a text file, extracts the name from the line, and uses that name to do a search query in the database. The following is the relevant code:

while line:
 lines = line.split('\t')
 if len(lines) > 1:
 date = lines[0]
 name = lines[2]
 address = lines[3]
 amount = int(float(lines[len(lines)-1]))
 named = name.split()
 first = named[1]
 last = named[0]
 zipc = lines[4]
 cur.execute("SELECT `Date`, `Contrib`, `Amount`, `Street`, `City`
 `State`, `Zip` FROM indiv_contribs WHERE Contrib = '%s, %s'" %
 (last, first))
 rows = cur.fetchall()

The error I keep getting is:

"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 'malley, matthew'' at line 1"

Question 2

For starters, you've got no comma between the City and State columns. I imagine that doesn't help.

Question 3

Does your input contain any double-quotes? You'll need to filter those so they don't end up in (last, first).

Question 4

Can you also print out what last, first contain? It's possible that matthew could have a ' character in there

Question 5

You can find out the problem by simply printing that SQL statement.

Question 6

And, instead of performing the string formatting yourself, perhaps switch to using defined parameters, as in this example mysql-python.sourceforge.net/MySQLdb.html#some-examples. Let's the driver handle any escaping issues etc that might be tripping you up at the moment.

Question 7

If your language is Python, your SQL statement should look like:

cur.execute("""SELECT Date, Contrib, Amount, Street, City, State, Zip FROM indiv_contribs WHERE Contrib = %s, %s""", (last, first))
rows = cur.fetchall()

Question 8

That won't do, as I suppose that Contrib is a column containing last name and first name. Better do

cur.execute("""SELECT Date, Contrib, Amount, Street, City, State, Zip FROM indiv_contribs WHERE Contrib = %s""", (last + ", " + first,))

Question 9

thanks glglgl! I tried AShelly's way frist but still received the error but it went away after stringing the last and first name together.

Richk 1315 bronze badges · Accepted Answer · 2011-11-30 07:40:36Z

3

If your language is Python, your SQL statement should look like:

cur.execute("""SELECT Date, Contrib, Amount, Street, City, State, Zip FROM indiv_contribs WHERE Contrib = %s, %s""", (last, first))
rows = cur.fetchall()

Share

Improve this answer

edited Nov 30, 2011 at 8:35

AShelly's user avatar

AShelly

35.9k17 gold badges98 silver badges163 bronze badges

answered Nov 30, 2011 at 7:40

Richk's user avatar

Richk

1315 bronze badges

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

2 Comments

glglgl

glglgl Over a year ago

That won't do, as I suppose that Contrib is a column containing last name and first name. Better do

cur.execute("""SELECT Date, Contrib, Amount, Street, City, State, Zip FROM indiv_contribs WHERE Contrib = %s""", (last + ", " + first,))

2011年11月30日T09:06:54.91Z+00:00

deedex11

deedex11 Over a year ago

thanks glglgl! I tried AShelly's way frist but still received the error but it went away after stringing the last and first name together.

2011年11月30日T16:59:50.957Z+00:00

CollectivesTM on Stack Overflow

MySQL syntax error?

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

CollectivesTM on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related