Python log SQL queries - IndexError: Replacement index 1 out of range for positional args tuple-CodePudding

I'm trying to set up my python script to log SQL queries executed.

I have this function for executing queries, which can accept values if passing a query with '?' placeholders:

def sqlQuery(type, query, values=None):
    dbCursor = dbCRM.cursor()
    if values != None:
        dbCursor.execute(query, values)
    else:
        dbCursor.execute(query)
    logging.debug('SQL query executed ({})'.format(query.replace("?", "{}").format(values)))

    if type == 'select':    
        data = []
        while True:
            row = dbCursor.fetchone()
            if row:
                data.append(tuple(row))
            else:
                break
        dbCursor.close()
        return data
    dbCRM.commit()

The logger.debug line is meant to log the SQL query by replacing the ?'s with {} and then using .format to fill in the values.

This is where the function is called. The two values are a string returned by an API request, and an integer returned by a different SQL query.

query = "UPDATE Company SET comp_bigchange_contactid = ? WHERE comp_companyid = ?"
sqlQuery('update',query, (apiResult.get('Result'), compID))

When trying to run this I get this error:

IndexError: Replacement index 1 out of range for positional args tuple

I am assuming there is a better way of logging this kind of SQL query.

CodePudding user response：

You need to unpack the values tuple in the second call to .format

More info on Unpacking in python here: What does the star and doublestar operator mean in a function call?

Your logging line should look like this:

logging.debug('SQL query executed ({})'.format(query.replace("?", "{}").format(*values)))

Note the .format(*values) at the very end.