Creating the Database Schema and Python database Functions 👨🏼‍💻
Tags: showdev, mysql, python
Published: 2021-01-03 10:56:39


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](Draw.io)

• 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](https://www.reddit.com/r/discordapp/comments/drtp5s/are_message_ids_universally_unique/).

• 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](https://www.youtube.com/channel/UC7cs8q-gJRlGwj4A8OmCmXg), this is what the Schema Looked!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/rh385edndadu9zoce1ip.png)

# Code and Thought process💭

**Server**
```SQL
CREATE TABLE "Server_Info" (
	"channel_name"	VARCHAR(255),
	"channel_id"	BIGINT,
	"server_name"	VARCHAR(255)
);
```

**Points**
```SQL
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**
```SQL
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**
```SQL
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.

```python
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**
```python
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**
```python
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**
```python
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**
```python
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**
```python
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

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