information_schema详解
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)
这样实现的功能,是我能批量获取数据库里面的表名与字段以及字段描述等信息。