databases

[harsh pulsar]

maybe you need to commit?

[modest ledge]

ye i did commit

wait nvm

i was being dumb

i was updating exp but exp was Null so it wouldnt do a +1

[broken estuary]

is there a way to pull database changes from heroku to my repo? im only using sqlite tho. im just wondering

[torn sphinx]

how to i return prefix: "!" ?

^Mongo

[modest ledge]

mysql.connector.errors.DataError: 1264 (22003): Out of range value for column 'user' at row 1@harsh pulsar could you tell why this is happening?

[proven arrow]

Is your user column type int? @modest ledge

[modest ledge]

yes

all are int

[proven arrow]

Well the error you have is because the User ID value is too large to store as an Int. Instead use BIGINT type. You can see all the values allowed here: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Signed int allows max value of 2147483647

[harsh pulsar]

i reiterate: "ugh, mysql"

[modest ledge]

oh

[proven arrow]

@modest ledge Btw, didnt you say you wanted this database for your discord bot?

[modest ledge]

yep

[proven arrow]

Well dont use mysqlconnector

[modest ledge]

im trying to move my data from JSON to mysql and its taking like forever

what should i use then

[proven arrow]

Mysqlconnector is a blocking lib, instead use aiomysql

[modest ledge]

so itll be async?

[proven arrow]

Yes

[modest ledge]

oh

thats gonna take a while to fix since i already spent arounf 20 minutes to update my whole code to operate with mysql rather than json

what does blocking lib mean

[proven arrow]

There is synchronous (sync), and asynchronous (async).
When you do something with sync you have to wait for it to complete before moving on to the next part (this waiting can cause what is known as blocking). For example, making a DB request and waiting for the database to get back to you with the data. This time you are waiting, is time wasted, and it blocks other code from running until your DB returns with the data. Whereas if you execute something with async, you can start doing other tasks whilst it finishes.
https://discordpy.readthedocs.io/en/latest/faq.html#what-does-blocking-mean

[modest ledge]

ohh

so for aiomysql the connecting is different

[proven arrow]

Not that blocking is always bad, but if your code relies around async then try not to block.

[modest ledge]

i mean i might have to write like once every 1-2 second

[proven arrow]

It should be similar, I haven't used it for a long time. Major differences will be that you have to await things

[modest ledge]

ye so i have to like

pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='mysql')
db = await pool.acquire()```

then im naming my sql as db?

do i have to do the loop=loop?

[proven arrow]

No

[modest ledge]

no to which one? 1 or 2

[proven arrow]

No to you dont need loop

Also you can just make the connection once, so like when the program starts. Then whenever you need to use the db just get a connection from the pool.

[modest ledge]

ye thats the plan

[proven arrow]

And that is the recommended, since making connections can be expensive so just keep single

[modest ledge]

so i'll keep the pool when the program starts

and everytime i need to get a connection i do db = await pool.acquire()?

[proven arrow]

Umm, well what do the docs say. I never use mysql with python so let me check

[modest ledge]

im not sure how the pool thing works but as much as i understood thats how it "should" work

[proven arrow]

Pools make it more efficient when you make the requests to the DB. It keeps multiple connections alive with the DB. So whenever you need to use the DB, instead of creating a new connection which takes resources, you can just get one from the pool.

async with pool.acquire() as conn:
    async with conn.cursor() as cursor:
        await cursor.execute(...)

As an example, so here pool is the name of where you database connection is stored.

[modest ledge]

                                      user='root', password='',
                                      db='mysql')``` and this is how we get pool?

[proven arrow]

Yeah looks fine.

[modest ledge]

perfect

thx

[proven arrow]

Just reference to the docs, https://aiomysql.readthedocs.io/en/latest/

[modest ledge]

async def create_poool():
    pool = await aiomysql.create_pool(host='127.0.0.1', port=3306,
                                      user='root', password='',
                                      db='mysql')
    return pool

pool = asyncio.run(create_poool())```

@proven arrow Py Exception ignored in: <function Connection.__del__ at 0x04A70B68> Traceback (most recent call last): File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 1072, in __del__ File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 298, in close File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\proactor_events.py", line 108, in close File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 719, in call_soon File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 508, in _check_closed RuntimeError: Event loop is closed

nvm i did it

[torn sphinx]

Ping

[modest ledge]

Wtf moving all my data from JSON to sqlite took like a few second but moving then to mysql is taking more than 15 mins

[torn sphinx]

how can i return prefix: "!" ?

idk how to return that value.

i want it to fetch this and send

[burnt turret]

in your code have you initialized the collection and kept?

[torn sphinx]

my code just sends the data.

[burnt turret]

are you using guild ID to match? or the guild's name?

[torn sphinx]

guild iD

idk why i put name. :P

[burnt turret]

the query would be something like

data = yourcollection.find_one({_id:<guild id>})
prefix = data['prefix']

[torn sphinx]

oh

i see

[burnt turret]

yourcollection has to be, well, your collection lmao. im assuming you have initialized it somewhere in your code so that you can use it like this

[torn sphinx]

yea i did define it

defined*

yes i initialized

[burnt turret]

this should work then iirc. if find_one doesnt get any matches it should return None i think, and when it does get matches it gives you normal python dictionaries

[torn sphinx]

@burnt turret sorry i had to get off discord. this is my code btw

CONNECTION_URL=os.environ.get("mongo")
cluster = MongoClient(CONNECTION_URL)
db = cluster["UserData"]

@client.command()
async def prefix(ctx):
  data = db['prefixes'].find_one({"_id": {ctx.guild.id}})
  prefix = data['prefix']
  await ctx.send(f'Your prefix is : {prefix}')

see that {ctx.guild.id} ? I’m pretty sure i’m doing something wrong

[burnt turret]

should work i think, you dont need ctx.guild.id in braces though

[torn sphinx]

ok

[burnt turret]

also, are you using MongoDB atlas? tell me how it goes, i'd tried using it and it was a bit slow at times, so i now use mongodb locally on the VM im hosting my bot on. It works fine because it is just for my server @torn sphinx

[torn sphinx]

yes i’m using atlas. (i think, becoz the site says mongodb atlas)

also yea it works

sends prefix

i guess i better make a function to get_prefix

so it will be ezier

[burnt turret]

but if you're planning on putting this bot in many servers, mongo isnt the best option i've heard, as pymongo is blocking so it could freeze your bot as it waits for responses (almost negligible when the queries are fast, but can get annoying), and the async driver for mongodb is called motor-and everyone hates it lmao

Are you paying for atlas?

or the free option?

[torn sphinx]

i choose the free option

i saw menudocs on yt use this. So yeah..

i didn’t know it would cause issues

didn’t know it was blocking

[burnt turret]

Well i havent had issues either lmao but everyone wouldnt just be lying i guess

[torn sphinx]

xD

mhm. i’ll try anyways

[burnt turret]

Yeah the queries were super quick for me so it didnt feel like it was blocking. You should be fine if there isnt a lot of data i guess

[torn sphinx]

yeah.

thanks though. appreciate your help :)

i’ll let yknow how it goes

[burnt turret]

😄 glad i could help. no one here seems to use mongo, everyone is SQL gang lmao

[torn sphinx]

xD

[cinder dome]

but if you're planning on putting this bot in many servers, mongo isnt the best option i've heard, as pymongo is blocking so it could freeze your bot as it waits for responses (almost negligible when the queries are fast, but can get annoying), and the async driver for mongodb is called motor-and everyone hates it lmao
@digital wharf Check that out

[digital wharf]

Not surprised

[near thicket]

is it worth learning different database management systems or just sticking to one

I know that for the most part they are same, I'm just wondering

[shell ocean]

is it worth learning different database management systems or just sticking to one
@near thicket why do you want to do that

[near thicket]

im just wondering

[shell ocean]

im just wondering
@near thicket okay, when you say that, do you mean different kinds of RDBMS (like Oracle, MySQL, Postgres etc.)

or including NoSQL (like Cassandra, MongoDB, Neo4J)

[near thicket]

different kind of RBDMS

[shell ocean]

hm, my personal opinion is that they are more or less similar enough that it would be better for you to go deep into one

(first)

[karmic wraith]

I have an assignment about data design anomalies, is this the correct channel to ask about it?

[spice wave]

print("Hola mundo:")
for i in tqdm(range(0, X_train_windowed.shape[0] - seq_len+1)):
X_train_Conv_LSTM[i] = current_seq_X
y_train_Conv_LSTM[i] = y_train[i + seq_len - 1]

(262, 3, 50, 50, 3) X_train_Conv_LSTM.shape = (1, 3, 50, 50, 3) current_seq_X.shape
(262, 1) y_train_Conv_LSTM.shape = (264,) y_train.shape

cupy\core\core.pyx in cupy.core.core.ndarray.setitem()

cupy\core_routines_indexing.pyx in cupy.core._routines_indexing._ndarray_setitem()

cupy\core_routines_indexing.pyx in cupy.core._routines_indexing._scatter_op()

cupy\core_kernel.pyx in cupy.core._kernel.ufunc.call()

cupy\core_kernel.pyx in cupy.core._kernel._get_out_args()

pritn ("hello word")

[torn sphinx]

i'm having problems with my database connection. (python3+mariadb).
traceback:
UnicodeEncodeError: 'ascii' codec can't encode character '\xd3' in position 89? ordinal not in range(128)

import mysql.connector

mydb = mysql.connector.connect(
    host='localhost',
    user='root',
    passwd='pass',
    database='db',
    charset='utf8mb4'
)

mycursor = mydb.cursor()

rsql = ('sis_cliente')


def runSQL(rsql):
    mycursor = mydb.cursor()

    mycursor.execute('select * from sis_cliente')

    result = mycursor.fetchall()

    return result


result = (runSQL(rsql))
print(result)

@willow cargo

@blissful flare

I made this but i don't think it's the best way

for i in result:
    for j in i:
        print((j.encode('utf-8') if type(j) == str else j))

[gloomy pike]

anyone know if it's possible to transfer .db files through FileZilla, and if so, how?

[willow cargo]

yes just send them as a normal file

[gloomy pike]

every time i try to send it to fz i get the error "could not start transfer"

which sounds pretty generic

[willow cargo]

how large is it?

[gloomy pike]

very small

[willow cargo]

it's just a binary file similar to an image

[gloomy pike]

3.26 mb

[willow cargo]

check that you can transfer other things?

[gloomy pike]

yes

everything else was transferred

like 100+ other .py files

[willow cargo]

hmm idk what to tell you

it should owkr

work*

[gloomy pike]

ok

maybe there's still a transaction going on

i'm pretty sure there's no transactions going on as the bot is offline

and also a single .log file is failing to transfer

along with the .db file

nvm it's there

[torn sphinx]

<?php

define("SERVIDOR", "localhost");
define("USUARIO", "root");
define("SENHA", "senha");
define("BANCO", "banco");

function runSQL($rsql) {
    $conexao = mysqli_connect(SERVIDOR, USUARIO, SENHA, BANCO);
     $res=mysqli_query($conexao, $rsql);
    return $res;
}
?>

var = runSQL("SELECT * FROM sis_cliente ORDER BY id")

[subtle vector]

php 😐

[torn sphinx]

uh

[eternal raptor]

and

[modest ledge]

@eternal raptor you have to do like await Cursor.fetchone()

[eternal raptor]

@modest ledge

@harsh pulsar

[eternal raptor]

Ignoring exception in on_member_join
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\user\Desktop\Zonnerty\cogs\greetings.py", line 40, in on_member_join
    await channel.send(axe1)
AttributeError: 'NoneType' object has no attribute 'send'

    @commands.Cog.listener()
    async def on_member_join(self, member):
        gildia = member.guild.id
        user = member.mention
        async with aiosqlite.connect("C:\\Users\\user\\Desktop\\Zonnerty\\zonnerty.db") as db:
            query1 = await db.execute("SELECT powitanie FROM guilds_general  WHERE GuildID = ? ", (gildia,))
            query2 = await db.execute("SELECT pow_ch_id FROM guilds_general WHERE GuildID = ? ", (gildia,))
            axe1 = await query1.fetchone()
            axe2 = await query2.fetchone()
            channel = self.client.get_channel(axe2)
            await channel.send(axe1)
            await db.commit()

PLZ help me

[modest ledge]

@eternal raptor dude first of all dont ping someone, secondly fetchone() returns a tuple you cant just get a channel from it like that

[eternal raptor]

hm...

how to change?

how to make correct?

Ignoring exception in on_member_remove
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\user\Desktop\Zonnerty\cogs\greetings.py", line 56, in on_member_remove
    channel = self.client.get_channel(int(axer2))
TypeError: int() argument must be a string, a bytes-like object or a number, not 'tuple'

    @commands.Cog.listener()
    async def on_member_remove(self, member):
        gildia = member.guild.id
        user = member.mention
        async with aiosqlite.connect("C:\\Users\\user\\Desktop\\Zonnerty\\zonnerty.db") as db:
            query1 = await db.execute("SELECT pozegnanie FROM guilds_general  WHERE GuildID = ? ", (gildia,))
            query2 = await db.execute("SELECT poz_ch_id FROM guilds_general WHERE GuildID = ? ", (gildia,))
            axer1 = await query1.fetchone()
            axer2 = await query2.fetchone()
            print(axer1)
            print(axer2)
            channel = self.client.get_channel(int(axer2))
            await channel.send(axer1)
            await db.commit()

[modest ledge]

print axer1 and axer2 and show me the results

[eternal raptor]

('Papa, {user}. Wracaj miśku :c',)
(719539016130625577,)

In Polish language.

[modest ledge]

!eval

channel_id = tuple[0]
print(channel_id)```

