Psycopg 是最受欢迎的用于连接 PostgreSQL 的 Python 驱动库, Psycopg 完全遵守 Python DB API 2.0 规范, 并且是线程安全的, 在底层直接调用 C 库 libpq
安装
$ sudo apt install libpq-dev
$ pip install psycopg2-binary
简单示例
import psycopg2# 连接 PostgreSQL
conn = psycopg2.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 psycopg2TABLE = "demo"# 连接 PostgreSQL
dsn = "host=127.0.0.1 port=5432 dbname=gorm user=jianghuixin password=123456"
with psycopg2.connect(dsn) as conn:with conn.cursor() as cursor:# 创建 demo 表cursor.execute(f"CREATE TABLE {TABLE} (id serial PRIMARY KEY, name VARCHAR(10), age INT)")cursor.executemany(f"INSERT INTO {TABLE}(name, age) VALUES(%s, %s)",[('David', 15), ('Alice', 25)])conn.commit()# 获取 SQL 结果cursor.execute(f"SELECT * FROM {TABLE}")records = cursor.fetchall()print(records)
SQL 传参
在 execute() 和 executemany() 方法中, 第一个参数为 SQL 语句, 可以包含多个 %s 占位符, 第二个参数为元组或列表, 给 SQL 语句中的 %s 依次传递数据
>>> cur.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() 方法查看, 占位符被替换以后完整的 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"\n INSERT INTO some_table (an_int, a_date, a_string)\n VALUES (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 会给字符串加上双引号, 得到的是错误的 SQL 语句
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)
