What does the Session do ?¶

In the most general sense, the Session establishes all conversations with the database and represents a “holding zone” for all the objects which you’ve loaded or associated with it during its lifespan. It provides the entrypoint to acquire a Query object, which sends queries to the database using the Session object’s current database connection, populating result rows into objects that are then stored in the Session, inside a structure called the Identity Map - a data structure that maintains unique copies of each object, where “unique” means “only one object with a particular primary key”.

The Session begins in an essentially stateless form. Once queries are issued or other objects are persisted with it, it requests a connection resource from an Engine that is associated either with the Session itself or with the mapped Table objects being operated upon. This connection represents an ongoing transaction, which remains in effect until the Session is instructed to commit or roll back its pending state.

All changes to objects maintained by a Session are tracked - before the database is queried again or before the current transaction is committed, it flushes all pending changes to the database. This is known as the Unit of Work pattern.

When using a Session, it’s important to note that the objects which are associated with it are proxy objects to the transaction being held by the Session - there are a variety of events that will cause objects to re-access the database in order to keep synchronized. It is possible to “detach” objects from a Session, and to continue using them, though this practice has its caveats. It’s intended that usually, you’d re-associate detached objects another Session when you want to work with them again, so that they can resume their normal task of representing database state.

Getting a Session¶

Session is a regular Python class which can be directly instantiated. However, to standardize how sessions are configured and acquired, the sessionmaker() function is normally used to create a top level Session configuration which can then be used throughout an application without the need to repeat the configurational arguments.

The usage of sessionmaker() is illustrated below:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# an Engine, which the Session will use for connection
# resources
some_engine = create_engine('postgresql://scott:tiger@localhost/')

# create a configured "Session" class
Session = sessionmaker(bind=some_engine)

# create a Session
session = Session()

# work with sess
myobject = MyObject('foo', 'bar')
session.add(myobject)
session.commit()

Above, the sessionmaker() call creates a class for us, which we assign to the name Session. This class is a subclass of the actual Session class, which when instantiated, will use the arguments we’ve given the function, in this case to use a particular Engine for connection resources.

A typical setup will associate the sessionmaker() with an Engine, so that each Session generated will use this Engine to acquire connection resources. This association can be set up as in the example above, using the bind argument.

When you write your application, place the result of the sessionmaker() call at the global level. The resulting Session class, configured for your application, should then be used by the rest of the applcation as the source of new Session instances.

An extremely common step taken by applications, including virtually all web applications, is to further wrap the sessionmaker() construct in a so-called “contextual” session, provided by the scoped_session() construct. This construct places the sessionmaker() into a registry that maintains a single Session per application thread. Information on using contextual sessions is at Contextual/Thread-local Sessions.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

# configure Session class with desired options
Session = sessionmaker()

# later, we create the engine
engine = create_engine('postgresql://...')

# associate it with our custom Session class
Session.configure(bind=engine)

# work with the session
session = Session()

# at the module level, the global sessionmaker,
# bound to a specific Engine
Session = sessionmaker(bind=engine)

# later, some unit of code wants to create a
# Session that is bound to a specific Connection
conn = engine.connect()
session = Session(bind=conn)

Using the Session¶

# query from a class
session.query(User).filter_by(name='ed').all()

# query with multiple classes, returns tuples
session.query(User, Address).join('addresses').filter_by(name='ed').all()

# query using orm-enabled descriptors
session.query(User.name, User.fullname).all()

# query from a mapper
user_mapper = class_mapper(User)
session.query(user_mapper)

user1 = User(name='user1')
user2 = User(name='user2')
session.add(user1)
session.add(user2)

session.commit()     # write changes to the database

session.add_all([item1, item2, item3])

merged_object = session.merge(existing_object)

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'), nullable=False)

>>> u1 = User(name='ed', addresses=[Address(email_address='ed@ed.com')])
>>> session.add(u1)
>>> session.commit()

>>> existing_a1 = u1.addresses[0]
>>> a1 = Address(id=existing_a1.id)

>>> a1.user = u1
>>> a1 = session.merge(a1)
>>> session.commit()
sqlalchemy.orm.exc.FlushError: New instance <Address at 0x1298f50>
with identity key (<class '__main__.Address'>, (1,)) conflicts with
persistent instance <Address at 0x12a25d0>

>>> a1 = Address()
>>> a1.user = u1
>>> a1 in session
True
>>> existing_a1 in session
True
>>> a1 is existing_a1
False

>>> a1 = Address(id=existing_a1.id, user_id=u1.id)
>>> assert a1.user is None
>>> True
>>> a1 = session.merge(a1)
>>> session.commit()
sqlalchemy.exc.IntegrityError: (IntegrityError) address.user_id
may not be NULL

>>> existing_a1.id = existing_a1.id
>>> existing_a1.user_id = u1.id
>>> existing_a1.user = None

