在完成金仓数据库的连接配置与连接池管理后，接下来需要掌握核心的数据操作技能。本文作为系列的下篇，将深入讲解SQL执行、结果集处理、批量操作、COPY命令等实用功能，帮助你在Python项目中高效操作金仓数据库。
一、SQL执行与结果集处理
执行SQL查询是数据库操作的基础。使用ksycopg2库时，首先需要通过连接对象的cursor()方法创建游标，然后执行SQL语句并获取结果。完整的查询流程如下：
import ksycopg2
conn = ksycopg2.connect(
database='TEST',
user='SYSTEM',
password='123456',
host='127.0.0.1',
port='54321'
)
cur = conn.cursor()
# 执行查询
cur.execute("SELECT id, name FROM test_user WHERE age > %s", (18,))
# 获取结果
rows = cur.fetchall()
for row in rows:
print(f"id: {row[0]}, name: {row[1]}")
cur.close()
conn.close()关键步骤说明：
- 创建游标：使用
cursor()创建游标对象 - 执行SQL：调用
execute()执行查询语句 - 获取结果：通过
fetchall()方法获取全部结果 - 资源清理：务必关闭游标和连接释放资源
获取结果集的三种方式：
- fetchone()：逐条获取，适合处理大数据集，不会一次性加载所有数据到内存
cur.execute("SELECT id, name FROM test_user")
while True:
row = cur.fetchone()
if row is None:
break
print(row)- fetchmany()：分批获取，平衡性能与内存占用
cur.execute("SELECT id, name FROM test_user")
while True:
rows = cur.fetchmany(100) # 一次拿100条
if not rows:
break
for row in rows:
process_row(row)- fetchall()：一次性获取全部结果，数据量较小时方便，超过万条建议避免
cur.execute("SELECT id, name FROM test_user")
rows = cur.fetchall() # 数据量小的时候用
for row in rows:
print(row)⚠️ 获取列信息：通过cursor.description属性可以获取查询结果的列名和类型信息，便于动态处理结果集。
cur.execute("SELECT id, name, created_at FROM test_user")
for col in cur.description:
print(f"列名: {col.name}, 类型码: {col.type_code}, 长度: {col.internal_size}")列名: id, 类型码: 23, 长度: 4
列名: name, 类型码: 1043, 长度: -1
列名: created_at, 类型码: 1114, 长度: 8执行非查询SQL：INSERT、UPDATE、DELETE等语句同样使用execute()执行，但务必调用commit()提交事务。
cur = conn.cursor()
# 插入
cur.execute("INSERT INTO test_user (id, name) VALUES (%s, %s)", (1, '张三'))
# 更新
cur.execute("UPDATE test_user SET name = %s WHERE id = %s", ('李四', 1))
# 删除
cur.execute("DELETE FROM test_user WHERE id = %s", (1,))
# 注意：需要提交事务
conn.commit()
cur.close()二、参数传递与防SQL注入
✅ 使用占位符传递参数：通过ksycopg2占位符传递参数，库会自动处理转义，有效防止SQL注入攻击。
# 正确写法：参数单独传递
cur.execute(
"INSERT INTO test_user (id, name) VALUES (%s, %s)",
(1, "张三")
)
# 错误写法：自己拼接字符串，有 SQL 注入风险
cur.execute(f"INSERT INTO test_user (id, name) VALUES ({id}, '{name}')")位置占位符示例：
# 用元组传参
cur.execute(
"SELECT * FROM test_user WHERE age > %s AND name LIKE %s",
(18, '%张%')
)命名占位符示例：
# 用字典传参，参数多的时候更清晰
cur.execute(
"SELECT * FROM test_user WHERE age > %(min_age)s AND name LIKE %(name_pattern)s",
{'min_age': 18, 'name_pattern': '%张%'}
)这种参数化查询方式与C++、JavaScript、TypeScript等语言中的预处理语句原理一致，都是将SQL结构与数据分离，保障安全性。
三、批量操作性能对比
3.1 executemany()的局限：虽然ksycopg2提供了executemany()方法，但其内部实现是循环调用execute()，每条SQL单独发送，性能提升有限。
data = [(1, '张三'), (2, '李四'), (3, '王五')]
cur.executemany("INSERT INTO test_user (id, name) VALUES (%s, %s)", data)
conn.commit()3.2 execute_batch()批量执行：ksycopg2.extras.execute_batch()将多条SQL分组发送，减少网络往返次数。
from ksycopg2 import extras
data = [(1, '张三'), (2, '李四'), (3, '王五'), ...] # 几百条数据
extras.execute_batch(
cur,
"INSERT INTO test_user (id, name) VALUES (%s, %s)",
data,
page_size=100 # 每100条发一次
)
conn.commit()参数page_size控制每组条数，建议设置在100-500之间，平衡网络开销与SQL长度。
3.3 execute_values()最优方案：execute_values()将所有数据拼成一条INSERT语句，效率最高。
from ksycopg2 import extras
data = [(1, '张三'), (2, '李四'), (3, '王五')]
extras.execute_values(
cur,
"INSERT INTO test_user (id, name) VALUES %s",
data,
page_size=100
)
conn.commit()INSERT INTO test_user (id, name) VALUES (1, '张三'), (2, '李四'), (3, '王五')三种批量插入方式对比（插入1万条数据测试）：
| 方式 | 网络往返 | 速度 | 适用场景 |
|---|---|---|---|
| executemany | 1万次 | 慢 | 少量数据 |
| execute_batch | 100次 | 中 | 中等数据量 |
| execute_values | 1次 | 快 | 大批量数据 |
通过对比可见，execute_values()性能最佳，大批量插入时推荐优先使用。这种优化思路与Go语言中批量写入数据库的实践类似，都是通过减少数据库交互次数提升性能。
四、存储过程调用与COPY命令
4.1 调用函数：
# 先创建函数
cur.execute("""
CREATE OR REPLACE FUNCTION add_user(p_id INTEGER, p_name TEXT)
RETURNS TEXT AS $$
BEGIN
INSERT INTO test_user (id, name) VALUES (p_id, p_name);
RETURN 'success';
END;
$$ LANGUAGE plpgsql;
""")
# 调用函数
cur.callproc('add_user', (10, 'test_user'))
result = cur.fetchone()
print(result) # ('success',)
conn.commit()4.2 调用存储过程：需使用execute()配合CALL语句。
-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_user_name(
p_id INTEGER,
p_new_name TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE test_user SET name = p_new_name WHERE id = p_id;
END;
$$;# 调用存储过程
cur.execute("CALL update_user_name(%s, %s)", (1, '新名字'))
conn.commit()4.3 COPY命令高效导入导出：COPY是金仓数据库提供的快速数据导入导出方式，比INSERT快数倍。
copy_from()从文件导入：
cur = conn.cursor()
with open('data.csv', 'r') as f:
cur.copy_from(
file=f,
table='test_user',
columns=('id', 'name'),
sep=',' # 列分隔符
)
conn.commit()默认分隔符为制表符\t，CSV文件需指定sep=','。NULL值默认用NULL表示，可自定义：
cur.copy_from(f, 'test_user', columns=('id', 'name'), sep=',', null='NULL')copy_to()导出到文件：
with open('export.csv', 'w') as f:
cur.copy_to(
file=f,
table='test_user',
columns=('id', 'name'),
sep=','
)
conn.commit()copy_expert()灵活控制：copy_expert支持完整的COPY语句，适用于数据迁移、日志导出等场景。
copy_sql = """
COPY test_user(id, name)
TO STDOUT
WITH CSV HEADER DELIMITER AS ','
"""
with open('export_with_header.csv', 'w') as f:
cur.copy_expert(sql=copy_sql, file=f)五、大对象处理与动态SQL
5.1 BLOB/CLOB处理：金仓数据库支持二进制大对象和字符大对象，ksycopg2可处理这些类型。
import ksycopg2
conn = ksycopg2.connect(
database='TEST', user='SYSTEM',
password='123456', host='127.0.0.1', port='54321'
)
cur = conn.cursor()
# 建表
cur.execute('DROP TABLE IF EXISTS test_lob')
cur.execute('''
CREATE TABLE test_lob (
id INTEGER,
b BLOB,
c CLOB,
ba BYTEA
)
''')
# 准备测试数据
ba_data = bytearray("中文测试bytearray", "UTF8")
b_data = bytes('中文测试bytes' * 2, "UTF8")
str_data = '中文str' * 4
# 插入
cur.execute(
"INSERT INTO test_lob VALUES (%s, %s, %s, %s)",
(1, ba_data, ba_data, ba_data)
)
cur.execute(
"INSERT INTO test_lob VALUES (%s, %s, %s, %s)",
(2, b_data, b_data, b_data)
)
cur.execute(
"INSERT INTO test_lob VALUES (%s, %s, %s, %s)",
(3, str_data, str_data, str_data)
)
conn.commit()
# 读取
cur.execute("SELECT id, b, c, ba FROM test_lob")
rows = cur.fetchall()
for row in rows:
for cell in row:
# bytea 类型返回 memoryview，需要转换
if isinstance(cell, memoryview):
print(cell[:].tobytes().decode('UTF8'), end=" ")
else:
print(cell, end=" ")
print()
cur.close()
conn.close()⚠️ 注意事项：
bytea类型在Python 3中返回memoryview，需用tobytes()转换- 大对象频繁读写性能较差
- 超大文件建议存储路径而非内容
5.2 动态SQL生成：ksycopg2.sql模块解决标识符不能直接参数化的问题。
from ksycopg2 import sql
# 错误写法：标识符不能参数化
cur.execute("SELECT * FROM %s WHERE id = %s", ('test_user', 1))
# 正确写法：用 sql 模块
cur.execute(
sql.SQL("SELECT * FROM {} WHERE id = %s").format(sql.Identifier('test_user')),
(1,)
)table_name = 'test_user'
id_column = 'user_id'
name_column = 'user_name'
query = sql.SQL("SELECT {id}, {name} FROM {table} WHERE {id} = %s").format(
id=sql.Identifier(id_column),
name=sql.Identifier(name_column),
table=sql.Identifier(table_name)
)
cur.execute(query, (100,))from ksycopg2 import sql
# 把 Python 值直接转成 SQL 字面量
value = sql.Literal("It's a test")
cur.execute(
sql.SQL("SELECT {}").format(value)
)
# 生成: SELECT 'It''s a test'ids = [1, 2, 3, 4, 5]
placeholders = ','.join(['%s'] * len(ids))
cur.execute(
f"SELECT * FROM test_user WHERE id IN ({placeholders})",
ids
)六、错误处理与完整示例
6.1 异常处理：
import ksycopg2
from ksycopg2 import OperationalError, IntegrityError
try:
conn = ksycopg2.connect(database='TEST', user='SYSTEM', password='123456')
cur = conn.cursor()
cur.execute("INSERT INTO test_user (id, name) VALUES (%s, %s)", (1, '张三'))
conn.commit()
except IntegrityError as e:
print(f"违反唯一约束: {e}")
conn.rollback()
except OperationalError as e:
print(f"连接或操作错误: {e}")
except Exception as e:
print(f"其他错误: {e}")
conn.rollback()
finally:
cur.close()
conn.close()常见异常类型：
IntegrityError：违反约束（主键重复、外键不存在）OperationalError：连接断开、语法错误ProgrammingError：表或列不存在
6.2 完整示例：从连接到批量插入：
import ksycopg2
from ksycopg2 import extras
def main():
conn = None
try:
conn = ksycopg2.connect(
database='TEST',
user='SYSTEM',
password='123456',
host='127.0.0.1',
port='54321'
)
conn.autocommit = False
cur = conn.cursor()
# 建表
cur.execute('DROP TABLE IF EXISTS test_user')
cur.execute('''
CREATE TABLE test_user (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# 批量插入
data = [(i, f'user_{i}') for i in range(1, 1001)]
extras.execute_values(
cur,
"INSERT INTO test_user (id, name) VALUES %s",
data,
page_size=200
)
# 查询
cur.execute("SELECT COUNT(*) FROM test_user")
count = cur.fetchone()[0]
print(f"插入了 {count} 条数据")
conn.commit()
cur.close()
except Exception as e:
print(f"操作失败: {e}")
if conn:
conn.rollback()
finally:
if conn:
conn.close()
if __name__ == "__main__":
main()[AFFILIATE_SLOT_1]
七、总结
本文覆盖了Python操作金仓数据库的核心场景：SQL执行与结果集获取（execute()、fetchone/fetchmany/fetchall）、防注入参数传递（%s占位符）、批量操作优化（execute_values性能最佳）、COPY命令（最快导入导出方式）、大对象与动态SQL（sql模块）。
结合上篇的连接管理与高可用配置，这套方案已能满足多数企业级应用需求。建议在实际项目中根据数据量选择合适方案，并关注金仓官方文档获取最新驱动。
[AFFILIATE_SLOT_2]
浙公网安备 33010602011771号