databases

[haughty ravine]

hm

there are some parts which dont work, but still thx a lot 😄

@torn sphinx @stark violet thx guys

thx a lot

collectionG, collectionU = get_database(self)
        if not isinstance(ctx.channel, discord.channel.DMChannel):
            if not ctx.author.guild_permissions.administrator:
                await ctx.send("You are not **administrator**")
                return
            elif collectionG.count_documents({"_id":ctx.guild.id}) == 0:
                collectionG.insert_one({"_id":ctx.guild.id})
            languagedata = collectionG.find_one({"_id":ctx.guild.id},{"language":1}).get("language")
            if languagedata == None:
                languagedata = 'en'
            if language == languagedata:
                await ctx.send('You have the same current language')
                return
            elif language != languagedata:
                if language != 'en':
                    collectionG.update_one({"_id":ctx.guild.id}, {"$set":{"language":language}})
                elif language == 'en':
                    collectionG.update_one({"_id":ctx.guild.id}, {"$unset":{"language":language}})
                await ctx.send(f'The language has been updated to {language}')
                return

this is the code which works for me

[torn sphinx]

oh ok cool then

[haughty ravine]

{"_id":ctx.guild.id},{"language":1} i dont understand why there is '1' then... only

[haughty ravine]

thx a lot for helping me 🙂

for not ignoring

[torn sphinx]

yeah sure !! ( ꈍᴗꈍ)

[haughty ravine]

also XD

how to check if there is only one value ||"_id"|| in a document

so i can delete it, as it contains no useful data

thats my last question XD

I promise

[torn sphinx]

ooh-

so the doc contains only the id right ?

[haughty ravine]

yep

basically these documents can be considered trash

XD

especially when you remove value

there is a chance only _id left

so how to checl that

[torn sphinx]

@haughty ravine you can check with
if collection.count_documents({"_id"}) == 1:
collection.delete_one({"_id"})

i think this is how it work

i forgot about deleting

it's rather delete or remove

[haughty ravine]

?

what do you mean

[torn sphinx]

you want to delete the ones with just "_id" : ctx.guild.id
and they contain no language

[haughty ravine]

i want to remove or delete (idk the difference) if there is only _id in a document

[torn sphinx]

yeah that what i said lol

just count the docs if there is only 1 doc which is the "_id" then delete it

[haughty ravine]

if collection.count_documents({"_id"}) == 1:
collection.delete_one({"_id"}) ?

[torn sphinx]

yes

that should work

probably

[haughty ravine]

cannot encode object: {'_id'}, of type: <class 'set'>

oof

@torn sphinx

collectionG.count_documents({"_id"}) == 1: in here

[torn sphinx]

ooh

hmm

i guess you have to pass the ctx.guild.id in their too

so it can detect it

[haughty ravine]

filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping

collectionG.delete_one({"_id"})

probably should pass guild id too

yay worked 😄

Thx a lot

:DDDDDDD

[torn sphinx]

oop

okeh

np

[worldly panther]

Hello 👋. Where and how should I manage a database in a open-source project? I mean, it's a database file that's empty, then you fill it through interacting with the app that's related to it, but the file is inside a folder and my code is on GitHub. Should that be displayed? Never seen any sort of database file inside a GitHub public repo...

[torn sphinx]

quick question , if someone saw this ,
um I'm trying to change my database to firebase , bc mongodb seems too slow for me , is firebase a better one , or should i not bother ?

[torn sphinx]

        existing = gang_db.find_one({"gang":arg})
        if lowerarg2 == str(existing["gang"].lower()):
          await ctx.send("That gang already exists!")
        else:
            newganguser = {"id":ctx.message.author.id,"gang":arg2}
            gang_db.insert_one(newganguser)
            await ctx.message.author.edit(nick=ctx.message.author.name+" "+arg2.translate(translator))
            print(arg2.translate(translator))
            await ctx.send("You joined a gang!")```

I need help

😭

TypeError: 'NoneType' object is not subscriptable

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable```

[torn sphinx]

Is the problem supposed to be related to the database?

[stark violet]

try to print existing before the the if loop and check whether its returning something or not

[elder socket]

hello

how do i update my calendar table? i need to add year 2021. my current table is only till 2020

[tepid cradle]

What do you mean your current table is only till 2020 @elder socket

[elder socket]

im using mysql

[tepid cradle]

You can just add rows, I don't see the problem here

[elder socket]

i need to add 2021, how do i add rows without adding it 1by1 ?

[tepid cradle]

First of all, you don't need all those columns. Any programming language or SQL itself will give you all the output simply based on the date itself

All the columns after the date are completely redundant

Second, why do you need a table like this? Without knowing the usage, it's difficult to answer your question. I don't know why you need to create a list of dates and store it beforehand, when that can be done on the fly at the time of any operation

[elder socket]

had to use it for powerbi reference , my colleuage made this one before i got this job. he had setup several views which uses the calendar table

[tepid cradle]

OK.

This is definitely not a well designed structure, but if you have to continue with it, then you will have to populate it, nothing to do

[elder socket]

thanks for the advice . i just need to setup this up first and make changes in the future . hehe im still learning things , just learned basic python and sql last year.

[tepid cradle]

OK

Look at this:
https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql

You can generate a series of dates like this, then insert it in the first column. At this point, all other columns will be null.

You can then use SQL date functions to populate rest of the columns

[elder socket]

checking , thanks!

[knotty gyro]

anyone here uses aiosqlite?

getMsg = await cursor.execute("SELECT message_number FROM firstMonth WHERE user_id = ?;",(ctx.author.id,))
    print(getMsg)

This is what this is printing

<aiosqlite.cursor.Cursor object at 0x000001C2AE4BC340>

How to make it print the values it get?

ping me if u know, thank you!

[wet mesa]

@knotty gyro https://pynative.com/python-cursor-fetchall-fetchmany-fetchone-to-read-rows-from-table/

[knotty gyro]

Thanks!

[torn sphinx]

hey any recommendation on sql schema viewer and sql manager in VSC?

[golden warren]

Can you help me to fix it?

[golden warren]

.

[civic gate]

If anyone can help, thanks #help-pie

[torn sphinx]

fire base is better than mongodb
firebase is fast and provide you with 1GB storage in the database
and the premium is low , like 5$ per GB and you can add GBs with just 1$ per GB
unlike mongodb is not that fast and only arround 400GB
and the premium price is high

[bright hound]

hey guys. I'm trying to connect a postgres database to a python script and I don't think it's working correctly

conn = psycopg2.connect(conn_string)
print("Connected to Database!")```

This is the code I'm using where those values like PGHOST are being imported from a config file
But when I execute the script I don't get the print statement returned in the console
the value of PGHOST is literally just 'localhost'. I wasn't sure if that was correct or not

[ember terrace]

I've been using a CSV file to keep >5000 rows of data which I query using pandas when I need to. I was thinking about converting to sql but none of the records have redundant data or any reason to normalize that I can tell. I guess SQL would be simpler for data selection, but I've already written those queries for pandas anyway. Any other reason I should consider?

[flat isle]

does anyone know how to import an sqlite database into a python project?

[tawny kelp]

Hadn't noticed the database channel 😦 I have this line:
cursor.execute("SELECT * FROM users WHERE id=?", (member.id,))
I am using MariaDB, and the docs say to do this as:

    "SELECT first_name,last_name FROM employees WHERE first_name=?", 
    (some_name,))```
Those look the same to me but I get a "Not all parameters were used in the SQL statement" error. I've looked around but all I can find is people doing things like using %d instead of %s and so, but MariaDB's documentation uses a ? so I am just confused 😦 I have also tried printing the this member.id I'm using as value and it is not null, and it has a matching type of int. The database exists, it is connecting correctly, I have tried converting to string, adding quotes, all kinds of stuff... It worked fine when I used an f-string and passed the variable as {member.id} but that does not seem safe and is just confusing with the quotes getting mixed up.

[silent roost]

if i have this select statement, sql select emp_firstname || ' ' || emp_surname, count(job_lead) from employees join jobs on emp_id = job_lead what can i put in a where clause, such that it only displays those employees, who've led the most jobs (i.e. those with the highest count(job_lead))

[wet mesa]

@silent roost
I'd usually section it using WITH

WITH results AS (
.... count(job_lead) as job_count
)
SELECT * FROM results
ORDER BY job_count DESC
LIMIT 10    ##### pick top 10

[torn sphinx]

everything looks fine, maybe employees error, used 2 e; other than that can't help without the code :/

anyone has a clue how to make dbeaver live sync every other second?

[lusty grail]

#career-advice message

Totally.

@shell ocean moreover their use case don't require mad wriringt

[shell ocean]

yeah

[lusty grail]

But our ceo is in low wish Cassandra so he tries to push it down everyone throats

[shell ocean]

I read about how Discord moved to Cassandra though

p cool

[lusty grail]

Yeah it's ideal for chat services

But case of my colleagues, they do some amount of bulk writes (like hundreds thousands of records) at knows point of time, smth like once a week or once 24h

And I don't think it justifies using Cassandra @shell ocean

[shell ocean]

yeah

it doesn't sound like it to me?

low throughput

just burst writes

[lusty grail]

Yeah,imo post gres or other sql would handle it easy

Especially since it's scheduled for time of day where no one uses app

[native cipher]

Hello, I'm currently working on a MySQL project using Python. I've got an error when I tried to run the first query example, the code works fine when I run the second example. Is there a way to make the first example of query possible so I can just input a needed name into the query instead of typing every name in database into the query separately and then using if/elses all the time?

[brazen charm]

remove the % to stop python formatting it

python shouldnt be doing the formatting

thats mysql's job of preparing the query safely

[sick dragon]

