Python MySQL Execute Parameterized Query using Prepared Statement

• 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 statement is the same, i.e. the query is not compiled again.
• The CURSOR’s execute() method takes an optional second argument containing a list of data values. each entry in the list must be in tuple format to associate with parameter markers in the statement.
• If the list argument is present, there must be one value per parameter marker in sequential order of placeholders.
  for example, you have two placeholders in a Query then a list of values will be like :
  list = [(10,2), (20,3), (40,4)]

conn = mysql.connector.connect(host='localhost',
                             database='python_db',
                             user='pynative',
                             password='pynative@#29')
cursor = connection.cursor(prepared=True)
query = "SELECT * FROM python_users WHERE id = %s"
cursor.execute(query, (1,)) 
# ... retrieve data ...
cursor.execute(query, (2,))                           
# ... retrieve data ...

• in the first cursor.execute(query, (1,))  python prepares statement i.e. Query gets compiled.
• For subsequent calls of a cursor.execute() 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,)

Use Parameterized query and Prepared Statement to Insert data into MySQL table

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`,`salary`,`city`) VALUES (%s,%s,%s,%s)"""
    emp_id = 3
    emp_name = "Jason"
    salary = 8000
    city = "New York"

    insert_tuple = (emp_id, emp_name, salary, city)

    result = cursor.execute(sql_insert_query, insert_tuple)
    connection.commit()

    print("variable inserted successfully into employee table using the prepared statement")

except mysql.connector.Error as error:
    connection.rollback()
    print("parameterized query failed {}".format(error))

finally:
    # closing database connection.
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

variable inserted successfully into employee table using the prepared statement

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

Now, Let see how to use the prepared statement and parametrized query to update the data of MySQL table.

When we need input from the user. For example, when user updating their 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 parametrized 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)
    
    # Update single record now
    sql_update_query = """Update employee set salary= %s where id = %s"""
    
    sal = 12000
    id = 5
    input_tuple = (sal, id)
    
    cursor.execute(sql_update_query, input_tuple)
    connection.commit()
    print("Record Updated successfully with prepared statement")

except mysql.connector.Error as error:
    connection.rollback()
    print("parameterized query failed {}".format(error))

finally:
    # closing database connection.
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Record Updated successfully with prepared statement

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

Now, Let see how to use the prepared statement and parametrized query to delete the data of MySQL table.

For an 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='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor(prepared=True)

    # Delete record now
    sql_Delete_query = """Delete from employee where id = %s"""
    emp_id = 3
    
    cursor.execute(sql_Delete_query, (emp_id,))
    connection.commit()
    print("\n Record Deleted successfully ")

except mysql.connector.Error as error:
    connection.rollback()
    print("parameterized query failed {}".format(error))

finally:
    # closing database connection.
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

The Output of the above program:

Record Deleted successfully

Next Steps:

To practice what you learned in this article, I have created a Python Database programming Quiz and Exercise Project.

That’s it. Folks Let me know your comments and questions in the section below.