Pandas Sqlite query using variable

Question

With sqlite3 in Python if I want to make a db query using a variable instead of a fixed command I can do something like this :

name = 'MSFT'

c.execute('INSERT INTO Symbol VALUES (?) ', (name,))

And when I try to access the SQL db using pandas data frame I can do this:

df = pd.read_sql_query('SELECT open FROM NYSEXOM', conn) 

However I am unsure how to load data from SQL to a pandas data frame while referencing a variable. I have tried the following:

conn = sqlite3.connect('stocks.db')
dates= [20100102,20100103,20100104]
for date in dates:

    f = pd.read_sql_query('SELECT open FROM NYSEMSFT WHERE date = (?)', conn, (date,))

When I run this I get an error saying "Incorrect number of bindings supplied, The current statement uses 1, and there are 0 supplied"

How can I properly format a command to load SQL data into a pandas data frame using a variable reference?

Answer 1

You need to use the params keyword argument:

f = pd.read_sql_query('SELECT open FROM NYSEMSFT WHERE date = (?)', conn, params=(date,))

Answer 2

As @alecxe and @Ted Petrou have already said, use explicit parameter names, especially for the params parameter as it's a fourth parameter in the pd.read_sql_query() function and you used it as a third one (which is coerce_float)

But beside that you can improve your code by getting rid of the for date in dates: loop using the following trick:

import sqlite3

dates=['2001-01-01','2002-02-02']
qry = 'select * from aaa where open in ({})'

conn = sqlite3.connect(r'D:\temp\.data\a.sqlite')

df = pd.read_sql(qry.format(','.join(list('?' * len(dates)))), conn, params=dates)

Demo:

Source SQLite table:

sqlite> .mode column
sqlite> .header on
sqlite> select * from aaa;
open
----------
2016-12-25
2001-01-01
2002-02-02

Test run:

In [40]: %paste
dates=['2001-01-01','2002-02-02']
qry = 'select * from aaa where open in ({})'
conn = sqlite3.connect(r'D:\temp\.data\a.sqlite')

df = pd.read_sql(qry.format(','.join(list('?' * len(dates)))), conn, params=dates)
## -- End pasted text --

In [41]: df
Out[41]:
         open
0  2001-01-01
1  2002-02-02

Explanation:

In [35]: qry = 'select * from aaa where open in ({})'

In [36]: ','.join(list('?' * len(dates)))
Out[36]: '?,?'

In [37]: qry.format(','.join(list('?' * len(dates))))
Out[37]: 'select * from aaa where open in (?,?)'

In [38]: dates.append('2003-03-03')   # <-- let's add a third parameter

In [39]: qry.format(','.join(list('?' * len(dates))))
Out[39]: 'select * from aaa where open in (?,?,?)'