gistfile1.py

"""
    SQL Alchemy example
"""
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()

class Sale(Base):
    __tablename__ = 'sale'

    id = Column(Integer, primary_key=True)
    reference = Column(String)

    def __repr__(self):
        return "<Sale(reference='%s')>" % (self.reference,)


class SaleLine(Base):
    __tablename__ = 'sale_line'

    id = Column(Integer, primary_key=True)
    description = Column(String)

    sale_id = Column(Integer, ForeignKey('sale.id'))
    sale = relationship("Sale", backref=backref('lines'))

    def __repr__(self):
        return "<SaleLine(description=%s, sale_id=%d)>" % (
            self.description, self.sale_id
        )


def sync_db():
    Base.metadata.create_all(engine)


if __name__ == '__main__':
    # Create all the tables first
    sync_db()

    # Create a new session
    sess = Session()


    sale = Sale(reference="1011")
    sale.lines = [
        SaleLine(description="Item 1"),
        SaleLine(description="Item 2"),
        SaleLine(description="Item 3"),
        SaleLine(description="Item 4"),
        SaleLine(description="Item 5"),
        SaleLine(description="Item 6"),
    ]

    sale2 = Sale(reference="1022")
    sale2.lines = [
        SaleLine(description="Item 1"),
        SaleLine(description="Item 2"),
        SaleLine(description="Item 3"),
        SaleLine(description="Item 4"),
        SaleLine(description="Item 5"),
        SaleLine(description="Item 6"),
    ]

    # add to the session and commit
    sess.add_all([sale, sale2])
    sess.commit()


    # Now search for the items
    query = sess.query(SaleLine)
    for sale_line in query.all():
        print sale_line.sale.reference