CODEWITHSUNDEEP
pythonsqlalchemy
Pedro Werneck
Pedro Werneck

Reputation: 41928

SQLAlchemy transparent access to same table accross databases

I'm using MySQL and SQLAlchemy, and I have two databases with identical tables, but different data. Database db1 is used for "hot" data, therefore has much better performance, and db2 for archival. I won't ever need to perform joins across databases, since all related data is moved accordingly.

Whenever I insert or update new data, it goes into db1, and db2 is basically read only, so it's simple for me to have two engines and use a db1 session whenever I commit. However, is there any simple way for SQLAlchemy to transparently query data from both and consolidate the results? For instance, when I add a new row, it always goes into db1, but when I query one with the primary key, I'd like it to search the tables in both db1 and db2 without having to refactor all queries in my code.

Upvotes: 1

Views: 556

Answers (1)

davidism
davidism

Reputation: 127400

You are looking for the Horizontal Sharding extension, an example usage of which is provided in the documentation. This allows you to use a special ShardedSession which uses various dispatch functions to decide which database to talk to.

def shard_chooser(mapper, instance, clause=None):
    """return a shard key based on the instance being handled"""

def id_chooser(query, ident):
    """return a shard key based on primary key"""

def query_chooser(query):
    """return a shard key based on the query"""

create_session = sessionmaker(class_=ShardedSession)
create_session.configure(
    shards={
        # map keys to engines
    },
    shard_chooser=shard_chooser,
    id_chooser=id_chooser,
    query_chooser=query_chooser
)

Upvotes: 2

Related Questions