Does Python support MySQL prepared statements?

Question

I worked on a PHP project earlier where prepared statements made the SELECT queries 20% faster.

I'm wondering if it works on Python? I can't seem to find anything that specifically says it does or does NOT.

Answer 1

Even if it doesn't, you may use the SQL Interface, documented here

Answer 2

Most languages provide a way to do generic parameterized statements, Python is no different. When a parameterized query is used databases that support preparing statements will automatically do so.

In python a parameterized query looks like this:

cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

The specific style of parameterization may be different depending on your driver, you can import your db module and then do a print yourmodule.paramstyle.

From PEP-249:

paramstyle

       String constant stating the type of parameter marker
       formatting expected by the interface. Possible values are
       [2]:

           'qmark'         Question mark style, 
                           e.g. '...WHERE name=?'
           'numeric'       Numeric, positional style, 
                           e.g. '...WHERE name=:1'
           'named'         Named style, 
                           e.g. '...WHERE name=:name'
           'format'        ANSI C printf format codes, 
                           e.g. '...WHERE name=%s'
           'pyformat'      Python extended format codes, 
                           e.g. '...WHERE name=%(name)s'

Answer 3

Not directly related, but this answer to another question at SO includes the syntax details of 'templated' queries. I'd say that the auto-escaping would be their most important feature...

As for performance, note the method executemany on cursor objects. It bundles up a number of queries and executes them all in one go, which does lead to better performance.

Answer 4

After a quick look through an execute() method of a Cursor object of a MySQLdb package (a kind of de-facto package for integrating with mysql, I guess), it seems, that (at least by default) it only does string interpolation and quoting and not the actual parametrized query:

if args is not None:
    query = query % db.literal(args)

If this isn't string interpolation, then what is?

In case of executemany it actually tries to execute the insert/replace as a single statement, as opposed to executing it in a loop. That's about it, no magic there, it seems. At least not in its default behaviour.

EDIT: Oh, I've just realized, that the modulo operator could be overriden, but I've felt like cheating and grepped the source. Didn't find an overriden mod anywhere, though.

Answer 5

Using the SQL Interface as suggested by Amit can work if you're only concerned about performance. However, you then lose the protection against SQL injection that a native Python support for prepared statements could bring. Python 3 has modules that provide prepared statement support for PostgreSQL. For MySQL, "oursql" seems to provide true prepared statement support (not faked as in the other modules).