Index   ¦   Tags   ¦   Archives

SQLAlchemy queries in Django

Django's ORM is great for 99% of the common web development use cases. Every now and then, however, a bit more flexibility would go a long way and help stay out of the raw SQL rabbit hole. For example, reporting queries are difficult to build with Django's API and could benefit from a little more abstraction.

In this respect, I believe that SQLAlchemy's SQL Expression Engine allows for significant flexibility with neat and reusable code. In this piece, we will look at how to write queries with the Expression Engine and have them executed by Django's DB engine without additional configuration.

Integrating Django and SQLAlchemy

The great aldjemy package makes the initial integration painless, and sets up SQLAlchemy to reuse Django's DB connection.

However, its README focuses on on ORM-style queries using SQLAlchemy's own ORM API. Instead, let's look at how to extract the table information to run our own queries built using the expression engine.

from aldjemy.core import get_tables, get_engine
from sqlalchemy.sql import select

# for the default DB
engine = get_engine()
# for another DB
engine = get_engine('tracking')

# a dict with DB tables
tables = get_tables()

# each table can now be accessed to build queries:
widgets = tables['widgets']

query = select([
            widget.c.id,
            widget.c.name])

Running the query

Since the engine object is already available, we can just open a connection and run the query:

conn = engine.connect()
result = conn.execute(query)

As an added note, I've found Pandas to be very useful in these kinds of situations. In fact, easy column and row-level operations at the Python level nicely complement the flexibility in data retrieval afforded by SQLAlchemy.

When using Pandas, fetching the query results into a DataFrame is even easier:

data_frame = pandas.read_sql_query(query, engine)

Under the hood

While Django doesn't support connection pooling, it obviously has its own transaction system which is syncronized with the request/response cycle. Aldjemy plugs into this with the core.DjangoPool class which operates as a SQLAlchemy NullPool that piggybacks on Django's connections. Each connection is wrapped in a wrapper.Wrapper to disable SQLAlchemy's handling of transactions and rely on Django's.

Tables are generated through reflection of Django's models in the tables.generate_tables function. Aldjemy keeps a mapping of Django field types to SQLAlchemy types and iterates through each model to add the relevant columns to its table.

Conclusions

We have seen how to use SQLAlchemy's SQL Expression Language to run complex queries on Django's database without needing to create additional connections or manually define the database schema.

© Nicolò Valigi. Built using Pelican. Theme originally by Giulio Fidente on github.