Preface
Goal: Playing SQL with Soccer Case. SQLAlchemy ORM.
Instead of regular statement, we can use reflection with ORM.
Simple Reflection
There is other way to connect, using table reflection.
people = Table('People', metadata, autoload=True)
The complete preparation is as below:
from sqlalchemy import (
create_engine, MetaData, Table)
from tabulate import tabulate
str_conn = "sqlite+pysqlite:///./soccer.db"
engine = create_engine(str_conn)
metadata = MetaData()
metadata.reflect(bind=engine)
people = Table('People', metadata, autoload=True)
Now we can build statement using ORM. For example, I want to find somebody with sepcific ID.
with engine.connect() as conn:
stmt = people.select().where(people.c.id == 7)
result = conn.execute(stmt)
row = result.fetchall()
print(row)
ks = result.keys()
print(tabulate(row, headers=ks,
tablefmt='rounded_outline'))
Result in CLI
The result is as below box.
❯ python 04-soccer.py
[(7, 'Vladimir Ivanov', 'vladimir.ivanov@example.com', 15, 'Male')]
╭──────┬─────────────────┬─────────────────────────────┬───────┬──────────╮
│ id │ name │ email │ age │ gender │
├──────┼─────────────────┼─────────────────────────────┼───────┼──────────┤
│ 7 │ Vladimir Ivanov │ vladimir.ivanov@example.com │ 15 │ Male │
╰──────┴─────────────────┴─────────────────────────────┴───────┴──────────╯
All the result can be seen here.
Reflection with Join
From this small soccer topic, we can dig out a few more examples.
Source Code
The source code can be obtained here:
Engine
As usual… prepare the engine.
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Table, select
from tabulate import tabulate
# create a SQLite engine
str_conn = "sqlite+pysqlite:///./soccer.db"
engine = create_engine(str_conn)
Metadata
This time we have three tables.
# create a SQLAlchemy metadata object
metadata = MetaData()
metadata.reflect(bind=engine)
# reflect the existing tables
people = Table('People',
metadata, autoload=True)
resps = Table('Responsibilities',
metadata, autoload=True)
staff = Table('StaffResps',
metadata, autoload=True)
Statement
With table reflections above, we can build our SQL statement.
stmt = people.select().\
join(staff, people.c.id == staff.c.person_id).\
join(resps, resps.c.id == staff.c.resp_id).\
where(resps.c.name == 'Doctor');
Fetch
Now we can fetch as usual,
and feed the result into tabulate
library.
with engine.connect() as conn:
result = conn.execute(stmt)
ks = result.keys()
ra = result.fetchall()
print(tabulate(ra, headers=ks))
Result in CLI
The result is as below box.
❯ python 07a-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
Join Statement
We can write above statement differently.
Source Code
The source code can be obtained here:
Engine
The engine is the same as above script.
Metadata
metadata = MetaData()
metadata.reflect(bind=engine)
people = metadata.tables['People']
resps = metadata.tables['Responsibilities']
staff = metadata.tables['StaffResps']
Statement
join_stmt = people
join_stmt = join_stmt.join(staff,
people.c.id == staff.c.person_id)
join_stmt = join_stmt.join(resps,
resps.c.id == staff.c.resp_id)
stmt = select(people.c.email, resps.c.name)
stmt = stmt.select_from(join_stmt)
stmt = stmt.where(resps.c.name == 'Doctor');
Fetch
The fetch code is the same as previous script.
Result in CLI
The result is as below box.
❯ python 07b-soccer.py
email name
------------------------ ------
komal.sharma@example.com Doctor
dr.johnson@example.com Doctor
What is Next 🤔?
We need to explore more about SQL statement.
Consider continue reading [ Soccer - SQL Alchemy - Part Three ].
Thank you for reading.