How Can I Use Prepared Statements with MySQL in Python to Improve Query Performance and Prevent SQL Injection?

Using Prepared Statements with MySQL in Python

When working with database operations, prepared statements can significantly improve query performance. In Python, prepared statements can be utilized with MySQL.

Parameterized Query Format in Python

In Python, you can create a parameterized query using the following format:

cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])

The parameter marker %s acts as a placeholder for the actual value. Different database drivers may use varying parameterization styles. To determine the appropriate style for your driver, you can import the driver module and print its paramstyle attribute.

Supported Parameterization Styles

As per Python Enhancement Proposal (PEP-249), the following parameterization styles are supported:

• 'qmark': Question mark style (e.g., ...WHERE name=?)
• 'numeric': Numeric, positional style (e.g., ...WHERE name=:1)
• 'named': Named style (e.g., ...WHERE name=:name)
• 'format': ANSI C printf format codes (e.g., ...WHERE name=%s)
• 'pyformat': Python extended format codes (e.g., ...WHERE name=%(name)s)

Example Usage

To demonstrate the usage of prepared statements, consider the following example:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

cursor = db.cursor()

# Create a parameterized query
parameterized_query = "SELECT * FROM users WHERE name = %s"

# Execute the query with the provided parameter
cursor.execute(parameterized_query, ("John Doe",))

# Fetch the results
results = cursor.fetchall()

# Close the cursor and connection
cursor.close()
db.close()

Using parameterized queries ensures that your code is protected from SQL injection attacks and improves query performance by allowing the database to prepare the query plan in advance.

The above is the detailed content of How Can I Use Prepared Statements with MySQL in Python to Improve Query Performance and Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!