chemistry-glassware

Use SQLAlchemy with Django

Why wouldn’t we use Django ORM?

Using Django ORM is not suited for real database operations. Sure, it might look okay(ish) when you are only performing basic CRUD operations. But it will force you to perform database queries in the least optimized way (see N+1 SELECT issue)…

Its syntax is inconsistent, and its lack of support for many SQL features (including JOIN) will make you go back to raw SQL queries as soon as things get tricky. Going back to raw SQL feels a bit like a failure when you are using an ORM, plus it is not easy to maintain.

So, what shall we do?

There is a lot of other ORMs for Python around:

  • SQLAlchemy – has a lot of functionnalities, thanks to its huge community
  • Peewee – very nicely written, lacks community
  • Pony ORM – wonderful syntax, is not free

Of course there is more of them, but I focused on the most popular ones.

I chose SQLAlchemy, because it is free, has a huge set of features, supports many dialects (such as PostgreSQL, MySQL…), and the project is quite mature.

How do we use SQLAlchemy with Django?

Installation

First, setup Aldjemy from your virtual environment (this will install SQLAlchemy as well):

pip install aldjemy

(for Python 3 and PostgreSQL’s HSTORE support, install the package manually from this repository)

In your Django application’s settings script, add the following item to the INSTALLED_APPS tuple:

'aldjemy',

Usage

Now let’s imagine we have a module called myapp.models, in which we defined three Django models (namely Blog, Author and Entry). The following declaration will make those entities available as SQLAlchemy models:

import myapp.models
Blog = myapp.models.Blog.sa
Author = myapp.models.Author.sa
Entry = myapp.models.Entry.sa

To access SQLAlchemy’s session object, define this little function:

def Session():
    from aldjemy.core import get_engine
    engine = get_engine()
    _Session = sessionmaker(bind=engine)
    return _Session()

Then simply call:

session = Session()

You can now perform SQLAlchemy queries within the Django ORM. Congratulations!

8 thoughts on “Use SQLAlchemy with Django

  1. Hi, I just wanted to let you know that while peewee doesn’t have near the community of Django or SQLAlchemy, it does have a devoted following! I also try to be very responsive to GitHub issues. Peewee also comes with a special extension module designed for working with Django — you might find it helpful should you ever try peewee:

    * http://charlesleifer.com/blog/-djpeewee-use-the-peewee-orm-with-your-django-models/
    * http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#djpeewee

  2. What about transactions? In this example what happend?
    (this is pseudocode)

    with atomic():
        # update with django orm
        x = mymodel.objects.get(pk=1)
        x.field+=1
        x.save()
        # Here the x.field changed?
        x.sa.read_field
    
  3. @Anton: I don’t think accessing objects both from Django ORM and SQLAlchemy inside the same code (method, class, etc.) would be recommended. This post intends to provide SQLAlchemy to those who need to achieve complex queries inside a Django project.

  4. Hi Mathieu,

    I’m a beginning software engineer learning Django. I followed your tutorial up to adding ‘aldjemy’ to INSTALLED_APPS in the settings.py file. Does the code in the “Usage” category of your tutorial also need to go in the settings.py file? Many thanks for your help, and for the instructions!

    Erin

    • The “Usage” code doesn’t go in settings.py, but rather where you want to use it (e.g., in a script where you wish to use your models).

Leave a Reply to Jack Guo Cancel reply