Python PostgreSQL

搞懂Python PostgreSQL的基本操作

PostgreSQL是一个功能强大的开源对象关系数据库系统。它拥有超过15年的积极开发阶段，并且其可靠的体系结构在可靠性，数据完整性和正确性方面赢得了极高的声誉。

要使用Python与PostgreSQL通信，你需要安装psycopg，这是为python编程提供的适配器，其当前版本为
psycog2。

psycopg2的编写目标是非常小巧，快速，稳定如磐石。在PIP（python的程序包管理器）下可用

使用PIP安装Psycog2

首先，请确保系统中已正确安装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

验证

要验证安装，请创建一个示例Python脚本，其中包含以下行。

 import mysql.connector

如果安装成功，则在执行安装时，不应出现任何错误-

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

PostgreSQL数据库连接

PostgreSQL提供了自己的外壳程序来执行查询。要建立与PostgreSQL数据库的连接，请确保已在系统中正确安装了它。打开PostgreSQL Shell提示符，并传递详细信息，例如服务器，数据库，用户名和密码。如果你提供的所有详细信息均适当，则将与PostgreSQL数据库建立连接。

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

Python PostgreSQL_https://bianchenghao6.com_【Python3 教程】_第1张

使用Python建立连接

psycopg2的连接类表示/处理连接的实例。你可以使用connect（）函数创建新的连接。这接受基本的连接参数，例如dbname，用户，密码，主机，端口，并返回一个连接对象。使用此功能，可以与PostgreSQL建立连接。

实例

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

 # Filename : example.py
 # Copyright : 2020 By Lidihuo
 # Author by : www.lidihuo.com
 # Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="postgres", user='postgres', password='password', 
    host='127.0.0.1', port= '5432'
 )
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 使用execute()方法执行MYSQL函数
 cursor.execute("select version()")
 # 使用fetchone()方法获取单行。
 data = cursor.fetchone()
 print("Connection established to: ",data)
 # 关闭连接
 conn.close()
 Connection established to: (
    'PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit',
 )

输出结果：

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

PostgreSQL创建数据库

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

语法

Following is the syntax of the CREATE DATABASE statement.

 CREATE DATABASE dbname;

实例

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

 postgres=# CREATE DATABASE testdb;
 CREATE DATABASE

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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（围绕SQL语句CREATE DATABASE的包装器）从命令提示符在PostgreSQL中创建数据库。

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

使用Python创建数据库

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

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

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

实例

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

 # Filename : example.py
 # Copyright : 2020 By Lidihuo
 # Author by : www.lidihuo.com
 # Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="postgres", user='postgres', password='password', 
    host='127.0.0.1', port= '5432'
 )
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 准备查询以创建数据库
 sql = '''CREATE database mydb''';
 # 创建数据库
 cursor.execute(sql)
 print("Database created successfully........")
 # 关闭连接
 conn.close()

输出结果：

 Database created successfully........

PostgreSQL创建表

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

语法

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

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

实例

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

 # Filename : example.py
 # Copyright : 2020 By Lidihuo
 # Author by : www.lidihuo.com
 # Date : 2020-08-15
 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中获取数据库中的表列表。创建表后，如果可以验证表列表，则可以在其中观察新创建的表，如下所示：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
postgres=# \dt
         List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

以相同的方式，你可以使用\ d来获取创建的表的描述，如下所示：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15postgres=# \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的表。

 # Filename : example.py
 # Copyright : 2020 By Lidihuo
 # Author by : www.lidihuo.com
 # Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 删除已存在的EMPLOYEE表
 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)
 print("Table created successfully........")
 # 关闭连接
 conn.close()

输出结果：

 Table created successfully........

PostgreSQL插入数据

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

语法

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

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

其中，column1，column2，column3，..是表的各列的名称，而value1，value2，value3，...是你需要插入表中的值。

实例

假设我们使用CREATE TABLE语句创建了一个名称为CRICKETERS的表，如下所示：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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语句在上面创建的表中插入一行：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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语句插入记录时，如果你跳过任何列名，则将插入Record，这将在你跳过的列上留下空白。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# insert into CRICKETERS 
    (First_Name, Last_Name, Country) values('Jonathan', 'Trott', 'SouthAfrica');
 INSERT 0 1

如果你传递的值的顺序与表中它们各自的列名相同，则也可以在不指定列名的情况下将记录插入表中。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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语句验证其内容，如下所示：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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)

使用Python插入数据

