Python SQLite3 How to refer to loop in query [duplicate]

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?

Martijn Pieters · Accepted Answer · 2015-02-28 19:45:57Z

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, 2015 at 19:45

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cameron Over a year ago

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.

rdh9 Over a year ago

@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 Over a year ago

@Cameron -- thanks for the additional info.

Collectives™ on Stack Overflow

Python SQLite3 How to refer to loop in query [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related