13

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.

Brad
  • 1,247
  • 1
  • 8
  • 16
  • A duplicate of this question? http://stackoverflow.com/questions/12704305/return-column-names-from-pyodbc-execute-statement – Leonid Dec 17 '13 at 21:58
  • 1
    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 May 01 '20 at 11:59

3 Answers3

20

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

Guillaume Jacquenot
  • 9,076
  • 5
  • 38
  • 47
Geordie Jon
  • 209
  • 2
  • 4
  • I rejected this solution in my initial question. Please read proposed solution #2 – Brad May 21 '13 at 22:29
  • 1
    I found this looking for a method to do this with `pypyodbc`, for which this method does not work. Any suggestions would be great. – ryanjdillon Apr 23 '15 at 17:03
  • 2
    @ryanjdillon: Try `row["column_name"]` instead. The syntax is slightly different for pypyodbc. As per the [docs](https://code.google.com/p/pypyodbc/wiki/Compatibility_with_pyodbc), where pyodbc uses `row.attribute` syntax, pypyodbc uses `row["attribute"]` syntax. – Quint May 28 '15 at 13:05
  • @Geordie Jon : You saved my day – Sakeer Sep 17 '18 at 07:19
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

Guillaume Jacquenot
  • 9,076
  • 5
  • 38
  • 47
Russell Lego
  • 141
  • 1
  • 4
-4

It doesn't seem to be possible to do what I want without writing a decent amount of code to wrap it up. None of the other answers actually answered the question of returning different column names by the table they originate from in some relatively automatic fashion.

Brad
  • 1,247
  • 1
  • 8
  • 16
  • 8
    This probably could've been left a comment instead of an accepted answer. This 'answer' offers no valuable information. – Anonymous Oct 01 '18 at 01:16