inputting a variable into select when using python and sqlite

Question

I have an sqlite db called clients.db with a table called prices. Within the table I have columns ['date', 'xyz', 'abc', 'sta, 'vert']. I am accessing the database from python 3.

I can get a specific number easily enough using:

conn = sqlite3.connect('clients.db')
c = conn.cursor()

c.execute('''SELECT "xyz" FROM prices WHERE date=?''', ('2019-01-07', ))
conn.close()

print(c.fetchone()[0])

This returns 1902 as expected.

However when I try the below, instead of the expected number I get xyz.

conn = sqlite3.connect('clients.db')
c = conn.cursor()

c.execute('''SELECT ? FROM prices WHERE date=?''', ('xyz', '2019-01-07', ))
conn.close()

print(c.fetchone()[0])

and when I add a =? I get sqlite3.OperationalError: near "=": syntax error:

conn = sqlite3.connect('clients.db')
c = conn.cursor()

c.execute('''SELECT =? FROM prices WHERE date=?''', ('xyz', '2019-01-07', ))
conn.close()

print(c.fetchone()[0])

in all your example you have missing closing quote ' at the end of the date, i.e. even the first one should not work if this is in your code — buran, May 20 '19 at 14:26
Possible duplicate of [pysqlite: Placeholder substitution for column or table names?](https://stackoverflow.com/questions/8841488/pysqlite-placeholder-substitution-for-column-or-table-names) — Ivan Vinogradov, May 20 '19 at 14:34
Possible duplicate of [Python sqlite3 string variable in execute](https://stackoverflow.com/questions/13880786/python-sqlite3-string-variable-in-execute) — glibdud, May 20 '19 at 14:36

score 0 · Answer 1 · answered May 20 '19 at 14:40

From Python documentation:

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

You need to use ? placeholder for values but for column names you can use string formatting.

I have created a class, inserted some dummy rows and run a select query which is mentioned in the question.

import sqlite3

class Database(object):
    def __init__(self):
        self.conn = sqlite3.connect('clients.db')
        self.c = self.conn.cursor()

    def create_table(self):
        try:
            self.c.execute('''CREATE TABLE prices (date text, xyz text, abc text, sta text, vert text)''')
        except:
            pass

    def insert_dummy_rows(self):        
        values = [('2019-01-07', 'xyz1', 'abc1', 'sta1', 'vert1'),
                     ('2019-01-07', 'xyz2', 'abc2', 'sta2', 'vert2'),
                     ('2019-01-08', 'xyz3', 'abc3', 'sta3', 'vert3'),
                    ]
        self.c.executemany('INSERT INTO prices VALUES (?,?,?,?,?)', values)
        self.conn.commit()

    def close_connection(self):
        self.conn.close()

    def get_single_row(self):
        t = ('2019-01-07',)
        query = "SELECT {} FROM prices WHERE date=?".format('xyz')
        self.c.execute(query, t)
        return self.c.fetchone()[0]

if __name__ == '__main__':
    db = Database()
    db.create_table()
    db.insert_dummy_rows()
    print(db.get_single_row())

Output:

xyz1

inputting a variable into select when using python and sqlite

1 Answers1