Python - automating MySQL index: passing parameter

Question

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.

shylent · Accepted Answer · 2010-01-08 23:12:48Z

2

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).

edited Jan 8, 2010 at 23:12

answered Jan 8, 2010 at 23:05

shylent

10.1k6 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tor Valamo · Accepted Answer · 2010-01-08 23:09:17Z

0

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))

answered Jan 8, 2010 at 23:09

Tor Valamo

33.9k11 gold badges76 silver badges82 bronze badges

5 Comments

shylent Over a year ago

Do question mark placeholders even work with MySQLdb (I know they do in sqlite3)?

Tor Valamo Over a year ago

They work with mysql in other languages, so I don't see why not... It's a DBAPI2.0 requirement isn't it?

shylent Over a year ago

I think, in this case this is a specification of the programming-language-specific db-api, rather than something, that has to do with MySQL itself (I could be wrong). I know, this is a part of python's database api standard, however I know, that MySQLdb's documentation only mentions percent-style placeholders. I can't test it right now so I'll leave it to you :)

Tor Valamo Over a year ago

Looked at dbapi, and it says the library decides what to use, but need to specify it in module constant 'format'. So I'm guessing %s style for mysqldb then.

bobince Over a year ago

Sadly, DBAPI requires a parameter type, but it doesn't specify which. You have to look at the module's paramstyle to determine which. Some DBAPI modules use the qmark style. MySQLdb uses the format style, which unfortunately results in exactly this confusion between parameterisation and string formatting. It's a mess.

ThreaderSlash · Accepted Answer · 2010-01-08 23:24:27Z

0

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.

answered Jan 8, 2010 at 23:24

ThreaderSlash

1,3133 gold badges27 silver badges44 bronze badges

1 Comment

shylent Over a year ago

No, this is not useful at all and, in fact, this is extremely harmful. You should not concatenate the parameter values into the query, but leave it to the database API. How do you ensure, that the parameters you are passing are quoted and escaped properly? In short, do not do it like that.

Collectives™ on Stack Overflow

Python - automating MySQL index: passing parameter

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related