vishal bhatt vishal bhatt - 6 months ago 36
Python Question

saving the column names of a sql table as a list using pyodbc

I need the names of columns of the sql table in a list to be used in another complex python code Tried some of the hacks show in some older SO posts:

db_columns = cursor.columns(table='result', schema='dbo').fetchall()

[(u'Basis', u'dbo', u'result', u'TXN_KEY', -5, u'bigint identity', 19, 8, 0, 10, 0, None, None, -5, None, None, 1, 'NO', 0, 0, 0, 1, None, None, None, None, None, None, 63),(u'Basis', u'dbo', u'result', u'Send_Agent', 12, u'varchar', 9, 9, None, None, 1, None, None, 12, None, 9, 2, 'YES', 0, 0, 0, 0, None, None, None, None, None, None, 39),(u'Basis', u'dbo', u'result', u'Pay_Agent', 12, u'varchar', 9, 9, None, None, 1, None, None, 12, None, 9, 3, 'YES', 0, 0, 0, 0, None, None, None, None, None, None, 39)]


The above output seems garbled and has a lot of unnecessary info. All I need is column names of the result table and assign it to a list = ['TXN_KEY','Send_Agent','Pay_Agent']

for row in cursor.columns(table='result'):
x = row.column_name
x
Out[14]: u'Pay_Agent'


This output gives me only one of the column names.

Thanks in advance for the insights.

Answer

SELECT like this?

SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbo' and TABLE_NAME = 'YourTableName'