from sqlalchemy import create_engine, ForeignKey
 
from sqlalchemy import Column, Date, Integer, String
 
from sqlalchemy.ext.declarative import declarative_base
 
from sqlalchemy.orm import relationship, backref
 
from sqlalchemy.orm import sessionmaker
 
import datetime
 
Base = declarative_base()
 
class Singleton(object):
 
    '''
 
    Singelton class
 
    '''
 
    def __init__(self, decorated):
 
        self._decorated = decorated
 
    def instance(self, *args, **kwargs):
 
        try:
 
            return self._instance
 
        except AttributeError:
 
            self._instance = self._decorated(*args, **kwargs)
 
            return self._instance
 
    def __call__(self, *args, **kwargs):
 
        raise TypeError('Singletons must be accessed through the `Instance` method.')
 
@Singleton
 
class Db(object):
 
    '''
 
    The DB Class should only exits once, thats why it has the @Singleton decorator.
 
    To Create an instance you have to use the instance method:
 
        db = Db.instance()
 
    '''
 
    engine = None
 
    session = None
 
    def __init__(self):
 
        self.engine = create_engine('sqlite:////tmp/funnydatabase.db', echo=True)
 
        Session = sessionmaker(bind=self.engine)
 
        self.session = Session()
 
        ## Create all Tables
 
        Base.metadata.create_all(self.engine)
 
    def instance(self, *args, **kwargs): 
 
        '''
 
        Dummy method, cause several IDEs can not handel singeltons in Python
 
        '''
 
        pass
 
class Model():
 
    '''
 
    This is a baseclass with delivers all basic database operations
 
    '''
 
    def save(self):
 
        db = Db.instance()
 
        db.session.add(self)
 
        db.session.commit()
 
    def saveMultiple(self, objects = []):
 
        db = Db.instance()
 
        db.session.add_all(objects)
 
        db.session.commit()
 
    def update(self):
 
        db = Db.instance()
 
        db.session.commit()
 
    def delete(self):
 
        db = Db.instance()
 
        db.session.delete(self)
 
        db.session.commit()
 
    def queryObject(self):
 
        db = Db.instance()
 
        return db.session.query(self.__class__)
 
class Artist(Model,Base):
 
    """"""
 
    __tablename__ = "artists"
 
    id = Column(Integer, primary_key=True)
 
    name = Column(String)  
 
class Album(Model,Base):
 
    """"""
 
    __tablename__ = "albums"
 
    id = Column(Integer, primary_key=True)
 
    title = Column(String)
 
    release_date = Column(Date)
 
    publisher = Column(String)
 
    media_type = Column(String)
 
    artist_id = Column(Integer, ForeignKey("artists.id"))
 
    artist = relationship("Artist", backref=backref("albums", order_by=id))
 
###### HERE STARTS THE PROGRAM ######
 
## Initialize the whole Database.
 
db = Db.instance()
 
## Create an artist
 
new_artist = Artist("Kittie")
 
new_artist.albums = [Album("I've Failed You", 
 
                           datetime.date(1988,12,01),
 
                           "Blubb", "CD")]
 
## add more albums
 
more_albums = [Album("Album 2",
 
                     datetime.date(1990,07,31),
 
                     "Blobb", "CD"),
 
               Album("Album 3", 
 
                     datetime.date(1999,11,16),
 
                     "Blebb", "CD")]
 
new_artist.albums.extend(more_albums)
 
## Adding The new Artist to the DB
 
new_artist.save()
 
## Query for an artist
 
x = Artist()
 
qo = x.queryObject()
 
y = qo.filter(Artist.name=="Kittie").first()
 
## Delete the Artist
 
if y: y.delete()
 
## Add severall Artists
 
x.saveMultiple([
 
    Artist("Stone Sour"),
 
    Artist("Metallica"),
 
    Artist("Porcupine Tree")
 
    ])