sqlalchemy dynamic binding to model

Question

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?

Hello, Did you manage to get a solution for this?

Nix
– Nix

2022-09-19 06:24:21 +00:00
Commented Sep 19, 2022 at 6:24 — Nix
– Nix, Commented Sep 19, 2022 at 6:24

Sam Hartman · Accepted Answer · 2017-05-16 12:16:55Z

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.

answered May 16, 2017 at 12:16

Sam Hartman

6,5393 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gCoh Over a year ago

multiple binds are mapped using sqlalchemy mapper object - docs.sqlalchemy.org/en/latest/orm/…. this object have three different types of mapping: docs.sqlalchemy.org/en/latest/orm/mapping_styles.html. is it possible binding a request arg with bind using mapper?

Tony D Over a year ago

What if we're building a SaaS application where the number of databases (one DB per tenant on MySQL since no "schemas") is constantly in flux (companies coming & going)? Basically we're looking for a way to dynamically generate the bind path (inclusive of DB Name) with a variable (customer ID).

gCoh · Accepted Answer · 2017-05-16 13:28:54Z

1

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

answered May 16, 2017 at 13:28

gCoh

3,0991 gold badge28 silver badges56 bronze badges

Collectives™ on Stack Overflow

sqlalchemy dynamic binding to model

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related