Python PostgreSQL - 数据库连接

2025 年 1 月 5 日 | 22 分钟阅读

PostgreSQL 具有用于查询执行的内置 shell。在尝试连接之前，请确保 PostgreSQL 已在您的系统上正确安装。按照 PostgreSQL shell 的提示提供服务器、数据库、登录名和密码等信息。如果您提供的信息全部正确，则可以建立到 PostgreSQL 数据库的连接。

在提供详细信息时，您可以使用 shell 建议的默认服务器、数据库、端口和用户名。

Python 连接建立

连接实例由 psycopg2 连接类表示和管理。connect() 方法可用于建立新连接。

示例

要连接到已存在的数据库，请使用以下 Python 代码。如果数据库已构建，则将返回一个代表该数据库的对象。PostgreSQL 的默认数据库名为 postrgre。

 
import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="postgres", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
# Using the cursor() function to create a cursor object
cursor = conn.cursor()
# Using the execute() function to run a MySQL function
cursor.execute("select version()")
# Use the fetchone() function to retrieve a single row.
data = cursor.fetchone()
print("Connection established to: ",data)
#Close the connection
conn.close()   

Python PostgreSQL - 创建数据库

PostgreSQL 中的 CREATE DATABASE 语句可用于创建数据库。

语法

示例

以下 PostgreSQL 查询创建一个名为 testdb 的数据库。

 
postgres=# CREATE DATABASE testdb;
CREATE DATABASE   

使用 PostgreSQL 中的 \l 命令，您可以列出数据库。如果您检查数据库列表，可以找到新创建的数据库。

 
postgres=# \l
                                           List of databases
Name       | Owner    | Encoding | Collate                    | Ctype       |
-----------+----------+----------+----------------------------+-------------+
mydb       | postgres | UTF8     | English_United States.1252 | ........... |
postgres   | postgres | UTF8     | English_United States.1252 | ........... |
template0  | postgres | UTF8     | English_United States.1252 | ........... |
template1  | postgres | UTF8     | English_United States.1252 | ........... |
testdb     | postgres | UTF8     | English_United States.1252 | ........... |
(5 rows)
C:\Program Files\PostgreSQL\11\bin> createdb -h localhost -p 5432 -U postgres sampledb
Password:   

使用 Python 创建数据库

psycopg2 的 cursor 类提供了多种方法来执行各种 PostgreSQL 命令、检索记录和传输数据。Connection 类的 cursor() 函数可用于构造游标对象。

该类的 execute() 函数接受 PostgreSQL 查询作为参数并执行它。

因此，请使用此方法执行 PostgreSQL 中的 CREATE DATABASE 查询来构建数据库。

示例

以下 Python 示例创建一个名为 mydb 的 PostgreSQL 数据库。

 
import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="postgres", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
conn.autocommit = True
# Using the cursor() function to create a cursor object
cursor = conn.cursor()
# Getting ready to create a database query
sql = '''CREATE database mydb''';
#Database Creation
cursor.execute(sql)
print("Database created successfully........")
#Close the connection
conn.close()   

输出

 
Database created successfully........

Python PostgreSQL - 创建表

PostgreSQL 中的 CREATE TABLE 命令可用于在数据库中创建新表。执行此操作时，您必须提供表名、列名和数据类型。

语法

以下是 PostgreSQL CREATE TABLE 语句的语法。

 
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);   

示例

以下示例创建了一个名为 CRICKETERS 的 PostgreSQL 表。

 
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=# \dt
            List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#   

同样，您可以使用 \d 来获取已构建表的描述，如下所示。

 
postgres=# \d cricketers
               Table "public.cricketers"
Column          | Type                   | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
first_name      | character varying(255) |           |          |
last_name       | character varying(255) |           |          |
age             | integer                |           |          |
place_of_birth  | character varying(255) |           |          |
country         | character varying(255) |           |          |
postgres=#   

