Getting Table and Column names in PyOdbc

Question

I'd like to retrieve the fully referenced column name from a PyOdbc Cursor. For example, say I have 2 simple tables:

• Table_1(Id, < some other fields >)
• Table_2(Id, < some other fields >)

and I want to retrieve the joined data

select * from Table_1 t1, Table2 t2 where t1.Id = t2.Id

using pyodbc, like this:

query = 'select * from Table_1 t1, Table2 t2 where t1.Id = t2.Id'

import pyodbc
conn_string = '<removed>'
connection =  pyodbc.connect(conn_string)

cursor = connection.cursor()cursor.execute(query)

I then want to get the column names:

for row in cursor.description:
    print row[0]

BUT if I do this I'll get Id twice which I don't want. Ideally I could get t1.Id and t2.Id in the output.

Some of the solutions I've thought of (and why I don't really want to implement them):

1. re-name the columns in the query - in my real-world use case there are dozens of tables, some with dozens of rows that are changed far too often
2. parse my query and automate my SQL query generation (basically checking the query for tables, using the cursor.tables function to get the columns and then replacing the select * with a set of named columns) - If I have too I'll do this, but it seems like overkill for a testing harness

Is there a better way? Any advice would be appreciated.

Answer 1

The PyOdbc docs offer

# columns in table x
for row in cursor.columns(table='x'):
    print(row.column_name)

www.PyOdbc wiki The API docs are useful

Answer 2

Here's how I do it.

import pyodbc
connection = pyodbc.connect('DSN=vertica_standby', UID='my_user', PWD='my_password', ansi=True)
cursor = connection.cursor()
for row in cursor.columns(table='table_name_in_your_database'):
    print(row.column_name)

You have to have your DSN (data source name) set up via two files. odbc.ini and odbcinst.ini