SQL Expression Language

last modified July 6, 2020

In this part of the SQLAlchemy tutorial, we work with SQLAlchemy's SQL Expression Language.

The SQLAlchemy Expression Language represents relational database structures and expressions using Python constructs. The expression language improves the maintainability of the code by hiding the SQL language and thus allowing not to mix Python code and SQL code.

The Object Relational Mapper, ORM, is built on top of the expression language.

Selecting all rows

In the first example, we use the expression language to select all rows from a table.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars])
    rs = con.execute(stm) 

    print rs.fetchall()

The example uses the select method to retrieve all rows from the Cars table.

meta = MetaData(eng)
cars = Table('Cars', meta, autoload=True)  

We load the definition of the Cars table.

stm = select([cars])

With the select method, we create an SQL SELECT statement. This particular expression selects all columns and rows from the provided table.

rs = con.execute(stm) 

The statement is executed.

print rs.fetchall()

With the fetchall method, we print all returned data.

$ ./exp_select_all.py 
[(1, u'Audi', 52642), (2, u'Mercedes', 57127), (3, u'Skoda', 9000), (4, u'Volvo', 29000), 
(5, u'Bentley', 350000), (6, u'Citroen', 21000), (7, u'Hummer', 41400), (8, u'Volkswagen', 21600)]

This is the output of the example.

Limiting selected output

In the second example, we limit the data retrieved from the table.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars.c.Name, cars.c.Price]).limit(3)
    rs = con.execute(stm) 

    print rs.fetchall()

The example prints two columns of three rows from the Cars table.

stm = select([cars.c.Name, cars.c.Price]).limit(3)

Between the square brackets, we provide the columns that we want to be displayed. The limit method limits the result set to three rows.

$ ./exp_select_limit.py 
[(u'Audi', 52642), (u'Mercedes', 57127), (u'Skoda', 9000)]

This is the output of the exp_select_limit.py program.

The where() method

The where method adds a WHERE clause to the statement generated by the select method.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select, and_    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars]).where(and_(cars.c.Price > 10000, 
                                  cars.c.Price < 40000))
    rs = con.execute(stm) 

    print rs.fetchall()

The example selects all cars whose price is between 10000 and 40000.

stm = select([cars]).where(and_(cars.c.Price > 10000, 
                                cars.c.Price < 40000))

To build the expected SQL statement, we use the select and where methods and the and_ operator.

$ ./exp_select_where.py 
[(4, u'Volvo', 29000), (6, u'Citroen', 21000), (8, u'Volkswagen', 21600)]

The code example prints all Cars with prices between 10000 and 40000.

The like() method

The like method adds a LIKE clause to the statement generated by the select method.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    stm = select([cars]).where(cars.c.Name.like('%en'))
    rs = con.execute(stm) 

    print rs.fetchall()

With the like method, we select all cars whose name ends with 'en'.

stm = select([cars]).where(cars.c.Name.like('%en'))

The like method is applied on the column name.

$ ./exp_select_like.py 
[(6, u'Citroen', 21000), (8, u'Volkswagen', 21600)]

There are two cars with names ending in 'en'.

Ordering rows

The order_by method adds a ORDER BY clause to the statement generated by the select method.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy.sql import select, asc
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    metadata = MetaData(eng)
    cars = Table('Cars', metadata, autoload=True)  

    s = select([cars]).order_by(asc(cars.c.Name))
    rs = con.execute(s) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

The example prints all rows ordered by cars' names.

s = select([cars]).order_by(asc(cars.c.Name))

The order_by methos is given the asc operator, which makes the ordering in ascending way.

$ ./exp_select_order.py
1 Audi 52642
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
2 Mercedes 57127
3 Skoda 9000
8 Volkswagen 21600
4 Volvo 29000

The example prints all rows. The rows are arranged in ascending order by the cars' names.

The in_() operator

The in_ operator is used to add IN clause to the generated SELECT statement.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, MetaData, tuple_
from sqlalchemy.sql import select
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:

    meta = MetaData(eng)
    cars = Table('Cars', meta, autoload=True)  

    k = [(2,), (4,), (6,), (8,)]
    stm = select([cars]).where(tuple_(cars.c.Id).in_(k))
    rs = con.execute(stm) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

The example prints four rows of the table, specified by the in_ operator.

stm = select([cars]).where(tuple_(cars.c.Id).in_(k))

With the help of the tuple_ and in_ operators, we build the statement containing the IN clause.

$ ./exp_select_in.py 
2 Mercedes 57127
4 Volvo 29000
6 Citroen 21000
8 Volkswagen 21600

This is the output of the example.

Creating a table

The next example creates a table in memory using the expression language.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///:memory:')

with eng.connect() as con:
    
    meta = MetaData(eng)
    cars = Table('Cars', meta,
         Column('Id', Integer, primary_key=True),
         Column('Name', String),
         Column('Price', Integer)
    )

    cars.create()

    ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642)
    con.execute(ins1)

    ins2 = cars.insert().values(Id=2, Name='Mercedes', Price=57127)
    con.execute(ins2)
    
    ins3 = cars.insert().values(Id=3, Name='Skoda', Price=6000)
    con.execute(ins3)    

    s = select([cars])
    rs = con.execute(s) 

    for row in rs:
        print row['Id'], row['Name'], row['Price']

The example creates a new table in memory, fills it with data, and executes a SELECT statement on the table.

eng = create_engine('sqlite:///:memory:')

The created table will be SQLite's in-memory table.

meta = MetaData(eng)
cars = Table('Cars', meta,
    Column('Id', Integer, primary_key=True),
    Column('Name', String),
    Column('Price', Integer)
)

We provide the definition of the table.

cars.create()

The table is created using the create method.

ins1 = cars.insert().values(Id=1, Name='Audi', Price=52642)
con.execute(ins1)

With the insert method, we insert a new row into the table.

s = select([cars])
rs = con.execute(s) 

for row in rs:
    print row['Id'], row['Name'], row['Price']

In the final step, we execute a SELECT statement and print all the returned data to the console.

$ ./exp_create_table.py 
1 Audi 52642
2 Mercedes 57127
3 Skoda 6000

This is the output of the example.

Joining tables

In the following example, we join fields from two tables. We use the join method.

#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlalchemy import (create_engine, Table, Column, Integer, 
    String, ForeignKey, MetaData)
from sqlalchemy.sql import select    
    
eng = create_engine('sqlite:///test.db')

with eng.connect() as con:
    
    meta = MetaData(eng)
    
    authors = Table('Authors', meta, autoload=True)
    books = Table('Books', meta, autoload=True)

    stm = select([authors.join(books)])
    rs = con.execute(stm) 

    for row in rs:
        print row['Name'], row['Title']

The example executes an inner join on two tables. We get authors and their corresponding titles.

authors = Table('Authors', meta, autoload=True)
books = Table('Books', meta, autoload=True)

The two tables are loaded from the database.

stm = select([authors.join(books)])

We create a SELECT statement with a JOIN clause.

$ ./exp_join_tables.py 
Jane Austen Emma
Leo Tolstoy War and Peace
Joseph Heller Catch XII
Charles Dickens David Copperfield
Joseph Heller Good as Gold
Leo Tolstoy Anna Karenia

This is the output of the example.

In this part of the SQLAlchemy tutorial, we worked with SQL Expression Language.