CODEWITHSUNDEEP
pythonlistsqlitesql-insert
Christina
Christina

Reputation: 255

Insert python list into SQLite3 column

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!

Upvotes: 0

Views: 1774

Answers (2)

Serge Ballesta
Serge Ballesta

Reputation: 148910

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

Upvotes: 2

meow
meow

Reputation: 2191

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

Upvotes: 1

Related Questions