>>> a1 = Address(id=existing_a1, user_id=user.id)
>>> assert a1 not in session
>>> a1 = session.merge(a1)

>>> a1 = Address(id=existing_a1, user_id=user.id)
>>> a1.user
>>> a1.__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1298d10>,
    'user_id': 1,
    'id': 1,
    'user': None}
>>> # we don't want user=None merged, remove it
>>> del a1.user
>>> a1 = session.merge(a1)
>>> # success
>>> session.commit()

# mark two objects to be deleted
session.delete(obj1)
session.delete(obj2)

# commit (or flush)
session.commit()

>>> address = user.addresses[1]
>>> session.delete(address)
>>> session.flush()
>>> address in user.addresses
True

>>> session.commit()
>>> address in user.addresses
False

mapper(User, users_table, properties={
    'addresses':relationship(Address, cascade="all, delete, delete-orphan")
})
del user.addresses[1]
session.flush()

session.query(User).filter(User.id==7).delete()

session.flush()

Session = sessionmaker(autoflush=False)

mysession = Session()
mysession.autoflush = False

session.expunge(obj1)

# immediately re-load attributes on obj1, obj2
session.refresh(obj1)
session.refresh(obj2)

# expire objects obj1, obj2, attributes will be reloaded
# on the next access:
session.expire(obj1)
session.expire(obj2)

# immediately re-load the attributes 'hello', 'world' on obj1, obj2
session.refresh(obj1, ['hello', 'world'])
session.refresh(obj2, ['hello', 'world'])

# expire the attributes 'hello', 'world' objects obj1, obj2, attributes will be reloaded
# on the next access:
session.expire(obj1, ['hello', 'world'])
session.expire(obj2, ['hello', 'world'])

session.expire_all()

for obj in session:
    print obj

if obj in session:
    print "Object is present"

# pending objects recently added to the Session
session.new

# persistent objects which currently have changes detected
# (this collection is now created on the fly each time the property is called)
session.dirty

# persistent objects that have been marked as deleted via session.delete(obj)
session.deleted

Cascades¶

Mappers support the concept of configurable cascade behavior on relationship() constructs. This behavior controls how the Session should treat the instances that have a parent-child relationship with another instance that is operated upon by the Session. Cascade is indicated as a comma-separated list of string keywords, with the possible values all, delete, save-update, refresh-expire, merge, expunge, and delete-orphan.

Cascading is configured by setting the cascade keyword argument on a relationship():

mapper(Order, order_table, properties={
    'items' : relationship(Item, cascade="all, delete-orphan"),
    'customer' : relationship(User, secondary=user_orders_table, cascade="save-update"),
})

The above mapper specifies two relationships, items and customer. The items relationship specifies “all, delete-orphan” as its cascade value, indicating that all add, merge, expunge, refresh delete and expire operations performed on a parent Order instance should also be performed on the child Item instances attached to it. The delete-orphan cascade value additionally indicates that if an Item instance is no longer associated with an Order, it should also be deleted. The “all, delete-orphan” cascade argument allows a so-called lifecycle relationship between an Order and an Item object.

The customer relationship specifies only the “save-update” cascade value, indicating most operations will not be cascaded from a parent Order instance to a child User instance except for the add() operation. save-update cascade indicates that an add() on the parent will cascade to all child items, and also that items added to a parent which is already present in a session will also be added to that same session. “save-update” cascade also cascades the pending history of a relationship()-based attribute, meaning that objects which were removed from a scalar or collection attribute whose changes have not yet been flushed are also placed into the new session - this so that foreign key clear operations and deletions will take place (new in 0.6).

Note that the delete-orphan cascade only functions for relationships where the target object can have a single parent at a time, meaning it is only appropriate for one-to-one or one-to-many relationships. For a relationship() which establishes one-to-one via a local foreign key, i.e. a many-to-one that stores only a single parent, or one-to-one/one-to-many via a “secondary” (association) table, a warning will be issued if delete-orphan is configured. To disable this warning, specify the single_parent=True flag on the relationship, which constrains objects to allow attachment to only one parent at a time.

The default value for cascade on relationship() is save-update, merge.

save-update cascade also takes place on backrefs by default. This means that, given a mapping such as this:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order')
})

If an Order is already in the session, and is assigned to the order attribute of an Item, the backref appends the Order to the items collection of that Order, resulting in the save-update cascade taking place:

>>> o1 = Order()
>>> session.add(o1)
>>> o1 in session
True

>>> i1 = Item()
>>> i1.order = o1
>>> i1 in o1.items
True
>>> i1 in session
True

This behavior can be disabled as of 0.6.5 using the cascade_backrefs flag:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order',
                                cascade_backrefs=False)
})

So above, the assignment of i1.order = o1 will append i1 to the items collection of o1, but will not add i1 to the session. You can, of course, add() i1 to the session at a later point. This option may be helpful for situations where an object needs to be kept out of a session until it’s construction is completed, but still needs to be given associations to objects which are already persistent in the target session.

