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?

Try changing it to this: ` f = pd.read_sql_query('SELECT open FROM NYSEMSFT WHERE date = (?)', conn, params=(date,))` — Ted Petrou, Dec 25 '16 at 21:17

score 10 · Accepted Answer · answered Dec 25 '16 at 21:07

10

You need to use the params keyword argument:

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

answered Dec 25 '16 at 21:07

alecxe

462,703
120
1,088
1,195

MaxU - stand with Ukraine · Answer 2 · 2016-12-25T21:48:51.607

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 (?,?,?)'

score 2 · Answer 3 · answered Jun 13 '17 at 20:34

2

Another option is to use pythons string formatting explicitly and forget about the params argument. That is to say you do something like this

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))

answered Jun 13 '17 at 20:34

arie64

572
6
10

You should never do string formatting on sql statements because your program would be vulnerable against sql injection attacks – Colin Moreno Burgess Mar 26 '19 at 10:10

Pandas Sqlite query using variable

3 Answers3

Linked