sqlite bindings error
(1) By anonymous on 2021-02-10 19:01:39 [link] [source]
(API Call Happens and Works)
r = requests.get(url, params=data) #<-- This is CSV
btc_price_rows = r.text <-- I can print this and see the correct data
connect to db, create tables and insert
c.execute("CREATE TABLE IF NOT EXISTS btc_price_table (btc_price_timestamp TEXT, btc_price_value REAL)")
values = (btc_price_rows,)
c.executemany("INSERT OR REPLACE INTO btc_price_table VALUES (?, ?)", (values,)) <-- I think this is the problem but not sure why
I get this error #
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 19931624 supplied.
My best guess here is that it has something to do with r and btc_price_rows are being presented to sqlite, I guess it's confused about columns vs data, it thinks every row of data is a unique column value? Hence the expecting 2 got 19931624 error?
if I print the data out it looks like this, just two columns, time and value.
2021-02-10T18:40:00Z,45016.18724409042
I read that ISO8601 RFC3339 is the time format which I believe sqlite wants it defined as datatype TEXT.
Appreciate any help on figuring this out.
(2.3) By Keith Medcalf (kmedcalf) on 2021-02-10 19:42:02 edited from 2.2 in reply to 1 [source]
That is only one value that is a text string that contains an embedded ",".
What you think are "multiple rows" are really just "\n" separated text.
The content of btc_price_rows is not rows of fields. It is one big text field that you need to parse into a list of tuples. .executemany requires a sequence of sequences/dictionaries as the second parameter. That is, each member of the outer sequence is used as the binding for the statement. That is:
c.executemany(SQL, bindings)
is semantically equivalent to
for onebinding in bindings:
c.execute(SQL, onebinding)
without the overhead of re-preparing the SQL for each iteration.
You probably want something like this:
btc_price_rows = []
for line in requests.get(url, params=data).text:
btc_price_rows.append(tuple(line.strip('\r\n').split(',')))
c.executemany("INSERT OR REPLACE INTO btc_price_table VALUES (?, ?)", btc_price_rows)
(3) By David Raymond (dvdraymond) on 2021-02-10 20:14:10 in reply to 1 [link] [source]
See Keith's response. But also, are you sure you copied and pasted everything correctly? I swear this should complain about only having 1 supplied. .executemany(sql, (values,)) should basically run .execute(sql, values) once, since (values,) is a 1 element tuple. And "values = (btc_price_rows,)" means values itself is also a 1 element tuple. So shouldn't it complain about only having 1 binding supplied? I would bet 19931624 is the size of r.text, and you did either .executemany(sql, (btc_price_rows,)) or you did values = btc_price_rows or something similar.