databases

[burnt turret]

first one but no comma after the ?

[prime kelp]

ah ok ty

await cursor.execute('Select * from users where user_id = ? and guild_id = ?', (member.id,member.guild,)) result = await cursor.fetchone()

sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

[austere portal]

member.guild.id

[prime kelp]

omgggg

thanks ---_---

[lost echo]

CREATE TABLE `Coupon` (
  `CouponId` int(11) NOT NULL,
  `CouponCode` char(10) NOT NULL,
  `CouponValue` decimal(13,4) NOT NULL,
  `CouponType` char(1) NOT NULL,
  `MinValueRequired` decimal(13,4) NOT NULL,
  `MaxUsage` smallint(6) NOT NULL,
  `TotalUsage` smallint(6) NOT NULL,
  `CreateBy` varchar(32) NOT NULL,
  `CreateDate` datetime NOT NULL,
  `StartDate` datetime NOT NULL,
  `ExpiredDate` datetime NOT NULL,
  `NoteText` varchar(512) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

What does ENGINE and CHARSET mean while creating this table?

[proven arrow]

Engine is for storage and what the database will use to handle whatever operations you do on it. You should use InnoDB instead of MyISAM for mysql. Charset is just the encoding type for characters

Only use MyISAM if you know what your doing or have a specific reason to use it.

[lost echo]

actually this is from a project I'm currently involved in and client gave me this to made some basic modification (I know basic stuff) so just got curious

I have set CHARSET to utf8mb4 (assuming it supports wide range of characters than latin1)

[proven arrow]

Yes that’s fine

[lost echo]

Alright, thanks

[harsh sandal]

idk which channel to ask

so my friend in korea has a raspberry pi and say I bought one, is there any way we could connect it? like a mini server so because i’m in singapore, if someone from malaysia connected, it would connect to the closer one, which is mine. Or am i getting this whole idea wrong??

[austere portal]

Try asking this question in #networks

It is more suitable to be asked there

[prime kelp]

helppp

cursor = await self.db.cursor()
        await cursor.execute("Select * from config where guild_id = ?", (ctx.guild.id))

        result = await cursor.fetchone()

        await cursor.execute("Select * from config where guild_id = ?", (ctx.guild.id))

        result = await cursor.fetchone()```

@austere portal

why 😢

nvm it worked 😡

its because i did

if not result instead of if result is None

[austere portal]

and you need to pass in a tuple for execute

[prime kelp]

yeah did that

ty tho

[austere portal]

👍

[prime kelp]

@austere portal

what does this mean

Traceback (most recent call last):
  File "C:\Users\caede\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\caede\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\caede\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InterfaceError: Error binding parameter 2 - probably unsupported type.

nvmmm

i was trying to add an object

sorry 🙇

[upper basin]

can anyone help me set up a pgsql server on a vps and be able to connect to it from my bot? i 've installed pgsql but i dont know how to access it from my computer with the hostname/pwd

everything i can find on google is for localhost

[faint blade]

If PostgreSQL is running on the same machine as your bot, then it should be on localhost.

[torn sphinx]

what is a good mongodb alternative

[ionic pecan]

postgresql

[torn sphinx]

are they similar

i am sorry if this seems weird i am new to databases-

[ionic pecan]

no not at all, postgresql uses SQL and mongodb uses ... its own ... construct

[torn sphinx]

ohh

[ionic pecan]

The benefit of learning to work with an SQL database is that you can apply it to many databases out there, so if you ever start work at a place which uses MySQL / MariaDB, Oracle, Microsoft SQL, sqlite, or whatever, it will still be the same language constructs you're using

another huge difference is that in postgresql you have fixed definitions of how you want your data to look and postgresql validates that for any record you add to the database

[torn sphinx]

ah so will MySQL and postgresql be similar in syntax

[ionic pecan]

Yes

[torn sphinx]

oh

[ionic pecan]

There's some small differences of course

[torn sphinx]

yeh i got it

[ionic pecan]

But most of it is standardized

[torn sphinx]

yeh tyvm

[brave bridge]

Like willy nilly typing (I'm looking at you SQLite)

[ionic pecan]

Oh god, don't remind me....

[prime kelp]

👋

Hello

So

im using aiosqlite

this is my table

is there a way to just remove that entire row

@commands.command(aliases=['rconf'])
    async def removeconfig(self, ctx:commands.Context):
        
        cursor = await self.db.cursor()
        await cursor.execute('Select * from config where guild_id = ?', (ctx.guild.id,))

so how would i just delete the cursor

area

[ionic pecan]

Replace SELECT * with DELETE

[prime kelp]

is that it?

do i do .commit then?

do i remove the * as well?

surely it would be DELETE *

do caps matter?

[ionic pecan]

Caps doesn't matter, it's case-insensitive

And no, you don't need the star

The star tells SELECT which fields to select

In DELETE you're deleting the entire row. It's not possible to only delete a few columns

[prime kelp]

ok ty!

[grim vault]

and yes, a commit is needed after a db change.

[prime kelp]

no - the execute works fine without the commit

cursor = await self.db.cursor()
        await cursor.execute('Delete from config where guild_id = ?', (ctx.guild.id,))```

this deletes it just fine

[ionic pecan]

as berndulas said, you will need a commit, or autocommit mode enabled

[prime kelp]

does aiosqlite have autocommit?

[prime kelp]

why does this method work?

[faint blade]

If you don't need to call commit then the answer to that is yes.

[prime kelp]

okay cool, ty

[grim vault]

If you did the select with the same connection the answer is: maybe. Because if you are still in the same transaction the row is deleted for this connection. Close and reconnect and take a look again.

[brave bridge]

Is making a connection pool for SQLite a stupid idea?

I assume so, since making a connection is pretty cheap, but maybe I'm wrong

[faint blade]

Yes because SQLite can't actually connection pool compared to RDMSs like PostgreSQL who can have several connections write at the same time (with smart locking).

Depends on the usage of course, like we talked about earlier you want to use a new connection if you're executing a very important transaction.

[brave bridge]

Yeah, I'm planning to just spawn a new connection whenever I would acquire on from a connection pool in postgres

[faint blade]

You could create a connection pool interface, and have one central connection. Any requests done directly to the pool would be directed to this connected.

But when you need to make a transaction, and want a new connection, you can add a method that creates the connects and returns it.

[warped magnet]

Hi guys, someone can help me with connections MySQL using Django D:? pls

[torn sphinx]

can anyone help me out with MongoDB? I am getting a weird error I am not sure I even understand

[rustic cipher]

I've ran into a small snag with my project. I'm running two Docker containers, one with my application and one with a MySQL database in it. The problem is that my app cannot connect to the database, because the host is being rejected. I can "solve" the problem by setting the host to a %wildcard% in the database, but I would also like to be able to log in to the database remotely, which means that it is exposed to the internet. Again, I can use the firewall to block everyone except myself (which I'm obviously already doing), but I am not happy about this. I would like to be able to set a fixed allowed host in the database, just like you would use "localhost" for a database running on the same host as the app.
I can't find any relevant information on this particular problem, so I'm hoping for some pointers. I'm a bit surprised that this isn't the single most common problem associated with databases in containers, so I am probably missing something obvious. I also sort of refuse to believe that everyone is just using the wildcard or static IP's for their containers, as this feels like very bad practice. I did try to use the container's name, but in the error, MySQL specifies that the host attempting to connect is in fact the IP address of the container, which I assume could change at any time, especially if I deploy on a different server.
How do people solve this?

[pure cypress]

Did you set up the containers so they're on the same network? You can create networks with Docker and assign containers to them.

With Docker compose, you can actually use the service's name as the host name when trying to connect to another container on the same network. This is more consistent than relying on an IP.

However, I believe you can configure the network to reserve static IPs for containers.

With that, you should be able to either whitelist the host via the service name or via a static IP you configured.

Maybe even specifying a hostname for the container with docker run --hostname ... Would give you something consistent to whitelist, but I've not tried that since I'm too used to Docker compose.

[torn sphinx]

How can I save keys inside another key in redis?

[narrow moth]

I'm having a strange problem with psycopg and Postgres.
I cannot see any difference in the queries but one causes an error.

    col_details = []
    print("TABLE_STR: ", table_str)
    try:
        cur = conn.cursor()
        cur.execute(sql.SQL("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE 'manufactors'"))
        col_details = cur.fetchall()
        print("\nHC QUERY: ", col_details, "END\n")

        cur.execute(sql.SQL("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE {}").format(sql.Identifier(table_str)))
        col_details = cur.fetchall()
        print("\nCUR VAR QUERY: ", col_details, "END\n")```

My output:
```TABLE_STR:  manufactors

HC QUERY:  [('m_id',), ('code',), ('material',), ('company',), ('model_range',), ('sculptor',), ('year_prod',), ('m_name',)] END

column "manufactors" does not exist
LINE 1: ... INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE "manufacto...
                                                             ^

(obviously, the ^ is pointing to the " in "manu...)

Hard coding the table name produces results, so it's not a permissions thing, and Information_Schema is meant to be public anyway, ... the only thing I'm swapping is the variable, which is 'manufactors', instead of writing it literally. But I'm obviously not doing that.

It looks like cur.execute(sql.SQL("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name LIKE {}").format(sql.Literal(table_str))) works, but that looks like a really bad practice.

[lost echo]

How can I join 3 tables where table a has ID for both table b and table c?

SELECT requestitem.RequestItemId, url.Url, masterservice.ServiceName
FROM requestitem
INNER JOIN url ON requestitem.urlId=url.UrlId;
INNER JOIN masterservice ON requestitem.ServiceId=masterservice.ServiceId;

I'm doing this but this is not working cause I think for this to work the 2nd table needs to have id of item in 3rd table

[narrow moth]

you've got an ";" in the middle of your query

[narrow moth]

^ (so it's breaking up the query into two parts)

[lost echo]

oh wow, works now! thanks @narrow moth

[desert sandal]

what's the difference ? and which one do i install

[brave bridge]

@desert sandal The second one contains the data you need to install, the first one will download it when you run it

I don't know which one, pick any

[desert sandal]

thanks lol

[uneven lodge]

can we store multiple comma separated values in a single row like data Watch, Bag, Table in a row

[upbeat slate]

On SQLite, when i import a timestamp with timezone, the timezone does not get imported with it fo some reason

when i select it

and fetch

its bugging me

[faint blade]

In a single row? Do you mean one column?

Just declare it a string (VARCHAR, or TEXT usually) and insert the comma seperated string you created.

[upbeat slate]

anyome has any idea of where this issue could come from?

[faint blade]

SQLite doesn't have a timestamp type. Are you storing an integer or formatted string?

[upbeat slate]

i am storing it as a datetime

[faint blade]

Datetime object?

[upbeat slate]

yea

i gets stored prefectly

but not imported perfectly

😬

[faint blade]

How? Like the repr? Can you show me what is stored?

[upbeat slate]

yeah sec

[upbeat slate]

do you also need details on how data is stored and retrieved?

[faint blade]

Huh, that's interesting-
https://www.sqlite.org/datatype3.html

[upbeat slate]

so i will have to convert it, store it, and re convert it after importing it...

[faint blade]

Yes

[upbeat slate]

i see

ty!

[placid hull]

what orm do you recommend for python?

[uneven lodge]

user = (ctx.author)
            sql = "SELECT EXISTS(SELECT * FROM Users WHERE userid=%s)"
            mycursor.execute(sql, user)
            mydb.commit()
            for x in mycursor:
                await ctx.send(x)

It returns could not process parameters. Why?

[upbeat slate]

you're on SQLite3?

[uneven lodge]

MySQL

@upbeat slate

[upbeat slate]

owh

can't help then sorry

[uneven lodge]

ok

[faint blade]

It should be .execute(sql, (user,))

[uneven lodge]

i fixed it but i am getting another error.

user = ctx.author
            sql = "SELECT EXISTS(SELECT * FROM Users WHERE userid=%s)"
            usr = user.id
            mycursor.execute(sql, (usr, ))
            mydb.commit()
            for x in mycursor:
                await ctx.send(x)

error:
Commands out of synyc

@faint blade

[faint blade]

I have no idea what that error means, can you remove mydb.commit()?

[uneven lodge]

let me try this

[placid hull]

maybe you need to await the database calls?

[uneven lodge]

Thanks bro it works. I thought there would be some issue for commit changes but at last i am just checking

[uneven lodge]

Also, is there any need to use a loop to print the output or directly use await ctx.send(mycursor)

[faint blade]

No you can't await ctx.send(my cursor).

That said I recommend you do it in one message though.

output = ""
for x in mycursor:
     output += x + "\n"

You spam Discord otherwise

[uneven lodge]

ok

[grim vault]

"SELECT EXISTS(SELECT * FROM Users WHERE userid=%s)" will only ever return one value (0 or 1), no need for a loop at all.

[upper basin]

lvlroles = {"Rank: Scavenger":(10,20),"Rank: Survivor" : (20,30),"Rank: Battleworn" : (40,50),"Rank: Warrior" : (50,75),"Rank: Sage" : (80,100),"Rank: Ascended" : (100,120)}

if roles := [role.name for role in member.roles if role in list(lvlroles.keys())]:
    await self.bot.primedb.execute("UPDATE userferins SET ferins = $1 where guildid = $2 AND userid = $3", user['ferins'] +1, guildid, memberid)
    print(f"1 ferins assigned cause of role {roles[0]}")
    await ctx.send(f"1 ferins assigned to {member.mention} with role {roles[0]}")
        
else:
    await ctx.send("empty list")

this gives me an empty list, i cant figure out why. does anyone have any clue what im doing wrong?

[faint blade]

This has nothing to do with the database part of the code..

list(lvlroles.keys()) is a list of strings, member.roles is a list of Role objects. You need to change if statement part to be if role.name in.

[upper basin]

oh my bad

thank you!

i have another question,
i am fetching the details of a user

user = await self.bot.primedb.fetchrow("SELECT * FROM userferins WHERE userid = $1 AND guildid = $2", memberid, guildid)
                
        if not user:
            await self.bot.primedb.execute("INSERT INTO userferins (userid, guildid, ferins, lastmsg) VALUES ($1, $2, $3, $4)",memberid, guildid, 1, time.time())

and inserting if it doesnt exist

my question is, do i have to fetch user again after i insert?

[faint blade]

Yes and no, you already have the values that would make up the row right? (userid, guildid, ferins and lastmsg)
It might be unnecessary to return the row.

If you add a RETURNING * at the end of your query and use fetchrow it will return the row created.

[upper basin]

await self.bot.primedb.fetchrow("INSERT INTO userferins (userid, guildid, ferins, lastmsg) VALUES ($1, $2, $3, $4) RETURNING *",memberid, guildid, 1, time.time())

like this?

[faint blade]

Yes

But of course you would want to assign it to user, so user = await self.bot.primedb.fetchrow(...)

[upper basin]

ahh, thank you!

[last igloo]

Traceback (most recent call last):
  File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\38050\Desktop\ds bot\cogs\registr.py", line 52, in on_ready
    if self.my_cursor_check(sql = f"SELECT id FROM users WHERE id = {response.author.id}") is None:
  File "C:\Users\38050\Desktop\ds bot\cogs\registr.py", line 32, in my_cursor_check
    return self.cursor.fetchone()[0]
KeyError: 0```

```class Ready(commands.Cog):
    def __init__(self, client):
        self.client = client
        self.connection = connection
        self.cursor = cursor
    def my_cursor(self, sql):
        while True:
            try:
                self.cursor.execute(sql)
                self.connection.commit()
                break
            except OperationalError:
                self.connection.ping(True)

    def my_cursor_check(self, sql):
        while True:
            try:
                self.cursor.execute(sql)
                return self.cursor.fetchone()[0]
                break
            except OperationalError:
                self.connection.ping(True)```

[faint blade]

fetchone returns a form of dict, not a tuple or list.

Just remove [0]

[austere portal]

I use pg_orm for postgresql

[hardy bay]

    @commands.command()
    @commands.has_permissions(administrator=True)   
    async def setreports(self, ctx, reports_channel):
        await self.client.db.execute('UPDATE reports SET reports_channel = $1 WHERE guild_id = $2', reports_channel, ctx.guild.id)
        await ctx.send(f'![roundTick](https://cdn.discordapp.com/emojis/774346252494176257.webp?size=128 "roundTick") Se ha marcado <#{reports_channel}> como canal de reportes.')
    


    @commands.command()
    async def reportar(self, ctx, reporte):
        reports_channel = await self.client.db.fetch('SELECT reports_channel FROM reports WHERE guild_id = $1', ctx.guild.id)
        print(reports_channel)
        embed = Embed(description=reporte, color=0x163c47, timestamp=datetime.utcnow())
        await reports_channel.send(embed=embed)

I have this two commands. The problem is that it does not save the reports channel and after with the reportar command i wanted to see what stores in reports_channel and it printed an empty list []. Do you know why ?

[austere portal]

Any error?

[hardy bay]

Command raised an exception: AttributeError: 'list' object has no attribute 'send'

as the reports_Channel is empty it cant send anything

i guess thats the error

[austere portal]

Looks like reports channel is a list

[hardy bay]

yeah exactly, an empty one

thats the problem

what datatype should be reports_channel

a biginter right?

[austere portal]

i dont know, you made the table...

show your table schema

[hardy bay]

i started with databases like 2h ago

so im full beginner

[austere portal]

How did you create the table

[austere portal]

print type(reports_channel)

[austere portal]

reports channel is a string

[hardy bay]

so what shall i put?

bigint?

[austere portal]

and Connection.fetch returns a list of Records

!d asyncpg.Record

[delicate field]

asyncpg.Record

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

[austere portal]

what do you want it to be?

[hardy bay]

the id of the channel

[austere portal]

then make it bigint

You can use ALTER to change the datatype of the column

You can do something like sql ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type;

[hardy bay]

okay i did through pg admin

now reports_channel is aa big int

Base de Datos conectada
Cliente inicializado...
Ignoring exception in command reportar:
Traceback (most recent call last):
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\HARRY\Desktop\Discord\DROGON\cogs\general.py", line 123, in reportar
    reports = self.client.get_channel(reports_channel)
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\client.py", line 793, in get_channel
    return self._connection.get_channel(id)
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\state.py", line 1118, in get_channel
    pm = self._get_private_channel(id)
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\state.py", line 329, in _get_private_channel
    value = self._private_channels[channel_id]
TypeError: unhashable type: 'list'

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

Traceback (most recent call last):
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\HARRY\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: unhashable type: 'list'

@austere portal

could the problem be that in the code is not storing well the reports_channel?

becasue when i do it i made refresh in pgadmin but it never show me that has stored the channel

[austere portal]

Send code

[hardy bay]

    @commands.command()
    @commands.has_permissions(administrator=True)   
    async def setreports(self, ctx, reports_channel:int):
        await self.client.db.execute('UPDATE reports SET reports_channel = $1 WHERE guild_id = $2', reports_channel, ctx.guild.id)
        await ctx.send(f'![roundTick](https://cdn.discordapp.com/emojis/774346252494176257.webp?size=128 "roundTick") Se ha marcado <#{reports_channel}> como canal de reportes.')
    


    @commands.command()
    async def reportar(self, ctx, reporte):
        reports_channel = await self.client.db.fetch('SELECT reports_channel FROM reports WHERE guild_id = $1', ctx.guild.id)
        reports = self.client.get_channel(reports_channel)
        print(reports)
        embed = Embed(description=reporte, color=0x163c47, timestamp=datetime.utcnow())
        await reports.send(embed=embed)

[austere portal]

Connection.fetch returns a list, you cant do client.get_channel(list)

I guess you meant fetchone instead of fetch

[hardy bay]

so i change self.client.db.fetch to self.client.db.fetchone

?

[austere portal]

Yes

[hardy bay]

gonna try it

[austere portal]

and changeclient.get_channel(reports_channel) to client.get_channel(reports_channel["reports_channel"])

@hardy bay

[hardy bay]

okay

[austere portal]

Beuase reports_channel is a asyncpg.Record object

[hardy bay]

mm okay

Command raised an exception: AttributeError: 'Pool' object has no attribute 'fetchone'

[austere portal]

oh

[hardy bay]

but im haveing the same problem, when i make .setreports and refresh the db it doesnt appear the reports_channel as stored

[austere portal]

my bad its db.fetchrow not fetchone

[hardy bay]

okay, shall i try it again?

[austere portal]

because you are only updating an existing row not creating a new one

[hardy bay]

reports = self.client.get_channel(reports_channel["reports_channel"])
TypeError: 'NoneType' object is not subscriptable

@austere portal

[austere portal]

looks like the query is returning None

[austere portal]

Yeah thats the issue because the db is empty

[hardy bay]

    @commands.command()
    @commands.has_permissions(administrator=True)
    async def setprefix(self, ctx, prefix):
        await self.client.db.execute('UPDATE guilds SET prefix = $1 WHERE guild_id = $2', prefix, ctx.guild.id)
        await ctx.send(f'Prefijo cambiado a **{prefix}**')

(in another file)
  prefix = await client.db.fetch('SELECT prefix FROM guilds WHERE guild_id = $1', message.guild.id)

i did this before

i wanted to make the same but instead of prefixes report channel

but i dont know what im changing

[austere portal]

You are updating

[hardy bay]

the prefixes is working

[austere portal]

not inserting

[hardy bay]

but the set reports is inserting also

[austere portal]

no

[hardy bay]

the first time im setting the reports channel

actually there is nothing on the table

[austere portal]

execute('UPDATE reports SET reports_channel = $1 WHERE guild_id = $2', reports_channel, ctx.guild.id)

[hardy bay]

ahh

thats the problem

can i insert none and after change it?

[austere portal]

wdym?

[hardy bay]

because righ now im not updating nothing cause there is nothing inserted

i havent inserted nothing in reports_channel

im trying to insert a new one

not updating an existing one

[austere portal]

then do an insert query

<@&831776746206265384> Looks like a scam

[hardy bay]

could yo tell me how please jaja, i dont now how to insert

[austere portal]

INSERT INTO reports (reports_channel, guild_id) VALUES($1, $2)

@hardy bay

That's a insert query

[hardy bay]

thank you

one question @austere portal , if someone runs the insert one, and after a while is runned again, it would work like an update? I mean if the Insert is runned several times it rewrites or it creates the same guild but with another reports channel? so ill have in the tables two same guilds with different reports channels?

[austere portal]

No, it will insert the values again

You can check if the values are already in the database, if it is already in it then run a update query, else run a insert query

[hardy bay]

how can i make that? sorry asking you but as i said i really have no idea with connections with db

i mean, how can i check if its in the table

[austere portal]

if (await Connection.fetchrow("SELECT * FROM table_name WHERE guild_id=$1", guild_id)) is not None:
    # it is in the db
else:
    # it is not in the db```

[hardy bay]

perfect tysm

[austere portal]

😄 👍

[hardy bay]

do you have any idea why Connection is in yellow?

[austere portal]

it is not defined ig...

by Connection i meant your asyncpg.Connection object in your case its self.client.db

[hardy bay]

ahh

[hardy bay]

what does the * mean? may i write something there?

[wind smelt]

• means return all values from said row

you can replace it with individual columns

for example, if your table has a username column and you want to return only that from the SELECT statement, you replace * with username

[hardy bay]

ookay thanks

[placid hull]

hey! what orm do you recomend for python?

[austere portal]

I use pg_orm

[austere portal]

* means everything

[hardy bay]

    @commands.command()
    @commands.has_permissions(administrator=True)
    async def disable(self, ctx, command):
        if (await self.client.db.fetchrow("SELECT * FROM statuses WHERE status")) == False:
            await ctx.send('![cross](https://cdn.discordapp.com/emojis/774346252119834694.webp?size=128 "cross") Este comando ya está desactivado.')
        if (await self.client.db.fetchrow("SELECT * FROM statuses WHERE status")) == True or None:
            if (await self.client.db.fetchrow("SELECT * FROM reports WHERE command=$1", command)) is not None:
                await self.client.db.execute('UPDATE statuses SET status = $1 WHERE (guild_id, command) VALUES ($2, $3)', False, ctx.guild.id, command)
                if command == ctx.command:
                    await ctx.send('![cross](https://cdn.discordapp.com/emojis/774346252119834694.webp?size=128 "cross") ¡No puedes hacer eso!')
                else:
                    command.enable = False
                    await ctx.send(f'![roundTick](https://cdn.discordapp.com/emojis/774346252494176257.webp?size=128 "roundTick") {command} ha sido desactivado.')
            else:
                await self.client.db.execute('INSERT INTO statuses (status, guild_id, command) VALUES($1, $2, $3)', False, ctx.guild.id, command)
                if command == ctx.command:
                    await ctx.send('![cross](https://cdn.discordapp.com/emojis/774346252119834694.webp?size=128 "cross") ¡No puedes hacer eso!')
                else:
                    command.enable = False
                    await ctx.send(f'![roundTick](https://cdn.discordapp.com/emojis/774346252494176257.webp?size=128 "roundTick") {command} ha sido desactivado.')
```should this work? because its not

[austere portal]

the sql queries are wrong

[sour sinew]

4. if (await self.client.db.fetchrow("SELECT * FROM statuses WHERE status")) == True or None:
You don't have to usevalue == True:, if value: would be the same;
If you want to check that value == value2 or value is None you must write it completely: value == value2 or value is None.

So:

value = await self.client.db.fetchrow("SELECT * FROM statuses WHERE status")
if value or value is None:
    ...```
Not sure about other code.

[hardy bay]

ah okay

[ionic pecan]

I would recommend you use SQLAlchemy.

[placid hull]

will use it, thanks

[noble ruin]

What do u use as a module for db ?

[primal zinc]

How can i do multiple searches with one request using mongo?

e.g. {"city_from":"LA", "city_to":"NY"},{"city_from":"NY", "city_to":"LA"}

[dense barn]

Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $1: '![1st](https://cdn.discordapp.com/emojis/866878996863385600.webp?size=128 "1st") Edition Fossi... (a boolean is required (got type str))

``` why am i getting this error when my data type for $1 is text?

[torn sphinx]

hi

if in redis save dictionaries in string, then can I use eval and not literal_eval? To improve performance

[austere portal]

Looks like you need to pass in a bool value

[dense barn]

yea but my data type for that is text not boolean

[austere portal]

hmm

Show your code

[dense barn]

await self.client.pg_dbbbbb.execute("UPDATE hunt SET target_name = $1 AND target_image = $2 AND target_state = $3 WHERE user_id = $4",targetcard['card_name'],targetcard['card_url'],True,ctx.author.id)
``` lemme know if you need the whole command

[austere portal]

check the data type for that column in pg admin

[dense barn]

yea its text

[austere portal]

hmm

try doing str(targetcard['card_name'])

[dense barn]

yea didnt work either

[austere portal]

the error says that it expects a bool

[dense barn]

[austere portal]

Sorry, I don't think I can figure this one out

[dense barn]

thats ok

[desert sandal]

i have just started learning databases and i am facing a hard time figuring out how to make a .db file cause whatever i do in the MySQL Workbench gets saved as a .mwb file

[proven arrow]

Values to update are to be separated by comma not and

[proven arrow]

Why do you want a .db file?

You don’t need to create one

[desert sandal]

wdym.. doesn't it stand for database

[dense barn]

Ahh so target_name = $1, target_state = $2 ?

[proven arrow]

No, and to create storage files is not your job. The database engine will do it.

[desert sandal]

oh ohk

[thorn canyon]

What am i doing wrong?

conn.execute('''CREATE TABLE IF NOT EXISTS warns(
    id integer PRIMARY KEY,
    user_id integer NOT NULL,
    rule_num float,
    reason text NOT NULL
)
''')

...

conn.execute(f'INSERT INTO warns (user_id, rule_num, reason) VALUES ({user_id}, {rule_num}, "{reason}")')```

`OperationalError: table warns has no column named rule_num`

[brave bridge]

Perhaps the table warns already existed, and it didn't have the rule_num column.

Also, never use f-strings to make queries. That's prone to SQL injection -- someone puts Don't spam "); DROP TABLE warns --" across multiple channels!, and your database is gone.

Instead, do ```py
conn.execute(f'INSERT INTO warns (user_id, rule_num, reason) VALUES (?, ?, ?)', (user_id, rule_num, reason))

[thorn canyon]

yeah, will fix that, i had experience with that on my site

[thorn canyon]

ok, i will drop it and create again

OperationalError: no such column: None

good

now not rule_num

i provided None into {rule_num}, but that's not a column

it is a value

[brave bridge]

@thorn canyon That's because your query now looks like sql INSERT INTO warns (user_id, rule_num, reason) VALUES (<user id>, None, <reason>) , and SQL interprets None as a column name, just like foo, bar, reason etc.

in SQL, the missing value is NULL instead

and that's also why you shouldn't use f-strings for formatting -- it will just format incorrectly

[thorn canyon]

ah, ok

Thank you, now it's working

will know

[snow dock]

Why this code isn't changing the value?

[brave bridge]

please post it as text, it's really hard to read

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[snow dock]

alr

with open(f"Logic/Home/Events/{a}-events.json", "r") as file:
        	randomMap1 = [2, 4, 17, 24]
        	map1Random = random.randint(1, 30)
        	list = []
        	for line in file.readlines():
        		json_data = json.loads(line)
        		dict = json.loads(json.dumps(json_data))
        		dict["maps"]["map_1"] = map1Random
        		list.append(dict)
        		file.close()
        with open('Logic/Home/EventSlots.json', 'w') as o:
                for i in list:
                	o.write(str(i).replace("'", '"') + '\n')
                	o.close()

[brave bridge]

Do you get any output/errors?

[snow dock]

nops

nope*

the path is correct
and I don't get errors

[brave bridge]

You shouldn't close the files manually, that's what with already does. You're also closing the files in the loop, which shouldn't work at all

[sacred ether]

`
def check_file_db(config, file_data):
con,cur = return_db_con(config)
query = f'select * from files where filename="{file_data["file_name"]}";'
cur.execute(query)
file_db_data = cur.fetchall()
for x in file_db_data:
if x['filename'] == file_data['file_name'] and x['file_path'].replace('\','') == file_data['file_path'].replace('\',''):
if con.is_connected():
print('DB is connected')
check_connection()
cur.close()
con.cmd_quit()
check_connection()
con = None
cur = None
file_db_data = None
return True
else:
con.close()
del con
del cur

def return_file(config, file_location=None):
pdf_location = config['Path']['source_path'] if not file_location else file_location
folder_li = []
pdf_file_det = None
while True:
for x in os.listdir(pdf_location):
file_path = os.path.join(pdf_location,x)
if os.path.isfile(file_path) and 'pdf' in x.split('.')[1]:
file_det = get_file_data(file_path)
if not check_file_db(config, file_det):
pass
elif os.path.isdir(file_path):
if file_path not in folder_li:
folder_li.append(file_path)
else:
if folder_li:
pdf_location = folder_li.pop(0)
else:
break
return pdf_file_det`

Hi all this my code which run in while loop until it finds a file and check file is been recorded in database or not with certain criteria.

In this check_file_db(config, file_det) function is to check whehter file path is in database (MySQL) or not every time when program executes my RAM increases drastically

eg. In 5 mins of program execution RAM went from 618 MB to 7020 MB

Any Idea why this much of RAM been consumed ?

Im using mysql-connector-python to connect with Mysql database

[brave bridge]

@sacred ether Can you show the code in return_db_con?
I suspect the issue is that you're not closing the connections.

[sacred ether]

def return_db_con(config): global cur,con con = connect( host=config['DB']['host'], port=config['DB']['port'], user=config['DB']['user_name'], password=config['DB']['paswd'], database=config['DB']['db'] ) cur = con.cursor(dictionary=True) return con,cur

But in check_file_db function I'm closing the connections

[brave bridge]

Do you know how the else clause of a loop works?

[sacred ether]

yes I kept it incase if condition don;t get satisfied

[brave bridge]

ah, i see, you're closing it in a loop

btw, you don't need to do py con = None cur = None file_db_data = None , and you don't need any of those del statements

[sacred ether]

Ok but I stuck with issue for long time thought of try those will solve my prblm

[brave bridge]

Maybe you can try https://pypi.org/project/memory-profiler/?

Can show the whole code, perhaps the issue is in a completely different place?

What does check_connection do?

[delicate field]

Hey @sacred ether!

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

[brave bridge]

(Btw, never use f-strings to construct queries. This is vulnerable to SQL injection. Instead of py cur.execute(f'select * from files where filename="{file_data["file_name"]}";') you should do ```py
cur.execute(f'select * from files where filename=?;', (file_data["file_name"],))

[sacred ether]

https://paste.pythondiscord.com/ixebapamun.yaml

Check this one this is output from memory-profiler

[sacred ether]

Ok

[sacred ether]

It just check db connection con variable still alive or not using con.is_connected

[brazen saffron]

I have a quick SQlite question for an assignment

For some reason I can't add a second condition to my query, although it works with a single condition. My best guess is that the syntax is incorrect

Any feedback would be appreciated

[slender atlas]

What method does sqlite3 use to parse query variables and defend against injections?

[brazen saffron]

place holders

[brazen saffron]

so something like db.execute(Select * from table WHERE name = ? and password = ?, username, password)

[slender atlas]

I know. I asked what does the library do with that.

Because I am curious.

[proven arrow]

They use what’s called a prepared statement which is the parameterised query you see

https://www.sqlite.org/c3ref/stmt.html

[glass pilot]

you need to repeat "movies.id in" after "and"

[cyan yacht]

class DB:
    def __init__(self, db):
        self.db = db

    async def insert1(self):
        async with self.db.cursor() as cursor:
            await asyncio.sleep(3)
            print('Insert 2')
            await cursor.execute('INSERT INTO test VALUES(2)')
            await self.db.commit()

    async def insert2(self):
        async with self.db.cursor() as cursor:
            print('Insert 1')
            await cursor.execute('INSERT INTO test VALUES(1)')
            await asyncio.sleep(10)           # some long task
            raise Exception           # exception that could occur in long task
            print('Insert 3')
            await cursor.execute('INSERT INTO test VALUES(3)')
            await self.db.commit()

In this code if insert1 and insert2 start together, and the exception occurs in insert2, I dont want the value "1" to be inserted. But the self.db.commit in insert1 seems to be committing all cursors, hence the value 1 is being committed
How to fix? Is there a way to commit just one cursor?

[grim vault]

I never used them but: https://www.sqlite.org/lang_savepoint.html

If we are talking about sqlite, of course.

[mystic vale]

Hey guys! How I can get one table by field?

[mystic vale]

in Mysql

sql request

[mortal light]

How much does it matter which version of PostgreSQL we use?
I have options between 9, 10, 11, 12, 13

This is a snippet of when the versions were released:

[brazen charm]

go with latest, (13)

[mortal light]

Not version 12.7? Since that version would have a bit of history and it would be easier to find answers to different problems that other people also faced?

[brazen charm]

generally the issues you'll have more common arent going to change across pg versions

pg 13 will have the longest service life compared to starting any of the other versions e.g. see v9 that drops support end of this year

the actual psql and SQL doesnt change much / at all during major version changes

it's mostly handling of data in storage handling that changes

you as a user will generally not notice anything diffrent in terms of implementation

[mortal light]

oh ok makes sense. I will go with v13.3 thanks a lot!!

[mortal light]

When I set up the database, there's instance which my guess is a virtual machine, and then there's database name.

So can we have multiple databases under 1 instance then?

[faint blade]

What is instance exactly?

Is this in pgAdmin?

[mortal light]

I'm setting up the database on aws RDS, and there is a DB instance identifier

[mortal light]

I dont think so. Instance is for aws.

So on pgAdmin when you have a url, username, and password, do you just create one single database? Or do people conventionally create multiple databases under those credentials?

ok we can have multiple databases under 1 instance

[steep ingot]

Hi, I'm having some trouble with pandas. Why is this returning an empty DB?

print(res3)
print(df3)```
Print df3 prints the database as expected, print mID prints the int that is in one of the spaces in my df.

The mID i'm looking for is in messagelink column

[narrow moth]

This is from a complete novice and using a server on my local network, but:
Server - postgres. Read-only user (ref: https://tableplus.com/blog/2018/04/postgresql-how-to-create-read-only-user.html). Editing user (uses same but with GRANT SELECT, ALTER, UPDATE, etc...) - Haven't tested Editing user yet, but pretty confident.

Client - pgAdmin4. Connects using postgres user.
- python scripts using psycopg2 (ref: https://www.postgresqltutorial.com/postgresql-python/connect/) with read-only user and editing-user in different sections.
- database.ini with correct .gitignore and chmod settings.

If there is something in that ^^^ that is bad or wrong, I couldn't say. But it does seem to be working really well so far.

[faint blade]

One instance seems to be a PostgreSQL server.

[mortal light]

oh ok I see what you mean. And you are allowed to have multiple databases in one PostgreSQL server?

[faint blade]

Yes! But you usually have one database per application/usage

Then you can have multiple tables in that database

[mortal light]

So we have server/instance -> database(can have multiple, but conventionally 1) -> tables(one or many).

Can you give an example of a database with couple of tables.

[torn sphinx]

!pban 846378875511635968 Nitro scam

[delicate field]

:x: User is already permanently banned (#43850).

[faint blade]

Well say you're making a website, for this you have a backend server running.

This backend server will have the following tables in one database:

• users
• articles

In these tables you would have whatever data, like an ID, name, and hashed password columns in the users table.

[mortal light]

ok

But something like Twitter or Discord would have multiple databases even though these are one standalone applications?

[pearl vapor]

what advantage does using async with conn.cursor() have over just cur.execute()

[torn sphinx]

can someone help with mongodb? I am using flask

I have also installed pymongo

[long dome]

what's the difference between "query, fetch and execute" in asyncpg wtf
arent they all the same shit
executing the query

[proven arrow]

They have different purposes. If you look up the methods in their documentation it explains it.

[proven arrow]

Multiple databases for what?

[elfin garnet]

Hi guys, I am new to databases. Can any one suggest me a road map to learn sql.

[proven arrow]

Depends how deep you want to get into it

[faint blade]

I think at that scale, you would have multiple databases for the different microservices.

Like login would be one database - posts, comments and likes would perhaps be one database - accounts and settings may be one?
All of those would have several tables though ^

As these most likely make internal REST APIs..

[faint blade]

aiosqlite right?

I think with the former you don't need to release the cursor?

[pearl vapor]

asyncpg, shoulda specified

[faint blade]

Are you sure you don't mean async for?

[pearl vapor]

you're right i misread the docs

and what exactly are transactions and what are they used for, cant seem to find on the docs

[elfin garnet]

I would like to make a start then see how can I utilise my learnings.

[torn sphinx]

can someone help me with MongoDB using pymongo in flask?

[faint blade]

Say I have 3 tables to insert into, these would be several queries..

The problem the transactions solve, is that if one of these queries fail, all of them would be rolled back.

That way you know that either all of them succeed, or all fail.

[faint blade]

Just ask your question 🙂

[pearl vapor]

ahhh okay I think i get it

And I guess I cant define the cursor and then do cur.execute() like I did in psycopg since its whining about a missing query, Id have to directly execute it as conn.cursor() and define cur as that

[proven arrow]

There is some resource in pinned messages, but https://www.w3schools.com/sql/ and the SQL For Dummies book is decent. Or this youtube channel is good if you want videos. He has some playlists that might help, https://www.youtube.com/channel/UCWczzX6DyFV2KOYP_-CEZUA/playlists

[torn sphinx]

I have installed it and I am trying to create a database in it

    client = MongoClient('localhost', 27017)
    db = client.test_database
    collection = db.test_collection

I used this to create a database and a collection and run the file but I still don't see the DB in MongoDB Compass

from flask import Flask, Response
from pymongo import MongoClient
```my import files

I was trying to follow this
https://pymongo.readthedocs.io/en/stable/tutorial.html

[faint blade]

A cursor allows you to do stuff in batch. Psycopg follows the Python specification for database wrappers, which is pretty weirdly designed and doesn't correctly get this point across.

For example, I will create a cursor with the query: SELECT * FROM users;, then I can do await cur.fetch(5). This will read 5 rows and give to me.

Otherwise I have no choice but to select all of them at the same time, which (with big tables) would be loaded into memory and I may not have enough.

[torn sphinx]

I want to create a database with this schema

[faint blade]

So when you do this @pearl vapor ```py
async for record in con.cursor('SELECT generate_series(0, 100)'):

You will loop over each row and do whatever (asyncpg doesn't actually make 100 fetches though, it does prefetches so in the end this maybe becomes 2 with batches of 50).

[faint blade]

What's wrong?

[torn sphinx]

@faint blade this is the problem

I also want to implement webhooks on github repo and then store the data in this DB

[faint blade]

but I still don't see the DB in MongoDB Compass

I don't think you create the database through Pymongo commonly. Can you not create it in MongoDB Compass and then access it through Pymongo?

[torn sphinx]

I am new to Mongo

[pearl vapor]

ah alright i gotcha, im mostly using it for some casino stuff in my bot so im gonna stick with a good ol regular cursor. Thanks a lot for your help you da man

[torn sphinx]

I thought this was the way

[torn sphinx]

I was trying to follow this

[faint blade]

You really don't need a cursor 😅

[faint blade]

That is how you get/access databases

[pearl vapor]

Hmm okay I guess youre right on that

[torn sphinx]

I am basically trying to create a webapp in flask to read the webhooks on github repo and store them in the DB
then display the updates on the DB on the app home page

that is what I want to do for now

[faint blade]

https://docs.mongodb.com/compass/current/databases/

Then you can access this from PyMongo

[torn sphinx]

I'll try this

and I'll keep asking here I guess

[long dome]

What's the equivalent of:

cursor = await bot.dblist.cursor()

for POSTGRESQL? we don't need to do .cursor()??

[faint blade]

Not in all cases, also what wrapper do you use?

[long dome]

asyncpg

[faint blade]

No, you do not always need a cursor. Not the same way you do in aiosqlite or psycopg2

[torn sphinx]

@faint blade can you help me create this schema?

[long dome]

so I just connect like

conn = await asyncpg.connect(....)

and then use the conn? like:

result = await conn.execute("SELECT discordid, waxid FROM main WHERE (waxid=?)", (str(waxid),))

[faint blade]

Yes, you may want to use a connection pool though. So create_pool

[long dome]

what's a connection pool?

whats the purpose

[faint blade]

So you have multiple connections to the database, rather than one.

[long dome]

is ti multithreaded safe? I'm going to have like 3 different programs connected to this db being read / updated / inserting data at likely dozens of times per second

or over a couple seconds

[faint blade]

I don't know, you probably don't want to use a connection pool. Just create 3 connections, one for each thread.

[long dome]

gotcha

I'll test connection pool

if i find this method too slow

ty

man switching from aioqlite to postgres is a pain

cant even use fetchone or fetchall either

[pearl vapor]

fetch(query, 1) works im p sure

[long dome]

how come this doens't work

exists = await conn.fetch("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'JTG'")
if not exists:
  await conn.execute('CREATE DATABASE JTG')

its only meant to run the create if exists doesn't exist

but i get an error saying it already exists

which means it's being ran

[faint blade]

fetchval if you only fetch a column for one row

fetchrow to fetch 1 row, and then use normal fetch to fetch whatever many rows.

execute will only execute the query and return what changed. For example INSERT 1 if you inserted 1 row.

[torn sphinx]

what is the best way to use mongodb in flask?

is pymongo the common way it is used?

or should I be using something else?

[faint blade]

I don't know much about Mongo, but I mostly see questions about PyMongo

[torn sphinx]

oh ok

how do I create a schema in mongodb?

I am trying to search it but I can't find

[long dome]

well fetchval still triggering the if not exists:

[torn sphinx]

it says I need to uplaod a file

[faint blade]

Have you printed what it returns?

[long dome]

returns none

but it still runs it

lol

well I just changed it to if exists != None and worked I guess

I assume this remains the same as aiosqlite no?

querystring, replacements  = "INSERT INTO channels (discordid, channelid) VALUES (?,?)",(str(member),str(channel.id))
await conn.executemany(querystring, replacements)

[faint blade]

No

querystring, replacements  = "INSERT INTO channels (discordid, channelid) VALUES ($1, $2)"
await conn.executemany(querystring, str(member), str(channel.id))

Quick note, storing someone's name is poor design, why aren't you doing str(member.id)?

[long dome]

I do but it's inside another function so member is just member.id being passed

[faint blade]

Ah, okay!

[long dome]

async def addusertodb(member, waxid, channel):
    conn = await asyncpg.connect(
    host="localhost",
    database="JTG",
    user="postgres",
    password="xxxx")
    querystring, replacements  = "INSERT INTO channels (discordid, channelid) VALUES (?,?)",(str(member),str(channel.id))
    await conn.executemany(querystring, replacements)
    await conn.commit()

its a bit confusing to adapt to postgres after using aiosqlite for so long

[faint blade]

This is where you would use a connection pool, opening connections is rather costly.

A connection pool will open several, and hold them, so that you can fluently acquire (wait to receive one) and release it back to the pool.

[long dome]

im just not too sure how thread safe they would be that's my issue

I essentially have 3 bots in my discord server

and they all access this same dv

db*

and theres dozens of people doing commands at the same time

[faint blade]

Do you want to share data between them?

[long dome]

yes

[faint blade]

Ah okay

Why do you run them together, but in different threads?

[long dome]

3 different programs, because of rate limiting issues, instead of running all commands on a single bot I run different commands on each different bots, this way I don't trigger rate limit.

One essentially handles new players, one does the game actions while the other does other commands like help, lottery, etc

So for this big one this is how it would work?

querystring= "INSERT INTO main (.......) VALUES ($1,$2,0,5,5,5,5,1,0,'xxxxx',$3,$4,0,0,100,10)"
await conn.executemany(querystring, str(member),str(waxid),landx,landy)

[faint blade]

executemany should be just execute, otherwise yes.

[pearl vapor]

How would I go about handling postgres errors in asyncpg, i had except asyncpg.Error as e: before to handle error code 23505 (something to do with a primary key already existing) yet I cant find anything in the docs about it and its telling me asyncpg doesnt have the Error attribute

[faint blade]

Why not have 3 pools then? https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.pool.create_pool

[faint blade]

I don't think that's the "root" error, hold on.

[pearl vapor]

Its how I handled it in psycopg2

except psycopg2.Error as e:
  if e.code == "23505":
    send message...```
Found the error code in psycopg2 docs so its definitely gonna be different in asyncpg lol

[long dome]

1 for each bot? Wonder if it'll be safe >.<

cant afford getting db corrupted

[faint blade]

No those come from PostgreSQL. Also I did find it, it's asyncpg.PostgresError. However asyncpg seperate those into several exceptions.

See the source for all errors: https://github.com/MagicStack/asyncpg/blob/master/asyncpg/exceptions/__init__.py

[pearl vapor]

Thanks a ton :)

[faint blade]

Look, here's the one with code 23505 https://github.com/MagicStack/asyncpg/blob/master/asyncpg/exceptions/__init__.py#L412-L413

[delicate field]

asyncpg/exceptions/__init__.py lines 412 to 413

class UniqueViolationError(IntegrityConstraintViolationError):
    sqlstate = '23505'```

[pearl vapor]

yeah UniqueViolationError, thats the one i was catching before, again thanks alot

[faint blade]

I don't think that can really happen with PostgreSQL. There's many systems in place for stuff like this.

Connection pools are just multiple connections..

[torn sphinx]

from pymongo import MongoClient
from pymongo.errors import CollectionInvalid
from collections import OrderedDict

db = MongoClient("mongodb://localhost:27019/")['test_database']

user_schema = {
    'id': {
        'type': 'int',
        'minlength': 1,
        'required': True,
    },
    'request_id': {
        'type': 'string',
        'minlength': 1,
        'required': True,
    },
    'author': {
        'type': 'string',
        'minlength': 1,
        'required': True,
    },
    'action': {
        'type': 'string',
        'minlength': 1,
        'required': True,
    },
    'from_branch': {
        'type': 'string',
        'minlength': 1,
        'required': True,
    },
    'to_branch': {
        'type': 'string',
        'minlength': 1,
        'required': True,
    },
    'timestamp': {
        'type': 'timestamp',
        'minlength': 1,
        'required': True,
    },
}

collection = 'test_collection'
validator = {'$jsonSchema': {'bsonType': 'object', 'properties': {}}}
required = []

for field_key in user_schema:
    field = user_schema[field_key]
    properties = {'bsonType': field['type']}
    minimum = field.get('minlength')

    if type(minimum) == int:
        properties['minimum'] = minimum

    if field.get('required') is True:
        required.append(field_key)

    validator['$jsonSchema']['properties'][field_key] = properties

if len(required) > 0:
    validator['$jsonSchema']['required'] = required

query = [('collMod', collection),
         ('validator', validator)]

try:
    db.create_collection(collection)
except CollectionInvalid:
    pass

command_result = db.command(OrderedDict(query))

```I tried this based to build a schema for the `test_database` with `test_collection`

I am still getting some errors

and I also cannot see the changes in the Compass

[storm mauve]

getting errors
whenever you get an error you cannot figure out on your own, say which error it is

[mortal light]

So it doesnt matter where we host our postrgres server, we can still use pgAdmin or sqlworkbench to look at our database?

[faint blade]

Yes

[mortal light]

Yup I entered the credentials it shows me the database that I had created in aws.

[pearl vapor]

Is the only way to get column names by using conn.prepare()?

[mortal light]

What's a good step by step guide to work with postgres on python?

I came across the following:
https://www.tutorialspoint.com/postgresql/postgresql_python.htm
https://www.postgresqltutorial.com/postgresql-python/
https://www.psycopg.org/docs/install.html

[crystal compass]

Its Mongodb btw:
The Collection looks like this:

"_id" : "839207316808007761", "settings": {more..}

cluster = MongoClient(
    "mongodb+srv://<username>:<passwort>@discord.mz3vr.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
db = cluster["Discord"]
guild_settings = db["guild settings"]


x = guild_settings.find_one({"_id": "839207316808007761"})

for results in x:
    print(results["settings"])

returns: TypeError: string indices must be integers

[pearl vapor]

There's no way to pass arguments as a tuple in asyncpg right?

[proven arrow]

No it won’t accept tuple

[pearl vapor]

fuck

[proven arrow]

Why?

[torn sphinx]

for

@commands.command(aliases = ['nick', 'name'])
    @commands.cooldown(1, 60, commands.BucketType.user)
    async def forcenick(self, ctx, member: discord.Member):
        await self.open_bank(ctx.author)
        forcer = await collection.find_one({'_id': ctx.author.id})
        print(forcer)```
i'm getting this error
```Traceback (most recent call last):
  File "/home/turtle/.local/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "/home/turtle/.local/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/home/turtle/.local/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: cluster0-shard-00-01.xvye6.mongodb.net:27017: ,cluster0-shard-00-00.xvye6.mongodb.net:27017: ,cluster0-shard-00-02.xvye6.mongodb.net:27017: , Timeout: 30s, Topology Description: <TopologyDescription id: 6101bfad46255a4b42e7e4bb, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.xvye6.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.xvye6.mongodb.net:27017: ')>, <ServerDescription ('cluster0-shard-00-01.xvye6.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.xvye6.mongodb.net:27017: ')>, <ServerDescription ('cluster0-shard-00-02.xvye6.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.xvye6.mongodb.net:27017: ')>]>```

[crystal compass]

i think you dont need await to find something

but im not sure

[torn sphinx]

i'm using motor

[crystal compass]

ah k

[long dome]

Having an issue, so I do the following:

async def db_conn():
    conn = await asyncpg.connect(
    host="localhost",
    user="postgres",
    password="pox")
    return conn

async def check_db_exists():
    conn = bot.dblist
    exists = await conn.fetchval("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'JTG'")

    if exists != None:
        await conn.execute('CREATE DATABASE JTG')

However, I randomly keep getting:

Ignoring exception in command verification:
Traceback (most recent call last):
  File "C:\Python\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\SteelHard\Desktop\Python\JTG\Main1.py", line 125, in verification
    conn = psycopg2.connect(
  File "C:\Python\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  database "JTG" does not exist

When I actually try to access the database

[pearl vapor]

trying to make an SQL command for my bot and previously I would enter the arguments as a tuple but now I cant, and since I dont know the amount of varaibles I cant just unpack the tuple

<@&831776746206265384>

[crystal compass]

Its Mongodb btw:
The Collection looks like this:

"_id" : "839207316808007761", "settings": {more..}

cluster = MongoClient(
    "mongodb+srv://<username>:<passwort>@discord.mz3vr.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
db = cluster["Discord"]
guild_settings = db["guild settings"]


x = guild_settings.find_one({"_id": "839207316808007761"})

for results in x:
    print(results["settings"])

returns: TypeError: string indices must be integers

[torn sphinx]

I might be wrong but for the id I think you need to put in an integer instead of a string.

so instead try
x = guild_settings.find_one({"_id": 839207316808007761})

[mortal light]

Does psycopg2 follow query convention from postgreSQL?

[crystal compass]

mhmm but in the document its "839207316808007761"

[torn sphinx]

try it and see what happens

[crystal compass]

yea wait

[crystal compass]

same error

[long dome]

What does this mean?

Traceback (most recent call last):
  File "C:\Users\SteelHard\Desktop\Python\JTG\Main1.py", line 1698, in <module>
    bot.loop.run_until_complete(check_db_exists())
  File "C:\Python\lib\asyncio\base_events.py", line 642, in run_until_complete
    return future.result()
  File "C:\Users\SteelHard\Desktop\Python\JTG\Main1.py", line 66, in check_db_exists
    await conn.execute("CREATE TABLE main (xxxxxxxxxxxx)")
  File "C:\Python\lib\site-packages\asyncpg\connection.py", line 297, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 338, in query
asyncpg.exceptions.DuplicateTableError: relation "main" already exists

I literally drop the databse before even creating this

[torn sphinx]

@crystal compass btw I don't think you need to loop through x if you're only finding one

<@&267628507062992896>

[delicate field]

:incoming_envelope: :ok_hand: applied purge ban to @winged gulch permanently.

[terse stump]

ohh.. damn

!pban 809118756999528480 steam scam

[delicate field]

:x: User is already permanently banned (#43860).

[terse stump]

!unban 744179317872787546

[delicate field]

:ok_hand: pardoned infraction ban for @winged gulch.

[torn sphinx]

oh no lmaoo

[terse stump]

thanks for the report, next time you see bad behaviour you can just ping moderators instead

admins are to be pinged if something on the server is broken

[torn sphinx]

gotchu

[crystal compass]

maybe but this was an example:

base: ```
{"_id" :"839207316808007761"},"settings":{"logger":{"status":"disable","channelid":"None"},"audit":{"status":"disable","channelid":"None"},"invite":{"status":"disable","role1":"None"},"language":"english","team role":"None"}}

i want to get multiple things from that

[torn sphinx]

wait, if you're trying to get multiple things from a document then find_one isn't what you need

[crystal compass]

so i need many?

[torn sphinx]

https://docs.mongodb.com/manual/reference/method/db.collection.find/

[pearl vapor]

i did...

[terse stump]

cool 😄

[torn sphinx]

they were talking to me

[pearl vapor]

that was 25 min before that tho

[mortal light]

Wondering if someone could explain:

cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print("Table created successfully")

Took it from https://www.tutorialspoint.com/postgresql/postgresql_python.htm

So are we saying create a table called company. In that table we will have:
ID column of data type int, which will be primary key(what is primary key?), not null means it's required.
Name column of data type Text is Unicode
ADDRESS column of data type CHAR is str
SALARAY column of data type REAL is float

And do we need an ID column in a table, or can we just skip it?

[proven arrow]

Yeah that’s right

Primary key is the column or columns that is used to identify a row

The table would work without it, but you should always have one as it’s the basic rules of database design and will help you achieve the basic principles of normalisation

For database design you should try to follow the normal forms

[mortal light]

Normal forms, database normalization?

[proven arrow]

Yeah search it up

[mortal light]

ok

[hazy mango]

!warn 868129841155805185 Please don't drop random YouTube video advertisements in the middle of an ongoing conversation

[delicate field]

:incoming_envelope: :ok_hand: applied warning to @torn sphinx.

[crystal compass]

why tf do i get there the error:
':' expected, End of statement expected

@client.command()
async def test(message):
    settings = await guild_settings.find_one({"_id": int(message.guild.id)})
    update = settings["settings"]
    print(update)
    await guild_settings.update_one({"_id": int(message.guild.id), {'$set': {'settings': update.update({'name_b': 'name_b_val'})}}) #There btw
    print(update)

[near cradle]

you're probably missing an } before the comma inside update_one()

otherwise it just doesn't make syntactic sense

[crystal compass]

oh yea i didnt saw that thx

[crystal compass]

is that the right way to update a dict in a dict

@client.command()
async def test(message):
    settings = await guild_settings.find_one({"_id": int(message.guild.id)})
    update = settings["settings"]["audit"]
    print(update)
    await guild_settings.update_one({"_id": int(message.guild.id)}, {"$set": {"settings": {"audit" : update.update({"status": "enable"}}}}) #This
    print(update)

error: Syntax error

[mortal light]

Does psycopg2 follow query convention of postgreSQL?

[paper radish]

Hey everyone - I'm trying to pass a list to my sql query (for IN), and want to know the safe way to do it, that prevents sql injection, any help would be greatly appreciated!

[cunning jolt]

i normally dont do this, but this is a great article on sql injection and how to prevent them in python: https://realpython.com/prevent-python-sql-injection/

if you just want to get to the part where you prevent it tho: https://realpython.com/prevent-python-sql-injection/#crafting-safe-query-parameters

[paper radish]

I read it

its about strings

[cunning jolt]

yes

you will have to transform your list into a string at some point

[paper radish]

Ok then

SQL doesn’t take it as a string tho

[faint blade]

You can use prepared statements, this is very much dependent on your wrapper. Do you use aiosqlite?

[hollow verge]

Hello! Question about MySQL DB with Python.
Let say I have a DB with a structure with one of the row as Lock
I have multiple servers running a process that modify that DB
Because all the servers are querying the DB quickly to complete all the stacked task, how to prevent multiple server to work on the same task?
I have read about SELECT FOR UPDATE but I'm not sure if it's the right thing.
So in Python what is the best way to lock a row in MySQL??? 😄 Thanks
Ps.: I have read somewhere that every time you do a select it locks the row???

The only way I have found is to have a queues API that fetch the next available task and return it to the server that asked... But this need to be sequential to prevent the same problem no?

[faint blade]

You want only one server to be able to select a specific task? So that multiple servers don't select the same task?

In SQL there's this thing called a sequence, it's a counter that is guaranteed to have no race conditions where you may get the same number.

If you give each row an ID, can you not then use a sequence when getting a row?

Each server would grab the next number on the sequence, and then fetch the row with that ID.

Stuff to consider with this:

• What happens if there's no rows? The sequence would increment but there would be no row to fetch?
• What happens if a server goes down and can't (I assume you delete them afterwards) cleanup, previously this row would simply be handled by another server. Now it will be skipped entirely

@hollow verge

[crystal compass]

    settings = await guild_settings.find_one({"_id": int(message.guild.id)})
    update = settings["settings"]["audit"]
    await guild_settings.update_one({"_id": int(message.guild.id)}, {"$set": {"settings": {"audit": update.update(
        {"status": "enable"})}}})

My Problem is this code deletes everything in the dict settings and dont update in audit the status

This is how it normaly looks like

how can i do that in settings audit status gets updated to "enable"

[paper radish]

Sorry just saw this an hour later 😄 I'm using sqlite3

Does %(list)l work?

what are the chances that this is actually really easy and I just haven't tested it 😂

[hollow verge]

OK make sense but what about this?
Is it safe to assume that with multiple servers querying at the same time it will result in only one locked row?

UPDATE cards
SET doc_by = 'LOCKED'
WHERE id=
(SELECT id WHERE doc_by is Null LIMIT 1 FOR UPDATE);

I want to have one row locked to a server, the server process the row and release it and continue with the next available row.

[paper radish]

can't even test it because fast api isn't working

[torn sphinx]

I downloaded sqlite3 browser used the installer but can't find the app

searched it in my search thing

checked file explorer

everything

[cunning jolt]

iirc innodb(the engine mysql uses) does that automatically, but you might wanna read into it

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

here's the appropriate docs

i can't help you much further than that since i dont use mysql

[burnt turret]

https://docs.mongodb.com/manual/tutorial/query-embedded-documents/#query-on-nested-field you have to use dot notation

[crystal compass]

i already fixxed it ty

[burnt turret]

Ah nice

Didn't realize that was from many hours ago 😅

[quasi dome]

hmm

Can Truncate be a text function?

[paper radish]

why the heck can't i figure out how to do question marks 🤔

For the sqlite 3 library

[pure cypress]

Are you referring to the placeholder ? used to insert values from your code into the query?

[paper radish]

• seperated ?s

yes

[pure cypress]

Can you show the query you're trying to run?

[paper radish]

sure

cur.execute("SELECT * FROM a WHERE b IN (?) AND c IN (?);", (x, y))

[pure cypress]

I'm not sure if IN is valid to use if there is only 1 value in the list.

[paper radish]

x and y are lists

[pure cypress]

Oh I see

[paper radish]

it worked when i had one placeholder but not two

Now i get a sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

[pure cypress]

I may be incorrect, but I think you have to explicitly have a placeholder for each value in the list. You can't use a single placeholder for an arbitrarily-sized list and expect it to auto-expand.

[paper radish]

it's dynamically sized

🤔

would a %s or %l or something work

[pure cypress]

Yeah... I don't think it was designed to support that. Here is what I found. https://stackoverflow.com/a/1310001

So, you do need to use string formatting, but ONLY to put the right number of ?.

[paper radish]

thanks!

I saw something like that but dismissed it because i thought it was wrong lol

it works!

Thank you so much!

[pure cypress]

A tricky problem. I would think it's not that rare to encounter it, but unfortunately the docs don't give any advice on this.

[paper radish]

I can finally do some more work on my frontend 😆

Maybe I'll do a pr for the docs

Now just have to adjust it for more than one in 🤔

i guess i just merge the lists and have multiple question marks

[pure cypress]

That's probably simpler honestly

[paper radish]

just need a .format to get it working, easy

One last quick question if you wouldn't mind - Is there an easy way to to change queries based on what variables are defined? Since sometimes b and c will be empty but a will not or vice versa

[pure cypress]

I don't understand the question, sorry.

a is a table. b and c are columns of a.

[paper radish]

misphrased it lol

gtg bye

[pure cypress]

Oh I didn't notice you have separate INs for different columns. Maybe merging the lists would be a bad idea then.

Anyway, see you.

[paper radish]

Managed to get back on -do you have a suggestion?

[pure cypress]

Put placeholders for each list

Might be helpful to make a function to do that

def get_placeholders(lst):
  return ",".join("?" * len(lst))

Then you can "... IN {0} AND ... IN {1}".format(get_placeholders(x), get_placeholders(y))

[paper radish]

and then I have to merge the lists afaik

[pure cypress]

No, you do not.

You can use the formatted query to do cur.execute(query, (x, y))

Ah wait

I see what you mean

Cause the lists aren't expanded

[paper radish]

it error's out when i do that

[pure cypress]

(*x, *y) I think will work

[paper radish]

I'll try it in the morning - 10:17 here 😄

Thank you for all your help!

[pure cypress]

You're welcome.

Sorry if I confused you with that

You were right after all, they do have to be merged.

[paper radish]

wait then this doesn't work?

[pure cypress]

Lost sight of that since we were focusing on placeholders

Yeah, it will

if they're both lists then x + y works too

[paper radish]

just realized what that does 🤦‍♂️

Thank you so much!

Plan is to use if statements to run different queries depending on which have content

gn

[grim vault]

Keep in mind that the update() method of an dictionary does the update in place but returns None:

>>> update = {"test": "old"}
>>> print(update)
{'test': 'old'}
>>> print(update.update({"test": "new"}))
None
>>> print(update)
{'test': 'new'}
>>>```

so ... {"audit": update.update({"status": "enable"}) ... will set audit to None

[calm prawn]

I can't seem to install asyncpg. I am getting this error

Can someone help me?

[long dome]

I'm using python with asyncpg for a local POSTGRESQL Database. Is there anyway I can see the current database like actually visually and that allows me to edit it without it being by command lines?

[unkempt prism]

You can use https://pgmodeler.io/

[faint blade]

Use the included pgAdmin

[jade reef]

INSERT INTO uuids (ign, uuid) VALUES('test','2ba64a33e0a6464eaf0f57aa86bd42f') ON CONFLICT (uuid) DO UPDATE SET ign = 'test';

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
(uuids are the same)
im new to sql so any help is appreciated, basically what im trying to do is add a new entry of ign | uuid, and if uuid is already there, then update the ign (uuid can never change but ign can)

[proven arrow]

You need have a unique constraint on the uuid column so the conflict can be found

[jade reef]

so like this?

CREATE TABLE uuids (
    ign VARCHAR(16) NOT NULL,
    uuid CHAR(32) NOT NULL,
    UNIQUE(uuid)
);

[proven arrow]

Yeah and also Postgres has a uuid column type as well

[jade reef]

yeah but im pretty sure that only supports dashed?

and i need undashed

not sure tho

[proven arrow]

Yeah its ok then

[shell ocean]

why?

[jade reef]

oh wait nvm

dashed works just fine i never tested whether or not it works lol

[rapid narwhal]

This is my database

crsr.execute("SELECT * FROM guildprefixes") 
check = crsr.fetchall()
print(check)

This returns [(672061097364291644, '%'), (760415029983051776, '&')] which a list with 2 tuple values in it

What's the most efficient way to get the value of a specific guildid other than iterating through all of them and checking individually

[jade reef]

so i made my first thingy with postgresql, theres prob a way to make it faster (if there is pls tell) (and yes ik about requests module i will learn aiohttp soon)

async def uuid(bot, ign):
    try:
        mcuuid = await bot.db.fetch("SELECT uuid FROM uuids WHERE ign = $1", ign)
        if len(mcuuid) == 0:
            raise KeyError
        mcuuid = mcuuid[0].get("uuid")
        return mcuuid

    except KeyError:
        request = requests.get(f"https://api.mojang.com/users/profiles/minecraft/{ign}")
        if request.status_code == 200:
            r = request.json()

            mcuuid = r["id"]
            await bot.db.execute(
                "INSERT INTO uuids (ign, uuid) VALUES($1, $2) ON CONFLICT (uuid) DO UPDATE SET ign = $1",
                ign, mcuuid)
            return mcuuid

        else:
            return request.status_code

[faint blade]

SELECT guildprefix FROM guildprefixes WHERE guildid = ?

[faint blade]

That looks good, other than the fact that you can technically use fetchval and skip a lot of checking

[jade reef]

can u link resource on how to use it?

[rapid narwhal]

Oh ty :D!

[faint blade]

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.pool.Pool.fetchval

[jade reef]

ty

[jade reef]

i changed it to this and it works, lmk if something can still be made better

async def ae(bot, ign):
    mcuuid = await bot.db.fetchval("SELECT uuid FROM uuids WHERE ign = $1", ign)

    if mcuuid is None:
        request = requests.get(f"https://api.mojang.com/users/profiles/minecraft/{ign}")
        if request.status_code == 200:
            r = request.json()

            mcuuid = r["id"]
            await bot.db.execute(
                "INSERT INTO uuids (ign, uuid) VALUES($1, $2) ON CONFLICT (uuid) DO UPDATE SET ign = $1",
                ign, mcuuid)
            return mcuuid
        else:
            return request.status_code

    else:
        return mcuuid

[faint blade]

Well you change it to be ```py
if mcuuid is not None:
return mcuuid

And then unindent all other code.

[jade reef]

thats much cleaner thanks

[grim vault]

I don't know, but you should not return two different types (mcuuid or request.status_code) if you can avoid it.

[austere portal]

And requests is blocking

[split basalt]

In my linux laptop(zorin OS) I want to use mysql.connector to connect to mysql via python.
In my windows, I used to access mysql in terminal using
mysql -u root -p
And proceed to give the mysql password
But in linux I use
sudo mariadb
And use my system password.
Now...

    host = "localhost",
    user = "root",
    password = "password",
    database = "database"
)```
doesnt work if I use mysql password or system password(sudo password).
It says 
``mysql.connector.errors.ProgrammingError: 1698 (28000): Access denied for user 'root'@'localhost'``

How do I connect to mysql using python in linux?

[pale jay]

does root user have all the perms? I had to create a new user

[split basalt]

How do you check permissions? I think it has permissions to create db, tables etc cos I did them.

[pale jay]

USE mysql;
SELECT * FROM user;

gonna be a messy table in terminal lol

[split basalt]

yes

it is 😆

[pale jay]

try doing 127.0.0.1 instead of localhost

[split basalt]

1. its localhost

2. The password it shows is not the password I set

[pale jay]

hmm?

[split basalt]

3. All are Y except for password_expired and is_role

[pale jay]

try host = "127.0.0.1"

[split basalt]

nope

dint work

[pale jay]

want to try creating a new user?

[split basalt]

My friend suggests I change password

[split basalt]

DM the code man. Would totally appreciate it

[pale jay]

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
``` @split basalt

[split basalt]

muchas gracias amigo

One more thing

[split basalt]

try this in ur pc and tell me if the password it shows is the same password you had set or is it a long string of chars

[pale jay]

it's hashed

[wraith shell]

Hi, Is anyone aware of how one can find all the loaded objects in SQLAlchemy. I have to do Session.expire() on some of them basis the type of object but not on all with Session.expire_all()

[pale jay]

you shouldn't just store the actual password in the database

[split basalt]

Mine is
*7F7Q025D730C58FF881504B140C993456D4385S2

1. Is it encrypted or something?
2. The password I had set was something like "mariadbpass"
3. Its not the actual ones, I changed some chars...dont wanna share passwords here

[long dome]

What am I doing wrong here? when adding a row to a POSTGRESQL DB with ASYNCPG:

querystring= "INSERT INTO main (discordid, waxid, balance, speed, strength, agility, endurance, level, unassignedstats, rank, landx, landy,state,totalxp,energy,energyph) VALUES ($1,$2,0,5,5,5,5,1,0,'Test',$3,$4,0,0,100,10)"
await conn.execute(querystring, str(member),str(waxid),landx,landy)

nothing gets added

[pale jay]

it's hashed

[split basalt]

ooo

when you first said its hased I thought it would be ########

[pale jay]

no

For security reasons, you may want to store passwords in hashed form. This guards against the possibility that someone who gains unauthorized access to the database can retrieve the passwords of every user in the system. Hashing performs a one-way transformation on a password, turning the password into another String, called the hashed password. “One-way” means that it is practically impossible to go the other way - to turn the hashed password back into the original password. There are several mathematically complex hashing algorithms that fulfill these needs.

[split basalt]

A quick google and I learnt something today

How do we unhash it tho?

[pale jay]

depends on the hashing algorithms

might be a question to ask in #cybersecurity

[split basalt]

Then thats where I am gonna go

Thanks for helping me man appreciate it a lotttt

[grim vault]

A hash is one way only, there is no unhash. You should read the whole quote from Arnav.

[pale jay]

also this was a followup for that quote, sorry for confusing you if I did

[crude shard]

If someone is good in py mongo can they take a look at #help-croissant

[pale jay]

should I use https://www.mysqltutorial.org/mysql-bit/ or tinyint(1) for boolean?

[faint blade]

What wrapper do you use?

[pale jay]

mysql-connector-python

also just in general

[faint blade]

If it was up to me, I would first test both. Perhaps mysql-connector will correctly convert it to bool for one of them?

[pale jay]

alright

I remeber tinyint(1) working for rust

[proven arrow]

MySQL will use tinyint internally for bool

[faint blade]

You can also use a trick I found online, set it to CHAR(0). NULL will be false and '' will be true.

But that will not have a nice interface inside Python because it's either an empty string or None (both considered falsely).

[pale jay]

oh alright

[pale jay]

lol

[faint blade]

Yes the bool aliases to tinyint

[torn sphinx]

Hi, How should I keep emojis in the database?

[austere portal]

doesnt mysql have a bool data type?

[pale jay]

MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1)
ah

[pale jay]

just found out lol

[faint blade]

What kind of emojis? Discord emojis?

[torn sphinx]

Yes, Discord emojis

but not custom emojis

[faint blade]

No custom ones? Only Discord's own emojis?

[torn sphinx]

Right

[harsh pulsar]

are you keeping a counter of emoji usages or something?

you can store them as discord's emoji names like :slight_smile:

[torn sphinx]

Do these Discord's emoji names change?

[pale jay]

idts

[harsh pulsar]

good question, i have no idea! i assume they don't change frequently, but they might change

i think most/all of them correspond to unicode characters, but i don't know if that correspondence is published or accessible

[torn sphinx]

but not custom emojis

[torn sphinx]

I understand,
I want to keep it as bytecode

>>> x = "emoji".encode("unicode_escape")
>>> x
b'\u2642\ufe0f'
>>> str(x, "unicode_escape")
emoji

[harsh pulsar]

in discord.py it looks like they support "true" unicode emoji in at least some places

https://discordpy.readthedocs.io/en/stable/api.html?highlight=emote#discord.Reaction.emoji

you might have to experiment

[foggy vapor]

Which is the most performant SQL database, that can do read/write operations concurrently?

My python-based discord bot is on hundreds of servers, and my current database(sql3lite db on disk) is bottlenecking the application(sql3lite also can't read/write/etc concurrently)

I'm thinking about using the fairly "new" asyncio SQLAlchemy package, and would like to know which database would be the best, than can scale.

Any suggestions?

[austere portal]

Postgresql

[faint blade]

Agreed, PostgreSQL with asyncpg

[foggy vapor]

@faint bladeI'm planning to use an ORM: SQLAlchemy has a new asyncio package

https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

[faint blade]

Personally I dislike ORMs as a whole, but that could be because I know SQL. Up to you but I cannot help you in that regard sorry

[foggy vapor]

even tho I also know SQL, I like to use ORM, because it makes your job easier

[weary fulcrum]

how do i create a secret key for my rest api with flask so no one can hack it??

[proven arrow]

You should worry less about the “most performant” as it’s unlikely you will ever get to the point where the database you pick is going to significantly improve your performance. It’s like asking if you should pick python when there are languages that would be more performant also. Other databases like sql server or MySQL, mariadb would also do the job. Learn that shitty design and architecture, badly written code etc will most likely be your bottleneck, and that performance benchmarks don’t always reflect real world problems.

[weary fulcrum]

how do i create a secret key for my rest api with flask so no one can hack it??

[proven arrow]

Flask, databases, secret key, hack? Not sure what’s the correlation?

Maybe ask in #web-development

[weary fulcrum]

no no

see i created a flask app

and then anyone who knows the link at which my app is hosting can access

the data

so how do i protect it

[brave bridge]

SQLite can read concurrently, just can't read/write together with another write. So if you're writing concurrently a lot, I guess you could look at PostgreSQL/MySQL

[brave bridge]

why?

[weary fulcrum]

first of all i eant to know how to host a flask restful api

[foggy vapor]

@proven arrowthat can be also a reason, but

I wrote a benchmark, that shows when in prime time - when thousands of users using the bot itself - it sometimes takes seconds to query the database

switching to postgres will probably solve this issue

[proven arrow]

Well i didnt mean sqlite. Obviously you should replace that, but i was talking about the server based dbs

[brave bridge]

I would assume postgres and other more sophisticated DBMSs have more sophisticated query optimizers

so that could improve the performance

[proven arrow]

Well for such use cases sqlite will always become a problem eventually, as their docs state: "SQLite does not compete with client/server databases. SQLite competes with fopen()."

[foggy vapor]

I didn't know my bot will be as popular as it is, sooo I didn't really care about a decent database 🙂

sqllite was easy to setup

you don't need docker, and other stuff

[brave bridge]

well, if your code has a decent architecture, it might not be as much of a task to move to postgres

perhaps you could make a portion of the bot use postgres and see how it improves?

just to be sure, you're using an async wrapper around sqlite3 like aiosqlite, right?

[foggy vapor]

@brave bridgeyep

[dawn valley]

If any one working in ETL with Python
How you are doing insertion to SQL server from a oracle ?
things i have done

1. Pandas + SQLAlchmey + PyODBC + executemany —> Not achieving better performance
2. pandas.to_sql is not helping
3. bulk insert —> i can't perform this. because of permission issues
4. Finally bcp —> which is some what good
   Any other suggestions

[harsh pulsar]

what exactly is the problem? too slow?

how much faster does it have to be? you might have to just use BCP

[torn sphinx]

When using sqlite3

when making a db

for numbers

would you do text?

or is there like a NUMBER one

[foggy vapor]

there are integer and real number(float) types

[frail heath]

hello i am currently trying to use MongoDB and have this error when trying to find a specific piece of data in a document TypeError: 'AsyncIOMotorCursor' object is not iterable with this code ``` for x in MongoUserData.find({"MemberId": "{}".format(useridmon)},{"Cash": 1}):
print(x)

[brave bridge]

I think it only supports async for

[ionic pecan]

are you 100% sure that sqlite is bottlenecking it? as in, have you checked whether all queries are slow or some queries are outliers? have you added indexes?

[foggy vapor]

@ionic pecan will see after I finished with implementing, and migrating the database

it's high time I learn to use docker 😄

[frail heath]

ah thx

[ionic pecan]

docker and databases 👻👻👻

[brave bridge]

Do you really need docker?

[foggy vapor]

@brave bridgenot really, but would be nice to finally learn to use it, and dockerize a project with it

[brazen charm]

Honestly, I use docker for everything

the ability to quickly spin something up without needing to download the setup, worry about OS compatibility etc... its just too nice lol

[torn sphinx]

@client.command()
async def work(ctx):
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute(f"SELECT money FROM main")
    result = cursor.fetchone()
    if result is None:
        return```

This is my code

I'm trying to make a work command

I just can't figure out what to do next

[paper radish]

Hey! Quick sql question - i am doing where statements but I still want it to return based on the other where statements if one of the IN lists is empty

[paper radish]

i can help a bit 👍

what are you trying to do?

[torn sphinx]

I'm getting help right now

I'll let you know if I need helper further

[paper radish]

on .work return money?

[paper radish]

wtf did my internet just do - this message just appeared above my question about what you're doing 🤔

[paper radish]

Meanwhile I'll just use a ton of if statements 👍

[dense barn]

I have number 1 in a row, and i want to add that with 1, how would I do that instead of fetching it first [result] and then doing result+1?

[burnt turret]

you'd use an UPDATE query

UPDATE table SET column = column + 1 WHERE ...

[dense barn]

Oh that's big brain

Column = column + 1

Didn't think of that

Thanks I'll try that

[dire depot]

what datatype should i use to store 64 bit unsigned ints in a postgres database?

will text cause any problems?

[dense barn]

Bigint should work

[dire depot]

bigint is signed

range isnt large enough

[dense barn]

Ah

[austere portal]

Use NUMERIC

[desert sandal]

mycursor = mydb.cursor()

sql = "SELECT * FROM players WHERE Name Like '%xyz%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(#something which gives output as the respective values of the row in which xyz is found)```

can someone help me with what can be in that #

abc                    2                     aaa
xyz                    7                     bbb```

[desert sandal]

for example if this is the table in MySQL and input is xyz then it should print 7 and bbb

[faint blade]

print(x[1], x[2])

Like that right?

[desert sandal]

oh f that's so easy ?

thanks 🙏

[desert sandal]

mycursor = mydb.cursor()

sql = "SELECT * FROM players WHERE Name Like '%Kimmich%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

@client.command(name="search")
async def search(ctx, player):
    for player in myresult:
        await ctx.send(f"x[1], x[2], x[3], x[4]")``` @faint blade this isn't working ![pepesad](https://cdn.discordapp.com/emojis/793773328849436682.webp?size=128 "pepesad")

oh i made a mistake

realised it

[faint blade]

You need to put the expressions (x[1]) inside curly brackets

[desert sandal]

yeah also another thing

thanks

sorry for the ping

[faint blade]

It should be {x[1]},

[faint blade]

No worries

[desert sandal]

@client.command(name="search")
async def search(ctx, player):
    mycursor = mydb.cursor()
    sql = "SELECT * FROM players WHERE Name Like '%{player}%'"
    mycursor.execute(sql)
    myresult = mycursor.fetchall()
    for player in myresult:
        await ctx.send(f"{x[1]}, {x[2]}, {x[3]}, {x[4]}")``` how do i fix this

[desert sandal]

need help

[grim vault]

You need to use binding vars:

    sql = "SELECT * FROM players WHERE Name Like ?"
    val = (f"%{player}%",)
    mycursor.execute(sql, val)```
The `?` maybe some other placeholder, it depends on your db-module (maybe it's `%s`)

[desert sandal]

ahh ohk thanks man

[austere portal]

x is not defined

[foggy vapor]

@desert sandalnever use string interpolation to insert values into a sql command: look "sql injection"

if you use sqlite: "cursor.execute("INSERT INTO books VALUES(?, ?, 0)", (name, author))"

? is the placeholder, and you need to pass the values in a tuple

[desert sandal]

oh ohk

thanks

[foggy vapor]

if you use string interpolation, users can inject codes that will run with the command 😄

[desert sandal]

ohh

didn't know that

[foggy vapor]

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

[desert sandal]

mydb = mysql.connector.connect(
  host="localhost",
  user="name",
  password="pw",
  database="xyz"
)``` this is in my main.py in my git repo which is deployed as bot.. it shows this error 
`mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)`

how do i fix this

[faint blade]

Well, do you have MySQL installed where you are hosting the bot?

[chilly temple]

x

[desert sandal]

The bot is hosted atm on Heroku with my git repo deployed

[faint blade]

Which means that the answer is no, you'll need to find some service that can host a MySQL database for you

[desert sandal]

Ahh ohk.. You know any ?

[faint blade]

No sorry

[foggy vapor]

https://devcenter.heroku.com/articles/cleardb

[desert sandal]

Wow thanks a lot

[foggy vapor]

google, first result

[desert sandal]

[cunning jolt]

well my suggestion would be to get a small vps and deploy on there

deploying your bot on heroku has multiple not that nice implications

[desert sandal]

Yeah but I'm a student and can't really buy a vps so Heroku is the best option for. Me atm

[dawn valley]

looking for better performance
so looking for suggestions

[shrewd frigate]

Can someone help. I've tried to get my database to store reasons but it keeps coming up like this.

[cunning jolt]

if you have stable enough internet you could buy a rasberry pi

but its up to you

[burnt turret]

show your queries

it looks like you're running insert_x twice separately instead of a single one

[stiff halo]

Hey

I'm not great at ER diagrams or logical designs but I gotta do one in this past paper I found

OrderItem (OrderNo, ItemCode, Quantity, OrderDate, Description, CustomerNo,

CreditLimit, DeliveryAddress)

The following Functional Dependencies (FDs) apply to OrderItem:

FD1 OrderNo, ItemCode -> Quantity

FD2 OrderNo, ItemCode -> OrderDate

FD3 ItemCode -> Description

FD4 OrderNo -> CustomerNo

FD5 CustomerNo -> CreditLimit

FD6 CustomerNo -> DeliveryAddress

[uneven stream]

if I narrow down fliters will they get faster?

instead of WHERE user_id = 1 using```sql
WHERE user_name = 'xxxx' AND user_id = 1 ...

[torn sphinx]

I get this error and i dont know how to fix it, im using replit and i havent ever used pip for it because its automatic

[pure sleet]

read the last line

how did you even get pymongo on replit?

just repeat the process but replace pymongo with pymongo[srv]

[harsh pulsar]

no, just filter on id

maybe your table should have an index on user_id if that query is too slow for your needs

[stiff gale]

@grim zephyr What is your database structure, then i can help you

[grim zephyr]

hi

[stiff gale]

so i assume you are using sql and cursors?

so to retrieve data you do something like cursor.execute("SQL")

[grim zephyr]

yep

[gentle roost]

what is a database journal in relation to sqlite?

[stiff gale]

allright

so what value are you trying to get and how is your database structured

i can figure it out too if you have a CREATE TABLE command for me

[austere portal]

its a temporary database file created by SQLite during a transaction between an application and a database

[gentle roost]

gotcha.

thanks a bunch

[austere portal]

It contains the most recent state of the database

[gentle roost]

OH gotcha gotcha

[shrewd frigate]

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

if that makes sense.

[austere portal]

You can use a select query

with a WHERE condition

[shrewd frigate]

How? I'm new to databases.

[austere portal]

Did you create the table?

[grim zephyr]

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

unsupported but why i have str value

[austere portal]

show code..

[shrewd frigate]

I'm using mongoDB so it creates itself.

[grim zephyr]

@austere portal ```py
Traceback (most recent call last):
File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

db_name = 'warn.db'

@client.event
async def on_ready():
    print("Bot is online")
    db = await aiosqlite.connect(db_name)
    cursor = await db.cursor()


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


@client.command()
async def warn(ctx, member: discord.Member, reason = None):
    db = await aiosqlite.connect(db_name)
    cursor = await db.cursor()
    await cursor.execute("SELECT * FROM warn WHERE guild_id = ? AND user_ID = ?", (ctx.guild.id, member.id))
    await cursor.execute("UPDATE warn SET warn_num = warn_num + 1 WHERE user_ID = ? AND guild_id = ?",(member.id, ctx.guild.id))
    data = await cursor.fetchone()

    if data is None:
        await cursor.execute("INSERT INTO warn(guild_id) VALUES (?)", (ctx.guild.id))
        await cursor.execute("INSERT INTO warn(user_ID) VALUES (?)", (member.id))
        await cursor.execute("INSERT INTO warn(warn_num) VALUES (?)", (str(1)))
        await db.commit()```

[stiff gale]

that doesnt have anything to do with databases i think

[grim zephyr]

i think maybe it has

[stiff gale]

i would reccommend changing your warn_num to an int

since its a number

[grim zephyr]

ok

[harsh pulsar]

@grim zephyr (member.id) is not a length-1 tuple, it's just member.id

you want (member.id,)

[shrewd frigate]

Any ideas?

[harsh pulsar]

or if you prefer, you can use a list, [member.id]

!paste @shrewd frigate you'll have to be more specific about what is and isn't working, and you'll also have to share your code. see below 👇

[delicate field]

Pasting large amounts of code

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

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

[grim zephyr]

can u explain what is a tuple

[stiff gale]

oh yeh

[harsh pulsar]

it's a type of sequence. a length-2 tuple (a, b) is also called a "pair". it's a good data structure for representing things like rows of a database, keys and values of a lookup table, etc.