I need to write a complex query, which retrieves a lot of data from a bunch of tables. Basically I need to find all instances of the models
for c, p, i in session.query(Customer, Payment, Invoice).\
# Do stuff ...
customers = Customer.objects.filter(...)
payments = Payment.objects.filter(customer=customer)
invoices = Invoice.objects.filter(customer=customer, payment_set=payments)
customers = Customer.objects.filter(...).prefetch_related(
After reading up on different solutions, I have decided to use SqlAlchemy on top of my Django models. Some people try to completely replace the Django ORM with SqlAlchemy, but this almost completely defeats the purpose of using Django, since most of the framework relies on the ORM.
Instead, I use SqlAlchemy simple for querying the tables defined by the Django ORM. I follow a recipe similar to this
# Setup sqlalchemy bindings import sqlalchemy as s from sqlalchemy.orm import sessionmaker engine = s.create_engine('postgresql://<user>:<password>@<host>:<port>/<db_name>') # Automatically read the database tables and create metadata meta = s.MetaData() meta.reflect(bind=engine) Session = sessionmaker(bind=engine) # Create a session, which can query the tables session = Session() # Build table instances without hardcoding tablenames s_payment = meta.tables[models.Payment()._meta.db_table] s_allocation = meta.tables[models.Allocation()._meta.db_table] s_customer = meta.tables[models.Customer()._meta.db_table] s_invoice = meta.tables[models.Invoice()._meta.db_table] report = session.query(s_payment.c.amount, ...).all()
There is room for a few improvements on this recipe, e.g. it is not very elegant to create an empty instance of Django models in order to find their table name, however, with a few lines of code, I get the full flexibility of SqlAlchemy without compromising with the Django ORM layer. This means both can live happily alongside each other.
One caveat is that SqlAlchemy will not use the same connection as the Django ORM, which means that the view of things may not appear consistent if I use both approaches in the same context. This won't be a problem for me though, since I just want to read a bunch of data from the database.