import pyodbc

driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
db_path = 'C:\\niPOD\\Database\\niPOD.accdb'
conn = pyodbc.connect('DRIVER={};DBQ={}'.format(driver,db_path))
cursor = conn.cursor()

"""
Explanation: First, we'll connect to the niPOD database. 
Note: if you have the 32 bit version of ACE installed, you must use a 32 bit python to do this. See http://stackoverflow.com/questions/25820698/how-do-i-import-an-accdb-file-into-python-and-use-the-data
End of explanation
"""


table_list = []
for row in cursor.tables():
    if 'SYSTEM' not in row[3]:
        table_list.append(row[2])

print 'Tables:'
print '  ' +'\n  '.join(table_list)

for table in table_list:
    cursor.execute("select * from [%s]" % table)
    columns = [column[0] for column in cursor.description]
    n = len(list(cursor))
    print "%s (%i rows)" % (table,n)
    print '  ' +'\n  '.join(columns)


"""
Explanation: Tada!
OK, now let's see what interesting tables we have.
End of explanation
"""


import pandas as pd
def get_df(table,cursor):
    cursor.execute("select * from [%s]" % table)
    columns = [column[0] for column in cursor.description]

    results = []
    for row in cursor.fetchall():
        results.append(dict(zip(columns, row)))

    return pd.DataFrame(results)

"""
Explanation: So a few of these are simply lookup tables. Many are empty. ProbeSpec, ProbePackage, and Mapping are the big ones. I'm guessing those have what we want.
Let's write up a little function to query them and drop the rows into a pandas dataframe.
End of explanation
"""


df = get_df('ProbeSpec',cursor)
print len(df)
df.head()

"""
Explanation: OK, let's see what's in one of these tables now.
End of explanation
"""


for table in table_list:
    df = get_df(table,cursor)
    if len(df)>0:
        df.to_csv('NiPOD-%s.csv' % table, 
                  encoding='utf-8',
                 index=False)

%ls *.csv

"""
Explanation: That looks like what we want. 1440 rows with DesignName as one of the columns and a bunch of data on the number of channels, shanks, etc.
Let's pull all of the non-empty tables down and drop them into CSV.
End of explanation
"""


