Geek asks and answers

Sqlalchemy autoloaded orm persistence

We use sqlalchemy autoload function to match columns to prevent hard coding 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 automatically loaded metadata / eagles, so we don’t need to go through the autoload process every time we need to reference our orm classes from other scripts / functions?

I looked at the cache-kaker and pickle, but did not find a clear answer if this is possible or how to do it.

Ideally, we run an autload script mapping only when we make changes to our database structure, but refer to an unauthorized / persistent / cached version of our database mapping from all other scripts / functions,

Any ideas?

+6
2

, , - (MySQL), , , , SQLite.

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()

, () -, ?

+6

@user1572502, . ~/.sqlalchemy_cache, .


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

metadata_pickle_filename = "mydb_metadata_cache.pickle"

# ------------------------------------------
# Load the cached metadata if it 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

!! , , . , . .

0

More articles:


All Articles