5

I'm looking for a way binding models to different databases (schema is identical in all databases), for instance - separated by countries

class User():
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(255))
    age = Column(Integer)

This model is shared between multiple databases:

postgresql://postgres:@localhost/US

postgresql://postgres:@localhost/RU

Multiple binds is possible using flask: Flask-SQLAlchemy multiple databases and binds but bind key is hardcoded in the model http://flask-sqlalchemy.pocoo.org/2.1/binds/

Is there a way binding it dynamically to the model?

Community
  • 1
  • 1
gCoh
  • 2,719
  • 1
  • 22
  • 46

2 Answers2

1

See the get_bind method of sqlalchemy's documentation for the full documentation on how sqlalchemy chooses to bind tables.

Ignoring flask, my recommendation would be to set the bind argument on your session objects based on which country you'd like to access. So for example say sess is your session object. you could execute

sess.bind = choose_bind_from_request_data(r)

You'd want to make sure the session had no active transaction when you change the bind, although it would be a better design to create a new session and set the bind rather than changing the bind on an existing session.

It doesn't look like it is easy to dynamically choose a per-table bind, although as pointed out above dynamic per-session binds are easy.

Sam Hartman
  • 6,210
  • 3
  • 23
  • 40
  • multiple binds are mapped using sqlalchemy mapper object - http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds. this object have three different types of mapping: http://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html. is it possible binding a request arg with bind using mapper? – gCoh May 16 '17 at 12:50
1

the suggested solution for this case (vertical partitioning): http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html#custom-vertical-partitioning

gCoh
  • 2,719
  • 1
  • 22
  • 46