[delicate field]

You are not allowed to use that command here. Please use the #bot-commands channel instead.

[modest ledge]

rip

anyway you know how its done now

[eternal raptor]

ok, wait, I will try it

.

axe1 = await query1.fetchone([0]) ?

[modest ledge]

no

when you do a fetchone() that returns the tuple and youre defining it as axe1 what you wanna do is axe1[0]

[eternal raptor]

ch_id = query1[0]

?

[modest ledge]

no

[eternal raptor]

axe1 = await query1[0].fetchone()

[modest ledge]

axe1 = await query1.fetchone()
# this returns the tuple
channel_id = axe1[0]
# thats the channel id you got```

idk if your column datatype was int or not, if it was then the tuple would return an int, if not then you'd have to convert the channel_id into int

[eternal raptor]

    @commands.Cog.listener()
    async def on_member_remove(self, member):
        gildia = member.guild.id
        user = member.mention
        async with aiosqlite.connect("C:\\Users\\user\\Desktop\\Zonnerty\\zonnerty.db") as db:
            query1 = await db.execute("SELECT pozegnanie FROM guilds_general  WHERE GuildID = ? ", (gildia,))
            query2 = await db.execute("SELECT poz_ch_id FROM guilds_general WHERE GuildID = ? ", (gildia,))
            axer1 = await query1.fetchone()
            axer2 = await query2.fetchone()
            channel_id = axer2[0]
            channel = self.client.get_channel(channel_id)
            await channel.send(f"{axer1}")
            await db.commit

('Papa, {user}. Wracaj miśku :c',) Why is {user} instead example: @eternal raptor

('Papa, @eternal raptor . Wracaj miśku :c',)

Deity

[harsh pulsar]

@eternal raptor you need to stop guessing and asking people. You need to look at your program and try to understand it.

Im sorry. I don't want to be harsh. But i think you know enough python to be able to start understanding and solving these problems. If English documentation is difficult to read, you can ask questions about it here.

If your database contains "{user}" then maybe you wrote that string to the database

Did you try to use an F-string to make a query?

[torn sphinx]

@burnt turret Check back when you’re online :)
This is my code to update my prefix.

@client.command()
@commands.has_permissions(administrator=True)
async def setprefix(ctx, newprefix:str=None):
  data = {"_id": ctx.guild.id,
  "guildName": ctx.guild.name,
  "prefix": newprefix}
  db["prefixes"].insert_one(data)
  await ctx.send(f"New prefix set to `{newprefix}`")

What i understood is that insert_one would make a new dict and add this new data, If there is a data existing already it would Update it. This isn’t working. What i understood this that is error happened because it has the same guild iD
Error:

[modest ledge]

it says duplicate key?

[burnt turret]

@torn sphinx insert_one always inserts- if you want to insert if not exists/update if exists, you should be using update_one, with a kwarg upsert=True
https://docs.mongodb.com/manual/reference/method/db.collection.update/#update-upsert
(edited the link to point towards upsert)

[torn sphinx]

ty

[burnt turret]

your query would look something like

yourcollection.update_one({filter},{your changes}, upsert=True)

[torn sphinx]

@client.command()
@commands.has_permissions(administrator=True)
async def setprefix(ctx, newprefix:str=None):
  data = {"_id": ctx.guild.id,
  "guildName": ctx.guild.name,
  "prefix": newprefix}
  db["prefixes"].update_one(data, upsert=True)
  await ctx.send(f"New prefix set to `{newprefix}`")

ok ty

let me try

am i missing something?

@burnt turret

[burnt turret]

what's your code looking like now?

[torn sphinx]

the above

@client.command()
@commands.has_permissions(administrator=True)
async def setprefix(ctx, newprefix:str=None):
  data = {"_id": ctx.guild.id,
  "guildName": ctx.guild.name,
  "prefix": newprefix}
  db["prefixes"].update_one(data, upsert=True)
  await ctx.send(f"New prefix set to `{newprefix}`")

[burnt turret]

oh, looks like you're missing a filter

basically you need to tell mongo which specific document you want to edit, and for that you use a filter

[torn sphinx]

filter = "prefix":

[burnt turret]

no, the filter is also a dictionary

wait a sec

[torn sphinx]

o ok

[burnt turret]

yourcollection.update_one({'guildName':ctx.guild.name}, {"$set":{"prefix":newprefix}}, upsert=True)

'guildName':ctx.guild.name} -> this part will make sure mongo is only searching for that exact guild's data

$set is mongo's operator to, well, set new data to a field

[torn sphinx]

ah tysm

[burnt turret]

and as you're only really changing the prefix data you can directly type it in, if you had more data you could put that dictionary instead, like
{"$set":data}

[torn sphinx]

what’s the use of Upsert

and as you're only really changing the prefix data you can directly type it in, if you had more data you could put that dictionary instead, like
{"$set":data}
ah i see

[burnt turret]

upsert -> update + insert
it searches through the db based on the filter you provided, if it exists it updates and if it doesnt it inserts

[torn sphinx]

thanks

[burnt turret]

😄

[torn sphinx]

let me try now

[burnt turret]

👍 i'm gonna be offline for a bit, ping me if there are any issues and i'll see when i come on

[torn sphinx]

alrighty

It works! :D

ty

[modest ledge]

is it just me or mongodb is terribly slow

is it cause my internet connection is slow or what

[burnt turret]

mongodb or their online atlas clusters? atlas can be slow if you're using the free version, and it also depends on your connection i guess, but mongodb locally has been running fast enough for me

[modest ledge]

well ye i meant atlas clusters

not only connecting is taking like 5 seconds but like all queries is taking 2-3 seconds each

should i be using pymongo?

[burnt turret]

pymongo is blocking, so it isnt really recommended for discord bots, and the async driver (motor) isnt very popular

everyone says it is trash lmao, i havent used it personally though. i have mongodb running locally on the VM that hosts my bot, and my bot is just in my server with a few people, so the data is small - using pymongo the queries are quick enough that it doesnt feel like it is being blocked

[modest ledge]

rip lol

seems like json is still the better choice for me, idk why sql isnt working out for me

[proven arrow]

@modest ledge Well remember, JSON is not a database, and is not made for storing data.
JSON was not made to store data, handle concurrent connections, heavy use, but instead for a way to represent or transport the data.
Unless your use case is to hold really little amount of data, which you will read/write to not so frequently, you should be using databases.
Databases allows you to query data by writing queries to it, lookup data is fast, makes abstraction/modelling much more simpler with different tables and the ability to do things like join/link data, can handle large data/requests, easier to scale, consistent/durable, they are concurrent and allow multiple programs to to use it at the same time, allows you to keep reliable and more consistent data, and the advantages go on.

A quick search on the web will give you much more detailed answer as to why databases would be better

[modest ledge]

ik man and i really tried to switch to something else but its just not working

[remote burrow]

Hey is anyone running charts on their mongodb database?

The setup requires a docker installation and I've been racking my head over why I can't make a connection to the database on installation. And I've done this with both remote and local servers

[modest ledge]

@remote burrow watch some tutorials on YouTube, it's not that hard

[remote burrow]

I've been stumbling on this connection issue since last night. I wouldn't consult if it were so easy :c

[scarlet atlas]

Can anyone help me with this error I get?

AttributeError: module 'motor' has no 'motor_asyncio' attribute

[hasty juniper]

async with db.execute(f'SELECT GuildAllowNSFW FROM GuildSetting WHER GuildID = (guild.id)') as cursor: that bad way, how can i do that correct

@long zephyr

😦

[cinder dome]

You dont use f strings in sql statements

Are you using aiosqlite?

@hasty juniper

[hasty juniper]

Yes its asqlite

[cinder dome]

async with db.execute("SELECT GuildAllowNSFW FROM GuildSetting WHERE GuildID = ?", guild.id) as cursor: try that

Also there’s simpler ways to use aiosqlite like how I did

With functions in a database.py file, and then make the connection in the main file

[hasty juniper]

Thx

[cinder dome]

Check my git if you want any reference

[tepid prairie]

Hi i am v new to Machine learning. But i want to use it for a project im working on

I have a data set that will contain many entries. Each entry is a sequential pattern like
aabcdaa ababcda cccdada

What tool should i use to predict outcomes live

Like as the user inputs the script tries to predict the next move

If you know how i can get started with this could you please ping me so i get a notification.

If ur interested in what im making im hoping to make a program that predicts the enemy teams behaviors in valorant depending on the way they pushed bomb sites. (:

[torn sphinx]

@tepid prairie I dont know that much about machine learning, but I'm just wondering, do you intend to use this for a single game? Because (while I'm not sure), using 13 inputs for a single team would not be enough to find a pattern

[tepid prairie]

@torn sphinx ye so i am trying to use their initial plan (rush slow push split) the site the intended to rush. If the planted the bomb or not, which site they planted on (maybe denoting the success of their initial plan) and whether they won the round or not. This is for each round. So it would be like 5 points of data per 13 rounds. Maybe more in the future accounting for econ and how many players were left on either side by rounds end. But the script ive written requires manual inputs for every round. So i input as i play to produce the data set. Im keeping things small rn bc idk how to automate it

Hopefully this will be enough to make some usefull information

But maybe not and im hoping to find more ways to figure it out

[torn sphinx]

Well do you plan on using data from a bunch of games from different teams or trying to find a pattern from one game and one team?

It would be interesting to see if the bot can pickup some sort I'd pattern all humans share, not just one team of players

[tepid prairie]

Oooo

Gotcha

[torn sphinx]

Plus you'd be able to get a lot more data

Which is important for machine learning

[tepid prairie]

Sorry i didnt understand the question. Riot has apis but idk how deep they go and how much info i can use as of rn.

Rn this is just developed around my games i played and have inputted

So small pool

As of rn

[torn sphinx]

Ah okay, but I think itll be manually inputted anyway

Which is fine

That's a very interesting project tho

[tepid prairie]

Thanks

[torn sphinx]

yeah man

I wish u luck

[celest matrix]

hello. I'm getting an sqlalchemy error where module 'sqlalchemy.ext' has no attribute 'declarative'. Encountered that when trying to generate a new alembic migration

I'm guessing it might be because alembic is 0.6.3 on my Ubuntu server with no way to update it

but i can't use alembic installed through pip

so I'm pretty stuck here lol.

[harsh pulsar]

@celest matrix you can't use a virtualenv/venv?

[celest matrix]

It is a venv.

The project is actually 3 docker containers, sql, server, and client. The Server container is what im focusing on since that is what handles database creation and interaction. Im making a migration for it using a dummy container but the error comes up

[meager vine]

Just realised there is this DB specific thread, wondering if anyone could take peek at my Q in #help-broccoli

[harsh pulsar]

!d g contextlib.closing

[delicate field]

`contextlib.closing`

contextlib.closing(thing)```
Return a context manager that closes *thing* upon completion of the block. This is basically equivalent to:

```py
from contextlib import contextmanager

