Skip to content

Instantly share code, notes, and snippets.

@chroto
Created April 16, 2012 07:06
Show Gist options
  • Save chroto/2396842 to your computer and use it in GitHub Desktop.
Save chroto/2396842 to your computer and use it in GitHub Desktop.
SA Bookstore
"""
# Bookstore
- Book => Images
- Book <=> Author
- Book <=> Genre
- Book -> Genre (Primary)
"""
from sqlalchemy import (
Column,
String,
Table,
ForeignKeyConstraint,
ForeignKey,
and_,
Integer
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
book_author = Table('_book_author', Base.metadata,
Column('book_isbn', String(10), ForeignKey('book.isbn')),
Column('author_id', Integer, ForeignKey('author.id'))
)
book_genre = Table('_book_genre', Base.metadata,
Column('book_isbn', String(10), ForeignKey('book.isbn')),
Column('genre_name', String, ForeignKey('genre.name'))
)
class Genre(Base):
__tablename__ = 'genre'
name = Column(String, primary_key=True)
def __repr__(self):
if self.id:
return 'Genre(id={self.id!r}, name={self.name!r})'.format(self=self)
return 'Genre(name={self.name!r})'.format(self=self)
class Book(Base):
__tablename__ = 'book'
isbn = Column(String(10), primary_key=True)
title = Column(String)
primary_genre_name = Column(String)
__table_args__ = (
ForeignKeyConstraint(
[isbn, primary_genre_name],
[book_genre.c.genre_name, book_genre.c.book_isbn],
name="fk_primary_genre", use_alter=True
),
)
authors = relationship("Author", secondary=book_author, backref="books")
genres = relationship(Genre,
secondary=book_genre,
primaryjoin=isbn==book_genre.c.book_isbn,
secondaryjoin=book_genre.c.genre_name==Genre.name,
backref='books')
images = relationship("Image", backref="book")
primary_genre = relationship(Genre, secondary=book_genre,
primaryjoin=and_(
isbn==book_genre.c.book_isbn,
primary_genre_name==book_genre.c.genre_name
),
secondaryjoin=book_genre.c.genre_name==Genre.name,
backref='primary_for',
uselist=False)
def __repr__(self):
return 'Book(title={self.title!r}, isbn={self.isbn!r}>'.format(self=self)
class Author(Base):
__tablename__ = 'author'
id = Column(Integer, primary_key=True)
name = Column(String)
books = relationship(Book,
secondary=book_author,
primaryjoin=Author.id==book_genre.c.author_id,
secondaryjoin=book_genre.c.book_isbn==isbn,
backref='authors')
def __repr__(self):
if self.id:
return 'Author(id={self.id!r}, name={self.name!r})'.format(self=self)
return 'Author(name={self.name!r})'.format(self=self)
class Image(Base):
__tablename__ = 'image'
name = Column(String)
url = Column(String, primary_key=True)
book_id = Column(String(10), ForeignKey('book.isbn'))
def __repr__(self):
return 'Image(name={self.name!r}, url={self.url!r})'.format(self=self)
__all__ = [Book, Author, Genre, Image]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment