Insert new column with values from a python list into an existing sql table

Question

MY PROBLEM: I want to create a new column in an existing table with values from a python list. My SQL table looks like this:

   gender  age  cholesterol  smoke
        1   13            1      0
        1   45            2      0
        0    1            2      1

And I have values in a python list: new_data = [1,2,3] I want to create a new column in the SQL table and then populate it with the values in the list. So the final table should look like this:

   gender  age  cholesterol  smoke  new_data
        1   13            1      0         1
        1   45            2      0         2
        0    1            2      1         3

SO FAR: I've tried to create a new column and then try to pass the values:

import sqlite3
con = sqlite3.connect('database.db')
curs = con.cursor()
curs.execute('''ALTER TABLE demo ADD COLUMN new_date''')
con.commit()
new_data = [1,2,3]
curs.execute("INSERT INTO demo(new_date) VALUES(?)", new_data))

But it doesn’t work and gives this error:

ProgrammingError: Incorrect number of bindings supplied.

What should I do?

Here is the dump for the example data:

df = pd.DataFrame(data = {'gender' :      [1,  1,  0],
                          'age' :         [13, 45, 1],
                          'cholesterol' : [1,  2,  2],
                          'smoke' :       [0,  0,  1]
                          })

That's not possible - INSERT INTO is for creating new rows not adding data to existing rows. — norie
– norie, Commented Mar 21, 2021 at 17:36

forpas · Accepted Answer · 2021-03-21 21:25:46Z

1

You must update the table after you add the new column and not insert new rows.

Instead of the list new_data = [1,2,3] pass a list of lists new_data = [[1, 1], [2, 2], [3, 3]] with the 2nd item of each list is the row number of the row that will be updated.
So, if you wanted to update the column with the values 100, 150, 250 then new_data should be new_data = [[100, 1], [150, 2], [250, 3]].

The UPDATE statement will have a WHERE clause that will check which row should be updated each time:

new_data = [[1, 1], [2, 2], [3, 3]]
sql = """
UPDATE demo AS d
SET new_data = ?
WHERE (SELECT COUNT(*) FROM demo dd WHERE dd.rowid <= d.rowid) = ?
"""
curs.executemany(sql, new_data)
con.commit()

Since your version of SQLite is 3.33.0 you could also use the UPDATE...FROM syntax in your UPDATE statement:

sql = """
UPDATE demo AS d1
SET new_data = ?
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY rowid) rn FROM demo) AS d2
WHERE d2.rowid = d1.rowid AND rn = ?
"""

edited Mar 21, 2021 at 21:25

answered Mar 21, 2021 at 21:00

forpas

165k10 gold badges51 silver badges85 bronze badges

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

1 Comment

Tito Sanz Over a year ago

Thanks! Btw I've updated my SQLite version, syntax is more friendly!

Collectives™ on Stack Overflow

Insert new column with values from a python list into an existing sql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related