@contextmanager
def closing(thing):
    try:
        yield thing
    finally:
        thing.close()
```  And lets you write code like this:

```py
from contextlib import closing
from urllib.request import urlopen

with closing(urlopen('http://www.python.org')) as page:
    for line in page:
        print(line)
```  without needing to explicitly close `page`. Even if an error occurs, `page.close()` will be called when the [`with`](../reference/compound_stmts.html#with) block is exited.

[harsh pulsar]

@meager vine check it out ^

[meager vine]

@harsh pulsar Yeah no, I can make a context manager... But pyodbc cursors are themselves usable with a context manager, and if you simply subclass them you lose the ability to run that context manager I think...

[harsh pulsar]

you shouldnt lose the context manager ability by subclassing

if the class implements __enter__ and __exit__ it is a context manager

so as long as the parent class has those methods, the child class should also be a valid context manager because it will inherit those methods

[meager vine]

I know that. Rather, if you override the pyodbc.connection.cursor method to make it return a subclassed cursor class, you lose the ability to use that in a context manager

I can't actually even point to this in the pyodbc source code because it's all cpp files which I struggle to follow properly

[harsh pulsar]

ah

that's kind of a janky solution anyway, monkeypatching the cursor method

[meager vine]

Yeah that's what I think

[harsh pulsar]

they dont make the Cursor class publicly importable? and they dont provide the option to create a cursor factory?

[meager vine]

I haven't seen it documented anywhere

As I say I had a brief attempt to look at the source code but since its all c extensions and I only ever learned python I struggled to follow/find the relevant part

[harsh pulsar]

ah

so wait. why are you replacing pyodbc.connection.cursor exactly?

have you considered wrapping instead?

[meager vine]

That approach comes from this google thread and who I believe is the author of pyodbc: https://groups.google.com/g/pyodbc/c/BVIZBYGXNsk

[harsh pulsar]

so i guess i still dont understand

for clarity, what functionality does your subclass add, that isnt already part of the native Cursor class?

[meager vine]

Nothing too fancy, mainly the ability to get results as a list of dicts

I figured I can add more functionality later

But because of how hacky subclassing/wrapping this felt I wanted to see if there was a better solution

[harsh pulsar]

first i'll offer that maybe you dont need to subclass at all, and just write a function

https://repl.it/@maximum__/misc-junk#srl_utils/sql_addenda.py check out cursor_fetchall_dict and friends

[meager vine]

Yes that's pretty much my class method...

[harsh pulsar]

second, i recommend taking a look at this blog post https://glyph.twistedmatrix.com/2017/05/the-sororicide-antipattern.html

[meager vine]

I just figured it was neater to add it as functionality directly to the cursor since its input is always a cursor anyway

[harsh pulsar]

class CursorWrapper:
    def __init__(self, cursor):
        self.cursor = cursor

    def fetchall_dict(self):
        # ...

    def __enter__(self):
        return self

    def __exit__(self, *exc_info):
        self.cursor.close()


class ConnectionWrapper:
    def __init__(self, connection):
        self.connection = connection

    def cursor(self):
        return CursorWrapper(self.cursor())


conn = ConnectionWrapper(pyodbc.connect(...))
with conn.cursor() as curs:
    curs.cursor.execute(...)

explicitly wrap only the minimum methods you need

otherwise just delegate to the underlying object

at least, that's what the blog post i linked would recommend

[meager vine]

Right, but I chose not to do that because the functionality of your __enter__ and __exit__ are not preserving the normal behaviour of with with conn.cursor()

Basically I wanted to be able to wrap connection.cursor but delegate context management to the underlying object

[harsh pulsar]

    def __enter__(self):
        return self.cursor.__enter__()

    def __exit__(self, *exc_info):
        self.cursor.__exit__(*exc_info)

you can do that too of course

[meager vine]

Aha I think that might be what I was after

Kind of thrown by the assumption that connection.cursor() was a method that didn't have an __enter__ or __exit__ which I guess was dumb

[harsh pulsar]

to be clear, connection.cursor is a method that returns an instance of some Cursor class

and the Cursor class is what has the enter and exit methods

[meager vine]

I think I just brainfarted on making the connection here

and not the db connection lol

[harsh pulsar]

btw it looks like Cursor is exported publicly, have you tried just importing import pyobc and typing pyodbc.Cursor?

note that i said "looks like" because i dont really know how C extensions work

[meager vine]

But how would that help?

[harsh pulsar]

then you can just subclass it

[meager vine]

I think we both agreed though that that was not a great idea haha

[harsh pulsar]

or can you not subclass classes defined with c extensions?

no, subclassing is fine

it inherits everything

wrapping and then trying to delegate everything is what makes a big mess

[meager vine]

But if I subclass the Cursor class I still have to override the connection.cursor method to return the subclassed cursor

[harsh pulsar]

right, i was about to say

constructing a subclassed cursor might be a problem

[meager vine]

And that becomes even more messy than simply wrapping I think

[harsh pulsar]

indeed

[meager vine]

But also since all the functionality I want is really residing on a Cursor rather than the connection, I don't see why I couldn't just wrap the cursor and make a Client() class which takes and exposes the cursor, but also adds some handy methods

so you can still do client.cursor.execute() but also client.select_as_dicts() or whatever

[harsh pulsar]

its always nice when the main developer holds weird and wildly idiosyncratic attitudes towards programming

I understand you want to use the dictionary approach to make it match
the others, but I think the ['xxx'] approach is a very bad match for
Python. Literally all non-C Python code is a dynamic lookup in a
dictionary based on what is after the period. For example, "print
sys.argv" creates a dictionary lookup of sys.dict['argv']. I
think the extra brackets and quotes are just clutter. The only thing
I can think of is they don't want column names conflicting with their
row methods, to which I say, don't make so many row methods!

@meager vine that's what i was suggesting

wrap, but don't smother

i dont think i could disagree with mklee anymore on this

[meager vine]

Haha ok, I think that's what my question ultimately was: what do we think is the best solution to providing convenience methods for cursors/connections

[harsh pulsar]

[] access is about namespace separation, not eliminating dynamic hash table lookups... how do people get these weird ideas

yeah i think your method makes sense

[meager vine]

I just wish everyone would use postgres so I only had to bother with psycopg2 which is easily the most useful python SQL client imo

[harsh pulsar]

hah don't we we all

i'm still waiting for the binary postgres client to be split off from asyncpg so it can be used with non-async code too

psycopg2 uses a text protocol apparently, whereas the binary one is supposedly a lot more efficient

[meager vine]

For most of what I do the efficiency of the python db layer is pretty inconsequential tbh

[harsh pulsar]

same

its more just a nice thing to have

[meager vine]

Normally the queries or postprocessing are the bottleneck

[harsh pulsar]

postgres exposes a binary protocol, why not use it?

but yeah exactly

[meager vine]

Only exception is probably parameterised bulk inserts

But execute_values seems to be reasonable at speeding that up

I have another question which maybe you can help with and it's something I understand wayyy less

I have a message consumer which waits on periodic messages, processes them, then inserts them into the DB. In this case making the connection is actually somewhat of a slow point, so ideally I wanted to open one connection and reuse it. However sometimes it can be hours between messages as opposed to seconds (they're like buses) and the connection can get dropped. I haven't yet come up with a good solution for recovering the connection and wondered if you had any thoughts?

[brazen charm]

It really doesn't matter

It just means if something fucks up between the database will rollback further or shorter

[prime chasm]

!shell

[eternal raptor]

In SQL query earlier ORDER BY or WHERE ??

[torn sphinx]

@burnt turret so i’m trying to insert this data into the db whenever the bot joins a guild.

@client.event
async def on_guild_join(guild:discord.Guild):
  data = {"id": guild.id, 
  "guildName": guild.name,
  "prefix": "v!"}  # data i want to insert
  db=cluster["Data"]
  db["prefixes"].insert_one(data) # This is how i insert right?
  print("Joined new guild") 

And when the bot joins a guild, it should print "Joined new guild". But it isn’t working, can you help when you’re free? Thanks :)

[burnt turret]

your insert syntax seems to be fine, is there an error message?

[eternal raptor]

SELECT * FROM Customers
WHERE ID >= 5
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Countr;
WHERE ID >= 5;

Which version is correct?

[torn sphinx]

your insert syntax seems to be fine, is there an error message?
@burnt turret nope

it doesn’t insert anything into the db.

[eternal raptor]

SCRUZ could you help me? ...

[torn sphinx]

sorry, I’m new to database. I’m asking for help from Ares 😅

[eternal raptor]

ok, sorry 🙂

[torn sphinx]

maybe you could check #❓｜how-to-get-help to get help

[eternal raptor]

problem solved

[burnt turret]

@torn sphinx are the rest of the queries running fine? maybe theres some connection issue with atlas?

lemme just look up the syntax real quick

also i think you should name the "id" field as "_id" -> pretty sure mongo by default makes an ID for each document using that second name, and by using _id you can override that with the id you are providing

also i dont think you have to type hint guild: discord.Guild there (it shouldnt be raising the issue, but still)

[torn sphinx]

pretty sure there’s no connection issues. i’ll make those changes

[torn sphinx]

i changed "id" to "_id" and joined the bot to another server. But nothing happened. I double checked the connection and it was fine. It didn’t print Joined new guild and also didn’t send anything to the db. And no error was raised too.

Am I inserting it properly? Is the insert_one(data) part correct?
@burnt turret

[torn sphinx]

Can someone take a look into how I build a dynamic SQL query for a REST API and see if you notice a chance for a possible SQL injection?

def get_soundtracks(self, limit, offset, **kwargs):

        def transform_to_dict(seq):
            keys = ("soundtrack_id", "title", "anime_id", "anime", "artist_id", "artist",)
            return dict(zip(keys, seq))

        allowed_keys = ("title", "artist", "type", "season", "position")

        where_q = ""
        for key, value in kwargs.items():
            if key in allowed_keys:
                where_q += f"AND {key} = %({value})s\n"
            else:
                del kwargs[key]

        query = ("SELECT soundtracks.soundtrack_id, soundtracks.title,\n"
            + "animes.anime_id, animes.title,\n"
            + "artists.artist_id, artists.title\n"
            + "FROM soundtracks, animes, artists\n"
            + "WHERE soundtracks.anime_id = animes.anime_id\n"
            + "AND soundtracks.artist_id = artists.artist_id\n"
            + where_q + '\n'
            + "ORDER BY soundtracks.title, animes.title, artists.title, soundtracks.soundtrack_id\n"
            + "LIMIT %(limit)s\n"
            + "OFFSET %(offset)s\n"
        )

        with self._conn.cursor() as cur:
            cur.execute(query, {'limit': limit, 'offset': offset, **kwargs})
            return tuple(transform_to_dict(seq) for seq in cur.fetchall())

[burnt turret]

i changed "id" to "_id" and joined the bot to another server. But nothing happened. I double checked the connection and it was fine. It didn’t print Joined new guild and also didn’t send anything to the db. And no error was raised too.

Am I inserting it properly? Is the insert_one(data) part correct?

@torn sphinx yeah the insert_one can be taking a dictionary, the way you did. Are the rest of the commands which depend on your database working?

put some other print statements at the very start of this event, see if it is even being invoked properly

also check the db/collection names (probably not the issue)

https://docs.atlas.mongodb.com/tutorial/insert-data-into-your-cluster/
the docs also show an example where they insert a dictionary similar to the way they have done

the docs are using dot notation (db.collection) instead of the dictionary style (db["collection"]) that you are doing, but as far as i know that shouldn't really be a problem, they're both said to be equivalent

[ionic marsh]

Is it possible for databases to store objects? For example, if I have a class named Person with a name and age attributes, could I put the object in a database?

[mental quiver]

Also, from everything ive been told, using json as a database (im aware it wasnt made to be a database) is fine for things like settings and stuff like that, right? Things that are unlikely to get updated super quick, maybe 5 times in a second in the very worst case?

And exactly how bad would processes be delayed if I used MySQL rather than Mongo or Postgre?

Please @ me when answering 🙂

[torn sphinx]

put some other print statements at the very start of this event, see if it is even being invoked properly
@burnt turret just figured it out. I had 2 on_guild_join events. It works now :)
Thank you for the help

[burnt turret]

haha that makes sense

[torn sphinx]

xd

[quaint tiger]

Is it possible for databases to store objects? For example, if I have a class named Person with a name and age attributes, could I put the object in a database?
@ionic marsh you can serialize them to JSON and store in a JSON field.

or a NoSQL db

[torn sphinx]

@burnt turret sorry for the ping 💀 You can reply whenever you’re free :)
I can do this to get the prefix right?

async def get_prefix(guild):
  db = cluster["Data"]
  data = db['prefixes'].find_one({"_id": ctx.guild.id}) # find prefix by server ID
  prefix = data['prefix']
  return prefix

now I can put client=commands.Bot(command_prefix = get_prefix) right?

[fleet kiln]

Suppose I have a boolean variable 'is_Crawled' and once the variable is set, Is there a way in mongo db to automatically reset the variable after 24 hours?

[quaint tiger]

AFAIK, no. You can use a cron or celery script to reset it though.

[mental quiver]

#databases message

[burnt turret]

@torn sphinx command_prefix = get_prefix this only points to the function get_prefix and not what it is returning. To get the prefix, you would have to pass the guild to it like get_prefix(guild).
But that function you've written wouldn't work, as ctx is undefined inside it but you used ctx.guild.id.

[torn sphinx]

oh yea lol

[burnt turret]

I'm not exactly sure how custom prefixes for each server works exactly, you could ask that in the d.py channel

[torn sphinx]

ight thanks :)

[burnt turret]

there's some coroutine associated with setting the prefix i think, but i dont remember which

[torn sphinx]

@mental quiver JSON is used for serialization. Meaning you store small amount of data just to transfer over a network (sometimes even as a configuration files). JSON is fine if you are using it to persist state of your app between multiple runs (like settings of your app changed by user). But if you are planning to read an write frequently and load data efficiently (as in retriving only what you need or querying by a key/other fields) you'll need a db. If your data is like worth a gigabyte, you'll just run out of memory with JSON, while with a DB you get in memory what you actually need at the moment (it's your responsabilty to dispose it once you're done with the data)

I am going to assume you are looking for persistance solution for something like a discord bot. Learn to use SQL, you'll really benifit from it.

And exactly how bad would processes be delayed if I used MySQL rather than Mongo or Postgre?
I have never used MySQL or Mongo. But I hear Mongo is easy to configure among them all. (Mongo is not an SQL database, it stores data in a "JSON like" format) I'd recommend you to look at atleast one DB that is actually an SQL but entirely your choice, you can use mongo.

[mental quiver]

Alright, I appreciate the feedback

[cinder dome]

Would rather use sql then mongo

Sqls easy to learn anyways

[burnt turret]

eh its personal choice i guess. i've learnt both and i'm a bit partial to mongo.

but sql dbs seem to have the better async drivers

[solemn ridge]

Anyone know how I can solve the following?

https://hastebin.com/yujuvuguta.sql

Using MacOS Catalina

Its an error I am getting while installing mysql-client

[jolly tiger]

What is the foremost book on databases?

[oblique ice]

Hey, so when using MongoDB Compass there's an option to edit a document and insert a new field after a selected field (and the field gets added in between if there's something after that) but when adding a field using the driver's update_one function it gets added in the end (same goes for the $addField aggregation operation). Is there a way to insert a new field in between fields using the driver or MongoDB Compass to multiple documents?

[quaint tiger]

What is the foremost book on databases?
@jolly tiger can you be more specific?
I really like "7 databases in 7 weeks" (2nd edition) ... it gives a broad overview of different DBs. If you want something more geared towards one DB (e.g. postgres), there are certainly more tailored ones.

[brazen charm]

no, its NoSQL its a structureless database, it doesnt matter which order they're in. Normally its organised alphabetically by mongo but it wont keep any sort of field order directly @oblique ice

[oblique ice]

ohhh i see thanks

[cinder dome]

Basically I don’t understand why I can’t warn a user more than once

One Moderator should be able to warn a user more than once

These are the tables: https://mystb.in/DiversePromotingLift.sql
Functions: https://mystb.in/TransportationMacroPerspective.python

Ping on reply

[proven arrow]

Your mod_id is a primary key with unique constraint @cinder dome

[cinder dome]

Yeah I changed it

But idk what it should point to

The message id?

[proven arrow]

warn_id can be unique

I would make the warn_id the primary key

[cinder dome]

warn_id SERIAL PRIMARY KEY UNIQUE?

I got it now

[cinder dome]

How would I go about deleting a specific warn?
The warn related tables: https://mystb.in/OmanExecutivesProspects.sql
My functions: https://mystb.in/FieldBrasFour.python

[quaint tiger]

self.execute("DELETE FROM warns WHERE warn_id = ?", warn_id) should do the trick.

[scarlet atlas]

Can anyone help me with this error I get?

AttributeError: module 'motor' has no 'motor_asyncio' attribute

[cinder dome]

self.execute("DELETE FROM warns WHERE warn_id = ?", warn_id) should do the trick.
@patryk.tech#4359 yes but how would they know what the warn id is

Plus when I try printing it out, is prints out as None

[scarlet atlas]

Can anyone check out my error?

[gilded cloak]

Hi, I just started working with MongoDB, I've got a Python bot for Discord running but I've got sections that I want the user to fill in with commands this would store their data in the db (correct me if this is the wrong channel to ask!)

[cinder dome]

Use sql before its too late

@gilded cloak

[gilded cloak]

hahah

It's not to late 😆

[cinder dome]

but if you're planning on putting this bot in many servers, mongo isnt the best option i've heard, as pymongo is blocking so it could freeze your bot as it waits for responses (almost negligible when the queries are fast, but can get annoying), and the async driver for mongodb is called motor-and everyone hates it lmao

Start using aiosqlite or asyncpg

[gilded cloak]

Would you be able to assist at all because I'm pretty new with this side of bot work

[hushed cape]

hi

[cinder dome]

what do you want

[meager vine]

SQL vs Mongo shouldn't be a decision based on your use case for interaction but rather the structure of your data

The two serve entirely different purposes

[gilded cloak]

so within discord an admin can trigger a command that will allow them to change: Embed colour (left side of embed), the channel the embed is allowed to be posted in, thumbnailand title

Per server this would put data in a db

I'm not sure if it has to link the server ID to it or which way to go

so data is not always pushed to the db only on the first setup and if details are changed

besides that it's only data being pulled off of the db

[meager vine]

That does sound like it's a better fit for traditional SQL than NoSQL (e.g. Mongo)

[gilded cloak]

Alright!

[meager vine]

You effectively have 1 table with 4 columns but the sounds of it?

And someone could periodically update the value in any of those 4 columns?

[gilded cloak]

I think so, that would be 1 table per server, with the columns for colour channel thumbnail title

[meager vine]

Yeah that sounds fine

[gilded cloak]

sqlite3 is ok to use?

[meager vine]

although is there any particular reason to have the same table structure for different servers rather than putting them all in a single table with a server column?

sqlite3 is ok to use?
@gilded cloak If it's not likely to get very big then yeah absolutely

[gilded cloak]

In my head (correct me if I'm wrong)
I need one table per server, within 4 columns
this will allow all the servers to use different details in the 4 columns and the bot recognising the server table with the server ID in it for that specific server

so my bot is on two servers e.g. yours and mine,

there will be 2 tables in the db
each table has 4 columns with our custom data in it for colour channel thumbnail title

[cinder dome]

@gilded cloak If it's not likely to get very big then yeah absolutely
@meager vine wait wdym by “big”?

[gilded cloak]

colours are hex codes, thumbnails are urls and the other two are text based fields

so shouldn't make the db too big

I hope I'm making sense lol

[meager vine]

@cinder dome Well tbh SQLite is going to be able to handle anything this guy builds, since it can technically handle 100TB +

I just would just usually use Postgres for anything beyond the quick and dirty because why not?

[gilded cloak]

and I'm still new with the db part of bots, I found this guide somewhere which included

        DB_NAME = "example"
        db_path = os.path.join(os.path.abspath(os.getcwd()), DB_NAME + ".db")
        self.db = sqlite3.connect(db_path)
        self.db_cursor = self.db.cursor()
        self.db_cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            discriminator TEXT
        )
        """)```

