SQLAlchemy Intro

Ulrich Petri @ulope

PyUGRM Treffen #16 2014-11-12

Was ist SQLAlchemy?

ORM (Object Relational Mapper)

"Übersetzt" zwischen Objekten (Python) und relationaler Speicherung (Datenbank)

In [1]:
from IPython.display import HTML
er_svg = """<svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="435px" height="276px" version="1.1"><defs/><g transform="translate(0.5,0.5)"><rect x="1" y="1" width="160" height="140" fill="#d0d0d0" stroke="#d0d0d0" transform="translate(2,3)" opacity="1"/><rect x="1" y="1" width="160" height="140" fill="#d4e1f5" stroke="#003366" pointer-events="none"/><g transform="translate(1,1)"><switch><foreignObject pointer-events="all" width="162" height="142" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 12px; font-family: Helvetica; color: rgb(0, 51, 102); line-height: 1.26; vertical-align: top; width: 160px; height: 140px; white-space: nowrap;"><p style="margin: 0px ; margin-top: 4px ; text-align: center ; text-decoration: underline"><strong>book</strong></p><hr /><p style="margin: 0px ; margin-left: 8px">id</p><p style="margin: 0px ; margin-left: 8px">title</p><p style="margin: 0px ; margin-left: 8px">published</p><p style="margin: 0px ; margin-left: 8px">publisher</p></div></foreignObject><text x="81" y="77" fill="#003366" text-anchor="middle" font-size="12px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><rect x="271" y="1" width="160" height="140" fill="#d0d0d0" stroke="#d0d0d0" transform="translate(2,3)" opacity="1"/><rect x="271" y="1" width="160" height="140" fill="#d4e1f5" stroke="#003366" pointer-events="none"/><g transform="translate(271,1)"><switch><foreignObject pointer-events="all" width="162" height="142" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 12px; font-family: Helvetica; color: rgb(0, 51, 102); line-height: 1.26; vertical-align: top; width: 160px; height: 140px; white-space: nowrap;"><p style="margin: 0px ; margin-top: 4px ; text-align: center ; text-decoration: underline"><strong>author</strong></p><hr /><p style="margin: 0px ; margin-left: 8px">id</p><p style="margin: 0px ; margin-left: 8px">name</p></div></foreignObject><text x="81" y="77" fill="#003366" text-anchor="middle" font-size="12px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><rect x="141" y="181" width="160" height="90" fill="#d0d0d0" stroke="#d0d0d0" transform="translate(2,3)" opacity="1"/><rect x="141" y="181" width="160" height="90" fill="#d4e1f5" stroke="#003366" pointer-events="none"/><g transform="translate(141,181)"><switch><foreignObject pointer-events="all" width="162" height="92" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 12px; font-family: Helvetica; color: rgb(0, 51, 102); line-height: 1.26; vertical-align: top; width: 160px; height: 90px; white-space: nowrap;"><p style="margin: 0px ; margin-top: 4px ; text-align: center ; text-decoration: underline"><strong>book_author</strong></p><hr /><p style="margin: 0px ; margin-left: 8px">book_id</p><p style="margin: 0px ; margin-left: 8px">author_id</p></div></foreignObject><text x="81" y="52" fill="#003366" text-anchor="middle" font-size="12px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><path d="M 125 233 L 91 233 Q 81 233 81 223 L 81 143" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><path d="M 140 233 L 133 237 L 125 233 L 133 229 Z" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><path d="M 88 155 L 81 142 L 75 155" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><g transform="translate(80,211)"><switch><foreignObject pointer-events="all" width="2" height="15" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 11px; font-family: Helvetica; color: rgb(0, 0, 0); line-height: 1.26; vertical-align: top; white-space: nowrap; text-align: center;"><div xmlns="http://www.w3.org/1999/xhtml" style="display:inline-block;text-align:inherit;text-decoration:inherit;background-color:#ffffff;"><br /></div></div></foreignObject><text x="1" y="13" fill="#000000" text-anchor="middle" font-size="11px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><g transform="translate(72,148)"><switch><foreignObject pointer-events="all" width="8" height="14" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 10px; font-family: Helvetica; color: rgb(0, 0, 0); line-height: 1.26; vertical-align: top; white-space: nowrap; text-align: right;"><div xmlns="http://www.w3.org/1999/xhtml" style="display:inline-block;text-align:inherit;text-decoration:inherit;background-color:#ffffff;">1</div></div></foreignObject><text x="4" y="12" fill="#000000" text-anchor="middle" font-size="10px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><path d="M 317 226 L 341 226 Q 351 226 351 216 L 351 143" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><path d="M 302 226 L 309 222 L 317 226 L 309 230 Z" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><path d="M 358 155 L 351 142 L 345 155" fill="none" stroke="#000000" stroke-miterlimit="10" pointer-events="none"/><g transform="translate(350,202)"><switch><foreignObject pointer-events="all" width="2" height="15" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 11px; font-family: Helvetica; color: rgb(0, 0, 0); line-height: 1.26; vertical-align: top; white-space: nowrap; text-align: center;"><div xmlns="http://www.w3.org/1999/xhtml" style="display:inline-block;text-align:inherit;text-decoration:inherit;background-color:#ffffff;"><br /></div></div></foreignObject><text x="1" y="13" fill="#000000" text-anchor="middle" font-size="11px" font-family="Helvetica">[Not supported by viewer]</text></switch></g><g transform="translate(342,148)"><switch><foreignObject pointer-events="all" width="8" height="14" requiredFeatures="http://www.w3.org/TR/SVG11/feature#Extensibility"><div xmlns="http://www.w3.org/1999/xhtml" style="display: inline-block; font-size: 10px; font-family: Helvetica; color: rgb(0, 0, 0); line-height: 1.26; vertical-align: top; white-space: nowrap; text-align: right;"><div xmlns="http://www.w3.org/1999/xhtml" style="display:inline-block;text-align:inherit;text-decoration:inherit;background-color:#ffffff;">1</div></div></foreignObject><text x="4" y="12" fill="#000000" text-anchor="middle" font-size="10px" font-family="Helvetica">[Not supported by viewer]</text></switch></g></g></svg>"""
In [2]:
HTML(er_svg)
Out[2]:

