Adding a list of values to a column in sqlite table

Question

so i am able to create a new table in my database and harvest all the information from a file to fill all but the last column in the table using cursor, and execute as one would normally do.

the last column of information comes from a different source and is in my code as a list of integers. how would i go about adding this list of integers into my last column?

i've tried various loops and iterations, but i am just missing something (probably quite obvious) in how to phrase it properly.

connection = sqlite3.connect('database.db')
cursor = connection.cursor()
for j in list:
    cursor.execute('insert into Table (column) values (?)', j)
connection.commit()

and i get the following error:

Traceback (most recent call last):
  File "Homework/Homework7/bam_counting.py", line 157, in <module>
    cursor.execute('insert into Genes (counts) values (?)', j)
ValueError: parameters are of unsupported type

unutbu · Accepted Answer · 2012-11-12T20:51:58.013

If you use INSERT INTO... then sqlite will create a new row. You must use UPDATE table ... to update rows that already exist.

You will need to tell sqlite which row to add the new column value to. Typically, you would do it with a SQL statement like

UPDATE Table SET column = ? WHERE column2 = ?

where column2 is some other column in the table.

So something like this is slow but possible:

cursor.execute('SELECT column2 FROM Table ORDER BY column2')
column2 = [row[0] for row in cursor]
for j, c in zip(lst, column2):
    cursor.execute('UPDATE Table SET column = ? WHERE column2 = ?', [j, c])

A better solution (if possible) is to hold off INSERTing into the table until you've assembled all the information needed and then doing a single INSERT.

PS. If you re-arrange your program to collect all the data to be inserted into a list of rows, args, then you can call

cursor.executemany('INSERT INTO ...', args)

which will be faster than many calls to cursor.execute.

i see. thanks for the clarification. i guess i didn't realize that adding all the info at once would be that much better. i will try to rearrange the code to do that. — user1714887, Nov 13 '12 at 00:24

score 0 · Answer 2 · edited May 23 '17 at 12:00

A full example using INSERT INTO courtesy of Python Central:

# Import the SQLite3 module
import sqlite3
db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT)''')
users = [
    ('John', '5557241'), 
    ('Adam', '5547874'), 
    ('Jack', '5484522'), 
    ('Monthy',' 6656565')
]

c.executemany('''INSERT INTO users(name, phone) VALUES(?,?)''', users)
db.commit()

# Print the users
c.execute('''SELECT * FROM users''')
for row in c:
    print(row)

db.close()

See also Is it possible to insert multiple rows at a time in an SQLite database?

Adding a list of values to a column in sqlite table

2 Answers2