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

Question

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.

SitiSchu · Accepted Answer · 2022-09-15T09:22:21.677

The second format expects two arguments since your query has two ? to replace but you only pass one argument. 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.

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

1 Answers1