Python PostgreSQL 更新表

以下是PostgreSQL中UPDATE语句的语法

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
假设我们使用以下查询创建了一个名为CRICKETERS的表-
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条记录，如 −
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–
postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = 'Shikhar' ;
UPDATE 1
postgres=#
如果你检索FIRST_NAME为Shikhar的记录，你会发现年龄值已被改为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 –
 
postgres=# UPDATE CRICKETERS SET AGE = AGE+1;
UPDATE 5
如果你使用SELECT命令检索表的内容，你可以看到更新的值为 −
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)
使用python更新记录
psycopg2的游标类提供了一个名为execute()的方法。这个方法接受查询作为参数并执行它。
因此，使用python向PostgreSQL中的一个表插入数据：
导入 psycopg2 软件包。
使用 connect() 方法创建一个连接对象，将用户名、密码、主机（可选，默认为 localhost）和数据库（可选）作为参数传递给它。
通过在属性 autocommit 中设置false来关闭自动提交模式。
 
#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()
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)]
            
Python PostgreSQL
Python PostgreSQL 简介Python PostgreSQL 数据库连接Python PostgreSQL 创建数据库Python PostgreSQL 创建表Python PostgreSQL 插入数据Python PostgreSQL 选择数据Python PostgreSQL Where 语句Python PostgreSQL Order ByPython PostgreSQL 更新表Python PostgreSQL 删除数据Python PostgreSQL 删除一个表Python PostgreSQL LIMITPython PostgreSQL 连接Python PostgreSQL 游标对象