psycopg2的游标类提供了一个名为execute（）方法的方法。此方法接受查询作为参数并执行它。

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

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 简历连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 准备SQL查询以将记录插入数据库。
 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)''')
 # 提交数据
 conn.commit()
 print("Records inserted........")
 # 关闭连接
 conn.close()

输出结果：

  Records inserted........

PostgreSQL查询数据

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

语法：

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

 SELECT column1, column2, columnN from table_name;

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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列的值。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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)

如果要检索每个记录的所有列，则需要用“⚹”替换列名，如下所示：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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=#

使用Python检索数据

任何数据库上的READ操作意味着要从数据库中获取一些有用的信息。你可以使用psycopg2提供的fetch（）方法从PostgreSQL中获取数据。

The Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,

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

注意：结果集是使用游标对象查询表时返回的对象。

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 检索数据
 cursor.execute('''SELECT * from EMPLOYEE''')
 # 正在从表中获取第一行
 result = cursor.fetchone();
 print(result)
 #Fetching 1st row from the table
 result = cursor.fetchall();
 print(result)
 # 提交数据
 conn.commit()
 # 关闭连接
 conn.close()

输出结果：

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

PostgreSQL Where条件查询

在执行SELECT，UPDATE或DELETE操作时，你可以指定条件以使用WHERE子句过滤记录。将在满足给定条件的记录上执行该操作。

语法

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

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

你可以使用比较或逻辑运算符指定search_condition。例如>，<，=，LIKE，NOT等。以下示例将使这个概念更清楚。

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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的记录-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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=#

Python Where查询

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 删除已存在的表
 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)
 # 填充表
 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)
 # 使用where子句检索特定记录
 cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23") 
 print(cursor.fetchall())
 # 提交数据
 conn.commit()
 # 关闭连接
 conn.close()

输出结果：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 [('Krishna', 'Sharma', 19, 'M', 2000.0), ('Raj', 'Kandukuri', 20, 'M', 7000.0)]

PostgreSQL Order by

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

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

语法

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

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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表的行-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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表的记录进行排序。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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将结果按降序排列-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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)

Python的ORDER BY子句

要按特定顺序检索表的内容，请在游标对象上调用execute（）方法，并将SELECT语句与ORDER BY子句一起作为参数传递给它。

实例

在以下示例中，我们将创建一个具有名称和Employee的表，填充该表，然后使用ORDER BY子句按其年龄的（升序）顺序检索其记录。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 cursor = conn.cursor()
 # 删除已存在的表
 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 INT,
    CONTACT INT)'''
 cursor.execute(sql)
 # 填充表
 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()
 # 使用ORDER BY子句检索特定记录
 cursor.execute("SELECT * from EMPLOYEE ORDER BY AGE")
 print(cursor.fetchall())
 # 提交
 conn.commit()
 # 关闭连接
 conn.close()

输出结果：

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

PostgreSQL更新表

你可以使用UPDATE语句修改PostgreSQL中表的现有记录的内容。要更新特定的行，你需要与其一起使用WHERE子句。

语法

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

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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的 -

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = 'Shikhar' ;
 UPDATE 1
 postgres=#

如果你检索FIRST_NAME是Shikhar的记录，则会观察到年龄值已更改为45-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# UPDATE CRICKETERS SET AGE = AGE+1;
 UPDATE 5

如果使用SELECT命令检索表的内容，则可以看到更新后的值为-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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软件包。
通过将用户名，密码，主机（可选的默认值：localhost）和数据库（可选的）作为参数传递给connect（）方法来创建连接对象。
通过将false设置为属性autocommit的值来关闭自动提交模式。
psycopg2库的Connection类的cursor（）方法返回一个游标对象。使用此方法创建一个游标对象。
然后，通过将UPDATE语句作为参数传递给execute（）方法来执行该语句。

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect (
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 #获取更新之前的所有行
 print("Contents of the Employee table: ")
 sql = '''SELECT * from EMPLOYEE'''
 cursor.execute(sql)
 print(cursor.fetchall())
 # 更新记录
 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'"
 cursor.execute(sql)
 print("Table updated...... ")
 # 获取更新后的所有行
 print("Contents of the Employee table after the update operation: ")
 sql = '''SELECT * from EMPLOYEE'''
 cursor.execute(sql)
 print(cursor.fetchall())
 # 提交
 conn.commit()
 # 关闭连接
 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)
 ]

PostgreSQL-删除数据

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

语法

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

 DELETE from table_name [WHERE Clause]

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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”的板球运动员的记录。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# DELETE from CRICKETERS WHERE LAST_NAME = 'Sangakkara';
 DELETE 1

如果使用SELECT语句检索表的内容，则由于我们已删除一条记录，因此只能看到4条记录。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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

由于已删除所有记录，因此，如果尝试检索CRICKETERS表的内容，则使用SELECT语句，你将获得一个空结果集，如下所示-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
postgres=# SELECT * FROM CRICKETERS;
 first_name | last_name | age | place_of_birth | country
------------+-----------+-----+----------------+---------
(0 rows)

使用Python删除数据

