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):

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
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.

A duplicate of this question? stackoverflow.com/questions/12704305/… — Leonid
– Leonid, Commented Dec 17, 2013 at 21:58
Some trouble with your desire to get prefixed column names - they actually don't exist as of any point where you'd be reading these query results, and you could never access those values by your proposed identifier (e.g., t1.Id would not be the name of the output column in SQL under any platform I'm aware of). 7+ years later, I'd recommend re-evaluating whether this is really what you want, or if perhaps you can simplify this by just aliasing each column to something like t1_id, prefixing with the table alias. — bsplosion
– bsplosion, Commented May 1, 2020 at 11:59

lot · Accepted Answer · 2022-05-03 19:24:32Z

29

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

edited May 3, 2022 at 19:24

lot

1296 bronze badges

answered May 2, 2013 at 0:21

Geordie Jon

2993 silver badges4 bronze badges

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

4 Comments

Brad Over a year ago

I rejected this solution in my initial question. Please read proposed solution #2

ryanjdillon Over a year ago

I found this looking for a method to do this with pypyodbc, for which this method does not work. Any suggestions would be great.

Quint Over a year ago

@ryanjdillon: Try row["column_name"] instead. The syntax is slightly different for pypyodbc. As per the docs, where pyodbc uses row.attribute syntax, pypyodbc uses row["attribute"] syntax.

Sakeer Over a year ago

@Geordie Jon : You saved my day

Guillaume Jacquenot · Accepted Answer · 2021-04-13 19:17:30Z

5

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

edited Apr 13, 2021 at 19:17

Guillaume Jacquenot

11.8k6 gold badges45 silver badges50 bronze badges

answered Sep 16, 2017 at 0:04

Russell Lego

4905 silver badges6 bronze badges

Collectives™ on Stack Overflow

Getting Table and Column names in PyOdbc

2 Answers 2

The PyOdbc docs offer

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

The PyOdbc docs offer

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related