Stuff

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Boolean, Date, UniqueConstraintfrom sqlalchemy import create_enginefrom sqlalchemy.orm import relation, scoped_session, sessionmakerimport datetimefrom sqlalchemy.ext.declarative import declarative_baseengine = create_engine('sqlite:///t.db', echo=True)Session = scoped_session(sessionmaker(transactional=True, autoflush=True, bind=engine))metadata = MetaData(bind=engine)Base = declarative_base(metadata=metadata)class PairKeyword(Base):    __tablename__ = "pair_keyword"    id         = Column('id', Integer, primary_key=True)    keyword_id = Column('keyword_id', ForeignKey('keyword.keyword_id'))    pair_id    = Column('pair_id', ForeignKey('pair.pair_id'))    constraint = UniqueConstraint('keyword_id', 'pair_id')class PairAnnotation(Base):    __tablename__ = "pair_annotation"    id            = Column('id', Integer, primary_key=True)    annotation_id = Column('annotation_id', ForeignKey('annotation.annotation_id'))    pair_id       = Column('pair_id', ForeignKey('pair.pair_id'))    constraint    = UniqueConstraint('annotation_id', 'pair_id')class Keyword(Base):    __tablename__ = "keyword"    keyword_id = Column('keyword_id', Integer, primary_key=True)    keyword    = Column('keyword', String(30), unique=True)class Annotation(Base):    __tablename__ = "annotation"    annotation_id = Column('annotation_id', Integer, primary_key=True)    annotation    = Column('annotation', String(30), unique=True)class Feature(Base):    __tablename__ = "feature"    feature_id   = Column('feature_id', Integer, primary_key=True)    feature_type = Column('feature_type', String(24), index=True)    pair_id      = Column('pair_id', Integer, ForeignKey('pair.pair_id'))    name         = Column('name', String(40), index=True)    q_or_s       = Column('q_or_s', String(1))    chromosome   = Column('chromosome', String(15), index=True)    start        = Column('start', Integer, index=True)    stop         = Column('stop',  Integer, index=True)    strand       = Column('strand',  Integer)class Pair(Base):    __tablename__ = "pair"    pair_id      = Column('pair_id', Integer, primary_key=True)    orientation  = Column('orientation', String(20))    date_created = Column('date_created', Date(default=datetime.date.today))    # pair has_a genespace...    # pair has a feature.    genespace    = relation('Genespace', uselist=False, backref="pair")    features    = relation(Feature,  uselist=True,  backref="pair")Pair.keywords  = relation(Keyword        , secondary=PairKeyword.__table__        , primaryjoin=Pair.__table__.c.pair_id==PairKeyword.__table__.c.pair_id        , secondaryjoin=Keyword.__table__.c.keyword_id==PairKeyword.__table__.c.keyword_id)Pair.annotations = relation(Annotation        , secondary=PairAnnotation.__table__        , primaryjoin=Pair.__table__.c.pair_id==PairAnnotation.__table__.c.pair_id        , secondaryjoin=Annotation.__table__.c.annotation_id==PairAnnotation.__table__.c.annotation_id)    #keywords    = relation(class Genespace(Base):    __tablename__ = "genespace"    genespace_id = Column('genespace_id', Integer, primary_key=True)    created_by   = Column('created_by', String(20))    revisit      = Column('revisit', Boolean)    qdups        = Column('qdups', Integer),    sdups        = Column('sdups', Integer),    pair_id      = Column('pair_id', ForeignKey('pair.pair_id'))    #Column('annotation_id', Integer, ForeignKey('annotation.annotation_id))    # keywordskeywords = ('bigfoot', 'appressed', 'duplicate hsps', 'special')annos    = ('missed exon(s)', 'false exon(s) called', 'splits and fusions')                #, 'created new Os loc' , 'created new Sb loc' , 'created new pair')Base.metadata.create_all()if __name__ == "__main__":    from sqlalchemy.exceptions import IntegrityError    for k in keywords:        k = Keyword(keyword=k)        Session.save(k)    for a in annos:        a = Annotation(annotation=a)        Session.save(a)    Session.commit()    try:        k = Keyword(keyword='bigfoot')        Session.save(k)        Session.commit()    except IntegrityError:        print "UniqueConstraint effective"        #Session.delete(k)        Session.clear()        Session.rollback()    p = Pair()    p.features.append(Feature())    p.features.append(Feature())    g = Genespace()    p.genespace = g    a1 = Session.query(Annotation).get_by(annotation='splits and fusions')    a2 = Session.query(Annotation).get_by(annotation='missed exon(s)')    print a1.annotation    print a2.annotation    p.annotations.append(a2)    p.annotations.append(a1)    print dir(p)    Session.save(p)    Session.commit()    print [a.annotation for annotation in p.annotations]    # ['splits and fusions', 'splits and fusions']