Recently I started using SQLAlchemy and am very impressed with it. I have used Django ORM a lot in the past. This post compares achieving same result¬†using Django and with SQLAlchemy. Let’s see which looks more intuitive.

Setup

Assuming we have a SQLAlchemy table called Country which looks like the following:

Table('country', metadata, Column('name', VARCHAR(100)), Column('continent', VARCHAR(100)), Column('id', INTEGER(), primary_key=True))

And we have a Django model called Country which looks like:

class Country(models.Model):
 name = models.CharField(max_length=100)
 continent = models.CharField(max_length=100)

I am using a PostgreSQL database but you are free to use any database. I already have the corresponding tables created in my db.

For using SQLAlchemy we need to connect to the database:

from sqlalchemy import create_engine, MetaData
connection_string = 'postgresql://akshar:[email protected]:5432/store'
db = create_engine(connection_string)
metadata = MetaData(db)
metadata.reflect(metadata.bind)

Let’s get reference to the table Country.

Country = metadata.tables['country']

Let’s write a helper function which will execute the select statement and return the rows:

def get_rows(s):
 """
 Expects a selectable, executes it and returns the rows.
 """
 rs = db.execute(s)
 return rs.fetchall()

Basic selects

With SQLA(SQLAlchemy), fetching all columns of all Countries looks like:

s = select([Country])
get_rows(s)

With Django, fetching all columns of all Countries:

Country.objects.all()

With SQLA, selecting only name of countries:

s = select([Country.c.name])
get_rows(s)

Django ORM way:

Country.objects.only('name')

But this selects id in addition to name. Log the query, you will find out.

If we only want the name and not id, we need to use .values().

countries = Country.objects.values('name')
names = [country['name'] for country in countries]

Filtering (WHERE clause)

With SQLA, fetching only name column for countries whose name is India.

s = select([Country.c.name]).where(Country.c.name == 'India')
get_rows(s)

With Django,

Country.objects.filter(name='India').values('name')

With SQLA, fetching only name column for countries whose name is not India.

s = select([Country.c.name]).where(Country.c.name != 'India')
get_rows(s)

With Django,

Country.objects.exclude(name='India').values('name')

Advanced filtering

We want to find out all countries where name is India or name is US.

from sqlalchemy import or_
s = select([Country.c.name]).where(or_(Country.c.name == 'India', Country.c.name == 'US'))
get_rows(s)

Django way:

from django.db.models import Q
Country.objects.filter(Q(name='India') | Q(name='US'))

Group by

We want to find number of countries per continent.

from sqlalchemy import func
num_countries = func.count(Country.c.continent)
s = select([num_countries]).group_by(Country.c.continent)
get_rows(s)

Django way

from django.db.models import Count
Country.objects.values('continent').annotate(num_countries=Count('continent'))

Let’s assume there is another table called brand which has Foreign Key to Country. So one Country can have many brands. eg: UK can have brands like Cadbury, Boodles etc. Let’s find number of brands per country:

from sqlalchemy import func
Brand = metadata.tables['brand']
s = select([Brand.c.country_id, func.count(Brand.c.id)]).group_by(Brand.c.country_id)
get_rows(s)

Django ORM:

country_with_num_brands = Brand.objects.values('country_id').annotate(num_brands=Count('country_id'))
# country_with_num_brands[0]['country_id']
# country_with_num_brands[0]['num_brands']

We also want to know the names of countries along with count of brands. This would involve join between the tables.

SQLA way:

s = select([Country.c.name, func.count(Brand.c.id)]).group_by(Country.c.name).select_from(Country.join(Brand))
get_rows(s)

Django way:

Country.objects.annotate(num_brands=Count('brand'))

Though the Django way is concise here but SQLA maps more directly to SQL and it’s less confusing.

Random

We want to find maximum id in the Country table

max_id = func.max(Country.c.id)
s = select([max_id])
get_rows(s)

Django ORM:

from django.db.models import Max
Country.objects.aggregate(max=Max('id'))

We want to find distinct continent names.

SQLA way:

distinct_continents = func.distinct(Country.c.continent)
s = select([distinct_continents])
get_rows(s)

Django ORM:

Country.objects.values('continent').distinct()

So far I found SQLAlchemy great. It maps very closely to SQL constructs like SELECT, FROM, WHERE, GROUP BY etc which is unlike Django.