Mar-29-2022, 01:23 PM
I have the below code which is intended to execute a SQL (MSSQL) stored procedure that both updates some file names in a SQL table as well as return those new file names to Python which in turn renames the files in a Windows network folder. The SQL stored procedure does the SQL update first and then returns the data to Python. The stored procedure works as expected when executed in SQL Management Studio. It does not work correctly when called from Python, somehow bypassing the SQL table update. It seemingly skips this update and then returns the new file names to Python and the rename of the files occurs as intended. There are no errors that I can see, it simply doesn't perform the update in SQL. I'm confused as to how this is even possible. Any help would be much appreciated.
import os import datetime from datetime import date import time import pyodbc # define directory for scanning today = date.today() folder = today.strftime("%m-%d-%y") directory = 'S:\\' + folder # establish SQL connection cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};" "Server=server;" "Database=db;" "Trusted_Connection=no;" "UID=uid;" "PWD=password;") # get image names and new names from SQL cursor = cnxn.cursor() cursor.execute('''EXECUTE [get_Rename] ?''',folder) data = cursor.fetchall() for data_out in data: old_name = directory + '\\' + data_out[0] new_name = directory + '\\' + data_out[1] try: os.rename(old_name, new_name) except FileNotFoundError as fnf_error: print(fnf_error)