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

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

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

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

Load your data as auto normalised tables, I suggest using this library that infers schema, types the data and has schema evolution too https://pypi.org/project/dlt/

You can even use this library to do an upsert on your structured data afterwards, here's an example where we use an id in the json to update the sql table generated at destination

data = [{'id': 1, 'name': 'John'}]

# open connection
pipe = dlt.pipeline(destination='postgres',
                    dataset_name='raw_data')

# Upsert/merge: Update old records, insert new
# Capture the outcome in load info
load_info = pipe.run(data,
                      write_disposition="merge",
                      primary_key="id",
                      table_name="users")