databases

[velvet fable]

but, I'm only using the specific DB for this one cog, I don't want it to connect in the main one

[burnt turret]

wdym

you have multiple databases for the same bot?

[velvet fable]

no, i only have one because I just switched over, I was gonna do that, is that a bad idea?

[burnt turret]

yeah rather wasteful i'd say

single database for the bot

the database can be having multiple tables representing different information

[velvet fable]

If I were to change my class in that cog so it connects a single DB for the entire bot, then have multiple tables per cog, how would I do that then?

[burnt turret]

no offence, but i'd completely drop that DB class

await aiosqlite.connect('Reactions.sqlite') this line is what connects to the database

you use that once in your main bot file and assign it to a bot variable

a bot variable, can be defined like

client.variable = "something"

so, for example you could do

client.db = await aiosqlite.connect('Reactions.sqlite')

[velvet fable]

If I were to just change the DB name, would the table still be there?

[burnt turret]

now, you might've noticed that in the __init__s of your cogs you have client?
your init will have a line like

self.client = client

so in cogs, you can access the db with self.client.db

[burnt turret]

different databases, different tables

[velvet fable]

but, if like right clicked the db, and change the name through that, would the table still be there?

[burnt turret]

it should i think

okay i gotta eat now, you can ask your questions here and i'll try answering once i get back if no one else has answered

[velvet fable]

ok

[bitter drift]

Any good tutorial for SQL beginner?

[velvet fable]

someone suggested me to use sqlbolt.com

[bitter drift]

I will check it out. Thanks!!

[velvet fable]

np

So, I got rid of the class for now (just #ed is out), and in the main bot.py, I put client.db = await aiosqlite.connect('Acer.sqlite'), with this, in the cog when I go to execute something using, await self.client.db.execute(f"SELECT channel_id FROM main WHERE channel_id = {message.channel.id}") I believe it works, but when I get to the cursor part of it, I tried to use result = await self.client.db.cursor.fetchone(), but it saying 'function' object has no attribute 'fetchone'

[stable mural]

can someone help me the beg doesnt update :/

this is my error: Command raised an exception: TypeError: unsupported operand type(s) for +: 'int' and 'sqlite3.Cursor'

[rain plank]

first of all I'm pretty sure you're supposed aiosqlite since sqlite3 is blocking

[stable mural]

?

whats aiosqlite?

[burnt turret]

a driver module for sqlite that is asynchronous

where have you defined the getBankBal variable

[stable mural]

let me screen shot it

[burnt turret]

no don't

copy paste it

https://paste.pydis.com

[stable mural]

oh ok

[burnt turret]

screenshots just make it tougher to read from

[stable mural]

ok

conn=sqlite3.connect('economy.sqlite')
c=conn.cursor()
c.execute("CREATE TABLE IF NOT EXISTS economy (userid TEXT, wallet INT, bank INT)")
getWalletBal = c.execute("SELECT wallet FROM economy")
getBankBal = c.execute("SELECT bank FROM economy")
result = c.fetchone()


@client.command(name="bal")
async def bal(ctx):
    if result is None:
        await ctx.send("You have no coins AT ALL")
    else:
        await ctx.send(f"You have {str(getWalletBal)} Geb Coins\nYou have {str(getBankBal)} Geb Coins")

@client.command(name="beg")
async def beg(ctx):
    begged = random.randrange(0, 1000)
    addBegged = begged + getBankBal


    if begged == 0:
        await ctx.send("No one gave you any Geb Coins")
        
    elif begged > 0: 
        sql = ("UPDATE economy SET wallet = ?")
        val = (addBegged)
        await ctx.send(f"Someone gave you {begged} Geb Coins")```

[rain plank]

this is very very blocking

[stable mural]

wdym?

is it bad?

[rain plank]

https://discordpy.readthedocs.io/en/latest/faq.html#:~:text=and nowhere else.-,What does “blocking” mean%3F,function that are not await .&text=Remember%2C if you block for,point to do other things.

[burnt turret]

that's a long link

[rain plank]

copied from google so I guess

[stable mural]

yup

[burnt turret]

ugh i need to make a "switching to aiosqlite" tutorial as well along with my "switching to motor" gist

this is asked way too often

[stable mural]

sry :(

[rain plank]

yes

[burnt turret]

not blaming you haha

[stable mural]

ok lol

[burnt turret]

i'm trying to find some message where i previously explained it

[stable mural]

ok

take your time

[burnt turret]

but in short, pip install aiosqlite and import it

everything is similar to sqlite

[rain plank]

and add some await's

[burnt turret]

but you add awaits almost everywhere

yeah lmao

[stable mural]

ok

bruh

[burnt turret]

stop spamming this across channels

[stable mural]

i did it

[rain plank]

now you want to add your queries to async funcs only

[burnt turret]

i gotta sleep now, i'm assuming toxic will help you now

[stable mural]

k

[stable mural]

??????

[torn sphinx]

heyy

[torn sphinx]

why?

[modest pulsar]

hi, i've got this error but i don't understand how to fix it....

result = function() sqlite3.OperationalError: near ")": syntax error

[torn sphinx]

Hello, I am trying to insert data into an SQL table if the current line in a csv file is empty. When I run the following, insert_hand_data isn't running.

class FileRow:
    def __init__(self, file):
        """
        Get a specific row from a specific file
        :param file_name: Get file name to use
        :param row_no: Get row number to use
        """
        self.file = file

    def __str__(self):
        return str(self.__dict__)

    def check_row(self, string_check):
        if not self.file:
            pass
            # print("New hand...")
        else:
            if string_check in self.file[0]:
                return True
...
        counter = 0
        while counter != len(file_reader):
            file = FileRow(file_reader[counter])
...
            elif file.check_row(""):
                hand = Hand(sess.hand_no, sess.date, sess.result)
                hand.insert_hand_data()
                counter += 1

maybe i should ask the actual help channel lol

didnt realize i was in a different chan

[modest pulsar]

i found, but now i've got another error:
c:\Users\thoma\OneDrive\Desktop\MeltBot bêta\MeltbotBêta.py:39: RuntimeWarning: coroutine 'Cursor.execute' was never awaited cursor.execute(''' RuntimeWarning: Enable tracemalloc to get the object allocation traceback

here's my code:

@bot.event
async def on_ready():

    db = await aiosqlite.connect('main.sqlite')
    cursor = await db.cursor()

    cursor.execute('''
        CREATE TABLE rpg(
            user_id INT,
            pétrole INT,
            pierre INT,
            xp INT,
            warns INT
            level_extract INT,
            level_car INT,
            bouclier INT,
            )
        ''')

    print(f'Logged in as: {bot.user.name}')
    print(f'With ID: {bot.user.id}')
    await bot.wait_until_ready()

[burnt turret]

It tells you what the error is

"cursor.execute was never awaited"

So await it

[modest pulsar]

yes, but when i do this, there is the old error

[modest pulsar]

here

pls help 😭

??

@terse stump can you warn him pls, he's spamming...

and can you help me quickly pls 😅

fixed

[torn sphinx]

Command raised an exception: ConnectionRefusedError: [Errno 111] Connect call failed what do I do with this error? Postgres database

[modest pulsar]

await cursor.execute(f"SELECT user_id FROM rpg WHERE user_id = ?", ctx.author.id)

This line is false, so how can i do it?

[frail frost]

I'm having an issue where I cannot remove varchars from my SQLite Database with a space in the middle of them.

GET_PLAYER_BY_NAME = "SELECT * FROM players WHERE players_name = ? COLLATE NOCASE"
DELETE_WITH_NAME = "DELETE FROM Players WHERE players_name = ? COLLATE NOCASE"

if message.content.startswith('$remove'):
        try:
            msg = str(message.content).split(" ", 2)
            print(msg)
            print(msg[1] + " " + "name")
            print(msg[1] + " " + "id")
            if msg[1] == "name":
                await message.channel.send("Removing "+msg[2]+"...")
                print("Removing from database")
                playerDatabase.remove_player_by_name(conn, msg[2])
                print("removed.")
                await message.channel.send("Removed successfully!")

            elif msg[1] == "id":
                val = int(msg[2])
                await message.channel.send("Removing user at ID "+msg[2]+"...")
                playerDatabase.remove_player_by_id(conn, val)
                print("removed.")
                await message.channel.send("Removed successfully!")
            else:
                await message.channel.send("Insert a name: $remove <name|id> <username (Case Sensitive)|database ID>")

        except Exception:
            await message.channel.send("Insert a name: $remove <name|id> <username|database ID>")

def remove_player_by_name(conn, name):
    with conn:
        #FIGURE OUT WHY NAMES WITH SPACES DONT WORK
        print(name)
        print(conn.execute(GET_PLAYER_BY_NAME, (name, )).fetchall())
        conn.execute(DELETE_WITH_NAME, (name, ))
        print("removed by name")

My output from my prints goes like this

['$remove', 'name', 'Jay See']
name name
name id
Removing from database
Jay See
[]
removed by name
removed.

So whenever I pull from the database using this persons name, it returns an empty list

However, running the same code on another user without a space in their name returns this

['$remove', 'name', 'Ahsan']
name name
name id
Removing from database
Ahsan
[(49, 'Ahsan', 'Recruit', 33343664, 0, 0, 0)]
removed by name
removed.

Which does return information

How can I get around this?

[sinful condor]

maybe before you put it in the database replace spaces with _

and then when you are seaching do the same

[frail frost]

I'll give that a try...

No dice. I used a .replace(" ", "_") and it didn't change the output

['$remove', 'name', 'Jay See']
name name
name id
Removing from database
Jay See
[]
Jay_See
[]
removed by name
removed.

[sinful condor]

then I dont know sorry

[frail frost]

😦

Appreciate the help 🙂

If anyone else might know, please @ me

[torn sphinx]

these columns are blocked

idk why

is this the reason it doesnt let me update them?

[charred delta]

maybe you need to authenticate?

[torn sphinx]

how tho?

i am just doing this

async with self.bot.db.acquire() as con:

[charred delta]

normally a db has a name, a user and a password

[torn sphinx]

do i need to close it for another one to use it?

i am connecting with password

[charred delta]

maybe another user?

[torn sphinx]

nop

e

same user

[charred delta]

can you access it from your code?

[torn sphinx]

i can with fetch

but then tried

.execute

with await because im using asyncpg

and it gets stuck there waiting

then i get into the gui of postgres

and they are locked

idk why

[charred delta]

can be connection problem or authentication problem

should give you errors

[torn sphinx]

since im using await it stays there

waiting

forever

never gives an error

[charred delta]

oh, then check if you are using async correctly

[torn sphinx]

i should add a timeout but then that would only throw a timeout error

[proven arrow]

That’s pgadmin showing it as read only. Because you have no primary key in that table.

It’s a pgadmin issue, and nothing to do with Postgres. You can still update it.

[torn sphinx]

it gets stuck in await

await con.execute()

never updates

[proven arrow]

That’s a different issue, but not to do with locking of tables.

[torn sphinx]

keeps waiting forever

o ok

i thought it was because since it couldnt get access it would keep trying indefinitely

now that people are active.... can I interrupt a bit so that I get an answer till tomorrow when I can try and fix it? Command raised an exception: ConnectionRefusedError: [Errno 111] Connect call failedI don't understand why this is happening

it's with postgres, too

[proven arrow]

That’s probably to do with your connection

[torn sphinx]

can it be because of the webhost?

cuz I thought that I could reach my local program-

[proven arrow]

I don’t understand what you mean?

How are you running it?

And where

[torn sphinx]

localhost, local server

tryna reach it from heroku

I guess it runs automatically by this pgadmin thing

it said active

[proven arrow]

Your trying to connect to a database on your local pc from heroku ?

[torn sphinx]

yes

[proven arrow]

Yeah that won’t work

They on a different network

Your pc is running it on localhost so only can be accessed from that pc

Heroku offers a Postgres service so use that

[torn sphinx]

makes sense

which is that?

[proven arrow]

I don’t follow?

[torn sphinx]

okay

np

[velvet fable]

In my main bot.py file I have my DB declared as, client.db = await aiosqlite.connect('Acer.sqlite').
In one of my cogs, I want to make a table, if not made set up as,

self.client.db.execute('''
            CREATE TABLE IF NOT EXISTS reactions(
            channel_id TEXT,
            emoji TEXT
            )
            ''')
```When I run this, I get an error of `'Bot' object has no attribute 'db'`, Can have some help?
I am using aiosqlite

[proven arrow]

That’s not really a database issue. It’s how you will have initialised those objects/variables.

[velvet fable]

hmm, ok

[frail frost]

Bumping this post.

[torn sphinx]

Hey guys.

I'm using MySQL with Pycharm

Am I filling in the info correctly? Like am I missing something? Because it keeps giving me an error

mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)