使用 Python 创建表

要使用 Python 创建表，您必须使用 pyscopg2 的 Cursor 的 execute() 函数来执行 CREATE TABLE 命令。

示例

以下 Python 示例使用 employee 表来创建。

 
import psycopg2
#Establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Create the cursor object using the cursor() method
cursor = conn.cursor()
#Drop the EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Create table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
#Close the connection
conn.close()   

输出

 
Table created successfully........

Python PostgreSQL - 插入数据

PostgreSQL 中的 INSERT INTO 命令可用于将记录插入到已存在的表中。执行此操作时，您必须提供表的名称以及其每个列的值。

语法

 
INSERT INTO TAB_NME (col1, col2, col3,...colN)
VALUES (val1, val2, val3,...valN);   

示例

假设，如下所示，我们已使用 CREATE TABLE 命令创建了一个名为 CRICKETERS 的表。

 
postgres=# CREATE TABLE CRICKETERS (
   fst_nme VARCHAR(255),
   lst_nme VARCHAR(255),
   age INT,
   pob VARCHAR(255),
   cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

以下 PostgreSQL 语句将一行添加到上面已创建的表中。

 
postgres=# insert into CRICKETERS (
   fst_nme, lst_nme, age, pob, cntry) 
   values('Ravi', 'Teja', 22, 'Bengalore', 'India');
INSERT 0 1
postgres=#   

如果您在使用 INSERT INTO 命令插入记录时省略了任何列名，则记录将在您跳过的列所在的位置以空值插入。

 
postgres=# insert into CRICKETERS (First_Name, Last_Name, Country) 
   values('Jonathan', 'Trott', '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
postgres=#   

插入数据后，您可以使用 SELECT 命令（如下所示）来确认表的内容。

 
postgres=# SELECT * from CRICKETERS;
fst_nme  | lst_nme  | age | pob| cntry
------------+------------+-----+----------------+-------------
Ravi     | Teja   |22  | Bengalore          | India
Jonathan    | Trott      |     |                | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)   

使用 Python 插入数据

execute() 函数是 psycopg2 游标类的一个特性。此函数在接受查询作为参数后执行它。

因此，要使用 Python 将数据插入 PostgreSQL 中的表，请执行以下操作：

导入 psycopg2 包。
通过向 connect() 函数提供用户名、密码、主机（可选，默认为 localhost）和数据库（可选）作为输入，您可以创建一个连接对象。
通过将 autocommit 属性设置为 false，您可以禁用自动提交选项。
游标对象是 psycopg2 库的 Connection 类的 cursor() 函数的结果。此函数使用它来创建游标对象。
接下来，使用 execute() 函数通过将其作为参数提供来执行 INSERT 语句。

示例

 
import psycopg2
#Establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
# Getting ready to insert a record into the database using SQL queries.
cursor.execute('''INSERT INTO EMP(FST_NME, LST_NME, AGE, 
   INCOME) VALUES ('Ramya', 'Rama priya', 27,  9000)''')
cursor.execute('''INSERT INTO EMP(FST_NME, LST_NME, AGE, 
   INCOME) VALUES ('Vinay', 'Battacharya', 20, 6000)''')
cursor.execute('''INSERT INTO EMP(FST_NME, LST_NME, AGE, 
   INCOME) VALUES ('Sharukh', 'Sheik', 25, 8300)''')
cursor.execute('''INSERT INTO EMP(FST_NME, LST_NME, AGE, 
   INCOME) VALUES ('Sarmista', 'Sharma', 26,  10000)''')
cursor.execute('''INSERT INTO EMP(FST_NME, LST_NME, AGE, 
   INCOME) VALUES ('Tripthi', 'Mishra', 24, 6000)''')
# Save your modifications to the database.
conn.commit()
print("Records inserted........")
# Close the connection
conn.close()   

输出

 
Records inserted........

Python PostgreSQL - 选择数据

