This module is a script demonstrating some basic features of the SQLAlchemy ORM declarative base. First shared with the Baltimore Python meetup 06/19/13.

sqlalchemy_demo.py

'''
This module is a script demonstrating some basic features of the 
SQLAlchemy ORM declarative base. First shared with the Baltimore
Python meetup 06/19/13.

It requires either a MySQL instance or SQLite to run as written but 
you can modify it run with any SQL flavor SQLAlchemy supports.

Alex C. Viana
alexcostaviana [at] gmail [dot] com
Twitter: @AlexVianaPro
GitHub: acviana
'''


from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import MultipleResultsFound
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship


def loadConnection(connection_string, echo=False):
    '''
    Create and engine using an engine string. Create a Base class and 
    Session class from the engine. Create an instance of the Session 
    class. Return the session instance and the Base class.
    '''
    engine = create_engine(connection_string, echo=echo)
    Base = declarative_base(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    return session, Base

# Call the factory function to get the session and Base. The 
# connection string will point to the database instance. The
# appropriate drivers and driver managers must be installed. 
#session, Base = loadConnection('sqlite:////home/test.db', echo=True)
session, Base = loadConnection('mysql+pymysql://root@localhost/test', echo=True)

# Define an object mapping with a custom init and representation 
# methods.
class Name(Base):
    '''The parent table.'''
    __tablename__ = 'name'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))

    def __init__(self, first_name, last_name):
        self.first_name = first_name
        self.last_name = last_name

    def __repr__(self):
           return "First: {}, Last: {}".format(self.first_name, self.last_name)

# Define an object mapping with a custom init and representation 
# methods. Also, define the relationship at the mapper level 
# between the two classes.
class Phone(Base):
    '''The child table'''
    __tablename__ = 'phone'
    id = Column(Integer, primary_key=True)
    number = Column(String(10))
    location = Column(String(50))
    name_id = Column(Integer, ForeignKey('name.id'))
    name = relationship("Name", backref=backref('phone', order_by=id))

    def __init__(self, number, location):
        self.number = number
        self.location = location

    def __repr__(self):
        return "{} : {}".format(self.location, self.number)

## Drop and recreate the tables.
Base.metadata.drop_all()
Base.metadata.create_all() 

## Load some Data
joe = Name('Joe', 'Strummer')
joe.phone = [Phone(1234567890, 'cell')]
nelson = Name('Nelson', 'Algren')
nelson.phone = [Phone(1987654321, 'home'),
    Phone(1111111111, 'cell')]
session.add_all([joe, nelson])
session.commit()

## Try to violate our foreign key constraints
test = Phone(number = '6666666666', location = 'home')
test.name_id = 3
session.add(test)
try:
    session.commit()
except IntegrityError:
    print 'Failed a Foreign Key constraint.'
    session.rollback()

## Query some data to see the __repr__ method.
name_query = session.query(Name)
for record in name_query:
    print record
phone_query = session.query(Phone)
for record in phone_query:
    print record

## Now lets look at some filters (SQL WHERE clasues)
name_query = session.query(Name).filter(Name.first_name == 'Joe')
for record in name_query:
    print record

## Instead we can just grab one record
phone_query = session.query(Phone)
print phone_query

## Get one record and ensure that only one exists.
## In this case there is only one.
name_query = session.query(Name).filter(Name.first_name == 'Joe').one()
print name_query
## In this case there is more than one.
try:
    name_query = session.query(Name).one()
except MultipleResultsFound:
    print 'Returned multiple rows for a .one() query.'

## A joined query
query = session.query(Name, Phone).join(Phone).all()
for record in query:
    print record

## And you can update with the update method on the query.
session.query(Phone).update({'number':9999999999})
session.commit()

## Now let's make some "stale" data.
print 'State Example'
for record in session.query(Phone).all():
    print 'Start Loop'
    session.query(Name).filter(Name.id == record.name_id).update({'first_name':'charlemagne'})
session.commit()