Python script leaves the database in restoring mode

I am attempting to use python to automate some of our database restores. When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the same SQL query the database is left in the restoring mode.

Any help with what I'm doing wrong would be greatly appreciated.

import pyodbc

#Set these Variables
sourceserver = 'MySourceServerName'
destinationserver = 'MyDestinationServerName'
dbname = 'test'

sourceserverplusport = sourceserver + ',1433'
destinationserverplusport = destinationserver + ',1433'

def ExecSQL(serverplusport, dbnameIN, sql):
    try:
        connexecsql = pyodbc.connect('Driver={SQL Server};'
                                         'Server=' + serverplusport +';'
                                         'Database=' + dbnameIN + ';'
                                         'Trusted_Connection=yes;')

        #Set AutoCommit
        if connexecsql is not None:
           connexecsql.autocommit = True
       
        print(sql)
        cursor = connexecsql.cursor()
        cursor.execute(sql)
    finally:    
        cursor.commit
        cursor.close
        connexecsql.commit
        connexecsql.close

sqlcommand = r"""IF @@SERVERNAME = 'MyDestinationServerName'
RESTORE DATABASE test FROM DISK = '\\MyBackupPath\test.BAK' WITH FILE = 1, MOVE N'test' to N'X:\Database\MDF\test.mdf',  MOVE N'test_log' to N'Y:\Database\LDF\test_log.ldf',  NOUNLOAD,  REPLACE, RECOVERY, STATS = 5;"""

ExecSQL(destinationserverplusport, "master", sqlcommand)

If I add one more ExecSQL command at the end of the script to run RESTORE DATABASE test WITH RECOVERY; at the end returns the error:

The database cannot be recovered because the log was not restored."

Including the REPLACE with the RECOVERY removes the error but leaves the database in restoring still.

Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.

Eg

cursor.execute(sql)
while cursor.nextset():
    pass 
cursor.close()

StackExchange Database Administrators Q#274122, answer score: 5