通过 pyodbc 连接access读取数据

pyodbc是一个开源的python模块，可以方便我们连接到ODBC数据库。

pyodbc 遵从Python DB API 2.0规范。

install pyodbc后即可通过python 连接 Microsoft SQL Server, Oracle, DB2, Microsoft Access, Sybase ASE, InterBase

下面的代码实现从access中读取数据并转成hdf

import pyodbcimport pandas as pdworkspace = 'C:/Users/...'# Connection function to use for accessdef Connection():    MDB = '/'.join([workspace,'Ying Hub Milk Data-part 2.accdb'])    DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'    return pyodbc.connect('DRIVER={};DBQ={}'.format(DRV,MDB))def get_milk_data():    conn = Connection()    cursor = conn.cursor()    sqlstring = 'select * from milk'    milk = list(cursor.execute(sqlstring))    id, milk, date, farm = zip(*milk)    milk = pd.DataFrame([id, milk, date, farm]).transpose()    milk.to_hdf('milk.hdf', key='milk_all')