|
1
|
|
|
""" |
|
2
|
|
|
Helper class for mysql database usage |
|
3
|
|
|
""" |
|
4
|
|
|
|
|
5
|
|
|
__author__ = "Frederik Glücks" |
|
6
|
|
|
__email__ = "[email protected]" |
|
7
|
|
|
__copyright__ = "Frederik Glücks - Glücks GmbH" |
|
8
|
|
|
|
|
9
|
|
|
# Built-in/Generic Imports |
|
10
|
|
|
import os |
|
11
|
|
|
import logging |
|
12
|
|
|
import hashlib |
|
13
|
|
|
import mysql.connector |
|
14
|
|
|
import mysql.connector.cursor |
|
15
|
|
|
|
|
16
|
|
|
# own libs |
|
17
|
|
|
from .data_caching import DataCaching |
|
18
|
|
|
|
|
19
|
|
|
|
|
20
|
|
|
class Database: |
|
21
|
|
|
""" |
|
22
|
|
|
Helper class for mysql database usage |
|
23
|
|
|
""" |
|
24
|
|
|
__cnx: mysql.connector = None |
|
25
|
|
|
__cursor: mysql.connector.cursor = None |
|
26
|
|
|
|
|
27
|
|
|
@staticmethod |
|
28
|
|
|
def open(): |
|
29
|
|
|
""" |
|
30
|
|
|
Creates a database connection by using environment vars. |
|
31
|
|
|
|
|
32
|
|
|
Environment vars: |
|
33
|
|
|
RDS_HOST = database host, default value "localhost" |
|
34
|
|
|
RDS_HOST_PORT = database port, default value "3306" |
|
35
|
|
|
RDS_DATABASE = database name, default value "" |
|
36
|
|
|
RDS_USERNAME = username, default value "" |
|
37
|
|
|
RDS_PASSWORD = password, default value "" |
|
38
|
|
|
|
|
39
|
|
|
:return: none |
|
40
|
|
|
""" |
|
41
|
|
|
host: str = os.getenv('RDS_HOST', 'localhost') |
|
42
|
|
|
port: str = os.getenv('RDS_HOST_PORT', '3306') |
|
43
|
|
|
database: str = os.getenv('RDS_DATABASE', 'None') |
|
44
|
|
|
username: str = os.getenv('RDS_USERNAME', '') |
|
45
|
|
|
password: str = os.getenv('RDS_PASSWORD', '') |
|
46
|
|
|
|
|
47
|
|
|
Database.__cnx = mysql.connector.connect(username=username, |
|
48
|
|
|
password=password, |
|
49
|
|
|
host=host, |
|
50
|
|
|
database=database, |
|
51
|
|
|
port=port) |
|
52
|
|
|
|
|
53
|
|
|
Database.__cnx.autocommit = True |
|
54
|
|
|
|
|
55
|
|
|
Database.__cursor = Database.__cnx.cursor(dictionary=True) |
|
56
|
|
|
|
|
57
|
|
|
@staticmethod |
|
58
|
|
|
def close(): |
|
59
|
|
|
""" |
|
60
|
|
|
Closes the database connection |
|
61
|
|
|
|
|
62
|
|
|
:return: none |
|
63
|
|
|
""" |
|
64
|
|
|
if Database.__cnx: |
|
65
|
|
|
Database.__cursor.close() |
|
66
|
|
|
Database.__cnx.close() |
|
67
|
|
|
|
|
68
|
|
|
@staticmethod |
|
69
|
|
|
def fetchall_without_cache(query: str, param: tuple = ()) -> dict: |
|
70
|
|
|
""" |
|
71
|
|
|
Returns the result of the query as dict. |
|
72
|
|
|
|
|
73
|
|
|
The luckywood DataCaching class will NOT be used. |
|
74
|
|
|
|
|
75
|
|
|
:param query: str |
|
76
|
|
|
:param param: tuple |
|
77
|
|
|
:return: dict |
|
78
|
|
|
""" |
|
79
|
|
|
if Database.__cursor is None: |
|
80
|
|
|
Database.open() |
|
81
|
|
|
|
|
82
|
|
|
if query == "": |
|
83
|
|
|
raise RuntimeError("Empty query") |
|
84
|
|
|
|
|
85
|
|
|
Database.__cursor.execute(query, param) |
|
86
|
|
|
|
|
87
|
|
|
return Database.__cursor.fetchall() |
|
88
|
|
|
|
|
89
|
|
|
@staticmethod |
|
90
|
|
|
def fetchall_with_cache(query: str, param: tuple = ()) -> dict: |
|
91
|
|
|
""" |
|
92
|
|
|
Returns the result of the query as dict by using the luckywood DataCaching |
|
93
|
|
|
class. |
|
94
|
|
|
|
|
95
|
|
|
:param query: str |
|
96
|
|
|
:param param: tuple |
|
97
|
|
|
:return: dict |
|
98
|
|
|
""" |
|
99
|
|
|
tuple_str: str = "" |
|
100
|
|
|
|
|
101
|
|
|
for var in param: |
|
102
|
|
|
tuple_str += str(var) |
|
103
|
|
|
|
|
104
|
|
|
query_hash: str = hashlib.md5(query.encode() + tuple_str.encode()).hexdigest() |
|
105
|
|
|
cache_name = "database" |
|
106
|
|
|
|
|
107
|
|
|
result_set = DataCaching.get(cache_name, query_hash) |
|
108
|
|
|
|
|
109
|
|
|
if len(result_set) == 0: |
|
110
|
|
|
logging.info("Query load from database") |
|
111
|
|
|
result_set = Database.fetchall_without_cache(query, param) |
|
112
|
|
|
DataCaching.set(cache_name, query_hash, result_set) |
|
113
|
|
|
else: |
|
114
|
|
|
logging.info("Query load from cache") |
|
115
|
|
|
|
|
116
|
|
|
return result_set |
|
117
|
|
|
|
|
118
|
|
|
@staticmethod |
|
119
|
|
|
def query(query: str, param: tuple = ()) -> int: |
|
120
|
|
|
""" |
|
121
|
|
|
Runs a query and returns the number of affected rows |
|
122
|
|
|
|
|
123
|
|
|
:param query: str |
|
124
|
|
|
:param param: tuple |
|
125
|
|
|
:return: int |
|
126
|
|
|
""" |
|
127
|
|
|
if Database.__cursor is None: |
|
128
|
|
|
Database.open() |
|
129
|
|
|
|
|
130
|
|
|
Database.__cursor.execute(query, param) |
|
131
|
|
|
|
|
132
|
|
|
if Database.__cursor.fetchwarnings() is not None: |
|
133
|
|
|
logging.info(Database.__cursor.fetchwarnings()) |
|
134
|
|
|
|
|
135
|
|
|
return Database.__cursor.rowcount |
|
136
|
|
|
|
gluecks-gmbh /
luckywood
