forked from galacticwarrior9/IslamBot
-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbhandler.py
139 lines (117 loc) · 5.53 KB
/
dbhandler.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
import aiomysql
import asyncio
import configparser
from aiomysql.sa import create_engine
from sqlalchemy import create_engine
import pandas as pd
config = configparser.ConfigParser()
config.read('config.ini')
host = config['MySQL']['host']
user = config['MySQL']['user']
password = config['MySQL']['password']
database = config['MySQL']['database']
server_translations_table_name = config['MySQL']['server_translations_table_name']
server_prayer_times_table_name = config['MySQL']['server_prayer_times_table_name']
user_prayer_times_table_name = config['MySQL']['user_prayer_times_table_name']
loop = asyncio.get_event_loop()
class DBHandler:
host = config['MySQL']['host']
user = config['MySQL']['user']
password = config['MySQL']['password']
database = config['MySQL']['database']
server_translations_table_name = config['MySQL']['server_translations_table_name']
server_prayer_times_table_name = config['MySQL']['server_prayer_times_table_name']
user_prayer_times_table_name = config['MySQL']['user_prayer_times_table_name']
@classmethod
async def create_connection(cls):
connection = await aiomysql.connect(host=host, user=user, password=password, db=database,
loop=loop, autocommit=True)
return connection
@classmethod
async def get_guild_translation(cls, guild_id):
try:
connection = await cls.create_connection()
except:
return 'haleem'
async with connection.cursor() as cursor:
await cursor.execute(f"SELECT translation "
f"FROM {server_translations_table_name} "
f"WHERE server = {guild_id}")
result = await cursor.fetchone()
if result is None: # If no translation has been set
translation = 'haleem'
else:
translation = result[0]
connection.close()
return translation
@classmethod
async def update_guild_translation(cls, guild_id, translation):
connection = await cls.create_connection()
async with connection.cursor() as cursor:
create_sql = f"INSERT INTO {server_translations_table_name} (server, translation) VALUES (%s, %s) " \
f"ON DUPLICATE KEY UPDATE server=%s, translation=%s"
await cursor.execute(create_sql, (guild_id, translation, guild_id, translation))
connection.close()
def create_df():
engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}')
connection = engine.connect()
user_df = pd.read_sql(f"SELECT * FROM {user_prayer_times_table_name}", connection)
server_df = pd.read_sql(f"SELECT * FROM {server_prayer_times_table_name}", connection)
connection.close()
return user_df, server_df
class PrayerTimesHandler(DBHandler):
user_df, server_df = create_df()
@classmethod
async def update_server_prayer_times_details(cls, guild_id, channel_id, location, timezone, method):
new_row = {'server': str(guild_id), 'channel': str(channel_id), 'location': location, 'timezone': timezone, 'calculation_method': method}
if str(channel_id) not in cls.server_df.channel.values:
cls.server_df = cls.server_df.append(new_row, ignore_index=True)
else:
updated_df = pd.DataFrame(new_row, index=[0])
cls.server_df.update(updated_df)
@classmethod
async def delete_server_prayer_times_details(cls, channel):
if str(channel) not in cls.server_df.channel.values:
pass
else:
cls.server_df = cls.server_df[cls.server_df.channel != str(channel)]
@classmethod
async def update_user_prayer_times_details(cls, user_id, location: str, timezone: str, method: str):
new_row = {'user': str(user_id), 'location': location, 'timezone': timezone, 'calculation_method': method}
if str(user_id) not in cls.user_df.user.values:
cls.user_df = cls.user_df.append(new_row, ignore_index=True)
else:
updated_df = pd.DataFrame(new_row, index=[0])
cls.user_df.update(updated_df)
@classmethod
async def delete_user_prayer_times_details(cls, user_id):
if str(user_id) not in cls.user_df.user.values:
pass
else:
cls.user_df = cls.user_df[cls.user_df.user != str(user_id)]
@classmethod
async def update_user_calculation_method(cls, user, method):
connection = await cls.create_connection()
async with connection.cursor() as cursor:
create_sql = f"INSERT INTO {user_prayer_times_table_name} (user, calculation_method) " \
"VALUES (%s, %s) " \
"ON DUPLICATE KEY UPDATE user=%s, calculation_method=%s"
await cursor.execute(create_sql, (user, method, user, method))
connection.close()
@classmethod
async def get_user_calculation_method(cls, user):
try:
connection = await cls.create_connection()
except:
return 4
async with connection.cursor() as cursor:
await cursor.execute(f"SELECT calculation_method "
f"FROM {user_prayer_times_table_name} "
f"WHERE user = {user}")
result = await cursor.fetchone()
if result is None: # If no calculation method has been set
method = 4
else:
method = result[0]
connection.close()
return method