Flask+SQLAlchemy实现Excel数据导入MySQL的问题求助

问题场景 #

使用最新版Python Flask、openpyxl及MySQL，通过SQLAlchemy将Excel数据存入MySQL表时遇到两个核心问题：

1. 从Excel读取的数据为元组，但db.session.bulk_insert_mappings()仅支持模型类和字典列表，不兼容元组
2. 读取Excel日期单元格时触发错误：'datetime.datetime' object is not subscriptable

相关代码 #

视图函数代码

@app.route('/', methods=['GET', 'POST'])
@login_required
def view_stocks():
    if request.method == 'POST':
        uploaded_file = request.files['file']
        print("file name")
        print(uploaded_file)
        print(app.config['UPLOAD_PATH'])
        if uploaded_file.filename != '':
            filename = secure_filename(uploaded_file.filename)
            file_ext = os.path.splitext(filename)[1]
            if file_ext not in app.config['UPLOAD_EXTENSIONS']:
                flash(f'Invalid file extension {file_ext}!', category='danger')
                return render_template('stocks.html')
            print(os.path.join(app.config['UPLOAD_PATH']))
            uploaded_file.save(os.path.join(app.config['UPLOAD_PATH'], filename))
            wb = load_workbook(filename=os.path.join(app.config['UPLOAD_PATH']) + filename)
            sheet_object = wb['cashbook']
            cashbook_list = []
            for i, row in enumerate(sheet_object.iter_rows()):
                if i == 0:
                    continue
                cashbook_list.append(tuple([cell.value for cell in row]))
            db.session.bulk_insert_mappings().values(Cashbook, cashbook_list)
            db.session.commit()

Flask SQLAlchemy模型

class Cashbook(db.Model, UserMixin):
    cashbook_no = db.Column(db.Integer(), primary_key=True, autoincrement=True)
    cashbook_date = db.Column(Date, nullable=False)
    nature_of_expenses = db.Column(db.String(length=100), nullable=False)
    details = db.Column(db.String(length=100), nullable=False)
    payments = db.Column(db.String(length=100), nullable=True)
    receipts = db.Column(db.String(length=100), nullable=True)
    closing_balance = db.Column(db.String(length=100), nullable=True)

    def __init__(self, **kwargs):
        super(Cashbook, self).__init__(**kwargs)

Excel表结构 #

第一行为表头，列顺序对应：日期、费用性质、详情、支出、收入、余额

解决方案 #

问题1：元组转字典适配bulk_insert_mappings #

bulk_insert_mappings()的正确调用方式是传入模型类和字典列表，需将Excel行的元组转换为与模型字段对应的字典：

修改Excel读取逻辑：

from datetime import datetime

# 定义模型字段列表，顺序与Excel列严格对应
field_names = ['cashbook_date', 'nature_of_expenses', 'details', 'payments', 'receipts', 'closing_balance']
cashbook_list = []

# 使用values_only=True直接获取单元格值，提升效率
for i, row in enumerate(sheet_object.iter_rows(values_only=True)):
    if i == 0:
        continue
    # 元组转字典：将字段名与行值一一映射
    cashbook_dict = dict(zip(field_names, row))
    # 处理日期类型转换
    if isinstance(cashbook_dict['cashbook_date'], datetime):
        cashbook_dict['cashbook_date'] = cashbook_dict['cashbook_date'].date()
    cashbook_list.append(cashbook_dict)

# 修正bulk_insert_mappings调用方式
db.session.bulk_insert_mappings(Cashbook, cashbook_list)
db.session.commit()

同时修复原代码中的路径拼接错误：

# 错误写法
wb = load_workbook(filename=os.path.join(app.config['UPLOAD_PATH']) + filename)
# 正确写法
wb = load_workbook(filename=os.path.join(app.config['UPLOAD_PATH'], filename))

问题2：日期类型不兼容处理 #

openpyxl读取日期单元格会返回datetime.datetime对象，而模型中cashbook_date定义为Date类型，需将datetime转为date对象（上述代码已包含此处理），避免类型不匹配错误。

备选方案：直接创建模型对象批量插入 #

如果不想使用bulk_insert_mappings，可以直接生成Cashbook对象后批量添加：

cashbook_objects = []
for i, row in enumerate(sheet_object.iter_rows(values_only=True)):
    if i == 0:
        continue
    date_val, nature, details, payments, receipts, balance = row
    # 日期转换
    if isinstance(date_val, datetime):
        date_val = date_val.date()
    # 创建模型对象
    cashbook = Cashbook(
        cashbook_date=date_val,
        nature_of_expenses=nature,
        details=details,
        payments=payments,
        receipts=receipts,
        closing_balance=balance
    )
    cashbook_objects.append(cashbook)

# 批量添加到会话
db.session.add_all(cashbook_objects)
db.session.commit()

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