Python语言基础

Python语言进阶

Python数据结构

<< Python mysql-connector Python mongodb >>

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建立连接

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的表。

你可以使用\ 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的表。

输出结果：

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的表，如下所示：

以下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的表-

如果我们使用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列的值。

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

使用Python检索数据

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

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

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

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

实例

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

输出结果：

('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的表-

如果我们使用INSERT语句将5条记录插入其中-

以下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的表-

如果我们使用INSERT语句将5条记录插入其中-

以下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的表-

如果我们使用INSERT语句将5条记录插入其中-

下面的语句修改的板球运动员，他的名字是年龄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的表-

如果我们使用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
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语句删除表。但是在删除任何现有表时需要非常小心，因为删除表后丢失的数据将无法恢复。

输出结果：

# Table dropped...

PostgreSQL-Limit

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

语法

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

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

实例

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

如果我们使用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-

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子句的用法-

输出结果：

[
   ('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 此属性指定特定光标是否可滚动。

找工作要求35岁以下，35岁以上的程序员都干什么去了？

长久以来，一直有一个问题困扰着技术人——如何打破“程序员的35岁职业魔咒”，这一天迟早会到来，或早或晚。

或许是选错了行业，程序员薪水虽高，但光鲜的外表下，背后的苦衷只有自己知道。三十多岁本该是一个人事业的黄金期，但技术变化日新月异，行业竞争异常残酷，对一个企业来说，永远有比你更年轻、劳动成本更低的人可以选择，这让你的中年危机提前到来。破局的智慧可以看看这本书！>>

<< Python mysql-connector Python mongodb >>

昵称：邮箱：