databases

[shrewd frigate]

..

[stiff gale]

if you use %s and (params)
you always need to finish your last param with a ,

[harsh pulsar]

@stiff gale that isn't true

you're hung up on the syntax

[grim zephyr]

still the same error Traceback (most recent call last): File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke await ctx.command.invoke(ctx) File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke await injected(*ctx.args, **ctx.kwargs) File "C:\Users\USER\AppData\Local\Programs\Python\Python39\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: ValueError: parameters are of unsupported type

[harsh pulsar]

the trailing , is only required for a length-1 tuple, it's an annoying ugly special case

[stiff gale]

in my experience i need to use it everywhere otherwise itll just not work

[grim zephyr]

ok i understood

[shrewd frigate]

Can someone explain to me how to list warns in a database. Basically the first warn a give a member it the warn it stays at.
if that makes sense.

[stiff gale]

that was just a general reccommendation. It doesnt rlly affect the issue you are having

[harsh pulsar]

that isn't specific enough, you need to be specific about what your code is supposed to do and why it looks like it isn't working

[grim zephyr]

well his way helped me

[harsh pulsar]

await cursor.execute("INSERT INTO warn(warn_num) VALUES (?)", (str(1)))

@grim zephyr why str()? seems like you should be storing numbers, not text

[grim zephyr]

i already changed it

[shrewd frigate]

Ok. My code is supposed to warn someone then upload the amount of warns and reasons. What happens is when I warn someone and give a reason say "Test" is stays as that even if I warn them 2-3 times again. I want to list all the reasons so it would look like:

GuildID: ...
MemberID: ...
Warns: 3
Reasons: Test, Test2, Test3 or in a list so

Test1
Test2
Test3. I don't mind I just need it to list reasons.

[harsh pulsar]

ok, that is more helpful. can you also show your code?

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[harsh pulsar]

please don't post a screenshot

[shrewd frigate]

    async def warn(self, ctx, user:discord.Member=None, *, reason="No reason specified"):
        id = user.id
        gid = ctx.guild.id
        text = reason
        if collection.count_documents({"guildid":gid}) == 0:
            collection.insert_one({"memberid": id, "warns": 0, "guildid": gid, "reason": reason})

        if user == ctx.author:
            await ctx.send("You cannot warn yourself!")
        elif user == None:
            return await ctx.send("Please mention a user")

        warn_count = collection.find_one({"guildid":gid})

        count = warn_count["warns"]
        new_count = count +1

        collection.update_one({"guildid":gid},{"$set":{"warns": new_count}})

        em = discord.Embed(title="Member Warned!", description=f"{user.mention} was warned. {new_count}", color=0x8EE5EE)
        em.add_field(name="Moderator", value=f"{ctx.message.author}")
        em.add_field(name="Reason", value=f"{reason}")
        await ctx.send(embed=em)

[harsh pulsar]

side note, in the if user == ctx.author block, you probably meant to write return await

otherwise, that code does look like it should work

[grim zephyr]

@harsh pulsar

can u help me a little more

[harsh pulsar]

@shrewd frigate it looks like you're mixing up guild id and member id

or you forgot to also update based on guild id?

[uneven stream]

o

[shrewd frigate]

It doesn’t work otherwise

[uneven stream]

thanks!

[shrewd frigate]

That’s intentional

[harsh pulsar]

why? you want to warn all guild members?

[shrewd frigate]

No

[harsh pulsar]

i would have imagined it should look more like this

    @commands.command()
    async def warn(
        self,
        ctx: Context,
        user: Optional[discord.Member] = None,
        *,
        reason: str = "No reason specified"
    ) -> None:
        mid = user.id
        gid = ctx.guild.id
        text = reason

       if user == ctx.author:
            return await ctx.send("You cannot warn yourself!")
        if user == None:
            return await ctx.send("Please mention a user")

        if collection.count_documents({"guildid": gid, "memberid": mid}) == 0:
            collection.insert_one(
                {"memberid": mid, "warns": 0, "guildid": gid, "reason": reason}
            )

        warn_count = collection.find_one({"guildid": gid, "memberid": mid})
        count = warn_count["warns"]
        new_count = count + 1
        collection.update_one(
            {"guildid": gid, "memberid": mid},
            {"$set": {"warns": new_count}}
        )

        em = discord.Embed(
            title="Member Warned!",
            description=f"{user.mention} was warned. {new_count}",
            color=0x8EE5EE
        )
        em.add_field(name="Moderator", value=f"{ctx.message.author}")
        em.add_field(name="Reason", value=f"{reason}")
        await ctx.send(embed=em)

[shrewd frigate]

Like I said. The code works fine I just need to list the reasons for the warns.

[harsh pulsar]

well the "reason" here looks like it's just a string

so you need to store an array, and $push new reasons onto the array

[grim zephyr]

@harsh pulsar do you know a way i can get the value of warn_num inside a var from a db

[harsh pulsar]

        if collection.count_documents({"guildid": gid, "memberid": mid}) == 0:
            collection.insert_one(
                {"memberid": mid, "warns": 0, "guildid": gid, "reason": []}
            )

        warn_count = collection.find_one({"guildid": gid, "memberid": mid})
        count = warn_count["warns"]
        new_count = count + 1

        collection.update_one(
            {"guildid": gid, "memberid": mid},
            {"$set": {"warns": new_count}, "$push": {"reason": reason}}
        )

like this, maybe

[grim zephyr]

i need help

[harsh pulsar]

can you be more specific?

[grim zephyr]

a table in which a row name guild_id and a row name user_id is there,
now i want to store the int of a user id inside a var
but how

[harsh pulsar]

i think you mean column, not row

[grim zephyr]

oops maybe

[harsh pulsar]

you want to get the user id out of the database, and store it in python?

[grim zephyr]

yes

but how

[grim zephyr]

is there any way i can do this

[harsh pulsar]

cursor = await db.execute('SELECT DISTINCT user_id FROM warn')
all_user_ids = [row[0] for row in await cursor.fetchall()]

related reading:
https://aiosqlite.omnilib.dev/en/stable/index.html
https://sqlite.org/lang_select.html
https://sqlbolt.com/lesson/select_queries_introduction
https://www.python.org/dev/peps/pep-0249/
https://docs.python.org/3/library/sqlite3.html

[grim zephyr]

why row[0]

[harsh pulsar]

the row will be a tuple, in this case a length-1 tuple because we only asked for 1 column

so we need to get the first element (in this case, the only element) of each row

[grim zephyr]

what in case of multiple

[harsh pulsar]

you can keep the data as a tuple

it depends on what you need to do

[slender atlas]

well... you are querying only user_ids

[harsh pulsar]

cursor = await db.execute('SELECT user_id, warn_num FROM warn')
rows = await cursor.fetchall()

each element of rows will be a length-2 tuple here

is the user_id the primary key of your table?

[grim zephyr]

no

[harsh pulsar]

what is the primary key?

[grim zephyr]

@harsh pulsar

    await cursor.execute("""CREATE TABLE IF NOT EXISTS warn(guild_id STR, user_ID STR, warn_num INT)""")

[harsh pulsar]

i see

so guild_id and user_id should uniquely identify a row in the table?

[grim zephyr]

i want to get the warn_num using guild_id and user_id

and store it in a var

but unable to figure out a way

[slender atlas]

oh wait i misread it

[harsh pulsar]

cursor = await db.execute(
    'SELECT warn_num FROM warn WHERE user_id = ? AND guild_id = ?',
    (user_id, guild_id)
)
result_row = await cursor.fetchone()
warn_num = result_row[0]

[grim zephyr]

i see

[harsh pulsar]

i also recommend setting guild_id and user_id to be a "composite primary key"

    await cursor.execute("""CREATE TABLE IF NOT EXISTS
      warn (
          guild_id STR,
          user_ID STR,
          warn_num INT,
          PRIMARY KEY (guild_id, user_id)
    )""")

[next sun]

I would like to load a csv into an sql file

[harsh pulsar]

that way the database ensures that every (guild_id, user_id) pair is unique, and queries will be faster

[next sun]

the sql file will look something like the following:

[grim zephyr]

ok

[next sun]

INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (868,'Radiatus_v3','WeatherSafeColumbia868');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (869,'Radiatus_v3','WeatherSafeColumbia869');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (870,'Radiatus_v3','WeatherSafeColumbia870');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (871,'Radiatus_v3','WeatherSafeColumbia871');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (872,'Radiatus_v3','WeatherSafeColumbia872');```

[gentle roost]

Am I misinterpreting or does that mean you can't have a duplicate entry with the same guild and user id?

[next sun]

the problem is that my string

sql_prefixo = f"""INSERT INTO 'WeatherData' (locationid,dataprovider) VALUES(CAST({dict_data['locationid']} AS DECIMAL),{dict_data['dataprovider']})"""

[harsh pulsar]

right, if you have a composite primary key on (guild_id, user_id), then you can't have two different rows with (guild_id=123, user_id=456)

[harsh pulsar]

don't use f-strings for sql

use query parameters

[gentle roost]

Having trouble connecting to sqlite db. The error I get is OperationalError: no such table: punishments. I assume it's because it's not conencting to the db rather than the table not being there. This is how I open it. Is this correct?sqlite3.connect(r'C:\Users\X\Documents\In The Loop\itl_db.db')

[harsh pulsar]

you can check if the db file is set up correctly with https://sqlitebrowser.org/

it's a great tool

[gentle roost]

will do

[next sun]

but that is the thing

[gentle roost]

Oh yes I'm using that and the table is there

[harsh pulsar]

that said, maybe you thought you wrote to the db but the table never got saved? you might need to "commit" your changes. show your full code and the full error output

[next sun]

look at this:

    with open(os.path.dirname(file_path) + '/weatherdata.csv', 'r') as f:
            sentence_list = f.readline().strip().split(',')
            arr_dicts = []
            reader = csv.reader(f)
            next(reader) # Skip the header row.

            for row in reader:
                dict_data = {}
                for index, val in enumerate(row):
                    dict_data[sentence_list[index]] = val

                arr_dicts.append(dict_data)
                sql_prefixo = f"""INSERT INTO 'WeatherData' (locationid,dataprovider) VALUES(CAST({dict_data['locationid']} AS DECIMAL),{dict_data['dataprovider']})"""```

I have a dictionary for every row

and I don't know how I would used the paramaterized this way

[harsh pulsar]

show how you do the insert

[royal rapids]

lol weatherdatas?

[next sun]

aaaha

ingnore the names

ignore the names

man

I am not inserting anything

[harsh pulsar]

what database are you using? and what python library are you using to connect?

[next sun]

I want the output to be an sql file

[harsh pulsar]

oh... you are just generating a bunch of INSERTs?

[next sun]

like weather.sql

[harsh pulsar]

i see

[next sun]

with all the inserts there

[desert sandal]

mydb = mysql.connector.connect(
  host="bh002.bluefoxhost.com",
  user="USERNAME",
  password="PASSWORD",
  database="s333_PlayersDB"
)

@client.command(name="search")
async def search(ctx, player):
    mycursor = mydb.cursor()
    sql = "SELECT * FROM TABLE 1 WHERE Name Like %s"
    val = (f"%{player}%",)
    mycursor.execute(sql, val)
    myresult = mycursor.fetchall()
    for player in myresult:
        await ctx.send(f"Name: `{player[0]}` Rating: `{player[1]}` Position: `{player[2]}` Version: `{player[3]}` Base Stats : `{player[4]}`")```
`Command raised an exception: ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TABLE 1 WHERE Name Like '%xyz%'' at line 1` help me fix this please

[harsh pulsar]

what data types are location_id and dataprovider?

[next sun]

so that I can pass into the command line psql and update the database

[harsh pulsar]

you could also use psycopg2 to do it from python...

[gentle roost]

    con = sqlite3.connect(r'C:\Users\X\Documents\In The Loop\itl_db.db')
    cursor = con.cursor()
    cursor.execute("SELECT * FROM punishments")

Traceback (most recent call last):
  File "C:\Users\X\Documents\In The Loop\venv\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\X\Documents\In The Loop\cogs\admin.py", line 251, in db
    await self.log_punish()
  File "C:\Users\X\Documents\In The Loop\cogs\admin.py", line 69, in log_punish
    cursor.execute("SELECT * FROM punishments")