Managing Transactions¶

A newly constructed Session may be said to be in the “begin” state. In this state, the Session has not established any connection or transactional state with any of the Engine objects that may be associated with it.

The Session then receives requests to operate upon a database connection. Typically, this means it is called upon to execute SQL statements using a particular Engine, which may be via Session.query(), Session.execute(), or within a flush operation of pending data, which occurs when such state exists and Session.commit() or Session.flush() is called.

As these requests are received, each new Engine encountered is associated with an ongoing transactional state maintained by the Session. When the first Engine is operated upon, the Session can be said to have left the “begin” state and entered “transactional” state. For each Engine encountered, a Connection is associated with it, which is acquired via the Engine.contextual_connect() method. If a Connection was directly associated with the Session (see Joining a Session into an External Transaction for an example of this), it is added to the transactional state directly.

For each Connection, the Session also maintains a Transaction object, which is acquired by calling Connection.begin() on each Connection, or if the Session object has been established using the flag twophase=True, a TwoPhaseTransaction object acquired via Connection.begin_twophase(). These transactions are all committed or rolled back corresponding to the invocation of the Session.commit() and Session.rollback() methods. A commit operation will also call the TwoPhaseTransaction.prepare() method on all transactions if applicable.

When the transactional state is completed after a rollback or commit, the Session releases all Transaction and Connection resources (which has the effect of returning DBAPI connections to the connection pool of each Engine), and goes back to the “begin” state, which will again invoke new Connection and Transaction objects as new requests to emit SQL statements are received.

The example below illustrates this lifecycle:

engine = create_engine("...")
Session = sessionmaker(bind=engine)

# new session.   no connections are in use.
session = Session()
try:
    # first query.  a Connection is acquired
    # from the Engine, and a Transaction
    # started.
    item1 = session.query(Item).get(1)

    # second query.  the same Connection/Transaction
    # are used.
    item2 = session.query(Item).get(2)

    # pending changes are created.
    item1.foo = 'bar'
    item2.bar = 'foo'

    # commit.  The pending changes above
    # are flushed via flush(), the Transaction
    # is committed, the Connection object closed
    # and discarded, the underlying DBAPI connection
    # returned to the connection pool.
    session.commit()
except:
    # on rollback, the same closure of state
    # as that of commit proceeds.
    session.rollback()
    raise

Session = sessionmaker()
session = Session()
session.add(u1)
session.add(u2)

session.begin_nested() # establish a savepoint
session.add(u3)
session.rollback()  # rolls back u3, keeps u1 and u2

session.commit() # commits u1 and u2

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
session.begin()
try:
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'
    session.commit()
except:
    session.rollback()
    raise

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
with session.begin():
    item1 = session.query(Item).get(1)
    item2 = session.query(Item).get(2)
    item1.foo = 'bar'
    item2.bar = 'foo'

# method_a starts a transaction and calls method_b
def method_a(session):
    session.begin(subtransactions=True)
    try:
        method_b(session)
        session.commit()  # transaction is committed here
    except:
        session.rollback() # rolls back the transaction
        raise

# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
    session.begin(subtransactions=True)
    try:
        session.add(SomeObject('bat', 'lala'))
        session.commit()  # transaction is not committed yet
    except:
        session.rollback() # rolls back the transaction, in this case
                           # the one that was initiated in method_a().
        raise

# create a Session and call method_a
session = Session(autocommit=True)
method_a(session)
session.close()

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

Session = sessionmaker(twophase=True)

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})

session = Session()

# .... work with accounts and users

# commit.  session will issue a flush to all DBs, and a prepare step to all DBs,
# before committing both transactions
session.commit()

Embedding SQL Insert/Update Expressions into a Flush¶

This feature allows the value of a database column to be set to a SQL expression instead of a literal value. It’s especially useful for atomic updates, calling stored procedures, etc. All you do is assign an expression to an attribute:

class SomeClass(object):
    pass
mapper(SomeClass, some_table)

someobject = session.query(SomeClass).get(5)

# set 'value' attribute to a SQL expression adding one
someobject.value = some_table.c.value + 1

# issues "UPDATE some_table SET value=value+1"
session.commit()

This technique works both for INSERT and UPDATE statements. After the flush/commit operation, the value attribute on someobject above is expired, so that when next accessed the newly generated value will be loaded from the database.

Using SQL Expressions with Sessions¶

SQL expressions and strings can be executed via the Session within its transactional context. This is most easily accomplished using the execute() method, which returns a ResultProxy in the same manner as an Engine or Connection:

Session = sessionmaker(bind=engine)
session = Session()

# execute a string statement
result = session.execute("select * from table where id=:id", {'id':7})

# execute a SQL expression construct
result = session.execute(select([mytable]).where(mytable.c.id==7))

The current Connection held by the Session is accessible using the connection() method:

connection = session.connection()

