PYnative

Python Programming

Home » Python » Databases » Python MySQL Execute Parameterized Query using Prepared Statement

Python MySQL Execute Parameterized Query using Prepared Statement

Updated on:  | 18 Comments

This article demonstrates how to use a Python Parameterized query or Prepared Statement to perform MySQL database operations.

We use Parameterized query to use Python variable in SQL query. For example: –

  • We often need to pass variables to SQL select query in where clause to check some conditions.
  • In the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

Further Reading:

For this article, we are going to use the Employee table present in my MySQL server. See its column details.

Employee table
Employee table

Table of contents

What is the Parameterized Query

A parameterized query is a query in which placeholders (%s) are used for parameters (column values) and the parameter values supplied at execution time.

Let’s see the example of a parameterized query:

sql_parameterized_query = """Update employee set Salary = %s where id = %s"""Code language: Python (python)

As you can see, we are using a placeholder (%s) for the salary and id column. We need to supply values in placeholders (%s) before executing a query. Pass Python variables at the placeholder’s position when we execute a query.

We need to pass the following two arguments to a cursor.execute() function to run a parameterized query.

  • SQL query
  • A tuple of parameter values. In our case, we need to pass two Python variables, one for salary and one for id.
query = """Update employee set Salary = %s where id = %s"""
tuple1 = (8000, 5)
cursor.execute(query, tuple1)Code language: Python (python)

Use of Parameterized Query and Prepared Statement

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

There are four main reasons to use.

  • Compile Once: Parameterized query compiled only once. When you use parameterized query, it gets precompiled and stored in a PreparedStatement object. Now, use this object to execute the same statement multiple times efficiently. Note: For a standard query, MySQL compiles the query each time before running it.
  • Improves Speed: If you execute SQL statements repeatedly with a precompiled query, it reduces the execution time.
  • Same Operation with Different Data: You can use it to execute the same query multiple times with different data. For example, you want to insert 200 rows in a table. In such cases, use parameterized query to repeatedly execute the same operation with a different set of values.
  • It prevents SQL injection attacks.

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

How to use Parameterized Query in Python

Create a Prepared statement object using a connection.cursor(prepared=True).

It creates a specific cursor on which statements are prepared and return a MySQLCursorPrepared class instance.

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

# this will retun MySQLCursorPrepared object
cursor = connection.cursor(prepared=True)Code language: Python (python)

Example to insert data into MySQL table using Parameterized Query

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.

First, you need to take user input into a variable and pass that variable to the INSERT query as a placeholder (%s). All values are dynamic, i.e., depending on user input.

Let’s see how to use the parameterized query to insert data into the MySQL database using Python.

import mysql.connector

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

    cursor = connection.cursor(prepared=True)
    # Parameterized query
    sql_insert_query = """ INSERT INTO Employee
                       (id, Name, Joining_date, salary) VALUES (%s,%s,%s,%s)"""
    # tuple to insert at placeholder
    tuple1 = (1, "Json", "2019-03-23", 9000)
    tuple2 = (2, "Emma", "2019-05-19", 9500)

    cursor.execute(sql_insert_query, tuple1)
    cursor.execute(sql_insert_query, tuple2)
    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")
Code language: Python (python)

Output

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

Refer to fetch rows from MySQL table in Python using parameterized Query.

Understand Python MySQL parameterized Query program

  • First, we established the connection with MySQL from Python.
  • Next, we created a prepared statement object.
  • Next, we created the parameterized SQL query. In this query, we are using four placeholders for four columns.
  • Next, we added the value of four columns in the tuple in sequential order.
  • Next, we passed SQL insert query and tuple to a cursor.execute() method, remember tuple contains user data in the sequential order of placeholders.
  • n the end, we are committing our changes to the database using the connection.commit().
  • We placed our all code in the try-except block to catch exceptions if any.

Note: You can also create a prepared statement by explicitly passing the MySQLCursorPrepared class as an argument while creating a cursor.  

connection.cursor(cursor_class=MySQLCursorPrepared)Code language: Python (python)

Use Parameterized Query Update data of MySQL table

Let’s see how to update the MySQL table using Python. In this example, we are updating the salary of an employee using a parameterized query.

import mysql.connector

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")
Code language: Python (python)

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’s see how to use the prepared statement and the parameterized query to delete the MySQL table’s data from Python.

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

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

    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")
Code language: Python (python)

Output:

Record Deleted successfully using Parameterized query
MySQL connection is closed

Working of a Parameterized Query

The first time you pass a SQL query statement to the cursor’s execute() method, it creates the prepared statement.

For subsequent invocations of executing, the preparation phase is skipped if the SQL statement is the same, i.e., the query is not recompiled.

  • In the first cursor.execute(query, tuple) Python prepares statement i.e. Query gets compiled.
  • For subsequent execution calls of cursor.execute(query, tuple), The query gets executed directly with passed parameter values.

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.

TweetF  sharein  shareP  Pin

About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

Related Tutorial Topics:

Python Python Databases

All Coding Exercises:

C Exercises
C++ Exercises
Python Exercises

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 25+ questions
  • Each Quiz contains 25 MCQ
Exercises
Quizzes

Comments

  1. Dangus says

    I often build SQL queries that include a list of values, like “WHERE ID IN (1, 3, 48, 121, 138)” I don’t necessarily know how long that list will be. ‘ %s’ inserts code-wrecking quote marks. ‘%d’ only allows discrete numbers. Is there a way of doing this, or is this sort of dynamically created SQL what parameterized queries prevents?

    Reply

  2. Frank Smith says

    Hi There,

    this is not an answer but just a plea for help on another thing I am trying for a week now do.
    I am writing a python code to search in a Mysql Table for a name in a field CHARVAR type.

    import mysql.connector