sqlite3.OperationalError: no such table: punishments

I threw in some random data into the table so it's not empty

It's saved

[next sun]

integer and varchar()

[harsh pulsar]

in python, not in sql

[harsh pulsar]

did you spell the filename right? does it work if you connect with the sqlite3 command line?

[next sun]

what do you mean?

[shrewd frigate]

NameError: name 'mid' is not defined

[next sun]

I am very confused

[harsh pulsar]

i showed you more of the code above, i used mid instead of id for the member id

[harsh pulsar]

in the csv data, locationid is supposed to be a number? and dataprovider is text?

[gentle roost]

Yes the filename is right. I tried to use sqlite3 from cmd earlier but it's not recognised. Do I need to install something beforehand for that?

[next sun]

yes mate

INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (868,'Radiatus_v3','WeatherSafeColumbia868');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (869,'Radiatus_v3','WeatherSafeColumbia869');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (870,'Radiatus_v3','WeatherSafeColumbia870');
            INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (871,'Radiatus_v3','WeatherSafeColumbia871');```

[harsh pulsar]

i see 2 obvious problems

1. you used the wrong quotes around 'WeatherData'

[next sun]

the sql file should have something like that, right?

[harsh pulsar]

2. you forgot to quote your string data

sql_prefixo = f"""INSERT INTO "WeatherData" (locationid,dataprovider) VALUES(CAST({dict_data['locationid']} AS DECIMAL), '{dict_data['dataprovider']}')"""

assuming you don't have any 's or other malicious text in your csv, that might work

[next sun]

sure

but if I were to use paramaterized substitutions

how could it have been done?

[gentle roost]

I just checked now with the cli, the table exists

hm I'll open a help channel

[stray stirrup]

Hi, I'm getting an error and I'm not sure where I went wrong. This is the code:

def make_list(query, thelist, key): # format list so not dict
    for i in query:
        thelist.append(i[key])
    return thelist

# create list of data for each country
    for x in ['Confirmed', 'Recovered', 'Deaths']:
        find = db.execute("SELECT ? FROM countries WHERE Country = ?", x, sys.argv[i])
        print(find)
        data = []
        data = make_list(find, data, x)```
`print(find)` prints this: 
```[{"'Confirmed'": 'Confirmed'}, {"'Confirmed'": 'Confirmed'}, {"'Confirmed'": 'Confirmed'}, {"'Confirmed'": 'Confirmed'}]```

And this is the error message:
```thelist.append(i[key])
KeyError: 'Confirmed'```

[split basalt]

I am trying to connect to mysql using mysql.connector in python
I get this error:

  File "main.py", line 60, in <module>
    database="mysql"
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/abstracts.py", line 716, in connect
    self._open_connection()
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 210, in _open_connection
    self._ssl)
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 144, in _do_auth
    self._auth_switch_request(username, password)
  File "/home/sriram/.local/lib/python3.7/site-packages/mysql/connector/connection.py", line 177, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'```
My python code is:
```py
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="mysql"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)```
Please help me
PS: It works as expected in windows and in my friend's linux

[stiff gale]

that means that the user credentials are incorrect

the code is not wrong itself

[split basalt]

Well thats what I thought. But the credentials are correct

[stiff gale]

it found the database so thats not the issue

or the database server i should say

hmm

i could be wrong but mabye python doesnt like that you are trying to use root

[split basalt]

I know the credentials are correct bcse I just got into maria fromt terminal now

[stiff gale]

yeah

[split basalt]

Like it doesnt allow it cos it req sudo?

[stiff gale]

and the password or username doesnt have any " in the password i assume

[split basalt]

Nope

[stiff gale]

it is a security move some developers make

because using root is dangerous (in a production enviroment)

but its annoying for testing

[split basalt]

How do I solve this problem

Man I first uninstalled and reinstalled maria cos a friend suggested I had given remote access or something as No

[stiff gale]

uhm i am trying to figure out if what i think is the case is correct but if it is the fact of using root you would have to create a new account for the database and assign the right permissions to that account

mysql can be a bit of a bitch about quotes sometimes so using a config file could also work

[split basalt]

I think root has all the permissions

[stray stirrup]

could someone please help?

[stiff gale]

thats correct but thats why some programs block you from using it. But according to the internet that is not the issue

could be a number of issues lol

[split basalt]

Do you think it would work if I create a new user?

[stiff gale]

mabye, but that would suggest root is being blocked which i dont think is the case

[split basalt]

Ok one more thing

Since maria is being a bitch, is there any other quick alternatives that would actually work?

This thing, I gotta finish it fast

[stiff gale]

for databases? You could use sqlite

it uses a file

From grepper:

import sqlite3

# Create database
conn = sqlite3.connect('tablename.db')
c = conn.cursor()
c.execute('''CREATE TABLE tablename(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, number REAL)''')
conn.commit()
conn.close()

# Insert Values
conn = sqlite3.connect('tablename.db')
c = conn.cursor()
c.execute("INSERT INTO tablename VALUES (?, ?)", (name, number))
conn.commit()
conn.close()

# Read Values
conn = sqlite3.connect('tablename.db')
c = conn.cursor()

for row in c.execute('SELECT * FROM tablename'):
    print(row)

number = [row[2] for row in c.execute('SELECT * FROM tablename')]
conn.close()

doesnt use credentials just a .db file

and is basically the same as mysql

[split basalt]

How do I start with it?

Should I apt install it?

[pure sleet]

you can only access a list using numerical keys, or indexes rather

[stiff gale]

pip install sqlite3

you dont need a server for this

[pure sleet]

you're using words to try and access a list

[stiff gale]

you can just do pip install sqlite3 and then use the code examples i sent to write the code

[stray stirrup]

it's a dictionary

[stiff gale]

its basically the same as mysql but instead of connecting to a server you use a file

[pure sleet]

  data = []
        data = make_list(find, data, x)

data is not a dictionary here, and it's what you are passing to make_list as thelist

[split basalt]

thank you soo much man @stiff gale

[pure sleet]

and x is a key, which is a string

hence the error you are getting

[stray stirrup]

for i in query is indexing through a list of dictionaries and then i[key] is the key in one of those dictionaries. The function works here which is why I don't think it's something with the list. #help-cake

datesDict = db.execute("SELECT Date FROM countries WHERE Country = 'Afghanistan'")
dates = []
dates = make_list(datesDict, dates, 'Date')```

[pure sleet]

let me show you what you are doing, maybe you'll understand

[pure sleet]

yea, nvm, i see where i was wrong. anyways,if you look at your find results, it looks like {" 'Confirmed' ": "confirmed"} if you look closely, the key is 'Confirmed' and using Confirmed is not the same key, your key is surrounded by single quotes

[harsh pulsar]

i've actually looked into this before, i think it depends on the library but in most cases it's not supported, annoyingly and unfortunately

[stray stirrup]

Yep, someone realized I could just do SELECT Confirmed, Recovered, Deaths and it all worked. Thanks!

[stiff gale]

does anyone know why when i interact with my mysql database from the console (ssh) it doesnt complete the query and instead times out?

select queries work but anything like ALTER INSERT UPDATE or whatever else doesnt work

its kind of annoying to have to use python to interact with my mysql database at all times

except for simple select queries

[thorny field]

Hello! So I have an SQL db. To browse it I use SQL db browser. Is it possible to see the changes to happen in real time in while the app is open and when the code is running and using the db?

[faint blade]

No I don't think so

You'll have to press the refresh button to see changes

[thorny field]

k, thx!

[astral pecan]

Is anyone familiar with AWS Timestream? Is there an equivalent sql query I can do for timestream's BIN()?

[torn sphinx]

with sqlite3, How do I make multiple tables? py @client.event async def on_ready(): await client.change_presence(status=discord.Status.online, activity=discord.Game('your mom')) db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute(''' CREATE TABLE IF NOT EXISTS main( money INTEGER, user_id INTEGER ) CREATE TABLE IF NOT EXISTS inventory( fish INTEGER, chicken INTEGER, rare_fish INTEGER ) ''') print("Bot has been launched") ^ This is my code

Ignoring exception in on_ready
Traceback (most recent call last):
  File "C:\Users\Kaden\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\Kaden\Documents\Bot\home.py", line 13, in on_ready
    cursor.execute('''
sqlite3.OperationalError: near "CREATE": syntax error``` ^ This is my error

[proven arrow]

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript

You can use that function

[torn sphinx]

thanks

I got help but if it's every a problem I'll use that

appreicate it

[late scaffold]

@torn sphinx I have a video from a firend where it goes over SQLite3 and I found it really good

[steep ingot]

What’s the best free database I can use, simple excel style thing. It’s going to be accessed by a discord bot running on heroku. It does not need to have a heroku integration but if there’s a free one that helps of course. Thanks.

[storm mauve]

sqlite is the most simple database I'd advise running, but from what I've heard heroku has a fairly simple PostgreSQL integration

Databases may feel very different from "Excel style" though - you're still working with Tables organised in Rows and Columns, but the way you interact with them is somewhat different

[steep ingot]

Are Sqlite and postgresql the same thing?

[storm mauve]

no, but both use SQL queries

[steep ingot]

Yeah I kinda meant like a 2d array, rather than some complex supper ott thing.

[storm mauve]

they're not too complex to work with but setting up Postgres on my local machine was not a fun experience

take a look at https://www.heroku.com/postgres

[steep ingot]

XD

Well what would be the easiest way to learn if I have a fair bit of experience using pandas and csv files? I kinda just want to be lazy and swap the line in my code from save to csv to update database. Is that possible?

[storm mauve]

not really, you must setup a bunch of stuff (well, a little) instead of only swapping one line

[austere portal]

but setting up Postgres on my local machine was not a fun experience
lol

[storm mauve]

SQL workflow usually looks like:```sql
--setup
CREATE TABLE IF NOT EXISTS example(
id INT PRIMARY KEY,
name VARCHAR(20)
)
--insert
INSERT INTO
example (id, name)
VALUES -- the way you insert may vary a little
(0, "Foo")
(1, "Bar")
--query
SELECT id, name FROM example
--optionally filter
WHERE example.id = 0

[steep ingot]

Cool, would I still be able to use pandas and all of its stuff?

[storm mauve]

(of course, you only create once and do not insert duplicate records)

Yes, pandas has a few sql commands

[steep ingot]

Oh nice, so they’re well integrated and I should be able to find some YouTube vids to get me started? (Inevitably followed by me cocking something and coming back here or stack XD)

[storm mauve]

I wouldn't say very integrated, but sure

[steep ingot]

Well enough that it’s not something I’d have to spend weeks reading documentation and trial and erroring.

[storm mauve]

Yeah, you can easily learn how to use SQL in a few days.
You might want to insert through a cursor instead of df.to_sql, but pd.read_sql should work I believe

[steep ingot]

And if I want to just like add to a value, like +1 for col c row 6?

Basically my df will look like James, 1,4,5,6,3,7,5 lots of text in final col

Then the next row will be another name and so on

[frail heath]

hello i would like to create a leader-board system using mongodb (i have a value on each document to compare) for a discord bot is there a simple way someone would recommend?

[steep ingot]

What do you mean by leader board? Just like joe: 10, Tom:21, ted:15? You could use a csv for that, it’d be easier.

[storm mauve]

usually to insert values you'll have a list of tuples, in which each tuple's values corresponds to the columns

[steep ingot]

And pretty much no matter what you end up you’ll need pandas for it to be efficient.

[frail heath]

be able to list top 10 ppl with the highest value of a certain value

[storm mauve]

if you are running it locally I would advise using sqlite3 instead of mongodb

[frail heath]

not locally

[steep ingot]

So I’d do copy df, search df, save cell location, save cell new value. Call cell update and pass location and value?

[steep ingot]

Keeping it in an order in the db is unnecessary. You can have it random order and then whenever your bot prints you sort it.

[steep ingot]

And complicated.

[frail heath]

im not trying to keep it in order i just want to be able to list top 10 of certain value when i want i to

[storm mauve]

you can update records as update x set y = z where a = b

[steep ingot]

Yeah you can do that, I think numpy has a way to sort a list so you pull the whole list from the db, numpy sorts it and then you print the first 10 items.

[steep ingot]

Sorry, I don’t understand. Could you explain it differently?

[nova glen]

If you're trying to avoid iterating over 1000 users to find the top 10 on top of a non-relational store like Mongo my suggestion is to think about what extra work you can do on your writes, eg. events that could change a person's position on the leader board.

[storm mauve]

kinda busy rn sorry

[steep ingot]

Ok, well thanks for all of your help so far! You’ve saved me a hell of a lot of research.

[austere portal]

filter the data

!d filter

[delicate field]

filter

filter(function, iterable)```
Construct an iterator from those elements of *iterable* for which *function* returns true. *iterable* may be either a sequence, a container which supports iteration, or an iterator. If *function* is `None`, the identity function is assumed, that is, all elements of *iterable* that are false are removed.

