0

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?

rdh9
  • 665
  • 2
  • 11
  • 20

1 Answers1

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