Use pyodbc to count and list tables, columns, indexes, etc
DFS
nospam at dfs.com
Fri Apr 1 00:07:45 EDT 2016
On 3/31/2016 11:44 PM, DFS wrote:
> ================================================================
> import pyodbc
>
> dbName = "D:\test_data.mdb"
> conn = pyodbc.connect('DRIVER={Microsoft Access Driver
> (*.mdb)};DBQ='+dbName)
> cursor = conn.cursor()
>
> #COUNT TABLES, LIST COLUMNS
> tblCount = 0
> for rows in cursor.tables():
> if rows.table_type == "TABLE": #LOCAL TABLES ONLY
> tblCount += 1
> print rows.table_name
> for fld in cursor.columns(rows.table_name):
> print(fld.table_name, fld.column_name)
>
> print tblCount,"tables"
> ================================================================
>
> Problem is, the 'for rows' loop executes only once if the 'for fld' loop
> is in place. So even if I have 50 tables, the output is like:
>
> DATA_TYPES
> (u'DATA_TYPES', u'FLD_TEXT', -9, u'VARCHAR')
> (u'DATA_TYPES', u'FLD_MEMO', -10, u'LONGCHAR')
> (u'DATA_TYPES', u'FLD_NBR_BYTE', -6, u'BYTE')
> 1 tables
>
> And no errors are thrown.
>
> If I comment out the 2 'for fld' lines, it counts and lists all 50
> tables correctly.
>
> Any ideas?
>
> Thanks!
Never mind! I discovered I just needed a 2nd cursor object for the columns.
-----------------------------------------------------------
cursor1 = conn.cursor()
cursor2 = conn.cursor()
tblCount = 0
for rows in cursor1.tables():
if rows.table_type == "TABLE":
tblCount += 1
print rows.table_name
for fld in cursor2.columns(rows.table_name):
print(fld.table_name, fld.column_name)
-----------------------------------------------------------
Works splendiferously.
More information about the Python-list
mailing list