开发者

Select column names in a table using PyODBC

I'm writing a Python program that selects some data from a Microsoft Access mdb file using PyODBC.

I need to discover the column names of several different tables. In SQL Server, this can be accomplished by using a query like

SELECT c.name FROM sys.columns c, sys.tables t
WHERE c.object_id = t.开发者_StackOverflow社区object_id
AND t.name = tableName

But that query doesn't work in Access. With

SELECT MSysObjects.Name FROM MSysObjects
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name

I can get a list of non-linked table names, but MSysObject doesn't seem to contain a list of column names.

Is there a way to use SQL to grab the column names of a table in an Access database?


I was unable to find an SQL query to accomplish this. However, I did discover that PyODB has a cursor method that can return a list of columns

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


Python 3

To access by conlumn name

table_row = conn.execute(query)
for row in table_rows:
     print (row.nameColumn)

To access by conlumn index

table_row = conn.execute(query)
for row in table_rows:
     print (row[0])


I'm not sure about the limitations in querying that version of MS-Access, but a solution I've seen used in other similar situations is to SELECT * FROM table LIMIT = 0 (or 1 depending). You are then able to gather the returned column names from the result.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