why is this raising me a sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 3 supplied.? ```py
results = sql_execute(
"SELECT * FROM sites WHERE title LIKE '%?%' OR url LIKE '%?%' OR description LIKE '%?%'",
(request.args["query"], request.args["query"], request.args["query"])
)

[neon idol]

Hello, I don't think you can use the placeholders like that

[sick dragon]

then how?

[neon idol]

normally you let the engine "stringify" them

so what I would do..

is add the "%" in the params themselves, and just use ? in the query string

[sick dragon]

any simple example? i don't understand what you mean

[neon idol]

yes just a moment

I'm writing one right now

results = sql_execute(
    "SELECT * FROM sites WHERE title LIKE ? OR url LIKE ? OR description LIKE ?",
    ('%{}%'.format(request.args["query"]), '%{}%'.format(request.args["query"]), '%{}%'.format(request.args["query"]))
)```

something like this

sorry I didn't test

but do you understand my point?

[sick dragon]

yes

let me try

[neon idol]

the engine is responsible for making sure that the params you pass it are safe, to prevent SQL injection

all the database engines work like that: sqlite, psycopg2, etc.

they might have different syntaxes, but it's always the same concept

[sick dragon]

yep, that works, apparently

thank you

btw

now it's raising me this - AttributeError: '_AppCtxGlobals' object has no attribute 'fetchall'

oh nvm

i returned in my sql_execute the database rather than the cursor 😅

[neon idol]

haha yes!

[sick dragon]

thank you again

now my search engine uses sql rather than json lol

[neon idol]

hope it helps!

[torn sphinx]

why :/

and how can i fix this

[sick dragon]

you can only use await in async def functions

[torn sphinx]

ok so then how do i open the users thing?

[brazen charm]

why are you using async in a system that doesnt need it

[torn sphinx]

im not

[brazen charm]

then why are you awaiting stuff

[pallid kayak]

open_useracc is an async function

[torn sphinx]

ik

yeah i figured it out

[nocturne yew]

i need a help

TypeError: not all arguments converted during string formatting```

[modern mulch]

Try adding a , at the end of the tuple

cursor.execute("""INSERT INTO movies (imdbid) VALUES (%s)""", (movie_event["id"],))
#                                                                               ^ Here

A 1-element tuple needs a comma

[nocturne yew]

sorry. i mistaken

psycopg2.errors.UndefinedColumn: column "imdbid" of relation "movies" does not exist
LINE 1: INSERT INTO public.movies (imdbid) VALUES ('tt0103776') ```
                                   ^

thank you so much for the suggestion. it seems working. but says not exist. strange

but it do exist

do i need to use double quotes for the column "imdbid" @modern mulch

[modern mulch]

No, the spelling is wrong

In your query you have imdbid

In the database it's imbdid

[nocturne yew]

i am so sorry! i was little nervous. i agree about the spelling, let me drop the table. thanks

[modern mulch]

np lol. Take a deep breath

[nocturne yew]

lol ❤️ it worked. can i ask one big doubt. i need to insert json value in the column. please please.

[modern mulch]

What's the issue?

[nocturne yew]

i have a big json values

[modern mulch]

Are you asking about what data type to choose or?

[nocturne yew]

it shows error dict

actually json worked well

but it is dict type

i show it in screenshot

    imbdid VARCHAR ( 10 ) PRIMARY KEY,
    directorlist json, boxoffice json)```

i created like this

maybe i need to say

boxofice dict or something?

psycopg2.ProgrammingError: can't adapt type 'dict' ```

[modern mulch]

I believe you have to json.dumps that boxOffice dictionary as well

[nocturne yew]

wow thanks @modern mulch ❤️

only one last last doubt please 🙂 please 🙂

regarding uuid

[modern mulch]

Go ahead

[nocturne yew]

how can i say to create uuid for every row

i show you what i done so far

uuid DEFAULT uuid_generate_v4 (),

it worked automatically!!

you are god!! @modern mulch

[modern mulch]

You figured out the uuid 😄

[nocturne yew]

yeah 🙂

its first time i am doing something on my own 🙂

thanks a lot for your help ❤️ ❤️ ❤️

[modern mulch]

np. Have fun!

[nocturne yew]

will it be possible to ask you doube after 30 mins. i need to key create table with foreign key. i hope that i can do my own. if not, can i ask you later 30 mins.

i created a movie table, next actor table, next movie_actor table

for example, how many films leonardo dicaprio acted information needs foreignkey from actor table

it sounds so complicated to imagine. hope that i can have fun with it 🙂

[modern mulch]

Sry I can't make commitments. Feel free to post your questions and others can help you when I can't 😄

[nocturne yew]

ok mushy, no problem 🙂

[native cipher]

ok thanks for the advise

[brazen charm]

look at https://stackoverflow.com/questions/775296/mysql-parameterized-queries#775399

[native cipher]

This helped a lot, just resolved my issue, you saved me thank you so much 👌👌👌

[icy crane]

I have a webapp with Flask, SQLalchemy and celery.

Some information is put in a database, with
db.session.add(info)
&
db.session.commit()
Then, a long task is send to Celery. This task would use the information from the database, but the Celery worker says the database is empty.
When I query the database from Flask, it says the information is in there.
It seems as though the changes don't get saved to the database correctly. Does anyone have a clue what the problem could be?

[icy crane]

Different question:

Is it even possible to use a simple site.db database file on a service like Heroku?

or should I always use something like Postgres

[nocturne yew]

i need one help.

[nocturne yew]

i have one table that insert json based on id

but it only insert the last id of the json

why

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "actors_pkey"

[rugged parrot]

Hello, can anyone help me with "import pyodbc"? I installed pyodbc through my command prompt with "pip install pyodbc" which was successful, but now my editor Canopy is getting this error message:

ImportErrorTraceback (most recent call last)
<ipython-input-1-dbc27c5f2947> in <module>()
----> 1 import pyodbc

ImportError: No module named pyodbc ```

[nocturne yew]

the file pyodbc.py is not in the root folder, maybe?

from psycopg2.errors import UniqueViolation
in a table, how can i mention it while insert the values

[rugged parrot]

@nocturne yew how do i know what my root folder is?

[nocturne yew]

import path
import os
project_root = os.path.dirname(os.path.dirname(file))

[glad sleet]

so im mongodb im wanting to make a list of dicts/dict or dicts idk which one to do where i can store many users like:

userid: 19281983938,
messages: 38778287
}``` in mongodb

so i can later do find_one in my list of dicts/dict or dicts to see that users information

what should i use?

[burnt turret]

you don't use a list of dicts @glad sleet

You use a collection

a collection is basically the equivalent of a table in mongodb

Into it you'd insert dicts of the form you sent

After that you can use find({"user":id}) to get all dicts with that user ID

find_one returns the first matching result

[rugged parrot]

So I ended up not going forward with Access since it's easier to just use CSV then migrate that to Access later on... but now I have a question about CSV

My code is this:

            #Prep the CSV file
            csv_columns = ['01_PostID','02_Hyperlink','03_Published','04_Author','05_Category','06_Title','07_Article']
            csv_file = "fblinks_data.csv"
            file = open(csv_file,'a')
            with file:
                writer = csv.DictWriter(file, fieldnames=csv_columns)
                writer.writeheader()
                for data in my_dict_list:
                    writer.writerow(data)
except IOError:
            print("I/O error")```

And my output was this (from range[0:5] iterations):

How do I get it to :
(a) not do a new line for each new row written?
(b) only post dictionary values (ignore keys) for each iteration?
(c) extra credit: check for duplicates and bypass them?

Let me know if you need the full code

[haughty ravine]

collectionG, collectionU = get_database(self)
resultG, resultU = None, None
if object == 'all' or object == 'guild' or object == 'g':
    resultG = f'guild: {list(collectionG.find({"_id":0},{arg:1}))}\n'
if object == 'all' or object == 'user' or object == 'u':
    resultU = f'user: {list(collectionU.find({},{arg:1}))}'
print(f'{resultG}{resultU}')```
**so i want to list from my MongoDB keys and values by a word WITH documents _ids only, so if document doesnt have this key or value, the program will just skip the document without pasting its _id** how do i do that :/

[haughty ravine]

pls help

[burnt turret]

can you elaborate on what you'er trying to do?

find_one will return None when there are no matches

[haughty ravine]

i want to list all keys and values with their ids IF they match one specific word in the input @burnt turret

[burnt turret]

you mean if key: some word from input exists in the collection, return that key value pair?

and there are multiple possible values that for the same key that you want the data for

is this what you mean

@haughty ravine

[haughty ravine]

yes

if key or value is same to the word given, it returns _id pair + this pair

but it brings ALL of these pairs in one collection

@burnt turret

[burnt turret]

key or value?

you can't match that way in the db

to check if either key or value matches your thing, you'd have to get all of the key:value pairs, make it a python dict and then loop through that and check

would recommend restructuring your data so that you don't have to check keys as well

then you could just let the db handle it by doing some query

[haughty ravine]

and key and value

???

[burnt turret]

by having some constant key, you'll be able to do something like

collection.find({"key": {"$in": ["value1", "value2", "value3"]}})

and the db will return all fields where the key is either value1/value2/value3

[burnt turret]

you can't match that way in the db
to check if either key or value matches your thing, you'd have to get all of the key:value pairs, make it a python dict and then loop through that and check

[haughty ravine]

so its either value or key?

[burnt turret]

no

as i understand it, you have some input
you have documents with key:value pairs
and you want to retrieve all documents where either key or value is equal to the input

and as i said, that's not possible natively in the database

#databases message you'd have to do this, but that's just python

[haughty ravine]

its a bad practise?

[burnt turret]

if you do this, you can then use #databases message

[burnt turret]

imo - but i haven't really learned databases like that

the best thing to do would be to use an SQL db for discord bots - you don't need the lack of structure of NoSQL databases like mongo

[haughty ravine]

hm

[burnt turret]

postgresql is more performant, and the db you'd use for bots are always very uniform

meaning that they can be easily tabulated

[haughty ravine]

is it easy to tranform data from noSQL to SQL db

[burnt turret]

depends

but not the data you seem to have now

because the keys are also changing?

[haughty ravine]

no lol

