[SOLVED] python mysql - can not delete row from database

Issue

I am trying to delete a row from my database but nothing worked. here is my code:

import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="user",passwd="1",database="workers")
mycursor = mydb.cursor()
query = "DELETE FROM `workers` WHERE id = %s"
# connect to the database server
# execute the query
mycursor.execute(query, (1,)) 
mysql.commit()  # You need to commit the transaction

Note: I am using python3.10, and my operating system is ubuntu. I also try to use %d instead of %s, but it does not work.

Solution

I created a test table:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.2.0     |
+-----------+

mysql> create table workers (id serial primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into workers values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from workers;
+----+
| id |
+----+
|  1 |
|  2 |
+----+

Demo script delete-test.py:

import mysql.connector

if __name__ == '__main__':
    config = { ... }

    mydb = mysql.connector.connect(**config)
    mycursor = mydb.cursor()

    # FIXED: use %s instead of %d
    query = "DELETE FROM `workers` WHERE id = %s"

    mycursor.execute(query, (1,))

    # FIXED: mydb instead of mysql 
    mydb.commit()

    mycursor.close()
    mydb.close()

Run script:

% python3 --version
Python 3.11.4
% python3 ./delete-test.py
%

Check results:

mysql> select * from workers;
+----+
| id |
+----+
|  2 |
+----+

Answered By - Bill Karwin

Answer Checked By - Mary Flores (WPSolving Volunteer)