sqlalchemy autoloaded orm persistence

user_1572502 0
Viewed 2534

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?

autoload sqlalchemy caching orm python
2 Answers

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.

user_27126520
Related