sqlalchemy autoloaded orm persistence

Question

We are using sqlalchemy's autoload feature to do column mapping to prevent hardcoding in our code.

class users(Base):
    __tablename__ = 'users'
    __table_args__ = {
        'autoload': True,
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

Is there a way to serialize or cache autoloaded metadata/orms so we don't have to go through the autoload process every time we need to reference our orm classes from other scripts/functions?

I have looked at beaker caching and pickle but haven't found a clear answer if it is possible or how to do it.

Ideally we run the autload mapping script only when we have committed changes to our database structure but reference a non-autoload/persistent/cached version of our database mapping from all other scripts/functions,

Any ideas?

Priscella

Why dont you do the other way around: define the full blown model in SA. As a side-effect, this will act as your source control for the database schema. Of course, this only works if your SA application has the primary control of the database(s) you are working with – Femoral

database development is handled seperately in my case, means the application wont how full control. However, I found a way to pickle metadata so I only need to reflect via database connection once to create the pickle, the time I use the pickled meta data to reflect which takes a fraction of the time through db connection (see below). – Priscella

Answer 1

What I am doing now is to pickle the metadata after running the reflection through a database connection (MySQL) and once a pickle is available use that pickled metadata to reflect on the schema with the metadata bound to an SQLite engine.

cachefile='orm.p'
dbfile='database'
engine_dev = create_engine(#db connect, echo=True)
engine_meta = create_engine('sqlite:///%s' % dbfile,echo=True)
Base = declarative_base()
Base.metadata.bind = engine_dev
metadata = MetaData(bind=engine_dev)

# load from pickle 
try:
    with open(cachefile, 'r') as cache:
        metadata2 = pickle.load(cache)
        metadata2.bind = engine_meta
        cache.close()
    class Users(Base):
        __table__ = Table('users', metadata2, autoload=True)

    print "ORM loaded from pickle"

# if no pickle, use reflect through database connection    
except:
    class Users(Base):
        __table__ = Table('users', metadata, autoload=True)

print "ORM through database autoload"

# create metapickle
metadata.create_all()
with open(cachefile, 'w') as cache:
    pickle.dump(metadata, cache)
    cache.close()

Any comments if this is alright (it works) or there is something I can improve?

Priscella

you could probably simplify this just to use the one MetaData object, and also just do a simple "if os.path.exists(cachefile)" to determine if you're unpickling or not. "Table('users', metadata, autoload=True)" and such only needs to be stated once since as you've already seen it skips the reflection if the table is already in the MetaData. – Styrene

I think there's no need to close a file when used inside a with statement, but that's not related. Your approach seems interesting, is it working as expected? – Sestet

Answer 2

My solution isn't terribly different from @user1572502's, but might be useful. I place my cached metadata files in ~/.sqlalchemy_cache, but they can be anywhere.

# assuming something like this:
Base = declarative_base(bind=engine)

metadata_pickle_filename = "mydb_metadata_cache.pickle"

# ------------------------------------------
# Load the cached metadata if it's available
# ------------------------------------------
# NOTE: delete the cached file if the database schema changes!!
cache_path = os.path.join(os.path.expanduser("~"), ".sqlalchemy_cache")
cached_metadata = None
if os.path.exists(cache_path):
    try:
        with open(os.path.join(cache_path, metadata_pickle_filename), 'rb') as cache_file:
            cached_metadata = pickle.load(file=cache_file)
    except IOError:
        # cache file not found - no problem
        pass
# ------------------------------------------

# -----------------------------
# Define database table classes
# -----------------------------
class MyTable(Base):
    if cached_metadata:
        __table__ = cached_metadata.tables['my_schema.my_table']
    else:
        __tablename__ = 'my_table'
        __table_args__ = {'autoload':True, 'schema':'my_schema'}

# ... continue for any other tables ...

# ----------------------------------------
# If no cached metadata was found, save it
# ----------------------------------------
if cached_metadata is None:
    # cache the metadata for future loading
    # - MUST DELETE IF THE DATABASE SCHEMA HAS CHANGED
    try:
        if not os.path.exists(cache_path):
            os.makedirs(cache_path)
        # make sure to open in binary mode - we're writing bytes, not str
        with open(os.path.join(cache_path, metadata_pickle_filename), 'wb') as cache_file:
            pickle.dump(Base.metadata, cache_file)
    except:
        # couldn't write the file for some reason
        pass

Important Note!! If the database schema changes, you must delete the cached file to force the code to autoload and create a new cache. If you don't, the changes will be be reflected in the code. It's an easy thing to forget.

Pizor