Reading SSMS output message using pyodbc

Question

I have a list of MS SQL CREATE scripts and I am trying to automate the process of executing each of these scripts. As this CREATE scripts does not result any records, I would want my automation script to return the SSMS output message that looks like: 'Command executed successfully'

Can I read this output message using pyodbc? Here is the sample code that I use to execute the script:

    conn = pyodbc.connect(r'DRIVER={SQL Server};SERVER=%s;Trusted_Connection=True;'% (db_conn_string))
    cursor = conn.cursor()
    cursor.execute(query)

Gord Thompson · Accepted Answer · 2021-09-15T18:49:23.907

2

It is not really necessary to capture the "Command executed successfully" message because an exception will occur if the command is not executed successfully.

So your Python code can just .execute the statement, catch any exception that occurs, and proceed accordingly, e.g.,

try:
    crsr.execute("DROP TABLE dbo.nonexistent")
    print("INFO: DROP TABLE succeeded.")
except pyodbc.ProgrammingError as err:
    error_code = err.args[0]
    if error_code == "42S02":  # [table] does not exist or you do not have permission
        print("INFO: DROP TABLE did not succeed.")
    else:
        raise  # re-raise unexpected exception

edited Sep 15 '21 at 18:49

answered Nov 11 '16 at 14:58

Gord Thompson

116,920
32
215
418

That helps a lot ... Are there any packages available that captures console message, if pyodbc is not capable to do so? – Neelan Nov 12 '16 at 02:38
@.Naran - I'd think that IronPython might be able to do it via the .NET `SqlClient` assembly, perhaps using the technique described [here](http://stackoverflow.com/q/1880471/2144390). – Gord Thompson Nov 12 '16 at 02:51

score 1 · Answer 2 · answered Mar 02 '20 at 22:51

1

cursor.rowcount

Adding the above line to your code will return the row count for the last executed query.

answered Mar 02 '20 at 22:51

Gwen Au

859
9
10

Reading SSMS output message using pyodbc

2 Answers2