for the connection part

When changing the create if not exists and name text / discriminator text part I could use this?

[cinder dome]

@cinder dome Well tbh SQLite is going to be able to handle anything this guy builds, since it can technically handle 100TB +
@meager vine uh what about mine though? So far I have a welcome/goodbye system, and a Warn system

And I plan on adding lots more

The welcome/goodbye system is able to change the channel and text

[craggy jackal]

Does anyone know the best way to pull a column from a row given a primary key

In SQLAlchemy

[meager vine]

You can almost certainly get by with SQLite if what you are using it for is mainly storage of data, and those fields are simple text/numeric and the number of users accessing the db directly is low

[cinder dome]

So its not good if my bots gonna be in servers with a lot of members

[craggy jackal]

For example, I can do something like this:

config = session.query(cls).get(guild.id)
channel = guild.get_channel(config.log_channel_id)
``` but I don't think `get` can have anything in the query other than the entire table.

[cinder dome]

Or if the bot is gonna be in tons of servers

I mean you can check my github too to see what I have, cause I wanna know before I keep adding more

[meager vine]

I have no idea how discord bots work tbh

Just because the bot is in multiple servers I don't think that's a huge issue, although if they all need to write to the db a lot then you might hit issues due to SQLite's concurrency restrictions

@craggy jackal is guild a table there?

[craggy jackal]

So basically I am defining class methods on my tables for common functions I need to perform, for example

    @classmethod
    def get_log_channel(cls, guild: discord.Guild) -> discord.TextChannel:
        """Get a guild's log channel."""
        with session_scope() as session:
            config = session.query(cls).get(guild.id)
            return guild.get_channel(config.log_channel_id)

I think I need to use filter to do what I want

I don't think I can use get despite guild.id being primary key

Because I think the full table needs to be the query parameter

[meager vine]

what is cls? The guild table?

[craggy jackal]

Right, this method resides in the table class

It's a guild configuration table

[meager vine]

I'm not sure if class methods for tables are the way to do things in sqlalchemy tbh

[craggy jackal]

I have a lot of common functionality that gets used in multiple different modules and it made the most sense to me to add the interface to the table itself.

So whatever needs it can just import the table

And do GuildConfig.get_log_channel(guild) for example

Rather than fuzzying the business logic with the SQL logic

[torn sphinx]

Hey guys, what is an easy database type to use for discord.py?

[gilded cloak]

sqlite3 I think from what the guys above discussed

[craggy jackal]

Like Welsh, I think I could do this:

    @classmethod
    def get_default_role(cls, guild: discord.Guild):
        """Get a guild's default role."""
        with session_scope() as session:
            role_id = session.query(cls.default_role_id).filter(cls.id == guild.id).scalar()
            return guild.get_role(role_id)

[torn sphinx]

Alr

[craggy jackal]

It is guaranteed to be a one row result

[meager vine]

I have no idea if that is a good way of doing it is what I am saying

[gilded cloak]

I'm just going to pretend I know

lol

[craggy jackal]

I will run with it for now.

I am going to generalize a lot of the methods with this getter I made

@classmethod
    def _get_col(cls, guild: discord.Guild, col_name: str):
        """Returns the value of the column for the specified guild."""
        with session_scope() as session:
            col = getattr(cls, col_name)
            val = session.query(col).filter(cls.id == guild.id).scalar()
            return val

So I can just make methods for getting each column.

    @classmethod
    def get_default_role(cls, guild: discord.Guild):
        """Get a guild's default role."""
        default_role_id = cls._get_col(guild, 'default_role_id')
        return guild.get_role(default_role_id)

Then this is how it will be used.

It would be even cooler to inject the functionality into the discord.Guild class itself

So you could do guild.default_role

[gilded cloak]

Well I think I will be fine, I just need to work out how to link the command to put data in the correct place, and then pull from the correct place for the right people 🙂

Created correctly so that's good

[meager vine]

@craggy jackal I'm a bit confused. Is discord.Guild a table?

[craggy jackal]

I am using the discord.py framework, it is a python library for making discord bots

Basically it is the object I use as a primary key in my guild (server) configuration table.

Every server has a unique id.

[gilded cloak]

this server's unique ID is 267624335836053506

[meager vine]

@craggy jackal I'm not going to tell you not do it, because frankly I very little sqlalchemy experience, but I would caution that I don't think I've seen anything in the docs that shows or recommends using the session object within a table class method. I've only just started playing around with SQLAlchemy though so if you find some good examples I'd be keen for the link

[craggy jackal]

Yeah I will need to look into it more

I need to find a big project that uses sqlalchemy

But it seems to work and I like the interface and it seems clean so?

Not sure.

[torn sphinx]

not_bruh_str = open("not_bruh.txt", 'r+')

def not_bruh_t(id):
    if f"{str(id)}: True" in not_bruh_str.read():
        return
    elif f"{str(id)}: False" in not_bruh_str.read():
        not_bruh_str.replace(f"{str(id)}: False", f"{str(id)}: True")
    else:
        not_bruh_str.write(f"{str(id)}: True")

hey guys, im trying to have this function write to a text file, but it doesn't write and doesn't show erros. anything im doing wrong?

i tried using json at first for this but it didn't work out, so i tried making a "database" in a similar way but with a text file

any suggestions?

[formal cosmos]

you did not call the function not_bruh_t at all

also you really should use a context manager when opening files

[craggy jackal]

And not_bruh_str is actually a file object not a string

So not_bruh_str.replace is invalid

Your function should open the file (with context manager as stated and do an operation on it)

[jolly tiger]

@quaint tiger General Database knowledge.

[torn sphinx]

@formal cosmos i do call it, just later in my code, and i used print breaks to find that the function will run, but just not write

what is a context manager?

im sorry, im quite new

what database should i use for a discord bot, first time using/learning databases

[formal cosmos]

You can use a context manager like this:

with open("not_bruh.txt", 'r+') as not_bruh_str:
    # Do stuff here

The advantage of doing so (in this case) is that you do not have to manually call file.close() at the end

Basically what a context manager does is handle resource management (such as closing a file) automatically

[torn sphinx]

ohhhhh i've seen that before

ah okay

[formal cosmos]

That way you run no risk of forgetting to handle the edge cases (such as if you get an error when processing the file, you still have to close the file)

[meager vine]

@torn sphinx You also call read() multiple times. The second time it will be empty...

[torn sphinx]

wait

you're joking

what

that must be it

because it always goes to the else

can i ask, why would i t do that?

[meager vine]

Because that's the way read works: https://stackoverflow.com/questions/3906137/why-cant-i-call-read-twice-on-an-open-file

[formal cosmos]

When you call read() the file object iterates through all of the lines in the file and stays at the end of the file.

[meager vine]

Also you probably want to move this out of the databases chat and into a dedicated help ( #❓｜how-to-get-help )

[torn sphinx]

yeah see i was thinking someone would suggest not to use this as a db and use sqlite or something

just tell me this is a bad idea

lol

[meager vine]

Wait, sorry, you are using this text file as a database?

Yeah, don't do that.

[torn sphinx]

sort of

yeah...

i was using json

but it wasn't working

and the .json is so similar to like a txt file i could just read and write to (i thought)

[meager vine]

What do you mean "wasn't working"

[torn sphinx]

here i saved the code

i don't remember

oh yeah

ok so one sec

[meager vine]

{"id1": true, "id2": false} would be fine as JSON version of this

[torn sphinx]

i know but

[meager vine]

But if you are repeatedly reading and updating then probably just make an SQLite table with id and status or comething

[torn sphinx]

when i was trying to parse the json to find if, say, <guild1> was true, it just wouldn't work

no errors

i mean i thought my parsing was fine

lemme get the code

okay so at first it was

f"'{ctx.guild.id}': False" in not_bruh_str
``` as the conditional i think

