Using the Pandas read_sql params for Dynamic Data Retrieval

The Pandas read_sql function provides a flexible params argument to pass parameters into SQL queries securely.

In this tutorial, you’ll learn how to use params parameter with lists, tuples, and dictionaries to filter, query, and retrieve data based on different criteria.

You’ll also learn how to construct dynamic queries by parameterizing conditions, joins, and complex logic.

 

 

Using params with Different Data Types

Pandas read_sql function allows you to pass parameters in different data types such as lists, tuples, and dictionaries.

Let’s examine how to use these different data types with the params parameter:

1. List: Ideal for positional parameters in your SQL query.
2. Tuple: Similar to lists, but immutable, offering a slight performance advantage.
3. Dictionary: Perfect for named parameters.

Example with List

import pandas as pd
import sqlite3
query = "SELECT * FROM customer_data WHERE age > ? AND region = ?"
params = [30, 'North']
conn = sqlite3.connect('telecom_data.db')
df = pd.read_sql(query, conn, params=params)

Output:

   customer_id  age region  plan
0         1023   35  North  Gold
1         1145   31  North  Silver

Here, the params list corresponds to the placeholders ? in the SQL query, filtering data for customers over 30 in the ‘North’ region.

Example with Tuple

params = (30, 'North')
df = pd.read_sql(query, conn, params=params)

Output:

   customer_id  age region  plan
0         1023   35  North  Gold
1         1145   31  North  Silver

The tuple version works similarly to the list, providing an immutable set of parameters.

Example with Dictionary

query = "SELECT * FROM customer_data WHERE age > :age AND region = :region"
params = {'age': 30, 'region': 'North'}
df = pd.read_sql(query, conn, params=params)

Output:

   customer_id  age region  plan
0         1023   35  North  Gold
1         1145   31  North  Silver

In this approach, each parameter in the query is named.

Handling Lists for IN Clauses

When dealing with SQL IN clauses, you may need to handle lists:

query = "SELECT * FROM call_logs WHERE network IN (:networks)"
params = handle_params(networks=('Verizon', 'AT&T'))
df = pd.read_sql(query, conn, params=params)

In this scenario, handle_params would need to be extended to properly format lists into a tuple, as SQLite requires for IN clauses.

 

Preparing SQL Queries for Parameterization

There are two primary placeholder syntaxes used in SQL queries: %s and :name.

Using %s as Placeholder

The %s syntax is often used with databases like MySQL and PostgreSQL. It represents positional parameters in your query.

import pandas as pd
import psycopg2  # assuming we're using PostgreSQL
conn = psycopg2.connect("dbname=telecom_data user=your_username")
query = "SELECT * FROM call_logs WHERE duration > %s AND network = %s"
params = [60, 'Verizon']
df = pd.read_sql(query, conn, params=params)

Output:

   call_id  duration   network
0      234        90  Verizon
1      562       120  Verizon

Here, %s placeholders are replaced by the values in params in the order they are provided.

Using :name as Placeholder

The :name syntax, commonly used with SQLite and Oracle databases, allows named parameters in your query.

import sqlite3
conn = sqlite3.connect('telecom_data.db')
query = "SELECT * FROM call_logs WHERE duration > :duration AND network = :network"
params = {'duration': 60, 'network': 'Verizon'}
df = pd.read_sql(query, conn, params=params)

Output:

   call_id  duration   network
0      234        90  Verizon
1      562       120  Verizon

In this example, the :name placeholders directly map to the keys in the params dictionary.

 

Dynamic Query Building

Dynamic query building is an advanced technique that allows your Python code to generate SQL queries based on varying parameters.

This approach is useful in scenarios where the query criteria depend on user input, application state, or other runtime conditions.

Suppose you want to filter call records based on different criteria like duration, network, and call_type.

import pandas as pd
import sqlite3

# Sample function to build dynamic query
def build_query(duration=None, network=None, call_type=None):
    query = "SELECT * FROM call_logs"
    conditions = []
    params = {}
    if duration:
        conditions.append("duration > :duration")
        params['duration'] = duration
    if network:
        conditions.append("network = :network")
        params['network'] = network
    if call_type:
        conditions.append("call_type = :call_type")
        params['call_type'] = call_type
    if conditions:
        query += " WHERE " + " AND ".join(conditions)
    return query, params
conn = sqlite3.connect('telecom_data.db')
query, params = build_query(duration=100, network='Verizon')
df = pd.read_sql(query, conn, params=params)

Output:

   call_id  duration   network call_type
0      345       120  Verizon   Mobile
1      567       150  Verizon   Landline

Here, the function build_query dynamically constructs the SQL query based on the provided parameters.

 

Handling Date and Time Types

Dates and times require careful handling to match the database’s expected format:

from datetime import datetime
query = "SELECT * FROM call_logs WHERE call_date > :date"
params = handle_params(date=datetime.now().strftime("%Y-%m-%d"))
df = pd.read_sql(query, conn, params=params)

In this case, dates are converted to a string format that matches the SQL database’s expected format.

 

Parameterized Queries

Using parameterized queries is the most effective way to safeguard against SQL injection.

It ensures that user-provided values are not directly interpolated into the SQL string, but are instead handled safely by the database driver.

# Unsafe: Direct interpolation (Do NOT use this approach)
user_input = "'; DROP TABLE customer_data; --"
unsafe_query = f"SELECT * FROM customer_data WHERE name = '{user_input}'"

# Safe: Parameterized query
safe_query = "SELECT * FROM customer_data WHERE name = :name"
params = {'name': user_input}

Always avoid directly interpolating user input into SQL queries. Instead, use the parameterized approach provided by Pandas and the underlying database driver.