Now I know what it means so thats why I created a server called 127.0.0.1 (instead of localhost) in the hopes it might fix the problem.

I am using this for discord.py

I am also using Heroku to host my Discord.py bot, I don't think its an issue with discord.py because ive made many bots with mysql and Heroku before just not with Pycharm Professional.

It's as if pycharm doesn't like it when I use MySQL because vscode worked fine with it.

await con.execute("UPDATE public.currency SET $1 = $1 + $2 WHERE user_id = $3 ", (currency.lower(), int(value), user.id))

this is incorrect syntax

?

just the sql part

[rain plank]

what's the actual error

one thing I see is that you're passing in a tuple

but it's expecting three more args

[torn sphinx]

i solved it

[inland stone]

I find that with mongo, I have a lot of DB code in my python... perhaps more than I would like

it's all very specific and can't seem to be generalized

for example

[torn sphinx]

I'm trying to learn to use MySQL constraints but I am running into an error.

...
HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandNumber` int,"
    "   `HandDate` date NOT NULL,"
    "   `HandResults` float,"
    "   CONSTRAINT `HandID` PRIMARY KEY (HandNumber)"
    ") ENGINE=InnoDB")
...
    def insert_hand_data(self):
        """
        Insert Hand data - hand number, date of hand played, result
        :return:
        """
        try:
            hand_insert = "INSERT INTO Hands " \
                          "SELECT %s, %s, %s " \
                          "WHERE NOT EXISTS " \
                          "(SELECT HandNumber, HandDate, HandResults " \
                          "FROM Hands " \
                          "WHERE HandNumber = %s AND HandDate = %s AND HandResults = %s)"
            cursor.execute(hand_insert, (self.hand_number, self.date, self.result,
                                         self.hand_number, self.date, self.result))
            sql_connection.commit()
            print("Hand Date Inserted Succesfully")
        except errorcode as err:
            print(f"Error: {err}")
...
                hand.insert_hand_data()

Gives me error: mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '674762936' for key 'hands.PRIMARY'

[sullen hamlet]

What is a good database to start with?

[torn sphinx]

i started with pandas and mysql

but im still quite new so

[inland stone]

yeah mysql or sqlite probably

probably the latter

[sullen hamlet]

is there a good tutorial to learn it or did you just read the documentation

[inland stone]

yeah i bet there is a good tutorial

i think zodb is also pretty cool but i'm probably wrong about it idk

i'm also a noob

https://zetcode.com/db/sqlite/

[sullen hamlet]

thanks bro

[inland stone]

this site is probably good but maybe not very concise

https://stackabuse.com/a-sqlite-tutorial-with-python/

this should be better

@sullen hamlet

[sullen hamlet]

your a life saver

[torn sphinx]

        sql = """SELECT tag_name
                 FROM tags
                 WHERE guild_id=$1 AND tag_name= $2
                 ORDER BY similarity(tag_name, $2) DESC
                 LIMIT 100;
              """

        results = await self.bot.db.fetch(sql, ctx.guild.id, query)

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedFunctionError: function similarity(character varying, text) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.```

the query was ash

idk why it is not able to find

    @commands.command() 
    @commands.has_guild_permissions(administrator=True)
    async def generate(self, ctx, currency, amount, user : discord.Member ): 
        async with self.bot.db.acquire() as con: 
            await con.execute("UPDATE public.currency SET value = value + $1 WHERE user_id = $2 and currency = $3", amount, user.id, currency.upper())
            await ctx.send(f"Generated `{amount}` {currency.lower().capitalize()} for user {user}")

Command raised an exception: DataError: invalid input for query argument $1: 'money' ([<class 'decimal.ConversionSyntax'>])

how can i convert it or idk

im guessing whats wrong tbh

nvm

i was entering the wrong arguments lol

[shy shuttle]

its not complaining about not finding the data, it's that there is no sql function similarity(character varying, text) on the database. so that you queried for "ash" doesnt matter because the DBMS cant even run your query. That looks like an error originating in postgres.

[torn sphinx]

how can i fix it?

[shy shuttle]

use a function that exists in the database

[torn sphinx]

how do i check what all function exists in my db

[shy shuttle]

try just doing ORDER BY tag_name. since that looks like it came from postgres, you can look here: https://www.postgresql.org/docs/13/functions.html

[torn sphinx]

        sql = """SELECT tag_name
                 FROM tags
                 WHERE guild_id=$1 AND tag_name= $2
                 ORDER BY tag_name DESC
                 LIMIT 100;
              """

its only showing 1 tag with same name

[golden warren]

Hello! why it doens't delete id in my db?

            # Check if id in db is in server.
            for ids, member in zip(user_id, guild):
                members_list.append(member.id)

                if ids[0] not in members_list:
                    self.logger.log('information', f'{ids[0]} not in server.')
                    DB.usersdb_cursor.execute(
                        f'DELETE FROM users WHERE user_id = {ids[0]}')
                    self.logger.log('information', f'{ids[0]} deleted!')
                else:
                    return

I get no error.

[torn sphinx]

how will i set up a asqlite connection pool?

[proven arrow]

It doesn’t support connection pools out of the box

[torn sphinx]

hmm ok i have another question rn

[proven arrow]

And it’s not really needed because sqlite connections are cheap to make

[torn sphinx]

Cog Moderation Loaded
Cog Tickets Loaded
Cog Jishaku Loaded
Cog Tags Loaded
Bot has logged in
Ignoring exception in command tag update:
Traceback (most recent call last):
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\cogs\tags.py", line 81, in update
    await cursor.execute(f'''UPDATE Tags SET saything = ? WHERE tag = ?''' , (str(content , name)))
TypeError: decoding str is not supported

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: decoding str is not supported

i get this error

        
    @tag.command()
    async def update(self , ctx , name = None , * , content = None):
        if name is None:
            await ctx.send("Tag name is a argument that is missing")

        if content is None:
            await ctx.send("Content is a argument that is missing")

        async with asqlite.connect('tags.db') as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(f'''UPDATE Tags SET saything = ? WHERE tag = ?''' , (str(content , name)))
                    await ctx.send("Tag Edited :thumbsup:")

my code

[proven arrow]

Well what even is str(content, name) ? Is that even valid

[torn sphinx]

its a argument

[torn sphinx]

@tag.command()
    async def update(self , ctx , name = None , * , content = None):

check the second one

it has defined content and name

[proven arrow]

But your passing 2 args to the string function

[torn sphinx]

then what i am supposed to do?

[proven arrow]

Remove the function and just pass the parameters

str is for converting an object to a string

[torn sphinx]

ok

[torn sphinx]

another question how will i get a command to be like list every tag created in that server?

[proven arrow]

Show what your table looks like

[torn sphinx]

 await cursor.execute('''CREATE TABLE Tags
                                    (guild_id , author , tag , saything)''')

[proven arrow]

SELECT tag FROM Tags WHERE guild_id = ?

[torn sphinx]

and then we do?

i need to be alpabetical order i believe

[proven arrow]

use order by for it

[torn sphinx]

???

[proven arrow]

SELECT tag FROM Tags WHERE guild_id = ? ORDER BY tag ASC

That will order it A-Z, by the tag column

[torn sphinx]

alr then i will do cursorf.fethcall?

i dont understand the difference between fetchall and fetchone

[proven arrow]

fetchone only gets one row, and fetchall gets all rows

[torn sphinx]

oh k

[torn sphinx]

    @commands.command()
    async def taglist(self , ctx):
        async with asqlite.connect('tags.db') as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute('''SELECT tag FROM Tags WHERE guild_id = ? ORDER BY tag ASC LIMIT 30''' , (ctx.guild.id))

                    result = await cursor.fetchall()

                    localem = discord.Embed(name = "h" , color = discord.Color.green())
                    localem.add_field(name = "Tags list" , value = result)
                    await ctx.send(embed = localem)

and this happens

[burnt turret]

pretty sure aiosqlite requires you to pass the parameters in a tuple

[proven arrow]

They are using another module otherwise it wouldve thrown an error