PostgreSQL 中的 SELECT 命令可用于获取现有表的内容。

语法

以下是 PostgreSQL SELECT 语句的语法。

示例

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

 
postgres=# CREATE TABLE CRICKETERS (
   fst_nme VARCHAR(255), lst_nme VARCHAR(255), 
   age int, pob VARCHAR(255), cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

此外，假设我们使用 INSERT 命令向其中添加了五个记录，如下所示。

 
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   

 
postgres=# SELECT fst_nme, lst_nme, cntry FROM CRICKETERS;
fst_nme  | lst_nme  | cntry
------------+------------+-------------
Shikhar     | Dhawan     | India
Jonathan    | Trott      | SouthAfrica
Kumara      | Sangakkara | Srilanka
Virat       | Kohli      | India
Rohit       | Sharma     | India
(5 rows)   

如果您希望获取所有记录的所有列，则必须将列名更改为 "*"，如下所示。

 
postgres=# SELECT * FROM CRICKETERS;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)
postgres=#   

使用 Python 检索数据

读取任何数据库操作都涉及从数据库中检索一些相关数据。使用 psycopg2 提供的 fetch() 函数，您可以从 PostgreSQL 检索数据。

Cursor 类中有三种方法——fetchall()、fetchmany() 和 fetchone()——其中，

fetchall() 函数检索查询结果集中的所有行，然后提供一个元组列表。（如果我们从几个行检索后调用此函数，它将返回剩余的行）。
fetchone() 函数检索查询结果中的下一行并将其作为一个元组返回。
与 fetchone() 类似，fetchmany() 从查询的返回集中获取下一组行，而不是只获取第一行。

注意：当使用游标对象查询表时，会返回一个称为结果集的对象。

示例

 
import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving the data
cursor.execute('''SELECT * from EMPLOYEE''')
#Fetching 1st row from the table
result = cursor.fetchone();
print(result)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#modify  your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
('Ramya', 'Rama priya', 27, 'F', 9000.0)
('Sharukh', 'Sheik', 25, 'M', 8300.0),
('Sarmista', 'Sharma', 26, 'F', 10000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)]

Python PostgreSQL - WHERE 子句

使用 WHERE 子句，您可以在执行 SELECT、UPDATE 或 DELETE 操作时设置条件来过滤记录。满足指定标准的记录将被用于该过程。

语法

PostgreSQL 中 WHERE 子句的语法如下。

 
SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]   

示例

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

 
postgres=# CREATE TABLE CRICKETERS ( 
   fst_nme VARCHAR(255), lst_nme VARCHAR(255), 
   age int, pob VARCHAR(255), cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

此外，假设我们使用 INSERT 命令向其中添加了五个记录，如下所示。

 
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   

以下 SELECT 查询检索年龄大于 35 的条目。

 
postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35;
fst_name  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(2 rows)
postgres=#   

使用 Python 的 WHERE 子句

使用 Python 程序，通过执行带有 WHERE 子句的 SELECT 语句并将其作为参数发送到 execute() 函数，可以从表中获取特定条目。

示例

此 Python 示例演示了如何在 Python 中使用 WHERE 命令。

 
import psycopg2
#establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Droping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMP(
   fst_nme CHAR(20) NOT NULL,
   lst_nme CHAR(20),
   age INT,
   sex CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
#Populating the table
insert_stmt = "INSERT INTO EMP (fst_nme, lst_nme, age, sex, income) 
   values (%s, %s, %s, %s, %s)"
data = [('Raju', 'Raj', 18, 'M', 3000), 
   ('Mohan', 'Sai', 21, 'M', 8000),
   ('Ramya', 'Ramapriya', 25, 'M', 5000),
   ('Sac', 'Rohan', 25, 'M', 6000)]
cursor.executemany(insert_stmt, data)
# Finding certain entries with the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())
#Modify changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
[('Raju', 'Raj', 18, 'M', 3000.0), ('Mohan', 'Sai', 21, 'M', 8000.0)]

