sqlalchemy_example.py

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///users.db', echo=True)
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

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

    def __repr__(self):
        return str.format("User with {id} and {name}", id=self.id, name=self.name)


# let's create the schema
Base.metadata.create_all(engine)

# Session, our beautiful ORM, will be the factory for any new session with db
Session = sessionmaker(bind=engine)
session = Session()

# Let's now do the logic
# creating new object
new_user = User(name='Yasser')

# since it is not added to the session, session will not
# track its status so this will print empty list
print(session.new)  # IdentitySet([])

session.add(new_user)
print(session.new)  # IdentitySet([User with None and Yasser])


# this will execute INSERT statement
session.commit()

# now this user is in memory and mapped to the row in its table
for obj in session.identity_map:
    print obj # (<class '__main__.User'>, (1,), None)


# changing object attributes will change it's state in the session layer
# which needs to be reflected later by update statement to database
new_user.name = 'Yasser 2'
print(session.dirty)  # IdentitySet([User with 1 and Yasser 2])

# this will execute UPDATE statement
session.commit()