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.

Soninlaw

See https://mcmap.net/q/103592/-does-the-mysqldb-module-support-prepared-statements-duplicate for a direct answer. TLDR 'no' – Mercator

Thanks @JamesMcMahon. Could you submit your comment as an answer? – Soninlaw

Done, see https://mcmap.net/q/103170/-does-python-support-mysql-prepared-statements – Mercator

I checked in python and ? not working(mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement ) but % way is work perfectly. – Mantling

possible duplicate of Does the MySQLdb module support prepared statements? – Dreher

@Dreher Considering the second question was 3 months later than this one, I suggest you flag it there. – Soninlaw

Answer 1

Direct answer, no it doesn't.

joshperry's answer is a good explanation of what it does instead.

From eugene y answer to a similar question,

Check the MySQLdb Package Comments:

"Parameterization" is done in MySQLdb by escaping strings and then blindly interpolating them into the query, instead of using the MYSQL_STMT API. As a result unicode strings have to go through two intermediate representations (encoded string, escaped encoded string) before they're received by the database.

So the answer is: No, it doesn't.

Mercator

When you say "python" does not, that isn't exactly correct. the MySQLdb module does not support prepared statements, but oursql does. launchpad.net/oursql – Lakeishalakeland

I'm not sure if I should feel icky about this or not. On one hand, I feel a bit brainwashed into using prepared statements (coming from php/pdo). On the other hand, the input is escaped which is obviously important, and mysqldb seems to come up trumps in most of the benchmarks I've googled... I guess I wonder why it doesn't; I assume there's a good reason? – Sudatorium

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'

Armoire

Are strings automatically escaped (made query safe)? – Totten

I think you're referring to automatic SQL quoting, not actual parameterized queries. – Dioscuri

@Totten Yes, and no. While you technically don't need to worry about escaping, and the query is inherently safe, it isn't because the parameters are being escaped. The reason is that the parameters are sent to the server as metadata to the query, not in-line with the query statement like they would be if you were doing naive string concatenation. (This is true if your database supports paramaterized queries; if not, the python database module uses robust string concatenation to emulate them) – Armoire

it seems MySQLdb is sending each query plain without "preparing" (also sending multiple plain executes within executemany), whereas oursql does a prepare followed by an execute (or an executemany, which only sends the parameters/values). launchpad.net/oursql – Elan

Good call @type. This thread recommends oursql over MySQLdb. MySQLdb can handle parametrized queries through interpolation, but does not support prepared statements. – Rorrys

mysql.connector.paramstyle == 'pyformat' but "Prepared statements ... can use the format (%s) or qmark (?) parameterization style. This differs from nonprepared statements ... which can use the format or pyformat parameterization style." So apparently non-prepared statements are more robust, in that they allow %(named)s substitutions. – Larimore

The paramstyle property only outputs the default style, not all available styles. – Lap

Answer 3

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.

Phrygia

Answer 4

For people just trying to figure this out, YES you can use prepared statements with Python and MySQL. Just use MySQL Connector/Python from MySQL itself and instantiate the right cursor:

https://dev.mysql.com/doc/connector-python/en/index.html

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html

Snigger

Am I reading correctly the syntax is more robust (with named substitutions) when you do NOT use so-called prepared statements? cursor = connection.cursor(); cursor.execute("SELECT * FROM t WHERE name = %(name)s", dict(name='Monty')) – Larimore

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

Krystynakshatriya

Answer 6

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.

Oriana

well, it just runs a insert into foo (f1,f2,f3) values (f11,f12,f13),(f21,f22,f23),... and so on (instead of having you execute those inserts in a loop). I don't say that it doesn't increase performance though. – Phrygia

looking at MySQLdb source it seems .executemany() only loops over .execute() – Elan

Answer 7

There is a Solution!

You can use them if you put them into a stored procedure on the server and call them like this from python...

cursor.callproc(Procedurename, args)

Here is a nice little tutorial on Stored procedures in mysql and python.

http://www.mysqltutorial.org/calling-mysql-stored-procedures-python/

Magnet