SQLAlchemy automap data and override some columns

Question

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)

Answer 1

The error is raised because the metadata owned by Base (that you pass to the automap_base constructor) is not bound to an engine, nor should it be. It just means you have to pass a bind to Base.prepare.

Also, since you're reflecting the metadata manually, you shouldn't tell Base.prepare to do so.

Change your final line to this:

Base.prepare(engine)

Edit: Of course, I was thinking about Base.prepare needing the engine to do the reflection, but since you're passing a pre-reflected MetaData(), you can just use:

Base.prepare()

Edit:

In reply to the comment below, note that you must pass extend_existing=True to the Table:

class User(Base):
    __tablename__ = 'user'
    __table_args__ = {'extend_existing': True}

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