在本教程中，我们将学习使用Python从MySQL数据库中删除数据。

删除表数据

要从表中删除数据，我们执行以下步骤。

• 使用mysql.connector.connect()方法连接到MySQL服务器。

• 从步骤1中创建的连接创建MySQL游标。

• 使用游标执行DELETE查询。

• 使用commit()方法保存更改。

• 关闭连接。

在本教程中，我们将使用"员工"表。

该表已经有一些行，如下所示。

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2016-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2016-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2016-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2016-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2016-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2016-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2016-01-05 13:14:15 |
| e08        | Dr.       | Who      | 2016-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)

删除记录

要从表中删除记录，我们借助execute()方法的帮助，并传递SQL查询和值，以帮助我们从表中删除记录。

在下面的Python程序中，我们将删除ID为e08的员工。

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:

    cnx = mysql.connector.connect(
        user='theitroadtheitroad',
        password='',
        host='127.0.0.1',
        database='mydb'
    )

except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')

# now delete data
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # placeholder
    employeeid = 'e08'
    
    # sql query
    query = "DELETE FROM employee WHERE employeeid = %s"
    
    # data
    data = (employeeid,)
    
    # execute
    cnxCursor.execute(query, data)
    
    # commit
    cnx.commit()
    
    # total number of rows inserted
    print("Total rows deleted: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    
    print("Error:", err.message)
    
    # close connection
    cnx.close()

except:

    print("Unknown error occurred!")

    # close connection
    cnx.close()

finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

如果成功，上面的代码将为我们提供以下输出。

Total rows deleted: 1

如果我们检查"员工"表，将得到以下输出。

mysql> SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2016-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2016-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2016-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2016-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2016-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2016-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2016-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
7 rows in set (0.00 sec)