title: What is the best way to handle sql connection in http server (Flask) without ORM in Python?
body: <p>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 hadling function, so I can be sure that number of connections to db will not exceed the certain number, but it creates overhead. I am sure that the same mysql connections can be used by other users, what other approach to handle sql connection and cursor I can use, if I do not use ORM ?</p>
  
  <p><strong>Context managers to hangle cursor and connection:</strong></p>
  
  <pre class="lang-py prettyprint-override"><code>from contextlib import contextmanager
  import mysql.connector
  from mysql.connector.errors import Error
  
  @contextmanager
  def mysql_connection(user, password, host, database, auth_plugin):
      _conn = mysql.connector.connect(user=user, password=password, host=host, database=database, auth_plugin=auth_plugin)
      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:
          raise
      else:
          # if everything is fine commit all changes to save them in db:
          _conn.commit()
      finally:
          # close connection to db, do not wait for timeout release:
          _conn.close()
  
  
  @contextmanager
  def mysql_curs(user, password, host, database, auth_plugin) -&gt; "curs":
      with mysql_connection(user=user, password=password, host=host, database=database, auth_plugin=auth_plugin) as _conn:
          _curs = _conn.cursor()
          try:
              yield _curs
          finally:
              _curs.close()  # close cursor when everything is done
  </code></pre>
  
  <p><strong>Some random Flask http handler function:</strong></p>
  
  <pre class="lang-py prettyprint-override"><code>
  @app.route('/admin_panel/repair', methods=["GET"])
  def repair_show_all_menu_webpages():
      """The page exists to repair menu if not existent flask function was added"""
      try:
          with common_db_ops.mysql_curs() as curs:
              left_side_menu = []
              webpages = admin_ops.show_all_menu_webpages_to_repair(curs)
      except (Exception, Error) as err:
          app.logger.error(f"Failed to repair website: {err}")
          abort(500)
  
      return render_template('repair_menu.html', webpages=webpages, left_side_menu=left_side_menu)
  
  </code></pre>
  
  <p><strong>Edit: I would like to add that I found the following article which discuss how to use Flask with PostgreSQL and create your customized sql connection context manager, but I have question where in Flask I should declare sql connectors Pool:</strong></p>
  
  <p><a href="https://medium.com/@thegavrikstory/manage-raw-database-connection-pool-in-flask-b11e50cbad3" rel="nofollow noreferrer">Manage RAW database connection pool in Flask</a></p>