databases

[distant radish]

windows users are able to run the script just fine

import mysql.connector

counter = 0

def parseSQLStatements(filename):
    try:
        sqlFile = open(filename, 'rt')
        sqlStatements = sqlFile.read()
        sqlFile.close()
        return sqlStatements
    except:
        print("Error opening file, closing program!")
        return None
        
    
def getStatementList(queryStrings):
    accumulator = ""
    queries = list()
    for character in queryStrings:
        accumulator += character
        if character == ';':
            queries.append(accumulator)
            accumulator = ""
    return queries

def resetDatabase():
    overseer = mysql.connector.connect(
    host="localhost",
    user="root",
    password="")

    cur = overseer.cursor()

    cur.execute("DROP DATABASE IF EXISTS overseer;")

    cur.execute("CREATE DATABASE Overseer;")

    cur.close()

def executeQueries(cursor, statements):
    global counter
    for i in statements:
        print(i)
        cursor.execute(i)
        counter+= 1

def main():
    resetDatabase()

    overseer = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="overseer")

    cur = overseer.cursor()

    ddl = parseSQLStatements("DDL.txt")
    dml = parseSQLStatements("DML.txt")

    if ddl == None: return
    if dml == None: return

    statements = getStatementList(ddl)
    executeQueries(cur, statements)
    statements = getStatementList(dml)
    executeQueries(cur, statements)
    
    overseer.commit()

    print("A total of: "  +str(counter) + " queries executed")

    cur.close()
    
if __name__ == "__main__":
    main()```

here's the script

[unreal jay]

do they have mysql installed and setup on thier macs?

[distant radish]

yep

we are using xampp

all our php code works

I had them install the mysql package for python too

[proven arrow]

Try 127.0.0.1 as the host

[distant radish]

I'll tell them to try it out

any other suggestions if that doesn't work?

[proven arrow]

Try specify the port you setup for it

Also can you connect to it through the xampp panel?

[distant radish]

using phpmyadmin?

we can just fine through that

[proven arrow]

Also I think xampp comes with phpmyadminq

Yeah try that

Oh ok I see

[distant radish]

yeah they can, but python cannot for whatever reason

[proven arrow]

You might need to specify the port as well then. Usually it’s 3306, but maybe xampp might have set a different one.

[distant radish]

File "/opt/anaconda3/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 233, in _open_connection
self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Unknown MySQL server host '127.0.0.1:8080' (0)

They’re getting this

[thick moat]

I'm using a bootcamp on Mac and a php project just works fine in windows while in mine in bootcamp it outputs a 500 HTML error

If you do figure out what was the problem plz do tell me

[proven arrow]

Did you give the correct port?

[distant radish]

Yep

[proven arrow]

Can you show how you did it?

[flat birch]

hey, is anyone willing to take a look at a few pastes (pgsql 20 rows, python 20 rows)? I'm trying to work with SQLAlchemy for the first time.

[distant radish]

They said they added a port variable to the connect statement

[thick moat]

@proven arrow AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server

I found this error logged in Apache, do you know how I can solve this?

[sweet fiber]

salam abdAllah

[proven arrow]

Have you tried searching it up. I see there are some posts on SO with same issue. It might be to do with your config files, which I’m unable to verify because I don’t have xampp installed.

[median swift]

Guys I need some help. I have run the command pip install asyncpg in my terminal on my raspberry pi. Though when I import it in python, it doesn't work and has a ModuleNotFoundError.

[prisma girder]

pip command comes from your venv or something?

Try pip3 if you are using Python 3

[median swift]

I shall try that thankyou 🙂

Nope still doesn't work 😦

[prisma girder]

Are you using virtual env?

[median swift]

What do you mean by that?

[prisma girder]

Have you created venv (or similar) directory using python3 -m venv venv/?

[median swift]

Don't think so

[prisma girder]

Okay, can you execute python3 -m pip?

[median swift]

Yes that runs fine

[prisma girder]

So try to run python3 -m pip install asyncpg

Then execute python3 and try to import what you want to import

[median swift]

It says requirement already satisfied

Though in my code it still says the same

[prisma girder]

Are you trying to call import asyncpg?

[median swift]

Yeah at the top of my code I have import asyncpg

[prisma girder]

But how about REPL? Have you tried to run this import in REPL?

[median swift]

I don't use repl and heard bad things about it so I prefer not to do this 🙂

[prisma girder]

I mean that execute just python3 - you should get interactive Python console

Then call import asyncpg and give full error

[median swift]

Like this?

It gives no errors

[prisma girder]

How do you execute your script?

[median swift]

Though in the IDE on rpi it throws an error with my code

[prisma girder]

Yep

[prisma girder]

It means that library is already installed

[prisma girder]

Which IDE?

[median swift]

I am using Thonny, I have only had my rpi for a couple of days and haven't got round to using different IDEs

[prisma girder]

Never used it, give me a second

Select Tools → Open system shell

Then pip install asyncpg

[median swift]

Still running the same error

[prisma girder]

It gives me nothing, can you share whole output?

[median swift]

Right this is weird, tried running my code in the terminal and it now works. Running it in Thonny and it doesn't. What I am going to do is convert my sql to asyncpg like I was doing then run it and see if it works

Thanks for your help. I will let you know how it goes 🙂

I shall just not run it in Thonny

[prisma girder]

You can try VS Code 😉

[prisma girder]

Your welcome!

[median swift]

I use VSCode on Windows, it is good! I just haven't got round to setting it all up on the pi yet

[prisma girder]

You can use VS Code using SSH to edit code on remote machine

As far as I remember

[median swift]

That would be pretty helpful tbf. I am currently using remote to use the rpi on my windows device which is helpful

[winged schooner]

Hello anyone know who to write to mailbird DB file?

inserting mails there

via py

[torn sphinx]

does anyone know why it says I dont put the right amount of when I'm sure I do put enough

Incorrect number of bindings supplied. The current statement uses 12, and there are 5 supplied.

I get this one

when I do this

c.executemany(
                f"""UPDATE {table} SET
                phone = :phone,
                first_name = :fn,
                last_name = :ln,
                gender = :gender,
                state = :state,
                city = :city,
                relationship = :rs,
                work = :work,
                date_created = :dc,
                email = :email,
                birthdate = :bd WHERE id = :id""",
                {"phone": phone if phone else None,
                 "fn": first_name if first_name else None,
                 "ln": last_name if last_name else None,
                 "gender": gender if gender else None,
                 "state": state if state else None,
                 "city": city if city else None,
                 "rs": relationship if relationship else None,
                 "work": work if work else None,
                 "dc": creation_date if creation_date else None,
                 "email": email if email else None,
                 "bd": birthdate if birthdate else None,
                 "id": id})```

so I'm sure I input 12 parameters yet it gives the error

[prisma girder]

Is it sqlite3? Are you sure that you should pass dict? Not list of dicts?

[torn sphinx]

yes this is sqlite3

no you can use a dict if you specify the names via :name

[prisma girder]

I mean that executemany accepts list like in those examples: https://docs.python.org/3/library/sqlite3.html

[torn sphinx]

ah I see

[prisma girder]

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany

seq_of_parameters

Wrap it with [ ]

[torn sphinx]

the only reason I used executemany is so it would be faster

since I add a lot of rows in it needs to be fast

but I guess it isnt faster

than execute is it

[prisma girder]

I never used sqlite3 so I cannot say nothing about it

[torn sphinx]

ok well thanks for the help

[prisma girder]

Your welcome

[median swift]

Hi @prisma girder, thought I'd let you know that is is all working and the asyncpg statments are all working. Must be an error with Thonny. Thanks again for your help 🙂

[prisma girder]

Nice to hear it!

[formal shell]

Any way to do something like RETURNING in postgresql but in mysql?

[edgy lotus]

File "c:\Users\User\Desktop\Campfire\venv\lib\site-packages\pymongo\topology.py", line 217, in _select_servers_loop
    (self._error_message(selector), timeout, self.description))
pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 606c8ab93c3d290aaa6658c5, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.gooig.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed',)>, <ServerDescription ('cluster0-shard-00-01.gooig.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed',)>, <ServerDescription ('cluster0-shard-00-02.gooig.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed',)>]>
``` what does this mean?

[true lily]

How can I improve this code

async def open_account(ctx):
  await guild_reg(ctx)
  users_data = await data_read()
  users = users_data.find({"_id": ctx.guild.id})
  async for result in users:
    try:
      if result[str(ctx.author.id)] != None:
        print("Already register.")
        break
        return False
    except KeyError: # User is not registed.
      mbr = {
        "WALLET": 0,
        "BANK": 0
      }
      await users_data.update_one({"_id": ctx.guild.id}, {"$set":{str(ctx.author.id): mbr}})
      print("New Bank Acc Opened.")
      break
  return True

It still blocking the event

[rain plank]

you mean it's blocking the event loop?

[true lily]

Yes

[rain plank]

how do you know?

[true lily]

My bot took too long to respond

[ornate fulcrum]

Hey, this is a piece of cog code for searching specific line from mongodb.
https://paste.pythondiscord.com/mevikakada.py
The result is as following;

Here's the whole database:
{'_id': ObjectId('606c8efbc7796badd86c5437'), 'writeup': 'insert everything here'}``` How to make the result shown like there's no id's or anything else but only the writeup?

[pliant robin]

SQL.execute(f'update Guild set pocket = pocket + ? where user="{user.id}" and server="{guild}"',(change))
        db.commit()

^^^ doesn't work

[burnt turret]

Why are you passing in some variables with the f-string?

As opposed to using the parametrized query (using the ? for all variables)

[burnt turret]

The issue is that the second argument is expected to be a tuple, and simply putting () around a single element doesn't make it a tuple. To make a single element tuple, you'd do (change,)

