Using placeholders in a database query doesn't work

Question

I am querying a mysql database version 5.6.13 using python 2.7.

This works:

whichCustomer = str(1934)
qry = ("SELECT * FROM customers WHERE customerid = " + whichCustomer)
cursor.execute(qry)

The query also works:

qry = ("SELECT * FROM customers WHERE customerid = 1934")
cursor.execute(qry)

BUT, when I try to use string substitution the query fails:

whichCustomer = 1934
qry = ("SELECT * FROM customers WHERE customerid = %d")
cursor.execute(qry, (whichCustomer))

Is there something I am missing. The full try/execute code follows:

try:
    import mysql.connector
    print 'Module mysql initialized'
    print 'Attempting connection to cheer database' 
    cnx = mysql.connector.connect(user='notsure', 
            password='notsure',
            host='localhost',
            database='notreal')
    cursor = cnx.cursor()
    whichCustomer = str(1934)
    qry = ("SELECT * FROM customers WHERE customerid = " + whichCustomer)
    cursor.execute(qry)
    recx = cursor.fetchone()
    print recx[1]
    cnx.close()
    print 'Successful connection to notreal database'
except:
    print 'Error initialzing mysql databsasr'

Make sure you report exactly what "doesn't work": if there is an error, this should include the message. — user2864740
– user2864740, Commented Jan 2, 2014 at 0:19

Martijn Pieters · Accepted Answer · 2014-01-02 01:12:09Z

3

You need to use %s for SQL parameters, and the second argument must be a sequence, like a tuple:

whichCustomer = 1934
qry = ("SELECT * FROM customers WHERE customerid = %s")
cursor.execute(qry, (whichCustomer,))

Note the comma in the second parameter; without a comma, that parameter is not a tuple and just the 1934 integer value is passed in instead.

Although both Python string interpolation placeholders and SQL parameters use closely related syntax, they are not the same thing. As such, SQL parameters for positional values are always expressed as %s regardless of the type.

edited Jan 2, 2014 at 1:12

answered Jan 2, 2014 at 0:00

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

Collectives™ on Stack Overflow

Using placeholders in a database query doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related