读取大型.mdb文件后遇打印、类型识别及存储问题求助

已完成操作 #

成功读取包含500万行、12列的大型.mdb文件，使用的代码如下：

conn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/..../Data.mdb')
curs = conn.cursor()

SQL = 'SELECT * FROM Data;'# insert your query here
curs.execute(SQL)

rows = curs.fetchall()

curs.close()
conn.close()

遇到的问题 #

• 无法打印rows，Visual Studio弹出提示“the window cannot open”，推测是内存不足导致
• 偶尔能打印时，执行print(rows.dtype)报错：this object has no attribute 'dtype'，而获取数据类型是保存为.parquet文件的必要步骤
• 尝试保存为CSV文件时触发Unicode编码错误，错误信息如下：

csv_writer = csv.writer(open('mytable.csv', 'w'), lineterminator='\n')

for row in rows:
    csv_writer.writerow(row)

---------------------------------------------------------------------------
UnicodeEncodeError                        Traceback (most recent call last)
Cell In [89], line 5
      2 csv_writer = csv.writer(open('mytable.csv', 'w'), lineterminator='\n')
      4 for row in rows:
----> 5     csv_writer.writerow(row)
File C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.10_3.10.2288.0_x64__qbz5n2kfra8p0\lib\encodings\cp1252.py:19, in IncrementalEncoder.encode(self, input, final)
     18 def encode(self, input, final=False):
---> 19     return codecs.charmap_encode(input,self.errors,encoding_table)[0]

UnicodeEncodeError: 'charmap' codec can't encode characters in position 15-17: character maps to <undefined>

1. 解决内存不足问题 #

fetchall()会一次性把所有数据加载到内存，500万行数据量太大直接撑爆内存。改用批量读取的方式，避免一次性加载全部数据：

conn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/..../Data.mdb')
curs = conn.cursor()

SQL = 'SELECT * FROM Data;'
curs.execute(SQL)

# 每次读取10000行，可根据自身内存情况调整批量大小
batch_size = 10000
while True:
    rows_batch = curs.fetchmany(batch_size)
    if not rows_batch:
        break
    # 在这里处理当前批次的数据，比如转成DataFrame后写入Parquet

curs.close()
conn.close()

2. 获取数据类型并保存为Parquet文件 #

pyodbc返回的rows是元组列表，本身没有dtype属性，必须转成Pandas DataFrame才能处理数据类型和保存为Parquet。结合批量读取逻辑，避免内存溢出：

import pandas as pd
import pyodbc

conn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/..../Data.mdb')
curs = conn.cursor()

SQL = 'SELECT * FROM Data;'
curs.execute(SQL)

# 先获取表的列名
columns = [desc[0] for desc in curs.description]

batch_size = 10000
first_write = True
while True:
    rows_batch = curs.fetchmany(batch_size)
    if not rows_batch:
        break
    # 把当前批次转成DataFrame
    df_batch = pd.DataFrame(rows_batch, columns=columns)
    # 写入Parquet，第一批次创建文件，后续批次追加
    df_batch.to_parquet('data.parquet', mode='w' if first_write else 'append', engine='pyarrow')
    first_write = False

curs.close()
conn.close()

• 提前安装依赖：pip install pandas pyarrow
• 转成DataFrame后，可通过df_batch.dtypes查看各列数据类型，满足Parquet保存的需求

3. 解决CSV保存的Unicode错误（可选） #

如果仍需要保存CSV，打开文件时指定UTF-8编码即可：

import csv
import pyodbc

conn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Users/..../Data.mdb')
curs = conn.cursor()

SQL = 'SELECT * FROM Data;'
curs.execute(SQL)
columns = [desc[0] for desc in curs.description]

# 打开文件时指定encoding='utf-8'
with open('mytable.csv', 'w', encoding='utf-8', newline='') as f:
    csv_writer = csv.writer(f, lineterminator='\n')
    csv_writer.writerow(columns)  # 写入列名
    # 逐行读取写入
    while True:
        row = curs.fetchone()
        if not row:
            break
        csv_writer.writerow(row)

curs.close()
conn.close()

内容的提问来源于stack exchange，提问作者bravopapa