SQL Server内存优化表通过SQLAlchemy建表报错及用户事务相关问题咨询

Microsoft SQL Server的内存优化表对DDL操作（CREATE/ALTER/DROP）有强制限制：这类操作不允许在显式声明的用户事务内部执行，只能在自动提交模式的独立事务下运行。你在SSMS中执行正常是因为SSMS默认开启自动提交模式，单条SQL执行完成后会自动提交事务，没有额外的显式事务包裹，因此符合内存表的DDL执行要求。

• SQL Server层面：指用户主动通过BEGIN TRANSACTION/COMMIT/ROLLBACK语句声明包裹的事务块，和数据库引擎自动创建的隐式事务、自动提交事务属于不同范畴。
• SQLAlchemy + pyodbc语境下：SQLAlchemy默认会为所有执行的SQL语句自动开启显式事务，哪怕你只执行单条CREATE语句，SQLAlchemy也会默认给它套一层BEGIN TRANSACTION块，这就刚好触发了内存表的DDL操作限制。

• 方案1：建表时单独使用自动提交连接
  执行建表语句时，单独拿到SQLAlchemy引擎的原生连接，临时开启自动提交配置后再执行建表SQL，不会影响后续数据导入的事务逻辑，示例代码如下：

  from sqlalchemy import create_engine
  import pandas as pd
  
  # 初始化引擎
  engine = create_engine("mssql+pyodbc://你的连接字符串")
  
  # 建表步骤单独用自动提交连接执行
  create_table_sql = "你调整好的内存表CREATE语句"
  with engine.connect().execution_options(autocommit=True) as conn:
      conn.execute(create_table_sql)
  
  # 建表完成后正常执行数据导入即可
  df.to_sql("目标表名", engine, if_exists="append", index=False)
  

• 方案2：全局开启引擎自动提交配置
  初始化SQLAlchemy引擎时直接设置全局自动提交参数，适合整个业务流程都不需要事务控制的场景：

  engine = create_engine(
      "mssql+pyodbc://你的连接字符串",
      connect_args={"autocommit": True}
  )
  # 后续直接执行建表、导数据操作即可
  

• 方案3：用pyodbc原生连接完成建表
  绕开SQLAlchemy的事务封装逻辑，直接用pyodbc原生连接执行建表语句，pyodbc默认开启自动提交模式，不会触发事务限制：

  import pyodbc
  
  conn = pyodbc.connect("你的ODBC连接字符串", autocommit=True)
  cursor = conn.cursor()
  cursor.execute("你调整好的内存表CREATE语句")
  cursor.close()
  conn.close()
  
  # 后续正常用SQLAlchemy执行df.to_sql导入数据即可
  

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