Brief syntax for inserting row into sqlite database using python sqlite3

Question

I have a csv file of size 360x120 that I want to import into my sqlite database row by row. For one row, I know that below syntax works if mytuple has 2 elements:

import sqlite3
conn = sqlite3.connect(dbLoc)
cur = conn.cursor()
mytuple = (a, b, c, ...) #some long tuple of 120 elements
cur.execute('INSERT INTO tablename VALUES (?, ?)', mytuple)

Problem is, my rows contain 120 columns and I can't really go type 120 question marks into the cur.execute() line. Actually I have, it works but yeah, it is not a good solution. One thing I have tried was:

cur.execute('INSERT INTO tablename VALUES ?', mytuple)

Thought it would just do ?=mytuple and replace ? with mytuple but it doesn't do that. A user comment on the article sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html shows such syntax, which would work for me but it does not:

t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)

As seen here he's able to replace a tuple into the execute string with a single ? used. How can I achieve the same with INSERT INTO tablename?

You could construct the 120 question marks with something like ','.join(itertools.repeat('?',120)). — Klaus D.
– Klaus D., Commented Sep 13, 2015 at 13:29
That's true, and as I said, I have done something like that and actually put 120 question marks there. But I still wanna know if there's a pretty and simple syntax something like I mentioned in my question. — Voyager
– Voyager, Commented Sep 13, 2015 at 13:33
I searched the docs and the web all day yesterday and didn't see anything either. For now, I'm going with '('+','.join(itertools.repeat('?',120))+')' but it would be very nice that this ugly little piece could be replaced with a single ?. Marked your comment as useful. — Voyager
– Voyager, Commented Sep 13, 2015 at 13:47
@KlausD.: to construct 120 question marks: '?'*120. Complete expression: '({}?)'.format('?, '*119) — jfs
– jfs, Commented Sep 13, 2015 at 14:12

jfs · Accepted Answer · 2015-09-13 14:40:07Z

1

sqlite3 doesn't support more concise syntax:

c.execute('INSERT INTO tablename VALUES ({}?)'.format('?,'*(len(t) - 1)), t)

Note: the default SQLITE_MAX_COLUMN is 2000. And some algorithms in SQLite are O(n**2) in the number of columns i.e., if you increase the limit; it may slow down db operations.

answered Sep 13, 2015 at 14:40

jfs

417k210 gold badges1k silver badges1.7k bronze badges

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

1 Comment

Voyager Over a year ago

Can't believe how I couldn't think of '?, '*119...This is better than my '('+blahblah+')' syntax. Thank you.

Collectives™ on Stack Overflow

Brief syntax for inserting row into sqlite database using python sqlite3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related