Get table name for field in database result in Python (PostgreSQL)

Question

I'm trying to get table name for field in result set that I got from database (Python, Postgres). There is a function in PHP to get table name for field, I used it and it works so I know it can be done (in PHP). I'm looking for similar function in Python.

pg_field_table() function in PHP gets results and field number and "returns the name of the table that field belongs to". That is exactly what I need, but in Python.

Simple exaple - create tables, insert rows, select data:

CREATE TABLE table_a (
    id INT,
    name VARCHAR(10)
);

CREATE TABLE table_b (
    id INT,
    name VARCHAR(10)
);

INSERT INTO table_a (id, name) VALUES (1, 'hello');
INSERT INTO table_b (id, name) VALUES (1, 'world');

When using psycopg2 or sqlalchemy I got right data and right field names but without information about table name.

import psycopg2

query = '''
    SELECT *
    FROM table_a A
    LEFT JOIN table_b B
        ON A.id = B.id
'''

con = psycopg2.connect('dbname=testdb user=postgres password=postgres')
cur = con.cursor()
cur.execute(query)

data = cur.fetchall()
print('fields', [desc[0] for desc in cur.description])
print('data', data)

The example above prints field names. The output is:

fields ['id', 'name', 'id', 'name']
data [(1, 'hello', 1, 'world')]

I know that there is cursor.description, but it does not contain table name, just the field name.

What I need - some way to retrieve table names for fields in result set when using raw SQL to query data.

EDIT 1: I need to know if "hello" came from "table_a" or "table_b", both fields are named same ("name"). Without information about table name you can't tell in which table the value is.

EDIT 2: I know that there are some workarounds like SQL aliases: SELECT table_a.name AS name1, table_b.name AS name2 but I'm really asking how to retrieve table name from result set.

EDIT 3: I'm looking for solution that allows me to write any raw SQL query, sometimes SELECT *, sometimes SELECT A.id, B.id ... and after executing that query I will get field names and table names for fields in the result set.

You can get some info from explain (verbose) <your query> at the Output node. For your example the output could be Output: A.id, A.name, B.id, B.name. Use format option to get the info in the desired format: explain (verbose, format json) ... for example. But how about more complex queries like select greatest(A.name, B.name) as name from ... where the source table can be various for each row? — Abelisto
– Abelisto, Commented Aug 8, 2016 at 19:14

Clodoaldo Neto · Accepted Answer · 2016-08-08 16:47:12Z

1

It is necessary to query the pg_attribute catalog for the table qualified column names:

query = '''
    select
        string_agg(format(
            '%%1$s.%%2$s as "%%1$s.%%2$s"',
            attrelid::regclass, attname
        ) , ', ')
    from pg_attribute
    where attrelid = any (%s::regclass[]) and attnum > 0 and not attisdropped
'''

cursor.execute(query, ([t for t in ('a','b')],))
select_list = cursor.fetchone()[0]

query = '''
    select {}
    from a left join b on a.id = b.id
'''.format(select_list)

print cursor.mogrify(query)
cursor.execute(query)
print [desc[0] for desc in cursor.description]

Output:

    select a.id as "a.id", a.name as "a.name", b.id as "b.id", b.name as "b.name"
    from a left join b on a.id = b.id

['a.id', 'a.name', 'b.id', 'b.name']

edited Aug 8, 2016 at 16:47

answered Aug 8, 2016 at 16:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dwich Over a year ago

Thank you for your reply. Your solution is built for SELECT *. I'm looking for solution that allows me to run any raw SQL query where I specify what columns I want to retrieve (after SELECT). Then after executing the query I expect to get only selected fields in the result set with field names and table names.

Clodoaldo Neto Over a year ago

@dwich select * is what your asked for

dwich Over a year ago

You are right, I used SELECT * in the example above. However I'm looking for solution that allows me to use any raw SQL - SELECT * or list fields I want to retrieve (and then find out from which table those field are). I updated the question (EDIT 3).

Collectives™ on Stack Overflow

Get table name for field in database result in Python (PostgreSQL)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related