SQLAlchemy automap data and override some columns

Viewed 2943

Is it possible in SQLAlchemy to automap existing database tables into classes but override some some fields of some tables?

I'm pickling the MetaData object, since it takes some time, it contains all the tables, but then when I'm trying to override some objects, it raises an exception that MetaData object is not bound to an Engine or Connection.

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine, MetaData, Column, String, Integer
import os, pickle

class MetadataCache(object):
    def __init__(self, engine, schema):
        self.engine = engine
        self.schema = schema
        self.metadata = None

    @property
    def cache_name(self):
        final_name = '{0}.{1}.cache'.format(self.engine.url.database,
                                            self.schema)
        return final_name

    def get_or_create_metadata(self):

        if os.path.exists(self.cache_name):
            with open(self.cache_name, 'r') as cachefile:
                self.metadata = pickle.load(cachefile)
        else:
            self.metadata = MetaData()
            self.metadata.reflect(bind=self.engine, schema=self.schema)
            with open(self.cache_name, 'w') as cachefile:
                pickle.dump(self.metadata, cachefile)

        return self.metadata

engine = create_engine('...')
metadata = MetadataCache(engine, 'schemaname').get_or_create_metadata()
Base = automap_base(metadata=metadata)


class User(Base):
    __tablename__ = 'user'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String)

class Profile(Base):
    __tablename__ = 'profile'
    id = Column('id', Integer, primary_key=True)
    userid = Column('userid', ForeignKey('user.id'))

Base.prepare(reflect=True)
1 Answers
Related