8.18. SQLAlchemy Model Define

  • Models inherits from Base

  • Models represents objects in a database

  • They are the most important entity in the project

8.18.1. SetUp

>>> from sqlalchemy import create_engine
>>> from sqlalchemy.orm import sessionmaker, declarative_base
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>>
>>> engine = create_engine(DATABASE)
>>> session = sessionmaker(bind=engine).__call__()
>>> Base = declarative_base()

8.18.2. Good practices

  • Model, View, Controller Pattern (MVC)

  • Fat Model, thin controller, thin view (best)

  • Thin model, fat controller, thin view (bad)

  • Thin model, thin controller, fat view (worst)

  • Model name should be the same as database table name

8.18.3. Table Specification

  • Models uses __tablename__ class attribute to specify reflected table

  • You can use legacy database to work on (database was created in the past now we use models to reflect the schema in order to query it)

  • Preferable is to use declarative SQLAlchemy database and table creation

  • Use only models and migrations to alter and version schema

>>> class User(Base):
...     __tablename__ = 'user'

8.18.4. Column Specification

>>> from sqlalchemy import Column, String, Integer
>>> class User(Base):
...     __tablename__ = 'user'
...
...     username = Column(String)
...     password = Column(String)

8.18.5. Initializer

  • Initializer is an __init__() method

  • All models has default initializer method

  • You can create initializer explicitly, which will overload the default one

>>> class User(Base):
...     __tablename__ = 'user'
...     username = Column(String, primary_key=True)
...     password = Column(String)

SQLAlchemy applies a default initializer (__init__) method, to all mapped classes that don't explicitly have their own __init__ method. The behavior of this method is such that it provides a convenient keyword constructor that will accept as optional keyword arguments all the attributes that are named. The constructor also applies to imperative mappings [1].

Note, that this must be keyword arguments. Positional argument won't work.

8.18.6. Stringification

  • __str__() method

  • __repr__() method

8.18.7. Create Schema

In order to create tables in database you have to call create_all() method of Base.metadata object and pass engine instance.

>>> Base.metadata.create_all(engine)

8.18.8. Create Model Instances

In order to create object simply instantiate it passing proper arguments. Creating instances will not modify anything in a database. If you want to store information in database you have to commit manually.

If any constraints will fail, the IntegrityError exception will be raised. This may happen for example if field should have unique values (like username) and we create two users with the same username.

Creating objects:

>>> mark = User('Mark', 'Watney')
>>> session.add(mark)
>>> session.commit()

You can also create two objects within one session (transaction). Mind, that both of those objects will be saved to database in the same time, as soon as the .commit() method is called.

>>> mark = User('Mark', 'Watney')
>>> melissa = User('Melissa', 'Lewis')
>>>
>>> session.add(mark)
>>> session.add(melissa)
>>>
>>> session.commit()

8.18.9. Use Case - 1

>>> from sqlalchemy import create_engine
>>> from sqlalchemy import Column, String, Integer
>>> from sqlalchemy.orm import sessionmaker, declarative_base
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>>
>>> engine = create_engine(DATABASE)
>>> session = sessionmaker(bind=engine).__call__()
>>> Base = declarative_base()
>>>
>>>
>>> class User(Base):
...     __tablename__ = 'user'
...     username = Column(String, primary_key=True)
...     password = Column(String)
>>>
>>>
>>> Base.metadata.create_all(engine)
>>>
>>>
>>> mark = User(firstname='Mark', lastname='Watney')
>>> melissa = User(firstname='Melissa', lastname='Lewis')
>>>
>>> session.add(mark)
>>> session.add(melissa)
>>>
>>> session.commit()

8.18.10. References

8.18.11. Assignments

# FIXME: Write tests

# %% About
# - Name: Model Define User
# - Difficulty: medium
# - Lines: 20
# - Minutes: 13

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Create model `User` with fields:
#    - firstname - first name
#    - lastname - last name
#    - birthdate - date of birth
#    - ssn - PESEL
#    - email - email address
#    - phone - phone with country code
# 2. Functional requirements:
#    - User has only one email and one phone
# 3. Non-functional requirements:
#    - Use SQLAlchemy ORM to create models
#    - Add `id` fields if needed
#    - You can use any module from standard library
#    - You can use SQLAlchemy and Alembic
#    - Do not install or use any additional packages

# %% Polish
# 1. Stwórz model `User` z polami:
#    - firstname - imię
#    - lastname - nazwisko
#    - birthdate - data urodzenia
#    - ssn - PESEL
#    - email - adres email
#    - phone - telefon z numerem kierunkowym kraju
# 2. Wymagania funkcjonalne:
#    - Użytkownik ma tylko jeden email i jeden telefon
# 3. Wymagania niefunkcjonalne:
#    - Użyj SQLAlchemy ORM do stworzenia modeli
#    - Dodaj pola `id` jeżeli są potrzebne
#    - Możesz użyć dowolnego modułu z biblioteki standardowej
#    - Możesz użyć SQLAlchemy i Alembic
#    - Nie instaluj ani nie używaj dodatkowych pakietów

