批量导出PostgreSQL数据库表结构信息到excel中

需求：导出数据库中表结构，写入到excel表中，一个表结构写入到一个sheet页中
使用python的pands读取数据，并写入到excel中

import pandas as pd
import sqlalchemy as sqla

# 获取连接
def getCon():
    return sqla.create_engine("postgresql+psycopg2://xxx:xxx@172.16.x.x/eimos_business")

# 拼接SQL
def getSQL(table_name):
    return '''
            SELECT  a.attnum 序号, 
                relname 表英文名称,
                cast(obj_description(relfilenode,'pg_class') AS varchar) 表中文名称,
                a.attname AS 数据项英文名称, 
                b.description AS 数据项中文名称,
                t.typname AS 字段类型, 
                a.atttypmod AS 字段长度
            FROM pg_class c 
            LEFT JOIN pg_attribute a  ON a.attnum > 0 AND a.attrelid = c.oid
            LEFT JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid
            LEFT JOIN pg_type t ON a.atttypid = t.oid
            WHERE c.relname = '{0}'
        '''.format(table_name)

if __name__ == '__main__':
    db = getCon()
    # 获取所有需要的表
    sql = ''' select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment 
             from pg_class 
             where relkind = 'r' and  relname ~ '^ltc'
    '''
    # 执行
    df = pd.read_sql_query(sql, con=db)
    # 读取写入目标文件
    writer = pd.ExcelWriter('D:\工作文档\数据.xlsx')
    for index, v in df.iterrows():
        # 获取表结构信息
        result = pd.read_sql_query(getSQL(v['tabname']), con=db)
        # 追加到EXCEL中，表名有注释使用表明作为sheet页名，否则使用表明
        if v['comment'] is None:
            result.to_excel(writer, sheet_name=v['tabname'], index=False)
        else:
            result.to_excel(writer, sheet_name=v['comment'], index=False)

    # 关闭文件，提交
    writer.save()