通过 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 pyodbc
import pandas as pd
workspace = 'C:/Users/...'


# Connection function to use for access
def 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')