Python | access转csv | 读取access mdb文件以csv格式重新存储

模块

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)