Does Python Support MySQL Prepared Statements

Using prepared statements with mysql in python

Using prepared statements with MySQL in Python is explained e.g at http://zetcode.com/db/mysqlpython/ -- look within that page for Prepared statements.

In your case, that would be, e.g:

sql = ('INSERT INTO {} (date, time, tag, power) VALUES '
       '(%s, %s, %s, %s)'.format(self.db_scan_table))

and later, "in the loop" as you put it:

self.cursor.execute(sql, (d, t, tag, power))

with no further string formatting -- the MySQLdb module does the prepare and execute parts on your behalf (and may cache things to avoid repeating work needlessly, etc, etc).

Do consider, depending on the nature of "the loop" you mention, that it's possible that a single call to .execute_many (with a sequence of tuples as the second argument) could take the place of the whole loop (unless you need more processing within that loop beyond just the insertion of data into the DB).

Added: a better alternative nowadays may be to use mysql's own Connector/Python and the explicit prepare=True option in the .cursor() factory -- see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html . This lets you have a specific cursor on which statements are prepared (with the "more efficient than using PREPARE and EXECUTE" binary protocol, according to that mysql.com page) and another one for statements that are better not prepared; "explicit is better than implicit" is after all one of the principles in "The Zen of Python" (import this from an interactive prompt to read all those principles). mysqldb doing things implicitly (and it seems the current open-source version doesn't use prepared statements) can't be as good an architecture as Connector/Python's more explicit one.

Does Python support MySQL prepared statements?

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.

Does the MySQLdb module support prepared statements?

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.

Getting an error when using prepared statement in python

You can't insert a table name as a query parameter. You can pass the name you're looking for as a parameter, but it should be in a tuple: ("check",)

cursor.execute("SELECT * FROM operations WHERE name = %s", ("check", ))

Python MySql Connector prepared statement and dictionary class

I have the same problem. Prepared and Dictionary don't work together.

I reported bug: https://bugs.mysql.com/bug.php?id=92700

Now we wait ;)

I get NotImplementedError when trying to do a prepared statement with mysql python connector

CEXT will be enabled by default if you have it, and prepared statements are not supported in CEXT at the time of writing.

You can disable the use of CEXT when you connect by adding the keyword argument use_pure=True as follows:

connection = mysql.connector.connect(user=username, password=password,
                                     host='sql_server_host',
                                     database='dbname',
                                     use_pure=True)

Support for prepared statements in CEXT will be included in the upcoming mysql-connector-python 8.0.17 release (according to the MySQL bug report). So once that is available, upgrade to at least 8.0.17 to solve this without needing use_pure=True.

Confusion between prepared statement and parameterized query in Python

Prepared statement: A reference to a pre-interpreted query routine on the database, ready to accept parameters
Parametrized query: A query made by your code in such a way that you are passing values in alongside some SQL that has placeholder values, usually ? or %s or something of that flavor.

The confusion here seems to stem from the (apparent) lack of distinction between the ability to directly get a prepared statement object and the ability to pass values into a 'parametrized query' method that acts very much like one... because it is one, or at least makes one for you.

For example: the C interface of the SQLite3 library has a lot of tools for working with prepared statement objects, but the Python api makes almost no mention of them. You can't prepare a statement and use it multiple times whenever you want. Instead, you can use sqlite3.executemany(sql, params) which takes the SQL code, creates a prepared statement internally, then uses that statement in a loop to process each of your parameter tuples in the iterable you gave.

Many other SQL libraries in Python behave the same way. Working with prepared statement objects can be a real pain, and can lead to ambiguity, and in a language like Python which has such a lean towards clarity and ease over raw execution speed they aren't really the greatest option. Essentially, if you find yourself having to make hundreds of thousands or millions of calls to a complex SQL query that gets re-interpreted every time, you should probably be doing things differently. Regardless, sometimes people wish they could have direct access to these objects because if you keep the same prepared statement around the database server won't have to keep interpreting the same SQL code over and over; most of the time this will be approaching the problem from the wrong direction and you will get much greater savings elsewhere or by restructuring your code.*

Perhaps more importantly in general is the way that prepared statements and parametrized queries keep your data sanitary and separate from your SQL code. This is vastly preferable to string formatting! You should think of parametrized queries and prepared statements, in one form or another, as the only way to pass variable data from your application into the database. If you try to build the SQL statement otherwise, it will not only run significantly slower but you will be vulnerable to other problems.

*e.g., by producing the data that is to be fed into the DB in a generator function then using executemany() to insert it all at once from the generator, rather than calling execute() each time you loop.

tl;dr

A parametrized query is a single operation which generates a prepared statement internally, then passes in your parameters and executes.

edit: A lot of people see this answer! I want to also clarify that many database engines also have concepts of a prepared statement that can be constructed explicitly with plaintext query syntax, then reused over the lifetime of a client's session (in postgres for example). Sometimes you have control over whether the query plan is cached to save even more time. Some frameworks use these automatically (I've seen rails' ORM do it aggressively), sometimes usefully and sometimes to their detriment when there are permutations of form for the queries being prepared.

Also if you want to nit pick, parametrized queries do not always use a prepared statement under the hood; they should do so if possible, but sometimes it's just formatting in the parameter values. The real difference between 'prepared statement' and 'parametrized query' here is really just the shape of the API you use.