The examples above deal with a Session that’s bound to a single Engine or Connection. To execute statements using a Session which is bound either to multiple engines, or none at all (i.e. relies upon bound metadata), both execute() and connection() accept a mapper keyword argument, which is passed a mapped class or Mapper instance, which is used to locate the proper context for the desired engine:

Session = sessionmaker()
session = Session()

# need to specify mapper or class when executing
result = session.execute("select * from table where id=:id", {'id':7}, mapper=MyMappedClass)

result = session.execute(select([mytable], mytable.c.id==7), mapper=MyMappedClass)

connection = session.connection(MyMappedClass)

Joining a Session into an External Transaction¶

If a Connection is being used which is already in a transactional state (i.e. has a Transaction established), a Session can be made to participate within that transaction by just binding the Session to that Connection. The usual rationale for this is a test suite that allows ORM code to work freely with a Session, including the ability to call Session.commit(), where afterwards the entire database interaction is rolled back:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from unittest import TestCase

# global application scope.  create Session class, engine
Session = sessionmaker()

engine = create_engine('postgresql://...')

class SomeTest(TestCase):
    def setUp(self):
        # connect to the database
        self.connection = engine.connect()

        # begin a non-ORM transaction
        self.trans = connection.begin()

        # bind an individual Session to the connection
        self.session = Session(bind=self.connection)

    def test_something(self):
        # use the session in tests.

        self.session.add(Foo())
        self.session.commit()

    def tearDown(self):
        # rollback - everything that happened with the
        # Session above (including calls to commit())
        # is rolled back.
        self.trans.rollback()
        self.session.close()

Above, we issue Session.commit() as well as Transaction.rollback(). This is an example of where we take advantage of the Connection object’s ability to maintain subtransactions, or nested begin/commit-or-rollback pairs where only the outermost begin/commit pair actually commits the transaction, or if the outermost block rolls back, everything is rolled back.

The Session object and sessionmaker() function¶

sqlalchemy.orm.session.sessionmaker(bind=None, class_=None, autoflush=True, autocommit=False, expire_on_commit=True, **kwargs)¶

Generate a custom-configured Session class.

The returned object is a subclass of Session, which, when instantiated with no arguments, uses the keyword arguments configured here as its constructor arguments.

It is intended that the sessionmaker() function be called within the global scope of an application, and the returned class be made available to the rest of the application as the single class used to instantiate sessions.

e.g.:

# global scope
Session = sessionmaker(autoflush=False)

# later, in a local scope, create and use a session:
sess = Session()

Any keyword arguments sent to the constructor itself will override the “configured” keywords:

Session = sessionmaker()

# bind an individual session to a connection
sess = Session(bind=connection)

The class also includes a special classmethod configure(), which allows additional configurational options to take place after the custom Session class has been generated. This is useful particularly for defining the specific Engine (or engines) to which new instances of Session should be bound:

Session = sessionmaker()
Session.configure(bind=create_engine('sqlite:///foo.db'))

sess = Session()

Options:

Parameters:

autocommit – Defaults to False. When True, the Session does not keep a persistent transaction running, and will acquire connections from the engine on an as-needed basis, returning them immediately after their use. Flushes will begin and commit (or possibly rollback) their own transaction if no transaction is present. When using this mode, the session.begin() method may be used to begin a transaction explicitly. Leaving it on its default value of False means that the Session will acquire a connection and begin a transaction the first time it is used, which it will maintain persistently until rollback(), commit(), or close() is called. When the transaction is released by any of these methods, the Session is ready for the next usage, which will again acquire and maintain a new connection/transaction. autoflush – When True, all query operations will issue a flush() call to this Session before proceeding. This is a convenience feature so that flush() need not be called repeatedly in order for database queries to retrieve results. It’s typical that autoflush is used in conjunction with autocommit=False. In this scenario, explicit calls to flush() are rarely needed; you usually only need to call commit() (which flushes) to finalize changes. bind – An optional Engine or Connection to which this Session should be bound. When specified, all SQL operations performed by this session will execute via this connectable. binds – An optional dictionary which contains more granular “bind” information than the bind parameter provides. This dictionary can map individual Table instances as well as Mapper instances to individual Engine or Connection objects. Operations which proceed relative to a particular Mapper will consult this dictionary for the direct Mapper instance as well as the mapper’s mapped_table attribute in order to locate an connectable to use. The full resolution is described in the get_bind() method of Session. Usage looks like: Session = sessionmaker(binds={ SomeMappedClass: create_engine('postgresql://engine1'), somemapper: create_engine('postgresql://engine2'), some_table: create_engine('postgresql://engine3'), }) Also see the Session.bind_mapper() and Session.bind_table() methods. class_ – Specify an alternate class other than sqlalchemy.orm.session.Session which should be used by the returned class. This is the only argument that is local to the sessionmaker() function, and is not sent directly to the constructor for Session. _enable_transaction_accounting – Defaults to True. A legacy-only flag which when False disables all 0.5-style object accounting on transaction boundaries, including auto-expiry of instances on rollback and commit, maintenance of the “new” and “deleted” lists upon rollback, and autoflush of pending changes upon begin(), all of which are interdependent. expire_on_commit – Defaults to True. When True, all instances will be fully expired after each commit(), so that all attribute/object access subsequent to a completed transaction will load from the most recent database state. extension – An optional SessionExtension instance, or a list of such instances, which will receive pre- and post- commit and flush events, as well as a post-rollback event. User- defined code may be placed within these hooks using a user-defined subclass of SessionExtension. query_cls – Class which should be used to create new Query objects, as returned by the query() method. Defaults to Query. twophase – When True, all transactions will be started as a “two phase” transaction, i.e. using the “two phase” semantics of the database in use along with an XID. During a commit(), after flush() has been issued for all attached databases, the prepare() method on each database’s TwoPhaseTransaction will be called. This allows each database to roll back the entire transaction, before each transaction is committed. weak_identity_map – When set to the default value of True, a weak-referencing map is used; instances which are not externally referenced will be garbage collected immediately. For dereferenced instances which have pending changes present, the attribute management system will create a temporary strong-reference to the object which lasts until the changes are flushed to the database, at which point it’s again dereferenced. Alternatively, when using the value False, the identity map uses a regular Python dictionary to store instances. The session will maintain all instances present until they are removed using expunge(), clear(), or purge().