[burnt turret]

I'd probably change that entire query to work like this though

[burnt turret]

You want your database to only return the "writeup" key and its value?

[pliant robin]

Oh thnx thnx

let me try this rq

[ornate fulcrum]

Yes that's what i'm after

In embed without the "writeup"

[burnt turret]

.find can take a second argument called a "projection", which is basically just a dictionary you use to tell the database which keys you want

https://docs.mongodb.com/manual/reference/method/db.collection.find/

[pliant robin]

thnx a ton buddy, i neva acc used this shi before loll

[burnt turret]

So in your case something like collection.find(my query, {"writeup":1}) will just return the writeup key and value and not the rest of the document (like the id)

[ornate fulcrum]

Added this line ```py
findpost = collection.find(myquery, {"writeup":1})

[burnt turret]

Can you show your code for the embed as well?

[ornate fulcrum]

async def findline(self, ctx, *, findthispls):
                myquery = {"writeup": { "$regex": findthispls} }
                findpost = collection.find(myquery, {"writeup":1})
                for result in findpost:
                    print("Requested this line from database:", findthispls)
                    print("Found requested line from database:", result["writeup"])
                    embed = discord.Embed(description=f"```fix\nFound this line from database:\n[{result}]\n```", color=discord.Color.blue())
                    await ctx.send(embed=embed)```

[burnt turret]

You need to access the value

[ornate fulcrum]

