Python Data Access 简明教程

Python MySQL - Delete Data

要从 MySQL 表格中删除记录，需要使用 DELETE FROM 语句。要移除特定记录，需要同时使用 WHERE 子句。

To delete records from a MySQL table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it.

Syntax

以下是 MYSQL 中 DELETE 查询的语法：

Following is the syntax of the DELETE query in MYSQL −

DELETE FROM table_name [WHERE Clause]

Example

假设我们在 MySQL 中使用 EMPLOYEES 作为名称创建了一个表格，如下所示：

Assume we have created a table in MySQL with name EMPLOYEES as −

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)

如果使用 INSERT 语句向其中插入了 4 条记录，如下所示：

And if we have inserted 4 records in to it using INSERT statements as −

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);

以下 MySQL 语句删除了 FIRST_NAME 为“Mac”的员工记录。

Following MySQL statement deletes the record of the employee with FIRST_NAME ”Mac”.

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

如果检索表格内容，可以看到只有 3 条记录，因为我们删除了一条记录。

If you retrieve the contents of the table, you can see only 3 records since we have deleted one.

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)

如果没有 WHERE 子句，执行 DELETE 语句会删除指定表格中的所有记录。

If you execute the DELETE statement without the WHERE clause all the records from the specified table will be deleted.

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 操作。

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

以下程序删除了 EMPLOYEE 中 AGE 超过 20 岁的所有记录：

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)]