python - 读取mdb文件

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