Why is this Python code exiting before pyodbc SQL commit is finished

Question

I have some Python code that I am executing from a C# solution. Everything is working well except for this one thing, which makes no sense to me given that Python is supposed to be synchronous by default.

The file that is the entry point for the Python code ends as soon as a function call is complete. It is basically this:

if __name__ == '__main__':
    # CODE OMITTED - setting up variables using command-line arguments received

    try:
        my_function(var1, var2, var3)
    except RuntimeError as codedErr:
        print('ERROR: ' + str(codedErr))
        raise SystemExit(0)

I know the error handling is a bit strange, but my C# solution is reading the output stream to see the execution result.

Anyway, my_function does a lot of stuff, but it ends like this:

    if my_cool_list is None or len(my_cool_list) < len(original_objects):
        logger.warning('Full execution was not possible with the selected parameters.')
        print('Full execution was not possible with the selected parameters.')
        data.set_execution_result(object_id, "NP")
    else:
        # CODE OMITTED - 3 different SQL-related function calls in the 'data' file

        data.set_execution_result(object_id, "O")        

    data.set_execution_progress_pct(99)
    return

Finally, the set_execution_progress_pct function in the file data.py looks like this:

def set_execution_progress_pct(progress_pct):
    logger.debug(f'Attempting to update progress of execution with ID={cfg.executionRequestId} to {progress_pct}%.')
    connString = get_connection_string()
    conn = pyodbc.connect(connString)
    cursor = conn.cursor()

    sql = f"UPDATE ExecutionRequest SET Progress='{progress_pct}' WHERE ID='{cfg.executionRequestId}'"
    result = cursor.execute(sql)
    cursor.commit()

    if result.rowcount != 1:
        logger.error('Failed to update progress on Execution Requests table.')
    else:
        logger.info('Progress updated.')

    cursor.close()
    conn.close()

    return result

In case it matters, the C# code that executes the Python code looks like this:

using (Process process = Process.Start(start) ?? throw new InvalidOperationException("Process failed to start"))
{
    output = process.StandardOutput.ReadToEnd() ?? string.Empty;
    error = process.StandardError.ReadToEnd() ?? string.Empty;

    process.WaitForExit();
}

The specific issue I'm having is this: Shortly after the Python execution is finished, the C# code does a small amount of other stuff and then attempts to change two values on the Execution Request on the database. Specifically, it fetches the Execution Request by ID, changes the Status to 'Complete' and the Progress to 100 (as in, 100%), then saves the object to the DB. The problem is, I am ending up with my Execution Request being "Complete" but having Progress = 99. In other words, the final SQL call from the Python code completes after the Python code has finished running and after the C# code has proceeded through several lines of code and even written to the same DB table itself.

How is this even possible?

I can fix this by putting time.sleep(5) just before the return in my_function, but that feels like a silly band-aid fix. Shouldn't cursor.commit() finish executing well before Python indicates that it's all finished?

Answer 1

It turns out that this issue was being caused by some peculiarity of the C# code. I still have not gotten to the bottom of it, but the point is that the issue has nothing to do with the Python code.

If I use the UI of my C# program to create a new object on the 'Execution Request' table on the database that then gets picked up and executed (which eventually runs the Python code), I do not have this problem. Progress ends up at 100.

The issue only arises if I access the database directly through SSMS and change a value in an existing row of the 'Execution Request' table so that it gets picked up and executed. Every time I do that, Progress ends up at 99.

Since there is no actual use case that involves direct database manipulation like this, I do not need to determine the reason. When a user uses my program, the results are as expected.

(To be clear, modifying an existing DB item in this way is just a shortcut I take while developing, as it is faster than creating a new object through the UI - or even in SSMS, due to foreign-key and uniqueness restrictions. I do not need to resolve this issue because it will not affect users at all. Furthermore, I will not have the time or resources to determine the cause and post the explanation here, as the cause may lie deep within a fairly large, multi-author C# solution and therefore not be worth the time needed to investigate.)

Answer 2

cursor.commit() is a function and look at this questions answer

maybe have a check to see if its done before ending like a while loop that does nothing until it sees its done (just put time.sleep(0) in there to avoid crashing)