databases

[sage hill]

it works on my laptop, but i'm cpu throttled

[fast kestrel]

Hi, if anyone mind providing their input, I’d appreciate it. I’m wondering if using raw SQL with Django’s ORM is safe, or if there are disadvantages to doing so.

[shell ocean]

Hi, if anyone mind providing their input, I’d appreciate it. I’m wondering if using raw SQL with Django’s ORM is safe, or if there are disadvantages to doing so.
@fast kestrel "safe" in what way?

like "won't cause an error" safe or "prevents SQL injection" safe

[fast kestrel]

SQL injection attacks. I didn’t consider them potentially causing an error.

[shell ocean]

SQL injection attacks. I didn’t consider them potentially causing an error.
@fast kestrel https://docs.djangoproject.com/en/3.1/topics/db/sql/#passing-parameters-into-raw

[fast kestrel]

Actually just started reading that page. I haven’t gotten down to that section. When it strongly suggested to use the ORM first, i second guessed my decision

I’ll read over it some more. My hesitation to use the ORM is that the query seemed more complicated than the raw sql that works in MySQL workbench

Thanks 🙏

[shell ocean]

I’ll read over it some more. My hesitation to use the ORM is that the query seemed more complicated than the raw sql that works in MySQL workbench
@fast kestrel what do you want to do?

[woven galleon]

Does anyone know how I could use JSON to store a dictionary of user ids with information like I would with a normal dictionary?

[tepid cradle]

You just use json.dump() to store it in a text file. If you want to add to an existing file, use json.dumps() to create a json string and write it to a text file.
Don't forget import json at the top of your script

[long slate]

How do i store a python dict in postgreSQL database ?

and i am using asyncpg

Hello ?

[tepid cradle]

Do you want to store the JSON directly or convert it to a tabular format and store it? @long slate

[long slate]

idk what is the good way of storing i just want to store the dict into database

and i should be able to convert that back easily

@tepid cradle

[tepid cradle]

If you're using relational database, then converting it to tabular format is a better way of storing it. But then you'll have to use sql queries, converting it back to dict is not a great idea and doesn't really work in the long run

Are you familiar with SQL queries?

[long slate]

oh

Are you familiar with SQL queries?
Nope

[tepid cradle]

If you're going to be using Postgres, you'll have to learn at least the basics of SQL. Otherwise you're better of storing the data in a JSON file for now. But that is not a long term solution.

SQL is easy, you can learn the basics in like 2 hours

[long slate]

I am not that fammilar with SQL lol

i mean i know a bit of SQL

i watched a video learn SQL in one video

Hello

so i know bit of sql

Yes but how you are able to use discord because you need to be aleast 13

i asked it in programmer server they told me to convert it into tabuler form is there any function or easy way to do that ?

[bold pelican]

oh well, @long slate

use, SQL queries directly

don't use dict

[burnt turret]

I'm having issues running pgAdmin4 (the starting up splash thing appears, but the UI never opens up. Is this a common issue? How do I fix it?

[tepid cradle]

Have you tried right clicking on the tray icon and selecting open new window? @burnt turret

[burnt turret]

is this what you mean? there's no option like that though

[tepid cradle]

No, there's an icon near the time. Click on the little up arrow near the time, pgadmin icon should be there. If it's not, then that's a different problem

[burnt turret]

Oh right

Okay so when I check there, there's no icon for pgadmin

When I start running one, it momentarily appears, then disappears

[tepid cradle]

Did you try restarting your computer? PGAdmin is not running at all for you

[burnt turret]

Oh, alright. Didn't think of restarting, I'll do it now

Alright so I restarted, and I kept getting the same issue

Tried a couple of times and one time this popped up

Pretty sure I've got Brave set up as my default browser though.

Is it unsupported or something? Should I change my default browser?

[tepid cradle]

Browser doesn't matter, it just opens an http link, Windows will send it to whatever is set as default

[burnt turret]

Oh, but I've got a default browser set up

Any idea what else could be the issue?

[torn sphinx]

Hey @burnt turret, I’m getting this error for sometime now. It says there is no data in prefix. But my bot is only in like 3 servers and those 3 server’s prefixes are stored in the db. So how can I fix this?

[burnt turret]

huh that's odd

show the code

Yeah I tried setting it to IE, still the same

and i can't do what you've shown in the image because it stays on there for like half a second

[proven arrow]

Strange, does the shell work?

[burnt turret]

psql? yeah

I'm considering just reinstalling it, maybe something might've gone wrong during setup

[indigo steeple]

anyone want to help me understand databases?

[proven arrow]

Yeah strange, also maybe you can just redownload pgadmin if it lets you as a standalone app. It might overwrite the existing install of it

https://www.pgadmin.org/download/

[torn sphinx]

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

async def get_prefix(bot, msg):
  db = cluster["Data"]
  data = db['prefixes'].find_one({"_id": msg.guild.id})
  prefix = data['prefix']
  return prefix
  await client.process_commands(msg)

Here’s my code. Pretty sure I don’t need await client.process_commands(msg) here 🤔

[indigo steeple]

Anyone know about how to represent relations in pgadmin4/postgreSQL? I'm a bit confused on this.

[burnt turret]

SCRUZ, that db['prefixes'].find_one({"_id": msg.guild.id}) seems to be not finding any matches

maybe print msg.guild.id and see if you're getting expected values/data types?

[torn sphinx]

ok. i’ll do that later. I’m busy rn. Thanks.

will let you know if i find any errors

[tepid cradle]

Anyone know about how to represent relations in pgadmin4/postgreSQL? I'm a bit confused on this.
@indigo steeple Don't ask to ask your question. That doesn't serve any purpose. There are many active users here, there will be someone who knows about your issue. But if you don't ask your question, they can't reply to it.
So just go ahead and ask your questions.

[indigo steeple]

ok sure

I'm confused about relationships. Are relationships represented as tables?

[tepid cradle]

Relationships are represented as foreign keys. Usually one table's foreign key refers to another table's primary key.
For instance, if you have a master table for employees, and another table where you make an entry every month for their pay, then the employees table will have employee_id as the primary key and monthly_pay table will have employee_id as foreign key referring to employees table.

It is important to understand that foreign keys are only used for maintaining data integrity. Like monthly_pay table will not accept any value for employee_id which does not exist in employees table.
But foreign key does not help you query data, you have to define the relationships in the query for every query.

At the same time, it is not essential to have a foreign key relationship for querying data. You can define the relationships on the fly inside your query itself. It will work regardless of whether you have foreign key or not.

[indigo steeple]

Hmm ok thank you. I'm new to SQL but I think I'm following.

