how to use variables in SQLite definition

Question

when I try to select rows by a date as variable I get None as result

c.execute('SELECT * FROM ES_F WHERE date = {};'.format('2017-10-23'))
----> None

when I put the same date inside the SQL definition it works fine and I get the row I wanted.

c.execute("""SELECT * FROM ES_F WHERE date = '2017-10-23';""")
----> ('2017-10-23', '09:27', 567, 'Mon', 2576.5, 2574.0, 2572.75, 2577.25, 2572.0, 2577.25, 2061.0, 159300, 3096574, 'ESZ7')

I would like to use as variable though because I have to use it recursively. Any idea of why it does not work? thanks

Answer 1

You could use value conversion to repr with the .format method:

'SELECT * FROM ES_F WHERE date = {!r};'.format('2017-10-23')

Or make the single-quotes part of the string:

'SELECT * FROM ES_F WHERE date = {};'.format("'2017-10-23'")

But really, best practice is not to use string-interpolation at all, because that makes you vulnerable to SQL injection.

As the docs state:

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.

So use:

c.execute('SELECT * FROM ES_F WHERE date = ?;', ('2017-10-23',))