In the world of Python and database interactions, Peewee is a popular ORM (Object-Relational Mapping) library. It provides an intuitive and easy-to-use interface for interacting with databases. While Peewee offers a rich set of functions for most common database operations, there might be cases where you need to execute raw SQL queries for more complex tasks.

In this blog post, we will explore how to execute raw SQL queries using Peewee and leverage the power of SQL within our Python applications.

Setup

Before diving into the raw SQL queries, let’s make sure you have Peewee installed. You can install it using pip:

pip install peewee

Next, let’s define a simple database model using Peewee:

from peewee import SqliteDatabase, Model, CharField, IntegerField

# Connect to an in-memory SQLite database
database = SqliteDatabase(':memory:')

# Define a base model
class BaseModel(Model):
    class Meta:
        database = database

# Define a model
class User(BaseModel):
    name = CharField()
    age = IntegerField()

Executing Raw SQL Queries

Peewee provides a method called execute_sql() for executing raw SQL queries. Here’s an example of how to use it:

# Import the necessary modules
from peewee import Model

# Execute a raw SQL query
query = "SELECT * FROM user WHERE age > ?"
params = (18,)  # Parameters to be substituted in the query

result = Model.execute_sql(query, params)

In the example above, we execute a raw SQL query that selects all users from the user table where the age is greater than 18. We pass a tuple (18,) as the parameter, which will be substituted in the query using prepared statements.

The execute_sql() method returns a cursor that can be used to fetch the results or perform other operations on the result set. You can use methods like fetchone(), fetchall(), or iterate over the cursor to access the returned data.

Handling Results

When executing raw SQL queries, you need to handle the results manually. Here’s an example of how to fetch the results from the cursor:

# Fetch the first row
row = result.fetchone()
while row:
    # Process the row
    print(row)

    # Fetch the next row
    row = result.fetchone()

In the example above, we fetch each row from the result set and process it. You can replace the print(row) line with any logic specific to your application.

Remember to close the cursor when you’re done using it:

result.close()

Conclusion

In this blog post, we’ve explored how to execute raw SQL queries using Peewee in Python. By leveraging the execute_sql() method and handling the results manually, you can perform complex database operations using the power of SQL within your Python applications.

Although raw SQL queries can be powerful, it’s important to use them judiciously and ensure proper validation and sanitization of user input to prevent security vulnerabilities such as SQL injection attacks.

Peewee offers a wide range of features that make database interactions convenient and efficient, but in cases where you need to go beyond the capabilities of the ORM, raw SQL queries can be a valuable tool in your Python arsenal.

Happy coding with Peewee and SQL!