Python PostgreSQL - ORDER BY

语法

PostgreSQL ORDER BY 子句的语法如下。

 
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];   

示例

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

 
postgres=# CREATE TABLE CRICKETERS ( 
   fst_nme VARCHAR(255), lst_nme VARCHAR(255), 
   age int, pob VARCHAR(255), cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

此外，假设我们使用 INSERT 命令向其中添加了五个记录，如下所示。

 
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('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1   

以下 SELECT 查询使用参与者的年龄对 CRICKETERS 表的行进行排序检索。

 
postgres=# SELECT * FROM CRICKETERS ORDER BY AGE;
fst_nme  | lst_nme  | age | pob | country
------------+------------+-----+----------------+-------------
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
(3 rows)es:   

表的记录可以使用多个列进行排序。以下 SELECT 查询使用 FIRST_NAME 和 age 字段对 CRICKETERS 表的记录进行排序。

 
postgres=# SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
(3 rows)   

ORDER BY 子句默认按升序对表的记录进行排序。使用 DESC 作为，您可以按降序对结果进行排序。

 
postgres=# SELECT * FROM CRICKETERS ORDER BY AGE DESC;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Shikhar     | Dhawan     | 33  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(3 rows)   

使用 Python 的 ORDER BY 子句

要以指定顺序获取表的內容，请在游标对象上使用 execute() 函数，并将 SELECT 语句和 ORDER BY 子句作为参数提供给它。

示例

在下面的示例中，我们创建了一个具有 name 和 employee 列的表，填充它，然后使用 ORDER BY 子句按年龄升序检索数据。

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Droping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#Create a table
sql = '''CREATE TABLE EMP(
   FST_NME CHAR(20) NOT NULL,
   LST_NME CHAR(20),
   AGE INT, SEX CHAR(1),
   INCOME INT,
   CONTACT INT
)'''
cursor.execute(sql)
#Populate the table
insert_stmt = "INSERT INTO EMP (
   FST_NME, LST_NME, AGE, SEX, INCOME, CON) VALUES 
   (%s, %s, %s, %s, %s, %s)"
data = [('Raj', 'Sharma', 26, 'M', 3000, 103), 
   ('Taj', 'Sandukuri', 20, 'M', 8000, 104),
   ('Samya', 'Hamapriya', 29, 'F', 9000, 105),
   ('Lac', 'Rohan', 26, 'M', 2000, 106)]
cursor.executemany(insert_stmt, data)
conn.commit()
# Finding certain entries with the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())
#Modify your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
[('Sharukh', 'Sheik', 25, 'M', 8300.0), ('Sarmista', 'Sharma', 26, 'F', 10000.0)]

Python PostgreSQL - 更新表

使用 UPDATE 命令可以更改 PostgreSQL 表中现有记录的内容。您必须将其与 WHERE 子句结合使用，以便更新特定行。

语法

PostgreSQL UPDATE 语句的语法如下。

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

示例

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

 
postgres=# CREATE TABLE CRICKETERS ( 
   Fst_Nme VARCHAR(255), Lst_Nme VARCHAR(255), 
   Age int, POB VARCHAR(255), Cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

此外，假设我们使用 INSERT 命令向其中添加了五个记录，如下所示。

 
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';
fst_nme  | lst_nme | age | pob | cntry
------------+-----------+-----+----------------+---------
Shikhar     | Dhawan    | 45  | Delhi          | India
(1 row)
postgres=#   

如果未使用 WHERE 子句，则所有记录的值都将被更改。由于以下 UPDATE 语句，CRICKETERS 表中的所有记录的年龄都增加了一岁。

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

如果您使用 SELECT 命令获取表的内容，您将看到更改后的值如下。

 
postgres=# SELECT * FROM CRICKETERS;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
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 更新记录