Note that `filter(function, iterable)` is equivalent to the generator expression `(item for item in iterable if function(item))` if function is not `None` and `(item for item in iterable if item)` if function is `None`.

See [`itertools.filterfalse()`](https://docs.python.org/3/library/itertools.html#itertools.filterfalse "itertools.filterfalse") for the complementary function that returns elements of *iterable* for which *function* returns false.

Similar names: label.filter, 2to3fixer.filter, django.filter

[ebon skiff]

Like jlewallen said iterating over 1000 users is not really the best option

Filter is faster but not as fast as their method, but tbh at this point i'd just suggest postgre 😆

[frail heath]

hmm ok thanks ill look into it

[austere portal]

yeah

[austere portal]

but built-in's are pretty fast

[frail heath]

wait whats filter

[austere portal]

This

[ebon skiff]

filter is a build in function inside python that is C based so a lot faster than a for loop or sum

[bright dune]

1000 values is a pretty small amount of values to loop through. you'll barely notice it

[nova glen]

The slow part here is getting the data from Mongo.

[frail heath]

yea i mean like how does it benefit me? i just get a list of all values and use that to order it?

[nova glen]

I made up 1000 cause that's a lot of blobs to just yank over the wire.

[ebon skiff]

Well it's about servers so it can get pretty high.

[nova glen]

It's also O(N) for your users.

[austere portal]

yeah

[torn sphinx]

send it

[bright dune]

like a discord bot? filtering 100k members is probably still faster than making the web request

[late scaffold]

Ok I will dm you it

[torn sphinx]

k

[ebon skiff]

Let's do a battle, postgre vs iterating.

[austere portal]

!d sorted or use this

[delicate field]

sorted

sorted(iterable, *, key=None, reverse=False)```
Return a new sorted list from the items in *iterable*.

Has two optional arguments which must be specified as keyword arguments.

*key* specifies a function of one argument that is used to extract a comparison key from each element in *iterable* (for example, `key=str.lower`). The default value is `None` (compare the elements directly).

*reverse* is a boolean value. If set to `True`, then the list elements are sorted as if each comparison were reversed.

Use [`functools.cmp_to_key()`](https://docs.python.org/3/library/functools.html#functools.cmp_to_key "functools.cmp_to_key") to convert an old-style *cmp* function to a *key* function.

[ebon skiff]

Does sorted exclude items?

[frail heath]

ill look into it thanks never used databases before so im still learning lol

[austere portal]

Sort it and get the first 10

[ebon skiff]

I think this will be slower than filter.

[frail heath]

how could i keep the link between the value and the document/another field such as user id?

[austere portal]

then use a sql db 😄 and run a sql query

[frail heath]

    """Display boards for scoreboard and leaderboard commands."""
    rank_number = 1
    async for entry in WorldRecords.find(query).sort("record", 1).limit(10):
        # Do stuff for first record entry . .
        rank_number += 1```
i found this online would i be able to use something like this?

[austere portal]

You will need to do something like this py data = ... # get the data from the db sorted_data = sorted(data.items(), key=lambda i: i[x]) first_ten = list(sorted_data)[:10] x will be the value you want to filter the data by

[frail heath]

hmm thanks ill try and get that to work

[austere portal]

this method might get a bit slow if you have a lot of data

[burnt turret]

This is definitely the way to go btw, you shouldn't have to sort and limit with python (as in you shouldn't have to do what's done in the example right above this)

[upper basin]

how do i insert values into an array data type with asyncpg

create table if not exists userdetails (guildid bigint, userid bigint, activebackground text, backgrounds text[] , featuredchar text, unique(userid, guildid))

user = await self.bot.primedb.fetch("INSERT INTO userdetails (userid, guildid, activebackground, backgrounds, featuredchar) VALUES ($1, $2, $3, $4, $5) RETURNING *",ctx.author.id, ctx.guild.id, 'wormhole','{"wormhole",}','unset')

is giving me a a sized iterable container expected (got type 'str')) error

🤨 do i just remove the surrounding brackets

because in the docs while inserting theyve surrounded the array with brackets

[austere portal]

You need to pass in an interable

[upper basin]

ah got it, thanks

another question,

does it have to have {} brackets

or can i pass in a list

[jaunty galleon]

How can I store a bytes or bytes-like object in PostgreSQL asyncpg?

[austere portal]

i dont think it has to

[austere portal]

Use the BYTEA data type

[upper basin]

docs says i can ARRAY[10000, 10000, 10000, 10000], but idk if i can do the same thing for asyncpg

[austere portal]

send the link

[upper basin]

https://www.postgresql.org/docs/9.5/arrays.html

[jaunty galleon]

And than I can get it back and await res.read()?

[austere portal]

You can get it back, but what is res?

[austere portal]

ah, I guess asyncpg will do the conversion
https://github.com/MagicStack/asyncpg/issues/237

[jaunty galleon]

The response

[austere portal]

You can save the http response in binary (BYTEA)

And get it from the db

[jaunty perch]

anyone here worked with redis and python?

[jaunty galleon]

How do I UPDATE two columns?

UPDATE guilds_config SET logs_channel, webook_url = ($1, $2) WHERE guild_id = ($3)``` i that valid/

[austere portal]

Two columns at once?

[jaunty perch]

the comma

[grim vault]

One after another:

UPDATE guilds_config SET logs_channel = $1, webook_url = $2 WHERE guild_id = $3```

[jaunty galleon]

Oh ok

Thanks

[grim zephyr]

i need help

[grim vault]

Don't we all.

[jaunty perch]

^

[grim zephyr]

@burnt turret can i get some help

[burnt turret]

just ask your question, and whoever can answer will help you

[grim zephyr]

my db is locked but why

[grim zephyr]

my db is locked but why

it is not supposed to be locked

[burnt turret]

that doesn't give us much info. frame that question with more details like what database, when this happens etc.
https://pythondiscord.com/pages/guides/pydis-guides/asking-good-questions/

also you dont have to keep pinging me

just ask your question and wait for an answer

[grim zephyr]

ok

[grim zephyr]

@austere portal do u know how to delete multiple values from a db using a single line

await cursor.execute("DELETE guild_id AND user_ID AND warn_num FROM warn WHERE guild_id = ? AND user_ID = ?",(ctx.guild.id, member.id))
                await db.commit()```

[austere portal]

DELETE FROM table_name WHERE condition;

[grim zephyr]

syntax error

[grim zephyr]

this once

[grim zephyr]

@austere portal this one

giving me syntax error

[austere portal]

The indentation looks wrong

[grim zephyr]

its not

[austere portal]

ok

[grim zephyr]

is it correct

??

[austere portal]

The indentation looks wrong, try doing py await cursor.execute("DELETE guild_id AND user_ID AND warn_num FROM warn WHERE guild_id = ? AND user_ID = ?",(ctx.guild.id, member.id)) await db.commit()

[grim zephyr]

ok

this but why

[grim zephyr]

Command raised an exception: OperationalError: near "guild_id": syntax error

[austere portal]

ah, mb

Change the query to DELETE FROM warn WHERE guild_id = ? AND user_ID = ?

[grim zephyr]

but what will it do

will it delete the whole line

or am i supposed to give it the values

[austere portal]

It will delete the whole row.

[grim zephyr]

ok it should work

[torn sphinx]

How would i go about adding simple appointment booking functionality? What should db structure look like?
Each order will have a appointment. Two orders cant have the same appointment. I also need to query available appointments or booked appointments.

[proven arrow]

There’s many ways of doing it. It’s a matter of picking one that suits your business logic. For a simple one, you can achieve it with a TimeSlots table, and Appointments table.
TimeSlots - Stores all available times which can be booked for an appointment.
Appointments - Store details of the appointment. You can reference it to the TimeSlots table with a foreign key if you want.

How you define the relationships and constraints will depend entirely on how your business logic is. Like I’m not sure or you didn’t make clear enough if for example an order can have multiple appointments, or if appointments can be cancelled/rebooked etc. But the above tables are flexible enough to allow the different relationship types.

[golden aurora]

what's an efficient way of storing lists of data per user, such as a list of roles someone holds on a discord server?

I currently just use a json file, but I doubt that's good for this

would it be okay to create tables for each user, which has the list of roles?

[grim vault]

You would have one table for users, one table for roles and one table for user_roles.

That way a user can have multiple roles, it's called an n:m relation.

[golden aurora]

n:m is many to many?

[grim vault]

Yes.

[golden aurora]

thanks, I think that should help me get started

[faint blade]

You have the user table:
ID - Name - Discriminator
1234567890 - Bluenix - 7543

Then you have the users and roles table:
UserID - RoleID
1234567890 - 987654321

What @grim vault explained is that you also have a roles table:
RoleID - Name - Permissions
987654321 - Helper - 548

One role can have many users, and one user can have many rows

I don't think that's necessary though, do you need to keep track of roles' names and permissions (or other values)?

@golden aurora ^

[grim vault]

Yes, I spoke in a more general way. If you already have IDs but don't need to store any additional data one table (user_roles) would be enough.

For discord wouldn't you need a GuildID (server) too?

[golden aurora]

thanks that makes sense

[golden aurora]

only need for one server 🙂

[faint blade]

In what context? Discord bot?

[golden aurora]

not really necessary to store the name or discriminator either? can just simply store
DiscordID - RoleId?

[faint blade]

Yup, if you only need to map user IDs to role IDs then that is fine.

[golden aurora]

thanks for the help

[faint blade]

There's a sqlite3 module in Python that can do this

[grim vault]

I'm using the DB Browser for SQLite to just browse a sqlite database, I think there is also a linux version https://github.com/sqlitebrowser/sqlitebrowser

[jade reef]

so if i have an ugly json file like this, how can i export it to a postgres table which has 2 columns (item and price) heres the code if u feel something can be improved in the function itself https://hst.sh/usahiresoy.py

[storm mauve]

first create the table, with the fields item (text or varchar(...) or whatever you feel like using) and price (looks like integer should suffice), then prepare a list of tuples and insert the values using psycopg2.extras.execute_values, assuming you're using psycopg2

[jade reef]

actually im using asyncpg (discord.py)

and yeah i have a table with text and int colums

[storm mauve]

I don't have much experience with that specific one, but their cursor probably has an execute_many method
edit; just connection.executemany it seems

from their API reference: ```py

await con.executemany('''
... INSERT INTO mytab (a) VALUES ($1, $2, $3);
... ''', [(1, 2, 3), (4, 5, 6)])

