pandas read mdb

1#### Using Pandas read_sql
2import pyodbc
3import pandas as pd
4
5# set up some constants
6# "." pwd + "\\" to overcome error 
7MDB = '.\\Akbar.mdb'
8DRV = '{Microsoft Access Driver (*.mdb, *.accdb)};'
9
10## connect to db
11con = pyodbc.connect('DRIVER={};DBQ={};'.format(DRV,MDB))
12
13## read with pandas 
14df = pd.read_sql('SELECT * FROM sheet2;',con)
15df.head(5)

1#### using pyodbc and csv module
2import csv, pyodbc
3
4# set up some constants
5MDB = 'c:/path/to/my.mdb'
6DRV = '{Microsoft Access Driver (*.mdb)}'
7PWD = 'pw'
8
9# connect to db
10con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
11cur = con.cursor()
12
13# run a query and get the results 
14SQL = 'SELECT * FROM mytable;' # your query goes here
15rows = cur.execute(SQL).fetchall()
16cur.close()
17con.close()
18
19# you could change the mode from 'w' to 'a' (append) for any subsequent queries
20with open('mytable.csv', 'w') as fou:
21    csv_writer = csv.writer(fou) # default field-delimiter is ","
22    csv_writer.writerows(rows)