databases

[heavy epoch]

Lmao

Night

[pure sleet]

is this heidiSQL?

[torn sphinx]

yes

[pure sleet]

why don't you test your query there and see what it gives?

[torn sphinx]

oh yh

it returns what its meant to

but the problem is when i do %s and pass the argument it does not work

which to me means its something to do with it being an int

[pure sleet]

what's %s? doesnt that mean string or something

[torn sphinx]

%s is just for arguments to prevent sql injection from what ive been told

[pure sleet]

what are you using to query in python?

[torn sphinx]

instead of using f strings

[pure sleet]

oh i see

[torn sphinx]

cause its a mysql query

im making a discord bot for my fivem server

oh

no im dumb

%s is the string temp variable

%i is the temp int variable

that still does not work

@bot.command()
async def idinfo(ctx, permid):
    sql = "SELECT * FROM vrp_user_identities WHERE user_id = '%i'"
    mycursor.execute(sql, int(permid))
    myresult = mycursor.fetchall()
    print (myresult)

[pure sleet]

man, what are you using to make this queries again?

[torn sphinx]

my sql knowledge

[pure sleet]

why did you put it in single qoute marks? it's still a string bruh

[torn sphinx]

cause in heidi single quotes work

[pure sleet]

crazy stuff

try what endor suggested

[pure sleet]

i mean in python

[torn sphinx]

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1

xD

[pure sleet]

what are you using to run the sql commands etc

[torn sphinx]

you mean what driver do i use

mysql.connector

[pure sleet]

alright

[torn sphinx]

see

his dont work either

[proven arrow]

MySQL uses %s

Not ?

[torn sphinx]

and %i

[proven arrow]

What’s the question?

@copper wyvern It would work because its sqlite, and its type system is dynamic. But you should try to stick with convention and use the same data type. Because other databases wouldn't accept this.

Also I would add a pk column to it so you can uniquely identify a row. Which would be explained in the link I sent you first about normalisation.

SQLite don’t care what you put. You could put your name and it would work.

Yeah actually I think sqlite tables already have a rowid hidden somewhere. But you get the idea. It’s so you can differentiate it from other rows.

[torn sphinx]

can anyone help me

@bot.command()
async def idinfo(ctx, permid):
    sql = "SELECT * FROM vrp_user_identities WHERE user_id = %s"
    mycursor.execute(sql, (permid))
    myresult = mycursor.fetchall()
    print (myresult)

the query does not return data just retuns [] when it should return data

user_id is an int

[torn sphinx]

[(1,)]

After Doing

myresult[0][0]

it returns 1

how can i convert 1 to a string

[copper wyvern]

so i made the thing but it created an sqlite_sequence table

any idea what that is

[errant moth]

does anyone know if any of these dont make sense

[austere portal]

str(myresult[0][0])

[grim vault]

You created a table with an AUTOINCREMENT primary key and that table is for sqlite to keep track of the last number. In SQlite you normaly don't really need AUTOINCREMENT, see https://www.sqlite.org/autoinc.html

[grim vault]

Doesn't the second parameter of execute needs to be a tuple (missing , in your case, to make it a tuple)?: mycursor.execute(sql, (permid,))

[tardy jay]

what async libraries are good for managing postgresql databases?

[austere portal]

Asyncpg, aiopg

[iron thunder]

Hey

[proven arrow]

Yes hello

[faint blade]

Level is a string, not a datetime object

You need to create a datetime object from that string, do you use strft to get that string when you insert it into the database?

[static stream]

no

[static stream]

how do i make it as datetime object?

[faint blade]

https://stackoverflow.com/questions/466345/converting-string-into-datetime

[static stream]

okay thanks

[serene oyster]

how can I delete a data in a db after X time ? I use SQLite3
for example : I insert into my db a data, and I remove it after 1h or less

[prisma girder]

You need to use cron job for example

[serene oyster]

oh... okay

[earnest lagoon]

What is “Letter Distribution” and what is “Word Distribution” in NLP dataset while preforming Exploratory data analysis(EDA)?

[thorn vapor]

            await self.bot.pgs_conn.execute("""

                    INSERT INTO servers(server_id,user_id)
                    VALUES ($1,$2)
                    ON CONFLICT (server_id)
                    DO UPDATE SET user_id= array_cat(servers.user_id, $2)
                    ;

            """,ctx.guild.id,member.id)

DataError: invalid input for query argument $2: 646937666251915264 (a sized iterable container expected (got type 'int'))
both server_id and user_id are bigints

                    INSERT INTO servers(server_id,user_id)
                    VALUES (111,'{222}')
                    ON CONFLICT (server_id)
                    DO UPDATE SET user_id= array_cat(servers.user_id, {222})
                    ;

This worked in shell but not working with asyncpg

[burnt turret]

that second argument needs to be a list or something, as it looks like you've defined that column in your table to be of an array type

that's what the a sized iterable container expected (got type 'int') is telling you

[thorn vapor]

i m getting error when i do '{$2}' too

[burnt turret]

no, when you pass the parameter pass it as a list

[thorn vapor]

ohhhhhhhh

ohhhhh

ohh

let me try

thankss

[thorn vapor]

thanks sir it worked

[calm prawn]

Is there any read and write limits for Mongodb? Sorry if I shouldn't be asking the question here.
Also I googled it but can't find a proper answer

[faint blade]

In terms of, permissions?

[calm prawn]

Yes

I only can find connections limit

[faint blade]

I am not sure MongoDB does that, relational database (PostgreSQL, MySQL) do have these features.

[calm prawn]

Okay

[faint blade]

https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/

It seems like MongoDB seems to have this to some extent.

What exactly are you trying to limit?

[calm prawn]

I don't exactly want to limit anything. Just wanted to know is there a limit to how many total read and writes any user can do

[faint blade]

Like, what are you expecting to happen when this total is hit?

I don't see why there would be a total amount of reads and writes that you can do to a database. You shouldn't need to worry about that.

[calm prawn]

oh okay thanks

[sharp kindle]

Say I have multiple products, is having a seperate table for each to store their licenses personal preference? or should i have one giant table with all licenses?

the licenses for all products have the same fields

[calm prawn]

I guess one table for each product. But thats personal preference

[faint blade]

The only total/limit would be that at some point the database will become extremely slow if you have a lot of data. As well when you use the disk that much you are exhausting it. If I remember correctly the average lifetime of a disk is some number between 5 and 10 years.

[calm prawn]

Oh thats interesting. Thanks for the info i never knew that

[misty sundial]

Hey, not sure if that is the best place to ask. I am learning to become Data Engineer and I am a bit stuck at the moment. Learned some Python, SQL and read about some more advanced concepts in DE in the last year or so, built my first ETL pipeline recently through a tutorial and I am not sure where to go next. I need a bit of advice, mentoring. Thanks!

[dense barn]

how do i get past this error?

ERROR: collations are not supported by type bigint
``` im trying to change one of my columns data type from text to big int but i get that error.

[prime falcon]

anyone know where i could learn SQLite3?

or AIOSQLite?

[half patrol]

I want to make a discord bot that creates databases for the GMD private server files. If you don't know what I mean, it's this (btw this is not advertisment): https://github.com/Cvolton/GMDprivateserver

What I want to do is make a bot with a custom command that allows someone to create a gdps with a database using those files and also some more custom commands that allows people to configure some stuff

If someone can help me, I would really appreciate it!

[prime falcon]

    @commands.command()
    @commands.check_any(commands.is_owner())
    async def reaction_role(self, ctx, question:str, emoji1:str, role1:str, emoji2:str, role2:str):
        msg = await ctx.send(question)
        await msg.add_reaction(emoji1)
        await msg.add_reaction(emoji2)
        cur.execute('''INSERT INTO reactionRoles ''')

how would i insert the strings from reaction_role into my sqlite3 file?

[thorn geode]

Did you learn the language?

[midnight birch]

I'm working with motor the async Python driver for MongoDB and Tornado or asyncio but looks like the whole module doesn't have type hints and I have to look up every thing in the docs. Can someone tell me if I'm doing something wrong or if the module doesn't really include type hints in it.

[prime falcon]

i learned some basic sql

[lean sparrow]

If anyone is familiar with mongod I'm having some issues with pymongo I cant figure out

[lean sparrow]

I keep getting pymongo.errors.ServerSelectionTimeoutError for weird reasons that have never come up before

I get it if I try to use find_one() but not if I use find()

[crude shard]

how can I find the number of times data was accessed on mongodb website

[austere portal]

The syntax to insert values to a table is INSERT INTO table_name VALUES('value 1', 'value 2')

[copper wyvern]

for aiosqlite is it better to do all your inserts and selects under one connection

async with aiosqlite.connect(db) as db:
       for stuff
           await db.execute(stuff)```
or in multiple connections
```py
for stuff:
      async with aiosqlite.connect(db) as db:
             await db.execute(stuff)```
or does it not matter

[austere portal]

Do everything under a single connection

[dusk dagger]

Hello, I have a relationship question with sqlclehmy async orm

Having four (simplified) models as below:

import sqlalchemy as sa

class UserModel(Base):
    __tablename__ = "users"

    id = sa.Column(sa.String, primary_key=True, , autoincrement=True)
    name = sa.Column(sa.String, nullable=False)
    profile = sa.orm.relationship("UserProfileModel", uselist=False)

class UserProfileModel(Base):
    __tablename__ = "user_profiles"

    id = sa.Column(sa.ForeignKey("users.id"), primary_key=True)
    skills = sa.Column(sa.ARRAY(sa.String))


class JobModel(Base):
    __tablename__ = "jobs"

    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
    name = sa.Column(sa.String, nullable=False)
    matches = sa.orm.relationship("MatchModel")


class MatchModel(Base):
    __tablename__ = "matches"

    job_id = sa.Column(sa.ForeignKey("jobs.id"), primary_key=True)
    candidate_id = sa.Column(sa.ForeignKey("users.id"), primary_key=True)
    candidate: UserModel = sa.orm.relationship("UserModel", uselist=False)
    extra_data = sa.Column(sa.dialects.postgresql.JSONB)

