如何使用pyodbc提交存储过程执行？Databricks中commit不生效

根因排查方向 #

• 你代码中注释的自动提交配置存在变量名错误：原有注释代码写的是conn1.autocommit=True，但你实际定义的连接变量是conn，即便是打开注释也不会生效
• 执行SQL语句过程中没有异常捕获逻辑，无法感知是否有权限不足、表/存储过程不存在、SQL语法错误等执行失败的情况
• 存储过程内部存在自行处理事务（如内置ROLLBACK、未提交的嵌套事务）的逻辑，导致外层commit无法生效
• 未验证单条SQL的执行效果，无法确认是SQL本身没有匹配到操作行，还是提交环节失效

解决步骤 #

1. 补全异常捕获逻辑，打印执行过程中的具体报错
2. 修正变量名错误，按需配置事务参数
3. 单独验证存储过程的执行效果：直接在Azure SQL侧执行exec db.SP_NAME '20211023'，确认可以正常修改数据
4. 执行每条SQL后打印受影响行数，确认SQL执行有效

修正后参考代码 #

import pyodbc

#### 连接Azure SQL
def db_connection():
  try:
    username = "starsusername"
    password = "password-db"
    server = "server-name"
    database_name = "db-name2"
    port = "db-port"
    conn=pyodbc.connect('Driver={ODBC Driver 17 for SQL server};SERVER=tcp:'+server+','+port+';DATABASE='+ database_name +';UID='+ username +';PWD='+ password)
    cursor=conn.cursor()
    return cursor, conn
  except Exception as e:
    print("连接AZURE SQL失败: \n"+str(e))
    return None, None

cursor, conn = db_connection()
if not conn or not cursor:
    exit()

try:
    # 若要测试自动提交模式可以打开下行注释
    # conn.autocommit = True
    # 执行删除
    del_count = cursor.execute("delete from db.table_name").rowcount
    print(f"删除操作受影响行数：{del_count}")
    # 执行插入
    insert_count = cursor.execute("insert into db.table_name(BUSINESS_DATE) values('2021-10-02')").rowcount
    print(f"插入操作受影响行数：{insert_count}")
    # 执行存储过程，提前加上SET NOCOUNT ON避免计数干扰
    cursor.execute("SET NOCOUNT ON; exec db.SP_NAME '20211023'")
    print("存储过程执行完成")
    # 提交事务
    conn.commit()
    print("事务提交成功")
except Exception as e:
    # 执行失败回滚
    conn.rollback()
    print(f"SQL执行失败，已回滚，错误信息：{str(e)}")
finally:
    conn.close()

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