and then i tried with keys

i think

[meager vine]

wut

Wouldn't you store the JSON like: {"id1": true, "id2": false} then just do something like:

with open('my_json.json', 'r') as infile:
    data = json.load(infile)
my_id = 'id1'
if data[my_id] is False:
    data[my_id] = True

Again, think you might want a dedicated help for this

Seems your question is broader than a database question

[torn sphinx]

thx @meager vine

[quaint tiger]

@quaint tiger General Database knowledge.
@jolly tiger then yeah, seven databases in seven weeks is great. It covers Redis, Neo4J, CouchDB, MongoDB, HBase, Postgres, and DynamoDB, so SQL, NoSQL document stores, Key-value stores, Graph Databases... SQL is the most commonly used (and has been for the past 50 years or something), and NoSQL (Mongo) is very popular among startups. If you learn Postgres, you can easily learn SQLite, MySQL/MariaDB, MS-SQL, etc.

Other than that, maybe the other thing worth learning is IndexedDB, which is used by browsers.

That books gives a great overview of all of them, and you can always focus on learning what interests you in more details later.

https://www.oreilly.com/library/view/seven-databases-in/9781680505962/

[tight crow]

can someone help explain the meaning of redundant data? does it means multiple copies should not exist or multiple copies does exist?>

[proven arrow]

It’s when multiple copies of data do exist in some data storage place.

[quaint tiger]

can someone help explain the meaning of redundant data? does it means multiple copies should not exist or multiple copies does exist?>

Table users:
User ID | User Name | User Address
Table orders:
Order ID| User ID | User Name | Product ID

Simple / dumb example, but you should have a single source of truth (in RDBMS) - user name should be stored in users and not in orders. orders should just have a foreign key to User ID.

[jovial rose]

I'm trying to insert a dictionary value into a request using an f-string.
Setting the variable "id" to the dictionary value and then using "id" in the f-string works fine.

import requests
query = input('> ')
articles = requests.get(f'https://subnautica.fandom.com/api/v1/Search/List?namespaces=0&query={query}').json()
id = articles['items'][0]['id']
article = requests.get(f'https://subnautica.fandom.com/api/v1/Articles/AsSimpleJson?id={id}').json()
print(article)

However, if I directly use the dictionary value in the f-string I get a syntax error.

import requests
query = input('> ')
articles = requests.get(f'https://subnautica.fandom.com/api/v1/Search/List?namespaces=0&query={query}').json()
article = requests.get(f'https://subnautica.fandom.com/api/v1/Articles/AsSimpleJson?id={articles['items'][0]['id']}').json()
print(article)

Why is this?

File "C:\path\get_articles.py", line 4
    article = requests.get(f'https://subnautica.fandom.com/api/v1/Articles/AsSimpleJson?id={articles['items'][0]['id']}').json()
                                                                                                      ^
SyntaxError: invalid syntax

This is the error I get if I try.

[hardy pine]

cause it really invalid

f' ['Something'] ' ❌

isn't valid

f' ["Something"] ' or f" ['Something'] " ✅

[quaint tiger]

Either use different quotes in f"foo {d['k']}" or use triple quotes for the f-string f"""foo {d["k"]}"""

[jovial rose]

ahh

Works now, hehe.

[quaint tiger]

i'm being [redacted] now but can't work out a proper mariadb SELECT query.
Step one: try to avoid using ableist language 🙂

[lucid beacon]

