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
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))
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()
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'))
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 |
+------+----------------------+----------------------------------+-------+----------+
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
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()
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.
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.
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.
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
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)
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)
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))
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
Conclusion
It works on my machine
Thank you for reading.
What do you think?