User.query.filter_by(**kwargs).first()

User.query.filter_by(**kwargs).all()

User.query.filter(xxx).all()

session.query(User).filter(and_(User.name == 'fred', User.id > 17))

# comparison of scalar relations to an instance
filter(Address.user == user)

# return all users who contain a particular address
filter(User.addresses.contains(address))

# return all users who *dont* contain the address
filter(~User.address.contains(address))

# return all users who contain a particular address with
# the email_address like '%foo%'
filter(User.addresses.any(Address.email_address.like('%foo%')))

# same, email address equals 'foo@bar.com'.  can fall back to keyword
# args for simple comparisons
filter(User.addresses.any(email_address = 'foo@bar.com'))

# return all Addresses whose user attribute has the username 'ed'
filter(Address.user.has(name='ed'))

# return all Addresses whose user attribute has the username 'ed'
# and an id > 5 (mixing clauses with kwargs)
filter(Address.user.has(User.id > 5, name='ed'))

session.query(Order).join('items')...

session.query(Order).join('items', aliased=True).
   filter(Item.name='item 1').join('items', aliased=True).filter(Item.name=='item 3')

session.query(Order).join('items', id='j1', aliased=True).
filter(Item.name == 'item 1').join('items', aliased=True, id='j2').
filter(Item.name == 'item 3').add_entity(Item, id='j1').add_entity(Item, id='j2')

# standard self-referential TreeNode mapper with backref
mapper(TreeNode, tree_nodes, properties={
    'children':relation(TreeNode, backref=backref('parent', remote_side=tree_nodes.id))
})

# query for node with child containing "bar" two levels deep
session.query(TreeNode).join(["children", "children"], aliased=True).filter_by(name='bar')

# search for the treenode along the path "n1/n12/n122"

# first find a Node with name="n122"
q = sess.query(Node).filter_by(name='n122')

# then join to parent with "n12"
q = q.join('parent', aliased=True).filter_by(name='n12')

# join again to the next parent with 'n1'.  use 'from_joinpoint'
# so we join from the previous point, instead of joining off the
# root table
q = q.join('parent', aliased=True, from_joinpoint=True).filter_by(name='n1')

node = q.first()

session.query(Blah).populate_existing().all()

myobject.foo = mytable.c.value + 1

user.pwhash = func.md5(password)

order.hash = text("select hash from hashing_table")

nodes = Table('nodes', metadata,
     Column('id', Integer, primary_key=True),
     Column('parent_id', Integer, ForeignKey('nodes.id')),
     Column('name', String(30)))

class TreeNode(object):
    pass

mapper(TreeNode, nodes, properties={
    'children':relation(TreeNode, lazy=False, join_depth=3)
})

create_session().query(TreeNode).all()

SELECT
nodes_3.id AS nodes_3_id, nodes_3.parent_id AS nodes_3_parent_id, nodes_3.name AS nodes_3_name,
nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.name AS nodes_2_name,
nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.name AS nodes_1_name,
nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.name AS nodes_name
FROM nodes LEFT OUTER JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id
LEFT OUTER JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id
LEFT OUTER JOIN nodes AS nodes_3 ON nodes_2.id = nodes_3.parent_id
ORDER BY nodes.oid, nodes_1.oid, nodes_2.oid, nodes_3.oid

class Point(object):
    def __init__(self, x, y):
        self.x = x
        self.y = y
    def __composite_values__(self):
        return self.x, self.y
    def __eq__(self, other):
        return other.x == self.x and other.y == self.y
    def __ne__(self, other):
        return not self.__eq__(other)

vertices = Table('vertices', metadata,
    Column('id', Integer, primary_key=True),
    Column('x1', Integer),
    Column('y1', Integer),
    Column('x2', Integer),
    Column('y2', Integer),
    )

class Vertex(object):
    def __init__(self, start, end):
        self.start = start
        self.end = end

mapper(Vertex, vertices, properties={
    'start':composite(Point, vertices.c.x1, vertices.c.y1),
    'end':composite(Point, vertices.c.x2, vertices.c.y2)
})

v = Vertex(Point(3, 4), Point(26,15))
session.save(v)
session.flush()

# works in queries too
q = session.query(Vertex).filter(Vertex.start == Point(3, 4))

# a Document class which uses a composite Version
# object as primary key
document = query.get(Version(1, 'a'))

mapper(Foo, foo_table, properties={
    'bars':dynamic_loader(Bar, backref='foo', <other relation() opts>)
})

session = create_session(autoflush=True)
foo = session.query(Foo).first()

foo.bars.append(Bar(name='lala'))

for bar in foo.bars.filter(Bar.name=='lala'):
    print(bar)

session.commit()

mapper(Class, table, properties={
    'foo' : deferred(table.c.foo, group='group1'),
    'bar' : deferred(table.c.bar, group='group1'),
    'bat' : deferred(table.c.bat, group='group1'),
)

session.query(Class).options(undefer_group('group1')).filter(...).all()

mapper(Foo, foo_table, properties={
   'bar':relation(Bar)
})
mapper(Bar, bar_table, properties={
   'bat':relation(Bat)
})
mapper(Bat, bat_table)

# eager load bar and bat
session.query(Foo).options(eagerload_all('bar.bat')).filter(...).all()

# use a dictionary relation keyed by a column
relation(Item, collection_class=column_mapped_collection(items.c.keyword))
# or named attribute
relation(Item, collection_class=attribute_mapped_collection('keyword'))
# or any function you like
relation(Item, collection_class=mapped_collection(lambda entity: entity.a + entity.b))

mapper(User, users, properties={
       'fullname': column_property((users.c.firstname + users.c.lastname).label('fullname')),
       'numposts': column_property(
            select([func.count(1)], users.c.id==posts.c.user_id).correlate(users).label('posts')
       )
    })

SELECT (SELECT count(1) FROM posts WHERE users.id = posts.user_id) AS count,
users.firstname || users.lastname AS fullname,
users.id AS users_id, users.firstname AS users_firstname, users.lastname AS users_lastname
FROM users ORDER BY users.oid

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('myengine://')
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)

# use the new Session() freely
sess = Session()
sess.save(someobject)
sess.flush()

Session.configure(bind=create_engine(...))

from sqlalchemy.orm import scoped_session, sessionmaker

Session = scoped_session(sessionmaker(autoflush=True, transactional=True))
Session.configure(bind=engine)

u = User(name='wendy')

sess = Session()
sess.save(u)
sess.commit()

# Session constructor is thread-locally scoped.  Everyone gets the same
# Session in the thread when scope="thread".
sess2 = Session()
assert sess is sess2

# "assignmapper"-like functionality available via ScopedSession.mapper
Session.mapper(User, users_table)

u = User(name='wendy')

Session.commit()

Session = sessionmaker(bind=engine, autoflush=True, transactional=True)

u = User(name='wendy')

sess = Session()
sess.save(u)

# wendy is flushed, comes right back from a query
wendy = sess.query(User).filter_by(name='wendy').one()

Session = sessionmaker(autoflush=True, transactional=False)

sess = Session()
sess.begin()

# use the session

sess.commit() # commit transaction

Session = sessionmaker(autoflush=True, transactional=False)

conn = engine.connect()
trans = conn.begin()
sess = Session(bind=conn)

# ... session is transactional

# commit the outermost transaction
trans.commit()