Create PostgreSQL schemas before create_all function start creating the tables in Flask-SQLAlchemy

Question

I am starting to develop an application with Flask and PostgreSQL for my back-end and I am using PostgreSQL schemas.

My problem is that, when I call the database.create_all() function, SQLalchemy thrown me an error because the schema doesn't exist.

I trying to create the required schemas before the creation process start, using SQLAlchemy events and CreateSchema, but I don't understand well how to use the CreateSchema, with the next code:

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.event import listens_for
from sqlalchemy.schema import CreateSchema


database = SQLAlchemy()

@listens_for(database.metadata, 'before_create')
def create_schemas(target, b, **kw):
    CreateSchema('app')
    database.session.commit()

The listener is called, but the error still persist. The schema isn't being created in the database.

NOTE: The database is initialized with the Flask application instance in another module as following:

from .model import database
database.init_app(app)

with app.app_context():
    database.create_all()

So, I am not getting any problem related to application context.

EDIT:

CreateSchema represent a CREATE SCHEMA sql statement, that must be executed with database.session.execute(CreateSchema('app')).

Now I realize that the listener is called every time one table is created, throwing the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) schema "app" already exists

Thanks.

José María · Accepted Answer · 2020-06-03 00:47:54Z

The fast way I solved my problem was creating the schemas right before I call database.create_all(). I used the statement CREATE SCHEMA IF NOT EXISTS schema_name that creates a schema just if it doesn't exist. This statement is just available starting from PostgreSQL 9.3

# my model module
def create_schemas():
    """ Note that this function run a SQL Statement available just in
        PostgreSQL 9.3+
    """

    database.session.execute('CREATE SCHEMA IF NOT EXISTS schema_name')
    database.session.execute('CREATE SCHEMA IF NOT EXISTS schema_name2')    
    database.session.commit()

I initializing the database and calling the function from another module:

 from .model import database, create_schemas

 database.init_app(app)

    with app.app_context():
        create_schemas()
        database.create_all()

Well, this is a solution that works for my specific situation.

Collectives™ on Stack Overflow

Create PostgreSQL schemas before create_all function start creating the tables in Flask-SQLAlchemy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related