databases

[burnt turret]

that'll work

[fading breach]

if get = 0?

[burnt turret]

notice how it says the object is NoneType.

meaning that the dictionary is None

so your if statement should be checking that

[fading breach]

but it equals 1

[burnt turret]

huh

then you should be checking that

either way, that if statement is incorrect

a single = is assignment

== is equality checking

[torn sphinx]

It isn't adding anything.

[torn sphinx]

mydb = myclient["BritishArmy-Moderation"]
                mycol = mydb["Warnings"]

                reason = Functions.joinSplit(spl[2:])
                def GetRank(message):
                    if HasTheRole(785112522499555369,message) == True: #admin
                        return "Chief Mod"
                    elif HasTheRole(793844239506341909,message) == True:
                        return "Administrator"
                    elif HasTheRole(785551694670069820,message) == True:
                            return "Moderator"
                high = mycol.find_one(sort=[("WarnID", -1)])
                wid = int(high["WarnID"])
                mydict = {"WarnID":str(wid + 1),"WarnDate":today.strftime("%d/%m/%Y"),"WarnedName":message.mentions[0].name,"WarnedID":str(message.mentions[0].id),"ModeratorName":message.author.name,"ModeratorID":str(message.author.id),"ModeratorRank":GetRank(message),"Reason":reason,"Test":False}
                mycol.insert_one(mydict)```

wid = int(high["WarnID"])

TypeError: 'NoneType' object is not subscriptable```

[torn sphinx]

error just mean that high has nothing in it. so when you try to get value you try to do something on nothing, which gives error

Why is nothing in it?

Cuz

In the database

there is.

because the mycol.find_one(sort=[("WarnID", -1)]) returned none, probably because its incorrect way to use it or the value is not there

anyways i have question

I read EAV model is not so good to use. Is it always like this?

[shell kiln]

how many free clusters can i get using mongodb?

and what exaclty is a "cluster"

[wheat path]

What is a "Database Notification"? I'm making a discord bot for someone and they wanted me to integrate it with some kind of db notification, but when I search it up on google I get no answers.

[torn sphinx]

Go, Postgres!!!

[shell kiln]

for pymongo when i query for something but nothing matches

what is returend

like what would mydoc have in it

if there was nothing in it?

and is mydoc a list then

?

[craggy arrow]

Ey, so I wrote:
https://github.com/jimcarreer/dinao#basic-example

And wondering if anyone finds it actually useful (it lets you bind parameterized SQL queries directly to a python function).

[shell kiln]

i'm using pymongo to do this

but how do i get it to only return the 500 value

this is my code rn

also how do you query in pymongo to check if soemthing exists?

[shell kiln]

i just used a janky solution of creating a substring haha

[lavish anchor]

looooooooooooooooool

classic

[shell kiln]

hey

i get through leetcode problems with that kinda of tomfoolery

it is the best

[lavish anchor]

i feel it

swag it out my friend

[maiden light]

If it is None then it means the record was not found.

[torn sphinx]

Try x.values()

Idk

 elif msg.startswith('.kicking'):
        await message.delete()
        if HasTheRole(793844239506341909,message) == True or HasTheRole(785112522499555369,message) == True:
            con = message.content.split()
            reason = Functions.joinSplit(con[2:])
            try:
                them = message.mentions[0]
                guild = message.guild
                myclient = pymongo.MongoClient("no leak")
                mydb = myclient["Moderation"]
                mycol = mydb["Kicks"]
                userobj = guild.get_member(them.id)
                await Functions.SendEmbedFail("Kicked",message,"You were kicked in the British Army for " + reason,message.mentions[0])
                await userobj.kick(reason=reason)
                loeh = await Functions.SendEmbedSuccess("Kicked",message,"<@" + str(message.mentions[0].id) + "> has been kicked for " + reason + " by " + "<@" + str(message.author.id) + ">",message.channel)
                await asyncio.sleep(8)
                await loeh.delete()
                high = mycol.find_one(sort=[("KickID", -1)])
                wid = int(high["KickID"])
                mydict = {"KickID":str(wid + 1),"KickDate":today.strftime("%d/%m/%Y"),"KickedName":message.mentions[0].name,"KickedID":str(message.mentions[0].id),"ModeratorName":message.author.name,"ModeratorID":str(message.author.id),"ModeratorRank":GetRank(message),"Reason":"**Kick - **" + reason,"Test":False}
                mycol.insert_one(mydict)
            except:
                awaiterror = await Functions.SendEmbedFail("Error",message,"You need to supply an Mention.\n\n``Example Usage:`` !kick @hidden flax Being mean.",message.channel)
                await asyncio.sleep(5)
                await awaiterror.delete()```

It's not adding anything to the database.

[torn sphinx]

Code:```py
@commands.Cog.listener()
async def on_message_delete(self, message):

    cluster = MongoClient('Mongo URL')
    collection = cluster.DataBase_1.settings

    for x in collection.find({"_id":message.guild.id}):
        logid = x["log"]
        print(logid)
        guild = get(self.bot.guilds, id=message.guild.id)
        log = get(guild.text_channels, id=logid)

        if message.content == "":
            return

        else:
            print(message.content)
            embed = discord.Embed(timestamp=message.created_at, description=f'**Message Author:** \n<@!{message.author.id}>({message.author.id}) \n\n**Message Channel:**\n<#{message.channel.id}> \n\n**Message Content:**\n`{message.content}`', color=242424)
            embed.set_author(name=f"Message Deleted", icon_url=message.author.avatar_url)
            embed.set_footer(text='Numix', icon_url=self.config.logo)
            await log.send(embed=embed)```

MongoDB:

Hello

I am getting error with something like foreign key but idk why?

Currently i have 4 products in database. I get the error on insert, and the insert it works with 2 of the products, but not the other 2.

This is error:
#1452 - Cannot add or update a child row: a foreign key constraint fails (`caillouxxx`.`order_items`, CONSTRAINT `order_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL)

Insert statement is: ```
insert into order_items (name, quantity, total, discount, order_id, product_id) values ('Apple iPhone X 64GB', 1, 739.99, 0, 51, 4);

[proven arrow]

The foreign key you are trying to associate with your order item doesn't exist, is what the error is saying.

And you are saying it does, (although it probably doesn't), so can you show your tables or explain how the relations exist between the different tables.

[torn sphinx]

Right so
Products -> SubProducts is 1-M
Orders -> OrderItems is 1-M

[proven arrow]

And can you show how you created the table order items?

If you no longer have the query you can run SHOW CREATE TABLE order_items to get the statement.

My guess is you have linked orderitems to the wrong product table, since you say it works for 2.

[torn sphinx]

@proven arrow Thanks you were right 😅
it was because wrong table was referenced,

[rancid badger]

guys i have a problem with my sql

    def CheckCustomerExistence(self):
        self.cursor.execute(f"SELECT phone_no FROM customers WHERE phone_no='{int(self.SenderDetails[0])}'; ")
        print(cursor.fetchall())

error

psycopg2.errors.SyntaxError: syntax error at or near "'1'"
LINE 1: SELECT phone_no FROM customers WHERE phone_no='1'; 
                                                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

@proven arrow hope u can help me!

[torn sphinx]

How do I update in MongoDB?

[maiden light]

@torn sphinx Oh no, don't do that, do cluster = ...
and then bot/client.db = cluster.db_name in your main file, you can use bot/client.db.collection_name anywhere.

[torn sphinx]

Code:```py
@commands.command(alisases=["set-logs", "audit-log"])
async def log(self, ctx, log: discord.TextChannel):
collection = self.db1.DataBase_1.settings

    update = {"_id": int(ctx.guild.id), "log": int(log.id)}

    collection.update_one(update)```

[proven arrow]

@rancid badger The query is fine. Most likely errror is because your single quotes might be some different character set.
You should use a parameterised query. 1) Because they are safer and prevent sql injection, and 2) It avoid errors like this as it will handle conversion and binding for you.
For example, the below is how you can do it.

self.cursor.execute("SELECT phone_no FROM customers WHERE phone_no=%s", (int(self.SenderDetails[0]),))

[maiden light]

https://www.w3schools.com/python/python_mongodb_update.asp

Btw use motor to avoid blocking @torn sphinx

[torn sphinx]

wdym "motor"?

[maiden light]

@torn sphinx collection.update_one({"_id": ctx.guild.id}, {"$set": {"log": log.id}})

[torn sphinx]

ok?

[maiden light]

Motor: Asynchronous Python driver for MongoDB

https://motor.readthedocs.io/en/stable/

It is similar to pymongo, you just have to put await before operations.

[torn sphinx]

why people use mongo for discord bot?

[maiden light]

Not everyone, depends on your choice

It's like json so you can guess why

[torn sphinx]

it doesnt even make sense to use it because most people have relational data

[maiden light]

I know

[torn sphinx]

and then they have difficulty in trying to use mongo and get support for it

[maiden light]

But Mongo isn't "bad" either.

Actually, json is as simple

That is your genuine difficulty as to when someone didn't read the docs.

[torn sphinx]

hmmm but also support is generally less for like mongo

[maiden light]

It's intuitive anyway, as it is python dicts.

[shadow patrol]

hi
guys im learning django and reached the pagination part, and now im trying to fetch data from a json file and then put it in the website, but what happens is that only one item gets showed in the website and then i get this error:
return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: FOREIGN KEY constraint failed

[proven arrow]

Does your foreign key exist in the parent table?

[shadow patrol]

is there any way to know?

[proven arrow]

Just check the parent table to see if the foreign key you were trying to insert exists.

[shadow patrol]

i sent you a DM

[autumn mountain]

Hi, can someone please help me? I just need some help to get this working: ```
inputName = input("Please input your name: ")

