Python连接postgresql并以字典返回查询结果

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)]

二、列表嵌套字典返回

1. 百度到的办法（报错包里没有extras变量，估计是python版本或者包版本问题，有兴趣的可自行研究）

connsql = psycopg2.connect(database=dbname, user=user, password=psw, host=host, port=dbport)
cur = connsql.cursor(cursor_factory=psycopg2.extras.RealDictCursor) 

2. 以上办法未果，自己实现（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}]