Python - automating MySQL index: passing parameter

Question

Hi Everybody,

I have a function with a new improved version of the code for automatic table indexing:

def update_tableIndex(self,tableName):
    getIndexMySQLQuery = """SELECT numberID
    FROM %s;""" % (tableName,)

    updateIndexMySQLQuery = """UPDATE %s 
    SET numberID=%s WHERE numberID=%s;""" % (tableName,)

    updateIndex=1
    self.cursorMySQL.execute(getIndexMySQLQuery)
    for row in self.cursorMySQL:
        indexID = row[0]
        self.cursorMySQL.execute(updateIndexMySQLQuery,(updateIndex,indexID))
        updateIndex+=1

While the query 'getIndexMySQLQuery' works fine with this syntax, the other one 'updateIndexMySQLQuery' doesn't work.

Any hints or suggestion how to get that fixed?

All comments and suggestions are highly appreciated.

Answer 1

Second one doesn't work, because you are using three placeholders inside the query string and provide only one variable for interpolation.

updateIndexMySQLQuery = """UPDATE %s 
SET numberID=%%s WHERE numberID=%%s;""" % (tableName,)

This way the string formatting mechanism doesn't expect you to provide 3 values, as the percent signs are "escaped" (shame on me for the first version of the answer).

Answer 2

Use %s to replace the table name in the beginning, but use a question mark to create a parameter replacement.

updateIndexMySQLQuery = """UPDATE %s 
SET numberID=? WHERE numberID=?;""" % (tableName,)
...
    self.cursorMySQL.execute(updateIndexMySQLQuery,(updateIndex,indexID))

Answer 3

Hi Guys.. thanks for the input. I just re-did the whole function. Here is how it's working and looks now:

def update_tableIndex(self,tableName,indexName):

    getIndexMySQLQuery = """SELECT %s
    FROM %s;""" % (indexName,tableName,)

    updateIndex=1
    self.cursorMySQL.execute(getIndexMySQLQuery)
    for row in self.cursorMySQL:
        indexID = row[0]

        updateIndexMySQLQuery = """UPDATE %s 
        SET %s=%s WHERE 
        %s=%s;""" % (tableName,
                     indexName,updateIndex,
                     indexName,indexID)

        self.cursorMySQL.execute(updateIndexMySQLQuery)
        updateIndex+=1

So, the only thing to do is to inform the column name and the table name as parameters. It allows to re-use the code for all other tables in the database.

Hope this can be useful for others too.