CCTVOwner/database.py at main · gitSomS/CCTVOwner · GitHub

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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
import sqlite3

def connect():
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTs cctv (id INTEGER PRIMARY KEY AUTOINCREMENT, nome TEXT DEFAULT 'Desconhecido', apelido TEXT DEFAULT 'Desconhecido', idade INTEGER DEFAULT 'Desconhecido', pontos INTEGER DEFAULT '1000', obs TEXT DEFAULT 'Nenhum', estado INTEGER DEFAULT '0', id_match INTEGER, face BLOB)")
    conn.commit()
    conn.close()

def insert(nome,apelido,idade,obs,id):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("UPDATE cctv SET nome=?, apelido=?, idade=?, obs=? WHERE id=?",(nome, apelido, idade, obs, id, ))
    conn.commit()
    conn.close()
    view()

def getNameFromID(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT nome FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def getAgeFromID(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT idade FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def getObsFromID(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT obs FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def getPointsFromID(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT pontos FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def setPointsForID(pontos, id_match):
    print(pontos)
    print(id_match)
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("UPDATE cctv SET pontos=? where id_match=?",(pontos, id_match, ))
    conn.commit()
    conn.close()

def matchExist(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    response = cur.execute("SELECT EXISTS(SELECT id FROM cctv WHERE id_match=?)", (id_match, ))
    fetched = response.fetchone()[0]
    # if fetched == 1:
    #     print("sim")
    # else:
    #     print("nao")
    return fetched

def insertMatch(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO cctv(id_match) VALUES(?)",(id_match, ))
    conn.commit()
    conn.close()

def view():
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("SELECT * FROM cctv")
    row = cur.fetchall()
    conn.close()
    return row

def search(nome="",apelido="",pontos="",idade="",obs="",id_match=""):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("SELECT * FROM cctv WHERE nome=? OR apelido=? OR idade=? OR pontos=? OR obs=? OR id_match=?",(nome,apelido,idade,pontos,obs,id_match))
    row = cur.fetchall()
    conn.close()
    return row

def delete(id):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    #cur.execute("DELETE FROM cctv where id=?",(id,))
    cur.execute("UPDATE cctv SET nome= 'Desconhecido', apelido= 'Desconhecido', idade= 'Desconhecido' WHERE id=?",(id, ))
    conn.commit()
    conn.close()

def update(id,nome,apelido,idade):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("UPDATE cctv SET nome=? ,apelido=? , idade=? where id=?",(nome,apelido,idade,id))
    conn.commit()
    conn.close()

def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
    return photo

def updateFace(face, id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("UPDATE cctv SET face=? where id_match=?",(face, id_match, ))
    conn.commit()
    conn.close()

def getFaceFromID(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT face FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def convertFaceCodeToImage(face, path):
    with open(path, 'wb') as file:
        file.write(face)

def getState(id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute('SELECT estado FROM cctv WHERE id_match = ?', (id_match, ))
    data = cur.fetchone()[0]
    conn.close()
    return data

def setState(state, id_match):
    conn = sqlite3.connect("cctv.db")
    cur = conn.cursor()
    cur.execute("UPDATE cctv SET estado=? where id_match=?",(state, id_match, ))
    conn.commit()
    conn.close()

connect()