import tkinter  as tk 
from tkinter import *

my_connect = mysql.connector.connect(
    host="localhost",
    user="root", 
    passwd="123Password+",
    database="material_batch_management"
)
my_cursor = my_connect.cursor()

####### end of connection ####

my_w = tk.Tk()
my_w.geometry("400x200") 

l1 = tk.Label(my_w,  text='Enter user ID: ', width=25 )  # added one Label 
l1.grid(row=1,column=1) 

t1 = tk.Text(my_w,  height=1, width=8,bg='yellow') # added one text box
t1.grid(row=1,column=2) 

b1 = tk.Button(my_w, text='Show Details', width=15,bg='red',
    command=lambda: my_details(t1.get('1.0',END)))
b1.grid(row=1,column=4) 

my_str = tk.StringVar()
l2 = tk.Label(my_w,  textvariable=my_str, width=30,fg='red' )  # added one Label 
l2.grid(row=3,column=1,columnspan=2) 

my_str.set("Output")

def my_details(id):
    try:
        val = int(id) # check input is integer or not
        try:
            my_cursor.execute("SELECT * FROM users WHERE name= t1.get(1,END))"
            student = my_cursor.fetchone()
            #print(student)
            my_str.set(student)
    
        except : 
             my_str.set("Database error")
    except:
        my_str.set("Check input")
my_w.mainloop()

    any help will be appreciated.

    Reply

  3. Moussa says

    Hallo every one, I need to make a function where I can write if the name exists in the table print….. Can someone PLZ help

    Reply

  5. Priya says

    i want to compare sql table data types with any list of file which has data types in python.please help me for this

    Reply

  6. salim says

    hi, i want to update the salary of an employee using a parameterized query but the employee id and salary i entred it in html file

    Reply

  7. Nasmie Solomons says

    Thanks, this page was a great help. I have booked marked it and will be return to it regularly!

    Reply

  8. Brian says

    Vishal: Thanks for the parameterized SQL query examples.

    I used the parameterized SQL query approach posted here by Vishal and it worked for me. I’m using:
    mysql-connector-python==8.0.20
    Ubuntu Linux 20.04
    MySQL Server version 8.0.20-0ubuntu0.20.04.1

    Reply

  9. venkat says

    The follwing parametrized query not working. There are rows and it is not giving any error either. I tried all options given by you in mysql. I do not where am I erring 

    import mysql.connector as mysql

myconn = mysql.connect(host="localhost",user="root",password="",database="knadb")
mycursor = myconn.cursor(prepared=True)

def getnames():
    namesrch = str(srchnametxt.get().upper())

    try:
        qry1= ("""SELECT name from stud  where name = %s""")
        mycursor.execute(qry1,(namesrch,))

        #mycursor.execute("Select * from stud where name like  '%snamesrch' ")  # 1

        #qry1="SELECT stud.name FROM stud WHERE name = %s";     ##ok        #2
        #mycursor.execute(qry1,(namesrch,) )

        #qry1 = """select * from stud where name = '%s' """    ##ok   #3
        #mycursor.execute(qry1, (namesrch,))

        print(namesrch)
        my_rows=mycursor.fetchall()
        rowsget= mycursor.rowcount
        print(rowsget)

        for result in my_rows:
            tab3scrtxt.insert(END,result)

    except mysql.DatabaseError as selecterr:
        errtxt1 = selecterr
        messagebox.showinfo("Warning", errtxt1)

    with 0 error
    the variable name appears while print(namesrch)

    Reply

  10. Steven Snead says

    Thanks for taking the time but it does not work for me. I wish once something I research would work.So many favors. I guess don’t have the same mysql.connector you do. Below is what I get:
    It does now work the system.

    	query = """SELECT * FROM ttp_assigned WHERE user_id = %s"""		
	data_turple = ("34")
        cursor.execute(query,data_turple)

    msg=”Incorrect number of arguments ” \
    mysql.connector.errors. ProgrammingError: 1210: Incorrect number of arguments executing prepared statement

    Reply

    • Steven Snead says

      Well, this is what I did to get it to work. Would have like to do you your way but maybe this will help someone like me that was having problems.

      cursor = db.cursor()

## defining the Query
query = "SELECT * FROM ttp_assigned WHERE user_id = "		
query = query+user_id
query = query+" AND device_id = "+device_id

## getting records from the table
result  = cursor.execute(query)
      Reply

    • Derek says

      The problem with Steven’s code is this:

      data_turple = ("34")
type(data_turple)

data_turple = ("34", )
type(data_turple)
      Reply

      • Derek says

        The website cut out part of the code I posted. The essence is that Steven’s “data_turple” is not a tuple but a string. To create a tuple for a single value, you need a add a comma at the end, so (“tuple”,) vs (“not_a_tuple”)

        Reply

  12. Rich Altmaier says

    Can you tell me what this syntax to .execute is doing? Where is this style of parameter substitution explained?

    	    c.execute(""" 
    	        select dateTime, rain  from archive where dateTime >= :now - (24 * 3600 * :past )
               AND dateTime <=  :now
               ORDER BY dateTime
               """, {"now": storm_now, "past": storm_in_past})

    I don’t find this: parameter style mentioned anywhere.

    Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your entire code </pre>

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Explore Python

Coding Exercises

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our:

Copyright © 2018–2026 pynative.com