SQLAlchemy批量导入CSV数据后会话自动回滚问题求助

我帮你分析下问题根源，再给出两种可行的解决方案：

问题核心：事务管理不匹配 #

你虽然创建了SQLAlchemy的Session，但实际执行BULK INSERT的是直接从Engine获取的连接——这两个对象的事务是完全独立的。调用session.commit()根本不会提交引擎连接里的未完成事务，当你关闭引擎连接时，数据库会自动回滚所有未提交的操作，这就是数据消失的原因。

方案一：直接管理引擎连接的事务 #

既然你是用Engine.connect()获取的连接来执行SQL，那就要直接对这个连接对象进行事务提交：

import sqlalchemy
import os
import glob
from sqlalchemy.orm import sessionmaker

# 创建SQL引擎（注意：连接字符串里的table_name应该是数据库名，这里修正下）
sql_engine = sqlalchemy.create_engine(
    'mssql+pyodbc://server_name/database_name?driver=SQL Server&Trusted_Connection=yes'
)
# 获取独立的连接对象
conn = sql_engine.connect()

# 遍历CSV批量插入
cd = os.getcwd()
all_files = glob.glob(cd + "/*.csv")
for file in all_files:
    # 注意：如果文件路径有特殊字符，建议用引号包裹更稳妥
    qry = f"BULK INSERT [database_name].[schema].[table_name] FROM '{file}' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"
    print(qry)
    conn.execute(qry)

# 提交当前连接的所有事务
conn.commit()
# 关闭连接
conn.close()

方案二：用Session统一管理事务（更符合SQLAlchemy最佳实践） #

如果想通过Session来管理事务，就要通过session.execute()来执行SQL语句，这样所有操作都会纳入Session的事务管理：

import sqlalchemy
import os
import glob
from sqlalchemy.orm import sessionmaker

# 创建SQL引擎
sql_engine = sqlalchemy.create_engine(
    'mssql+pyodbc://server_name/database_name?driver=SQL Server&Trusted_Connection=yes'
)
# 绑定引擎创建Session类并实例化
Session = sessionmaker(bind=sql_engine)
session = Session()

try:
    # 遍历CSV批量插入
    cd = os.getcwd()
    all_files = glob.glob(cd + "/*.csv")
    for file in all_files:
        qry = f"BULK INSERT [database_name].[schema].[table_name] FROM '{file}' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"
        print(qry)
        session.execute(qry)
    
    # 提交Session的所有事务
    session.commit()
except Exception as e:
    # 出错时回滚事务
    session.rollback()
    raise e
finally:
    # 无论成功失败都关闭Session
    session.close()

额外注意事项 #

1. 你的原连接字符串里把数据库名写成了table_name，这是笔误，一定要修正为实际的数据库名称，否则连接可能会出问题。
2. 如果CSV文件路径包含空格、特殊字符，直接拼接SQL语句容易出现语法错误，建议确保路径用单引号正确包裹，或者考虑使用SQLAlchemy的文本查询参数化（不过BULK INSERT的文件路径参数化需要特殊处理，具体可以参考MSSQL的文档）。

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