Python Data Access 简明教程

Python PostgreSQL - Drop Table

你可以使用 DROP TABLE 语句从 PostgreSQL 数据库中删除一张表。

You can drop a table from PostgreSQL database using the DROP TABLE statement.

Syntax

以下是 PostgreSQL 中 DROP TABLE 语句的语法 −

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

DROP TABLE table_name;

Example

假设我们已经使用以下查询创建了两个名为 CRICKETERS 和 EMPLOYEES 的表 −

Assume we have created two tables with name CRICKETERS and EMPLOYEES using the following queries −

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int,
   Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#
postgres=# CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT,
   SEX CHAR(1), INCOME FLOAT
);
CREATE TABLE
postgres=#

现在，如果你使用 “\dt” 命令验证表列表，你可以看到以上创建的表，如下所示 −

Now if you verify the list of tables using the “\dt” command, you can see the above created tables as −

postgres=# \dt;
List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
public  | employee   | table | postgres
(2 rows)
postgres=#

以下语句从数据库中删除名为 Employee 的表 −

Following statement deletes the table named Employee from the database −

postgres=# DROP table employee;
DROP TABLE

由于你已经删除了 Employee 表，因此如果你再次检索表列表，则只会在其中观察到一张表。

Since you have deleted the Employee table, if you retrieve the list of tables again, you can observe only one table in it.

postgres=# \dt;
List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

如果你再次尝试删除 Employee 表，由于你已经删除过它，因此你会收到一个错误，表明 “表不存在”（如下所示） −

If you try to delete the Employee table again, since you have already deleted it, you will get an error saying “table does not exist” as shown below −

postgres=# DROP table employee;
ERROR: table "employee" does not exist
postgres=#

要解决此问题，你可以将 IF EXISTS 子句与 DELTE 语句一起使用。这将在表存在时将其删除，否则将跳过 DLETE 操作。

To resolve this, you can use the IF EXISTS clause along with the DELTE statement. This removes the table if it exists else skips the DLETE operation.

postgres=# DROP table IF EXISTS employee;
NOTICE: table "employee" does not exist, skipping
DROP TABLE
postgres=#

Removing an entire table using Python

根据实际需要，可以使用 DROP 语句随时删除表格。但删除任何现有表格时必须小心谨慎，因为删除表格后丢失的数据将无法恢复。

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

import psycopg2

#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)

#Setting auto commit false
conn.autocommit = True

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

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

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

Output

#Table dropped...