How to use Python variables in SQL statement

Python can be used in SQL statements to help developers analyze data. It is very useful in the field of machine learning. When you incorporate Python with SQL statements, you will get a clear and efficient output. There are several approaches in SQLite and MySQL to use Python variables in SQL statements. In this blog, you will explore all the approaches to using Python variables in SQL statements and how to use them in detail with examples for each.

Table of Contents:

• Different Approaches to Use Python Variables in SQL Statements
  • Method 1. Using Parameterized Queries
  • Method 2. Parameterized Queries with Named Placeholders
  • Method 3. Using f-Strings for Query Construction
  • Method 4. Using SQLAlchemy ORM
  • Method 5. Using Views in SQLite
  • Method 6. Using a Custom SQLite function
• Alternative Methods Using MySQL Server
  • Using the Stored procedure approach in MySQL
• Performance Consideration
• Real-World Examples
• Conclusion

Different Approaches to Use Python Variables in SQL Statements

There are different approaches, like parameterized queries with named placeholders, f-strings, SQLAlchemy, etc., to use Python variables in SQL statements.

Method 1. Using Parameterized Queries

This method uses SQLite to integrate Python into SQL statements. Sometimes, when combining the two different languages may compromise system security, which will lead to SQL injection in this case. But using parameterized queries, we can prevent the SQL injection. It will be efficient if you use a cloud-based platform like Google Colab.

Example:

import sqlite3
# Connect to (or create) the database
conn = sqlite3.connect('example_positional.db')
cursor = conn.cursor()
# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')

# Python variables
user_name = "Alice"
user_age = 30
# Parameterized query using positional placeholders
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (user_name, user_age))
# Commit and fetch results
conn.commit()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)
conn.close()

Output:

Explanation: Here, the parameterized queries will ensure proper data type handling and efficient query execution.

Method 2. Parameterized Queries with Named Placeholders

The parameterized query with named placeholders will also protect the environment from SQL injection. The Python interacts with SQLite and gets the necessary input from the database.

Example:

import sqlite3
# Connect to (or create) the database
conn = sqlite3.connect('example_named.db')
cursor = conn.cursor()
# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')
# Python variables
user_name = "Bob"
user_age = 25
# Parameterized query using named placeholders
cursor.execute("INSERT INTO users (name, age) VALUES (:name, :age)", {"name": user_name, "age": user_age})
# Commit and fetch results
conn.commit()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)
conn.close()

Output:

Explanation: The code shows that the named placeholders in SQLite maps the “name” and “age” directly to the dictionary, and this makes the query easier to read and understand.

Method 3. Using f-Strings for Query Construction

You can use the f-string approach for the query construction, but it needs to be handled carefully because even a single mistake in the query will lead to SQL injection vulnerabilities. This is another method that directly handles the Python objects. Get the query from a trusted source so that the SQL injection will not occur.

Example:

import sqlite3
# Connect to (or create) the database
conn = sqlite3.connect('example_fstring.db')
cursor = conn.cursor()
# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')
# Python variables
user_name = "Charlie"
user_age = 35
# Using f-string to format the SQL query (make sure the variables are safe)
query = f"INSERT INTO users (name, age) VALUES ('{user_name}', {user_age})"
cursor.execute(query)
# Commit and fetch results
conn.commit()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)
conn.close()

Output:

Explanation: Though f-strings are a quick way to build queries, they do not escape the inputs. You can only use this method if you are so sure that your data is safe.

Method 4. Using SQLAlchemy ORM

SQLAlchemy ORM, ORM is abbreviated as Object Relational Mapping. It is a layer that handles the SQL queries and manages data with the help of Python objects.

Example:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker 
engine = create_engine('sqlite:///example_sqlalchemy.db')
Base = declarative_base()  # Updated for SQLAlchemy 2.0
# Define User model
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False) 
    age = Column(Integer, nullable=False)

    def __repr__(self):
        return f"User(name='{self.name}', age={self.age})"
# Create a table 
Base.metadata.create_all(engine)
# Create a session
Session = sessionmaker(bind=engine)
session = Session()
# Insert new users only if they don't already exist
user_name = "Praveen"
user_age = 28
existing_user = session.query(User).filter_by(name=user_name, age=user_age).first()
if not existing_user:
    new_user = User(name=user_name, age=user_age)
    session.add(new_user)
    session.commit()
    print("New user added.")
else:
    print("User already exists.")
# Fetch and print all users
users = session.query(User).all()
for user in users:
    print(user)
# Close session
session.close()

Output:

Explanation: This method makes the code simple and better maintained, as SQLAlchemy handles querying, creating, and updating while letting you work with Python classes instead of raw SQL.

Method 5. Using Views in SQLite

Whenever SQLite doesn’t support stored procedures, you can use views to open and use already stored queries using the encapsulation technique rather than writing the same queries again and again. You can define them once and use the codes when you need them.

