(Simple) Read-Only SqlAlchemy Sessions

The Right Way™ to make database access read-only is to create a read-only user.   So, why not just Do It Right™ ?

  1. Not all databases (eyes at you Sqlite!) support these fancy “users”
  2. Sometimes creating this second user (and changing configuration files during program invocation) is overkill, or a hassle.
  3. During development, it’s nice to be able to temporarily make a database read-only, or read-only from a particular session.
  4. I am very lazy.

In SQLAlchemy, there is a simple solutionmonkeypatch the session.flush method.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

## Based on code by Yannick Gingras

def abort_ro(*args,**kwargs):
    ''' the terrible consequences for trying 
        to flush to the db '''
    print "No writing allowed, tsk!  We're telling mom!"

def db_setup(connstring='sqlite:///:memory:', 
               echo=False, readonly=True):
    engine = create_engine(connstring, echo=echo)
    Session = sessionmaker(bind=engine, autoflush=False, autocommit=False)
    session = Session()
    if readonly:
        session.flush = abort_ro   # now it won't flush!

    return session, engine

Session objects are still writable within the session, and this functionality can now be enforced at the session level.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s