Relations

Relations#

In this page I want to describe the mechanism of sqlalchemy that allows to perform sql JOIN operation in a really natural for python way. For each “main” table, you can define related essentials and you’ll be able to access them only from queries on the “main” table.

Prepare#

In the following cell we have just defined basic things for sqlalchemy.

!docker run -d --rm\
    --name relations_example\
    -e POSTGRES_PASSWORD=postgres\
    -p 5000:5432\
    postgres:15.4 &> /dev/null
!sleep 5

from sqlalchemy import (
    create_engine, 
    Column, 
    Integer,
    String,
    ForeignKey
)
from sqlalchemy.orm import (
    sessionmaker, 
    declarative_base, 
    relationship
)
from random import randint

URL = "postgresql://postgres:postgres@localhost:5000/postgres"
engine = create_engine(URL)

SessionLocal = sessionmaker(
    autoflush=False,
    autocommit=False,
    bind=engine
)
session = SessionLocal()

# defining data model
Base = declarative_base()

Data model#

Data model is a key feature of this page. There are:

  • MainTable, which contains

    • Some information that is unique to each record - id and text_var;

    • Some information that can be mapped to the other describe table;

  • DescribeTable - auxiliary table that describes certain states of the MainTable.

So we need a mechanism to tell sqlalcemy that it needs to join some infromation from DescribeTable into MainTable.

With DescribeTable it’s very simple, you just define primary key and text_var that simulate some content to be joined.

MainTable has:

  • id as primary key;

  • text_var that simulate some content;

  • describe_id in its declaration contains ForeignKey which indicates which field of their third-party table this field is associated with;

  • describe is a field where objects of DescribeTable class will be written to so that they can be accessed.

class DescribeTable(Base):
    __tablename__="describe_table"
    id = Column(Integer, primary_key=True)
    text_var=Column(String)

class MainTable(Base):
    __tablename__="main_table"
    id = Column(Integer, primary_key=True)
    text_var = Column(String)
    describe_id = Column(
        Integer, ForeignKey("describe_table.id")
    )
    describe = relationship("DescribeTable")

# duplicate datamodel in the database
Base.metadata.create_all(engine)

Filling of the table#

Below content of the tables is created. Few records that defines mapping for the descriptions. And some random content for the MainTable.

for i in range(2):
    session.add(DescribeTable(
        text_var = f"descrion {i+1}"
    ))

for i in range(10):
    session.add(MainTable(
        text_var = "".join([chr(randint(97,107)) for i in range(10)]),
        describe_id = randint(1,2)
    ))

session.commit()

%%bash
docker exec -i relations_example psql -U postgres -d postgres
SELECT * FROM describe_table;
SELECT * FROM main_table;

 id |  text_var  
----+------------
  1 | descrion 1
  2 | descrion 2
(2 rows)

 id |  text_var  | describe_id 
----+------------+-------------
  1 | kciihbjiii |           1
  2 | cgdebdabhh |           1
  3 | bichegaghb |           2
  4 | kkaidfdiki |           1
  5 | icicbkcihj |           1
  6 | eijbdfjkff |           1
  7 | dhacdkgahd |           2
  8 | hhiebijhbc |           2
  9 | dccdgfkiee |           2
 10 | fhbgideidg |           2
(10 rows)

Query#

An object that describes the entity of the linked table has been declared in the main table, it is through this object that the content of the linked table can be retrieved.

So despite the fact that the query was made in MainTable the corresponding data from DescribeTable are automatically pulled into the results.

for main_table_instance in session.query(MainTable).all():
    print("============================")
    print(
        "text_var:", main_table_instance.text_var, "\n"
        "describe_id:", main_table_instance.describe_id, "\n"
        "description content:", main_table_instance.describe.text_var
    )

============================
text_var: kciihbjiii 
describe_id: 1 
description content: descrion 1
============================
text_var: cgdebdabhh 
describe_id: 1 
description content: descrion 1
============================
text_var: bichegaghb 
describe_id: 2 
description content: descrion 2
============================
text_var: kkaidfdiki 
describe_id: 1 
description content: descrion 1
============================
text_var: icicbkcihj 
describe_id: 1 
description content: descrion 1
============================
text_var: eijbdfjkff 
describe_id: 1 
description content: descrion 1
============================
text_var: dhacdkgahd 
describe_id: 2 
description content: descrion 2
============================
text_var: hhiebijhbc 
describe_id: 2 
description content: descrion 2
============================
text_var: dccdgfkiee 
describe_id: 2 
description content: descrion 2
============================
text_var: fhbgideidg 
describe_id: 2 
description content: descrion 2

Stop container#

!docker stop relations_example

relations_example
Contents