# %% Doctests
"""
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types

# %% Data

# %% Result

# FIXME: Write tests

# %% About
# - Name: Model Define UserAddress
# - Difficulty: medium
# - Lines: 20
# - Minutes: 13

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Create model `User` with fields:
#    - firstname - first name
#    - lastname - last name
#    - birthdate - date of birth
#    - ssn - PESEL
#    - email - email address
#    - phone - phone number with country code
# 2. Create model `Address` with fields:
#    - type - address type: billing, delivery
#    - street - street, house number, apartment number
#    - postcode - postal code
#    - city - city
#    - region - region or state
#    - country - country
# 3. Functional requirements:
#    - Address may not have street or postal code
# 4. Non-functional requirements:
#    - Use SQLAlchemy ORM to create models
#    - Add `id` fields if needed
#    - You can use any module from standard library
#    - You can use SQLAlchemy and Alembic
#    - Do not install or use additional packages

# %% Polish
# 1. Stwórz model `User` z polami:
#    - firstname - imię
#    - lastname - nazwisko
#    - birthdate - data urodzenia
#    - ssn - PESEL
#    - email - adres email
#    - phone - telefon z numerem kierunkowym kraju
# 2. Stwórz model `Address` z polami:
#    - type - rodzaj adresu: rozliczeniowy, dostawy
#    - street - ulica, numer domu, numer mieszkania
#    - postcode - kod pocztowy
#    - city - miasto
#    - region - województwo lub stan
#    - country - kraj
# 3. Wymagania funkcjonalne:
#    - Adres może nie mieć ulicy lub kodu pocztowego
# 4. Wymagania niefunkcjonalne:
#    - Użyj SQLAlchemy ORM do stworzenia modeli
#    - Dodaj pola `id` jeżeli są potrzebne
#    - Możesz użyć dowolnego modułu z biblioteki standardowej
#    - Możesz użyć SQLAlchemy i Alembic
#    - Nie instaluj ani nie używaj dodatkowych pakietów

# %% Doctests
"""
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types

# %% Data

# %% Result

# FIXME: Write tests

# %% About
# - Name: Model Define ProductOrders
# - Difficulty: medium
# - Lines: 20
# - Minutes: 13

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% English
# 1. Create model `User` with fields:
#    - firstname - first name
#    - lastname - last name
#    - birthdate - date of birth
#    - ssn - PESEL
#    - email - email address
#    - phone - phone with country code
# 2. Create model `Address` with fields:
#    - type - type of address: billing, delivery
#    - street - street, house number, apartment number
#    - postcode - postal code
#    - city - city
#    - region - region or state
#    - country - country
# 3. Create model `Product`:
#    - ean13 - EAN-13 barcode
#    - name - Product name
#    - price - Net price
# 4. Create model `Orders`:
#    - user - User
#    - product - Product
# 5. Functional requirements:
#    - User has only one email and one phone
#    - User can have one billing address and one delivery address
#    - Address may not have a street or postal code
#    - User can buy many products
#    - Product could not be purchased
# 6. Non-functional requirements:
#    - Use SQLAlchemy ORM to create models
#    - Add `id` fields if needed
#    - You can use any module from the standard library
#    - You can use SQLAlchemy and Alembic
#    - Do not install or use additional packages

# %% Polish
# 1. Stwórz model `User` z polami:
#    - firstname - imię
#    - lastname - nazwisko
#    - birthdate - data urodzenia
#    - ssn - PESEL
#    - email - adres email
#    - phone - telefon z numerem kierunkowym kraju
# 2. Stwórz model `Address` z polami:
#    - type - rodzaj adresu: rozliczeniowy, dostawy
#    - street - ulica, numer domu, numer mieszkania
#    - postcode - kod pocztowy
#    - city - miasto
#    - region - województwo lub stan
#    - country - kraj
# 3. Stwórz model `Product`:
#    - ean13 - Kod kreskowy EAN-13
#    - name - Nazwa produktu
#    - price - Cena netto
# 4. Stwórz model `Orders`:
#    - user - Użytkownik
#    - product - Produkt
# 5. Wymagania funkcjonalne:
#    - Użytkownik ma tylko jeden email i jeden telefon
#    - Użytkownik może mieć jeden adres rozliczeniowy i jeden do wysyłki
#    - Adres może nie mieć ulicy lub kodu pocztowego
#    - Użytkownik może zakupić wiele produktów
#    - Produkt mógł nie zostać kupiony
# 6. Wymagania niefunkcjonalne:
#    - Użyj SQLAlchemy ORM do stworzenia modeli
#    - Dodaj pola `id` jeżeli są potrzebne
#    - Możesz użyć dowolnego modułu z biblioteki standardowej
#    - Możesz użyć SQLAlchemy i Alembic
#    - Nie instaluj ani nie używaj dodatkowych pakietów

# %% Doctests
"""
"""

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% Imports

# %% Types

# %% Data

# %% Result