Not getting all InfoMessage Events with Python and win32com

Question

I am currently trying to get the percentage complete messages that are returned by the InfoMessage event from ADO (and a SQL server) when running the BACKUP command. (See my previous question for more details).

I have managed to connect to the SQL server and issue it SQL commands, and event get events back. However when I execute the the BACKUP command the cmd.Execute method blocks until the backup is complete.

But during this time I will get a single InfoMessage event call (which will have a message like "1 Percent Complete") and after that I won't receive any more events.

I have tried this using a stored procedure, where the stored procedure prints 3 messages, and even here I will get the first message and nothing else.

I suspect that I need to call pythoncom.PumpWaitingMessages(), but because the cmd.Execute() call blocks I never get anything of any use.

Can anyone work out how to get more that just a single InfoMessage event.

Below is the code that I'm currently using:

import win32com
import pythoncom
import adodbapi
import time
import win32gui
from win32com.client import gencache
gencache.EnsureModule('{2A75196C-D9EB-4129-B803-931327F72D5C}', 0, 2, 8)

defaultNamedOptArg=pythoncom.Empty
defaultNamedNotOptArg=pythoncom.Empty
defaultUnnamedArg=pythoncom.Empty

global connected
connected = False

class events():
    def OnInfoMessage(self, pError, adStatus, pConnection):
        print 'Info Message'
        a = pError.QueryInterface(pythoncom.IID_IDispatch)
        a = win32com.client.Dispatch(a)
        print a.Description
        print a.Number
        print a.Source
        #print 'B', adStatus
        c = pConnection.QueryInterface(pythoncom.IID_IDispatch)
        c = win32com.client.Dispatch(c)
        print c.Errors.Count
        print c.Errors.Item(0).Description
        return 1

    def OnCommitTransComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass
    def OnWillExecute(self, Source=defaultNamedNotOptArg, CursorType=defaultNamedNotOptArg, LockType=defaultNamedNotOptArg, Options=defaultNamedNotOptArg
            , adStatus=defaultNamedNotOptArg, pCommand=defaultNamedNotOptArg, pRecordset=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Execute Event'
        return Source
    def OnDisconnect(self, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): 
        print 'Disconnected'
    def OnExecuteComplete(self, RecordsAffected=defaultNamedNotOptArg, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pCommand=defaultNamedNotOptArg
            , pRecordset=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Execute complete'
    def OnWillConnect(self, ConnectionString=defaultNamedNotOptArg, UserID=defaultNamedNotOptArg, Password=defaultNamedNotOptArg, Options=defaultNamedNotOptArg
            , adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'About to connect'
    def OnConnectComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):
        print 'Connected'
        global connected
        connected = True
    def OnBeginTransComplete(self, TransactionLevel=defaultNamedNotOptArg, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg):pass
    def OnRollbackTransComplete(self, pError=defaultNamedNotOptArg, adStatus=defaultNamedNotOptArg, pConnection=defaultNamedNotOptArg): pass

if __name__ == '__main__':

    pythoncom.CoInitialize()
    conn = win32com.client.DispatchWithEvents("ADODB.Connection", events)

    conn.ConnectionString = 'Data Source=HPDX2250RAAZ\\SQLEXPRESS; Provider=SQLOLEDB; Integrated Security=SSPI'
    conn.CommandTimeout = 30
    conn.CursorLocation = 2
    conn.Open(pythoncom.Empty,pythoncom.Empty,pythoncom.Empty,0x10)

    while not connected:
        #pythoncom.PumpWaitingMessages()
        win32gui.PumpWaitingMessages()
        time.sleep(0.1)

    conn.BeginTrans()
    conn.Errors.Clear()
    cmd=win32com.client.Dispatch("ADODB.Command")
    cmd.ActiveConnection=conn
    cmd.CommandTimeout = 30  #v2.1 Simons
    cmd.CommandText="EXECUTE [test].[dbo].[Test] "
    print 'Execute'
    cmd.Execute()

    pythoncom.PumpWaitingMessages()
    print 'Called'
    print ''
    print conn.Errors.Count
    conn.RollbackTrans()
    conn.Close()

Answer 1

I found a workaround that is compatible with pymssql and other drivers. I use the SQL from Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process? plus a background thread that each X seconds run that query. Now, for notification I use http://pydispatcher.sourceforge.net/ to get back the progress.

#This is rough extract from my actual code. Probably not work as is, but outline the idea
import dispatch #Decoupled send of messages, identical to django signals

def monitorBackup(self):
    return self.selectSql(SQL_MONITOR)

def backup(sql):
    con = self.getCon() #Get new connection, we are in another thread!
    con.execute_query("HERE THE BACKUP SQL")

result = threading.Thread(target=partial(backup, sql))

result.start()

while result.isAlive():
    time.sleep(5) # with the monitor SQL result, is possible to get a estimated time to complete and adjust this...
    rows = self.monitorBackup()

    if len(rows) > 0:
        percentage = rows[0].Percent

        self.send(
            msg="%d %%" % percentage,
            action="progress",
            progress=percentage
        )