Nothing happens after executing an SQL command with python [duplicate]

Question

This is my code:

import mysql.connector 
mydb = mysql.connector.connect(host='localhost', 
                        database='glabo', 
                        user='root', 
                        password='.........') 
mycursor = mydb.cursor()
mycursor.execute("INSERT INTO orders (o_userid, o_labo, o_jour, o_heure) SELECT users.user_id, labos.l_id, jours.j_id, heures.h_id FROM users, labos, jours, heures WHERE users.user_name = 'Kbayad' AND labos.l_nom = 'LABO 1' AND jours.j_jour = 'Mardi' AND heures.h_heure = '10-12';")

After running this python code, I don't get any error but nothing changes in the database

Are you sure the INSERT's SELECT returns any data? Try INSERT with VALUES instead. — jarlh
– jarlh, Commented Jun 18, 2024 at 21:35
Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. (Not the answer, but still a good advice.) — jarlh
– jarlh, Commented Jun 18, 2024 at 21:37

Jaeeun Grace Lee · Accepted Answer · 2024-06-18 21:56:06Z

1

this is probably because MySQL transactions in Python are not auto-committed.

in MySQL, when you execute a query that modifies data (like INSERT), those changes are not automatically saved (or "committed") to the database.

you need to explicitly tell the database to save those changes by using the commit() method.

import mysql.connector

mydb = mysql.connector.connect(
    host='localhost', 
    database='glabo', 
    user='root', 
    password='.........'
)

mycursor = mydb.cursor()

mycursor.execute("""
    INSERT INTO orders (o_userid, o_labo, o_jour, o_heure) 
    SELECT users.user_id, labos.l_id, jours.j_id, heures.h_id 
    FROM users, labos, jours, heures 
    WHERE users.user_name = 'Kbayad' 
    AND labos.l_nom = 'LABO 1' 
    AND jours.j_jour = 'Mardi' 
    AND heures.h_heure = '10-12';
""")

mydb.commit()
mycursor.close()
mydb.close()

edited Jun 18, 2024 at 21:56

answered Jun 18, 2024 at 21:39

Jaeeun Grace Lee

264 bronze badges

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

1 Comment

Med Op Over a year ago

OOH THANKS IT'S WORKING NOW THANK YOU A LOT!

Collectives™ on Stack Overflow

Nothing happens after executing an SQL command with python [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related