Python Postgresql 简明教程

Python PostgreSQL - Quick Guide

Python PostgreSQL - Introduction

PostgreSQL 是一个功能强大的开源对象关系数据库系统。它拥有超过 15 年的积极开发阶段，并拥有经过验证的架构，使其享有很高的可靠性、数据完整性和正确性声誉。

要使用 Python 与 PostgreSQL 进行通信，您需要安装 psycopg，一个为 python 编程提供的适配器，它的当前版本是 psycog2 。

psycopg2 的编写目标是体积小、速度快且稳定。它在 PIP（python 的包管理器）中可用。

Installing Psycog2 using PIP

首先，确保系统中正确安装了 python 和 PIP，并且 PIP 是最新的。

要升级 PIP，打开命令提示符并执行以下命令 -

C:\Users\Tutorialspoint>python -m pip install --upgrade pip
Collecting pip
   Using cached
https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl
Installing collected packages: pip
   Found existing installation: pip 19.0.3
      Uninstalling pip-19.0.3:
         Successfully uninstalled pip-19.0.3
Successfully installed pip-19.2.2

然后，以管理员模式打开命令提示符并执行 pip install psycopg2-binary 命令，如下所示 -

C:\WINDOWS\system32>pip install psycopg2-binary
Collecting psycopg2-binary
   Using cached
https://files.pythonhosted.org/packages/80/79/d0d13ce4c2f1addf4786f4a2ded802c2df66ddf3c1b1a982ed8d4cb9fc6d/psycopg2_binary-2.8.3-cp37-cp37m-win32.whl
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.3

Verification

要验证安装，请使用以下行创建一个示例 python 脚本。

import mysql.connector

如果安装成功，在执行该脚本后，不应出现任何错误：

D:\Python_PostgreSQL>import psycopg2
D:\Python_PostgreSQL>

Python PostgreSQL - Database Connection

PostgreSQL 提供了自己的 shell 来执行查询。要建立与 PostgreSQL 数据库的连接，请确保已将其正确安装在您的系统中。打开 PostgreSQL shell 提示符并传递服务器、数据库、用户名和密码等详细信息。如果您提供的详细信息全部正确，则与 PostgreSQL 数据库建立的连接。

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

Establishing Connection Using Python

psycopg2 的连接类表示/处理连接的实例。您可以使用 connect() 函数创建新连接。此函数接受基本连接参数，例如 dbname、user、password、host、port，并返回连接对象。使用此函数，您可以与 PostgreSQL 建立连接。

Example

以下 Python 代码演示如何连接到现有数据库。如果数据库不存在，则它将被创建，最终将返回一个数据库对象。PostgreSQL 的默认数据库名称为 postrgre。因此，我们把它当作数据库名称提供。

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

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

#Executing an MYSQL function using the execute() method
cursor.execute("select version()")

#Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection established to: ",data)

#Closing the connection
conn.close()
Connection established to: (
   'PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit',
)

Output

Connection established to: (
   'PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit',
)

Python PostgreSQL - Create Database

你可以使用 CREATE DATABASE 语句在 PostgreSQL 中创建一个数据库。你可以通过在命令后指定要创建的数据库的名称，在 PostgreSQL shell 提示符中执行此语句。

Syntax

以下是 CREATE DATABASE 语句的语法。

CREATE DATABASE dbname;

Example

以下语句在 PostgreSQL 中创建一个名为 testdb 的数据库。

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

你可以使用 \l 命令列出 PostgreSQL 中的数据库。如果你验证了数据库列表，则可以找到新创建的数据库，如下所示：

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)

你还可以使用命令提示符中的命令 createdb（CREATE DATABASE SQL 语句的包装器）在 PostgreSQL 中创建一个数据库。

C:\Program Files\PostgreSQL\11\bin> createdb -h localhost -p 5432 -U postgres sampledb
Password:

Creating a Database Using Python

psycopg2 的游标类提供了各种方法来执行各种 PostgreSQL 命令、获取记录和复制数据。你可以使用 Connection 类的 cursor() 方法创建一个游标对象。

此类的 execute() 方法接受 PostgreSQL 查询作为参数并执行它。

因此，要在 PostgreSQL 中创建一个数据库，请使用此方法执行 CREATE DATABASE 查询。

Example

以下 python 示例在 PostgreSQL 数据库中创建了一个名为 mydb 的数据库。

import psycopg2

#establishing the connection

conn = psycopg2.connect(
   database="postgres", user='postgres', password='password',
   host='127.0.0.1', port= '5432'
)
conn.autocommit = True

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

#Preparing query to create a database
sql = '''CREATE database mydb''';

#Creating a database
cursor.execute(sql)
print("Database created successfully........")

