SQLAlchemy - mapping one class to two tables

Question

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?

Hmm, if you use the second queue first, does it reverse the order? — Spencer Rathbun, Oct 11 '11 at 15:05
I've just rewritten the code using inheritance, now it just raises an error "Class 'sms.message.SMSMessage' is not mapped". Should I cast input objects to mapped class? It seems strange. — Enchantner, Oct 11 '11 at 15:19

score 0 · Answer 1 · edited May 23 '17 at 09:59

0

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.

edited May 23 '17 at 09:59

Community

1
1

answered Oct 11 '11 at 14:49

Spencer Rathbun

14,510
6
54
73

I've updated code in topic - rewritten to use inheritance for both queue classes while passing tablename as a constructor argument. But it doesn't work, too. – Enchantner Oct 11 '11 at 15:02

SQLAlchemy - mapping one class to two tables

1 Answers1