deleting sql code with python and selecting from JOIN

Question

I have just started with Python and am already aware of the basics of SQL. In this code I'm trying to join tables and just test out python however I have two problems that I am not able to solve. Problem 1) Every time I run my code the data is entered a new. I understand why this is happening however to avoid this I need code that deletes duplicate data. Can somebody write a possible solution? Problem 2) When I use the the JOIN query and try to select first_name and job I recieve an error message telling me first_name is an unknown column??? This is my code:

import sqlite3
conn = sqlite3.connect('learning_sql')
c = conn.cursor()

def create_table1():
    c.execute('CREATE TABLE IF NOT EXISTS family (first_name TEXT, last_name TEXT, date_of_birth TEXT, age INTEGER)')

def create_table2():
    c.execute("CREATE TABLE IF NOT EXISTS jobs (first_name TEXT, last_name TEXT, job TEXT)")
    
def data_entry1():
    c.execute("INSERT INTO family VALUES ('Sven','Niles','31-03-2001', 16)")
    c.execute("INSERT INTO family VALUES ('Max','Niles','10-12-2005', 12)")
    c.execute("INSERT INTO family VALUES ('David','Niles','18-01-1971', 46)")
    c.execute("INSERT INTO family VALUES ('Katja', 'Niles', '16-08-1978', 39)")

def data_entry2():
    c.execute("INSERT INTO jobs VALUES ('Sven', 'Niles', 'NO')")
    c.execute("INSERT INTO jobs VALUES ('Max', 'Niles', 'NO')")
    c.execute("INSERT INTO jobs VALUES ('David', 'Niles', 'YES')")
    c.execute("INSERT INTO jobs VALUES ('Katja', 'Niles', 'YES')")

def read_from_db():
    c.execute("SELECT first_name, job FROM family JOIN jobs ON jobs.first_name = family.first_name WHERE job = 'YES'")
    data = c.fetchall()
    print (data)
    for row in data:
        print (row)

create_table1()
create_table2()
data_entry1()
data_entry2()
read_from_db()

this is the error message I am experiencing:

Traceback (most recent call last):
  File "/home/pi/Desktop/praciticing_sql.py", line 34, in <module>
    read_from_db()
  File "/home/pi/Desktop/praciticing_sql.py", line 24, in read_from_db
    c.execute("SELECT first_name, job FROM family JOIN jobs ON jobs.first_name = family.first_name WHERE job = 'YES'")
sqlite3.OperationalError: ambiguous column name: first_name

Traceback (most recent call last): File "/home/pi/Desktop/praciticing_sql.py", line 34, in <module> read_from_db() File "/home/pi/Desktop/praciticing_sql.py", line 24, in read_from_db c.execute("SELECT first_name, job FROM family JOIN jobs ON jobs.first_name = family.first_name WHERE job = 'YES'") sqlite3.OperationalError: ambiguous column name: first_name — Sven Niles
– Sven Niles, Commented Dec 17, 2017 at 17:23
Welcome to SO! As shown here, exact errors are important; "ambiguous" is very different from unknown. Always provide exact errors, in the body of your post (not comments). — erik258
– erik258, Commented Dec 17, 2017 at 19:18
Self-vandalism rolled back. See meta.stackexchange.com/questions/209436/… — General Grievance
– General Grievance, Commented May 4, 2022 at 16:20

Greynerd · Accepted Answer · 2017-12-17 17:53:54Z

1

You have two tables in your query with the field "first name". Your query needs to specify which one you want to reference.

Replace your select line with

SELECT jobs.first_name, job FROM family....

answered Dec 17, 2017 at 17:53

Greynerd

261 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CL. · Accepted Answer · 2017-12-17 19:28:48Z

0

You are already using CREATE TABLE IF NOT EXISTS, but it might be easier to wrap all four creating/insertion functions into a check for the database contents:

if not table_exists(conn, "family"):
    conn.execute("BEGIN")
    with conn:
        create_table1()
        create_table2()
        data_entry1()
        data_entry2()

read_from_db()

The column name first_name is ambiguous because it occurs in both tables. When you join using USING instead, the database will automatically remove the duplicate column:

c.execute("""SELECT first_name, job
             FROM family
             JOIN jobs USING (first_name)
             WHERE job = 'YES'""")

answered Dec 17, 2017 at 19:28

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

deleting sql code with python and selecting from JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related