python-3.x - Unable to read column types from amazon redshift using psycopg2

Question

I'm trying to access the types of columns in a table in redshift using psycopg2.

I'm doing this by running a simple query on pg_table_def like as follows:

SELECT * FROM pg_table_def;

This returns the traceback:

psycopg2.NotSupportedError: Column "schemaname" has unsupported type "name"

So it seems like the types of the columns that store schema (and other similar information on further queries) are not supported by psycopg2.

Has anyone run into this issue or a similar one and is aware of a workaround? My primary goal in this is to be able to return the types of columns in the table. For the purposes of what I'm doing, I can't use another postgresql adapter.

Using:

python- 3.6.2

psycopg2- 2.7.4

pandas- 0.17.1

标签： python-3.xtypesamazon-redshiftpsycopg2

Answer 1

您可以执行以下操作，并将结果返回给调用服务。

cur.execute("select * from pg_table_def where tablename='sales'")

results = cur.fetchall()
for row in results:
    print ("ColumnNanme=>"+row[2] +",DataType=>"+row[3]+",encoding=>"+row[4])  

不确定异常，如果所有权限都很好，那么它应该可以正常工作，打印如下所示。

ColumnNanme=>salesid,DataType=>integer,encoding=>lzo
ColumnNanme=>commission,DataType=>numeric(8,2),encoding=>lzo
ColumnNanme=>saledate,DataType=>date,encoding=>lzo
ColumnNanme=>description,DataType=>character varying(255),encoding=>lzo