SQLAlchemy - mapping one class to two tables

I have two implementations of database queue (they use different tables) and want them to use objects of the same class. So, they both look really similar:

class AbstractDBQueue(object):
    def __init__(self, tablename):
        self.tablename = tablename
        self.metadata = MetaData()
        self.engine = create_engine('mysql+mysqldb://%s:%s@%s:%d/%s' % (
            settings.DATABASE.get('USER'),
            settings.DATABASE.get('PASSWORD'),
            settings.DATABASE.get('HOST') or '127.0.0.1',
            settings.DATABASE.get('PORT') or 3306,
            settings.DATABASE.get('NAME')
        ), encoding='cp1251', echo=True, pool_recycle=7200)
        self.metadata.bind = self.engine
        self.session = sessionmaker(bind=self.engine)()

    def setup_table(self, table, entity_name):
        self.table = table
        newcls = type(entity_name, (SMSMessage, ), {})
        mapper(newcls, table)
        return newcls

    def put(self, message=None, many_messages=[]):
        if message:
            self.session.add(message)
        else:
            for m in many_messages:
                self.session.add(m)
        self.session.commit()

    def get(self, limit=None):
        if limit:
            q = self.session.query(self.SMSClass).limit(limit)
        else:
            q = self.session.query(self.SMSClass)
        smslist = []
        for sms in q:
            smslist.append(sms)
        self.session.expunge_all()
        return smslist

class DBQueue(AbstractDBQueue):
    """
    MySQL database driver with queue interface
    """
    def __init__(self):
        self.tablename = settings.DATABASE.get('QUEUE_TABLE')
        super(DBQueue, self).__init__(self.tablename)
        self.logger = logging.getLogger('DBQueue')
        self.SMSClass = self.setup_table(Table(self.tablename, self.metadata, autoload=True), "SMSQueue")

class DBWorkerQueue(AbstractDBQueue):
    开发者_如何转开发"""
    MySQL database driver with queue interface for separate workers queue
    """

    def __init__(self):
        self.tablename = settings.DATABASE.get('WORKER_TABLE')
        super(DBWorkerQueue, self).__init__(self.tablename)
        self.logger = logging.getLogger('DBQueue')
        self.SMSClass = self.setup_table(Table(self.tablename, self.metadata, autoload=True), "SMSWorkerQueue")

    def _install(self):
        self.metadata.create_all(self.engine)

SMSMessage is the name of the class I want to use. The map_class_to_table() function is a hack I've found in SQLAlchemy documentation: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName

But it doesn't seems to help - when the first queue instance maps SMSMessage to it's table, then all objects I pass to second queue's put() are implicitly casted to first queue's mapped class, and second database is still empty after session.commit().

I need to use both queues at the same time, maybe even using threads (I think, pool connection will be useful), but I just can't make this work. Could you help, please?

I think your problem relates to the tablename variable. It's a class variable which gets defined when you create the class, and then does not change. So, it will be the same for both of your instances, when they access it with self.tablename. To fix this, move it inside the init function, and make it a self.tablename. That will initialize it each time you create a new object.