Use pyodbc to count and list tables, columns, indexes, etc

[DFS]

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.