sqlite3 update/adding data to new column

Question

I made new column with NULL values called 'id' in table. Now I want to add data to it from list. It holds about 130k elements.

I tried with insert, it returned error:

conn = create_connection(xml_db)
cursor = conn.cursor()
with conn:
    cursor.execute("ALTER TABLE xml_table ADD COLUMN id integer")
    for data in ssetId:
        cursor.execute("INSERT INTO xml_table(id) VALUES (?)", (data,))
        conn.commit()

I also tried with update:

conn = create_connection(xml_db)
cursor = conn.cursor()
with conn:
    cursor.execute("ALTER TABLE xml_table ADD COLUMN id INTEGER")
    for data in ssetId:
        cursor.execute("UPDATE xml_table SET ('id' = ?)", (data,))
        conn.commit()

What is incorrect here ?

EDIT for clarification.

The table was already existing, filled with data. I want to add column 'id' with custom values to it.

I'm confused why you have to alter the table after its been created. Is there some reason you can't create the table with the column to begin with? Something like `create table if not exists xml_table (id INTEGER);` — Sean Breckenridge, Dec 08 '18 at 07:09
If you want to update a specific row, you have to have a WHERE clause in the UPDATE to limit it to just one... otherwise it'll update every single row with the new value. — Shawn, Dec 08 '18 at 07:10
@SeanBreckenridge The table was already made and holds values with columns. I want to add new column and fill it with data from list. — flowian, Dec 08 '18 at 07:51

Sean Breckenridge · Accepted Answer · 2018-12-08T08:29:30.107

0

Heres an example similar to yours which may be useful.

import sqlite3

conn = sqlite3.connect("xml.db")

cursor = conn.cursor()

with conn:

    # for testing purposes, remove this or else the table gets dropped whenever the file is loaded
    cursor.execute("drop table if exists xml_table")

    # create table with some other field
    cursor.execute("create table if not exists xml_table (other_field integer not null)")

    for other_data in range(5):
        cursor.execute("INSERT INTO xml_table (other_field) VALUES (?)", (other_data,))

    # add id field
    cursor.execute("ALTER TABLE xml_table ADD COLUMN id integer")

    # make sure the table exists
    res = cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    print("Table Name: {}".format(res.fetchone()[0]))

    # add data to the table
    for data in range(5):
        cursor.execute("UPDATE xml_table SET id = ? WHERE other_field = ?", (data, data))

    # if you must insert an id, you must specify a other_field value as well, since other_field must be not null
    cursor.execute("insert into xml_table (id, other_field) VALUES (? ,?)", (100, 105))


    # make sure data exists
    res = cursor.execute("SELECT id, other_field FROM xml_table")
    for id_result in res:
        print(id_result)

    conn.commit()

conn.close()

As I stated in the comment below, since one of your rows has a NOT NULL constraint on it, no rows can exist in the table that have that column NULL. In the example above other_field is specified NOT NULL, therefore there can be no rows that have NULL values in the column other_field. Any deviation from this would be an IntegrityError.

Output:

Table Name: xml_table
(0, 0)
(1, 1)
(2, 2)
(3, 3)
(4, 4)
(100, 105)

edited Dec 08 '18 at 08:29

answered Dec 08 '18 at 07:26

Sean Breckenridge

1,932
16
26

Edited OP for clarification - Table is already filled with data and columns. I want to add new column and fill its values from list. – flowian Dec 08 '18 at 07:53
@flowian Edited. I'm confused why this is an issue, it doesn't feel right to add to a table in the middle of execution. In any case, What is the error you're getting? – Sean Breckenridge Dec 08 '18 at 07:58
`IntegrityError: NOT NULL constraint failed: xml_table.column_1` The error is about: `cursor.execute("INSERT INTO xml_table(id) VALUES (?)", (data,))` – flowian Dec 08 '18 at 08:04
**it doesn't feel right to add to a table in the middle of execution.** You mean that `conn.commit()` should be out of `for` cycle ? – flowian Dec 08 '18 at 08:05
No, just in general. If you need to add a column for something, why doesnt it exist in the first place. The `IntegrityError` states that you tried to insert some `id` value into `xml_table`, but there was another column that has a `not null` constraint on it, and you're not respecting that. In other words, there can be no rows in the table that have `xml_table.column_1` null, and many of the inserts attempted in `INSERT INTO xml_table(id) VALUES (?)` pay no regard to `column1`. I shall update my answer to reflect this constraint. – Sean Breckenridge Dec 08 '18 at 08:17
I see. Is it possible fill new column while keeping other columns untouched though? – flowian Dec 08 '18 at 08:36
Theres [no way to alter a column](https://stackoverflow.com/questions/4007014/]) after creating it in sqlite. If possible, and if it makes sense for your data, you could remove the not null constraint from `column1` (which would be located at its definition/creation), but Im unsure of what that means for your data. Currently, the `NOT NULL` constraint essentially means "Any row that gets inserted must have this column". You could insert it with a dummy value for `column1`, but then you're breaking the error checking thats built into the table. – Sean Breckenridge Dec 08 '18 at 08:44

sqlite3 update/adding data to new column

1 Answers1