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