Pandas Sqlite query using a 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 SQL db using the pandas framework I can do this:

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

However, I'm not sure how to load data from SQL into a pandas data frame when referencing a variable. I 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 the error message "The number of bindings supplied is incorrect, the current statement uses 1, and there are 0"

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

Answer 1

params:

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

Answer 2

@alecxe @Ted Petrou, , params, pd.read_sql_query(), (coerce_float)

, for date in dates:, :

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:

SQLite:

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

:

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

:

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 add a third parameter

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

Answer 3

- pythons params. -

conn = sqlite3.connect("database.sqlite")

df = pd.read_sql_query("                                           \
           SELECT * FROM table WHERE name= %s AND                  \
           number=%f" %(some_string_variable,some_float_variable)  \
           ,conn))