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')