So to start making the BOT, I first need to come up with a Database Schema that will store all the data. Looked various database for my BOT and finally used SQLite Database 🎉.
I started making Schema using Draw
• Created Tables for the following:
1. Created_By
2. Given_To
3. Server_info
4. Points
• So after looking up, I found out that Discord generated unique Message_id for all the messages Blog Link.
• Made Message_Id as a Primary Key, and Made it Foreign Key for all the other tables!
After a Lots of Improvement and consulting this from Alex the Analyst, this is what the Schema Looked!
Code and Thought process💭
Server
CREATE TABLE "Server_Info" (
"channel_name" VARCHAR(255),
"channel_id" BIGINT,
"server_name" VARCHAR(255)
);
Points
CREATE TABLE "Points" (
"user_tag" INTEGER,
"username" VARCHAR(255),
"message_id" BIGINT,
"total_points" INTEGER,
"Is_Helper" BOOLEAN,
FOREIGN KEY("message_id") REFERENCES "Created_By"("message_id")
);
Given_To
CREATE TABLE "Given_To" (
"ID" INTEGER AUTO INCREMENT,
"message_id" BIGINT,
"username" VARCHAR(255),
"user_id" BIGINT,
"user_tag" INTEGER,
"Is_Helper" BOOLEAN,
FOREIGN KEY("message_id") REFERENCES "Created_By"("message_id")
);
Created_By
CREATE TABLE "Created_By" (
"ID" INTEGER AUTO INCREMENT,
"message_id" BIGINT,
"user_tag" INTEGER,
"username" VARCHAR(255),
"user_id" BIGINT,
"message_desc" VARCHAR(255),
"points_given" INTEGER,
"channel_name" VARCHAR(255),
"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY("message_id")
);
After Creating the Schema and Tables, Now was the time to Create python functions that will do the work for me 😄!
• First Created the Connect_Function() which will connect the program to the Database.
def get_connection():
mydb = sqlite3.connect('points.db')
cursor = mydb.cursor()
return mydb, cursor
• Next steps were to created functions for CRUD Operations(Although delete command is not used for now🙊)
Submitted_by Method
def submitted_by(msg_id, usr_tag, username, user_id, msg_desc, points, channel_name):
mydb, cursor = get_connection()
query = """
INSERT INTO Created_By (message_id, user_tag, username, user_id, message_desc, points_given, channel_name) VALUES (?, ?, ?, ?, ?, ?, ?);
"""
values = (msg_id, int(usr_tag), str(username), user_id, str(msg_desc), points, str(channel_name))
cursor.execute(query, values)
mydb.commit()
cursor.close()
mydb.close()
print('Inserted Data Successfully to the Created_By Database!')
Server Method
def server(channel_name, channel_id, server_name):
mydb, cursor = get_connection()
query = """
INSERT INTO Server_Info (channel_name, channel_id, server_name) VALUES (?, ?, ?);
"""
values = (channel_name, channel_id, server_name)
cursor.execute(query, values)
mydb.commit()
cursor.close()
mydb.close()
print('Inserted Data Successfully to the Server Database!')
Given_to Method
def Given_To(msg_id, username, user_id, user_tag, Is_helper):
mydb, cursor = get_connection()
query = """
INSERT INTO Given_To (message_id, username, user_id, user_tag, Is_Helper) VALUES (?, ?, ?, ?, ?);
"""
values = (msg_id, str(username), user_id, int(user_tag), Is_helper)
cursor.execute(query, values)
mydb.commit()
cursor.close()
mydb.close()
print('Inserted Data Successfully to the Given_to Database!')
After some try/error method, I found out that the user points should be updated when points are given to them multiple times.
To solve this, I first checked if the user exists in a Database. If yes then I simply need to update his points. Else I first need to insert his records.
After lot's of finding/debugging(literally 2 days 🤐), I came up with the following approach 😃
Adding Points to the Users
def add_points(user_tag, username, msg_id, point, Is_helper):
mydb, cursor = get_connection()
query = """
SELECT username FROM Points WHERE user_tag = ?;
"""
value = (user_tag, )
cursor.execute(query, value)
check = cursor.fetchall()
if check:
# print('Already Present')
query = """
UPDATE Points SET total_points = total_points + ? WHERE user_tag = ?;
"""
values = (point, user_tag)
cursor.execute(query, values)
print(f'Updated Points for {username}! \n')
mydb.commit()
cursor.close()
mydb.close()
else:
query = """
INSERT INTO Points (user_tag, Username, message_id, total_points, Is_Helper) VALUES (?, ?, ?, ?, ?);
"""
values = (user_tag, str(username), msg_id, point, int(Is_helper))
cursor.execute(query, values)
print(f'Inserting the Record for {username}! \n')
mydb.commit()
cursor.close()
mydb.close()
Removing Points from Users
def remove_points(user_tag, point):
mydb, cursor = get_connection()
query = """
SELECT Username FROM Points WHERE User_tag = ?;
"""
value = (user_tag, )
cursor.execute(query, value)
check = cursor.fetchall()
if check:
query = """
UPDATE Points SET Total_Points = Total_Points - ? WHERE User_Tag = ?;
"""
values = (point, user_tag)
cursor.execute(query, values)
print(f'Removed Points for {user_tag}! \n')
mydb.commit()
cursor.close()
mydb.close()
else:
print(f'User Does Not Exist! \n')
At last only 1 method needed to be created, To return single users points from the Database
def users_points(user_tag):
mydb, cursor = get_connection()
cursor.execute(""" SELECT total_points FROM Points WHERE user_tag = ?""", (user_tag, ))
row = cursor.fetchone()
return row
mydb.commit()
cursor.close()
mydb.close()
print('Fetching Data for users!\n')
This makes the End of the Database part and connecting it with python for automating the CRU(Create, Read, Update) commands
🖖🏼😃 Hope you'll like this Blog!!! 🖖🏼😃
Top comments (0)