class sqlalchemy.orm.session.Session(bind=None, autoflush=True, expire_on_commit=True, _enable_transaction_accounting=True, autocommit=False, twophase=False, weak_identity_map=True, binds=None, extension=None, query_cls=<class 'sqlalchemy.orm.query.Query'>)¶

Manages persistence operations for ORM-mapped objects.

The Session’s usage paradigm is described at Using the Session.

__init__(bind=None, autoflush=True, expire_on_commit=True, _enable_transaction_accounting=True, autocommit=False, twophase=False, weak_identity_map=True, binds=None, extension=None, query_cls=<class 'sqlalchemy.orm.query.Query'>)¶

Construct a new Session.

Arguments to Session are described using the sessionmaker() function, which is the typical point of entry.

add(instance)¶

Place an object in the Session.

Its state will be persisted to the database on the next flush operation.

Repeated calls to add() will be ignored. The opposite of add() is expunge().

add_all(instances)¶

Add the given collection of instances to this Session.

begin(subtransactions=False, nested=False)¶

Begin a transaction on this Session.

If this Session is already within a transaction, either a plain transaction or nested transaction, an error is raised, unless subtransactions=True or nested=True is specified.

The subtransactions=True flag indicates that this begin() can create a subtransaction if a transaction is already in progress. For documentation on subtransactions, please see Using Subtransactions with Autocommit.

The nested flag begins a SAVEPOINT transaction and is equivalent to calling begin_nested(). For documentation on SAVEPOINT transactions, please see Using SAVEPOINT.

begin_nested()¶

Begin a nested transaction on this Session.

The target database(s) must support SQL SAVEPOINTs or a SQLAlchemy-supported vendor implementation of the idea.

For documentation on SAVEPOINT transactions, please see Using SAVEPOINT.

bind_mapper(mapper, bind)¶

Bind operations for a mapper to a Connectable.

mapper

A mapper instance or mapped class

bind

Any Connectable: a Engine or Connection.

All subsequent operations involving this mapper will use the given bind.

bind_table(table, bind)¶

Bind operations on a Table to a Connectable.

table

A Table instance

bind

Any Connectable: a Engine or Connection.

All subsequent operations involving this Table will use the given bind.

close()¶

Close this Session.

This clears all items and ends any transaction in progress.

If this session were created with autocommit=False, a new transaction is immediately begun. Note that this new transaction does not use any connection resources until they are first needed.

classmethod close_all()¶

Close all sessions in memory.

commit()¶

Flush pending changes and commit the current transaction.

If no transaction is in progress, this method raises an InvalidRequestError.

By default, the Session also expires all database loaded state on all ORM-managed attributes after transaction commit. This so that subsequent operations load the most recent data from the database. This behavior can be disabled using the expire_on_commit=False option to sessionmaker() or the Session constructor.

If a subtransaction is in effect (which occurs when begin() is called multiple times), the subtransaction will be closed, and the next call to commit() will operate on the enclosing transaction.

For a session configured with autocommit=False, a new transaction will be begun immediately after the commit, but note that the newly begun transaction does not use any connection resources until the first SQL is actually emitted.

connection(mapper=None, clause=None)¶

Return a Connection object corresponding to this Session object’s transactional state.

If this Session is configured with autocommit=False, either the Connection corresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the Connection returned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted).

Alternatively, if this Session is configured with autocommit=True, an ad-hoc Connection is returned using Engine.contextual_connect() on the underlying Engine.

