Psycopg 是最受欢迎的用于连接 PostgreSQL 的 Python 驱动库, Psycopg 完全遵守 Python DB API 2.0 规范, 并且是线程安全的, 在底层直接调用 C 库 libpq

安装
$ pip install psycopg2-binary

psycopg2-binary 是预编译的包, 自带 libpq 和 libssl 库

psycopg2 的命名来源于 http://psyco.sourceforge.net/, 末尾的 pg 表示 PostgreSQL

简单示例
import psycopg2 as pg# 连接 PostgreSQLconn = pg.connect(host='127.0.0.1', port=5432, dbname='gorm', user='jianghuixin',password='123456')cursor = conn.cursor()# 执行 SQL 查询语句cursor.execute("SELECT * FROM user_infos")# 获取 SQL 查询结果records = cursor.fetchall()print(records)# 关闭连接cursor.close()conn.close()
使用

connect() 函数创建一个数据库的 session, 并返回一个 connection 实例

  1. connection 实例支持:
  • 使用 cursor() 方法创建 cursor 实例
  • 事务操作例如 commit() 或 rollback() 方法
  1. cursor 实例支持:
  • 通过 execute() 与 executemany() 方法执行 SQL 语句
  • 遍历 cursor 获取 SQL 查询的数据, 或者调用 fetchone(), fetchmany(), fetchall() 方法

connection 和 cursor 均支持 with 语句

import psycopg2 as pgdsn = "host=127.0.0.1 port=5432 dbname=gorm user=jianghuixin password=123456"with pg.connect(dsn) as conn:with conn.cursor() as cursor:# 清理 demo 表cursor.execute("DROP TABLE IF EXISTS demo")# 创建 demo 表cursor.execute("CREATE TABLE demo (id serial PRIMARY KEY, name VARCHAR(10), age INT)")# 插入记录cursor.executemany("INSERT INTO demo(name, age) VALUES(%s, %s)",[('David', 15), ('Alice', 25)])# 提交事务conn.commit()cursor.execute("SELECT * FROM demo")records = cursor.fetchall()print(records)
SQL 传参

在 execute() 和 executemany() 方法中, 第一个参数为 SQL 语句, 可以包含多个 %s 占位符, 第二个参数为元组或列表, 给 SQL 语句中的 %s 依次传递数据

>>> cursor.execute("""... INSERT INTO some_table (an_int, a_date, a_string)... VALUES (%s, %s, %s);... """,... (10, datetime.date(2005, 11, 18), "O'Reilly"))

对应的 SQL 语句为:

INSERT INTO some_table (an_int, a_date, a_string)VALUES (10, '2005-11-18'::date, 'O''Reilly');

可以通过 cursor.mogrify() 方法查看 %s 占位符被替换以后完整的 SQL 语句

>>> cursor.mogrify("""...: INSERT INTO some_table (an_int, a_date, a_string)...: VALUES (%s, %s, %s);...: """,...: (0, datetime.date(2005, 11, 18), "O'Reilly"))b"\nINSERT INTO some_table (an_int, a_date, a_string)\nVALUES (0, '2005-11-18'::date, 'O''Reilly');\n"

注意占位符只能使用 %s, 即使待传递的数据是整数, 也不能使用 %d

execute() 方法的第二个参数也可以是字典, 占位符需要改为 %(name)s, name 为字典的键

d = {'language': 'C++', 'year': 35}cursor.execute("INSERT INTO demo(name, age) VALUES(%(language)s, %(year)s)", d)

在 SQL 语句中, 如果要传递表名或字段名, 不能使用 %s 占位符机制, 因为 %s 会给字符串加上单引号, 单引号在 PostgreSQL 中仅用于标识实际的值, 不能标识表名或字段名

FIELD = 'name'cursor.execute("SELECT %s FROM demo", (FIELD,)) # 错误cursor.execute("SELECT %s FROM demo" % FIELD) # 正确(使用 % 运算符)cursor.execute(f"SELECT {FIELD} FROM demo") # 正确(使用 f-string)

以下展示了具体的替换结果:

In [5]: cursor.mogrify("SELECT %s FROM demo", (FIELD,))Out[5]: b"SELECT 'name' FROM demo"In [6]: cursor.mogrify(f"SELECT {FIELD} FROM demo")Out[6]: b'SELECT name FROM demo'