MYSQL sessions

A MYSQL database will be created for every application that gets installed in a portal. The database name will be of the following format

<provider-code>_<app-code>

For example, an application with provider code ‘aalam’ and app code ‘base’ the database name will be aalam_base. The database name of the application be obtained from the api

aalam_common.sqa.get_database_name()

These databases are unique for the application. No application can access the database of another application. The database connection details can be in the .ini config file which can accessed from aalam_common.config.cfg. The database connection will be present in sqlalchemy section under the name url.

See configs

SQLAlchemy

This framework uses sqlalchemy for managing relational database access. It is not mandatory for the application to use sqlalchemy. We might use other relational databases in the future. So, by using SQLAlchemy, it offers tremendous flexibility in such cases.

SQLAlchemy is widely used and there are lots of sqlalchemy questions answered in sites like stackoverflow.

Engine Initialization

If an application chooses to use sqlalchemy, it should initialize the database engine.

The database engine is created automatically by

aalam_common.sqa.init_engine(db_name, declarative_base)

Parameters db_name is the database name, declarative_base is the base on which the table classes are defined. The declarative base should be instantiated by the application before declaring the tables, for example

import aalam_common

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TableName1(Base):
    __tablename__ = "table-1"
    __table_args__ = {"schema": aalam_common.sqa.get_database_name()}

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255), nullable=False)

The init_engine should be called before accessing any session. Application’s entry_point is the ideal place to call init_engine.

Sessions

For every request a scoped session will be instantiated. The session can be accessed from the request object with the attribute sqa_session, like

request.sqa_session

After every request ends, the framework is responsible to cleanup the session. If there are an pending transactions in the session after the request, based upon the response status code of the handler, the frameworks commits or disposes the transactions.

If the status set by the handler is in the range of 200 - 399, the transactions will be commited. Whereas the transaction will be rolled back if the status code is in the range of 400 - 599.

New session

If an application desires to use a session outside the scope of a request, it can do so by calling aalam_common.sqa.Session(db_name). If a session is used in this manner the client of this session is responsible in cleaning it up. After the session is used, the client must issue session.remove() to close the connection after commit/rollback of the session.