Python Data Access 简明教程

Python PostgreSQL - Delete Data

您可以使用PostgreSQL数据库中的 DELETE FROM 语句删除现有表中的记录。要删除特定记录，您需要与WHERE子句一起使用它。

You can delete the records in an existing table using the DELETE FROM statement of PostgreSQL database. To remove specific records, you need to use WHERE clause along with it.

Syntax

以下是PostgreSQL中DELETE查询的语法 −

Following is the syntax of the DELETE query in PostgreSQL −

DELETE FROM table_name [WHERE Clause]

Example

假设我们使用以下查询创建了一个名为 CRICKETERS 的表 -

Assume we have created a table with name CRICKETERS using the following query −

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=#

并且如果我们使用 INSERT 语句向其中插入 5 条记录，如下所示 -

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

postgres=# insert into CRICKETERS values ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1

下面的语句删除了姓氏为“Sangakkara”的板球运动员的记录。 −

Following statement deletes the record of the cricketer whose last name is 'Sangakkara'. −

postgres=# DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
DELETE 1

如果您使用 SELECT 语句检索表中的内容，您只能看到 4 条记录，因为我们删除了其中一条。

If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one.

postgres=# SELECT * FROM CRICKETERS;
first_name  | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+-------------
Jonathan    | Trott     | 39  | CapeTown       | SouthAfrica
Virat       | Kohli     | 31  | Delhi          | India
Rohit       | Sharma    | 33  | Nagpur         | India
Shikhar     | Dhawan    | 46  | Delhi          | India
(4 rows)

如果您在没有WHERE子句的情况下执行DELETE FROM语句，将删除指定表中的所有记录。

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

postgres=# DELETE FROM CRICKETERS;
DELETE 4

由于您删除了所有记录，如果您尝试使用 SELECT 语句检索 CRICKETERS 表中的内容，您将获得空的结果集，如下所示：

Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below −

postgres=# SELECT * FROM CRICKETERS;
first_name  | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
(0 rows)

Deleting data using python

psycopg2 的 cursor 类提供了一个名为 execute() 的方法。此方法接受查询作为参数并执行该查询。

The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it.

因此，要使用 python 将数据插入到 PostgreSQL 中的表中 -

Therefore, to insert data into a table in PostgreSQL using python −

Import psycopg2 package.
Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
Turn off the auto-commit mode by setting false as value to the attribute autocommit.
The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.
Then, execute the UPDATE statement by passing it as a parameter to the execute() method.

Example

以下Python代码删除了年龄值大于25岁的EMPLOYEE表的记录 −

Following Python code deletes records of the EMPLOYEE table with age values greater than 25 −

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

#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())

#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')

#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

Contents of the table:
[('Ramya', 'Rama priya', 27, 'F', 9000.0),
   ('Sarmista', 'Sharma', 26, 'F', 10000.0),
   ('Tripthi', 'Mishra', 24, 'F', 6000.0),
   ('Vinay', 'Battacharya', 21, 'M', 6000.0),
   ('Sharukh', 'Sheik', 26, 'M', 8300.0)]
Contents of the table after delete operation:
[('Tripthi', 'Mishra', 24, 'F', 6000.0),
   ('Vinay', 'Battacharya', 21, 'M', 6000.0)]