Postgres Python实现查询数据库表的所有字段信息

关键代码

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": "名称"
    }
]