Example:

import sqlite3
# Connect to SQLite database 
conn = sqlite3.connect("test_db.sqlite")
cursor = conn.cursor()

#Create a 'users' table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
""")
conn.commit()

#Insert Sample Data
cursor.execute("SELECT COUNT(*) FROM users")
if cursor.fetchone()[0] == 0:
    users = [
        ("Praveen", 28),
        ("John", 25),
        ("Alice", 30),
        ("Mike", 22),
        ("Sarah", 27)
    ]
    cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)
    conn.commit()
    print("Sample data inserted.")

#Create an SQLite View 
cursor.execute("""
    CREATE VIEW IF NOT EXISTS user_view AS
    SELECT id, name, age FROM users WHERE age > 26;
""")
conn.commit()
print("View 'user_view' created successfully!")

#Query from the View
cursor.execute("SELECT * FROM user_view")
print("\nData from 'user_view':")
for row in cursor.fetchall():
    print(row)

#Define and Register a Custom SQLite Function in Python
def double_age(value):
    return value * 2

conn.create_function("double_age", 1, double_age)

#Use the Custom Function in a Query
cursor.execute("SELECT name, double_age(age) FROM users")
print("\nData using custom function 'double_age':")
for row in cursor.fetchall():
    print(row)
#Close connection
cursor.close()
conn.close()

Output:

Explanation: In this method, it is efficient and easy when we perform the operation by reusing the queries.

Method 6. Using a Custom SQLite function

You can use the Python objects and classes directly within the SQL queries, which are particularly useful for computational logic.

Example:

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect("test_db.sqlite")
cursor = conn.cursor()

# Step 1: Create a 'users' table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
""")
conn.commit()

# Step 2: Insert Sample Data
cursor.execute("SELECT COUNT(*) FROM users")
if cursor.fetchone()[0] == 0:
    users = [
        ("Praveen", 28),
        ("John", 25),
        ("Alice", 30),
        ("Mike", 22),
        ("Sarah", 27)
    ]
    cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)
    conn.commit()
    print("Sample data inserted.")

# Step 3: Define a Custom SQLite Function in Python
def reduce_age(value):
    """Custom function to reduce the age by 4."""
    return value - 4

# Step 4: Register the function with SQLite
conn.create_function("reduce_age", 1, reduce_age)

# Step 5: Use the Custom Function in a Query
cursor.execute("SELECT name, age, reduce_age(age) FROM users")

# Step 6: Display the Results
print("\nData using custom function 'reduce_age':")
for row in cursor.fetchall():
    print(row) 

# Close connection
cursor.close()
conn.close()

Output:

Explanation: This example shows that the Custom SQLite function gets the age of the people by reducing it with the help ofthe return value – 4 command.

Alternative Methods Using MySQL Server

Using the Stored procedure approach in MySQL

You can use the stored procedures in MySQL, as it allows the SQL logic to be stored in the database that protects the queries from the threat. It can use the query again as it stores the query globally within a database.

Example:

----Create database company
CREATE DATABASE CompanyDB;
USE CompanyDB;

CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

INSERT INTO Employees (Name, Department, Salary) 
VALUES 
    ('Alice', 'HR', 50000),
    ('Bob', 'IT', 60000),
    ('Charlie', 'Finance', 55000),
    ('David', 'IT', 62000),
    ('Eve', 'HR', 51000);

----To retrieve data
DELIMITER $$

CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM Employees WHERE Department = dept_name;
END $$

DELIMITER ;
</pre>

<pre>
# Install required libraries
!pip install mysql-connector-python pandas

import mysql.connector
import pandas as pd
from IPython.display import display

# Define database connection parameters
db_config = {
    "host": "your_host",     
    "user": "your_username",  
    "password": "your_password",  
    "database": "CompanyDB"    
}

try:
    # Connect to MySQL database
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    #Create a Stored Procedure
    procedure_query = """
    DELIMITER //
    CREATE PROCEDURE IF NOT EXISTS GetEmployeesByDept(IN dept_name VARCHAR(50))
    BEGIN
        SELECT ID, Name, Department, Salary
        FROM Employees
        WHERE Department = dept_name;
    END //
    DELIMITER ;
    """
    cursor.execute(procedure_query, multi=True)

    #Define department name
    department = "IT"

    #Call Stored Procedure
    cursor.callproc('GetEmployeesByDept', (department,))

    #Fetch results and convert to Pandas DataFrame
    for result in cursor.stored_results():
        df = pd.DataFrame(result.fetchall(), columns=['ID', 'Name', 'Department', 'Salary'])

    #Display results
    display(df)

except mysql.connector.Error as err:
    print(f"Error: {err}")

finally:
    # Close connections
    if cursor:
        cursor.close()
    if conn:
        conn.close()

Output:

