Update sqlite3 db from lists

Question

The columns in my table look like this:

Some of the Number1 and Number2 columns are Null, and I am only trying to update the Null rows only. To do this, I have 6 lists:

List1 contains the values in Name1, in the same order
List2 contains the values in Name2, in the same order
List3 contains the values in Name1, but in a different order
List4 contains the values in Name2, but in the same order as List3

Lists 5 and 6 contains the values to be inserted in Number1 and Number2 respectively, and are in the same order as Lists 3 and 4.

So what I am trying to do is:

search the table for all the rows with Null values in Number1 AND Number2;
search Lists 3 OR 4 for values to match Name1 OR Name2, so if a match is found in list 3, the values for Number1 and Number2 should still be filled in from lists 5 and 6, respectively.

So the code I'm thinking of looks something like this:

for i in Name1:
    c.execute("SELECT * FROM Mytable WHERE Number1 IS NULL")
    if Name1 is List3:
        c.execute("INSERT INTO Mytable (Number1, Number2)" " VALUES (?, ?)",
(List5[i], List6[i]))
    elif Name2 is List4:
        c.execute("INSERT INTO Mytable (Number1, Number2)" " VALUES (?, ?)",
(List5[i], List6[i]))

However, when I run it, it adds rows to the table, but no values for Number1 or Number2. Where am I going wrong?

score 0 · Answer 1 · edited May 23 '17 at 12:31

Where am I going wrong?

Probably here:

if Name1 is List3:
[...]
elif Name2 is List4:

The is keyword tests for object identity, which isn't what you want here. And even the test for equality won't help you here, as the order in both lists are different.

>>> a = ["a", "b", "c"]
>>> b = a
>>> a is b
True
>>> a = ["a", "b", "c"]
>>> b = ["a", "b", "c"]
>>> a is b
False
>>> a == b
True
>>> b = ["b", "c", "a"]
>>> a == b
False

The resulting behaviour is that none of your if branches is executed. To achieve your goal you need to test if both lists contain the same members:

Testing if a list contains another list with Python

However, your question is unclear about the exact condition: Are Name1/List3 and Name2/List4 supposed to contain exactly the same members, or is it sufficient for one member to be contained in the other? You might be able to figure the solution out yourself; if not I suggest you open another question on this specific problem.

Update sqlite3 db from lists

1 Answers1