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 实例
- connection 实例支持:
- 使用 cursor() 方法创建 cursor 实例
- 事务操作例如 commit() 或 rollback() 方法
- 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'