Python Data Access 简明教程
Python MySQL - Drop Table
您可以使用 DROP TABLE 语句删除整个表。您只需指定需要删除的表的名称即可。
Example
在删除表之前,请使用 SHOW TABLES 语句获取表列表,如下所示:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb |
+-----------------+
| contact |
| cricketers_data |
| employee |
| sample |
| tutorials |
+-----------------+
5 rows in set (0.00 sec)
以下语句将表 sample 完全从数据库中删除:
mysql> DROP TABLE sample;
Query OK, 0 rows affected (0.29 sec)
由于我们已从 MySQL 中删除了名为 sample 的表,如果您再次获取表列表,则不会在其中找到表名 sample。
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 语句删除表,但您在删除任何现有表时需要非常小心,因为删除表后丢失的数据将无法恢复。
要使用 python 从 MYSQL 数据库中删除表,请在游标对象上调用 execute() 方法,并将 drop 语句作为参数传递给它。
Example
以下表从数据库中删除名为 EMPLOYEE 的表。
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
如果您尝试删除数据库中不存在的表,则会出现错误,例如:
mysql.connector.errors.ProgrammingError: 1051 (42S02):
Unknown table 'mydb.employee'
您可以通过在 DELETE 语句中添加 IF EXISTS 来验证表是否存在,从而防止此错误。
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()