Python MySQL - Hapus Data

Untuk menghapus record dari tabel MySQL, Anda perlu menggunakan DELETE FROMpernyataan. Untuk menghapus catatan tertentu, Anda perlu menggunakan klausa WHERE bersamanya.

Sintaksis

Berikut ini adalah sintaks dari query DELETE di MYSQL -

DELETE FROM table_name [WHERE Clause]

Contoh

Asumsikan kita telah membuat tabel di MySQL dengan nama EMPLOYEES sebagai -

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)

Dan jika kita telah memasukkan 4 record ke dalamnya menggunakan pernyataan INSERT sebagai -

mysql> INSERT INTO EMPLOYEE VALUES
   ('Krishna', 'Sharma', 19, 'M', 2000),
   ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'F', 5000),
   ('Mac', 'Mohan', 26, 'M', 2000);

Mengikuti pernyataan MySQL akan menghapus catatan karyawan dengan "Mac" FIRST_NAME.

mysql> DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac';
Query OK, 1 row affected (0.12 sec)

Jika Anda mengambil isi tabel, Anda hanya dapat melihat 3 catatan karena kami telah menghapus satu.

mysql> select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Krishna    | Sharma    | 20   | M    | 2000   |
| Raj        | Kandukuri | 21   | M    | 7000   |
| Ramya      | Ramapriya | 25   | F    | 5000   |
+------------+-----------+------+------+--------+
3 rows in set (0.00 sec)

Jika Anda menjalankan pernyataan DELETE tanpa klausa WHERE, semua rekaman dari tabel yang ditentukan akan dihapus.

mysql> DELETE FROM EMPLOYEE;
Query OK, 3 rows affected (0.09 sec)

If you retrieve the contents of the table, you will get an empty set as shown below −

mysql> select * from EMPLOYEE;
Empty set (0.00 sec)

Removing records of a table using python

DELETE operation is required when you want to delete some records from your database.

To delete the records in a table −

import mysql.connector package.
Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
Create a cursor object by invoking the cursor() method on the connection object created above.
Then, execute the DELETE statement by passing it as a parameter to the execute() method.

Example

Following program deletes all the records from EMPLOYEE whose AGE is more than 20 −

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
print("Contents of the table: ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Preparing the query to delete records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (25)

try:
   # Execute the SQL command
   cursor.execute(sql)
   
   # Commit your changes in the database
   conn.commit()
except:
   # Roll back in case there is any error
   conn.rollback()

#Retrieving data
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output

Contents of the table:
[('Krishna', 'Sharma', 22, 'M', 2000.0), 
   ('Raj', 'Kandukuri', 23, 'M', 7000.0), 
   ('Ramya', 'Ramapriya', 26, 'F', 5000.0), 
   ('Mac', 'Mohan', 20, 'M', 2000.0), 
   ('Ramya', 'Rama priya', 27, 'F', 9000.0)]

Contents of the table after delete operation:
[('Krishna', 'Sharma', 22, 'M', 2000.0), 
   ('Raj', 'Kandukuri', 23, 'M', 7000.0), 
   ('Mac', 'Mohan', 20, 'M', 2000.0)]