I believe the \n[{result is the issue

[burnt turret]

It's a dictionary that the database returns

result["writeup"] will give the value of the "writeup" key

[ornate fulcrum]

"fix\nFound this line from database:\n[{result["writeup"]}]\n"

[ornate fulcrum]

Error https://paste.pythondiscord.com/esurisesos.sql

I m ean it works as intented in print

[burnt turret]

You opened the f-string with double quotes

So use single quotes for the writeup part

[ornate fulcrum]

Ok that was the issue. Thank you very much! Didn't know you can separate stuff like this. Appreciate

[burnt turret]

Note that the because of the projection we added the database is only giving you that key-value pair

I said that because I thought that was what you wanted, but it looks like you just wanted help with the "accessing values from dictionary" part, so if you need all the data from the db just remove the projection

[fallen hatch]

im new to python, and ive tried to do some research on how to setup a mysql database for my project. I seriously cant find anything! Will someone please help me?😙

[ornate fulcrum]

@fallen hatch Try w3schools, it helped me and lead me into pymongo. TechWithTim youtube has some instruction videos

[burnt turret]

You shouldn't be using pymongo with discord.py btw https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace

[fallen hatch]

im gonna try w3schools! But tech with tim's tutorial didnt work for me idk why

oh its not for discord.py

[burnt turret]

Was referring to the other guy

[ornate fulcrum]

Yes i have the motor thing on to-do list

[burnt turret]

It's quite simple to switch, as explained in the gist

[true lily]

But its not worked for me

[burnt turret]

What's up?

[true lily]

.

[burnt turret]

How do you know it is the database calls which is blocking?

Motor is asynchronous

[true lily]

My bot is taking too long to respond

Same issue before i use motor

[burnt turret]

That does not answer my question, how do you know it is the database queries that's blocking and not some other part of your code

[burnt turret]

This makes me believe that you've got other blocking code somewhere

[ornate fulcrum]

If techwithtim didn't work there's usually conclusion in comments section

[true lily]

#guild insert
async def guild_reg(ctx):
  users_data = await data_read()
  
  try:
    post = {"_id":ctx.guild.id}
    await users_data.insert_one(post)
  except:
    pass

Here is another code

[burnt turret]

Again, not answering what I asked

[true lily]

Its blocking all of my code

._.

[burnt turret]

That does not answer my question, how do you know it is the database queries that's blocking and not some other part of your code

Emphasis on "how do you know it is the database that is blocking and not something else in your code"

You might be making HTTP requests with the requests or urllib3 modules and then thinking it is the database that's the culprit (for example)

[elder robin]

tables=['user', 'recipe', 'manual','meal','mealplan','ingredient','measurement','groceryList','kitchensupply']
create = {}
create['user']="CREATE TABLE user (UID INT NOT NULL auto_increment, firstname varchar(255), lastname varchar(255), email varchar(255), password varchar(255), primary key(UID))"
create['Recipe']="CREATE TABLE Recipe (RecipeID VARCHAR(255), DateCreated Date, specification VARCHAR(255), totalcal float,  primary key(RecipeID))"
create['Manual']="CREATE TABLE Manual (ManualID VARCHAR(255), steps int, instructions VARCHAR(255), instructionID int, primary key(instructionID))"
create['Meal']="CREATE TABLE Meal (MealID varchar(255), Mimage varchar(255),  primary key(MealID))"
create['MealPlan']="CREATE TABLE MealPlan (MPID varchar(255), UID varchar(255),  primary key(MPID)), foreign key(UID) references user(UID))"
create['Ingredient']="CREATE TABLE Ingredient (IngredientID varchar(255), IName varchar(255), calories float, primary key(IngredientID))"
create['Measurement']="CREATE TABLE Measurement (MID varchar(255), Quantity varchar(255), measurements float,  primary key(MID))"
create['GroceryList']="CREATE TABLE Meal (GLID varchar(255), IngredientID varchar(255),  primary key(GLID))"
create['KitchenSupply']="CREATE TABLE Meal (IngredientID varchar(255), MID varchar(255), KSID varchar(255), primary key(KSID))"

anyone could tell me why recipe, mealplan, grocerylist and kitchen supply table isn't creating

[ornate fulcrum]

Hey i try to get the motor work with pymongo but i receive an error py Traceback (most recent call last): File "C:\Users\polvi\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 606, in _load_from_module_spec spec.loader.exec_module(lib) File "<frozen importlib._bootstrap_external>", line 790, in exec_module File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed File "C:\BOT3\cogs\mongocmd.py", line 11, in <module> cluster = MongoClient(mClient) File "C:\Users\polvi\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\mongo_client.py", line 632, in __init__ for entity in host: File "C:\Users\polvi\AppData\Local\Programs\Python\Python39\lib\site-packages\motor\core.py", line 237, in __getitem__ return db_class(self, name) File "C:\Users\polvi\AppData\Local\Programs\Python\Python39\lib\site-packages\motor\core.py", line 516, in __init__ delegate = kwargs.get('_delegate') or Database( File "C:\Users\polvi\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\database.py", line 107, in __init__ raise TypeError("name must be an instance " TypeError: name must be an instance of str This is the cog https://paste.pythondiscord.com/ruzanojabe.py Wonder what i do wrong, i followed this site https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace

[proven arrow]

You have an extra closing bracket mealplan, and the foreign key data type doesn’t match the parent columns data type. Grocery and kitchen don’t get created because you already have a table called Meal

Also user is a keyword so don’t use that as a table name

[jovial bay]

Hi, i have some problems importing the pymongo module, it says that the module doesn't exist and gives me an error.
I have installed the module pip install pymongo and it was successfully installed. I checked using the pip list command

ping me if you know why please

[distant radish]

hey, we are still having the same issue with mysql and mac

this is the connect statement:

import mysql.connector
overseer = mysql.connector.connect(
        host="localhost",
        user="root",
        password="",
        database="overseer", 
        port=8080)
print("Done!")```

[proven arrow]

Did you try 3306 port

[distant radish]

it hangs and errors out here

we used a fixed port

we can access phpmyadmin with xampp

but for some reason python can't access it still

[proven arrow]

Yeah that’s strange

I can only suggest trying to reinstall xampp and try with its default settings and port. Or maybe try mamp, which is what I used for mac in the past and worked better than xampp.

[distant radish]

any other suggestions than switching to mamp?

[proven arrow]

What’s the output if you run the following in phpmyadmin:
SHOW GLOBAL VARIABLES LIKE 'PORT';

[distant radish]

it gives 3306

[proven arrow]

And so your trying with port 8080?

[distant radish]

might be why lol

we have the localhost on 8080

[proven arrow]

But that’s different to what the database runs on

That’s for phpmyadmin web app

[distant radish]

he got this:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/network.py", line 607, in open_connection
    self.sock.connect(sockaddr)
ConnectionRefusedError: [Errno 61] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/DKMB/.bitnami/stackman/machines/xampp/volumes/root/htdocs/capstone-project-mohcamanagement/py/test.py", line 2, in <module>
    overseer = mysql.connector.connect(
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/init.py", line 278, in connect
    return MySQLConnection(*args, kwargs)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 108, in init
    self.connect(kwargs)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1003, in connect
    self._open_connection()
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 351, in _open_connection
    self._socket.open_connection()
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/network.py", line 609, in open_connection
    raise errors.InterfaceError(
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (61 Connection refused)```

[proven arrow]

Try 127.0.0.1 as the hostname

Also how did you enter the port number? As a int or string?

[distant radish]

this is the test script

import mysql.connector
overseer = mysql.connector.connect(
        host="127.0.0.1",
        user="root",
        password="",
        database="overseer", 
        port=3306)
print("Done!")```

wait

updated it to the new one

[proven arrow]

Yeah try that. If not then only other thing I can think of is, try make a new user:

create user “fishguy” identified by “fish”;
grant all on overseer.* to fishguy;

You might have to replace the quotes I put for the username because iOS quotes probably will error if copied directly

[ornate fulcrum]

Hey. Here's the piece of cog file using mongodb, the !showdatabase makes bot post whole database one by one. And i want it all to be in one message instead of many, any ideas how should it be done? py @commands.command() @commands.has_permissions(manage_messages=True) async def showdatabase(self, ctx,): showall = collection.find({}).sort("writeup") async for result in showall: print("Requested everything from database:", result["writeup"]) embed = discord.Embed(description=f"Here's the whole database:\n{result['writeup']}", color=discord.Color.blue()) await ctx.send(embed=embed)

[wooden stag]

i need some help on my bot i'm trying to use embed but it won't work

[white lava]

just made this website using psql where you input your info and it continually checks the database for open vaccine appointments the sends you a text

if anyone wants to check it out, its vaccinefinder9000.com

[tawdry grotto]

    collection.insert_one({post})
TypeError: unhashable type: 'dict'``` this error

db = cluster["Test"]
collection = db["Test"]
post = {"id:": 0, "name:" : "time"}
collection.insert_one({post})```

[proven arrow]

Try: collection.insert_one(post)

[tawdry grotto]

oke

[shut agate]

So i have this sqlalchemy model for a table. I need to trigger a function whenever a row is updated. How should i proceed?

[tawdry grotto]

IndexError: pop from an empty deque``` when this error

[proven arrow]

It should have some sort of events or observers that allow you to do this

[shut agate]

Does property getter setter work?

[proven arrow]

https://docs.sqlalchemy.org/en/14/core/event.html

[tawdry grotto]

  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationFailure: bad auth : Authentication failed., full error: {'ok': 0, 'errmsg': 'bad auth : Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}``` ```py
@client.command()
async def test(ctx):
    cluster = MongoClient("mongodb+srv://AquaXcherry:<password>#@cluster0.ljkvy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
    db = cluster['Bazaa']
    collection = db['new']
    post = {"command": 1}
    collection.insert_one(post)``` error :c

[river ice]

i use xampp .. have had this issue only when the server is not started from xampp prior to trying to connect to the database.

error is mysql.connector.errors.InterfaceError: 2003:

The error (2003) Can't connect to MySQL server on ' server ' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.

so i am supposing thta the port we are using is not the port that phpmyadmin is using.

even though we can check phpmyadmin to see the databases, it's for a different port. (as far as i know it)

i suggest downloading xampp , starting the server from its command panel, recreating the database and connecting to it as a test.

[river ice]

seeing that the authentication is failing. it says "ok" which i see means that the discord server got the request ... and returned the information saying that your authentication doesn't match the authentication required to make the query that is requested

[tawdry grotto]

oke

mhm

[river ice]

the code syntax i see is broken

[tawdry grotto]

the code is bad?

[river ice]

cluster is not getting defined ... mongoClient is outside of the function, and then after the "other stuff" is done

i see that it is

inform me 😄

[tawdry grotto]

okey

[river ice]

we can also print which parameters are being sent to the discord server. that way we know exactly what the server is refusing

doing it before we make the request

[tawdry grotto]

print()?

[river ice]

yes

[tawdry grotto]

a oke wait

local variable 'cluster' referenced before assignment

[river ice]

i don't have experience with mongodb ... wondering where it connects to discord

[tawdry grotto]

mhm okey

[river ice]

well that's not part of the problem, as far as i know. i am saying that i don't have the information on what this code is supposed to be doing.

[tawdry grotto]

the command is to connect the bot to the database and save anything

[river ice]

so have you replaced <password> with your password when this is run?

and cluster is not defined .. it is cluster =
then cluster is called after like it's a dictionary , with a value stored in ``` cluster["Bazaa"]

but we havent put that value in the dictionary

suggest posting all of the code (without passwords/other security identifying information) .

[river ice]

was this the issue? @tawdry grotto

[tawdry grotto]

a no

oh no

[river ice]

i see it's difficult to assist

[tawdry grotto]

wait

[river ice]

ping me when theres more details , moving to other channels

[tawdry grotto]

oke

[tawdry grotto]

all code or 1 command?

[river ice]

prefer all code so we can see all the functions/variables and libaries included/used

[tawdry grotto]

oke

!paste

[delicate field]

Pasting large amounts of code

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

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

[tawdry grotto]

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

[river ice]

line 171 there is sytax error. last line @tawdry grotto

[tawdry grotto]

171 line?

[river ice]

also when usign the print function, we pass what we want to print , inside the function. like this ```print("using the print function ")

[tawdry grotto]

a oke

[river ice]

client.run("bot token)

[minor badger]

Hi

[tawdry grotto]

yes i put token

[minor badger]

Can you teach me

Coding

[tawdry grotto]

youtube

[minor badger]

please.

what

[tawdry grotto]

Please do not write here, only on
#python-discussion

[minor badger]

@minor badger AquaCherry please

i know

bye

[tawdry grotto]

bye

[tawdry grotto]

I'm pasting a bot token there just mask it now

[river ice]

so you have passed the token as client.run("12312dkasdkalsdk21l3j21k3ldasd") or something like this

or stored it in a variable and ran it from there , ok

whats the error?

[tawdry grotto]

oke'

[river ice]

this is the error?

[tawdry grotto]

yes yes

i have 3 bots now i'm doing 4: D

error is

in thispy @client.command() async def test(ctx): cluster = MongoClient("mongodb+srv://AquaXcherry:<12345>@cluster0.ljkvy.mongodb.net/Bazaa?retryWrites=true&w=majority") db = cluster['Bazaa'] collection = db['new'] post = {"command": 1} collection.insert_one(post)

or maybe change the database from moongle?

[river ice]

what is the error?

have you been using python ? I saw you may not have had experience using the print function

[tawdry grotto]

wait

  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\dawid\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationFailure: bad auth : Authentication failed., full error: {'ok': 0, 'errmsg': 'bad auth : Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}```

in command

[river ice]

where is our authentication declared?

[tawdry grotto]

@client.command()
async def test(ctx):
    cluster = MongoClient("mongodb+srv://AquaXcherry:<haslo>@cluster0.ljkvy.mongodb.net/Bazaa?retryWrites=true&w=majority")
    db = cluster['Bazaa']
    collection = db['new']
    post = {"command": 1}
    collection.insert_one(post)```

authentication?

[river ice]

i see it is inside this MongoClient("mongodb+srv://AquaXcherry:<haslo>@cluster0.ljkvy.mongodb.net/Bazaa?retryWrites=true&w=majority")

[tawdry grotto]

yes

[river ice]

where is the documentation

https://pymongo.readthedocs.io/en/3.6.0/api/pymongo/mongo_client.html

[tawdry grotto]

oke

[river ice]

we are passing all of these connection parameters inside a url

here is an example ``` try:
# Python 3.x
from urllib.parse import quote_plus
except ImportError:
# Python 2.x
from urllib import quote_plus

uri = "mongodb://%s:%s@%s" % (
quote_plus(user), quote_plus(password), host)
client = MongoClient(uri)```

ours would look like ```
mongoClient("mongodb://%s:%s@%s" % (
"AquaXcherry","haslo","cluster0.1jkvy.mongodb.net/Bazaa?retryWrites=true&w=majority")

[tawdry grotto]

mhm

aha okey

and change?

[river ice]

i don't know about that hostname

if your server were hosted on "your computer" that would be "127.0.0.1:<port>"

i don't know whether your hostname is good

[tawdry grotto]

my server hosted 0.0.0.0

[river ice]

then i wonder why is this command calling the cluster thing

[tawdry grotto]

mhm

[river ice]

also wondering why your code is doing mongodb+srv:// , and this example recommends mongodb://

in this case , hostname i see is supposed to be 0.0.0.0

[tawdry grotto]

yes

[river ice]

which is ```
mongoClient("mongodb://%s:%s@%s" % (
"AquaXcherry","haslo","0.0.0.0:<port>")

[tawdry grotto]

oke change it?

[river ice]

i suggest trying it

[tawdry grotto]

oke

[river ice]

now i see we are trying to connect to a server, cluster0.1jkvy.mongodb.net/Bazaa?retryWrites=true&w=majority and we don't have parameters /details for connecting to it.

where did this server come from?

[tawdry grotto]

a

AWS / Frankfurt (eu-central-1)

[river ice]

i mean why are we connecting to this server. Where did we get the url?

[tawdry grotto]

because i would like to find the data there

[river ice]

i see .. but i see that the command we are calling is a command that connects to a database . are we passing parameters that connect us to a database that stores the data?

or are we connecting to our database on our system

[tawdry grotto]

yes

[river ice]

wondering whether it is supposed to be our database, or their database

[tawdry grotto]

database in mongod

our guess

[river ice]

so we need to make sure our details are correct

where-ever we got our credentials for connecting to their database, we need to make sure the credentials are right

and if its our database then we need to make sure the hostname is correct and the other details are correct

when this is for connecting to our database, and our database is on 0.0.0.0 , then the hostname that i see you were using beofre is incorrect

[tawdry grotto]

oke

So should I bet on my IP?

[tranquil totem]

is there a server for pymongo

also i finally understood databases!!

[tawdry grotto]

yes

[tranquil totem]

can i get an invite in dms if anyone knows abt it?

also, how to make a leaderboard command?
yes i know how to extract the values from the result but how do i extract the ten greatest values and put them in descending order?

[tawdry grotto]

or an invitation?

[burnt turret]

https://www.w3schools.com/python/python_mongodb_sort.asp
and,
https://www.w3schools.com/python/python_mongodb_limit.asp

[tranquil totem]

oh thanks

[ornate fulcrum]

Hey, here's the piece of code inside a cog file(mongodb in use) that makes bot post everything under the key "writeup" when command !showdatabase is executed. My problem is the bot posts everything it finds with one post at the time, and i'd like to have it making a list and post all keys in one post instead. But i don't know how to. Help me please. https://paste.pythondiscord.com/ovayotofep.less

The database will not be that huge it need paging or anything. Just everything in one post.

[lone panther]

i don't know anything about databases, but how can i save an array of objects in a file?

and then read it

[prisma girder]

json.dumps and json.loads?

[lone panther]

emh

is there a guide or something like this?

[prisma girder]

Here you have some examples https://docs.python.org/3/library/json.html

import json
with open("file.txt") as file:
  data = file.read()
data = json.loads(data)
print(data)

@lone panther you can also use pickle module: https://docs.python.org/3/library/pickle.html

[lone panther]

i tried to use pickle but it doesn't work for list of object

[prisma girder]

Can you show your code?

[tacit fjord]

from utils.mongo import Document```

this isnt working.. i did "pip install mongo" 
and i have imported pymongo.

I dont know if this follows within this...

[lone panther]

i deleted it😅

[prisma girder]

It shouldn't be from mongo import Document?

[tacit fjord]

i tried that too

same error. the module doesnt exist

oh... nvm i think i got it. i didnt install the right version

i updated py and now it doesnt shoot an error

[lone panther]

class ids:
    def __init__(idss, textual, vocal, user):
        idss.user = user
        idss.textual = textual
        idss.vocal = vocal


list = []
#list.append(ids(textual,vocal,user))
list.append(ids(828970923229970503,828970890674044978,464066500047929365))
list.append(ids(828997530976256121,828997548206981141,634142574721892352))
list.append(ids(829090145449672764,829090169101221948,763800282370146324))
list.append(ids(829355771908849735,829355771065925682,478980125648748544))

i want to save all the "list.append" in a file

[torn sphinx]

how do i switch json to db

[prisma girder]

It works with pickle

[lone panther]

oh

ok, i will try

IT WORKS!

but how can i add an object to in the list?

[prisma girder]

Yay

[prisma girder]

What do you want to do?

[lone panther]

i want to append a new object

if i use the dump function only the 3 ids keep in the file

[prisma girder]

Show me your code

[lone panther]

class ids:
    def __init__(idss, textual, vocal, user):
        idss.user = user
        idss.textual = textual
        idss.vocal = vocal


list = []
#list.append(ids(textual,vocal,user))
list.append(ids(828970923229970503,828970890674044978,464066500047929365))
list.append(ids(828997530976256121,828997548206981141,634142574721892352))
list.append(ids(829090145449672764,829090169101221948,763800282370146324))
list.append(ids(829355771908849735,829355771065925682,478980125648748544))


with open('club.data', 'wb') as filehandle:
    pickle.dump(list, filehandle)

with open('club.data', 'rb') as filehandle2:
    mylist = pickle.load(filehandle2)
    for x in mylist:
        print (x.user)

i want to append a new object to "filehandle2"

the read file

oh wait i think i can do one thing for this

[prisma girder]

First of all you shouldn't use list as variable name

[lone panther]

okay

[prisma girder]

After load you can update mylist like mylist.append(ids(0, 0, 0)) and then open('club.data', 'wb') and write updated data

[lone panther]

yeah i've already done it,thanks anyway

[prisma girder]

Well done

[lone panther]

ty so much

[torn sphinx]

how do i use sqlite3

[tranquil totem]

how do I combine .sort and .limit into one statement?

[burnt turret]

What have you tried so far?

[torn sphinx]

does anyone have any idea on how to create a attendance table in the backend and also how to create a attendance gui with c#??(i want it for my payroll management system. I have done every thing else i am just struck at attendance part)

[tranquil totem]

doing them on separate lines but thats just 2 diff ways of sorting the data, technically i havent thought anything since idk how to end the statement with both .sort and .limit

[burnt turret]

You can put them both on a single line I believe

...find().sort(...).limit(...)

I don't remember the exact parameters but I think I used to do it like this

[tranquil totem]

ok lemme check

[jagged cove]

just a quick question, when making a discord bot that's gonna be released publically to everyone and is going to have to carry large amounts of data and return them with as little delay as possible

should i use MySQL or literally anything else?

updated question: should i use mySQL or a json file?

[brave bridge]

That's not all the choices you have. A single JSON is certainly a bad idea for a large database -- to read or change anything, even one number, you have to read the entire thing into memory.

There are several alternatives. If you just want a key-value store -- a mapping from a key (like an ID) to a piece of data -- you can use a folder with files if you need only that. That's what browsers use for caching, for example.

Then there are proper database management systems. Unless your bot is operating on a very large scale, you'll probably be fine with SQLite. Python already comes with an adapter to SQLite (the sqlite3 module), but if you want to use it with async, you should use an asynchronous version -- aiosqlite https://github.com/omnilib/aiosqlite

[jagged cove]

why thank you for being a voice of reason

it was an ironic question because my co-developer all of the sudden insists we switch over our entire database system from MySQL to mongoDB

not because there's any obvious advantage for our situation

but because he "prefers the syntax" of all reasons

[brave bridge]

well, MongoDB is not the same as a JSON file 😄

[jagged cove]

hha yeah i know it was an exaggeration

but the point remains that mongoDB is no match for MySQL

especially considering we already have our entire system set up in MySQl

[brave bridge]

How much data do you have?

[jagged cove]

every database function is written in SQL

our entire project has been dependant on sql up to this point

so why would we ever migrate to mongoDB for something as stupid as prefering the syntax

[brave bridge]

ask them

[jagged cove]

i did

his grande reason was "because sql is stupid"

"look at this"

then he sends me a screenshot of the database setup he told me to use

also he said that "mongoDB is the best NoSQL system out there"

even though there's no reason to stop using sql in the first place

and it just gets progressively dumber from that point onwards

[brave bridge]

Maybe they mean something different by "syntax"? People don't always mean "syntax" when they say "syntax".

SQL is certainly a bit confusing, but it's not a reason to stop using relational databases. You might want to try EdgeDB if you want a relational database with "better syntax".

[jagged cove]

well he said he preferred the syntax mainly because you can have objects inside of each other

[brave bridge]

This is not really a platform to rant about other people, so you should just discuss it with the other developer.

[jagged cove]

for example multiple member objects inside a guild object

also i would but he blocked me hha

[brave bridge]

Well, then he's not your co-developer anymore, so no issues

[jagged cove]

but yeah it was never my intention to rant like this, just wanted to hear from someone else that i'm not crazy for saying that mysql > mongodb

[brave bridge]

I did not say that

[jagged cove]

also he still is, he just does this when he's mad but in an hour he'll have me unblocked, plus i kinda rely on him for hosting etc

i mean am i crazy?

do you think it makes more sense to use mongoDB instead?

[brave bridge]

I'd use a relational database, because a discord bot probably holds lots of relational data 🙂

[jagged cove]

personally i don't think we use relational data

brb btw i just received 2 alerts

[brave bridge]

What data do you store?

[jagged cove]

1 that the bot has restarted and an instant error mentioning "sql error" so

i should check on that

i'll be right back

alright i'm back, it was nothing after all

we just have a few tables to store guild settings, as well as a table for every command so we can easily enable/disable them

also for every guild we're in, a database table is made that holds member logs with their server scores and unlocked cosmetics etc

i know that's a pretty bad way of handling it and i do plan on changing that but for now we're stuck with it

[burnt turret]

A table for every command sounds excessive

Table per guild too doesn't sound like a good idea

[jagged cove]

no no

not a table for every command

a single table

holding every command

[burnt turret]

Right I misunderstood

[jagged cove]

commands (table
-- command1 - enabled - help info etc
-- command2 - disabled - help info etc

i may have said it wrong aswell i don't really know

also idk if you remember

but a while ago we had this same conversation about a table per guild

[burnt turret]

That's why this feels so deja vu lmao

[tranquil totem]

hey anand, i did this

    @commands.command()
    async def lb(self, ctx):
        top_ten = collection.find().sort("bankcookies" + "walletcookies", reverse=True).limit(10)
        rich_users = top_ten["bankcookies" + "walletcookies"]
        await ctx.send(rich_users)
```is it fine?

[jagged cove]

you mentioned joined queries to which i replied "we store all user info in the same table" hha

but yeah i'm likely going to be redoing big parts of the bot soon and i plan on changing it

[burnt turret]

By bankcookies + walletcookies, are you trying to sort them both in descending order?

Right now you're just adding those strings together

[burnt turret]

Ah that's good haha

[tranquil totem]

no bank cookies is the no. of cookies they have in their bank and wallet is the no. of cookies they have in their wallet. on adding both of them, whoever has the most number of cookies, their name should be displayed first

[burnt turret]

The table per guild system sounds like a pain to manage

[jagged cove]

i mean we don't really know yet

so far the tables are also pretty cemented so we rarely need to change anything

[burnt turret]

Riight, I'm not really sure how that's done in mongodb sorry

[jagged cove]

plus even when we do we're only in 5 guilds atm

[tranquil totem]

hmm okay

[jagged cove]

@tranquil totem this might be a stretch but what if you made a third field total_cookies

which is always just wallet cookies + bank cookies

and sort by that?

[tranquil totem]

hmmmm

i can do that, yes

[burnt turret]

Would've been a simple SQL query 😔

[tranquil totem]

but i think i might have to change it for all of the commands...?

[jagged cove]

wdym

[burnt turret]

Me Everytime I have to write mongo queries

[jagged cove]

personally i've only ever worked with SQL

i don't get why some people hate on it so much

[tranquil totem]

do i have to add the total field in all of the posts?

[jagged cove]

like it can be a pain at times but that's just part of coding isn't it

[tranquil totem]

wait lemme check, im confused

[jagged cove]

no no just for the leaderboards or so

[tranquil totem]

just for the leaderboard cmd right?

[jagged cove]

yes

[tranquil totem]

even then, ill still have to type "bankcookies" + "wallletcookies"

making total_cookies the variable here

assigning the value to it

[jagged cove]

maybe yeah

or what you can do

in on_message

which is where i prefer to do things that need to happen regularly

[brave bridge]

"bankcookies" + "walletcookies" is the same as the string "bankcookieswalletcookies"

there's no magic to + there

[jagged cove]

add a check if walletcookies + bankcookies = totalcookies

and if it's not then totalcookies = walletcookies + bankcookies

[tranquil totem]

woah

[jagged cove]

?

[tranquil totem]

wait arent those two the same thing

[jagged cove]

!e ```py
x = "walletcookies"
y = "bankcookies"
print(x+y)

[tranquil totem]

just the other way around

[delicate field]

You are not allowed to use that command here. Please use the #bot-commands channel instead.

[jagged cove]

wdym

oh

[tranquil totem]

same thing isnt it?

[jagged cove]

mathematically yes

pythonically (???) no

[tranquil totem]

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: sort() got an unexpected keyword argument 'reverse

[tranquil totem]

oh right

[jagged cove]

yeah

i meant add an if statement

so

[tranquil totem]

top_ten = collection.find().sort("bankcookies" + "walletcookies", reverse=True).limit(10)

isnt reverse right

[jagged cove]

reverse is right

[tranquil totem]

then why does it show an error :/

Ignoring exception in command lb:
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/runner/MainBot/cogs/currency.py", line 299, in lb
    top_ten = collection.find().sort("bankcookies" + "walletcookies", reverse=True).limit(10)
TypeError: sort() got an unexpected keyword argument 'reverse'

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 902, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, 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: sort() got an unexpected keyword argument 'reverse

@jagged cove

uh

[jagged cove]

don't know

i meant right as in you need to reverse the results

[tranquil totem]

oh okay then

[jagged cove]

i don't know if it's the right syntax though

[tranquil totem]

alr

[torn sphinx]

does anyone have any idea on how to create a attendance table in the backend and also how to create a attendance gui with c#??(i want it for my payroll management system. I have done every thing else i am just struck at attendance part)

[torn sphinx]

@bot.event
async def on_guild_join(server):
    for member in server.members:
        if await economy.find_one({"id":member.id}):
            print(f"{member} is already in economy database, source: on_guild_join event")
        else:                                            
            await collection.insert_one({"id":member.id, "economy_start_check":None, "gold":0, "silver":0, "money":0, "sword":"basic", "shield":"basic", "job":None, "inventory":[], "xp":0, "next_level_xp":1000, "level":0})
            print(f"Inserted {member} into economy database, source: on_guild_join event")
    if await guilds.find_one({"id":server.id}):
        print(f"{server} is already in database, source: on_guild_join")
    else:
        await guilds.insert_one({"guildid":server.id, "prefix":"-", "welcome_channel":None})

Why won't this work? there is no errors, the member inserting works but the guild inserting doesn't

Nvm. I got it working

[harsh night]

is there a way to check if a value is highest or second highest and find the values place in sqlite3?

like rank the values

[torn sphinx]

i dont know whether u can find highest or lowest individually or not, but u can use ORDER BY to put data in desc or asec

[torn sphinx]

https://www.w3schools.com/sql/sql_orderby.asp

[brave bridge]

Yeah, you can get e.g. the top two elements and see if your item is one of them

[proven arrow]

You can use the row_number window function for this.

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

[ornate fulcrum]

@commands.command()
@commands.has_permissions(manage_roles=True)
    async def usethischannel(self,ctx, *, chanid):
            for guild in self.bot.guilds:
                x = guild.id
                y = collection.find_one(x)
                print(y)
                if y == x:
                    print("Guild already exist")
            else:
                createguild = {"guildid":x ,"channel":chanid}
                guildss.insert_one(createguild)
                print ("guild id:", x,"guild channel:", chanid)``` This is supposed to check from mongodb if the guild id key exist there already. For some reason the if == y doesn't happen, channel and guild gets saved with new id, and i get this errormessage as result ```py
<Future pending cb=[_chain_future.<locals>._call_check_cancel() at C:******Python\Python39\lib\asyncio\futures.py:384]>``` Wonder what can acause this.

[unkempt prism]

what am i doing wrong here? https://i.imgur.com/6pxYtU8.png

[night charm]

What does "stonks" look like?

[pallid flax]

@unkempt prism try replacing totalstonks in line 3 with sum(amount)

basically totalstonks is not a column in your table but is just an alias given to the sum aggregate

[dreamy vapor]

remove the where, if you want to filter with a grouped columns you have to use having

SELECT stonk, SUM(amount) AS totalstonks
FROM stonks
GROUP BY stonk
HAVING totalstonks < 0;

[unkempt prism]

thanks

[tranquil totem]

wait so im still confused

ive got this for my leaderboard command```py
@commands.command()
async def lb(self, ctx):
top_ten = collection.find(projection={ 'cookies': { '$add': ['bankcookies', 'walletcookies'] } }).sort('cookies', reverse=True).limit(10)
rich_users = top_ten[bankcookies + walletcookies]
await ctx.send(rich_users)

but it doesnt work

whats wrong in it?

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @frosty kiln until 2021-04-08 06:09 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[maiden sphinx]

!unmute 750006475400675370

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction mute for @frosty kiln.

[maiden sphinx]

Please do this in #bot-commands

and a little slower haha

Oh

I forgot there are channel specific onees

[frosty kiln]

sorry , thanks a lot for unmuting

[maiden sphinx]

if you want the full list, you can see it on github

[frosty kiln]

oh ok

[maiden sphinx]

https://github.com/python-discord/sir-lancebot/blob/main/bot/resources/evergreen/py_topics.yaml

[frosty kiln]

thanks

[tranquil totem]

someone help me ;-;

how do i fetch the top 10 highest values of 2 fields added together in pymongo?

ok its ticket time

[tranquil totem]

I need some help
So I'm making a leaderboard command using discord.py and pymongo
I want to fetch 2 fields in a collection and then add them up and then find the top 10 greatest values of them and send them on the channel
This is my code:
ive got this for my leaderboard command

    @commands.command()
    async def lb(self, ctx):
    top_ten = collection.find(projection={ 'cookies': { '$add': ['bankcookies', 'walletcookies'] } }).sort('cookies', pymongo.DESCENDING).limit(10)
        rich_users = top_ten[bankcookies + walletcookies]
        await ctx.send(rich_users)```

[dry patio]

Is it possible to make a postgres COLUMN only accept 1 of X amount of choices.

So an account_type COLUMN can only be "SELLER", "CUSTOMER", "HYBRID".

[tranquil totem]

someone help me

[burnt turret]

Your query looks fine, the part that's wrong is how you're trying to manipulate the data.
Try to understand what you've done with the query -
you make the database $add the values of bankcookies and walletcookies, and return it in a key called "cookies", and this is done for all the documents.
So you're getting documents like

{'cookies': 5}
{'cookies':3}
``` and so on. Now why are you trying to do `bankcookies + walletcookies` _again_?

[burnt turret]

Just loop over top_ten, and to access the number of cookies they have (which will be the sum of walletcookies and bankcookies already, as I explained above) use the "cookies" key

[proven arrow]

There are multiple ways to do this

1. Foreign key
2. Check constraint
3. Enum type

[burnt turret]

https://www.postgresql.org/docs/9.1/datatype-enum.html an enum perhaps?

Ah nice

[proven arrow]

But enums come with certain restrictions so depending on what you want you might want to take a look into each

[tranquil totem]

hmm okay

[burnt turret]

Note that you're only retrieving the cookie count now

You might want to be retrieving the users as well for a leaderboard command

Just add that field to the projection as well

[tranquil totem]

so this part is correct isnt it ```py
.sort('cookies', pymongo.DESCENDING).limit(10)

[tranquil totem]

hmm yes thanks

[burnt turret]

I think so

I'm on mobile right now so I can't verify the syntax, just check the docs

[tranquil totem]

okay

    @commands.command()
    async def lb(self, ctx):
        top_ten = collection.find(projection={ 'cookies': { '$add': ['bankcookies', 'walletcookies'] } }).sort('cookies', pymongo.DESCENDING).limit(10)

        for result in top_ten:
            await ctx.send(top_ten["_id", "cookies"])

this one gives me no outcome

and no error

'_'

[burnt turret]

what are you trying to do with top_ten["_id", "cookies"]

You can't access two keys like that, you'd have to write it twice

[tranquil totem]

no im using it to extract the data

[burnt turret]

I know

[tranquil totem]

its gonna loop it and its gonna extract id and cookies from top_ten

oh

[burnt turret]

You can't extract it like that, putting two keys inside it

[tranquil totem]

oh

[burnt turret]

See top_ten is the list of 10 records that the db gives you

[tranquil totem]

yes

[burnt turret]

To access each record you're using the for loop

[tranquil totem]

but if i print top ten idk what i get

[tranquil totem]

I used a for loop-

[burnt turret]

but under the for loop you're still doing top_ten

[tranquil totem]

top_ten is the list of the top 10 users

[burnt turret]

yes

[tranquil totem]

out of which im extracting the id and cookies

[burnt turret]

Does it make any sense that the list of top 10 users would just have an _id and cookies

Or would each person in the top ten have an id and cookies

[tranquil totem]

uh

[tranquil totem]

they have more

but im just extracting the id and cookies

[burnt turret]

I know that that's not the point

[tranquil totem]

then?

[burnt turret]

The point is you're trying to extract the ID and the cookies from the wrong thing

[tranquil totem]

oh

[burnt turret]

right now you're trying to access an ID and cookies from a list of 10 users, while you should be accessing it from each user

[tranquil totem]

wait nooo i sorted the data according to cookies so all of the id and other data is there in that set of 10 records

[burnt turret]

for record in top_ten:
    record["_id"], record["cookies"]

[tranquil totem]

and im just extracting the other field values from it-

[tranquil totem]

oh

[burnt turret]

Do you understand the mistake now?

top_ten is the entire list of records, and you loop over it and access each record's ID and cookies

[tranquil totem]

isnt (record["_id", ["cookies"]) the same thing?

[burnt turret]

No it isn't

[tranquil totem]

oh

[burnt turret]

That isn't valid syntax

[tranquil totem]

alright then

thanks

so its still not working

no output and no error

here:```py
@commands.command()
async def lb(self, ctx):
top_ten = collection.find(projection={ 'cookies': { '$add': ['bankcookies', 'walletcookies'] } }).sort('cookies', pymongo.DESCENDING).limit(10)

    for result in top_ten:
        await ctx.send(result["_id"], result["cookies"]) 

actually

[burnt turret]

You have an error handler somewhere that's silently eating up errors

[tranquil totem]

it did show an error

[burnt turret]

The ctx.send is wrong

You need to combine those together into a single string before sending it

But that isn't a databases question

[tranquil totem]

hMm

!paste

hmm wait

theres a datatype error

idk where

https://paste.pythondiscord.com/jigerovada.sql

how else can i add the values if i dont use strings

[burnt turret]

The error tells you what's wrong

You're trying to make the database add a string and a number

[tranquil totem]

$add only supports numeric or date types, not string

yes

how else can i add the values if i dont use strings

top_ten = collection.find(projection={ 'cookies': { '$add': ['bankcookies', 'walletcookies'] } }).sort('cookies', pymongo.DESCENDING).limit(10)```

add bankcookies and walletcookies

which are the field names

their values

how else do i add them

theyre not constant

[burnt turret]

I think you'd have to add them like $add: ['$bankcookies', '$walletcookies] https://docs.mongodb.com/manual/reference/operator/aggregation/add/

[tranquil totem]

hmm ok ill try that

"discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InvalidOperation: cannot set options after executing query"

'_'

ill need to change the order

how can i separate the data before filtering it?

[dry patio]

How can I store how long to wait before doing something in postgres? Im making a mod mail bot, and I want to make it so server staff can set it up to wait a certain length of time if no message and then a message was sent to ping staff.

I'm assuming I can't use date type. Cuz that would be a past or present time. I can't store a length of time in it.

[prisma girder]

You can make cronjob

Every minute or every five minutes you can check for messages that are available to send to the staff

[jagged cove]

@dry patio try storing the time in seconds?

[tranquil totem]

Why isn't this in descending order?```py
@commands.command()
async def lb(self, ctx):
top_ten = collection.find(projection={ 'cookies': { '$add': ['$bankcookies', '$walletcookies'] } }).sort('cookies', pymongo.DESCENDING).limit(10)

    top_users = ""
    for result in top_ten:
        top_users = top_users + f'\n<@{result["_id"]}>\nNumber of cookies: {result["cookies"]}'

    await ctx.send(top_users)

https://media.discordapp.net/attachments/611562347512856604/829687023174156359/unknown.png

[tranquil totem]

anyone?

[true lily]

raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IntegrityError: UNIQUE constraint failed: main.USER_ID

Sql

[burnt turret]

UNIQUE constraint failed: main.USER_ID
You're trying to insert a new row with some user ID that already exists in the table

[torn sphinx]

is there any database for python that doesnt require a separate server? I want to ship my program with only one folder and no extra setup

[prisma girder]

SQLite?

[true lily]

Yes uhh, I need to insert new guild id but with the user id too, is that possible

[burnt turret]

SQLite is a file based database

[prisma girder]

You can even run in-memory database

[torn sphinx]

I see, is there any key-value one? I don't need to store that much

[torn sphinx]

has to stay between sessions

[prisma girder]

Just for testing purposes

[burnt turret]

🤔 I guess shelve is key value but it isn't really a database as such

[torn sphinx]

...still need a proper place to store my stuff 😅

[torn sphinx]

i could use json but i hate how python handles json

[prisma girder]

Why not just a simple config-like file?

[burnt turret]

I don't really understand but you could put guild IDs in a different column?

[torn sphinx]

how do I dynamically read it? (reload on change)

[prisma girder]

You need to create some daemon to watch for changes

[torn sphinx]

i see

in node i would just update the variable whenever i made changes/needed to read lol

[true lily]

Can I put two primary keys?

[torn sphinx]

basically let config = fs.readFile('path')

[torn sphinx]

yes

(afaik)

[true lily]

Alr, thx

[true lily]

thx

[tranquil totem]

[torn sphinx]

how do i do this

or should i just use shelve

[prisma girder]

I think that spawning some thread which observes file/directory using something like watchdog

However using SQLite looks a lot of easier

[torn sphinx]

ima just use sqlite

why cant nodejs have sqlite 😭

gotta install python and all lol

[prisma girder]

Rly?

[torn sphinx]

nodejs has sqlite yes, but you have to get build tools or whatever its called

includes visual studio, python 2.7 etc

a bunch of crap that would bloat a system

[brazen charm]

nodejs' sqlite basically just port's Python's Sqlite

which is a lil weird

[tranquil totem]

can i not be ghosted

[prisma girder]

Sorry but I cannot help 😦

I don't have any experience with Mongo libs in Python

How about other queries?

Are not working too?

[tranquil totem]

well i like that, i was at least expecting a reply if they couldnt help, saying that they didnt know abt it

[tranquil totem]

hmm?

no only this query wasnt in descending order which i expected it to be in

[prisma girder]

Which library are you using?

I will see what I can do for you

[tranquil totem]

d.py and pymongo ig

yeah

[prisma girder]

I am not master of MongoDB since it shouldn't be something with aggregation?
https://docs.mongodb.com/manual/reference/operator/aggregation/sort/#examples

[tranquil totem]

oh this makes more sense ig

thanks

[prisma girder]

Hope it helps

[tranquil totem]

still gives the same output :/

idk if theres something wrong with the other part of the code but it doesnt sort it in any order

i think ill have to wait for someone who knows it

[tranquil totem]

pain

bruh what

👁️ 👄 👁️

[torn sphinx]

Guys... I have a problem setting up my flask sqlalchemy database

[tranquil totem]

me too kid

[torn sphinx]

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[torn sphinx]

class User(db.Model): id = db.Column(db.Integer,primary_key=True) name = db.Column(db.String(120),nullable = False) email = db.Column(db.String(70),primary_key=True,nullable = False) post = db.Column(db.String(10000),default ="") date_created = db.Column(db.DateTime, default = datetime.utcnow) def __repr__(self): return f'{self.id},{self.name},{self.email},{self.post},{self.date_created}' 

@app.route('/sign_up',methods = ["POST","GET"]) def sign_up(): if request.method == 'POST': email = request.form.get('email') first_name = request.form.get('first_name') password_1 = request.form.get('password') password_2 = request.form.get('password-2') user = User.query.filter_by(email=email).first() if user: flash("Email aleady exists", category = 'error') elif len(email) < 5: flash("Email name must be greater than 5 characters", category='error') elif len(first_name) < 2: flash("First Name too short",category='error') elif password_1 != password_2: flash("Make sure you typed the password correctly",category='error') elif len(password_1) < 8: flash("Make sure your password as at least 8 characters", category='error') else: new_user = User(name= first_name,email = email) db.session.add(new_user) db.session.commit() flash("Account Created",category='success') return redirect(url_for('home')) return render_template("sign-up.html")  

Nd i keep getting an error

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: user.id [SQL: INSERT INTO user (name, email, post, date_created) VALUES (?, ?, ?, ?)] [parameters: ('test', '[e-mail]', '', '2021-04-06 20:10:03.191785')] (Background on this error at: sqlalche.me/e/14/gkpj). The error I get –

[sinful condor]

I know its very chaotic but how would I make it so some things dont overlap on this https://pastebin.com/raw/YaUAm47x

like 18th place is also for some reason 23rd

and some of them are off like 24th is 26th

or maybe a better question would be how to remove the first x number of things in a list of dictionaries

[torn sphinx]

i need help with shelve why does it not save (resets after turning off my program) I'm using it properly (hopefully)

s = shelve.open('database', writeback=True)

s['broadcast']['last'] == date_now()

[burnt turret]

== is the equality operator, = is assignment

[edgy lotus]

    (self._error_message(selector), timeout, self.description))
pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 606f3b51b3a6539679519c0f, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.gooig.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed',)>, <ServerDescription ('cluster0-shard-00-01.gooig.mongodb.net', 27017) server_type: Unknown, rtt:
None, error=AutoReconnect('connection closed',)>, <ServerDescription ('cluster0-shard-00-02.gooig.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed',)>]>``` can somebody tell me what does this mean? i dont seem to understand

[torn sphinx]

Fuck i hope it's just that small mistake

Otherwise I'm fuked lol

[valid vault]

I want to get the nth result of a MySQL query, but I can't seem to find an efficient way to do so. Currently its literally doing

SELECT * FROM <table> WHERE <query> LIMIT <n>

and then Im doing c.fetchall()[-1]

What is a better way of doing this?

[proven arrow]

Just add offset

Of 1

LIMIT n, 1

You might have to do n-1 because I think it starts at 0.

[valid vault]

Yeah this is what I just found out. Thanks for that! I always thought offset worked before the query but of course it doesn't

[kind void]

Anyone in here using EdgeDB? It looks pretty nice. Was wondering about your experience with it

[burnt turret]

I'd asked the same thing a while back, but didn't get any responses :( probably gonna try it out for myself when I get the time

It does look super nice

[kind void]

Yea. Though the python lib is still pretty basic. I wonder how hard it would be to get pydantic to work with it...

[torn sphinx]

im setting s['destinations'] to an array containing the string 'me' but it returns a nonetype what

wait

how do i push and pull from a list in shelve?

[twin ivy]

Quick question about MongoDB, what would the if statement look like if it wanted to check if the value exist?

I was thinking that it would be something like this:

if collection.find({"value": something}).count()>=1:```

[jagged cove]

do you really need to have ids?

i'm making a very simplistic to-do list system

so i'm thinking i could just

/ list / task /

there's not really a use for IDs right?

at least in this scenario

[prisma girder]

Is it relational database?

[jagged cove]

no clue what that means but i highly doubt it

it's just a single table

2 rows (if IDs not necessary)

one to store in which list the task belongs

another to store the task itself

(just a long str)

[prisma girder]

Is it multiuser system?

[signal cloak]

Why do i get an error like this

[jagged cove]

it's complicated

it's for a personal server

each channel in the server gets a new to-do list

[proven arrow]

Then how do you uniquely identify a task?

[jagged cove]

by the text

all it really does it store user-submitted text

[proven arrow]

That’s inefficient but What if there’s multiple tasks with same name.

[signal cloak]

pymongo.errors.ServerSelectionTimeoutError:

[jagged cove]

then using a !view command they can see all the tasks in this channel

[signal cloak]

I cant fix it

[jagged cove]

or if the command is sent over DM, their private to-do list

[prisma girder]

Hard to say without code

[jagged cove]

private lists would employ the same system but the list field would be users instead

[proven arrow]

What if they want to delete a task? Must they type the entire task out back again letter by letter just so you can match it in the db?

[prisma girder]

However I am not using PyMongo

[jagged cove]

well tasks would be written very short

such as "fix blank" or "rewrite blank"

etc

[proven arrow]

There’s only benefits of having an ID, as to not. Also will help you meet the normal forms.

[signal cloak]

right here

[jagged cove]

true but then again it'd be rather difficult to employ, plus i didn't necessarily see the benefits at first

[signal cloak]

db = client.discordUsers
collection = db.user_data

[jagged cove]

@proven arrow although if i may ask a follow up question

if the main advantage is just deleting tasks more easily

would you go with like a number ID?

one that increases with each new tasks

or just a short randomly generated word

or string or whatever

[prisma girder]

How about connect=True?

[prisma girder]

Autoincrement int id is something like standard

[signal cloak]

i resolved it

[proven arrow]

Yes because int are better for performance. And if you use an auto increment for the column, it will increment itself for each row you add.

[jagged cove]

i mean when you use sqlite3 functions to insert items into a table it doesn't right?

[signal cloak]

how do i set a field in mongoDB to nuill

if collection.count_documents(search, limit=1):
     print("deeper")
     collection.find_one_and_update(search, 
        {"$unset":
        {"joined_at": null}
     })```

[torn sphinx]

hi, I have a problem, when using max() in mysql, it only returns (None,)

maxid = await obj.execute(
            "SELECT MAX(suggestionid) AS maxid FROM suggestions WHERE guildid = %s",
            (message.guild.id))
        print(maxid) # return (None,)
        print(maxid)
        if not maxid[0]:
            print(0)
            await obj.execute(
                "INSERT INTO suggestions(guildid, suggestionid)"
                "VALUES(%s, %s)", (message.guild.id, 0))
            maxid = 1
else:
    maxid = await obj.fetchone()

ignore syntax just use cat file

[void dawn]

Hello peoples

👍

[ornate fulcrum]

Ok i put it way simpler lol. py print("Found requested line from database:", result["writeup"]) If there was another key i wanted to print with "writeup", like "writeup2", how the cmdline should look?py print("Found requested line from database:", result["writeup","writeup2"])If i put it like this i get syntax error ?

[solemn root]

Which database should I use? I'm currently using SQLite3, but idk if I should switch or not.

[dull raptor]

how to make the bot autoreact to its own messages

[torn sphinx]

Can anyone help me understand this

@solemn root any thoughts on this

[solemn root]

It means it couldn't find the table. Also, you're trying to fetch directly from the table when you haven't actually selected anything because you are executing. You use .fetchall() in your cursor when you need to fetch the rows.

Like this:

        rows = crsr.fetchall()```@torn sphinx

[torn sphinx]

Hello people of the database channel

[solemn root]

Hullo

[torn sphinx]

Wait @ebon skiff what db am i supposed to use

[ebon skiff]

Postgre

[torn sphinx]

Lemme look it up

[ebon skiff]

https://vcokltfre.dev/tips/storage/

[torn sphinx]

I have to pay oh gosh

[ebon skiff]

Wut

It's free

[torn sphinx]

Ohh

It said I needed to pay in the front screen

[ebon skiff]

Sleep

[torn sphinx]

Wait I’m on the wrong thing

It says Avien

[ebon skiff]

Im going off

Goodday

[torn sphinx]

Wait nvm I’m right

Nvm no I’m not

I’m losing brain cells being up

sqlite3 is fine

it does the job

[solemn root]

I'm losing some to you losing some.

[torn sphinx]

I lost some from you saying your losing some from me losing some

[solemn root]

@torn sphinx Did you read my reply?

[torn sphinx]

I did yeah

Your way is good practice but my way works too

My problem was my .db file location

[solemn root]

oh, glad you were able to figure it out

[true lily]

OperationalError: database is locked

[prisma girder]

Can you make another table?

Like table blog_post with id, title and text columns and blog_comment with id, post_id and text columns

Then one post is able to have many comments

You can add banned_guilds with columns global_ban_id and guild_id?

[oak swan]

hi i install sql server 2019 on centos 7

when i try to conenct through windows using management stuido it throw error

some connection error

[prisma girder]

Are you sure that ports are open?

[oak swan]

i completely disabled the firewall @prisma girder

both in centos and windows

[prisma girder]

You can add column guild_id and have many entries in globalbans

[prisma girder]

Hard to say then

[oak swan]

is there anything that i have to allow db user remote setting or etc

[prisma girder]

Depending how you store guilds - in separate table?

When you have different table you store two ints, when you add one more column you store several fields twice or more

However it's one query less

Like in blog posts and comments example
blog_posts table columns

id | title | text

blog_comments table columns

id | post_id | text

blog_comments.post_id refers to blog_posts.id ofc

blog_posts is your globalbans

No, you have two tables in my example - blog_posts and blog_comments

Oh, okay

I didn't understand

So then when you have fields

member_id | staff_id | reason | time

You need to add one more column guild_id which contains information about banned guild

Like I said, you have data replication in this case

So you can add one more column and store few entries in globalbans table or add one more table and have many entires there

However you don't have staff_id and reason there

You can do it in this way

However I would rather use globalbans.id

When you have table globalbans add column id as a primary key

[prisma girder]

Create globalbanguilds with id as primary key, banid as foreign key to globalbans and guild_id

Or guild name, I don't know how you identify guilds

[torn sphinx]

How do I store an empty array in a shelve?

Shelve stores a NoneType instead of an empty array 🤔

[zealous nova]

hi, im using mongodb and motor, when i do this py await self.db['guild'].update_one({'_id': self.constants.GUILD_ID}, {'$set': {'leaderboard_id', leaderboard_id}})
i get this error

bson.errors.InvalidDocument: cannot encode object: {829987691037851648, 'leaderboard_id'}, of type: <class 'set'>

[prisma girder]

Isn't it a typo?
Not

{'leaderboard_id', leaderboard_id}

but

{'leaderboard_id': leaderboard_id}

[zealous nova]

i wanna die

rn

[prisma girder]

Don't do it!

[zealous nova]

lmfao

im such an idiot

[prisma girder]

The most important part of the error:

cannot encode object: ... type: <class 'set'>

[zealous nova]

yea

i was confused

like wtf

i didnt even realise i put a ,

well noow it works

[prisma girder]

🎉

[zealous nova]

lmfao

[torn sphinx]

How can I do this with Sql?
So I want to display all orders from orders table. But for each order I want it to say which order number (different to order ID) the order is for that user.

So if User A placed 2 orders then their first order row would contain order number 1 and second row contain order number 2.

[burnt turret]

the ROW_NUMBER window function would work I think?

[torn sphinx]

Can you give example? I don’t understand

[burnt turret]

something like ROW_NUMBER() OVER (PARTITION BY user) in your case

[torn sphinx]

Ok I’ll try this thxx

[bright flame]

Hi guys 🙂 and hope this is the correct channel to ask this question.
So far I have a database (postgreSQL ). and in that a table contains the person name and their face encodings.
So as an application, I have a face recogniton application which detects the faces and match against the face encodings from the
database.

To ensure a tamper proofness to the data(like no external entity added a new values or alter the values in the database table), I want to hash it in a way
such that, the hash will be store in another place.
So when my face recognition app runs, first it will check whether the hash of database matches with the hash we have stored.

So my question is

1. What is the effective way to hash --- hash the entire database or just the table.
   I think hashing the table alone is enough right, because its possible we may another table in the same database
2. if so, is there a tool for it?

Thanks in advance, sorry for the long message.

table for reference

[green raptor]

hello i use sqlite, how to check if genre exist?

getting this error:

i tried to check if genre "lol" exists

[prisma girder]

You shouldn't build queries in that way

[next smelt]

Is someone used to work with the SQL module of Qt ?

[prisma girder]

Use binding like in this examples: https://docs.python.org/3/library/sqlite3.html

cur.execute("SELECT * FROM stocks where symbol = ?", symbol)

[green raptor]

i used now but giving me other errors, i need to check if it exists or not

last time i used this and it worked, idk why it doesnt work now

[prisma girder]

fetch variable doesn't exist

[green raptor]

but how could i use it few days ago hahaha

i am so confused, i thought i did understood

[next smelt]

I don't know much about the sql lib you're using but why are you commiting after a select ?

from the doc, fetchall return an empty list if no rows are found

so chances are you can simplify your code by checking if the result of the query is an empty list or not

so return fetch != [], something like this (I don't do much python tbh)

[devout girder]

CREATE TABLE disabled_commands (
snowflake_id BIGINT,
command_name TEXT
)
how would i do on conflict for both columns?

[hushed hatch]

#discord-bots

[austere portal]

What's the postgresql uri if I am running on 127.0.0.1?. Thanks

[ornate fulcrum]

@commands.command()
    @commands.has_permissions(manage_messages=True)
    async def settimee(self, ctx, *, timee):
                embed = discord.Embed(description=f"```yaml\nSetting this post time:\n{timee}\n```", color=discord.Color.blue())
                myquery = {"_id": 1}
                collection.delete_one(myquery)
                post = {"_id": 1, "time":timee,}
                collection.insert_one(post)
                await ctx.send(embed=embed)
                print("This time set in database:", timee)  ``` Is this a correct way at all to replace old key value "time" with new one?

yaml is just the way text shows in diff colors

[burnt turret]

there's update_one, you don't need to delete and insert

[loud ivy]

Why is it wrong to use F strings in sql queries?

[burnt turret]

#databases message this explains it quite well

[loud ivy]

The ? is parsed by the sql module?

[brazen charm]

it's parsed by the database itself

[dull cypress]

it's not wrong but the input won't be sanitized before sending to the db. this means it's more prone to sql injection

[ornate fulcrum]

Ok that worked. This instead doesn't do anything. No errors either. It should post a random post from database once the time set in database equals in current time py @tasks.loop(seconds=1) async def called_once_a_day(self): self.target_channel = self.bot.get_channel(self.discordchannelid) x = datetime.datetime.utcnow() y = (x.strftime("%H:%M:%S")) mystuff = {"_id": 1} bot.findmystuff = collection.find(mystuff, {"time": 1}) z = bot.findmystuff if y == z: randness = collection.aggregate([{'$sample': {'size': 1 }}]) async for result in randness: print("Requested random post from database:", result["writeup"]) embed = discord.Embed(description=f"yaml\nMessage of the day:\n{result['writeup']}\n", color=discord.Color.blue()) await self.target_channel.send(embed=embed)

The code used a file before, and randness works under command.command() but now it doesn't reach there for some reason

[next smelt]

doesn't look database related

[ornate fulcrum]

It's mongodb and discordpy

Any directions where should i ask then?

[next smelt]

you shouls try the #discord-bots channel then

[ornate fulcrum]

Bet they point me here lol

mystuff = {"_id": 1}
findmystuff = collection.find(mystuff, {"time": 1})``` Making it simpler, is this correctly, does this grab the time keyvalue?

If time is 05:00:00 in database, findmystuff =05:00:00 or 1?

Should it be py {"time": {} } ?

So it would return any value time is set in database

[burnt turret]

that second dictionary you're passing is called a "projection", it just tells the database what all specific key:value pairs it should return from each document

doing "time": 1 there is basically the same as "time": True, and it's just saying "retrieve the 'time' key and it's value where the id matches what you specify in the first dictionary"

[burnt turret]

also, collection.find returns a Cursor object which you can loop over, so findmystuff won't really ever be exactly either of these

[fringe hazel]

Hey i'm writing a class called QueryHandler to handle all queries to different databases. The QueryHandler has a bunch of functions to run specific queries.

For example:

def queryX(self, date, person=None, department=None):
  # Run a SQL query with the specified parameters. 
  # ...
  return df

The actual query string is imported from another file. If for example person=None and department = None I import the SQL query QUERY_PERSON_DEPARTMENT. If person='Bob' and department = None I import the SQL query QUERY_PERSON, and so on.

Because the different SQL queries used by the function queryX() tend to be very similar I'm looking for a "best practice" to build a query string instead of having several almost duplicate pre-written queries imported and run from the function depending on the functions arguments.

My idea would be to have a file containing a template query, then format the template query with the WHERE statements as needed. Example:

QUERY_TEMPLATE = """
SELECT * FROM table WHERE date= %s {person_query, department_query}
"""

PERSON_QUERY = """ AND person=%s
"""

DEPARTMENT_QUERY = """
AND department=%s
"""

From my understanding concatenating a SQL query from python strings is bad (SQL Injection Prone?), what is the best option?

[ornate fulcrum]

Hmm ok so i probably should put it like py for result in findmystuff if y == result["time] print("okok")

I jumped a bit over everything in there 😄 but result gives the value of time in this case

@tasks.loop(seconds=1)
    async def called_once_a_day(self):
        self.target_channel = self.bot.get_channel(self.discordchannelid)
        x = datetime.datetime.utcnow()
        y = (x.strftime("%H:%M:%S"))
        mystuff = {"_id": 1}
        findmystuff = collection.find(mystuff, {"time": {} })
        for result in findmystuff:
            z = result["time"]
        if y == z:
            randness = collection.aggregate([{'$sample': {'size': 1 }}])
            async for result in randness:
                            SHORTENED THIS TO MAKE CLEARER``` error ```line 36, in called_once_a_day
    for result in findmystuff:
TypeError: 'AsyncIOMotorCursor' object is not iterable```

[burnt turret]

you need to async for result in findmystuff

[ornate fulcrum]

https://paste.pythondiscord.com/hodagipedo.sql

[burnt turret]

#databases message

it needs to be a True/False (1/0)

not the empty dictionary

[ornate fulcrum]

Holy mother IT WORKS THANKS @burnt turret !!!!!!!!

Works as intented, gets the time value

One question, does this tasks.loop check database each sec? Or just grab the value once and check the value each sec if it matches with value from database?

[burnt turret]

it's fetching from the database each second

[ornate fulcrum]

Ok need to do something for that i assume. But that's later. Thanks.

[tender steeple]

What do you guys use to manage your postgres databases? I've used mysqlworkbench before and I was looking for something similar.

[burnt turret]

pgadmin is the postgres equivalent to mysql's workbench I think

although I've never used it myself

[tender steeple]

All right, I'll check it out

[oak swan]

hi i isntall sql 2019 on centos 7

i can login from centos

but when i conenct through windows sql management studion

it throw this

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

[short flicker]

Having issues using cx_Oracle. Getting a ORA-01843 error "Not a valid Month"

and actual_date = to_date('01-FEB-2021','DD-MON-YYYY')
-- actual_date is in a date format
-- this works when run in sqldeveloper and plsql developer

I can't change the NLS_DATE_FORMAT

Anyone have a similar issue and know a way to get around this?
cx_Oracle Version : 8.1.0
cx_Oracle Client Version : 19,5,0,0,0

[noble cedar]

https://biorender.com/

[ornate fulcrum]

https://paste.pythondiscord.com/japifubimo.py trying to set the function a into global function that it get's fetched each hour(tasks.loop) from db, and other loop check each second if that variable a matches to current utctime. But getting error from line py if y == a: that a is not defined ? How to define it as global then?

[signal cloak]

How can I add a field in Mongo DB with my custom Objects in it

like a list with my custom objects

[torn sphinx]

How would i find a specific item in a file?

[torn sphinx]

Hi all, basic question here.

I've a user table, a user has a role (admin, developer, normal) which is a FK to role table.

Can I do this by having a user and a role table, or should I've 3 tables? if so, why is that?

I've googled around and saw that some people use 3 tables; user, user_role and role. But i can't seem to wrap my head around that

[shy shuttle]

are you using postgres? why are you concerned with the way those tables are used by the system?

[torn sphinx]

sorry, no just SQL (sqlalchemy), but i'm currently modelling the database

[shy shuttle]

are you making these tables to manage access to an application that will use the database?

[torn sphinx]

Yes, I'm working on a flask app

some sort of blog site!

[proven arrow]

The 3 tables is because it’s a many to many relationship

[shy shuttle]

either way, user is for.... users.... and the user-role table is a bridge table

[torn sphinx]

aaaah okay, that clicks

[proven arrow]

Your users and roles are linked by the third junction/pivot table

[gloomy canopy]

Mongo stores its data as JSON documents, so you'll have to serialize your objects to a structure that is compatible with JSON

[torn sphinx]

shouldn't it be 1 to many, because many users can have 1 role?

[proven arrow]

But a role can belong to many users

So it’s both ways

[shy shuttle]

no, users may have any number of roles

[torn sphinx]

there we go, I get it now

perfect, thanks guys!

    name = { "name": f"{username}", "pwd": f"{pwd}"}
                                 ^
SyntaxError: invalid syntax

##code##
db = client["logins"]
db2 = db["logins"]


def main():
    username = input("Whats Your Username: ")
    password = input("Choose A Password: ")
    password2 = input("Choose A Password: ")
    if password2 == password:
        name = { "name": f"{username}", "password": f"{password}"}
        x = db2.insert_one(name)
        else:
            pass

main()

[shy shuttle]

your else is on the wrong indent level

and you dont need the else, it's not doing anything

[signal cloak]

How can I do that?

[gloomy canopy]

You either need to convert your object and its properties to JSON by adding them to a dictionary and then storing that dictionary in Mongo, and then when you read from the database you can put it back into an object. Or you can use the pickle module to convert your Python code to a string which you can then store in the database, but pickle isn't safe if you open other peoples pickle files

[signal cloak]

Can I send you the model of the object

I need to add them in a list of objects

so the field in mongo is list of objects

[torn sphinx]

this looks ok, right?

[shy shuttle]

yep, that will do the job

[signal cloak]

How can I insert an object like that

    "id": "8a8229c7-5d0d-4356-8fbd-bf01531776ad",
    "title": "asda",
    "description": "xcvxcv",
    "media": ""
}```

to a document in MongoDB like that

{
"id": "",
"user": "",
"disciminator": "",
"notes": []
}

[shy shuttle]

that is already pretty much a json

[signal cloak]

I did