Discord.py Reaction Remove SQLite3 Database row and Discord Message

Question

This is the last part of my program I need, the delete function. The bot works perfectly up until this part. I'm try to add a delete function that deletes both the Discord Embed and the corresponding database row.

So this program works by the user being prompted and then their input is stored in my SQLite3 database and then it is displayed in an embed. I then have another command !view that displays an embed for every row in the database with the corresponding information. It also adds a trashcan emoji at the end of every embed. I want this delete function when the reaction is pressed to delete the embed, as well as the row it relates to in the database. I can't seem to make either work.

Here is the !view command:

if message.content.startswith('!view'):
        rows = cursor.execute("SELECT name, renter, duration, price, start, end FROM rental").fetchall()
        for row in rows:
            embed = discord.Embed(title='Rental', color=0x0000FF)
            embed.set_thumbnail(url='https://www.pngkit.com/png/detail/231-2316751_database-database-icon-png.png')
            embed.add_field(name='Bot Name:', value=row[0], inline=False)
            embed.add_field(name='Renter Name:', value=row[1], inline=False)
            embed.add_field(name='Rental Duration:', value=row[2], inline=False)
            embed.add_field(name='Rental Price:', value=row[3], inline=False)
            embed.add_field(name='Start Date:', value=row[4], inline=False)
            embed.add_field(name='End Date:', value=row[5], inline=False)
            embed.set_footer(icon_url='https://pbs.twimg.com/profile_images/1325672283881484289/oaGtVIOD_400x400.png', text='Created by @Expected')
            msg = await message.channel.send(embed=embed)
            await msg.add_reaction('\U0001F5D1')

I had an idea of how to delete the discord message, but the database entry is where it's tripping me up. Do I need to delete by a unique identifier like the ROWID? I'm not really sure where to take that part of this.

TheV TheV · Accepted Answer · 2021-02-28T18:25:57

Generally yes, you would need a unique id for every db entry if you want to remove them individually if no other value in unique. ROWID can actually change, so you should create your own unique identifier and store it as primary key in the database

CREATE TABLE IF NOT EXISTS rental(id INTEGER PRIMARY KEY, name...);

SELECT id, name, renter, duration, price, start, end FROM rental

The query would simply be:

DELETE from rental WHERE id = ?

Discord.py Reaction Remove SQLite3 Database row and Discord Message

1 Answers