sql  
Where to Discuss?

Local Group

Preface

Goal: Playing SQL with Soccer Case. SQLAlchemy ORM.

With SQLAlchemy, the SQL statement can be written, as either regular text or in object, s ORM (Objet Relational Mapper).

Consider continue previous example, using session query with help of People class.


Session Query: Simple Example

Here is a simple example of ORM.

Source Code

The source code can be obtained here:

Import

As usual, we declare required libraries.

from sqlalchemy import (
  create_engine, Table, Column, Integer, String)
from sqlalchemy.orm import (
  sessionmaker, DeclarativeBase)

from tabulate import tabulate

SQAlchemy: Session Query: Import

Class

Then, we declare class for People table.

class Base(DeclarativeBase):
  pass

class People(Base):
  __tablename__ = 'People'

  id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  email = Column(String(100), nullable=False)
  age = Column(Integer)
  gender = Column(String(50))

SQAlchemy: Session Query: Class

Initialization

Here is where we declare the session.

str_conn = "sqlite+pysqlite:///./soccer.db"
engine   = create_engine(str_conn)

Session = sessionmaker(bind=engine)
session = Session()

SQAlchemy: Session Query: Initial

Also getting the column head, to be displayed in tabular data.

keys = People.metadata\
  .tables['People'].columns.keys()

Running Query

For this to work with tabulate library, we should manually translate the row result, using list comprehension.

result = session.query(People).all()
rows = [
  (p.id, p.name, p.email, p.age, p.gender)
  for p in result]

print(tabulate(rows, headers=keys,
  tablefmt='psql'))

SQAlchemy: Session Query: Result

Result in CLI

The result is a little bit long.

❯ python 05a-soccer.py
+------+----------------------+----------------------------------+-------+----------+
|   id | name                 | email                            |   age | gender   |
|------+----------------------+----------------------------------+-------+----------|
|    1 | Takumi Sato          | takumi.sato@example.com          |    17 | Male     |
|    2 | Sakura Yamamoto      | sakura.yamamoto@example.com      |    18 | Female   |
|    3 | Rajesh Patel         | rajesh.patel@example.com         |    16 | Male     |
|    4 | dr. Komal Sharma     | komal.sharma@example.com         |    26 | Female   |
|    5 | Jian Chen            | jian.chen@example.com            |    15 | Male     |
|    6 | Yan Liu              | yan.liu@example.com              |    17 | Female   |
|    7 | Vladimir Ivanov      | vladimir.ivanov@example.com      |    15 | Male     |
|    8 | Ekaterina Petrova    | ekaterina.petroova@example.com   |    26 | Female   |
|    9 | Yusuf Abdullah       | yusuf.abdullah@example.com       |    17 | Male     |
|   10 | Fatima Al-Khalifa    | fatima.alkhalifa@example.com     |    18 | Female   |
|   11 | Andi Suharto         | andi.suharto@example.com         |    15 | Male     |
|   12 | Lia Wijaya           | lia.wijaya@example.com           |    16 | Female   |
|   13 | Marco Rossi          | marco.rossi@example.com          |    17 | Male     |
|   14 | Alessia Bianchi      | alessia.bianchi@example.com      |    16 | Female   |
|   15 | Gustav Andersen      | gustav.andersen@example.com      |    15 | Male     |
|   16 | Maria Svensson       | maria.svensson@example.com       |    18 | Female   |
|   17 | Ahmad Rahman         | ahmad.rahman@example.com         |    17 | Male     |
|   18 | Nur Hidayah          | nur.hidayah@example.com          |    16 | Male     |
|   19 | Joko Susilo          | joko.susilo@example.com          |    15 | Male     |
|   20 | Ratih Wijayanti      | ratih.wijayanti@example.com      |    17 | Female   |
|   21 | Abdullah Al-Bakr     | abdullah.albakr@example.com      |    16 | Male     |
|   22 | Huda Al-Farsi        | huda.alfarsi@example.com         |    15 | Male     |
|   23 | Tetsuya Suzuki       | tetsuya.suzuki@example.com       |    18 | Male     |
|   24 | Akira Kato           | akira.kato@example.com           |    16 | Male     |
|   25 | Ravi Singh           | ravi.singh@example.com           |    15 | Male     |
|   26 | Nikolai Ivanov       | nikolai.ivanov@example.com       |    15 | Male     |
|   27 | dr. Johnson Bun      | dr.johnson@example.com           |    50 | Male     |
|   28 | Wilson Weasley       | busdriver.wilson@example.com     |    37 | Male     |
|   29 | Smith Sonian         | coach.smith@example.com          |    40 | Male     |
|   30 | Kim Kwan             | physio.kim@example.com           |    35 | Male     |
+------+----------------------+----------------------------------+-------+----------+

SQAlchemy: Session Query: Tabulate Result

We are going to play with different result, with just one People table.


Session Query: More Example

This is basically just the same as regular SQL expression. But this time we wrap SQL statement as ORM.

Source Code

The source code can be obtained here:

Import

from sqlalchemy import (
  create_engine, Table, Column, Integer, String)
from sqlalchemy.orm import (
  sessionmaker, DeclarativeBase)

from tabulate import tabulate
from termcolor import colored

SQAlchemy: Session Query: Import

Class

The class declaration is exactly the same as above.

Initial

# Initialization
str_conn = "sqlite+pysqlite:///./soccer.db"
engine   = create_engine(str_conn)

Session = sessionmaker(bind=engine)
session = Session()

keys = People.metadata\
  .tables['People'].columns.keys()

SQAlchemy: Session Query: Initial

