#databases | Python | Page 144

signal cloak Apr 9, 2021, 10:01 PM

#

I need to add it to notes list

torn sphinx Apr 9, 2021, 10:01 PM

#

Coming back to this real quick, kinda confused still.

a user can have 1 role, shouldn't that be a 1 - many instead of many to many?

shy shuttle Apr 9, 2021, 10:02 PM

#

are you sure a user can only have 1 role? if so, you dont need 3 tables. But i would rethink that to make sure it's what you want

signal cloak Apr 9, 2021, 10:03 PM

#

like I need the pythonic way to insert in a list of an existing document

torn sphinx Apr 9, 2021, 10:03 PM

#

shy shuttle are you sure a user can only have 1 role? if so, you dont need 3 tables. But i w...

Yeah I guess so? for example, me should be an admin, so I can't be a normal user or even a developer.

#

but a normal user, should have 1 role which is the 'user' one

#

then the developer should get the developer role

shy shuttle Apr 9, 2021, 10:04 PM

#

torn sphinx but a normal user, should have 1 role which is the 'user' one

if you do that you create an implicit hierarchy of roles. otoh if you use many-many relation, then a developer is also a user, for example

torn sphinx Apr 9, 2021, 10:06 PM

#

okay, i'll just keep it many to many for now

#

maybe it will click tomorrow, pretty tired too atm

shy shuttle Apr 9, 2021, 10:06 PM

#

torn sphinx maybe it will click tomorrow, pretty tired too atm

keep it many-to-many, you'll be happier later on, trust me

torn sphinx Apr 9, 2021, 10:06 PM

#

it's a ERM anyways 😛

#

sure sure! thanks.

#

user with subcription should be 1 - many right

#

a user can have a subscription

#

but a subscription can be bought by multiple users

shy shuttle Apr 9, 2021, 10:10 PM

#

torn sphinx but a subscription can be bought by multiple users

that depends on what a "subscription" means. If its a generic object that is assigned to users, then that is many to many (users may have many subscriptions, and a subscription can be assigned to many users). but if it is treated more like an account, then it's one to many (a user may have many subscriptions, but each entry in the subscription table is only for 1 user)

torn sphinx Apr 9, 2021, 10:12 PM

#

Makes sense, thanks for your help

#

I've got to watch a crash course in the morning

signal cloak Apr 9, 2021, 10:13 PM

#

any idea guys, I need like the method on how to do it

#

in python

#

add json to list in exitsting item in collection

torn sphinx Apr 9, 2021, 10:13 PM

#

signal cloak like I need the pythonic way to insert in a list of an existing document

isn't that just update?

#

https://stackoverflow.com/questions/13710770/how-to-update-values-using-pymongo

Stack Overflow

How to update values using pymongo?

I've a mongodb collection in this form:

{id=ObjectId(....),key={dictionary of values}}
where dictionary of values is {'a':'1','b':'2'.....}
Let dictionary of values be 'd'.
I need to update the v...

signal cloak Apr 9, 2021, 10:29 PM

#

ty

torn sphinx Apr 9, 2021, 10:37 PM

#

shy shuttle that depends on what a "subscription" means. If its a generic object that is ass...

I'm starting to think it's a 1-1

#

1 user is able to have 1 subscription in the account

#

a user has a subscription, that's 1-1 right

shy shuttle Apr 9, 2021, 10:39 PM

#

torn sphinx a user has a subscription, that's 1-1 right

if they can only ever have 1 subscription at a time, then that is right. But in that case, what really is the difference between an account and a subscription? does it make sense to have separate tables, or can you merge the subscription columns into an existing table?

signal cloak Apr 9, 2021, 10:41 PM

#

