sql  
Where to Discuss?

Local Group

Preface

Goal: Playing SQL with Soccer Case. SQLAlchemy ORM.

With SQLAlchemy we can retrieve data programatically in python. With this data we can show the data in website using Flask. And download the report as excel spreadsheet or PDF and so on. For further use, we can mine statistic for complex data.

However we need to start on something. Instead of starting from rendering web pages, I always start with data structure.

Official Documentation

And of course, start from official documentation.

My article below would only give you brief example, so we can moved on to web based using flask. For reference on how to do SQLAlchemy, you need to carefully reading the official documentation.


Display Result

How about begin from simple code?

Connection String

The first to know is how to make connection string.

from sqlalchemy import create_engine

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

You should see official documentation for reference.

Raw Data

Retrieving data in SQLAlchemy is as simply as:

from sqlalchemy import create_engine
from sqlalchemy import text

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

with engine.connect() as conn:
  stmt   = "SELECT * FROM People LIMIT 5"
  result = conn.execute(text(stmt))

  print(result.keys())
  print(result.all())

With the result as.

❯ cd soccer
❯ python 01-soccer.py
RMKeyView(['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')]

SQAlchemy: Connection: Execute

Formatting Text

You can use ra = result.all(), so that the result can be reuse.

then you can use the legendary f format.

from sqlalchemy import create_engine
from sqlalchemy import text

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

with engine.connect() as conn:
  stmt   = "SELECT * FROM People LIMIT 5"
  result = conn.execute(text(stmt))

  ra = result.all()

  for row in ra:
    print(f"id: {row.id}  "\
          f"name: {row.name}  "\
          f"age: {row.age}  "\
          f"gender: {row.gender}")
❯ python 02-soccer.py
id: 1  name: Takumi Sato  age: 17  gender: Male
id: 2  name: Sakura Yamamoto  age: 18  gender: Female
id: 3  name: Rajesh Patel  age: 16  gender: Male
id: 4  name: dr. Komal Sharma  age: 26  gender: Female
id: 5  name: Jian Chen  age: 15  gender: Male

SQAlchemy: Format Result

This could be useful for automation, for example to extract from oldschool database, that do not have capability to dump SQL.

Tabulate

I prefer ready to use library. But this is a little bit tricky at first, because we also have to get the column header using keys().

from sqlalchemy import create_engine
from sqlalchemy import text
from tabulate import tabulate
from termcolor import colored

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

with engine.connect() as conn:
  stmt   = "SELECT * FROM People LIMIT 5"
  result = conn.execute(text(stmt))

  ks = result.keys()
  ra = result.all()

SQAlchemy: Result keys as tabulate’s column header

Then we can freely show in tabulated form.

  print(colored('PSQL', 'red',
    attrs=['bold']))
  print(tabulate(ra, headers=ks,
    tablefmt='psql'))
  print()

  print(colored('Github', 'green',
    attrs=['bold']))
  print(tabulate(ra, headers=ks,
    tablefmt='github'))
  print()

  print(colored('Rounded Outline', 'blue',
    attrs=['bold']))
  print(tabulate(ra, headers=ks, 
    tablefmt='rounded_outline'))
  print()

SQAlchemy: Show result in tabulation

Tabulate has many forms, here we can display three form kinds.

This one looks like classic SQL output format.

PSQL
+------+------------------+-----------------------------+-------+----------+
|   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     |
+------+------------------+-----------------------------+-------+----------+

If you are working with markdown you are going to love this.

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

And this looks like modern terminal user interface.

Rounded Outline
╭──────┬──────────────────┬─────────────────────────────┬───────┬──────────╮
│   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     │
╰──────┴──────────────────┴─────────────────────────────┴───────┴──────────╯

All the result can be seen here.

SQAlchemy: Tabulate Result


Using View

The VIEW is set in database level. So, using view is just like using any other table.

Complex Statement

Consider this script, but without the SQL statement

from sqlalchemy import create_engine, text
from tabulate import tabulate

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

with engine.connect() as conn:
  stmt = ...<long SQL statement>...

  result = conn.execute(text(stmt))

  ks = result.keys()
  ra = result.all()

  print(tabulate(ra, headers=ks, tablefmt='psql'))

SQAlchemy: Connection: All Code

The original statement can be as complex as below:

with engine.connect() as conn:
  stmt = 'SELECT '\
    + '  People.name, People.age, '\
    + '  People.gender, Seats.name as seat '\
    + 'FROM People '\
    + 'INNER JOIN PlayersSeats '\
    + '  ON People.id = PlayersSeats.person_id '\
    + 'INNER JOIN Roles '\
    + '  ON PlayersSeats.role_id = Roles.id '\
    + 'INNER JOIN Seats '\
    + '  ON PlayersSeats.seat_id = Seats.id; '\

SQAlchemy: Connection: Statement

❯ python 06a-soccer.py
+-----------------+-------+----------+------------+
| name            |   age | gender   | seat       |
|-----------------+-------+----------+------------|
| Takumi Sato     |    17 | Male     | Captain    |
| Jian Chen       |    15 | Male     | Striker    |
| Vladimir Ivanov |    15 | Male     | Winger     |
| Yusuf Abdullah  |    17 | Male     | Winger     |
| Andi Suharto    |    15 | Male     | Midfielder |
| Marco Rossi     |    17 | Male     | Midfielder |
| Gustav Andersen |    15 | Male     | Defender   |
| Ahmad Rahman    |    17 | Male     | Defender   |
| Nur Hidayah     |    16 | Male     | Striker    |
| Joko Susilo     |    15 | Male     | Goalkeeper |
| Huda Al-Farsi   |    15 | Male     | Goalkeeper |
| Tetsuya Suzuki  |    18 | Male     | Back       |
| Akira Kato      |    16 | Male     | Back       |
| Ravi Singh      |    15 | Male     | Striker    |
| Nikolai Ivanov  |    15 | Male     | Winger     |
+-----------------+-------+----------+------------+

SQAlchemy: Connection: Tabulate Result

Simpler Statement

Again, consider this script, now with the SQL statement

from sqlalchemy import create_engine, text
from tabulate import tabulate

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

with engine.connect() as conn:
  stmt = 'SELECT name, age, gender, seat '\
       + 'FROM Players'
  result = conn.execute(text(stmt))

  ks = result.keys()
  ra = result.all()

  print(tabulate(ra, headers=ks, tablefmt='psql'))

SQAlchemy: Connection: All Code

With VIEW this statement can be as simple as:

  stmt = 'SELECT name, age, gender, seat '\
       + 'FROM Players'

This will produce about the same result.

SQAlchemy: Session Query: Tabulate Result


What is Next 🤔?

We need to explore more about class in SQLAlchemy.

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

Thank you for reading.