Python连接postgresql并以字典返回查询结果
程序员文章站
2024-01-02 11:52:22
Python连接postgresql并以字典返回查询结果一、列表嵌套元组返回import psycopg2# 基本查询,以列表嵌套元组返回[(1, 2, 3), (2, 3, 4), ...]def execute(sql): conn = psycopg2.connect(database=db, user=user, password=password, host=host, port=port) cur = conn.cursor() try: cur...
Python连接postgresql并以字典返回查询结果
一、列表嵌套元组返回
import psycopg2
# 基本查询,以列表嵌套元组返回[(1, 2, 3), (2, 3, 4), ...]
def execute(sql):
conn = psycopg2.connect(database=db, user=user, password=password, host=host, port=port)
cur = conn.cursor()
try:
cur.execute(sql)
rows = cur.fetchall()
finally:
cur.close()
conn.close()
return rows
测试用例:
h_table = "hr_department"
# execute和下例execute_dict在文件a中,此处将文件a引入并别名cr,调用函数与变量
res = cr.execute("select * from %s order by id desc limit 3" % h_table)
print(res)
返回如下:
[(2855, '管理部门', '管理部门', True, 59, None, None, None, None, None, 10149, datetime.datetime(2020, 11, 17, 10, 29, 29, 471786), 10149, datetime.datetime(2020, 11, 17, 10, 32, 1, 956590), 'GL01', 1, None, True, 32468, True, None, False, 0),
(2854, '张部门', '张部门', True, 44, None, None, None, None, None, 10151, datetime.datetime(2020, 11, 12, 15, 47, 6, 326161), 10151, datetime.datetime(2020, 11, 12, 15, 47, 6, 326161), 'Z01', 1, None, False, None, True, None, False, 0),
(2853, '测试部门', '测试部门', True, 2, None, None, None, None, None, 10206, datetime.datetime(2020, 11, 5, 17, 58, 24, 24950), 10206, datetime.datetime(2020, 11, 5, 18, 0, 54, 814418), 'TEST01', 14, None, True, 32317, True, None, False, 0)]
二、列表嵌套字典返回
- 百度到的办法(报错包里没有extras变量,估计是python版本或者包版本问题,有兴趣的可自行研究)
connsql = psycopg2.connect(database=dbname, user=user, password=psw, host=host, port=dbport)
cur = connsql.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
- 以上办法未果,自己实现(zip函数,建立在已知行名的情况下,行名可通过数据库表查得,具体视系统而定)
def execute_dict(col, sql):
conn = psycopg2.connect(database=db, user=user, password=password, host=host, port=port)
cur = conn.cursor()
try:
cur.execute(sql)
rows = cur.fetchall()
finally:
cur.close()
conn.close()
return [dict(zip(col, row)) for row in rows]
测试用例:
h_table = "hr_department"
h_column_res = cr.execute(cr.column_sql.format(model_name=h_table.replace('_', '.')))
h_columns = []
select_sql = "select "
from_sql = " from " + h_table
for column in h_column_res:
select_sql += column[0] + ', '
h_columns.append(column[0])
select_sql = select_sql[:-2]
res = cr.execute_dict(h_columns, select_sql + from_sql + " limit 3 ")
print(res)
返回结果:
[{'id': 730, 'comment': None, 'note': None, 'complete_name': '科室A', 'active': True, 'company_id': 1, 'manager_id': None, 'color': None, 'create_uid': 2, 'create_date': datetime.datetime(2020, 2, 12, 10, 1, 18, 41664), 'write_uid': 10148, 'write_date': datetime.datetime(2020, 6, 22, 16, 38, 58, 101475), 'cost_center_id': 1335, 'charge_manager_id': None, 'prepare_people': 0, 'name': '科室A', 'code': 'DEPT1', 'parent_id': 727},
{'id': 2823, 'comment': None, 'note': None, 'complete_name': '科室B', 'active': False, 'company_id': 1, 'manager_id': None, 'color': None, 'create_uid': 2, 'create_date': datetime.datetime(2020, 2, 17, 11, 51, 8, 361662), 'write_uid': 2, 'write_date': datetime.datetime(2020, 2, 17, 11, 52, 38, 44401), 'cost_center_id': 221, 'charge_manager_id': None, 'prepare_people': 0, 'name': '科室B', 'code': 'DEPT2', 'parent_id': None},
{'id': 2822, 'comment': None, 'note': None, 'complete_name': '科室C', 'active': False, 'company_id': 1, 'manager_id': None, 'color': None, 'create_uid': 2, 'create_date': datetime.datetime(2020, 2, 17, 11, 45, 17, 352213), 'write_uid': 2, 'write_date': datetime.datetime(2020, 2, 17, 11, 45, 47, 652841), 'cost_center_id': 1048, 'charge_manager_id': None, 'prepare_people': 0, 'name': '科室C', 'code': 'DEPT3', 'parent_id': None}]
本文地址:https://blog.csdn.net/Fitz_GuangChen/article/details/110917165