app: working web prototype

1 files changed, 43 insertions, 0 deletions

diff --git a/app/app_database.py b/app/app_database.py
new file mode 100644
index 0000000..77f092a
--- /dev/null
+++ b/app/app_database.py
@@ -0,0 +1,43 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from os import environ
+from functools import partial
+from contextlib import contextmanager
+
+import psycopg2
+from psycopg2.extras import NamedTupleCursor
+from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED
+
+import embrace
+from embrace import pool
+
+
+# Connection to the PostgreSQL database server.
+# Using a cursor which exposes result rows as named tuples for convenience.
+# TODO: switch to psycopg3 and use asynchronous queries in handlers.
+db_connect = partial(
+    psycopg2.connect,
+    environ['DATABASE_URL'],
+    cursor_factory=NamedTupleCursor)
+
+db_pool = pool.ConnectionPool(db_connect, limit=4)
+
+
+# Turn our annotated SQL queries into Python functions.
+queries = embrace.module('./sql/', auto_reload=True)
+
+
+@contextmanager
+def db_transaction(isolation_level=ISOLATION_LEVEL_READ_COMMITTED):
+    """
+    Get a connection from the connection pool and begin a transaction with the
+    given isolation level. The transaction is automatically rolled back if an
+    uncaught exception escapes the current context. Otherwise, it is
+    automatically committed when finishing with no error.
+    """
+    with db_pool.connect() as conn:
+        conn.set_isolation_level(isolation_level)
+        with queries.transaction(conn) as tx:
+            yield tx