Ambiguity in multi-bind or unbound Session objects can be resolved through any of the optional keyword arguments. This ultimately makes usage of the get_bind() method for resolution.

Parameters:	mapper – Optional mapper() mapped class, used to identify the appropriate bind. This argument takes precedence over clause. clause – A ClauseElement (i.e. select(), text(), etc.) which will be used to locate a bind, if a bind cannot otherwise be identified.

delete(instance)¶

Mark an instance as deleted.

The database delete operation occurs upon flush().

deleted¶

The set of all instances marked as ‘deleted’ within this Session

dirty¶

The set of all persistent instances considered dirty.

Instances are considered dirty when they were modified but not deleted.

Note that this ‘dirty’ calculation is ‘optimistic’; most attribute-setting or collection modification operations will mark an instance as ‘dirty’ and place it in this set, even if there is no net change to the attribute’s value. At flush time, the value of each attribute is compared to its previously saved value, and if there’s no net change, no SQL operation will occur (this is a more expensive operation so it’s only done at flush time).

To check if an instance has actionable net changes to its attributes, use the is_modified() method.

execute(clause, params=None, mapper=None, **kw)¶

Execute a clause within the current transaction.

Returns a ResultProxy representing results of the statement execution, in the same manner as that of an Engine or Connection.

execute() accepts any executable clause construct, such as select(), insert(), update(), delete(), and text(), and additionally accepts plain strings that represent SQL statements. If a plain string is passed, it is first converted to a text() construct, which here means that bind parameters should be specified using the format :param.

The statement is executed within the current transactional context of this Session, using the same behavior as that of the Session.connection() method to determine the active Connection. The close_with_result flag is set to True so that an autocommit=True Session with no active transaction will produce a result that auto-closes the underlying Connection.

Parameters:

clause – A ClauseElement (i.e. select(), text(), etc.) or string SQL statement to be executed. The clause will also be used to locate a bind, if this Session is not bound to a single engine already, and the mapper argument is not passed. params – Optional dictionary of bind names mapped to values. mapper – Optional mapper() or mapped class, used to identify the appropriate bind. This argument takes precedence over clause when locating a bind. **kw – Additional keyword arguments are sent to get_bind(), allowing additional arguments to be passed to custom implementations of get_bind().

expire(instance, attribute_names=None)¶

Expire the attributes on an instance.

Marks the attributes of an instance as out of date. When an expired attribute is next accessed, a query will be issued to the Session object’s current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.

To expire all objects in the Session simultaneously, use Session.expire_all().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or Session.commit() methods are called, so that new state can be loaded for the new transaction. For this reason, calling Session.expire() only makes sense for the specific case that a non-ORM SQL statement was emitted in the current transaction.

Parameters:	instance – The instance to be refreshed. attribute_names – optional list of string attribute names indicating a subset of attributes to be expired.

expire_all()¶

Expires all persistent instances within this Session.

When any attributes on a persistent instance is next accessed, a query will be issued using the Session object’s current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.

To expire individual objects and individual attributes on those objects, use Session.expire().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or Session.commit() methods are called, so that new state can be loaded for the new transaction. For this reason, calling Session.expire_all() should not be needed when autocommit is False, assuming the transaction is isolated.

expunge(instance)¶

Remove the instance from this Session.

This will free all internal references to the instance. Cascading will be applied according to the expunge cascade rule.

expunge_all()¶

Remove all object instances from this Session.

This is equivalent to calling expunge(obj) on all objects in this Session.

flush(objects=None)¶

Flush all the object changes to the database.

Writes out all pending object creations, deletions and modifications to the database as INSERTs, DELETEs, UPDATEs, etc. Operations are automatically ordered by the Session’s unit of work dependency solver.

Database operations will be issued in the current transactional context and do not affect the state of the transaction, unless an error occurs, in which case the entire transaction is rolled back. You may flush() as often as you like within a transaction to move changes from Python to the database’s transaction buffer.

For autocommit Sessions with no active manual transaction, flush() will create a transaction on the fly that surrounds the entire set of operations int the flush.

objects

Optional; a list or tuple collection. Restricts the flush operation to only these objects, rather than all pending changes. Deprecated - this flag prevents the session from properly maintaining accounting among inter-object relations and can cause invalid results.

get_bind(mapper=None, clause=None)¶

Return a “bind” to which this Session is bound.

The “bind” is usually an instance of Engine, except in the case where the Session has been explicitly bound directly to a Connection.

For a multiply-bound or unbound Session, the mapper or clause arguments are used to determine the appropriate bind to return.

Parameters:

mapper – Optional mapper() mapped class or instance of Mapper. The bind can be derived from a Mapper first by consulting the “binds” map associated with this Session, and secondly by consulting the MetaData associated with the Table to which the Mapper is mapped for a bind. clause – A ClauseElement (i.e. select(), text(), etc.). If the mapper argument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically a Table associated with bound MetaData.

