Úvod do SQLAlchemy ORM 

Co je to SQLAlchemy?

• sada nástrojů pro práci s SQL databázemi

• MySQL
• PostgreSQL
• Oracle
• MSSQL
• SQLite
• ...

• jednotné API
• součástí je ORM framework

Co je to ORM?

• Object Relational Mapping

SQLAlchemy

• podpora různých dialektů (DBAPIs)

from sqlalchemy import create_engine

# vytvoření enginu pro SQLite
engine = create_engine('sqlite:///:memory:')

# vytvoření enginu pro MySQL
engine = create_engine(
    'mysql://scott:tiger@localhost/test',
    isolation_level='READ UNCOMMITTED',  # úroveň izolace
)

# vytvoření enginu pro PostgreSQL
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/test')

SQLAlchemy CORE

• sada nástrojů pro práci s databází bez ORM

# katalog všech definovaných tabulek
metadata = MetaData()

# definice tabulky users
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)

# vytvoření tabulek (CREATE TABLE ...)
metadata.create_all(engine)

# otevření nového spojení spojení k DB
conn = engine.connect()

# vytvoření nového uživatele
conn.execute(users.insert().values(name='jan', fullname='Jan Novak'))

SQLAlchemy ORM

• sada nástrojů pro práci s databází s ORM

# katalog všech definovaných tabulek
Base = declarative_base()

# definice modelu users (dědí Base)
class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)

# vytvoření tabulek (CREATE TABLE ...)
Base.metadata.create_all(engine)

# vytvoření modelu uživatele
user = User(name='jan', fullname='Jan Novak')

# přidání do session (další slide) a commit
session.add(user)
session.commit(user)

SQLAlchemy Session

• udržuje modely synchronizované s DB
• spousta magie ⇒ spoustu problémů, které se špatně debugují

# příprava továrny
session_factory = sessionmaker(bind=engine)

# vytvoření nové session
session = session_factory()

# scoped session!
session = scoped_session(session_factory)

Příklady dotazů

• WHERE name='prema'

session.query(User).filter(User.name == 'prema')

Příklady dotazů

• WHERE name='prema'

session.query(User).filter(User.name == 'prema')

• WHERE fullname NOT LIKE '%kar%'

session.query(User).filter(~User.name.like('%kar%')

Příklady dotazů

• WHERE name='prema'

session.query(User).filter(User.name == 'prema')

• WHERE fullname NOT LIKE '%kar%'

session.query(User).filter(~User.name.like('%kar%')

• GROUP BY user_id HAVING count(*) > 4

session.query(Eshop).group_by(Eshop.user_id).having(func.count('*') > 4))

 Vykonání SELECT dotazu

• načtení všech objektů do paměti

session.query(User).all()

 Vykonání SELECT dotazu

• načtení všech objektů do paměti

session.query(User).all()

• načtení prvního objektu

session.query(User).first()
session.query(User).get(24)  # podle primárního klíče

 Vykonání SELECT dotazu

• načtení všech objektů do paměti

session.query(User).all()

• načtení prvního objektu

session.query(User).first()
session.query(User).get(24)  # podle primárního klíče

• iterace ve for-cyklu

for user in session.query(User):
    print(user.name)

UPDATE a DELETE

• aktualizace

# s načtením celého objektu do paměti
user.name = 'mergado'
session.add(user)
session.commit()

# bez načtení do paměti
session.query(User).filter(User.id == 23).update({User.name: 'mergado'})

UPDATE a DELETE

• aktualizace

# s načtením celého objektu do paměti
user.name = 'mergado'
session.add(user)
session.commit()

# bez načtení do paměti
session.query(User).filter(User.id == 23).update({User.name: 'mergado'})

• odstranění

# s načtením celého objektu do paměti
session.delete(user)
session.commit()

# bez načtení do paměti
session.query(User).filter(User.id == 23).delete()

Spojování tabulek

• definice pomocí relationship

class User(Base):
    # ...
    eshops = relationship(Eshop)  # one to many

user = sessinon.query(User).get(2)
for eshop in user.eshops:
    print(eshop.name)

Spojování tabulek

• definice pomocí relationship

class User(Base):
    # ...
    eshops = relationship(Eshop)  # one to many

user = sessinon.query(User).get(2)
for eshop in user.eshops:
    print(eshop.name)

• explicitní JOIN

# SELECT user.* FROM user INNER JOIN eshop ...
session.query(User).join(Eshop).filter(Eshop.name = 'Mironet.cz')

# SELECT project.*, log.* FROM project INNER JOIN log ...
session.query(Project, Log).join(Log, Project.id == Log.wtf_id)

# SELECT project.* FROM project LEFT OUTER JOIN log ...
session.query(Project).outerjoin(Log, Project.id == Log.wtf_id)

V SA ORM jde všechno!

• UNION, INTERSECT, WITH, ...

• subqueries

• pracnější je CREATE VIEW

V SA ORM jde všechno!

query = session.query(all_columns())  # vznikne KeyedTuple
for Model in models:
    columns = get_columns(Model)
    query = query.outerjoin(
        Model,
        pg.and_(cls.set_id == Model.set_id,
                cls.item_id == Model.item_id)
    ).add_columns(*columns)
return query

• UNION, INTERSECT, WITH, ...

• subqueries

• pracnější je CREATE VIEW

• spojení tabulek ve for-cyklu:

... až na FULL JOIN

• Chris Whithers:
  "This feels like a newbie question, but how would I do a full outer join in SQLAlchemy?"

... až na FULL JOIN

• Chris Whithers:
  "This feels like a newbie question, but how would I do a full outer join in SQLAlchemy?"
• Michael Bayer (autor SQLAlchemy):
  "I have a similar question - why?"

... až na FULL JOIN

• Chris Whithers:
  "This feels like a newbie question, but how would I do a full outer join in SQLAlchemy?"
• Michael Bayer (autor SQLAlchemy):
  "I have a similar question - why?"
• Chris Whithers:
  "I have a self join [...]"

... až na FULL JOIN

• Chris Whithers:
  "This feels like a newbie question, but how would I do a full outer join in SQLAlchemy?"
• Michael Bayer (autor SQLAlchemy):
  "I have a similar question - why?"
• Chris Whithers:
  "I have a self join [...]"
• Michael Bayer:
  "[...] full outer join isn’t in the library but you can just subclass Join and do a @compiles to get that exact syntax."
• :-)

Záludnosti

• nezapomínat uzavírat session - v Postgresu pak uváznou dotazy na "IDLE in transaction"

Záludnosti

• nezapomínat uzavírat session - v Postgresu pak uváznou dotazy na "IDLE in transaction"
• stejný objekt nesmí být ve dvou session naráz -
  InvalidRequestError: Object '<Project at 0x7f13d4260b90>' is already attached to session '1' (this is '2')

Záludnosti

• nezapomínat uzavírat session - v Postgresu pak uváznou dotazy na "IDLE in transaction"
• stejný objekt nesmí být ve dvou session naráz -
  InvalidRequestError: Object '<Project at 0x7f13d4260b90>' is already attached to session '1' (this is '2')
• expire_on_commit=True - jakmile změny zapíšete, označí se všechny objekty jako "expired", příštím přístupem k vlastnosti jiného objektu (např. user2.name) se nejdříve zavolá SELECT ...

Twitter/GitHub: @paveldedik