Explanation: Here, the stored procedure in MySQL retrieved the employee’s data from stored data.

Performance Consideration

Real-World Examples

Case 1: For e-commerce websites, fetching order details.

Example:

----Set up a database in Colab 
import sqlite3
import os

# Remove existing database to prevent locking issues
if os.path.exists("ecommerce.db"):
    os.remove("ecommerce.db")

# Create a new database connection
conn = sqlite3.connect("ecommerce.db", timeout=30)
cursor = conn.cursor()

print("SQLite database connected successfully!")

----Create table and database
# Enable WAL mode for better performance
cursor.execute("PRAGMA journal_mode=WAL;")
conn.commit()

# Create products table
cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER NOT NULL
)
""")

# Insert sample products
products_data = [
    (1, "Laptop", 999.99, 10),
    (2, "Smartphone", 499.99, 25),
    (3, "Headphones", 79.99, 50),
    (4, "Monitor", 199.99, 15)
]

cursor.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", products_data)
conn.commit()

# Create orders table
cursor.execute("""
CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    total_price REAL NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
)
""")

# Insert sample orders
orders_data = [
    ("Alice", 1, 1, 999.99), 
    ("Bob", 2, 2, 999.98),    
    ("Charlie", 3, 1, 79.99) 
]

cursor.executemany("INSERT INTO orders (customer_name, product_id, quantity, total_price) VALUES (?, ?, ?, ?)", orders_data)
conn.commit()

print("Tables created and sample data inserted successfully!")

----Fetch order details
# Fetch orders with product details
query = """
SELECT o.order_id, o.customer_name, p.name AS product_name, o.quantity, o.total_price
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.customer_name = ?
"""

customer_name = "Alice"
cursor.execute(query, (customer_name,))
orders = cursor.fetchall()

# Display results
print("Order Details for", customer_name)
for order in orders:
    print(order)

# Close connection
conn.close()

Output:

Explanation: The parameterized queries fetched the order details. This prevents the SQL injection and leakage of data.

Case 2: Fetching the students who have enrolled in courses.

Example:

---Create database
import sqlite3
import os
# Remove any existing database to avoid locking issues
if os.path.exists("students.db"):
    os.remove("students.db")
# Create a new database connection
conn = sqlite3.connect("students.db", timeout=30)
cursor = conn.cursor()
print("SQLite database connected successfully!")

----Create a table and insert the values
# Enable WAL mode for better concurrency
cursor.execute("PRAGMA journal_mode=WAL;")
conn.commit()
# Create students table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
)
""")
# Create courses table
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
    course_id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_name TEXT NOT NULL,
    teacher TEXT NOT NULL
)
""")
# Create enrollments table
cursor.execute("""
CREATE TABLE IF NOT EXISTS enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER,
    course_id INTEGER,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
)
""")
# Insert sample students
students_data = [
    ("Alice", 20),
    ("Bob", 22),
    ("Charlie", 19)
]
cursor.executemany("INSERT INTO students (name, age) VALUES (?, ?)", students_data)
conn.commit()
# Insert sample courses
courses_data = [
    ("Mathematics", "Dr. Smith"),
    ("Computer Science", "Prof. Johnson"),
    ("Physics", "Dr. Brown")
]
cursor.executemany("INSERT INTO courses (course_name, teacher) VALUES (?, ?)", courses_data)
conn.commit()
# Insert sample enrollments
enrollments_data = [
    (1, 1),  
    (1, 2),
    (2, 3), 
    (3, 1)   
]
cursor.executemany("INSERT INTO enrollments (student_id, course_id) VALUES (?, ?)", enrollments_data)
conn.commit()
print("Tables created and sample data inserted successfully!")

------Fetch the enrolled students lists
# Query to get courses a student is enrolled in
query = """
SELECT s.name AS student_name, c.course_name, c.teacher
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.name = ?
"""
student_name = "Alice"  
cursor.execute(query, (student_name,))
courses = cursor.fetchall()
# Display results
print(f"Courses enrolled by {student_name}:")
for course in courses:
    print(course)
# Close the connection
conn.close()

Output:

Explanation: The parameterized query fetched the details of students who enrolled in subjects.

Conclusion

Python can be used with SQL to help in analyzing the data. Some approaches can also be used in Python with SQL, like parameterized queries, named placeholders, f-strings, custom SQLite functions, and SQLAlchemy ORM. The parameterized and named placeholders are very helpful in protecting from SQL injection and f-strings (which have to be handled carefully), so use queries from a trusted source. SQLAlchemy ORM provides an abstract layer for better maintenance. Views in SQLite and stored procedures from MySQL, which simplify the execution. Choose the correct method based on the performance, and query flexibility of your database.

To learn more about this, check out the Python course and SQL course. Also, explore SQL Interview Questions prepared by industry experts.

How to use Python variables in SQL statement – FAQs