DEV Community: Saurabh

Making BOT using Python

Saurabh — Mon, 04 Jan 2021 13:36:17 +0000

After making a Database connection using python and MySQL, Now was the time to start making commands for my BOT.

Steps to creating various commands:

1. Creating the custom Help command.
2. Creating Helper and Member command to add points.
3. Creating the points command that will display points of specific users.
4. Creating the Helper and Member leaderboard command.

First downloaded and imported all the required libraries.

discord.py              1.5.1
prettytable             2.0.0
mysql-connector         2.2.9
mysql-connector-python  8.0.21
json

So, discord has it's own help command. First thing was to remove the help command and make a custom one!

import discord 
import json

from datetime import datetime
from discord.ext import commands
from config_database import *

from prettytable import PrettyTable
from prettytable import from_db_cursor

# Custom Prefix for BOT
bot = commands.Bot(command_prefix="++")

# reading the help commands from `JSON` File 
config_file = open("config.json")
config = json.load(config_file)

# Removing Discord in-build Help command
bot.remove_command("help")

Creating Custom Help Command!

@bot.command(pass_context = True)
async def help(ctx):

    print("!help Command Is Running!")

    embed = discord.Embed(
        title = "Help command list", 
        color=0x8150bc,
        timestamp = datetime.utcnow(),
        description = "Please Follow these commands from any channel!\n\n "
    )

    embed.add_field(name = "1. Member", value = config['Member_add/remove'], inline = False)
    embed.add_field(name = "2. Helper", value = config['Helper_add/remove'], inline = False)
    embed.add_field(name = "3. Leaderboard", value = config['Leaderboard'], inline = False)
    embed.add_field(name = "4. Points", value = config['points'], inline = False)

    embed.set_footer(text = 'Bot Made By Saurabh')
    embed.set_footer(text="Command invoked by {}".format(ctx.message.author.name))
    await ctx.send(embed = embed)

It took me more than 3 weeks for creating the member and helper commands for adding and removing points😢. Added database functions to insert the data into the database.

Passing all the required parameters to the functions!

submitted_by(message_id, user_tag, username, user_id, message_Content, point, channel_name)
server(channel_name, channel_id, Server_name)
Given_To(message_id, g_name, g_id, g_tag, has_role)
add_points(g_tag, g_name, message_id, point, has_role)

After finishing these commands, I created commands for the leaderboard and looking for individual users points.

Command for displaying specific user Points

@bot.command(pass_context = True)
async def pts(ctx, username: discord.Member = None):
    print('Command points is running!')

    if username:
        tag = username.discriminator
        member_name = username
        user_point = users_points(tag)

        embed = discord.Embed (
            title = f"{member_name} has {user_point[0]} Points!", 
            color = 0x8150bc,
            timestamp = datetime.utcnow(),
        )
        embed.set_footer(text = 'Bot Made By Saurabh')
        embed.set_footer(text="Command invoked by {}".format(ctx.message.author.name))   
        await ctx.send(embed = embed)

    else:
        await ctx.send("Please add Username to see their points🙈 !!!!!")

This method will display points to the specific users in the Channel.

Command for Leaderboard

@bot.command(pass_context = True)
async def lb(ctx, command = None):
    print('Command Leaderboard is running!')

    mydb, cursor = get_connection()

    if command == 'helper':

        fetch = '''
            SELECT Username, Total_Points, dense_rank() 
            OVER ( order by Total_points desc ) 
            AS 'rank' FROM points WHERE Is_Helper = 1;
        '''

        cursor.execute(fetch)
        value = from_db_cursor(cursor)

        embed = discord.Embed (
            title = f"Leaderboard for {command}!", 
            color = 0x8150bc,
            timestamp = datetime.utcnow(),
            description = "```

\n"+value.get_string(border=True)+"

```"
        )
        embed.set_footer(text = 'Bot Made By Saurabh')
        embed.set_footer(text="Command invoked by {}".format(ctx.message.author.name))   
        await ctx.send(embed = embed)
        print('Fetching Data for Helper Leaderboard!\n')

    elif command == 'member':

        fetch = '''
            SELECT Username, Total_Points, dense_rank() 
            OVER ( order by Total_points desc ) 
            AS 'rank' FROM points WHERE Is_Helper = 0   ;
        '''

        cursor.execute(fetch)
        value = from_db_cursor(cursor)

        embed = discord.Embed (
            title = f"Leaderboard for {command}!", 
            color = 0x8150bc,
            timestamp = datetime.utcnow(),
            description = "```

\n"+value.get_string(border=True)+"

```"
        )
        embed.set_footer(text = 'Bot Made By Saurabh')
        embed.set_footer(text="Command invoked by {}".format(ctx.message.author.name))   
        await ctx.send(embed = embed)
        print('Fetching Data for Member Leaderboard!\n')

    else:
        await ctx.send('Please add helper/member in your command for displaying the leaderboard!')

These methods will check, whether helper/member is passed as a parameter, if not passed it will display the else block!

After searching StackOverflow, watching youtube videos, At last, my BOT was working 🎉🤩 which took me almost 1.5-months😩, since I was very new to making Discord BOT.

🖖🏼😃 Hope you'll like this Blog!!! 🖖🏼😃

Creating the Database Schema and Python database Functions 👨🏼‍💻

Saurabh — Sun, 03 Jan 2021 10:56:39 +0000

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!!! 🖖🏼😃

Introduction

Saurabh — Sun, 03 Jan 2021 10:55:04 +0000

This is my first Discord Bot Blog post. I am Building a Discord BOT using Python and MySQL database for storing all the data.

Steps For Building the Project are as follows. 😃

Downloading Discord and mysql.connector packages 🎉.
Setting up a Discord Server and saving the TOKEN for the server.
Creating Database Schema and Diagrams 💬.
Creating Python Commands, database connectivity using python 👨🏼‍💻.

Completed Hacktoberfest

Saurabh — Thu, 15 Oct 2020 06:18:45 +0000

What I Learned From Hacktoberfest

Learned how open-source community work, how to create a pull request, merging the PR. It was a great experience participating in it😁😍🎉