[frail heath]

    MongoUserData = MongoData["UserData"]
    rank_number = 1
    async for entry in MongoUserData.find({ "MemberId": { $exists: True }}).sort("Cash", 1).limit(10):
        print(entry)
        rank_number += 1``` i am trying to create a database but i am getting a syntax error about this part `{ $exists: True }` anyone able to help?

[austere portal]

Put quotes aroud $exists

Make it a string

[frail heath]

ayy thanks got it working

[torn sphinx]

hi, I want to see a user's rank (position) and I don't know how, I am creating a level system for a game and it is only 3 important columns, xp, req and level. How can I see the rank of a user?

in MySQL

[ionic pecan]

@torn sphinx what determines the rank in your application?

you're most likely looking for ORDER BY

https://www.w3schools.com/sql/sql_ref_order_by.asp

[torn sphinx]

It's all messy, I already used it

top = await obj.execute(
    """SELECT userid, level, req, xp, row_number() over(order by level + xp asc)
    FROM levels
    WHERE guildid = %s
    ORDER BY level DESC, xp DESC
    LIMIT 10""", (message.guild.id)
)

When seeing a lot, it appears in a messy way.

[ionic pecan]

I see. Could you put that in a helper function?

[torn sphinx]

how much data could a postgres database hold on a 16gb chromebook?

[storm mauve]

a better question is, how much data do you need to hold?

[torn sphinx]

probably about 1 to 3gb

[storm mauve]

almost definitely enough then

[torn sphinx]

alright

thanks

[torn sphinx]

how

[torn sphinx]

@ionic pecan

[dense barn]

im getting this error when i try to make a column with a deafult value

ERROR: cannot use column reference in DEFAULT expression

[ionic pecan]

well, creating a new function that takes your cursor and the guild_id as arguments, and calling it. that way you don't need to duplicate the rank code in all the places

[ionic pecan]

do you want to use a string default value? in that case, you need to single-quote it

if you double-quoted it, your database engine will probably have thought that you tried to reference a column

also, having the full sql command might be helpful

[dense barn]

yea i want "Uncompeleted" as the default value for that column

[ionic pecan]

yeah, you will need to use single quotes then

[dense barn]

yep, it works thanks! ill keep that in mind the next time i want to do that.

[ionic pecan]

👍

[dense barn]

ok, so i tried updating it from 'Uncompeleted' to 'Uncaught', but when i try refreshing and running the script again it does not update it, neither does it when i fetch it.

[ionic pecan]

show your code to update it

[dense barn]

well, i updated it through pgadmin

[dense barn]

should i just fetch the whole list and then iterate over it and just change it?

[austere portal]

You want to rename the column?

[dense barn]

no change the default value of a column

[austere portal]

You need to alter the table

[dense barn]

alter?

[austere portal]

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT expression```

https://www.postgresql.org/docs/current/sql-altertable.html

[austere portal]

That should work

[dense barn]

ok ill try that

ALTER TABLE cards ALTER COLUMN card_status SET DEFAULT 'Uncaught'
``` @austere portal do i need quotes for the expression?

@austere portal still didnt work

but its ok i can just delete it and rename it that

[timber birch]

Hi everyone, I'm new to databases. I want to setup a local database for a simple web application that performs queries from that database. What's something easy to setup and interract with python?

[storm mauve]

sqlite3 is probably the easiest, and it's included in the standard library

[nova glen]

100% endorsed.

[timber birch]

thanks, I'll look into that one

[storm mauve]

you still have to learn the basics of SQL, but it's really not much - just CREATE, INSERT and SELECT (possibly also ALTER and UPDATE)

[timber birch]

is it easy to setup on a linux machine?

[storm mauve]

you do not really have to setup anything, it's about as simple as opening a json file

[timber birch]

wow that's great

[storm mauve]

the async version might be a bit more complicated since async stuff, but probably not much

[nova glen]

I think sqlite is a huge asset skillset wise, it's not a tool you'll outgrow.

I use https://sqlitebrowser.org/ frequently with it, also with Linux support.

Sqlite is everywhere.

[austere portal]

You need single quotes if the expression is a string

[austere portal]

You can do that, but take a look into ALTER, if your table already has a lot of data you cant delete it and rename it

[torn sphinx]

Yes

you only import the module

import sqlite3

# To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:
con = sqlite3.connect('example.db')
cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

[austere portal]

What placeholders does the aiopg library use?

[grim vault]

The example I've seen uses %s, maybe print(aiopg.paramstyle) will work?

[austere portal]

maybe print(aiopg.paramstyle) will work?'
Didn't work

Thanks, looks like its %s

[grim vault]

Hm: https://www.python.org/dev/peps/pep-0249/#paramstyle

not pep compatible then.

[austere portal]

I'll switch from asyncpg to aiopg

[austere portal]

%s is in that table

[grim vault]

I meant These module globals must be defined: ... paramstyle

[austere portal]

Oh, sorry didn't see hat part

[grim vault]

🎩 _ _ ;)

[calm prawn]

Can anyone tell me how can I do a partial search using pymongo?

[austere portal]

How can I add a column and rename the table in a single query? (postgres)

[grim zephyr]

Command raised an exception: IndexError: tuple index out of range

[faint blade]

Why exactly?

[austere portal]

my application uses psycopg2 and asyncpg, the problem is I need to generate a new query for asyncpg because of the different placeholders instead of reusing the same query

[faint blade]

You can't, but if you want to do it in one function call you can do two queries: ```sql
ALTER table blah blah blah; ALTER table blah blah blah;

[austere portal]

Thanks, figured it out

[faint blade]

Huh, why are you using both? You'll block the event loop

[austere portal]

its a lib, it uses psycopg2 for sync usage asyncpg for async usage

[grim zephyr]

someone help

[faint blade]

Hmm, I see.

[faint blade]

You're not really putting much effort into asking your question, why should I put effort into helping you?

Please provide the full traceback, appropriate code, what you have already tried and what happened, and lastly what database/wrapper you use.

[grim zephyr]

only this error no traceback ````Command raised an exception: IndexError: tuple index out of range```

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[grim zephyr]

this error is occuring again and again

[faint blade]

You always get a traceback, do you have an error handler that doesn't print the traceback?

[grim zephyr]

well i have a error handler and thats probably printing this

let me raise the errror

[faint blade]

Can you comment it out so that we can see a full traceback?

[austere portal]

await cursor.execute("SELECT bank_account FROM economy WHERE guild_id = ? AND user_ID = ?", (ctx.guild.id, ctx.author.id))
            result = await cursor.fetchone()
            bank_middle = result[2]```

The result tuple only has one item

[grim zephyr]

its also showing this

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[austere portal]

You are only selecting bank_account

[grim zephyr]

iyep i want to get its value

[austere portal]

Print result and see

[grim zephyr]

ok

let me check

[grim zephyr]

the same error again

[faint blade]

You need to print before the error

Print before you do bank_middle = ...

[grim zephyr]

ok

[grim zephyr]

(3113,) this was printed

(3113,)

[faint blade]

Exactly, it's a tuple with 1 item.

[grim zephyr]

i see

[faint blade]

But you try to pick the 3rd item (index 2), the tuple isn't that long

You need to pick index 0.

[grim zephyr]

well can u plz explain me whats with the tuple, i mean what is the proper meaning of tuple in python

[austere portal]

Yeah

[faint blade]

A tuple is pretty much the simplest sequence of items that you can create in Python.

Tuples are immutable, that means you cannot change them after you've created them.

Otherwise they behave pretty much like lists.

[austere portal]

you have that in multiple places

[faint blade]

They have an order, and you can index them (my_tuple[0]).

[grim zephyr]

i see

well sry guys but a new error

[faint blade]

That's okay, can you tell us what it is?

[grim zephyr]

let me give the code

this part

[faint blade]

This means that you have 2 ? but you gave 3 arguments

Your second query is wrong

[grim zephyr]

well looks like it has problem in the 1st cursor.execute and the 2nd cursor.execute

[faint blade]

You do WHERE guild_id AND user_ID

[grim zephyr]

wdym

can i do like this py await cursor.execute("UPDATE economy SET bank_account = bank_account - {amount} WHERE guild_id = ? AND user_ID = ?", (ctx.guild.id, ctx.author.id)) await db.commit()

[grim vault]

missing = ?

[grim zephyr]

@austere portal

[austere portal]

no

[grim zephyr]

so how

[austere portal]

#databases message

bank_account = bank_account - ? and pass in the value

[grim zephyr]

i didn't get you

wdym

[austere portal]

Your query should be UPDATE economy SET bank_account = bank_account - ? WHERE guild_id = ? AND user_ID = ? and pass in the value you want to deduct in the parameters

[grim zephyr]

sry but i am still confused but a simple example will be helpful

[austere portal]

Replace {amount} with ?

[faint blade]

It should be WHERE guild_id = ? AND user_ID = ?

[grim zephyr]

it is py await cursor.execute("UPDATE economy SET bank_account = bank_account - ? WHERE guild_id = ? AND user_ID = ?", (amount, ctx.guild.id, ctx.author.id))

[austere portal]

Yes

And commit

[grim zephyr]

it is not working

[faint blade]

await cursor.execute("UPDATE economy SET wallet = wallet + ? WHERE guild_id = ? AND user_ID = ?", (amount, ctx.guild.id, ctx.author.id))

[grim zephyr]

i got it thanks @faint blade@austere portal

sry for the ping

[austere portal]

I don't mind pings 😄

[grim vault]

If you are selecting or updating the same table it can be done in one go, no need to split it up per column.

[grim zephyr]

just due to a '=' this was not working lol

[grim vault]

                upd_stmt = (
                    "UPDATE economy"
                      " SET bank_account = bank_account - ?,"
                          " wallet = wallet + ?"
                    " WHERE guild_id = ?"
                      " AND user_ID = ?"
                )
                await cursor.execute(upd_stmt, (amount, amount, ctx.guild.id, ctx.author.id))
                await db.commit()
                sel_stmt = (
                    "SELECT bank_account, wallet"
                     " FROM economy"
                    " WHERE guild_id = ?"
                      " AND user_ID = ?"
                )
                await cursor.execute(sel_stmt, (ctx.guild.id, ctx.author.id))
                bank_account, wallet = await cursor.fetchone()

[faint blade]

Hm? You could simply remove the second query by adding a RETURNING * which will return the updated row.

[grim vault]

You could remove all select (except the first one) because the very first select is a select * from economy and to the +/- in python and set the value in the update directly to the new one. Also the > amount and == amount else branches can be done in one >= amount and you can ...

He will learn (I hope).

[remote plinth]

            connection = await self.pool.acquire()
            cursor = await connection.cursor()
``` can this work too as in aiomysql?

[faint blade]

Are you having problems with that line?

[remote plinth]

no but i cant test rn

i just try to make a context manager for mysql pool

[hallow dome]

Hi can can anyone suggest a free database that communicates via requests and is able to execute under high traffic.
Key-Value databases would be the best

[calm prawn]

can someone tell how can I do a random query to mongodb using pymongo?

[limber gyro]

Wrestler.first_name as Wrestler_away_first, 
Wrestler.last_name as Wrestler_away_last,
Fights.wrestler_away_id,
Wrestler.first_name as Wrestler_home_first,
Wrestler.last_name as Wrestler_home_last,
Fights.wrestler_home_id
FROM Fights
INNER JOIN Wrestler ON Fights.wrestler_home_id = Wrestler.wrestler_id AND Fights.wrestler_away_id= Wrestler.wrestler_id ```
Why is this not working?
In table fights I have wrestler_home_id and wrestler_away_idand  in table wrestler I have first_name, last_name and wrestler_id and I'd like to display the names for home and away. If I delete everything after AND it works just for home.

[snow niche]

async def dbinit():
  conn = await aiosqlite.connect("db's/modlogs.db")
    cursor = conn.cursor()
    await cursor.execute("CREATE TABLE IF NOT EXISTS modlogs (guild_id int, channel_id int)")
    await conn.commit()
