Does Python support MySQL prepared statements?
PythonMysqlPrepared StatementPython Problem Overview
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.
Python Solutions
Solution 1 - Python
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'
Solution 2 - Python
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.
Solution 3 - Python
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.
Solution 4 - Python
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
Solution 5 - Python
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).
Solution 6 - Python
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.
Solution 7 - Python
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/