Insert python list into SQLite3 column

Question

I have three python lists with about 1.5 million entries each and would like to insert these into a new SQLite table. When doing this I get the error:

OperationalError: no such column: days

This is the code I have:

con = sqlite3.connect('database.db')
cur = con.cursor()
...
cur.execute("DROP TABLE IF EXISTS days")
cur.execute("CREATE TABLE IF NOT EXISTS days(DAYS_NEEDED integer, RAISED_TIME text, POSTED_TIME text)")
cur.execute("INSERT INTO days (DAYS_NEEDED, RAISED_TIME, POSTED_TIME) VALUES (days, rt_list, pt_list)")
con.commit()

"days" is a list of integers, rt_list and pt_list are both lists of strings. Does anyone know what I'm doing wrong here?

Any help is much appreciated!

your lists are strings, that wont work this way, you have to use VALUES (?, ?, ?) — meow
– meow, Commented Jun 22, 2018 at 8:54
@meow Do you mean like this? cur.execute("INSERT INTO days (DAYS_NEEDED, RAISED_TIME, POSTED_TIME) VALUES (?,?,?)", (days, rt_list, pt_list)) — Christina
– Christina, Commented Jun 22, 2018 at 8:58
yes like this but I think it expects a list of tuples and not a tuple of lists (although I never tried that, so please tell me if that works too). See my answer for a more detailed version, that should work directly. — meow
– meow, Commented Jun 22, 2018 at 9:09

Serge Ballesta · Accepted Answer · 2018-06-22 09:25:21Z

That's not the way you can insert list of values in SQL. First, you must give a valid SQL instruction using ? as placeholders. Then if you want to insert more than one row at a time, you will need the executemany method. It is a true improvement because the SQL in only parsed and prepared once.

So you should have written:

cur.execute("DROP TABLE IF EXISTS days")
cur.execute("CREATE TABLE IF NOT EXISTS days(DAYS_NEEDED integer, RAISED_TIME text, POSTED_TIME text)")
cur.executemany("INSERT INTO days (DAYS_NEEDED, RAISED_TIME, POSTED_TIME) VALUES (?,?,?)",
                zip(days, rt_list, pt_list))
con.commit()

BTW, the direct usage of zip is a Sqlite3 module extension, the DB-API 2.0 Python interface normally requires a sequence where zip returns an iterator, so the more portable way (any DB engine) would be:

cur.executemany("INSERT INTO days (DAYS_NEEDED, RAISED_TIME, POSTED_TIME) VALUES (?,?,?)",
                tuple(zip(days, rt_list, pt_list)))

meow · Accepted Answer · 2018-06-22 09:01:28Z

You have to use ? placeholders inside your VALUES() and then provide the actual values to the execute method. Something along the lines should do the job:

con = sqlite3.connect('database.db')
cur = con.cursor()
...
cur.execute("DROP TABLE IF EXISTS days")
cur.execute("CREATE TABLE IF NOT EXISTS days(DAYS_NEEDED integer, RAISED_TIME text, POSTED_TIME text)")

def insert(days_needed, rt, pt):
    cur.execute("INSERT INTO days (DAYS_NEEDED, RAISED_TIME, POSTED_TIME) VALUES (?, ?, ?)", (days_needed, rt, pt))

for d, rt, pt in zip(days, rt_list, pt_list):
    insert(d, rt, pt)

con.commit()

Collectives™ on Stack Overflow

Insert python list into SQLite3 column

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related