Python Data Access 简明教程

Python PostgreSQL - Update Table

您可以使用 UPDATE 语句修改 PostgreSQL 中现有表记录的内容。要更新特定行，您需要同时使用 WHERE 子句。

You can modify the contents of existing records of a table in PostgreSQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it.

Syntax

以下是 PostgreSQL 中 UPDATE 语句的语法：

Following is the syntax of the UPDATE statement in PostgreSQL −

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

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

以下语句修改了姓氏为 Shikhar 的板球运动员的年龄：

Following statement modifies the age of the cricketer, whose first name is Shikhar −

postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = 'Shikhar' ;
UPDATE 1
postgres=#

如果您检索 FIRST_NAME 为 Shikhar 的记录，您会发现其 age 值已更改为 45：

If you retrieve the record whose FIRST_NAME is Shikhar you observe that the age value has been changed to 45 −

postgres=# SELECT * FROM CRICKETERS WHERE FIRST_NAME = 'Shikhar';
first_name  | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
Shikhar     | Dhawan    | 45  | Delhi          | India
(1 row)
postgres=#

如果您未使用 WHERE 子句，所有记录的值都将被更新。以下 UPDATE 语句将 CRICKETERS 表中所有记录的年龄增加 1：

If you haven’t used the WHERE clause, values of all the records will be updated. Following UPDATE statement increases the age of all the records in the CRICKETERS table by 1 −

postgres=# UPDATE CRICKETERS SET AGE = AGE+1;
UPDATE 5

如果您使用 SELECT 命令检索表中的内容，您就能够看到更新后的值：

If you retrieve the contents of the table using SELECT command, you can see the updated values as −

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

Updating records 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 代码更新 Employee 表的内容并检索结果 -

Following Python code updates the contents of the Employee table and retrieves the results −

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

#Fetching all the rows before the update
print("Contents of the Employee table: ")
sql = '''SELECT * from EMPLOYEE'''
cursor.execute(sql)
print(cursor.fetchall())

#Updating the records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'"
cursor.execute(sql)
print("Table updated...... ")

#Fetching all the rows after the update
print("Contents of the Employee table after the update operation: ")
sql = '''SELECT * from EMPLOYEE'''
cursor.execute(sql)
print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

Contents of the Employee table:
[('Ramya', 'Rama priya', 27, 'F', 9000.0),
   ('Vinay', 'Battacharya', 20, 'M', 6000.0),
   ('Sharukh', 'Sheik', 25, 'M', 8300.0),
   ('Sarmista', 'Sharma', 26, 'F', 10000.0),
   ('Tripthi', 'Mishra', 24, 'F', 6000.0)]
Table updated......
Contents of the Employee table after the update operation:
[('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)]