Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Povídání o SQLAlchemy

Povídání o SQLAlchemy

Představení knihovny SQLAlchemy. Přednáška pro lednové PyVo 2012.

Petr Viktorin

January 28, 2012
Tweet

More Decks by Petr Viktorin

Other Decks in Programming

Transcript

  1. “Disproving the myth of ‘the best database layer is the

    one that makes the database invisible’ is a primary philosophy of SA.
  2. “Disproving the myth of ‘the best database layer is the

    one that makes the database invisible’ is a primary philosophy of SA. If you don't want to deal with SQL, then there's little point to using a database in the first place.”
  3. Definice tabulek from sqlalchemy import (Table, Column, Integer, String, MetaData,

    ForeignKey) metadata = MetaData() users = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('fullname', String), ) addresses = Table('addresses', metadata, Column('id', Integer, primary_key=True), Column('user_id', None, ForeignKey('users.id')), Column('email_address', String, nullable=False) )
  4. Dotazování from sqlalchemy import create_engine from sqlalchemy.sql import select engine

    = create_engine('sqlite:///:memory:', echo=True) conn = engine.connect() s = select([users]) result = conn.execute(s) for row in result: print row (1, u'jack', u'Jack Jones') (2, u'wendy', u'Wendy Williams') (3, u'fred', u'Fred Flintstone') (4, u'mary', u'Mary Contrary')
  5. Dotazování s1 = select([users]) s1 = s1.filter(users.c.name == 'fred') s2

    = select([users]) s2 = s2.filter(users.c.id >= 3) s3 = select([users.c.name, users.c.fullname]) s4 = select([users, addresses], users.c.id==addresses.c.user_id)
  6. Dotazování s = select([(users.c.fullname + ", " + addresses.c.email_address).label('title')], and_(

    users.c.id==addresses.c.user_id, users.c.name.between('m', 'z'), or_( addresses.c.email_address.like('%@aol.com'), addresses.c.email_address.like('%@msn.com') ) ) )
  7. Dotazování s = select([(users.c.fullname + ", " + addresses.c.email_address).label('title')], and_(

    users.c.id==addresses.c.user_id, users.c.name.between('m', 'z'), or_( addresses.c.email_address.like('%@aol.com'), addresses.c.email_address.like('%@msn.com') ) ) ) Dotazy a výrazy jsou objekty – dá se s nimi dál pracovat
  8. Architektura SQLAlchemy SQLAlchemy Core SQLAlchemy ORM Object-Relational Mapper Schema, Types

    Expression Language Engine Connection Pool Expression Dialect DBAPI
  9. Deklarativní model from sqlalchemy import Column, Integer, String, ForeignKey from

    sqlalchemy.orm import relationship, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) user_id = Column(None, ForeignKey('users.id')) email_address = Column(String, nullable=False) user = relationship("User", backref=backref('addresses', order_by=id))
  10. Unit of Work from sqlalchemy.sql import select from sqlalchemy.orm import

    sessionmaker Session = sessionmaker(bind=engine) session = Session() ed_user = User(name='ed', fullname='Ed Jones') session.add(ed_user) session.commit()
  11. ORM je nadstavba nad Core >>> User.__table__ Table('users', MetaData(None), Column('id',

    Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>),
  12. “The Tao of SQLAlchemy” SQL databases behave less like object

    collections the more size and performance start to matter Object collections behave less like tables and rows the more abstraction starts to matter
  13. Zdroje http://www.sqlalchemy.org http://spyced.blogspot.com/2009/05/ belated-2009-introduction-to-sqlalchemy.html http://www.sqlalchemy.org/blog/2011/09/19/ sqlalchemy-at-pygotham/ Obrázky na pozadí ©

    Blye Widmar (CC BY-SA 3.0) http://notshurly.deviantart.com/gallery/23303244 Šířeno pod licencí Creative Commons Attribution-ShareAlike 3.0 http://creativecommons.org/licenses/by-sa/3.0/