Devin Binger - 2008-06-17

I'm new to mysqldb and DB-API both, and I'm having some problems with parameters on cursor.execute(). Evidently, mysqldb wraps those in singlequotes automatically after sanitizing them. My problem is that I'm trying to build the database dynamically (it's being fed from another database and I'd really like it to be agnostic about what columns exist in that db) and I can't pass column names as parameters because they get singlequoted and treated as literals. I've ended up working in string formatting as a workaround, but that really sticks in my craw (it's not that I'm worried about SQL injection attacks in the column names from the other DB, exactly, but it's the principle of the thing, and the maintainability).

Right now my code looks something like this:

def buildTable:

    querystring = #do some string formatting to get the column names and types into a SQL query

    cursor.execute(querystring)

def insertRows:

    querystring = #do some string formatting to get the column names in and add the right "%(columnname)s" parameter hooks to the          query

    cursor.execute(querystring, parameters) #where parameters is a dict with column names keying values for a particular object

I'm not really sure if that's how it's meant to be, but it doesn't feel quite right. It seems like I ought to be able to do something more like this:

queryhandles = #a string constructed by checking the length of the list of columns and building a string with that many "%s"s in it

def buildTable:

    querystring = "CREATE TABLE name (%s) etc" % queryhandles

    cursor.execute(querystring, columnnames)

I can't find anything in PEP 249 that even mentions enclosing parameters at all, so I'm not sure if that's a de facto part of the spec based on usage in the major implementations, or a peculiarity of mysqldb, but it's weird enough that I'm thinking either it's not supposed to work this way, or there must be a cleaner way to handle it than what I'm doing.