-
Notifications
You must be signed in to change notification settings - Fork 0
/
dboperate.py
160 lines (144 loc) · 5.05 KB
/
dboperate.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
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
148
149
150
151
152
153
154
155
156
157
158
159
import mysql.connector
import hashlib
import os
import fnmatch
from helper import litowin
import json
with open('dbconfig.json') as f:
data = json.loads(f.read())
somasdb = mysql.connector.connect(
host=data['host'],
user=data['user'],
passwd=data['password'],
auth_plugin='mysql_native_password',
database=data['db']
)
db_cursor = somasdb.cursor()
def isExistsDB(id, attribute, table):
db_cursor.execute(f'''SELECT {attribute} FROM {table} WHERE {table}.{attribute} = '{id}';''')
value = db_cursor.fetchall()
try:
if value[0][0]==id:
return True
except:
return False
def isExistsComposite(id1,id2,attribute1,attribute2,table):#checks if composite key tuple exists
db_cursor.execute(f'''SELECT {attribute1}, {attribute2} FROM {table} WHERE {attribute1} = '{id1}' AND {attribute2} = '{id2}';''')
value = db_cursor.fetchall()
try:
if value[0][0]==id1 and value[0][1]==id2:
return True
except:
return False
def passwordCheck(email, table, password):
password = hashlib.md5(password.encode('utf-8')).hexdigest()
db_cursor.execute(f'''SELECT password FROM {table} WHERE email='{email}';''')
value = db_cursor.fetchall()
try:
if value[0][0] == password:
return True
except:
return False
def attendeeParse(alist):
attendanceList = []
u_type = ""
if alist != []:
for attendee in alist:
if attendee[1] == None:
u_type = "Academic"
else:
u_type = attendee[1]
if len(attendee)==4:
attendanceList.append({
'name': attendee[0],
'no': u_type,
'email': attendee[2],
'e_name': attendee[3]
})
else:
attendanceList.append({
'name': attendee[0],
'no': u_type,
'email': attendee[2]
})
return attendanceList
def getSocietyInfo(app, slug):
db_cursor.execute(f'''SELECT * FROM Society WHERE slug='{slug}';''')
info = db_cursor.fetchall()[0]
pattern = f"{slug}.*"
result = ""
for root, dirs, files in os.walk(os.path.join(app, 'static', 'logos')):
for name in files:
if fnmatch.fnmatch(name, pattern):
result = os.path.join(root, name)
refactored_result = litowin(result)
info = {
'email' : info[0],
'name' : info[1],
'logo' : refactored_result,
'budget' : info[4],
'description' : info[5],
'admin-mail' : info[6],
'advisor-mail' : info[7],
'slug' : slug
}
return info
def getSocAdmAdv(slug):
db_cursor.execute(f'''SELECT admin_mail, advisor_mail FROM Society WHERE slug='{slug}';''')
info = db_cursor.fetchall()[0]
info = {
'admin-mail' : info[0],
'advisor-mail' : info[1]
}
return info
def societyAuth(email, slug):
db_cursor.execute(f'''SELECT email FROM Society WHERE slug='{slug}';''')
info = db_cursor.fetchall()[0]
if (email == info[0]):
return True
return False
def getImage(app, pattern, file):
pattern = f"{pattern}.*"
result = ""
for root, dirs, files in os.walk(os.path.join(app, 'static', file)):
for name in files:
if fnmatch.fnmatch(name, pattern):
result = os.path.join(root, name)
return result
def parseEvents(events, app):
event_list = []
for event in events:
result = getImage(app, f"{event[6]}-{event[4]}", 'posters')
db_cursor.execute(f'''SELECT guest FROM Event_Guest WHERE soc_email='{event[5]}' AND e_name='{event[0]}';''')
guests = db_cursor.fetchall()
guest_list = []
if guests:
for guest in guests:
guest_list.append(guest[0])
refactored_result = litowin(result)
info = {
'event_name' : event[0],
'date' : event[1],
'soc_name' : event[2],
'description' : event[3],
'event_slug' : event[4],
'soc_email' : event[5],
'soc_slug' : event[6],
'poster' : refactored_result,
'guests' : guest_list
}
event_list.append(info)
return event_list
def getEvents(user, level, app):
event_list = []
if(level == 0):
db_cursor.execute(f'''SELECT event_name, e_date, name, e.description, e.slug, soc_email, s.slug FROM Event e LEFT JOIN Society s on s.email=e.soc_email WHERE e.status = {level} AND adv_email="{user}" ORDER BY e.e_date DESC;''')
events = db_cursor.fetchall()
if events != []:
event_list.extend(parseEvents(events, app))
else:
db_cursor.execute(f'''SELECT event_name, e_date, name, e.description, e.slug, soc_email, s.slug FROM Event e LEFT JOIN Society s on s.email=e.soc_email WHERE e.status = {level} ORDER BY e_date DESC;''')
events = db_cursor.fetchall()
if events != []:
event_list.extend(parseEvents(events, app))
return event_list