#SQL query trouble

1 messages · Page 1 of 1 (latest)

glossy orbit
#

I can't figure it out

#

I have a command to check what the database contains

#verify data
@plugin.slash_command(name="verify-data", description="Verifies all data is stored correctly")
async def verify_integ(self, inter:CommandInteraction):
    cursor.execute("SELECT * FROM Roles")
    roles = cursor.fetchall()
    
    cursor.execute("SELECT * FROM Users")
    users = cursor.fetchall()
    await inter.response.send_message(f"ROLES\n{roles}\n\nUSERS\n{users}")

Which spits out

image.png
#

But when trying to

@plugin.listener("on_member_update")
async def member_update(before:Member, after:Member):
    beforef = [role.id for role in before.roles]
    afterf = [role.id for role in after.roles]
    removed_roles = set(beforef) - set(afterf)
    cursor.execute("SELECT * FROM Users WHERE ? IN (roleids)", (str(list(removed_roles)[0]),))
    users = cursor.fetchall()
    print(users)
#

My output is []

# 
# Database setup
connection = sqlite3.connect("data/database.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS Roles (roleid INTEGER PRIMARY KEY UNIQUE)")
cursor.execute("CREATE TABLE IF NOT EXISTS Users (userid INTEGER PRIMARY KEY UNIQUE, roleids TEXT)")
connection.commit()
tulip saddle
#

try printing

(str(list(removed_roles)[0])
glossy orbit
chrome marten
#

I don't think "IN" works like you expect it to

#

in your query

glossy orbit
#

But also you are probably right

chrome marten
#

in doesn't check for substrings afaik

glossy orbit
chrome marten
#

mhm

#

it'd probably make more sense to use an array type there instead of a string anyways

glossy orbit
#

Well, this bot could have just used json, I chose SQL simply to learn it

chrome marten
#

I chose SQL simply to learn it
very valid

glossy orbit
#

I'm storing the roleids like id;id;id;id etc

chrome marten
#

could be mysql can't make arrays ig

glossy orbit
#

I'm using sqlite3

chrome marten
#

ah right

#

yeah seems sqlite can't do it

glossy orbit
#

I'm now just adding eye candy (embeds)

chrome marten
#

welp postgres for the win ig

glossy orbit
#

Because instead of selecting, then editing I could have just edited

glossy orbit
chrome marten
#

not supported by disnake yet outside of some PR

glossy orbit
#

PR?

chrome marten
#

pull request

glossy orbit
#

So it's probably going to be added soon

#

Alright, thanks again

#

I don't actually know how to close this forum

chrome marten
#

but yeah in the meantime something like this can be manually done

#general message

chrome marten