book


id

title

published

publisher

[Not supported by viewer]

author


id

name

[Not supported by viewer]

book_author


book_id

author_id

[Not supported by viewer]

[Not supported by viewer]
1
[Not supported by viewer]

[Not supported by viewer]
1
[Not supported by viewer]
In [3]:
from contextlib import closing
from pprint import pprint

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, scoped_session, sessionmaker, backref


Base = declarative_base()
In [4]:
class Author(Base):
    __tablename__ = 'author'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(300), nullable=False)
    
    def book_count(self):
        return self.books.count()

    def __repr__(self):
        return (u"<Author(id={s.id}, name=\"{s.name}\", "
                "book_count={bc})>").format(s=self, bc=self.book_count())
In [5]:
class Book(Base):
    __tablename__ = 'book'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    published = Column(Integer, nullable=False)
    publisher = Column(String(100))

    authors = relationship(Author, secondary='book_author', 
                           backref=backref('books', lazy='dynamic'), 
                           cascade='all', lazy='dynamic')
    
    def __repr__(self):
        return (u"<Book(id={s.id}, title=\"{s.title}\", "
                "published={s.published})>").format(s=self)
In [6]:
book_author = Table(
    'book_author',
    Base.metadata,
    Column('book_id', Integer, 
           ForeignKey('book.id', ondelete='CASCADE'), 
           primary_key=True),
    Column('author_id', Integer, 
           ForeignKey('author.id', ondelete='CASCADE'), 
           primary_key=True)
)
In [7]:
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
In [8]:
adams = Author(name="Douglas Adams")
print adams
<Author(id=None, name="Douglas Adams", book_count=0)>

In [9]:
thgttg = Book(title="The Hitchhiker's Guide to the Galaxy", 
              published=1979, 
              authors=[adams])
print thgttg
<Book(id=None, title="The Hitchhiker's Guide to the Galaxy", published=1979)>

In [10]:
print adams
<Author(id=None, name="Douglas Adams", book_count=1)>

In [11]:
with closing(Session()) as session:
    with session.transaction:
        session.add(adams)
    print adams
    print thgttg
<Author(id=1, name="Douglas Adams", book_count=1)>
<Book(id=1, title="The Hitchhiker's Guide to the Galaxy", published=1979)>

In [12]:
book2 = Book(title="The Restaurant at the End of the Universe", 
             published=1980, 
             authors=[adams])
book3 = Book(title="Life, the Universe and Everything", 
             published=1982, 
             authors=[adams])

author2 = Author(name="Daniel Greenfeld")
author3 = Author(name="Audrey Roy")
book4 = Book(title="Two Scoops of Django: Best Practices For Django 1.6", 
             published=2014, 
             authors=[author2, author3])
In [13]:
with closing(Session()) as session:
    with session.transaction:
        session.add(book2)
        session.add(book3)
        session.add(book4)
In [14]:
with closing(Session()) as session:
    pprint(session.query(Author).all())
[<Author(id=1, name="Douglas Adams", book_count=3)>,
 <Author(id=2, name="Daniel Greenfeld", book_count=1)>,
 <Author(id=3, name="Audrey Roy", book_count=1)>]

In [15]:
with closing(Session()) as session:
    pprint(session
           .query(Book)
           .order_by(Book.published.desc())
           .all())
[<Book(id=4, title="Two Scoops of Django: Best Practices For Django 1.6", published=2014)>,
 <Book(id=3, title="Life, the Universe and Everything", published=1982)>,
 <Book(id=2, title="The Restaurant at the End of the Universe", published=1980)>,
 <Book(id=1, title="The Hitchhiker's Guide to the Galaxy", published=1979)>]

In [16]:
with closing(Session()) as session:
    pprint(
        session
            .query(Book)
            .filter(Book.published >= 1980, 
                    Book.published < 2000)
            .order_by(Book.published.desc())
            .all()
    )
[<Book(id=3, title="Life, the Universe and Everything", published=1982)>,
 <Book(id=2, title="The Restaurant at the End of the Universe", published=1980)>]

In [17]:
print Book.published >= 2010 
book.published >= :published_1