I need to get the fields attributes from a query, like in this question:How to get column attributes query from table name using PostgreSQL? but for a query, is there a way of doing this?
1 Answer
Assuming you're using psycopg2 as your database driver, then the cursor.description field is what you want:
import pprint
import psycopg2
conn = psycopg2.connect('');
curs = conn.cursor()
curs.execute("SELECT 1 as col1, 2 as col2, 'text' as colblah");
pprint.pprint(curs.description)
produces:
(Column(name='col1', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
Column(name='col2', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
Column(name='colblah', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None))
The type codes are PostgreSQL's internal object IDs.
For more detail see the psycopg2 manual, which explains how to turn the type oids into type names, among other things.
psycopg2database driver?