MySQL syntax error?

Question

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"

For starters, you've got no comma between the City and State columns. I imagine that doesn't help. — Dan J
– Dan J, Commented Nov 30, 2011 at 4:01
Does your input contain any double-quotes? You'll need to filter those so they don't end up in (last, first). — David K. Hess
– David K. Hess, Commented Nov 30, 2011 at 4:20
Can you also print out what last, first contain? It's possible that matthew could have a ' character in there — Victor Parmar
– Victor Parmar, Commented Nov 30, 2011 at 4:21
You can find out the problem by simply printing that SQL statement. — Jesvin Jose
– Jesvin Jose, Commented Nov 30, 2011 at 4:45
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. — Mark Streatfield
– Mark Streatfield, Commented Nov 30, 2011 at 6:03

AShelly · Accepted Answer · 2011-11-30 08:35:46Z

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

edited Nov 30, 2011 at 8:35

AShelly

35.9k17 gold badges98 silver badges163 bronze badges

answered Nov 30, 2011 at 7:40

Richk

1315 bronze badges

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

2 Comments

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

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.

Collectives™ on Stack Overflow

MySQL syntax error?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related