SQLAlchemy is a popular Python ORM (Object Relational Mapping) library that provides a high-level, Pythonic interface for interacting with databases. While SQLAlchemy provides a powerful Query API for most common database operations, there might be situations where you need to execute raw SQL queries.

In this blog post, we will explore how to execute raw SQL queries using SQLAlchemy in Python. Let’s dive in!

Connecting to the Database

Before we start executing raw SQL queries, we need to establish a connection to the database using SQLAlchemy. Assuming you have already installed SQLAlchemy, here’s an example snippet to connect to a PostgreSQL database:

from sqlalchemy import create_engine

# Replace <db_url> with your actual database URL
db_url = "postgresql://username:password@localhost:5432/mydatabase"
engine = create_engine(db_url)

# Establish a connection
connection = engine.connect()

Make sure to replace <db_url> with your actual database URL, including the username, password, hostname, port, and database name.

Executing Raw SQL Queries

Once we have established a connection to the database, we can execute raw SQL queries using the execute() method provided by the SQLAlchemy Connection object. The execute() method accepts a string containing the SQL query and returns a result object.

Here’s an example of executing a raw SQL query that selects all rows from a table:

result = connection.execute("SELECT * FROM users")

Retrieving Results

After executing the raw SQL query, you can retrieve the results using various methods provided by the result object. For example, to fetch all rows, you can use the fetchall() method:

rows = result.fetchall()
for row in rows:
    print(row)

Alternatively, if you want to fetch only a single row, you can use the fetchone() method:

row = result.fetchone()
print(row)

Parameterized Queries

To execute parameterized queries, where you pass values dynamically into the SQL query, you can use SQLAlchemy’s parameter binding feature. This helps prevent SQL injection attacks and ensures proper handling of data types.

Here’s an example of executing a parameterized query using the execute() method with named parameters:

name = "John"
result = connection.execute("SELECT * FROM users WHERE name = :name", name=name)

In this example, we bind the value of the name parameter using named parameter syntax :name and pass it as a keyword argument name=name in the execute() method.

Closing the Connection

After executing raw SQL queries, it’s important to close the connection to release the resources. You can close the connection using the close() method:

connection.close()

Conclusion

SQLAlchemy provides a convenient way to execute raw SQL queries in Python, allowing you to harness the full power of SQL while still benefiting from the ORM features. However, it’s important to use raw SQL queries judiciously and prefer the SQLAlchemy ORM where possible.

I hope this blog post helps you execute raw SQL queries using SQLAlchemy in Python. Happy coding!