keys dont change

i just want to get all data by one key for example

[burnt turret]

then why do you need to check if either key or value matches?

[haughty ravine]

and get all data by one value

[burnt turret]

if the key is always the same

[haughty ravine]

hm

idk

[burnt turret]

frame your question properly please

if you have a single key and you want to return all documents where that key is having a value you'd do
find({key:value})
if you have a key, and you need to return all documents where the value of that key is any of the items you have in a list
find({key: {"$in":[list of values]}})

this is the same thing as what i'd said earlier, that's a more syntactically correct example

[haughty ravine]

but how do i show _id too

of a document

where key or value matches input

[burnt turret]

if it matches you get the entire document from the db

not just the key value pair that matched

{
_id: 000,
user: 123,
data: 1223
}
``` say you have this data, and you queried for `collection.find({'user':123})`

it will return the full dict,{ _id: 000, user: 123, data: 1223 }

[haughty ravine]

but how do i return only the pair and _id pair

hmmm

[burnt turret]

oh wait

do your keys change or not

[golden warren]

Hello their! I have this error: [Tue Jan 12 10:46:17 2021] Error: no such column: DevBot
And my code:

    async def initialization(self):
        async with aiosqlite.connect('src\\db\\users.db') as usersdb:
            try:
                logger.log('sucess', 'Connected on db.')
                await usersdb.execute(
                    "CREATE TABLE IF NOT EXISTS user(id INTEGER, user_name STRING NOT NULL, user_id INTEGER)")
                await usersdb.execute(
                    "CREATE TABLE IF NOT EXISTS infraction(id INTEGER, user_name STRING NOT NULL, user_id INTEGER, moderator_name STRING NOT NULL, moderator_id INTEGER, reason STRING, infranction_number INTEGER)")
                logger.log(
                    'sucess', 'Table creation was successfull create in usersdb.')
                try:
                    for guilds in self.client.guilds:
                        for members in guilds.members:
                            await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(members, members))
                except Exception as e:
                    logger.log('error', e)
            except Exception as e:
                logger.log('error', e)

[haughty ravine]

no

[burnt turret]

if not, you can just

collection.find({...}, {"_id": 1, <your key>: 1})

pass that as a second dict

the first dict is where you tell what it has to match

i think that should work

if that doesn't work, explicitly tell the db you don't want the rest of the key:value pairs, by passing some_other_key: 0 into the second dict

[haughty ravine]

hmmmm

ok thx 🙂

collectionG = get_database(self)
print(collectionG)
collection = сollectionG```

why is my collectionG not defined? when i try to assign it to collection

[burnt turret]

i don't know what your get_database function does

[haughty ravine]

print shows me some stuff: Collection(Database(MongoClient(hos blablablha

[burnt turret]

then it's worked

[haughty ravine]

but i still get this error name 'сollectionG' is not defined

like the next line to print

[burnt turret]

why are you redefining it there though

just directly assign it as collection?

[haughty ravine]

i have 2 collections

[burnt turret]

yeah but you've assigned both variables to the same collection

[haughty ravine]

wait

collectionG, collectionU = get_database(self)
print(collectionG, collectionU)
if BRUH:
    collection = сollectionG
elif BRUH:
    collection = сollectionU```

thats why

then i want to do some stuff with collection

[burnt turret]

why do you have an if else statement with the same condition

that indentation looks a bit off here, is it discord messing up

are you also sure that your get_database is return 2 collections?

[haughty ravine]

no BRUH things are different

i have there some other different code in if statements

its related to other library

[burnt turret]

right

can you show the error

along with some more of the code maybe; i can't tell what's wrong here

also - use motor, not pymongo

pymongo is blocking; no-no for bots

[haughty ravine]

NameError: name 'сollectionG' is not defined

[haughty ravine]

hmm

[burnt turret]

https://paste.pydis.com

can't tell what's wrong without seeing more code

[golden warren]

Can you ping me when you have finished :) ?

[burnt turret]

are you sure this is where the error is being raised? it says no column named DevBot but i don't see a query that is asking for a column like that

[golden warren]

Yes me too. I don't know why i have this error.

[burnt turret]

well the error says somewhere you've tried querying a table, asking for some data from a column called DevBot

[golden warren]

I just put as parameter my client in my main file. But i don't understand that error

[burnt turret]

seeing the name, im assuming that is the db name? or maybe the table name

check your queries

[golden warren]

Okay i see, i have only two parameter

[burnt turret]

is this fixed?

[haughty ravine]

OTHER FILE:
langM, langS = get_language(self, object, os.path.abspath(__file__))
OTHER FILE:

    cluster = MongoClient('**some stuff'**)
    db = cluster['Database']
    collectionG = db['Guild']
    collectionU = db['User']
    return collectionG, collectionU```
OTHER FILE:
```def get_language(self, object, path):
    collectionG, collectionU = get_database(self)
    print(collectionG, collectionU)
    if isinstance(object, discord.Guild):
        collection = сollectionG
    elif isinstance(object, discord.Member):
        collection = сollectionU

ERROR:

NameError: name 'сollectionG' is not defined```

[golden warren]

I have only two tables and no Devbot lol

[burnt turret]

yeeah check through the code, find where your query is asking for a DevBot because that's what seems to error - the error definitely isn't on the code you'd sent earlier i think

[haughty ravine]

here

[golden warren]

But my only code which touch to db is what i send.

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : db.py                                   | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 07/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import aiosqlite
import discord
from discord.ext import commands


from src.helpers.logger import logger


class DB:

    def __init__(self):
        pass

    async def initialization(self, client):
        async with aiosqlite.connect('src\\db\\users.db') as usersdb:
            try:
                logger.log('sucess', 'Connected on db.')
                await usersdb.execute(
                    "CREATE TABLE IF NOT EXISTS user(id INTEGER, user_name STRING NOT NULL, user_id INTEGER)")
                await usersdb.execute(
                    "CREATE TABLE IF NOT EXISTS infraction(id INTEGER, user_name STRING NOT NULL, user_id INTEGER, moderator_name STRING NOT NULL, moderator_id INTEGER, reason STRING, infranction_number INTEGER)")
                logger.log(
                    'sucess', 'Table creation was successfull create in usersdb.')
                for guilds in client.guilds:
                    for members in guilds.members:
                        await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(members, members))
            except Exception as e:
                logger.log('error', e)


if __name__ == "__main__":
    pass

[burnt turret]

what line number does the errr tell you is wrong

[haughty ravine]

line 11 in get_language (in if statement)

[golden warren]

Me or e-girl?

[burnt turret]

both

[haughty ravine]

lmao

[burnt turret]

im really unsure about yours btw; maybe wait for someone else, i don't see whats wrong in the little code that you've sent

[golden warren]

No line but that error:

Ignoring exception in on_connect
Traceback (most recent call last):
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\devbot.py", line 27, in on_connect
    await DB().initialization(self)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\helpers\db.py", line 34, in initialization
    await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(members, members))
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 175, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 122, in _execute
    return await future
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 98, in run
    result = function()
sqlite3.OperationalError: no such column: DevBot

[haughty ravine]

ok :/

[golden warren]

Use class @haughty ravine ?

And @burnt turret why there is no line in my error?

[haughty ravine]

i use classes

[burnt turret]

the error line in your file is line 34

read the entire traceback,
File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\helpers\db.py", line 34, in initialization await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(members, members))

[golden warren]

Oh i see but how fix that ?

Do you know how call my client in my db file.

?

[burnt turret]

wdym

[golden warren]

wdym?

I'm not english sorry

[burnt turret]

what do you mean* by call client in db file

[golden warren]

@haughty ravine Do that

from dir import class_name

var = class_name()
var2 = var.сollectionG

Try this.

Because in my class db, it's only for manage db but i would like to fetch all users id, ect... And i need to call my client.

[burnt turret]

well i see you've got the client as a parameter to your functions

[golden warren]

How can i call it. Because it's doesn't work ><

[burnt turret]

so when you use it you can just pass that in?

[golden warren]

Yes and i think, error from that.

[burnt turret]

how are you called this class

i mean where in your main bot code have you imported it

and how are you calling the initialization function

[golden warren]

    async def on_connect(self):
        await DB().initialization(self)
        self.logger.log('sucess', 'Connected to discord.')

[burnt turret]

yeah

[golden warren]

Like that

[burnt turret]

you don't pass self

you pass client if that's what you've called your instance of commands.Bot

[haughty ravine]

maybe its a problem in assigning another variable to a mongo collection

[golden warren]

self = client

[golden warren]

Maybe not sure about it

[burnt turret]

you've subclassed commands.Bot?

[haughty ravine]

me?

[burnt turret]

nope

[golden warren]

Nop

[burnt turret]

then self is not equal to client

[haughty ravine]

oh oof

[burnt turret]

i dont know what self is without seeing that entire class

[golden warren]

Aaaah yes sure i think you talked about db class lol

[burnt turret]

probably a cog im guessing

[golden warren]

My main file is commands.Bot

*has

[burnt turret]

show that file

https://paste.pydis.com

[golden warren]

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

[burnt turret]

wait a min

[golden warren]

Sure.

[burnt turret]

i can't figure this out for some reason sqlite is raising an error based on the...name of your subclass of bot

but you've got no queries that directly use it

why are you subclassing commands.Bot though? you know you can do it just by making an instance

[golden warren]

You wan't subclassing what?

I see what you mean i think.

[golden warren]

It work if i subclassing my db class?

[torn sphinx]

anyone has a clue how to use rollback() function in sqlite3 module for a single transaction?

[burnt turret]

Subclassing commands.Bot is generally unnecessary is what I meant

[haughty ravine]

how do i list only some keys without putting ('key':False) for others in Mongo?

[golden warren]

Okay @burnt turret So for my problem, have you got an idea to fix it?

[burnt turret]

what happens when you tried what i said first?

[haughty ravine]

i forgot but it doesnt work

[burnt turret]

only specifying the ones which you want

what was it

that didn't work

[haughty ravine]

???

[burnt turret]

i mean you said you tried it and it didn't work

[haughty ravine]

no like the question is different one :>

[burnt turret]

im asking what was it that didn't work

yes i knpw

[haughty ravine]

like i only want two keys from all

[haughty ravine]

idk

[burnt turret]

but i'd answered this question that time too

[haughty ravine]

this didnt work

the other problem i fixed somehow myself :/

with collection variable

[burnt turret]

right then i think this is the only way

to make the db do the work

[burnt turret]

and this is the same as key: false if you didn't know

[haughty ravine]

but how do i list keys, if i have a custom key

like user can input any key

i want only _id and custom key show

[burnt turret]

so your keys aren't all the same 🙁

this is what i'd asked you so many times that time

[haughty ravine]

wait what

they are same

[burnt turret]

there's no way to query and see if something matches a key

[haughty ravine]

user just can input custom

ohhhhh

i thought the other way

sorry XD

[burnt turret]

there's only ways to query and see if something matches the value of the key

[haughty ravine]

:///

So

i cant make other keys dont appear in my custom find query by key

[burnt turret]

you can, but only using the key: false method

at least that's the only way i know of

[haughty ravine]

oof

[burnt turret]

and i've had to learn mongodb at school 😔

[haughty ravine]

how do i search by values tho?

[haughty ravine]

bruh, you had mongo at school?

list(collectionG.find({value:{'$exists':1}}))

[burnt turret]

wdym?

that should work yeah

[haughty ravine]

like i put a value

and it searches throug all documents

[burnt turret]

but that'll still return the entire document

[haughty ravine]

to find this value

[burnt turret]

not just this specific key:value pair

[haughty ravine]

this searches for my custom key

but i cant search for custom variable

[burnt turret]

what do you mean by custom variable?

{key: value} can you use these terminology lmao you're confusing me with what you're trying to do

[haughty ravine]

no like

emm

emmmmmm

like to search for a custom variable

like i input 10

and if it finds 10 in any field of value it would print all documents with it

[burnt turret]

what i'd meant earlier when i was asking if "keys change" is this:
imagine you have a collection with user details

Format 1:
{
user_id: 12346789,
data: 1234
}

Format 2:
{
"123456789": 1234
}

in the second one you've kept the key of the dictionary directly as the user ID itself - so now there's no way to query and find a document where that is the user ID

but in the first case, you can do something like find({"user_id": 123456789})

if your data is in the second format, querying becomes harder

[haughty ravine]

how do i make "user_id" all?

like all keys

not this key only

my data is structured like in format 1

[burnt turret]

you want to get all documents that have a user_id?

you can just use the $exists that you'd done earlier

[haughty ravine]

i want to get all documents with the same variable for all keys

[haughty ravine]

that checks only for custom key

list(collectionG.find({'$exists':1}:value)) doesnt work

eventhough list(collectionG.find({value:{'$exists':1}})) works

[burnt turret]

like if

{
something: 1,
another_thing: 2,
third_thing: 1
}
``` you want to get `{something: 1, third_thing: 1}` ?

[haughty ravine]

yes

in all documents

maybe lile...

{
'trash': 5
'fortnute': 7
'eee': 7
}
{
'f': 7
'uuu': 3
}
would give me if i search by 7 {
'fortnute': 7
'eee': 7
}
{
'f': 7
}

[burnt turret]

yeah you can't really do that; i don't know how to explain this properly lmao let me think

[burnt turret]

lemme just think this through one sec

i usually don't need queries of this format so i've forgotten a bunch

[twilit oyster]

How do I edit a document with pymongo

[burnt turret]

you'll probably need some kind of aggregation

[burnt turret]

collection.update_one

[twilit oyster]

Thanks

[burnt turret]

takes two arguments, the first dict is a filter, second is what to change

[haughty ravine]

thats what i want to do

hope ya got me

[burnt turret]

yeah,

you'll probably need some kind of aggregation

i need to look it up one second

[haughty ravine]

ok

[burnt turret]

that works nice

nvm

[haughty ravine]

hm

[burnt turret]

i can't think of a way to do that with a query probably just write the in python?

[haughty ravine]

???

[burnt turret]

we can try match key: value, but i can't think of a way to match value to key

[burnt turret]

get all documents, loop through and check for key: value pairs where value is = to your condition

or use the filter() function

[haughty ravine]

oof

well, thx 🙂

eeeck

so there is no way to make others keys from _id and custom one False?

[burnt turret]

https://docs.mongodb.com/manual/tutorial/project-fields-from-query-results/ here's their tutorial doc on this

only skimmed through and by the looks of it, no

[haughty ravine]

ooof

mongo trash

[burnt turret]

😔 yes

it's easy to setup and get started with and all, but i guess that's the only advantages i can think of

[young gyro]

How to use ROW_NUMBER() in where clause?(Postgresql)

[tepid cradle]

You can't use row_number() in the where clause. It's a window function and window functions can't be used directly in the where clause

What you do is you have to use the row_number() in select to add a calculated column to the query result, then use the whole thing as a subquery and filter it outside

Here's an example

select * from (
  select 
    join_date, 
    user_name, 
    row_number() over (order by join_date) as rk
  from user_info
)  t1 
where t1.rk < 20

[young gyro]

Thank you!

[young gyro]

How to use subquery with update statement? ._.

[tepid cradle]

I'm not conversant with update queries. But if you detail out what you want to do, I might be able to look it up and help you

[young gyro]

UPDATE table SET column = value WHERE here_is_rownumber = 1

@tepid cradle

[tepid cradle]

Does your table have an autoincrementing id column or something?

[young gyro]

No

[golden warren]

@burnt turret I'm back sorry, can you help me again?

[burnt turret]

ive forgotten what had happened; let me just look through

!remind 15m

[delicate field]

Not gonna happen.

Sorry, you can't do that here!

[golden warren]

You know where look ? Are you wan't i send it again?

[burnt turret]

i know where to look yeah

im eating rn sorry, ill look in a bit

[golden warren]

Okay ;)

[burnt turret]

sorry; i don't really know why that error would be coming :/

by subclassing commands.Bot, im asking

class DevBot(commands.Bot):
  ...
``` why are you doing this

you can run a bot just by making an instance of commands.Bot like

bot = commands.Bot(command_prefix=...)

[golden warren]

Because i learn subclassing. So i try things :)

