Create a single table using Flask-SQLAlchemy while using binds

Question

I'm trying to setup multiple databases with the same model in flask-sqlalchemy

A sample model looks like below

db = flask.ext.sqlalchemy.SQLAlchemy(app)

app.config['SQLALCHEMY_DATABASE_URI'] = 'your_default_schema_db_uri'
app.config['SQLALCHEMY_BINDS'] = {'other_schema': ''mysql+pymysql://'+UNMAE+':'+PASS+'@'+SERVERURL+':3306/'+ DBNAME,'##your_other_db_uri}

class TableA(db.Model):
    # This belongs to Default schema, it doesn't need specify __bind_key__
    ...

class TableB(db.Model) :
      # This belongs to other_schema
    __bind_key__ = 'other_schema'
    ...

db.create_all() works fine and creates the tables in their individual schemas.

I was following https://stackoverflow.com/a/34240889/8270017 and wanted to create a single table using:

TableB.__table__.create(db.session.bind, checkfirst=True)

The table gets created in the default bind and not other_schema.

Is there something I'm missing here? How can I fix it so that it gets created in the other schema.

score 0 · Answer 1 · answered Jun 20 '21 at 08:44

0

For PG at least I do it like this:

class TableB(db.Model):
    __table_args__ = {"schema":"schema_name"}

answered Jun 20 '21 at 08:44

kakou

636
2
7
15

tried setting it like above. but then it still creates the table that is mentioned in the SQLALCHEMY_DATABASE_URI. Again issue is only when using TableB.__table__.create. works fine when using - db.create_all() – rakesh Jun 20 '21 at 09:30

score 0 · Accepted Answer · answered Jun 21 '21 at 08:48

0

You need to supply the correct engine to the create function. The correct bind can be retrieved in the following way:

from sqlalchemy.orm import object_mapper

TableB.__table__.create(db.session().get_bind(object_mapper(TableB())), checkfirst=True)

answered Jun 21 '21 at 08:48

rfkortekaas

6,049
2
27
34

Create a single table using Flask-SQLAlchemy while using binds

2 Answers2