#Closing the connection
conn.close()

Output

Database created successfully........

Python PostgreSQL - Create Table

可以使用 CREATE TABLE 语句在 PostgreSQL 中创建一个新表。在执行此操作时，您需要指定表名、列名及其数据类型。

Syntax

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

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

Example

以下示例在 PostgreSQL 中创建一个名为 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=#

您可以使用 \dt 命令检索 PostgreSQL 中数据库中的表列表。创建表后，如果您确认表列表，您可以按如下方式观察其中新创建的表：

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

Creating a Table Using Python

要使用 Python 创建表，您需要使用 pyscopg2 的光标的 execute() 方法来执行 CREATE TABLE 语句。

Example

以下 Python 示例创建一个名为 employee 的表。

import psycopg2

#Establishing the connection

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

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

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating 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........")

#Closing the connection
conn.close()

Output

Table created successfully........

Python PostgreSQL - Insert Data

你可以使用 INSERT INTO 语句将记录插入到 PostgreSQL 中现有的表中。执行此操作时，你需要指定表的名称以及其中的列值。

Syntax

以下是 INSERT 语句的建议语法 -

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

其中，column1、column2、column3 等是表的列名称，value1、value2、value3 等是你需要插入到表中的值。

Example

假设我们使用 CREATE TABLE 语句创建了一个名为 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=#

以下 PostgreSQL 语句在上述创建的表中插入一行：

postgres=# insert into CRICKETERS
   (First_Name, Last_Name, Age, Place_Of_Birth, Country) values
   ('Shikhar', 'Dhawan', 33, 'Delhi', '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;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      |     |                | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
(5 rows)

Inserting Data Using Python

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

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

Import psycopg2 package.
使用 connect() 方法创建连接对象，方法是向其传递用户名、密码、主机（可选，默认：localhost）和数据库（可选）作为参数。
通过将 false 设置为属性 autocommit 的值来关闭自动提交模式。
cursor() 的 Connection 类的 psycopg2 库方法返回一个游标对象。使用此方法创建一个游标对象。
然后，通过将 INSERT 语句作为参数传递给 execute() 方法来执行它/它们。

Example

以下 Python 程序在 PostgreSQL 数据库中创建一个名为 EMPLOYEE 的表，并使用 execute() 方法向其中插入记录：

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

# Preparing SQL queries to INSERT a record into the database.
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Ramya', 'Rama priya', 27, 'F', 9000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Vinay', 'Battacharya', 20, 'M', 6000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Sharukh', 'Sheik', 25, 'M', 8300)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Sarmista', 'Sharma', 26, 'F', 10000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Tripthi', 'Mishra', 24, 'F', 6000)''')

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

print("Records inserted........")

# Closing the connection
conn.close()

Output

Records inserted........

Python PostgreSQL - Select Data

你可以使用 SELECT 语句来检索 PostgreSQL 中现有表的内容。在此语句中，你需要指定表的名称，它以表格形式返回其内容，这就是所谓的结果集。

Syntax

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

SELECT column1, column2, columnN FROM table_name;

Example

假设我们使用以下查询创建了一个名为 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

以下 SELECT 查询会从 CRICKETERS 表检索 FIRST_NAME、LAST_NAME、COUNTRY。

postgres=# SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
 first_name | last_name  | country
------------+------------+-------------
Shikhar     | Dhawan     | India
Jonathan    | Trott      | SouthAfrica
Kumara      | Sangakkara | Srilanka
Virat       | Kohli      | India
Rohit       | Sharma     | India
(5 rows)

如果你想检索每条记录的所有列，你需要将列的名称替换成“⚹”，如下所示 −

postgres=# SELECT * FROM CRICKETERS;
first_name  | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
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=#

Retrieving Data Using Python

对任何数据库的读取操作意味着从数据库中获取一些有用的信息。你可以使用 psycopg2 提供的 fetch() 方法从 PostgreSQL 中获取数据。

游标类提供了三种方法，即 fetchall()、fetchmany() 和 fetchone()，其中：

fetchall() 方法检索查询结果集中的所有行，并以元组列表的形式返回它们。（如果我们在检索几行后执行此操作，它会返回其余行）。
fetchone() 方法获取查询结果中的下一行，并将其作为元组返回。

Note - 结果集是在使用光标对象查询表格时返回的对象。

Example

以下 Python 程序连接到 PostgreSQL 的名为 mydb 的数据库并检索名为 EMPLOYEE 的表中的所有记录。

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

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

#Closing the connection
conn.close()

Output

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

Python PostgreSQL - Where Clause

在执行SELECT、UPDATE或DELETE操作时，可以使用WHERE子句指定条件以筛选记录。该操作将对满足给定条件的记录执行。

