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')]
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
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()
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()
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.
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'))
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; '\
❯ 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 |
+-----------------+-------+----------+------------+
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'))
With VIEW this statement can be as simple as:
stmt = 'SELECT name, age, gender, seat '\
+ 'FROM Players'
This will produce about the same 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.