CODEWITHSUNDEEP
pythondatabasesqlitesql-insert
FlawlessCalamity
FlawlessCalamity

Reputation: 135

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

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?

Upvotes: 0

Views: 93

Answers (1)

AKX
AKX

Reputation: 168967

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)

Upvotes: 1

Related Questions