Does Python MySQL support prepared statements?

Question

I used to work on a PHP project when prepared statements made SELECT queries 20% faster.

I am wondering if it works in Python? It seems that I can not find anything that specifically says that it does or NOT.

Answer 1

The direct answer is no.

joshperry answer is a good explanation of what it does.

From eugene y, answer a similar question ,

Check MySQLdb Package Comments :

"Parameterization" is performed 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 must go through two intermediate views (encoded string, escaped string) before they are received by the database.

So the answer is: No, it is not.

Answer 2

Most languages ​​provide a way to create generic parameterized statements; Python is no different. When a parameterized query is used, databases that support agent provisioning 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 differ depending on your driver, you can import your db module and then 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, eg '...WHERE name=?' 'numeric' Numeric, positional style, eg '...WHERE name=:1' 'named' Named style, eg '...WHERE name=:name' 'format' ANSI C printf format codes, eg '...WHERE name=%s' 'pyformat' Python extended format codes, eg '...WHERE name=%(name)s' 

Answer 3

After a quick look at the execute () method of the Cursor object of the MySQLdb package (it seems to be a de facto package for integrating with mysql, I think), it seems that (at least by default) this is only string interpolation and quoting, and not the actual parameterized query:

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

If this is not string interpolation, then what is?

In the case of execution, it actually tries to perform insert / replace as a single statement, rather than execute it in a loop. Something about it seems to be no magic. At least not by default.

EDIT: Oh, I just realized that the modulo operator can be overestimated, but I felt like a hoax and blurred the source. However, no mod overrides were found.

Answer 4

Using the SQL interface suggested by Amit might work if you are only concerned about performance. However, you are losing the protection against SQL injection that native Python support for prepared statements can bring. Python 3 has modules that provide prepared support for PostgreSQL statements. For MySQL, “oursql" seems to provide true support for prepared statements (not fake, as in other modules).

Answer 5

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

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

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

Answer 6

Not directly related, but this answer to another question in SO includes details on the syntax of "templated" queries. I would say that automatic shielding will be their most important feature ...

Regarding performance, pay attention to the executemany method on cursor objects. It combines several queries and executes them all in one go, which leads to improved performance.

Answer 7

There is a solution!

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

 cursor.callproc(Procedurename, args) 

Here is a small tutorial on stored procedures in mysql and python.

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