is_active¶

True if this Session has an active transaction.

is_modified(instance, include_collections=True, passive=False)¶

Return True if instance has modified attributes.

This method retrieves a history instance for each instrumented attribute on the instance and performs a comparison of the current value to its previously committed value.

include_collections indicates if multivalued collections should be included in the operation. Setting this to False is a way to detect only local-column based properties (i.e. scalar columns or many-to-one foreign keys) that would result in an UPDATE for this instance upon flush.

The passive flag indicates if unloaded attributes and collections should not be loaded in the course of performing this test.

A few caveats to this method apply:

• Instances present in the ‘dirty’ collection may result in a value of False when tested with this method. This because while the object may have received attribute set events, there may be no net changes on its state.

• Scalar attributes may not have recorded the “previously” set value when a new value was applied, if the attribute was not loaded, or was expired, at the time the new value was received - in these cases, the attribute is assumed to have a change, even if there is ultimately no net change against its database value. SQLAlchemy in most cases does not need the “old” value when a set event occurs, so it skips the expense of a SQL call if the old value isn’t present, based on the assumption that an UPDATE of the scalar value is usually needed, and in those few cases where it isn’t, is less expensive on average than issuing a defensive SELECT.

  The “old” value is fetched unconditionally only if the attribute container has the “active_history” flag set to True. This flag is set typically for primary key attributes and scalar references that are not a simple many-to-one.

merge(instance, load=True, **kw)¶

Copy the state an instance onto the persistent instance with the same identifier.

If there is no persistent instance currently associated with the session, it will be loaded. Return the persistent instance. If the given instance is unsaved, save a copy of and return it as a newly persistent instance. The given instance does not become associated with the session.

This operation cascades to associated instances if the association is mapped with cascade="merge".

See Merging for a detailed discussion of merging.

new¶

The set of all instances marked as ‘new’ within this Session.

classmethod object_session(instance)¶

Return the Session to which an object belongs.

prepare()¶

Prepare the current transaction in progress for two phase commit.

If no transaction is in progress, this method raises an InvalidRequestError.

Only root transactions of two phase sessions can be prepared. If the current transaction is not such, an InvalidRequestError is raised.

prune()¶

Remove unreferenced instances cached in the identity map.

Note that this method is only meaningful if “weak_identity_map” is set to False. The default weak identity map is self-pruning.

Removes any object in this Session’s identity map that is not referenced in user code, modified, new or scheduled for deletion. Returns the number of objects pruned.

query(*entities, **kwargs)¶

Return a new Query object corresponding to this Session.

refresh(instance, attribute_names=None, lockmode=None)¶

Expire and refresh the attributes on the given instance.

A query will be issued to the database and all attributes will be refreshed with their current database value.

Lazy-loaded relational attributes will remain lazily loaded, so that the instance-wide refresh operation will be followed immediately by the lazy load of that attribute.

Eagerly-loaded relational attributes will eagerly load within the single refresh operation.

Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction - usage of refresh() usually only makes sense if non-ORM SQL statement were emitted in the ongoing transaction, or if autocommit mode is turned on.

Parameters:	attribute_names – optional. An iterable collection of string attribute names indicating a subset of attributes to be refreshed. lockmode – Passed to the Query as used by with_lockmode().

rollback()¶

Rollback the current transaction in progress.

If no transaction is in progress, this method is a pass-through.

This method rolls back the current transaction or nested transaction regardless of subtransactions being in effect. All subtransactions up to the first real transaction are closed. Subtransactions occur when begin() is called multiple times.

scalar(clause, params=None, mapper=None, **kw)¶

Like execute() but return a scalar result.

Contextual/Thread-local Sessions¶

A common need in applications, particularly those built around web frameworks, is the ability to “share” a Session object among disparate parts of an application, without needing to pass the object explicitly to all method and function calls. What you’re really looking for is some kind of “global” session object, or at least “global” to all the parts of an application which are tasked with servicing the current request. For this pattern, SQLAlchemy provides the ability to enhance the Session class generated by sessionmaker() to provide auto-contextualizing support. This means that whenever you create a Session instance with its constructor, you get an existing Session object which is bound to some “context”. By default, this context is the current thread. This feature is what previously was accomplished using the sessioncontext SQLAlchemy extension.

from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session(sessionmaker())

>>> # call Session() the first time.  the new Session instance is created.
>>> session = Session()

>>> # later, in the same application thread, someone else calls Session()
>>> session2 = Session()

>>> # the two Session objects are *the same* object
>>> session is session2
True

# create some objects
u1 = User()
u2 = User()

# save to the contextual session, without instantiating
Session.add(u1)
Session.add(u2)

# view the "new" attribute
assert u1 in Session.new

# commit changes
Session.commit()

# remove current contextual session
Session.remove()