execute() 函数是 psycopg2 游标类的一个特性。此函数在接受查询作为参数后执行它。

因此，为了使用 Python 将数据插入 PostgreSQL 数据库，请执行以下操作：

导入 psycopg2 包。
通过向 connect() 函数提供用户名、密码、主机（可选，默认为 localhost）和数据库（可选）作为输入，您可以创建一个连接对象。
通过将 autocommit 属性设置为 false，您可以禁用自动提交选项。
游标对象是 psycopg2 库的 Connection 类的 cursor() 函数的结果。此函数使用它来创建游标对象。
接下来，使用 execute() 函数通过将其作为参数提供来执行 UPDATE 语句。

示例

以下 Python 代码修改了 Employee 表的数据并获取了结果。

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create 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())
#Update 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())
#Modify your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
Contents of the Employee table:
  [ ('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:
  [ ('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 - 删除数据

PostgreSQL 数据库的 DELETE FROM 语句可用于删除现有表中的记录。您必须将其与 WHERE 子句结合使用，以删除特定数据。

语法

PostgreSQL 中 DELETE 查询的语法如下。

示例

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

 
postgres=# CREATE TABLE CRICKETERS ( 
   Fst_Nme VARCHAR(255), Lst_Nme VARCHAR(255), 
   Age int, POB VARCHAR(255), Cntry 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   

使用此声明，姓氏为“Sangakkara”的记录将被删除。

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

我们已经删除了一个条目，因此当您使用 SELECT 查询获取表的内容时，您只会看到四个。

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

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

 
postgres=# DELETE FROM CRICKETERS;
DELETE 4   

当您尝试使用 SELECT 查询获取 CRICKETERS 表的内容时，您将收到一个空的结果集，如下所示，因为所有记录都已被删除。

 
postgres=# SELECT * FROM CRICKETERS;
fst_nme  | lst_nme | age | pob | cntry
------------+-----------+-----+----------------+---------
(0 rows)   

使用 Python 删除数据

execute() 函数是 psycopg2 游标类的一个特性。此函数在接受查询作为参数后执行它。

因此，为了使用 Python 将数据插入 PostgreSQL 数据库，请执行以下操作：

导入 psycopg2 包。
通过向 connect() 函数提供用户名、密码、主机（可选，默认为 localhost）和数据库（可选）作为输入，您可以创建一个连接对象。
通过将 autocommit 属性设置为 false，您可以禁用自动提交选项。
游标对象是 psycopg2 库的 Connection 类的 cursor() 函数的结果。此函数使用它来创建游标对象。
接下来，使用 execute() 函数通过将其作为参数提供来执行 UPDATE 语句。

示例

以下 Python 代码删除了 EMP 表中年龄值大于 25 的行。

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieve contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMP''')
print(cursor.fetchall())
#Delete records
cursor.execute('''DELETE FROM EMP WHERE AGE > 25''')
#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMP")
print(cursor.fetchall())
#Modify your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
Contents of the table:
   ('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)]

Python PostgreSQL - 删除表

语法

示例

假设我们使用以下查询生成了两个表：CRICKETERS 和 EMPLOYEES。

 
postgres=# CREATE TABLE CRICKETERS (
   Fst_Nme VARCHAR(255), Lst_Nme VARCHAR(255), Age int, 
   POB VARCHAR(255), Cntry 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" 命令验证表列表，则之前生成的表现在可以显示如下。

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

以下语句将 Employee 表从数据库中删除。

 
postgres=# DROP table employee;
DROP TABLE   

如果您再次获取表列表，您将只能看到一个表，因为您已删除 Employee 表。

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

由于您之前已删除 Employee 表，因此当您尝试再次删除它时，您将收到以下错误：“表不存在。”

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

您可以使用 DELTE 语句结合 IF EXISTS 子句来解决此问题。如果表存在，则将其删除；否则，将跳过 DELETE 操作。

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

使用 Python 移除整个表

使用 DROP 语句，您可以随时删除表。但是，由于无法恢复从现有数据库中删除的数据，因此在删除任何表时都必须格外小心。

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists
cursor.execute("DROP TABLE emp")
print("Table dropped... ")
#Modify your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
#Table dropped...

Python PostgreSQL - LIMIT

PostgreSQL SELECT 语句中的 LIMIT 子句允许您限制数量。

语法

PostgreSQL 中 LIMIT 子句的语法如下。

 
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]   

示例

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

 
postgres=# CREATE TABLE CRICKETERS ( 
   Fst_Nme VARCHAR(255), Lst_Nme VARCHAR(255), Age int, 
   POB VARCHAR(255), Cntry VARCHAR(255)
);
CREATE TABLE
postgres=#   

此外，假设我们使用 INSERT 命令向其中添加了五个记录，如下所示。

 
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 ('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1   

以下语句使用 LIMIT 子句从 Cricketers 表中提取前三行。

 
postgres=# SELECT * FROM CRICKETERS LIMIT 3;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(3 rows)   

您可以将 OFFSET 子句与 LIMIT 结合使用，以获取从特定记录开始的记录，也称为偏移量。

 
postgres=# SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2;
fst_nme  | lst_nme  | age | pob | cntry
------------+------------+-----+----------------+----------
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Rohit       | Sharma     | 32  | Nagpur         | India
(2 rows)
postgres=#   

使用 Python 的 LIMIT 子句

以下 Python 示例通过检索名为 EMPLOYEE 的表的内容，将返回的行数限制为两个。

示例

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host, port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieve single row
sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''
#Execute the query
cursor.execute(sql)
#Fetch the data
result = cursor.fetchall();
print(result)
#Modify your changes in the database
conn.commit()
#Closine the connection
conn.close()   

输出

 
[('Sharukh', 'Sheik', 25, 'M', 8300.0), ('Sarmista', 'Sharma', 26, 'F', 10000.0)]

Python PostgreSQL - JOIN

在将数据拆分到两个表中后，您可以使用 JOIN 来获取这两个表的组合记录。

示例

假设我们已将表命名为 CRICKETERS 并向其中添加了五个记录，如下所示。

 
postgres=# CREATE TABLE CRICKETERS (
   Fst_Nme VARCHAR(255), Lst_Nme VARCHAR(255), Age int, 
   POB VARCHAR(255), Cntry VARCHAR(255)
);
postgres=# insert into CRICKETERS values (
   'Shikhar', 'Dhawan', 33, 'Delhi', 'India'
);
postgres=# insert into CRICKETERS values (
   'Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica'
);
postgres=# insert into CRICKETERS values (
   'Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka'
);
postgres=# insert into CRICKETERS values (
   'Rohit', 'Sharma', 32, 'Nagpur', 'India'
);   

此外，如果我们创建了另一个名为 OdiStats 的数据库并向其中添加了五个记录，如下所示。

 
postgres=# CREATE TABLE ODIStats (
   First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, 
   Centuries INT, HalfCenturies INT
);
postgres=# insert into OdiStats values ('Shikhar', 133, 5518, 44.5, 17, 27);
postgres=# insert into OdiStats values ('Jonathan', 68, 2819, 51.25, 4, 22);
postgres=# insert into OdiStats values ('Kumara', 404, 14234, 41.99, 25, 93);
postgres=# insert into OdiStats values ('Virat', 239, 11520, 60.31, 43, 54);
postgres=# insert into OdiStats values ('Rohit', 218, 8686, 48.53, 24, 42);   

通过合并这两个表的值检索到的数据如下。

 
postgres=# SELECT
Cricketers.Fst_Nme, Cricketers.Lst_Nme, Cricketers.Cntry,
OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies
from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name;
first_name  | last_name  | country     | matches | runs  | centuries | halfcenturies
------------+------------+-------------+---------+-------+-----------+---------------
Shikhar     | Dhawan     | India       | 133     | 5518  | 17        | 27
Jonathan    | Trott      | SouthAfrica | 68      | 2819  | 4         | 22
Kumara      | Sangakkara | Srilanka    | 404     | 14234 | 25        | 93
Rohit       | Sharma     | India       | 218     | 8686  | 24        | 42
(4 rows)
postgres=#   

使用 Python 的 JOIN

在将数据拆分到两个表中后，您可以使用 JOIN 来获取这两个表的组合记录。

示例

以下 Python 程序演示了 JOIN 子句的使用。

 
import psycopg2
#establish the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='dif_host', port= 'dif_port'
)
#Set auto commit false
conn.autocommit = True
#Create a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieve single row
sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''
#Execute the query
cursor.execute(sql)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Modify your changes in the database
conn.commit()
#Close the connection
conn.close()   

输出

 
[('Ramya', 'Rama priya', 27, 'F', 9000.0, 101, 101, 'Krishna@mymail.com', 'Hyderabad'), 
   ('Vinay', 'Battacharya', 20, 'M', 6000.0, 102, 102, 'Raja@mymail.com', 'Vishakhapatnam'), 
   ('Sharukh', 'Sheik', 25, 'M', 8300.0, 103, 103, 'Krishna@mymail.com ', 'Pune'), 
   ('Sarmista', 'Sharma', 26, 'F', 10000.0, 104, 104, 'Raja@mymail.com', 'Mumbai')]

下一主题如何在 Python 中绘制多个图表

Python PostgreSQL - 数据库连接

Python 连接建立

Python PostgreSQL - 创建数据库

使用 Python 创建数据库

Python PostgreSQL - 创建表

使用 Python 创建表

Python PostgreSQL - 插入数据

使用 Python 插入数据

Python PostgreSQL - 选择数据

使用 Python 检索数据

注意：当使用游标对象查询表时，会返回一个称为结果集的对象。

Python PostgreSQL - WHERE 子句

使用 Python 的 WHERE 子句

Python PostgreSQL - ORDER BY

使用 Python 的 ORDER BY 子句

Python PostgreSQL - 更新表

使用 Python 更新记录

Python PostgreSQL - 删除数据

使用 Python 删除数据

Python PostgreSQL - 删除表

使用 Python 移除整个表

Python PostgreSQL - LIMIT

使用 Python 的 LIMIT 子句

Python PostgreSQL - JOIN

使用 Python 的 JOIN

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

其他

Python PostgreSQL - 数据库连接

Python 连接建立

Python PostgreSQL - 创建数据库

使用 Python 创建数据库

Python PostgreSQL - 创建表

使用 Python 创建表

Python PostgreSQL - 插入数据

使用 Python 插入数据

Python PostgreSQL - 选择数据

使用 Python 检索数据

注意：当使用游标对象查询表时，会返回一个称为结果集的对象。

Python PostgreSQL - WHERE 子句

使用 Python 的 WHERE 子句

Python PostgreSQL - ORDER BY

使用 Python 的 ORDER BY 子句

Python PostgreSQL - 更新表

使用 Python 更新记录

Python PostgreSQL - 删除数据

使用 Python 删除数据

Python PostgreSQL - 删除表

使用 Python 移除整个表

Python PostgreSQL - LIMIT

使用 Python 的 LIMIT 子句

Python PostgreSQL - JOIN

使用 Python 的 JOIN

相关帖子

Python中的Kaprekar常数

Python中的别名

Python中的os.kill()方法

如何以追加模式打开Python文件

使用Python-OpenCV在图像上用鼠标绘图

使用Python Pandas读取CSV文件的特定列

如何在Python中使用Pandas进行vLookup

如何从文件中读取字典到Python

Python和Bash的区别

Response.headers - Python Requests

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器