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)