Ú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

Made with Slides.com