From 86a50a4f6df9ece982743a3b7ca510846d248909 Mon Sep 17 00:00:00 2001
From: leonoverweel <l.overweel@gmail.com>
Date: Tue, 24 Jan 2017 12:05:15 +0100
Subject: Initial commit

---
 opendc/util/database.py | 82 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 82 insertions(+)
 create mode 100644 opendc/util/database.py

(limited to 'opendc/util/database.py')

diff --git a/opendc/util/database.py b/opendc/util/database.py
new file mode 100644
index 00000000..16fff5f0
--- /dev/null
+++ b/opendc/util/database.py
@@ -0,0 +1,82 @@
+from datetime import datetime
+import json
+import sqlite3
+
+# Get keys from config file
+with open('/var/www/opendc.ewi.tudelft.nl/web-server/config/keys.json') as file:
+    KEYS = json.load(file)
+
+DATETIME_STRING_FORMAT = '%Y-%m-%dT%H:%M:%S'
+
+def execute(statement, t):
+    """Open a database connection and execute the statement."""
+
+    # Connect to the database
+    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    cursor = connection.cursor()
+
+    # Turn on foreign key checks
+    cursor.execute('pragma foreign_keys=ON')
+    
+    # Execute the statement
+    cursor.execute(statement, t)
+
+    # Get the id
+    database_id = cursor.execute('SELECT last_insert_rowid()').fetchone()[0]
+
+    # Disconnect from the database
+    connection.commit()
+    connection.close()
+
+    # Return the id
+    return database_id
+
+def fetchone(statement, t=None):
+    """Open a database connection and return the first row matched by the SELECT statement."""
+
+    # Connect to the database
+    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    cursor = connection.cursor()
+
+    # Execute the SELECT statement
+
+    if t is not None:
+        cursor.execute(statement, t)
+    else:
+        cursor.execute(statement)
+
+    value = cursor.fetchone()
+
+    # Disconnect from the database and return
+    connection.close()
+    return value
+
+def fetchall(statement, t=None):
+    """Open a database connection and return all rows matched by the SELECT statement."""
+
+    # Connect to the database
+    connection = sqlite3.connect(KEYS['DATABASE_LOCATION'])
+    cursor = connection.cursor()
+
+    # Execute the SELECT statement
+    
+    if t is not None:
+        cursor.execute(statement, t)
+    else:
+        cursor.execute(statement)
+
+    values = cursor.fetchall()
+
+    # Disconnect from the database and return
+    connection.close()
+    return values
+
+def datetime_to_string(datetime_to_convert):
+    """Return a database-compatible string representation of the given datetime object."""
+
+    return datetime_to_convert.strftime(DATETIME_STRING_FORMAT)
+
+def string_to_datetime(string_to_convert):
+    """Return a datetime corresponding to the given string representation."""
+
+    return datetime.strptime(string_to_convert, DATETIME_STRING_FORMAT)
-- 
cgit v1.2.3