[wheat umbra]

you can use cogs

[golden warren]

?

[wheat umbra]

it's basically like subclass

check out cogs in the docs first

get the basic idea

[golden warren]

I see but it's change nothing about my error.

[wheat umbra]

what error

[golden warren]

Ignoring exception in on_connect
Traceback (most recent call last):
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\devbot.py", line 27, in on_connect
    await DB().initialization(self)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\helpers\db.py", line 32, in initialization
    await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(member, member))
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 175, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 122, in _execute
    return await future
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 98, in run
    result = function()
sqlite3.OperationalError: no such column: DevBot

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : db.py                                   | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 07/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import aiosqlite
import discord
from discord.ext import commands

from src.helpers.logger import logger


class DB:
    def __init__(self):
        pass

    async def initialization(self, client):
        # Connection + Create tables
        usersdb = await aiosqlite.connect('src\\db\\users.db')
        logger.log('sucess', 'Connected on db.')
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS user(id INTEGER NOT NULL, user_name STRING NOT NULL, user_id INTEGER NOT NULL)")
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS infraction(id INTEGER NOT NULL, user_name STRING NOT NULL, user_id INTEGER NOT NULL, moderator_name STRING NOT NULL, moderator_id INTEGER NOT NULL, reason STRING, infranction_number INTEGER NOT NULL)")
        logger.log(
            'sucess', 'Table creation was successfull create in usersdb.')

        # Fetch all member name / id
        guild = client.get_guild(760780795970715660)
        for member in guild.members:
            await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(member, member))
            logger.log('sucess', 'Fetch all data from users.')


if __name__ == "__main__":
    pass

Ups long post sorry ><

[wheat umbra]

np

[golden warren]

np?

[wheat umbra]

no problem *

kinda hard to understand without line numbers

[golden warren]

Which line you wan't?

[wheat umbra]

don't worry I'm checking

[golden warren]

Okay :)

[wheat umbra]

there's more files

[golden warren]

What you need?

devbot?

[wheat umbra]

wait

show the devbot

[golden warren]

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : devbot.py                               | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 06/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import discord
from discord.ext import commands

from src.helpers.logger import Logger
from src.helpers.functions import Functions
from src.helpers.db import DB
from src.Mock.mock_bot import Bot


class DevBot(commands.Bot):
    def __init__(self):
        super().__init__(command_prefix=Bot.prefixes,
                         status=discord.Status.dnd)

        self.logger = Logger()

    async def on_connect(self):
        await DB().initialization(self)
        self.logger.log('sucess', 'Connected to discord.')

    async def on_ready(self):
        self.logger.log('sucess', 'Logged on as {0}'.format(self.user))

    async def on_disconnect(self):
        self.logger.log(
            'warning', '{0} is deconnected from discord.'.format(self.user))

    async def on_resumed(self):
        self.logger.log('sucess', 'Logged again as {0}'.format(self.user))

[wheat umbra]

helpers is also a file right?

or module

[golden warren]

Nop, dir

[wheat umbra]

yeah

DB() might be causing the error

show the code for it

remove sensitive data

[golden warren]

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : db.py                                   | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 07/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import aiosqlite
import discord
from discord.ext import commands

from src.helpers.logger import logger


class DB:
    def __init__(self):
        pass

    async def initialization(self, client):
        # Connection + Create tables
        usersdb = await aiosqlite.connect('src\\db\\users.db')
        logger.log('sucess', 'Connected on db.')
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS user(id INTEGER NOT NULL, user_name STRING NOT NULL, user_id INTEGER NOT NULL)")
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS infraction(id INTEGER NOT NULL, user_name STRING NOT NULL, user_id INTEGER NOT NULL, moderator_name STRING NOT NULL, moderator_id INTEGER NOT NULL, reason STRING, infranction_number INTEGER NOT NULL)")
        logger.log(
            'sucess', 'Table creation was successfull create in usersdb.')

        # Fetch all member name / id
        guild = client.get_guild(760780795970715660)
        for member in guild.members:
            await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(member, member))
            logger.log('sucess', 'Fetch all data from users.')


