CODEWITHSUNDEEP
pythonlistsqlite
user1714887
user1714887

Reputation: 157

Adding a list of values to a column in sqlite table

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

Upvotes: 0

Views: 8905

Answers (2)

zelusp
zelusp

Reputation: 3678

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?

Upvotes: 0

unutbu
unutbu

Reputation: 879591

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.

Upvotes: 6

Related Questions