Lambda

:(\

Since we have reusable line to extraxt row. It is better to use anonymous function.

person_row = lambda p : (\
  p.id, p.name, p.email, p.age, p.gender)

The backlash is not an expression, but rather change line.

Case 01

Let’s pick random name. Such as person with id 8 in people table.

print(colored('case 1', 'red', attrs=['bold']))
person = session.query(People).filter_by(id=8)\
 .first()

row = person_row(person)
print(tabulate([row], headers=keys,
  tablefmt='psql'))
print()

You can see how the lambda work. And also how the tabulate works with only row.

SQAlchemy: Session Query: Case 01

The result in CLI displayed as below table:

❯ python 05b-soccer.py
case 1
+------+-------------------+-------------------------------+-------+----------+
|   id | name              | email                         |   age | gender   |
|------+-------------------+-------------------------------+-------+----------|
|    8 | Ekaterina Petrova | ekaterina.petrova@example.com |    26 | Female   |
+------+-------------------+-------------------------------+-------+----------+

Case 02

Let’s get the first seven people that come out from query.

print(colored('case 2', 'green', attrs=['bold']))
all_persons = session.query(People)\
  .limit(7).all()

rows = [person_row(u) for u in all_persons]
print(tabulate(rows, headers=keys,
  tablefmt='psql'))
print()

You can see how the lambda work with list comprehension.

SQAlchemy: Session Query: Case 02

The result in CLI displayed as below table:

case 2
+------+------------------+-----------------------------+-------+----------+
|   id | name             | email                       |   age | gender   |
|------+------------------+-----------------------------+-------+----------|
|    1 | Takumi Sato      | takumi.sato@example.com     |    17 | Male     |
|    2 | Sakura Yamamoto  | sakura.yamamoto@example.com |    18 | Female   |
|    3 | Rajesh Patel     | rajesh.patel@example.com    |    16 | Male     |
|    4 | dr. Komal Sharma | komal.sharma@example.com    |    26 | Female   |
|    5 | Jian Chen        | jian.chen@example.com       |    15 | Male     |
|    6 | Yan Liu          | yan.liu@example.com         |    17 | Female   |
|    7 | Vladimir Ivanov  | vladimir.ivanov@example.com |    15 | Male     |
+------+------------------+-----------------------------+-------+----------+

Case 03

Let’s find person with specific e-mail address.

print(colored('case 3', 'blue', attrs=['bold']))
persons_with_email = session.query(People)\
  .filter_by(email='coach.smith@example.com')\
  .all()

rows = [person_row(u) for u in persons_with_email]
print(tabulate(rows, headers=keys,
  tablefmt='psql'))
print()

The lambda with list comprehension, is really reducing amount of code.

SQAlchemy: Session Query: Case 03

The result in CLI displayed as below table:

case 3
+------+--------------+-------------------------+-------+----------+
|   id | name         | email                   |   age | gender   |
|------+--------------+-------------------------+-------+----------|
|   29 | Smith Sonian | coach.smith@example.com |    40 | Male     |
+------+--------------+-------------------------+-------+----------+

Session Query: Multiple Classess

We could do more.

ORM is also good for complex query.

Source Code

The source code can be obtained here:

Prepare

Import and Base Class

from sqlalchemy import (
  create_engine, ForeignKey, 
  Table, Column, Integer, String)
from sqlalchemy.orm import (
  sessionmaker, relationship, DeclarativeBase)

from tabulate import tabulate

Along with class definition

class Base(DeclarativeBase):
  pass

SQAlchemy: Session Query: Import

Classes

We have three different custom classes.

It is not hard to make this class, if we can have the CREATE TABLE SQL statement.

class People(Base):
  __tablename__ = 'People'

  id = Column(Integer, primary_key=True)
  name = Column(String(255), nullable=False)
  email = Column(String(100), nullable=False)
  age = Column(Integer)
  gender = Column(String(50))
class Resp(Base):
  __tablename__ = 'Responsibilities'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False)
class StaffResps(Base):
  __tablename__ = 'StaffResps'

  person_id = Column(Integer,
    ForeignKey('People.id'), primary_key=True)
  resp_id = Column(Integer,
    ForeignKey('Responsibilities.id'),
    primary_key=True)

SQAlchemy: Session Query: Classes

We have more classes, but for this article, I only provide three.

Initialization

We have talk about this. This is very common. Nothing special

str_conn = "sqlite+pysqlite:///./soccer.db"
engine   = create_engine(str_conn)

Session = sessionmaker(bind=engine)
session = Session()

keys = People.metadata\
  .tables['People'].columns.keys()

person_row = lambda p : (\
  p.id, p.name, p.email, p.age, p.gender)

SQAlchemy: Session Query: Initialization

We can change the columnn in the lambda, to suit our need.

Statement

Example Case

Finally the ORM statement.

Select people with doctor role!

doctors = session.query(People)\
  .join(StaffResps)\
  .join(Resp)\
  .filter_by(name='Doctor')\
  .all()

rows = [person_row(u) for u in doctors]
print(tabulate(rows, headers=keys))

SQAlchemy: Session Query: Statement

It turned out to be short.

Result in CLI

With the result as below:

❯ python 08-soccer.py
  id  name              email                       age  gender
----  ----------------  ------------------------  -----  --------
   4  dr. Komal Sharma  komal.sharma@example.com     26  Female
  27  dr. Johnson Bun   dr.johnson@example.com       50  Male

SQAlchemy: Session Query: Result in CLI


Conclusion

It works on my machine

Thank you for reading.

What do you think?