if __name__ == "__main__":
    pass

This one?

[wheat umbra]

yep

you have to assign to a variable first

in on_connect

[golden warren]

I need to assign what?

[wheat umbra]

got it

wait brb

in class DevBot check the on_connect function

[golden warren]

Check the function?

[wheat umbra]

look at it

should I rewrite it?

[golden warren]

I don't really understand what i need to do.

^^'

I'm french, i don't understand all :/

[wheat umbra]

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : devbot.py                               | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 06/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import discord
from discord.ext import commands

from src.helpers.logger import Logger
from src.helpers.functions import Functions
from src.helpers.db import DB
from src.Mock.mock_bot import Bot


class DevBot(commands.Bot):
    def __init__(self):
        super().__init__(command_prefix=Bot.prefixes,
                         status=discord.Status.dnd)

        self.logger = Logger()

    async def on_connect(self):
        _db = DB() 
        await _db.initialization(self)
        self.logger.log('sucess', 'Connected to discord.')

    async def on_ready(self):
        self.logger.log('sucess', 'Logged on as {0}'.format(self.user))

    async def on_disconnect(self):
        self.logger.log(
            'warning', '{0} is deconnected from discord.'.format(self.user))

    async def on_resumed(self):
        self.logger.log('sucess', 'Logged again as {0}'.format(self.user))

[gilded sleet]

well, the image says it

[wheat umbra]

yeah

[golden warren]

Nop always the same error @wheat umbra

[wheat umbra]

show us the errors

[golden warren]

Ignoring exception in on_connect
Traceback (most recent call last):
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\devbot.py", line 28, in on_connect
    await _db.initialization(self)
  File "C:\Users\33671\Documents\Programmation\Discord Bot\DevBot\src\helpers\db.py", line 35, in initialization
    await usersdb.execute("INSERT INTO user(id, user_name) VALUES({0.id}, {1.name})".format(member, member))
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 175, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 122, in _execute
    return await future
  File "C:\Users\33671\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 98, in run
    result = function()
sqlite3.OperationalError: no such column: DevBot

[burnt turret]

i really don't understand why you'd use a subclass

just making an instance is always easier

[wheat umbra]

yeah

but it might be for db

windows..

right?

totally forgot

might need time but don't know

[burnt turret]

nope even for handling a database, subclassing doesn't really seem necesasry

you can assign the database handler like a cursor to a bot variable

[wheat umbra]

yeah never used it so yeah

plus there's too many files affected

[burnt turret]

wdym by too many files affected

[wheat umbra]

look

have to eat soon

[burnt turret]

are you talking about the traceback?

[wheat umbra]

yeah

[burnt turret]

that doesn't mean it affects many files

[wheat umbra]

ik

no column

[burnt turret]

that's not the problem

he doesn't have a query where he tries to get a column called DevBot

[wheat umbra]

he didn't create the column itself right?

so can't really add or do anything with the records?

welp

[burnt turret]

there's no query trying to access a column like that either

[wheat umbra]

yeah I saw that

thought he already created the column

should we tell him

@golden warren

@jovial notch what's the error

[jovial notch]

no errors

[wheat umbra]

where's the bot.db?

[jovial notch]

I have defined that dont work its just only this db that doesnt work

[wheat umbra]

did you check the db from cli or anything

[proven arrow]

Your error is because you didnt wrap your values in quotes so it treats it as a column name. If you're inserting a string value in a column then you need to put it in quotes or else it will be interpreted as a column name. However, you should be using parameterised query for this, which will not only solve the problem you are having now, but will also be a safer/correct way to make this kind of query.

[burnt turret]

oh, that makes sense 😄 i didn't realize that

it's been a while since they've had that error

[wheat umbra]

those values

show me the line maybe?

still can't assure myself

@proven arrow which question did you answer?

[proven arrow]

To the one I replied to

Or the 3rror they had

[wheat umbra]

person's name pls

[golden warren]

@proven arrow What i need to put with quotes?

And wich parameterised query i need to do?

[wheat umbra]

db.py line 35?

@proven arrow btw don't we normally use "?" as a placeholder??

[proven arrow]

Yeah you do in sqlite, but in other modules it might be different

[wheat umbra]

thanks. so the only problem was those values? or did I mess something up

[proven arrow]

I'm not sure what you are referring to?

[wheat umbra]

look at the on_connect function

[proven arrow]

I was just saying why they got the error in the message they sent

The correct way to do it would be:

await usersdb.execute("INSERT INTO user(id, user_name) VALUES(?, ?)", (member.id, member.name))

[golden warren]

Like this, without subclassing, is good?

# -*- coding: utf-8 -*-

# + ------------------------------------------------- + #
# | File    : devbot.py                               | #
# | Author  : UgolinDeveloper                         | #
# | Date    : 06/01/21                                | #
# + ------------------------------------------------- + #

# Import modules / files
import discord
from discord.ext import commands

from src.helpers.logger import Logger
from src.helpers.functions import Functions
from src.helpers.db import DB
from src.Mock.mock_bot import Bot

DevBot = commands.Bot(command_prefix=Bot.prefixes,
                      status=discord.Status.dnd)

logger = Logger()


async def on_connect():
    _db = DB()
    await _db.initialization(DevBot)
    logger.log('sucess', 'Connected to discord.')


async def on_ready():
    logger.log('sucess', 'Logged on as {0}'.format(DevBot))


async def on_disconnect():
    logger.log(
        'warning', '{0} is deconnected from discord.'.format(DevBot))


async def on_resumed():
    logger.log('sucess', 'Logged again as {0}'.format(DevBot))

[wheat umbra]

for bots yes

[golden warren]

Nice!

But, when i launched it, my programm launch and it's run but othing is printing. Have you got an idea?

[proven arrow]

Although i think your missing the decorators for the events but better ask in the discord channel

[wheat umbra]

@golden warren is it still throwing errors?

[golden warren]

Okay it work thx!

[wheat umbra]

@proven arrow what's your view on sqlalchemy?

[proven arrow]

I never have used sqlalchemy, but i do like ORMs and would use them where applicable.

[ember terrace]

I'm using sqlite/alchemy, if I have a shirt table which has a colors field which can be any number of a set of 10 different colours (strings). Is it worth making a color table and establishing a relationship? Then I would have a lot of redundant data in my color table wouldn't I?

[wheat umbra]

nice!. I mostly use sqlalchemy with orm it's pretty cool

[proven arrow]

I use eloquent which is the one with php laravel, in most of my applications. Works great. When i use python i mostly just write raw queries.

[wheat umbra]

oh wait another question

ok nvm

[proven arrow]

@ember terrace Yes because then you can do queries like get me all shirts with x colour

Not that you cant do the same with a single table, however normalising it would be the better approach

And if colours was to increase in the future its easier to implement it

[ember terrace]

The alternative would be converting a list into a string for storing inside the shirt? I guess you'd still have redundant data there, just not in its own table.

but also have to convert when extracting, and queries would be a headache

[proven arrow]

It wont be redundant if you make the colours table.

[golden warren]

Hum, when do that, nothing is write in my db. But when i do print(member.id) i show me all id. Do you have any idea?

        # Fetch all member name / id
        guild = client.get_guild(760780795970715660)
        for member in guild.members:
            await usersdb.execute("INSERT INTO user(user_id, user_name) VALUES(?, ?)", (member.id, member.name))
            logger.log('sucess', 'Fetch all data from users.')

[ember terrace]

True, I'm too used to thinking in programming terms now, put off working with DB's for too long. thanks 🙂

[proven arrow]

Your need to commit also its better to use executemany when in a for loop like that @golden warren
So dont make an execute call for each iteration but rather after its finished. Especially if you will have many items in the list.

await db_connection.commit()

[golden warren]

It work thx :)

[ember terrace]

Is there any recommended structure for your sqlalchemy python classes and operations methods? Anything out there worth reading?

[wheat umbra]

if it's about sqlalchemy (orm) than first check the docs and examples. then just look around for game databases for complex structures

I would recommend you to use database designer software to quickly check your ideas and quickly implement, do tests with sqlalchemy (orm). it's pretty efficient in terms of testing

[ember terrace]

like https://dbdiagram.io/home?

[wheat umbra]

exactly

[ember terrace]

Cool, I did that bit already, was just struggling to find examples or principles on how to structure the db related code. It seems that a models.py is a common pattern and where I'll start.

[wheat umbra]

do you mean how to structure files / models or how to write it

if you want multiple files with models in them. then you have to create a separate file with base model and just import them in the model files. then a final or main script to import every model including the base and use it.

if you want example codes I can show you some

[ember terrace]

That sound unnecessarily complex, I think I'll be okay with just having the models in one place.

[wheat umbra]

that's the general idea. but if it gets bigger than splitting them is the best thing. but normally splitting seems harder but it's actually very easy

I'm going to tinker around with sqlalchemy a bit . been away from python for a long time

[ember terrace]

Thanks for the help

[wheat umbra]

happy to help

[agile laurel]

hey guys

