关键代码
def transSqlData(field_list: list, data: list = None):
"""
将给定表头的数据以及表格内容数据转换成字典形式
:param field_list: 表头数据, 例如 ["name", "age"]
:param data: 表格内容数据, 例如 [("Jack", 20), ("Mark", 30)]
:return: [{"name": "Jack", "age": 20}, {"name": "Mark", "age": 30}]
"""
if not data:
return []
result = []
for item in data:
obj = map(lambda x, y: (x, transDataType(y)), field_list, item)
result.append(dict(obj))
return result
def getConnection(sql_cfg: dict, conn=None, times=3) -> psycopg2._psycopg.connection:
"""
获取数据库连接对象
:param sql_cfg: 数据库配置信息
:param conn: 数据库连接对象
:param times: 最大重连次数
:return:
"""
try:
while times >= 0:
if conn is not None and conn.closed != 1:
return conn
conn = psycopg2.connect(**sql_cfg)
times -= 1
raise Exception(f"重连次数超过{times}次")
except Exception as ex:
raise Exception(f"获取数据库连接对象失败, {ex}")
def executeSqlRet(conn, sql: str, *args):
"""
执行sql语句
:param conn: psycopg2连接对象
:param sql: sql语句
:param args: 格式化参数
:return:
"""
if not conn:
raise Exception("数据库连接失败, 请检查数据库数据.")
cursor = conn.cursor()
cursor.execute(sql, args)
conn.commit()
result = cursor.fetchall()
cursor.close()
return result
def queryAllFieldInfo(conn, table_name: str):
"""
查询数据库表的所有字段信息
:param conn: psycopg2连接对象
:param table_name: 表名
:return:
"""
sql = """
select i.column_name, i.data_type, i.character_maximum_length, i.column_default, i.is_nullable, d.description
from information_schema.columns i, pg_class c, pg_attribute a
left join pg_description d on d.objoid=a.attrelid and d.objsubid=a.attnum
where i.table_name=%s and c.relname=i.table_name and a.attrelid=c.oid and a.attname=i.column_name
"""
result = executeSqlRet(conn, sql, table_name)
field_list = ["field_name", "data_type", "data_type_len", "default", "is_nullable", "desc"]
return transSqlData(field_list, result)
db_info = {
"database": "VideoInfo",
"user": "postgres",
"password": "postgres",
"host": "localhost"
}
data = queryAllFieldInfo(getConnection(db_info), "tb_down")
print(json.dumps( data, indent=4))
测试结果
[
{
"field_name": "id",
"data_type": "integer",
"data_type_len": null,
"default": "nextval('tb_down_id_seq'::regclass)",
"is_nullable": "NO",
"desc": "id"
},
{
"field_name": "name",
"data_type": "character varying",
"data_type_len": 200,
"default": "111",
"is_nullable": "NO",
"desc": "名称"
}
]