"Übersetzt" zwischen Objekten (Python) und relationaler Speicherung (Datenbank)
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>"""
HTML(er_svg)
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()
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())
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)
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)
)
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
adams = Author(name="Douglas Adams")
print adams
<Author(id=None, name="Douglas Adams", book_count=0)>
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)>
print adams
<Author(id=None, name="Douglas Adams", book_count=1)>
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)>
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])
with closing(Session()) as session:
with session.transaction:
session.add(book2)
session.add(book3)
session.add(book4)
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)>]
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)>]
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)>]
print Book.published >= 2010
book.published >= :published_1