从redshift获取DB tables blueprint,比如从redshift获取“describe table_name”命令,从python获取DB2

2021-04-11 14:06:17 发布

您现在位置:Python中文网/ 问答频道 /正文

我希望使用python代码获取数据,就像我们对describe [tableName] statement所做的那样。我想在Redshift和DB2上这样做。在

我尝试使用Pandas和cursor来实现这一点,我尝试了以下命令块:

  1. "set search_path to SCHEMA; select * from pg_table_def where schemaname = 'schema' and LOWER(tablename) = 'TableName';

  2. describe schema.tableName

  3. select column_name, data_type, character_manimum_length from information_schema.columns where table_schema = 'Schema' and table_name = 'TableName';

  4. \d or \d+

一。在

import psycopg2
import numpy as np
import pandas as pd   



con=psycopg2.connect(dbname= 'DBNAME', host="Host-Link",
    port= '5439', user= 'username', password= 'password')
    print(con)

cur = con.cursor()
query  = "set search_path to Schema; select * from pg_table_def where schemaname = 'Schema' and LOWER(tablename) = 'TableName';"
cur.execute(query)
temp = cur.fetchall()
print(temp)

data_frame = pd.read_sql("set search_path to Schema; select * from pg_table_def where schemaname = 'Schema' and LOWER(tablename) = 'TableName';", con)
print(data_frame)

con.close()

我希望输出如下:

^{pr2}$