sql  
Where to Discuss?

Local Group

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)

SQAlchemy: Using reflection table

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

SQAlchemy: Fecth result using ORM

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.

SQAlchemy: Tabulate Result


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)

SQAlchemy: Reflection: Engine

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)

SQAlchemy: Reflection: Metadata

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');

SQAlchemy: Reflection: Statement

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

SQAlchemy: Reflection: Fetch

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

SQAlchemy: Tabulate Result


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

SQAlchemy: Reflection: Metadata

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');

SQAlchemy: Reflection: Statement

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

SQAlchemy: Tabulate Result


What is Next 🤔?

We need to explore more about SQL statement.

Consider continue reading [ Soccer - SQL Alchemy - Part Three ].

Thank you for reading.