0

(Python 3.7.2 on Windows 7)

When I query a sqlite3 database in Python, it works as it should:

cursor.execute('SELECT name FROM names')
#returns [('Bob',), ('Alice',), ('Jim',), ('Sally',)]

However, when I try to execute a prepared statement, this happens:

c.execute('SELECT ? FROM names', ('name',))
#returns [('name',), ('name',), ('name',), ('name',)]

My best guess is that something weird is occurring that causes the actual execution statement to look like "SELECT 'name' FROM names", as opposed to "SELECT name FROM names". Below is some more detailed code elaborating on this problem.

import sqlite3

conn = sqlite3.connect('db.db')
# This is a database with a single table:

# CREATE TABLE 'names' (
#   'name'  TEXT,
#   PRIMARY KEY('name')
# );

# 'names' has four entries:
# "Bob"
# "Alice"
# "Jim"
# "Sally"

c = conn.cursor()

c.execute('SELECT name FROM names')
print(c.fetchall())
# prints: [('Bob',), ('Alice',), ('Jim',), ('Sally',)]
c.execute('SELECT ? FROM names', ('name',))
print(c.fetchall())
# prints: [('name',), ('name',), ('name',), ('name',)]
c.execute('SELECT ? FROM names', ('chicken nuggets',))
print(c.fetchall())
# prints: [('chicken nuggets',), ('chicken nuggets',), ('chicken nuggets',), ('chicken nuggets',)]
c.execute('SELECT "name" FROM names')
print(c.fetchall())
# prints: [('Bob',), ('Alice',), ('Jim',), ('Sally',)]
c.execute("SELECT 'name' FROM names")
print(c.fetchall())
# prints: [('name',), ('name',), ('name',), ('name',)]
c.execute("SELECT ? FROM names", ("name",))
print(c.fetchall())
# prints: [('name',), ('name',), ('name',), ('name',)]

conn.close()

How should I fix this problem?

joedeandev
  • 626
  • 1
  • 6
  • 15
  • 1
    You can't pass a column name as a parameter. It has to be in the SQL itself. See [Passing a column name in a SELECT statement in Python](https://stackoverflow.com/questions/29646301) and [Using Python to access SQL with a variable column name](https://stackoverflow.com/questions/24748898) – khelwood Mar 19 '19 at 14:43
  • @khelwood I believe you can, the documentation shows a few examples of this: https://docs.python.org/2/library/sqlite3.html – joedeandev Mar 19 '19 at 14:46
  • I can't see any example in there that is passing a **column name** as a parameter. – khelwood Mar 19 '19 at 14:48

0 Answers0