Re: 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.


--
https://mail.python.org/mailman/listinfo/python-list