use context manager in MysqlTimeoutSession

add documentation for database creation

3 files changed, 36 insertions, 24 deletions

diff --git a/README.md b/README.md
index cd60505..1b31200 100644
--- a/README.md
+++ b/README.md
@@ -16,9 +16,20 @@ That's it.
 ### MySQL / MariaDB
 
 1. Create a user 'jwebmail' with a password
+
+    CREATE USER jwebmail@localhost IDENTIFIED BY '$PASSWORD';
+
 2. Create a database 'jwebmaildb1'
+
+    CREATE DATABASE jwebmaildb1;
+    USE jwebmaildb1;
+
 3. Create a table 'session' with the following schema
 
-    (user VARCHAR(64) PRIMARY KEY, password VARCHAR(255), timeout TIMESTAMP(2) NOT NULL);
+    CREATE TABLE session (user char(64) PRIMARY KEY, password varchar(255), timeout timestamp NOT NULL);
+    CREATE INDEX timeout_idx ON session (timeout); -- Optional
 
 4. Grant privileges to the user jwebmail for the above table for at least SELECT, INSERT, UPDATE and DELETE
+
+    GRANT SELECT, INSERT, UPDATE, DELETE PRIVILEGES ON 'jwebmaildb1'.'session' TO 'jwebmail'@'localhost';
+
diff --git a/src/jwebmail/__init__.py b/src/jwebmail/__init__.py
index 631215b..875bcb5 100644
--- a/src/jwebmail/__init__.py
+++ b/src/jwebmail/__init__.py
@@ -36,7 +36,7 @@ else:
 
     toml_read_file = dict(load=toml_load, text=True)
 
-__version__ = "2.3.0.dev3"
+__version__ = "2.3.0.dev4"
 
 csrf = CSRFProtect()
 
diff --git a/src/jwebmail/read_mails.py b/src/jwebmail/read_mails.py
index 44eba8d..404a242 100644
--- a/src/jwebmail/read_mails.py
+++ b/src/jwebmail/read_mails.py
@@ -1,3 +1,4 @@
+from contextlib import closing
 from datetime import datetime, timedelta
 
 from flask import current_app, g
@@ -36,7 +37,7 @@ class RedisTimeoutSession:
 
 
 class MysqlTimeoutSession:
-    def __init__(self, username, passwd, timeout, port=3306):
+    def __init__(self, username, passwd, timeout, database="jwebmaildb1", port=3306):
         import mysql.connector
 
         self.timeout = timeout
@@ -45,35 +46,35 @@ class MysqlTimeoutSession:
             port=port,
             username=username,
             password=passwd,
-            database="jwebmaildb1",
+            database=database,
         )
 
     def set(self, key, value):
         timeout = datetime.now() + timedelta(seconds=self.timeout)
-        cur = self.conn.cursor()
-        cur.execute("DELETE FROM session WHERE user = %s", [key])
-        cur.execute("INSERT INTO session VALUES (%s, %s, %s)", [key, value, timeout])
-        self.conn.commit()
-        cur.close()
 
-    def get(self, key):
-        cur = self.conn.cursor()
-        cur.execute("DELETE FROM session WHERE timeout < NOW()")
-        cur.execute("SELECT password FROM session WHERE user = %s", [key])
-        row = cur.fetchone()
-
-        if row is None:
-            self.conn.commit()
-            cur.close()
-            return None
-        else:
-            timeout = datetime.now() + timedelta(seconds=self.timeout)
+        with closing(self.conn.cursor()) as cur:
+            cur.execute("DELETE FROM session WHERE user = %s", [key])
             cur.execute(
-                "UPDATE session SET timeout = %s WHERE user = %s", [timeout, key]
+                "INSERT INTO session VALUES (%s, %s, %s)", [key, value, timeout]
             )
             self.conn.commit()
-            cur.close()
-            return row[0]
+
+    def get(self, key):
+        with closing(self.conn.cursor()) as cur:
+            cur.execute("DELETE FROM session WHERE timeout < NOW()")
+            cur.execute("SELECT password FROM session WHERE user = %s", [key])
+            row = cur.fetchone()
+
+            if row is None:
+                self.conn.commit()
+                return None
+            else:
+                timeout = datetime.now() + timedelta(seconds=self.timeout)
+                cur.execute(
+                    "UPDATE session SET timeout = %s WHERE user = %s", [timeout, key]
+                )
+                self.conn.commit()
+                return row[0]
 
 
 def select_timeout_session():