So, I want to query a user's posted job with its candidates, and with match.extra_data.

async def get_my_posted_job(db: AsyncSession, user_id: int, job_id: int):
    query = (
        sa.select(JobModel)
        .where(
            JobModel.id == project_id,
            JobModel.user_id == user_id,
        )
        .options(
            sa.orm.selectinload(JobModel.matches),
            # how to select match.candidate and candidate's profile here ?
         )
     )
    result = await db.execute(query)
    job = result.scalars().one()
   
    """
    expect job to be like this:
    {
        "id": 1,
        "user_id: 1,
        "name": "I need someone to do dishes"
        "matches": [
            "job_id": 1,
            "candidate_id: 2,
            "candidate": {
                "id": 2,
                "name": "Bob",
                "profile": {
                    id: 2,
                    skills: ["do dishes"]
                },
            },
            "extra_data": {
                "message": "Hi, I can help you"
            },
        ],
    }
    """

How to make correct query in this case?

I already posted it on github disscussion (https://github.com/sqlalchemy/sqlalchemy/discussions/6800)

[dusk dagger]

For anyone want to know the solution to the above question, please refer to this doc (https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#relationship-loading-with-loader-options)

[torn echo]

How to connect with database my python project

[austere portal]

You need to use a database driver

[untold dust]

how much data does mongodb give for a free cluster

[torn sphinx]

anyone know how to edit json from mysql in python and edit the data

{"armour":0,"health":185,"inventory":{"wammo|WEAPON_PUMPSHOTGUN":{"amount":10},"wbody|WEAPON_PUMPSHOTGUN":{"amount":1}},"groups":{"admin":true,"recruiter":true,"user":true,"superadmin":true},"thirst":0,"customization":{"1":[6,0,0],"2":[0,0,0],"3":[1,0,0],"4":[20,0,0],"5":[0,0,0],"6":[25,0,0],"7":[0,0,0],"8":[31,0,0],"9":[0,0,0],"10":[0,0,0],"11":[10,0,0],"12":[0,0,0],"13":[0,0,0],"14":[0,0,255],"15":[0,0,100],"16":[0,0,0],"17":[0,0,0],"18":[0,0,0],"19":[0,0,0],"20":[0,0,42],"0":[0,0,0],"p8":[-1,0],"p9":[-1,0],"p7":[-1,0],"p6":[-1,0],"p5":[-1,0],"p4":[-1,0],"p0":[42,1],"p1":[-1,0],"p2":[-1,0],"p3":[-1,0],"p10":[-1,0],"modelhash":1885233650},"weapons":[],"hunger":0,"position":{"z":57.58551788330078,"x":-1333.413818359375,"y":-447.1412048339844}}

i got this i had to convert from a tuple

but now i need to edit the json in the data destion "groups"

[faint blade]

I don't completely follow what you mean, you want to insert this data into MySQL now?

[torn sphinx]

so basically

the data in this row is stored as json

ive manged to get it from a full tuple of data

[torn sphinx]

to this

then to this

all i need to do now

i need to edit it

[torn sphinx]

make sense?

say i want to get from

{'admin': True, 'recruiter': True, 'user': True, 'superadmin': True}

to

{'admin': True, 'recruiter': True, 'user': True, 'superadmin': True, 'Police Officer': True}

[faint blade]

Is this what you get from MySQL?

[torn sphinx]

i get this from sql

{"armour":0,"health":185,"inventory":{"wammo|WEAPON_PUMPSHOTGUN":{"amount":10},"wbody|WEAPON_PUMPSHOTGUN":{"amount":1}},"groups":{"admin":true,"recruiter":true,"user":true,"superadmin":true},"thirst":0,"customization":{"1":[6,0,0],"2":[0,0,0],"3":[1,0,0],"4":[20,0,0],"5":[0,0,0],"6":[25,0,0],"7":[0,0,0],"8":[31,0,0],"9":[0,0,0],"10":[0,0,0],"11":[10,0,0],"12":[0,0,0],"13":[0,0,0],"14":[0,0,255],"15":[0,0,100],"16":[0,0,0],"17":[0,0,0],"18":[0,0,0],"19":[0,0,0],"20":[0,0,42],"0":[0,0,0],"p8":[-1,0],"p9":[-1,0],"p7":[-1,0],"p6":[-1,0],"p5":[-1,0],"p4":[-1,0],"p0":[42,1],"p1":[-1,0],"p2":[-1,0],"p3":[-1,0],"p10":[-1,0],"modelhash":1885233650},"weapons":[],"hunger":0,"position":{"z":60.68812942504883,"x":-1333.413818359375,"y":-447.1412048339844}}

i managed to get it down to just the groups by loading the json

@bot.command()
@commands.has_role('L3')
async def addgroup(ctx, perm, group):
    permid = int(perm)
    sql = f"SELECT * FROM lgn_user_data WHERE user_id = {permid}"
    mycursor.execute(sql)
    result=mycursor.fetchall()
    jsonfile = result[0][2]
    lgndata = json.loads(jsonfile)
    print(lgndata['groups'])

[faint blade]

And now you want to change the groups, and save that to MySQL?

[torn sphinx]

yes

[faint blade]

Right so now that you have the groups, change what you want to change (see example) and save. ```py
lgndata = json.loads(jsonfile)
lgndata['groups']['pythonista'] = True # Replace the second string with what new group you want to give
dump = json.dumps(lgndata)

Now you need to update the row and save dump

[torn sphinx]

is it that easy to change data

[dire marsh]

Hey anyone available to chat?\

[torn sphinx]

@faint blade can you use F strings in json

[torn sphinx]

what sup

[dire marsh]

can i ask you about Databases?

[faint blade]

Depends on what you mean

[faint blade]

Yes go ahead, you're in the right channel

[dire marsh]

I got lot's of questions, since i'm just getting started with it

So as from my little knowledge, there are two types right? Relational and non-relational

in relational, what are the different types of keys and how do they function?

Like I've heard of Primary, foreign and composite, Are there more?
also Can you tell me how composite key works?

[faint blade]

Pretty much yeah, but not really.

The biggest ones you'll hear about are:

• Relational (MySQL, PostgreSQL)
• Document-based which is also NoSQL (MongoDB)

[faint blade]

You're thinking about those a little bit incorrectly

[dire marsh]

Please enlighten me

[torn sphinx]

Ignoring exception in command addgroup:
Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\Administrator\Desktop\caz-lootbox\main.py", line 188, in addgroup
    mycursor.execute(sql2)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 568, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 686, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\mysql\connector\connection.py", line 573, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"armour": 0, "health": 185, "inventory": {"wammo|WEAPON_PUMPSHOTGUN": {"amoun...' at line 1

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

Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"armour": 0, "health": 185, "inventory": {"wammo|WEAPON_PUMPSHOTGUN": {"amoun...' at line 1

how to fix that @faint blade im trying to enter the data again

@bot.command()
@commands.has_role('L3')
async def addgroup(ctx, perm, group):
    permid = int(perm)
    sql = f"SELECT * FROM lgn_user_data WHERE user_id = {permid}"
    mycursor.execute(sql)
    result=mycursor.fetchall()
    jsonfile = result[0][2]
    lgndata = json.loads(jsonfile)
    lgndata['groups'][f'{group}'] = True
    dump = json.dumps(lgndata)
    print(lgndata['groups'])
    sql2 = f"UPDATE lgn_user_data SET dvalue = {dump} WHERE user_id = {permid}"
    mycursor.execute(sql2)
    result2 = mycursor.fetchall()
    print(result2)

[faint blade]

I don't really know why they are called keys, but they're actually constraints.

You constraint the value of a column, for example you can declare it NOT NULL or UNIQUE. PRIMARY KEY is the same as NOT NULL + UNIQUE as well as it tells the database that this is the column to assume when you only specify a table (there can additionally only be one column marked as PRIMARY KEY).

[faint blade]

I've told you, do not use f-strings. You need to change them to %s and do (dump, permid) to the function.

[torn sphinx]

@faint blade its an int you cant inject sql with an integer

can you just help me solve my issue

[faint blade]

Continuing on that @dire marsh, FOREIGN KEY is a way to tell the database "This value must exist in this table". So it is also a constraint.

A composite key is when you combine multiple columns to be the primary key.

[dire marsh]

Like how do we combine FOREIGN or multiple Keys to form a composite key?

[faint blade]

Why not be on the safe side?

You need to do ```py
my cursor.execute(sql2, (dump, permid))

And of course change `sql2` to instead of `{dump}` and `{permid}` they need to be `%s`.

[torn sphinx]

can you just help me fix the issue at hand ill worry about safety after

[dire marsh]

Also... Are Database secure by default?
Like we do have lots of DBMS in market right?
MySQL, PostgreSQL, etc... Are they secure enough to hold confidential data of a certain Organization?

[faint blade]

A foreign key cannot be multiple columns in most SQL databases. How would you combine two columns into one?

A composite key, is a primary key consisting of multiple columns.

[torn sphinx]

thats up to you if you trust it or not and its up to you to protect your passwords, ip lock it etc

[dire marsh]

I didn't get the composite key one

[faint blade]

No, you should be hashing or encrypting the data (ALWAYS PROTECT PASSWORDS). But you can trust it to never mess up.

[dire marsh]

Okay if the password is secure, what are the other thing's i need to worry (Like SQL injection)

[torn sphinx]

protect search bars and input tables

worry about xss

etc

[faint blade]

The issue is that when you use f-string like that you are causing issues with the SQL. If you just do like I showed that will be fixed.

[faint blade]

I have to go, but I can explain this more in detail when I get back

[torn sphinx]

@faint blade

[dire marsh]

Okay thanks dude, mention me when you are available

[torn sphinx]

1 more ting, whenevr i use %s with an integer it never works

that why i was using an f string

\

see

when i fetch data using %s with my permid

it wont work

[faint blade]

Ah, you may need to use %d for when you want integers.

[torn sphinx]

ij\

[faint blade]

Let me think up an example of where you'd use a composite key

[torn sphinx]

Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: Not all parameters were used in the SQL statement

@faint blade this happens when i use that

[faint blade]

Can you show how it looks when you use that?

[torn sphinx]

how what looks

the script or what im doing

[faint blade]

The code

[torn sphinx]

@bot.command()
@commands.has_role('L3')
async def addgroup(ctx, perm, group):
    permid = int(perm)
    sql = f"SELECT * FROM lgn_user_data WHERE user_id = {permid}"
    mycursor.execute(sql)
    result=mycursor.fetchall()
    jsonfile = result[0][2]
    lgndata = json.loads(jsonfile)
    lgndata['groups'][f'{group}'] = True
    dump = json.dumps(lgndata)
    sql2 = "UPDATE lgn_user_data SET dvalue = %s WHERE user_id = %d"
    mycursor.execute(sql2, (dump, permid))
    mydb.commit()
    result2 = mycursor.fetchall()
    
    print(result2)

its gotta do with the json i reckon

[faint blade]

Alright I think I have a good example @dire marsh, say we have a deck of cards.

In one column we have Hearts, Diamonds, Clubs, and Spades. In the other we have Ace, Jack, Three, Ten, King, etc.

We can't say that just Clubs itself is unique and can identify a row. And Ten on its own cannot be used to identify the row. But together!

There can only be one Clubs Ten, only one Diamonds King. That's why we make a composite key of those together.

[faint blade]

Can you try changing it back to %s and doing str(permid)?

[torn sphinx]

perm id is an int tho

[]

it returns thast

its a json issue

[faint blade]

Switch those to ?, it's better anyway I think

[faint blade]

What do you think is wrong?

[torn sphinx]

idk bro

[faint blade]

Oh, then why do you think the issue is to do with the JSON?

[torn sphinx]

because its an issue with params not the WHERE clause

[faint blade]

Ah, I thought you were printing somewhere else.

UPDATE doesn't return anything. That's why result2 is empty

[torn sphinx]

ok

so how do i make this do what i want

[faint blade]

You can add RETURNING *; to your query, that will return the new row I think

[torn sphinx]

im not making a new row

its updating a column in a row

[faint blade]

Yes, that will return the updated row

[torn sphinx]

how do i do that

wait but its failing to update it

thats the issue

is there any fast and easy way of making a small db for a discord bot?

[thorn geode]

Yes you can use a(io)sqlite

No hassle needed as it's file based

You do need to know the SQL language though.

[dire marsh]

I kinda got it

@faint blade also what are required to establish a connection with a DB server?

[thorn geode]

(@torn sphinx)

[dire marsh]

how can one be hacked, how to prevent the attacks, and what are the most secure ones you recommend?

[grim elk]

what is a dimension and a fact in a database? (Yes, i've googled them, but need to have a better understanding for the interview)

[faint blade]

Usually name and password.

[formal coral]

its most of the time just a username - password protection

[dire marsh]

and on the inner side?

[formal coral]

maybe there is also some ssh key authentication options on very critical data

[formal coral]

yea usually in the uri

[faint blade]

Well yes you would establish a secure connection to the database up to today's standards

[dire marsh]

is MySQL secure and reliable enough to store datas for a Large Company?

[torn sphinx]

    @commands.command()
    @commands.has_permissions(administrator=True)
    async def change(ctx,prefix:str):
        print("bruh")
        collection.update_one({'_id':ctx.guild.id},{'$set':{'prefix' : prefix}})
        print("bruh2")
        embed = discord.Embed(
        title='Prefix',
        description=f'Prefix has been changed to `{prefix}`.',
        color=discord.Color.random()
        )
        await ctx.send(embed=embed)

I got this command, changes the prefix of the bot, the issue is in the database part, it doesnt work. No errors too. The "bruh" is printed, the "bruh2" is not so i know the problem is there.

its in mongodb

[dire marsh]

What do i need to be careful about in that context?

[formal coral]

have a strong password

[dire marsh]

.

[formal coral]

maybe use try except to catch the error?

[torn sphinx]

how tho

[rapid dust]

Whats wrong
It was worked well

[formal coral]

try:
  my update line
except Exception as ex:
  print(ex)

[torn sphinx]

alr

[rapid dust]

.

[faint blade]

I would say so? Pretty sure there's loads of companies that use MySQL.

[formal coral]

Yea

[torn sphinx]

'RPG' object has no attribute 'guild

well thats a question for #discord-bots

[formal coral]

yea

[torn sphinx]

ty @formal coral

[dire marsh]

what IDE should i use to work on it? any recommendation?

[formal coral]

np, btw maybe you are missing self

[torn sphinx]

i just used vs code

[torn sphinx]

bruh i forgot tyy

[formal coral]

lol np

[torn sphinx]

works perfectly now

tyvm

[formal coral]

np

[dire marsh]

okay thanks, how do i set up?

[formal coral]

google vscode, download

[grim elk]

I'd like to ask my question again. what is a dimension and a fact in a database? (Yes, i've googled them, but need to have a better understanding for the interview)

[brazen charm]

for the interview
Are you doing a interview that requires you know database or smth?

[grim elk]

yea, it's a data engineering role

[brazen charm]

when is this?

[grim elk]

tomorrow

[faint blade]

Not particularly no, I use Visual Studio Code and with the right extensions that can be amazing. I know many here use PyCharm which also has some support for databases (I know it has for PostgreSQL at least).

[brazen charm]

Man ngl, but you're fucked if you have no idea what databases are and have never worked with them before if the interview is tomorrow

[torn sphinx]

How to get the top role of a user?

[grim elk]

I know them, but never had to think about dimensions and facts

[faint blade]

They know what databases are, just don't understand the terms 'dimension' and 'fact'.

[brazen charm]

oh

😅 Okay well thats slightly less concerning

[grim vault]

You have created a file called select.py which is now prefered over the select standard module of python which is needed. Rename your local file.

[dire marsh]

That's something going on with me XD

[brazen charm]

yikes

[grim elk]

fact table basically sounds like a table where you store data that business will be using. so instead of business users querying 4 different tables and fucking up their joints, you create a separate table with values they need

am i right?

[rapid dust]

Thx 💙

[torn sphinx]

i know a bit of sql, but isnt there a wrapper or something?

also, if it is file bassed... it means it uses jsons?

[faint blade]

Looking at the Wikipedia this doesn't appear to be an unreasonable claim.

Example of a star schema; the central table is the fact table

[torn sphinx]

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1 

anyone know what this means

@faint blade can you help with this, its when i try to do the measures to protect injection

SELECT COUNT(*) FROM vrp_user_vehicles WHERE vehicle = %s

[faint blade]

Yes that is incorrect SQL, do you not give it arguments?

[torn sphinx]

i do

@bot.command()
async def hmc(ctx, car):
    sql = "SELECT COUNT(*) FROM vrp_user_vehicles WHERE vehicle = %s"
    mycursor.execute(sql, (car))
    result = mycursor.fetchall()
    print (result)

@faint blade

and when i do L!hmc asea

its meant to be a count of 3

but its 0

if i put the %s in single quotes

and without i get error

[grim vault]

see #help-chestnut

[faint blade]

This doesn't become a tuple you need to do (car,)

[median quiver]

hi
how do i open a xls file in pandas? all google guide don't see to work

pd.read_excel('test.xls')
´´´
ValueError: File is not a recognized excel file
i try change the engine, but it dont work

i cant change format to xlsx(linux user whit no M.Office here)

[stoic finch]

i'm using MongoDB with my discord bot, but i'm starting to worry about becoming rate limited with the increase in users/ servers whats the best way to avoid this?

[thorn geode]

There are a few wrappers for python, yes.

[thorn geode]

No.

[ionic pecan]

rate limited on discord or on mongodb? are you using a cloud provider for mongodb?

[stoic finch]

mongo, yeah cloud provider. ive heard about change streams but im not really clued up on that

[ionic pecan]

which limits do they provide? i would assume they have a document limit or database size limit? or how is it restricted?

[fading relic]

how to get randint to choose images

[austere portal]

you cant use random.randint to choose images, you need to use random.choice

[fading relic]

so how would i go about that

[ionic pecan]

put your images in a list or tuple, then run random.choice on it

[stoic finch]

they have a 100 requests a minute, limit. but because ill be expanding server and can imagine servers will have a hand full of people on them i can see that 100 a minute being blown away :/

[ionic pecan]

do they have statistics where you can check how many requests you are currently doing?

[stoic finch]

atm it wont be a problem because it isnt in other servers atm. but when i release to others thats when my worry will come

[faint blade]

Which queries do you think you do a lot?

For example a custom prefix is commonly stored in the database, you should cache this.

[stoic finch]

ive got a set prefix, i have a points command and success module. they would get battered the most

[stoic finch]

also do you have a link to a document about caching because i could definitely use this on some stuff

[faint blade]

Not really, I mean I can Google one right now but so could you. Many use redis for more advanced caching.

You could use a lru cache on your prefix stuff. Search up LRU (least recently used) caches, the idea is that you store the data in a way that means you can easily figure out which one was least recently used and then remove it.

I know there is a LRU cache in Python's standard library

[stoic finch]

alright nice one cheers, ill have a look at that

[torn sphinx]

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\index.py", line 99, in on_message
    await bot.process_commands(msg)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 975, in process_commands
    ctx = await self.get_context(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 886, in get_context
    prefix = await self.get_prefix(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 831, in get_prefix
    ret = await discord.utils.maybe_coroutine(prefix, self, message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\utils.py", line 343, in maybe_coroutine
    return await value
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\index.py", line 24, in get_prefix
    prefix = await bot.db.fetch('SELECT prefix FROM guilds WHERE guild_id = $1', message.guild.id)
AttributeError: 'Connection' object has no attribute 'fetch'
Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\cogs\levelling.py", line 58, in on_message
    ctx = await self.bot.get_context(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 886, in get_context
    prefix = await self.get_prefix(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 831, in get_prefix
    ret = await discord.utils.maybe_coroutine(prefix, self, message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\utils.py", line 343, in maybe_coroutine
    return await value
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\index.py", line 24, in get_prefix
    prefix = await bot.db.fetch('SELECT prefix FROM guilds WHERE guild_id = $1', message.guild.id)
AttributeError: 'Connection' object has no attribute 'fetch'
Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\cogs\Messages.py", line 40, in on_message
    ctx = await self.bot.get_context(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 886, in get_context
    prefix = await self.get_prefix(message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 831, in get_prefix
    ret = await discord.utils.maybe_coroutine(prefix, self, message)
  File "C:\Users\eyal2\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\utils.py", line 343, in maybe_coroutine
    return await value
  File "C:\Users\eyal2\OneDrive\שולחן העבודה\MyBot\index.py", line 24, in get_prefix
    prefix = await bot.db.fetch('SELECT prefix FROM guilds WHERE guild_id = $1', message.guild.id)
AttributeError: 'Connection' object has no attribute 'fetch'

Its postresql

[ionic pecan]

we need to know which database library you're using, and what you've done to solve the error yourself

[torn sphinx]

any easy and simple database for a discord server?

only need 5 or 6 fields

i mean

[austere portal]

sqlite , postgres, mysql

[torn sphinx]

for a discord bot*

mysql is like end game xD

[ionic pecan]

sqlite is the simplest proper database you can go

postgresql is the sanest, but not the simplest

[austere portal]

Yes

[cerulean ledge]

      async with aiosqlite.connect("laborbot.db") as con:
         cursor = await con.cursor()
         active = await cursor.execute(f'SELECT active FROM bumping WHERE active="0"')
         activeend = str(await active.fetchall())
         print(activeend)
         await cursor.execute(f"SELECT REPLACE ('bumping', '{activeend}', '1')")
         await con.commit()
         print("Updated everyones activity")

Why doesn't this delete anything?

[pure sleet]

uhm, cause you're not deleting anything

[arctic adder]

How do I start integrating db in my bot?

[white mauve]

is there free database server?

hmmm

[jaunty galleon]

I mean yeah it's not the simplest, but it's also not that hard. The hardest part for me was to connect to my db(using asyncpg, I first used local and than elephantsql), and learning SQL syntax(Which every sql db uses)

[jaunty galleon]

I use elephantsql for postgresql

[white mauve]

are they free?

[jaunty galleon]

There is Tiny turtle package which is free

https://elephantsql.com/

It's not vert good but I couldn't find any better way

[white mauve]

ohhh

[vocal thicket]

is it possible to have an array or dict in an sqlite db

[white mauve]

I don't understand MySQL execute haha

is it better if I use MongoDB?

[charred fractal]

It could be possible to have anything store in a sq database, a database is basically a giant dictionary.

[torn sphinx]

Missing expected field "locale", full error: {'index': 0, 'code': 4, 'errmsg': 'Missing expected field "locale"'}

[torn sphinx]

error in pymongo while i tried to change the values of a field, idk what this means

wait i think i figured it out

alr its workin

[austere portal]

By using a python database driver

[arctic adder]

What is a database driver?

[austere portal]

It gives the ability for your python program to interact with databases

[arctic adder]

Ohh
Like motor?

[austere portal]

Yeah, motor is used to interact with the mongodb database

[arctic adder]

When I try to connect to motor using this
it shows

import discord
from discord.ext.commands.core import bot_has_any_role
import motor.motor_asyncio


class ConnectDatabase(commands.Cog):
    def __init__(self, bot, client):
        self.bot = bot
        self.client = motor.motor_asyncio.AsyncIOMotorClient("localhost", 27017)
        db = client["Bot Database"]
        collection = self.db["guild collection"]


def setup(bot):
    bot.add_cog(ConnectDatabase(bot))

Traceback (most recent call last):
  File "/mnt/1AA2F7F2A2F7CFED/Big Projects/Omni8Bot2/Omni8-Bot/env/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 618, in _load_from_module_spec
    setup(self)
  File "/mnt/1AA2F7F2A2F7CFED/Big Projects/Omni8Bot2/Omni8-Bot/cogs/bot_database.py", line 16, in setup
    bot.add_cog(ConnectDatabase(bot))
TypeError: __init__() missing 1 required positional argument: 'client'

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

Traceback (most recent call last):
  File "/mnt/1AA2F7F2A2F7CFED/Big Projects/Omni8Bot2/Omni8-Bot/main.py", line 82, in <module>
    bot.load_extension(f"cogs.{filename[:-3]}")
  File "/mnt/1AA2F7F2A2F7CFED/Big Projects/Omni8Bot2/Omni8-Bot/env/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 678, in load_extension
    self._load_from_module_spec(spec, name)
  File "/mnt/1AA2F7F2A2F7CFED/Big Projects/Omni8Bot2/Omni8-Bot/env/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 623, in _load_from_module_spec
    raise errors.ExtensionFailed(key, e) from e
discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.bot_database' raised an error: TypeError: __init__() missing 1 required positional argument: 'client'```

[arctic adder]

Why is it so?

[austere portal]

Becuase your class init takes a bot parameter and a client parameter

You have not passed in the client parameter

[cerulean ledge]

Uh I meant replace

It doesn’t replace anything

[austere portal]

You are only getting data from the database and not updating anything

[oak carbon]

can we save embed object in postgresql?

[austere portal]

What embed object?

[oak carbon]

discord embed object

i want to save an embed in my db

[austere portal]

you can save the attributes of the embed in the database

[oak carbon]

can we not save the the object itself?

[austere portal]

No

[oak carbon]

hm so how can i save its attributes then?

[austere portal]

You can do py cursor.execute("INSERT INTO embeds (title, description) VALUES(%s, %s)", (embed.title, embed.description))

[oak carbon]

sure

[austere portal]

assuming you have a table named "embeds" which has a "title" column and "description" column

[grim zephyr]

someone help

[pure sleet]

you want to replace the data in the database?

select returns results, it doesn't alter anything

you're probably looking for update

[cerulean ledge]

there is a replace

I use it in my code a bit before and it works

[pure sleet]

what are you trying to do exactly?

[cerulean ledge]

replace every "activeend" with one

[pure sleet]

explain like i don't know what's going on

[cerulean ledge]

So I have this column called "active" right?

I want to replace every active with 0

[pure sleet]

in the database? or in your query

[cerulean ledge]

in my db yeah

[pure sleet]

you want to update your database with a select statement?

[cerulean ledge]

Replace*

[grim vault]

This select is useless: SELECT active FROM bumping WHERE active="0"

[cerulean ledge]

it is?

[pure sleet]

you want to replace the contents in the database, with a select statement? bro

[grim vault]

You'll just get a list of 0

[cerulean ledge]

I have the select replace statement in my code a bit before and it works pretty well

I just googled it

[grim vault]

If you just want every 0 to become a 1 you do:
for INTEGERS: UPDATE bumping SET active = 1 WHERE active = 0
for TEXT: UPDATE bumping SET active = '1' WHERE active = '0'

[pure sleet]

bro, that only affects the data you're getting back tho

[cerulean ledge]

thanks!

[torn sphinx]

uh..

Command raised an exception: TypeError: list indices must be integers or slices, not str```

[grim vault]

Sorry, I used " which is not for strings in SQL, I updated my post above.

[cerulean ledge]

Thank u!

[vocal thicket]

What’s the advantage of using sqlite or some other database instead of just storing my data in a file (either json or a py file) if I’m coding a small to medium sized discord bot

[marsh mango]

@bot.command()
async def blubb(ctx, arg1, arg2, arg3, args):
  await ...
```do i have to use all args?

[finite ice]

just use them..?

[lavish idol]

hello guys i have a problem cause i cant send data to mongodb and im getting an error:

[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificat

[ionic pecan]

ease of querying, ease of not having to maintain the file format yourself, ease of a standardized language to access your data which you can also apply elsewhere

[ionic pecan]

no

[ionic pecan]

you'll need to paste the full traceback for us to be able to help you

[torn sphinx]

It's fixed again

[blazing onyx]

hey guys umm when i am connecting my python with mysql I am facing this issue any solutions?

[austere portal]

you need to install the mysql library

[blazing onyx]

okay am not sure if i have installed it but i do have mysql on my laptop

[austere portal]

No, you need to install the mysql python module to interact with the mysql database

[blazing onyx]

how can I do that sir?

using the mysql community installer?

[austere portal]

No

You need to use pip to install the mysql python module

[blazing onyx]

done that

do i have to restart my laptop or something

[austere portal]

no

What OS are you using?

[blazing onyx]

windows 10

[austere portal]

then do pip show mysql to see if it is installed

[blazing onyx]

yep one sec

here ☝️ @austere portal

[crystal cobalt]

help why does this not work valid = re.findall('(^+1\d{10})',pnumber)
it finds a match even when its more than 10 numbers following the 1
(if this isnt the right channel tell me)

[austere portal]

Now you have installed it

It should work now

[blazing onyx]

no no i had it before

the first thing i did was to install pip

showed the same error

i updated the pip to latest

still

[austere portal]

Do you have a virtual environment activated?

[blazing onyx]

virtual environment variables yeah ive changed it

added it to the path

you there?

[austere portal]

Yeah

I cannot think about what is causing the issue

[austere portal]

Try running this and see py import sys print(sys.executable)

[blazing onyx]

in cmd yeah?

[austere portal]

In spider

[austere portal]

I think spider is using a different python environment which doesn't have mysql.connector installed

Yeah, that seems to be the problem

[blazing onyx]

so how do i solve it?

[austere portal]

Enter your anaconda environment

and install mysql-connector in the anaconda environment

[blazing onyx]

ah am sorry am new to this stuff can you guide me a little more

[austere portal]

Search "Anaconda command prompt"

and open it

[blazing onyx]

yep its open

also it showed this error

[austere portal]

Yeah, because now you are in your anaconda environment

Now install mysql-connector there

[blazing onyx]

how is that possible?

[austere portal]

try doing pip show mysql-connector

[blazing onyx]

this is the output

[austere portal]

Did you open the anaconda command prompt?

[blazing onyx]

yep

this is in anaconda prompt only

[austere portal]

Try running that code to verify it

[blazing onyx]

in spyder?

[austere portal]

That should be the output

[austere portal]

no, in the anaconda prompt

[blazing onyx]

ah

import is not the command

[austere portal]

enter py first to enter the python console

[austere portal]

sorry forgot to mention that

[blazing onyx]

so its
py import sys
print(sys.executable)

yeah?

[austere portal]

type in py separately at first

[blazing onyx]

same output as you said

[austere portal]

Hmm doesnt seem like the anaconda env is activated

or else the result should be the same as the result you got when you ran it in spider

[blazing onyx]

how do i activate it then?

wait idk why i have 2 anaconda prompts

and i think am doing all this in anaconda 3.3 while my spyder is anaconda 4

[austere portal]

can you open the cmd in spider?

like is there a option to open a inbuilt cmd?

If so, open it

[blazing onyx]

i dont use spider but its my teacher who asked me to so i have no idea

[austere portal]

Yeah, spider is a bit complex

But it makes you look cool when using it

[blazing onyx]

do you think because of those double anaconda prompt its making a difference

[blazing onyx]

yeah buddy but right now i wanna solve it so that i can go to bed 12Am here

[austere portal]

I guess you should go to bed now...

[blazing onyx]

i cant i have a class on monday and i need to solve this issue by today

[austere portal]

The problem is spider is using a different python environment and you have installed mysql-connector in a different python environment.

[blazing onyx]

can it be solved?

i installed mysql connector in this prompt and this is what i got

[austere portal]

Looks like now you are in your anaconda environment

Try running the code i sent in it (and show the result)

[blazing onyx]

now on spider ya?

[austere portal]

no, in the cmd (which you have open), run the code i sent

[austere portal]

run spider and see if it works

[blazing onyx]

gonna do that now

okay

that error solved finally

now

there is another error

[austere portal]

🙂

What is the error?

[austere portal]

oh no, can you send the full trace back?

[blazing onyx]

the full code?

[austere portal]

Yeah, and the full error

!paste

[delicate field]

Pasting large amounts of code

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

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

[blazing onyx]

mydb=mysql.connector.connect(host="localhost",user="root",passwd="500500")
mycursor=mydb.cursor()```

[austere portal]

Sorry I am not familiar with the mysql-connector library, someone else will try to help

[blazing onyx]

no worries thanks a lot though! 🙏

[austere portal]

😄

[blazing onyx]

solved that error too finally going to bed thank you again!

[peak lake]

so I getting this error, however I keep wondering why it isn't transfering over the ID from another table

this is where I made the original table

[austere portal]

You should pass in the PatientID

[grim vault]

An foreign key means that the value you insert must exist in the referenced table, not that one is just taken.

[peak lake]

well the key I'm trying to pass in is in the referenced table the problem for me i suppose is actually filling it in the 2nd table

[peak lake]

so would I do surgery(PROCEDURE, PATIENTID) ?

[torn sphinx]

Do what rush said

To fix your issue

[grim vault]

Yes, that would be correct.

[austere portal]

Yes

[peak lake]

now im coming up with this, i assume I have to look back on the first table and type in the appropriate value?

[austere portal]

In the VALUES you need to pass in the patient id

eg: INSERT INTO surgery(PROCEDURE, PATIENTID) VALUES('Heart surgery', <a_valid_id_in_the_members_table>)

[hoary pagoda]

Is the SQLite SUM function really slow?

Like 30460ms on a 453000 record database

[faint blade]

I am not completely sure, what are you SUMming?

[faint blade]

Do you have that many records?

[hoary pagoda]

Just some balances

I filter down to about 68 records

[faint blade]

Then you should really have no worry about the speed of SUM

[proven arrow]

@hoary pagoda What is the query?

There could be other factors affecting it

[brazen charm]

Sum can be really slow if there are no indexes on the table which by default i dont believe sqlite does

Which leads to a sequential scan

[proven arrow]

Doesn't really matter as aggregates have to run through all the columns regardless

[hoary pagoda]

I think I figured it out

I ran sum on the whole table then filtered instead of filter then sum

I’m not at my computer but that should speed it up I think

[brazen charm]

Depends on the implementation really but indexes do make a considerable diffrence to relational databases

[proven arrow]

When filtering yes

Aggregate still has to go through all columns which sum is

[torn sphinx]

all columns? i thought only 1?

[proven arrow]

That should say rows instead, but yeah only 1 column, i meant all the filtered rows for column that is being aggregated

[topaz wharf]

How can I check if some value is arl in the table from my db? To avoid duplicate values SQL

[shell ocean]

don't do that

just create a unique constraint

you don't need to check for duplicate values if you can't have them

[topaz wharf]

hmmm, I will learn about it

ty

[shell ocean]

yw

[dusty gyro]

How do I do an UPDATE/RETURNING with serverside-computed columns within sqlalchemy ORM? I'm looking at something like:

import asyncio
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.asyncio
import datetime

registry = sqlalchemy.orm.registry()
engine = sqlalchemy.ext.asyncio.create_async_engine("postgresql+asyncpg:///", echo=True)

@registry.mapped
class X:
    __tablename__ = "x"
    pk: int = sqlalchemy.Column(sqlalchemy.BigInteger, primary_key=True)
    time: datetime.datetime = sqlalchemy.Column(sqlalchemy.TIMESTAMP, nullable=False)

async def main():
    async with engine.begin() as conn:
        await conn.run_sync(registry.metadata.create_all)
    async with sqlalchemy.ext.asyncio.AsyncSession(engine) as session:
        stmt = sqlalchemy.update(X).returning(X)\
            .where(X.pk == 123, X.time < sqlalchemy.func.current_timestamp())\
            .values(time=sqlalchemy.func.current_timestamp())
        for x in await session.execute(stmt):
            print(x)
asyncio.run(main())

However it's telling me sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate current_timestamp". Specify 'fetch' or False for the synchronize_session execution option.
A quick google shows that the synchronize_session thing is for sqlalchemy.orm.Query? And also that update/returning cannot be done with Query?

[unkempt prism]

Does it need the parenthesis? In raw sql postgres doesn't need it.

select current_timestamp

If that doesn't work with sql alchemy. You may be able to use now

select now()

[granite echo]

help!! i need help installing mysql and connecting it properly to python

i get this error

when i do this

pls help

i installed mysql using pip install mysql-connector-python

[dusty gyro]

this is an error on the python side not the sql side

[granite echo]

anyone pls help

[dusty gyro]

so apparently the magic word is .execution_options(synchronize_session=False)

        stmt = sqlalchemy.update(X).returning(X)\
            .where(X.pk == 123, X.time < sqlalchemy.func.current_timestamp())\
            .values(time=sqlalchemy.func.current_timestamp())\
            .execution_options(synchronize_session=False)

[blazing onyx]

okay so
-> write the same code again but remove the database this time and see if it worked.
-> have you installed mysql-connector?

[granite echo]

yes i installed mysql connector

[blazing onyx]

do show mysql-connector in cmd and tell me what it says there

[granite echo]

yes 1 min

it says show is not recognised as an internal or external command,
operable program or batch file.

@blazing onyx

[dusty gyro]

looks like sqlalchemy doesn't actually support marshalling postgres INTERVAL types?

or maybe it's asyncpg

[granite echo]

when i ran without database

error

[dusty gyro]

asyncpg wants datetime.timedelta for INTERVAL, and doesn't like sqlalchemy's sqlalchemy.dialects.postgresql.INTERVAL

[granite echo]

but when i try to install mysql-connector-python again, it shows its already installed

[blazing onyx]

have you changed the path in environment variables?

[granite echo]

to scripts right?

[blazing onyx]

yes

also whenever you reply to me just @ me as well

[granite echo]

ahhh no i didnt. stupid brain.

@blazing onyx

[blazing onyx]

you gotta do that

[granite echo]

can u just tell me what i hv to do there pls @blazing onyx

i hv this @blazing onyx

[blazing onyx]

wait one secc

: •https://www.youtube.com/watch?v=dj5oOPaeIqI

follow this

[granite echo]

ok tnx

[velvet flume]

I had a query regarding MongoDB

For Index Creation

1. Is it something that needs to be done on a regular basis? If no, can we run it once on mongoshell and it's enough for future documents too? more like configuration?

[lavish idol]

[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate pls someone help i dont know what i should do. Im sending smth do mongodb and that shows up

[faint blade]

Asyncpg should have full support iirc?

[dusty gyro]

yeah it worked when I passed datetime.timedelta

but not the sqlalchemy INTERVAL type

[faint blade]

Huh, interesting haha

[untold dust]

why doesnt this work (im using mongodb)

def _load(self):
        '''The _load() function is used to load variables from the database'''
        info = self.db["info"]
        self.info = info.find({})
        for item in self.info:
            json_str = dumps(item)
            item2 = loads(json_str)
            self.info[item] = item2```

when i do _load it throws the error

TypeError: 'Collection' object is not callable. If you meant to call the 'find' method on a 'Database' object it is failing because no such method exists```

[faint blade]

Can you show the rest of your code?

You can print type(info), it will be a database iirc

[untold dust]

heres the code

class Database_Handler:
  def __init__(self):
    self.db = MongoClient("CONNECTION_STRING")
    self.info = {}

    def _load(self):
        '''The _load() function is used to load variables from the database'''
        self.info = self.db.info.find()
        for item in self.info:
            json_str = dumps(item)
            item2 = loads(json_str)
            self.info[item] = item2

    def _save(self):
        '''The _save() function is used to save the variables to the database'''
        self._load()
        info = self.db.info
        info.delete_many({})
        for player_info in self.info:
            info.insert_one(player_info)
database_handler = Database_Handler()``` heres the class where i handle the database related things

[untold dust]

its a database yea

wait wat

[faint blade]

MongoClient follow this order:

MognoClient.your_database.some_collection

info is a database, you need to do self.db.info.SOME_COLLECTION.find() (replace SOME_COLLECTION with the collection you're looking for)

[untold dust]

gtg brb

[faint blade]

Alright

[untold dust]

works, ty!

[torn sphinx]

are there any sql experts here? Had a question -

I have been trying this use case but had no luck, so any help would be really helpful.
I am currently on redshift db and I am trying to create a table which holds all the table name from one of the schemas. I am trying more of an automated approach rather than a manual one. So what I did was

delete from mytable Insert into mytable select table_name from information_schema.tables where table_schema = 'source_schema' and table_name like 'report%'

But looks like redshift doesn't allow to use leader node table with compute mode tables.
Any idea, any suggestion on what I can do to create a table which will hold all table names from particular schema??

This is the error I have been getting

Specified types or functions (one per INFO message) not supported on Redshift tables

tried creating a stored procedure and a view as well but kept getting same thing

with cte as ( select table_name from information_schema.tables where table_schema = 'source_schema' and table_name like 'report%') select * from cte)

[next summit]

For any MongoDB users, can someone explain to me how long building a text index for 50 documents should take? It seems to just be hanging on 0% forever but there are no error messages?

[next summit]

One field from 50 documents

[ionic pecan]

I don't know MongoDB myself, but this sounds like it could maybe be a locking issue? maybe the index build is waiting to acquire a lock that another client is holding

[harsh night]

with mongodb how can i push data to a object?

[snow niche]

anyone know sqlite

[austere portal]

Yes

[snow niche]

lol

im trying to make modlogs

so heres what i have so far

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

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

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

error:

Traceback (most recent call last):
  File "C:\Users\hp\Documents\Zion\cogs\mod.py", line 181, in kick
    await ch.send(embed=em)
AttributeError: 'NoneType' object has no attribute 'send'

[jaunty galleon]

That is d.py issue ig

[austere portal]

And you are creating a connection every time the command is used, which is bad

[snow niche]

i need it to work like logs

i might just create a webhook and send using that

but for now

[austere portal]

Cursor.fetchone returns a tuple not a single integer

[snow niche]

so how do i fetch int

[austere portal]

You can index the tuple

[snow niche]

example

[austere portal]

Print the channel variable and see

[snow niche]

ok

@austere portal this is how i initialize the db, is it right?

async def dbinit():
# mod-logs db
    mldb = await aiosqlite.connect("db's/modlogs.db")
    await mldb.execute("CREATE TABLE IF NOT EXISTS modlogs (guild_id int, channel_id int)")
    await mldb.commit()

[austere portal]

Yeah, you are connecting to it and creating a table named modlogs

[snow niche]

but it said no table named modlogs

A weird error occured:
Command raised an exception: OperationalError: no such table: modlogs

[austere portal]

Maybe you are connecting to the wrong database file

[snow niche]

it is right

[austere portal]

Hmm

And you can have a single database file with multiple tables

[snow niche]

blacklist is for dev only

[austere portal]

I think it's cursor.execute

[snow niche]

i did execute it

[austere portal]

cursor = conn.cursor()

[snow niche]

# ========== DATABASE ============
# warn db
async def dbinit():
    await client.wait_until_ready()
    warndb = await aiosqlite.connect("db's/warnData.db")
    await warndb.execute("CREATE TABLE IF NOT EXISTS warningsData (guild_id int, admin_id int, user_id int, reason text)")
    await warndb.commit()
# blacklist db
    bldb = await aiosqlite.connect("db's/blacklist.db")
    await bldb.execute("CREATE TABLE IF NOT EXISTS userBL (user_id int)")
    await bldb.commit()
# mod-logs db
    mldb = await aiosqlite.connect("db's/modlogs.db")
    await mldb.execute("CREATE TABLE IF NOT EXISTS modlogs (guild_id int, channel_id int)")
    await mldb.commit()

this is my 3 inits

[austere portal]

Try opening the database in db browser sqlite and check if there is a table created

[snow niche]

i did that

said no table

[austere portal]

Hmm

[snow niche]

should i try this

[snow niche]

or is this fine

[austere portal]

Ig that should be fine

Restart your program and see

[dusky siren]

in sqlite, I made a value without making it autoincrement by accident when I need it to autoincrement. How can I make it autoincrement, preferably with code [rather than with a DB browser]

[subtle gazelle]

is there any asyncio wrapper module for pymongo?

[brazen charm]

mongo motor

[subtle gazelle]

ty

[next summit]

I'm not sure, it's just stuck at 0% but not sure where to diagnose the problem...

[faint blade]

You shouldn't connect to the database each command. You should connect on startup, then acquire cursors on each command

[proven arrow]

Shouldn’t really matter for sqlite as SQLite connections are very cheap to makes. As it doesn’t have to go through all the steps that a server based db would to create a connection.

[torn sphinx]

redis is an in-memory database?

[ionic pecan]

yes, and it also flushes its data to disk every now and then. however, if you plan to use it as something where you actually want to be sure you keep your data, you need to tweak some settings of it

[torn sphinx]

I would only use it as in memory

[faint blade]

Just because the cost is smaller doesn't mean it would be acceptable. I am not sure how aiosqlite would handle if two commands run at the exact same time (race conditions).

It's simply better to just follow good practice

[brave bridge]

on my computer, making an sqlite3.Connection object on a file-based (not in-memory) database takes 24 µs

which is about as long as 2-3 queries, and a pretty small duration

but yeah, I don't know how aiosqlite handles concurrency

[torn sphinx]

do you mena this?

its from sqlite docs

[dense barn]

I have a column with numbers which im trying to fetch using the ORDER BY ASC in postgres but it seems to be messing up, it starts with 3 digit numbers and then to 2 digits. Does the column need to be an integer? Because the data type for that column now it text iirc

[faint blade]

Yeah so having multiple connections is a very bad idea (corruption thanks to race conditions).

[torn sphinx]

But it says you can have multiple connections?

With sqlite theres no harm in multiple connection.

[torn sphinx]

yes

other wise you will get alphabetical ordering

[faint blade]

No it says it right there "But only one process can be making changes to the database at any moment in time, however". I interpret this as you should not let two connections make changes to the database at the same time, meaning that you need to implement sufficient locking.

[torn sphinx]

No i think you misundestand

that just how sqlite is

its a limitation of it

only 1 thing can be writing at a time

It doesnt mean you can't have connection multiple times

[brave bridge]

That's not what it means. It means that SQLite literally won't let you enter a situation where at the same time there's one writer and any reader, or more than one writer

[torn sphinx]

@brave bridge what is this symbol? 24 µs

[brave bridge]

https://en.wikipedia.org/wiki/Mu_(letter)

µs means 'microsecond'

[torn sphinx]

oh ok i see

[faint blade]

Who handles the locking? The OS?

[brave bridge]

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

SQLite uses reader/writer locks to control access to the database. <...> SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update.

Actually, this can cause an issue. I just tested: if you accidentally do two writes at the same time, on one connection it's fine (it will just queue the writes), but across two connections it will throw an OperationalError: database is locked.

So you're probably right that one should make only one connection and reuse it

[torn sphinx]

that happens when you are still writing

so its better to make a new connection each time?

[faint blade]

Ah, I remember hearing about it. Guess I'll have to look into it more.

In databases like PostgreSQL there's always a server that handles these locks, but there's no server for SQLite.. this feels prone to the classic thread increment race condition: https://en.wikipedia.org/wiki/Race_condition#Example
A reads, B reads; A writes, B writes. Now both of them think they have the lock

[torn sphinx]

well it doesnt really matter if a new connection or global connection i guess since you just need to make sure it is ready to write

[faint blade]

No, it opens up a lot of issues. In DTS's case, they should create the connection when the bot starts and add it as an attribute to the bot. Then they can do await self.bot.db.cursor() and continue like usual.

[torn sphinx]

What issues?

Not sure i understand

[brave bridge]

If one connection is in the process of writing, another connection will get an exception if it tries to read or write. So if you want to use multiple connections, you'll have to make some sort of retry system.

However, if you use a shared connection (at least in aiosqlite), it will queue up the reads and writes

[torn sphinx]

I just create connection and close it, works fine for me

never had an issue

[faint blade]

The ones we talked about: I was wrong (SQLite has measures in place so that it doesn't cause corruption), but you still get errors if you try to write when another connection has the lock.

[faint blade]

This is because to cause the issue we're talking about, you need to write at the same exact time.

[brave bridge]

This consistently causes an error for me:

import aiosqlite
import asyncio

async def write():
    async with aiosqlite.connect("dummy.db") as conn:
        await conn.execute("INSERT INTO foo (bar) VALUES (1)")
        await conn.commit()

async def main():
    async with aiosqlite.connect("dummy.db") as conn:
        await conn.execute("CREATE TABLE foo (bar)")
        await conn.commit()

    await asyncio.gather(*[write() for _ in range(100)])

asyncio.run(main())

[torn sphinx]

but the same issue can also happen with a single connection too then right? If two people write at the exact same time with a global connection?

[faint blade]

No, because the library can handle this. It will put the writes in a queue and do them one at a time.

[dense barn]

ok lemme try changing it. Wait can i even change it from text to int

[faint blade]

The library doesn't know of your other connections, so it can't do them one at a time like with one connection.

[faint blade]

!e print("365" > "5")

[delicate field]

@faint blade :white_check_mark: Your eval job has completed with return code 0.

False

[dense barn]

[faint blade]

When it is a string it gets ordered alphabetically. 3 comes before 5 so 5 is bigger

[dense barn]

ohhh

thats why

[faint blade]

!e The same way: ```py
print("azzzzzzzzzzzzzz" > "zaaaaaaaaaaaaaa")

[delicate field]

@faint blade :white_check_mark: Your eval job has completed with return code 0.

False

[dense barn]

yea, i get it now

[torn sphinx]

Ok well you can always increase the timeout so it can wait longer. theres a parameter to set the timeout

anyways im done for today

[dense barn]

@faint blade one more thing, what is/are collations?

cuz im getting an error: ERROR: collations are not supported by type integer

[faint blade]

When do you get this?

[dense barn]

when trying to change my column's data type from text to bigint/int

[faint blade]

Do you have data in the columns?

[dense barn]

Yea I do

[faint blade]

PostgreSQL most likely won't convert it.

Here's what I recommend:

• Create new column with integer type
• Create Python script to select, convert, and update (could potentially be done in pure SQL)
• Delete old column
• Rename the new column

[dense barn]

Ah

Yes

Ty

My dumb brain would have just deleted that column and make a new one

But thanks!

[austere portal]

Use the same connection across the bot.

[drowsy lily]

Hello, I have a question of SQLite, How do I create a field that is the results of 2 other fields. I need to create a new column that is [field2]/[field3] and the name is "average"

[brave bridge]

Is this what you're looking for? https://www.sqlite.org/gencol.html

[drowsy lily]

I am trying to create a new field that, is the average of another field that, is the division of 2 other fields, then group it by another field

Im not sure how to explain it

[storm mauve]

are you sure that this field should be a column in the database?

[austere portal]

bot.db = bot.loop.run_until_complete(aiosqlite.connect(...))

now you can access the db conn as bot.db

What do you mean?

The database will lock if a process is happening and you start another process (2 processes at the same time).

[brave bridge]

Actually, here's another issue: how do you manage transactions if you share a single connection?

[austere portal]

I always used to get "sqlite3.OperationalError: database is locked" in my django application (when I insert data multiple times)

[brave bridge]

speaking of SQLite, why doesn't the executescript method accept parameters? That would make some things easier

[austere portal]

no

bot = commands.Bot(...)
bot.db = bot.loop.run_until_complete(aiosqlite.connect(...))```

no

what is db1 and db2?

yes

[torn sphinx]

client.db.collection.create_index('expireAt: 1', [{'expireAfterSeconds': 0 }])

PyMongo - Anyone know the correct usage for creating a collection index with TTL?

[brave bridge]

I think it's a big argument against reusing a single connection. It's possible to increase the maximum busy timeout: https://www.sqlite.org/pragma.html#pragma_busy_timeout, which could make clashing errors extremely rare

[pale lava]

if not {result for result in data.find({"_id": 123})}:
  db.insert_one({"_id": 123})
else:
  db.inert_one({"_id": 123})
``` PyMongo - if there is already key with an id value of 123, would db.insert_one overwrite that key?

[torn sphinx]

Hey GUYs ...
I got a project of making a school management system through which we can add and search student's data ...simply this much ...I look for the tutorials but all of they uses php and xammp ...but my system doesn't support xammp ..being 32 bit...
so can i do it through python or by other way?

[austere portal]

You can use a database python database driver to interact with a database

[faint blade]

What do you mean? If you use transactions you would simply hold the lock?

[brave bridge]

1| async def f(conn: Connection):
2|      async with conn.cursor() as cur:
3|          await cur.execute("INSERT INTO foo (bar) VALUES (1)")
4|          await spam()
5|          await cur.execute("INSERT INTO foo (bar) VALUES (2)")
6|          await conn.commit()

7| async def g(conn: Connection):
8|       async with conn.cursor() as cur:
9|          await cur.execute("INSERT INTO foo (bar) VALUES (3)")
a|          await ham()
b|          await cur.execute("INSERT INTO foo (bar) VALUES (4)")
c|          await conn.commit()

Suppose that you launch f and g with the same connection at the same time, and they execute like this (line numbers):

2 8 3 4 9 5 6 

then suppose thata (await ham()) raises an exception. That probably means we should roll back what g is doing. But we can't -- f has already commited 1, 2, 3 to the database

In something like Postgres, if I understand correctly, when you want to process a request, you:

1. Acquire a connection from a connection pool
2. Start a transaction on that connection
3. Do whatever you want to process the request inside the transaction
4. Either roll back or commit that transaction

[faint blade]

Are you sure? I'd expect one to hold at the context management entering (line 2 or 8)

[faint blade]

Yup that is how that's handled, each command (or function/usage rather) would acquire a connection from the pool and use that however it pleases.

[brave bridge]

As far as I know, a transaction is connection-wise, not cursor-wise.

I think that's also what will happen on other DBMSs

So... I don't really have a solution for this other than

1. Don't use SQLite
2. Bite the bullet and accept that once in a while you'll get an error
3. Write a better library

[faint blade]

That's both interesting and rather disappointing, why isn't aiosqlite taking care of this..

[faint blade]

Yeah haha, we can simply agree that SQLite is not good for these applications 😅

[brave bridge]

I think option 2 is not that terrible

if you write so much that you can't get to write in 1000 ms, you probably need a different store

[austere portal]

It will put the writes in a queue and do them one at a time.
Does the sqlite3 lib queue the insertions?

[brave bridge]

aiosqlite does

if I understand correctly what it does

[austere portal]

Interesting...

[brazen charm]

aiosqlite does it because it has to stay in the same thread and is ran in a background thread

so operations are fed in as a queue of changes and then given futures are resolved after

standard sqlite just uses the traditional Lock system

[brave bridge]

https://github.com/omnilib/aiosqlite/issues/19

I feel like anything depending on transactions should be using separate connections for each concurrent transaction

[torn sphinx]

I have a many to many users-products. How to get all the products a user doesn’t have?

[faint blade]

Ah, yeah. So the bottom line is:

• Use one connection global to the application
• Whenever transactions are deliberately used, get another connection

[faint blade]

Do you know how to get all products a user has?

[torn sphinx]

Yes

[faint blade]

Can you send that query?

[torn sphinx]

It needs to be different to that because for that I currently use multiple queries. The doesn’t have one needs to be done in a single query.

[proven arrow]

You can use a subquery with where not exists to filter out products user already has

[torn sphinx]

Hmm not sure I understand what you mean

[proven arrow]

select
    *
from
    products
where
    not exists (
        subquery goes here
    )

[brazen charm]

where is that SCREAMING CASE convention

[torn sphinx]

I see let me try something I well get back if I have issues

[proven arrow]

uppercase is ugly, like fortran or cobol if you remember that

But it doesn't really matter these days since editors support syntax highliting

[brave bridge]

SQL in general feels a bit like COBOL to be honest...

[proven arrow]

even discord does

[brazen charm]

BUT SELECT * FROM foo

[proven arrow]

People before would use uppercase as it stands out, but i dont mind either tbh

I guess for inline queries uppercase helps to standout

[brazen charm]

personally im just in the habbit of doing uppercase

[chilly temple]

Im quite certain you could develop some form of deep personality test by studying a persons SQL "style"

lower case all the way for me

probably indicative of being a mass murderer

[faint blade]

I use uppercase for all SQL words, then lowercase for my columns, tables, indexes etc. That makes it very easy to distinguish between SQL and user-created.

[chilly temple]

sensible

[torn sphinx]

So i tried this but returns empty although no error.

select * from products where not exists (SELECT 1 FROM product_user WHERE product_user.user_id = 1)

i expect to return 5 products as user only has 1 of 6 products.

[proven arrow]

Your subquery is incorrect

@torn sphinx Try this,

select
    *
from
    products
where
    not exists (
        select
            1
        from
            users
            inner join product_user on users.id = product_user.user_id
        where
            products.id = product_user.product_id
            and user_id = ?
    )

[torn sphinx]

Thanks soo much man 😋
That works although I might need to add extra where clause so it filters deleted products.

[vapid vapor]

I'm getting

table e_users has 7 columns but 6 values were supplied

await bot.db.execute("CREATE TABLE IF NOT EXISTS e_users (id int, name text, guildid int, bal double, totalearnings double, profilecolor text, lotterieswon int)")

[austere portal]

Your insertion query?

[vapid vapor]

    async def add_player(self, member_object):
        """Adds a player to the database"""
        try:
            await bot.db.execute("INSERT INTO e_users VALUES (?, ?, ?, 100.0, 0.0, 'FFFFFF')", (member_object.id, member_object.name, member_object.guild.id,))
            await bot.db.commit()
            return "Done! View your profile with `e$profile`"
        except Exception as e:
            return str(e)

[austere portal]

The table has 7 columns but you have only supplied 6 values

[vapid vapor]

oh shoot

thx

also im getting database is locked?

[austere portal]

sqlite being sqlite

[vapid vapor]

how to fix

nvm fixed

[grim vault]

I would

select *
  from products
 where id not in (select product_id
                    from product_user
                   where user_id = ?)```

[proven arrow]

Yes but they said "user doesnt have" so the join would allow to filter further attributes, and not exists can be faster

And not to mention the weird results not in will cause with nulls, although its unlikely in this case. But i would generally use not exists over not in.

[grim vault]

Hm, wouldn't the exists need to do a select for each product where the not in only needs one subselect?

[trim lintel]

I am making a project where I have to send data from mysql to a telegram bot when the user presses a button, however I cannot do so. The program is suppose to send the list of usernames to the user with the 'Show Data' button is pressed.

The issue I faced is that I constantly keep getting this error 'KeyError: ('csp',)'. csp which is one of the usernames.

This is the list of usernames it is suppose to print in telegram

('csp',) ('gss',) ('ccy',) ('ag',) ('rc',)

import mysql.connector as mysql
import json
import logging
import sys
import time
import telepot
from urllib import request
from telepot.loop import MessageLoop
from telepot.namedtuple import InlineKeyboardMarkup, InlineKeyboardButton
from pprint import pprint

# store the TOKEN for the Telegram Bot
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "",
database = "database"
)

cursor = db.cursor()

TOKEN = 'token'
bot = telepot.Bot(TOKEN)
def on_chat_message(msg):
    content_type, chat_type, chat_id = telepot.glance(msg)

    keyboard = InlineKeyboardMarkup(inline_keyboard=[
                   [InlineKeyboardButton(text='Show Data', callback_data='show')],
                   [InlineKeyboardButton(text='Delete Data', callback_data='delete')],
                   [InlineKeyboardButton(text='Show latest Data', callback_data='lastest')],
               ])

    bot.sendMessage(chat_id, 'Use the menu to show your WeatherStation values', reply_markup=keyboard)

def on_callback_query(msg):
    
    query_id, from_id, query_data = telepot.glance(msg, flavor='callback_query')

    print('Callback Query:', query_id, from_id, query_data)

    if(query_data == 'show'):
        
        query = "SELECT user_name FROM users"
        usernames = cursor.execute(query)

        bot.sendMessage(from_id, msg[usernames])
        

            
    elif(query_data == 'delete'):
        query = "DELETE FROM users"
        cursor.execute(query)
        db.commit()
 
    elif(query_data == 'lastest'):
        bot.sendMessage(from_id, text='test3')
        

bot = telepot.Bot(TOKEN)
MessageLoop(bot, {'chat': on_chat_message,'callback_query': on_callback_query}).run_as_thread()
print('Listening ...')

while 1:
    time.sleep(100)        

anyone?

[grim vault]

I don't know but this msg[usernames] looks like you are accessing a dict with the result of the select as key?

[trim lintel]

Hello

so it should be just one value?

how to get correct value then of username 😬 ?

[grim vault]

I don't know what msg is.

[trim lintel]

is just usernames list/dict

[faint blade]

Is it a list or dict?

[trim lintel]

list of dict

[trim lintel]

Hello how are you?

[grim vault]

It must be a dict or you wouldn't get a KeyError.

[faint blade]

A list with each item being a dict? Can you print the structures of msg as well as usernames?

[grim vault]

From the code above usernames is a iterator over the user select.

usernames = cursor.execute(query)

[trim lintel]

how to get the value from usernames?

[grim vault]

It's an iterator, you just iterate over it:

        query = "SELECT user_name FROM users"
        usernames = cursor.execute(query)
        for (user_name,) in usernames:
          # do something with it```

[jagged forum]

hello, I have an issue

so, I just wrote this code:

import sqlite3 as sql
db = sql.connect('database.db') #where database.db is an empty database file
db.execute('CREATE TABLE prefixes(id int, prefix str);')
db.execute('CREATE TABLE birthdays(id int, day int, month int, year int);')
#it created the tables succesfully
db.execute('INSERT INTO prefixes(id, prefix) VALUES(0, "j.");')

it returns me <sqlite3.Cursor object at 0x00000267EC8B6A40> but when I run the db file I cannot see the values into the table, whyyyyyyy?

[cedar cargo]

you just need to write db.commit()

[prime kelp]

hello

aiosqlite here!

wondering how to use the fetchall method...basically i have 4 columns, and i want to see which people have the top 10 levels of people (Imagine 10 people were on the db). Not sure if its fetchall, just taking a guess.

so it like

looks through xp, finds the top 10 xp in the same specific guild

[brave bridge]

SELECT user_id, guild_id, xp, level
  FROM users
  WHERE guild_id = :guild_id
  ORDER BY xp ASC
  LIMIT 10
``` ![this](https://cdn.discordapp.com/emojis/470903994118832130.webp?size=128 "this") Is this what you want?

[prime kelp]

i think so, yes, ty - ill try in the morning

[forest slate]

d

d

[paper bluff]

what does line 3 do? with the colon :

[brave bridge]

It's for substituting parameters with proper escaping. You can do either

execute("SELECT name FROM users WHERE id = ?", (user_id,))
``` or ```py
execute("SELECT name FROM users WHERE id = :id", {"id": user_id})

[paper bluff]

ah

[forest slate]

hello

[junior pecan]

hi

[sinful condor]

My self vps hosted mongodb database keeps ressetting data can someone help me. I have no clue what the problem is.

[desert sandal]

what DB do i use for my discord bot project ? MySQL PostgreSQL or MongoDB

[grim badger]

can I create the sql file direct from python?

[austere portal]

Yes, you can use open

[prime kelp]

hey, just woke up - this isnt a walrus right?

[prime kelp]

do i use execute for this?

[austere portal]

? 💪

[prime kelp]

OperationalError: unrecognized token: ":"

[austere portal]

no, thats not how you do it

[prime kelp]

i removed the : and it didnt error

[prime kelp]

how can i get the results from the excecute?

[austere portal]

using fetchall or fetchone

[prime kelp]

i just wanna fetch the top 10

so fetchall?

maybe?

[austere portal]

Remove the :

[austere portal]

Yes, you need to use fetchall and limit it to top 10

[prime kelp]

ok so fetchall(limit=10)?

ot

or?

[austere portal]

No

You need to limit it in the sql query

[austere portal]

ORDER BY xp ASC LIMIT 10

[prime kelp]

yeah i did that

await cursor.execute("SELECT user_id, xp, level FROM users WHERE guild_id = {} ORDER BY xp ASC LIMIT 10".format(ctx.guild.id))

[austere portal]

No, dont use .format

await cursor.execute("SELECT user_id, xp, level FROM users WHERE guild_id = ? ORDER BY xp ASC LIMIT 10", (ctx.guild.id,))

and data = await cursor.fetchall()

[austere portal]

here ? is a placeholder, it will be replaced by the values you pass in as the parameters argument

[prime kelp]

ok ill use this, ty

user_id, guild_id, xp, level, name = result

ValueError: too many values to unpack (expected 4)

only allowd 4 or smthing?

[austere portal]

the tuple only has 3 items

[jaunty galleon]

4

[prime kelp]

it has 5

[austere portal]

No

[austere portal]

SELECT user_id, xp, level FROM users

I guess you meant SELECT * ...

[austere portal]

Yeah, but you are only selecting 3 columns

[jaunty galleon]

!e

a, b, c, d, f = (1, 2, 3, 4)```

[delicate field]

@jaunty galleon :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | ValueError: not enough values to unpack (expected 5, got 4)

[austere portal]

print result and see

[prime kelp]

im doing await cursor.execute('Select * from users where user_id = ?', (member.id,))

[austere portal]

hmm, print the result and see

[prime kelp]

ok

[austere portal]

Yeah, something like that is happening here

[prime kelp]

so

its 5

but one is nonetype

so it counts it as 4

mhmm

why is it none

[austere portal]

how are you inserting the values

you can make columns not nullable in sql

[prime kelp]

this is the find_or_insert function

[austere portal]

column_name DATA_TYPE NOT NULL

[prime kelp]

cursor = await self.db.cursor()
        await cursor.execute('Select * from users where user_id = ?', (member.id,))
        result = await cursor.fetchone()
        if result is None:
            result = (member.id, member.guild.id, 0, 0, member.display_name)
            await cursor.execute('Insert into users values(?, ?, ?, ?, ?)', result)
            await self.db.commit()

        return result```

excuse the indenting

is it something to do with the fact they are all int except the str?

oh wait

the names are null

hm

[austere portal]

member.display_name it returning None ig

[prime kelp]

now its givin this

its not none therefore

[compact anvil]

can someone help me ?

[jaunty galleon]

Don't ping him

[austere portal]

there are 5 values

one, two, three, four, five = result

[prime kelp]

its fine i gt it working

thank you for all your help

[austere portal]

👍

[prime kelp]

i just started sqlite after getting bullied for using json 😢

[lone island]

how does fetchrow() return a row from database ? is the row returned in a list ?

[prime kelp]

i cant help it!! 😢

[compact anvil]

insert = ("""
INSERT INTO Books(BookID ,BookName , Writer , Title , Subject , Category , Pages , Translator , YOP , Publisher)
VALUES (DEFAULT , %s , %s , %s , %s , %s , %s , %s , %s , %s)
;""")
cur.execute(insert,((name),(writer),(title),(subject),(category),(pages) , (translator) , (YOP) , (publisher)))
ms.showinfo('Done','Book added successfully')
db.commit()
db.close

[compact anvil]

guys i use this code for insert

[austere portal]

it returns a tuple

[austere portal]

or if you are using asyncpg it returns a Record object

[compact anvil]

but all of the inserted values is empty!

[lone island]

like i wanna return a value of a row from a database with fetchrow

[austere portal]

cursor.execute(query)
row = cursor.fetchrow()```

[lone island]

yea but what i dont know is how does it return it in row, if im not selecting a specific column of the row

[austere portal]

Depends on the query

It returns the first

[lone island]

this is the query output, i need the guildrank value, and cant use select at this situation

[austere portal]

SELECT * FROM table_name WHERE Id=1 here you are getting single row with the id of one

[austere portal]

Show your code

And why cant you use select?

[lone island]

SELECT
    *
FROM (
  SELECT
    t.g_id,
    t.g_user,
    t.top,
    RANK() OVER (PARTITION BY t.g_id
                 ORDER BY t.top DESC) AS guildrank
  FROM (
    SELECT
      g_id,
      g_user,
      MAX(g_user_xp) AS top
    FROM xpsystem
    WHERE g_id = $1
    GROUP BY g_id, g_user
  ) AS t
) AS rt
WHERE
    rt.g_user = $2```

[lone island]

lets say idk how to lol

[austere portal]

you are using select right there...

[lone island]

i need to use another select to get guildrank only

[compact anvil]

Can you help me ? 🦥

[austere portal]

Sure

[compact anvil]

why my inserted values in database is empty ?

[austere portal]

did you re run the query after inserting the values?

[lone island]

can u write it the way ur thinking of

[compact anvil]

yes

insert = ("""
INSERT INTO Books(BookID ,BookName , Writer , Title , Subject , Category , Pages , Translator , YOP , Publisher)
VALUES (DEFAULT , %s , %s , %s , %s , %s , %s , %s , %s , %s)
;""")
cur.execute(insert,((name),(writer),(title),(subject),(category),(pages) , (translator) , (YOP) , (publisher)))
ms.showinfo('Done','Book added successfully')
db.commit()
db.close

[compact anvil]

there is any problem ?

[austere portal]

What do you want to do exactly?

[lone island]

the query returns a row, i need an outpot of guildrank only

[austere portal]

SELECT guild_rank instead of SELECT *

[lone island]

how i didnt think of that 🤦‍♂️

ty

[austere portal]

hmm, I don't see any problem in it

[austere portal]

The indentation looks wrong

[prime kelp]

is it

'Select * from users where user_id = ?, and guild_id= ?' or
'Select * from users where user_id = ? and where guild_id = ?'