#Inserting something into my MySQL using slash command

1 messages · Page 1 of 1 (latest)

lunar plinth
# 
async def addstaffroler(ctx, role: discord.Option(discord.SlashCommandOptionType.role, "Enter the role name", required = True)): # type: ignore
    db = await connect(
    host = "",
    user = "",
    password = "",
    database = ""
)
    async with db.cursor(cursor=DictCursor) as cursor:
        await cursor.execute("""INSERT IGNORE INTO databaseName.table_name VALUES ("test2");""")
        embed = discord.Embed(description=f"![CheckGreen](https://cdn.discordapp.com/emojis/939578766362030161.webp?size=128 "CheckGreen") Added {role.mention} to the options list", 
        color=0xe18040).set_footer(text = "")
        await ctx.respond(embed = embed)

Basically, I get the right response to the slash command but for some reason nothing is inserted into the database. If I use the same part here ```py
await cursor.execute("""INSERT IGNORE INTO databaseName.table_name VALUES ("test2");""")

lunar plinth
#

Guessing something is wrong with my approach as running it twice gives no response

meager surge
#

slash commands do allow database executions

#

it's more than likely a logic error

lunar plinth
#

hm, could it be because I'm opening a connection before on start in startup then opening one again inside the slash command?

meager surge
#

no i do that all of the time w/ tortorise orm. it's more than likely w/ the sql statement

lunar plinth
meager surge
#

hm

lunar plinth
#

Do I need to close a connection by chance? or does it handle that automatically?

#

Because I kinda just left it at this in the on_ready() function. Basically creating a database for each server and adding in the guild id and name (with some filtering)

unknown.png
#

^ works fine tho

meager surge
#

the context manager should handle that automatically, but u may need to close the connection manually

#

ideally you would want to create one table in one database that is already created and each server should be it's own row

lunar plinth
meager surge
# lunar plinth seems like a good way

yeah ideally each SQL transaction is expensive, and will take time. You would want to reduce the amount of unnecessary calls by optimizing your queries to only get and set what is really needed

lunar plinth
meager surge
lunar plinth
#

yeah running the slash command twice caused this lol Rip

unknown.png
#

Lock wait timeout exceeded, definelty something wrong with my approach on this

meager surge
#

yeah maybe you are doing too much transactions with the current code

lunar plinth
#

mhm, gonna try swapping to a pool

meager surge
#

oh yeah also use a connection pool

#

Might help with that

lunar plinth
#

helped with the response thing \o/ btw should I be using asyncio.run anywhere? I'm not using cogs

lunar plinth
#

@meager surge lmaooo my entire issue was not having ```py
autocommit=True

grim fox
rain perchBOT