SQLAlchemy经典映射模型到分片的Postgres数据库 [英] SQLAlchemy Classical Mapping Model to sharded Postgres databases

问题描述

情况:

我有一组12个表(按月表示数据)，分布在6个数据库中.我需要获取任何给定月份中所有这些数据库中的数据的样本集.

I have a set of 12 tables (representing data by month) that are sharded across 6 databases. I need to get a sample set of data across any of these databases for any given month.

为什么我使用古典映射模型而不是声明性模型:

我只需要访问12种类型的表中的1种，因为每次运行此代码时，我将只收集给定一个月的数据样本.经典映射模型使我可以在运行时动态定义要映射到的表名，而不必像我声明的那样需要为6个数据库中的所有12个表创建映射.

I only require access to 1 of the 12 types of table as I will only be gathering a sample of data for a single given month each time this code is run. The Classical Mapping Model allows me to dynamically define the table name I want to map to at run time, rather than create mappings for all 12 tables across the 6 databases as I believe would be required with Declarative.

问题:

我正在尝试遵循此处提供的实体名称示例映射我的月份数据在6个不同的数据库上对给定月份的每个表进行分类.

I am trying to follow the entity_name example given here mapping my month data class to each of the tables for the given month on the 6 different databases.

但是正在得到一个UnmappedClassError，表明我的所有新类都派生自的基类未映射".

But am getting an UnmappedClassError stating that my base class, which all new classes are derived from, 'is not mapped'.

因此，在尝试初始化我的新映射表type: <class '__main__.db1month1'>之一时，它正在报告UnmappedClassError: Class 'audit.db.orm.mappedclasses.MonthData' is not mapped.

So on trying to initialise one of my new mapped tables type: <class '__main__.db1month1'> it is reporting UnmappedClassError: Class 'audit.db.orm.mappedclasses.MonthData' is not mapped.

有什么想法吗?

如果需要，我可以在这里粘贴代码，但我担心它会有点长.我正在使用entity_name示例中定义的map_class_to_some_table方法进行映射，并且没有更改它.

If needed I can paste in my code here but I'm worried it's a little long. I am using the map_class_to_some_table method defined in the entity_name example for the mappings and haven't altered it.

推荐答案

最终废弃了所有内容，并遵循了

Ended up scrapping all that and following this ShardedSession example instead.

我的最后一堂课看起来像这样:

My final class looks something like this:

class ShardSessionManager(object):

    def __init__(self, month):
        self.month = month

        #Step1: database engines
        self.engines = {}
        for name, db in shard_dbs.iteritems():
            self.engines[name] = create_engine('postgresql+psycopg2://', creator=db.get_connection, client_encoding='utf8')

        #Step2: create session function - bind shard ids to databases within a ShardedSession
        self.create_session = sessionmaker(class_=ShardedSession)
        self.create_session.configure(shards=self.engines,
                                      shard_chooser=self.shard_chooser, 
                                      id_chooser=self.id_chooser, 
                                      query_chooser=self.query_chooser)
        #Step3: table setup
        self._make_tables(self.month)

        #Step4: map classes
        self._map_tables()

    @staticmethod
    def shard_chooser(mapper, instance, clause=None):
        if isinstance(instance, DataTable):
            return id_chooser(instance.brand_id)

    @staticmethod
    def id_chooser(data_id):
        ...

    @staticmethod
    def query_chooser(query):
        ...

    def _make_tables(self, month):
        self.meta = MetaData()
        self.data_table = DataTable(month, self.meta).table 
        ... other tables ...

    def _map_tables(self):
        try:
            mapper(DataTable, self.data_table, 
                   properties={ ... })
            ...

    def get_random_data(self, parent_id):
        session = self.create_session()
        return session.query(DataTable).filter(...

这篇关于SQLAlchemy经典映射模型到分片的Postgres数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！