sqlite3 in Python: Update a column in one table through a column from another table when the primary key is the same

Question

I want to use sqlite3 in Python. I have a table with four columns (id INT, other_no INT, position TEXT, classification TEXT, PRIMARY KEY is id). In this table, the column for classification is left empty and will be updated by the information from table 2. See my code below. I then have a second table which has three columns. (id INT, class TEXT, type TEXT, PRIMARY KEY (id)). Basically, the two tables have two common columns. In both tables, the primary key is the id column, the classification and class column would eventually have to be merged. So the code needs to be able to go through table 2 and whenever it finds a matching id in table 1 to updating the class column (of table 1) from the classification column of table 2. The information to build the two tables comes from two separate files.

# function to create Table1...
# function to create Table2...

(the tables are created as expected). The problem occurs when I try to update table1 with information from table2.

def update_table1():
    con = sqlite3.connect('table1.db', 'table2.db')  #I know this is wrong but if how do I connect table2 so that I don't get error that the Table2 global names is not defined? 

    cur = con.cursor()
    if id in Table2 == id in Table1:
        new_classification = Table2.class # so now instead of Null it should have the class information from table2
    cur.execute("UPDATE Table1 SET class = ? WHERE id =? ", (new_classification, id))
    con.commit()

But, I get an error for line2: TypeError: a float is required. I know that it's because I put two parameters in the connect method. But then if I only connect with Table1 I get the error Table2 is not defined.

I read this post Updating a column in one table through a column in another table I understand the logic around it but I can't translate the SQL code into Python. I have been working on this for some time and can't seem to just get it. Would you please help? Thanks

After the comments of a user I got this code but it still doesn't work:

#connect to the database containing the two tables 
cur.execute("SELECT id FROM Table1")
for row in cur.fetchall():
    row_table1 = row[0]

cur.execute("SELECT (id, class) FROM Table2")
for row1 in cur.fetchall():
    row_table2 = row[0]    #catches the id
    row_table2_class = row[1]    #catches the name
if row_table1 == row_table2:
    print "yes"               #as a test for me to see the loop worked
    new_class = row_table_class
cur.execute("UPDATE Table1 SET classification=? WHERE id=?", (new_class, row_table1))
con.commit()

From this however I get an operational error. I know it's my syntax, but like I said I am new to this so any guidance is greatly appreciated.

If you think that constructing Table2 is unnecessary and I can just update the relevant information to Table1 from the file that was used to make Table2 then again I am open to that suggestion. Hope you can help you. Thanks – — FgS2, Jun 16 '15 at 22:56
Not really sure what you are doing. Do you have 2 tables in one file or 2 files with a table each? What are Table1 and Table2? — Josep Valls, Jun 16 '15 at 23:53
Why would you need to *translate* SQL code? You just *execute* it! — CL., Jun 17 '15 at 10:29
@Josep Valis: I don't know how to execute the SQL code using python but I have to use sqlite3 module to do it because of facility restrictions. I have 2 files that could make 2 tables in one database. Table 1 is what I make from the information from File 1 and Table 2 is what I make from the information from File2. — FgS2, Jun 18 '15 at 06:27

Sandman · Answer 1 · 2015-06-18T18:58:33.500

You need a lot more code than what you have there. Your code logic should go something like this:

connect to sqlite db
execute a SELECT query on TABLE2 and fetch rows. Call this rows2.
execute a SELECT query on TABLE1 and fetch rows. Call this rows1.
For every id in rows1, if this id exists in rows2, execute an UPDATE on that particular id in TABLE1.

You are missing SELECT queries in your code:

cur = con.cursor()
if id in Table2 == id in Table1:
    new_classification = Table2.class

You can't just directly test like this. You need to first fetch the rows in both tables using SELECT queries before you can test them out the way you want.

Find below modified code from what you posted above. I have just typed that code in here directly, so I have not had the chance to test it, but you can look at it to get an idea. This could probably even run.

Also, this is by no means the most efficient way to do this. This is actually very clunky. Especially because for every id in Table1, you are fetching all the rows for Table2 everytime to match. Instead, you would want to fetch all the rows for Table1 once, then all the rows for Table2 once and then match them up. I will leave the optimization to make this faster upto you.

import sqlite3

#connect to the database containing the two tables
conn = sqlite3.connect("<PUT DB FILENAME HERE>")

cur = conn.execute("SELECT id FROM Table1")
for row in cur.fetchall():
    row_table1_id = row[0]

    cur2 = conn.execute("SELECT id, class FROM Table2")
    for row1 in cur2.fetchall():
        row_table2_id = row1[0]  # catches the id
        row_table2_class = row1[1]  # catches the name

        if row_table1_id == row_table2_id:
            print "yes"  # as a test for me to see the loop worked
            new_class = row_table2_class
            conn.execute("UPDATE Table1 SET classification=? WHERE id=?", (new_class, row_table1_id))
            conn.commit()

Thank you for your answer. I will try and follow your advice. — FgS2, Jun 18 '15 at 04:07
This is all I got for now but I doesn't seem to do the trick just yet I have added it to the question. Please check and if you can spot the mistake please let me know. — FgS2, Jun 18 '15 at 06:27

sqlite3 in Python: Update a column in one table through a column from another table when the primary key is the same

1 Answers1