Python MySQL Execute Parameterized Query using Prepared Statement

This article demonstrates how to use a Parameterized query or Prepared Statement from your python to perform MySQL database operations. Let’s see how to execute Python MySQL Parameterized Query using Prepared Statement.

For this article, we are going to use the Employee table present in my MySQL server. see it’s column details.

What is the parameterized query in python

A parameterized query is a query in which placeholders used for parameters and the parameter values supplied at execution time. That means parameterized query gets compiled only once.

Let’s see the example of a parameterized query:

sql_parameterized_query = """Update employee set Salary = %s where id = %s"""

As you can see, we are passing a parameter (%s) for the values. You must supply values in place of the placeholders (%s) before you can execute this query using a Prepared Statement. Here we provide python variables at the position of the placeholder.

Sometimes it is more convenient to use a parameterized Query or prepared statement for sending SQL queries to the database. But the main question is why to use Prepared Statement in python? 

Why use parameterized Query and Prepared Statement while working with the database in Python?

There are the main 4 reasons to use. There are main four reasons to use.

• Improves Speed: If you want to execute SQL statement/query many times, it usually reduces execution time
• Compile Once: The main advantage of using a parameterized query is that parameterized query compiled only once.  That means when you executed the parameterized query,  MySQL can just run the SQL statement without having to recompile it. It uses an already precompiled query and directly executes it.
  Note: For a standard query, MySQL compiles query each time before executing it.
• Same Operation with Different Data: if you want to execute the same query multiple times with different data. For example, you want to insert 200 rows in a table then you can use parameterized query so it can compile once and execute every time with different column values.
• Preventing SQL injection attacks.

Let’s see how to use the parameterized query to perform MySQL database operations using Python.

Note: We are using MySQL Connector Python to execute a Parameterized query.

How to use Parameterized query and prepared statement in Python

We can use the MySQLCursorPrepared class to execute the parameterized query. MySQLCursorPrepared inherits from MySQLCursor  (CURSOR class).

Cursor() method of the connection object returns a MySQLCursorPrepared object of a regular cursor object. Using MySQLCursorPrepared class, we can execute the prepared statement.

Create a Prepared statement using connection.cursor(prepared=True). The simpler syntax is to use a prepared=True argument in the connection.cursor() method.

import mysql.connector
connection = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')

cursor = connection.cursor(prepared=True) #this will return MySQLCursorPrepared object

Note: You can also create a prepared statement by explicitly passing the MySQLCursorPrepared class as an argument.  connection.cursor(cursor_class=MySQLCursorPrepared)

Python parameterized query and Prepared Statement to Insert data into MySQL table

Sometimes you need to insert a python variable as a column value in the insert query. For example, a user has filled an online form and clicked on submit. So you need to insert those values into a MySQL table you can do that using a parameterized query. First, you need to take user input into a variable and pass that variable to the INSERT query as a placeholder ( %s ).

For example, you want to insert a new row into the MySQL Employee table, all values are dynamic i.e. depends on user input. Let see how to insert into a table using a parameterized query or prepared statement.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='python_db',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor(prepared=True)
    sql_insert_query = """ INSERT INTO Employee
                       (id, Name, Joining_date, salary) VALUES (%s,%s,%s,%s)"""

    insert_tuple_1 = (1, "Json", "2019-03-23", 9000)
    insert_tuple_2 = (2, "Emma", "2019-05-19", 9500)

    cursor.execute(sql_insert_query, insert_tuple_1)
    cursor.execute(sql_insert_query, insert_tuple_2)
    connection.commit()
    print("Data inserted successfully into employee table using the prepared statement")

except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

You can get the following output if you execute the above program.

Data inserted successfully into employee table using the prepared statement
MySQL connection is closed

Understand Python MySQL parameterized Query program

• We used the functionmysql.connector.connect to connect the MySQL Database. This function accepts the required parameters: Host, Database, User, and Password. if a connection is successfully established it will return the connection object.
• We created a prepared statement object using the function connection.cursor(prepared=True). This function returns the MySQLCursorPrepared class.
• Then we created the parameterized SQL query. in this query, we are using four placeholders for four columns.
• Next, We used the prepared statement to accept user input using a placeholder, i.e. we put four placeholders in the insert query 
• We then added those four columns value in the input tuple in sequential order and passed SQL insert query and input tuple t0 cursor.execute() method,  remember tuple contains user data in the sequential order of placeholders.
• In the end, we are committing our changes to the database using connection.commit()
• We placed our all code in the try-except block to catch exceptions if any.

Working of a Parameterized Query

A cursor instantiated from connection.cursor(prepared=True) ( MySQLCursorPrepared  class) works like this:

The first time you pass a SQL query statement to the cursor’s execute() method, it prepares the statement.
For subsequent invocations of executing, the preparation phase is skipped if the SQL statement is the same, i.e. the query is not compiled again.

• in the first cursor.execute(sql_insert_query, insert_tuple_1)  Python prepares statement i.e. Query gets compiled.
• For subsequent calls of  cursor.execute(sql_insert_query, insert_tuple_2) The query will not be compiled again, this step is skipped and the query executed directly with passed parameter values.
• Note: we passed placeholders value in tuple format (1, "Json", "2019-03-23", 9000)

Use Parameterized query and Prepared Statement to Update data of MySQL table

Now, Let see how to use the prepared statement and the parameterized query to update the data of MySQL table. When we need input from the user. For example, when users updating Employee profile through User Interface that time values are determined at runtime. we need to use python variables in a query.

It is always best practice to use parameterized query i.e. placeholders ( %s ) inside an Update SQL statements that contain input from users. Let’ s see the example program now, in this example, we are updating the salary of an employee using a parameterized query.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='python_db',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor(prepared=True)
    sql_update_query = """UPDATE Employee set Salary = %s where Id = %s"""

    data_tuple = (12000, 1)
    cursor.execute(sql_update_query, data_tuple)
    connection.commit()
    print("Employee table updated using the prepared statement")

except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Employee table updated using the prepared statement
MySQL connection is closed

Use Parameterized query and Prepared Statement to Delete data from MySQL table

Now, Let see how to use the prepared statement and the parameterized query to delete the data of MySQL table. For example when user deleting their data from the web portal. In such a scenario, we need to use those variables inside a parameterized query using a placeholder ( %s ).

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='python_db',
                                         user='root')

    cursor = connection.cursor(prepared=True)
    sql_Delete_query = """Delete from employee where id = %s"""
    empId = 2

    cursor.execute(sql_Delete_query, (empId,))
    connection.commit()
    print("Record Deleted successfully using Parameterized query")

except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Record Deleted successfully using Parameterized query
MySQL connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.