collection.update_one(
        {'_id': user_id},
        {"$set": {"notes": [].append(note)}},
    upsert=True)```

#

This should work or not cause rn its not working

#

i get a null value in mongo

signal cloak Apr 9, 2021, 10:44 PM

#

shy shuttle if they can only ever have 1 subscription at a time, then that is right. But in ...

Man sry for bothering, any idea?

gloomy canopy Apr 9, 2021, 10:46 PM

#

signal cloak ```py collection.update_one( {'_id': user_id}, {"$set": {"notes"...

Instead of using $set you can use $push which will add the value to an existing list

signal cloak Apr 9, 2021, 10:47 PM

#

gloomy canopy Instead of using `$set` you can use `$push` which will add the value to an exist...

what if there is no list, how do i catch that ty btw

#

also its giving me a null value still

gloomy canopy Apr 9, 2021, 10:49 PM

#

signal cloak what if there is no list, how do i catch that ty btw

If it doesn't exist I believe pymongo will raise an exception, you can catch that then rerun the query with $set

signal cloak Apr 9, 2021, 10:51 PM

#

Ok thanks

#

also i see now

#

its giving them like a json file

#

like that

gloomy canopy Apr 9, 2021, 10:51 PM

#

signal cloak what if there is no list, how do i catch that ty btw

If you don't mind not being able to add duplicate notes you can use $addToSet which will append if it already exists or create the field

signal cloak Apr 9, 2021, 10:51 PM

#

There is no way to remove the 0: or 1:

#

and do it like

#

    {"id": 1},
    {"id":"asdas114=-ads"}

]```

#

smt like that

#

like a list of objects

signal cloak Apr 9, 2021, 10:54 PM

#

gloomy canopy If you don't mind not being able to add duplicate notes you can use `$addToSet` ...

ty for that

#

or i think its in my code the problem

gloomy canopy Apr 9, 2021, 10:57 PM

#

Yes, that is possible. You've got to make sure the value you're setting is a Python dictionary, looks like it was an empty string, then None, then a string of a dictionary (from json.dumps()?)

signal cloak Apr 9, 2021, 10:57 PM

#

I did json.loads(str)

#

can I show you haha

#

                note = Journal(str(id),title.content,details.content, "")
                note = note.toJSON()
                note = json.loads(note)
                print(ctx.author.id)
                await insertNote(ctx.author.id, note)

#

collection.update_one(
        {'_id': user_id},
        {"$addToSet":{"notes": note}},
    upsert=True)```

#

the second one is the function

#

def toJSON(self):
        return json.dumps(self, default=lambda o: o.__dict__, 
            sort_keys=False, indent=4)```

crystal aspen Apr 9, 2021, 11:49 PM

#

hey i am using sqlite3 and i am trying to pass this query but getting syntax error how should i fix it ?
f"insert into k_cards(print, card_name,series_name,ed2_code) values(?,?,?,?) on conflict(print,card_name,series_name) do update ed2_code = {code}",prints,name,series,code

shy shuttle Apr 10, 2021, 12:02 AM

#

crystal aspen hey i am using sqlite3 and i am trying to pass this query but getting syntax err...

looks like a valid python tuple.

rocky moth Apr 10, 2021, 3:55 AM

#

I have two database tables:
Table A
Receipt_ID Line_Number
abc 1
abc 2
Table B
Receipt_ID Line_Number Ship_Number
abc 1 1
abc 2 2
How do I validate if there is a 1:1 or 1:many relationship between Tables A & B?
I don't want to check 1 Receipt_ID at a time manually. I'd like to find some sort of query logic.

obtuse glacier Apr 10, 2021, 4:37 AM

#

Can someone help me with databases cus I'm not understanding

torn sphinx Apr 10, 2021, 4:50 AM

#

    await bot.db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, levelup_id int, PRIMARY KEY (guild_id,))")

Im asking at this part

PRIMARY KEY (guild_id,))

Do i need the ,
or can i just have (guild_id))

twin palm Apr 10, 2021, 8:01 AM

#

How can we create ER diagram of NoSQL Database(Firebase)?

worthy lance Apr 10, 2021, 9:08 AM

#

Hello 🙂 I need to store NACE codes (https://www.ssb.no/en/klass/klassifikasjoner/6) in a Postgres db (will be using Python and SQLAlchemy). Could use some help regarding the structure. As you can see, the codes are organized in levels and typically take on this structure:

  Level    Code    Desc    Short_desc   
 ------- -------- ------ -------------- 
      1   A        Text   Shorter text  
      2   01       Text   Shorter text  
      3   01.1     Text   Shorter text  
      4   01.11    Text   Shorter text  
      5   01.110   Text   Shorter text

rich trout Apr 10, 2021, 9:30 AM

#

It would probably make the most sense to attach each code to an ID, and then store them straightforwardly as level being an int, code a varchar, desc a text, and short as a text as well

#

While you could go with a composite datatype for Code, that feels like overkill, is not portable, and is essentially no faster

worthy lance Apr 10, 2021, 9:32 AM

#

@rich trout I (we) are trying to normalize to the greatest extent possible. The SSB API gives us a useful field "parentCode", which in the case above would be the "Code" for the row above.

#

So we're thinking of maybe having a relation between code and parentCode

rich trout Apr 10, 2021, 9:33 AM

#

Well, consider that in many cases normalizing to the maximum amount possible is not always a good thing

worthy lance Apr 10, 2021, 9:33 AM

#

But the problem is code and parentCode wouldn't be unique (as a composite primary key)

#

Yeah, I think this might be one of those cases honestly

rich trout Apr 10, 2021, 9:33 AM

#

Me too

#

Protip: do not use string primary keys

worthy lance Apr 10, 2021, 9:33 AM

#

Thanks 🙂 Why not? 😄

rich trout Apr 10, 2021, 9:33 AM

#

Because they're hard

#

numeric primary keys are very easy to work with, store, compare, etc

worthy lance Apr 10, 2021, 9:35 AM

#

Thanks for pointing out. We'll avoid it where we can, but some of the API's we use do it (e.g. some kind of UUID)

#

And we're creating a system for automated data retrieval (with historic data), so we need to rely on the API response for primary key selection

rich trout Apr 10, 2021, 9:35 AM

#

Well, uuids have their own type distinct from text for this purpose

worthy lance Apr 10, 2021, 9:35 AM

#

Oh, that's good to know!

rich trout Apr 10, 2021, 9:36 AM

#

A very common solution is to ensure each table has an integer primary key, and use that for within-database operations, ensuring everything links the same way, and then exporting out to the more complex representation when needed

#

In your case, where each entry can have a ton of parents, and the resultant code is the combination of them

#

someone smarter than me could write queries to simply have parent and a one character code.. but that's not me

#

and I'd just advocate updating them as a group

worthy lance Apr 10, 2021, 9:37 AM

#

rich trout someone smarter than me could write queries to simply have parent and a one char...

I was going for that initially, but got "stuck" when I realized the code + parentCode combination wouldn't be unique in itsself

#

You'd have to traverse all the way up the parent tree to get a unique value

rich trout Apr 10, 2021, 9:38 AM

#

Hm

#

I've found something

#

https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/22376973#22376973

Stack Overflow

What is the most efficient/elegant way to parse a flat table into a...

Assume you have a flat table that stores an ordered tree hierarchy:

Id Name ParentId Order
1 'Node 1' 0 10
2 'Node 1.1' 1 10
3 'Node 2' ...

#

https://www.postgresql.org/docs/current/ltree.html

PostgreSQL Documentation

F.21. ltree

F.21. ltree F.21.1. Definitions F.21.2. Operators and Functions F.21.3. Indexes F.21.4. Example F.21.5. Transforms F.21.6. Authors This module implements a data …

worthy lance Apr 10, 2021, 9:40 AM

#

Wow, thanks! I've been thinking about lately how google-fu is really what makes the programmer

#

And I couldn't figure out how to google this one lmao

rich trout Apr 10, 2021, 9:42 AM

#

Yeah, took me a moment lol

#

It's a tree structure, so I should have searched postgres tree

#

wish you luck!

worthy lance Apr 10, 2021, 9:43 AM

#

Thanks again 🙂

#

I found this article as well which should be helpful going forward
https://www.kite.com/blog/python/sqlalchemy/

Code Faster with Kite

Brendan

Modeling Trees with SQLAlchemy ORM and Postgres Ltree

SQLAlchemy is a powerful Python relational database tool. I’ll walk through how to implement a tree in Python using SQLAlchemy and PostgreSQL Ltree.

#

@rich trout I have one final question though - how should I define the primary key, considering I need to match it against API responses in the future?

rich trout Apr 10, 2021, 9:50 AM

#

oh nice

#

The primary key is the path to the node

worthy lance Apr 10, 2021, 9:51 AM

#

Oh okay - so the ltree column actually stores the entire path?

#

If so, I didn't get that initially 😄

rich trout Apr 10, 2021, 9:52 AM

#

Pretty much. But the example you linked has an id column on top of that

#

Which I do recommend. It's essentially free

worthy lance Apr 10, 2021, 9:54 AM

#

Yeah, in one way it makes sense to me. I guess it "helps" the db engine to have reasonably typed primary keys. In another way, a generated primary key wouldn't help in mapping API data to the db

#

I guess we'd typically have to query the ltree column then to get the mapping right

rich trout Apr 10, 2021, 9:54 AM

#

well, consider

#

If you have the path -> query ltree

#

if you have a db row -> query database normally with ids

worthy lance Apr 10, 2021, 9:55 AM

#

Of course, that absolutely makes sense

#

So store relations using id, and map against API using path

#

Now I'm hearing you 🙂

#

Is there some type of command I can execute here to appreciate your helpfulness? 😄

rich trout Apr 10, 2021, 10:02 AM

#

nope, and I'm thankful. Just here to help and hope I'm finding the right things and not the wrong ones lemon_sweat

civic schooner Apr 10, 2021, 1:21 PM

#

anyone here that knows databases

#

well I need help to understand one thing

#

is it possible to add data from a login system to SQL database? and how will i do this?

cunning jolt Apr 10, 2021, 1:22 PM

#

wdym data based on a login system?

elder vessel Apr 10, 2021, 1:31 PM

#

civic schooner is it possible to add data from a login system to SQL database? and how will i d...

wdym

#

hey!

#

can i have 2 permissions in @commands.has_permissions(kick_member=True or ban_member=True) ?

#

hmm??

burnt turret Apr 10, 2021, 1:43 PM

#

Wrong channel, #discord-bots would be the place, but check out commands.check_any

elder vessel Apr 10, 2021, 1:43 PM

#

burnt turret Wrong channel, <#343944376055103488> would be the place, but check out `commands...

ohk

signal cloak Apr 10, 2021, 1:46 PM

#

Can somebody come voice and explain me how mongo db arrrays waork?

north locust Apr 10, 2021, 2:00 PM

#

how do i use google firebase in python?

civic schooner Apr 10, 2021, 2:05 PM

#

cunning jolt wdym data based on a login system?

is it possible to add data from a login system to SQL database? and how will i do this?

cunning jolt Apr 10, 2021, 2:05 PM

#

you can add any relational data to sql

#

also wdym with "login system"

civic schooner Apr 10, 2021, 2:06 PM

#

yes but i have a login system how can i link that with the database?

cunning jolt Apr 10, 2021, 2:06 PM

#

a login system can mean anything

tame mauve Apr 10, 2021, 2:06 PM

#

Hi there, just wondering if you have ever got your question replied :) I'm kind of going through the same path. Cheers

cunning jolt Apr 10, 2021, 2:06 PM

#

you need to be a bit more specific

civic schooner Apr 10, 2021, 2:06 PM

#

cunning jolt also wdym with "login system"

I have made my own login system and I am trying to link it to SQL database, how will this action be performed?

cunning jolt Apr 10, 2021, 2:07 PM

#

insert the data and request it when you need it

civic schooner Apr 10, 2021, 2:07 PM

#

cunning jolt insert the data and request it when you need it

what?

cunning jolt Apr 10, 2021, 2:07 PM

#

https://realpython.com/python-sql-libraries/

Introduction to Python SQL Libraries – Real Python

In this step-by-step tutorial, you'll learn how to connect to different database management systems by using various Python SQL libraries. You'll interact with SQLite, MySQL, and PostgreSQL databases and perform common database queries using a Python application.

civic schooner Apr 10, 2021, 2:07 PM

#

btw I have no experience with SQL

cunning jolt Apr 10, 2021, 2:08 PM

#

yes this is a tutorial intended for beginners

civic schooner Apr 10, 2021, 2:08 PM

#

yes i get it check a tutorial, but all i need is to know how to link it then actually start coding in SQL

cunning jolt Apr 10, 2021, 2:09 PM

#

?

#

you dont code in sql

civic schooner Apr 10, 2021, 2:16 PM

#

No

civic schooner Apr 10, 2021, 2:17 PM

#

cunning jolt you dont code in sql

I just want to low key link it to my python login system file.

cunning jolt Apr 10, 2021, 2:30 PM

#

civic schooner I just want to low key link it to my python login system file.

so i think you're having a few misunderstandings:
a database is a program that usually runs on a server but can also run on your local pc
sql is a standard for interacting with a database which has been adopted by a few databases (mariadb, postgressql, etc.)

civic schooner Apr 10, 2021, 2:31 PM

#

cunning jolt so i think you're having a few misunderstandings: a database is a program that u...

okay and this tells me what?

#

i dont think you are answering my question

#

I just want to low key link SQL database to my python login system file. Am I able to do this?

cunning jolt Apr 10, 2021, 2:32 PM

#

so if you want to store data on a database you do these things:
install the db
set it up
and tell your program how to interact with it

civic schooner Apr 10, 2021, 2:33 PM

#

i don't feel like you are answering my question

cunning jolt Apr 10, 2021, 2:33 PM

#

i am

civic schooner Apr 10, 2021, 2:34 PM

#

so with what you are saying I can't link it to a SQL database

#

uh .. dear programmers

cunning jolt Apr 10, 2021, 2:34 PM

#

you dont link a database

#

a database is like google for example

#

a really bad comparison but it'll do for now

#

you can ask it for data and it'll give it to you

#

also the other way around

#

so if you want to "link" your program to an sql database you have to install one first

#

then find the appropriate library for your database

#

and then your program can add/remove/request data from the database

civic schooner Apr 10, 2021, 2:37 PM

#

hm ... okay so what if I want to push all the data from the login system to the database, how will I do this?

cunning jolt Apr 10, 2021, 2:37 PM

#

how does it work currently

civic schooner Apr 10, 2021, 2:37 PM

#

I have no idea, I have just worked with flat files

cunning jolt Apr 10, 2021, 2:38 PM

#

can you maybe send the code?

civic schooner Apr 10, 2021, 2:38 PM

#

there is nothing to send

#

like i said i have just operated with flat files through a link

#

i have not started coding in SQL

cunning jolt Apr 10, 2021, 2:38 PM

#

no i meant the code of how the system currently works

civic schooner Apr 10, 2021, 2:39 PM

#

what .. ?

#

my Login system?

cunning jolt Apr 10, 2021, 2:39 PM

#

yes

civic schooner Apr 10, 2021, 2:39 PM

#

is that what you mean? ... Well why do you want to see it

cunning jolt Apr 10, 2021, 2:40 PM

#

to get an understanding of what you want to do

civic schooner Apr 10, 2021, 2:40 PM

#

there is no errors there is really nothing to see

civic schooner Apr 10, 2021, 2:41 PM

#

cunning jolt to get an understanding of what you want to do

hm .. but what I want to do I have already explained you

cunning jolt Apr 10, 2021, 2:41 PM

#

also for a login system with a db it usually works this way: you have a user stored in a db the user has a hashed password attribute when the user enters a password it gets hashed and the program requests the hashed password stored in the db if they are equal the user authentication is successful

civic schooner Apr 10, 2021, 2:42 PM

#

I just want to push data to a database from a login system which I have made

cunning jolt Apr 10, 2021, 2:42 PM

#

so

#

i have no idea what this data is you're talking about

#

i have no idea how the login system you have works

civic schooner Apr 10, 2021, 2:43 PM

#

have you not seen a login system?

cunning jolt Apr 10, 2021, 2:43 PM

#

i have no idea what the login system you have even protects

cunning jolt Apr 10, 2021, 2:43 PM

#

civic schooner have you not seen a login system?

a website login system?

civic schooner Apr 10, 2021, 2:43 PM

#

nope

#

a login system made in tkinter

cunning jolt Apr 10, 2021, 2:43 PM

#

a operating system login?

civic schooner Apr 10, 2021, 2:44 PM

#

no a login system made in tkinter

cunning jolt Apr 10, 2021, 2:44 PM

#

what for

civic schooner Apr 10, 2021, 2:44 PM

#

for learning

cunning jolt Apr 10, 2021, 2:44 PM

#

then sql isnt what you're looking for

civic schooner Apr 10, 2021, 2:45 PM

#

why?

cunning jolt Apr 10, 2021, 2:45 PM

#

because sql is intended for storing much more data

#

ie a website authenticating thousands of users

#

if its just for a single password its not really worth it

#

if you really want to use sql use sqllite

#

https://www.sqlitetutorial.net/sqlite-python/

SQLite Tutorial

admin

SQLite Python

This section guides you how to work with the SQLite database using Python sqlite3 module.

civic schooner Apr 10, 2021, 2:47 PM

#

Look ... I have decided to learn SQL no matter what and all I need is to push data from a login system and I do not know how to do this?

cunning jolt Apr 10, 2021, 2:47 PM

#

what data do you even want to push?

civic schooner Apr 10, 2021, 2:48 PM

#

any data accessed in the Login system... Look do you know how to do this or not?

cunning jolt Apr 10, 2021, 2:48 PM

#

i know how to do this

civic schooner Apr 10, 2021, 2:48 PM

#

cunning jolt i know how to do this

then just tell me

cunning jolt Apr 10, 2021, 2:48 PM

#

i just dont know what exactly you're trying to do

civic schooner Apr 10, 2021, 2:49 PM

#

cunning jolt i just dont know what exactly you're trying to do

Just tell me

#

why I need it is not really your business besides I already told you learning purposes

cunning jolt Apr 10, 2021, 2:50 PM

#

look if you're not telling me what you want to do i cant help you

north locust Apr 10, 2021, 2:51 PM

#

Extension 'cogs.socialv2' raised an error: NameError: name 'db' is not defined```
```py
import firebase_admin
from firebase_admin import credentials

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
firebase_admin.initialize_app(cred)
ref = db.reference("/")```
is that code valid?

civic schooner Apr 10, 2021, 2:51 PM

#

cunning jolt look if you're not telling me what you want to do i cant help you

Now look ... I have told you this several times All I want to do is to push or store data that comes from my login system to a database.

cunning jolt Apr 10, 2021, 2:52 PM

#

cunning jolt https://www.sqlitetutorial.net/sqlite-python/

read this

#

there is a library called sqllite

proven arrow Apr 10, 2021, 2:52 PM

#

civic schooner Now look ... I have told you this several times All I want to do is to push or s...

Have you decided what database to use first?

#

There’s a dozen ways to answer that question, so you need be more specific

cunning jolt Apr 10, 2021, 2:53 PM

#

this

proven arrow Apr 10, 2021, 2:53 PM

#

north locust ```Exception has occurred: ExtensionFailed Extension 'cogs.socialv2' raised an e...

You didn’t define db

civic schooner Apr 10, 2021, 2:53 PM

#

proven arrow Have you decided what database to use first?

But i want the data to automatically be stored in the database without I need to push it in there all the time

cunning jolt Apr 10, 2021, 2:53 PM

#

doesnt work that way

north locust Apr 10, 2021, 2:54 PM

#

proven arrow You didn’t define db

how do i define db?
like what do i put in it>

cunning jolt Apr 10, 2021, 2:54 PM

#

data just isnt somewhere magically

proven arrow Apr 10, 2021, 2:54 PM

#

civic schooner But i want the data to automatically be stored in the database without I need to...

Well nothing happens automatically, you need to code it somewhere

cunning jolt Apr 10, 2021, 2:54 PM

#

would be nice if it were that way tho

civic schooner Apr 10, 2021, 2:54 PM

#

Well Like I said I don't know anything about databases and I do not know which library I should use based out of hearing what I want to do ... Which one do u recommend for me.

cunning jolt Apr 10, 2021, 2:55 PM

#

so if its just local use sqllite

#

its a library that you can store/retrieve data from using sql

north locust Apr 10, 2021, 2:55 PM

#

what should i define db =
what should i put in it?

civic schooner Apr 10, 2021, 2:55 PM

#

proven arrow Well nothing happens automatically, you need to code it somewhere

Well yes ... I linked it to a flat file and the data was always accessed automatically

proven arrow Apr 10, 2021, 2:56 PM

#

north locust how do i define db? like what do i put in it>

I think you need to import it from firebase admin

civic schooner Apr 10, 2021, 2:56 PM

#

cunning jolt would be nice if it were that way tho

flat files.

cunning jolt Apr 10, 2021, 2:56 PM

#

also dont work automatically

north locust Apr 10, 2021, 2:56 PM

#

proven arrow I think you need to import it from firebase admin

? please explain

north locust Apr 10, 2021, 2:57 PM

#

proven arrow I think you need to import it from firebase admin

i was also going off this guide
https://www.freecodecamp.org/news/how-to-get-started-with-firebase-using-python/

freeCodeCamp.org

How to Get Started with Firebase Using Python

This article a detailed guide that'll help you set up your Firebase database and perform simple CRUD operations on it using Python. Firebase, as you might know, is a platform provided by Google to accelerate app development. It offers BaaS or backend as a service, which means that Firebase takes

civic schooner Apr 10, 2021, 2:57 PM

#

cunning jolt also dont work automatically

yes as long as you have linked it to the python file .... What do you mean??

proven arrow Apr 10, 2021, 2:58 PM

#

north locust i was also going off this guide https://www.freecodecamp.org/news/how-to-get-sta...

Then in that guide it shows you how they import it

civic schooner Apr 10, 2021, 2:58 PM

#

the data is accessed dynamically

north locust Apr 10, 2021, 2:58 PM

#

proven arrow Then in that guide it shows you how they import it

wait where in it was it because i mightve missed it

proven arrow Apr 10, 2021, 2:58 PM

#

north locust wait where in it was it because i mightve missed it

How to Write Using the set() Function

north locust Apr 10, 2021, 2:59 PM

#

proven arrow How to Write Using the set() Function

ok thanks i will look there 🙂

proven arrow Apr 10, 2021, 2:59 PM

#

Also the official firebase docs had good examples

north locust Apr 10, 2021, 3:00 PM

#

lol im dumb i apparently accadentally deleted this line

#

from firebase_admin import db```

#

    @commands.command(name='boop', description='boop someone', alias=['boops', 'Boop', 'Boops'])
    async def boop(self, ctx):
        ref.set({
            "Books":
            {
                "Best_Sellers": -1
            }
        })

        ref = db.reference("/Books/Best_Sellers")
        import json
        with open("book_info.json", "r") as f:
            file_contents = json.load(f)

        for key, value in file_contents.items():
            ref.push().set(value)

where do i put the database url?

proven arrow Apr 10, 2021, 3:08 PM

#

In the db.reference

signal cloak Apr 10, 2021, 3:18 PM

#

#

How can I customize 0,1,2

#

like push my variables there

#

as ekys

#

keys

burnt turret Apr 10, 2021, 3:22 PM

#

you mean you don't want 0, 1, 2..there, you want your own keys? then that wouldn't be an array anymore

#

those are indices

untold robin Apr 10, 2021, 3:32 PM

#

suppose i have dataframe:

id  tstamp  val  day
1   2021-03-etc  63.9  2
4   2021-03-etc  89.3  4

#

imagine that there are ids from 1 to 6 240k rows. i want to cut my dataframe down in aggregate by:

compute the mean of the value for the given day associated with it (this day was acquired by stringformat time, takes the day of the tstamp column)
then, also, retrieve the max timestamp value for the given day associated with it

#

so i am left with a dataframe that has the average value for the day as well as max timestamp

#

i am new to pandas and stuff and when I use groupby I tend to lose the timestamp, i can do one or the other and lose information, but not both

#

any help would be very much appreciated.

#

crap, i realised this might be the wrong section

#

sorry..

signal cloak Apr 10, 2021, 3:35 PM

#

burnt turret you mean you don't want `0, 1, 2..`there, you want your own keys? then that woul...

lets say I dont need an array I need like json objects like the most preferable is th

#

smt like that

#

{
  "notes": [
    {
      "id": 1,
      "name": "john"
    },
    {
      "id": 456478548,
      "name": "Smiths"
    }
  ]
}```

#

smt like that

#

Would be the best

burnt turret Apr 10, 2021, 3:38 PM

#

signal cloak ```json { "notes": [ { "id": 1, "name": "john" }, { ...

it is like that right now

#

this is just the way that the mongodb UI displays it

#

if you use the shell to display it, it'd look like this

signal cloak Apr 10, 2021, 3:39 PM

#

Ok great then

#

how Can I retreive them now?

signal cloak Apr 10, 2021, 3:39 PM

#

burnt turret it is like that right now

I want to called them by my custom ID

burnt turret Apr 10, 2021, 3:42 PM

#

to query? that'd be something like collection.find({"notes.id": <ID you want to retrieve>})

#

https://docs.mongodb.com/manual/tutorial/query-array-of-documents/

Query an Array of Embedded Documents — MongoDB Manual

MongoDB Manual - how to query an array of documents, how to query an array of nested documents, how to query an array of embedded documents.

signal cloak Apr 10, 2021, 3:43 PM

#

so note.id is not going to check the numbers there, its going straight to the object?

burnt turret Apr 10, 2021, 3:43 PM

#

yeah it won't check by the index

#

The docs I linked explains all this quite well

signal cloak Apr 10, 2021, 3:46 PM

#

I read I understand now

#

I so if I need to check 2 coinditions

#

I can go like

#

collection.find ({"_id": "my_custom_user_id", "notes": {"id": "my_custom_note_id"}})

#

or notes.id

#

as you say above

burnt turret Apr 10, 2021, 3:49 PM

#

notes.id would work

signal cloak Apr 10, 2021, 3:49 PM

#

both correct?

burnt turret Apr 10, 2021, 3:49 PM

#

what you've written searches for an exact match

#

like the object in the array should be just {"id": "my_custom_note_id"} and nothing more

signal cloak Apr 10, 2021, 3:50 PM

#

oh ok

#

so with notes.id

#

it itterates in all note objects

#

and checks their id

burnt turret Apr 10, 2021, 3:51 PM

#

yep

signal cloak Apr 10, 2021, 3:51 PM

#

cool

#

thank you soo much

signal mica Apr 10, 2021, 6:44 PM

#

Hi

austere portal Apr 10, 2021, 7:14 PM

#

Is there a way to generate a postgres uri using python?

fringe dock Apr 10, 2021, 7:27 PM

#

can someone help me in class 12 python

#

indian syllabus

#

and sql

#

i have a good hold on networking

#

can someone help

prisma girder Apr 10, 2021, 8:05 PM

#

fringe dock can someone help

Post your question here, if someone is able to answer it then you is going to get the answer

torn sphinx Apr 10, 2021, 8:05 PM

#

#

Im trying to make a login system that fetches the mongodb inputs and checks

#

def login():
user = input("What Is Your Username: ")
password = input(f"What Is Your Password {user}? ")
print("Please Wait While I Log You In")
time.sleep(1)
name = mycol.find_one(name)
print (f"Found {name}")

prisma girder Apr 10, 2021, 8:05 PM

#

torn sphinx def login(): user = input("What Is Your Username: ") password = input(f"...

!code

delicate fieldBOT Apr 10, 2021, 8:05 PM

#

Here's how to format Python code on Discord:

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

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

torn sphinx Apr 10, 2021, 8:06 PM

#

def login():
    user = input("What Is Your Username: ")
    password = input(f"What Is Your Password {user}? ")
    print("Please Wait While I Log You In")
    time.sleep(1)
    name = mycol.find_one(name)
    print (f"Found {name}")

prisma girder Apr 10, 2021, 8:06 PM

#

torn sphinx

The error message stays clear for me - you are using variable that is not created

torn sphinx Apr 10, 2021, 8:06 PM

#

im trying to make it find the input inside the databse

prisma girder Apr 10, 2021, 8:07 PM

#

torn sphinx ```py def login(): user = input("What Is Your Username: ") password = in...

It shouldn't be name = mycol.find_one(user)?

torn sphinx Apr 10, 2021, 8:07 PM

#

then it returns non

#

none

#

and i did put in my username

prisma girder Apr 10, 2021, 8:07 PM

#

torn sphinx then it returns non

What is mycol?

torn sphinx Apr 10, 2021, 8:08 PM

#

the collection

#

db = myclient["logins"]
mycol = db["logins"]

def main():
    username = input("Whats Your Username: ")
    password = input("Choose A Password: ")
    password2 = input("Choose A Password: ")
    if password2 == password:
        name = { "name": username, "password":password}
        x = mycol.insert_one(name)
        print (x)
        login()
    else:
        pass
    
def login():
    user = input("What Is Your Username: ")
    password = input(f"What Is Your Password {user}? ")
    print("Please Wait While I Log You In")
    time.sleep(1)
    name = mycol.find_one(name)
    print (f"Found {name}")
    

main()
```\

prisma girder Apr 10, 2021, 8:08 PM

#

torn sphinx the collection

Now I don't know what is myclient pithink Which library are you using?

torn sphinx Apr 10, 2021, 8:08 PM

#

pymongo

#

myclient is the mongodb connector

#

myclient = pymongo.MongoClient("string")

prisma girder Apr 10, 2021, 8:10 PM

#

torn sphinx ```py db = myclient["logins"] mycol = db["logins"] def main(): username = i...

Try to use .find_one({"name": user}) instead of .find_one(user)

#

Here you have docs https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.find_one

torn sphinx Apr 10, 2021, 8:11 PM

#

#

still finds none

#

prisma girder Apr 10, 2021, 8:12 PM

#

torn sphinx

You are trying to find Caz when in database is caz

torn sphinx Apr 10, 2021, 8:12 PM

#

omg

#

im an idiot

#

lol

#

tysm

prisma girder Apr 10, 2021, 8:12 PM

#

Obvious typo

torn sphinx Apr 10, 2021, 8:12 PM

#

i now understand mongodb a bit better, it is case sensitive

#

im hoping to make my first webapp

prisma girder Apr 10, 2021, 8:13 PM

#

torn sphinx im hoping to make my first webapp

Good luck then!

torn sphinx Apr 10, 2021, 8:13 PM

#

is there a way to encode a password when entering it to databse?

#

and it can still be read?

prisma girder Apr 10, 2021, 8:13 PM

#

torn sphinx is there a way to encode a password when entering it to databse?

Use hash functions

#

!pypi bcrypt

delicate fieldBOT Apr 10, 2021, 8:13 PM

#

bcrypt v3.2.0

Modern password hashing for your software and your servers

torn sphinx Apr 10, 2021, 8:13 PM

#

docs?

#

tys

#

tys

#

tysm

#

my M key dosent want to work

prisma girder Apr 10, 2021, 8:13 PM

#

No worries

torn sphinx Apr 10, 2021, 8:15 PM

#

so before entering the pwd

i would go

password = input(Whats your pwd)
hashedpwd = bcrypt.hashpw(password, bcrypt.gensalt())

#

@prisma girder is this correct?

prisma girder Apr 10, 2021, 8:16 PM

#

torn sphinx so before entering the pwd i would go password = input(Whats your pwd) hashedp...

You need to store salt somewhere

torn sphinx Apr 10, 2021, 8:16 PM

#

whats salt?

prisma girder Apr 10, 2021, 8:17 PM

#

torn sphinx whats salt?

Salt is (almost always) random data to protect against offline cracking
https://en.wikipedia.org/wiki/Salt_(cryptography)

Salt (cryptography)

In cryptography, a salt is random data that is used as an additional input to a one-way function that hashes data, a password or passphrase. Salts are used to safeguard passwords in storage. Historically a password was stored in plaintext on a system, but over time, additional safeguards were developed to protect a user's password against being ...

torn sphinx Apr 10, 2021, 8:17 PM

#

Traceback (most recent call last):
File "main.py", line 31, in <module>
main()
File "main.py", line 12, in main
encryptedpw = bcrypt.hashpw(password, bcrypt.gensalt())
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\bcrypt_init_.py", line 80, in hashpw
raise TypeError("Unicode-objects must be encoded before hashing")
TypeError: Unicode-objects must be encoded before hashing

prisma girder Apr 10, 2021, 8:17 PM

#

torn sphinx Traceback (most recent call last): File "main.py", line 31, in <module> ma...

If you have string you need to use .encode() method

#

!e

string = "xyz"
print(string)
print(string.encode())

delicate fieldBOT Apr 10, 2021, 8:17 PM

#

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | xyz
002 | b'xyz'

torn sphinx Apr 10, 2021, 8:18 PM

#

what do i import

#

#

does that look correct

torn sphinx Apr 10, 2021, 8:24 PM

#

prisma girder If you have string you need to use `.encode()` method

So i have done that how can i make it convert the password from binary to check if the user entered the correct password

prisma girder Apr 10, 2021, 8:24 PM

#

torn sphinx So i have done that how can i make it convert the password from binary to check ...

You cannot and it is the key

#

Use verify method

torn sphinx Apr 10, 2021, 8:25 PM

#

bcrypt.checkpw?

prisma girder Apr 10, 2021, 8:25 PM

#

torn sphinx bcrypt.checkpw?

Yes

torn sphinx Apr 10, 2021, 8:26 PM

#

so what?

passwordcheck = bcrypt.checkpw(password)
password = mycol.find_one({"password": passwordcheck})

#

like that?

prisma girder Apr 10, 2021, 8:28 PM

#

torn sphinx like that?

Nope, get hash from the database and then call bcrypt.checkpw

torn sphinx Apr 10, 2021, 8:29 PM

#

hmm, how would i do this?

prisma girder Apr 10, 2021, 8:32 PM

#

torn sphinx hmm, how would i do this?

Firstly get password from database for selected user

torn sphinx Apr 10, 2021, 8:33 PM

#

how would i do this?

prisma girder Apr 10, 2021, 8:33 PM

#

I don’t know, I am not using pymongo and I am away of computer now

torn sphinx Apr 10, 2021, 8:47 PM

#

how do i create an SQL database with salted hashes

torn sphinx Apr 10, 2021, 9:09 PM

#

Is there a reason this isnt working??

cursor.execute("INSERT INTO balance (user_id, amount) VALUES (?, ?) ON CONFLICT(user_id) DO UPDATE SET balance = balance + ?;", [user, amount, amount])```
And its weird because im not even getting a error so i added prints and this is line is where it stops

#

ping if someone can help

prisma girder Apr 10, 2021, 9:11 PM

#

torn sphinx how do i create an SQL database with salted hashes

What do you mean?

torn sphinx Apr 10, 2021, 9:11 PM

#

Login database

prisma girder Apr 10, 2021, 9:13 PM

#

torn sphinx Is there a reason this isnt working?? ```py cursor.execute("INSERT INTO balance ...

Which library are you using?

torn sphinx Apr 10, 2021, 9:13 PM

#

sqlite3

proven arrow Apr 10, 2021, 9:17 PM

#

torn sphinx Is there a reason this isnt working?? ```py cursor.execute("INSERT INTO balance ...

What do you mean it stops?

#

Did you commit() ?

torn sphinx Apr 10, 2021, 9:17 PM

#

that is at the end of the command

prisma girder Apr 10, 2021, 9:18 PM

#

How about database logs?

torn sphinx Apr 10, 2021, 9:18 PM

#

this is my full command```py

@commands.command()
@commands.cooldown(1, 10, commands.BucketType.user)
async def beg(self, ctx):
dbase = sqlite3.connect('economy.db')
cursor = dbase.cursor()

    user = ctx.author.id

    names = [
        'Carl',
        'Jimmy',
        'Bob',
        'Lily',
        'Gerald',
        'Joe',
        'Karal'
    ]

    print('hello')
    amount = random.randint(0, 1000)
    print('hellodsgasdf')

    cursor.execute("INSERT INTO balance (user_id, amount) VALUES (?, ?) ON CONFLICT(user_id) DO UPDATE SET balance = balance + ?;", [user, amount, amount])
    print('henlo')
    amount = ('{:,}'.format(amount))
    await ctx.send(f'{random.choice(names)} Gave you :dankmerchants: **{amount}**!')

    dbase.commit()
    dbase.close()```

prisma girder Apr 10, 2021, 9:18 PM

#

Is there info about this query?

torn sphinx Apr 10, 2021, 9:19 PM

#

prisma girder Is there info about this query?

one sec

#

feel stupid but where are the db logs?

#

nvm

prisma girder Apr 10, 2021, 9:21 PM

#

torn sphinx this is my full command```py @commands.command() @commands.cooldown(1, ...

Shouldn’t you commit on cursor object?

torn sphinx Apr 10, 2021, 9:21 PM

#

huh?

#

so i have something similer to this in another command and it works like a charm but this wont work it is basicly the same code

#

but thee table name changed

proven arrow Apr 10, 2021, 9:23 PM

#

What is balance? Name of table or column ?

torn sphinx Apr 10, 2021, 9:23 PM

#

both

#

or can the name of table and colum not have the same name??

proven arrow Apr 10, 2021, 9:24 PM

#

It can be same

torn sphinx Apr 10, 2021, 9:25 PM

#

hmm then idk whats going wrong

#

because its just not even executing it

proven arrow Apr 10, 2021, 9:26 PM

#

You need to share more info. How you created table etc.

torn sphinx Apr 10, 2021, 9:26 PM

#

i created table in the sqlite browser

#

idk what else to share ig

prisma girder Apr 10, 2021, 9:30 PM

#

How about logs? Are there any info?

torn sphinx Apr 10, 2021, 9:30 PM

#

this```
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
SELECT "rowid",* FROM "main"."balance" WHERE ("rowid" LIKE '%765322777329664089%' ESCAPE '' OR "user_id" LIKE '%765322777329664089%' ESCAPE '' OR "balance" LIKE '%765322777329664089%' ESCAPE '') ORDER BY "balance" DESC LIMIT 0, 49999;
SELECT COUNT() FROM "main"."balance" WHERE ("rowid" LIKE '%765322777329664089%' ESCAPE '' OR "user_id" LIKE '%765322777329664089%' ESCAPE '' OR "balance" LIKE '%765322777329664089%' ESCAPE '')
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
SELECT COUNT() FROM "main"."balance" WHERE ("rowid" LIKE '%765322777329664089%' ESCAPE '' OR "user_id" LIKE '%765322777329664089%' ESCAPE '' OR "balance" LIKE '%765322777329664089%' ESCAPE '')
SELECT "rowid",* FROM "main"."balance" WHERE ("rowid" LIKE '%765322777329664089%' ESCAPE '' OR "user_id" LIKE '%765322777329664089%' ESCAPE '' OR "balance" LIKE '%765322777329664089%' ESCAPE '') ORDER BY "balance" DESC LIMIT 0, 49999;

prisma girder Apr 10, 2021, 9:31 PM

#

No inserts here, how about error log?

torn sphinx Apr 10, 2021, 9:31 PM

#

one sec

#

nothing

#

its literly clear

north locust Apr 10, 2021, 9:33 PM

#

what did i do wrong?

import discord
from discord.ext import commands
from cogs.listvar import scritch

import firebase_admin
from firebase_admin import credentials
from firebase_admin import db

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
firebase_admin.initialize_app(cred)
ref = db.reference("https://whiskeybotdatabase-default-rtdb.firebaseio.com/")
def setup(bot):
    bot.add_cog(socialv2(bot))

class socialv2(commands.Cog):
    """A couple of simple commands."""
    def __init__(self, bot: commands.Bot):
        self.bot = bot
        print('loading socialv2 cog')
    
    @commands.command(name='boop', description='boop someone', alias=['boops', 'Boop', 'Boops'])
    async def boop(self, ctx):
        ref.set({
            "Books":
            {
                "Best_Sellers": -1
            }
        })

        ref = db.reference("https://whiskeybotdatabase-default-rtdb.firebaseio.com/Books/Best_Sellers")
        import json
        with open("book_info.json", "r") as f:
            file_contents = json.load(f)

        for key, value in file_contents.items():
            ref.push().set(value)

Exception has occurred: ExtensionFailed
Extension 'cogs.socialv2' raised an error: ValueError: Invalid database URL: "None". Database URL must be a non-empty URL string.```

prisma girder Apr 10, 2021, 9:36 PM

#

torn sphinx its literly clear

Are you sure that there are no exceptions? Have you tried to run it using debugger?

torn sphinx Apr 10, 2021, 9:36 PM

#

prisma girder Are you sure that there are no exceptions? Have you tried to run it using debugg...

no IDK what that is... like the VS code one??

prisma girder Apr 10, 2021, 9:36 PM

#

torn sphinx no IDK what that is... like the VS code one??

I am using PyCharm for this

torn sphinx Apr 10, 2021, 9:37 PM

#

prisma girder I am using PyCharm for this

oh i hate pycharm

north locust Apr 10, 2021, 9:38 PM

#

the vscode one would work

torn sphinx Apr 10, 2021, 9:38 PM

#

ok

#

lemme try

prisma girder Apr 10, 2021, 9:39 PM

#

north locust what did i do wrong? ```py import discord from discord.ext import commands from ...

You have None somewhere where you need to have str

#

Is this whole code?

torn sphinx Apr 10, 2021, 9:39 PM

#

i have to go eat atm but i will be back soon

north locust Apr 10, 2021, 9:40 PM

#

yes it is

#

also

#

i couldnt find it lol

prisma girder Apr 10, 2021, 9:40 PM

#

north locust also

It can be default argument

north locust Apr 10, 2021, 9:41 PM

#

oops i think i forgot this code

default_app = firebase_admin.initialize_app(cred_object, {
    'databaseURL':databaseURL
    })```

#

i found this ina guide

twilit oyster Apr 10, 2021, 9:41 PM

#

What would be better? Having a table for every person and then in that table every server they are in or having a table for every server and then in that every person in that server?

north locust Apr 10, 2021, 9:42 PM

#

north locust oops i think i forgot this code ```py default_app = firebase_admin.initialize_ap...

also do i put the url in the ' ' or after the :

north locust Apr 10, 2021, 9:43 PM

#

twilit oyster What would be better? Having a table for every person and then in that table eve...

probially per person.

#

how many people?

twilit oyster Apr 10, 2021, 9:43 PM

#

It could be any number

#

Since it is a discord bot

proven arrow Apr 10, 2021, 9:43 PM

#

twilit oyster What would be better? Having a table for every person and then in that table eve...

A table per person or guild is inefficient

#

It’s not recommended

twilit oyster Apr 10, 2021, 9:43 PM

#

proven arrow A table per person or guild is inefficient

What way should I do it then?

proven arrow Apr 10, 2021, 9:44 PM

#

Have a table of users, a table for guilds

#

And then a table in the middle if you want it so a user can be in many guilds.

north locust Apr 10, 2021, 9:44 PM

#

default_app = firebase_admin.initialize_app(cred_object, {
    'databaseURL':https://whiskeybot[redacted].firebaseio.com
    })```
or
```py
default_app = firebase_admin.initialize_app(cred_object, {
    'https://whiskeybot[redacted].firebaseio.com':databaseURL
    })```
which one is correct?

twilit oyster Apr 10, 2021, 9:44 PM

#

How would that be more efficient?

proven arrow Apr 10, 2021, 9:45 PM

#

twilit oyster How would that be more efficient?

This is why it’s not recommended #databases message

#

Those are just some reasons. Also when your database grows it creates unnecessary complexity, and databases are not designed for that kind of design.

twilit oyster Apr 10, 2021, 9:46 PM

#

Hmm okay

north locust Apr 10, 2021, 9:46 PM

#

lol does anyone know which codebox is correct? ^

north locust Apr 10, 2021, 9:48 PM

#

north locust ```py default_app = firebase_admin.initialize_app(cred_object, { 'databaseUR...

@prisma girder

proven arrow Apr 10, 2021, 9:48 PM

#

Don’t you just pass it the credentials?

north locust Apr 10, 2021, 9:49 PM

#

i pass the credentials in a json file

proven arrow Apr 10, 2021, 9:49 PM

#

What’s the dict for

north locust Apr 10, 2021, 9:49 PM

#

which dict?

#

the cred_object?

proven arrow Apr 10, 2021, 9:50 PM

#

I see one dict there, so obviously that one.

north locust Apr 10, 2021, 9:50 PM

#

the cred_object is a var defined above to tell it where the cred file is

#

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
default_app = firebase_admin.initialize_app(cred, {
    'databaseURL':databaseURL
    })```

proven arrow Apr 10, 2021, 9:50 PM

#

What do the docs say on how to use that? Do you have a link?

north locust Apr 10, 2021, 9:51 PM

#

im using thus https://www.freecodecamp.org/news/how-to-get-started-with-firebase-using-python/

proven arrow Apr 10, 2021, 9:53 PM

#

https://firebase.google.com/docs/reference/admin/python/firebase_admin

Firebase

firebase_admin module | Firebase

#

It’s explained there, if you scroll to that method

north locust Apr 10, 2021, 9:55 PM

#

so i got the code

#

that looks like

firebase_admin.initialize_app(credential=None, options=None, name='[DEFAULT]')```

#

do i put databaseURL into options?

torn sphinx Apr 10, 2021, 9:56 PM

#

torn sphinx i have to go eat atm but i will be back soon

im back

proven arrow Apr 10, 2021, 9:56 PM

#

It’s not required no. They are all optional args you can give

#

Just give the creds

north locust Apr 10, 2021, 9:57 PM

#

ok how do i add the url tho

north locust Apr 10, 2021, 9:58 PM

#

proven arrow Just give the creds

where do i put the url so i can use the database?

north locust Apr 10, 2021, 9:59 PM

#

proven arrow Just give the creds

hows this look?
the new code

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
firebase_admin.initialize_app(credential=cred, options=None, name='[DEFAULT]')```

proven arrow Apr 10, 2021, 9:59 PM

#

https://firebase.google.com/docs/database/admin/start

Firebase

Introduction to the Admin Database API | Firebase Realtime Databa...

#

There’s examples there, which I sent you earlier today as well.

#

It has the code too so you should maybe take a look at the links people send

north locust Apr 10, 2021, 10:01 PM

#

i looked at the link you sent

#

i came up with this code

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
firebase_admin.initialize_app(credential=cred, {
    'databaseURL': 'https://whiskeybotdatabase-default-rtdb.firebaseio.com'
})```
but now im getting this error
```Extension 'cogs.socialv2' raised an error: SyntaxError: positional argument follows keyword argument (socialv2.py, line 10)```

#

so i apparently mistyped something here is the code i got to work

import firebase_admin
from firebase_admin import credentials
from firebase_admin import db

cred = credentials.Certificate("beta/cogs/whiskeybotdatabase-firebase-adminsdk-g9ckh-c52eb0824a.json")
firebase_admin.initialize_app(cred, {
    'databaseURL': 'https://whiskeybotdatabase-default-rtdb.firebaseio.com'
})

ref = db.reference("/")```

#

thanks

north locust Apr 10, 2021, 10:07 PM

#

proven arrow https://firebase.google.com/docs/database/admin/start

NameError: name 'db' is not defined
... im so confused now what do i define db with

north locust Apr 10, 2021, 10:28 PM

#

nevermind im dumb and accadentally deleted the database code : /

torn sphinx Apr 10, 2021, 10:38 PM

#

I still dont understand why this code wont do anything also than stop the code

cursor.execute("INSERT INTO balance (user_id, amount) VALUES (?, ?) ON CONFLICT(user_id) DO UPDATE SET balance = balance + ?;", [user, amount, amount])

north locust Apr 10, 2021, 10:43 PM

#

        users_ref.set({
    'alanisawesome': {
        'date_of_birth': 'June 23, 1912',
        'full_name': 'Alan Turing'
    },
    'gracehop': {
        'date_of_birth': 'December 9, 1906',
        'full_name': 'Grace Hopper'
    }
})```
is there a way that i can use variables in the json? so i can use inputs from the user?

proven arrow Apr 10, 2021, 10:53 PM

#

north locust ```py users_ref.set({ 'alanisawesome': { 'date_of_birth': 'J...

Use f strings

#

Or you can just replace it with a variable you define some where else

north locust Apr 10, 2021, 10:54 PM

#

like

        users_ref.set({
    f'var': {
        'date_of_birth': 'June 23, 1912',
        'full_name': 'Alan Turing'
    },
    'gracehop': {
        'date_of_birth': 'December 9, 1906',
        'full_name': 'Grace Hopper'
    }
})```?

proven arrow Apr 10, 2021, 10:56 PM

#

Yeah

north locust Apr 10, 2021, 10:59 PM

#

it didnt work

proven arrow Apr 10, 2021, 11:00 PM

#

Oh yeah I missed that

#

The variable needs to be in curly braces

signal cloak Apr 11, 2021, 12:07 AM

#

I try to get a field from an inner object in the mongo DB

#

but I cant

#

x = collection.find_one({"notes.id": "1e5d719d"})
print(x["notes"]["title"])```

#

and with dot

#

but nothing works

north locust Apr 11, 2021, 12:39 AM

#

proven arrow The variable needs to be in curly braces

oh so like

 users_ref.set({
    f'{var}': {
        'date_of_birth': 'June 23, 1912',
        'full_name': 'Alan Turing'
    },
    'gracehop': {
        'date_of_birth': 'December 9, 1906',
        'full_name': 'Grace Hopper'
    }
})```

prisma girder Apr 11, 2021, 12:53 AM

#

signal cloak I try to get a field from an inner object in the mongo DB

How about it?

x = collection.find_one({"notes": {"id": "1e5d719d"}})
print(x["notes"]["title"])

signal cloak Apr 11, 2021, 12:53 AM

#

nop

#

not working

signal cloak Apr 11, 2021, 12:54 AM

#

prisma girder How about it? ```py x = collection.find_one({"notes": {"id": "1e5d719d"}}) print...

wdym btw?

prisma girder Apr 11, 2021, 1:05 AM

#

signal cloak wdym btw?

Not {"notes.id": ...} but {"notes": {"id": ... }}

prisma girder Apr 11, 2021, 1:12 AM

#

signal cloak ```py x = collection.find_one({"notes.id": "1e5d719d"}) print(x["notes"]["title"...

Okay, I am wrong, check this answer: https://stackoverflow.com/a/18515539

Stack Overflow

nested queries in pymongo using collection.find()

I would like to create a data base using mongodb and I am wondering how to query nested entities. For instance, let's assume we create a db as follows:

from pymongo import MongoClient
db = client['

signal cloak Apr 11, 2021, 1:13 AM

#

ye I was using an array

#

so

#

thats the prob

#

i ll switch to nested now

torn sphinx Apr 11, 2021, 4:55 AM

#

hi

#

Is there a way to select all the records from a table?
example:

SELECT * FROM table WHERE column = %s

The problem with that code is that it only returns with the first thing that matches the condition, but it doesn't return everything.

exotic sand Apr 11, 2021, 4:57 AM

#

SELECT *
FROM table;

Returns all records.

torn sphinx Apr 11, 2021, 4:58 AM

#

but not when I add WHERE

exotic sand Apr 11, 2021, 4:58 AM

#

Then it will return all records which meet you WHERE condition

torn sphinx Apr 11, 2021, 4:59 AM

#

it doesn't

exotic sand Apr 11, 2021, 5:00 AM

#

It definitely does. There is probably some other issue you are running into.

torn sphinx Apr 11, 2021, 5:22 AM

#

@trim wave how tall r u

trim wave Apr 11, 2021, 5:22 AM

#

<@&267629731250176001> can someone ban for harassment ^^

torn sphinx Apr 11, 2021, 5:22 AM

#

wtf

torn sphinx Apr 11, 2021, 5:24 AM

#

trim wave <@&267629731250176001> can someone ban for harassment ^^

@rugged locust #python-discussion
help #databases message

Discord

Discord - A New Way to Chat with Friends & Communities

Discord is the easiest way to communicate over voice, video, and text. Chat, hang out, and stay close with your friends and communities.

analog sigil Apr 11, 2021, 5:24 AM

#

@torn sphinx I'm not sure why you've decided to ask a random user personal details but please keep discussions relevant to the channel's topic.

torn sphinx Apr 11, 2021, 5:27 AM

#

lol

#

+--------------------+-----------+--------------------+--------------------+-------------+
| guildid            | expressive | physical             | mental             | occupational |
+--------------------+-----------+--------------------+--------------------+-------------+
| 781918826312892437 |      NULL |               NULL | 827911893447344139 |        NULL |
| 781918826312892437 |      NULL |               NULL | 827911893447344139 |        NULL |
| 781918826312892437 |      NULL |               NULL | 827911893447344139 |        NULL |
| 781918826312892437 |      NULL | 827911893447344139 |               NULL |        NULL |
| 781918826312892437 |      NULL | 781930115708747827 |               NULL |        NULL |
+--------------------+-----------+--------------------+--------------------+-------------+

Using SELECT expressive, physical, mental, occupational FROM levels WHERE guildid = %s returned (None, None, 827911893447344139, None)

#

Is there a way to select all the records from a table?
example:

SELECT * FROM table WHERE column = %s

The problem with that code is that it only returns with the first thing that matches the condition, but it doesn't return everything.

trim wave Apr 11, 2021, 5:30 AM

#

@torn sphinx btw what is your %s

#

condition

torn sphinx Apr 11, 2021, 5:31 AM

#

guildid

trim wave Apr 11, 2021, 5:31 AM

#

are there a list of ids you're looking for?

torn sphinx Apr 11, 2021, 5:33 AM

#

I think, it is a table with completely BIGINT columns, representing the id.

trim wave Apr 11, 2021, 5:34 AM

#

wait are you just trying to return all the rows?

#

im not sure what you mean by that

torn sphinx Apr 11, 2021, 5:35 AM

#

trim wave wait are you just trying to return all the rows?

Yes

trim wave Apr 11, 2021, 5:35 AM

#

then just use SELECT * FROM table

#

are you trying to just get specific columns?

#

returned

torn sphinx Apr 11, 2021, 5:36 AM

#

torn sphinx ```py +--------------------+-----------+--------------------+-------------------...

I try to get it to return all matches with guildid

trim wave Apr 11, 2021, 5:36 AM

#

oh ok guild id is just one number right

torn sphinx Apr 11, 2021, 5:37 AM

#

sure

trim wave Apr 11, 2021, 5:38 AM

#

and which row are you trying to match with the guildid

#

like are you trying to match a particular id under a column under guildid

#

or are you trying to match a constant guildid with a number in a different column

#

oh wait sorry my b i couldnt read the table properly cuz my screen was too small

#

i think you just do

SELECT * FROM table_name WHERE guildid = "id ur looking for"

#

it shouldnt return one value

#

but all the results

torn sphinx Apr 11, 2021, 5:41 AM

#

trim wave i think you just do ```mysql SELECT * FROM table_name WHERE guildid = "id ur lo...

I don't understand what you mean, lol, I mean match the guildid column with the id, exactly this.

trim wave Apr 11, 2021, 5:41 AM

#

??

#

yeah thats the code

torn sphinx Apr 11, 2021, 5:42 AM

#

yes, the problem now is that it only returns a tuple that represents a row

trim wave Apr 11, 2021, 5:42 AM

#

that means there is only one match

torn sphinx Apr 11, 2021, 5:43 AM

#

(None, None, 827911893447344139, None), knowing that the code is correct and the table is fine, I have no idea what the problem is.

trim wave Apr 11, 2021, 5:43 AM

#

hmm yeah i would assume only one match is found

torn sphinx Apr 11, 2021, 5:45 AM

#

yeah I hope it returns all the rows that match the condition but only returns what matches first.

#

and surely the search ends when it finds the first match. I'm getting something wrong.

keen rain Apr 11, 2021, 7:47 AM

#

hello guys can anyone help me with MongoDB?

lunar kettle Apr 11, 2021, 8:09 AM

#

while installing mysqlclient in virtaulevn i got this error

     command: /Users/mac/rohil/user_api_django/bin/python3 -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/setup.py'"'"'; __file__='"'"'/private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-pip-egg-info-trjspjto
         cwd: /private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/
    Complete output (12 lines):
    /bin/sh: mysql_config: command not found
    /bin/sh: mariadb_config: command not found
    /bin/sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/setup.py", line 15, in <module>
        metadata, options = get_config()
      File "/private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/setup_posix.py", line 65, in get_config
        libs = mysql_config("libs")
      File "/private/var/folders/w4/76p33pr92x59lq3slgfw6p200000gn/T/pip-req-build-z6i7lulh/setup_posix.py", line 31, in mysql_config
        raise OSError("{} not found".format(_mysql_config_path))
    OSError: mysql_config not found
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

prisma girder Apr 11, 2021, 2:08 PM

#

lunar kettle while installing mysqlclient in virtaulevn i got this error ```ERROR: Command e...

It seems that you don't have mysql_config command

celest rain Apr 11, 2021, 2:10 PM

#

just asking, is there a way i can change the output format of sqlite3? for example, for fetchall(), it sends this
[('a',), ('b',), ('c',), ('d',), ('e',), ('f',)]
how can i make it so it outputs:
a
b
c
d
e
f

Thanks!

prisma girder Apr 11, 2021, 2:13 PM

#

celest rain just asking, is there a way i can change the output format of sqlite3? for examp...

Are you talking about printing those data?

celest rain Apr 11, 2021, 2:13 PM

#

yes

#

and there would only be 1 value per column

prisma girder Apr 11, 2021, 2:14 PM

#

!e

data = [('a',), ('b',), ('c',), ('d',), ('e',), ('f',)]
for entry in data:
  print(*entry)

delicate fieldBOT Apr 11, 2021, 2:14 PM

#

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | a
002 | b
003 | c
004 | d
005 | e
006 | f

prisma girder Apr 11, 2021, 2:14 PM

#

delicate field <@690641855045697653> :white_check_mark: Your eval job has completed with return...

@celest rain

celest rain Apr 11, 2021, 2:14 PM

#

oh alright, thanks!!

celest rain Apr 11, 2021, 2:16 PM

#

prisma girder !e ```py data = [('a',), ('b',), ('c',), ('d',), ('e',), ('f',)] for entry in da...

just a question, what does the * in *entry do?

prisma girder Apr 11, 2021, 2:17 PM

#

celest rain just a question, what does the * in *entry do?

When you have iterable then * operator just unroll the values

#

!e

data = ["a", "b", "c"]
print(data)
print(*data)

delicate fieldBOT Apr 11, 2021, 2:18 PM

#

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | ['a', 'b', 'c']
002 | a b c

celest rain Apr 11, 2021, 2:18 PM

#

ah okay

#

thanks again!

torn sphinx Apr 11, 2021, 2:32 PM

#

Hi

#

Help me

#

Database required int array extensio

#

n

torn sphinx Apr 11, 2021, 3:01 PM

#

torn sphinx and surely the search ends when it finds the first match. I'm getting something ...

help

proven arrow Apr 11, 2021, 3:44 PM

#

torn sphinx help

Issue is not with the sql. So you need to show the full code including how you use it with python.

torn sphinx Apr 11, 2021, 3:46 PM

#

that's something I don't expect when the code is in good condition

proven arrow Apr 11, 2021, 3:47 PM

#

Are you going to show the code or not?

torn sphinx Apr 11, 2021, 3:48 PM

#

yes

#

lol

torn sphinx Apr 11, 2021, 3:52 PM

#

proven arrow Are you going to show the code or not?

yes, I'll just copy where I insert and select

#

Cause the rest I guess is unrelated

torn sphinx Apr 11, 2021, 4:10 PM

#

Help me pls

torn sphinx Apr 11, 2021, 4:15 PM

#

proven arrow Are you going to show the code or not?

levelchannels = await temp.execute(
                """CREATE TABLE IF NOT EXISTS levelchannels(
                guildid BIGINT,
                expressive BIGINT,
                physical BIGINT,
                mental BIGINT,
                occupational BIGINT)""")

requests = await obj.execute("SELECT * FROM levelchannels WHERE guildid = %s",
                                             (message.guild.id))
requests = await obj.fetchone()
print(requests)

proven arrow Apr 11, 2021, 4:16 PM

#

What does fetchone() tell you?

#

It’s literally in the name

torn sphinx Apr 11, 2021, 4:17 PM

#

lol

#

true

proven arrow Apr 11, 2021, 4:17 PM

#

You need fetchall()

torn sphinx Apr 11, 2021, 4:18 PM

#

now yes returns everything. thanks!

proven arrow Apr 11, 2021, 4:22 PM

#

torn sphinx Help me pls

With what?

torn sphinx Apr 11, 2021, 4:24 PM

#

@proven arrow Is there a way to group the tuples by column and that does not collect what would be NULL?
example:

((expressive column), (physical column), (mental column), (occupational column))

and that it is not like that

((None, None, 827911893447344139, None), (None, None, 827911893447344139, None), (None, None, 827911893447344139, None), (None, 827911893447344139, None, None), (None, 781930115708747827, None, None), (801137870622556181, None, None, None), (781930022374080562, None, None, None), (781930022374080562, None, None, None))

proven arrow Apr 11, 2021, 4:25 PM

#

I don’t understand what you mean

torn sphinx Apr 11, 2021, 4:28 PM

#

sort the columns in the tuple it will return, because normally it will return all the content of the columns in row order, But I would like it to be grouped or sorted by column and None does not appear.

proven arrow Apr 11, 2021, 4:30 PM

#

You can specify the columns you need instead of doing *

celest rain Apr 11, 2021, 4:35 PM

#

prisma girder When you have iterable then `*` operator just unroll the values

hello, am back, is there a way i can make sqlite3 do this (change the format from [('a',), ('b',), ('c',), ('d',), ('e',), ('f',)] to
a
b
c
d
e
f?

#

thanks!

proven arrow Apr 11, 2021, 4:44 PM

#

celest rain hello, am back, is there a way i can make sqlite3 do this (change the format fro...

Do you want a list of those letters?

torn sphinx Apr 11, 2021, 5:51 PM

#

proven arrow With what?

prisma girder Apr 11, 2021, 6:01 PM

#

celest rain hello, am back, is there a way i can make sqlite3 do this (change the format fro...

I don't know what are you exactly asking for pithink
Can you show your code?

celest rain Apr 11, 2021, 6:01 PM

#

prisma girder I don't know what are you exactly asking for <:pithink:652247559909277706> Can ...

nvm, i found a fix (hopefully) :D

prisma girder Apr 11, 2021, 6:02 PM

#

celest rain nvm, i found a fix (hopefully) :D

Okay, nice!

torn sphinx Apr 11, 2021, 6:39 PM

#

torn sphinx Apr 11, 2021, 7:07 PM

#

can someone help me in #help-chili, thanks

twilit oyster Apr 11, 2021, 7:14 PM

#

if I need to store data inside each user, should I do it where I create a Table for each guild or create a table for each user? I will be storing data inside of it such as XP.

#

I am using mongodb

torn sphinx Apr 11, 2021, 8:07 PM

#

valid vault Apr 11, 2021, 10:07 PM

#

I’m constantly getting a “Too many connections” error using aiomysql but I’m sure all my connections are being closed after use and the max is set to 225 which should be plenty - do I need to wrap all my connections things in try/except to make sure it always closes connections or something? or is there a way to force a connection to time out?

#

Not really sure how to debug the issue. Any ideas?

wind sand Apr 11, 2021, 10:41 PM

#

hello, I am using asyncpg and upon running on query which is await cursor.execute("DELETE FROM roles WHERE role = $1 and guild = $2 and type $3", role.id, guild, type) I get an asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$3" Everything looks correct so I am not sure what I am missing. Any ideas?

wind sand Apr 11, 2021, 10:44 PM

#

valid vault I’m constantly getting a “Too many connections” error using aiomysql but I’m sur...

are you sure your application closing connections to the server when they are no longer needed?

valid vault Apr 11, 2021, 10:47 PM

#

wind sand are you sure your application closing connections to the server when they are no...

yeah running conn.close() in every function - actually just been told about connection pooling which I might look into now

wind sand Apr 11, 2021, 10:48 PM

#

yeah, look into connection pooling, should fix your issue

valid vault Apr 11, 2021, 10:48 PM

#

brilliant, thanks for that!

wind sand Apr 11, 2021, 10:48 PM

#

if it is still a problem, you can increase the number of allowed connections using the SET GLOBAL command and/or decrease the wait_timeout

valid vault Apr 11, 2021, 10:50 PM

#

yeah I tried to increase the connections and the same issue was caused but after a slightly longer period of time haha. wait_timeout was something I saw, might have to play around with that if pools don't help

#

thanks for the help!

wind sand Apr 11, 2021, 10:50 PM

#

np!

lost pebble Apr 11, 2021, 11:13 PM

#

to connect to a remote database via ssh tunnel should I just ssh -L or use the sshtunnel library

#

ssh-L seems like it would have the advantage of not putting login info in a python script

#

other may be the proper way though its whats in all the tuts

summer nimbus Apr 12, 2021, 3:46 AM

#

Hey guys how to make the code of leaderboard working?
https://mystb.in/CheapAolArchitecture.python

#

like how do i find keys only having guild id as ctx.guild.id?

torn sphinx Apr 12, 2021, 3:54 AM

#

await guild.update_one({"guildid":message.guild.id}, {"$set": {"deleted_message_channel":message.channel.id, "content": {"deleted_message_content":message.content, "deleted_message_author":message.author}}}, upsert=True)

bson.errors.InvalidDocument: cannot encode object: <Member id=457137840820715522 name='dendo' discriminator='6341' bot=False nick=None guild=<Guild id=no name='no' shard_id=None chunked=True member_count=2873>>, of type: <class 'discord.member.Member'>

Why am I getting this error and what does it mean?

cyan yacht Apr 12, 2021, 4:02 AM

#

torn sphinx ```py await guild.update_one({"guildid":message.guild.id}, {"$set": {"deleted_me...

Try using message.author.id

torn sphinx Apr 12, 2021, 4:08 AM

#

cyan yacht Try using message.author.id

thanks

uneven lion Apr 12, 2021, 8:07 AM

#

(MONGODB / PYMONGO) How would i make commands like this: Welcome channel set, goodbye channel set, and prefix set? Every server would be different btw | ping if answer

ornate fulcrum Apr 12, 2021, 8:24 AM

#

https://paste.pythondiscord.com/dequcumefi.py
This is piece of cog file from Message of the day bot. User can do !addline "thisismsgoftheday" and line gets saved into database. The bot posts random line from database daily at specific time. Problem with this !showdatabase is it posts every line like one at a time, and i want the bot post the lines in a single post instead. So all lines from database should be inside a single post instead of multiple. How to do that?

tranquil totem Apr 12, 2021, 11:44 AM

#

Code:```py
@commands.command()
async def enable(self, ctx, feature = None, channel: discord.TextChannel = None):
if feature == "suggest":
if channel is None:
embed = discord.Embed(description = "You need to mention a channel also", color = 0xf04947)
await ctx.send(embed=embed)
else:
collection.update_one({"_id": ctx.guild.id}, {"$set": {"suggestion_channel": channel } } )
await ctx.send(f"Suggestions enabled on {channel.mention}")

Error:```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InvalidDocument: cannot encode object: <TextChannel id=831048029276340224 name='suggestions' position=8 nsfw=False news=False category_id=831045454749761566>, of type: <class 'discord.channel.TextChannel'>

#

why tho

#

someone help me pls

#

asap

prisma girder Apr 12, 2021, 11:49 AM

#

tranquil totem Code:```py @commands.command() async def enable(self, ctx, feature = Non...

Cannot encode object of type discord.channel.TextChannel, try to parse it into str pithink

tranquil totem Apr 12, 2021, 11:52 AM

#

prisma girder Cannot encode object of type `discord.channel.TextChannel`, try to parse it into...

but for another piece of code ill have to use the suggestion_channel id in order to send msgs to that channel
how do i make it such that it only takes integer values that i can then use to get the channel and send a msg on it?

round agate Apr 12, 2021, 11:53 AM

#

Hello there! A little suggestion please! I was using Moon Modeller software for modelling E-R, unfortunately it's paid and with the free version you can have only 10 tables. Any suggestion for a free tool for relational databases? Thanks!

prisma girder Apr 12, 2021, 11:53 AM

#

tranquil totem but for another piece of code ill have to use the suggestion_channel id in order...

Idk, try to ask on #discord-bots channel

tranquil totem Apr 12, 2021, 11:54 AM

#

its related to databases tho so ill ask it here

#

how to change the datatype of a value to int

#

and then post it

#

i.e. { "channel_id": ...} which can only take int values

prisma girder Apr 12, 2021, 11:58 AM

#

tranquil totem how to change the datatype of a value to int

Use int like int(variable)

#

!e

num = "10"
print(type(num), num)
num = int(num)
print(type(num), num)

delicate fieldBOT Apr 12, 2021, 11:58 AM

#

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | <class 'str'> 10
002 | <class 'int'> 10

tranquil totem Apr 12, 2021, 12:00 PM

#

prisma girder Use `int` like `int(variable)`

so does int automatically ignore the other symbols and characters and only take into the numbers into account?

prisma girder Apr 12, 2021, 12:01 PM

#

tranquil totem so does `int` automatically ignore the other symbols and characters and only tak...

!e

valid_num = "10"
invalid_num = "10!"
another_one = "10xyz"
print("valid_num", int(valid_num))
print("invalid_num", int(invalid_num))
print("another_one", int(another_one))

delicate fieldBOT Apr 12, 2021, 12:01 PM

#

@prisma girder :x: Your eval job has completed with return code 1.

001 | valid_num 10
002 | Traceback (most recent call last):
003 |   File "<string>", line 5, in <module>
004 | ValueError: invalid literal for int() with base 10: '10!'

prisma girder Apr 12, 2021, 12:02 PM

#

tranquil totem so does `int` automatically ignore the other symbols and characters and only tak...

As you can see, nope

austere portal Apr 12, 2021, 12:03 PM

#

what is the postgresql uri?

prisma girder Apr 12, 2021, 12:06 PM

#

austere portal what is the postgresql uri?

Connection URI is a string which contains several informations like host, port, database name and others

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp

See more here: https://www.postgresql.org/docs/9.3/libpq-connect.html#SECT3

PostgreSQL Documentation

Database Connection Control Functions

austere portal Apr 12, 2021, 12:07 PM

#

Thanks

tranquil totem Apr 12, 2021, 12:08 PM

#

okay

#

okay

#

simpler question

#

how do you store a channel as a value in a database

#

that can be used in the code to send a msg in it

burnt turret Apr 12, 2021, 12:30 PM

#

Store the channel's ID

#

You can retrieve the channel object later with guild.get_channel/bot.get_channel

tranquil totem Apr 12, 2021, 1:03 PM

#

burnt turret Store the channel's ID

is there a way to extract the id?

#

you know how discord.Member always works with the id or the mention

#

is there a way to do the same with discord.TextChannel?

cedar surge Apr 12, 2021, 1:26 PM

#

with mysql can i use brackets for the optional things.

    `id` INTEGER [PRIMARY KEY] [AUTO_INCREMENT],
```  like that?

prisma girder Apr 12, 2021, 1:30 PM

#

cedar surge with mysql can i use brackets for the optional things. ```mysql `id` INTEGE...

What do you mean by optional?

torn sphinx Apr 12, 2021, 1:47 PM

#

hi

thorn geode Apr 12, 2021, 1:47 PM

#

tranquil totem is there a way to do the same with `discord.TextChannel`?

You just change discord.Member to discord.TextChannel?

torn sphinx Apr 12, 2021, 1:48 PM

#

I have a problem execute this

"DELETE expressive, physical, mental, occupational FROM levelchannels WHERE guildid = %s"

return 109, "Unknown table 'expressive' in MULTI DELETE"

#

in the row only one column has a value, the others are NULL

#

and I would like to delete the whole row where the id and column is located

tranquil totem Apr 12, 2021, 2:02 PM

#

thorn geode You just change discord.Member to discord.TextChannel?

yeah ig

#

but i get an error here

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'Int64' object has no attribute 'send'

#

hold on

thorn geode Apr 12, 2021, 2:03 PM

#

You need to convert it to a text channel object

tranquil totem Apr 12, 2021, 2:04 PM

#

yeah

tranquil totem Apr 12, 2021, 2:04 PM

#

thorn geode You need to convert it to a text channel object

so i get the channel with this id from the guild isnt it?

thorn geode Apr 12, 2021, 2:22 PM

#

Guild or bot, yea

#

This would be better suited to #discord-bots

#

@tranquil totem ^

green raptor Apr 12, 2021, 2:36 PM

#

hey i am using sqlite to store links of gifs, i am sometime getting "none" instead of link (gif)

prisma girder Apr 12, 2021, 2:40 PM

#

green raptor hey i am using sqlite to store links of gifs, i am sometime getting "none" inste...

Can you paste any code? Hard to say without that

green raptor Apr 12, 2021, 2:41 PM

#

how i access db

    fetch = sql.execute("SELECT Url FROM tbl_Hug ORDER BY RANDOM() LIMIT 1;").fetchall()
    conn.commit()
    result = fetch[0][0]
    return result```

#

how i send the gif in chat
await ctx.send(f"{url}")

cedar surge Apr 12, 2021, 2:41 PM

#

prisma girder What do you mean by *optional*?

i meant to say datatypes

green raptor Apr 12, 2021, 2:42 PM

#

@prisma girder i added some info

prisma girder Apr 12, 2021, 2:43 PM

#

cedar surge i meant to say datatypes

Use rather DEFAULT NULL pithink Make sure that column is nullable

prisma girder Apr 12, 2021, 2:43 PM

#

green raptor how i send the gif in chat ```await ctx.send(f"{url}")```

Still don't know how you assign url variable

green raptor Apr 12, 2021, 2:44 PM

#

prisma girder Still don't know how you assign `url` variable

url = await get_hug_gif() i use my function from above to access db and get random url

prisma girder Apr 12, 2021, 2:45 PM

#

green raptor ```url = await get_hug_gif()``` i use my function from above to access db and ge...

Can you print full result of fetch?

green raptor Apr 12, 2021, 2:48 PM

#

prisma girder Can you print full result of `fetch`?

sometimes none sometimes url

prisma girder Apr 12, 2021, 2:48 PM

#

green raptor sometimes none sometimes url

Are you sure that your table doesn't contain NULL values?

green raptor Apr 12, 2021, 2:49 PM

#

hahahahahaha

#

omg

#

bro sorry for being stupid, i forgot that i wanted to fill them

#

omfg haha

prisma girder Apr 12, 2021, 2:49 PM

#

green raptor bro sorry for being stupid, i forgot that i wanted to fill them

Here you are 🙂

green raptor Apr 12, 2021, 2:49 PM

#

https://tenor.com/view/certain-magical-index-anime-ugh-dumb-baka-gif-13706740

Tenor

hardy swan Apr 12, 2021, 2:49 PM

#

green raptor https://tenor.com/view/certain-magical-index-anime-ugh-dumb-baka-gif-13706740

@green raptor https://tenor.com/view/epic-embed-fail-gypsy-crusader-caption-joker-meme-gif-20224902

Tenor

prisma girder Apr 12, 2021, 2:50 PM

#

green raptor https://tenor.com/view/certain-magical-index-anime-ugh-dumb-baka-gif-13706740

Don't worry, obvious mistake

green raptor Apr 12, 2021, 2:51 PM

#

thank u for help! ❤️

prisma girder Apr 12, 2021, 2:55 PM

#

green raptor thank u for help! ❤️

Glad to help!

tranquil totem Apr 12, 2021, 3:09 PM

#

thorn geode <@719807591869317201> ^

thanks

craggy pawn Apr 12, 2021, 3:13 PM

#

would a mysql database wreck my 2gb rpi?

prisma girder Apr 12, 2021, 3:36 PM

#

craggy pawn would a mysql database wreck my 2gb rpi?

I had Postgres inside Docker container and I/O operations kills the RPi

brazen charm Apr 12, 2021, 3:37 PM

#

cuz of that slow SD speed

prisma girder Apr 12, 2021, 3:40 PM

#

That's right

craggy pawn Apr 12, 2021, 3:42 PM

#

would a usb 3 drive be any faster?

torn sphinx Apr 12, 2021, 3:58 PM

#

torn sphinx I have a problem execute this ```py "DELETE expressive, physical, mental, occupa...

help

prisma girder Apr 12, 2021, 4:00 PM

#

craggy pawn would a usb 3 drive be any faster?

CPU still could be a problem

prisma girder Apr 12, 2021, 4:01 PM

#

torn sphinx I have a problem execute this ```py "DELETE expressive, physical, mental, occupa...

Never used this kind of query, always did something like

DELETE FROM table WHERE column = value

cedar surge Apr 12, 2021, 4:07 PM

#

prisma girder Use rather `DEFAULT NULL` <:pithink:652247559909277706> Make sure that column i...

why would i want a Id column to be null?

prisma girder Apr 12, 2021, 4:10 PM

#

cedar surge why would i want a Id column to be null?

Why would you want to make id column optional?

cedar surge Apr 12, 2021, 4:13 PM

#

hmm?

#

you said this "Use rather DEFAULT NULL pithink Make sure that column is nullable"

proven arrow Apr 12, 2021, 4:18 PM

#

cedar surge hmm?

To your intiial question the optional arguments don’t need to be in brackets.

#

You can leave them out or put them in your query without the brackets.

cedar surge Apr 12, 2021, 4:20 PM

#

proven arrow You can leave them out or put them in your query without the brackets.

ok thanks

scarlet geyser Apr 12, 2021, 6:45 PM

#

(MONGODB / PYMONGO) How would i make commands like this: Welcome channel set, goodbye channel set, and prefix set? Every server would be different btw | ping if answer

torn sphinx Apr 12, 2021, 6:47 PM

#

scarlet geyser (MONGODB / PYMONGO) How would i make commands like this: Welcome channel set, go...

idk, learn sql and think what tables and columns you need.

thorn geode Apr 12, 2021, 7:14 PM

#

Note that there is no SQL

jagged cove Apr 12, 2021, 7:24 PM

#

how do database APIs work and can i still use them with SQL?

#

or does that depend on the API

median swift Apr 12, 2021, 7:37 PM

#

I need some help with PGAdmin4, I have a table but I want to reset the primary keys so the start from 0 again. I had around 10 records and deleted them though the PK starts at 11 now

jagged cove Apr 12, 2021, 7:40 PM

#

nvm got an answer from another source, thanks anyhow

ebon skiff Apr 12, 2021, 7:44 PM

#

scarlet geyser (MONGODB / PYMONGO) How would i make commands like this: Welcome channel set, go...

It's better to use motor with mongodb

#

You'd have to make some queries to save the data, i wanna know where you at now, then i can help you better.

proven arrow Apr 12, 2021, 7:46 PM

#

median swift I need some help with PGAdmin4, I have a table but I want to reset the primary k...

You can truncate the table

median swift Apr 12, 2021, 7:47 PM

#

proven arrow You can truncate the table

Don't really want to do that mate, as the same issue occurs in other things 🙂

#

I think I found a solution

proven arrow Apr 12, 2021, 7:48 PM

#

median swift Don't really want to do that mate, as the same issue occurs in other things 🙂

What?

median swift Apr 12, 2021, 7:48 PM

#

Doesn't matter

proven arrow Apr 12, 2021, 7:49 PM

#

Ok 👍😆

distant radish Apr 12, 2021, 8:20 PM

#

for the issue I was having where python wasn't connecting to the database on mac

#

it's because xampp's default installation on mac was as a VM

#

just in case anyone is looking for a fix to a problem similar to mine

torn sphinx Apr 12, 2021, 10:21 PM

#

How can I make a range table?

#

I try to see the ones that have a bigger int and I would like to select maximum 10 for a classification table

storm sierra Apr 13, 2021, 2:25 AM

#

    async def whitelist(self, ctx, user_id=None):
        self.client.db = await aiosqlite.connect('whitelist.db')
        cursor = await self.client.db.cursor()
        result = await cursor.fetchall()
        
        await cursor.execute(f"SELECT wl FROM whitelist WHERE guild_id == {ctx.guild.id}")
        print(result)
        
        await cursor.execute("INSERT INTO whitelist(wl, guild_id) VALUES (?,?)", (user_id, ctx.guild.id, ))
        await self.client.db.commit()``` why result is []?

#

I also tried printing result in other command almost the same and result is [] too, I also noticed it inserts but result is []

burnt turret Apr 13, 2021, 2:45 AM

#

You're fetching before executing a query

#

Also you shouldn't be using f-strings with SQL queries, this message explains why

storm sierra Apr 13, 2021, 3:01 AM

#

burnt turret You're fetching before executing a query

How?

#

Also why? Even if I run the command twice its []

burnt turret Apr 13, 2021, 3:25 AM

#

https://why.life-is-pa.in/HJy7PV you've done the fetch before querying

HJy7PV.png

#

so there's nothing to fetch, so it gives you []

#

just fetch after the query

storm sierra Apr 13, 2021, 3:26 AM

#

Ok

#

Worked xd

ornate fulcrum Apr 13, 2021, 5:35 AM

#

https://paste.pythondiscord.com/yadicotule.py
Hey, this is Message of the day bot. It posts random message from database at specific time set by user. This piece of code is from cog that checks the time from database each 5 sec(will change this into 10min, 5sec is for testing) and should make the post with sleep.until. However, this thing doesn't sleep at all. It posts each 5 sec it lets the loop go through. What would be the correct way to do this? Bot shouldn't post when the time doesn't match.

upbeat slate Apr 13, 2021, 7:30 AM

#

Hello, IDK if this is the right place to ask, but how can i get many PY scripts to read a file that is being edited IRT by another script, and how can i get multiple files not to edit a file at the same time? Also is it possible to import a variable without having to run the whole program inside another without using text files?

#

ik that is alot of questions but i need answers

#

again sorry if I asked in the wrong channel

dull cypress Apr 13, 2021, 7:39 AM

#

what are you trying to accomplish?

upbeat slate Apr 13, 2021, 7:55 AM

#

dull cypress what are you trying to accomplish?

I am trying to do a discord bot that has alot of background programs for better eror spotting and to avoid threads

dull cypress Apr 13, 2021, 7:56 AM

#

this is architecture issue

upbeat slate Apr 13, 2021, 8:01 AM

#

dull cypress this is architecture issue

how can i solve it?

#

like how can i get to my goal

#

is it even possible?

dull cypress Apr 13, 2021, 8:33 AM

#

I need to know your project scope and goals, and implementation constraints. it's not uncommon for the issue you're stuck with being the results of poor implementation.

upbeat slate Apr 13, 2021, 8:40 AM

#

I see

#

i didnt actually start doing it

#

i tried it once before

#

but it didnt go well

#

I basically want to make an RPG like bot, just for practice, and I i want it to have idle miners

#

to make those work, I thought of a system that checks how many miners an ID has, multiplies that number by some amout, and adds it to the ID's money

#

this is why I need many programs to edit at once

#

To make the bot faster, I thought of an autosave system that exports all JSON vars and stuff from the programs and edits them

#

cuz editing at every action is long asf

#

@dull cypress

elfin palm Apr 13, 2021, 12:40 PM

#

hello

#

i'm getting an error 'NoneType' object is not subscriptable when i trying to fetch cursor py return cursor.fetchone()[0]

#

but this cursor shouldn't return none

#

 cursor.execute(f"SELECT 1 FROM information_schema.tables WHERE table_name = '{table_name}'")

#

if the table doesn't exists should return en empty tuple right?

#

where i cna find the values?

slow kettle Apr 13, 2021, 1:09 PM

#

does anybody have pointers to guides for setting up a DB eg postgres on Windows ? i have been avoiding using databases for too long :'(

pliant robin Apr 13, 2021, 1:35 PM

#

Hey i was making a bot on replit, how do i open my base.sql database file?

#

On opening the file in db browser after downloading the zip folder, i noticed the entries weren't properly updated, whats wrong?

cyan yacht Apr 13, 2021, 2:38 PM

#

You have to commit whenever you update

gaunt garden Apr 13, 2021, 2:39 PM

#

Error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.```

Code:
```py
db = sqlite3.connect('punish.sqlite')
c = db.cursor()
results = c.execute("SELECT * FROM tempbans WHERE active = ?", ('True',)).fetchall()
c.close()
db.close()

Sorry if this is a noobish question, but i don't know why im getting this error...

#

holdup, im in the wrong location. sorry should be looking at mutes, not tempbans.

pliant robin Apr 13, 2021, 2:42 PM

#

cyan yacht You have to commit whenever you update

i did, when i see enter the bot command to check balance, it's changed and updated but the database file isn

#

t

delicate fieldBOT Apr 13, 2021, 3:29 PM

#

Hey @lunar kettle!

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

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

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

https://paste.pythondiscord.com

lunar kettle Apr 13, 2021, 3:31 PM

#

i cant install mysqlclient in windows pc

  Downloading mysqlclient-2.0.3.tar.gz (88 kB)
     |████████████████████████████████| 88 kB 99 kB/s
Using legacy 'setup.py install' for mysqlclient, since package 'wheel' is not installed.
Installing collected packages: mysqlclient
    Running setup.py install for mysqlclient ... error

it is installing tar.gz file

quick saddle Apr 13, 2021, 9:05 PM

#

#

Trying to figure out what this is doing in some custom programs I'm trying to understand

#

At this point I'm guessing it's binary?

prisma girder Apr 13, 2021, 9:08 PM

#

quick saddle At this point I'm guessing it's binary?

It looks like binary format and I quickly checked that .ldb extension is a Microsoft Access Lock File

torn sphinx Apr 13, 2021, 9:11 PM

#

Question time

I've a user table and a settings table, the settings table has columns that are set to NOT NULL and some can be NULL.

In user i've a FK that refers to settings, does this FK needs to be set to NOT NULL aswell or not?

#

I'm quite confused

#

The settings table has columns that are set to NOT NULL, which means they NEED to provide data to set it, but how can I do this if a user registers first, then later on creates their settings?

quick saddle Apr 13, 2021, 9:13 PM

#

So this file is used to unlock a mdb file?

prisma girder Apr 13, 2021, 9:14 PM

#

quick saddle So this file is used to unlock a mdb file?

Idk, never used MS Access

quick saddle Apr 13, 2021, 9:15 PM

#

Because I've been trying to open a. Mdb file to no success so I'm guessing this is the key

molten hound Apr 13, 2021, 9:15 PM

#

lunar kettle i cant install mysqlclient in windows pc ```Collecting mysqlclient Downloading...

run this commands

#

sudo apt install libpq-dev python3-dev

#

sudo apt install build-essential
sudo apt-get install python3-dev default-libmysqlclient-dev

quick saddle Apr 13, 2021, 9:38 PM

#

#

OK here's where I'm at

#

What is this code on the right? Is it decompiled incorrectly?

proven arrow Apr 13, 2021, 10:35 PM

#

torn sphinx Question time I've a **user** table and a **settings** table, the settings tabl...

The FK in the users table can be null or not null (either is fine). It’s how you decide your apps logic to be.
If you set it to allow null then a user can exist without a settings record. If you set it to be required then a user must always have a settings record.

glad marten Apr 14, 2021, 12:47 AM

#

is it necessary to create a class for your table if all you are doing is accessing the data and not creating or writing

#

new to flask

#

or better yet, does someone have a good example of connecting to an existing database and setting up queries

candid coral Apr 14, 2021, 1:21 AM

#

im using mysql with python to write data to a database, but counting affected rows returns -1. Any help? what causes this?

prisma girder Apr 14, 2021, 1:22 AM

#

glad marten is it necessary to create a class for your table if all you are doing is accessi...

I am using SQLAlchemy with Flask and I have model classes for every table

prisma girder Apr 14, 2021, 1:22 AM

#

candid coral im using mysql with python to write data to a database, but counting affected ro...

Can you paste some code?

glad marten Apr 14, 2021, 1:23 AM

#

prisma girder I am using SQLAlchemy with Flask and I have model classes for every table

but im accessing an existing database and have no need for creating model classes

candid coral Apr 14, 2021, 1:23 AM

#

conn = sqlite3.connect('pythonDB.db')
c = conn.cursor()

c.execute('CREATE TABLE IF NOT EXISTS notifList (sentid INTEGER, onoff TEXT)')
tiname = '.3060ti'
  sendid = ctx.author.id
  sendid = int(sendid)
  c.execute("SELECT COUNT(*) FROM notifList WHERE sentid = (?)", (sendid,))
  row_count = c.rowcount
  print ("number of affected rows: {}".format(row_count))
  if row_count == 0:
    c.execute("INSERT INTO notifList VALUES (?, ?)", (sendid, 'on'))
    conn.commit()
    await ctx.send('Added!')
  else:
    c.execute("DELETE FROM notifList WHERE sentid = (?)", (sendid,))
    await ctx.send('Removed!')```
I'm also using discord.py

prisma girder Apr 14, 2021, 1:24 AM

#

glad marten but im accessing an existing database and have no need for creating model classe...

It doesn't matter whether your database is set up or not, model classes allow you to manipulate with database data (get and update)

prisma girder Apr 14, 2021, 1:25 AM

#

candid coral im using mysql with python to write data to a database, but counting affected ro...

It doesn't look like MySQL pithink

candid coral Apr 14, 2021, 1:26 AM

#

prisma girder It doesn't look like MySQL <:pithink:652247559909277706>

😳 sqlite sorry!

prisma girder Apr 14, 2021, 1:27 AM

#

candid coral ```py conn = sqlite3.connect('pythonDB.db') c = conn.cursor() c.execute('CREATE...

Have you checked out the documentation? https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.rowcount

As required by the Python DB API Spec, the rowcount attribute “is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. This includes SELECT statements because we cannot determine the number of rows a query produced until all rows were fetched.
Try to call c.fetchall()

#

However I may be wrong, I am not sqlite3 user

candid coral Apr 14, 2021, 1:32 AM

#

prisma girder Have you checked out the documentation? https://docs.python.org/3/library/sqlite...

same result :/

prisma girder Apr 14, 2021, 1:36 AM

#

candid coral same result :/

Why you have row_count = c.rowcount and later you use rowcount1 variable?

candid coral Apr 14, 2021, 1:37 AM

#

prisma girder Why you have `row_count = c.rowcount` and later you use `rowcount1` variable?

i have fixed that already, sent an outdated code snippet, fixed now

prisma girder Apr 14, 2021, 1:40 AM

#

candid coral i have fixed that already, sent an outdated code snippet, fixed now

Why do you use .rowcount instead of getting COUNT(*) value? pithink

candid coral Apr 14, 2021, 1:41 AM

#

im not sure, just started sql today soooo

candid coral Apr 14, 2021, 1:42 AM

#

prisma girder Why do you use `.rowcount` instead of getting `COUNT(*)` value? <:pithink:652247...

can .rowcount select by values, etc.?

prisma girder Apr 14, 2021, 1:43 AM

#

Can you show how you used .fetchall()?

candid coral Apr 14, 2021, 2:34 AM

#

just with a c.fetchall() (no idea how to 😭) @prisma girder

candid coral Apr 14, 2021, 2:59 AM

#

prisma girder Can you show how you used `.fetchall()`?

how should i be using it?

lunar kettle Apr 14, 2021, 3:13 AM

#

molten hound run this commands

bro i am using window pc not linux or mac

molten hound Apr 14, 2021, 3:14 AM

#

Just change te sudo command for those that u use

lost pebble Apr 14, 2021, 3:18 AM

#

how can I speed up database queries.

#

I am currently using mysql

#

with sqlachemy

#

typical query
query = current_app.config["DiscordDB"].query.order_by(desc('message_id')).filter(current_app.config["DiscordDB"].content.contains(q)).limit(size)

#

This takes about 2 seconds of maxed cpu

#

on a 800mb table

proven arrow Apr 14, 2021, 3:26 AM

#

Make sure to have the proper indexes on your columns

lost pebble Apr 14, 2021, 3:29 AM

#

indexes?

#

I have the primary key set

proven arrow Apr 14, 2021, 3:30 AM

#

https://stackoverflow.com/questions/1108/how-does-database-indexing-work

Stack Overflow

How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?

For information on queries to index a field, check out ...

proven arrow Apr 14, 2021, 3:31 AM

#

lost pebble I have the primary key set

But is your query querying by that primary key?

lost pebble Apr 14, 2021, 3:31 AM

#

yes its the first WHERE

#

the message_id

proven arrow Apr 14, 2021, 3:31 AM

#

That’s order by?

lost pebble Apr 14, 2021, 3:32 AM

#

oh wrong query

#

meant to post the one that queried that

#

I do have other queries that do other columns

#

what other indexes are their? I can't do unique

proven arrow Apr 14, 2021, 3:33 AM

#

It’s not always about unique

#

You can index most data types/columns

#

Read the post, I sent which explains it in a few different ways

#

In most cases indexing can speed it up a lot. However, remember indexing comes at a cost as well.

lost pebble Apr 14, 2021, 3:37 AM

#

ah interesting

#

yea I have plenty of storage

#

not sure about the fragmentation... but eh I have to do something

#

so I assume that fields that query parameters would be good for indexes

#

i.e. guild_id, server_id, channel_id

proven arrow Apr 14, 2021, 3:39 AM

#

lost pebble yea I have plenty of storage

Storage is not an issues as well today because it’s cheap. But writing can take longer when you have more indexes. Anyways in your use case this shouldn’t be a big concern.

proven arrow Apr 14, 2021, 3:40 AM

#

lost pebble i.e. guild_id, server_id, channel_id

Yeah if your querying a lot using those columns then sure.

lost pebble Apr 14, 2021, 3:40 AM

#

will indexing take a long time?
with ALTER TABLE table_name ADD INDEX column_name

#

wondering if I need to run it in a screen to avoid being stuck in a ssh session for 5 hrs lol

#

eh will just screen it

lost pebble Apr 14, 2021, 3:41 AM

#

proven arrow Yeah if your querying a lot using those columns then sure.

but I assume that a column with widelly varying and indeterminate length wouldn't be good to index?

#

such as message_content?

#

or should I index that too.

proven arrow Apr 14, 2021, 3:42 AM

#

Depends what you store or how you query it

#

Because sometimes it might still use a full table scan instead of using the index.

lost pebble Apr 14, 2021, 3:43 AM

#

ah true. I'm my way i'm pretty sure it scans full table

#

since its looking for %text%

proven arrow Apr 14, 2021, 3:43 AM

#

Yeah

lost pebble Apr 14, 2021, 3:43 AM

#

well thank you. The post linked discussed what makes a good index but I didn't really get it.

proven arrow Apr 14, 2021, 3:43 AM

#

MySQL also has full text searching which you can look into

lost pebble Apr 14, 2021, 4:07 AM

#

I used them and oh my god its instant now

#

db go brr

#

thank you a bunch

scarlet geyser Apr 14, 2021, 4:11 AM

#

Is the Rsk a good visor for the Sq88 daatabse? Im talking about using 5-10 collections btw

austere portal Apr 14, 2021, 5:08 AM

#

Are there any libs that I can use to query the db like django's db api?

austere portal Apr 14, 2021, 6:22 AM

#

I want a to do something like py Model.objects.all() # returns everythinglike django's query api

whole condor Apr 14, 2021, 7:23 AM

#

Depends what you store or how you query it
Because sometimes it might still use a full table scan instead of using the index.

whole condor Apr 14, 2021, 7:24 AM

#

scarlet geyser Is the Rsk a good visor for the Sq88 daatabse? Im talking about using 5-10 colle...

It’s not always about unique
You can index most data types/columns
Read the post, I sent which explains it in a few different ways
In most cases indexing can speed it up a lot. However, remember indexing comes at a cost as well.

grave eagle Apr 14, 2021, 8:07 AM

#

can i open a sqlite3 db multile times? like 1 db for more scripts

proven arrow Apr 14, 2021, 8:38 AM

#

grave eagle can i open a sqlite3 db multile times? like 1 db for more scripts

Yes but only one can write to it at a time.

#

Many readers, single writer.

warm rain Apr 14, 2021, 9:24 AM

#

when using yield from in aiomysql.Cursor.execute
It tells me can't use that since it's an async function

#

The documentation at https://aiomysql.readthedocs.io/en/latest/connection.html#connection tends to disagree

burnt turret Apr 14, 2021, 9:53 AM

#

the aiomysql doc is rather outdated

#

it still uses old style @asyncio.coroutine in many spots

burnt turret Apr 14, 2021, 9:53 AM

#

warm rain when using yield from in aiomysql.Cursor.execute It tells me can't use that sinc...

just use await there instead

#

i'd made an issue about it on their repo but to no avail lemon_pensive

warm rain Apr 14, 2021, 9:55 AM

#

what should I use instead?

burnt turret Apr 14, 2021, 9:56 AM

#

instead of yield from, use await

warm rain Apr 14, 2021, 9:56 AM

#

I mean as an entire better lib?

#

which is also async

burnt turret Apr 14, 2021, 9:56 AM

#

ah right, i haven't heard of any other async mysql lib sadly

warm rain Apr 14, 2021, 10:02 AM

#

Wanna use it for discord bots

#

And the code doesn't work

weak tinsel Apr 14, 2021, 1:43 PM

#

async/await syntax works in aiomysql

austere portal Apr 14, 2021, 3:06 PM

#

def create(name, expiry):
    with psycopg2.connect(uri) as conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO FoodProducts(Name, Expiry) VALUES (%s, %s);", (name, expiry))
            conn.commit()```
How can I make this return the id of the new row created (the table already has a id column). Thanks

torn sphinx Apr 14, 2021, 3:11 PM

#

yo anyone good with database

ornate fulcrum Apr 14, 2021, 3:11 PM

#

    @commands.command()
    @commands.has_permissions(manage_messages=True)
    async def requestdata(self, ctx):
                print("Requested data into dbstorage.txt")
                ssslsst = []
              
                async for result in collection.find({}):
                            ssslsst = str([result["writeup"]]).strip("[]'")
                            f=open ('dbstorage.txt', 'a')
                            f.seek (0, 0)
                            f.write(ssslsst + "\n")                          
                            f.close()``` This should get any values from keys "writeup" from database, but i don't get any values if the document has more keys than "writeup" only. How should this be done. I need only "writeup" values saved inside a file but doesn't save anything if doc have for example "author" key and value exist in doc

torn sphinx Apr 14, 2021, 3:12 PM

#

anyone good with database that knows how to make a server sided command toggle

#

Hey, i have a problem. I have this dict ```py
{'name1': [20, 10, 2, 2, 9, 10], 'name2': [1, 1, 1, 1, 1, 1], 'name3': [2, 2, 2, 2, 2, 2], 'name4': [3, 3, 3, 3, 3, 3], 'name5': [4, 4, 4, 4, 4, 4]}

dense barn Apr 14, 2021, 3:29 PM

#

can you use enabled=True for per server commands?

ornate fulcrum Apr 14, 2021, 4:22 PM

#

https://paste.pythondiscord.com/kejutesiyi.less Getting this error https://paste.pythondiscord.com/uyohigokac.sql and not all values with key "writeup" are saved inside the file, what causes this?

#

Yayyy i found solution myself, there was one entry that didn't have whole key "writeup" which caused the error

lost pebble Apr 14, 2021, 6:21 PM

#

I am using aiomysql with a query INSERT IGNORE
... it ignores it but it also write a warning to stdout

#

can I suppress this warning?

dusky siren Apr 14, 2021, 6:40 PM

#

I'm using SQLite3 for this
currently my code is doing this:

calenderCards = ["poketcb-15", "poketcb-16", "poketcb-17", "poketcb-18", "poketcb-19", "poketcb-20", "poketcb-21",
                 "poketcb-22", "poketcb-23", "poketcb-24", "poketcb-25", "poketcb-26", "poketcb-27", "poketcb-28",
                 "poketcb-29", "poketcb-30", "poketcb-31", "poketcb-32", "poketcb-33", "poketcb-34", "poketcb-35",
                 "poketcb-36", "poketcb-37", "poketcb-38"]

superCards = ["poketcb-12a", "poketcb-39a", "poketcb-7"]


async def pointRefresh(cardID):
    c.execute("SELECT * from cards WHERE id = ?", [cardID])
    i = c.fetchone()
    basePoints = pointDict.get(i[7].lower(), 1)
    releaseDate = setReleasedict.get(i[6], 2099)
    if releaseDate < 2010:
        multiplier = (0.96 * ((releaseDate - 2010) ** 2)) + 1
    else:
        multiplier = 1
    if i[17] is not None:
        gradeMulti = 9.7 * ((i[17] / 10) ** (4 ** (0.199 * i[17]))) + 0.4
    else:
        gradeMulti = 1
    cardPoints = (basePoints * multiplier) * gradeMulti
    if i[6] is not None and i[6].lower() == "shiny vault":
        cardPoints += 2000 * gradeMulti
    if i[2] in calenderCards:
        cardPoints /= 100
    elif i[2] in superCards:
        cardPoints *= 100
    c.execute("UPDATE cards SET points = ? WHERE id = ?", [cardPoints, cardID])
    conn.commit()

With 1 million+ database rows.
Every bit of code in there seemingly runs fast enough except the queries. The queries take long enough the entire code is going to take days to run. Anyone have better ideas on how to make this more efficient? Say, a way to create a query that will take a list of card ids and a list of card points and insert all at once to save on some time?

thorn geode Apr 14, 2021, 8:10 PM

#

You can try using aiosqlite

#

Which isn't blocking.

brazen charm Apr 14, 2021, 8:21 PM

#

well you sorta have a few things you can do

#

doing it for each card individually is very slow, you want to use execute many (more on this in a sec)
you should really try move all those calculations closest to the data SQL does a great job at this
You want to really use a bigger DB for this like postgres which is multi processed and able to handle much bigger and heavier loads than SQLite (also gives you executemany which reduces your round trips needed on every query)

round agate Apr 14, 2021, 9:34 PM

#

some one know how to change data type of a column in dBeaver?!?! I'm getting mad..

#

#

in sqlite

south charm Apr 14, 2021, 10:05 PM

#

hello

#

anyone knows MySQL here?

#

#

anyone knows the answer of the third question?

pure cypress Apr 14, 2021, 10:28 PM

#

Use a SELECT with a WHERE. The WHERE can use the > operator to compare dates.

warm rain Apr 14, 2021, 10:34 PM

#

I'm setting up postgre DB
When I type psql (I already added the bin folder to PATH)
I get this error:

        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?```
How do I see if port 5432 is blocked/occupied by another app>

flint salmon Apr 15, 2021, 6:25 AM

#

import sqlite3
import os
connection = sqlite3.connect("user_data.db")
cursor = connection.cursor()

# cursor.execute("""CREATE TABLE users(
#     username integer
# )""")
pass_var = os.environ['PASS_VAR']
pass_input = input('Enter administration login password(ALP): ')
if pass_input == pass_var:
    username_input = int(input('Enter a username: '))
    cursor.execute(f"INSERT INTO users VALUES({username_input})")
    print('Username added succesfully.')
    cursor.execute("SELECT * from users")
    print(cursor.fetchall())

#

i got this and it returns a key error

#

KeyError: 'PASS_VAR'

#

in my .env file that is in the same dir , i got that:

PASS_VAR="z0hFd"

#

what do i do wrong

tranquil totem Apr 15, 2021, 6:28 AM

#

how to individually fetch data from different collections in pymongo?

flint salmon Apr 15, 2021, 6:30 AM

#

getone

#

getone(var)

torn sphinx Apr 15, 2021, 6:32 AM

#

res = await collection.find_one({"id":ID})
await ctx.reply(res)

Why does this give me cannot send an empty message error and how can I fix that?

tranquil totem Apr 15, 2021, 6:43 AM

#

flint salmon getone(var)

how?

#

collection.find_one({"_id": member.guild.id})["welcome_channel"]

#

This is an example and its in a cog where the login info is specified

#

now what would i do if i were to remove the login details from this cog and add them only in my main file?

#

heres my login info
||```py
client = pymongo.MongoClient(os.environ["DB_login"])
db = client.discord_database
collection = db["server_data"]

#

and this is for one collection

#

i need to fetch data from several collections
what would i do in that case?

upbeat slate Apr 15, 2021, 6:59 AM

#

How to append in json from python 😫

#

friend_json[author].append(mentionned) does not work

#

append is not recognized

devout girder Apr 15, 2021, 8:16 AM

#

is it a dict?

lost pebble Apr 15, 2021, 8:22 AM

#

https://tenor.com/bmwO0.gif

Tenor

#

I have more mysql questions

#

Should I be using varchar instead of text

#

for storing values such as names and emails I am presently using text

#

According to some stack posts it seems that varchar queries faster and works better with indexing

#

however this seems to be a point of contention as other benchmarks disagree

proven arrow Apr 15, 2021, 9:12 AM

#

@lost pebble for MySQL yes varchar would be the better choice for things like email and names

proven arrow Apr 15, 2021, 9:16 AM

#

lost pebble however this seems to be a point of contention as other benchmarks disagree

And which benchmarks do you refer to?

torn sphinx Apr 15, 2021, 9:29 AM

#

selecting a row that doesn't exist in postgres, will it return None or will an error be raised?

shell ocean Apr 15, 2021, 9:47 AM

#

torn sphinx selecting a row that doesn't exist in postgres, will it return None or will an e...

you mean like selecting with a condition that no rows satisfy?

tranquil totem Apr 15, 2021, 10:19 AM

#

tranquil totem `collection.find_one({"_id": member.guild.id})["welcome_channel"]`

_ _ this

torn sphinx Apr 15, 2021, 11:19 AM

#

shell ocean you mean like selecting with a condition that no rows satisfy?

yeah

shell ocean Apr 15, 2021, 11:40 AM

#

torn sphinx yeah

it should return an empty list

#

but it depends on which method of the driver you use of course

torn sphinx Apr 15, 2021, 11:41 AM

#

what about fetchval

#

asyncpg.fetchval

shell ocean Apr 15, 2021, 11:41 AM

#

torn sphinx what about `fetchval`

why not try it

torn sphinx Apr 15, 2021, 11:42 AM

#

okie

#

thx

burnt turret Apr 15, 2021, 11:42 AM

#

fetchval should be giving None iirc

tranquil totem Apr 15, 2021, 3:00 PM

#

tranquil totem `collection.find_one({"_id": member.guild.id})["welcome_channel"]`

_ _⬆️

tepid cradle Apr 15, 2021, 3:07 PM

#

When I run the following code, I get an error saying RuntimeError: Event loop is closed. How do I run this? I'm just trying to get started with Asyncpg and not able to figure out how to connect and run a statement using this library.

import asyncio
import asyncpg

async def connect_db():
    conn = await asyncpg.connect('postgresql://******')
    return conn


async def test():
    conn = await connect_db()
    async with conn.transaction():
        cur = await conn.cursor("select * from pizza_mst")
        print(await cur.fetchrow())

asyncio.run(test())

tranquil totem Apr 15, 2021, 3:07 PM

#

😩

rain plank Apr 15, 2021, 3:26 PM

#

tepid cradle When I run the following code, I get an error saying `RuntimeError: Event loop i...

you probably have to close the cursor

#

actually no thonk

#

What's the full error you get?

pastel yoke Apr 15, 2021, 4:30 PM

#

Is there any library other than sqlite to interact with databases?

burnt turret Apr 15, 2021, 4:34 PM

#

there are other libraries to interact with other databases 🙂

#

to elaborate; SQLite is just one database, and python has the sqlite3 module to interact with it
If you were to use another database, you'd use the corresponding module. For example if you were using mysql, you could use mysql-connector-python, if you were using postgresql, you could use psycopg2 etc

torn sphinx Apr 15, 2021, 4:51 PM

#

How would i select last 5 rows from table without having id in my table -> sql

brazen charm Apr 15, 2021, 6:39 PM

#

torn sphinx How would i select last 5 rows from table without having `id` in my table -> sql

thats generally a very hard and intensive task without a auto incrementing ID

#

or tbh even then that doesnt guarantee the order

proven arrow Apr 15, 2021, 6:43 PM

#

torn sphinx How would i select last 5 rows from table without having `id` in my table -> sql

What columns do you have?

#

If you don’t have anything that can be ordered then you can use a window function, to give a number to each row as inner query. And then use this to order by/ limit.

torn sphinx Apr 15, 2021, 6:55 PM

#

hey, i have a database that looks like this:
Posts: ID, title, image
Subreddit: Subr

i want to know how many times a post is posted in a certain subreddit. does someone know a good way to save this data? The thing i came up with is:

another table that looks like this:
Link: postid, subreddit, how_many_posts

with 2 subreddits and 2 posts itd look like this then:
postid_1, subr_1, 0
post_id1, subr_2, 0
post_id2, subr_1, 0
post_id2, subr_2, 0

proven arrow Apr 15, 2021, 7:00 PM

#

torn sphinx hey, i have a database that looks like this: Posts: ID, title, image Subreddit: ...

Why can’t you just count the posts by querying all the posts in a subreddit?

#

That way you don’t need keep track manually

torn sphinx Apr 15, 2021, 7:03 PM

#

proven arrow Why can’t you just count the posts by querying all the posts in a subreddit?

i need to know how many times a post has been posted in a certain subreddit

#

it needs to post the post where how_many_posts is the lowest amount

rocky crypt Apr 15, 2021, 7:49 PM

#

torn sphinx hey, i have a database that looks like this: Posts: ID, title, image Subreddit: ...

you need a many to one relationship, where one post can be present in many subreddits

#

but i dont get how one post can be posted many times in the same subreddit

#

that doesnt make sense

normal carbon Apr 15, 2021, 8:20 PM

#

hi, can any one help me with a django project?
i need to create a DB for users

unkempt prism Apr 15, 2021, 9:30 PM

#

im new to databases. I wrote a python script to query the database every 3 seconds (its a tiny database, SUB 30 rows) and i get the error "Too many clients already" but i dont understand this. https://i.imgur.com/wmtUT5Y.png

Imgur

#

the script should be closing the database once it is done (uses a with statement)

#

im using Postgresql

torn sphinx Apr 15, 2021, 10:24 PM

#

brazen charm thats generally a very hard and intensive task without a auto incrementing ID

Aright so, can i set it to increase id of row in every new row?

#

So i dont need to do it manually?

quick radish Apr 16, 2021, 12:50 AM

#

Hey does anyone know why this query doenst round up my result?

SELECT nom, salaire, ROUND(salaire * 1.0525, 2) AS AugmentationSalariale
FROM employe
WHERE no_emp_resp BETWEEN 2 AND 4 AND (salaire * 1.25 <= 70000)

rocky crypt Apr 16, 2021, 4:47 AM

#

torn sphinx How would i select last 5 rows from table without having `id` in my table -> sql

you can have a timestamp column and get the 5 latest rows according to the timestamp

rocky crypt Apr 16, 2021, 4:52 AM

#

unkempt prism im new to databases. I wrote a python script to query the database every 3 secon...

could you send the script? this error basically means that you opened up more connections to the postgres server than its supposed to handle

unkempt prism Apr 16, 2021, 4:55 AM

#

rocky crypt could you send the script? this error basically means that you opened up more co...

unfortunately no, id like to not to share the code.

#

🙁

rocky crypt Apr 16, 2021, 4:57 AM

#

i dont think i can really help without looking at the code directly.
youre basically opening more connections than allowed (probably in a for loop?)

#

try to debug with this in mind

unkempt prism Apr 16, 2021, 5:26 AM

#

i cant seem to find anything that wouldnt close a connection. I dont have any database connections in a for loop. so that out

#

hmmm

#

is there a way to change my database settings to time out a connection when its passed a certain time?

#

say, if something is older than 5 minutes, it times out?

proven arrow Apr 16, 2021, 7:26 AM

#

unkempt prism i cant seem to find anything that wouldnt close a connection. I dont have any da...

How are you creating the connection? Can you share the code

unkempt prism Apr 16, 2021, 7:52 AM

#

im using a lib called dataset

#

https://i.imgur.com/EIjPwnU.png

Imgur

#

all my database connections are opened with with statements

#

so i would think it would close automatically right?

dull cypress Apr 16, 2021, 10:38 AM

#

looks like you spawn a new connection for every loop

#

what you should do instead is init a db conn at the start, and reuse it throughout your code. that way your script only spawns "one connection"

tranquil totem Apr 16, 2021, 11:11 AM

#

@burnt turret whats this

#

:/

burnt turret Apr 16, 2021, 11:11 AM

#

.find isn't .find_one

#

i explain why we don't await the find towards the end of that gist

tranquil totem Apr 16, 2021, 11:12 AM

#

oh

#

i thought it was for any type of .find

#

ty

#

@burnt turret what abt if statements?

#

if (collection.count_documents({"_id": ctx.author.id}) == 0) or (collection.find_one({"_id": ctx.author.id})["note"] is None):
```do i add await here as well?

burnt turret Apr 16, 2021, 11:27 AM

#

there's nothing special about if statements, you'll have to await them there too

tranquil totem Apr 16, 2021, 11:27 AM

#

ok thank you

#

switched to motor successfully

signal cloak Apr 16, 2021, 12:19 PM

#

#

How can I delete the 1 object in that array

#

its I only have the id

#

I need like the code

#

I know its something like