sql = "SELECT * FROM accounts WHERE name = inputName"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
print(x)``` But when i try using inputName in the sql variable it wont work! Please help!

[proven arrow]

Your query is a harcoded string.

[fading breach]

@maiden light it equals 0

[autumn mountain]

and that means

or how do i fix it

[proven arrow]

It means the input value is never passed to it. The query string you have stays like how you coded it.

[autumn mountain]

.

[proven arrow]

Which database driver are you using?

[autumn mountain]

all my code

I use remotemysql.com

[proven arrow]

So you would want to use a prepared statement for this.
You can do it like this,

mycursor.execute("select * from accounts where name = %s", (inputName,))```

[autumn mountain]

i will try

[proven arrow]

Basically this way the value inputname is substituted with %s

[autumn mountain]

It worked!

thanks!!!

you just saved my day!

[shadow patrol]

guys help please

[maiden light]

It is not supposed to be None? If it is None it doesn't exist.

[fading breach]

actually, it equals one

@maiden light

oh wait

my ID isnt in there

[maiden light]

See

It doesn't exist

[fading breach]

`wait

it still doesnt work @maiden light

my ID is: 556605996730941480

[brazen charm]

what are you actually running

and what are you trying todo exactly?

[fading breach]

@maiden light everyones ID is in there, and level is set to 1

[fading breach]

me?

[brazen charm]

yes you

[fading breach]

im making a discord bot

economy and level system

[brazen charm]

i mean with the db

not in general

[fading breach]

storing the amount of messages sent in the levels db

[brazen charm]

👏 what 👏 is 👏 your 👏 code

[fading breach]

find = await db["Scope Level"].find_one({"id": message.author.id})
get = find["level"]

[brazen charm]

okay

well your id is a string

[fading breach]

get = find["level"]
TypeError: 'NoneType' object is not subscriptable

[brazen charm]

and you're telling it to find a int

[fading breach]

oh, ok

find = await db["Scope Level"].find_one({"id": str(message.author.id)})
get = find["level"]

is that better @brazen charm

[brazen charm]

tias

[fading breach]

now I get this error ```
TypeError: update_one() missing 1 required positional argument: 'update'

[maiden light]

Oh! Why didn't I notice ://

[maiden light]

Show your code where it uses update_one

It failed to update

[fading breach]

record = {"id": str(user), "level": get+1}
await db["Scope Level"].update_one(record)

[maiden light]

Correct way is :
await db["Scope Level"].update_one({"id": str(user)}, {"$inc": {"get": 1}})
@fading breach

$inc means increase

It will add 1 to get

[brazen charm]

https://www.w3schools.com/python/python_mongodb_getstarted.asp low key

[fading breach]

ok

[brazen charm]

Would be a really good idea

to go through w3school's mongo tutorial

[fading breach]

im using motor though

[brazen charm]

doesnt matter

they follow the same query logic and positioning

[maiden light]

Yes it would be, I already said what is necessary above in this channel

[fading breach]

ok

[brazen charm]

the only difference is you await the query

[maiden light]

Just read everything

[fading breach]

btw user = str(message.author.id)

[maiden light]

Oh, then only user @fading breach

It's already str, you can directly do "id": user

[fading breach]

I have

[maiden light]

Btw there are other methods, like $set which sets it to what you give, read docs :/

[fading breach]

ok

btw, it works!

[maiden light]

Nice

[lethal depot]

hi so im using docker-compose to create a reboot feature where i can reboot my file remotely,

    bot.pg_con = await asyncpg.create_pool(host='54.212.127.203', database='warns', user='admin', password='password')```
i added host to my postgres conn pool and made it the ip of my vps, then, when i run the file using ``sudo docker-compose up``
i get this error: https://mystb.in/PortraitConflictsJuly.error
anyone know why?
https://cdn.discordapp.com/attachments/775507084288065557/795332697651150898/Screen_Shot_2021-01-03_at_8.48.01_AM.png

[proven arrow]

@lethal depot Is your code is running from your vps?

[lethal depot]

my db is on my vps

[proven arrow]

And your code?

[lethal depot]

same

but its running in a docker-compose container

[proven arrow]

So then you can use localhost as the address

[lethal depot]

but the localhost would be the container

and the db isnt in the container

[proven arrow]

I see, well am unsure how docker works since i dont use it. Other thing you could do is open the database to listen on that IP address

Similar to how you do it for remote connection

[lethal depot]

listen_addresses?

[proven arrow]

Yes i think thats what postgres has it under

[torn sphinx]

this code doesnt do anything can anyone help?

[burnt turret]

what do you expect it to do?

[torn sphinx]

no i am not misslepping the name

i am creating the collection

[burnt turret]

try inserting some data

i think it will show up only once you insert some data into it

[torn sphinx]

i get this

not that

but ill try

to insert data

[burnt turret]

yeah, i think mongo only actually "creates" the collection once you put some data in it

[torn sphinx]

it did...

thanks dude

thank you so much...

i wouldnt have bothered you but I never use mongodb on python 😄

[burnt turret]

😄 all good, afaik mongo does this for both databases and collections - only creates it once you put some data in it

[torn sphinx]

Using aiomysql what is the proper way to prepare statements to avoid sql injection attacks? Please tag on reply.

[fading breach]

@maiden light how can I retrieve the top levels from mongo

for example this is my db:

I want to retrieve the top 5 levels and IDs and return them back as a string for a leaderboard command

[cedar violet]

Security considerations for password storage are generally more holistic than where they are stored.

[native portal]

heroku config:set DB_URL=YOUR_OWN_DB_URI
heroku config:set TOKEN_SECRET=YOUR_OWN_SECRET

Can someone please help me find these variables on my heroku client?

[jovial notch]

@pure cypress or you bro, i seen that yall know about systemmd.

[maiden light]

You can loop through all data like :
async for find in bot.db["Collection name"].find():

do something with data, like append it to a list, and use list.sort with reverse and a key to get top 5

I forgot a simpler way to do it

[cold ocean]

it returns true even when the username and password is not in the database. how do i fix this?

[proven arrow]

That's because even if it doesn't exist the query still returns a result of 0. So your if statement will.pass because there was a result there.

Also you don't need to be doing exists for that query. An easier way is to do, SELECT COUNT(*) FROM users WHERE name=? AND password=? LIMIT 1),
This will return a value of 1 or 0, of how many rows were matched.

[cold ocean]

i will try it later, thank you

[cold ocean]

it returns an object, not a number. how do i use that?

[proven arrow]

You can do if cur.fetchone()[0] > 0 : // match found

[torn sphinx]

Anyone mongoDB user ?

@proven arrow are you?

[tepid cradle]

@torn sphinx go ahead and ask your question. Anyone familiar with it can respond

[torn sphinx]

nvm i solved

🙂

[fading breach]

@maiden light TypeError: 'AsyncIOMotorCursor' object is not iterable

@commands.command()
    async def leaderboard(self, ctx):
        for find in db["Kingdoms-Level"].find():
            return find

[delicate field]

Hey @torn sphinx!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[torn sphinx]

Hi, I really need some help please! I am using requests to do an api call to grab data from 2010-2020 separately to enter 10 separate entries into a db for each year. When I first ran the code only 1 entry from 2010 was entered into the db instead of 1 for each year. I ran the code again then my custom error msg 'print the root error message' printed out 10 times. I have attached the file below. I don't know what I am doing wrong and a solution would be a lifesaver!

https://paste.pythondiscord.com/satoxesewi.py

[tropic trellis]

on getting a value through tkinter i just get an emptystring for everything

can somebody plz help?

🙏

[buoyant marsh]

Hi! If I use a aiomysql connection pool ( PyMySQL ), my app will just get stuck after a while when requesting a connection. Do you know how to fix this?

[maiden light]

async for not for

[fading breach]

ok

[torn sphinx]

Anyone have examples for a database querying website with a python backend

Actually that would be kinda worse than just a simpler PHP backend script

[grim cliff]

Hi, I am trying to make a list of the best scores in my DB (mongodb). I need the names and the scores.

I don't know what to do :(

[vestal fox]

database

[tender shadow]

pls help me, I cant get to install mysqlclient, Ive been trying to solve for this weeks now, and I just dont get why it cant get installed. My version of python is 3.8 and I have the latest version of pip

[rotund abyss]

Hello guys I've just opened a new github discussions about System Design
Feel free to join and contribute to this discussion by sharing thoughts,tips and other stuffs

The aim of these discussions is to fit the gaps around System Design backgrounds, misunderstandings

Please help contribute, giving your thoughts and feedback I'll be waiting for you right there

https://github.com/HipsterSantos/system-design-primer/discussions/1

[naive umbra]

pickleDB good for production ?

[torn sphinx]

I'm planning on a project that will have thousands of DB entries a minute. I understand that that many DB commits in sqlite is a bad idea though due to the speed limitations.

How should I restrict it? A timer loop every 60 seconds? or a counter to save every hundred entries?

[tepid cradle]

@torn sphinx You should not restrict it, you should use a different Db. SQLite is not meant for that kind of operations. I would recommend using postgresql db for a write-heavy application. The database will handle the operations properly

[torn sphinx]

Should I though... because SQLite can handle many thousands of entries a second... It just can't write to disk that often...

also I'm still working on bach degree...

lol

I don't really want to have to deal with setting up a server

and wanted to be able to just upload easily.

"Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. " Hum this sounds like I could just drop it into a ram-disk... and get really good speed.

[radiant elbow]

If you don't care about persistence, you don't need a file at all. You can use sqlite3.connect(":memory:") to create a purely in-memory database

@torn sphinx

[torn sphinx]

I found that but I do need persistance and can't find a way to save that to a file

isolation_level in sqlite has an incremental and defered option. I think one of those may do what I want.

[radiant elbow]

If you need persistence, then a ramdisk seems like a bad idea. Everything will be lost if the machine crashes.

[torn sphinx]

yea that is not a good option.

[radiant elbow]

The usual performance tip is to make each SQL statement do as much work as possible. If you need to insert 1000 rows, do it in 1 transaction instead of 1000. Is you need to delete 1000 rows, try to do it in 1 statement, or at least 1 transaction. Etc.

And make sure that your data model is properly normalized, so that you aren't writing more than necessary.

[proven arrow]

You mention thousand of writes but are they all concurrent writes?

[torn sphinx]

Hello guys!

Anyone MongoDB dev?

I am a making a discord bot, and i want to delete a entry after x days

Anyone know how can i?

[proven arrow]

Because what you said before that sqlite can handle many per second, although true because writes don't take that long, however that doesn't change the fact that sqlite can only have a single writer at any given time. For concurrent writes in the many thousands a client server would always be better, unless you have a different approach on how you handle the writes.

[radiant elbow]

If it's only one server process that wants to write to the database in the first place, then adding concurrency into the mix isn't likely to speed things up much, I suspect

[torn sphinx]

Help me!

[torn sphinx]

yes all one connection

but I am using aiosqlite

[proven arrow]

What I meant was how many users/processes trying to write at once? You should have a read of here https://www.sqlite.org/whentouse.html
It should help you decide which you need, and they cover this sort of issue as well.

[proven arrow]

Key value pairs have syntax like, key : value. You have something else going on there.

[torn sphinx]

Just the one process

[proven arrow]

So all the writes are queued then.

[torn sphinx]

yes

their will be reads from another process though

[proven arrow]

Then sqlite should be fine, but just do what godlykeek said regarding the statements doing as little work. Or write in batches.

And reads are fine.

[torn sphinx]

batches were my original plan. How should I restrict it? A timer loop every 60 seconds? or a counter to save every hundred entries?

[torn sphinx]

I cant reduce the inserts

well I could queue them elsewhere... but that seems slower

every 10 should do for now...

[proven arrow]

@torn sphinx batch insert would just be temporarily storing that data to be inserted somewhere for example in memory, and then having something like a task/loop or whatever is appropriate in your application to insert that data. And when you insert in batch you should insert using executemany, which would be more efficient and quicker than a bunch of executes.

[torn sphinx]

I think I need to make a test benchmark program...

I will probably have to go with an insert many though. Thank you

[still igloo]

im getting the following error while trying to insert new records to a table in SQL server-

Invalid column name 'value_1'.```
code to create table-
```create table "table_name"
        ("col_1" varchar(256) not null,
         "col_2" varchar(16) not null,
         "a_date" datetime not null,
         primary key("col1"));```