any one familiar with this error?

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationFailure: Authentication failed., full error: {'ok': 0, 'errmsg': 'Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}

I was following up a tutorial on how to create a discord bot leveling system using mongodb

and unfortunately after following the hole video this kept happening.

:/

[torpid elk]

hi

I'm having trouble doing db.execute() after my recursion

please help

here is my error:
register testing.execute(sql, val) execute result = self._query(query) _read_packet packet.raise_for_error() in raise_for_error err.raise_mysql_exception(self._data)

pymysql.err.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

[torpid elk]

how should i fix this?

[golden warren]

Hello their! I have this error: sqlite3.OperationalError: near "CASE": syntax error
Code:

        # Fetch all member name / id
        guild = client.get_guild(760780795970715660)
        guild = guild.chunk()
        for member in await guild:
            print(member)
            await usersdb.execute(f"""
            SELECT user_id
                CASE
                    WHEN user_id != {member.id}
                    THEN INSERT INTO user(user_id, user_name) VALUES(?, ?)
                END
            FROM users
            """, (member.id, member.name))
            await usersdb.commit()
        logger.log('sucess', 'Fetch all data from users.')

[proven arrow]

You cant do that

What is it you are trying to do?

for case statements it is WHEN condition THEN result so insert wont work

[golden warren]

Okay, how can i use insert with a condition?

[golden warren]

THEN is always str ?

[proven arrow]

Your better of handling this stuff inside your application rather than sql

for member in guild:
  if member.id != x:
    insert

[golden warren]

What mean x?

[golden warren]

How can i get all id of my table?

[proven arrow]

Can you first explain in more detail what your trying to do, to get a better solution.

[golden warren]

I initialize my db, i would like to get all member name and id. I do it. But when i relaunch my bot, it enter the same value. So i would like to check if a member id is already inside my db.

I'm french, my english is not very good. Do you understand what i said?

[proven arrow]

So each user can only be in the table once?

[golden warren]

Yes.

[proven arrow]

Then add a unique constraint to the user_id column in the database. This way the user id column can never have more than 1 of the same value

[golden warren]

Hum, i don't really see how do that. Can you be more precisely ?

[proven arrow]

I dont know how to be more precise? Do you know what a unique column is?

[golden warren]

Nop, can you explain it?

[proven arrow]

Unique columns only have unique values. A value cant exist in the column more than once. So that is what you want. You dont want the same user_id twice.

[golden warren]

Where can i fond some example to this what it look?

[proven arrow]

You can find the example here on how to use it. https://www.db-fiddle.com/f/hDdBzCCfdyZHBwuPwHqZFH/0

When you insert you would want to use insert or ignore. This will try to insert a row, however if the user_id already exists then it will ignore it

[golden warren]

Okay so i do that:

class DB:
    def __init__(self):
        pass

    async def initialization(self, client):
        # Connection + Create tables
        usersdb = await aiosqlite.connect('src\\db\\users.db')
        logger.log('sucess', 'Connected on db.')
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS user(user_id INTEGER NOT NULL UNIQUE, user_name STRING NOT NULL)")
        await usersdb.execute(
            "CREATE TABLE IF NOT EXISTS infraction(id INTEGER NOT NULL, user_name STRING NOT NULL, user_id INTEGER NOT NULL, moderator_name STRING NOT NULL, moderator_id INTEGER NOT NULL, reason STRING, infranction_number INTEGER NOT NULL)")
        logger.log(
            'sucess', 'Table creation was successfull create in usersdb.')

        # Fetch all member name / id
        guild = client.get_guild(760780795970715660)
        guild = guild.chunk()
        for member in await guild:
            print(member)
            await usersdb.execute("INSERT OR IGNORE INTO user(user_id, user_name) VALUES(?, ?)", (member.id, member.name))
            await usersdb.commit()
        logger.log('sucess', 'Fetch all data from users.')

[golden warren]

And i have this error: sqlite3.OperationalError: database is locked

[proven arrow]

Yeah code is fine

Your error is because something else is writing to the database

If you have any application for browsing the datbase be sure to close that too

[golden warren]

Hum... I write nothing else, all db management is in this class.

Oh damn.

Okay i see.

Okay nice it work!

Thx very much!

[torn sphinx]

What is wrong with this? nothing gets into the DB.```py
@commands.command(aliases=["add-dj"])
@commands.has_permissions(administrator=True)
async def dj(self, ctx, *, role: discord.Role):
await ctx.send(f'{self.config.success} Added <@&{role.id}> to DJ roles.')
try:
collection = self.db1.DataBase_1.settings

        collection.insert_one({ "_id": int(ctx.guild.id), "dj": [ int(role.id) ] })

    except Exception as e:
        print(e)
        myquery = { "_id": int(ctx.guild.id) }

        newvalues = { "$addToSet": { "_id": int(ctx.guild.id), "dj": int(role.id) } }

        collection.update_one(myquery, newvalues)```

[burnt turret]

self.db1.DataBase_1.settings this is confusing

are you sure that's right?

is db1 an instance of MongoClient or something, and then Database_1 is the actual database, and settings is the collection?

confusing variable names

[torn sphinx]

I keep getting an error when I try to drop a row from my pandas dataframe, anyone that can help me with it?

[low iron]

i'm trying to export my postgresql database using pgAdmin but the option in the tool bar is greyed out and if i right click on the database it's not there

[jovial notch]

I got a question, how could I have my output sorted from highest len(warnings) to lowest?

[burnt turret]

!d sorted

[delicate field]

`sorted`

sorted(iterable, *, key=None, reverse=False)```
Return a new sorted list from the items in *iterable*.

Has two optional arguments which must be specified as keyword arguments.

*key* specifies a function of one argument that is used to extract a comparison key from each element in *iterable* (for example, `key=str.lower`). The default value is `None` (compare the elements directly).

*reverse* is a boolean value. If set to `True`, then the list elements are sorted as if each comparison were reversed.

Use [`functools.cmp_to_key()`](functools.html#functools.cmp_to_key "functools.cmp_to_key") to convert an old-style *cmp* function to a *key* function.

The built-in [`sorted()`](#sorted "sorted") function is guaranteed to be stable. A sort is stable if it guarantees not to change the relative order of elements that compare equal — this is helpful for sorting in multiple passes (for example, sort by department, then by salary grade).... [read more](https://docs.python.org/3/library/functions.html#sorted)

[burnt turret]

You’d use the key parameter to be len

Not on-topic for this channel though @jovial notch

[jovial notch]

This isn't off topic its called #databases @burnt turret

[cobalt talon]

sql = c.execute('SELECT username FROM account').fetchone()
check = str(sql).strip("('',)'")

Suspicious argument in str.strip call

[burnt turret]

it is called databases, and your question didn't seem to pertain to databases

there's no sign of it being related to a database, by reading the question you asked

[torn sphinx]

can someone go to #help-chili

[sinful condor]

can someone help me I use aiosqlite but my database isnt working right and its saying database is locked

[burnt turret]

someone else had this issue today; read from here #databases message

check and see if that's your issue too

[sinful condor]

I dont really understand what that means my bot is the only thing accessing my database

@burnt turret

[burnt turret]

you sure you don't have some other program, used to view the database in a GUI?

[sinful condor]

no

one problem might be not having a global db thing would that help

@burnt turret

[burnt turret]

wdym?

also are you sure you don't have multiple instances of your bot running?

[sinful condor]

yes

I can make sure but im pretty sure

[burnt turret]

this is what i could find online, that's why i was asking you that

[sinful condor]

ok so

what database should I switch to then to handle it to never have to change it again

I changed it from json to sqlite to aiosqlite

[burnt turret]

for discord bots, postgresql is really good

you'd use the asyncpg module

[sinful condor]

ok can you give an example of how to set that up

[burnt turret]

well, changing from sqlite to aiosqlite isn't really a change in database 😄

[sinful condor]

wdym

it took me 6 hours to change it

[burnt turret]

well you'd have to install postgresql, their website will explain it

[sinful condor]

ok

[burnt turret]

they're both using the same database underneath, aiosqlite and sqlite are just the python libraries you use to interact with it

[sinful condor]

yeah but you had to change alot to use it

or I did one of the 2 wrong

@burnt turret for now I am just going to try to unlock my database is there a way to do that

[burnt turret]

im not sure how you'd do that with aiosqlite, the stackoverflow post i sent the screenshot from said you could pass timeout= to your sqlite.connect(, and set the timeout to be higher

dunno if that works for aiosqlite

[torn sphinx]

Aight' need some help with mongodb. Ever since i added levels to my bot, where basically the bot updates the user's document on each message, but the problem is that now the bot is slow since updating/inserting or any action that's related to mongodb is slow. Any idea how i can make it faster?

[wheat umbra]

@torn sphinx try async mongodb driver

[torn sphinx]

?

[wheat umbra]

async driver such as "motor"

[torn sphinx]

oh

i see

[torn sphinx]

thanks

[wheat umbra]

np

[torn sphinx]

@client.command()
async def test(ctx,level):
    db = sqlite3.connect("Levels.sqlite")
    cursor = db.cursor()
    cursor.execute(f"SELECT level FROM main WHERE member = {ctx.author.id}")
    result = cursor.fetchone()
    print(result)
    
    if result is None:
        sql = ("INSERT INTO main(member, level) VALUES(?,?)")
        val = (ctx.author.id,level)
        await ctx.send("Added")

    elif result is not None:
        sql = ("UPDATE main SET member = ? WHERE level = ?")
        val = (ctx.author.id,level)
        await ctx.send("Updated")

    cursor.execute(sql,val)
    db.commit()
    cursor.close()
    db.close()

I'm trying to update my database tables but I can't could someone help me?

someone ping me if they can help

[proven arrow]

You should use UPSERT for this kind of stuff. It will try to insert, but if the insert violates a unique constraint then it will update it instead. Saves you from writing 3 queries as well.

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

[torn sphinx]

kk thx

[worldly plaza]

What is the difference between remove and pop

[heady hatch]

Remove deletes the first matched value in a list.

Pop removes the value from a specific index and returns it.

a = [0, 2, 3, 2]
a.remove(2)
a

OUTPUT:
[0, 3, 2]

a = [4, 3, 5] 
a.pop(1)

3
a
[4, 5]

[worldly plaza]

I am trying to remove some information from a json, I have sort of a class with the users name and then the reason for the warn and I know how to remove the users name but not the users reason.

Oop sorry for ping

[heady hatch]

Well, you first have to convert the json to a dictionary, then you access the item in the list nested in the dictionary using an index.

[worldly plaza]

It is in a curly bracket, to remove it I was going to do this

report['users'].remove({
            'name': member.id,
            'reasons': 
            })

just don't know what to put in the 'reasons'

[heady hatch]

Well, that's an invalid JSON format I believe.

[worldly plaza]

That is not in the json

that is in my bot's code to remove the info

[heady hatch]

{ 'name': member.id, 'reasons': }

is both invalid Dictionary or JSON

[worldly plaza]

my json looks like this

{"users": [{"name": 768615938055471116, "reasons": ["e"]}]}

I want to remove that users ID and their reason

[heady hatch]

Okay, one second.

report['users'][0] = {"name": "", "reasons": []}

does that make sense?

[worldly plaza]

How would I remove the reason in the code?

[heady hatch]

because it's a dictionary, there has to be a key and a value to that key.

You cannot have {"reason": } by itself. there must be something in its place.

you can always put None for the value.

or you can remove 'reason' completely.

report['users'][0].update({"reasons": None})

and then it looks like so.

{'users': [{'name': 768615938055471116, 'reasons': None}]}

to completely remove it you can do report['users'][0].pop("reasons", None)

and the result looks likes this.

{'users': [{'name': 768615938055471116}]}

[worldly plaza]

@heady hatch I got the command to run with no errors but it doesn't remove the data from the json

[heady hatch]

can you provide your code?

[worldly plaza]

for current_user in report['users']:
            report['users'][0].update({"reasons": None})
            await ctx.send('done')

[heady hatch]

wo you need to do this...

for current_user in report['users']: current_user.pop("reasons", None) await ctx.send('done')

you started a loop where each user is represented by current_user so you no longer have to specify the index in the list.

[worldly plaza]

It completes the command however doesn't remove the data

[heady hatch]

Hmm, since we are in the database channel, are you executing the change and committing it to the database?

In your example you showed me using a dictionary as your source of data.

If you are retrieving it from the database, you need to create a query statement that removes that column from your table.

[worldly plaza]

Here is my full command and my json file

@commands.command(pass_context = True)
    @commands.has_permissions(kick_members=True)
    async def unwarn(self, ctx, member:discord.Member):
        with open('reports.json', encoding='utf-8') as f:
            try:
                report = json.load(f)
            except ValueError:
                report = {}
                report['users'] = []
        for current_user in report['users']:
            current_user.pop("reasons", None)
            await ctx.send('done')

{"users": [{"name": 768615938055471116, "reasons": ["e"]}]}

[heady hatch]

one second.

so what you have done is opened and read the content of the JSON file.

so you then pop() the reason in your program but not the file itself.

What you need to do now is write to the file the changes you have made.

[worldly plaza]

@heady hatch So dump it?

Commit it to the main file

[heady hatch]

with open('reports.json', encoding='utf-8') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
for current_user in report['users']:
current_user.pop("reasons", None)

with open('new_reports.json', 'w') as file:
json.dump(report, file)

or overwrite your existing file.

[worldly plaza]

@heady hatch Alright, I got it to work, it will now remove the infractions but it messes the json so the next time I try to warn it does not work

[heady hatch]

That's great to hear.

Thanks for your patience.

So your exception removes your report data.

lets take a look

[worldly plaza]

When I remove all their infractions it will remove the preset class of name and reasons. so the next time I try and warn someone it has no where to append that data to

[heady hatch]

Sorry

got pulled away for fatherly duties.

here you go
`with open('reports.json') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
for current_user in report['users']:
current_user.pop("reasons", None)

with open('reports.json', 'w') as f:
json.dump(report, f)`

I gotta go though. I'll check in on my mobile app if you have any further questions.

On phone. Did that work for you?

[snow imp]

why isnt this working?

this is the error

[wheat umbra]

@snow imp orm right?

[snow imp]

?

sqlalchemy

[wheat umbra]

yes. sqlalchemy orm?

so check the line before the else statement. you cannot query using the "arg"

[burnt turret]

i don't think sqlalchemy is asnync right?

[wheat umbra]

yeah

[burnt turret]

asking because you ought to be using an async db driver for discord bots

[wheat umbra]

but there's async version of sqlalchemy too

[burnt turret]

i think i'd seen an async sqlalchemy

yeah

[worldly plaza]

No sorry, It is removing the entire "reason" class and ruining the format of the json so any other warnings cannot be cast.

[heady hatch]

Was that not what you requested? #databases message

`with open('reports.json') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
for current_user in report['users']:
current_user.update({"reasons": []})

with open('reports.json', 'w') as f:
json.dump(report, f)`

if you want to remove the username you can do the following:

`with open('reports.json') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
for current_user in report['users']:
current_user.update({"name": None})

with open('reports.json', 'w') as f:
json.dump(report, f)`

if you want to do both:

`with open('reports.json') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
for current_user in report['users']:
current_user.update({"name": None, "reasons": []})

with open('reports.json', 'w') as f:
json.dump(report, f)`

[sinful condor]

Ok so I am trying to use either postgres or mysql. 2 questions 1. Which is better for a discord bot in 100+ servers? 2. Where can I host a database for whichever one is better for free?

[worldly plaza]

Great! it worked! Sorry to bother you once more but is there a way to completely wipe the json when needed?

[torn sphinx]

This is my code ! For connecting my discord bot to mongodb , but it shpws this error and for password / db name i have written it that way to censor my info but it tried putting info it shows this error .

Ping me if anyone knows

the cluster one is line 20 which has that url

[wheat umbra]

@torn sphinx try to get the full Traceback. most of it got cut on the right side

[wheat umbra]

install dnspython

[torn sphinx]

I did

still error are coming

now for line 27

which is collection.insert_one(post)

[wheat umbra]

Traceback?

[heady hatch]

No worries. So you need be clear what you want to wipe out.
The entire file is JSON.

If you mean each instance of a user dictionary then that's different.

I think you mean wipe all the user instances:

`with open('reports.json') as f:
try:
report = json.load(f)
except ValueError:
report = {}
report['users'] = []
report['users'] = [] # this line will clear all instances of users

with open('reports.json', 'w') as f:
json.dump(report, f)`

[worldly plaza]

I have it set to re write all the needed information if the json is empty so I can just totally wipe the json.

[heady hatch]

then you simply just create the file like so and it will overwrite the contents and leave it blank:

with open('reports.json', 'w') as f: print("file cleared")

the file will literally have no content at all.

[worldly plaza]

Will I need to dump it after I do that?

[amber helm]

i am new here anyone help me to learn python language from basic.??

[lethal depot]

code: https://mystb.in/BrazilRaymondAbsent.python
error: https://mystb.in/PasswordsSuggestionMonroe.sql
anyone know what this error means? it seems its something to do with my asyncpg connection

[heady hatch]

cursor = cnx.cursor(buffered=True)

[burnt turret]

haha just read a stackoverflow post that said that

it's because there are records left unread in the cursor before trying to execute another query or something along those lines

[torn sphinx]

is there any way to use another database in repl it?

[burnt turret]

if the database is remotely hosted i assume you should be able to connect to it, if you're able to install the modules necessary to use the db

but not by hosting it on repl.it

wait, would sqlite work? it's file-based, so you could try that

[torn sphinx]

k

thanks

[lethal depot]

code: https://mystb.in/BrazilRaymondAbsent.python
error: https://mystb.in/PasswordsSuggestionMonroe.sql
anyone know why asyncpg is erroring? this is usuallly how i fetch from dbs

[heady hatch]

@lethal depot What database are you using? SQLite3?

[brazen charm]

@lethal depot if I remember that error was caused by either not being in a transaction context manager or having Concurrent operations on the same pool worker

[lethal depot]

postgres, hence asyncpg

[brazen charm]

Timeout

[lethal depot]

umbra suggested i switch to putting the messages into a dict then a task that then uploaded into the db would that work?

[brazen charm]

That would probably work yes

Also would be alot more efficient

[lethal depot]

@brazen charm so something like this:

    async def on_message(self, message):
        if message.author not in self.typed:
            self.typed.append(message.author)
            self.messages.append(message)



    @tasks.loop(seconds=60)
    async def econtask(self):
        self.typed = []
        for message in self.messages:
            con = await self.bot.pg_con.acquire()
            query = """
                    IF NOT EXISTS (SELECT total_coins FROM economy WHERE user_id = $1)
                        INSERT INTO economy (user_id, total_coins) 
                        VALUES ($1, $2)
                    ELSE
                        UPDATE economy SET total_coins = total_coins + 10 WHERE user_id = $1'
                    """
            async with con.transaction():
                await con.execute(query, message.author.id, 0)
            self.messages.append(message)```

sorry shouldve used a mystbin

@brazen charm when i did that i still got the same error

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/ext/tasks/__init__.py", line 101, in _loop
    await self.coro(*args, **kwargs)
  File "/home/admin/comfybot/cogs/economy.py", line 45, in econtask
    async with con.transaction():
  File "/home/admin/.local/lib/python3.7/site-packages/asyncpg/transaction.py", line 68, in __aenter__
    await self.start()
  File "/home/admin/.local/lib/python3.7/site-packages/asyncpg/transaction.py", line 138, in start
    await self._connection.execute(query)
  File "/home/admin/.local/lib/python3.7/site-packages/asyncpg/connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 316, in query
RuntimeError: Task <Task pending coro=<Loop._loop() running at /usr/local/lib/python3.7/dist-packages/discord/ext/tasks/__init__.py:101>> got Future <Future pending cb=[Protocol._on_waiter_completed()]> attached to a different loop

[brazen charm]

Out of curiosity

How are you creating the initial connection pool

[lethal depot]

yeah i messed up there @brazen charm i did asyncio.run(connectionpoolcreatefunchere)

instead of using bot loop

                    VALUES ($1, 10)
                    ON CONFLICT (total_coins) DO UPDATE SET total_coins = total_coins + 10
``` how would i fix the ambiguous error here?

[tribal glacier]

how can i create many to many relation between two tables

[brazen charm]

You shouldn't ever have a many to many relationship

You either want one to one or many to one

[proven arrow]

What's wrong with many to many? They are perfectly fine to have.

[proven arrow]

You would need to have an extra table that sits in the middle of your 2 tables. This is basically the junction table, and inside this you can store the primary keys of your other 2 tables.

[pearl bloom]

Which database would be the best to store large amounts of data

[proven arrow]

What kind of data? Structured? Unstructured?

How often will you read/write?

[pearl bloom]

well I will be reading every time when user wants to find something

[proven arrow]

What is it your working on?

[pearl bloom]

stock data

[proven arrow]

Hard to say without seeing exactly what your working with, or at what scale. But I'm guessing if you was at a large scale then you probably won't be asking here. In the case postgres or even MySQL will so fine.

[tribal glacier]

yes indeed, the db.relationship where should I use it? I wrote it in the first 2 tables

[pearl bloom]

thanks

[proven arrow]

Look at the example below of a many to many table schema. A user can have many roles, and a role can belong to many users. The user and roles table have no knowledge of the third table called user_roles. This extra table is there to just store our relationships.

Users

• id (primary key)
• name

Roles

• id (primary key)
• name

user_roles

• id
• user_id (Foreign Key for users table)
• role_id (Foreign key for roles table)

[tribal glacier]

i understand, thanks!

[velvet fable]

I am trying to find the data rather then the _id (using MongoDB)

_id : 32423423432
List : ABC
DM : No

I am trying to find ABC so I can get the _id, but every time I try to do it, it tells me data equals None.
Can someone help?

[torn sphinx]

I don't necessarily have an issue so much as I really need to figure out how I can speed this up. I'm building a database of reddit comments to train a chatbot using comments and replies. Slapping the information of a reply into the database is fast enough but the second I start searching for the parent comment of that reply, things go DRASTICALLY slower. I ran it for 6 hours and only got 33k pairs (comments linked to replies) which is not nearly enough and made me realize I really need to speed this up lol

Loop collecting data from dataset

#Build Data
    with open("C:/Repos/Chatbot/Data/RedditDataset", buffering = 100) as File:
        for Row in File:
            RowCount += 1
            Row = Json.loads(Row)
            CommentID = Row['id']
            Comment = Functions.FormatData(Row["body"])
            ParentID = Row["parent_id"].split("_")[1]
            Parent = Functions.FindParent(ParentID) #Pretty sure this is causing the slow down
            Subreddit = Row["subreddit"]
            Score = Row["score"]
            Time = Row["created_utc"]```

FindParent function
```py
def FindParent(ParentID):
    try:
        Cursor.execute(f"SELECT Comment FROM RedditComments WHERE CommentID = '{ParentID}' LIMIT 1")
        Result = Cursor.fetchone()
        if Result != None:
            return Result[0]
        else: return False
    except Sql.Error as Error:
        print("FindParent", Error)
        return False```

Not sure but how I slap the data into the database might help give insight to the issue so I wanted to include it

Adding to the "transaction queue"

def SqlInsert(ParentID, Parent, CommentID, Comment, Subreddit, Score, Time):
    try:
        Command = f"""INSERT INTO RedditComments (ParentID, Parent, CommentID, Comment, Subreddit, Score, Unix) VALUES ("{ParentID}", "{Parent}", "{CommentID}", "{Comment}", "{Subreddit}", "{Score}", "{int(Time)}");"""
        TransactionBuilder(Command)
    except Exception as Error:
        print("SQL Insert: ", Error)```

Slapping it into the db
```py
#SQL Functions
def TransactionBuilder(Command):
    global SqlTransaction
    SqlTransaction.append(Command)

    if len(SqlTransaction) > 1000:
        Cursor.execute("BEGIN TRANSACTION")
        for Command in SqlTransaction:
            try:
                Cursor.execute(Command)
            except:
                pass
            Connection.commit()
            SqlTransaction = []```

Using sqlite3 if that's any help

[pure cypress]

What is your table's primary key?

It should be the comment ID

Though in sqlite, to do that, I think you have to use some special sql blurb to disable the default autoincrement PK column

In any case, you could manually create an index on that column

Oh, that isn't strictly necessary. If you have an integer PK declared, it will just use that instead of the default.

I was thinking of this https://www.sqlite.org/withoutrowid.html

[lapis wind]

so I just switched over to using mongodb for my discord bot today, but I'm getting this weird ass error when using motor:
https://paste.pythondiscord.com/niyohulufu.sql
it's just happening when I try to do this:
storage = await settings.find_one({"id": str(id)})
Idk why this is happening, if anyone knows please ping me 🙂

[burnt turret]

It just says timed out

It's happened to me while using atlas

I just installed it locally instead

[lapis wind]

yeah but I only started the bot like 30 seconds ago-

and it keeps repeating the error until I shut it off

[burnt turret]

That's odd

I've had inconsistent results while using atlas, it'd work fine sometimes but time out at other times

As I didn't want that inconsistency I just installed mongodb locally

On the VM I run my bot

[lapis wind]

the whole point of using it for me is not having a flat file

and being able to access it from other places

[burnt turret]

Well you wouldn't have a flat file if you installed mongodb, it's a db server

And I'm pretty sure there are ways you can make the db accessible from anywhere

I've done that with SQL dbs but haven't tried mongo, but I'm guessing it won't be too hard

[torn sphinx]

pymongo is blocking remember that

https://pypi.org/project/motor

[lapis wind]

but I'm getting this weird ass error when using motor:

lmao

[burnt turret]

motor isn't all that good though :/

[wooden grotto]

what are popular ORMs people work with in python? SQLAcademy?

[golden warren]

I would like to know, how can i delete a line of my table. I try this:

DB(self.client).usersdb.execute(
                'DELETE FROM user WHERE user_id={}'.format(member.id))

But i don't know how to delete.

[tawny fulcrum]

Hey guys, I want to importing a csv file daily from ftp to a database, what kind of database should I use ?

[torn sphinx]

Hi I have a question regarding updating my database could someone help me?

ping me back and i'll send my code over 🙂

[burnt turret]

What happens when you do this? Also, don't use .format, it is unsafe; see the pinned messages in this channel if you wanna know why

[golden warren]

It happen nothing :(

[burnt turret]

You have to commit the changes as well

connection_object.commit(), you do this after all statements that make some changes to your data

[golden warren]

Yes i commit it. But nothing is do.

[burnt turret]

Just ask your question and someone will answer if they can

[torn sphinx]

kk

[golden warren]

            DB(self.client).usersdb.execute(f'''
                DELETE FROM user WHERE user_id={member.id}''')
            DB(self.client).usersdb.commit()

I would like to compare user_id inside my db and then delete line. Have you got idea?

[burnt turret]

Afaik you execute statements with the cursor, and commit with the connection but you seem to be doing both with the cursor

The query looks fine, unless you've stored the id as a string or something

[golden warren]

It' an int.

[golden warren]

I don't understand this ^^'

[burnt turret]

The database drivers I use generally has

1. a connection object to the database
   like
   cnx = mysql.connector.connect(...)
2. a cursor object
   like
   cur = cnx.cursor()

We execute statements using the cursor (cur in this example, cur.execute()) and commit changes with the connection (cnx.commit())

You're executing and committing with the same object

I don't know what database you're using and it's specific so I cannot be sure

Also, read the pinned messages on why you should not use string formatting @golden warren

[torn sphinx]

@client.command()
async def test(ctx,level):
    db = sqlite3.connect("Levels.sqlite")
    cursor = db.cursor()
    cursor.execute(f"SELECT level FROM main WHERE member = {ctx.author.id}")
    result = cursor.fetchone()
    print(result)
    
    if result is None:
        sql = ("INSERT INTO main(member, level) VALUES(?,?)")
        val = (ctx.author.id,level)
        await ctx.send("Added")

    if result is not None:
        sql = ("UPDATE main SET member = ? WHERE level = ?")
        val = (ctx.author.id,level)
        await ctx.send("Updated")
        

    cursor.execute(sql,val)
    cursor.close()
    db.close()
    db.commit()

If i add a new entry to the table it works however if i try to update the entries in the table they don't update.

[golden warren]

Okay i see, i going to try something thx!

[burnt turret]

I don't know how sqlite works but committing after closing doesn't seem right, try switching the order

[torn sphinx]

oh, in my code it's before

[burnt turret]

I see in your code

db.close()
db.commit()

[torn sphinx]

i mean in my ide it's right when i copy pasted it over here i messed up

[golden warren]

@burnt turret I do this in my db.py

class DB:
    def __init__(self, client):
        self.client = client
        self.usersdb = sqlite3.connect('src\\db\\users.db')
        self.usersdb_cursor = self.usersdb.cursor()

Also, i do this in my events.py:

    @commands.Cog.listener()
    async def on_member_remove(self, member):
        try:
            DB(self.client).usersdb_cursor.execute(f'''
                DELETE FROM user WHERE user_id={member.id}''')
            await self.logs_channel.send('`{0}` leaving! Data deleted.'.format(member))
        except Exception as e:
            Logger.log('error', e)

But i do nothing, have you got idea?

[burnt turret]

Where's the commit?

Also, Everytime you're making a new instance for the query

Just make one DB() and then use it's methods throughout the cog?

Also don't use fstrings in a query as well lmao, did you read the pinned message?

[torn sphinx]

Hi

[torn sphinx]

How would I create a variable if someone logs in and then do a if statement to store that they have previously logged in

[junior perch]

Question: Should I create a column in a table who's value is defined by another tables summary relating to that column?

[foggy iron]

someone help me pls? i have a json data with a lot of 'prices' per 'dates'='timestamp" and i was trying to create a XY graphic about it, but idk how i make the 'timestamp' have a id for i can put it in order on the X graphic

[fading breach]

Hi, I want to be able to store account details in mongodb with pymongo, how can I do this?