The following function assigns a unique key (derived from a SQL table) to files so they will comply with a naming convention

def assign(fList, p):
    for i in fList:
        p  = 1
        lz = leadingZero(p)
        oldName = fileDirPath   fr'\{i}'
        if lz == 1:
            newName = fileDirPath   r'<prefix value>'   str(p)   '<suffix value>'
            print(newName)
        else:
            newName = fileDirPath   r'<prefix value>'   str(p)   '<suffix value>'
            print(newName)
    if leadingZero(p) == 1:
        sqlConnectWrite('0'   str(p))
    else:
        sqlConnectWrite(str(p))

In order to properly comply with the naming convention the key 'p' must always be 5 digits, and have a leading zero if the key value is less than 10,000. The following function sets an integer "lz" equal to 1 if a leading zero needs to be added, and 0 if it does not need to be added.

def leadingZero(num):
    lz = 0
    if num < 10000:
        lz = 1
    elif num >= 10000:
        lz = 0
    else:
        logging.error("Leading Zero Boolean: something has gone terribly wrong")
        print("ERROR: Invalid Integer Passed, please email <email>")
    return lz

The first function (def assign) then passes the last key assigned to the following function so that it can update the SQL table that stores the most recent key value, so we can keep track of what key values have been assigned

def sqlConnectWrite(pFinal):

    try:
        conn = pyodbc.connect('Driver={SQL Server};'
                              'Server=<server>;'
                              'Database=<database>;'
                              r'UID=<user>;'
                              'PWD=<pass>;'
                              'Trusted_Connection=yes;')
        cursor = conn.cursor()
        print("SQL Connection Successful!")
        print("Running Query....")
        print('SQL WRITE OPERATION INITIATED')
        print(pFinal)
        cursor.execute(f'UPDATE <SQL TABLE> SET [Last Used Number]  = {str(pFinal)}')
        conn.commit()
    except pyodbc.Error:
        logging.exception('SQL: Exception thrown in write process')
    finally:
        print('SQL Operations Successful')
        conn.close()

Despite my best efforts, when I update the SQL table, the p value seems to persistently revert back to an integer, which removes the leading zero (shown below). The SQL table value is an nchar(5) data type but I cannot seem to find a way to update the table such that the leading zero is retained. I cannot determine why this is the case.

SQL Table

CodePudding user response：

String values in an SQL expression need to be surrounded with single quotes. Otherwise, they are interpreted as integers, and integers don't have leading zeros.

        cursor.execute(f"UPDATE <SQL TABLE> SET [Last Used Number]  = '{pFinal:05d}'")

Quoting is vitally important in an SQL context. Even better would be to get in the habit of allowing your database connector to do the substitution:

        cursor.execute("UPDATE <SQL TABLE> SET [Last Used Number] = ?", (f"{pFinal:05d}",))

Assuming SQL Server uses ? for substitution. Some databases use %s.