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 tableYou 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__()
methodAll 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