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.


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)

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])

With Django, fetching all columns of all Countries:


With SQLA, selecting only name of countries:

s = select([])

Django ORM way:


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([]).where( == 'India')

With Django,


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

s = select([]).where( != 'India')

With Django,


Advanced filtering

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

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

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)

Django way

from django.db.models import Count

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(]).group_by(Brand.c.country_id)

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([, func.count(]).group_by(

Django way:


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


We want to find maximum id in the Country table

max_id = func.max(
s = select([max_id])

Django ORM:

from django.db.models import Max

We want to find distinct continent names.

SQLA way:

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

Django ORM:


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