``` it says no table named modlogs

[brave bridge]

Can you show the error?

[snow niche]

Traceback (most recent call last):
  File "/home/runner/Zion/cogs/mod.py", line 172, in kick
    await kickdb.execute(f'SELECT channel_id FROM modlogs WHERE guild_id="{ctx.guild.id}"')
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.OperationalError: no such table: modlogs

[brave bridge]

Can you show the function where the error occurs?

[snow niche]

this is my kick cmd for my bot

    @commands.command(usage='kick <member> [reason]')
    @commands.guild_only()
    @commands.has_guild_permissions(kick_members=True)
    @commands.cooldown(1, 3, commands.BucketType.guild)
    async def kick(self, ctx, member:discord.Member, *, reason=None):
        """Kicks a member from the server."""
        if reason is None:
            reason = 'No reason provided'
        await member.kick(reason=reason)
        await ctx.send(f'{self.bot.yes} Kicked `{member}`')

        try:
            kickdb = await aiosqlite.connect("db's/modlogs.db")
            cursor = await kickdb.cursor()
            await kickdb.execute(f'SELECT channel_id FROM modlogs WHERE guild_id="{ctx.guild.id}"')
            channel = await cursor.fetchone()
            print(channel)
            ch = self.bot.get_channel(channel)

            em = discord.Embed(
                title = 'Member Kicked',
                description = f'**Moderator:** {ctx.author}\n**Member:** {member}\n**Reason:** {reason}\n**Guild:** {ctx.guild.name}',
                color=discord.Color.random()
            )
            await ch.send(embed=em)
        except Exception as e:
            await ctx.send("".join(traceback.format_exception(e, e, e.__traceback__)))

[brave bridge]

Can you open the database externally (with the sqlite3 command-line program or with a viewer like sqlitebrowser)?

[snow niche]

ok

[brave bridge]

If you're using the command-line program, just query SELECT * FROM modlogs;, otherwise look at what tables you have

[snow niche]

im using this:
https://inloop.github.io/sqlite-viewer/

[brave bridge]

so, what does SELECT * FROM modlogs; give?

[brave bridge]

why are you selecting from null?

[snow niche]

oops

same thing

[brave bridge]

As you can see, you haven't created the table.

Where did you call dbinit? How do you know that it has been called?

[snow niche]

at last when running bot

# ===== starting bot =======
if __name__ == '__main__':
    bot.loop.create_task(dbinit())
    zion = Zion()
    zion.run()
    asyncio.run(dbinit.close())

[brave bridge]

What is bot and what is zion?

[snow niche]

zion is my bot name

discord.py

[brave bridge]

And what is bot?

[snow niche]

bot = Zion()

class Zion(commands.Bot):
    def __init__(self):
        super().__init__(
            command_prefix=commands.when_mentioned_or('z.','Z.'),
            intents=discord.Intents.all(),
            case_insensitive=True
        )
        self._BotBase__cogs = commands.core._CaseInsensitiveDict()
        self.yes = ':yes:'
        self.no = ':no:'
        self.token = os.getenv('TOKEN')

        for extension in INITIAL_EXTENSIONS:
            try:
                self.load_extension(extension)
            except Exception as e:
                print('Failed to load extension {}\n{}: {}'.format(
                    extension, type(e).__name__, e))

    async def on_ready(self):
        if not hasattr(self, 'uptime'):
            self.uptime = datetime.datetime.utcnow()
        print('Bot online')

    async def close(self):
        await super().close()
        await self.session.close()
    
    def run(self):
        super().run(self.token, reconnect=True)

bot = Zion()

[brave bridge]

You created two bot instances, didn't you?

and you ran the one where you don't add dbinit() as a task

[snow niche]

at last i didnt

[brave bridge]

1. bot = Zion()
2. zion = Zion()

Why did you initialize it twice?

[snow niche]

the 2nd one is just a var for running it

so like zion.run

[brave bridge]

Do you understand how objects and classes work?

[snow niche]

instead of Zion().run()

[brave bridge]

What you did is you added the task to the bot object's loop, but you ran the zion object.

[snow niche]

[brave bridge]

bot and zion are two different objects.

[snow niche]

ok?

[brave bridge]

Do you see what the issue is?

[snow niche]

yeah

so i could have just done bot.run() instead of zion.run()?

[brave bridge]

Yeah

although... now that I think about it, that's not the issue, but you should probably do that anyway

[snow niche]

hmm ok

let me see if it works

[torn sphinx]

hey, im using sqlalchemy and i have this code:

    lensubs = range(subs.count())
    print(lensubs)
    for x in lensubs:
        print(subs[x].subreddit)
    print("~~~~")
    for x in subs:
        print(x.subreddit)

but for some reason the for loop with lensubs doesnt work the prints im getting from it is:

redditdev
redditdev
testing
test
~~~~
testingground4bots
redditdev
testing
test

for some reason it changes testingground4bots to redditdev, does someone know what could cause this?

[snow niche]

still saying no such table

[brave bridge]

One alternative would be to just run a synchronous function (using sqlite3 before you start the bot

[snow niche]

aiosqlite is async version of sqlite

[grim vault]

You'll need two separate joins to wrestler:

SELECT
  Fights.weight, 
  Wrestler_away.first_name as Wrestler_away_first, 
  Wrestler_away.last_name as Wrestler_away_last,
  Fights.wrestler_away_id,
  Wrestler_home.first_name as Wrestler_home_first,
  Wrestler_home.last_name as Wrestler_home_last,
  Fights.wrestler_home_id
 FROM Fights
INNER JOIN Wrestler AS Wrestler_home ON Wrestler_home.wrestler_id = Fights.wrestler_home_id
INNER JOIN Wrestler AS Wrestler_away ON Wrestler_away.wrestler_id = Fights.wrestler_away_id

[brave bridge]

I know, but if you want to just initialize the tables, you can just run a function using sqlite3 before you start the bot.

I don't see any big issues with that.

[snow niche]

hmm btw i cant dm @rugged locust

[brave bridge]

You should open your DMs on this server here

[snow niche]

ok

[jade reef]

how can i prettify this?

(without doing ctrl+shift+i and instead doing it from inside python)

[brave bridge]

not sure how it's related to databases, but json.dump/json.dumps accepts an indent parameter

!d json.dump

[delicate field]

json.dump

json.dump(obj, fp, *, skipkeys=False, ensure_ascii=True, check_circular=True, allow_nan=True, cls=None, indent=None, separators=None, default=None, sort_keys=False, **kw)```
Serialize *obj* as a JSON formatted stream to *fp* (a `.write()`-supporting [file-like object](https://docs.python.org/3/glossary.html#term-file-like-object)) using this [conversion table](https://docs.python.org/3/library/json.html#py-to-json-table).

If *skipkeys* is true (default: `False`), then dict keys that are not of a basic type ([`str`](https://docs.python.org/3/library/stdtypes.html#str "str"), [`int`](https://docs.python.org/3/library/functions.html#int "int"), [`float`](https://docs.python.org/3/library/functions.html#float "float"), [`bool`](https://docs.python.org/3/library/functions.html#bool "bool"), `None`) will be skipped instead of raising a [`TypeError`](https://docs.python.org/3/library/exceptions.html#TypeError "TypeError").

The [`json`](https://docs.python.org/3/library/json.html#module-json "json: Encode and decode the JSON format.") module always produces [`str`](https://docs.python.org/3/library/stdtypes.html#str "str") objects, not [`bytes`](https://docs.python.org/3/library/stdtypes.html#bytes "bytes") objects. Therefore, `fp.write()` must support [`str`](https://docs.python.org/3/library/stdtypes.html#str "str") input.

If *ensure\_ascii* is true (the default), the output is guaranteed to have all incoming non-ASCII characters escaped. If *ensure\_ascii* is false, these characters will be output as-is.

[jade reef]

yeah i didnt know which channel to ask this in

[brave bridge]

If there's no specific topical channel, you should see #❓｜how-to-get-help and claim a normal help channel

[steep ingot]

Hi, I have a csv database that needs to be replaced by something hosted online. It needs to be free and accessible from heroku. It can't be public.

I looked at sqlite and it looked to me like it was basically the same as a csv. I can't find a free way to host an postgresql db

[brave bridge]

Have you seen https://www.heroku.com/postgres?

[steep ingot]

no, 1 min and I'll check if that'd work

thanks

Looks perfect, how did I miss it XD

[brave bridge]

just make sure you read about the restriction of the tier you're using

(e.g. the free tier only allows for 10000 rows)

SQLite doesn't share much in common with using CSV, but you do need access to a persistent file system.

[steep ingot]

Yeah, 10k should be enough. it's got to store 1 row per reported(like ingame report comes in saying x is saying n word kinda thing) player of a game that has <3k players. If it hits the limit we will be able to upgrade XD

[steep ingot]

Yes, what I meant was it's on heroku and we have to download and reupload the database every update

[thorny field]

Hello! So I am coding a discord bot that has a SQLite database. In one of the commands I require to change values from a row that I get by using fetchone(). How do I change the values of the row valueCheck?
Code:

@bot.command()
async def buystock(ctx, arg1, arg2):
    user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)

    cursor.execute(
        "SELECT 1 FROM main WHERE user=? and guild=?",
        [user_id, guild_id]
    )

    do_query = """SELECT * from main"""
    cursor.execute(do_query)

    valueCheck = cursor.fetchone()

    if (valueCheck is None):
        await ctx.send("You need to be registered in this server to use this command.")
    elif (valueCheck is not None):
        if (int(arg2) < 0):
            await ctx.send("You can't buy negative shares!")
        elif (int(arg2) == 0):
            await ctx.send("You can't buy 0 shares!")

[gentle roost]

I'm using sqlite, trying to order a table and return the position of a row in that table

Just for context, i'm developing a levelling system for a discord bot and trying to implement a rank command. The command will show the user's xp in relation to the rest of the server. eg. "You have 25th most xp in this server"

I'm able to order the table but not get the entry's position. I'd appreciate it if you could ping me if you have any advice

[pure cypress]

You could use a subquery with row_number() to generate positions for each row, and then select from that. However, that doesn't deal with the edge case that two people have the same xp.

[limber gyro]

Thanks for your answer, but this returns no results :/ Tried this with diffrent approches but didnt work till now 😉 There is no Error but also no result

[proven arrow]

Share a reproducible example, with some test data

https://www.db-fiddle.com/

[fresh nova]

is good idea to store image in database?

[limber gyro]

On this website it works perfect (https://www.db-fiddle.com/f/7nJkvPDUHX2v3Hgc7v4fJQ/0) but on my DB Browser or SQLite it doesn't. Now I have the right answer but I'm confused 😄

[gentle roost]

Hm i've managed to do it, but it's probably not the most efficient

WITH rankings AS (
SELECT guild_id, user_id, xp , RANK () OVER (PARTITION BY guild_id ORDER BY xp DESC ) as 'rank' FROM users)
SELECT user_id, rank FROM rankings WHERE user_id = x AND guild_id = x

[void current]

I have a question regarding a project I'm doing which uses a database

i have a function that uses an input from a user to get a specific row using the primary key, i then need to update those rows with the information the user inputs. But i need to somehow not update the column if they leave the input blank.

This is the original function

def change_into_product_db(new_product, new_price, new_product_id):
    load_dotenv()
    host = os.environ.get("mysql_host")
    user = os.environ.get("mysql_user")
    password = os.environ.get("mysql_pass")
    database = os.environ.get("mysql_db")

    connection = pymysql.connect(
        host,
        user,
        password,
        database
    )

    cursor = connection.cursor()

    sql = 'UPDATE Products SET Product_Name = %s, Product_Price = %s WHERE Product_Id = %s'
    val = [(new_product, new_price, new_product_id)]

    cursor.executemany(sql, val)

    connection.commit()
    cursor.close()
    connection.close()

I've tried doing it this way but still the same issue

def change_into_product_db(*args):
    load_dotenv()
    host = os.environ.get("mysql_host")
    user = os.environ.get("mysql_user")
    password = os.environ.get("mysql_pass")
    database = os.environ.get("mysql_db")

    connection = pymysql.connect(
        host,
        user,
        password,
        database
    )

    cursor = connection.cursor()

    sql = 'UPDATE Products SET Product_Name = %s, Product_Price = %s WHERE Product_Id = %s'
    val = [(args)]

    if args == None:
        args
    else:
        cursor.executemany(sql, val)

    connection.commit()
    cursor.close()
    connection.close()

my Product_Price price only accepts floats so it throws an error when i leave the new_price input blank

[grim vault]

    sql = 'UPDATE Products SET Product_Name = %s'
    if new_price:
        sql += ', Product_Price = %s'
        val = (new_product, new_price, new_product_id)
    else:
        val = (new_product, new_product_id)
    sql += ' WHERE Product_Id = %s'

    cursor.execute(sql, val)```
?

[void current]

i'll try this now

[long dome]

I assume POSTGRESQL locks the access to the DB when INSERT/DELETE/UPDATE operations are being done to prevent data corruptions right? which also delays further actions until that specific one is done no?

In that case isn't it better to have different databases to separate stuff like "accounts" "deposits" "withdrawals" instead of keeping it all in a single DB and just different tables?

[void current]

I get the same issue, when i skip the input prompt it just leave it empty instead of leaving the data how it is and the price throws an error saying cannot convert string to float

[grim vault]

Adjust the if then like if not isinstance(new_price, (float, int)): or so

[void current]

Ok, i'll try this

[grim vault]

Does the error even come from the database execute?

[void current]

Im not sure what you mean xS

[grim vault]

I was just wonderng if the throws an error saying cannot convert string to float is raised by the cursor.execute(sql, val).

[tawdry pawn]

for item in db.find(): File "C:\Users\Michael\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\collection.py", line 3480, in __call__ raise TypeError("'Collection' object is not callable. If you " TypeError: 'Collection' object is not callable. If you meant to call the 'find' method on a 'Database' object it is failing because no such method exists.

guilds = self.client.guilds
        dbguilds = []
        for item in db.find():
            try:
                dbguilds.append(item['guild_id'])
            except:
                pass
        for guild in guilds:
            if len(dbguilds) == 0 or guild.id not in dbguilds:
              try:
                antitoggle.insert_one({
                  "guild_id": guild.id,
                  "channel_delete": "True",
                  "channel_create": "True",
                  "massban": "True",
                  "masskick": "True",
                  "role_create": "True",
                  "role_delete": "True",
                  "webhook_ud": "True",
                  "vanity_protect": "True",
                  "scrape_guard": "True"
                })```

[void current]

This is the error i get

[grim vault]

That's python not the database.

[void current]

i think it because my input is surrounded by float

[void current]

You're right

Should i remove the float(Input())

[grim vault]

If you want to allow an empty string as input you'll need to rethink your input strategy.

[wary grail]

Let's say i have a table teams and i have 2 tables participants and moderators. I want to team to have a name, players (can be of participants but also of moderators). How would i create a connector table to has a teamid and either a staffId or participantId?

[pure sleet]

make a 3rd table for the relationship

[void current]

Okay, thanks for the help Berndulas, i appreciate it 🙂

[wary grail]

So just a table that contains a teamId, moderatorId (which can be null) and a participantId (which can be null)?

[pure sleet]

yea.

[wary grail]

Okey sounds actually quite logicial

[pure sleet]

although, i feel like you could make a table to represent a person and add some roles to tell whether they are just a participant or a moderator. that way you'll just have a relationship between a team and a person if that makes sense

[wary grail]

Yeah i was also thinking about doing that, but i am not quite sure yet, since i also have staff appointments that are seperate from this, so it might be handy to have them seperated

[pure sleet]

alright

[next sun]

I have a multiline sql statement

sql = f"""INSERT INTO "WeatherData" (\
locationid, dataprovider\
) VALUES\
"""
            is_first = True
            for row in reader:
                values_dict = {}
                for index, value in enumerate(row):
                    values_dict[columns[index]] = value
                
                if is_first:
                    is_first = False
                else:
                    sql+=","

                sql += f"""\
({values_dict['locationid']},\
'{values_dict['dataprovider']}')
"""

which returns the following string:

'INSERT INTO "WeatherData" (locationid, dataprovider) VALUES(847,\'Radiatus_v3\')\n'

that will error in postgres

[grim vault]

If you are using """ you don't need a \ at the end of line.

[next sun]

hum

but still the second value should be 'Radiatus_v3'

I don't understand what is it escaping the value

can someone help ?

[grim vault]

Something like:

insert_list = []
idx_locationid = columns.index('locationid')
idx_dataprovider = columns.index('dataprovider')
for row in reader:
    insert_list.append(
        (row[idx_locationid], row[idx_dataprovider])
    )

sql = 'INSERT INTO "WeatherData" (locationid, dataprovider) VALUES (%s, %s)'
cursor.executemany(sql, insert_list)
cursor.commit()

[next sun]

no, but I want to the output to be:

INSERT INTO mytable(locationid,dataprovider,locationcode,) VALUES (868,'Radiatus_v3',)
            , (869,'Radiatus_v3')

I am not executing the query

[grim vault]

one way would be:

insert_list = []
idx_locationid = columns.index('locationid')
idx_dataprovider = columns.index('dataprovider')
for row in reader:
    insert_list.append(
        (row[idx_locationid], row[idx_dataprovider])
    )

sql = 'INSERT INTO "WeatherData" (locationid, dataprovider) VALUES '
sql += ",\n   ".join(str(row) for row in insert_list)
print(sql)```

[next sun]

humm....

let me try it

but I have a dictionary of values

that is different

[grim vault]

I based my example on your code.

[next sun]

yes

let me try it then

[next sun]

can I not use triple quotes

because I will have 21 columns

I only put two to simplify

for test purposes

[grim vault]

Sure you can. Your example also worked, the escaping only was done because you used something like repr(sql).

[next sun]

                values_dict = {}
                for index, value in enumerate(row):
                    values_dict[columns[index]] = value
                
                if is_first:
                    is_first = False
                else:
                    sql+=","```

remember that I am building a dictionary of values that is going to be used in the sql

sorry

don't worry

I just noticed something on your code ahah

the output is not the desired

that wouldn't work in the database

[dry iron]

I am trying to make a dataframe that gets the difference of two values from two different dataframes given that these values were taken 1 year apart, and this is the code I have for it

However, the output is only 2 rows and there should be many more rows and I'm not sure why this is (here's the output)

Btw this is what the china_df_2019 dataframe looks like if that helps

the china_df_2020 dataframe looks basically the same except the year is 2020

could anyone help me figure out why this is happening?

[hearty fox]

guys anybody here has worked with back4app database ? I am realizing it is very vulnerable to DDoS attacks. I tried googling. no help.

[torn sphinx]

hello i need help is postgreSQL can someone show me how to make a table please and thank you

[austere portal]

You can create tables using CREATE TABLE

[austere portal]

https://www.postgresql.org/docs/current/sql-createtable.html

[torn sphinx]

thank you

[lone island]

does asyncpg return the row in a tuple with fethrow() ?

[austere portal]

No, it returns a asyncpg.Record object

!d asyncpg.Record

[delicate field]

asyncpg.Record

class Record```
A read-only representation of PostgreSQL row.

[lone island]

can i have an example of returning the data from the record ?

[jaunty galleon]

#databases message

[austere portal]

record["column_name"]

[lone island]

ty

sooo if i use record[0] it should return the value of the first column

[jaunty galleon]

That you fetched by the order you wanted I think

[austere portal]

pin dis

[lone island]

im a bit confused

row[1]

this is not working

[austere portal]

the indentation looks wrong

and you need to pass in a query for conn.fetchrow

Whats the error?

[lone island]

toponeid = queryone[1]
TypeError: 'NoneType' object is not subscriptable

[austere portal]

yeah, row is None

you need to pass in a sql query

[lone island]

the sql qeuery is working fine in pgadmin
here is the code

it does return the row

but its nonetype here

this is the output of the query

toponeid = topone[1]
TypeError: 'NoneType' object is not subscriptable

[lone island]

so?

[jaunty galleon]

Got it from here

[grim vault]

But this says guildrank = 2 and you select rt.guildrank = $2 and supply 1 for $2

[lone island]

thats the output of another query which has guildrank = 2

can anyone help with the above error ?

[narrow moth]

I haven't written the docstring or any typing but this is my code for inserting postgres sql from a dictionary:

https://pastebin.com/t4P8txZB

It's not very robust and needs the dict in order of parent table then descendant table for FKs (and only 1 primary key (no composites), 0-1 foreign key)... but it's got the sql.SQL/Identifier stuff pretty good. (I think/hop)

[austere portal]

you cant index None

[vast wolf]

Can anyone help me to Run a python script from url in terminal?

[grim vault]

But you said that's the query and that's the output. Now you say it's not the same query, so maybe try the same query to check what's what. The error clearly states that topone is None, so your fetchrow() returned None. Check your select again in pgadmin (with the same sql and values you used in the program).

[lone island]

whats the solution

[lone island]

i just sent the wrong picture, thats not the problem

[austere portal]

I guess the parameters you passed in pgadmin and the parameters you passed in the python code are different

[lone island]

i checked it 100 times and its alright, ima check it again then

they r not different

[austere portal]

you are passing the id as a string

[lone island]

yes the column type is string (name), im also passing string in the code

[austere portal]

then in your python code try doing str(g_id)

[austere portal]

ah

[lone island]

wait

i think i just found out

..

i was passing user's id in the code instead of guild's id.............

nice im still getting the same error

nvm i know whys this one happening

[uncut garnet]

Hey! I want to paginate some results of a query with a MySQL database to process my data chunk per chunk.
After some research I found that a way of doing it is to sort my result with some sort of id then apply a WHERE clause on that column
But, I also saw that mysql-connector provides the fetchmany which seems to do what I want. I would execute my initial request without any LIMIT, then do multiple fetchmany until there's no more data to fetch.
What are the downsides of using fetchmany for that?

[smoky radish]

as far as i understand a query and subsequent fetchmany will load all the data on the server and fetch it to the client in chunks. i think what you're describing would more be a server side chunking using LIMIT and OFFSET.

in both cases you ultimately get your data to the client in chunks, but with fetchmany it has to be loaded in one query on the server, and with LIMIT OFFSET you run multiple queries to get multiple chunks. ultimately, fetchmany only lightens the load on the client while LIMIT OFFSET should also reduce the load server side

[uncut garnet]

Ok I see, I tried to use the limit offset but the performance were awful (as expected), I guess I'll stick with fetchmany, easier to setup on my end, thanks for your response!

[proven arrow]

For mysql lib only a buffered cursor will fetch everything at once

You can use fetchone() or fetchmany() to consume certain rows at a time

What’s the Sql query you are running?

[smoky radish]

whats the size of the query, as in, how many results does it produce when run without limit?

[uncut garnet]

About 850k entries

[uncut garnet]

A SELECT with 3-4 JOIN I believe

[formal coral]

is there any way I can check that if a record already exists in a table and if yes, update it, if no, make a new record all in 1 query? btw using sqlite

I know how I can do this in multiple queries, but was wondering if it is possible in 1

also ping me if you got an answer for me :))

[burnt turret]

http://www.sqlite.org/draft/lang_upsert.html

[formal coral]

thank you! didn't knew there was a clause for my exact need

[formal coral]

one last thing, does the row (which will have the non-unique conflict) has to be the Primary Key?

Like in this example, can I use ON CONFLICT(phonenumber)?

[harsh pulsar]

@formal coral you can apply a UNIQUE constraint to a column that isn't the primary key

however the "conflict" will only happen if there is a violated uniqueness constraint

i.e. if you don't have a unique constraint on the column, there won't ever be a conflict

i recommend https://www.db-fiddle.com/ and/or https://sqlitebrowser.org/ for messing around with this stuff

[formal coral]

ok so atleast UNIQUE must be defined beforehand

damn, I will have to edit my table schema

no worries, its still in beta :)

[harsh pulsar]

well it looks like name is the primary key here

so it doesn't matter?

[formal coral]

yea

yea

[harsh pulsar]

primary key is always unique

[formal coral]

yea was just about to type that

[harsh pulsar]

maybe consider that names in a phone book shouldn't be unique

[formal coral]

so for conflict to occur, the row has to have the UNIQUE property

[harsh pulsar]

yes, but primary keys are always unique

but again... what if there are 2 people with the same name in the phonebook?

[formal coral]

yea I know, that example is from sqlite docs

[harsh pulsar]

hah

they should have made the phone number the primary key!

[formal coral]

yea I thought so too

didn't knew there was an exact feature for this

very helpful, thank you so much

[harsh pulsar]

yeah, most databases nowadays have some version of ON CONFLICT

[jaunty galleon]

I try to decode this imagr of bytes(ofc it is much much longer)and it errors me. The error says this:

invalid start byte

It's bytes from discord image for discord bot, and I got it from BYTEA column from postgersql

[formal coral]

maybe you are using the wrong byte type in the decoder?

like bytes for strings are usually encoded in utf-8

images are encoded with some other type akaik

[jaunty galleon]

m!eval

from io import BytesIO
async with self.bot.pool.acquire() as conn:
  data = await conn.fetch('''SELECT * FROM messages WHERE message_id = ($1)''', 871764300073668650)
#print(data[0]['attachment'])
buffer = data[0]['attachment']
await ctx.send(file=discord.File(fp=buffer, filename='Hello.png'))``` This is my code if that's what you mean

[formal coral]

do you have to use BytesIO here?

[jaunty galleon]

CREATE TABLE messages (guild_id bigint, channel_id bigint, message_id bigint, author_id bigint, message_content text, attachment bytea)```

[formal coral]

lol I just copied this screenshot

[jaunty galleon]

lol

[formal coral]

idk, haven't used postgres much

also never stored bytes

[jaunty galleon]

ok

Anyone maybe know?

[grim vault]

Have you tried to actually use the imported BytesIO?

buffer = BytesIO(data[0]['attachment'])```

[jaunty galleon]

Thank you

[torn sphinx]

Please don't use ableist language here.

[jaunty galleon]

I am sorry,I won't do it again!

[torn sphinx]

You're good, thanks!

[steep ingot]

Hi, I'm trying to use pymongo and I got this error:
pymongo.errors.OperationFailure: user is not allowed to do action [listDatabases] on [admin.], full error: {'ok': 0, 'errmsg': 'user is not allowed to do action [listDatabases] on [admin.]', 'code': 8000, 'codeName': 'AtlasError'}

This is my code:```py
mongo_uri = "mongodb+srv://cluster0.b2rau.mongodb.net/myFirstDatabase"
client = pym.MongoClient(mongo_uri)

client.list_database_names()```

[haughty lark]

what databases are covered here?

is YAML covered here?

[harsh pulsar]

YAML isn't a database, it's a file format

[haughty lark]

can it not be used as a database?

[harsh pulsar]

no

you can use it to store data

but not as a "database" that supports arbitrary reading and writing

[haughty lark]

ahh ok

what channel could i get help for yaml parsing

#algos-and-data-structs ?

[harsh pulsar]

it depends... are you writing a yaml parser as a hobby project?

if you are asking about reading and writing yaml data then this place is fine. but you can also ask in a help channel, see #❓｜how-to-get-help

if you are asking about writing a parser from scratch then i think it depends on the question, but yes #algos-and-data-structs might be helpful if you have questions about parsing

[haughty lark]

ok, im trying to read a yaml file, edit it, and then write it, but im using a dictionary with a custom class as the value and its chucking a fit

construct_undefined raise ConstructorError(None, None, yaml.constructor.ConstructorError: could not determine a constructor for the tag 'tag:yaml.org,2002:python/object:main.Meme'

class Meme():
    likes = 0
    dislikes = 0
    weight = 0
    reactors = []


with open(fileName) as f:
    data = yaml.load(f, Loader=yaml.FullLoader)

[harsh pulsar]

i've never actually used this functionality

smells like XML, i don't like it 😛

[haughty lark]

XD

[harsh pulsar]

pyyaml uses tags for deserialization?

honestly i think the idea is kind of misguided, imo it makes more sense to have a classmethod that loads data from arbitrary yaml (or rejects it)

i won't tell you your setup is entirely wrong, but i will suggest https://marshmallow.readthedocs.io/en/stable/ as an alternative

as for the question itself, it's possible that pyyaml doesn't know how to deal with classes defined in __main__ (i.e. the "current script")

can you 1) show the full script you used to save the yaml data, 2) show the saved yaml data itself

[haughty lark]

so hold on haha whats wrong with what ive done here?

should i just use a nested dictionary instead of a class

bc i could do that

[high nest]

Do Null and the word 'Test-text' have the same size?

in sql

[brazen charm]

no

or well

depends on the database

[haughty lark]

thanks but i think ill just use nested dictionaries, it works out easier

[brazen charm]

but no modern sql databases will pad a null value to the max string length

[high nest]

when I create varchar (15), after adding a row to the table, it has an automatically defined size,or depending on what I write (so characters)?

[brazen charm]

it's just a constraint

most SQL dbs dont pad to that length

they just store upto that length and dont care about making all the rows that length via padding

it's just a runtime check rather than a storage thing

[high nest]

okay thank you

[formal coral]

CREATE TABLE message_count (
  user_id INTEGER,
  guild_id INTEGER,
  count INTEGER
)

My table scheme is like above, I am making a message counting system, in which, it will store the data in a local variable, updates the database with the new data every like 2 minutes.
I saw UPSERT today but in my case, the user_id and guild_id are not unique, as a member can be in different guilds, and there can be members in the same guild so the ids will repeat.
So I was thinking, can I, by any chance, run the checks, insert/update in one query? As of now, I have only found that I will have to get the data, and check with the data, wrap the packets, and use insert (many) and update (many) manually.
As they are not unique, is there anything related to it, I can do in this case?

this is in a discord bot and its a sqlite db^

for guildid, memdata in messages.items():
  for datapairs in memdata:
    c.execute('SELECT count FROM message_count WHERE guild_id = ? AND user_id = ?', (guildid, datapairs[0]))
    d = c.fetchone()
    if d:
      to_be_updated_data.append((datapairs[1]+d[0], guildid, datapairs[0]))
    else:
      to_be_inserted_data.append((datapairs[0], guildid, datapairs[1]))

c.executemany("INSERT INTO message_count VALUES (?, ?, ?)", to_be_inserted_data)
db.commit()

c.executemany("UPDATE message_count SET count = ? WHERE guild_id = ? AND user_id = ?", to_be_updated_data)
db.commit()

This is what I had written, but it is not efficient.

messages = {
  6666666666666: [(123123, 53), (543464, 12)], # 6666666666666: guild_id, 123123: user_id, 53: count
  7777777777777: [(765412, 76), (756711, 64)],
  8888888888888: [(761999, 34), (564123, 89)]
}

[brazen charm]

looks like you want a composite key? to make the combo of user_id and guild_id be the primary key / contraint

[formal coral]

whats a composite key?

[brazen charm]

basically means that the combination of two or more keys is unqiue but not necessarily the keys separately

[formal coral]

Oo

[brazen charm]

https://www.educba.com/composite-key-in-sql/

[formal coral]

so here ```sql
CREATE TABLE message_count (
user_id INTEGER,
guild_id INTEGER,
count INTEGER,
PRIMARY KEY (user_id, guild_id)
)

[brazen charm]

see the above link

[formal coral]

so they together would be unique

[brazen charm]

just requires an extra CONTRAINT <comp name> section at the front

yes

[formal coral]

so when using an UPSERT, it would be like

INSERT INTO message_count (user_id, guild_id, count) VALUES (1, 2, 3)
  ON CONFLICT(<comp name>) DO UPDATE SET count = excluded.count
```?

or in place of <comp name>, (user_id, guild_id)?

[brazen charm]

(user_id, guild_id)

also i think you mean excluded instead of extended?

[formal coral]

oh yea mb

tysm, I will experiment with this in a few mins

[formal coral]

thank you so much! I just checked and it worked :))

[gloomy spindle]

in PostgreSQL, there's the date datatype. i have a table userinfo(user_id bigint PRIMARY KEY, birthdate date)
what would be an efficient way to fetch the dates if the date matches today's day and month, but not year?

[cunning jolt]

you mean like today a year ago, two years ago, etc.?

for that you can use extract date_part

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

so for example:sql SELECT * FROM userinfo WHERE EXTRACT(DAY FROM birthdate) = 1;

[gloomy spindle]

ohhh thx ❤️

[cunning jolt]

dw

[whole coral]

Hi all. I'm using a python discord bot with PostgreSQL database. But each day my bot stops interacting with database abruptly. Could happen in 12-22 hours after bot restart.
No errors in log file.
Connection to database is acquired using asyncpg.create_pool(min_size=20, max_size=30)

please, any ideas on why could this be happening?
All commands that are related to database transactions just stop working

[cunning jolt]

maybe try running it for debug logs for a while

other than that i can't help you

[tawdry pawn]

    data = await antitoggle.find_one({ "guild_id": role.guild.id })```

`TypeError: object dict can't be used in 'await' expression`

[cunning jolt]

i'll need more context for that

what db are you using

what library and if what orm

[thorny field]

Can someone explain how to update/change the records in a SQLite database?

[whole coral]

after I done

logger = logging.getLogger('discord')
logger.setLevel(logging.DEBUG)```
at the top of the script - do I need to do anything else - or it will write all verbose errors info in the consile at this point already?

[cunning jolt]

not sure if you need to get the logger but yes more or less

[whole coral]

thanks.

[cunning jolt]

import discord
import logging

logger = logging.getLogger('discord')
logger.setLevel(logging.DEBUG)
handler = logging.FileHandler(filename='discord.log', encoding='utf-8', mode='w')
handler.setFormatter(logging.Formatter('%(asctime)s:%(levelname)s:%(name)s: %(message)s'))
logger.addHandler(handler)```

id write it to a file like that tho

[whole coral]

I'm already writing stdout to a file, so I don't need this line in my case :)

[cunning jolt]

oh fair

[harsh pulsar]

@whole coral if you don't add the handler, the log messages will go nowhere

[whole coral]

thank you very much! If I go with basicConfig it won't suffice?
logging.basicConfig(level=logging.DEBUG, stream='stdout')

[harsh pulsar]

that will apply to the root logger, not just the 'discord' logger

[whole coral]

by root logger you mean that all console output will go to stdout?

or "info, warning, error" stuff?

I just want to have the verbose output of what's happening in my stdout, since my stdout is being written to a log file already.

[whole coral]

in the case described above - logging.basicConfig(level=logging.DEBUG, stream='stdout') will be enough?

[harsh pulsar]

no, those are different log levels

when you write getLogger('discord') you are getting the logger named "discord"

all loggers are hierarchical, in that the logger a.b.c is a "child" of logger a.b and a.b. is a "child" of a

and all loggers are a child of the root logger

when you set a log level, log handler etc. on a logger, all child loggers are also affected

[whole coral]

so what kind of info does the root logger provide?

will things like "established a connection to postgresql database" be logged too this way?

[harsh pulsar]

nothing unless you use logging.getLogger(), which returns the root logger. but if you set a handler or something on the root logger, it will also affect all other loggers

the idea is that library authors can all use their own loggers

[whole coral]

then in my case - will getting only 'discord' logger be enough?

[harsh pulsar]

yes, if that's all you want to see

[whole coral]

I just want to learn why my bot disconnects from psql database xD

[harsh pulsar]

hm, that might or might not help

what postgres library are you using?

[cunning jolt]

god, reasons as to why i use loguru instead of logging

[harsh pulsar]

the discord logger is provided by the discord.py library, so it will only be related to that library.. unlikely to be postgres related

[harsh pulsar]

i think basicConfig should have a logger kwarg so you could apply the config to any specific logger

would solve a lot of the "idk logging hard" problems in python

[cunning jolt]

fair

tho loguru in general is a lot more nicer

[harsh pulsar]

i do like how the loguru model has less complexity, you don't have formatters handlers etc.

[whole coral]

asyncpg

[whole coral]

.

[harsh pulsar]

there is an asyncpg logger, but it looks like it's only used to log 1 warning message https://github.com/MagicStack/asyncpg/blob/075114c195e9eb4e81c8365d81540beefb46065c/asyncpg/pool.py#L22

[delicate field]

asyncpg/pool.py line 22

logger = logging.getLogger(__name__)```

[whole coral]

so I'd better get asyncpg logged once more and specify more verbose output?

[faint blade]

Is this PostgreSQL instance running on your computer?

[whole coral]

no, on a VM

[faint blade]

Hmm, can it possibly loose the connection then?

[whole coral]

the bot script is hosted at the same VM

could it still be losing connection ?

[harsh pulsar]

it looks like it only emits a warning log

how do you know it loses connection? you get errors?

[faint blade]

They said it just stops working

Arr you able to provide the errors from your commands @whole coral?

[whole coral]

no errors

just abruptly commands that require database connection stop working

that's why I'm trying to set a more detailed logging

[harsh pulsar]

what does "stop working" mean?

[whole coral]

no output

[harsh pulsar]

can you show an example of the code for one of these commands?

you don't see any errors in the console on the server either?

and the other bot commands keep working fine?

[whole coral]

@commands.has_permissions(administrator=True)
async def show(ctx, member: discord.Member):
    """Shows the info about user/ показываем данные пользователя"""
    db = await pool.acquire()
    data = await db.fetchrow(f'SELECT * FROM discord_users WHERE id={member.id};')
    if data is not None:
        achievments = 0
        negative_achievements = 0
        warns = int(data['warns'])
        for role in member.roles:
            if 'ачивка' in role.name.lower():
                achievments += 1
                if role.color == discord.Colour(int('ff4f4f', 16)):
                    negative_achievements += 1
        positive_achievements = achievments - negative_achievements
        try:
            seven_days_activity_records = await db.fetch(
                f"SELECT login, logoff from LogTable WHERE login BETWEEN '{datetime.datetime.now() - datetime.timedelta(days=7)}'::timestamptz AND '{datetime.datetime.now()}'::timestamptz AND user_id={member.id} ORDER BY login ASC;")
            thirty_days_activity_records = await db.fetch(
                f"SELECT login, logoff from LogTable WHERE user_id={member.id} AND login BETWEEN '{datetime.datetime.now() - datetime.timedelta(days=30)}'::timestamptz AND '{datetime.datetime.now()}'::timestamptz ORDER BY login ASC;")
        finally:
            await pool.release(db)```

[whole coral]

those that do not include db connection - yes

[harsh pulsar]

aren't you supposed to use async with pool.acquire() as db? then you don't need the try/finally

my only guess is somehow that your discord_users table is getting wiped; also your query uses f-strings, which is bad practice

it's likely to result in errors, even if you don't have to worry about sql injection security

see the example from the asyncpg docs:

    await conn.execute('''
        INSERT INTO users(name, dob) VALUES($1, $2)
    ''', 'Bob', datetime.date(1984, 3, 1))

you could debug by sending a message back to the user if data is None

also this looks incomplete; it doesn't actually send any data back to discord

[whole coral]

yeah, next part is just add output to embed and send

[harsh pulsar]

ok, maybe do

if data is None:
    # send message to user saying "no user data found"
else:
    # your current code with the database queries

and remove the try/finally

[whole coral]

I don't know how to make a request without fstrings like this
f"SELECT login, logoff from LogTable WHERE user_id={member.id} AND login BETWEEN '{datetime.datetime.now() - datetime.timedelta(days=30)}'::timestamptz AND '{datetime.datetime.now()}'::timestamptz ORDER BY login ASC;"

[harsh pulsar]

regarding query parameters, you would want to rewrite your first query like this

data = await db.fetchrow(f'SELECT * FROM discord_users WHERE id=$1', member.id)

[whole coral]

ohhhh

so it's like this?

I thought you ought to write VALUES (1,2,3)

[harsh pulsar]

that's for an insert

VALUES is part of INSERT syntax

[whole coral]

so for UPDATE it will be the same? like update, where id=$1', user.id ?

[harsh pulsar]

yes

activity_query = """
    SELECT login, logoff
    FROM LogTable
    WHERE
      user_id = $1 AND
      login BETWEEN $2 AND $3
    ORDER BY login ASC
"""
t_now = datetime.datetime.now(tz=datetime.timezone.utc)
t_7days_ago = t_now - datetime.timedelta(days=7)
t_30days_ago = t_now = datetime.timedelta(days=30)
seven_days_activity_records = await db.fetch(
    activity_query, member.id, t_now, t_7days_ago
)
thirty_days_activity_records = await db.fetch(
    activity_query, member.id, t_now, t_30days_ago
)

as per https://magicstack.github.io/asyncpg/current/usage.html#type-conversion this will produce timestamp data, not timestamptz data, unless you specify a time zone in the datetime object

what timezone are you using in the database? UTC?

@whole coral ☝️

[whole coral]

yes

okay, in sum - If i rewrite all asyncpg.pool methods to "with pool.acquire as db" and all queries to the variand with $1, $2, $3 - abrupt disconnections might be fixed, since there are no other errors?

[old patrol]

Does anyone have any advice on the table structure for keeping track of users who viewed a particular record / job posting:

self.cursor.execute("""CREATE TABLE IF NOT EXISTS job_postings (
                id integer PRIMARY KEY,
                parent_search_id integer NOT NULL,
                title text,
                company text,
                description text,
                location text,
                post_date text,
                latitude text,
                longitude text,
                url text,
                category text,
                
                FOREIGN KEY(parent_search_id) REFERENCES searches(search_id)
                );""")

self.cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS users (
                  user_id integer PRIMARY KEY,
                  country text,
                );
                """

example: User with user_id 1 views job_posting records with id 1 and 2. What table structure would be most efficient to keep track of that?

[pure sleet]

create a 3rd table to keep track of how many times a person viewed a specific post etc

[old patrol]

thanks

[delicate frigate]

Traceback (most recent call last):
  File "E:\Learn MySQL\main.py", line 3, in <module>
    mydb = mysql.connector.connect(
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 208, in _open_connection
    self._do_auth(self._user, self._password,
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 137, in _do_auth
    packet = self._protocol.make_auth(
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\protocol.py", line 99, in make_auth
    packet += self._auth_response(client_flags, username, password,
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "C:\Users\neild\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\authentication.py", line 190, in get_auth_plugin
    raise errors.NotSupportedError(
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

im stupid, anyone know why?

[old patrol]

show code where you establish connection

[whole coral]

please, whenever you'll be online tell me if this request will work as intended:

thirty_days_activity_records = await db.fetch(
                        "SELECT login, logoff from LogTable WHERE user_id=$1 AND login BETWEEN $2::timestamptz AND $3::timestamptz ORDER BY login DESC;", member.id, t_30days_ago, datetime.datetime.now())```

[delicate frigate]

na, i was dumb, wrong setup

do you know any good resourses to learn SQL?

[whole coral]

also, after I've rewritten all functions from
db = await pool.acquire
await pool.release(db)
to
async with pool.acquire() as db:

operations that were taking 4-5 seconds are now taking ~40 seconds. That is like 8-10 times slower

[harsh pulsar]

hard to say, but youre going from "doing weird stuff" to "doing less weird stuff" so hopefully yes

[harsh pulsar]

you can omit the ::timestamptz, if you use datetime.datetime.now(tz=datetime.timezone.utc) you will get timezone-aware timestamp conversion as per https://magicstack.github.io/asyncpg/current/usage.html#type-conversion

[harsh pulsar]

...that is very surprising

can you show the code for one of these?

the docs specifically recommend using async with https://magicstack.github.io/asyncpg/current/api/index.html#connection-pools

[whole coral]

@bot.command()
async def top(ctx, count: int = 10):
    result_list = []
    #await ctx.message.delete()
    users_count, users_ids = await initial_db_read()
    checkrole = discord.utils.find(lambda r: ('СОКЛАНЫ' in r.name.upper()), ctx.guild.roles)
    t_30days_ago = datetime.datetime.now() - datetime.timedelta(days=30)
    async with pool.acquire() as db:
        for member in ctx.guild.members:
            if member.id in users_ids and checkrole in member.roles and not (member.id == member.guild.owner_id):
                gold = await db.fetchval("SELECT gold from discord_users WHERE id=$1;", member.id)
                if int(gold) > 0:
                    warns = await db.fetchval("SELECT warns from discord_users WHERE id=$1;", member.id)
                    thirty_days_activity_records = await db.fetch(
                        "SELECT login, logoff from LogTable WHERE user_id=$1 AND login BETWEEN $2::timestamptz AND $3::timestamptz ORDER BY login DESC;", member.id, t_30days_ago, datetime.datetime.now())
                    activity = await count_result_activity(thirty_days_activity_records, warns)
                    result_list.append((member.mention, activity))
        res = sorted(result_list, key=itemgetter(1), reverse=True)
        count = len(res) if count > len(res) else count
        output = "".join(f"{i + 1}: {res[i][0]}, актив: {res[i][1]} часа(ов);\n" for i in range(count))
        embed = discord.Embed(color=discord.Colour(int('efff00', 16)))
        embed.add_field(name='Топ активности', value=output)
        await ctx.send(embed=embed)```

[harsh pulsar]

and what was it before?

[whole coral]

the same just with f-strings in queries and db = pool.acquire()

[harsh pulsar]

can you post it? just for completeness

you shouldn't see any reduction in performance from that change

[whole coral]

@bot.command()
async def top(ctx, count: int = 10):
    result_list = []
    #await ctx.message.delete()
    db = await pool.acquire()
    users_count, users_ids = await initial_db_read()
    checkrole = discord.utils.find(lambda r: ('СОКЛАНЫ' in r.name.upper()), ctx.guild.roles)
    for member in ctx.guild.members:
        if member.id in users_ids and checkrole in member.roles and not (member.id == member.guild.owner_id):
            gold = await db.fetchval(f"SELECT gold from discord_users WHERE id={member.id};")
            if int(gold) > 0:
                warns = await db.fetchval(f"SELECT warns from discord_users WHERE id={member.id};")
                thirty_days_activity_records = await db.fetch(
                    f"SELECT login, logoff from LogTable WHERE user_id={member.id} AND login BETWEEN '{datetime.datetime.now() - datetime.timedelta(days=30)}'AND '{datetime.datetime.now()}' ORDER BY login DESC;")
                activity = await count_result_activity(thirty_days_activity_records, warns)
                result_list.append((member.mention, activity))
    res = sorted(result_list, key=itemgetter(1), reverse=True)
    count = len(res) if count > len(res) else count
    output = "".join(f"{i + 1}: {res[i][0]}, актив: {res[i][1]} часа(ов);\n" for i in range(count))
    embed = discord.Embed(color=discord.Colour(int('efff00', 16)))
    embed.add_field(name='Топ активности', value=output)
    await ctx.send(embed=embed)
    await pool.release(db)```

[harsh pulsar]

some other notes:

• you can omit the ; at the end
• if you are capitalizing sql keywords, you should also capitalize FROM
• you're calling datetime.datetime.now multiple times, you really should call it only once (and use the timezone like i said to)
• if you use the timezone as above, you can omit the ::timestamptz casting

[whole coral]

okay, I think i can omit the timestamptz

[harsh pulsar]

as for your actual code, try moving the res = sorted ... outside the async with

[whole coral]

tried both ways. no difference

[harsh pulsar]

like this?

@bot.command()
async def top(ctx, count: int = 10):
    result_list = []
    #await ctx.message.delete()
    users_count, users_ids = await initial_db_read()
    checkrole = discord.utils.find(lambda r: ('СОКЛАНЫ' in r.name.upper()), ctx.guild.roles)
    t_now = datetime.datetime.now(tz=datetime.timezone.utc)
    t_30days_ago = t_now - datetime.timedelta(days=30)
    async with pool.acquire() as db:
        for member in ctx.guild.members:
            if member.id in users_ids and checkrole in member.roles and not (member.id == member.guild.owner_id):
                gold = await db.fetchval("SELECT gold FROM discord_users WHERE id=$1", member.id)
                if int(gold) > 0:
                    warns = await db.fetchval(
                        "SELECT warns FROM discord_users WHERE id=$1", member.id
                    )
                    thirty_days_activity_records = await db.fetch(
                        "SELECT login, logoff FROM LogTable WHERE user_id=$1 AND login BETWEEN $2 AND $3 ORDER BY login DESC",
                        member.id, t_30days_ago, t_Now
                    )
                    activity = await count_result_activity(thirty_days_activity_records, warns)
                    result_list.append((member.mention, activity))
    res = sorted(result_list, key=itemgetter(1), reverse=True)
    count = len(res) if count > len(res) else count
    output = "".join(f"{i + 1}: {res[i][0]}, актив: {res[i][1]} часа(ов);\n" for i in range(count))
    embed = discord.Embed(color=discord.Colour(int('efff00', 16)))
    embed.add_field(name='Топ активности', value=output)
    await ctx.send(embed=embed)

[whole coral]

yep

so strange...I've never had any function that were calculating for 40+ seconds.

[harsh pulsar]

that is definitely very strange

are you sure it wasn't just a coincidence?

it happens every time now?

[whole coral]

yes, it happens every time now

[harsh pulsar]

is it possible that the query was malformed before, due to the f-string thing, and now it's executing "properly"?