无法在交易中恢复;自动加入正在打开

Question

我正在将sqlalchemy与pyodbc一起使用mssql" .bak"文件。我已经遵循了以前有关进行交易的建议，但似乎并没有改变任何事情。任何帮助将不胜感激。

from urllib.parse import quote_plus
from sqlalchemy import create_engine
params = quote_plus("Driver={SQL Server Native Client 11.0};"
                    "Server=ComputerSQLEXPRESS;"
                    "Database=master;"
                    "Trusted_Connection=yes;")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.raw_connection()
db_path = r"C:\Path\to\OutputDB.bak"
move_path = r"C:\Path\to\backup\db.mdf"
move_log_path = r"C:\Path\to\backup\db_Log.ldf"
sql_cmd = f"""
    RESTORE DATABASE [db]
    FROM DISK = N'{db_path}'
    WITH FILE = 1,
    MOVE N'db'
    TO N'{move_path}',
    MOVE N'test_log'
    TO N'{move_log_path}',
    RECOVERY,
    NOUNLOAD,
    REPLACE,
    STATS = 5
"""

connection.autocommit = True
cursor = connection.cursor()
cursor.execute(sql_cmd)
while cursor.nextset():
    pass
connection.autocommit = False

我收到以下错误消息：

programMingmingerror ：（'42000'，'[42000] [Microsoft] [SQL Server本地客户端11.0] [SQL Server] [SQL Server]无法在交易中执行备份或还原操作。（3021）（SQLEXECECDIRECTW）; [42000]; [42000] [[42000] [[42000] [[42000] [[42000] [[42000] [[42000] [[42000] [[42000]Microsoft] [SQL Server本地客户端11.0] [SQL Server]还原数据库异常终止。（3013）'）

Answer 1

我通过将connect_args={'autocommit': True}传递给create_engine来解决此问题。cursor.execute(sql_cmd).execution_options(autocommit=True)或connection.autocommit = True似乎都没有工作。

from urllib.parse import quote_plus
from sqlalchemy import create_engine
params = quote_plus("Driver={SQL Server Native Client 11.0};"
                    "Server=ComputerSQLEXPRESS;"
                    "Database=master;"
                    "Trusted_Connection=yes;")
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, connect_args={'autocommit': True})
connection = engine.raw_connection()
db_path = r"C:\Path\to\OutputDB.bak"
move_path = r"C:\Path\to\backup\db.mdf"
move_log_path = r"C:\Path\to\backup\db_Log.ldf"
sql_cmd = f"""
    RESTORE DATABASE [db]
    FROM DISK = N'{db_path}'
    WITH FILE = 1,
    MOVE N'db'
    TO N'{move_path}',
    MOVE N'test_log'
    TO N'{move_log_path}',
    RECOVERY,
    NOUNLOAD,
    REPLACE,
    STATS = 5
"""
cursor = connection.cursor()
cursor.execute(sql_cmd)
while cursor.nextset():
    pass