code to insert new record-
```insert into table_name(col_1, col_2, a_date)
      values("value_1", "value_2", getdate());```
can anyone let me know what i am doing wrong

[torn sphinx]

@proven arrow How do I make this work?

[brazen charm]

Absolutely fucking not, NEVER us pickle for production like that

Not JSON, Not Pickle not Shelve etc...

pick a actual DB for that

[burnt turret]

the $ operators have to be passed as strings in pymongo/motor
"$addToSet"

[tender shadow]

#databases message pls help...

[silent frigate]

Anyone knows how to completed remove the sql pluggable database without dropping every single table?

[torn sphinx]

could be administrative right issue?

run cmd as an admin

then repeat, i know very silly recommendation, but sometimes people forget that

[long zealot]

How to find database latency in pymongo?

Please ping me for the answer thnx

[torn sphinx]

not strictly about python, but im looking for a database thats hosted in the cloud for an application im building. Whats the best database to use?

hoping its cheap too ¯_(ツ)_/¯

[craggy arrow]

The cheapest thing that has the features you need

[torn sphinx]

yeah

[torn sphinx]

best database and free

[torn sphinx]

How?

[torn sphinx]

hey guys did sqlite3 do something with db format?
sqlite3.DatabaseError: file is not a database
no problem when create it with sqlite or sql format

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

documentation uses db format

[grim cliff]

Thanks

[vestal fox]

cool

[tender shadow]

Ok thanks man

[torn sphinx]

did it actually work?

[torn sphinx]

    stats = await levelling.find_one({"guild": message.guild.id})
            ^
SyntaxError: 'await' outside async function
``` Anyone what to do?

nvm i solved

[still summit]

Anyone know PostgreSQL?

I am getting an error:

RuntimeError: column "purchases.company_name" must appear in the GROUP BY clause or be used in an aggregate function

On line:

db.execute("SELECT SUM(shares), ticker, company_name FROM purchases WHERE id = ? GROUP BY ticker HAVING SUM(shares) > 0;", (session["user_id"]))

It works in SQLite though

[torn sphinx]

rankings = await levelling.find().sort("xp", -1)

it says await cannot be used 😦

and also

            rankings = await levelling.find().sort("xp", -1)
            i = 1
            embed = discord.Embed(title='Rankings', color=0xff0000)
            for x in rankings:
                try:
                    temp = ctx.guild.get_member(x["id"])
                    tempxp = x["xp"]
                    embed.add_field(
                        name=f"{i} : {temp.name}",
                        value=f"Total Xp: {tempxp}",
                        inline=False)
                    i += 1

Not iterable 😦

[still summit]

@torn sphinx Do you know what to do about this error?

[radiant elbow]

You're selecting company_name, but you're aggregating multiple rows together using group by ticker. It won't let you do that because it doesn't know, for each set of rows with a given ticker, which one to take the company_name from.

[still summit]

But they are all the same

Would this change the results?

GROUP BY ticker, company_name

[radiant elbow]

It doesn't know that they'll all be the same

[still summit]

It got rid of the error, but I don't want it to mess up the statement

[radiant elbow]

Yes, that's a reasonable way to fix it

[still summit]

Thanks!

[radiant elbow]

That will give one result row power unique ticker/company name combination. And if they're one to one, that's still only one result row per ticker.

Alternatively, you could do:

SELECT SUM(shares), ticker, max(company_name) FROM purchases WHERE id = ? GROUP BY ticker HAVING SUM(shares) > 0;

[still summit]

Ok

And to get this value:

stock['SUM(shares)']

Do I have to change that?

It is throwing a key error

[radiant elbow]

That depends on the database, I'm not sure.

[still summit]

PostgreSQL

I don't know all of the technical details

[radiant elbow]

You could try select sum(shares) as num_shares

[still summit]

I am switching mine from SQLite to Postgresql

Could I also do

as sum(shares)?

So I don't have to change anything?

[radiant elbow]

That should let you get it with stock["num_shares"]

[torn sphinx]

Can anyone help me? I am switching from pymongo to motor

[radiant elbow]

I'm not sure. Never used postgresql, don't know if it minds parentheses in its column names or not.

[still summit]

Ok

[radiant elbow]

You could also just print(stock) to see what you got

It could be it was all lowercase instead of mixed case, or something.

[still summit]

Oh yeah, thanks!

[frail imp]

hello

anyone can tel that how to use sql databse with discord.py

[tepid cradle]

@frail imp That's a very broad question, you should either ask a more specific question, about what part you're stuck on, or look up a tutorial for it

[frail imp]

i am unable to

[tepid cradle]

Unable to what?

[frail imp]

unable to get a tutorial for dbs with discord.py

[tepid cradle]

Do you know how to use database with python?

Just with Python in general, not discord.py

[flat drift]

Guys

i need help

My MYsql losses Connection Automatically

am using Heroku's ClearDB mysql.

[maiden light]

return stops the whole function and the loop becomes pointless, append it to a list, use a key and list.sort to get top 5.

[torn sphinx]

Anyone mondoDB?

[maiden light]

@torn sphinx Pymongo is blocking, install Motor and read that

[maiden light]

Just read that ://

[torn sphinx]

kk

[crimson granite]

is here busy or can i ask a question

i have table with foreign key, when inserting into table with python how can i pass None or whatever to that column with foreign key?

def somequerymethod(val=None):
  query = f"""INSERT INTO sometable(somecolumn) VALUES ({val})"""
  cursor.execute(query)

but passing None or 0 doesn't work

[proven arrow]

If you want it to be null during insert then you can just leave out that column, because it will then get inserted as null automatically.

The reason it doesnt work for you in the code above is because you passed None as a string. It needs to be of the None type.

This would work,

query = "INSERT INTO sometable(somecolumn) VALUES (?)"
cursor.execute(query, (None,))

[crimson granite]

i passed 'NULL' as val and it worked

thanks

[torn sphinx]

Can GitHub not connect to databases or do I need to white list my GitHub op

IP

[fading breach]

Ok, thx

[timber sphinx]

hey guys, Does anyone know of a way to display MySQl data on Tkinter's window?

[crimson granite]

@timber sphinx connect to your database, query it and get your data

When i get requests i insert the data into my database, my question is:

lets say if i get 20 requests per second, would my synchronous database class have problem writing all the data without any delay or loss?

[timber sphinx]

I did, but it doesn't display anything (also i'd ask for your papers rn but it isn't the right time...)

[crimson granite]

i never tried tkinter but

first of all did yu check if your query is working?

[timber sphinx]

The thing is i don't know if there is a way to display the entire table on tkinter.😅 . I tried a few queries but it kept throwing an error

[fading breach]

@maiden light I get this error py @commands.command() async def leaderboard(self, ctx): async for find in db["Kingdoms-Level"].find(): find.append() find.sort(reverse = True, limit = 5) ```
find.append()
AttributeError: 'dict' object has no attribute 'append'

