Python訪問(wèn)PostgreSQL數(shù)據(jù)庫(kù)詳細(xì)操作
一、前言
1.1 概述
Python 是一種高級(jí)、通用的解釋型編程語(yǔ)言，以其優(yōu)雅、準(zhǔn)確、 簡(jiǎn)單的語(yǔ)言特性，在云計(jì)算、Web 開(kāi)發(fā)、自動(dòng)化運(yùn)維、數(shù)據(jù)科學(xué)以及機(jī)器學(xué)習(xí)等人工智能領(lǐng)域獲得了廣泛應(yīng)用。Python 定義了連接和操作數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)接口 Python DB API。不同的數(shù)據(jù)庫(kù)在此基礎(chǔ)上實(shí)現(xiàn)了特定的驅(qū)動(dòng)，這些驅(qū)動(dòng)都實(shí)現(xiàn)了標(biāo)準(zhǔn)接口。支持PostgreSQL的庫(kù)也有不少，從PostgreSQL提供的 WIKI 可以得知，使用得最多的而且最成熟的是Psycopg2。
PostgreSQL 數(shù)據(jù)庫(kù)是最常用的關(guān)系型數(shù)據(jù)庫(kù)之一，最吸引人的一點(diǎn)是它作為開(kāi)源數(shù)據(jù)庫(kù)且具有可拓展性，能夠提供豐富的應(yīng)用。運(yùn)用python可以很簡(jiǎn)單的建立PostgreSQL 數(shù)據(jù)庫(kù)連接，最常見(jiàn)的 Python 驅(qū)動(dòng)程序就是 psycopg，它完全實(shí)現(xiàn)了 Python DB-API 2.0 接口規(guī)范。接下來(lái)我們介紹如何通過(guò) psycopg 連接和操作 PostgreSQL 數(shù)據(jù)庫(kù)。
1.2 什么是 Psycopg
PostgreSQL可以使用 psycopg 模塊與Python集成，是用于 Python 編程語(yǔ)言的 PostgreSQL 數(shù)據(jù)庫(kù)適配器，其是非常小，快速，穩(wěn)定的。 不需要單獨(dú)安裝此模塊，因?yàn)槟J(rèn)情況下它會(huì)隨著Python一起發(fā)布。首先，我們需要安裝 Python 。Python 可以通過(guò)官方網(wǎng)站下載，安裝之后可以通過(guò)以下命令查看版本信息：
Python -v
然后通過(guò) pip 安裝最新的 psycopg：
# 適用于Python2 pip install --upgrade psycopg2 # 適用于Python3 pip3 install --upgrade psycopg2
也可以在pycharm中查找psycopg2安裝包：
想要使用psycopg2，必須用import語(yǔ)句導(dǎo)入該包：
import psycopg2
二、操作 PostgreSQL
2.1 連接數(shù)據(jù)庫(kù)
為了方便開(kāi)發(fā)，在 Pycharm 中新建一個(gè)項(xiàng)目 PythonPostgreSQL，然后創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)連接的配置文件 dbconfig.ini，添加以下內(nèi)容：
[postgresql] host = 127.0.0.1 port = 5432 database = HR user = navicat password = testnavicat
配置文件中存儲(chǔ)了數(shù)據(jù)庫(kù)的連接信息：主機(jī)、端口、數(shù)據(jù)庫(kù)、用戶以及密碼，按照自己的環(huán)境進(jìn)行配置。要使用 psycopg2 模塊，必須首先創(chuàng)建一個(gè)表示數(shù)據(jù)庫(kù)的 Connection 對(duì)象，然后可以選擇創(chuàng)建可以執(zhí)行所有SQL語(yǔ)句的游標(biāo)對(duì)象。新建一個(gè)測(cè)試數(shù)據(jù)庫(kù)連接的 Python 文件 postgresql_connection.py，示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
print("Opened database successfully")
pass
if __name__ == '__main__':
main()上述代碼展示了如何連接到一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)不存在，那么它就會(huì)被創(chuàng)建，最終將返回一個(gè)數(shù)據(jù)庫(kù)對(duì)象。connection類表示數(shù)據(jù)庫(kù)連接對(duì)象，是由psycopg2.connect()方法創(chuàng)建。psycopg2.connect() 函數(shù)創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)會(huì)話并且返回一個(gè)連接對(duì)象，以執(zhí)行 DML、DDL、DQL 等數(shù)據(jù)庫(kù)事務(wù)。該函數(shù)的參數(shù)為：
| 參數(shù) | 說(shuō)明 |
|---|---|
| database | 要連接的數(shù)據(jù)庫(kù)名稱 |
| user | 連接數(shù)據(jù)庫(kù)的用戶名 |
| password | 連接數(shù)據(jù)庫(kù)的密碼 |
| host | 數(shù)據(jù)庫(kù)地址，一般為 localhost，或者主機(jī)的IP地址 |
| port | 端口，默認(rèn)為5432 |
connection提供了如下常用的數(shù)據(jù)庫(kù)操作：
| 數(shù)據(jù)庫(kù)操作 | 說(shuō)明 |
|---|---|
| commit() | 提交任何未提交的事務(wù)到數(shù)據(jù)庫(kù) |
| rollback() | 回滾 |
| close() | 關(guān)閉數(shù)據(jù)庫(kù)。如果關(guān)閉數(shù)據(jù)庫(kù)時(shí)仍有未提交的事務(wù)，則執(zhí)行回滾操作 |
為了執(zhí)行任何 SQL 事務(wù)或語(yǔ)句，我們需要實(shí)現(xiàn)游標(biāo) cursor 類，用來(lái)里執(zhí)行 PostgreSQL 命令，其由 connection.cursor() 方法創(chuàng)建。它們?cè)谡麄€(gè)生命周期內(nèi)都綁定到連接，并且所有命令都在連接包裝的數(shù)據(jù)庫(kù)會(huì)話的上下文中執(zhí)行，退出程序時(shí)也會(huì)關(guān)閉光標(biāo)。
cur = conn.cursor();
有了cursor對(duì)象，就可以操作數(shù)據(jù)庫(kù)了，cursor 提供了 execute 方法用來(lái)執(zhí)行SQL語(yǔ)句，詳細(xì)方法繼續(xù)往下看。
2.2 異常處理
使用psycopg2的 Error 進(jìn)行異常捕獲，能捕獲到sql執(zhí)行時(shí)期的所有異常。處理此異常的最佳方法是將 conn 對(duì)象和 cursor 的閉包放在 finally 塊中，以防我們的連接出現(xiàn)錯(cuò)誤。另外，如果數(shù)據(jù)庫(kù)沒(méi)有連接，并且對(duì)連接對(duì)象應(yīng)用了 close 方法，我們的 finally 塊中就會(huì)出錯(cuò)。下面代碼中表test是庫(kù)中不存的表，執(zhí)行sql后會(huì)報(bào)異常，經(jīng)過(guò)異常捕獲后非常美觀，不影響程序運(yùn)行；
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
global connection
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
print("連接成功，PostgreSQL 服務(wù)器版本：")
except (Exception, DatabaseError) as e:
print("連接 PostgreSQL 失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
print("PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。")
pass
if __name__ == '__main__':
main()2.3 創(chuàng)建表
當(dāng)數(shù)據(jù)庫(kù)建立連接成功后，可以通過(guò)執(zhí)行 CREATE TABLE 和 DROP TABLE 語(yǔ)句執(zhí)行DDL 事務(wù)來(lái)創(chuàng)建和刪除數(shù)據(jù)表。我們創(chuàng)建一個(gè)新的 Python 文件 postgresql_create.py 在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)表，示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = """ create table createtabletest (
id serial primary key,
name character varying(10) not null unique,
created_at timestamp not null
) """
# 執(zhí)行 SQL 命令
cur.execute(sql)
# 關(guān)閉游標(biāo)
cur.close()
# 提交事務(wù)
connection.commit()
print("操作成功！")
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
print("PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。")
pass
if __name__ == '__main__':
main()同樣是先連接數(shù)據(jù)庫(kù)，然后利用游標(biāo)對(duì)象的 execute() 方法執(zhí)行 SQL 命令創(chuàng)建表。commit 方法用于提交事務(wù)修改，如果不執(zhí)行該操作不會(huì)真正創(chuàng)建表，因?yàn)?psycopg2 連接 PostgreSQL 默認(rèn)不會(huì)自動(dòng)提交（autocommit）。執(zhí)行該腳本的結(jié)果如下：
操作成功！
PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。
如果 createtabletest 表已經(jīng)存在，將會(huì)返回以下錯(cuò)誤：
操作失?。?relation "createtabletest" already exists
2.4 INSERT 操作
我們將使用 INSERT 命令執(zhí)行 DML 事務(wù)，可以將一些數(shù)據(jù)添加到表中。Python 中游標(biāo)對(duì)象的 execute() 方法用于執(zhí)行 SQL 語(yǔ)句，該方法可以接收參數(shù)實(shí)現(xiàn)預(yù)編譯語(yǔ)句。以下顯示了我們?nèi)绾卧谝褎?chuàng)建的表中插入記錄，示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = "insert into createtabletest(name, created_at) values (%s, %s) RETURNING id"
# 執(zhí)行 SQL 命令
cur.execute(sql, ('tony', '2023-04-15 00:56:00'))
cur.execute(sql, ('zhangSan', '2023-04-15 00:56:00'))
cur.execute(sql, ('liSi', '2023-04-15 00:56:00'))
cur.execute(sql, ('wangWu', '2023-04-15 00:56:00'))
# 獲取 id
id = cur.fetchone()[0]
# 提交事務(wù)
connection.commit()
print("操作成功！ 用戶 id：", id)
# 關(guān)閉游標(biāo)
cur.close()
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
print("PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。")
pass
if __name__ == '__main__':
main()上面示例中，sql 變量中的百分號(hào)（%）是占位符，這些占位符的值在 execute() 方法中進(jìn)行替換；游標(biāo)對(duì)象的 fetchone 方法用于返回一行結(jié)果，這里用于獲取 RETURNING id 返回的用戶 id。
2.5 SELECT 操作
當(dāng)我們創(chuàng)建表完成并插入數(shù)據(jù)后，可以執(zhí)行一個(gè) SELECT 事務(wù)來(lái)查看插入的數(shù)據(jù)。游標(biāo)對(duì)象提供了三種獲取返回結(jié)果的方法。如下表所示：
| 方法 | 說(shuō)明 |
|---|---|
| fetchone() | 獲取下一行數(shù)據(jù)。當(dāng)沒(méi)有更多的數(shù)據(jù)是可用時(shí)，返回null |
| fetchmany([size=cursor.arraysize]) | 獲取下一組數(shù)據(jù)行。當(dāng)沒(méi)有找到記錄，返回空列表。 |
| fetchall() | 獲取所有查詢結(jié)果，返回值為tuple列表。空行時(shí)則返回空列表 |
我們創(chuàng)建一個(gè)新的文件來(lái)展示如何獲取并顯示在上面的例子中創(chuàng)建的記錄，示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = "select id, name, created_at from createtabletest"
# 執(zhí)行 SQL 命令
cur.execute(sql)
print("用戶數(shù)量：", cur.rowcount)
# 獲取結(jié)果
user = cur.fetchone()
while user is not None:
print(user)
user = cur.fetchone()
# 關(guān)閉游標(biāo)
cur.close()
except (Exception, DatabaseError) as e:
print("操作失敗：", e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
pass
if __name__ == '__main__':
main()在上面示例中，游標(biāo)對(duì)象的 rowcount 屬性代表了返回的數(shù)據(jù)行數(shù)，fetchone() 方法返回一行數(shù)據(jù)或者 None，while 循環(huán)用于遍歷和打印查詢結(jié)果。
2.6 UPDATE 操作
更新數(shù)據(jù)的流程跟之前的查詢、插入類似，可以使用UPDATE語(yǔ)句來(lái)獲取并顯示更新的記錄，psycopg2支持單條數(shù)據(jù)更新及批量更新，不過(guò)需要注意的是，PostgreSQL的數(shù)據(jù)被修改后，直接SELECT的話，數(shù)據(jù)并不會(huì)根據(jù)主鍵自動(dòng)排序。示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = "update created_at set name = %s where id = %s"
# 執(zhí)行 SQL 命令
cur.execute(sql, ('tom', 1))
# 獲取 id
rows = cur.rowcount
# 提交事務(wù)
connection.commit()
print("操作成功！ 更新行數(shù)：", rows)
# 再次查詢數(shù)據(jù)
sql = "select id, name, created_at from createtabletest where id = 1"
cur.execute(sql)
user = cur.fetchone()
print(user)
# 關(guān)閉游標(biāo)
cur.close()
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
pass
if __name__ == '__main__':
main()2.7 DELETE 操作
刪除操作很簡(jiǎn)單，與更新數(shù)據(jù)的操作基本相同，只需要將 UPDATE 語(yǔ)句替換成 DELETE 語(yǔ)句，就可以刪除表中的數(shù)據(jù)。示例代碼如下所示：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = "delete from createtabletest where id = %s"
# 執(zhí)行 SQL 命令
cur.execute(sql, (1,))
rows = cur.rowcount
# 提交事務(wù)
connection.commit()
print("操作成功！ 刪除行數(shù)：", rows)
# 關(guān)閉游標(biāo)
cur.close()
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
pass
if __name__ == '__main__':
main()在 MySQ 中，TRUNCATE TABLE 語(yǔ)句會(huì)將表的數(shù)據(jù)清空，而且會(huì)重置自增主鍵的計(jì)數(shù)。但是在 PostgreSQL 中，必須要在這個(gè)語(yǔ)句后面添加 RESTART IDENTITY，才能將主鍵重置，不然就只會(huì)清空表。
2.8 事務(wù)管理
在前面的示例中，需要使用 connection.commit() 提交對(duì) PostgreSQL 數(shù)據(jù)庫(kù)執(zhí)行的修改，這是因?yàn)?psycopg2 默認(rèn)沒(méi)有打開(kāi)自動(dòng)提交功能，我們也可以利用連接對(duì)象的 autocommit 屬性設(shè)置是否自動(dòng)提交。
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 打印和設(shè)置自動(dòng)提交
print('默認(rèn) autocommit：', connection.autocommit)
connection.autocommit = True
print('新的 autocommit：', connection.autocommit)
# 創(chuàng)建一個(gè)游標(biāo)
cur = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = " insert into createtabletest(name, created_at) values (%s, %s) RETURNING id "
# 執(zhí)行 SQL 命令
cur.execute(sql, ('tony', '2020-06-08 11:00:00'))
# 獲取 id
id = cur.fetchone()[0]
print("操作成功！ 用戶 id：", id)
# 關(guān)閉游標(biāo)
cur.close()
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
print("PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。")
pass
if __name__ == '__main__':
main()通過(guò) connection.autocommit 設(shè)置了自動(dòng)提交，所以 INSERT 語(yǔ)句插入數(shù)據(jù)之后不需要再執(zhí)行 commit 操作。如果一個(gè)事務(wù)中包含多個(gè)數(shù)據(jù)庫(kù)操作，還是應(yīng)該在事務(wù)的最后統(tǒng)一執(zhí)行提交，并且在異常處理部分通過(guò)連接對(duì)象的 rollback() 方法回滾部分完成的事務(wù)。
另一種管理事務(wù)的方法是使用 with 語(yǔ)句，這樣可以避免手動(dòng)的資源管理和事務(wù)操作。
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 with 語(yǔ)句管理事務(wù)
with psycopg2.connect(**db_config) as connection:
# 創(chuàng)建一個(gè)游標(biāo)
with connection.cursor() as cur:
# 插入數(shù)據(jù)
sql = " insert into createtabletest(name, created_at) values (%s, %s) "
cur.execute(sql, ('Jason', '2023-04-15 10:30:00'))
# 更新數(shù)據(jù)
sql = "update createtabletest set created_at = %s where name = %s"
cur.execute(sql, ('2023-04-15 11:00:00', 'tony'))
sql = " select id, name, created_at from createtabletest "
# 查詢數(shù)據(jù)
cur.execute(sql)
# 獲取結(jié)果
user = cur.fetchone()
while user is not None:
print(user)
user = cur.fetchone()
except (Exception, DatabaseError) as e:
print("操作失敗：", e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
pass
if __name__ == '__main__':
main()在上面示例中，整個(gè)事務(wù)包含插入數(shù)據(jù)、更新數(shù)據(jù)以及查詢數(shù)據(jù)三個(gè)操作。
2.9 調(diào)用存儲(chǔ)函數(shù)
游標(biāo)對(duì)象的 callproc() 方法可以用于執(zhí)行存儲(chǔ)函數(shù)。我們先創(chuàng)建一個(gè)返回用戶數(shù)量的函數(shù) get_user_count：
CREATE OR REPLACE FUNCTION get_user_count() returns int AS $$ DECLARE ln_count int; BEGIN select count(*) into ln_count from users; return ln_count; END; $$ LANGUAGE plpgsql;
接下來(lái)創(chuàng)建一個(gè)新的文件來(lái)展示如何調(diào)用存儲(chǔ)函數(shù)：
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 with 語(yǔ)句管理事務(wù)
with psycopg2.connect(**db_config) as connection:
# 創(chuàng)建一個(gè)游標(biāo)
with connection.cursor() as cur:
# 調(diào)用存儲(chǔ)函數(shù)
cur.callproc('get_user_count')
row = cur.fetchone()[0]
print('用戶總數(shù)：', row)
except (Exception, DatabaseError) as e:
print("操作失敗：", e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
pass
if __name__ == '__main__':
main()callproc() 方法調(diào)用存儲(chǔ)函數(shù)也可以寫(xiě)成以下等價(jià)的形式：
cur.execute('select * from get_user_count()')
2.10 批量操作
上面示例中提到 python操作 PostgreSql 的操作數(shù)據(jù)基本用法，但這并不能滿足我們操作大量數(shù)據(jù)的美好心愿，在實(shí)際使用中，更多的時(shí)候需要進(jìn)行批量的增刪改查操作。批量操作數(shù)據(jù)庫(kù)有多種方法，根據(jù)官網(wǎng)的敘述，數(shù)據(jù)批量操作有以下幾種方法：
| 方法 | 說(shuō)明 |
|---|---|
| executemany | 最簡(jiǎn)單的一種方法，屬于游標(biāo)的實(shí)例方法 |
| execute_batch | 性能好，速度快的方法，屬于類方法 |
| execute_values | 官方推薦的方法，但是條件是要批量操作的字段的值必須相同 |
import psycopg2
from psycopg2 import DatabaseError
from configparser import ConfigParser
from psycopg2.extras import execute_batch
# 讀取數(shù)據(jù)庫(kù)配置文件，返回一個(gè)字典對(duì)象
def read_db_config(filename='dbconfig.ini', section='postgresql'):
# 創(chuàng)建解析器，讀取配置文件
parser = ConfigParser()
parser.read(filename)
# 獲取 postgresql 部分的配置
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception('文件 {1} 中未找到 {0} 配置信息！'.format(section, filename))
return db
db_config = read_db_config()
connection = None
def main():
try:
# 使用 psycopg2.connect 方法連接 PostgreSQL 數(shù)據(jù)庫(kù)
connection = psycopg2.connect(**db_config)
# 打印和設(shè)置自動(dòng)提交
print('默認(rèn) autocommit：', connection.autocommit)
connection.autocommit = True
print('新的 autocommit：', connection.autocommit)
# 創(chuàng)建一個(gè)游標(biāo)
cursor = connection.cursor()
# 定義 SQL 語(yǔ)句
sql = " insert into createtabletest(name, created_at) values (%s, %s) RETURNING id "
data = [['member%d' % x, '2023-04-16 11:%d:00' % x] for x in range(0, 60)]
# 執(zhí)行 SQL 命令
execute_batch(cursor, sql, data, page_size=len(data))
# 獲取 id
id = cursor.fetchone()[0]
print("操作成功！ 用戶 id：", id)
# 關(guān)閉游標(biāo)
cursor.close()
except (Exception, DatabaseError) as e:
print("操作失?。?, e)
finally:
# 釋放數(shù)據(jù)庫(kù)連接
if connection is not None:
connection.close()
print("PostgreSQL 數(shù)據(jù)庫(kù)連接已關(guān)閉。")
pass
if __name__ == '__main__':
main()總結(jié)
到此這篇關(guān)于Python訪問(wèn)PostgreSQL數(shù)據(jù)庫(kù)的文章就介紹到這了,更多相關(guān)Python訪問(wèn)PostgreSQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- Python隨機(jī)生成數(shù)據(jù)后插入到PostgreSQL
- Python連接PostgreSQL數(shù)據(jù)庫(kù)的方法
- Python實(shí)現(xiàn)連接postgresql數(shù)據(jù)庫(kù)的方法分析
- python連接PostgreSQL數(shù)據(jù)庫(kù)的過(guò)程詳解
- python 兩個(gè)數(shù)據(jù)庫(kù)postgresql對(duì)比
- Python 中創(chuàng)建 PostgreSQL 數(shù)據(jù)庫(kù)連接池
- Python讀取postgresql數(shù)據(jù)庫(kù)詳情
相關(guān)文章
python模擬嗶哩嗶哩滑塊登入驗(yàn)證的實(shí)現(xiàn)
這篇文章主要介紹了python模擬嗶哩嗶哩滑塊登入驗(yàn)證的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-04-04
這篇文章主要介紹了淺談pandas中對(duì)nan空值的判斷和陷阱，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-03-03
python控制臺(tái)實(shí)現(xiàn)tab補(bǔ)全和清屏的例子
今天小編就為大家分享一篇python控制臺(tái)實(shí)現(xiàn)tab補(bǔ)全和清屏的例子，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2019-08-08
在PyTorch中,我們經(jīng)常需要處理多維數(shù)據(jù),如張量tensors,這篇文章主要介紹了一些Pytorch常用的維度轉(zhuǎn)換方法,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-03-03
Python采用Django開(kāi)發(fā)自己的博客系統(tǒng)
這篇文章主要為大家詳細(xì)介紹了Python采用Django開(kāi)發(fā)自己的博客系統(tǒng)，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-08-08
centos系統(tǒng)升級(jí)python 2.7.3
CentOS上安裝的python版本是2.6，不能滿足我運(yùn)行軟件的要求，所以對(duì)python進(jìn)行升級(jí)。Python的最新版本已經(jīng)是3.3，但是Python3的兼容性可能還有一定的問(wèn)題，所以還是升級(jí)到2.7較為保險(xiǎn)。2014-07-07
今天小編就為大家分享一篇基于Python中求和函數(shù)sum的用法詳解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-06-06
這篇文章主要為大家詳細(xì)介紹了pygame游戲之旅的第10篇，教大家如何添加游戲界面按鍵圖形，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-11-11
Pycharm中pyqt工具配置(Qt Designer、PyUIC、PyRCC)
Pycharm中進(jìn)行擴(kuò)展工具設(shè)置，從而實(shí)現(xiàn)在pycharm中打開(kāi)Qt Designer、Ui文件生成Py文件、資源文件生成Py文件三個(gè)功能，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-07-07
excel中的圖片非常常見(jiàn)，但是通過(guò)python讀取excel中的圖片沒(méi)有很好的解決辦法。今天小編給大家分享一種比較聰明的方法，感興趣的朋友跟隨腳本之家小編看看吧2018-07-07