Web Server          Web Framework        User-defined Controller Call
--------------      --------------       ------------------------------
web request    ->
                    call controller ->   # call Session().  this establishes a new,
                                         # contextual Session.
                                         session = Session()

                                         # load some objects, save some changes
                                         objects = session.query(MyClass).all()

                                         # some other code calls Session, it's the
                                         # same contextual session as "sess"
                                         session2 = Session()
                                         session2.add(foo)
                                         session2.commit()

                                         # generate content to be returned
                                         return generate_content()
                    Session.remove() <-
web response   <-

Partitioning Strategies¶

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

Session = sessionmaker(twophase=True)

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})

session = Session()

Session Utilities¶

sqlalchemy.orm.session.make_transient(instance)¶

Make the given instance ‘transient’.

This will remove its association with any session and additionally will remove its “identity key”, such that it’s as though the object were newly constructed, except retaining its values. It also resets the “deleted” flag on the state if this object had been explicitly deleted by its session.

Attributes which were “expired” or deferred at the instance level are reverted to undefined, and will not trigger any loads.

sqlalchemy.orm.session.object_session(instance)¶

Return the Session to which instance belongs.

If the instance is not a mapped instance, an error is raised.

Attribute and State Management Utilities¶

These functions are provided by the SQLAlchemy attribute instrumentation API to provide a detailed interface for dealing with instances, attribute values, and history. Some of them are useful when constructing event listener functions, such as those described in ORM Event Interfaces.

sqlalchemy.orm.attributes.del_attribute(instance, key)¶

Delete the value of an attribute, firing history events.

This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to establish attribute state as understood by SQLAlchemy.

sqlalchemy.orm.attributes.get_attribute(instance, key)¶

Get the value of an attribute, firing any callables required.

This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to make usage of attribute state as understood by SQLAlchemy.

sqlalchemy.orm.attributes.get_history(obj, key, **kwargs)¶

Return a History record for the given object and attribute key.

Parameters:

obj – an object whose class is instrumented by the attributes package. key – string attribute name. kwargs – Optional keyword arguments currently include the passive flag, which indicates if the attribute should be loaded from the database if not already present (PASSIVE_NO_FETCH), and if the attribute should be not initialized to a blank value otherwise (PASSIVE_NO_INITIALIZE). Default is PASSIVE_OFF.

sqlalchemy.orm.attributes.init_collection(obj, key)¶

Initialize a collection attribute and return the collection adapter.

This function is used to provide direct access to collection internals for a previously unloaded attribute. e.g.:

collection_adapter = init_collection(someobject, 'elements')
for elem in values:
    collection_adapter.append_without_event(elem)

For an easier way to do the above, see

set_committed_value().

obj is an instrumented object instance. An InstanceState is accepted directly for backwards compatibility but this usage is deprecated.

sqlalchemy.orm.attributes.instance_state()¶

Return the InstanceState for a given object.

sqlalchemy.orm.attributes.is_instrumented(instance, key)¶

Return True if the given attribute on the given instance is instrumented by the attributes package.

This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required.

sqlalchemy.orm.attributes.manager_of_class()¶

Return the ClassManager for a given class.

sqlalchemy.orm.attributes.set_attribute(instance, key, value)¶

Set the value of an attribute, firing history events.

This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to establish attribute state as understood by SQLAlchemy.

sqlalchemy.orm.attributes.set_committed_value(instance, key, value)¶

Set the value of an attribute with no history events.

Cancels any previous history present. The value should be a scalar value for scalar-holding attributes, or an iterable for any collection-holding attribute.

This is the same underlying method used when a lazy loader fires off and loads additional data from the database. In particular, this method can be used by application code which has loaded additional attributes or collections through separate queries, which can then be attached to an instance as though it were part of its original loaded state.

class sqlalchemy.orm.attributes.History¶

A 3-tuple of added, unchanged and deleted values, representing the changes which have occurred on an instrumented attribute.

Each tuple member is an iterable sequence.

added¶

Return the collection of items added to the attribute (the first tuple element).

deleted¶

Return the collection of items that have been removed from the attribute (the third tuple element).

empty()¶

Return True if this History has no changes and no existing, unchanged state.

has_changes()¶

Return True if this History has changes.

non_added()¶

Return a collection of unchanged + deleted.

non_deleted()¶

Return a collection of added + unchanged.

sum()¶

Return a collection of added + unchanged + deleted.

unchanged¶

Return the collection of items that have not changed on the attribute (the second tuple element).

sqlalchemy.orm.attributes.PASSIVE_NO_INITIALIZE¶

Symbol indicating that loader callables should not be fired off, and a non-initialized attribute should remain that way.

sqlalchemy.orm.attributes.PASSIVE_NO_FETCH¶

Symbol indicating that loader callables should not boe fired off. Non-initialized attributes should be initialized to an empty value.

sqlalchemy.orm.attributes.PASSIVE_OFF¶

Symbol indicating that loader callables should be executed.