[crimson granite]

you cant append to dictionary

at least not like that

so from what i understand

db["Kingdoms_level"].find() returns you a dictionary

in order to add key, value to a dictionary

you do

mydict["mykey"] = "myvalue"

.append method is for lists

[fading breach]

ok

@crimson granite am I supposed to set mykey and myvalue to something?

[crimson granite]

when you loop through db["kingodms-level"].find(), each iteration returns you a dictionary

so if you want to add new data in each iteration, then yes you have to set key and value

[fading breach]

what should I set it to?

1

[crimson granite]

i don't know what you are trying to do, you can set it to anything

[fading breach]

ok

[mental quiver]

I am considering switching to aiosqlite because of the fact that my application is async, and obviously that would be ideal. My question is, can there be multiple aiosqlite connections active and interacting with a db at the same time, or would that cause errors? Also, if I have one application w/ aiosqlite connections, but another application connected to the same db w/ normal sqlite module, would both applications interacting w/ the db at same time cause an issue? Please @ me when responding to my questions, and thank you.

[fading breach]

can I do myDict.sort()?

[crimson granite]

no

[fading breach]

what should I use then

[crimson granite]

you wouldn't need to sort it because you acces dictionary items with keys

you cant do mydict[0]

it would look for key 0

[fading breach]

can I convert a dictionary into a list?

[crimson granite]

point of dictionary is, its with key and value

if i were you, i would debug and see what db["Kingdoms-Level"].find() returns

[fading breach]

now I have this ```py

@commands.command()
async def leaderboard(self, ctx):
    async for find in db["Kingdoms-Level"].find():
        myDict["1"] = "1"

[crimson granite]

myDict was an example 😆

[fading breach]

I know

decided to name it that anyway

[crimson granite]

what exactly are you trying to achieve here?

[maiden light]

people = []
async for ... :
people.append(find)

Then use a key, and sort people.

[fading breach]

sort all the levels from highest to lowest

but have a limit of only keeping 5

def key(d):
   leaderboard = []
   return d["levels"]
   

@commands.command()
    async def leaderboard(self, ctx):
        async for find in db["Kingdoms-Level"].find():
            leaderboard.append(find)

        leaderboard.sort(key=key, reverse=True)
            

[crimson granite]

yeah you can do that

[maiden light]

def key(d):
return d["balance"]
people.sort(key=key, reverse=True, ...)

You need a key :/

That gives it balance, or bank + balance.

[fading breach]

should I replace balance with levels

[maiden light]

I guess yeah

That sort line should be outside of loop

Why is leaderboard = [] randomly inside key, put it under the key function.

[fading breach]

should the function be inside or outside

[fading breach]

indents are weird

[maiden light]

Inside

[fading breach]

the discord snippet indents are weird

when I copy and paste

[maiden light]

Put the sort line after you have prepared leaderboard, under the loop

[crimson granite]

guys not to be ass but this is database channel, you would get better help in help channels

[fading breach]

@commands.command()
    async def leaderboard(self, ctx):
        def key(d):
           leaderboard = []
             return d["levels"]

        async for find in db["Kingdoms-Level"].find():
            leaderboard.append(find)

        leaderboard.sort(key=key, reverse=True)
        leaderboard = leaderboard[:5]
        print(leaderboard)

[maiden light]

@mental quiver Indents mess up in discord

[fading breach]

yeah

how is the code now?

[mental quiver]

Huh, I have not had that happen to me. Strange.

[maiden light]

Fine, but the key function should be inside the command.

[fading breach]

inside the async

or before it

[maiden light]

It doesn't matter

[fading breach]

ok

how do I set a limit of 5

limit=5?

[maiden light]

leaderboard = leaderboard[:5] I guess, after you have sorted

[fading breach]

is the code fine now

[real timber]

Does limit stop at first match?

Or just limit the overall results to a particular shape

[maiden light]

👌

[fading breach]

ok

great

[mental quiver]

Anyone on #databases message rq? I think its a fairly simple set of questions, just had some issues finding clear answers online.

And if you are talking about LIMIT <amt> in sql cmds @real timber, it limits the results to the amt given.

[fading breach]

now I need to make it a discord.py embed

[maiden light]

But

[mental quiver]

discord.py docs.

[torn sphinx]

its easy

[real timber]

Cheers

[torn sphinx]

ill show u

[maiden light]

You again have randomly that leaderboard = [] line in key @fading breach

[fading breach]

ok

[torn sphinx]

https://cog-creators.github.io/discord-embed-sandbox/

this does it for you

[fading breach]

I already know ow to do it

[mental quiver]

ah ok then

[real timber]

I wanted to get the first match from a column but big query doesn't have TOP command, so I used

Select count(*) from table where col = value

It seems expensive though

[maiden light]

Put leaderboard = [] under and outside key @fading breach

Or before, doesn't matter

[fading breach]

ok

[mental quiver]

From what I know, this would get the amount of matches to the WHERE, rather than the actual results. Is that your intention?

[real timber]

Yes, then I can test if test if it's zero or not zero

But I only need logic, so first match is fine

[mental quiver]

got ya. then from what I know that looks good. and yeah I think if you add LIMIT 1 to the end of that, itll stop at first result.

[real timber]

Ok, I'm not sure what the behaviour of any( ) in python is referred to as (stop at first match?) But that's what I want

So if there's a column with 1 billion rows, it'll only check 1 row if the value is the first element

[maiden light]

@fading breach Btw, this is what I see

[fading breach]

ok

it printed it out successfully

[maiden light]

The @commands.command() and async def start from the same point, right?

[fading breach]

yes

right now im splitting all the IDs and levels up

[real timber]

I've got a suspicion limit will process all the data and then just return the first n, rather than stopping at first match. I'll check in bigquery in a bit though

[mental quiver]

Yeah just tested it, Limit doesnt work on counts. hmm

[real timber]

To be clear, I'm not interested in the count exactly, I'm interested in testing whether a value is in a column, it's just count provides me something I can test

A return value of 0 or 1 would be fine, rather than 0 or the total count (which I currently get)

[mental quiver]

right. What is the size of the table btw? also, I would recommend only selecting the column you are interested in as well to further increase speed, like SELECT col_name FROM table_name WHERE col_name = value.

[real timber]

Table atm is small, few million, but it'll grow

[mental quiver]

Yeah didnt find much online that is super helpful, though hopefully this could help: https://stackoverflow.com/questions/33889922/how-to-get-the-number-of-total-results-when-there-is-limit-in-query

Wish I could help more, but I am just not experienced enough in sql. GL.

[real timber]

cheers , i don't have a limit in the query, i'm just trying to break on first match

[crimson granite]

is this vulnerable to sql injection?

cursor.execute(f"SELECT * FROM SOMETABLE WHERE name={yourname}")

[torn sphinx]

is yourname an input()?

if yes then yes

use cursor.execute("SELECT * FROM sometable WHERE name = ?", (yourname,))

[crimson granite]

yourname is passed as argument into a method

ok thanks

if i have a Names table and names are set to Unique, when i try to insert new names into table and duplicate name exists, is it fine if i just use try/except and pass in exception

or should check if name exists then add?

[proven arrow]

Do you want something to happen in the except, other than just pass?

[crimson granite]

no just pass, because without try/except it will raise exception becaue of unique column

[proven arrow]

Right ok so you can do that or you can also have the database ignore the error if there is a unique constraint violation, and so you don't need to handle it in your code.

What database are you using?

[crimson granite]

mysl

mysql

[proven arrow]

Ok so MySQL has INSERT IGNORE

[crimson granite]

i see

thanks, i will check that

[dry plover]

hi can someone help me with some sqllite 3 code

sql = "UPDATE " + cat  + " SET Item = " + iteminp + ", Price = "+ priceinp +" WHERE Item = " + orgitem

this is giving the error "no column : iteminp"

[hallow brook]

you're missing something

[dry plover]

any idea what it is

sql = "UPDATE ? SET Item = ?, Price = ? WHERE Item = ?"
vals = (itemcat, ite, pricee, ManageMenu.item)
cur.execute(sql, vals)

ive also tried this

this gives me a different error

[hallow brook]

what is the error with that second version?

[dry plover]

near "?": syntax error

[hallow brook]

sql = "UPDATE ? SET Item = ?, Price = ? WHERE Item = ?"
vals = (itemcat, ite, pricee, ManageMenu.item)
cur.execute(sql, vals)

^ correct?

I'm not certain dynamically inserting the table name is supported syntax (UPDATE ?)

[dry plover]

still get the error

hmm

[hallow brook]

you may want to insert it using an f-string instead:

sql = f"UPDATE {itemcat} ..."

[dry plover]

wdym?

[hallow brook]

it's the same as doing 'UPDATE {} ...'.format(itemcat)

[dry plover]

sql = "UPDATE " + itemcat + " SET Item = ?, Price = ? WHERE Item = ?"

[hallow brook]

or 'UPDATE ' + itemcat

[dry plover]

does it have to be '

[hallow brook]

no

quotes are arbitrary in python

as long as you're consistent

[dry plover]

oh it works

cheers

Thanks

[frozen quail]

Hello

i have problem s SQLite3

when i try to create no sqlite3 db in folder other than my home

i got error unable to open database file

[noble fractal]

i need someone who can talk and help me

[hearty jungle]

Anyone used mongodb?
is it possible to find all "user_ids" in a collection then add the results to a list?

        for x in collection.find({}, {"_id": 0, "user_id": 1}):
            test_list = list(x.values())

            print(test_list)

tried this but get this :

[77413093111111111]
[1111111111111111111]

i want it to be
[77413093111111111, 1111111111111111111]

[shell ocean]

@hearty jungle create an empty list at the start and append

however...think you just want list(collection.find({}, {"_id": 0, "user_id": 1}))

[hearty jungle]

@shell ocean cheers.
im trying to get the list of ids.

        for x in collection.find({}, {"_id": 0, "user_id": 1}):
            print(x)
            test = x["user_id"]
        print(list(test))

test gives me the values of the user ids. However, when trying to put them in a list i get this:

TypeError: 'int' object is not iterable

---

i tried "list(collection.find({}, {"_id": 0, "user_id": 1}))"
but idk how i would just extract the numbers

[flint imp]

listname = []
for x in collection.find({"_id": 0}, {"user_id": 1}):
    listname.append(x)
await ctx.send(listname)```
@hearty jungle try this

