官方API说明
Changing Query Results with Rowfactories
cx_Oracle “rowfactories” are methods called for each row that is retrieved from the database. The Cursor.rowfactory() method is called with the tuple that would normally be returned from the database. The method can convert the tuple to a different value and return it to the application in place of the tuple.
译文
使用Rowfactories更改查询结果
cx_Oracle“rowfactories”是为从数据库检索的每一行调用的方法。rowfactory()方法与通常从数据库返回的元组一起调用。该方法可以将元组转换为不同的值,并将其返回给应用程序来代替元组
参考代码
# coding=utf-8
import cx_Oracle
# 连接数据库,下面括号里内容根据自己实际情况填写
conn = cx_Oracle.connect(用户名, 密码, '数据库IP:1521/实例名')
# 使用cursor()方法获取操作游标
cur = conn.cursor()
# 使用execute方法执行SQL语句
cur.execute(sql)
# 使用Rowfactories更改查询结果
columns = [col[0] for col in cur.description]
cur.rowfactory = lambda *args: dict(zip(columns, args))
# fetchall()一次取完所有结果,fetchone()一次取一行结果
data = cur.fetchall()
# 关闭游标
cur.close()
# 断开数据库连接
conn.close()
# 打印查询结果
print(data)
查询结果如下
[
{'PATIENT_ID': '00000068', 'VISIT_NO': '0000158', 'VISIT_CLASS_CODE': 'PTC1', 'VISIT_CLASS_DESC': '自费', 'VISIT_DATETIME': datetime.datetime(2018, 9, 19, 10, 6, 29), 'CREATE_HOSPITAL_CODE': 'CSYY', 'DISCHARGE_DATETIME': None},
{'PATIENT_ID': '00000068', 'VISIT_NO': '0000141', 'VISIT_CLASS_CODE': 'PTC1', 'VISIT_CLASS_DESC': '自费', 'VISIT_DATETIME': datetime.datetime(2018, 9, 17, 14, 20, 36), 'CREATE_HOSPITAL_CODE': 'CSYY', 'DISCHARGE_DATETIME': None},
{'PATIENT_ID': '00000068', 'VISIT_NO': 'P0001679', 'VISIT_CLASS_CODE': 'PTC3', 'VISIT_CLASS_DESC': '普通医保', 'VISIT_DATETIME': datetime.datetime(2019, 8, 6, 18, 39, 29), 'CREATE_HOSPITAL_CODE': 'CSYY', 'DISCHARGE_DATETIME': None}
]
拓展
拿到如上数据后,可以通过遍历列表,再获取内层字典的key和value值,从而进行相应的格式处理,与json/xml的测试类型数据进行校验
参考官方文档URL
https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html#changing-query-results-with-rowfactories