-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbfuncs.py
71 lines (65 loc) · 2.92 KB
/
dbfuncs.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
import mysql.connector
from variables import DATABASE_NAME, DATABASE_PASSWORD, DATABASE_USER, DATABASE_HOST
from constants import info
class Database(object):
def _init_(self,connection,cursor):
self.connection = connection
self.cursor = cursor
def connect(self):
print(f'{info} Connecting with the backup database...')
try:
self.connection = mysql.connector.connect( host = DATABASE_HOST,
database = DATABASE_NAME,
user = DATABASE_USER,
password = DATABASE_PASSWORD )
self.cursor = self.connection.cursor(prepared=True)
except Exception as e:
print(e)
def insertSessions(self,sessions):
print(f'{info} Creating backup sessions...')
backupSessions = []
for session in sessions:
query = """INSERT INTO sessions (initDate, duration, score, gameId, gameName, gameEmulator, gamePath, userId, state)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
statement = (
session['initDate'],
session['duration'],
session['score'],
session['gameId'],
session['gameName'],
session['gameEmulator'],
session['gamePath'],
session['userId'],
'CREATED'
)
self.cursor.execute(query, statement)
backupSessions.append({'id': self.cursor.lastrowid, 'session': session})
return backupSessions
def updateSessions(self, sessions, state):
print(f'{info} Updating backup sessions...')
for session in sessions:
query = 'UPDATE sessions SET state = %s WHERE id = %s'
self.cursor.execute(query, (state, session['id']))
def getErrorSessions(self):
query = 'SELECT * FROM sessions WHERE state = "ERROR"'
self.cursor.execute(query)
columns = [desc[0] for desc in self.cursor.description]
rows = self.cursor.fetchall()
sessions = []
for row in rows:
rowSession = dict(zip(columns, row))
id = rowSession.pop('id')
initDate = rowSession.pop('initDate')
rowSession['initDate'] = str(initDate)
sessions.append({'id': id, 'session': rowSession})
return sessions
def takeid(self,valor):
# TODO: this should be changed by a fetch to the api
print("Extrayendo id del usuario...")
query = """SELECT id FROM usuario WHERE id_nfc = %s"""
self.cursor.execute(query,[float(valor)])
return self.cursor.fetchall()[0][0]
def close(self):
self.connection.commit()
self.cursor.close()
self.connection.close()