Categories
python sql

How to use variables in SQL statement in Python?

117

I have the following Python code:

cursor.execute("INSERT INTO table VALUES var1, var2, var3,")

where var1 is an integer, var2 and var3 are strings.

How can I write the variable names without Python including them as part of the query text?

    140

    cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))
    

    Note that the parameters are passed as a tuple.

    The database API does proper escaping and quoting of variables. Be careful not to use the string formatting operator (%), because

    1. it does not do any escaping or quoting.
    2. it is prone to Uncontrolled string format attacks e.g. SQL injection.

    9

    • Interesting, why does it work with the vars separately instead of in an array (var1,var2,var3)?

      – Andomar

      May 23, 2009 at 20:31

    • According to the DB API specs, it looks like it can be either way: python.org/dev/peps/pep-0249

      May 23, 2009 at 20:57

    • 9

      @thekashyap Read again carefully. What’s insecure is using the string formatting operator %. In fact, I say so in the answer.

      Feb 10, 2014 at 21:21

    • 3

      Downvoted because answer says not to use % but uses it three times. More explanation would be great.

      – eric

      Dec 8, 2019 at 4:09


    • 6

      @eric the answer says do not use the % operator to format the string. Those % in the string are being used by cursor.execute directly, and since it knows it’s generating SQL it can do more to protect you.

      Aug 7, 2020 at 16:21

    84

    Different implementations of the Python DB-API are allowed to use different placeholders, so you’ll need to find out which one you’re using — it could be (e.g. with MySQLdb):

    cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))
    

    or (e.g. with sqlite3 from the Python standard library):

    cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))
    

    or others yet (after VALUES you could have (:1, :2, :3) , or “named styles” (:fee, :fie, :fo) or (%(fee)s, %(fie)s, %(fo)s) where you pass a dict instead of a map as the second argument to execute). Check the paramstyle string constant in the DB API module you’re using, and look for paramstyle at http://www.python.org/dev/peps/pep-0249/ to see what all the parameter-passing styles are!

    1

    • Is possible to do the same thing but with the external SQL script?

      – Novitoll

      Dec 6, 2016 at 9:11

    63

    Many ways. DON’T use the most obvious one (%s with %) in real code, it’s open to attacks.

    Here copy-paste’d from pydoc of sqlite3:

    # Never do this -- insecure!
    symbol="RHAT"
    cur.execute("SELECT * FROM stocks WHERE symbol="%s"" % symbol)
    
    # Do this instead
    t = ('RHAT',)
    cur.execute('SELECT * FROM stocks WHERE symbol=?', t)
    print(cur.fetchone())
    
    # Larger example that inserts many records at a time
    purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
                 ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
                 ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
                ]
    cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
    

    More examples if you need:

    # Multiple values single statement/execution
    c.execute('SELECT * FROM stocks WHERE symbol=? OR symbol=?', ('RHAT', 'MSO'))
    print c.fetchall()
    c.execute('SELECT * FROM stocks WHERE symbol IN (?, ?)', ('RHAT', 'MSO'))
    print c.fetchall()
    # This also works, though ones above are better as a habit as it's inline with syntax of executemany().. but your choice.
    c.execute('SELECT * FROM stocks WHERE symbol=? OR symbol=?', 'RHAT', 'MSO')
    print c.fetchall()
    # Insert a single item
    c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', ('2006-03-28', 'BUY', 'IBM', 1000, 45.00))
    

    1

    • 8

      Some of the DB-API implementations actually use %s for their variables — most notably psycopg2 for PostgreSQL. This is not to be confused (though it easily is) with using %s with the % operator for string replacement. I would be really nice if, for portability, we could just have a defined standard way of specifying SQL parameters for DB-API.

      Nov 24, 2014 at 18:03