|
1. 安装pyodbc
pip3 install pyodbc -i https://pypi.tuna.tsinghua.edu.cn/simple
2. 安装accessdatabaseengine
https://www.microsoft.com/zh-cn/download/details.aspx?id=54920
安装完成后可以在ODBC数据源管理程序查看是否安装成功
3. 连接测试
import pyodbc
mdb_file = './test.mdb'
driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
conn = pyodbc.connect(f'Driver={driver};DBQ={mdb_file}')
cur = conn.cursor()
# 查看table
for table_name in cur.tables(tableType='TABLE'):
print(table_name.table_name)
# 执行sql
sql = 'select * from test_table'
cur.execute(sql)
# row = cur.fetchone()
rows = cur.fetchall()
cur.close()
conn.close()
# mdb转xlsx
import pyodbc
import pandas as pd
mdb_file = './test.mdb'
driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
conn = pyodbc.connect(f'Driver={driver};DBQ={mdb_file}')
cur = conn.cursor()
sql = 'select * from test_table'
data = pd.read_sql(sql, conn)
data.to_excel('test.xlsx', index=None)
cur.close()
conn.close()
|