[fluid totem]

can someone help with this:
https://stackoverflow.com/questions/65607835/when-fucntion-recieve-text-its-working-but-if-recieve-x-when-x-text-cant-ru

?

[foggy garnet]

    @bot.command()
    async def dbtest(ctx,user_id,reason=str):
        connection = sqlite3.connect("warns.db") 
        crsr = connection.cursor() 
        create_table = """CREATE TABLE warns (  
warnnum INTEGER PRIMARY KEY NOT NULL,  
user_id text NOT NULL,  
reason text NOT NULL
);"""

        add_values = (f'INSERT INTO warns (user_id,reason) VALUES({user_id} , {reason})')
        select_values_2 = """SELECT warnnum FROM warns"""
        crsr.execute(add_values)
        connection.commit()
        crsr.execute(select_values_2) 
        getting_id = crsr.fetchall()
        getting_id_2 = getting_id[-1]
        gotten_id = getting_id_2[0]
        connection.commit() 
        select_values = (f"""SELECT * FROM warns WHERE warnnum = {gotten_id};""")
        crsr.execute(select_values) 
        ans = crsr.fetchall()
        tuple_ans = ans[0]
        warnnum = tuple_ans[0]
        user_id = int(tuple_ans[1])
        reason = tuple_ans[2]
        connection.commit()
        connection.close()
        print(ans)
``` my code to connect to sql db in python
the error:

Ignoring exception in command dbtest:
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 69, in dbtest
    crsr.execute(add_values)
sqlite3.OperationalError: near "<": syntax error

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "<": syntax error

sqlite 3

ping whenever

[timber sphinx]

hey guys
i'm just wondering
is it possible to to use a variable to insert information into MySQL

[proven arrow]

What kind of variable? One that you define and then use in a query?

[timber sphinx]

yup

as in: [ just an example]
x=5
c1.execute( "INSERT INTO TABLE VALUES(",x,")")

[proven arrow]

You can do it from python using a prepared statement, or if you want to do it purely MySQL then you can have temporary user defined variables as explained here, https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

If you want to do it from python using prepared statement then you can do it as follows:

x = 5
cursor.execute("INSERT INTO table VALUES(%s)", (x,))

[timber sphinx]

Aight will try this out

gimme a few

[proven arrow]

The %s might be different depending on what library you use to connect to mysql but basically what it does is replaces the values you give as the second argument in order.

[timber sphinx]

I'm getting an error that says:
Python 'entry' cannot be converted to a MySQL type.

[proven arrow]

What data type is your value?

[timber sphinx]

There are 4 values
3 are varchars, and 1 is a Bigint

[red sparrow]

cursor.execute("INSERT INTO tables (yourTableHeaders)VALUES(%s)",(x))

it should lok like this

[proven arrow]

Then can you show the code, the issue is your passing an invalid type from python to mysql

[timber sphinx]

yeah i kinda missed the table headers

[proven arrow]

You only need column names if your inserting in a subset of columns

[timber sphinx]

i'll try it out and let you know if it works

[timber sphinx]

Yup still getting the same error

but my code is damn huge

its prolly 250 lines

[red sparrow]

thats not that huge

[proven arrow]

Well as I said your error is saying your trying to insert a entry type of object, and MySQL does not understand what that is.

[timber sphinx]

Well this is my first time (and most likely my last) using MySQL and Python for a school project😅

[proven arrow]

So without code it's not easy to help. Anyways I got to go for a bit so you can ask here for someone else.

[timber sphinx]

so is there any way to change it such that MySQL will understand it?

[proven arrow]

Give it the correct types

[red sparrow]

yeah show the code or the part where you have the variable and the query so we undrestand the picture

[timber sphinx]

ok
before i send it, I'm using Tkinter to input the values

[red sparrow]

ok

[timber sphinx]

def to_add():
nW1=Toplevel(top)
c1=mycon.cursor()
Np=Label(nW1,text='----------ADD THE DETAILS BELOW----------')
Np.grid(row=1,column=2)

global ans1
global ans2
global ans3
global ans4
det1 = Label(nW1,text='Enter the Products name')
det1.grid(row=3,column=1)
ans1=Entry(nW1,bd=5)
ans1.grid(row=3,column=3)
det2=Label(nW1,text='Enter the Product number')
det2.grid(row=4 ,column=1 )
ans2=Entry(nW1,bd=5)
ans2.grid(row= 4,column= 3)
det3=Label(nW1,text='Enter the stock')
det3.grid(row= 5,column=1 )
ans3=Entry(nW1,bd=5)
ans3.grid(row=5 ,column= 3)
det4=Label(nW1,text='Enter the price')
det4.grid(row=6 ,column=1 )
ans4=Entry(nW1,bd=5)
ans4.grid(row=6 ,column=3 )
pro=Button(nW1,text="Continue",command=add_products)
pro.grid(row=10,column=2)

def add_products():

c1.execute("insert into Products(Product_name,Product_no,Stock,Price) values(%s,%s,%s,%s",(ans1,ans2,ans3,ans4))
mycon.commit()
msg1=Label(nW1,text='Product has been succesfully added')
msg1.grid(row=7,column=2)
L=Button(nW1,text='Exit',command=close_tab)
L.grid(row=8,column=1)

[red sparrow]

try c1.execute("insert into Products(Product_name,Product_no,Stock,Price) values(%s,%s,%s,%s",(str(ans1),str(ans2),str(ans3),str(ans4)))

assuming your database types are all string

[timber sphinx]

not all of them are strings though...

[red sparrow]

str() for string

[timber sphinx]

but i'll do it for those which are

[red sparrow]

int() for numbers

and your missing parentheses after %s

[timber sphinx]

thanks!, will correct that as well

TypeError: int() argument must be a string, a bytes-like object or a number, not 'Entry'

this is what i get on correcting it 😦

when i make it a string it says incorrect integer value

so i guess i'll have to change the int value to a str

[red sparrow]

no dont change anything

try c1.execute("insert into Products(Product_name,Product_no,Stock,Price) values(%s,%s,%s,%s)",(ans1.get(),ans2.get(),ans3.get(),ans4.get()))

this should do it

you were passing the entry object, but you needed the value

thus entry.get()

returns the value

[timber sphinx]

aight

it'll work cos i've made ans1,ans2,ans3 and ans4 global

just give me a momenr

*moment

Yup it Works

[red sparrow]

👍

[timber sphinx]

THANK YOU SO MUCH MATE

I CAN FINALLY FINISH MY PROJECT!!!😄

@red sparrow one last thing
for updating and deleting records i can use the same thing right

That is
c1.execute(delete from Products where Product_no(%s)",(R1.get()))

Can anyone tell me if i can do this?

[uneven stream]

no spamming

just ask it once if ur message is the latest one

[timber sphinx]

sorry

[uneven stream]

anyways wym the same thing

[timber sphinx]

%s and <object>.get() thing

[timber sphinx]

this is what i mean....

[uneven stream]

why dont u just use ? or : format?

its been years since I used %s so thats a bit confusing

[timber sphinx]

oh alright

but 'll have to correct it in a lot of places, so not looking to change it for now.

[uneven stream]

c.execute("select * from table where col = ? and col2 = ? and col3 = ?", [firstPlaceholderValue, secondPlaceholderValue, thirdPlaceholderValue])`
```or 
```py
c.execute("select * from table where col = :col1 and col2 = :col2 and col3 = col:3", {col1: firstPlaceholderValue,  col2: secondPlaceholderValue, col3: thirdPlaceholderValue])

