Python: PostgreSQL Python 教程 Tutorial
目录
Python 有各种用于 PostgreSQL 的数据库驱动程序。目前,psycopg 是 Python 语言最流行的 PostgreSQL 数据库适配器。psycopg 完全实现了 Python DB-API 2.0 规范。
psycopg 的当前版本是 2 或 psycopg2。psycopg2 数据库适配器以 C 语言作为 libpq 包装器实现,既快速又安全。psycopg2 提供了许多有用的功能,例如客户端和服务器端游标、异步通知和通信、COPY 命令支持等。
此外,psycopg2 驱动程序支持许多开箱即用的 Python 类型。psycopg2 将Python 对象与 PostgreSQL 数据类型相匹配,例如,列表与数组、元组与记录、字典与 hstore。如果你想定制和扩展类型适配,你可以使用灵活的对象适配系统。
此 PostgreSQL Python 部分涵盖了在 Python 应用程序中与 PostgreSQL 交互的最常见活动:
- 连接到 PostgreSQL 数据库服务器– 从 Python 连接到 PostgreSQL 数据库服务器。
- 创建表– 从 Python 在 PostgreSQL 中创建新表。
- 将数据插入表中– 在 Python 中将数据插入 PostgreSQL 数据库表。
- 更新表中的数据– 更新 PostgreSQL 表中的数据的各种方法。
- 事务– 在 Python 中执行事务。
- 查询数据– 在 Python 应用程序中从 PostgreSQL 表查询数据的步骤。
- 调用 PostgreSQL 函数– 在 Python 中调用 PostgreSQL 函数。
- 调用 PostgreSQL 存储过程– 在 Python 应用程序中调用存储过程。
- 处理 BLOB 数据– 在 Python 应用程序中插入和选择 PostgreSQL BLOB 数据的示例。
- 从表中删除数据– 在 Python 中删除表中的数据。
示例数据库
摘要:在本教程中,出于演示目的,我们将使用suppliers
示例数据库。下图展示了数据库的结构suppliers
:
该suppliers
数据库有以下表格:
vendors
表:存储供应商数据。parts
表:存储零件数据。parts_drawings
表:存储零件的图纸。vendor_parts
表:存储哪个供应商提供哪些零件的数据。
连接到 PostgreSQL 数据库服务器
摘要:在本教程中,您将学习如何在Python程序中使用psycopg
数据库适配器连接到PostgreSQL数据库服务器。
安装 psycopg2 模块
首先,访问此处的 psycopg2 软件包。
其次,从终端使用以下命令行:
pip install psycopg2
如果您已经将源码包下载到电脑中,则可以使用setup.py,如下所示:
python setup.py build
sudo python setup.py install
创建一个新数据库
首先,使用任何客户端工具(例如 pgAdmin 或 psql)登录 PostgreSQL 数据库服务器。
其次,使用以下语句在 PostgreSQL 数据库服务器中创建一个名为suppliers
的新数据库。
CREATE DATABASE suppliers;
使用 psycopg2 连接到 PostgreSQL 数据库
要连接到suppliers
数据库,您可以使用psycopg2
模块的connect()
函数。
connect()
函数创建一个新的数据库会话并返回一个connection
类的新实例。通过使用connection
对象,您可以创建一个新的cursor
对象来执行任何SQL 语句。
要调用connect()
函数,您可以将 PostgreSQL 数据库参数指定为连接字符串并将其传递给该函数,如下所示:
conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
或者您可以使用关键字参数列表:
conn = psycopg2.connect(
host="localhost",
database="suppliers",
user="postgres",
password="Abcd1234")
以下是连接参数列表:
database
:要连接的数据库的名称。user
:用于验证的用户名。password
:用于验证的密码。host
:数据库服务器地址,例如 localhost 或 IP 地址。port
:端口号,如果不提供则默认为5432。
为了更方便,您可以使用配置文件来存储所有连接参数。
下图显示了database.ini
文件的内容:
[postgresql]
host=localhost
database=suppliers
user=postgres
password=SecurePas$1
通过使用database.ini
,您可以在代码迁移到生产环境时更改 PostgreSQL 连接参数,而无需修改代码。
请注意,如果您使用 git,则需要将database.ini
添加到.gitignore
文件中,以免将敏感信息提交到 github 等公共仓库。该.gitignore
文件将是这样的:
database.ini
下面的config()
函数会读取database.ini
文件并返回连接参数。该config()
函数放置在config.py
文件中:
#!/usr/bin/python
from configparser import ConfigParser
def config(filename='database.ini', section='postgresql'):
# create a parser
parser = ConfigParser()
# read config file
parser.read(filename)
# get section, default to postgresql
db = {}
if parser.has_section(section):
params = parser.items(section)
for param in params:
db[param[0]] = param[1]
else:
raise Exception('Section {0} not found in the {1} file'.format(section, filename))
return db
下面的connect()
函数连接到suppliers
数据库并打印出 PostgreSQL 数据库版本。
#!/usr/bin/python
import psycopg2
from config import config
def connect():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
if __name__ == '__main__':
connect()
怎么运行的。
- 首先,从
database.ini
文件中读取数据库连接参数。 - 接下来,通过调用
connect()
函数创建一个新的数据库连接。 - 然后,新建一个
cursor
并执行SQL语句来获取 PostgreSQL 数据库版本。 - 之后,通过调用游标对象的
fetchone()
方法读取结果集。 - 最后,通过调用
cursor
和connection
对象的close()
方法关闭与数据库服务器的通信。
执行 connect.py 文件
要执行connect.py
文件,请使用以下命令:
python connect.py
您将看到以下输出:
Connecting to the PostgreSQL database...
PostgreSQL database version:
('PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit',)
Database connection closed.
这意味着您已经成功连接到 PostgreSQL 数据库服务器。
故障排除
如果发生错误,connect()
函数将会抛出DatabaseError
异常。要查看它是怎么产生的,您可以更改database.ini
文件中的连接参数。
例如,如果将 host 参数更改为localhosts
,程序将输出以下消息:
Connecting to the PostgreSQL database...
could not translate host name "localhosts" to address: Unknown host
当您将数据库更改为不存在的数据库时,例如supplier
,会显示下面的错误消息:
Connecting to the PostgreSQL database...
FATAL: database "supplier" does not exist
如果将 user 参数改为 postgress
,则不会认证成功,如下:
Connecting to the PostgreSQL database...
FATAL: password authentication failed for user "postgress"
在本教程中,您学习了如何从 Python 程序连接到 PostgreSQL 数据库服务器。
创建表
摘要:在本教程中,您将学习如何使用 Python 在 PostgreSQL 数据库中创建新表。
本教程假设您知道如何编写CREATE TABLE
语句。如果还不知道怎么编写,您应该查看CREATE TABLE
教程。
在 Python 中创建 PostgreSQL 表的步骤
要在 PostgreSQL 数据库中创建新表,请使用以下步骤:
- 首先,构造CREATE TABLE语句。
- 接下来,通过调用
connect()
函数连接到 PostgreSQL 数据库。该connect()
函数会返回一个connection
对象。 - 然后,通过调用
connection
对象的cursor()
方法来创建一个cursor
对象。 - 之后,通过调用
cursor
对象的execute()
方法来执行CREATE TABLE
语句。 - 最后,通过调用
cursor
和connection
对象close()
的方法,关闭与 PostgreSQL 数据库服务器的通信。
在 Python 中创建表的示例
1) 创建 Python 程序
首先,创建一个名为create_table.py
的新文件。
其次,在create_table.py
文件内定义一个名为create_tables()
的新函数。
该create_tables()
函数在suppliers
数据库中创建四个表:vendors
、parts
、vendor_parts
和part_drawings
。
#!/usr/bin/python
import psycopg2
from config import config
def create_tables():
""" create tables in the PostgreSQL database"""
commands = (
"""
CREATE TABLE vendors (
vendor_id SERIAL PRIMARY KEY,
vendor_name VARCHAR(255) NOT NULL
)
""",
""" CREATE TABLE parts (
part_id SERIAL PRIMARY KEY,
part_name VARCHAR(255) NOT NULL
)
""",
"""
CREATE TABLE part_drawings (
part_id INTEGER PRIMARY KEY,
file_extension VARCHAR(5) NOT NULL,
drawing_data BYTEA NOT NULL,
FOREIGN KEY (part_id)
REFERENCES parts (part_id)
ON UPDATE CASCADE ON DELETE CASCADE
)
""",
"""
CREATE TABLE vendor_parts (
vendor_id INTEGER NOT NULL,
part_id INTEGER NOT NULL,
PRIMARY KEY (vendor_id , part_id),
FOREIGN KEY (vendor_id)
REFERENCES vendors (vendor_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (part_id)
REFERENCES parts (part_id)
ON UPDATE CASCADE ON DELETE CASCADE
)
""")
conn = None
try:
# read the connection parameters
params = config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
cur = conn.cursor()
# create table one by one
for command in commands:
cur.execute(command)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
create_tables()
2) 执行 Python 程序
要执行 Python 程序,请使用以下命令:
python create_table.py
3) 验证表创建
首先,使用 psql 程序登录 PostgreSQL 数据库服务器。
其次,使用\dt
命令显示suppliers
数据库中的表列表。
suppliers=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | part_drawings | table | postgres
public | parts | table | postgres
public | vendor_parts | table | postgres
public | vendors | table | postgres
(4 rows)
从输出中可以清楚地看到,我们在suppliers
数据库中成功创建了四个表。
如果您使用其他客户端工具(例如 pgAdmin),您可以通过public
模式下的表列表查看表。
在本教程中,您逐步学习了如何使用 psycopg 数据库适配器在 Python 中创建新的 PostgreSQL 表。
将数据插入表中
摘要:本教程逐步向您展示如何使用 Python 将一行或多行插入到 PostgreSQL 表中。
将一行插入 PostgreSQL 表的步骤
在 Python 中要将行插入到 PostgreSQL 表中,请使用以下步骤:
首先,通过调用psycopg
模块的connect()
函数连接到 PostgreSQL 数据库服务器。
conn = psycopg2.connect(dsn)
该connect()
函数返回一个connection
类的新实例。
接下来,通过调用该connection
对象的cursor()
方法来创建一个新的cursor
对象。
cur = conn.cursor()
然后,通过使用输入值调用cursor
对象的execute()
方法,执行INSERT
语句。
cur.execute(sql, (value1,value2))
您将该INSERT
语句作为第一个参数传递给execute()
方法,并将值列表作为第二个参数传递给该方法。
如果表的主键是序列或标识列,则可以在插入行后获取生成的 ID。
为此,您可以使用INSERT
语句中的RETURNING id
子句。调用execute()
方法后,调用cursor
对象的fetchone()
方法来获取id
值,如下所示:
id = cur.fetchone()[0]
之后,调用该connection
对象的commit()
方法将更改永久保存到数据库中。
conn.commit()
如果忘记调用该commit()
方法,psycopg2
将不会对表进行任何更改。
最后,通过调用cursor
和connection
对象的close()
方法,关闭与 PostgreSQL 数据库服务器的连接。
cur.close()
conn.close()
将一行插入 PostgreSQL 表的示例
为了演示,我们将使用我们在创建表教程中创建的suppliers
数据库中的vendors
表。
以下insert_vendor()
函数将新行插入vendors
表中并返回新生成的vendor_id
值。
#!/usr/bin/python
import psycopg2
from config import config
def insert_vendor(vendor_name):
""" insert a new vendor into the vendors table """
sql = """INSERT INTO vendors(vendor_name)
VALUES(%s) RETURNING vendor_id;"""
conn = None
vendor_id = None
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.execute(sql, (vendor_name,))
# get the generated id back
vendor_id = cur.fetchone()[0]
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return vendor_id
将多行插入 PostgreSQL 表的示例
向表中插入多行的步骤与插入一行的步骤类似,只不过在第三步中,您调用的是cursor
对象的executemany()
方法,而不是调用execute()
方法。
例如,以下insert_vendor_list()
函数将多行插入vendors
表中。
def insert_vendor_list(vendor_list):
""" insert multiple vendors into the vendors table """
sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
conn = None
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.executemany(sql,vendor_list)
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
要测试insert_vendor()
和insert_vendor_list()
函数,您可以使用以下代码片段:
if __name__ == '__main__':
# insert one vendor
insert_vendor("3M Co.")
# insert multiple vendors
insert_vendor_list([
('AKM Semiconductor Inc.',),
('Asahi Glass Co Ltd.',),
('Daikin Industries Ltd.',),
('Dynacast International Inc.',),
('Foster Electric Co. Ltd.',),
('Murata Manufacturing Co. Ltd.',)
])
在本教程中,您学习了从 Python 程序向 PostgreSQL 表插入一行或多行的步骤。
更新表中的数据
摘要:在本教程中,您将学习如何通过 Python 程序更新 PostgreSQL 表中的数据。
使用 psycopg2 更新 PostgreSQL 表数据的步骤
更新数据的步骤与将数据插入 PostgreSQL 表的步骤类似。
首先,通过调用psycopg
模块的connect()
函数连接到 PostgreSQL 数据库服务器。
conn = psycopg2.connect(dns)
该connect()
方法会返回一个新的connection
对象。
接下来,通过调用该connection
对象的cursor()
方法来创建一个新的cursor
对象。
cur = conn.cursor()
然后,通过调用cursor
对象的execute()
方法使用输入值执行 UPDATE 语句。
cur.execute(update_sql, (value1,value2))
该execute()
方法接受两个参数。第一个参数是要执行的 SQL 语句,在本例中是UPDATE
语句。第二个参数是要传递给UPDATE
语句的输入值列表。
如果想获取该UPDATE
语句影响的行数,可以在调用该execute()
方法后,从cursor
对象的rowcount
属性中获取。
之后,通过调用连接对象的commit()
方法将更改永久保存到数据库中。
conn.commit()
最后,通过调用游标和连接对象的close()
方法,关闭与 PostgreSQL 数据库服务器的通信。
cur.close()
conn.close()
更新数据示例
为了演示,我们将使用在创建表教程中创建的suppliers
数据库中的vendors
表。
假设供应商更改了名称,我们想要更新vendors
表中的更改。为此,我们开发了根据供应商 ID 更新供应商名称的update_vendor()
函数:
#!/usr/bin/python
import psycopg2
from config import config
def update_vendor(vendor_id, vendor_name):
""" update vendor name based on the vendor id """
sql = """ UPDATE vendors
SET vendor_name = %s
WHERE vendor_id = %s"""
conn = None
updated_rows = 0
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the UPDATE statement
cur.execute(sql, (vendor_name, vendor_id))
# get the number of updated rows
updated_rows = cur.rowcount
# Commit the changes to the database
conn.commit()
# Close communication with the PostgreSQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
if __name__ == '__main__':
# Update vendor id 1
update_vendor(1, "3M Corp")
首先,使用 psql 登录 PostgreSQL 数据库服务器上的suppliers
数据库。
第二步,从vendors
表中查询数据:
SELECT * FROM vendors WHERE vendor_id = 1;
vendor_id | vendor_name
-----------+-------------
1 | 3M Co.
(1 row)
第三步,启动另一个终端并运行 Python 程序来更新 id 为 1 的供应商名称:
python update.py
最后,从 psql 程序中查询vendors
表中的数据以验证更新。
SELECT * FROM vendors WHERE vendor_id = 1;
vendor_id | vendor_name
-----------+-------------
1 | 3M Corp
(1 row)
ID 为 1 的供应商名称已按预期更改。
在本教程中,您学习了如何通过 Python 程序更新 PostgreSQL 数据库表中的数据。
事务
摘要:在本教程中,您将学习如何使用 psycopg 数据库适配器在 Python 中处理 PostgreSQL 事务。
psycopg 中的事务介绍
在 psycopg 中,connection
类负责处理事务。当您使用cursor
对象向 PostgreSQL 数据库发出第一个 SQL 语句时,psycopg 会创建一个新事务。
从那时起,psycopg 将在同一事务中执行所有后续语句。如果任何语句失败,psycopg 将中止事务。
connection
类有两种结束事务的方法:commit()
和rollback()
。如果您想将所有更改永久提交到 PostgreSQL 数据库,请调用commit()
方法。如果您想取消更改,请调用rollback()
方法。关闭连接对象或使用del
销毁它也将导致隐式回滚。
需要注意的是,一个简单的SELECT
语句将启动一个事务,这可能会导致表膨胀和锁定等不良影响。因此,如果您正在开发长期运行的应用程序,则应在长时间不使用连接之前调用commit()
或rollback()
方法。
或者,您可以将connection
对象的autocommit
属性设置为True
。这确保 psycopg 执行每个语句后立即提交。
当您执行需要在事务外部执行的语句(例如CREATE DATABASE和 VACUUM)时,该autocommit
模式也很有用。
下面显示了 psycopg 中处理事务的典型模式:
#!/usr/bin/python
import psycopg2
conn = None
try:
conn = psycopg2.connect(dsn)
cur = conn.cursor()
# execute 1st statement
cur.execute(statement_1)
# execute 2nd statement
cur.execute(statement_1)
# commit the transaction
conn.commit()
# close the database communication
cur.close()
except psycopg2.DatabaseError as error:
print(error)
finally:
if conn is not None:
conn.close()
Postgres Python 事务示例
我们将使用我们在创建表教程中创建的parts
和vendor_parts
表进行演示。
假设您需要添加一个新部件并同时指定供应该部件的供应商。为此,首先将新行插入parts
表中并获取部件 ID。然后,将行插入vendor_parts
表中。下面的add_part()
函数演示了这个想法:
#!/usr/bin/python
import psycopg2
from config import config
def add_part(part_name, vendor_list):
# statement for inserting a new row into the parts table
insert_part = "INSERT INTO parts(part_name) VALUES(%s) RETURNING part_id;"
# statement for inserting a new row into the vendor_parts table
assign_vendor = "INSERT INTO vendor_parts(vendor_id,part_id) VALUES(%s,%s)"
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
# insert a new part
cur.execute(insert_part, (part_name,))
# get the part id
part_id = cur.fetchone()[0]
# assign parts provided by vendors
for vendor_id in vendor_list:
cur.execute(assign_vendor, (vendor_id, part_id))
# commit changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
为了测试该add_part()
函数,我们调用它来插入一些部件并给它们分配各自的供应商,如下所示:
if __name__ == '__main__':
add_part('SIM Tray', (1, 2))
add_part('Speaker', (3, 4))
add_part('Vibrator', (5, 6))
add_part('Antenna', (6, 7))
add_part('Home Button', (1, 5))
add_part('LTE Modem', (1, 5))
让我们查询parts
和vendor_parts
表来确认事务。
suppliers=# select * from parts;
part_id | part_name
---------+-------------
1 | SIM Tray
2 | Speaker
3 | Vibrator
4 | Antenna
5 | Home Button
6 | LTE Modem
(6 rows)
suppliers=# select * from vendor_parts;
vendor_id | part_id
-----------+---------
1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3
6 | 4
7 | 4
1 | 5
5 | 5
1 | 6
5 | 6
(12 rows)
如您所见,我们已成功将数据插入到parts
和vendor_parts
表中。
让我们插入另一部分,但这一次,我们故意使用无效的供应商 ID 来进行演示。程序不应在未给新部件分配供应商的情况下添加新部件。
# no rows inserted into the parts and vendor_parts tables
add_part('Power Amplifier', (99,))
发生异常。
insert or update on table "vendor_parts" violates foreign key constraint "vendor_parts_vendor_id_fkey"
DETAIL: Key (vendor_id)=(99) is not present in table "vendors".
我们再次从parts
和vendor_parts
表中查询数据,没有新数据,因此,该函数的运行符合预期。
使用with
语句运行事务
从 psycopg 2.5 开始,连接和游标是上下文管理器,因此您可以将它们与with
语句一起使用:
with psycopg2.connect(dsn) as conn:
with conn.cursor() as cur:
cur.execute(sql)
如果with
代码块内没有发生异常,Psycopg 就会提交事务,否则会回滚事务。
与其他上下文管理器对象不同,退出with
块不会关闭连接,而只会终止事务。因此,您可以在后续的另一个事务的with
语句中使用相同的connection
对象,如下所示:
conn = psycopg2.connect(dsn)
# transaction 1
with conn:
with conn.cursor() as cur:
cur.execute(sql)
# transaction 2
with conn:
with conn.cursor() as cur:
cur.execute(sql)
conn.close()
在本教程中,您学习了如何使用 psycopg 事务以及如何使用事务在 PostgreSQL 数据库中插入数据。
查询数据
摘要:在本教程中,您将学习如何使用 psycopg 数据库适配器在 Python 中查询 PostgreSQL 表中的数据。
Python 中从 PostgreSQL 表中查询数据的步骤
要在 Python 中查询一个或多个 PostgreSQL 表中的数据,请使用以下步骤。
首先,通过调用psycopg
模块的connect()
函数,建立与 PostgreSQL 数据库服务器的连接。
conn = psycopg2.connect(dsn)
如果连接创建成功,connect()
函数会返回一个新的connection
对象,否则会抛出DatabaseError
异常。
接下来,通过调用该connection
对象的cursor()
方法创建一个新游标。该cursor
对象用于执行SELECT语句。
cur = conn.cursor()
然后,通过调用该execute()
方法来执行一条SELECT
语句。如果要将值传递给SELECT
语句,请在SELECT
语句中使用占位符 ( %s
) ,并在调用execute()
方法时绑定输入值,如下所示。
cur.execute(sql, (value1,value2))
之后,使用 fetchone()
、 fetchall()
、 或 fetchmany()
方法处理存储过程返回的结果集。
fetchone()
会获取结果集中的下一行。它会返回单个元组,或者在没有更多行可用时返回None
。fetchmany(size=cursor.arraysize)
会获取size
参数指定的下一组行。如果省略此参数,arraysize
的值将决定要获取的行数。该fetchmany()
方法会返回元组列表,如果没有更多可用行,则返回空列表。fetchall()
会获取结果集中的所有行并返回元组列表。如果没有要获取的行,该fetchall()
方法将返回一个空列表。
最后,通过调用cursor
和connection
对象的close()
方法,关闭与 PostgreSQL 的通信
cur.close()
conn.close()
使用 fetchone() 方法查询数据
对于演示,我们将使用我们在创建表教程中创建的suppliers
数据库中的parts
、vendors
和vendor_parts
表
以下get_vendor()
函数从vendors
表中查询数据,并使用fetchone()
方法获取行 。
def get_vendors():
""" query data from the vendors table """
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name")
print("The number of parts: ", cur.rowcount)
row = cur.fetchone()
while row is not None:
print(row)
row = cur.fetchone()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
下面显示了该get_vendors()
函数的输出。
if __name__ == '__main__':
get_vendors()
The number of parts: 7
(1, '3M Corp')
(2, 'AKM Semiconductor Inc.')
(3, 'Asahi Glass Co Ltd.')
(4, 'Daikin Industries Ltd.')
(5, 'Dynacast International Inc.')
(6, 'Foster Electric Co. Ltd.')
(7, 'Murata Manufacturing Co. Ltd.')
使用 fetchall() 方法查询数据
以下get_parts()
函数使用游标对象的fetchall()
方法从结果集中获取行并显示parts
表中的所有零件。
def get_parts():
""" query parts from the parts table """
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT part_id, part_name FROM parts ORDER BY part_name")
rows = cur.fetchall()
print("The number of parts: ", cur.rowcount)
for row in rows:
print(row)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
get_parts()
The number of parts: 6
(4, 'Antenna')
(5, 'Home Button')
(6, 'LTE Modem')
(1, 'SIM Tray')
(2, 'Speaker')
(3, 'Vibrator')
使用 fetchmany() 方法查询数据
以下get_suppliers()
函数使用fetchmany()
方法查询零件和供应商数据。
def iter_row(cursor, size=10):
while True:
rows = cursor.fetchmany(size)
if not rows:
break
for row in rows:
yield row
def get_part_vendors():
""" query part and vendor data from multiple tables"""
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("""
SELECT part_name, vendor_name
FROM parts
INNER JOIN vendor_parts ON vendor_parts.part_id = parts.part_id
INNER JOIN vendors ON vendors.vendor_id = vendor_parts.vendor_id
ORDER BY part_name;
""")
for row in iter_row(cur, 10):
print(row)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
get_part_vendors()
('Antenna', 'Foster Electric Co. Ltd.')
('Antenna', 'Murata Manufacturing Co. Ltd.')
('Home Button', 'Dynacast International Inc.')
('Home Button', '3M Corp')
('LTE Modem', 'Dynacast International Inc.')
('LTE Modem', '3M Corp')
('SIM Tray', 'AKM Semiconductor Inc.')
('SIM Tray', '3M Corp')
('Speaker', 'Daikin Industries Ltd.')
('Speaker', 'Asahi Glass Co Ltd.')
('Vibrator', 'Dynacast International Inc.')
('Vibrator', 'Foster Electric Co. Ltd.')
在本教程中,我们向您展示了在 Python 中使用fetchone()
、fetchall()
和fetchmany()
方法,从 PostgreSQL 表中查询数据的各种方法。
调用 PostgreSQL 函数
摘要:在本教程中,您将学习如何从 Python 程序调用 PostgreSQL 函数。
在 Python 中调用 PostgreSQL 函数的步骤
要从 Python 程序调用 PostgreSQL 函数,请使用以下步骤:
首先,通过调用psycopg2
模块的connect()
函数创建一个新的数据库连接,连接到 PostgreSQL 数据库服务器。
conn = psycopg2.connect(dsn)
该connect()
方法会返回一个connection
类的新实例。
接下来,通过调用连接对象的cursor()
方法创建一个新游标。
cur = conn.cursor()
然后,将函数名称和可选输入值传递给游标对象的callproc()
方法。
cur.callproc('function_name', (value1,value2))
在内部,callproc()
方法会将函数调用和输入值转换为以下语句:
SELECT * FROM function_name(value1, value2);
因此,可以使用游标对象的execute()
方法来调用函数,如下所示:
cur.execute("SELECT * FROM function_name( %s,%s); ",(value1,value2))
两种形式的语句具有相同的效果。
之后,使用fetchone()
、fetchall()
、或fetchmany()
方法处理函数返回的结果集。
最后,调用cursor
和connection
对象的close()
方法,关闭与 PostgreSQL 数据库服务器的通信。
cur.close()
conn.close()
调用函数示例
下面的get_parts_by_vendors()
函数返回指定供应商提供的部件列表。
CREATE OR REPLACE FUNCTION get_parts_by_vendor(id integer)
RETURNS TABLE(part_id INTEGER, part_name VARCHAR) AS
$$
BEGIN
RETURN QUERY
SELECT parts.part_id, parts.part_name
FROM parts
INNER JOIN vendor_parts on vendor_parts.part_id = parts.part_id
WHERE vendor_id = id;
END;
$$ LANGUAGE plpgsql;
下面的get_parts()
函数调用get_parts_by_vendors()
函数:
#!/usr/bin/python
import psycopg2
from config import config
def get_parts(vendor_id):
""" get parts provided by a vendor specified by the vendor_id """
conn = None
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a cursor object for execution
cur = conn.cursor()
# another way to call a function
# cur.execute("SELECT * FROM get_parts_by_vendor( %s); ",(vendor_id,))
cur.callproc('get_parts_by_vendor', (vendor_id,))
# process the result set
row = cur.fetchone()
while row is not None:
print(row)
row = cur.fetchone()
# close the communication with the PostgreSQL database server
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
下面调用get_parts()
函数来获取 id 为 1 的供应商提供的部件列表:
if __name__ == '__main__':
get_parts(1)
在本教程中,您逐步学习了如何在 Python 中调用 PostgreSQL 函数。
调用 PostgreSQL 存储过程
摘要:在本教程中,您将学习如何从 Python 程序调用 PostgreSQL 存储过程。
在 Python 中调用 PostgreSQL 存储过程的步骤
要在 Python 程序中调用 PostgreSQL 存储过程,请按照以下步骤操作:
首先,通过调用connect()
函数创建新的数据库连接,连接到 PostgreSQL 数据库服务器:
conn = psycopg2.connect(dsn)
该connect()
方法会返回一个connection
类的新实例。
接下来,通过调用连接对象的cursor()
方法创建一个新游标。
cur = conn.cursor()
然后,将存储过程的名称和可选输入值传递给cursor
对象的execute()
方法。例如:
cur.execute("CALL sp_name(%s, %s);", (val1, val2))
如果您的存储过程不接受任何参数,则可以省略第二个参数,如下所示:
cur.execute("CALL sp_name();")
之后,调用commit()
方法来提交事务:
conn.commit();
最后,调用cursor
和connection
对象的close()
方法,关闭与 PostgreSQL 数据库服务器的连接。
cur.close()
conn.close()
调用存储过程示例
首先,在suppliers
数据库中创建如下的add_new_part()
存储过程。
CREATE OR REPLACE PROCEDURE add_new_part(
new_part_name varchar,
new_vendor_name varchar
)
AS $$
DECLARE
v_part_id INT;
v_vendor_id INT;
BEGIN
-- insert into the parts table
INSERT INTO parts(part_name)
VALUES(new_part_name)
RETURNING part_id INTO v_part_id;
-- insert a new vendor
INSERT INTO vendors(vendor_name)
VALUES(new_vendor_name)
RETURNING vendor_id INTO v_vendor_id;
-- insert into vendor_parts
INSERT INTO vendor_parts(part_id, vendor_id)
VALUEs(v_part_id,v_vendor_id);
END;
$$ LANGUAGE PLPGSQL;
其次,创建一个名为stored_proc.py
的新文件并定义如下的add_part()
函数。add_part()
函数从suppliers
数据库调用存储过程add_new_part()
:
#!/usr/bin/python
import psycopg2
from config import config
def add_part(part_name, vendor_name):
conn = None
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a cursor object for execution
cur = conn.cursor()
# call a stored procedure
cur.execute('CALL add_new_part(%s,%s)', (part_name, vendor_name))
# commit the transaction
conn.commit()
# close the cursor
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
add_part('OLED', 'LG')
执行 python 文件
要执行 python 文件,请使用以下命令:
python stored_proc.py
要验证插入,您可以从parts
、vendors
和vendor_parts
表中查询数据:
SELECT * FROM parts;
SELECT * FROM vendors;
SELECT * FROM vendor_parts;
在本教程中,您逐步学习了如何在 Python 中调用 PostgreSQL 存储过程。
处理 BLOB 数据
摘要:在本教程中,您将学习如何在 Python 中处理 PostgreSQL BLOB 数据。
SQL 标准将 BLOB 定义为用于在数据库中存储二进制数据的二进制大对象。使用 BLOB 数据类型,您可以将图片、文档等内容存储到表中。
PostgreSQL 不支持 BLOB,但您可以使用 BYTEA 数据类型来存储二进制数据。
我们来看一下part_drawings
表。
part_drawings
表在drawing_data
列中存储了部件的图片。我们将向您展示如何将二进制数据插入此列并将其读回。
将 BLOB 插入表中
要将 BLOB 数据插入表中,请使用以下步骤:
- 首先,从文件中读取数据。
- 接下来,通过从
connect()
函数创建新的连接对象来连接到 PostgreSQL 数据库。 - 然后,从该
connection
对象创建一个cursor
对象。 - 之后,使用输入值执行 INSERT 语句。对于 BLOB 数据,您需要使用 psycopg 模块的
Binary
对象。 - 最后,通过调用
connection
对象的commit()
方法,将更改永久提交到 PostgreSQL 数据库。
下面的write_blob()
函数,从path_to_file
参数指定的文件中读取二进制数据,并将其插入到part_drawings
表中。
#!/usr/bin/python
import psycopg2
from config import config
def write_blob(part_id, path_to_file, file_extension):
""" insert a BLOB into a table """
conn = None
try:
# read data from a picture
drawing = open(path_to_file, 'rb').read()
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# execute the INSERT statement
cur.execute("INSERT INTO part_drawings(part_id,file_extension,drawing_data) " +
"VALUES(%s,%s,%s)",
(part_id, file_extension, psycopg2.Binary(drawing)))
# commit the changes to the database
conn.commit()
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
下面的代码片段调用write_blob()
函数两次,以将两个新部件和相应图片文件的二进制数据插入到part_drawings
表中。
if __name__ == '__main__':
write_blob(1, 'images/simtray.jpg', 'jpg')
write_blob(2, 'images/speaker.jpg', 'jpg')
读取表中的 BLOB
从表中读取 BLOB 的步骤与从表中查询数据的步骤类似。从表中获取二进制数据后,我们可以保存到文件、输出到网络浏览器等。
下面的read_blob()
函数根据指定的部件 ID 从part_drawings
表中查询 BLOB 数据,并将 BLOB 数据保存到文件中。
def read_blob(part_id, path_to_dir):
""" read BLOB data from a table """
conn = None
try:
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# execute the SELECT statement
cur.execute(""" SELECT part_name, file_extension, drawing_data
FROM part_drawings
INNER JOIN parts on parts.part_id = part_drawings.part_id
WHERE parts.part_id = %s """,
(part_id,))
blob = cur.fetchone()
open(path_to_dir + blob[0] + '.' + blob[1], 'wb').write(blob[2])
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
以下代码片段读取 id 值为 1 和 2 的部件的二进制数据,并将二进制数据保存到文件夹images/blob
中。
在本教程中,您学习了如何在 Python 中使用 psycopg 数据库适配器处理 PostgreSQL BLOB 数据。
从表中删除数据
摘要:本教程向您展示在 Python 中如何使用 psycopg 数据库适配器从 PostgreSQL 表中删除数据。
Python 中删除 PostgreSQL 表数据的步骤
要在 Python 中删除 PostgreSQL 表中的数据,请使用以下步骤:
首先,通过调用 psycopg 模块的connect()
函数创建一个新的数据库连接。
conn = psycopg2.connect(dsn)
该connect()
函数会返回一个新的connection
对象。
接下来,要执行任何语句,您需要一个cursor
对象。要创建新的游标对象,请调用连接对象的cursor()
方法,如下所示:
cur = conn.cursor()
然后,执行DELETE语句。如果要将值传递给DELETE
语句,请在DELETE
语句中使用占位符 (%s
),并将输入值传递给execute()
方法的第二个参数。
带有一个用于表示字段id
值的占位符的DELETE
语句如下:
DELETE FROM table_1 WHERE id = %s;
要将值value_1
绑定到占位符,您可以调用execute()
方法,并将输入值作为元组传递给第二个参数,如下所示:
cur.execute(delete_sql, (value_1,))
之后,通过调用该connection
对象的commit()
方法,将更改永久保存到数据库中。
conn.commit()
最后,通过调用cursor
和connection
对象的close()
方法,关闭与 PostgreSQL 数据库服务器的通信。
cur.close()
conn.close()
Python 删除 PostgreSQL 表中数据的示例
为了演示,我们将使用在创建表教程中创建的suppliers
数据库中的parts
表。
以下delete_part()
函数删除part_id
指定的parts
表中的一行。
#!/usr/bin/python
import psycopg2
from config import config
def delete_part(part_id):
""" delete part by part id """
conn = None
rows_deleted = 0
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the UPDATE statement
cur.execute("DELETE FROM parts WHERE part_id = %s", (part_id,))
# get the number of updated rows
rows_deleted = cur.rowcount
# Commit the changes to the database
conn.commit()
# Close communication with the PostgreSQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return rows_deleted
在测试delete_part()
函数之前,我们从parts
表中查询数据结果如下:
suppliers=# SELECT * FROM parts;
part_id | part_name
---------+-------------
1 | SIM Tray
2 | Speaker
3 | Vibrator
4 | Antenna
5 | Home Button
6 | LTE Modem
(6 rows)
现在我们运行 Python 程序来删除 id 为 1 的部件。
if __name__ == '__main__':
deleted_rows = delete_part(2)
print('The number of deleted rows: ', deleted_rows)
我们再次从parts
表中查询数据以确认 Python 程序所做的删除。
suppliers=# SELECT * FROM parts;
part_id | part_name
---------+-------------
2 | Speaker
3 | Vibrator
4 | Antenna
5 | Home Button
6 | LTE Modem
(5 rows)
部件 ID 为 1 的行已按预期成功删除。
在本教程中,您学习了如何使用 Python 从 PostgreSQL 表中删除数据。