%s %(sth)s and ?, escaping mysqldb variables

http://stackoverflow.com/questions/775296/python-mysql-with-variables

You have a few options available. You'll want to get comfortable with python's string iterpolation. Which is a term you might have more success searching for in the future when you want to know stuff like this.

Better for queries:

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

Considering you probably have all of your data in an object or dictionary already, the second format will suit you better. Also it sucks to have to count "%s" appearances in a string when you have to come back and update this method in a year :)

############

Beware of using string interpolation for sql queries, since it won't escape the input parameters correctly and will leave your application open to sql injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

Incorrect, with security issues:

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

Correct:

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

It adds to the confusion that the modifiers used to bind parameters in a sql statement varies between different DB API implementations and that the mysql client library uses printf-style syntax instead of the more commonly accepted ? marker (used by for example python-sqlite).

2

To be more specific ... the cursor.execute() method takes an optional argument which contains values to be quoted and interpolated into the SQL template/statement. This is NOT done with a simple% operator! cursor.execute(some_sql, some_params) is NOT the same ascursor.execute(some_sql % some_params)

The Python DB-API specifies that any compliant driver/module must provide a .paramstyle attribute which can be any of 'qmark', 'numeric', 'named', 'format', or 'pyformat' ... so that one could, in theory, adapt your SQL query strings to the supported form through introspection and a little munging. This should still be safer than trying to quote and interpolate values into your SQL strings yourself.

I was particularly amused to read Warning Never, never, NEVER use Python string ... interpolation ... Not even at gunpoint. in the PsycoPG docs.

x

You could use triple quotes and raw string format

self.cur.execute(r"""SELECT dist FROM distance ... """,...)

y

When I remember it right, then you don't need to manually encode your unicode strings. The mysqldb module will do this for you.

And the mysqldb module uses %s as parameters instead of ?. This is the reason for the error in your first example.