[torn sphinx]

its asqlite :Angery:

[burnt turret]

oh right

[proven arrow]

If it returns nothing then no result was matched in your db

[torn sphinx]

i have 1 tag inside it

[proven arrow]

Then the values you pass to the query probably dont match

[burnt turret]

may i ask why you're using this module instead of the more popular one?

i can easily find docs for the other but i can't find any for this

[torn sphinx]

 »   Error: Couldn't find that app.
 »
 »   Error ID: not_found
```that's the only thing it tells me for my heroku postgres database

I've got a pool, I used it and it doesn't work. I'm using discord.py so some things are just keywords of the discord wrapper, but they always tell me that my problem is not discord.py, so...

DB_SETTINGS = {
        data
    }
    bot.pool = await asyncpg.create_pool(**DB_SETTINGS)```
but it doesn't find the database or something

[hasty juniper]

807190213452759050 807190214233161788 807190215148044289 807190215533658173 807190216723922984

ChannelAllUser = await guild.create_voice_channel(name = f'''Members: {len(guild.members)}''')
ChannelInVoice = await guild.create_voice_channel(name = f'''In voice: {voice}''')
ChannelAllChannel = await guild.create_voice_channel(name = f'''Chanels {len(guild.text_channels) + len(guild.voice_channels)}''')
ChannelsAllTextChannels = await guild.create_voice_channel(name = f'''TextChannels: {len(guild.text_channels)}''')
ChannelsAllVoiceChannels = await guild.create_voice_channel(name = f'''VoiceChannels: {len(guild.voice_channels)}''')
print(ChannelAllUser.id, ChannelInVoice.id, ChannelAllChannel.id, ChannelsAllTextChannels.id, ChannelsAllVoiceChannels.id)
await self.bot.con.execute("UPDATE Guilds SET ChannelAllUserId = $1 and ChannelInVoiceId = $2 and ChannelAllChannelId = $3 and ChannelsAllTextChannelsId = $4 and ChannelsAllVoiceChannelsId = $5 WHERE GuildId = $6", ChannelAllUser.id, ChannelInVoice.id, ChannelAllChannel.id, ChannelsAllTextChannels.id, ChannelsAllVoiceChannels.id, guild.id)

output = 807190213452759050 807190214233161788 807190215148044289 807190215533658173 807190216723922984

asyncpg.exceptions.DatatypeMismatchError: "channelalluserid" column is bigint and expression is boolean TIP: Rewrite or convert the expression.

[brazen charm]

, seperation when updating columns not and

[torn sphinx]

it has no difference its normal sqlite but using async with functions

[bronze tiger]

hey how do we store list items in a single column named as tagged_users?
like i want to store tagged_users=[1,2,5,7,3] to one of the column in my db table
whats the hack?

[proven arrow]

What database?

[bronze tiger]

sqlite or postgres

[proven arrow]

Generally you would normalise that list so it’s stored in tables/column

Instead of storing it as a whole list

[bronze tiger]

yeah then i could collapse them if they gets deleted

[proven arrow]

However Postgres supports storing as array, but sqlite doesnt. So for sqlite you will have to store a serialised string version of it.

[bronze tiger]

what is sqlalchemy datatype for list in postgres?

[proven arrow]

Not sure I don’t use it, your better off checking docs

[torn sphinx]

hello so i need some help

my old command died

it gives error

so i am moving on

anyways

i am making a search command

how will i make a sqlite query that searches?
like if someone says
>tag search ash it will say all the tags starting from the name ash

[bronze tiger]

hey can i DM you? @proven arrow

[proven arrow]

I’m at work, better of asking here

[torn sphinx]

can i have some help again?

[bronze tiger]

sure mate 🙂

[torn sphinx]

oh hi pikachu youre here too e.e

anyway

i am making a search command
how will i make a sqlite query that searches?
like if someone says
||>tag search ash|| it will say all the tags starting from the name ||ash||

Command raised an exception: AttributeError: 'Bot' object has no attribute 'pool' at