Syntax

以下是PostgreSQL中WHERE子句的语法 −

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

您可以使用比较或逻辑运算符（如>、<、=、LIKE、NOT等）指定search_condition。以下示例将阐明此概念。

Example

假设我们使用以下查询创建了一个名为 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

以下SELECT语句检索年龄大于35岁的记录 −

postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(2 rows)

postgres=#

Where Clause Using Python

要使用python程序从表中获取特定记录，请使用WHERE子句执行SELECT语句，通过将其作为参数传递给 execute() 方法。

Example

以下python示例演示了使用python的WHERE命令。

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 IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT)'''
cursor.execute(sql)

#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE
   (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 19, 'M', 2000), ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'M', 5000),('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)

#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

[('Krishna', 'Sharma', 19, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]

Python PostgreSQL - Order By

通常，如果你尝试从表中检索数据，你将按照插入数据的顺序获取记录。

使用 ORDER BY 子句，在检索表记录时，你可以根据所需的列以升序或降序对结果记录进行排序。

Syntax

以下是 PostgreSQL 中 ORDER BY 子句的语法。

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

Example

假设我们使用以下查询创建了一个名为 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

选择语句检索 CRICKETERS 表的行按升序排列它们的年龄—

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(5 rows)es:

您可以使用多列来对表的记录进行排序。下面的 SELECT 语句根据列 age 和 FIRST_NAME 对 CRICKETERS 表的记录进行排序。

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Virat       | Kohli      | 30  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Shikhar     | Dhawan     | 33  | Delhi          | India
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Kumara      | Sangakkara | 41  | Matale         | Srilanka
(5 rows)

默认情况下， ORDER BY 子句按升序对表的记录进行排序。您可以使用 DESC 按降序排列结果，如下所示：

postgres=# SELECT * FROM CRICKETERS ORDER BY AGE DESC;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Kumara      | Sangakkara | 41  | Matale         | Srilanka
Jonathan    | Trott      | 38  | CapeTown       | SouthAfrica
Shikhar     | Dhawan     | 33  | Delhi          | India
Rohit       | Sharma     | 32  | Nagpur         | India
Virat       | Kohli      | 30  | Delhi          | India
(5 rows)

ORDER BY Clause Using Python

要以特定顺序检索表的全表，请对光标对象调用 execute() 方法，并将 SELECT 语句连同 ORDER BY 子句作为参数传递给它。

Example

在下面的示例中，我们使用 ORDER BY 子句创建了一个名为 Employee 的表，填充它，并按年龄（升序）检索其记录。

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 IF EXISTS EMPLOYEE")

#Creating a table
sql = '''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT, SEX CHAR(1),
   INCOME INT,
   CONTACT INT)'''
cursor.execute(sql)

#Populating the table
insert_stmt = "INSERT INTO EMPLOYEE
   (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME, CONTACT) VALUES (%s, %s, %s, %s, %s, %s)"

data = [('Krishna', 'Sharma', 26, 'M', 2000, 101),
   ('Raj', 'Kandukuri', 20, 'M', 7000, 102),
   ('Ramya', 'Ramapriya', 29, 'F', 5000, 103),
   ('Mac', 'Mohan', 26, 'M', 2000, 104)]
cursor.executemany(insert_stmt, data)
conn.commit()

#Retrieving specific records using the ORDER BY clause
cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
print(cursor.fetchall())

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

#Closing the connection
conn.close()

Output

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

Python PostgreSQL - Update Table

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

Syntax

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

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

Example

假设我们使用以下查询创建了一个名为 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 的记录，您会发现其 age 值已更改为 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)

Updating Records Using Python

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

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

Import psycopg2 package.
使用 connect() 方法创建连接对象，方法是向其传递用户名、密码、主机（可选，默认：localhost）和数据库（可选）作为参数。
通过将 false 设置为属性 autocommit 的值来关闭自动提交模式。
psycopg2 库的 Connection 类的 cursor() 方法返回一个游标对象。使用此方法创建一个游标对象。
然后，通过将 UPDATE 语句作为参数传递给 execute() 方法来执行它。

Example

以下 Python 代码更新 Employee 表的内容并检索结果 -

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

Python PostgreSQL - Delete Data

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

Syntax

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

DELETE FROM table_name [WHERE Clause]

Example

假设我们使用以下查询创建了一个名为 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

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

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

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

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语句，将删除指定表中的所有记录。

postgres=# DELETE FROM CRICKETERS;
DELETE 4

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

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

Deleting Data Using Python

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

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

Import psycopg2 package.
使用 connect() 方法创建连接对象，方法是向其传递用户名、密码、主机（可选，默认：localhost）和数据库（可选）作为参数。
通过将 false 设置为属性 autocommit 的值来关闭自动提交模式。
psycopg2 库的 Connection 类的 cursor() 方法返回一个游标对象。使用此方法创建一个游标对象。
然后，通过将其作为参数传递给 execute() 方法来执行 DELETE 语句。

Example

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

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

Python PostgreSQL - Drop Table

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

Syntax

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

DROP TABLE table_name;

Example

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

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” 命令验证表列表，你可以看到以上创建的表，如下所示 −

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

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

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

Removing an Entire Table Using Python

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

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...

Python PostgreSQL - Limit

在执行 PostgreSQL SELECT 语句时，你可以使用 LIMIT 子句限制其结果中的记录数。

Syntax

以下为 PostgreSQL 中 LMIT 从句的语法 -

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

Example

假设我们使用以下查询创建了一个名为 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

以下语句使用 LIMIT 子句检索 Cricketers 表的前 3 条记录：

postgres=# SELECT * FROM CRICKETERS LIMIT 3;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+-------------
 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;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+----------
 Kumara     | Sangakkara | 41  | Matale         | Srilanka
 Virat      | Kohli      | 30  | Delhi          | India
 Rohit      | Sharma     | 32  | Nagpur         | India

   (3 rows)
postgres=#

Limit Clause Using Python

以下 python 示例检索名为 EMPLOYEE 的表的表内容，将结果中的记录数量限制为 2 -

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 single row
sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''

#Executing the query
cursor.execute(sql)

#Fetching the data
result = cursor.fetchall();
print(result)

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

#Closing the connection
conn.close()

Output

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

Python PostgreSQL - Join

当你在两张表中划分数据时，你可以使用连接从这两张表中获取组合记录。

Example

假设我们已经创建了一个名为 CRICKETERS 的表，并向其中插入了 5 条记录，如下所示 −

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int,
   Place_Of_Birth VARCHAR(255), Country 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 ('Virat', 'Kohli', 30, 'Delhi', 'India');
postgres=# insert into CRICKETERS values ('Rohit', 'Sharma', 32, 'Nagpur', 'India');

而且，如果我们创建了另一个名为 OdiStats 的表，并向其中插入了 5 条记录，如下所示 −

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.First_Name, Cricketers.Last_Name, Cricketers.Country,
   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
 Virat      | Kohli      | India       | 239     | 11520 | 43        | 54
 Rohit      | Sharma     | India       | 218     | 8686  | 24        | 42
(5 rows)

postgres=#

Joins Using Python

当你在两张表中划分数据时，你可以使用连接从这两张表中获取组合记录。

Example

下面的 python 程序演示了 JOIN 子句的用法 −

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 single row
sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''

#Executing the query
cursor.execute(sql)

#Fetching 1st row from the table
result = cursor.fetchall();
print(result)

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

#Closing the connection
conn.close()

Output

[
   ('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 PostgreSQL - Cursor Object

psycopg 库的 Cursor 类提供方法以使用 Python 代码在数据库中执行 PostgreSQL 命令。

使用它的方法，您可以执行 SQL 语句、从结果集中获取数据、调用过程。

你可以使用 Connection 对象/类的 cursor() 方法来创建 Cursor 对象。

Example

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

Methods

以下是 Cursor 类/对象提供的各种方法。

Sr.No.	Methods & Description
1	callproc() 该方法用于调用 PostgreSQL 数据库中的现有过程。
2	close() 该方法用于关闭当前游标对象。
3	executemany() 该方法接受一个列表系列的参数列表。准备一个 MySQL 查询，并用所有参数执行该查询。
4	execute() 该方法接受一个 MySQL 查询作为参数，并执行给定的查询。
5	fetchall() 该方法检索查询的结果集中的所有行，并以元组列表的形式返回它们。（如果在检索几行后执行此操作，它将返回剩余的行）
6	fetchone() 该方法提取查询结果中的下一行，并以元组形式返回该行。
7	fetchmany() 该方法类似于 fetchone()，但它检索查询结果集中下一组行，而不是单行。

Properties

以下为 Cursor 类的属性：

Sr.No.	Property & Description
1	description 这是一个只读属性，它返回结果集中包含的列描述的列表。
2	lastrowid 这是一个只读属性，如果表中存在任何自动增量列，则返回最后一次 INSERT 或 UPDATE 操作中为该列生成的值。
3	rowcount 对于 SELECT 和 UPDATE 操作，它返回返回/更新的行数。
4	closed 该属性指定游标是否已关闭，如果是，则返回 true，否则返回 false。
5	connection 这会返回使用此游标创建的连接对象的引用。
6	name 该属性返回游标的名称。
7	scrollable 该属性指定特定游标是否可滚动。