CODEWITHSUNDEEP
pythonsqlsqlite
user826436
user826436

Reputation: 245

Python & SQLite3 Selecting from two tables

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.

Upvotes: 3

Views: 6967

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1122322

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.

Upvotes: 5

Detonavomek
Detonavomek

Reputation: 442

If I understand you:

cursor.execute("SELECT Users.name, contact.address FROM Users, contact WHERE contact.Id = Users.id;")
for row in cursor:
    name= row[0]
    address= row[1]
    text_file.write(name+" "+address)

Upvotes: 1

Related Questions