:(

i used SELECT client.id as 'id', software.name as 'sw_name' from Client JOIN Software.id=Client.software_id but cant work out how to get it to work for both columns

got it working, had to do
Select sw.name as 'sw_name', future_sw.name as 'future_sw_name' From Client JOIN Software AS sw ON sw.id=Client.software_id JOIN Software AS future_sw ON future_sw.id=Client.future_software_id

[gilded cloak]

Hi

[gilded cloak]

I've got a question about how to set up sql in a python discord bot

[quaint tiger]

More specifically? SQL is just a language...

You would need to add some kind of database (sqlite, postgres, etc.).

[gilded cloak]

I'm using sqlite3

@quaint tiger

I've created the table // columns but I'm not sure how I get admin commands in discord to interact with them

so e.g. when the bot is invited to a server it creates a table with the server ID and 4 columns that have no value in them, when the admin than uses... $setchannel 12345455553 would create a value in the channel column under that specific server ID

if that makes sense lol

[tidal turtle]

is this error because my program is connected to the database for over 24 hours without a reconnect? (Module: mysql-connector-python)

Lost Connection to MySQL serverat 'MYDOMAIN:3306', system error : 32 Broken pipe
(MYDOMAIN is not the real domain)

[jolly tiger]

@quaint tiger thanks

[low iron]

Are there any good resources to get started with SQLite? Never used a database before

[harsh pulsar]

this channel needs some good pinned resources

ill ask around if anyone has a good sql-with-sqlite intro book or guide

[quaint tiger]

https://www.sqlite.org/docs.html
I'd recommend Overview/About SQLite: https://www.sqlite.org/about.html
Followed by Tools/Command=line Shell: https://www.sqlite.org/cli.html

But their docs are top-notch tbh

[low iron]

Ah
Thanks

[harsh pulsar]

@quaint tiger i find their docs hard to search tbh

@low iron i recommend using https://sqlitebrowser.org/ if you want a graphical interface to using sqlite databases

it can be very helpful for running queries and seeing what they do

this is a bit of a dense "getting started" document, with a lot of information that isnt really helpful at the start, unless you already know and understand sql

[mossy blaze]

Hi. What is the easiest and most efficient way of preventing SQL Injection in a Python script?

[low iron]

Oh, I see
I'll check it out, thanks 👍

[quaint tiger]

Hi. What is the easiest and most efficient way of preventing SQL Injection in a Python script?
@mossy blaze prepared statements

As a general rule for SQL.

Implementation will depend on your libs

[mossy blaze]

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"

so this is one of them. how would I replace the question marks with what I want in Python?

[harsh pulsar]

@mossy blaze sql interface libraries allow you to pass "parameters" to the .execute method

curs = conn.cursor()
curs.execute("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)", (val1, val2, val3))

[mossy blaze]

oh ok cool

thanks 😄

[harsh pulsar]

https://www.python.org/dev/peps/pep-0249/ here is the "db-api 2.0" spec that most sql libraries in python follow, at least more or less

you rarely need to manually construct prepared statements explicitly, ive never needed to do it

[minor ruin]

https://bobby-tables.com/

[harsh pulsar]

however @mossy blaze many database libraries diverge from that spec in one way or another, so make sure to read the docs for the particular library you are using

[mossy blaze]

cool. thanks for the help, I really appreciate it 😄

[left scaffold]

Hi is this near correct for inserting into a db

    @commands.command()
    @commands.guild_only()
    async def suggest(self, ctx, *, suggestion):
        suggestions = self.client.get_channel(740091642698006558)
        async with aiosqlite.connect(r"C:\Users\dylan\OneDrive\Documents\GitHub\bots\slio\slio\cogs\suggestions.db") as db:
            await db.execute(f"INSERT INTO suggestions VALUES ({ctx.author.id}, {suggestion}")
            await db.commit()
            await ctx.send('Your suggestion has been posted to our suggestions channel in our support server!')
            embed=discord.Embed(
                title='New suggestion',
                description=f'{suggestion}',
            )
            embed.set_thumbnail(url=f'{ctx.author.avatar_url}')
            embed.add_field(name='User', value=f'{ctx.author}', inline=False)
            await suggestions.send(embed=embed)

[harsh pulsar]

dont use f strings for sql

see the above discussion on parameterized queries

what database library is this @left scaffold ?

also url=f'{ctx.author.avatar_url}' and value=f'{ctx.author}' are silly, just do url=ctx.author.avatar_url and value=ctx.author

[left scaffold]

Aiosqlite

[harsh pulsar]

await db.execute("INSERT INTO suggestions VALUES (?, ?)", (ctx.author.id, suggestion))

or

await db.execute("INSERT INTO suggestions VALUES (:author_id, :suggestion)", {'author_id': ctx.author.id, 'suggestion': suggestion})

not all database libraries use the same syntax for those placeholders. sqlite3 and aiosqlite happen to use ? for unnamed parameters and :name for named parameters

[left scaffold]

Okay

I'll try it

So this is the updated code:

    @commands.command()
    @commands.guild_only()
    async def suggest(self, ctx, *, suggestion):
        suggestions = self.client.get_channel(740091642698006558)
        async with aiosqlite.connect(r"C:\Users\dylan\OneDrive\Documents\GitHub\bots\slio\slio\cogs\suggestions.db") as db:
            await db.execute("INSERT INTO suggestions VALUES (user, suggestion)", (ctx.author.id, suggestion))
            await db.commit()
            await ctx.send('Your suggestion has been posted to our suggestions channel in our support server!')
            embed=discord.Embed(
                title='New suggestion',
                description=f'{suggestion}',
            )
            embed.set_thumbnail(url=f'{ctx.author.avatar_url}')
            embed.add_field(name='User', value=f'{ctx.author}', inline=False)
            await suggestions.send(embed=embed)

But the error I get is

returning exception no such column: user
Traceback (most recent call last):
  File "C:\Users\dylan\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 171, in run
    result = function()
sqlite3.OperationalError: no such column: user

And the db says

CREATE TABLE "suggestions" (
    "user"    INTEGER,
    "suggestion"    TEXT
);

[harsh pulsar]

read my code carefully @left scaffold

that isnt what i said to do

[left scaffold]

You said either one so I went with first one heh?

[harsh pulsar]

but thats not what i said to do

i wrote ? and you wrote column names

the ?s are literal ?s

they're placeholders for the data

[left scaffold]

Ah cos it looks like ? means fill in here lol

[harsh pulsar]

ah, no

i guess that wasnt clear

anyway what it was trying to do in the query there was insert data from itself using a nonexistent column

hence the "no column named user" error

[left scaffold]

This is literally my first ever attempt to insert data into a database dw

[harsh pulsar]

you should read the original sqlite3 docs because aiosqlite is based on sqlite3

!d g sqlite3

[delicate field]

`sqlite3`

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

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:... read more

[left scaffold]

Wow it worked!

[harsh pulsar]

that goes into more detail on how to use query parameters and provides more usage help / examples

[left scaffold]

Yeah I learnt sqlite on codeacademy

Didn't think to read the docs tho lol

[harsh pulsar]

never hurts to read the docs, or at least try

the worst thing that happens is, they're badly written and you give up

[hasty juniper]

@commands.Cog.listener()
    async def on_guild_join(self, guild):
        async with aiosqlite.connect('data/db/Settings.sql') as db:
            cursor = await db.execute("SELECT GuildAllowNSFW FROM GuildSetting WHERE GuildID = (?)", (guild.id,))
            AllowNSFW = await cursor.fetchall()
            if AllowNSFW is None:
                await db.execute("INSERT INTO GuildSetting (GuildID, GuildAllowNSFW) VALUES (?,?)", (guild.id,'Yes'))
                await db.commit()
```not work((((

@commands.command()
    async def testdb(self, ctx, arg:int):
        async with aiosqlite.connect('data/db/Settings.sql') as db:
            cursor = await db.execute("SELECT GuildAllowNSFW FROM GuildSetting WHERE GuildID = (?)", (arg,))
            AllowNSFW = await cursor.fetchall()
        await ctx.send(AllowNSFW)

help pls

[harsh pulsar]

@hasty juniper try if len(AllowNSFW) == 0

this schema seems strange

also try "SELECT GuildAllowNSFW FROM GuildSetting WHERE GuildID = ?", (guild.id,)). ? instead of (?). not sure if that is different.

[hasty juniper]

Thx

that work

[harsh pulsar]

👍

@hasty juniper ```python
@commands.Cog.listener()
async def on_guild_join(self, guild):
async with aiosqlite.connect('data/db/Settings.sql') as db:
await db.execute("""
INSERT INTO GuildSetting (GuildID, GuildAllowNSFW) VALUES (:guild_id, :value)
ON CONFLICT (GuildID) DO UPDATE SET value = :value
""", {'guild_id': guild.id, 'value': 'Yes'})
await db.commit()

[hasty juniper]

wow

[harsh pulsar]

or ```python
@commands.Cog.listener()
async def on_guild_join(self, guild):
async with aiosqlite.connect('data/db/Settings.sql') as db:
await db.execute("""
INSERT INTO GuildSetting (GuildID, GuildAllowNSFW) VALUES (?, ?)
ON CONFLICT (GuildID) DO UPDATE SET value = excluded.value
""", (guild_id, 'Yes'))
await db.commit()

https://www.sqlite.org/lang_UPSERT.html

[hasty juniper]

Thx

[harsh pulsar]

you need UNIQUE or PRIMARY KEY constraint on GuildID for this

[celest matrix]

Is there a way to read a specific column of old tables in alembic migration scripts?

context: I'm making a migration script that adds another column to tables that represent posts. for the old tables, I need to make the new column's values equivalent to one of their columns

if there is an old post, the migration script will automatically copy over a value from one of its columns into the new one

[torn sphinx]

so i have a JSON file that lists out mutes, and it's set up so each entry is it's own dictionary (because i have to delete each entry once a certain condition is met). An entry looks like this:

{"218497277210132481": {"mute start": 1598483334.0, "mute end": 1598484234.0}}

What i want to do is for a my script to look through each dictionary's "mute end" and compare the timestamp with the current one; and if the current one is higher than the mute end, it'll delete the entire entry. How can I do this?

[harsh pulsar]

@torn sphinx what are the keys? user ids?

@torn sphinx ```python
delkeys = []
for key, data in mute_info.items():
if mute_info[key]['mute_end'] < current_timestamp:
delkeys.append(key)
for key in delkeys:
del mute_info[key]

i do it in 2 "stages" because you shouldn't mutate something that you're iterating over

[torn sphinx]

yea the user ids

[harsh pulsar]

you can also just overwrite the whole dict and filter the elements you don't want

mute_info = {k: v for k, v in mute_info.items() if v['mute_end'] < current_timestamp}

[torn sphinx]

i feel like that'll cause a lot of empty lists, wouldn't it? I mean there are times when this json file will be empty

[harsh pulsar]

im not sure what you mean

how often are you doing this operation

[neon oak]

I’m trying to connect my discord bot to a mongodb database, but having trouble. Do you guys have any good videos/articles explaining mongo from scratch?

[manic geyser]

Anyone know why the database I built using SQLAlchemy would delete itself?

[coral pilot]

Nope, SQLAlchemy operates on level of tables, does not allow to drop or create a database.

~~SQLAlchemy-Utils address what you would like in a way compatibile with SQLAlchemy: https://sqlalchemy-utils.readthedocs.io/en/latest/database_helpers.html.~~

[manic geyser]

interesting, I was using it, but recently it would say could not find a specific table

so I just ran an update, and it just seemed to fresh start the db x3

[harsh girder]

With peewee, is it possible to have an index such that a field will be required to be unique, but only if it is not null? Like this in SQL:

CREATE UNIQUE INDEX test_index
ON test_table(test_column)
WHERE test_column IS NOT NULL;```

Hmm, apparently this is the default behaviour of unique fields in some databases

...including the database I wish to use, so all is well, sorry for bothering

[harsh girder]

...and now it is complaining peewee.IntegrityError: UNIQUE constraint failed: so I guess I do need help after all

[craggy jackal]

is test_column unique @harsh girder ?

[harsh girder]

The SQL above is just an example of how I might do it if I were not using peewee

and i would not make test_column unique, no

[craggy jackal]

if it isn't unique then you can't make a unique index

you can just make an index, but not UNIQUE INDEX

[harsh girder]

Hmm

I may be misremembering but i think i have done that succesfully on sql server

[torn sphinx]

Hello, do you know if it's good or not? Cause I'm not too sure

[craggy jackal]

Unique indexes ensure that no two rows for the indexed column have the same value.

[harsh girder]

@torn sphinx That's a success, yes, but wrong channel

[craggy jackal]

That isn't database related, but if you don't want to use a cached install you can specify --no-cache-dir argument

[torn sphinx]

Bad channel?

[craggy jackal]

This is the databases channel.

That isn't database related

Perhaps a general help channel would be better or #tools-and-devops

[harsh girder]

This is a success in SQLite:

CREATE TABLE test (a VARCHAR);
CREATE UNIQUE INDEX testindex ON test(a) WHERE a IS NOT NULL;

I am trying to do that but via peewee

[craggy jackal]

Maybe SQLite will throw an integrity error if the constraint is violated.

However other flavors enforce it at index creation time.

[harsh girder]

What constraint?

[craggy jackal]

The unique index.

[harsh girder]

I want the constraint to be enforced

But only when a is not null

and that seems to be working

sqlite> insert into test values (null);
sqlite> insert into test values (null);
sqlite> insert into test values (null);
sqlite> insert into test values ('abc');
sqlite> insert into test values ('abc');
Error: UNIQUE constraint failed: test.a

But I want to do it via peewee

[craggy jackal]

I am unfamiliar with peewee so maybe I am not the best to help, best of luck though.

[harsh girder]

ok, thanks

[meager vine]

What do we think of the DBConn context manager examples in this article https://blog.ramosly.com/python-context-managers-and-the-with-statement-8f53d4d9f87 ?

[karmic wraith]

Can someone tell me the insert anomaly present here? I cant seem to figure it out

Is it that if one of the employees picks up a new skill, you can't add it?

[meager vine]

An insertion anomaly is the inability to add data to the database due to absence of other data.

[karmic wraith]

Is it that if one of the employees picks up a fourth skill, you can't add it
So this is it right?

[meager vine]

https://stackoverflow.com/questions/12998521/insertion-anomaly

Look at rows 9/10. What exactly are they telling you?

[karmic wraith]

There were no employees with those skill so they added them that way

but is it an insertion anomaly if they actually did add the data into the database

[meager vine]

I mean, the fact those columns are NULL does't mean they don't exist

So effectively you now have a employee that's NULL

Those skills are still associated with a employee value, it just happens to be NULL

[karmic wraith]

Whats NULL?

[meager vine]

Well I assume those employee columns are...

If you are asking more broadly what a NULL value is then I suggest you go away and google it. It's the SQL value for "nothing" - the analogue of the python None value

Of course those values might actually be empty strings '', which might actually be worse than them being NULL

[karmic wraith]

I guess... I think the "correct" answer are the rows 6/9/10/13/15 but isnt it a paradox

If they were insert anomalies arent they suppose to not exist

If they do exist as data

Are they insert anomalies

[meager vine]

This sounds like a coursework question, which we aren't really meant to answer here, but talking conceptually it really depends what the framing is

They are insert anomalies in the sense that they needed to be associated with "values" for columns they don't actually have

Albeit NULL/empty values

[cinder dome]

So Im planning on switching from aiosqlite to asyncpg {Postgresql}, but what should I do to first get started? Setup wise

This is my current aiosqlite setup if needed
In Main File: https://mystb.in/DrivenSamplingArthritis.sql
In database.py: https://mystb.in/BotherAdjustmentsElection.python

Ping on reply also

[hardy pine]

https://stackoverflow.com/questions/63593395/how-can-any-out-of-6-value-in-same-document Can anyone help me in this mongo query

[subtle rock]

how to store python list in sqlite3 db?

[harsh pulsar]

!e ```python
import json
import sqlite3

Create or connect to a database

db = sqlite3.connect(':memory:')

Register adapters for JSON serialization and deserialization

sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_adapter(list, json.dumps)
sqlite3.register_converter('JSON1', json.loads)

Make some data

user_data = [
{'id': 123, 'aliases': ['salt rock', 'srl', 'salt rock lamp']},
{'id': 456, 'aliases': ['MOHAN', 'mohan']}
]

Create table & insert data

with db:
db.execute('create table user (id integer primary key, aliases json1)')
db.executemany('insert into user values (:id, :aliases)', user_data)

Query data

curs = db.execute('select aliases from user where user.id == ?', (123,))
print( curs.fetchone()[0] )

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

["salt rock", "srl", "salt rock lamp"]

[harsh pulsar]

@subtle rock ^

[subtle rock]

thanks

[harsh pulsar]

For more info, see the sqlite3 documentation about registering adapters and converters:

!d g sqlite3

[delicate field]

`sqlite3`

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

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:... read more

[subtle rock]

thanks

[torn sphinx]

is there a performance drop when I repeatedly upsert instead of UPDATE in PostgreSQL? like for example i have to insert a data every hour, and update it every minute, would it be better to just INSERT for the first minute, and then UPDATE for the next 59 minutes, or just constantly upsert?

[quaint tiger]

• Is the data formatted in such a way that the upsert would insert at the top of every hour? (I.e. unique date/hour combo)?
• How large is your dataset?
  I think an upsert is basically [lock table, check index/scan table for target (hopefully it is indexed), if target found, update else insert] so it should generally be fairly performant.

Easiest thing to do would be to write it as an upsert, and worry about performance if/when needed. Keep code as simple as possible, and measure it before you optimize. Sometimes premature optimization is a waste of time.

[torn sphinx]

any way to delete entries in a JSON file using an iterating loop? The file is there just to temporarily store info that gets added and deleted

yes, the time itself is a composite key along with a foreign key, and this key is kinda estimated to be in the thousands, im not sure if that's gonna be significant, basically each foreign key will have their own date&time every hour.

[quaint tiger]

Thousands of entries? Then that shouldn't be too slow.

[torn sphinx]

i see, ig i will stick with upsert, and consider using another approach once it shows significant performance drop

[quaint tiger]

I had tables with hundreds of millions of rows that could be queried under a second, and that was a decade ago.

I wouldn'texpect any significant drop, tbh.

[torn sphinx]

oh really? I see, then it should be easy then

[quaint tiger]

any way to delete entries in a JSON file using an iterating loop? The file is there just to temporarily store info that gets added and deleted
@torn sphinx do that in memory.

[torn sphinx]

memory?

[quaint tiger]

Quicker and safer to read the file, process the JSON, and write than deleting one record at a time from the file...

Can you post sample data?

untested pseudocode-ish...

data = None
with open(FILE_NAME) as f:
    data = json.load(f)
if data is not None:
    # filter data here
    with open(FILE_NAME, "w") as o:
        json.dump(data, o)

is what I meant

[placid chasm]

Does anyone have steps passing mySQL, how to assimilate it into python

I mean after getting the basics of mySQL down.

[quaint tiger]

I've only ever used the MySQL connector.
https://dev.mysql.com/doc/connector-python/en/

Good thing about that is it's similar to the C version. (Which I haven't touched in years).

