renalreg/radar

# SQLAlchemy Sessions

Sometimes we need to create a new SQLAlchemy session that is committed
separately from the main session. For example you may want to log a message
even if the main session's transaction is rolled back.

For the following examples we use this `User` model and `engine`:

```python
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

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

    def __repr__(self):
        return "<User(name='%s')>" % self.name


engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
```

Using `sessionmaker`:

```python
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

# Create a new session and add a new user without a name
# This will raise an IntegrityError when committed
session1 = Session()
session1.add(User(name=None))

# Create a new session and add a new user
session2 = Session()
session2.add(User(name='Rupert'))
session2.commit()

try:
    # Commit the user without a name raising an IntegrityError
    session1.commit()
except Exception as e:
    print e

# Create a new session and display the new user
session3 = Session()
print session3.query(User).all()
```

Using `scoped_session`:

```python
from sqlalchemy.orm import sessionmaker, scoped_session

session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)

# Try to add a user without a name
# This will raise an IntegrityError when committed
session1 = Session()
session1.add(User(name=None))

# Create a new session and add a new user
# Session.session_factory() always creates a new session
# Session() will reuse an existing session
session2 = Session.session_factory()
session2.add(User(name='Rupert'))
session2.commit()

try:
    # Commit session1 raising an IntegrityError
    session1.commit()
except Exception as e:
    print e

# session1 == session3 because we are using scoped_session
session3 = Session()

try:
    # This will raise an exception because the transaction hasn't been
    # rolled back
    print session3.query(User).all()
except Exception as e:
    print e

# Create a new session and display the new user
session4 = Session.session_factory()
print session4.query(User).all()
```