async with bot.pool.acquire() as connection:
        async with connection.transaction():
            await connection.execute(
                f'''UPDATE first_table
                SET first_message = {message}''', 
                (arg)
            )```
even tho I defined it at the start of the code:
```Python
async def dbinit():
    DB_SETTINGS = {
            #data not for you (I guess)
        }
    bot.pool = await asyncpg.create_pool(**DB_SETTINGS)
    client.dbconnection = connection```
why is that?

[torn sphinx]

connection pool? 🆒

[burnt turret]

I'm not asking why you aren't using sqlite, I'm asking why you aren't using aiosqlite

[torn sphinx]

i had problems setting it up

[burnt turret]

when do you actually call this dbinit function?

[torn sphinx]

after the imports and defining the bot and client

[burnt turret]

can you show it

[torn sphinx]

import asyncio, datetime, json, math, os, random, time, operator
from time import gmtime
import aiohttp
import discord
from discord.ext import commands, tasks
from discord.utils import get
import sqlalchemy
from sqlalchemy import create_engine
import asyncpg

bot = commands.Bot(command_prefix="Ar!", description="An Armonian.", intents=discord.Intents.all())
timer = datetime.datetime.utcnow().strftime("%B %d %Y - %H:%M:%S")
bot.remove_command('help')
client = discord.Client()
# -----------------SETUP----------------------
async def dbinit():

[burnt turret]

first of all, remove that client = discord.Client

you use only one, commands.Bot or discord.Client

second, that's not where you're calling the dbinit function

that's where you're defining it

that function will never run without you calling it

[torn sphinx]

makes sense

where should I call it?

[burnt turret]

you can't just call it anywhere now

because it's an async function you've defined, you'd need to await it as well

check out the first pinned message in this channel

it has a link explaining making the connection pool (although it's just one way of doing it)

[torn sphinx]

    @tag.command()
    async def search(self , ctx , name):
        idguild = ctx.guild.id
        async with asqlite.connect('tags.db') as conn:
            async with conn.cursor() as cursor:
                await cursor.execute('''SELECT tag FROM Tags WHERE tag LIKE ? AND guild_id = ?''', (name , idguild))
                result = await cursor.fetchall()
                
                localem = discord.Embed(name = "h" , color = discord.Color.green())
                for row in result:
                    localem.add_field(name = "Matching Tags" , value = result)
                await ctx.send(embed = localem)

hey so i have this code

but this happens

this one?

nvm found it

somebody help me e.e

[torn sphinx]

i need help creating a connection pol so can you send it to me?

the link :Eee:

#databases message

[burnt turret]

it's linked as "using asyncpg with discord.py" in the message

but i don't think whatever module you're using comes with connection pools?

[torn sphinx]

:eee: i need help with asqlite connection pool

[torn sphinx]

idk if it does

lets talk about that later can you help me rn?

[burnt turret]

hence why i was asking why you aren't using one with proper documentation

[torn sphinx]

    @tag.command()
    async def search(self , ctx , name):
        idguild = ctx.guild.id
        async with asqlite.connect('tags.db') as conn:
            async with conn.cursor() as cursor:
                await cursor.execute('''SELECT tag FROM Tags WHERE tag LIKE ? AND guild_id = ?''', (name , idguild))
                result = await cursor.fetchall()
                
                localem = discord.Embed(name = "h" , color = discord.Color.green())
                for row in result:
                    tag = row
                    localem.add_field(name = "Matching Tags" , value = tag)
                await ctx.send(embed = localem)

i have this code

it does this

[torn sphinx]

because i couldnt set it up i tried 10 times and asqlite was the only one that worked for me

[burnt turret]

and you have data in your table that is equal to joe

[torn sphinx]

yes

and in my command it will be bot.db.acquire

right?

[burnt turret]

yeah

note that you don't always have to be acquiring connections from the pool, await bot.db.execute(... will all still work directly too

[burnt turret]

whats the data type of your guild ID column

[torn sphinx]

you can see e.e

[burnt turret]

what is that first row

{name} is also a tag you made?

[torn sphinx]

so what is the point even putting it in my code?

[torn sphinx]

not

can I delete all of it?

it just

randomly happened

probably my some error

f strings e.e

[burnt turret]

i'm...not very sure how that works :/

[torn sphinx]

sorry if I'm interupting too much

[burnt turret]

i was just mentioning that they're also methods of the pool object you get

[torn sphinx]

its ok there

[burnt turret]

but the way you said was right, that's how you'd acquire from it

[torn sphinx]

okay

[burnt turret]

yeah i don't see the point of that row

[torn sphinx]

I'm trying it out now

[burnt turret]

but you didn't answer me, what data type is the guild ID column

but judging by that first row im guessing its some kind of character type

[burnt turret]

i.e you're storing guild IDs as strings in your database, so you want to pass str(ctx.guild.id) in while checking

[torn sphinx]

i did

[burnt turret]

that's not what i see in your code

you're passing in the id directly

as an integer

[torn sphinx]

so how will id o it e.e

[burnt turret]

i literally said what to do

so you want to pass str(ctx.guild.id) in while checking

[torn sphinx]

await cursor.execute('''SELECT tag FROM Tags WHERE tag LIKE ? AND guild_id = ?''', (name , str(idguild))

like this?

[burnt turret]

try it

[torn sphinx]

or i am doing it wrong :Panic

[burnt turret]

that looks right

[torn sphinx]

    @tag.command()
    async def search(self , ctx , name):
        idguild = ctx.guild.id
        async with asqlite.connect('tags.db') as conn:
            async with conn.cursor() as cursor:
                await cursor.execute('''SELECT tag FROM Tags WHERE tag LIKE ? AND guild_id = ?''', (name , str(idguild)))
                result = await cursor.fetchall()
                
                localem = discord.Embed(name = "h" , color = discord.Color.green())
                for row in result:
                    tag = row
                    localem.add_field(name = "Matching Tags" , value = tag)
                await ctx.send(embed = localem)

[burnt turret]

yes

[torn sphinx]

i need it to send the name

[burnt turret]

have you worked with this module before?

[torn sphinx]

well yes only some commands tho

[burnt turret]

you're directly passing in the Row object that the database returned

you want to be accessing the data from it

[torn sphinx]

how to?

i never used the LIKE statement before

[burnt turret]

i've never used sqlite, so i don't know how you do it

[burnt turret]

that's not related to this

whenever you get data it'd be in this form

gonna guess you should be able to use indexing or something, but best to refer to sqlites documentation

!d sqlite3.Row

[delicate field]

`sqlite3.Row`

class sqlite3.Row```
A [`Row`](#sqlite3.Row "sqlite3.Row") instance serves as a highly optimized [`row_factory`](#sqlite3.Connection.row_factory "sqlite3.Connection.row_factory") for [`Connection`](#sqlite3.Connection "sqlite3.Connection") objects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and [`len()`](functions.html#len "len").

If two [`Row`](#sqlite3.Row "sqlite3.Row") objects have exactly the same columns and their members are equal, they compare equal.

`keys`() This method returns a list of column names. Immediately after a query, it is the first member of each tuple in [`Cursor.description`](#sqlite3.Cursor.description "sqlite3.Cursor.description").

Changed in version 3.5: Added support of slicing.

[burnt turret]

oh nice

#databases message it works similar to this by the looks of it

[torn sphinx]

I got a....

Syntax error

[burnt turret]

where

[torn sphinx]

bot.db = bot.loop.run_until_complete(asyncpg.create_pool(
        'host': 'yes',
        'port': 5432,
        'database': 'yes',
        'user': 'yes',
        'password': 'yes'
    ))```

at the host one

[burnt turret]

it won't take in a dictionary like that

wait

[torn sphinx]

oh ye {}

innit

[burnt turret]

you're doing it right here

you can define the dictionary separately

and then pass it into the create_pool like **dictionary_name, the way you've done it there

[torn sphinx]

okay

[burnt turret]

note that i'm only saying you're doing the dictionary stuff right there

[torn sphinx]

yes

understood

DB_SETTINGS = {
    'host': 'yes',
    'port': 5432,
    'database': 'yes',
    'user': 'yes',
    'password': 'yes'
}
    bot.pool = await asyncpg.create_pool(**DB_SETTINGS)
bot.db = bot.loop.run_until_complete(asyncpg.create_pool(
    ))
```?

wait no

[burnt turret]

no need of that bot.pool line

[torn sphinx]

yeah

[burnt turret]

just pass **DB_SETTINGS to asyncpg.create_pool

[torn sphinx]

DB_SETTINGS = {
    'host': 'yes',
    'port': 5432,
    'database': 'yes',
    'user': 'yes',
    'password': 'yes'
}
bot.db = bot.loop.run_until_complete(asyncpg.create_pool(**DB_SETTINGS))

[burnt turret]

yep

[torn sphinx]

yessir

no pg_hba.conf entry for host yep, SSL off

and that's a heroku problem

innit

[burnt turret]

dunno, have never used heroku for postgres

or seen that error before

[torn sphinx]

asyncpg.exceptions.InvalidAuthorizationSpecificationError

[burnt turret]

i think its because you've gotta change some stuff in pg_hba.conf for remote access to the database, have you done all that?

[torn sphinx]

where to do that?

[burnt turret]

dunno how it works on heroku :/

heroku probably has their tutorial article for postgres won't they

[torn sphinx]

don't think so

let me check

they used something completely different

import os
import psycopg2

DATABASE_URL = os.environ['DATABASE_URL']

conn = psycopg2.connect(DATABASE_URL, sslmode='require')```

also pip install psycopg2-binary

[burnt turret]

no other setup?

the module shouldn't be a problem i think

[torn sphinx]

they said be sure to update the requirements.txt with these imports

no more

[burnt turret]

wait a minute, let me check how you'd pass that sslmode in asyncpg

[torn sphinx]

yeah that could be a problem

[burnt turret]

you can pass ssl="require" to match what you've sent

[torn sphinx]

inside the create_pool()?

[burnt turret]

yes

[torn sphinx]

I did this command

@bot.command()
async def setup(ctx, message):
    async with bot.db.acquire() as connection:
        async with connection.transaction():
            await connection.execute(
                f'''UPDATE first_table
                SET first_message = {message}'''
            )
            await ctx.send("**All set!**")
```but ``Ar!setup this is my message`` doesn't work, because ``this`` is not a column

and Ar!setup first_message this is my message? set it to this is my message

[burnt turret]

i don't understand

[torn sphinx]

me neither

[burnt turret]

no i dont understand what your problem is lmao

[torn sphinx]

I thought it's
UPDATE first_table
SET first_message = {message}

[burnt turret]

i don't see the point of that statement

[torn sphinx]

but after the equal sign I still have to define the column

[burnt turret]

how is this first_table defined

[torn sphinx]

that's a table

[burnt turret]

also, dont use f-strings: the right way to do it is explained in the first pinned message (linked as "parametrized queries")

[burnt turret]

i got that, whats its structure

[torn sphinx]

it has one column

and 1 row

I think

[burnt turret]

um right

[torn sphinx]

wait

what did I even do...

[burnt turret]

i don't know

but i don't see the point of a table that's just one column and one row

is that data even important enough to be stored then

[torn sphinx]

I'm just testing

I've got the table first_table with the column first_message

wait I can't update a column

[burnt turret]

no

are you trying to rename the column

[torn sphinx]

no

I'm trying to update a row

then I have to delete it

and make another one

[burnt turret]

to update a row, you have to specify which row is to be updated

[torn sphinx]

with making an id for it?

[burnt turret]

actually nvm i think you can UPDATE without a WHERE too

and it just updates every row i think

[torn sphinx]

I've got one row

no I actually don't have any

[burnt turret]

are you sure you've got a row

yeah

insert into the table then

[torn sphinx]

wait what is the command for psql to check that?

the rows

[burnt turret]

?

to retrieve data?

[torn sphinx]

yeah basically

[burnt turret]

you do that with a SELECT statement

check out sqlbolt, it's got interactive exercises to help you learn SQL

[torn sphinx]

oh damn

I already watched hours of a tutorial video from freeCodeCamp, I just forget everything

[burnt turret]

i think this is better

because you're actually doing

[torn sphinx]

okay

ty for the tips

[crisp zodiac]

Recursion of python

[gilded sleet]

is this how to do children-parent structure database?

i use mongodb

[cedar flare]

I want to append the new profile to json file, but it appends to the end of the file instead of appending it inside the users: [ ]
Python file-

import json


def createProfile(user_id):
    userProfile = {
        'user_id': user_id,
        'wins': 0,
        'loses': 0,
        'draw': 0,
        'balance': 0
    }

    return userProfile

userID = 8291

with open('userdata.json', 'r+') as f:
    users = json.load(f)
    users.update(createProfile(userID))
    json.dump(user, f, indent=2)

Json file-

{
  "users": [
    {
      "user_id": 458279513349160963,
      "wins": 1,
      "loses": 0,
      "draw": 0,
      "balance": 0
    },
    {
      "user_id": 631891684250222592,
      "wins": 0,
      "loses": 0,
      "draw": 0,
      "balance": 0
    }
  ]
}

[modest pulsar]

hi, how can I do to read a db?

like a json?

[burnt turret]

i don't understand, what db do you use?

[torn sphinx]

You can use directly any database provider or an orm.

For example to read from postgres you can use psycopg2, but in this case you have to use sql commands too.

But you can use sqlalchemy or other orm, which is an abstraction layer to access databases without (too much) sql specific command.

    @tag.command()
    @commands.has_permissions(manage_messages = True)
    async def update(self , ctx , name = None , * , content = None):
        if name is None:
            await ctx.send("Tag name is a argument that is missing")

        if content is None:
            await ctx.send("Content is a argument that is missing")

        async with asqlite.connect('tags.db') as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(f'''UPDATE Tags SET saything = ? WHERE tag = ?''' , ((content , name)))
                    await ctx.send("Tag Edited :thumbsup:")

hey so i have this code what i want to do is that the tag can only be edited if it was created by the ctx.author which i m saving

[modest pulsar]

ok thx, but i think you didn't really understand my question... how can I do to read my db from the code? like if in want to get informations?

[torn sphinx]

ca anybody help me?

[torn sphinx]

Your question is not clear for me.
I don't understand this:
"how can I do to read my db from the code"

Do you want read data from your database?
Do you want run sqlquery?

[modest pulsar]

yes sorry

i don't know how to explain that in english

i give you an example:

I'm making a rpg: I want to get the money of a user, which is in the column "money" of the line with the user_id

[proven arrow]

what does your table look like?

[torn sphinx]

i told you 13 hours ago

[modest pulsar]

😂

[proven arrow]

And you expect me to remember that?

[torn sphinx]

lmao i tried something myself

        
    @tag.command()
    @commands.has_permissions(manage_messages = True)
    async def update(self , ctx , name = None , * , content = None):
        nameman = ctx.author
        if name is None:
            await ctx.send("Tag name is a argument that is missing")

        if content is None:
            await ctx.send("Content is a argument that is missing")

        async with asqlite.connect('tags.db') as conn:
                async with conn.cursor() as cursor:
                    try:
                        await cursor.execute(f'''UPDATE Tags SET saything = ? WHERE tag = ? AND author = ?''' , ((content , name , nameman)))
                        await ctx.send("Tag Edited :thumbsup:")
                    except Exception:
                        await ctx.send("You dont Own the tag")
                        print(Exception)

but now it tells me i dont own the tag

like what

[proven arrow]

Your exception is very vague, it could be anything thats causing that

[torn sphinx]

lemme try without that statement

[torn sphinx]

Cog Moderation Loaded
Cog Tickets Loaded
Cog Jishaku Loaded
Cog Tags Loaded
Bot has logged in
Ignoring exception in command tag update:
Traceback (most recent call last):
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\cogs\tags.py", line 85, in update
    await cursor.execute(f'''UPDATE Tags SET saything = ? WHERE tag = ? AND author = ?''' , ((content , name , nameman)))
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\asqlite.py", line 149, in execute
    return await self._post(self._cursor.execute, sql, parameters)
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\asqlite.py", line 57, in _call_entry
    result = entry.func(*entry.args, **entry.kwargs)
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

[proven arrow]

You can do something like: SELECT money FROM users_table WHERE user_id_column = some_id

[torn sphinx]

this error happens

[proven arrow]

pass it as a single tuple, await cursor.execute('''UPDATE Tags SET saything = ? WHERE tag = ? AND author = ?''' , (content , name , nameman))

[torn sphinx]

ok

[torn sphinx]

Ignoring exception in command tag update:
Traceback (most recent call last):
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\cogs\tags.py", line 85, in update
    await cursor.execute('''UPDATE Tags SET saything = ? WHERE tag = ? AND author = ?''' , (content , name , nameman))
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\asqlite.py", line 149, in execute
    return await self._post(self._cursor.execute, sql, parameters)
  File "c:\Users\PREMIER\Desktop\Word Sheets\code\asqlite.py", line 57, in _call_entry
    result = entry.func(*entry.args, **entry.kwargs)
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\PREMIER\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InterfaceError: Error binding parameter 2 - probably unsupported type.

still the same error

[modest pulsar]

only this? i don't have to do something like with open ('qsdf') as r:?

[proven arrow]

Then your values you pass to the query will be of a type that sqlite cant understand.

[torn sphinx]

:uhh: it is a str

[proven arrow]

You do, but you didnt provide enough details to give you a full answer.

[torn sphinx]

i dont think its of that type its either a int or a str

[proven arrow]

No its not, the error literally says its not of the supported type

Show where you define those values

[torn sphinx]

what

[modest pulsar]

i gave an example... Otherwise, i don't know how to explain it to you...

[proven arrow]

You have nameman = ctx.author . So its definetly not what you think it is.

[torn sphinx]

wait

[modest pulsar]

so @proven arrow ? I just have to add with open ('main.sqlite') as r: before?

[torn sphinx]

thisis how i said it

how i am saving it

[modest pulsar]

close and click on save

[torn sphinx]

alr done

[modest pulsar]

ctrl+s so?

[proven arrow]

Ive told you your error, and why you get it. Your passing the incorrect type.

[torn sphinx]

still not

[torn sphinx]

what is the incorrect type you can see there is nothing wrong

[proven arrow]

Maybe go ask in discord channel what ctx.author returns and then try again.

[modest pulsar]

so it's already saved

@torn sphinx ctx.author return the name and the #

like this: thomanii#2222

[gilded sleet]

is this how i update db for mongo

the structure is

[torn sphinx]

then i see nothing wrong @proven arrow

[proven arrow]

Well did you find out what it returns?

[torn sphinx]

its not a valid sql type people say

[proven arrow]

Which i, as well as your error already told you many times. 😐

[torn sphinx]

@proven arrow I FIXED IT

WOHOOOO

[modest pulsar]

@proven arrow you didn't answer to my question 😅

[torn sphinx]

How to change foreign key to update when primary key is changed?

[proven arrow]

You can add on update cascade to the column to make the changes reflect

[burnt turret]

not really, ctx.author is a discord.Member object representing the person who called the command
what you mean is that casting it to string str(ctx.author) gives you the username#discrim form

[proven arrow]

Here its shown how you can use it, with code example https://docs.python.org/3/library/sqlite3.html

First 2 codeblocks

[modest pulsar]

yes that's true

[modest pulsar]

ok thx

[torn sphinx]

well yeah i did fix it but it didnt update

[proven arrow]

That reply was for someone else, not you 🙂

[torn sphinx]

anyway this happens e

[proven arrow]

You need to commit the changes

[torn sphinx]

the creator of asqlite says it isnt needed

also it was working before i added the author thing

[proven arrow]

Then that would mean no rows matched the filters in your where clause

[modest pulsar]

Hi, i'm new to db, so i did this:

with open("main.sqlite", "r") as f:

            money = await cursor.execute("SELECT argent FROM rpg WHERE user_id = ?",(ctx.author.id,))
            stone = await cursor.execute("SELECT pierre FROM rpg WHERE user_id = ?",(ctx.author.id,))
            petrole = await cursor.execute("SELECT pétrole FROM rpg WHERE user_id = ?",(ctx.author.id,))
            xp = await cursor.execute("SELECT xp FROM rpg WHERE user_id = ?",(ctx.author.id,))
            level_extract = await cursor.execute("SELECT level_extract FROM rpg WHERE user_id = ?",(ctx.author.id,))
            level_car = await cursor.execute("SELECT level_car FROM rpg WHERE user_id = ?",(ctx.author.id,))
            shield = await cursor.execute("SELECT bouclier FROM rpg WHERE user_id = ?",(ctx.author.id,))

But here is the result...
Can anyone help me pls?

[proven arrow]

@modest pulsar Because your not fetching the data, from the cursor, and instead you are directly outputting the cursor object.

Also you can make a single query for it instead of all those

[modest pulsar]

hum sorry i don't really understand

[proven arrow]

db_connection = await aiosqlite.connect("main.sqlite")
cursor = await db_connection.execute("SELECT argent, pierre, pétrole, xp, level_car, bouclier FROM rpg WHERE user_id = ?", (ctx.author.id,))
result = await cursor.fetchone() 

Then result will be (all, your, values, ..) which you can access by result[0] for argent, result[1] for pierre ...

[modest pulsar]

ok thxxxx

sorry my server had been attacked

so it's why i desapeardsuddenly

[modest pulsar]

@proven arrow

db = await aiosqlite.connect("main.sqlite")
        cursor = await db.cursor()
        
        cursor.execute("SELECT argent, pétrole, pierre, xp, warns, level_extract, level_car, bouclier FROM rpg WHERE user_id = ?", (ctx.author.id,))
        result = await cursor.fetchone() 

        money = result[0]
        petrole = result[1]
        stone = result[2]
        xp = result[3]
        warn = result[4]
        level_extract = result[5]
        level_car = result[6]
        shield = result[7]

Here's the error, line money = result[0]:

raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

[burnt turret]

because result is None

meaning there were no rows which matched your query

[modest pulsar]

yes there is, i verified...

[burnt turret]

well that's not what your database seems to say

[burnt turret]

what is the data type of the user_id column

[modest pulsar]

INT

[burnt turret]

this is getting all columns right, you can just write SELECT * ... then btw

[modest pulsar]

but, this is not the problem, because i already used it, with another command, and it works

[burnt turret]

i dunno aiosqlite, does execute have to be awaited?

[modest pulsar]

ok thx

[burnt turret]

from LP's example, he awaits execute

[modest pulsar]

sorry i'mp stupid -_-

[burnt turret]

why aren't you doing this?

[modest pulsar]

i try with an await

[burnt turret]

he seems to have told you what you want

[modest pulsar]

it works

thx

[modest pulsar]

but, it's what i did...

[burnt turret]

it isn't

[modest pulsar]

why?

[burnt turret]

uh you didn't await it while he did?

[modest pulsar]

yes i just forgot this 😂

but i don't want to just copy paste lol

i can't learn if i do this

[edgy geode]

What is the best DB and how can I learn it

[burnt turret]

I don't think there is a "best" DB out there, different databases for different use cases

[uneven lava]

Hello
I've a question with sqlalchemy, I search in the doc & internet, but didn't found

I've 2 tables in my db, and they both have their primary_key equivalent to a discord guild ID

i've a Guild table, which contain informations like prefix etc... and Toggle, which contains informations of what commands are enable/disable in the guild

I've one-to-one relation, so I can do Guild.toggle.xx

and I want that when I create a Guild object and append it to the db, it automatically create also a Toggle object

class Guild(Base):
    __tablename__ = "guilds"

    guild_id = Column("guild_id", Integer, primary_key=True)
    prefix = Column(String, default="!")

    toggle = relationship('Toggle', uselist=False, back_populates="guild")

class Toggle(Base):
    __tablename__ = "toggles"
    guild_id = Column("guild_id", ForeignKey('guilds.guild_id'), primary_key=True)
    guild = relationship("Guild", back_populates="toggle")

    say = Column(Boolean, default=1)
    emote = Column(Boolean, default=1)

[torn sphinx]

Not exactly sure what I'm doing wrong and why this is happening from my understanding that you don't need to include an auto incremented column when inserted data in MySQL

...
HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandID` int NOT NULL AUTO_INCREMENT,"
    "   `HandNumber` int UNIQUE,"
    "   `HandDate` date NOT NULL,"
    "   `HandResults` float,"
    "   PRIMARY KEY (HandID)"
    ") ENGINE=InnoDB")
...
            hand_insert = "INSERT INTO Hands " \
                          "SELECT %s, %s, %s " \
                          "WHERE NOT EXISTS " \
                          "(SELECT HandNumber, HandDate, HandResults " \
                          "FROM Hands " \
                          "WHERE HandNumber = %s AND HandDate = %s AND HandResults = %s)"
            cursor.execute(hand_insert, (self.hand_number, self.date, self.result,
                                         self.hand_number, self.date, self.result))
...

Gives me error: mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1

would it be a good idea when making an inventory system to save item ids in an array as a value of an inventory column?

does sql have trouble handling long arrays of numbers ?

[modest pulsar]

await cursor.execute("UPDATE rpg SET bio = ? FROM user_id = ?",(bio,ctx.author.id,))

i've got this error:
raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "=": syntax error

[torn sphinx]

isnt the from a where?

if you dont enter a condition it will update every single row

[rain plank]

the syntax is (I think)

UPDATE table_name SET column = value WHERE column = value

[torn sphinx]

it is

actually

about the ? i think that works as $1, $2, and so on

[modest pulsar]

that was the error thx

sorry i forgot to say here that i fixed it

[torn sphinx]

ye there was no point in that from

[modest pulsar]

thx but i already fixed it

[modest pulsar]

yes

sorry

it's a stpid mistake

[torn sphinx]

its ok

we all get something wrong at times

[torn sphinx]

anyone?

[rain plank]

it works for both $<number> and ?

[torn sphinx]

o ok

[modest pulsar]

I have this issue, but i don't know how to fix it... I did the same things than the other times...
raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: database is locked

db = await aiosqlite.connect('main.sqlite')
            cursor = await db.cursor()

            await cursor.execute(f"UPDATE rpg SET pierre = pierre+{pierres} WHERE user_id = ?",(ctx.author.id,))

[torn sphinx]

why do you have to use f strings

about the error there are lots of posts

online

[modest pulsar]

because it's a local variabme

variable

[torn sphinx]

cant you do the same with "?"

[modest pulsar]

so t's not a problem, is it,

[modest pulsar]

ok

[torn sphinx]

no its not bad its bad practice

a guy explained it here

#help-honey message

[modest pulsar]

ok thx, but i found the solution

i was closing my db

but this wasn't the good thing to do

[velvet fable]

using aiosqlite, how would you update a row? would it be UPDATE from sticky where value=??

[torn sphinx]

i am trying to populate a table but the data isn't being inserted in MySQL

HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandID` int NOT NULL AUTO_INCREMENT PRIMARY KEY,"
    "   `HandNumber` int UNIQUE,"
    "   `HandDate` date NOT NULL,"
    "   `HandResults` float"
    ") ENGINE=InnoDB")
...
            hand_insert = "INSERT INTO Hands (HandNumber, HandDate, HandResults) " \
                          "SELECT %s, %s, %s " \
                          "FROM Hands " \
                          "WHERE NOT EXISTS " \
                          "(SELECT HandNumber, HandDate, HandResults " \
                          "FROM Hands " \
                          "WHERE HandNumber = %s AND HandDate = %s AND HandResults = %s)"
            cursor.execute(hand_insert, (self.hand_number, self.date, self.result,
                                         self.hand_number, self.date, self.result))

[primal cave]

How to make a 'list' of ID channels on mongodb in which the user will receive experience (I already have a system for gaining xp for messages)

[torn sphinx]

anyone knwo, how i can add a new Database on beekeeper without the files from the standard one? I use postgressql.

[torn sphinx]

@client.command(aliases=['inf'])
@commands.has_permissions(kick_members = True)
async def infractions(ctx , user : discord.Member = None):
  
            if user is None:
              user = ctx.author
              '''
              Displays the person's infractions similar to Modlogs
              '''

            async with asqlite.connect('modlogs.db') as conn:
                async with conn.cursor() as cursor:
                  
                        

                       
                        query = f"SELECT * FROM Modlogs WHERE user_id = ?"


                        await cursor.execute("SELECT * FROM Modlogs WHERE user_id = ?" ,(str(user.id , ctx.guild.id))

)
                        
                        m = await cursor.execute('''SELECT rowid FROM Modlogs Where user_id = ?''', (str(user.id),))
                        result = await cursor.fetchall()
                        localem = discord.Embed(name = f"Infractions Of {user}" , color = discord.Color.green())
                        
                        localem.add_field(name =f"All infractions of {user.name}" , value = "All the infractions of the given user")
                        for row in result:
                            guild_id , mod_name,reasonthing, use_namer, time , command = row
                            
                            localem.add_field(name =f"Infraction By {mod_name} ID : {m}" , value = f"Type: {command} \n Reason: {reasonthing} \nAt: {time}" , inline=False)
                            await ctx.send(embed = localem)
                            

                        
                        if len(result) == 0:
                          localem.add_field(name = "Infractions" , value = "Not Found")
                          await ctx.send(embed = localem)

hey so i have this code

but it doesnt send the embed

no errors either

[restive pilot]

What type of database are you working with, RDBMS?

[proven arrow]

What even is your end goal here? It makes no sense why you're inserting like this.
What values are you inputting into the query?

[torn sphinx]

Is someone else experiencing sudden problems with mongodb atlas?

[cerulean jackal]

is built-in module the recommended way to work with sqlite?

[proven arrow]

Yes

[cerulean jackal]

thanks

[quartz moon]

Hello I'm connecting to mongodb by means of the following https://mystb.in/StatutesConsciousDaniel.ini and I feel like it is unnecessary since client.conexion points to localhost:27017. How can I substitude the usage of motor so that I'm able to connect to mongodb locally. Thank you very much in advance

[burnt turret]

i don't understand, that connection URI looks almost like something you'd use to connect to the local mongodb server?

you've misspelled the name of the class though, it's motor.motor_asyncio.AsyncIOMotorClient

[quartz moon]

Yeah that's exactly what I want to do. Btw I apologize for the misspelled, but I'm using the correct syntax inside my code

[burnt turret]

right

this really depends on how you've structured your data and if your bot is designed to handle multiple guilds or just a private guild

mongodb supports arrays so you can directly insert a list into it

channels: [channel1, channel2] etc this document can be inserted into a collection of your choice

[burnt turret]

https://docs.mongodb.com/manual/reference/operator/update-array/ @primal cave

You can use the $addToSet operator

Or $push too

[static brook]

does anyone know what session.flush() does in sqlalchemy?

[torn sphinx]

wdym what type

sql

[restive pilot]

^ alright, store the inventory into tables.
E.g
table user holds information about the user, it has a inventory_id that has relationship to the inventory table that holds information about the inventory of the user

[torn sphinx]

Cant it be in arrays of numbers

Only the item ids?

[burnt turret]

Or you could add user_id as a column in the inventory table and have that as foreign key which references the main table

[torn sphinx]

Thats what i do

And since it is already unique it is basically a pk

[burnt turret]

SQL doesn't really have issues with arrays but as I understand it, usually you can just not use an array and instead have separate columns for the items in an array

[autumn epoch]

What is the best way to format this:
<Record balance=451>
To this:
451?
Btw this is asyncpg.

[burnt turret]

That's probably just my own opinion though

[torn sphinx]

I mean

[burnt turret]

Check the pinned message in this channel

[autumn epoch]

Ok

[burnt turret]

The first one, has a link to "record objects"

[torn sphinx]

As long as it can access the content in the array fast

[burnt turret]

I don't know how the speeds compare really

[torn sphinx]

Because columns of items would be a lot

I dont think thats a good idea

[burnt turret]

Querying data will become less intuitive when you use an array though

[torn sphinx]

I know that

But i find it the only way

I really dont know

[restive pilot]

no need to store arrays in databases... SQL databases can handle millions of rows, so for each item in the inventory of a player, store it in the inventory tables, just my way of thinking.

E.g

id | user_id | item_id | qty | damage | stats
----------------------------------------------

[burnt turret]

That's what I think as well

[torn sphinx]

I just thought of that because thats how i also made my currency table

Now i know it works

Thanks

[restive pilot]

@torn sphinx sounds like you need to read up on RDBMS and relationships....

[torn sphinx]

Arent fks relations

[restive pilot]

yeye

[autumn epoch]

Ok got it thx

[torn sphinx]

Same with unique values joins and that?

[celest rain]

hello! how can I make it so my program/bot will automatically delete a row in my sqlite database once it reaches a certain date?

[proven arrow]

Its something you have to manually do on your application side, or in some sort of cron job, since sqlite doesnt have built in functionality for this

[mighty delta]

Hello, I was wondering if anyone could give me some insight on how to approach my problem. So i want to do a sum of all shares traded by each company that are done on a specific stock exchange

SELECT
c.name,
t.shares
FROM trade t
JOIN company c
ON c.stock_id = t.stock_id
WHERE (stock_ex_id) IN (SELECT stock_ex_id 
                                  FROM trade
                                 WHERE stock_ex_id = 3
                                 )
ORDER BY t.shares DESC
;

this is my query right now

current output

so these are all the trades done on the stock exchange #3

but im not sure how to implement a group by company name and do the sum of the shares for each company

[proven arrow]

Why are you doing where in? You can just filter that directly in the where clause

To get sum of shares your on the right track with group by / summing them, so what have you tried so far?

[mighty delta]

yeah that was the initial plan but i assumed maybe where in would be a good idea if i want to apply a sum on the shares

[proven arrow]

where clause does not aggregate data so no

It just checks if the stock_ex_id is returned by the subquery

[mighty delta]

right so the where clauses acts like a filter rather than actually aggregating data

what do you suggest i attempt or look into

also if you have any resources for me to learn more oraclesql please send them my way

[proven arrow]

Nothing more than what you already said. You just need to group by the name, and then sum the share like you already said

[mighty delta]

i wouldnt be able to just do group by just company name correct?

[torn sphinx]

to put data into a table. im inserting like "that' because that's how i saw it on the mysql docs. better to ask them instead of me. also the values im putting into the query as also in the post. cursor.execute(hand_insert (self.hand_number, self.date, self.result, self.hand_number, self.date, self.result))

[proven arrow]

You can yeah

[mighty delta]

would i be applying the sum function on the shares inside the select clause?

[proven arrow]

yes

[mighty delta]

SELECT
c.name,
SUM(t.shares)
FROM trade t
JOIN company c
ON c.stock_id = t.stock_id
GROUP BY c.name
ORDER BY t.shares DESC
;

[burnt turret]

Hey, Lufthansa-Pilot #databases message there was this question here earlier today asking if putting data into an array (it was for an inventory table or something) is a good idea, what do you think is the right way? I recommened to be making columns for each inventory item in a separate inventory table, or will that approach end up being slower/worse?

[mighty delta]

so i tried this and i got "not a GROUP BY expression"

[proven arrow]

Well they were probably showing something specific. Although it is possible to insert like that, thats for inserting values from a table.
Your select query in the insert works like this: if a row matching the values you pass don't exist in the table currently then you will be returned all rows in that table. But if the values do match then you will be returned nothing.
So when you insert 2 things can happen:

1. If values exist - Nothing happens
2. If values don't exist - It tries to insert all the rows into the table again, but obviously fails due to the unique constraint you have

[proven arrow]

I think for oracle you must put all columns you select in the group by.

Either that you need to use aggregate function on all the columns

[mighty delta]

i got group function is not allowed here

 
SELECT
c.name,
SUM(t.shares)
FROM trade t
JOIN company c
ON c.stock_id = t.stock_id
GROUP BY c.name, SUM(t.shares)
ORDER BY t.shares DESC
;```

[proven arrow]

No not sum there

sum only in select

[proven arrow]

Yeah so your recommendation is what should be done i.e. normalise the data

[mighty delta]

okay so i fixed that and added the where clause for stock_ex_id = 3

my current output

[proven arrow]

And it wont be slower (databases are designed to store lots of data). More like faster because makes querying/managing the data also will be easier. Storing data in lists like that defeats the purpose of relational dbs

[burnt turret]

Right, that makes sense. Thanks

[mighty delta]

not sure why it wont group them properly into separate names

[torn sphinx]

I am trying to fix this issue but I read that I must use SELECT instead of VALUES when I am using WHERE NOT EXISTS

[proven arrow]

Yeah but why do you want where not exists in that query. Because of that one of 2 things can happen, which I listed in my previous message

[torn sphinx]

so i dont put in the same data?

[proven arrow]

NOT EXISTS would imply you want the sub query to return 0 rows

[torn sphinx]

if i run the script twice, i input the same data twice

i am trying to preven thtat

[proven arrow]

So then add unique constraints to your columns and just use a standard insert statement without a select

[torn sphinx]

im not sure why i didnt do that in the first place but i probably thought i already had and didn't work out, but it works out now, thanks for being patient with me and helping me out

[half kettle]

I'm really new to SQL so I tried using a tool called SqlDBM and ended up with this script for the schema for my discord.py economy database https://mystb.in/WritersIndicatorActs.sql . However, it looks very messy compared to some similar databases, and some of my friends agree, so I'm assuming SqlDBM hasn't done a very good job at generating it, and I can't find any alternatives. I also tried to test it on a few SQL testing sites and they all resulted in the error BLOB/TEXT column 'name' used in key specification without a key length. But I'm too scared to touch the script myself, even after trying to do some research into that error I got. Would someone mind checking over the resulting script and fixing it up a bit? Or giving me a good pointer as to how to fix it myself? Thanks so much in advance!

I am

[proven arrow]

What about it isn’t a “good job”?

[half kettle]

Well, I suppose inefficient would be the better term, or not the traditional way. For example, this seems to specify unique columns separately, whereas everywhere else I've seen unique used, it's been on the same line as the column's definition

Yes

To clarify though, I am very new to MySQL so I could be completely missing something lol

Here is one of these examples where I've seen unique on the same line as the column https://paste.sr.ht/~vex/84d3be4b80eb7a062651cf2f8acb4046087b9031

ngl, I just went with the one I'd heard of and had seen in search results the most. My assumption is kinda that the more popular it is, the easier it will be to get help with, and being a newbie, I'm definitely gonna need help (like here)

Oh... bruh

Welp guess I'm switching to PostgreSQL then lol

Hopefully SqlDBM has something where I can just convert it, and I don't have to completely start over ;-;

[proven arrow]

It’s perfectly normal to add constraints like unique at the end, it’s how it’s done most of the times

Also allows you to name the constraints this way

[half kettle]

Oh wait I've remembered another reason I'm using MySQL- because it's the only database that my VPS has pre-installed and setup, and I feel like it would be more of a pain to setup a completely new database

Would it be easier to just use a different module to asyncpg?

[proven arrow]

MySQL is fine to use

Aiomysql will be the asynchronous library to use

[half kettle]

MySQL seems more popular (so easier to get help for), is pre-installed on my VPS (so I already have it setup) and is what I've used so far (and I've already spent 4 hours on this lol) so yeh it's probably best I stick with it haha

[half kettle]

Thanks 👍

[half kettle]

I mean, if I'm just naming my unique constraint 'UNIQUE', it seems kinda useless to me, but oh well haha. Either way, I've still got an error that I've not been able to figure out how to fix so SqlDBM has still done something wrong

Alright haha

[proven arrow]

What error

[half kettle]

I also tried to test it on a few SQL testing sites and they all resulted in the error BLOB/TEXT column 'name' used in key specification without a key length. But I'm too scared to touch the script myself, even after trying to do some research into that error I got.

[proven arrow]

Right that’s because your adding an index to it

It can’t index the entire thing

[half kettle]

Ye that's what I found online but... I don't understand what that means 😂 idek what an index is in this case

afaik, all I'm doing is saying it's a tinytext, saying it can't be null and saying that it must be unique within it's column

I probably sound really dumb right now bruh

[proven arrow]

Well in short it means it can’t index it all because it might be too big. That’s because text usually can contain a lot of data, and indexes are not cheap, and come at a cost.

[half kettle]

So some sort of 'index' (still don't know what that is though) is taking up all of the bytes that usually, my actual text would be taking up, and since it's a tinytext, there is very few of those bytes available?

[proven arrow]

However looking at the code you sent a lot of the column types you used need rethinking

[half kettle]

So pretty much, just make it a normal text?

Oh?

[proven arrow]

Generally for things like name and stuff you just use varchar which you can index.
And index in simple terms is a way for the database to sort data in a column, and store in somewhere in a way that allows you to find it very quickly.

[half kettle]

So should I just change all of my text data types to varchar?

[proven arrow]

From looking at your column names yes you can use varchar

[half kettle]

When would I use or not use it, for future reference?

Since as far as I can tell, the only difference between all the text data types is how many characters it is limited to

[proven arrow]

Yes that’s when, you want to store different sizes.

[half kettle]

I assumed that the point of that was that the smaller the max value, the more efficient it was in some way, so to use the smallest one that works for the case

When would you specifically want to limit yourself to more or less characters?

[proven arrow]

As you say when you want better performance

[half kettle]

And surely better performance is always better? 😂

[proven arrow]

Of course.

It also depends what your working on and sometimes you can sacrifice performance.

[half kettle]

Well ye, for my description, I did text since I knew I'd need more characters for that

Oh well, I suppose I'll just change them lol

Now there's another error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL , `description` varchar NOT NULL , `duration` varchar NOT NULL , ' at line 4

[proven arrow]

For varchar you need to specify a length

For example varchar(255)

[half kettle]

Wait, so it's a fixed length?

Or is that the max?

[proven arrow]

No. Char is the fixed length. You can read more about both these types here
https://dev.mysql.com/doc/refman/8.0/en/char.html

[half kettle]

And does that rule still apply where less chars = more performance?

[proven arrow]

Yeah

[half kettle]

Alright 👍

There's no errors 😄

Now to just try and put it into production haha

Am I able to put all of the script in one SQL request or do I have to enter them individually?

[proven arrow]

You can enter into as a single command, as long as each statement ends with a ;

[half kettle]

Alright 👍

Well, when I pasted it, it didn't do the new lines and most of it got cut off ;-; I'll try using a .sql file

I tried mysql -u root -p [my password] economy < /tmp/economy.sql and all it did was spam the console (shown in screenshot). Any ideas why it's not working? And I tried just tmp/economy.sql too to make sure it was using the file, and it said that no file or directory by that name exists so

And I made sure, there are still no tables in the economy database

[turbid edge]

hi

[half kettle]

hi

[turbid edge]

i need some help as showing some direction how to proceed to code

i am a beginner so i really appreciate some guidance

[half kettle]

This doesn't looks like a databases question

I think you're mixing up datasets with databases

[turbid edge]

it seems real easy but since the function is given i cannot bring it

i am a total beginner so i don't know the difference much 🙂

this is about dictionary but since there is given function i cannot predict how to go through

[half kettle]

This looks like more of a general Python question so #❓｜how-to-get-help

[turbid edge]

they have told me to move help channel so i guess i have to return there

[half kettle]

If someone told you to move help channel, you likely asked in one that wasn't available, so once again #❓｜how-to-get-help

[turbid edge]

okay, thank you

[torn sphinx]

Getting error: mysql.connector.errors.DatabaseError: 1265 (01000): Data truncated for column 'PlayerResults' at row 1 with the following:

...
HAND_HISTORY_DB_TABLES['Hands'] = (
    "   `HandResults` float"
HAND_HISTORY_DB_TABLES['Players'] = (
    "   `PlayerResults` float"
...
        try:
            result_insert = "INSERT INTO Players (PlayerResults) VALUES (%s)"
            hand_result_sum = "SELECT SUM(HandResults) FROM Hands"
            cursor.execute(result_insert, (hand_result_sum, ))
...

mysql command for hand_result_sum works in the dashboard but no in my script

[half kettle]

Still not managed to fix this. Anybody got any ideas?

[half kettle]

Would it be 'safe' to try this from Python to get around the above issue? https://mystb.in/AssociationEntInstructional.yaml

I really don't want to break something lol

[solemn root]

This prints the other user's data to the channel if more than one user is in the database. How Do I fix it so each person sees their own cash and not someone esle's? ```
@commands.cooldown(1, 5, commands.cooldowns.BucketType.user)
@bot.command()
async def work(ctx):
#random number Gen
cash_to_add = math.floor(random.random()*100)
#Retrieve discord author message
author = ctx.message.author
#Connect to database
connection = sqlite3.connect('tinker.db')
#create cursor
crsr = connection.cursor()

#if the table is empty insert into it otherwise update
try:
crsr.execute("""INSERT INTO tinker (user_cash, user_token) VALUES (?, ?);""", (cash_to_add, author.id))
except sqlite3.IntegrityError:
crsr.execute("""UPDATE tinker SET user_cash = user_cash + ? WHERE user_token = ?;""", (cash_to_add, author.id))
print("------------------")
connection.commit()
crsr.execute("""SELECT * FROM tinker""")
rows = crsr.fetchall()
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")

connection.close()
await ctx.send(f"Your Wallet: **${row[1]} **\n")

Ex user1 = 7 user2 = 3 user1 sees 3, but actually has 41 user2 = 23

[celest rain]

how can I check for duplicates before inserting a data into a SQLite3 DB?

[restive pilot]

Make the column that you want to avoid duplicates on is unique?

[celest rain]

ive solved the issue hours earlier now, forgot to say that its fixed now sorry

[lament dragon]

I am working with unity with firebase. I got a problem with coroutine when I using it with the firebase code. i.e. I can use coroutine when I am not using the firebase. Here is my code and I was stuck for two days... https://hatebin.com/euvmjrkgeq

[lone adder]

hey i got a sqlite error here is the traceback https://gyazo.com/373c7536a9841e1512004a44dc5757ea

[slender atlas]

you're supposed to use $1, $2, $3 etc. and pass a list of values to paste

not %s

[lone adder]

oh

[slender atlas]

you can also use just ? and make sure the order of insertion and amount of stuff in the list are of the same amount as in the amount of ?

i personally like to use $ everywhere

[lone adder]

so like ("SELECT * FROM user WHERE name = $1", [name])??

[slender atlas]

precisely

ok i just read a bit and it doesn't need to be a list, it can be any iterable

[lone adder]

well you sure that this works for sqlite

you are not talking other things are you

[slender atlas]

ye ye i'm talking about sqlite

aiosqlite, at least.

[lone adder]

@slender atlas well i got a new problem now

https://gyazo.com/35444f670d9ffc4684e213338a27c559

[slender atlas]

literally read the error

you don't have a column named testryid

[wooden garden]

DBJson - Simple Json file database - My First Open Source Project
https://github.com/KetanIP/dbjson

[torn sphinx]

if I do

@bot.command()
async def setup(ctx, message):
    async with bot.db.acquire() as connection:
        async with connection.transaction():
            await connection.execute(
                f'''UPDATE first_table
                SET first_message = {message}'''
            )
            await ctx.send("**All set!**")
```where ``first_table`` is a table and ``first_message`` is a column, then it should update every record in the ``first_message`` to ``message``. Why is it counting ``message`` as the column?

do I do it false?

[proven arrow]

Because your using string format the message is not being treated as a string literal

In other words the value needs to be wrapped in quotes. Best to use a parametrised query for this as it handles it for you.

[torn sphinx]

parametrised query?

[burnt turret]

Check out the first pinned message, has a link for parametrized query using asyncpg

[green oak]

$vib (vibrate)

[half kettle]

Still having this issue 🙁

[fallen vault]

Is there an async connector for mysql?

[half kettle]

aiomysql

[fallen vault]

Thanks

[lost whale]

Heyo guys i have some issues trying to find how to do a live listener to my firebase database using SDK

or should i use pyrebase (and what does it changes)

[lost whale]

ping me if answer please

[torn sphinx]

hi, I'm using sqlite3, I want to select the column called "id" in the "users" table, I'm not sure how to do that

is doing sql = "SELECT id FROM users"?

[quartz heath]

How to compare saved time with current time on mongodb

Suppose I saved a specific time in mongodb and I want to check if that specific time has pass the current time or not

How to compare time in mongodb python

[rain plank]

did you try it?

[torn sphinx]

@burnt turret, Postgres didn't like this:

'SELECT * FROM $1', ok

wait is this a problem?

import sqlalchemy
from sqlalchemy import create_engine
import asyncpg

I deleted it, it wasn't

hi

in asyncpg, how can I modify a value?

Something like this:

MODIFY VALUE column, column2, ... FROM table WHERE column = value

[proven arrow]

Use update statement https://www.w3schools.com/sql/sql_update.asp

[torn sphinx]

(in asyncpg)

[restive pilot]

Not be harsh or anything, but asyncpg is just PostgreSQL with async support.
@proven arrow literally provided with what you need.

Go read / study SQL.

I wonder if people even study these days...(with these simple questions...) programming is something everyone can do, but it takes skill / study to actually write something good and understand what's going on.

[torn sphinx]

lol

not everything is the same

[restive pilot]

What do you mean? As I said, asyncpg is an async library for PostgreSQL, with that you just write SQL statements.. that you execute in async so you don't block the main thread of your code.

[torn sphinx]

It works perfectly, Thanks!

[tulip patio]

Hi all, data storage question here:

Let's say that every 10 seconds we,

1. Fetch the CPU's temperature
2. Load and append to a NumPy array stored in ./my_temps.npy
3. Rewrite my_temps.npy with the new array

What happens to ./my_temps.npy if I lose power during step 3? Would a case where I append to ./my_temps.csv be different than this case with a .npy file?

Should I be using a completely different approach?

Thanks!

[torn sphinx]

hello, I am getting an error which I can't fix. using mysql, I am trying to insert the sum of one table's column to the column of another table. i can get this to work in the mysql workbench, but not ran as a script using python. The following code gives me this error: mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'PlayerName' doesn't have a default value

HAND_HISTORY_DB_TABLES['Hands'] = (
    "CREATE TABLE `Hands` ("
    "   `HandResults` float"
    ") ENGINE=InnoDB")
HAND_HISTORY_DB_TABLES['Players'] = (
    "CREATE TABLE `Players` ("
    "   `PlayerName` varchar(16) PRIMARY KEY,"
    "   `PlayerResults` float"
    ") ENGINE=InnoDB")
...

    def insert_player_results(self):
        """
        Insert Player Results
        :return:
        """
        try:
            cursor.execute("INSERT INTO Players (PlayerResults) SELECT ROUND(SUM(HandResults), 2) FROM Hands")
            sql_connection.commit()
            print("Player Results inserted succesfully")
        except mysql.IntegrityError as err:
            print(f"Error: {err}")
...
    plyr.insert_player_results()
...

[jaunty galleon]

Hi, any recommended websites to know how to make a track messages of member system?

and how do i download it?
pip install pandas
doesn't work

[torn sphinx]

what error are you getting when you pip install pandas

you could try pip install 'pandas<0.21' and see if that works

[half kettle]

for server in self.servers:
  await self.mysql.execute("SELECT * FROM servers where server_id = %s",(server["id"]))
  if await self.mysql.fetchall() == ():
    print("Test")
    await self.mysql.execute("INSERT INTO `servers` (`server_id`) VALUES (%s)",(server["id"],))```I'm able to use SELECT perfectly fine from aiomysql, but INSERT doesn't seem to work. "Test" is printed, but my database isn't edited from the INSERT, nor is there an error. Any ideas why?

[proven arrow]

That’s because your not specifying any data to be inserted for the playername column of your table. So when you leave out a column in an insert it will attempt to use any default values you have provided for that column. But in your create table there is no default value for that column which is why you get the error.

[proven arrow]

Did you commit the changes?

[half kettle]

Uhh how do I do that lol

[proven arrow]

The database connection will have a commit method

[half kettle]

Alrigh, thanks! In trying to find that on their docs, I also found a parameter for making the Connection called autocommit so I'll use that haha

[torn sphinx]

I have changed my code to the following and am getting a wrong syntax error ') FROM Hands WHERE PlayerName = 'PolarFox''

            result_insert = "INSERT INTO Players (PlayerResults) " \
                            "SELECT ROUND(SUM(HandResults), 2)) FROM Hands " \
                            "WHERE PlayerName = %s"
            cursor.execute(result_insert, (self.player_name, ))

I've tried switching the position of the SELECT and WHERE statements and same error

[proven arrow]

Did you read what I said?

[torn sphinx]

yes, the issue was that I wasn't providing a value for the playername

which I believ I am doing now

[proven arrow]

I explained why you get the error yet you don’t fix what I said

[rain plank]

no you aren't

[torn sphinx]

if i knew how to fix it i wouldn't be here lol

ive been stuck on this issue for days

[proven arrow]

Currently your only providing one value to be inserted which is playerresults

[torn sphinx]

yes, because the playername already has a value

[proven arrow]

Where?

[torn sphinx]

so i am trying to find that specific row and insert data

') FROM Hands WHERE PlayerName = 'PolarFox''

[proven arrow]

Insert statement is for inserting new rows

Not for updating existing data

For updating data you want to use the update statement

[torn sphinx]

I need to insert something in teh first place

in order to update

[rain plank]

so why do you have where

[torn sphinx]

to find the row where i am trying to insert the data

because apparently that was my issue was that i was trying to insert data into a one row without specificying which row that was

so i am trying to find where column playername row is equal to "polarfox" and then insert the sum of handresults into playerresults for the corresponding row

[proven arrow]

What your saying is confusing. Are you adding a new row to the table or updating an existing row?

[torn sphinx]

i dont even know anymore, this is getting very confusing for me

i have a column named playerresults that has no values in it

i want to put a value into that row/column

and then from i would have to update since i would finally have a value

[proven arrow]

I don’t see what’s confusing. It’s a simple question, are you updating existing data or not?

[torn sphinx]

yes and no

i dont know mysql

i am trying to learn it

i dont know exactly what "data" means in this case

beacuse there is a row and a column that im trying to insert something into

which means the data of that column and row should be there, but whether it has data in there idk

is the in statement efficient or not to use? does it make the query again for every row in outer query?

[proven arrow]

That would depend if it’s a correlated query or not

If the inner query depends on the outer then yeah

[torn sphinx]

i have a table named hands which has a column handresults, which i want the sum of, and to insert this data into a column which has a row but has no data inserted into it, and from there, because it has data, i would then need to update it

[proven arrow]

Otherwise the db probably will just cache it somewhere

[torn sphinx]

ive given the code that im using

Ok thanks yeah that understand now

[restive pilot]

@torn sphinx
at this point I suggest you read about SQL and learn more about it:
https://www.khanacademy.org/computing/computer-programming/sql

[torn sphinx]

reading up on mysql has gotten to this point

i am stuck

i am literally reading these examples from the doc sites and the tutorial sites

ive been asking the same qeustion here for days now

i have literally copy pasted the code that i am using

i have a table named hands that has a column named handresults which has a bunch of floats, and i want to sum those up, and put them into a column in a specified row in a different table. ive tried insert and update and nothing

[proven arrow]

I guess that is your issue then, copy pasting without understanding. Even yesterday when you posted your query it was a rather complex way of doing it, which you also seemed to have pulled from MySQL website. Maybe try to go over some simple tutorials again.

[torn sphinx]

when i tell you that i copy pasted code, i mean i copy pasted the code HERE

FOR YOU TO SEE WHAT I AM DOING

i am sorry i am not all knowing like you

[proven arrow]

Then maybe I misunderstood what you meant here

[torn sphinx]

sigh

i dont get what is confusing you

from my undrestanding, you were asking why i was styling it the way i did, using backticks

i was very thrown off by your statement about the use of backticks or the way i styled it

[proven arrow]

Well that’s not what I said

[torn sphinx]

this is what im referring to, "why ar eyou inserting like that"

i have no idea what you mean by that question and can only assume that you meant about my styule

at this point, i am simply trying to brute force a solution by throwing everything at the wall

ive read the mysql docs which honestly aren't has helpful as the tutorial sites are

ive sat that and looked over and over and theres something im missing but i cant figure out what that is

im quite frustrated over this as ive been stuck for the past couple days

ive made other projects using mysql but didn't run into this amount of trouble

let me see if i can reword this: i have a table named Players, with two columsn: PlayerName and PlayerResults. PlayerName has one row with the value of "PolarFox" and PlayerResults has is empty

I wan to take data from another table and insert it into the empty column/row

ive tried insert into and also update, both of which have produced a syntax error

[proven arrow]

Right so you want to update some data (value). Which I already asked you before. Show what code you have currently

Insert is for adding new rows.
Update for updating/editing existing rows. The name of the statement says it all.

[torn sphinx]

honestly insert into isn't obvious as you think it is

insert into doesn't immediately come to made as add new

but this is my current code and ill try to ctrl z back to the update version

            result_insert = "INSERT INTO Players (PlayerResults) " \
                            "SELECT ROUND(SUM(HandResults), 2)) FROM Hands " \
                            "WHERE PlayerName = %s"
            cursor.execute(result_insert, (self.player_name, ))

[proven arrow]

Well any Sql guide including the docs do mention it’s for adding new rows. Anyways know you know.

[torn sphinx]

hey, new to sql. If I have a value in table x, and table y references a value from x as a foreign key, if I delete that value in table x, will the one in y also be deleted?

btw the update tutorial im reading is this:
https://www.w3schools.com/sql/sql_update.asp

[proven arrow]

UPDATE Players
SET PlayerResults = (SELECT ROUND (SUM(HandResults), 2) FROM Hands)
WHERE PlayerName = %s

[proven arrow]

It depends how you setup the foreign key