isnt this easier?

[timber sphinx]

tbh idk

looks the same to me

[uneven stream]

more readable

[timber sphinx]

anyways @red sparrow can you hmu on pc when you see this?
thanks mate!

[proven arrow]

Because different libraries have their own implementation of what characters are used for the placeholders. ? would not work here

[uneven stream]

all sqlite libraries have the same execute method!?!?

[proven arrow]

They are using Mysql

For sqlite that would work

[timber sphinx]

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

This is the error i got on trying the same thing for deleting a record

@proven arrow any idea to solve this issue

[proven arrow]

You need to provide the code, otherwise i will just repeat what your error already told you.

[torpid wigeon]

What would you guy recommend hosting a django website with?

guys*

[timber sphinx]

lol aight gimme a moment

`def to_remove():
global R1
nW2=Toplevel(top)
c1=mycon.cursor()
D1=Label(nW2,text="Enter the product number you want to remove completely")
D1.grid(row=1,column=1)
R1=Entry(nW2,bd=10)
R1.grid(row=1,column=3)
pro=Button(nW2,text="Continue",command=delete_products)
pro.grid(row=2,column=1)

def delete_products():
nW2=Toplevel(top)
c1.execute("DELETE from Products WHERE Product_no =%s",(R1.get()))
mycon.commit()
msg2=Label(nW2,text='Product has been removed')
msg2.grid(row=3,column=1)
L1=Button(nW2,text="exit",command=close_tab)
L1.grid(row=5,column=3)`

[proven arrow]

Your parameters need to be passed as a tuple/list.

c1.execute("DELETE from Products WHERE Product_no =%s",(R1.get(),)) would work.

[timber sphinx]

ah okay

yup it worked!

[proven arrow]

(1) is just an int, (1,) is a tuple where first value is an int. It needs something it can iterate through for it to work.

[timber sphinx]

oh ok

@proven arrow one last problem

.

def update_products():
nW3=Toplevel(top)
if R2.get()==N:
U1=Label(nW3,text="enter a new product name")
U1.grid(row=4,column=1)
U2=Entry(nW3,bd=15)
U2.grid(row=4,column=3)
c1.execute('Update Products SET Product_name=%s',(U2.get(),), 'WHERE Product_no =%s',(E1.get(),))
mycon.commit()
msg3=Label(nW3,text="Details updated")
msg3.grid(row=5,column=3)
L2=Button(nW3,text="exit",command=close_tab)
L2.grid(row=5,column=3)
elif R2.get()==S:
U3=Label(nW3,text="enter the new stock number available")
U3.grid(row=4,column=1)
U4=Entry(nW3,bd=15)
U4.grid(row=4,column=3)
c1.execute('Update Products SET Stock=%s',(U3.get(),), 'WHERE Product_no =%s'(E1.get(),))
mycon.commit()
msg5=Label(nW3,text="Details updated")
msg5.grid(row=5,column=3)
L3=Button(nW3,text="exit",command=close_tab)
L3.grid(row=5,column=3)
elif R2.get()==P:
U5=Label(nW3,text="enter the new price")
U5.grid(row=4,column=1)
U6=Entry(nW3,bd=15)
U6.grid(row=4,column=3)
c1.execute('Update Products SET Product_name=%s'(U2.get(),),' WHERE Product_no =%s',(E1.get(),))
mycon.commit()
msg4=Label(nW3,text="Details updated")
msg4.grid(row=5,column=3)
L4=Button(nW3,text="exit",command=close_tab)
L4.grid(row=5,column=3)
else:
U1=Label(top,text="Wrong option, restart to update")
U1.grid(row=4,column=1)
L2=Button(top,text="exit",command=close_tab)
L2.grid(row=5,column=3)

why am i getting this error?:
NameError: name 'P' is not defined

Didn't I define P correctly?

[proven arrow]

Thats a mess to read, but if it says you didnt define it then you will have not defined it anywhere.

And i dont see P defined anywhere

[timber sphinx]

oh damn sorry
i don't have to define P

my bad

[proven arrow]

And your update statements are wrong

The query is a whole, and not to be seperated like whatever you had going on there

[timber sphinx]

oh okay

but since there are 2 values, i thoguht separating the query would be a good idea

[proven arrow]

But its 1 query

[timber sphinx]

so is there any way to take 2 separate values and enter it as 1 query?

[proven arrow]

use multiple placeholders

[timber sphinx]

like this?

[proven arrow]

yes but you would use %s and not ?

[timber sphinx]

Alright

Thank you so much man!

[uneven stream]

why ping :reeee:

[timber sphinx]

sorry man, but had to quote you to understand something.

And thanks for typing that 😄

[hearty jungle]

still getting
[{'user_id': 774130931245645857}, {'user_id': 1111111111111111111}]
im just wanting the numbers.

[flint imp]

then irate through it

listname = []
for x in collection.find({"_id": 0}, {"user_id": 1}):
    listname.append(x["user_id"])
