I'm trying to create multiple databases using Flask-SQLAlchemy and its bind feature. Creating a single database using SQLALCHEMY_DATABASE_URI
works just fine. But, when I add SQLALCHEMY_BINDS
it fails with the following error:
File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database "customer1" does not exist
This is my config.py file:
class BaseConfig:
"""Base configuration"""
SECRET_KEY = 'random_key'
SQLALCHEMY_TRACK_MODIFICATIONS = False
TESTING = False
class DevelopmentConfig(BaseConfig):
"""Development configuration"""
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL')
SQLALCHEMY_BINDS = {
'customer1': 'postgres://postgres:postgres@auth-db:5432/customer1'
}
And this is my init.py file
db = SQLAlchemy()
migrate = Migrate()
def create_app():
# instantiate app
app = Flask(__name__)
app_settings = os.getenv('APP_SETTINGS')
app.config.from_object(app_settings)
db.init_app(app)
migrate.init_app(app, db)
from project.api.auth import auth_blueprint
app.register_blueprint(auth_blueprint)
@app.shell_context_processor
def ctx():
return { 'app': app, 'db': db }
return app
I'm creating my db via the command line through my manage.py file:
@cli.command('recreate_db')
def recreate_db():
db.drop_all()
db.create_all()
try:
db.session.commit()
except exc.SQLAlchemyError as e:
logging.info(f'{e}')
I'm creating a multi-tenant app and the model I'm trying to create should be created in both databases; my model.py file is like this:
class Widgets(db.Model):
__tablename__ = 'widgets'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(100), nullable=False, unique=True)
create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
I've look all over for an answer and I've found several but even after trying all them, I continue to get this error. Am I missing something? Flask-migrate doesn't work either but I suspect I need to fix this error first. Any help would be appreciated.
db.drop_all()
anddb.create_all()
only drop and create all table.(https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.create_all)If you want to create database (customer1), you should use sqlalchemy-utils. link below is usage.
How to create a new database using SQLAlchemy?