database.py
3.84 KB
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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
from enums import Table, States, Services, Datatypes
from services import serviceFactory
import sqlite3, json
class DBconnection:
tables = ["msg","history"]
# Estructura de las tablas
structure = {
Table.id : "INTEGER PRIMARY KEY",
Table.path : "TEXT",
Table.serv : "TEXT",
Table.dest : "TEXT",
Table.type : "TEXT",
Table.info : "TEXT",
Table.conf : "TEXT",
Table.state : "TEXT"
}
def __init__(self,db):
self.db = db
query = "CREATE TABLE IF NOT EXISTS msg("
for column in DBconnection.structure:
query += column + " " + DBconnection.structure[column] + ","
query = query.strip(",") + ")"
# Tabla principal
self.query(query)
# Historial de mensajes enviados que fueron consultados
self.query(query.replace("msg","history",1))
def query(self,query,*args):
if query.upper().find("INSERT") == 0:
return "Use la funcion insert() para insertar"
entities = None
for ar in args:
entities = ar
con = sqlite3.connect(self.db)
cursor = con.cursor()
if entities:
cursor.execute(query,entities)
else:
cursor.execute(query)
rows = cursor.fetchall()
con.commit()
con.close()
return rows
def insert(self,table,insertions):
if not table in DBconnection.tables:
return "La tabla " + table + " no existe o no está contemplada"
query = "INSERT INTO " + table + "("
values = " VALUES("
checked = self.check(insertions)
if type(checked) == str: # error
return checked
for column in insertions:
query += column + ","
values += "'" + str(insertions[column]) + "',"
query = query.strip(",") + ")"
values = values.strip(",") + ")"
con = sqlite3.connect(self.db)
cursor = con.cursor()
cursor.execute(query + values)
cursor.execute("SELECT last_insert_rowid()")
id = cursor.fetchall()[0][0]
con.commit()
con.close()
return id
def update(self,table,comparator,alterations):
if not table in DBconnection.tables:
return "La tabla " + table + " no existe o no está contemplada"
if not Table.validate(comparator[0]):
return "El comparador no es una columna valida"
query = "UPDATE " + table + " SET "
where = " WHERE " + comparator[0] + "=" + str(comparator[1])
checked = self.check(alterations)
if type(checked) == str: # error
return checked
for column in alterations:
query += column + "='" + str(alterations[column]) + "',"
query = query.strip(",")
con = sqlite3.connect(self.db)
cursor = con.cursor()
cursor.execute(query + where)
con.commit()
con.close()
def check(self,information):
valid = True
for column in information:
# columna no existe en la tabla
if not Table.validate(column):
return "La columna '" + column + "' no existe"
# chequeos de validez de datos
if column == Table.serv:
valid = Services.validate(information[column])
elif column == Table.type:
data = json.loads(information[column])
for key in data:
valid = valid and Datatypes.validate(data[key])
elif column == Table.info:
return serviceFactory(information[Table.serv]).validateinfo(information[column])
elif column == Table.state:
valid = States.validate(information[column])
elif column == Table.conf:
valid = information[column] == None or information[column] == 'None' or information[column].lower() == "true" or information[column].lower() == "false"
if not valid:
return "El dato '" + str(information[column]) + "' no es valido"
return None
@staticmethod
def parseToTable(rows):
dictarray = []
for row in rows:
dictrow = {}
i = 0
for column in DBconnection.structure:
dictrow[column] = row[i]
i += 1
dictarray.append(dictrow)
return dictarray