How do you pass through a python variable into sqlite3 query?

Question

The code is supposed to execute the query base on the input from the user in the python environment (not within the sql query). For example, the variable is defined in the Python environment, and in raw input for table name = customers, I would expect the query to print the column names of the table customers.

However, the below code report syntax error. If I remove the backward slash and the inner quotation mark, it will report no such column: table_name. It appears that the value customers is not being pass through into the query, and the query is reading table_name as a string.

Please help. Thanks

import sqlite3

    def ask_column(db_name, table_name):
        conn = sqlite3.connect(db_name)
        c = conn.cursor()
        c.execute('SELECT sql FROM sqlite_master WHERE type = \'table\' And name = \'table_name\'') 
        print c.fetchall()
        conn.close()

db_name = raw_input("Please type the database name : ")
table_name = raw_input("Please type the table name: ")
ask_column(db_name, table_name)

snakecharmerb · Answer 1 · 2022-12-14T08:47:08.090

You can do this using parameter substitution. When using Python's sqlite3 module, replace your parameter values with question marks (?) and provide a tuple^* of values. Python will handle the substitution automatically and also escape the values to limit the risk of SQL injection.

Here's an example: first, create a table:

>>> import sqlite3
>>> c = conn.cursor()
>>> c.execute("""CREATE TABLE test (fruit, colour)""")
<sqlite3.Cursor object at 0x7f41e929f650>

Now insert some values: note how the ? character is used as a placeholder for the values:

>>> c.executemany("""INSERT INTO test VALUES (?, ?)""", [('apple', 'green'), ('banana', 'yellow'), ('cherry', 'red')])
<sqlite3.Cursor object at 0x7f41e929f650>

And here's a query (note that we keep pass the values as a tuple, even though there is only one value):

>>> c.execute("""SELECT fruit, colour FROM test WHERE fruit = ?;""", ('apple',)) 
<sqlite3.Cursor object at 0x7f41e929f650>
>>> print(c.fetchall())
[('apple', 'green')]

^* In fact it can be any object that implements the Sequence protocol, but providing a tuple is conventional.

How do you pass through a python variable into sqlite3 query?

1 Answers1