SQLAlchemy - Multiple Database Issues

Question

We are making a game server using SQLAlchemy.

because game servers must be very fast, we have decided to separate databases depending on user ID(integer).

so for example I did it successfully like the following.

from threading import Thread
from sqlalchemy import Column, Integer, String, DateTime, create_engine
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import sessionmaker

DeferredBase = declarative_base(cls=DeferredReflection)
class BuddyModel(DeferredBase):
    __tablename__ = 'test_x'

    id = Column(Integer(), primary_key=True, autoincrement=True)
    value = Column(String(50), nullable=False)

and the next code will create multiple databases.

There will be test1 ~ test10 databases.

for i in range(10):
    url = 'mysql://user@localhost/'
    engine = create_engine(url, encoding='UTF-8', pool_recycle=300)
    con = engine.connect()
    con.execute('create database test%d' % i)

the following code will create 10 separate engines.

the get_engine() function will give you an engine depending on the user ID.

(User ID is integer)

engines = []
for i in range(10):
    url = 'mysql://user@localhost/test%d'% i

    engine = create_engine(url, encoding='UTF-8', pool_recycle=300)

    DeferredBase.metadata.bind = engine
    DeferredBase.metadata.create_all()
    engines.append(engine)

def get_engine(user_id):
    index = user_id%10
    return engines[index]

by running prepare function, the BuddyModel class will be prepared, and mapped to the engine.

def prepare(user_id):
    engine = get_engine(user_id)
    DeferredBase.prepare(engine)

** The next code will do what I want to do exactly **

for user_id in range(100):
    prepare(user_id)

    engine = get_engine(user_id)
    session = sessionmaker(engine)()
    buddy = BuddyModel()
    buddy.value = 'user_id: %d' % user_id
    session.add(buddy)
    session.commit()

But the problem is that when I do it in multiple threads, it just raise errors

class MetalMultidatabaseThread(Thread):

    def run(self):
        for user_id in range(100):
            prepare(user_id)

            engine = get_engine(user_id)
            session = sessionmaker(engine)()
            buddy = BuddyModel()
            buddy.value = 'user_id: %d' % user_id
            session.add(buddy)
            session.commit()
threads = []
for i in range(100):
    t = MetalMultidatabaseThread()
    t.setDaemon(True)
    t.start()
    threads.append(t)

for t in threads:
    t.join()

the error message is ...

ArgumentError: Class '<class '__main__.BuddyModel'>' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper.  clear_mappers() will remove *all* current mappers from all classes.

so.. my question is that How CAN I DO MULTIPLE-DATABASE like the above architecture using SQLAlchemy?

score 1 · Answer 1 · answered Jan 10 '15 at 22:52

this is called horizontal sharding and is a bit of a tricky use case. The version you have, make a session based on getting the engine first, will work fine. There are two variants of this which you may like.

One is to use the horizontal sharding extension. This extension allows you to create a Session to automatically select the correct node.

The other is more or less what you have, but less verbose. Build a Session class that has a routing function, so you at least could share a single session and say, session.using_bind('engine1') for a query instead of making a whole new session.

score 0 · Accepted Answer · answered Jan 08 '15 at 00:56

I have found an answer for my question.

For building up multiple-databases depending on USER ID (integer) just use session.

Before explain this, I want to expound on the database architecture more.

For example if the user ID 114 connects to the server, the server will determine where to retrieve the user's information by using something like this.

user_id%10 # <-- 4th database

Architecture

DATABASES 
  - DB0 <-- save all user data whose ID ends with 0 
  - DB1 <-- save all user data whose ID ends with 1
  .
  .
  .
  - DB8 <-- save all user data whose ID ends with 9

Here is the answer

First do not use bind parameter.. simply make it empty.

Base = declarative_base()

Declare Model..

class BuddyModel(Base):
    __tablename__ = 'test_x'

    id = Column(Integer(), primary_key=True, autoincrement=True)
    value = Column(String(50), nullable=False)

When you want to do CRUD ,make a session

engine = get_engine_by_user_id(user_id)
session = sessionmaker(bind=engine)()

buddy = BuddyModel()
buddy.value = 'This is Sparta!! %d' % user_id
session.add(buddy)
session.commit()

engine should be the one matched with the user ID.

SQLAlchemy - Multiple Database Issues

2 Answers2