You can also use an ORM (Object relational mapper, i think).

Such as https://www.sqlalchemy.org/

Or if you use a web framework, Django has its own ORM which should be fairly similar.

[gaunt meadow]

what error? pgadmin4

[quaint tiger]

Might be a pgadmin bug / encoding issue? I don't see anything wrong with the query itself.

Did you copy/paste it, or write it directly in there?

[meager vine]

Does anyone have any advice for connection pooling using pyodbc and connecting to SQL Server?

I'm thinking something similar to psycopg2.pool functionality

[jovial rose]

Is it possible to do a for x in y: loop to find all keys called text regardless of their position/indentation?
The reason I'd like this is because I may have a text value at data['sections'][0]['content'][0]['text'] and one at data['sections'][14]['content'][0]['elements'][0]['text'] or an entirely different path.

I don't know the dict structure beforehand, I just know the data I want are at text keys.

[quaint tiger]

Think the easiest way to do that is recursively.

[long slate]

+--------------------+
|   user   | invites |
|  user 1  |    1    |
|  user 2  |    3    |
|  user 3  |    9    |
+--------------------+``` I want to accecs invites through the `user` in using asyncpg as text how do i do that

[quaint tiger]

I want to accecs invites through the user in using asyncpg as text how do i do that
@long slate ideally, add a primary key id column, but if you want it as text, select invites from foo where user = "user 1";

[upbeat lily]

@chilly copper

ask your question here

[chilly copper]

I am total new to database..

From where I have to start it

[quaint tiger]

What are you trying to do?

What do you need the DB for?

[chilly copper]

storage ... of my progress in game

pygame

and i have no idea ..

totally new to sql

[quaint tiger]

SQLite is good for those kinds of things.

https://docs.python.org/3/library/sqlite3.html

[chilly copper]

yeah, i watch some tutorials, but didn't understood

i just need to figure it out

[quaint tiger]

Make a list of stuff you want to keep in it, and post it here.

And the data types.

[chilly copper]

how to update it

[quaint tiger]

E.g. score (integer), inventory (special item class)...

[chilly copper]

directly from the pygame

[quaint tiger]

First you need to define the schema.

You can also use JSON files, pickle, or other serializable formats (including custom formats).... SQLite is easy to edit/cheat.

[chilly copper]

alright, i will seek for that

[quaint tiger]

Step one: define what you want to store in it.
Step two: define the schema.
Step three: use the sqlite module to open the db, create the necessary tables, update the schemas if necessary, etc.

Step four: store data in it.

Step 5: read that data.

[celest matrix]

Alright I have been fiddling around with this code and it doesn't work:

DO
$do$
DECLARE creation_time_candidate TIMESTAMP;
    BEGIN
    FOR creation_time_candidate IN (
        SELECT creation_time FROM "post" WHERE file_last_modified_time IS NULL
        ) LOOP
            UPDATE "post"
            SET file_last_modified_time = creation_time_candidate
            WHERE
                NOT EXISTS (
                    SELECT creation_time FROM "post"
                    WHERE file_last_modified_time = creation_time_candidate
            );
END LOOP;
END
$do$

It's supposed to loop through the table of posts, and copy the value from column A (creation_time) to column B (file_last_modified_time) if column B is null.

But it somehow manages to write into column B even though it is no longer null

I'm running this through op.execute()

Also I'm using PostgreSQL

[left scaffold]

Hi im new to using sqlite databases and haven't long finished learning it as a course but just have a question. How can I go to my database and lookup a code that the user has put in a command (its a discord bot) and then it also reads from that row so it will return something like 9110(<-- code) 748789274989923(<-- user id) Test suggestion(<-- suggestion)? I hope this makes sense but ping me if it doesn't

In other words these are the fields in the database and by using the code a user puts in I want it to identify the values of the information that is under these rows and return them but not the entire database by using a *, just 1 row

[quaint tiger]

select [fields or * for all FIELDS] from TABLE where CODE=9110

select * from TABLE where code=9110 or select user, suggestion from TABLE where code=9110

[left scaffold]

Okay I'll try that

Okay but when I have that how exactly to I get the output (what its just selected) to print in a terminal or send to a channel etc?

[harsh pulsar]

@left scaffold you've used a database library, right? i think i helped you with this the other day

[left scaffold]

Yes

I just need to know how to return its findings

[harsh pulsar]

did you get a chance to read any docs? i ask because im curious how far you got before you got confused and/or stuck 😛

that way i can understand how much you do and don't already know

[left scaffold]

Haven't read docs yet. The course I took to learn it went over a lot of commands and how to use them but there are loads

[long slate]

How could i get the output when ever i excute a command i am using asyncpg

[harsh pulsar]

@left scaffold in that case i recommend trying to figure out: 1) how to fetch data from the result of a query, and 2) what data structure (list, tuple, dict, etc.) the output data has

[left scaffold]

alr

[harsh pulsar]

@ me if you get stuck

[quaint tiger]

await db.execute(f"UPDATE GuildSetting SET GuildAllowNSFW 'Yes' WHERE GuildID = (?)", (ctx.guild.id,))

SET GuildAllowNSFW = 'Yes'

[hasty juniper]

Yes

[quaint tiger]

Missing a =, whoever posted and deleted lol

[hasty juniper]

i fix

Thx

[cursive ibex]

I have this site

and it's going to have many results, with many pages

I'm using postgresql with offset and limit to show 12 results every page

I also want to add arrows to go to the next and last page

to do this, I need to know if the given records are the first set or the last set to hide the page arrows

is there anyway I can do this except for get 1 extra record at the beginning and end and check?

[quaint tiger]

Run separate queries.

One to count the number or records or pages, and the other to get the records for page X.

if page != 1: print link to prev and if page != last: print link to next

[cursive ibex]

ah

thanks!

[clever flame]

If I have a kivy app that takes user input and I want that input to be sent to a SQL database, do I need to make an API? If so, any links to how to create an API would be much appreciated.

[torn sphinx]

Anyone know a good SQL chat?

Or wouldn’t mind hearing my business plan regarding SQL

Hi, so I'm currently using SQLite3 and am making a custom prefix command and I was wondering; how can I get the guilds prefix from the database and input it into my get_prefix function?

[cinder dome]

First you should use aiosqlite

and make a guilds table

CREATE TABLE IF NOT EXISTS guilds (
id INTEGER PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS prefix_list (
guild_id INTEGER, 
prefix TEXT, 
FOREIGN KEY guild_id REFERENCES guilds (id)
);```

I think its like that but you can try

[minor ruin]

TaylorNicole214, try off topic if you want

[torn sphinx]

@minor ruin off topic ?

[crude geyser]

hola spkin spanish?

[left scaffold]

Yo I'm using mysql in python but just want to ask - Do I need to await my execute functions?
like await db.execute or just db.execute

[proven arrow]

@left scaffold Depends on the library you are using for your DB. If it’s an async one then yes you will have to use await.

[left scaffold]

I'm using mysql

[proven arrow]

Mysql is a DBMS. Your library will be the one you import and use to interact with the database.

[left scaffold]

Okay

Also

How can I fix this mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

[proven arrow]

Not sure, looks like your python is not supporting that. Make sure you have the correct version of the lib installed. One that supports your python.

This is the one you should have, https://pypi.org/project/mysql-connector-python/

[dull hull]

is it worth it to use an ORM

[proven arrow]

I guess it depends on your use case. Sometimes it might just be easier to use raw sql. Also ORM can be a bit slower, but dev with it can be faster.

[dull hull]

im planning to use sqlalchemy with discord.py, idk i just don't want to deal with sql 😅

[proven arrow]

Yeah you can use one, just be sure to use an async version. If I remember there is an async version available for sqlalchemy.

[dull hull]

thank you for your time

[arctic anvil]

I'm using flask-sqlalchemy, and looking at putting some of my common fields in a base class.

In some examples I have seen this @declated_attr decorator used, like this:

class IdModel(db.Model):
    __abstract__ = True

    @declared_attr
    def created_at_field(self):
        return db.Column('created_at', db.DateTime(), default=now)

Is that decorator necessary? I read a bit about it but I'm not sure I understood correctly.

or can you also just declare attributes in the abstract model, like you do in the models?
id = db.Column(db.Integer, primary_key=True)

(of course, those examples do not match)

in my very limited testing it seems to work fine just declaring the attributes on the abstract model

oh I see this answer suggests doing just that:
https://stackoverflow.com/questions/25055877/flask-sqlalchemy-abstract-base-model#25057230

I do see one difference, although not particularly meaningful

when fields are defined with the decorator, in the db they seem to be placed at the end of the field list, while the attributes on the model are at the top of the field list.

personally I like seeing the id first and the dates at the end, but that's just for my lazy select * ... investigations

[jaunty ibex]

