Python Data Access 简明教程

Python MySQL - Drop Table

您可以使用 DROP TABLE 语句删除整个表。您只需指定需要删除的表的名称即可。

You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete.

Syntax

以下是 MySQL 中 DROP TABLE 语句的语法:

Following is the syntax of the DROP TABLE statement in MySQL −

DROP TABLE table_name;

Example

在删除表之前,请使用 SHOW TABLES 语句获取表列表,如下所示:

Before deleting a table get the list of tables using the SHOW TABLES statement as follows −

mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| contact         |
| cricketers_data |
| employee        |
| sample          |
| tutorials       |
+-----------------+
5 rows in set (0.00 sec)

以下语句将表 sample 完全从数据库中删除:

Following statement removes the table named sample from the database completely −

mysql> DROP TABLE sample;
Query OK, 0 rows affected (0.29 sec)

由于我们已从 MySQL 中删除了名为 sample 的表,如果您再次获取表列表,则不会在其中找到表名 sample。

Since we have deleted the table named sample from MySQL, if you get the list of tables again you will not find the table name sample in it.

mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb  |
+-----------------+
| contact         |
| cricketers_data |
| employee        |
| tutorials       |
+-----------------+
4 rows in set (0.00 sec)

Removing a table using python

您可以根据需要使用 MYSQL 的 DROP 语句删除表,但您在删除任何现有表时需要非常小心,因为删除表后丢失的数据将无法恢复。

You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table.

要使用 python 从 MYSQL 数据库中删除表,请在游标对象上调用 execute() 方法,并将 drop 语句作为参数传递给它。

To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.

Example

以下表从数据库中删除名为 EMPLOYEE 的表。

Following table drops a table named EMPLOYEE from the database.

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 the list of tables print("List of tables in the database: ")
   cursor.execute("SHOW Tables") print(cursor.fetchall())

#Doping EMPLOYEE table if already exists cursor.execute
   ("DROP TABLE EMPLOYEE") print("Table dropped... ")

#Retrieving the list of tables print(
   "List of tables after dropping the EMPLOYEE table: ")
   cursor.execute("SHOW Tables") print(cursor.fetchall())

#Closing the connection conn.close()

Output

List of tables in the database:
[('employee',), ('employeedata',), ('sample',), ('tutorials',)]
Table dropped...
List of tables after dropping the EMPLOYEE table:
[('employeedata',), ('sample',), ('tutorials',)]

Drop table only if exists

如果您尝试删除数据库中不存在的表,则会出现错误,例如:

If you try to drop a table which does not exist in the database, an error occurs as −

mysql.connector.errors.ProgrammingError: 1051 (42S02):
   Unknown table 'mydb.employee'

您可以通过在 DELETE 语句中添加 IF EXISTS 来验证表是否存在,从而防止此错误。

You can prevent this error by verifying whether the table exists before deleting, by adding the IF EXISTS to the DELETE statement.

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 the list of tables
print("List of tables in the database: ")
cursor.execute("SHOW Tables")
print(cursor.fetchall())

#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
print("Table dropped... ")

#Retrieving the list of tables
print("List of tables after dropping the EMPLOYEE table: ")
cursor.execute("SHOW Tables")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output

List of tables in the database:
[('employeedata',), ('sample',), ('tutorials',)]
Table dropped...
List of tables after dropping the EMPLOYEE table:
[('employeedata',), ('sample',),
('tutorials',)]