I have some relationship that I am representing, which has two foreign keys from two other tables (I assume these are master tables, I'm not familiar with the term.). So I wasn't sure about how to represent the relationship but I think that I should make a table for the relationship which has two foreign keys (and maybe some more non-foreign columns). I'm not sure if this relationship table should have a primary key though.

[tepid cradle]

Master table is not a defined term, it's just often used to refer to tables which contain primary, non-transactional data. But people may use other terms as well.
Relationships can be one-to-many or many-to-many. One-to-many relationships are easily represented with foreign keys. For many to many relationships, you usually need to create a mapping table.

[burnt turret]

New to postgres, how do I set a default value for a column?

[indigo steeple]

@tepid cradle thank you sir, I think I got it now.

[tepid cradle]

New to postgres, how do I set a default value for a column?
@burnt turret These questions can be easily and quickly answered if you just Google them

[torn sphinx]

so i tried to get a specific pokemons row and generation row
py @commands.command() async def csv(self, ctx, pokemon): name = pokemon with open('rby.csv', newline='') as csvfile: reader = csv.DictReader(csvfile) for name in reader: print(name['Pokemon'], name['Generation']) await ctx.send((name['Pokemon'], name['Generation']))
with the csv file on https://github.com/rby2k20/rby2k20/blob/master/data/RBY2k20Data-RBYPokemon.csv

but it basically gave me EVERY pokemons inf
info
while i only wanted pikachu for example
any solutions?
(basically getting only these two info)
https://cdn.discordapp.com/attachments/738572311107469354/749203903383601202/unknown.png

and not
https://cdn.discordapp.com/attachments/738572311107469354/749203969254883338/unknown.png

so the name argument has a input where i type pikachu

and i basically only want pikachus row (26)

not the whole row

like every pokemon row including generation row

[weak charm]

@torn sphinx you still need help?

[torn sphinx]

yes

[burnt turret]

How do we make prepared statements (is that what they're called) in asyncpg?

[weak charm]

@torn sphinx the name = pokemon have no usage, because when you use that for loop name get another value, so i guess you can remove it, and for getting just one pokemon by name you can try in that for loop to compare pokemon variable with name['Pokemon'] and await ctx.send((name['Pokemon'], name['Generation'])) only if they are equal, be aware of case sensitivity, and try renaming the variables more organized

[cold marten]

Has anyone used MySQL in Python? I tried installing it today and it keeps saying I don't have admin privilege's to access the database (even though my account is). I installed MySQL through the package manager and got everything set up expect the Python Connector as I was met with the same error. I tried to install the python connector manually, but again, I was met with the same error. I installed the mysql.connector with PIP in a virtual environment without any problems but I still get the same error when trying to connect. Has anyone else had this issue?

[quaint tiger]

Has anyone else had this issue?
@cold marten first, make sure you can connect with the mysql client, not python.

[cold marten]

I am new to MySQL and still learning the basics. How do I set up a connection in the workbench?

Yes, I believe I am able to connect with the client

[rough hearth]

Theoretically speaking, can you have an entity that could have an identifying relationship to one of two other types of entities?

[torn sphinx]

@weak charm

        @commands.command()
        async def csv(self, ctx, pokemon):
            with open('rby.csv', newline='') as csvfile:
                reader = csv.DictReader(csvfile)
                for name in reader:
                    if name['pokemon'] == pokemon:
                        print(name['Pokemon'], name['Generation'])
                        await ctx.send((name['Pokemon'], name['Generation']))
                    else:
                        return```

basically right?

File "/usr/local/lib/python3.6/dist-packages/discord/ext/commands/core.py", line 94, in wrapped raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: KeyError: 'pokemon' but that would've return a KeyErrror

[cold marten]

I figured out my issue. Install mysql-connector instead of mysql-connector-python

[torn sphinx]

        @commands.command()
        async def csv(self, ctx, pokemon):
            with open('rby.csv', newline='') as csvfile:
                reader = csv.DictReader(csvfile)
                for name in reader:
                    if await ctx.send((name['Pokemon'])) == pokemon:
                        await ctx.send((name['Pokemon']))
                    if await ctx.send((name['Pokemon'])) != pokemon:
                        return
``` return True but gives me bulbasaur if i try pikachu

How can i insert more data into the db?

    @commands.command()
    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.Member, *, reason:str=None):
      ok=f'📩 Warning issued to {member.mention} successfully.'
      if reason == None:
        await ctx.send("You need to pass in a warning.")
      elif reason == reason:
        warn_data={"_id": member.id,
          "warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
        db=cluster["Data"]
        db["warns"].insert_one(warn_data)
        em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
        em.set_author(name="Infraction Received!")
        await member.send(embed=em)
        await ctx.send(ok)

This is the error:

[burnt turret]

That is probably because that user had already been warned once

So the second warn tries to insert another field now, but can't because a field already exists with that user in there

[torn sphinx]

yeah so how can i make this

warnTo:"SCRUZ"
warnBy:415529530518929408
warning: "testing database warn logs"

something like this:

warnTo:"SCRUZ"
warnBy:415529530518929408
warning: "testing database warn logs", "test 2"

[burnt turret]

But that would not be recording the warnTo and warnBy for the rest of the warns

[torn sphinx]

ah.

[burnt turret]

You're storing the warns member-wise?

[torn sphinx]

so just make a new file and search for the same _id ?

[burnt turret]

nah that seems unnecessary

[torn sphinx]

You're storing the warns member-wise?
@burnt turret i’m not sure how to store it

[burnt turret]

You can be making those fields into lists

wait a sec let me think how that would work

[torn sphinx]

👌

[burnt turret]

meanwhile can you tell me how you print out the entire exception to the chat lmao

[torn sphinx]

exception as in errors?

[burnt turret]

yeah

Okay so here's what I think would work the neatest:
Make your data structured like this:

_id: 415529530518929408
warns: [
  {warnTo: "SCRUZ", 
   warnBy: 415529530518929408,
   warning: "testing database warn logs"},
  {warnTo: ...}
]

Basically make warns the key, and the value a list of dictionaries, each dictionary containing all the data of a warn

Then after that to add to that list of warns, you would prepare a data dict like you already do, but without the _id part of it

After that you would be using update statements instead of inserts

db["warns"].update_one({"_id":member.id}, {"$addToSet":{"warns":warn_data}}, upsert=True)

I think this should work @torn sphinx
Here's the documentation on $addToSet operator - https://docs.mongodb.com/manual/reference/operator/update/addToSet/#up._S_addToSet
tl;dr on it is that it adds something to a set if it already doesn't exist
Again here the upsert makes sure to create a new field if one doesn't already exist

[torn sphinx]

ah tysm

My error handlers: https://pastebin.com/GuRszfQ7 @burnt turret
^ pretty sure this prints the exception to chat
Mainly just await ctx.send(str(error))

[burnt turret]

Thanks :)

[torn sphinx]

Hey @burnt turret, it says warns is not defined
Code so far:

    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.Member, *, reason:str=None):
      ok=f'📩 Warning issued to {member.mention} successfully.'
      if reason == None:
        await ctx.send("You need to pass in a warning.")
      elif reason == reason:
        warn_data={"_id": member.id,
        warns:[
          {"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
                  ]
                         }
        db=cluster["Data"]
        db["warns"].update_one({"_id": member.id},{"$addToSet": {"warns": warn_data}}, upsert=True)
        em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
        em.set_author(name="Infraction Received!")
        await member.send(embed=em)
        await ctx.send(ok)

[burnt turret]

        warns:[
          {"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}]}
``` You've used `:` instead of `=`.
Also this seems to be invalid syntax, why's there an extra curly brace outside the `]`? @torn sphinx

oh wait

[torn sphinx]

oo

[burnt turret]

you gotta make warns a string

like "warns"

my bad, the indentation threw me off

[torn sphinx]

ah ok 👍

so should i make : to =

[burnt turret]

Yeah one sec

[torn sphinx]

kk

[burnt turret]

Because i think it should be a list now, and not a dict

because you're using the addToSet operator

i think you can replace this

        warns:[
          {"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}]}

with

warn_data = {"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}

And then in the query try to add this element to the array, warns

I think the simple way to do it would be to

1. fetch the member's existing warns
2. as the warns field is a list, you could append warn_data to that list
3. set the entire list back as the value
   I say this because the query will become something easier to understand, and probably what you're already familiar with

something like

db = cluster['Data']
existing_data =  db["warns"].find_one({"_id":member.id})
existing_warns_list = existing_data["warns"]

Then you would prepare the warn_data dict as usual, then do existing_warns_list.append(warn_data)
Then to push the entire data back you would do

 db["warns"].update_one({"_id":member.id}, {"$set":existing_warns_list}, upsert=True)

You'd have to do some try: ...except:... for in case it is the user's first warn (as that find_one would return None)
(wow i type a lot)

[torn sphinx]

Lol. Thank you so much. I’ll do it. I’m a bit confused on this part (<#databases message>).

[burnt turret]

That was just one option, if you wanted to follow what I'd said earlier today (using the $addToSet operator)

You can follow the second option too, which seems easier to understand (at least for me) (this being the second option <#databases message>)

[torn sphinx]

alright, thanks :)

[torn sphinx]

I'm attempting to create a discord bot that incorporates time-limited mutes (this is a database question don't shoo me to #discord-bots). To do this, I'm attempting to upon muting someone, it saves the duration of the mute and the mute targets Discord ID to a CSV file (to then go back to later and check if it's time to unmute). Upon unmuting them automatically later, it removes said userid/duration from the CSV. I don't have trouble utilizing CSV, however, what would be the 'best' or most Pythonic way to prevent my CSV from having a bunch of blank lines wherever a user has been unmuted, and instead just shift everything up as high as it can be (to get rid of the blank lines)?

[craggy jackal]

I would not use a CSV file and use an actual database library

@torn sphinx

[torn sphinx]

For example?

[craggy jackal]

asyncpg

sqlalchemy

sqlite

I would argue that it would be far less a headache than trying CSV.

[torn sphinx]

Which is suggested?

And CSV appears rather simple to me?

[craggy jackal]

CSV is slow and won't scale and will be hard to query specific entries.

asyncpg is probably your best bet, I think it is relatively simply.

For example I also have timed punishments in my bot.

This is the query I do to find people who need to be un-punished.

[brazen charm]

CSV is also DiskIO bound which will block your bot

[craggy jackal]

If you wanted to do something like that you would use JSON instead of CSV.

However still asyncpg is the best.

[torn sphinx]

I was more-so looking for the smallest file format possible, but JSON in the past has been a bit iffy for me on size.

I'll look into Asyncpg. Thanks.

[craggy jackal]

SQL will have better memory utilization than CSV.

And be 10000x quicker

And SQL is a language so the hard work is done for you

[torn sphinx]

Question though. I assume when grabbing data from the file Asyncpg will have an await on that. What if when it gives up control (via the await), my mute command occurs and then writes to said data at basically the same time?

[brazen charm]

asyncpg isnt a file

its the async driver to bind to a PostgreSQL server

[torn sphinx]

I meant the command, but I haven't looked at it yet, so I can't say the actual command.

[brazen charm]

Which is a dedicated Database Server you'll need to setup

[craggy jackal]

Databases at their core are files, however they are heavily optimized

[torn sphinx]

The command used to pull data from the SQL file.

I assume it's asynchronous

yes?

[craggy jackal]

Yes

With asyncpg it is

asyncpg is an async wrapper around postgres

[brazen charm]

postgre itself is sync

[torn sphinx]

So what happens when I do that, but at the same time my mute command attempts to add to said database?

Would they conflict?

[brazen charm]

no

[craggy jackal]

No

[brazen charm]

The requests are async the database itself is not

[torn sphinx]

Ok, and how does this solve my issue of blank lines?

[brazen charm]

so you wont run into the issue of data races

[craggy jackal]

SQL manages the table for you

[torn sphinx]

Sweet.

[craggy jackal]

You'll learn to love databases

[torn sphinx]

I hope so.

[craggy jackal]

They are the core of all software engineering really

[torn sphinx]

I also currently store a little bit of data in a small JSON file. It's just a dictionary with 4 key/value pairs. I don't see a need to put that into a database of it's own since it's so small. Will that be an issue?

(also for a discord bot)

[craggy jackal]

Also if you develop with windows WSL (I use WSL2) it will make everything way easier too

A bot configuration can be in something like JSON (not recommended imo)

I use a config.py for my bot configuration

And I have a per guild configuration table

[torn sphinx]

It's simply storing the roleIDs for set roles (which can be changed via bot command)

{"muted": 0, "administrator": 0, "moderator": 0, "donator": 0}

[craggy jackal]

You would want to be that into a database if you wanted those values to be guild-specific

However if its a 1 guild bot

That is fine

One of the hardest parts of making a bot is making everything guild-agnostic (I think I am using that correctly)

[torn sphinx]

Yeah it's a one guild bot.

I've made cogs in a way that can be plugged into other bots though.

But this part is this-guild exclusive.

[craggy jackal]

Yep then that is fine

You might consider using a config.py instead

It is more versatile than JSON

[torn sphinx]

Please elaborate on what that is.

I know what config.ini is, not config.py

[craggy jackal]

So it is just a python module you import where you need the config

For example

Let's say I had the following:

connection_string = 'POSTGRES_CONNECTION_STRING'
token = 'TOKEN_HERE'
log_dir = 'logs/'

Anywhere you need these values

You can import config

And then I can do config.connection_string

To fetch that value

[torn sphinx]

That code block is the only thing you put in config.py?

[craggy jackal]

And if you wanted more complex data in there, it's a python file so you can do anything

For my bot, yes it is the only thing that is like for the entire bot

Everything guild-specific for mine is in a database

[torn sphinx]

And how would you go about updating that config.py from within the bot?

[craggy jackal]

I never have a reason to change anything I put in there but if yours is configurable it's easier to just use JSON.

[torn sphinx]

Yeah, mine's intended to be configurable via command.

Just for conveniences sake.

@craggy jackal Since you said you have timed punishments for your bot. I assume to have it continuously check if it's time to unmute/punish someone, you have a tasks in the background running once every X seconds. How many seconds do you wait?

[craggy jackal]

I do 15 but you could do less if you wanted tbh

[torn sphinx]

Just curious, as someone in discord.py suggested like 1 minute the other day but I thought that- that would make it a bit slow/delayed.

And a couple more questions, actually.

[craggy jackal]

+- 1 minute on automatic punishment undoings isn't a big deal

[torn sphinx]

First off, what file format is an asyncpg database?

[craggy jackal]

I wouldn't think of it as a file...

How it works is implementation defined

[torn sphinx]

Well, I just have no idea how to create the initial file to work with.

[craggy jackal]

A databases is a server that you send queries to

Queries can insert, update, select, and delete data

You don't create any file

[torn sphinx]

Sure, but my database will just be stored within the same folder as the bot, I assume? I'd rather not get another server just for the database.

[craggy jackal]

You install Postgres, create the database and associated tables within Postgres (table creation can be done thru your bot)

And then make a connection in your python code

The database is stored on the operating system its running on

Don't worry about its location

So, are you using WSL?

[torn sphinx]

WSL?

What if I were to transfer the server that the bot is running on? How would I go about transferring punishment data?

[craggy jackal]

Ultimately, all the data is persisted on disk so transfer is possible, however...

The best way to transfer might be a simple python script

Where you make a connection to the local server and remote server

And perform some queries that copy over all the tables

[minor ruin]

no

[runic pilot]

this is the exact use case for a database provider

[minor ruin]

PostGres has dump function where it export everything into file with SQL commands to put it back

[runic pilot]

separate your db server and app server

[minor ruin]

https://www.postgresql.org/docs/current/backup-dump.html

[craggy jackal]

Wasn't aware of that postgres feature :)

[minor ruin]

just about every database server has it

Maria/MySQL/MSSQL

[runic pilot]

most databases have that so that you can recover from a failover

[craggy jackal]

And for a small project where you are learning databases I would say using a DB server separately is a bit overkill

[runic pilot]

heroku is an example that offers a free postgres db

[craggy jackal]

Although good practice obviously

[runic pilot]

(though limited)

[torn sphinx]

Yeah but I'll be hosting the bot on a small server once it's complete so.

Anyhow.

How do you go about recommending I check if the database exists, and if it doesn't, create it? Like, when should I check it? On cog load, or other?

Or will it create it automatically in the event it doesn't exist.

[craggy jackal]

You create a database within the server and tables within the database

[runic pilot]

that's usually a one-off task, since it's supposed to be done once

[craggy jackal]

I create the database manually

Then table creation automatically

You can create tables if they don't exist

[runic pilot]

use a migration tool like alembic to help create and keep track up tables and table updates

[minor ruin]

and if it's a truly tiny bot that won't grow, there is always SQLite

[craggy jackal]

Creating the database is as simple as specifying a name and then specifying a user that has access to it.

[torn sphinx]

I've used a small amount of SQLite in the past, but don't remember what for.

I'll take a deeper look into this once I get home from work. Thanks for answering my questions! Helped significantly!

@burnt turret like this?

    @commands.command()
    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.Member, *, reason:str=None):
      ok=f'📩 Warning issued to {member.mention} successfully.'
      if reason == None:
        await ctx.send("You need to pass in a warning.")
      elif reason == reason:
        db=cluster["Data"]
        existing_data=db["warns"].find_one({"_id":member.id})
        existing_warns_list=existing_data["warns"]
        warn_data={"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
        existing_warns_list.append(warn_data)
        db["warns"].update_one({"_id":member.id}, {"$set":existing_warns_list}, upsert=True)
        em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
        em.set_author(name="Infraction Received!")
        await member.send(embed=em)
        await ctx.send(ok)```

oh wait. nvm. I messed up

same error.

KeyError: 'warns'

[burnt turret]

on which line?

[torn sphinx]

uhm

[burnt turret]

oof

What does the data look like on mongodb rn

I'm guessing it is because the data hasn't been restructured to look like this now

[torn sphinx]

i didn’t input anything new. It’s still the same

[burnt turret]

yeah that's why it is happening

wait a sec

#databases message
you have to structure your data like this first

pretty sure new entries will be automatically added in this format because of upsert

but you'd wanna do some try: ... except:... that time, because existing_data=db["warns"].find_one({"_id":member.id}) this will evaluate to None for a member with no warns yet

[torn sphinx]

i have to manually edit the db ?

like this #databases message

[burnt turret]

Only for the person who is already existing in there

[torn sphinx]

alright

[burnt turret]

add the try except code, and then try warning someone who hasnt been warned yet

[torn sphinx]

👍

i’ll do

bruh how do i edit this? I tried to edit but i mess up so i just hit the cancel button.

[burnt turret]

oh yeah it can be confusing

i guess just remove that entire field

and let the code add it

just dont forget the try: except: then

[torn sphinx]

ok lemme try

wait, so i just delete this?

and let the code add it
Ares#7286 i’m gonna delete this

[burnt turret]

yeah

[restive stone]

Okay so I have this Discord bot that uses sqlite3. I have a few commands that pull from the db like the example below. I was wondering once I make a connection to the db should I close it? Cache it? If so how would I go about doing that?

    @commands.command(aliases=["changeprefix", "prefixchange", "newprefix", "new_prefix"])
    @commands.has_permissions(manage_channels=True)
    async def prefix(self, ctx, prefix):
        embed = discord.Embed(
            title="You did not send a valid prefix!",
            colour=discord.Colour.red()
        )
        if not prefix:
            return await ctx.send(embed=embed)
        if len(prefix) > 5:
            too_long = discord.Embed(
                colour=discord.Colour.red(),
                title=f'That prefix is too long!'
            )
            await ctx.send(embed=too_long)
            return
        list = self.client.cur.execute(f"SELECT * FROM prefix WHERE guild={ctx.guild.id}").fetchall()
        try:
            if list == []:
                prefix_changed1 = discord.Embed(
                title=f"Prefix changed to `{prefix}`",
                colour=discord.Colour.green()
            )
                await ctx.send(embed=prefix_changed1)
                self.client.cur.execute(f"INSERT INTO prefix(guild, prefix) VALUES({ctx.guild.id}, '-')")
                self.client.con.commit()
            else:
                self.client.cur.execute(f"UPDATE prefix SET prefix = '{prefix}' WHERE guild = {ctx.guild.id}")
                self.client.con.commit()
                prefix_changed2 = discord.Embed(
                    title=f"Prefix changed to `{prefix}`",
                    colour=discord.Colour.green()
                )
                await ctx.send(embed=prefix_changed2)
        except Exception as e:
            print(e)

[hasty juniper]

``

@restive stone use async db

[restive stone]

I would have to learn a whole new db 💀

[hasty juniper]

https://pypi.org/project/aiosqlite/

no, it's easy

i used to prefix json

yes in a year I have not had any problems with him

[rough hearth]

Are you allowed to have an identifying relationship where two entities combined map to one entity?

[torn sphinx]

@burnt turret, this is what i got so far. You can check back when you’re free. It’s late here rn, so i’ll check back later.

    @commands.command()
    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.Member, *, reason:str=None):
      ok=f'📩 Warning issued to {member.mention} successfully.'
      if reason == None:
        await ctx.send("You need to pass in a warning.")
      elif reason == reason:
        try:
          db=cluster["Data"]
          existing_data=db["warns"].find_one({"_id":member.id})
          existing_warns_list=existing_data["warns"]
          warn_data={"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
          existing_warns_list.append(warn_data)
          db["warns"].update_one({"_id":member.id}, {"$set":existing_warns_list}, upsert=True)
          em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
          em.set_author(name="Infraction Received!")
          await member.send(embed=em)
          await ctx.send(ok)
        except:
          db=cluster['Data']
          warn_data={"_id": member.id,
          "warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
          db["warns"].insert_one(warn_data)
          await ctx.send("Warning applied.")

[brazen charm]

you have UserData and Data

[cinder dome]

What database is that using?

[earnest parcel]

MongoDB

[burnt turret]

@torn sphinx sorry for the late reply, I'd gone to sleep
The error is being raised because in the first warn you arent insert the data in the right format
That part should be making a warns field who's value is a list

except:
          db=cluster['UserData']
          warn_data=[{
          "warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}]
          db["warns"].insert_one({"_id":member.id, "warns":warn_data})
          await ctx.send("Warning applied.")

ugh indentation got messed up but you get the idea

now this makes the warns field a list of dictionaries, so that from the second warn on, a dictionary of details of that warn will be appended to this list

[daring mica]

Can someone help me with a project regarding python and mysql connector
I'm a complete noob at python. See I have created a mysql table consisting names of 3 voters. And in the python program I want it to access those names from the mysql table and when the user is inputting their names into it I want the program to verify with the mysql table names and if the names match then allow the person to vote and if not print a message saying your not allowed to vote.
The question is how to do so

[inland stone]

why is sqlite so shit

[fallow elm]

sqlite powers an incredible amount of software

maybe you're using it for a bad use case

[inland stone]

my use case is very light

it's just a software i'm building, serving only my business from the EC2 server

but the ec2 doesn't have enough memory to run mysql

[fleet kiln]

hi guys, i am trying to install mongo db on a windows 7 32 bit machine and i got this journal not supoorted warning.

what does it mean?

[fallow elm]

well sqlite isn't really meant for concurrent writers. i'm not sure what you're trying to use it for though

[inland stone]

so yeah the automated processes will run singularly

using a lock

and I will change the db from my personal machine and update the file also using a lock

[fallow elm]

@fleet kiln the journal provides a log of changes so if the database crashes you don't lose data

it's saying that's disabled by default on 32bit windows and if you want that behaviour you have to pass the --journal flag

[fleet kiln]

could you guide me how to do that @fallow elm ?

[inland stone]

my current problem is that I'm trying to import a csv into an empty db and i'm forced to create the table first with the same number of columns. I'm reluctant to do that.

[fallow elm]

@fleet kiln the error tells you what to do

just add --journal

[inland stone]

yeah

[fallow elm]

to the rest of your command

[inland stone]

even i knew that hehe

[fallow elm]

@inland stone sqlite is a relational database, you can't import data without specifying the columns ahead of time

it requires a strict schema

[inland stone]

yeah i really wanted to use a nosql one actually but couldn't find the right solution, any advice?

given how noob i am

[fallow elm]

how much data is it?

[inland stone]

few thousand

[fallow elm]

that's nothing for a computer. just leave it in csv and loop over it

[fleet kiln]

thanks @fallow elm I was not sure if anything extra to be added like -- journal enable or something, but now it worked

[inland stone]

hmmph.

[fallow elm]

@inland stone i often grab 500MB network dumps and turn them into csv's with tshark and i can aggregate all the data in python in a couple seconds

[inland stone]

trying to think how elegant this would be in order to do some stuff

it does sound like i would have harder work to write python automations, no?

than i would using provided libraries e.g. sqlite3?

or would it be more like just using other words to say the same things?

[fallow elm]

you lose the query language and have to write python to do aggregations/etc

wonder if you could use this as a library https://github.com/Ragnarok540/csvql

lets you run sql over csv files

[inland stone]

lets check it out

"csvql has no requirements or dependencies other than Python 3"

already taking to it

[fallow elm]

i'm thinking this might just convert it to sqlite under the covers

haha

[inland stone]

its first use is converting csv to sqlite which is what i was looking for earlier

[fallow elm]

maybe that solves your problem then

[inland stone]

just trying to run it out of the box it says

reading '1112.csv'
Traceback (most recent call last):
  File "/usr/local/bin/csvql", line 7, in <module>
    from csvql import main
  File "/usr/local/lib/python3.6/site-packages/csvql.py", line 373, in <module>
    args.func(args)
  File "/usr/local/lib/python3.6/site-packages/csvql.py", line 237, in _import
    db.create_table(args.table_name, columns, types)
  File "/usr/local/lib/python3.6/site-packages/csvql.py", line 60, in create_table
    self.query_db(sql)
  File "/usr/local/lib/python3.6/site-packages/csvql.py", line 41, in query_db
    cur.execute(query, args)
sqlite3.OperationalError: near "-": syntax error

be hard to debug this one.

[fallow elm]

does your csv have a header

[inland stone]

it does have a header line yes

[fleet kiln]

sorry to interrupt you guys, but now i have this new error and it is not letting me start mongo, I tried googling up for the hotfix but it seems microsoft does not have it anymore

[tepid cradle]

@fleet kiln which version of MongoDB are you installing?

[torn sphinx]

@torn sphinx sorry for the late reply, I'd gone to sleep
The error is being raised because in the first warn you arent insert the data in the right format
That part should be making a warns field who's value is a list

except:
          db=cluster['UserData']
          warn_data=[{
          "warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}]
          db["warns"].insert_one({"_id":member.id, "warns":warn_data})
          await ctx.send("Warning applied.")

@burnt turret Thanks :)

[fleet kiln]

@tepid cradle 3.2.21 , i have a windows 7 32 bit OS

[tepid cradle]

Ah, ok. Was checking because the latest version doesn't support Windows 7, only Windows 10.
Any particular reason for using an older version of Windows?

[fleet kiln]

Ah, ok. Was checking because the latest version doesn't support Windows 7, only Windows 10.
Any particular reason for using an older version of Windows?
@tepid cradle i have not tried installing windows 10 and i am planning to buy a new laptop soon

actually my use case is that for my spider bot project, i want to store the crawled links into a data base

[tepid cradle]

I'm not exactly solving your exact problem here, but you could try using MongoDB's free tier Atlas. It will be slightly slower than a local instance of course, but it will be easier to setup

[fleet kiln]

sure, i will look into it

[tepid cradle]

And unless there is a specific reason for using MongoDB, you could also use Sqlite for this purpose

Robo 3t is just a GUI to work with MongoDB, you'll still need the MongoDB instance

[inland stone]

sql sucks though 😦

[tepid cradle]

Uh, no

[fleet kiln]

Robo 3t is just a GUI to work with MongoDB, you'll still need the MongoDB instance
@tepid cradle yea sorry i am just a noob 😅

[tepid cradle]

SQL is the most awesome thing. It's very straightforward compared to most languages.
For instance, look at something like this:

select dt, sum(value) over(order by dt rows between 3 preceding and current row)
  from some_table

Even if you have no experience with SQL, you'll still be able to guess what that query does
@inland stone Any particular reason why you think SQL sucks?

@tepid cradle yea sorry i am just a noob 😅
@fleet kiln No worries, that's how my journey started as well. The first time I wanted to use a database, I installed MySQL Workbench and couldn't understand where the database was 😆

[inland stone]

@tepid cradle mostly because I'm a complete noob, I suspect 🙂

I just feel like it's so limiting

[fleet kiln]

it does look very readable, but could you tell me why we prefer sql when we have pandas?

[inland stone]

like the fact you have to tell it how many columns there are in a table before you import to it

[tepid cradle]

The basics of SQL are easy to pick up, but the advanced stuff can be a bit daunting. Not to say I'm some kind of expert, but I have written a few advanced queries

[inland stone]

SQL is the most awesome thing. It's very straightforward compared to most languages.
For instance, look at something like this:

select dt, sum(value) over(order by dt rows between 3 preceding and current row)
  from some_table

Even if you have no experience with SQL, you'll still be able to guess what that query does
@inland stone Any particular reason why you think SQL sucks?
@tepid cradle actually... i'm having some trouble :\

[tepid cradle]

Pandas is not a database, it's just a dataframe for manipulating data

[inland stone]

nosql like mongo just seems so easy to me, you can add just add stuff, get stuff, there's nothing to sync and relate and an item is just an item

[tepid cradle]

Think of it like this, if you have a table with daily data and you want the maximum value in each month, to use Pandas you'll have to fetch the entire table and then summarise. That's a lot of wasted bandwidth and unnecessary latency. If you write SQL query for the same thing, you can just summarise the data and fetch that directly. It will be much faster

nosql like mongo just seems so easy to me, you can add just add stuff, get stuff, there's nothing to sync and relate and an item is just an item
@inland stone Yes, it seems easier to get started with. But in a realistic environment, you eventually realise that data is meaningless unless you structure it some way. So even with MongoDB, a real production environment will have to define a schema, otherwise the code won't know what to do.

[inland stone]

that does make sense yes

[tepid cradle]

And data is inherently relational. You can't stuff all data in a single collection. Eventually you'll have to split it into multiple collections. At that point, the ability to quickly and easily perform joins, the way you can in SQL, will be sorely missed.

[torn sphinx]

@burnt turret Uhm, it’s showing me Duplicate KeyError :/

    @commands.command()
    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.Member, *, reason:str=None):
      ok=f'📩 Warning issued to {member.mention} successfully.'
      if reason == None:
        await ctx.send("You need to pass in a warning.")
      elif reason == reason:
        try:
          db=cluster["Data"]
          existing_data=db["warns"].find_one({"_id":member.id})
          existing_warns_list=existing_data["warns"]
          warn_data={"warnTo": member.name,
          "warnBy": ctx.author.id,
          "warning": reason}
          existing_warns_list.append(warn_data)
          db["warns"].update_one({"_id":member.id}, {"$set":existing_warns_list}, upsert=True)
          em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
          em.set_author(name="Infraction Received!")
          await member.send(embed=em)
          await ctx.send(ok)
        except:
          db=cluster['Data']
          warn_data=[{
            "warnTo": member.name,
            "warnBy": ctx.author.id,
            "warning": reason
            }]
          db["warns"].insert_one({"_id": member.id, "warns": warn_data})
          em=discord.Embed(colour=discord.Colour.orange(),description=f"You have Received a warning.\n`Warning:` {reason}")
          em.set_author(name="Infraction Received!")
          await member.send(embed=em)
          await ctx.send(ok)

This is how it looks in the db rn.

[tepid cradle]

Think of it like this, if you have a table with daily data and you want the maximum value in each month, to use Pandas you'll have to fetch the entire table and then summarise. That's a lot of wasted bandwidth and unnecessary latency. If you write SQL query for the same thing, you can just summarise the data and fetch that directly. It will be much faster
@fleet kiln does this answer your question? I've only just touched the surface here of course

[burnt turret]

yeah @torn sphinx that looks right

[torn sphinx]

it’s giving me dup key error

[fleet kiln]

@fleet kiln does this answer your question? I've only just touched the surface here of course
@tepid cradle yes I understand it now 🙂

[torn sphinx]

in the _id part

gives me dup key error

[burnt turret]

oh wait lemme read

[torn sphinx]

fyi: The Database is named UserData i don’t want others to think that i’m storing Literal User Data. That’s why I change it to Data.

[burnt turret]

{"$set":existing_warns_list} has to be {"$set":{"warns":existing_warns_list}}

[torn sphinx]

alright let me try

Yeah it works :D

tysm 👍

now i understand how it stores

[burnt turret]

👍

[torn sphinx]

ok, so now i’m trying to get the warning:

    @commands.command(aliases=['warnings', 'past', 'infractions'])
    @commands.guild_only()
    @commands.has_permissions(manage_messages=True)
    async def infraction(self, ctx, member: discord.Member):
      db=cluster["Data"]
      existing_data=db["warns"].find_one({"_id":member.id})
      existing_warns_list=existing_data["warns"]
      warning=existing_warns_list["warning"] # Is this how i get the `warning:` ?
      em=discord.Embed(colour=discord.Colour.blue(),title=f"{member}’s Total Infractions")
      em.add_field(name="Infractions",value=warning)
      await ctx.send(embed=em)

It shows TypeError

TypeError: list indices must be integers or slices, not str

[torn sphinx]

@burnt turret

[shell ocean]

@burnt turret
@torn sphinx what do you think taht error means?

[burnt turret]

oof sorry I was in class

the error says it

existing_warns_list is a list

so it only accepts integer indexes, and not strings like what you've done warning=existing_warns_list["warning"]

how are you trying to get the warning?

i mean how are you getting the specific warn

if you just want the list of all the warns till then, that is existing_warns_list

[torn sphinx]

oof sorry I was in class
@burnt turret oh sorry . u can check back when ur free

[burnt turret]

I've got a few mins now

the error is basically what i said ^

[torn sphinx]

yeah i’m trying to get the warnings that’s it

[burnt turret]

if you just want an embed with all the warns, you could iterate through the existing_warns_list and add fields for each of them

[torn sphinx]

ah ok

[burnt turret]

for warning in existing_warns_list:
  embed.add_field(...)   #format in any way you want, warning is a 
                           #dictionary, with the keys you had specified

[torn sphinx]

Thanks 👍

i’ll do it

[tepid cradle]

How do I update a postgresql table from a Pandas dataframe?
I have a table like

+-----+-------+
| id  | value |
+-----+-------+
| 1   |  23   |
| 2   |  45   |
+-----+-------+

And a Pandas dataframe like this;

+-----+-------+-----------+
| id  | value | updated   |
+-----+-------+-----------+
| 1   |  23   |     67    |
| 2   |  45   |     98    |
+-----+-------+-----------+

I want to add this column to the postgresql table

nevermind, I just imported the whole table, merged it with my df, and put it back

[timber karma]

Hi guys

is it possible to see the logs of mysql database?

for example who logged

[hardy sonnet]

idk why its saying this

I have a table called xp 😠

[eternal raptor]

https://mystb.in/BoardHansenFunds.python

correct path to table?

[tawdry fjord]

if you make a dictionary a class you, is it so you call the method passing in the arguments which are inserted into the dictionary, then u can use that freely, or create another one by just calling the method again?

[restive stone]

How do I import aiosqlite

Cause import aiosqlite sends the error Unable to import aiosqlite

[hasty juniper]

@restive stone u install it

[restive stone]

Yes

with pip

[hasty juniper]

u can write pip list

and check that

[restive stone]

aiosqlite 0.15.0

[hasty juniper]

sry idk

mb thats help u

https://stackoverflow.com/questions/63221777/ive-installed-aiosqlite-but-it-wont-import

[restive stone]

Nope 🙁

[burnt turret]

is there a way to put a limit on the maximum number of rows a table can have? (postgres)

[long slate]

Hello

How do i covert dict into tabular format

what is the easy way of doing that

[shell ocean]

is there a way to put a limit on the maximum number of rows a table can have? (postgres)
@burnt turret set a trigger

[burnt turret]

Oh, can you give me some example?

[long slate]

how to convert dict into table in using SQL ?

i am using postgresql and a asyncpg

[shell ocean]

Oh, can you give me some example?
@burnt turret https://gis.stackexchange.com/questions/261652/limit-the-number-of-rows-allowed-in-a-table-in-postgresql

how to convert dict into table in using SQL ?
@long slate depends on what your dict looks like

they are fundamentally different structures

[burnt turret]

Thanks 😄

[long slate]

My dict look like this py d = { 'Something#2323' : ['V1', 'V2', 'V3'] 'another#2325' : ['v1','v2'] }

@shell ocean

[shell ocean]

how do you expect to convert that to a table

like what do you think the result would look like in tabular form?

[long slate]

idk but i should be able to accecs that easily and convert that into a python dict again @shell ocean

[shell ocean]

well

if you don't even know what you want your data to look like when converted to a table

how can anyone help you?

why do you want to do this anyway?

sounds like an XY problem

do you just want to save your dict in something?

[long slate]

+-----------------+ 
|  key |  value   |
|  k1  | 1,24,asd |
| k2   | meber#@3 |
+-----------------+``` @shell ocean

like this ?

[shell ocean]

yes

but that looks nothing like your original dict

[long slate]

yeah

i have no idea

that's why

[lapis mulch]

you should model your data in a table oriented fashion

[long slate]

i want to store and convert it back

[lapis mulch]

i find any drawing tool helps if you can't conceptualize it in your head.

[shell ocean]

i want to store and convert it back
@long slate why must it be SQL?

imagine you have a deeply nested dict

[long slate]

Because i want to store in database

[shell ocean]

that's just not going to work in SQL

why not use something like MongoDB

SQL is meant for tabular data

(well, there're JSON fields nowadays but)

[long slate]

i know there is json

[lapis mulch]

relational databases do well with nested structures.

you just need to learn how to represent your data in tables.

the normal forms, etc.

[shell ocean]

relational databases do well with nested structures.
@lapis mulch yes, you can normalise your data

but it seems like it would be a bit too much work for this case?

feels more like they want to throw the whole dict in without bothering about that kind of thing

in which case a key-value store would be much more appropriate.

[broken estuary]

hey guys i got this 2 kinds of classifications:
if it is the 1st classification , i need to store it as a data in a column "delivery period",
and the 2nd , i need to store it as "contract duration".
so im just wondering, if the best way to tackle this is just create 2 columns? and just leave the other one Null?

[shell ocean]

hey guys i got this 2 kinds of classifications:
if it is the 1st classification , i need to store it as a data in a column "delivery period",
and the 2nd , i need to store it as "contract duration".
so im just wondering, if the best way to tackle this is just create 2 columns? and just leave the other one Null?
@broken estuary why must they be in the same table?

[broken estuary]

no the 2 classifications is in another table

[lapis mulch]

why not just duration and you interpret it in the application side?

both contract period and delivery period are a time interval

so just have it as a generic interval

and interpret it correctly based on the classification

[broken estuary]

youre actually right,

that is what i will do, that question is really dumb now that i think about that

thanks

[burnt turret]

how do i make prepared statements in asyncpg? (is that what they're called? the safe way to add user inputs in)

[fallow elm]

@burnt turret if you want prepared statements they are documented here: https://magicstack.github.io/asyncpg/current/api/index.html#prepared-statements but if you just want to use bind params (which is safe from sql injection) the example here shows them in use https://magicstack.github.io/asyncpg/current/usage.html#asyncpg-usage

[lapis mulch]

parameter binding and prepared statements are two different things. they are often used together but that's not a necessity

preparing a statement allows you to execute it multiple times without having to send the full sql text to the server over and over again

with a potential performance gain

you can of course prepare and execute a statement once during a request and afaik it's no slower than any old sql query

||aside: asyncpg's type conversion internals are a mess. it bounces between cython and python a lot, and there are basically no comments. you have to be an asyncpg maintainer to be able to understand that crap.||

[brazen charm]

You havent seen psycopg2's :P

[devout glen]

guys, quick question:
I got an app running continuously (while True) and it checks a table in pgsql for new content.
If there is new content, it does something, everyone happy. Then it resumes the same loop.
Is it possible to replace that timed loop with a "push" from pgsql? I know how to write a trigger a procedure upon every update, but how do I send that to an already running python script?

any ideas that would help me research the right thing would be much appreciated

[silver breach]

so you're running the same query N times ?

[minor ruin]

Chrisl25 use proper messaging system like RabbitMQ

[torn sphinx]

How do we use databases to store data? Is the data that we're talking about the codes or strings in our bot file?

[warped frigate]

is postgresql 10 fork-safe? I want to try using it with psycopg2 and multiprocessing, even though all my experience is with databases that aren't fork-safe (mongodb mostly)

[torn sphinx]

Does anyone know how to connect to a local database FILE using asyncpg?

[restive stone]

How can I get the latency for sqlite3?

like self.con.latency?

client.con = sqlite3.connect('prefixes_sqlite.db') is where I got con

Im in cogs btw

discord.py

[agile scaffold]

@restive stone I can't find anything on how to get the latency but if you are using it for discord I'd suggest looking up aiosqlite

the discord wrapper is completely async and if you are concerned about latency you should use aiosqlite

async wrapper for sqlite

[polar isle]

Hey, I am trying to sort values in a database by two values
query = f"SELECT * FROM {TABLE} ORDER BY wallet, bank DESC"
This is what I am trying right now but this doesnt seem to work. The database holds 3 values currently; id, wallet, bank. They are all BIGINT
I want it to sort the database by the wallet and bank values added together. Is there an easy way to do this with a query?

[tepid cradle]

@polar isle
Yes, order by wallet+bank

[polar isle]

oh thanks

[torn sphinx]

.

[tepid cradle]

@torn sphinx did you want to ask something?

[torn sphinx]

Nop

[meager vine]

Just so you know, your current command will order by wallet and then bank

And whilst it will do bank in descending order due to the DESC, it will do wallet in whatever the default order is (eg ASC for SQL Server)

If you want to ensure they are both ordered descending you need to do ORDER BY wallet DESC, bank DESC @polar isle

[polar isle]

@meager vine, by doing that ^ will it also do what i was trying?

[meager vine]

Ah no. It won't do the sum. I was just clarifying something about your original query I think was important to understand

btw if you want the sum in the select as well as the order by you can use a computed column in the order by

So like:
SELECT wallet + bank as tot FROM mytable ORDER BY tot DESC;

[torn sphinx]

Hello, so I currently have this leaderboard command that is getting users with most balance to lowest from this def on my database code and when I add a balance to a user in that guild then kick them, It shows them as "None" so I made it add that None to a list but I wanna figure how to say like "Top 3 richest" but with that None still in there it says "Top 4". My code is https://paste.pythondiscord.com/xeralomaja.py

Hope I explained that good xD

[fallow elm]

are you working on the same thing is the question asker above?

[torn sphinx]

Yup, I am

He was actually trying to help me XD but he had to go

[fallow elm]

i don't exactly understand your question.

[torn sphinx]

Frick I knew i didnt explain it good

[fallow elm]

do you just want to exclude None's?

[torn sphinx]

Yes yes

So like

There were 4 people with balance in the guild

I kick one of them

Now it counts them as a None

And I dont want my bot to count that None

[fallow elm]

is it null in the database?

[torn sphinx]

I am not sure

[fallow elm]

        for user in info:
            if user is None:
                pass
            else:
                users[user[0]] = user[1] + user[2]

this is more easily read as just

for user in info:
  if user is not None:
    users[user[0]] = user[1] + user[2]

having a condition for just a pass is not idiomatic

what are the columns in your table?

[torn sphinx]

It is

Just id BIGINT, wallet BIGINT and bank BIGINT

[fallow elm]

what database driver are you using?

[torn sphinx]

Sqlite3

[fallow elm]

pretty sure your fetch query could just be something like

def fetch_top(self, limit=10):
    query = f"SELECT id, (wallet + bank) AS balance FROM {TABLE} ORDER BY balance DESC LIMIT {limit}"
    self.cursor.execute(query)
    return  {
        row[0]: row[1]
        for row in self.cursor.fetchall()
    }   

also ```py
if ctx.guild.id != 710000162092613662:
return
else:

this `else` is unnecessary and pushes all your code right. if the condition is true then you return so the else is superfluous

[torn sphinx]

O ok

What should I put after the "for row"?

[fallow elm]

so the code you shared is still showing a None user?

you don't need anything after for row. that's a dict comprehension

that's the complete code for fetch_top

[torn sphinx]

Oh lol

[fallow elm]

@commands.command(aliases=["lb", "rich"])
@commands.guild_only()
async def leaderboard(self, ctx, number=5):
    if ctx.guild.id != 710000162092613662:
        return
        
    player = Player(self.bot, ctx)

    top = player.fetch_top(number=number)
    top_users = []

    for enum, user in enumerate(top.keys()):
        dcuser = discord.utils.get(ctx.guild.members, id=user)
        if dcuser is None:
            continue
        top_users.append(f'{enum+1}. {dcuser} - **${top[user]}**')

    embed = discord.Embed(
        title=f":moneybag: Top {len(top)} richest people in {ctx.guild.name} :moneybag:", 
        description='\n'.join(top_users), 
        colour=random.randint(0x000000, 0xFFFFFE)
    )
    await ctx.send(embed=embed)

i didn't really change anything in this function but cleaned it up a bit to be more idiomatic. i'd need to see what the incorrect output looks like to diagnose more

oh

you're doing len(top)

[torn sphinx]

Ye ye

[fallow elm]

you should be doing len(top_users)

[torn sphinx]

Oh

[fallow elm]

top has everyone from the database

[torn sphinx]

Oh LMAO

await DBCursor.execute("""
            DELETE FROM MuteData WHERE ID = ?;
        """, (12345))```
results in ``TypeError: '>=' not supported between instances of 'ValueError' and 'int'``
Unsure what I'm doing wrong. Any tips?

[fallow elm]

you should show the full traceback

i'm not sure that's coming from that line

[torn sphinx]

I've placed print statements directly before and after this line. The one before runs, the one after does not.

Rudimentary, but effective at narrowing down the cause of errors.

The only other thing of value in the traceback is parameters are of unsupported typeIgnoring exception in on_command_error

<class 'ValueError'>

Which is an error from something else

So can be ignored.

[fallow elm]

what is the type of ID in your database

[torn sphinx]

BigInt

[fallow elm]

what database driver are you using

[torn sphinx]

Driver?

[fallow elm]

which library

[torn sphinx]

aiosqlite

[fallow elm]

try (12345,)

notice the trailing comma

[torn sphinx]

That worked. Can you please explain why?

[fallow elm]

it expects a tuple of all parameters as the second argument

(12345) is not a tuple but the trailing comma makes it one

[torn sphinx]

Understood, thank you.

[fallow elm]

the error is very unhelpful though

[torn sphinx]

Sure is, lol.

Ty gary

[fallow elm]

no problem

[burnt turret]

I'm making a discord bot, and I'm unsure how I would structure my data to store tags
Should I make a table for every guild (maybe have it create a table when the bot joins a guild), or do I keep just one table for all guilds, and add a guild_id column to that table? Will I run into performance issues if I keep them all in one table, or would making a table for every guild be much more expensive?

[proven arrow]

Have a single table, and have the guild_id as the column with a unique constraint. There wont be any performance issues. Databases are made to handle large amount of data, and even if you were to have millions of rows, they wont be a problem.

Postgres is also very good at handling large data, if you do get to that stage, which is unlikely for a bot.

[burnt turret]

True, that makes sense. Is there some way for me to limit the number of rows per server if I was in a single table?

[proven arrow]

How do you mean limit rows?

[burnt turret]

I mean, to not let a server have more than x number of rows (tags), and if it tries inserting beyond that it raises an error maybe

[proven arrow]

Uh i think postgres has a trigger functionality, which you can use for this. Personally, ive never had a need to use it, since I would just handle this stuff in the application itself.

[burnt turret]

Oh alright. Yeah checking it in the code itself seems simpler. Thanks!

Have a single table, and have the guild_id as the column with a unique constraint. There wont be any performance issues. Databases are made to handle large amount of data, and even if you were to have millions of rows, they wont be a problem.
Just realized, wouldn't keeping the unique constraint on guild_id not let a guild have more than 1 row?

[proven arrow]

Well it kind of depends on what you are storing in this table? You may need to abstract it further and then reference the guild_id with a foreign key.

I assumed you wanted only one guild, but you don't need to keep it unique if that's what suits your needs.

[burnt turret]

What do you mean by 'abstract it further'? Aren't foreign keys used to sort of link two tables?

[proven arrow]

Yes they are used to reference tables together. I meant you may be storing more data than you need in a single table, so split it up so like each table would be storing a specific kind of data. So you may want one table which stores only guild data, and then another that references this guilds table when you need to use the guild_data, if that makes sense?

[burnt turret]

Oh okay yeah, I get it now. Thanks for the explanation

[flint wadi]

I'm working on profile pic upload. Which system makes more sense to you?

1. Create blank avatar db row on signup. Use update for any change.
2. Create db row on image upload. Use insert, update, delete.

The advantage I see from the first is no if row: insert, else: update, only update.
The disadvantage would be unnecessary database row for each user who has no picture.
Would it depend on website, e.g. Facebook chooses option 1, LinkedIn chooses option 2?
Is worrying about this premature optimization?

[restive stone]

@restive stone I can't find anything on how to get the latency but if you are using it for discord I'd suggest looking up aiosqlite
@agile scaffold Im moving to that when python stops being dumb and cant import it

and yes I have it intsalled

[earnest parcel]

It's probably just your ide being dumb

[flat swan]

I have old program, we use DBF for that. Do you know, how to connect DBF with Python ?

[hazy mango]

How can I do something likesql DELETE FROM $1 WHERE guild_id=$2in PostgreSQL?

The $1 gives a syntax error

[brazen charm]

string formatting

❗

you have to be very careful with it tho

generally with my ORM the table objects are self aware and pre format their queries with their name to stop any accidental injections

because honestly you deserve it if you name your table a injection attack

[hazy mango]

For context I'm basically trying to get all the tables in my database with a specified column (guild_id) and then delete rows where the guild_id is a specified value

Basically in a discord bot, when the bot leaves a guild I'll delete any information in my database pertaining to that guild

    @commands.Cog.listener()
    async def on_guild_remove(self, guild: discord.Guild):
        for table in self.bot.database_tables:
            _query = "SELECT TRUE FROM pg_attribute WHERE attrelid=$1::regclass AND attname=$2 AND NOT attisdropped"
            if await self.bot.execute_sql(_query, table, "guild_id", index=0, return_all=True):
                await self.bot.execute_sql("""DELETE FROM $1 WHERE guild_id=$2""", table, guild.id)
                self.bot.logger.info(f"Left a guild: ID - {guild.id}, NAME - {guild.name}")
```this is what I've got right now, where ```sql
self.bot.database_tables = SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'

It's a really hacky way but it works up to the DELETE FROM $1 ...

[brazen charm]

safest idea would be get all the table names on start, keep them safe and format you're queries directly and then just use them as and when needed

[hazy mango]

Yea I get the table names in the database at startup, then when the bot leaves a guild I filter those tables by the ones which have the guild_id column

[brazen charm]

normally any queries i make are constants and then clones of them are generated

same with any data types

[hazy mango]

So basically I need to use string formatting in place of the $1 here?

Not sure I really understand what's needed

@brazen charm

[torn sphinx]

s1 = (f"SELECT msg FROM react WHERE guild = ? AND msg = ??")

error

sqlite3.OperationalError: near "?" syntax error

please help me

[brazen charm]

@hazy mango yeah youll need string formatting

@torn sphinx ?? is wrong

only 1 ?

[torn sphinx]

ok

[hazy mango]

@brazen charm wdym by "string formatting"? Isn't that what $x is?

[brazen charm]

no

thats done 100% by the database

in this case you need to do literal string formatting with python's string fomatters

[hazy mango]

So like an f-string?

[brazen charm]

yh

[hazy mango]

Right, okay

I'll try that in a sec, thanks

That worked, ty 👍

[daring stratus]

Thought this blog was worth a share...I enjoyed seeing the similarities and differences of traditional software testing and testing your machine learning systems. It's crazy to me that testing data isn't
at the same standard of traditional software testing.
https://www.jeremyjordan.me/testing-ml/

[torn sphinx]

                dd1 = (f"DELETE msg FROM react WHERE guild = {ctx.guild.id} AND msg = {mes}")
``` any ideas why it not remove anything from db (in execute parameter) i really ned help

[hazy mango]

a) you shouldn't really use f-strings for database stuff
b) which sql database are you using?
c) did you commit the change?

@torn sphinx

[burnt turret]

You seem to be trying to delete the value from just one column now?

Wouldn't the statement be something like DELETE FROM react WHERE ...

[hazy mango]

If you want to delete the entire record then yes

Depends on use-case

[burnt turret]

Can we remove values from just one column with DELETE? Or would you update and put a Null in that place?

[hazy mango]

iirc you can remove just one column

[torn sphinx]

a) you shouldn't really use f-strings for database stuff
b) which sql database are you using?
c) did you commit the change?
@hazy mango a) i dont understand b) sqlite3 c) yes

[hazy mango]

Actually don't think you can

[burnt turret]

Yeah I'd thought so too

[hazy mango]

@torn sphinx For sql you should use ? syntax instead of f-strings etc.

execute(f"SELECT * FROM table WHERE row={val}") --> execute("SELECT * FROM table WHERE row=?", (val,))

And your actual issue is that you can't delete a singular column from a table using DELETE

If you want to remove the entire row then you do DELETE FROM table WHERE cond

[low iron]

My discord bot uses sqlite3 as a bot database but I want to switch to postgresql
I don't really know where to start

[hazy mango]

Do you know how postgresql works?

If not you can check the docs

General: https://magicstack.github.io/asyncpg/current/
Database Connection: https://magicstack.github.io/asyncpg/current/usage.html#connection-pools

In terms of where to start, basically completely remove all your sqlite stuff and gradually replace it with the postgresql equivalent

(asyncpg is the async driver of postgresql for Python btw)

[glass gorge]

what would be the uri for a locally hosted db?

I'm trying to hook the db up to a flask app

is it just the path

[quaint tiger]

What kind of DB?

If it is sqlite, you can normally just use a file path 'sqlite.db' or '/opt/my_app/sqlite.db'

If it is mongodb or mysql or postgres, you normally connect to localhost (or a .sock file).

[lucid haven]

hey

how can i export my table on mysql

i have a table with values in it, i want to export into a .sql file and send it to someone else

how would i do this

[tepid cradle]

would it work if you exported it as csv? SQL isn't exactly a data storage/transfer format. It's more for DDLs and such

[lucid haven]

ermm

yh

i can do csv

[tepid cradle]

https://www.mysqltutorial.org/mysql-export-table-to-csv/
see this

[low iron]

@hazy mango Oh okay
Thanks!

[cinder dome]

So Im gonna make a mute command using aiosqlite, and was wondering if these are the right columns I would need to make it?

[silver sequoia]

I'm learning SQL right now and I had a question that I think is pretty simple but I need clarification. I'm watching a tutorial that is pull columns from another table however they never explained you can do that with one query. The problem that the information from both tables don't appear unless he does either a GROUP BY or a ORDER BY. I'm trying to understand which command needs to be inputed if you're going to pull columns from multiple tables and how that works exactly.

[torn sphinx]

When a database has a one section set as 'null', and you pull it out with Python, does it act as a None in python, or is it a string "null"?

[steel bramble]

Just a quick: Howdy and Join us from the People, Postgres, Data server!

https://discord.gg/E7WjZhz

[cinder dome]

When a database has a one section set as 'null', and you pull it out with Python, does it act as a None in python, or is it a string "null"?
@torn sphinx I think it would result into None

[torn sphinx]

Ok, thanks.

[tepid cradle]

So Im gonna make a mute command using aiosqlite, and was wondering if these are the right columns I would need to make it?
@cinder dome probably better to ask in #discord-bots

[tepid cradle]

The problem that the information from both tables don't appear unless he does either a GROUP BY or a ORDER BY. I'm trying to understand which command needs to be inputed if you're going to pull columns from multiple tables and how that works exactly.
@silver sequoia You don't need group by or order by to get data from another table. You need a join. To get an intersection, you need INNER JOIN; to get a union, you need an OUTER JOIN.
If you join two or more tables, you'll get data from both, no need for group by or order by.

[hollow stone]

Hi,

Tired of reinventing the wheel in different projects I made and open source Ormar python async ORM inspired by encode/orm and later I found some inspiration in ormantic (which is no longer maintained).

The goal was to create a solution that handles for you Sqlalchemy tables and Pydantic models simultaneously (DRY!) and at the same time can be used directly in Fastapi as route parameters and response models. All in asynchronous way.

Fell free to check it out and leave a feedback, all contributors are welcomed! https://github.com/collerek/ormar

[warped glen]

await db.execute("INSERT INTO guilds VALUES ChannelID = ? WHERE GuildID = ?", channel.id, ctx.guild.id)
That SQL Statement is completely wrong How do i correct it

[naive delta]

Hey

Suggesstions for best local database and a tutorial for it?

Speed is a factor

[agile scaffold]

@naive delta if you don't want a database that runs on a server use sqlite3

it can hold a good amount of data

like a very good amount

[naive delta]

What if it's supposed to run on a server 😅

[agile scaffold]

then any other database

[naive delta]

Hm

[agile scaffold]

PostgreSQL, MySQL

[naive delta]

Oooh

[agile scaffold]

those are my recommendations

MySQL holds an inhumane amount of data

like

millions

used by Netflix and other huge companies

[naive delta]

And have you maybe got a good resource to learn sqlite?

[agile scaffold]

uhh

well sqlite3 is really easy to start off

you can learn the syntax

and just get an idea of what to expect

Sentdex has a really good sqlite3 tutorial

[naive delta]

Aight

[agile scaffold]

and then if you wanna jump straight into MySQL follow Tech With Tim's tutorial on MySQL

I haven't seen any PostgreSQL tutorials

but that's also a good one

PostgreSQL is really good for Discord bots

[naive delta]

Ooh

Yeah, I've heard about it being used for discord bots

Spoiler: Ticket tool also uses postgres

[agile scaffold]

I think it just has a really good Async wrapper

that's my assumptions

also sqlite3 has a pretty nice async wrapper for it and most of the time you don't need too much data for discord

which is why I usually use sqlite3 for bots

unless I make mee6 or dyno

[naive delta]

Dyno bad, no offense

[agile scaffold]

but most of them time I make it specifically for 1 server so I don't see the point in using a server for it

what was dyno programmed in do you know

I tried looking it up

can't find it

[naive delta]

Um

My first guess is Js

Lemme do a little researche

Well, I don't know, but I'm sure it's not python lol, no offense python lovers dor discard bots

[torn sphinx]

I don't mean to get off topic here but I believe MEE6 was made in python

[burnt turret]

How do I do pattern matching in postgres (asyncpg)? What is wrong with my query -

...execute("SELECT tag, value FROM guilds_tags WHERE tag LIKE '%$%' AND guild_id = $", tag, ctx.guild.id)

This says invalid syntax on or near $

[hazy mango]

asyncpg.exceptions.DatatypeMismatchError: column "logging" is of type json but expression is of type text
HINT:  You will need to rewrite or cast the expression.

what am I doing wrong?py await self.bot.execute_sql("""UPDATE settings SET logging=logging||'{"on_raw_message_update":$1}' WHERE guild_id=$2""", channel.id, ctx.guild.id)Isn't '{"on_raw_message_update": $1}' json?

Also tried using string formatting instead of $1, same issue

[torn sphinx]

@burnt turret you miss the number after $ symbol

like $1

[burnt turret]

oh, alright

@hazy mango im not sure, but im guessing it still counts it as a string

try something like json.dumps on it maybe

[hazy mango]

"""INSERT INTO settings(guild_id, prefixes, logging) VALUES ($1, '{"!"}', '{"enabled": false}')"""```this works fine though

And that's the same kinda format

[burnt turret]

Oh, I'm clueless then that was the only idea I had :/

[hazy mango]

Aka '{"key": value}'

Yea thanks anyway

How would using json.dumps look, just out of interest

[burnt turret]

The way I've understood json.dumps is that it converts the python object into a json string, so figured that would make it the right data type, because that's what the error said

[torn sphinx]

you have tried to cast in sql?

[hazy mango]

@torn sphinx wdym?

[torn sphinx]

like this here, https://www.postgresqltutorial.com/postgresql-cast/

[hazy mango]

lemme try that

I'm now gettingpy asyncpg.exceptions.UndefinedFunctionError: operator does not exist: json || json HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Withpy await self.bot.execute_sql(f"""UPDATE settings SET logging=logging||'{{"on_raw_message_edit": {channel.id}}}'::json WHERE guild_id=$1""", ctx.guild.id)(also tried using CAST(...))

I had to change to an f-string cause $x isn't accepted in json apparently

I think it's saying that the conversion to JSON worked, but I can't use || to combine jsons but if that's the case then idk what I'm supposed to do instead

@torn sphinx

[torn sphinx]

uhh not sure friend sorry, is maybe because || is not supported? Can you possibly try with jsonb like they say here, https://www.postgresql.org/docs/9.4/datatype-json.html

[hazy mango]

Would I need to change my db schema for that?

[torn sphinx]

i think you can just do replace json with jsonb inside query

i never have tried this but, i just read from there

[hazy mango]

operator does not exist: json || jsonb```

In the db it's of type json so I need to change that

Yep, that works

Tyvm @torn sphinx 👍

[torn sphinx]

oh nice

[hazy mango]

f"""UPDATE settings SET logging=logging||'{{"on_raw_message_edit": {channel.id}}}'::jsonb WHERE guild_id=$1""", ctx.guild.id```this is the final statement

[simple arch]

any1 know how to host a api python flask mongodb?

[burnt turret]

    async def tag_search(self, ctx, *, tag):
        data = await self.bot.postgres.execute("SELECT tag, value FROM guilds_tags WHERE tag LIKE '%$1%' AND guild_id = $2", tag, ctx.guild.id)

Doing this gives me an error saying could not determine data type of parameter $1. What is happening? tag is a string when I printed type(tag)

[brazen charm]

@burnt turret needs typing in the query itself

[burnt turret]

type hinting as a string?

[rich trout]

I think you should try LIKE $1 and changing $1 to be "%data%"

[burnt turret]

But wouldn't that check only for patterns that have data in them?

(as opposed to whatever value it holds)

[brazen charm]

you can try it

[burnt turret]

@brazen charm sorry for the ping, what did you mean by "in the query itself"

[brazen charm]

'%$1::TEXT%'

im not sure exactly but im pretty sure it can be put down to the fact that postgre has several types that are all associated with the str type in python, e.g anyenum, char, name, varchar, text, xml etc....

presuming the column you're trying to use the wildcard on is a TEXT type not some other type

[burnt turret]

It's varchar

[brazen charm]

then use varchar over text

[burnt turret]

Right, let me try that

[brazen charm]

no idea if that will work or what not but asyncpg uses it for its conversions so ig it can work

[burnt turret]

Still gave the same error, doing what Bast had said would almost always turn out to give 0 results when there is no records with "data" in them, so I did something like this -

SELECT tag, value FROM guilds_tags WHERE tag LIKE $1 AND guild_id = $2", f'%{tag}%', ctx.guild.id
``` Does this beat the entire point of not using f-strings lmao?

[brazen charm]

Well it will fuck you in the ass in terms of injection

Wildcard operators are a god send with injection attacks because they're so easy to pickup and close and then just do what ever you want

[burnt turret]

Oh lmao I'd thought this is a bad idea too. Any idea what else could be causing the error?

[brazen charm]

if you do

WHERE tag::varchar LIKE '%$1%'```

[burnt turret]

still gives me the indeterminate datatype error

[brazen charm]

whats the exact error

is it a SQL error or is it a asyncpg internal error

[burnt turret]

asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $1

[brazen charm]

well thats both a good and a bad thing

good thing is that it seems to be asyncpg's checking before executing

bad thing is it offers no info because docs ™️

[burnt turret]

yeah what is up with asyncpg's docs 😔
ping me if anyone get's any idea how i'd run that query safely, thanks!

[torn sphinx]

Hey so i have this code here

import discord
from discord.ext import commands
import asyncio
import datetime
import sqlite3
from time import sleep
import json
import requests
from bs4 import BeautifulSoup
import time

class VipCog(commands.Cog, name="Vip"):
    def __init__(self, bot):
        self.bot = bot

    @commands.command()
    async def vip(self, ctx, user :discord.Member):
        db = sqlite3.connect("main.db")
        cursor = db.cursor()
        cursor.execute(f"SELECT user_id, time FROM vipList WHERE user_id = '{user.id}'")
        result = cursor.fetchone()
        print(result)

        if result is not None:
            sql = ("INSERT INTO vipList(user_id, time) VALUES (?,?)")
            val = (user.id, time.time())
            cursor.execute(sql, val)
        else:
            sql = ("UPDATE vipList SET time = ? WHERE user_id = ?")
            val = (time.time(), user.id)
            cursor.execute(sql, val)

        print(val)
        db.commit()
        cursor.close()
        db.close()

        await ctx.send(f"Added `{user}` to the vip list")




def setup(bot):
    bot.add_cog(VipCog(bot))
    print("Vip is loaded")
```Its not writing anything to my db and its not throwing any errors i have been look at it for a while anyone know why?

@me

[harsh pulsar]

@burnt turret do like $1 in the query and send the parameter f"%{tag}%"

also please try to post your code as text and not a screenshot. it can be very hard for some people to read code in screenshots

@torn sphinx don't share cursors across queries. in sqlite3 you can use db.execute and not have to worry about cursors anyway

[torn sphinx]

Ive never worked with that

[harsh pulsar]

what do you mean?

don't use the same cursor for multiple queries

and definitely dont use f-strings to put data into a sql query

        cursor = db.cursor()
        cursor.execute("SELECT user_id, time FROM vipList WHERE user_id = ?", [user.id])

do this instead, this is a "parameterized query" and it is described in the sqlite3 documentation

!d g sqlite3

[delicate field]

`sqlite3`

Source code: Lib/sqlite3/

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

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

To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:... read more

[snow crescent]

I'm a bit new to this and struggling with the terminology-- I'm trying to set up a pipeline where functions will request data from up the chain depending on their status, while still acting concurrently. Would I use a database like sqlite to handle passing data/status from function to function?

[cinder dome]

Aight so I’m in the middle of making this mute/unmute command but apparently I’m getting this error when I’m trying to mute a user

Error: https://mystb.in/SociologyUruguaySleeping.sql

Here’s the functions: https://mystb.in/QuitForumsEmbassy.python

Commands: https://mystb.in/MelissaNewarkQuestions.python
Tables: https://mystb.in/SadStepBusy.sql

I’m using aiosqlite

[harsh pulsar]

@snow crescent sqlite could work, i think people use key-value databases (like redis) for this too, as well as purpose-build "message brokers" like rabbitmq

[cinder dome]

If anyone replies ping

[native wagon]

mycursor.execute("SELECT current_n FROM ServerList WHERE server_id = " + str(message.guild.id))

        for i in mycursor:
            print(i)

So I have this, and it does work, but it outputs the data like (0,)
How do I extract the actual integer?

[earnest parcel]

It's a tuple (0,)[0] will give you the first value

[native wagon]

cool ty

[south cobalt]

could anyone help me resolve this I cant seem to create any databases in SQL studio

[snow crescent]

@harsh pulsar Any recommendations on which of those is the most user friendly? This is my first foray into this sort of thing so I'm looking for simplicity over functionality. It doesn't need to be scaleable or anything.

[harsh pulsar]

that i don't know. rabbitmq and redis are both very popular though

[snow crescent]

I'll give them a look. Thanks for the help!

[native wagon]

mycursor.execute("INSERT INTO auth_keys (key_str, created, expiry) VALUES (%s, %s, %s)", (random_str(16), datetime.datetime.now().date(), (datetime.datetime.now() + datetime.timedelta(days=7)).date()))
mycursor.execute("SELECT * FROM auth_keys")

for i in mycursor:
    print(i)

ive ran this a couple time and each time it only gives me the last entry

fixed it, i wasn't doing .commit()

[earnest saffron]

https://cdn.discordapp.com/attachments/750036165654216775/750546903829184643/unknown.png
How can I get the data from this mysql colum without the dictonary text junk
I want the out put to be Wins: 0 Losses : 0 ties : 0
instead the out put is {"wins":"0","losses":"0","ties":"0"}

I know how to do this in json but not with mySQL.

[vale lodge]

Is this mysql 5.7 or 8? What type of column is this?

[earnest saffron]

Im using my webhost mySQL Im not sure where to find the version.

Database client version: libmysql - 5.6.43

[vale lodge]

Can you provide a table DDL?

[earnest saffron]

if that helps any.

[vale lodge]

Or the code/query that inserts this data?

[earnest saffron]

Oh yes,

    mycursor = mydb.cursor(buffered=True)
    sql = f"SELECT * FROM wpdc_pixiehuge_team WHERE name = '{team}'"
    mycursor.execute(sql)
    myresult = mycursor.fetchall()
    print(mycursor.rowcount)
    for row in myresult:
        await ctx.send(row[8])

thought maybe I could use stringe replace, but I dont think that will work.

[vale lodge]

That is selecting data, how is it being inserted?

[earnest saffron]

on my website.

Im just trying to get the data from it and push it to a discord channel.

[fallow elm]

@earnest saffron is row[8] a json field or something?

[earnest saffron]

actually it might be its a wordpress template.

[fallow elm]

you just need to format it if that's a dictionary then you'd do something like

",".join(["{}: {}".format(k.upper(), v) for k, v in row[8].items()])

i didn't test that code but that's the gist

[earnest saffron]

gotcha, I think I can work with this.

[burnt turret]

do like $1 in the query and send the parameter f"%{tag}%"
@harsh pulsar Yeah but that open's it back up to injections right? #databases message
(oof sorry for the ping)

[harsh pulsar]

kind of, in that it's open to injections into the pattern

but not into the query itself which will still be properly quoted

[burnt turret]

Oh alright. Thank you!

[weak charm]

What it's used instead of migration for mongodb? if it's used something

[steady epoch]

sqlite3.DatabaseError: database disk image is malformed

everytime i run my db

it gets corrupted

[tepid cradle]

Show your code @steady epoch

[steady epoch]

        cursor = await db.cursor()
        await cursor.execute("""
            CREATE TABLE IF NOT EXISTS player(
            discord_id integer Not NULL ,
            player_tag text UNIQUE)
        """)
        await cursor.execute("""
            CREATE TABLE IF NOT EXISTS clan(
                discord_id integer NOT NULL,
                clan_tag text,
                UNIQUE(discord_id,clan_tag))
        """)
        await cursor.execute("""
            CREATE TABLE IF NOT EXISTS mute(
                discord_id integer NOT NULL,
                time_of_unmute text,
                reason text,
                server_id int NOT NULL)
        """)```

@tepid cradle

[tepid cradle]

That's all?

[steady epoch]

@tepid cradle yeh its failing here only

[weak charm]

I have a question, if i have a one to many relationship in mongodb and i dont want to use embeded document, for users and posts, i put a list with post_ids in user and a user id in post?

[tepid cradle]

@tepid cradle yeh its failing here only
@steady epoch ok. I'm not familiar with aiosqlite, but can you change the db name to bot_db.sqlite
.sql is generally used for SQL script files

[steady epoch]

i am using .sql

[tepid cradle]

Read my message again

I have a question, if i have a one to many relationship in mongodb and i dont want to use embeded document, for users and posts, i put a list with post_ids in user and a user id in post?
@weak charm if you want to structure your data that way, use a relational db, like MySQL

[weak charm]

I am aware if there are too many posts maybe document will be too large, but i want to use mongodb. Edit: A relational db fits better for my project

[tepid cradle]

In that case, you only need to put user ids in posts, not the other way round

[crisp nymph]

Hey guys! Now this may not be the correct place to ask, but I have a db file which I created using sqlite3 in python. Now I created a table in it and added rows. Is there a way I can see this graphically in something such as excel or access?

[proven arrow]

@crisp nymph You can use https://sqlitebrowser.org/

[slender sentinel]

for my project ..can i make a password manager where all the password is stored in a sql database and can be accessed using a convenient gui?

[crisp nymph]

@proven arrow I did look at it, but is there anyway to see it in ms access or excel?

[proven arrow]

Not sure, you would have to check those programs if they support it.

[harsh pulsar]

@crisp nymph you can export to CSV and read that with Excel

[crisp nymph]

ok @harsh pulsar

[harsh pulsar]

oh wait

you can do this https://www.devart.com/odbc/sqlite/docs/excel.htm

i forgot that excel supports ODBC

[crisp nymph]

I actually tried out the browser. And its pretty good for my needs, that is, just checking the data

[harsh pulsar]

https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.SQLiteODBCDriver

ok

[torn sphinx]

can someone help with an oracle sql query? how can i do a

SELECT CASE WHEN DATE_COLUMN >= 1/1/2020 THEN COUNT COLUMN1 END AS "DURING2020", CASE WHEN DATE_COLUMN < 1/1/2020 THEN COUNT COLUMN1 END AS "BEFORE2020"

i want to do a count within a case statement for several different cases when the date is within a specified range

[wild void]

Did you try a nested query or using a temporal table?

[torn sphinx]

i didn't. is there a way to do it without doing that?

like just doing the case when date_column >= [date] then count(x)

[cobalt sparrow]

Hey,I need a python script that can connect to the database,and run the sql file. Then the script should capture the final table output and should place output into an email.
Please help.

#help-chocolate

[quaint tiger]

Hey,I need a python script that can connect to the database,and run the sql file. Then the script should capture the final table output and should place output into an email.
Please help.
@cobalt sparrow What have you done? What code are you struggling with? If you just want someone to do the work for you, hire somebody; this isnt r/slavelabor ...

[cobalt sparrow]

I just need help.

I connected to oracle database using cx_oracle .Ran the query have output , want to send that output table to email, can i do this?

[pseudo cove]

https://realpython.com/python-send-email/

[cobalt sparrow]

Thanks for the help.

[dusky plaza]

Hello,
I need help from someone experienced with mysql.connector.

I have a function that should update one of the columns, but it should be passed inside the function, not predefined. It looks something like this:

def update_user(e_mail, key, value):
    sql = ("UPDATE tablename SET %s = %s WHERE e_mail = %s")
    values = (key, value, e_mail)
    c.execute(sql, values)
    conn.commit()    

However this errors out since it inserts the column name (key) with single quotes. I need a way to insert it without them but that it is still secure and prevents SQL injection attacks

[quaint tiger]

I connected to oracle database using cx_oracle .Ran the query have output , want to send that output table to email, can i do this?
@cobalt sparrow that is a very vague question that has literally zero code and is therefore impossible for us to answer.
Python can do that, and you can just google each step if you don't want to share your code...

• python connect to oracle
• python cx_oracle query
• python send email

Also, I recommend googling "how to ask good questions."

[dusky plaza]

If someone knows a different connector for mysql that works better or another way to write this function I am open to suggestion. Right now I have a function written for each of the columns and that kinda sucks

[quaint tiger]

def update_user(e_mail, key, value):
    sql = (f"UPDATE tablename SET {key} = %s WHERE e_mail = %s")
    values = (value, e_mail)
    c.execute(sql, values)
    conn.commit()    

Maybe an f-string (if Py >= 3.6, or str.format) would work?

[dusky plaza]

yeah that was my workaround but that is not secure

[quaint tiger]

But I would probably not do that.

[gloomy pike]

will rollback rollback every single transaction/query made in executemany or just the last one in the list argument? (aiosqlite)

[brazen charm]

probably everything

[quaint tiger]

If you want security, do if/else

Basically, I wouldn't trust user input. x.x

[dusky plaza]

There has gotta be a better way to do this then writing 15 if/else statements. I refuse to believe that is the best way

[brazen charm]

a list...

[quaint tiger]

a set*

Validate the key

Then use f-string.

[brazen charm]

f strings still p bad

usually in my orm the system does if columns.get(key): execute....

[dusky plaza]

yeah I am still inserting raw strings

[brazen charm]

you wont be able to get around that really

[dusky plaza]

I talked to my friend and in php there is a function inside connector that does it. Like I believe it is a very common issue, how can it not be implemented?

[quaint tiger]

valid_keys = { "email", "password", "foo", "bar"}
if key not in valid_keys:
    raise HackerError
...

or key.lower()

[dusky plaza]

is that secure?

[quaint tiger]

Should be...

[dusky plaza]

also []

[quaint tiger]

[]?

You mean using a list?

[dusky plaza]

ye

[quaint tiger]

You can. For ~15 items it shouldn't matter.

[tepid cradle]

more like

select COLUMN_NAME 
    from information_schema.`COLUMNS` 

[quaint tiger]

It also depends on whether you want all columns to be updateable.

[dusky plaza]

@tepid cradle could you please write the full sql for what I am trying to do? I am still very new with it

[quaint tiger]

x in set O(1) is generally quicker than x in list O(n).

[dusky plaza]

all columns should be updateable besides e-mail but that is currently the WHERE statement

[tepid cradle]

valid_keys = { "email", "password", "foo", "bar"}
if key not in valid_keys:
    raise HackerError
...

@quaint tiger In this, replace the first line with

cursor.execute('select column_name from information_schema.columns where table_name = %s', (table_name,))
valid_columns = list(cursor.fetchall())

I don't know what the right placeholder for mysql is, I use %s because I am used to PostgreSQL

[brazen charm]

pst

thats because psycopg2 is bad

postgre is actually $n not %s <- which is mysql's btw psycopg2 just likes to be weird

[dusky plaza]

psycopg2 has a solution to my problem

but I dont think it supports mysql

in it you can just write %%s for column name and thats it

[tepid cradle]

in it you can just write %%s for column name and thats it
@dusky plaza I did not know this, thanks for mentioning

[dusky plaza]

@tepid cradle I am best at solving others problems but not mine lol.. I read through the whole docs in the morning and couldn't find a solution

[tepid cradle]

Actually, I tried this %%s thing and it didn't work. I'm not able to find anything in the documentation either.
Rather, this page is specifically suggesting using Python string formatting for table and column names.
https://www.psycopg.org/docs/sql.html

[south cobalt]

def addfic100(values):
    insert_query = '''INSERT INTO [FIC-100] ([FIC-100 PV], [FIC-100 OP], [FIC-100 SP])
                    VALUES (?, ?, ?);'''
    cursor.execute(insert_query, values)
    cnxn.commit()
def addfic101(values):
    insert_query = '''INSERT INTO [FIC-101] ([FIC-101 PV], [FIC-101 OP], [FIC-101 SP])
                    VALUES (?, ?, ?);'''
    cursor.execute(insert_query, values)
    cnxn.commit()
def addlic100(values):
    insert_query = '''INSERT INTO [LIC-100] ([LIC-100 PV], [LIC-100 OP], [LIC-100 SP])
                    VALUES (?, ?, ?);'''
    cursor.execute(insert_query, values)
    cnxn.commit()

should i be closing the cursor after every commit?

this is with MSSQL and pyODBC if thats not clear

[harsh pulsar]

@south cobalt you should not re-use a cursor between queries

make a new cursor for every query

[south cobalt]

so make and close it each time

[harsh pulsar]

i dont think it needs to be closed explicitly, but it (probably) can't hurt

[south cobalt]

that goes for other CRUD operations as well right

i will need to make some read functions and some that erase all data from each of the tables

[harsh pulsar]

its true for anything using a database library in python

[south cobalt]

ok ty, never used pyodbc or sql in general before

idk if there is a place i could find the general do's and dont's

[harsh pulsar]

obviously your database library's docs are a good place to start

also most python database libraries follow the "DB-API 2.0" spec

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

[south cobalt]

ok ill give it a read as well as the ODBC docs

[harsh pulsar]

most libraries diverge in one way or another, or add their own extensions. but the general API design is similar in all of them

[cinder dome]

So for making prefix tables and stuff using aiosqlite should I go with the first or second option

[proven arrow]

I would go with the first. Just store it with the guild data.

[cinder dome]

aight

Thanks

[torn sphinx]

if i have table like this:
id | date | price
1 1/1/2020 2
2 2/13/2020 3
3 2/12/2020 3
4 4/20/2020 2
5 4/25/2020 1

how can i write a query to get the following result containing sum of the price before and after 2/15/2020:

before 2/15/2020 | after 2/15/2020
8 3

[mossy blaze]

+ "user_id text PRIMARY KEY,\n"
+ "money integer 100,\n"
+ ");"

near "750093664130170881": syntax error -- where that id is the guild's id. why does it error?

[pseudo cove]

need more context

@mossy blaze

[torn sphinx]

what are you using for @torn sphinx ?

use pandas. it is best for databases

[rain apex]

2020-09-02T19:47:32.224327+00:00 app[worker.1]: Ignoring exception in on_message
2020-09-02T19:47:32.224472+00:00 app[worker.1]: Traceback (most recent call last):
2020-09-02T19:47:32.224521+00:00 app[worker.1]:   File "/app/.heroku/python/lib/python3.8/site-packages/discord/client.py", line 312, in _run_event
2020-09-02T19:47:32.224522+00:00 app[worker.1]:     await coro(*args, **kwargs)
2020-09-02T19:47:32.224522+00:00 app[worker.1]:   File "bot.py", line 91, in on_message
2020-09-02T19:47:32.224523+00:00 app[worker.1]:     await self.process_commands(msg)
2020-09-02T19:47:32.224524+00:00 app[worker.1]:   File "/app/.heroku/python/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in process_commands
2020-09-02T19:47:32.224524+00:00 app[worker.1]:     ctx = await self.get_context(message)
2020-09-02T19:47:32.224527+00:00 app[worker.1]:   File "/app/.heroku/python/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 853, in get_context
2020-09-02T19:47:32.224528+00:00 app[worker.1]:     prefix = await self.get_prefix(message)
2020-09-02T19:47:32.224531+00:00 app[worker.1]:   File "bot.py", line 79, in get_prefix
2020-09-02T19:47:32.224531+00:00 app[worker.1]:     self.c.execute("""
2020-09-02T19:47:32.224558+00:00 app[worker.1]: psycopg2.InterfaceError: cursor already closed

I started getting this error

why does the connection just break?

do I just to a try except for all database queries to check if the connection works and if not reconnect

[crisp nymph]

Hey guys, this is a weird question, but suppose I have a .db file on a server / vps. Now I want to see the contents of it. How can I do it? I used firestore earlier, and I could just go there and see stuff. Possible? like I use sqlite3

[quaint tiger]

ssh into the server, and run sqlite3 my.db (wiith the appropriate file name, of course)

[crisp nymph]

any other way lol