@welcome.command()
async def text(ctx, *,text):
    if ctx.message.author.guild_permissions.administrator:
        db =sqlite3.connect('main.sqlite')
        cursor = db.cursor()
        cursor.execute(f"SELECT msg FROM main WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()
        if result is None:
            sql = ("INSERT INTO main(guild_id, msg) VALUES(?,?)")
            val = (ctx.guild.id, text)
            await ctx.send(f"Welcome text has been set to `{text}`")
        elif result is not None:
            sql = ("UPDATE main SET msg = ? WHERE guild_id =?")
            val = (ctx.guild.id, text)
            await ctx.send(f"Welcome text has been set to `{text}`")
        cursor.execute(sql, val)
        db.commit()
        cursor.close()
        db.close()
``` Why it doesnt fill the text and show it as none?

?

The error

Ignoring exception in on_member_join
Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "e:\Hydra-Kame\disc.py", line 82, in on_member_join
    channel = bot.get_channel(id=int(result[0]))
TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

[burnt turret]

it says so in the error

[jaunty ibex]

why is it none?

[burnt turret]

it is in on_member_join event

what's line 82?

[jaunty ibex]

py channel = bot.get_channel(id=int(result[0]))

[burnt turret]

what is the result list

[jaunty ibex]

ok lemem run and tell

[arctic anvil]

btw, I found more info, this link clearly explains the use of the declared_attr decorator
https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-columns

[reef crag]

can anyone help with these errors? or point me to a solid resource other than python.org or realpython.com that i have already looked into. TY

NotADirectoryError Traceback (most recent call last)
<ipython-input-7-1cb0e413c5bf> in <module>
48
49 # Main
---> 50 generateURL("y2019", 5)

<ipython-input-7-1cb0e413c5bf> in generateURL(startYear, numYears)
32 directory = "all_stats_html/%s" % stat.replace('/', ' ') #need to replace to avoid
33 if not os.path.exists(directory):
---> 34 os.makedirs(directory)
35 years = []
36 for option in html.find("select", class_="statistics-details-select").find_all("option"):

~\AppData\Local\Programs\Python\Python38-32\lib\os.py in makedirs(name, mode, exist_ok)
221 return
222 try:
--> 223 mkdir(name, mode)
224 except OSError:
225 # Cannot rely on checking for EEXIST, since the operating system

NotADirectoryError: [WinError 267] The directory name is invalid: 'all_stats_html/SG: Tee-to-Green'

sorry I forgot to make it code for the thread and what is used to make it appear as code?

[reef crag]

anyone interested to share some expertise?

[quaint tiger]

Put three backticks ```before and after``` the code

And your dir name appears to be invalid all_stats_html/SG: Tee-to-Green ... are colons even allowed in dir names?

https://stackoverflow.com/a/10386576/11396744 guess not, @reef crag

[tepid cradle]

@reef crag show the code which leads to this error

But in general, you can't have : in your filename

[little bough]

Learning SQLite, I know the benefits of an in-memory database (not being limited by I/O), but what is a usecase where I would rather put a database in memory, because isnt the data stored on disk in the form of the script that builds it every time the script runs.

[quaint tiger]

If you don't need to persist data, you can store it in memory (e.g. process information). If you need to persist, write it to disk.

TBH, I don't really use in-memory DBs.... ever?

[little bough]

The only thing I could think was like maybe an application that used one for more static data,

[reef crag]

TY @quaint tiger

@tepid cradle here is the code:

[delicate field]

Hey @reef crag!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[reef crag]

and here is the output w/errors:

NotADirectoryError                        Traceback (most recent call last)
<ipython-input-7-1cb0e413c5bf> in <module>
     48 
     49 # Main
---> 50 generateURL("y2019", 5)

<ipython-input-7-1cb0e413c5bf> in generateURL(startYear, numYears)
     32         directory = "all_stats_html/%s" % stat.replace('/', ' ') #need to replace to avoid
     33         if not os.path.exists(directory):
---> 34             os.makedirs(directory)
     35         years = []
     36         for option in html.find("select", class_="statistics-details-select").find_all("option"):

~\AppData\Local\Programs\Python\Python38-32\lib\os.py in makedirs(name, mode, exist_ok)
    221             return
    222     try:
--> 223         mkdir(name, mode)
    224     except OSError:
    225         # Cannot rely on checking for EEXIST, since the operating system

NotADirectoryError: [WinError 267] The directory name is invalid: 'all_stats_html/SG: Tee-to-Green'

[minor ruin]

@little bough We have used them at work for caching

we don't want caching to persist restart for any reason

[quaint tiger]

and here is the output w/errors:
@reef crag you can't use colons in directory/file names.

The directory name is invalid: 'all_stats_html/SG: Tee-to-Green'

[reef crag]

@quaint tiger so how would you write it? I am not quite following you. For instance, if I wanted the new file that is scraped form the PGA site to saved in the directory c:\Python\PGA\allstats\PGA20200828.csv

here si the code:

import os
import gevent
import requests
from bs4 import BeautifulSoup

statUrlFormat = "https://www.pgatour.com/stats/stat.%s.%s.html" # statId, year
categoryUrlFormat = 'https://www.pgatour.com/stats/categories.%s.html'
categoryLabels = ['ROTT_INQ', 'RAPP_INQ', 'RARG_INQ', 'RPUT_INQ', 'RSCR_INQ', 'RSTR_INQ', 'RMNY_INQ', 'RPTS_INQ']

def saveHTML(url, filename):
    print ("Saving", url, "to", filename)
    r = requests.get(url)
    with open(filename, 'wt') as f:
        f.write(r.text)

That's the first part (had to pslit due to being more than 2000 characters

[quaint tiger]

wait

[reef crag]

# startYear: Most recent year of stats
# numYears:  Previous # of years
def generateURL(startYear, numYears):
    statIds = []
    for category in categoryLabels:
        categoryUrl = categoryUrlFormat % (category)
        page = requests.get(categoryUrl)
        html = BeautifulSoup(page.text.replace('\n',''), 'html.parser')
        for table in html.find_all("div", class_="table-content"):
            for link in table.find_all("a"):
                statIds.append(link['href'].split('.')[1])
    for statId in statIds:
        url = statUrlFormat % (statId, startYear)
        page = requests.get(url)
        html = BeautifulSoup(page.text.replace('\n',''), 'html.parser')
        stat = html.find("div", class_="main-content-off-the-tee-details").find('h1').text
        directory = "all_stats_html/%s" % stat.replace('/', ' ') #need to replace to avoid
        if not os.path.exists(directory):
            os.makedirs(directory)
        years = []
        for option in html.find("select", class_="statistics-details-select").find_all("option"):
            year = option['value']
            if year not in years and len(years) < numYears and year != "y2020":
                years.append(year)
        urlFilenamePairs = []
        for year in years:
            url = statUrlFormat % (statId, year)
            filename = "%s/%s.html" % (directory, year)
            if not os.path.isfile(filename):
                urlFilenamePairs.append((url, filename))
        jobs = [gevent.spawn(saveHTML, pair[0], pair[1]) for pair in urlFilenamePairs]
        gevent.joinall(jobs)

# Main
generateURL("y2019", 5)
````Code

second part shown above. TY for all fo the help.

[quaint tiger]

As you can see from the error trace, the problem is in the generateUrl function.

        directory = "all_stats_html/%s" % stat.replace('/', ' ') #need to replace to avoid
        if not os.path.exists(directory):
            os.makedirs(directory)

I guess stat must have a value of SG: Tee-to-Green or SG:/Tee-to-Green

.replace('/', ' ') replaces the / with a space...

But you never replace the colon.

        directory = "all_stats_html/%s" % stat.replace('/', ' ').replace(':', '_')

should replace colons with underscores and create the directory

[reef crag]

ok.. I got it I think... let me try adn edit it.

@quaint tiger almost now I have this error, but it's the only one.

File "<ipython-input-1-4e8260c3a37d>", line 32
    directory = "all_stats_html/%s" %
````Code

@quaint tiger I fixzed that obvious syntax, but now this appeared and I am not expereinced enough to even understand this .

Saving https://www.pgatour.com/stats/stat.02674.y2019.html to all_stats_html/%s/y2019.html
Saving https://www.pgatour.com/stats/stat.02674.y2018.html to all_stats_html/%s/y2018.html
Traceback (most recent call last):
File "src\gevent\greenlet.py", line 854, in gevent._gevent_cgreenlet.Greenlet.run
File "<ipython-input-2-32fa1f4f4433>", line 14, in saveHTML
f.write(r.text)
File "C:\Users\jrm12\AppData\Local\Programs\Python\Python38-32\lib\encodings\cp1252.py", line 19, in encode
return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 52386-52387: character maps to <undefined>
2020-08-28T20:13:06Z <Greenlet at 0x154f248: saveHTML('https://www.pgatour.com/stats/stat.02674.y2019.ht, 'all_stats_html/%s/y2019.html')> failed with UnicodeEncodeError

Traceback (most recent call last):
File "src\gevent\greenlet.py", line 854, in gevent._gevent_cgreenlet.Greenlet.run
File "<ipython-input-2-32fa1f4f4433>", line 14, in saveHTML
f.write(r.text)
File "C:\Users\jrm12\AppData\Local\Programs\Python\Python38-32\lib\encodings\cp1252.py", line 19, in encode
return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 52386-52387: character maps to <undefined>
2020-08-28T20:13:07Z <Greenlet at 0x134df08: saveHTML('https://www.pgatour.com/stats/stat.02674.y2017.ht, 'all_stats_html/%s/y2017.html')> failed with UnicodeEncodeError

Traceback (most recent call last):
File "src\gevent\greenlet.py", line 854, in gevent._gevent_cgreenlet.Greenlet.run
File "<ipython-input-2-32fa1f4f4433>", line 14, in saveHTML
f.write(r.text)
File "C:\Users\jrm12\AppData\Local\Programs\Python\Python38-32\lib\encodings\cp1252.py", line 19, in encode
return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 52386-52387: character maps to <undefined>
2020-08-28T20:13:07Z <Greenlet at 0x134ddf8: saveHTML('https://www.pgatour.com/stats/stat.02674.y2016.ht, 'all_stats_html/%s/y2016.html')> failed with UnicodeEncodeError

Traceback (most recent call last):
File "src\gevent\greenlet.py", line 854, in gevent._gevent_cgreenlet.Greenlet.run
File "<ipython-input-2-32fa1f4f4433>", line 14, in saveHTML
f.write(r.text)
File "C:\Users\jrm12\AppData\Local\Programs\Python\Python38-32\lib\encodings\cp1252.py", line 19, in encode
return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 52475-52476: character maps to <undefined>
2020-08-28T20:13:08Z <Greenlet at 0x134dd70: saveHTML('https://www.pgatour.com/stats/stat.02674.y2015.ht, 'all_stats_html/%s/y2015.html')> failed with UnicodeEncodeError

Saving https://www.pgatour.com/stats/stat.02674.y2017.html to all_stats_html/%s/y2017.html
Saving https://www.pgatour.com/stats/stat.02674.y2016.html to all_stats_html/%s/y2016.html
Saving https://www.pgatour.com/stats/stat.02674.y2015.html to all_stats_html/%s/y2015.html

[minor ruin]

how is this database related?

[reef crag]

related? I am going to work on that as next steps. Is there a way to save the file as a .CSV formatted one?

[sage hill]

any suggestions for a databaseless storage option?

cluster admins are dragging their feet on getting me mysql access since they are upgrading the cluster in a month or so

i mostly just need to get my data out of memory

spun up to 100GB RAM 3TB swap before crashing

I was thinking HDF5, but I want to see if anyone has any suggestions that could work better

a unit of observation is a couple identifying fields then a longish text field

[minor ruin]

?

Mongo/Cassandra cluster?

or if it's just pure text and you don't care, shove into Azure Blob/AWS S3/GCP Storage bucket

[sage hill]

i would rather just have a local file

i have plenty of disk space

[minor ruin]

a bunch of JSON files?

[sage hill]

i could probably do that, but ideally i would like something I could query

thats why i was thinking hdf5

i might just end up dumping a bunch dicts as pickles

its unideal, but whatever

[brazen charm]

sqlite

[sage hill]

oh lol i didn't even check if sqlite was installed

yeah that'll do the job haha

[steel bramble]

Just a quick invite to a relatively well known database for use with Python: The Postgres Discord 😄 https://discord.gg/tjxNBCz

[woven galleon]

Does anyone know how to use asyncpg with discord.py?

[proven arrow]

Asynpg is not specific to discord.py, and works the same as it does elsewhere. But yes you can ask questions about asynpg here. What exactly do you need help with?

[woven galleon]

@proven arrow, well, I’m trying to store data in a dictionary to altar. So just a file that contains a dictionary with data for each person. I was using uh, pickle before and thought it would be a good idea to switch. I have no knowledge of asyncpg, so I came here to ask about It.

[proven arrow]

Are you confident with using relational databases and using sql in general?

Also Asyncpg is just a asynchronous library for Postgresql.

[woven galleon]

I’d like to say yes, but no experience whatsoever.

[proven arrow]

Ok well postgres is a database management system. It uses the SQL language, which is a query language to write queries.
I find this a good website for learning SQL and good for a refresher too: https://sqlbolt.com/

Then you would also need to have postgres installed on your computer or wherever you run it. There are plenty of guides on Google how to do this. After you install the database then you can start using the python asynpg module so you can start interacting and manipulating the database from within python.

Once you do all tbat, maybe have a play around with it first, so you understand how it works a little, rather than directly trying to integrate it into your project at first which could lead to confusion. If you need help then just ask here.

[woven galleon]

@proven arrow Thank you!

[frozen loom]

whats the difference between aiosqlite and aiosqlite3? I'm looking into using databases for a discord bot

[sinful condor]

how do I search aiosqlite to get the top few things from a list

[sage hill]

#algos-and-data-structs message

might have been better for this channel

trying to make a multiprocessing pool along with a dedicated database writer process

because sqlite3 keeps throwing a disk I/O error

[minor ruin]

hfhry, multiprocessing and SQLite is going to end badly

you should upgrade to real database server at that point

[sage hill]

yeah, thats what i'm thinking

i need to press on my server admins