Python & SQLite3 Selecting from two tables
Question: I have written this code in python, which I basically opens up my SQLite3
database and looks at each row in the table 'contact' and then takes each 'id'
number and then looks at the matching 'id' in the table 'Users'. My problem is
that it only outputs the first one and does not loop through all the rows.

    
    
    import sqlite3
    
    conn = sqlite3.connect('sqlite3.db')
    
    cursor = conn.cursor()
    cursor2 = conn.cursor()
    cursor3 = conn.cursor()
    
    text_file = open("Output.txt", "w");
    try:
        cursor.execute("SELECT Id, address FROM contact;") # Get address details by ID
        for row in cursor:
            ID = row[0]
            address= row[1]
    
            cursor2.execute("SELECT name FROM Users WHERE id= " + str(ID) + ";") # Get users's name by ID
            row2 = cursor2.fetchone()
            sendername = row2[0]
    
            text_file.write(firstname, lastname, address);
    
    
    finally:
        conn.close()
    

Any suggestions, I'm very new to python.


Answer: You can ask the database to do a join instead:

    
    
    cursor.execute("""\
        SELECT u.name, c.address
        FROM contact c
        INNER JOIN Users u ON u.id = c.Id
        """)
    with open('Output.txt', 'w') as outfh:
        for name, address in cursor:
            outfh.write('{} {}\n'.format(name, address)
    

The `INNER JOIN` tells SQLite to only pick rows for which there is an actual
match on the `id` columns. If you marked the `id` column as a foreign key in
the `contact` table, you could use a `NATURAL INNER JOIN` as well, and omit
the `ON` clause.