psycopg2的游标类提供了一个名为execute（）方法的方法。此方法接受查询作为参数并执行它。

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

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 # 使用cursor()方法创建游标对象
 cursor = conn.cursor()
 # 检索表的内容
 print("Contents of the table: ")
 cursor.execute('''SELECT * from EMPLOYEE''')
 print(cursor.fetchall())
 # 删除记录
 cursor.execute('''DELETE from EMPLOYEE WHERE AGE > 25''')
 # 删除后检索数据
 print("Contents of the table after delete operation ")
 cursor.execute("SELECT * from EMPLOYEE")
 print(cursor.fetchall())
 # 提交
 conn.commit()
 # 关闭连接
 conn.close()

输出结果：

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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)
 ]

PostgreSQL删除表

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

语法

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

 DROP TABLE table_name;

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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”命令验证表列表，则可以看到上面创建的表为-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
postgres=# \dt;
            List of relations
 Schema | Name       | Type  | Owner
--------+------------+-------+----------
 public | cricketers | table | postgres
 public | employee   | table | postgres
(2 rows)
postgres=#

以下语句从数据库中删除名为Employee的表-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# DROP table employee;
 DROP TABLE

由于已删除Employee表，因此，如果再次检索表列表，则只能观察其中的一个表。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
postgres=# \dt;
List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

如果你尝试再次删除Employee表，因为已经将其删除，你将收到一条错误消息，指出“表不存在”，如下所示-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# DROP table employee;
 ERROR: table "employee" does not exist
 postgres=#

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 postgres=# DROP table if EXISTS employee;
 NOTICE: table "employee" does not exist, skipping
 DROP TABLE
 postgres=#

使用Python删除整个表

你可以根据需要使用DROP语句删除表。但是在删除任何现有表时需要非常小心，因为删除表后丢失的数据将无法恢复。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432')
 # 设置自动提交
 conn.autocommit = True
 cursor = conn.cursor()
 # 删除已存在的表
 cursor.execute("DROP TABLE emp")
 print("Table dropped... ")
 # 提交
 conn.commit()
 # 关闭连接
 conn.close()

输出结果：

# Table dropped...

PostgreSQL-Limit

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

语法

以下是PostgreSQL中LMIT子句的语法-

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

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入其中-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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来获取。

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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=#

Python Limit查询

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", 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()
 # 检索单行
 sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''
 # 执行查询
 cursor.execute(sql)
 # 遍历数据
 result = cursor.fetchall();
 print(result)
 # 提交
 conn.commit()
 # 关闭连接
 conn.close()

Output

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

PostgreSQL Join

将数据分为两个表后，可以使用联接从这两个表中获取组合记录。

实例

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

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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条记录插入-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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);

以下语句检索结合这两个表中的值的数据-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
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=#

Python使用join

将数据分为两个表后，可以使用联接从这两个表中获取组合记录。

实例

以下python程序演示了JOIN子句的用法-

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 import psycopg2
 # 建立连接
 conn = psycopg2.connect(
    database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
 )
 # 设置自动提交
 conn.autocommit = True
 cursor = conn.cursor()
 # 检索单行
 sql = '''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''
 # 执行查询
 cursor.execute(sql)
 # 正在从表中获取第一行
 result = cursor.fetchall();
 print(result)
 # 提交
 conn.commit()
 # 关闭连接
 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')
 ]

PostgreSQL Cursor

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

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

您可以使用Connection对象/类的cursor（）方法创建Cursor对象。

实例

 # Filename : example.py
# Copyright : 2020 By Lidihuo
# Author by : www.lidihuo.com
# Date : 2020-08-15
 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()

方法

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

编号	方法 & 描述
1	callproc() 此方法用于调用现有过程PostgreSQL数据库。
2	close() 此方法用于关闭当前光标对象。
3	executemany() 此方法接受一系列参数列表list。准备一个MySQL查询并使用所有参数执行它。
4	execute() 该方法接受一个MySQL查询作为参数并执行给定的查询。
5	fetchall() 此方法检索查询结果集中的所有行，并将它们作为元组列表返回。（如果我们在检索到几行之后执行此操作，它将返回剩余的行）
6	fetchone() 此方法获取查询结果中的下一行，并将其作为元组返回。
7	fetchmany() 此方法与fetchone（）相似，但是它检索查询结果集中的下一组行，而不是单行。

属性

以下是Cursor类的属性-

编号	属性 & 描述
1	description 这是一个只读属性，它返回包含结果集中列说明的列表。
2	lastrowid 这是一个只读属性，如果表中有任何自动递增的列，它将返回在上一次INSERT或UPDATE操作中为该列生成的值。
3	rowcount 如果执行SELECT和UPDATE操作，则返回返回/更新的行数。
4	closed 此属性指定游标是否关闭，如果关闭，则返回true，否则返回false。
5	connection 这将返回对创建该光标所使用的连接对象的引用。
6	name 此属性返回游标的名称。
7	scrollable 此属性指定特定光标是否可滚动。

Python mysql-connector

Python PyMySQL