Python SQLite3 How to refer to loop in query

Question

I have a SQLLite3 database containing a single table of stock prices, which looks like this:

   Ticker             DateTime   Open   High    Low  Close   Volume
0    ZUMZ  2011-11-01 00:00:00  21.65  23.24  20.74  22.84   558700
1    ZUMZ  2011-11-02 00:00:00  23.33  23.86  22.58  23.03   543200
2    ZUMZ  2011-11-03 00:00:00  23.28  23.74  21.82  22.67   492100
3    ZUMZ  2011-11-04 00:00:00  22.43  22.86  21.87  22.45   267320

I am trying to loop through a list of symbols and return the appropriate data.

I can return the correct results for one symbol in a list, like this:

tickers = ('AA',)
cursor.execute('SELECT * FROM tbl_USELOC_02 WHERE Ticker=?', tickers)
data = cursor.fetchall()
df =  pd.DataFrame(data)
print df

        0                    1          2          3          4          5  \
0      AA  1951-06-11 00:00:00   0.675918   0.682062   0.675918   0.682062   
1      AA  1951-06-12 00:00:00   0.679605   0.686978   0.679605   0.683291   
2      AA  1951-06-13 00:00:00   0.682062   0.684520   0.680834   0.683291   
3      AA  1951-06-14 00:00:00   0.685749   0.700497   0.685749   0.700497   
4      AA  1951-06-15 00:00:00   0.705412   0.710328   0.705412   0.707870   
5      AA  1951-06-18 00:00:00   0.705412   0.710328   0.705412   0.707870

But, if I try to do the same with a loop, like this:

tickers = ['ZUMZ', 'AA']
for ticker in tickers:
    cursor.execute('SELECT * FROM tbl_USELOC_02 WHERE Ticker=?', ticker)
    data = cursor.fetchall()
    df =  pd.DataFrame(data)
    print df

...I get an error message: 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.'

Can anybody tell me how I can run the query on the items in the list?

score 1 · Accepted Answer · answered Feb 28 '15 at 19:45

1

You forgot to make ticker a tuple:

cursor.execute('SELECT * FROM tbl_USELOC_02 WHERE Ticker=?', (ticker,))

You could also make it a list:

cursor.execute('SELECT * FROM tbl_USELOC_02 WHERE Ticker=?', [ticker])

answered Feb 28 '15 at 19:45

Martijn Pieters

1,048,767
296
4,058
3,343

And the '4 supplied' bit in the error message refers to the four characters in the string 'ZUMZ', because it's treating the argument as an iterable. – Cameron Feb 28 '15 at 19:47
@Martijn Pieters -- thanks for adding the specific answer even though it was a duplicate. Apologies for the duplicate, I didn't find that answer when I was searching. Appreciate the help. – rdh9 Feb 28 '15 at 20:08
@Cameron -- thanks for the additional info. – rdh9 Feb 28 '15 at 20:09

Python SQLite3 How to refer to loop in query

1 Answers1