Flask, how to use SQL alchemy session in REST application properly?

Viewed 342

Thanks in advance for your patience. I am a flask beginner trying to understand the proper approach when building a REST api for many concurrent users using sql-alchemy ORM.

Info: I have a python project with many entities written using SQL alchemy declarative base, for example:

Base = declarative_base(cls=Base)
class Training(Entity, Base):
      id = Column(BigInteger, Sequence('training_id_seq', start=1, increment=1))
      meet           = Column(Integer,  primary_key=True, default=Constants.DefaultInteger)
      trdate         = Column(Date,      primary_key=True)
      hrno           = Column(String,    primary_key=True)
      sptime         = Column(DateTime,  primary_key=True)
      part      = Column(String,    nullable=True)
      partno    = Column(String,    nullable=True)

Now I would also like to use these entities in my Rest API using flask.

app = Flask('rest-api', template_folder='../templates', static_folder='../static')
CORS(app)

@app.errorhandler(InvalidUsage)
def handle_invalid_usage(error):
     response = jsonify(error.to_dict())
     response.status_code = error.status_code
     return response


if __name__ == "__main__":
app.config['SQLALCHEMY_DATABASE_URI'] = ServerConstants.FUNDAMENTAL_DB
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.register_blueprint(training_api)
app.run(host=ServerConstants.host, port=ServerConstants.port, debug=ServerConstants.debug,
        ssl_context=('cert.pem', 'key.pem'))

Using blueprints:

training_api = Blueprint('training_api', __name__)

@training_api.route("/training/get", methods=['GET'])
     def get_training():
     **session = Connector.Session()**

     query = session.query(Training)
     query = order_by_latest(query, Training)
     query = add_pagination(request, query)
     trainings = query.all()

     **session.close()**

     return respond_with_json({'trainings': [t.toDict() for t in trainings]})

Where Connector.Session() is saved sessionmaker to open a new session for each request:

from sqlalchemy.orm import sessionmaker
from tools import ServerConstants

Session = sessionmaker(bind=ServerConstants.ENGINE)

Now with your experience you can possibly see many things that I am doing wrong.

My questions are:

  1. Is it good idea to open and close the session inside the rest method just like I am doing it?

  2. Is it possible to have it set up like this for many concurrent users?

  3. Why some documentation suggests using the following construct?

    db = SQLAlchemy(app)
    db.session.query(Training)
    ...
    

    3.1 In this scenario I m very unsure where and when to close the session?

  4. In some specific rest methods I need to use psycopg connection and I access it using the following approach:

      session.connection()
    

    4.1 Again what are the implications of me opening session and the again open connections from that session for each of the clients requesting the api?

  5. Is there an elegant approach to solve sqlalchemy querying inside rest application with many concurrent users? I've seen flask-sqlalchemy, but that requires me to recode all of my entitities using their style and possibly restructure the whole application or am I wrong?

Thank you for all your suggestions and help.

0 Answers
Related