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.

• James McMahon: See stackoverflow.com/a/2425500/20774 for a direct answer. TLDR 'no'
• rubayeet: Thanks @JamesMcMahon. Could you submit your comment as an answer?
• James McMahon: Done, see stackoverflow.com/a/17237567/20774
• alireza: 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.
• acrosman: possible duplicate of Does the MySQLdb module support prepared statements?

Answer 1

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'

• scippie: Are strings automatically escaped (made query safe)?
• Jeremy Stein: I think you're referring to automatic SQL quoting, not actual parameterized queries.
• joshperry: @scippie 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)
• type: 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
• floer32: Good call @type. This thread recommends oursql over MySQLdb. MySQLdb can handle parametrized queries through interpolation, but does not support prepared statements.

Answer 2

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.

• underrun: 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
• Darragh Enright: 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?

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.

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

• Bob Stein: 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'))

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

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.

• shylent: 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.
• type: looking at MySQLdb source it seems .executemany() only loops over .execute()

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/