await ctx.send(listname)```

[golden warren]

Hello their, when i create table in my db file i have this line created.

My code.

[torn sphinx]

Hey I'm programming a Discord Bot (this is not related to discord) In which I want to recover the energy of all the players present in the database every ten minutes. My problem is I have to do a lot of requests :
-One to get all the ids in the database :
then in a for list with all the ids :
-Two to fetch the Energy value (current energy value of the player) and Stamina value (max energy of the player)
then one to modify the data if the energy is lower than the stamina

My question is : can I do this with only one request and will this be five time faster ?

It currently takes half a second with four users, I'm afraid it would take hours with a lot more users in the database

I have a table called "userdata"

it contains many fields, with the three important ones here being "id" (integer), "Energy" (integer) and "Stamina" (integer)

I'm using sqlite3 with python

Tl;DR : can I do with only one request "Increment the energy value for all the users in the database only if the energy value of this user is lower than the stamina value of this user" and if so, how ?

[proven arrow]

That is because you used auto increment for your primary key. So it would make that table, https://sqlite.org/autoinc.html

[proven arrow]

You can do this with a single UPDATE query. For ex, Update users set energy = energy + your_energy_amount where energy < stamina; where your_energy_amount is the value you want to increase it by

[golden warren]

Okay thx!

[torn sphinx]

Thanks a lot !

[slate terrace]

Im trying to setup a db for a small project im working on

For some reason it's trying to do '' in the query

and that returns an error

[proven arrow]

what error?

[proven arrow]

What version of mysql are you using?

Im not sure if thats a phymyadmin related, but generally it should replace the ' ' with NULL so it auto increments

[slate terrace]

idk that's what phpmyadmin did

now doing it manually

[proven arrow]

Manually should work, other reasons why it might not be working might be you have strict mode enabled.

[timber sphinx]

Guys if i had to make a login system using MySQL and Tkinter, what should i use to validate the username and password i enter to the ones on MySQL table?

[proven arrow]

@timber sphinx You can select the count(*) where the username and password equals the values you are testing against. This way the query will return you a value of 1 or 0 depending on if it exists or not.

Also you should index the username column for quick lookup and to avoid a full table scan if you haven't already done so.

[torn sphinx]

I need to query to find rows where one row has the same key as multiple other rows in another column. gah semi complex queries hurt my brain.

-----|-----
. 1 | p
. 2 | k
. 2 | n
. 3 | k
. 6 | l
. 2 | p

where querying would give 2=P,N

oh I don't think I can do this with one query

will have to loop query.

ok thats easy then

[paper saddle]

i need help

[proven arrow]

should K not be in that list?

If so then you can use group concat

[torn sphinx]

well no K was going to be the other search

basically I have a list of user ids and a list of channels

and I want to be able to check a channel and find all users who have posted in other channels

so K would be the search key

I didn't mention that though

what I will have to do is loop through members who have posted in the search channel and for each of those check what other channels

[proven arrow]

Then K does not belong in that column

[torn sphinx]

no it does. that is accurate sample input

simplified

I mean my list of messages has a channel column and every channel is there lol

Hello

hi

I have a question about structure

so basically should i put my database code inside my class that takes user input and gives user output or somewhere else

[proven arrow]

Well that is one way to do it, but not always the best.
Generally the database stuff lies separately in whats known as the data access layer. So it is separate from your controllers (the class that you say does your input/output with the user). This helps decouple your controllers from the database one step further.

This helps keep your controllers thin, and unaware of your database implementation, and also keeps things DRY.

[torn sphinx]

Oh

[proven arrow]

People generally adopt some kind of design pattern like the Repository pattern, but end of the day how you implement it will depend on your project that you are working on.

[torn sphinx]

oK, well this project is for an assignment so idk if it matters. But project is a web app.

[proven arrow]

Yeah such things are quite common in web apps. Whether it provides any advantage to you maybe you should check your assignment spec, to see what it grades you on.

[torn sphinx]

okie

i will try to find out but some people said to do this in some tutorials so i was confused a little

[copper echo]

asyncpg.exceptions._base.InterfaceError: unsupported authentication method requested by the server: 10 hey im getting problem with asyncpg

what should i check and what can be causing this?

i cant find anything on stackoverflow ;v

[crimson seal]

import sqlite3

conn = sqlite3.connect("customers.db")

c = conn.cursor()
many_customers = [
    ('Billy','Hanes','[e-mail]'),
    ('West','Brown','[e-mail]'),
    ('lax','Gaue','[e-mail]'),
]
c.executemany("INSERT INTO customers VALUES (?,?,?)",many_customers)
print(c.fetchall())
print("saved")
conn.commit()

conn.close()```
i ran this like 100 times and it wont add data to database

i deleted it and tried again so many times

it worked before like a week ago

[torn sphinx]

@worthy geyser

[worthy geyser]

This isn't a help channel. Type something in #help-burrito

[torn sphinx]

hello , I'm currently using mongodb as a database , and i noticed that it's quite slow when it it come to accessing it , is there any advice , or ways to make it faster , or even suggestion of other databases ??

[timber sphinx]

Have you tried MySQL?

[torn sphinx]

no , I haven't

[timber sphinx]

So something like

c1.execute("SELECT COUNT(*) FROM EMPLOYEE WHERE Name= %s and Password = %s",(username,password,))

does this work?

cos i tried it and got a programming error

[timber sphinx]

why not give it a try?

cos so far i've not experienced any slow processing from MySQL

[glad sleet]

i have a list structured like this:

