How do i join functions within Python MySql program?

Question

So i want to have it so that i have 6 columns. But the 6th is for orders.

The way i see it is VNr is connected to vare and ordrelinje. And thats where OrdreNr comes in which is number of orders, which is what i want.

So i tried write something like this. But any hint on how you would make it work

def DisplayData():
     sqlCon = pymysql.connect(host = "localhost", user = "root", password = "root", database = "varehusdb")
     cur =sqlCon.cursor()

     sql="SELECT VNr, Betegnelse, Pris, KatNr, Antall, OrdreNr FROM vare JOIN ordrelinje ON vare.VNr = ordrelinje.VNr"
     cur.execute(sql)
     result = cur.fetchall()
     if len(result) !=0:
         self.varer_records.delete(*self.varer_records.get_children())
         for row in result:
             self.varer_records.insert('', END, values =row)
         sqlCon.commit()
     sqlCon.close()

When i try to enter this in MySql it works like a charm. But for some reason i get this error when i try to click "Display" button in Python application

"pymysql.err.OperationalError: (1052, "Column 'VNr' in field list is ambiguous") "

That's a SQL error, it has nothing to do with Python. You don't specify which table the vnr column should come from. As there are two tables in scope with that column name, your reference is ambiguous. Instead, fully qualify your column names; table.column, like you already did in the join. — MatBailie
– MatBailie, Commented Jan 31, 2024 at 9:13

Stefano Mior · Accepted Answer · 2024-01-31 09:17:04Z

1

sql="SELECT vare.VNr, Betegnelse, Pris, KatNr, Antall, OrdreNr FROM vare JOIN ordrelinje ON vare.VNr = ordrelinje.VNr"

Try to put the table name before the field name

answered Jan 31, 2024 at 9:17

Stefano Mior

112 bronze badges

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

1 Comment

LarsWiseGuy Over a year ago

You saved me! Thanks. Putting "vare.VNr" same with others worked!

JohnyCapo · Accepted Answer · 2024-01-31 10:16:51Z

0

You should always define from which table you are selecting information every time you join.

Simply use this "template":

SELECT d1.[column_name], d2.[column_name] .... 
FROM table1 d1 
JOIN table2 d2 ON d1.smth = d2.smth
JOIN .......

You can always define custom name of the table once you are JOINing it or FROMing it.

This will save you from alot of trouble.

answered Jan 31, 2024 at 10:16

JohnyCapo

3153 silver badges14 bronze badges

Collectives™ on Stack Overflow

How do i join functions within Python MySql program?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related