Why wont my python list be inserted into my SQLITE database from a loop?

Question

I am having a very confusing time, I want to add default data to my table from a python list. However it fails every time to add the list data, but I can add hard coded data using the for loop I just don't see why it doesn't work with the list data.

this works, and updates the db:

categories=["test 1","test 2","test 3","test 4"]

cur = db.cursor()
for category in categories:
    cur.execute("INSERT INTO CATEGORIES (name) VALUES ('category')")
                    
db.commit()
cur.close()

this doesnt work:

categories=["test 1","test 2","test 3","test 4"]

cur = db.cursor()
for category in categories:
    cur.execute("INSERT INTO CATEGORIES (name) VALUES (?)",category)
                    
db.commit()
cur.close()

My CATEGORIES table has an id set to auto increment and the a name column. I am beyond confused. Would love some help?

AKX · Accepted Answer · 2020-12-18 11:04:16Z

1

You will need to wrap the category in an 1-tuple (and you'd have an N-tuple for N parameters in a query).

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE CATEGORIES (name TEXT)")

categories = ["test 1", "test 2", "test 3", "test 4"]
print("Inserting...")
cur = db.cursor()
for category in categories:
    cur.execute("INSERT INTO CATEGORIES (name) VALUES (?)", (category,))
db.commit()
print("Retrieving...")
for row in db.execute("SELECT * FROM CATEGORIES"):
    print(row)

prints out

Inserting...
Retrieving...
('test 1',)
('test 2',)
('test 3',)
('test 4',)

A more succinct way to write this would be to use executemany.

The generator ((cat,) for cat in categories) does the same tuple-wrapping here.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE CATEGORIES (name TEXT)")

categories = ["test 1", "test 2", "test 3", "test 4"]
print("Inserting...")
db.executemany(
    "INSERT INTO CATEGORIES (name) VALUES (?)",
    ((cat,) for cat in categories),
)
db.commit()
print("Retrieving...")
for row in db.execute("SELECT * FROM CATEGORIES"):
    print(row)

answered Dec 18, 2020 at 11:04

AKX

171k17 gold badges148 silver badges230 bronze badges

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

3 Comments

FlawlessCalamity Over a year ago

Great thank you this worked, just so I understand correctly for the future. When inserting from a list, should the list be a list of tuples (in best practice)?

AKX Over a year ago

Yes, since e.g. INSERT INTO CATEGORIES (name, something_else) would be input as [(name1, something1), (name2, something2), (name3, something3)] – there's no special case for only a single variable.

FlawlessCalamity Over a year ago

Thanks you all is working great now, thanks for ending my confusing its been about a 24hours worth of googling and staring at my screen wondering why.

Collectives™ on Stack Overflow

Why wont my python list be inserted into my SQLITE database from a loop?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related