DEV Community

loading...
Cover image for Creating the Database Schema and Python database Functions πŸ‘¨πŸΌβ€πŸ’»

Creating the Database Schema and Python database Functions πŸ‘¨πŸΌβ€πŸ’»

megabarrel profile image Saurabh ・Updated on ・3 min read

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!

Alt Text

Code and Thought processπŸ’­

Server

CREATE TABLE "Server_Info" (
    "channel_name"  VARCHAR(255),
    "channel_id"    BIGINT,
    "server_name"   VARCHAR(255)
);
Enter fullscreen mode Exit fullscreen mode

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")
);
Enter fullscreen mode Exit fullscreen mode

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")
);
Enter fullscreen mode Exit fullscreen mode

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")
);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

β€’ 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!')

Enter fullscreen mode Exit fullscreen mode

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!')
Enter fullscreen mode Exit fullscreen mode

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!')
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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!!! πŸ––πŸΌπŸ˜ƒ

Discussion (0)

pic
Editor guide