From fcd4b6fac4296d2b6975e1504dd57d7fc59d3dd5 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Wed, 15 Nov 2023 09:25:40 +0300
Subject: Commit changes on query success.

---
 qc_app/db_utils.py | 19 ++++++++++++++++---
 1 file changed, 16 insertions(+), 3 deletions(-)

(limited to 'qc_app/db_utils.py')

diff --git a/qc_app/db_utils.py b/qc_app/db_utils.py
index 98f8332..7eca93a 100644
--- a/qc_app/db_utils.py
+++ b/qc_app/db_utils.py
@@ -1,5 +1,8 @@
 """module contains all db related stuff"""
-from typing import Tuple, Optional
+import logging
+import traceback
+import contextlib
+from typing import Tuple, Optional, Iterator
 
 from urllib.parse import urlparse
 import MySQLdb as mdb
@@ -14,8 +17,18 @@ def parse_db_url(db_url) -> Tuple:
             parsed_db.password, parsed_db.path[1:], parsed_db.port)
 
 
-def database_connection(db_url: Optional[str] = None) -> mdb.Connection:
+@contextlib.contextmanager
+def database_connection(db_url: Optional[str] = None) -> Iterator[mdb.Connection]:
     """function to create db connector"""
     host, user, passwd, db_name, db_port = parse_db_url(
         db_url or app.config["SQL_URI"])
-    return mdb.connect(host, user, passwd, db_name, port=(db_port or 3306))
+    connection = mdb.connect(
+        host, user, passwd, db_name, port=(db_port or 3306))
+    try:
+        yield connection
+        connection.commit()
+    except mdb.Error as _mdb_err:
+        logging.debug(traceback.format_exc())
+        connection.rollback()
+    finally:
+        connection.close()
-- 
cgit v1.2.3