How to use variables in SQL statement in Python?

Question

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?

Answer 1

Very old question, but here’s some more detail.

Different DBMS drivers implement different parameter styles. You can find out which one using something like the following snippet:

import sqlite3
print(sqlite3.paramstyle)           #   'qmark'
import pyodbc
print(pyodbc.paramstyle)            #   'qmark'
import mysql.connector
print(mysql.connector.paramstyle)   #   'pyformat'
import psycopg2
print(psycopg2.paramstyle)          #   'pyformat'
import oracledb
print(oracledb.paramstyle)          #   'named'

I’ve included the results from my own testing.

You can get a list of parameter styles from:

https://peps.python.org/pep-0249/#paramstyle

Here is an extract:

paramstyle	meaning
numeric	Numeric, positional style, e.g. … WHERE name=:1
named	Named style, e.g. … WHERE name=:name
pyformat	Python extended format codes, e.g. … WHERE name=%(name)s
qmark	Question mark style, e.g. … WHERE name=?
format	ANSI C printf format codes, e.g. … WHERE name=%s

After that, you can create two variables, one for the SQL and one for the parameters:

sql = 'INSERT INTO table VALUES ?,?,?'
data = (var1, var2, var3)
cursor.execute(sql, data)

Here, I have used the qmark style. The data is a tuple of values, but it could have been a list. The point is, even if it’s only one value, you need to make a tuple or list.

If your DBMS uses the pyformat or named styles, you could use something like this:

sql = 'INSERT INTO table VALUES %(first)s,%(second)s,%(third)s' #   pyformat
sql = 'INSERT INTO table VALUES :first,:second,:third'          #   named
data = {'first': var1, 'second': var2, 'third': var3}
cursor.execute(sql, data)

Here you use a dictionary where the keys match the arbitrary names used in the SQL.

Answer 2

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 be wrapped in an ordered 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.

Answer 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:

... beware of using Python’s string operations to assemble queries, as they are vulnerable to SQL injection attacks. For example, an attacker can simply close the single quote and inject OR TRUE to select all rows:

# Never do this -- insecure!
symbol = input()

sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
print(sql)

cur.execute(sql)

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

Answer 4

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

Note that the parameters are passed as a tuple, (a, b, c). If you're passing a single parameter, the tuple needs to end with a comma, (a,).

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.

Answer 5

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

Answer 6

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!