What Is The Best Way To Handle Sql Connection In Http Server (flask) Without Orm In Python?

I am using Flask with MySQL (MariaDB) database. To handle sql connection and cursor I use self-made context manager. I open and close connection inside each Flask http request hadl

Solution 1:

Try to pool connections

From offical docs:

A pool opens a number of connections and handles thread safety when providing connections to requesters

Implementing connection pooling, you can reuse existing connections

dbconfig = {
  "database": "test",
  "user":     "joe"
}

cnxpool = mysql.connector.connect(pool_name = "mypool",
                                  pool_size = 3,    # or any number to suit your need
                                  **dbconfig)


# then to get a connection from pool use
cnx = cnxpool.get_connection()

For more see: https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

Solution 2:

If anybody is interested in the approach of handling sql connection without ORM, I made the following steps to combine MySQL Connections Pool, context manager and Flask:

SQL_CONN_POOL = pooling.MySQLConnectionPool(
    pool_name="mysqlpool",
    pool_size=10,
    user=DB_USER,
    password=DB_PASS,
    host=DB_HOST,
    database=DATABASE,
    auth_plugin=DB_PLUGIN
)


@contextmanagerdefmysql_connection_from_pool() -> "conn":
    conn_pool = SQL_CONN_POOL  # get connection from the pool, all the rest is the same# you can add print(conn_pool) here to be sure that pool# is the same for each http request

    _conn = conn_pool.get_connection()
    try:
        yield _conn
    except (Exception, Error) as ex:
        # if error happened all made changes during the connection will be rolled back:
        _conn.rollback()
        # this statement re-raise error to let it be handled in outer scope:raiseelse:
        # if everything is fine commit all changes to save them in db:
        _conn.commit()
    finally:
        # actually it returns cursor to the pool, rather than close it
        _conn.close()


@contextmanagerdefmysql_curs_from_pool() -> "curs":
    with mysql_connection_from_pool() as _conn:
        _curs = _conn.cursor()
        try:
            yield _curs
        finally:
            _curs.close()

I used the following links to answer the question:

Manage RAW database connection pool in Flask

MySQL docs