import pandas as pd import re import psycopg2 from sqlalchemy import create_engine # 连接库账户信息 gongsi_engine = psycopg2.connect(dbname='kk_dw',user='*******',password='*****',host='***') # 从公司数据库读取需要的数据表 table_data = pd.read_sql(r"select * from pg_tables where schemaname = 'dwd';",con=gongsi_engine) table_data.head() data = pd.read_sql(r"""select * from information_schema.columns where table_schema='dwd' and table_name='dwd_store_other_in_storage_orders_detail'""",con=gongsi_engine) data[['table_name','column_name','udt_name']]
获取描述等
data = pd.read_sql(r"""select
a.attnum,
n.nspname,
c.relname,
a.attname as field,
t.typname as type,
a.attlen as length,
a.atttypmod as lengthvar,
a.attnotnull as notnull,
b.description as comment
from
pg_namespace n left join pg_class c
on n.oid = c.relnamespace
left join pg_attribute a
on 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
n.nspname = 'dwd' --table_schema
and c.relname = 'dwd_store_o2o_sales_detail' -- table_name
and a.attnum > 0
order by
a.attnum;""",con=gongsi_engine)
这样实现的功能,是我能批量获取数据库里面的表名与字段以及字段描述等信息。