模块
import pypyodbc
import pandas
由于下载pypyodbc1.3.4和1.3.5版本报错~只能下载1.3.2版本
pip install pypyodbc==1.3.2
pip install pandas
代码案例
注意存储的格式是utf-8-sig
utf-8和utf-8-sig的区别(CSV文件 乱码)_人生苦短,我用python-CSDN博客
import pypyodbc
import pandas
path = r'xxx.mdb' # 数据库文件
table = 'admintbl'
savePath = r'xxx.csv' # r''表示非转义字符
def getAllColumnName(filePath, tableName):
"""
输出表的字段名
:param filePath: mdb文件路径
:param tableName: mdb表名
:return: 返回一个存储所有字段名的list
"""
pypyodbc.lowercase = False # 是否将字段名转为小写
conn = pypyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + filePath + ";Uid=;Pwd=;")
cursor = conn.cursor()
sql = "SELECT TOP 1 * FROM " + tableName
cursor.execute(sql)
list = []
for col in cursor.description:
list.append(col[0])
return list
def getAllRows(filePath, tableName):
"""
返回遍历的所有行数据
:param filePath: mdb文件路径
:param tableName: mdb表名
:return: 返回pypyodbc.Cursor类型
"""
pypyodbc.lowercase = False # 是否将字段名转为小写
conn = pypyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + filePath + ";Uid=;Pwd=;")
cursor = conn.cursor()
sql = "SELECT * FROM " + tableName
params = cursor.execute(sql)
print(type(params))
return params
def save2csv(columnName,list,savePath):
"""
按照字段名columnName,数据list存储在savePath路径上
:param columnName:字段名
:param list:数据
:param savePath:csv存储路径
"""
pandas.DataFrame(columns=columnName,data=list).to_csv(savePath,encoding ="utf_8_sig")
columnName = getAllColumnName(path,table)
dataList =getAllRows(path,table)
save2csv(columnName,dataList,savePath)