How to use variables in SQL statement in Python?

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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!

Method 3

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'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.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),
            ]
c.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))

Method 4

http://www.amk.ca/python/writing/DB-API.html

Be careful when you simply append values of variables to your statements:
Imagine a user naming himself ';DROP TABLE Users;' —
That’s why you need to use SQL escaping, which Python provides for you when you use cursor.execute in a decent manner. Example in the URL is:

cursor.execute("insert into Attendees values (?, ?, ?)", (name, seminar, paid))

Method 5

The syntax for providing a single value can be confusing for inexperienced Python users.

Given the query

INSERT INTO mytable (fruit) VALUES (%s)

Generally*, the value passed to cursor.execute must wrapped in a sequence such as a tuple or list even though the value itself is a singleton, so we must provide a single element tuple, like this: (value,).

cursor.execute("""INSERT INTO mytable (fruit) VALUES (%s)""", ('apple',))

Passing a single string

cursor.execute("""INSERT INTO mytable (fruit) VALUES (%s)""", ('apple'))

will result in an error which varies by the DB-API connector, for example

• psycopg2:
  

  TypeError: not all arguments converted during string formatting

• sqlite3
  

  sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 5 supplied

• mysql.connector
  

  mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax;

* The pymysql connector handles a single string parameter without erroring. However it’s better to wrap the string in a tuple even if it’s a single because

• you won’t need to change the code if you switch connector package
• you keep a consistent mental model of the query parameters being a sequence of objects rather than a single object.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0