欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-02 12:24:15
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)]

二、列表嵌套字典返回

  1. 百度到的办法(报错包里没有extras变量,估计是python版本或者包版本问题,有兴趣的可自行研究)
connsql = psycopg2.connect(database=dbname, user=user, password=psw, host=host, port=dbport)
cur = connsql.cursor(cursor_factory=psycopg2.extras.RealDictCursor) 
  1. 以上办法未果,自己实现(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

相关标签: postgresql python