How can I use something stored in a DataBase? SQLite / Python

Question

So, I am new at DataBases and I have a question. I first made a re-search in the internet but could not find anything or actually I could not understand correctly what they were explaining. Before starting with my question I want to say that I am currently working in a Discord Bot in Python as Main Language and I am trying to create a per-server-prefix system. I have already made it once but in .JSON format. Then I heard that using .JSON to save this kind of Data is not actually good so I moved to DataBases. Now, my question is:

I have stored the guild_id and prefix in my DB, how could I use that for a per-server-prefix system? I have not tried anything yet except writing the Code to store both Texts. I would really love if anyone could explain to me not just tell me the Code! Any answer will be appreciated <3.

Main.py:

def get_prefix(client, message):

    db = sqlite3.connect("main.sqlite")
    cursor = db.cursor()
    cursor.execute(f"SELECT prefix FROM prefixes WHERE guild_id = {message.guild.id}")
    result = cursor.fetchone()
    if result is None:
        return "/"
    else:
        

client = commands.Bot(command_prefix = get_prefix)

Prefixes.py (COGS):

    @commands.command()
    @commands.has_permissions(administrator=True)
    async def prefix(self, ctx, prefix=None):

        db = sqlite3.connect("main.sqlite")
        cursor = db.cursor()
        cursor.execute(f"SELECT prefix FROM prefixes WHERE guild_id = ?", ctx.guild.id)
        result = cursor.fetchone()
        if result is None:
            sql = ("INSERT INTO prefixes(guild_id, prefix) VALUES(?,?)")
            val = (ctx.guild.id, prefix)
            await ctx.channel.send(f"{prefix}")
        elif result is not None:
            sql = ("UPDATE prefixes SET prefix = ? WHERE guild_id = ?")
            val = (prefix, ctx.guild.id)
            await ctx.channel.send(f"update `{prefix}`")
        cursor.execute(sql, val)
        db.commit()
        cursor.close()
        db.close()

That is pretty much the whole code. If you think anything should be changed or have any suggestions, answer in the comments!

I'm not entirely sure what the problem is. As far as I can tell, your code should work. All you need to do is return the result after the else clause. — 0rdinal
– 0rdinal, Commented Mar 11, 2021 at 13:43
That's the point, I do not know what the else will be. As I said, I'm new at DataBases and I'm still learning. So I'm asking if anyone knows how the else will work. — xXSkillexZ
– xXSkillexZ, Commented Mar 11, 2021 at 14:03

0rdinal · Accepted Answer · 2021-03-11 14:15:24Z

2

All you need to do is, after the else, put return result. For example:

result = cursor.fetchone()
if result is None:
    return "/"
else:
    return result

cursor.fetchone() returns a tuple with each element requested in the row, as you only requested the prefix, it will contain just that (e.g: ("!",)) which is permitted as your command_prefix callable can return a string or tuple of strings.

Warning: You may want to add a check to ensure that someone doesn't specify an empty string (A zero length string with nothing in it) as their prefix, otherwise your bot will attempt to run every message it sees as a command

References: discord.ext.commands.Bot.command_prefix

edited Mar 11, 2021 at 14:15

answered Mar 11, 2021 at 14:10

0rdinal

6714 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

xXSkillexZ Over a year ago

Damn I feel stupid now, that was a bit easy to be honest. Anyways thank you for the answer. Also about the Warning, what you are saying is that I should put an error for everyone that set's "" for a prefix?

0rdinal Over a year ago

I used "" to show an empty string (e.g: nothing within it). Just check that the length of the prefix someone sets is greater than zero.

xXSkillexZ Over a year ago

Oh alright alright! I see. I'll try right now the code and I will tell you if it works!

0rdinal Over a year ago

No problem. Good luck with your coding adventures!

0rdinal Over a year ago

@AlexKim You're right, else is redundant. But from a beginner perspective, it is much easier to understand the code flow than a random return. There is also little to no difference in performance.

|

Collectives™ on Stack Overflow

How can I use something stored in a DataBase? SQLite / Python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related