dic=[
{
"obj1": value1,
"obj2": value2,
},```

how would i edit obj2 in mongodb (pymongo)? edit it to lets say `newvalue`

[glad sleet]

how would i edit this? https://media.discordapp.net/attachments/796532751385100318/796926869592866846/unknown.png
(with pymongo)

[red sparrow]

do it like that c1.execute("SELECT * FROM EMPLOYEE WHERE Name = %s and Password = %s",(username,password,))

[timber sphinx]

yup done that

don't worry mate i figured it out

thanks man! 😄

[brazen charm]

you would need to use {"$set": {"key": {"key": "thing"}}}

well actual

nvm its a array didnt notice that

you'd need to update the list yourself and then

{"$set": {"roles": [new array goes here n stuff]}}

you can if you really want do it via mongo queries but its a bit more complicated

[ashen flower]

you can do .update_one({"roles.roleid": role_id}, {"$set": {"roles.$.roleid": "newvalue"}}) @glad sleet

[haughty ravine]

Who can help me with structuring MongoDB? With structuring .json

For my project

So em

I want to have a key value “plan” in my json

If a plan is default, there is no need to place it for the user?

Like

id: 1
plan: default

Should be

id: 1

[torn sphinx]

Hi, I would like to create my own API of dog images that display the image of the dog, the type of dog and a short description. I have never done this before and would like some advice as where to start please. I want to do it all from scratch so I would like to first create a database of dog images, with the name and description? If so where would I start with making my own db? I have some PSQL experience. So if anyone knows of any good tutorials or have any tips then that would be awesome!

[glass gorge]

uhh

idk how to ask this

but i have a dns

that im trying to grant access to, in mysql

i know that I can use a wildcard to allow variations of that dns

but, for some reason its the first part of my dns that is change

for context im using aws ec2

ec2-22-33-44-5555.compute-1.amazonaws.com'

so like

ec2-22-33-44-6666. or like ec2-22-33-44-8888.

can i do something like

ec2-22-33-44-%.

i thought that the wild card had to take the place of all the values in between the .

so like 11.22.33.4444, i thought i could only do something like 11.% or 11.22.33.%

or can i do something like

11.2%.33.4444

?

[keen plaza]

hello

[earnest fog]

Hello, I'm looking to do a migration with SQLAlchemy and alembic. The migration is actually a data update to a child table (article_version in my sample). I need to update only the latest record for all the items in the articles table.

The table structure is like article and article_version

The idea is not to use a cursor ( I already made this solution but it was not well received )

Any suggestion is well received.
Thanks in advance.

[torn sphinx]

  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 69, in gang
    testing = str(existing["gang"])
TypeError: 'NoneType' object is not subscriptable

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

        testing = str(existing["gang"])
        print(testing)```

[haughty ravine]

should variables in MongoDB (.json database) be short? as i know data is stored in documents, not tables...

like it is preferable to write for example 'l' isntead of 'language'

[dreamy flint]

I have my own SQL server right? I connect to it using a mysql-connector.
I recently made the server.

I know how to Select, Insert, Update, Delete etc.
My question is, how do I make a file where I establish the connection to the server then I write a lot of functions in said file.

And I can import said file to other files to use these functions without needing to reconnect all the time?

[haughty ravine]

languagedata = collectionG.find_one({"_id":1})["language"]
error: KeyError: 'language'
why help
;/

[haughty ravine]

heeeeeeelp

heeeelp

[torn sphinx]

languagedata = collectionG.find({"_id": 1})
for lang in languagedata:
language = lang["language"]

[haughty ravine]

hm

[torn sphinx]

i think this is how it should work if you want to get a key from your collection

[haughty ravine]

what if it doesnt exist

[torn sphinx]

what you exactly trying to do ?

[haughty ravine]

em

get a value from data

[torn sphinx]

so like for example you want to get 'something' from language ?

i guess that should work

then just print (language) or whatever to display it

[haughty ravine]

oh ok 😄

Thx

btw i wanted to ask @torn sphinx ...

is it fine to store long variables in .json?

[torn sphinx]

sure

[haughty ravine]

like lang instead of language

will it take less space

[torn sphinx]

are you using a json file as a database ?

[haughty ravine]

MongoDB

Where database is structured like .json documents

[torn sphinx]

there is no difference actually

i mean the difference will take just little bit of space , and not that visual difference

[haughty ravine]

hm

ok

[torn sphinx]

mongo db got like 400mb free space or more lol , dw you will never run out of space
unless you got alot alot of collection

[haughty ravine]

TypeError: string indices must be integer

[haughty ravine]

hm

i still want to keep as less as i can

[torn sphinx]

don't even bother of that

[haughty ravine]

languagedata = x["language"]
TypeError: string indices must be integers

help XD

I am dumb to understand this

[torn sphinx]

did that just came out of the code i gave you ?

[haughty ravine]

yes

[proven arrow]

x is a string type

Your trying to index it by name which won't work

[haughty ravine]

so ["language"][x]

[proven arrow]

It's like doing this,

x = "hello"
print(x['language']) # gives error
print(x[0]) # prints letter h

As the error says you can only use integer to index a string

[haughty ravine]

            for x in data:
                languagedata = ["language"][x]```

nah, still error ;/

[proven arrow]

Your value is in x so what are you even doing there

[haughty ravine]

?

            for x in data:
                languagedata = x["language"]```
KeyError: 'language'

pls help

[torn sphinx]

do you even have the key language on your collection

[haughty ravine]

no

i want to make so if it doesnt have one

[torn sphinx]

then why you searching for it

[haughty ravine]

then it would do other thing

[torn sphinx]

oh ok

[stark violet]

@haughty ravine it would be very helpful if you can show us your database structure

[haughty ravine]

i removed find_one and the error changed

[haughty ravine]

_id:blablah

thats it lol

[stark violet]

so if you have no "language" key how can you find one?

[haughty ravine]

i want so if it doesnt have "language" key then it would create one

[stark violet]

then use upsert

for it

[haughty ravine]

?

[stark violet]

collectionG.update_one({"_id":1},{"$set":{"language":value}},upsert=True)

[haughty ravine]

but i want to get a language value

[torn sphinx]

if collection.count_document({"_id":1 , "language"}) ==0:
collection.insert_one({"_id":1,"language})

i guess this one mighy work , I don't remember most of it

[haughty ravine]

                collectionG.insert_one({"_id":1})
            data = collectionG.find_one({"_id":1})["language"]```

[stark violet]

but if the object doesn't have a language value how can it return one?
You need to check first do it like this:
collections.find_one({"_id":1},{"language":1,"_id":0})
if this return None it means you don't have any language key

[haughty ravine]

thats what i had already

[torn sphinx]

hmm does it work though?

[haughty ravine]

no

[torn sphinx]

you have to insert the language too

collectionG.insert_one({"_id":1 , "language"})

[haughty ravine]

invalid syntax

[stark violet]

sorry this will not return None instead it will return you an empty dictionary

[haughty ravine]

if collection.count_document({"_id":1 , "language"}) ==0: syntax error

[haughty ravine]

oh alr

[torn sphinx]

documents

and collectionG

[haughty ravine]

still syntax error

[torn sphinx]

;-;

can you show me your code

i have to see what's going on there

[stark violet]

if its empty you can check it by if conditions, and then use the upsert condition if you to update

[haughty ravine]

if collectionG.count_documents({"_id":ctx.guild.id, "language"}) == 0:
                collectionG.insert_one({"_id":ctx.guild.id})
            data = collectionG.find_one({"_id":ctx.guild.id})["language"]
            if languagedata == None:
                if language != 'en':
                    collectionG.insert_one({"_id":ctx.guild.id})
                    await ctx.send(f'The language has been set to {language}')
                    return
                languagedata = 'en'
            if language == languagedata:
                await ctx.send('You have the same current language')
                return
            elif language != languagedata:
                if language != 'en':
                    collectionG.update_one({"_id":ctx.guild.id}, {"$set":{"language":1}})
                elif language == 'en':
                    collectionG.update_one({"_id":ctx.guild.id}, {"$unset":{"language":1}})
                await ctx.send(f'The language has been updated to {language}')
                return

thats my discord bot

thats why it has ctx.guild.id etc

[stark violet]

where is your languagedata variable declared?

[haughty ravine]

?

[stark violet]

you are using languagedata but it seems you are storing the dictionary in data variable

[haughty ravine]

its data XD sorry'

still syntax error

at the exact same line

[stark violet]

yes it would give you the syntax error because it's returning a dictionary in which there is not "language" key but you are trying to access it

data = collectionG.find_one({"_id":ctx.guild.id})["language"]
ig this line is giving you the error?

[haughty ravine]

elif collectionG.count_documents({"_id":ctx.guild.id, "language"}) == 0:

this

[torn sphinx]

you have to pass a value to "language"

[stark violet]

same reason

[haughty ravine]

what value :/

[stark violet]

don't pass language there

[torn sphinx]

what language you are trying to find

[haughty ravine]

it can be anything

[stark violet]

only use _id

[haughty ravine]

an exception: KeyError: 'language'

[stark violet]

.

[haughty ravine]

languagedata = collectionG.find_one({"_id":ctx.guild.id})["language"]

but what should i use then

if it doesnt have language variable

how do i check it if it doesnt have one

[stark violet]

this one

and check if the dictionary is empty or not, if its empty that means you don't have a language key

[haughty ravine]

hm

language:1

bruh

it just pasted "1" as a key to "language" XD

[stark violet]

wdym

you know the basics of mongodb right?

[haughty ravine]

language:1

it looks like this

[haughty ravine]

hm yes i believe lol

ohhh

wait

i think i understood where my mistake is

[stark violet]

collections.find_one({"_id":1},{"language":1,"_id":0})

[haughty ravine]

nah

it still shows as "1"

in my case i want to show ru

as russian

or if it is en

then it would be just blank

to keep less data

hm

wait lmao

i found my mistake

lets try again..

[torn sphinx]

if collectionG.count_documents({"_id":ctx.guild.id, "language": "en"}) == 0:
                collectionG.insert_one({"_id":ctx.guild.id, "language":"en"})
            data = collectionG.find({"_id":ctx.guild.id})
for x in data:
          
                if x["language"] != 'en':
                    collectionG.insert_one({"_id":ctx.guild.id, "language": "en"})
                    await ctx.send(f'The language has been set to {language}')
                    return
                elif : x["language"]== 'en'
          
                await ctx.send('You have the same current language')
                return
            
      

this what i came out with xD

so basically what is does , when it find the language is not set to en
it update it to en , and then send the message
else if it's already set to en it says it's already en

[haughty ravine]

hm

[torn sphinx]

tell me what you trynna do with that code

[haughty ravine]

emm

like i trying to create a command which sets discord bot language

{'language': 'en'}

how do i get en from here?

languagedata = collectionG.find_one({"_id":ctx.guild.id},{"language":language,"_id":0}) gets me {'language': 'en'}

but how do i get en :/

hm

[haughty ravine]

how do i get only language key from this line? cause i get language dictionary which i dont need

[torn sphinx]

oh ok so you want to update the language to the language that the user specified
if they didn't specify it stays as en ?

[haughty ravine]

it stays as it was before

i have two languages

en and ru

[stark violet]

save it in a variable and use .get("language") on that variable, if it has a language key it will return one

you can also use it like this
var['language']

[haughty ravine]

ihm

alr thx

will text it

[haughty ravine]

so if there is no "language" in my collection for this user

that means he has english as default

if it has for example russian

[stark violet]

print(var['language'])```

[haughty ravine]

then it puts "ru" to his document

[torn sphinx]

you are the one who sets the language in the first place

so en as default

but if they used the command and specified ru

it get updated to ru

this is what i think it should look like

so for every server you set en as default for the language

[stark violet]

best way is to use upsert, it's a one line of code

[torn sphinx]

if they wanted to change it to ru , they just execute the command with specifying ru

[haughty ravine]

yep

languagedata = collectionG.find_one({"_id":ctx.guild.id},{"language":language,"_id":0}).get("language")
            print(language)
            print(languagedata)

language and languagedata are always the same!

why lol

there are actually no marks in a database

if i say ru in command

languagedata turns ru too

goshh

            print(language)
            print(languagedata)
            if languagedata == None:
                languagedata = 'en'
            if language == languagedata:
                await ctx.send('You have the same current language')
                return

this thing always says "You have the same current language"

pika_ is typing...

XD

[torn sphinx]

@client.command()
async def set_language(ctx, language):
( you access to you mongodb)


guild_id = ctx.guild.id 

server_language = {"_id": guild_id, "language" : "en"}
if collectionG.count_documents(server_language) ==0:
collectionG.insert_one(server_id)

data =collection.find(server_language)
for x in data:
lang = x["language"]
    if lang == language:
        return await ctx.send("you already have that language set up")
else:
new_language = language 
collection.update_one({"_id": guild_id},{"$set" :{"language": new_language}}, upsert = True)
await ctx.send(f"you set the language to {language}")

this is how it should work i think, if i didn't miss a thing

@haughty ravine

[haughty ravine]

woah 😄

thx lol 😄

what is upsert = True

[torn sphinx]

update and insert

you just do it so you don't have to do insert again

[haughty ravine]

ohh

alr

thx a lot 😄

[torn sphinx]

try the command , if there is any errors ,

yw