databases

[nova cove]

PRIMARY KEY is probably preferred over UNIQUE

[main oriole]

Ok, so it's a NoSQL DB, how do I make it a primary key column?

[nova cove]

there are no columns in NoSQL

it isn't relational

there are no tables

its just a bunch of JSON data put into collections

[main oriole]

Ah

Ok

Well

How do I make it so there's no way of a duplicate?

[nova cove]

well the type of NoSQL depends on if its a graph, document, wide-column, or key-value db

but NoSQL db's don't use SQL, as hinted in the name

[nova cove]

not sure I don't do NoSQL db's.

[main oriole]

Shit

[glossy matrix]

what's up Python gang, can anyone tell me why GCP is giving me this error?

    ```

[cobalt bolt]

(gonna post this again in hopes that more people are awake)

Anyone using SQLModel, Pydantic’s condecimal and Pyright? Pyright doesn’t like call expressions, i.e. condecimal being the type of a field, and although using a type alias works fine I’m wondering if anyone has another way? Working on a model for a crusty database with like 7 different MySQL decimal formats, and would prefer not to need 7 constrained decimal aliases (e.g. Decimal_10_2, Decimal_12_2, etc.)

[sonic hinge]

def in_json(filename, key, arg1):
    with open(filename, 'r') as f:
        data = json.load(f)
    return arg1 in data[key]

y is this erroring? im rinning it with these args

print(in_json("users.json", "UserID", "2638955970"))

[nova cove]

please don't use json as a db

[brave bridge]

what error are you getting?

[torn sphinx]

add if statement like py if arg1 in data[key]: return arg1?

lemme test ur code

[torn sphinx]

uh it returns a bool

but my json looks like json { "UserID" : ["2638955970"] }

out putTrue

[raven trail]

What is the best design pattern for handling having a route that will create an entry if none exists and otherwise will get the entry? What is the best way of doing this efficiently?

[torn sphinx]

I need to do this in the cmd of ubuntu for mongo (its apart of an assignments example) but i cant seem to get it to work. i have tried a google search and video searching but to no avail. I've typed is letter for letter in both the normal cmd and the mongo cmd, which again did not work. i made the database and the collections called vulnerabilities, this is meant to add some data to it i believe (i am learning about this). If someone has the time to explain what is happening and possibly what the error is, it would be greatly appreciated. This is wat they provided and there instructions were to simply copy the following and paste it in. The errors i receive from the normal cmd are a whole lot of commands not found. you might think this is stupid, to put it in there, logically that is. but the thing is, what they gave me had a $ Infront at the start as u can see. The error i get then in the mongo cmd is "missing } after property list :". I have looked it over so many times and cant find where i have missed it.```
$ db.vulnerabilities.insert
({a_number: 'A1', type:
'Injection', threatAgents:
'application specific',
exploitability: '3', "security
weakness": {prevalence: '2',
detectability: '3', },
"impacts": {technical: '3',
business: 'business
specific',}})

a ping would be appreciated when answering my problem, thanks.

[grim vault]

My guess would be to put this into the mongo cmd as a one liner:
$ db.vulnerabilities.insert({a_number: 'A1', type: 'Injection', threatAgents: 'application specific', exploitability: '3', "security weakness": {prevalence: '2', detectability: '3', }, "impacts": {technical: '3', business: 'business specific',}})

The line breaks mess up the syntax.

[torn sphinx]

How can i make that happen, i did try but yet again, i did not succeed, thanks for the feedback btw.

[torn sphinx]

i just realized, it is in a one liner, my screen is just small due to it being a virtual machine

[grim vault]

Sorry, I've never worked with mongo. I just made a guess.

[torn sphinx]

Thank you. non the less you tried. 🙂

[grim vault]

I don't see the db.vulnerabilities.insert part in the mongo-cmd screenshot. insert sounds like a function of the db instance, so the line in mongo-cmd should start with db.vulnerabilities.insert( (the open bracket is important). I'm pretty sure you can do it like:

db.vulnerabilities.insert({
    a_number: 'A1',
    type: 'Injection',
    threatAgents: 'application specific',
    exploitability: '3',
    "security weakness": {
        prevalence: '2',
        detectability: '3',
    },
    "impacts": {
        technical: '3',
        business: 'business specific',
    }
})

[torn sphinx]

The the previous commands to make it all is

[torn sphinx]

use NameOfDatabase This creates a db. then the cmd db.createCollection("vulnerabilities") which makes a collection when inside of NameOfDatabase then it says : "Let’s insert a document" then the rest is in the new screen shot

[grim vault]

I can't tell how the mongo-cmd pasrses it's sytnax, but in python the open bracket of a function call can't be in a new ,line.

[torn sphinx]

Ill do some more research. Thank you for your help.

[torn sphinx]

ima put this is a help channel

[crystal vapor]

Hi there i am trying to have the db save a age number for the discord user, like this, do you know how this would work?

[manic zinc]

Hi all, any help would be appreciate, been really stuck on this syntax for a bit, its probably something simple, but I cant seem to put my finger on it.

        if($staff_id)$staff_check = "WHERE s.id = '$staff_id'";
        
        $sql = "SELECT `work_schedule`.`staff_id`, `staff`.`id`, `staff`.`first_name`, `staff`.`last_name`,
                SUM(`job`.`radiation_exposure`) AS `exposure`
                FROM `work_schedule`
                $staff_check
                LEFT JOIN `staff` ON `work_schedule`.`staff_id` = `staff`.`id` 
                LEFT JOIN `job` ON `work_schedule`.`job_id` = `job`.`id`
                GROUP BY `staff`.`id`
                ORDER BY `exposure`;";```
I think its related to the variable WHERE clause, is there a certain way i should be importing it?

[gaunt kernel]

another simple question for the sql masters out there, whats the point of this pattern here

https://github.com/datacharmer/test_db/blob/master/employees.sql#L25-L27

[delicate field]

employees.sql lines 25 to 27

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;```

[gaunt kernel]

Isn't it kinda sketch to drop a database if it exists?

[grim vault]

It's a test database. So to have a defined state you just create it from scratch every time. Guess the IF NOT EXISTS for the create is not really needed because you just dropped it.

[torn sphinx]

sooo I can't connect to my MongoDB Database

1. What does it mean by version when I press on Connect from Atlas?

2. In the produced URL String, if I put my password after my username, it interprets is as the port number (wtf?)

[main oriole]

How do I make it so there's no duplicates in a JSON db?

Or

If there's a value put in that's already in that db, it overrides all the data in that row (idk what the row equivalent is in a JSON db)

[fading patrol]

Sounds like you mean a flat file? (JSON is not a DB.) You have to read the whole thing and search the existing values. If you want to store JSON in a DB, you could use MongoDB

[main oriole]

I'm using https://deta.sh/ atm, idk what it would class as, probably a NoSQL database?
https://docs.deta.sh/docs/base/about/

[fading patrol]

I see. I'm not at all familiar with it but it looks like a document store similar to MongoDB. You may need to fetchall() unless they give you a better way to check

[main oriole]

Honestly, I haven't got a clue

[fading patrol]

I don't see a better way glancing through the docs... so try fetchall(), should work fine if you're not dealing with many thousands of records

beyond that, exactly what you need to do depends on how your data is structured

[main oriole]

I don't know how many records there'll be

It depends entirely on how many server my discord bot goes into

[fading patrol]

You probably want to structure your data in a SQL db instead. It's easy to spin up a free instance of Postgres on Heroku.

You don't need to learn SQL in great depth, just the basic concepts of joins, foreign keys, etc.

[main oriole]

Doesn't heroku only have like 500 hours?

[main oriole]

@fading patrol I was gonna use Deta because it's free permanently and also that I can have it up at all times

Whereas heroku has 500 hours iirc

[nova cove]

don't host discord bots on Heroku

[main oriole]

It's the database

Not the actual bot

I'm still finding a free host that isn't replit or heroku for thag

[nova cove]

you can host on a vps, but it isn't free

it is extremely cheap though

[main oriole]

My bank account is dry as fuck

[wooden needle]

if any(ctx.content.lower() == bad_word for bad_word in bad_words):
                await ctx.delete()
                user_id = ctx.author.id
                username = ctx.author.name
                for doc in self.collection.find_one({"_id": user_id}):
                    if doc["_id"] == user_id:
                        self.collection.update_one({"_id": user_id}, {"$inc": {"score": 1}})
                    else:
                        self.collection.insert_one({"_id": user_id, "name": username, "score": 1})
                    if doc["score"] >= 5:
                        role = ctx.guild.get_role(915104422277824534)
                        await user.add_roles(role)
                        doc["score"] = 0
                        embed = disnake.Embed(description=f"{user.mention} has been muted for 5 minutes for using slurs.")
                        await ctx.channel.send(embed=embed)
                        await asyncio.sleep(300)
                        await user.remove_roles(role)

i have this code as part of my discord bot

but

i keep running into an error with mongo

when i try to do for doc in self.collection.find_one({"_id": user_id}):

what exactly am i doing wrong

pymongo.errors.ServerSelectionTimeoutError: joybot-shard-00-01.zhidz.mongodb.net:27017: connection closed,joybot-shard-00-02.zhidz.mongodb.net:27017: connection closed,joybot-shard-00-00.zhidz.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 62278dbbd35c65940a393bc2, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('joybot-shard-00-00.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-00.zhidz.mongodb.net:27017: connection closed')>, <ServerDescription ('joybot-shard-00-01.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-01.zhidz.mongodb.net:27017: connection closed')>, <ServerDescription ('joybot-shard-00-02.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-02.zhidz.mongodb.net:27017: connection closed')>]>

my error

[cunning wagon]

One second as I look it over

@wooden needle did you close it at the end of the code?

[wooden needle]

oh was i supposed to

im new to mongo 💀

databases in general actually

[cunning wagon]

Try that as a fix

[wooden needle]

doc = self.collection.find_one({"_id": user_id})
                if doc["_id"] == user_id:
                    self.collection.update_one({"_id": user_id}, {"$inc": {"score": 1}})
                else:
                    self.collection.insert_one({"_id": user_id, "name": username, "score": 1})
                if doc["score"] >= 5:
                    role = ctx.guild.get_role(915104422277824534)
                    await user.add_roles(role)
                    doc["score"] = 0
                    self.cluster.close()

my code rn

*just the mongo bit

[cunning wagon]

That should be correct

[wooden needle]

k ill try

[cunning wagon]

Ok

[wooden needle]

hmm doesnt work

same error

and nothing changes in the collection

[cunning wagon]

Hmm

Ok so, I looked back at your code and it seems that there is a line somewhere that’s causing a break in your code.

[wooden needle]

oh

wait i can send the full error

ut

its a bit long

[cunning wagon]

Yes please

Dm me it if you want

[wooden needle]

File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\disnake\client.py", line 531, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\\Desktop\Programming\Joy-The-Cat-Bot\cogs\core_cog.py", line 63, in on_message
    doc = self.collection.find_one({"_id": user_id})
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\collection.py", line 1114, in find_one
    for result in cursor.limit(-1):
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\cursor.py", line 1159, in next
    if len(self.__data) or self._refresh():
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\cursor.py", line 1057, in _refresh
    self.__session = self.__collection.database.client._ensure_session()
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\mongo_client.py", line 1603, in _ensure_session    
    return self.__start_session(True, causal_consistency=False)
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\mongo_client.py", line 1553, in __start_session    
    server_session = self._get_server_session()
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\mongo_client.py", line 1589, in ```

_get_server_session
    return self._topology.get_server_session()
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\topology.py", line 530, in get_server_session
    session_timeout = self._check_session_support()
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\topology.py", line 514, in _check_session_support
    self._select_servers_loop(
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\pymongo\topology.py", line 216, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: joybot-shard-00-02.zhidz.mongodb.net:27017: connection closed,joybot-shard-00-00.zhidz.mongodb.net:27017: connection closed,joybot-shard-00-01.zhidz.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 622792edbf8102a07c84a923, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('joybot-shard-00-00.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-00.zhidz.mongodb.net:27017: connection closed')>, <ServerDescription ('joybot-shard-00-01.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-01.zhidz.mongodb.net:27017: connection closed')>, <ServerDescription ('joybot-shard-00-02.zhidz.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('joybot-shard-00-02.zhidz.mongodb.net:27017: connection closed')>]>

[cunning wagon]

One second as I look it over

[wooden needle]

thanks

[cunning wagon]

Ok I believe it’s 1 of two things. 1) there is something in the code that is causing it to stop 2) I believe this might be it, there is probably something that is trying to run but it is taking to long so it times out

[wooden needle]

oh

the only thing in my code that takes a long time to execute is a script i set up with the reddit api

but thats unrelated to this

everything else runs smoothly

[wooden needle]

if you have any experience with discord/disnake.py i can send all the code

@bot.event
async def on_message(message)
  if any(ctx.content.lower() == bad_word for bad_word in bad_words):
                await ctx.delete()
                # guild = ctx.guild.id
                user_id = ctx.author.id
                username = ctx.author.name
                doc = self.collection.find_one({"_id": user_id})
                if doc["_id"] == user_id:
                    self.collection.update_one({"_id": user_id}, {"$inc": {"score": 1}})
                else:
                    self.collection.insert_one({"_id": user_id, "name": username, "score": 1})
                if doc["score"] >= 5:
                    role = ctx.guild.get_role(915104422277824534)
                    await user.add_roles(role)
                    doc["score"] = 0
                    self.cluster.close()
                    embed = disnake.Embed(description=f"{user.mention} has been muted for 5 minutes for using slurs.")
                    await ctx.channel.send(embed=embed)
                    await asyncio.sleep(300)
                    await user.remove_roles(role)

[cunning wagon]

Something in there is taking way to long. I don’t know what because I don’t have the code, so I would recommend looking through

I am about to log off, but if you do me it I will take a look at it later and fix it

[wooden needle]

ah sure

thanks

[cunning wagon]

Ok, thank you

Anytime

[wooden needle]

oh i think i got it

i dont have a doc in the collection with _id: user_id

how can i check if it exists without using find_one

[grim vault]

I think you want:
if any(bad_word in ctx.content.lower() for bad_word in bad_words):
to check if the bad word is in the content, otherwise the full content must be the bad word only.

[vernal kettle]

can someone tell me if i'm taking crazy pills or one of my bosses is? because he just came in and asked me to turn the general query log on our QA server so that he could look at all the queries... and as his stated goals he said both "the data needs to be normalized" and "we need to stop using joins" -- aren't those the opposite?

i hope that teaching him the EXPLAIN SELECT command exists will make him understand.

[empty hinge]

He probably just mispoke or misunderstood and wants de-normalized views of the data

if you don't have any that makes understanding things at a glance quite hard

[tropic dagger]

Has anybody got a good guide explaining how SQLalchemy and alembic work, step by step, please?

[split lagoon]

hi guys I need help...
I am trying to learn sqlite3 and I am getting an error that I have never seen before can you pls help me?
the error is the image. thanks

[nova cove]

you didn't close the parenthesis...

[split lagoon]

I did

just a sec

[nova cove]

show more code

[split lagoon]

c.execute("""
    CREATE TABLE customers (
        first_name TEXT,
        last_name TEXT,
        email TEXT,
    )
""")

[nova cove]

remove trailing comma and add ; after the parenthesis

        email TEXT
    );

[split lagoon]

ok

thanks

same error

never mind I amnot smart

thanks

[torn sphinx]

trying to start adding databases to my bot what database do yall recommed me to use??

[nova cove]

SQLite if you are a beginner

And if it’s a small bot

But, PostgreSQL once you get more experience and your bot grows

[torn sphinx]

thx

[nova cove]

np

[frosty kernel]

Anyone know what [...] means in front of a value after updating it?

[empty hinge]

The official tutorial is excellent: https://docs.sqlalchemy.org/en/14/tutorial/index.html

[nova cove]

Show some code

[frosty kernel]

I figured out it's whitespace and had to lstrip it

[nova cove]

oh ok lol

[nocturne sequoia]

Hii, Can anyone guide me how to run a python script only one time for data from the database and if some new data is added then it only runs for them not for the older one which already been executed

[wooden needle]

class Test:
    def __init__(self, mongo_url):
        self.url = mongo_url

    user_id = 0000
    username = 0000
    cluster = MongoClient(MONGO)
    db = cluster["discord"]
    collection = db["user_info"]

    @classmethod
    def main(cls, txt):
        if txt in bad_words:
            if cls.collection.find_one({"_id": Test.user_id}):
                cls.collection.update_one({"_id": Test.user_id}, {"$inc": {"score": 1}})
            else:
                cls.collection.insert_one({"_id": Test.user_id, "name": Test.username, "score": 1})
            print("Warn Applied!")
            if cls.collection.find_one({"_id": Test.user_id})["score"] >= 5:
                print("Final Warn!")
                cls.collection.find_one({"_id": Test.user_id})["score"] = 0
        else:
            print("Ok...")


while True:
    Test.main(input(">"))

just a test to check if my input is a "bad word" and if it is

i want to check if a document with _id: user_id exists

if it does

i want to update the score of that document

otherwise i want to create a document with that id

doesnt seem to work

[crystal vapor]

Hello am using MongoDb to store data in and i was wondering how do i store the users age in the DB, like the bot will know from the user if that make sense here is a ss

Can any one help me out please

[radiant pine]

hey does anyone here know about many to many relationship in python

[wise goblet]

Table #2
Id

Table #2
I'd

Table #3 links
Table #1 Id
Table #2 id

Woala. Many to many.

[radiant pine]

well , i tried that

[radiant pine]

which database do you use

[wise goblet]

Postgresql

[radiant pine]

im currently using postgres

[radiant pine]

porfect

made for each other

😂

[torn sphinx]

anyone

i need help

when ever i open python it close 😢

[radiant pine]

that describes it well🥲

[tepid maple]

anyone experienced "SSL SYSCALL error: Software caused connection abort (0x00002745/10053)" when executing program that had postgreSQL connection?

[radiant pine]

so @wise goblet are u free for a minute

[wise goblet]

What is your SQL library

[radiant pine]

what's that ?

[wise goblet]

I am probably too lazy for this, it will take me time to setup from zero

[radiant pine]

actually i already did everything what i can not do is make a model class out of it and link two models together

[crystal vapor]

Hello

[wise goblet]

U can write in raw SQL
Or in different ORM languages, like Sqlalchemy or DjangoORM.

It will make declaration of tables in python classes code

[radiant pine]

my aim is creating a user and he has 4 skills python c django etc now multiple users can have multiple skills

[wise goblet]

I am lately using only djangoORM, only in this thing I can do fast

[radiant pine]

Yup im using it in django too

ill dm u later if ur not free rn

[wise goblet]

Or better ping here later

[radiant pine]

ok sure

[wise goblet]

I am in the middle of global infra refactorization

Moving my infra from managed kubernetes in DigitalOcean to cheap bare metal microk8s in VPS ;)

[crystal vapor]

Hello

Can someone help me please?

[wise goblet]

https://pythondiscord.com/pages/resources/guides/asking-good-questions/

That is really bad question to ask

[crystal vapor]

Well i did ask a question but no one answered me

but any way

Hello am using MongoDb to store data in and i was wondering how do i store the users age in the DB, like the bot will know from the user if that make sense here is a ss

[wise goblet]

Make at least link in msg to older question, if not wishing copying fully

[wise goblet]

MongoDB is just sophisticated JSON, so it should be having type of data for numbers

[crystal vapor]

Ok well i want it to represend the user actual age.

[wise goblet]

https://stackoverflow.com/questions/40740415/create-numberlong-integer-using-pymongo

https://www.geeksforgeeks.org/datatypes-in-mongodb/

[wise goblet]

Basically check the docs for the python mongo library u a using

It will give u precise answer how to create integer instance to store age

[crystal vapor]

Yeah but it will be able to tell the users age without actually storing the age in the db?

[crystal vapor]

^

[wise goblet]

I don't understand your question

Database is not responsible for giving its data without storing

If u wish to do it without storing in db, u don't use db

U use smth else

[crystal vapor]

OK whats up with that then?

Ok what am trying to do is for my profile command i am trying to store the users age so when i run my command it will have the age field with also the users age based off the users role

Does that make sense?

now

[wise goblet]

How long do u wish to store user age

How can be age based on user role

If u wish to store the age in persistent way between app restarts...
U have option only to store in file locally or in database, it can be postgres, it can be mongo, and many other choices

If u don't need to store age between app restarts, u can store it in application memory
Optionally in Redis in memory database

[compact marlin]

hi i have this piece of code that i think is working and its letting me use an sqlite file

`try:
sqliteConnection = sqlite3.connect('money_data.sqlite')
cursor = sqliteConnection.cursor()
print("Database created and Successfully Connected to SQLite")

sqlite_select_Query = "select sqlite_version();"
cursor.execute(sqlite_select_Query)
record = cursor.fetchall()
print("SQLite Database Version is: ", record)
cursor.close()

except sqlite3.Error as error:
print("Error while connecting to sqlite", error)
finally:
if sqliteConnection:
sqliteConnection.close()
print("The SQLite connection is closed")`

but i dont know how to dump data to the sqlite file

[tough kettle]

can anyone here help me with Relationships
I'm using SQLAlchemy
Here's the Code

class User(db.Model):
    """ User Class, Will Represent all The Users """
    __tablename__ = "user"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(1000), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password = db.Column(db.String(100), nullable=False)

    todo_lists = db.relationship("TodoList", back_populates="user")

    def __repr__(self):
        return f"<User: {self.name}>"


class TodoList(db.Model):
    """
    This Class Will Represent the table "todo_data"
    Each User Can Have As Many TodoLists As they Want
    """
    __tablename__ = "todo_list"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    user = db.relationship('User', back_populates="todo_lists")
    list_data = db.relationship('ListItem')  # db.Column(db.String, nullable=False)

    def __repr__(self):
        return f"<TodoList: {self.id}>"


class ListItem(db.Model):
    """
    This Class will Depend On The TodoList Class Which depends on the User Class\n
    So Each User can have different TodoLists, and that TodoList can have different List-Items
    This class Represents That
    """
    __tablename__ = "list_items"

    id = db.Column(db.Integer, primary_key=True)

    list_id = db.Column(db.Integer, db.ForeignKey('todo_list.id'))
    list = db.relationship('TodoList', back_populates="todo_lists")

db.create_all()

Here's how i create users

user = User(name="name", email="email", password="password")
db.session.add(user)
db.session.commit()

Here's how TodoList should be created

new_li = TodoList(
    user=user, # For now using the user which was created above
)

db.session.add(new_li)
db.session.commit()

Pls ping me when u reply

[urban sail]

hello my code https://paste.pythondiscord.com/ubaninebap my code works

but when i interrupt my code in between to check proper data is inserted in table or not . when i use select * from table_name i am getting empty rows

ping me when reply

[frigid ruin]

I want to compare timestamp fields to a standard timestamp value, and the field can be in any format (epoch, yyyymmdd hhmmss), how can I handle all these timestamp values

[pure sleet]

date time

[wooden needle]

Datetime moment

[brave bridge]

Where do you get these values in unknown format from?

And what date does 20051206 represent?

It could be 12th June 2005 or 6th December 2005 or 20th May 1206

If it's really any format and not a fixed list of distinct formats, you can't decide what it is

[frigid ruin]

Yup

It can be in the form of epoch or the timestamp

And if I convert epoch to timestamp, I can't handle standard timestamp, and vise versa

[brave bridge]

Where are you getting the data from?

Just convert it to a single format before storing/processing it

[frigid ruin]

From data sources

[brave bridge]

That's as vague as it gets

So why can't you convert the timestamp to epoch when you fetch it from the data source?

[rocky arch]

Hey guys I am currently learning SQl but I am not sure how to use it correctly.

I programmed a Discord Bot which offers a Game and saves a lot of stuff while people are playing. One game is about 15min and people have write a lot of messages. I want to store all these Information's (how much messages where send each min etc). Right now I do all this with a normal python list.

My question is, how do I save all these Information's the right way? Do I write every new message instantly in the SQl Database or do I create a pyhton list and only add it to the SQL database after the Game ends? I am really not sure how fast this connection is.

And I also have to read older entry's and add a new entry depending on the last one.
(pls ping me)

[nova cove]

you make queries to the database, using SQL, based on your Python data.

you can use ORM's to make doing queries easier

[rocky arch]

ORM's?

[frigid ruin]

Like database for example

Like database for example

Or stripe

[rocky arch]

But that basically means, I safe everything in my SQL database and dont store it in a python list until the game is over?

[frigid ruin]

Other message didn't delivered 😅

[frigid ruin]

But instead of epoch if I get a standard timestamp value, my code fails

It's the case for postgres

[nova cove]

Object Relational Mapper

so you don't have to write raw SQL which is kinda annoying sometimes

[rocky arch]

Yeah thats true. But tbh I am a bit lost with SQL in python and think this would make it even more complicated for me right now 🙈

[grizzled wadi]

Yeah, ORMs can bring other benefits too such as autocomplete and the ability to statically type check your queries which is difficult / impossible to do with SQL

In my (biased) opinion, the ORM I've created (https://github.com/RobertCraigie/prisma-client-py) is even easier to use than raw SQL as you get autocomplete and your editor will show you any errors you make when writing a query

[brave bridge]

Well, then if you get a standard timestamp, convert it to epoch

Are you asking how to detect which format it is?

[frigid ruin]

I mean is there a way to write a single query that can handle all this

[grim vault]

Without examples not really possible to tell. I would guess: yes, there might be a way.

[cobalt bolt]

@grizzled wadi I want to spend some time with Prisma. Are there any examples comparable to an SQLModel setup around, since that’s what I’m using?

[grizzled wadi]

There aren't any at the moment, sorry, there are other examples you can check out though https://github.com/RobertCraigie/prisma-client-py/tree/main/examples

[gusty mulch]

what's the recommended package for interacting with postgres sql dbs? One of my bots uses asyncpng but I've also heard of psycopg2

[cobalt bolt]

Thanks, I’ll just have to do some playing around

[grizzled wadi]

Let me know if you have any questions :)

[cobalt bolt]

there's no in-memory connectors yet right? that's more of a Prisma core issue?

[grizzled wadi]

No there isn't yet unfortunately and that's correct it is a Prisma core issue

[cobalt bolt]

gotchya. no biggie, just been doing my SQLModel stuff in-memory. Feels great to use so far though

[grizzled wadi]

Thanks :)

[hexed notch]

i am doing project where i have a huge list where i have to (Print a data frame with only two columns item_name and item_price ) can anyone help me with this?

[unborn sentinel]

Okay, my brain isn't working. Say you have two tables in SQL. We have 2 tables, Users and Groups.

We can have users be in multiple groups and a group will have multiple users. My thought is to have an interim table that has a single user and a single group they're a part of

What would that middle table be called

[brave bridge]

many-to-many relation?

ah, you mean the table?

[unborn sentinel]

Yeah

[brave bridge]

users_groups or something

[unborn sentinel]

Is there a term for that, though?

[brave bridge]

or group_memberships

[unborn sentinel]

Or is it just another table

[brave bridge]

In a relational database management system, such relationships are usually implemented by means of an associative table (also known as join table, junction table or cross-reference table), say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).
https://en.wikipedia.org/wiki/Many-to-many_(data_model)

[unborn sentinel]

Gooooootcha

Thanks, it was driving me bonkers

[brave bridge]

TIL that this table has a name lmao

[unborn sentinel]

Yeah I knew there was a specific term. Makes it easier for me to hunt for

[dry crag]

Hey guys, can someone explain to me why is this query returning None values instead of 0 in case there are no records between queried period of time?

                                         Table "public.transactions"
      Column      |            Type             | Collation | Nullable |           Default            
------------------+-----------------------------+-----------+----------+------------------------------
 id               | bigint                      |           | not null | generated always as identity
 info             | text                        |           |          | 
 title            | text                        |           |          | 
 amount           | numeric(15,6)               |           | not null | 
 currency         | text                        |           | not null | 
 src_amount       | numeric(15,6)               |           |          | 
 src_currency     | text                        |           |          | 
 transaction_date | timestamp without time zone |           | not null | 
 place            | text                        |           |          | 
 category         | text                        |           |          | 
 user_id          | bigint                      |           |          | 
 bank_id          | bigint                      |           |          | 
Indexes:
    "transactions_pkey" PRIMARY KEY, btree (id)
    "upsert_constraint" UNIQUE CONSTRAINT, btree (amount, currency, transaction_date, user_id, bank_id)
Foreign-key constraints:
    "transactions_bank_id_fkey" FOREIGN KEY (bank_id) REFERENCES banks(id) ON DELETE SET NULL
    "transactions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

SELECT
    SUM (CASE
            WHEN "amount" >= 0 THEN "amount"
            ELSE 0
        END) AS incoming,
    SUM (CASE
            WHEN "amount" < 0 THEN "amount"
            ELSE 0
        END) AS outgoing,
    SUM ("amount") AS difference
FROM "transactions"
WHERE "transaction_date"  
    BETWEEN '2021-11-01 00:00:00' AND '2021-12-01 00:00:00';

Crap, it seems writing this question actually enlightened me. When there are no records, I'm returning NULL to SUM functions, and therefore returning tuple of NULLs.

So another question, is it OK to use COALESCE to change NULLs to 0?

Or is there more elegant way to structure this query?

[winter token]

I have a sqlite database in which I store my custom objects "Track". Info is like Album, Artist, Year, Genre and such. When I want to get it from the database I do something like this:

    def get_tracks_by(self, key: str, value: Union[str, int]) -> Iterable[Track]:
        conn = self.get_connection()
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        if value:
            value = value.replace("'", "''")
            cursor.execute(f"SELECT * FROM tracks WHERE {key} = '{value}'")
        else:
            cursor.execute(f"SELECT * FROM tracks WHERE {key} IS NULL")

        tracks: List[Track] = []
        rows = cursor.fetchall()
        for row in rows:
            track = Track(
                track_id=row["track_id"],
                file_path=row["file_path"],
                title=row["title"],
                album=row["album"],
                artist=row["artist"],
                composer=row["composer"],
                genre=row["genre"],
                year=row["year"],
                length=row["length"],
                )

            tracks.append(track)

        return tracks

I would like to optimize the speed of this, if that's possible. It works fast as is, but when I wanna get 12000 Track objects from the database, it takes almost 0.6 seconds, which creates some lag in my PyQt application. Is there a way to improve this algorithm?

I tried returning a generator instead of a list but it didn't make any difference (never used them before, so I don't know if I should use them here)

[waxen finch]

i believe the generator's a step in the right direction but using fetchall() will still load all 12000 tracks into memory

to load one row at a time you can keep calling fetchone() until it returns None

and then just make sure your application only retrieves rows as necessary

[ionic pecan]

absoluteuly, that's the reason it's there :D

[sterile ocean]

i don't know if i'm in the right place or not. this all looks alot move advanced then what i'm doing.

[empty hinge]

the advantage of a generator is that unlike a list it doesn't have to have all the data in place before you can iterate over it - it "generates" data on the fly every time dunder-next() is called. If you're going to be iterating over all the data for some purpose immediately anyway, then a generator offers no real difference in performance or implementation compared to a list

120,000 rows with that many columns is a decent chunk of data. I don't think you'll be able to affect any noticeable performance difference working in standard python. Have you considered adding a loading spinner?

[devout pilot]

how would i add all the values in a column

[empty hinge]

with a SUM aggregator. exact syntax depends on your db engine.

[devout pilot]

sorry im new to using db, im using sqlite3 so how would i do it?

[empty hinge]

https://sqlite.org/lang_aggfunc.html#sumunc, https://docs.python.org/3/library/sqlite3.html should be good starting resources.

https://www.sqlitetutorial.net/ also seems legit. Tho I have a lot of adblocking on by default. I tend to prefer the official documentation sources (sqlite.org and python.org)

[devout pilot]

ty

[devout pilot]

i read this but i don't get it, would it be something like

cursor.execute("TOTAL result1")

or am i just completely wrong?

result1 is the column btw

[waxen finch]

i would suggest sqlbolt, its got an interactive tutorial and also goes over how to use aggregate functions later in the lessons https://sqlbolt.com/lesson/select_queries_with_aggregates

but the syntax for using total would look like this sql SELECT TOTAL(a_column) FROM my_table;

[devout pilot]

ty

[storm mauve]

you probably meant SUM though?

[devout pilot]

i read one of the things and it said that it would cause an integer overload if they are all integers, all of mine will be integers

[storm mauve]

your values would have to be really big in order to cause an overflow

[devout pilot]

that makes sense

[devout pilot]

do i still do cursor.execute for this

[waxen finch]

yeah

[storm mauve]

it looks like sqlite3 turns them into floats instead of overflowing though?

[devout pilot]

for sum it's the same as before but just replacing total with sum, right?

[storm mauve]

!e yeah.
Also, about overflowing: I'd expect for it to behave the same in this case as with SUM so```py
import sqlite3
con = sqlite3.connect(":memory:")
print(con.execute(f"SELECT {2 ** 62}").fetchone())
print(con.execute(f"SELECT {2 ** 62} + {2 ** 62}").fetchone())
print(con.execute(f"SELECT {2 ** 63}").fetchone())

[delicate field]

@storm mauve :white_check_mark: Your eval job has completed with return code 0.

001 | (4611686018427387904,)
002 | (9.223372036854776e+18,)
003 | (9.223372036854776e+18,)

[devout pilot]

for some reason i got

<sqlite3.Cursor object at 0x0000022D7B988740>

[storm mauve]

you must fetchone() or fetchall()

[devout pilot]

ty for your help

it prints (48,) how would i make it just give 48

[storm mauve]

that is a tuple containing the element 48, so you must get the first element of a tuple

[devout pilot]

Ty

[bronze spire]

can anyone tell me what the number(,) is?

[proven arrow]

Do you mean the 11,0 ?

That is for precision and scale

NUMBER(precision, scale)

[bronze spire]

yea the 11, 0

[rocky arch]

Is it normal in SQL to create multiple Databases for one problem? Or should I always use one Database with dozens of tables?
I am working on a Discord bot and I am not sure if I should create a new Database for every new Server. Is this a normal thing to do? And can I connect these Databases somehow?

[proven arrow]

No it is not normal to do this. Goes against the whole principle of what databases were made for and how they should be used.

[bronze spire]

oh ok thx

[proven arrow]

@rocky arch database per server you should not even consider unless they are completely decoupled applications. And here are some reasons for why you should not have a table for each server again if it’s running under the same application. #databases message

[rocky arch]

Thanks, good I asked

[proven arrow]

But the main reason above all is try to stick to following the normal forms. It will simplify programming and development as well.

[rocky arch]

I am new to sql and dont know the "normal forms" 🙈

[proven arrow]

They are just some rules on how databases should be designed. Not sure what guides are online resources are good these days for it because I don’t look at those resources anymore, but a quick web search should bring up some results.

https://en.m.wikipedia.org/wiki/Database_normalization

[rocky arch]

okay yeah makes sense. So my "server" table could look like this:
Server table
server1, info1
server1, info2
server1, info3
server2, info4
server3, info5
server1, info6
server3, info7

Instead of

Table Server 1
server1, info1
server1, info2
server1, info3
server1, info6

Table Server 2
server2, info4

And so on.

ups sorry for pinging

[rocky arch]

okay thanks

[proven arrow]

Yes like that, you get the idea

[rocky arch]

Ah okay. Yeah that helps a lot, thank you

[tall bolt]

sup folks?
I'm trying to count unique values in a df column, I do len() but sometimes there is nan that I want to exclude. Do you know of any fast method on counting the number of values minus the nan value?

[rocky arch]

One more thing, I want to store a lot of data in one "user" database.
Is there a limit on how much I can store in a table? And will it get slower?

[tall bolt]

and why --if nan in df-- doesn't work?

[proven arrow]

There is no actual limit. Your more likely to hit your filesystem storage limit before any database limit. “Slow” depends on many factors. So it is difficult to give an answer without seeing any database structure.

However; you should not worry about performance or these kind of issues now. Databases are designed to store lots and lots of data.

[rocky arch]

Lets say, my bot is on 50 big Discord Servers and I want to safe every message with time stemp.
And then I want to sort for every message from one specific user send on Server x.
(I dont want to do that and I am never going to have so much data, but maybe its a good example)

[rocky arch]

Its more about the "Select ... Where", where I am not sure about.

[proven arrow]

It depends how often you write to the database. Not sure what your numbers are.

[storm mauve]

for reference: Even SQLite is designed to handle over a Terabyte of data, discord is storing our messages in a database somewhere, and you can Search (though it's sometimes quite broken) in here

[rocky arch]

You mean how often I've got a new entry? Lets say very often.

[rocky arch]

Ahh yeah thats a really good example. Okay so I am more then safe with that

[proven arrow]

Again this depends on your table structure as I said before. Hard to know without seeing anything. Having indexes on columns will speed up filtering, and make your selects faster. But indexing also comes at a cost.

[rocky arch]

I dont know my structure yet, this is also my main problem. I had Sql in school for some time, but that was some years ago. I am trying to work my way back in.

But I think etrotta's point with "discord" is pretty solid. And my database wont be a fraction of a fraction... of this.

[proven arrow]

Do you know if discord uses sql? At this scale I don’t think so.

[rocky arch]

Oh I read it like "discord is storing our messages in a sql database". But true, idk. But what else?

[proven arrow]

They probably use some nosql data store. Maybe not in all parts of their application but most yes.

I think a good plan would first be to figure out what you want to store, and what you want your database to handle. Think of the tables you might need, what columns and relations between these tables. Then worry about the rest later.

[storm mauve]

I think that it's nosql (Cassandra iirc?), but still, SQL can handle it well

[rocky arch]

Sorry that I switch the subject, but the python panda module is also some kind of a database module right?
Maybe I should have informed my self better before working on something.
I really dont like writing SQL code in my python script. :/

[storm mauve]

pandas is not a database.

[proven arrow]

Use an ORM if you want to avoid writing sql.

[storm mauve]

it is mostly an analytical tool - you can load data into it, then explore that data, perform data transformations, or feed it into Machine Learning models, and the results back to a file, but it is not a file storage format

[proven arrow]

But with it comes a learning curve, and so like everything else you need to weigh up the advantages and disadvantages of it.

[rocky arch]

I know what I want to store, because until know I use python lists for everything. And I want to transfer everything to something that was created to store data.
I am also using pebbleHost (a host server) which has its own mysql module. Thats why I thought of it.

[rocky arch]

uhm ups, thought I read it somewhere

[storm mauve]

pandas is widely used for working with data, but it's all in memory.
it can read and write files, but it isn't a database on it's own

[rocky arch]

Ah okay, good to know

I guess sql is the best solution. And I will have a look at ORM

okay lol ORM sounds amazing

[storm mauve]

it might be worth it to learn how to do it without it, but yeah take a look at SQLAlchemy or others I guess

[nova cove]

Prisma is the best ORM

like it is high quality, type safe, and easy to use/understand

[rocky arch]

for python?

[grizzled wadi]

Yeah

I am the creator of the python version so if you have any questions or need any help feel free to ping me :)

[nova cove]

@grizzled wadi legend

you are a legend

[grizzled wadi]

Thanks :)

That means a lot to me :)

[nova cove]

I'm currently using the NodeJS Prisma for a NestJS app, but I assume that it is likely the same

I read your docs

its the same (besides Python specific things)

[grizzled wadi]

Yeah it is very similar, one of my goals with it was to try and make the transition between languages as seamless as possible

[nova cove]

If you happen to know, how can I return my query in JSON for my endpoints

it returns JSON (with the information I provide through my request maker) but I want it to have a different format

[grizzled wadi]

If you're only returning a single model you just have to call .json() on the query response

[nova cove]

like on my DELETE method endpoint, it sends me back the JSON with the information of the user I deleted

but I would like to show like userDeleted: "Some message..."

!ot - can we move here since this might get off topic

[delicate field]

Off-topic channel: #ot2-the-original-pubsta

Please read our off-topic etiquette before participating in conversations.

[grizzled wadi]

@nova cove sure, there is also a community discord server you could join if you just want to talk about prisma stuff

[nova cove]

oh ok

[grizzled wadi]

You can find the link in the readme, I don't know if I'd be allowed to just post the link here

[nova cove]

ok

[grizzled wadi]

Is this the kind of thing you want? ```py
from prisma import Prisma
from prisma.models import User

class DeletedUser(User):
userDeleted: str = 'Some message...'

async def main() -> str:
prisma = Prisma()
await prisma.connect()

user = await DeletedUser.prisma().delete(
    where={
        'id': 'foo',
    },
)
if user is None:
    # handle not found
    ...

return user.json()

[nova cove]

  @Delete('delete/:id')
  @UseFilters(HttpExceptionFilter)
  async deleteUser(@Param('id') id: number): Promise<UserModel> {
    if (!id) {
      throw new UserNotFoundException();
    }

    return this.usersService.user.delete({
      where: {
        id: Number(id),
      },
    });
  }

so like, if I have this, do I just add .json() to the end of the delete query (don't mind that exception throw, that doesn't work)

[grizzled wadi]

Yes

You do have to handle the case where the record isn't found

[nova cove]

yeah I'm working on the exception

[grizzled wadi]

In that example I'm only subclassing the model to add the userDeleted attribute

[nova cove]

so would I do this?

const deletedUser = await this.usersService.user.delete({
  where: {
    id: Number(id),
  },
});

return deletedUser.json();

It should be the same in node prisma?

[grizzled wadi]

Yes

[nova cove]

Property 'json' does not exist on type 'User'

[grizzled wadi]

Oh sorry I misunderstood your question

In node prisma the returned data are raw objects, off the top of my head I can't actually remember how to dump objects to json in TypeScript

[nova cove]

oh ok

[grizzled wadi]

Looks like it should be something like this: ```ts
const deletedUser = await this.usersService.user.delete({
where: {
id: Number(id),
},
});

return JSON.stringify(deletedUser);

That's one of the main differences between the python client and the node client

The python client returns pydantic BaseModels https://pydantic-docs.helpmanual.io/usage/models/

[nova cove]

Type 'string' is not assignable to type 'User'.

might have to change my types a bit

[grizzled wadi]

yeah probably

[nova cove]

I'll do it tomorrow

I whipped up an Express + Prisma + PostgreSQL API in like 30 minutes

but my friend NestJS over here is giving me some issues

i think i actually might be able to use express Response in nest

[grizzled wadi]

I'm not surprised, Prisma gives you insane speed for prototyping

[nova cove]

https://github.com/akabinds/express-prisma-api/blob/master/src/api.ts#L8-L15

[delicate field]

src/api.ts lines 8 to 15

app.get('/users/', async(req, res) => {
    try {
        const users = await prisma.user.findMany();
        return res.json(users);
    } catch (err) {
        return res.status(500).json(err);
    }
});```

[nova cove]

thats one of my express GET requests

I'm like 99% sure I can access res in nest

because there is a @Res() decorator

[grizzled wadi]

I haven't used nest before so I can't help you in that regard unfortunately

[nova cove]

yeah its fine. I can consult the docs

[grizzled wadi]

There are custom Prisma generators, someone might've made one for nest

[nova cove]

ok i'll see

well imma head out now. but you a legend.

unfortunately, I stopped doing Python projects so I might not use Prisma Client Python in the foreseeable future, but ik it will be good if I ever use it

[grizzled wadi]

No worries, thanks for the kind words!

[nova cove]

👍

[soft gorge]

With sqlite3 If my SELECT statment is NULL / None how can i have it INSERT without doing a if else statement?

[cobalt bolt]

Well I got a rudimentary Prisma Strawberry FastAPI project working, and I think I’ll make an example repo out of it

[frigid ruin]

@grim vault @brave bridge

-- q1
-- this works if timestamp_col is the same format as 2021-02-01 12:30:08.000
select * from table_name where timestamp_col::timestamp > "2021-02-01 12:30:08.000";

-- q2
-- if timestamp_col is in the format of epoch above doesn't work instead this works:
select * from table_name where to_timestamp(timestamp_col::bigint) > "2021-02-01 12:30:08.000";

q1 doesn't work for epoch, and q2 doesn't work for "2021-02-01 12:30:08.000" this format

and i want a single query with which i can handle both these cases

sry for the ping btw

[grim vault]

maybe: https://www.db-fiddle.com/f/517jdWgBKWJey6KGe1WZqV/0

[compact marlin]

"`@client.command(aliases=["balance", "bal"])
async def show_balance(ctx):
cursor = await db commit()
USER_ID = ctx.message.author.id
USER_NAME = str(ctx.message.author)

await cursor.execute(f"SELECT user_id FROM main WHERE user_id=(USER_ID)")
result_userID = await cursor.fetchone()

if result_userID == None:
await cursor.execute("INSERT INTO main(user_name, balance, user_id) values(?, ?, ?)", (USER_NAME, START_BAL, USER_ID))
await db.commit()

await ctx.send("Hi, you're a new user and you need to register/nTo register execute the command one more time!")

else:
await cursor.execute(f"SELECT balance FROM main WHERE user_id={USER_ID}")
result_userBal = await cursor.fetchone()

await ctx.send(f"{USER_NAME}'s balance!/n/nMoney:/n$(result_userBal[0])") ` “

hi im using this code to try to use sqlite as a database

what do you guys think of it

[sly pivot]

are u guys familiar with 0-3 normalisation can you tell me if this 3 normalised

[torn sphinx]

files = ["1.csv", "2.csv", ...]

def search(search_terms):
    df = pd.read_csv(f)
    for file in files:
        # search file for column that match my input
        # append the row without loosing to much time to df
    return all_rows_that_match_my_criteria
    # how to search df for my dictionary of colum names and and values?
    # in other words how do i search

           
search({
    "year": "2018",
    "Type": "animal"})

better way of doing this?

[compact marlin]

this is my code

async def on_ready():
  client.database_connection = await aiosqlite.connect("money_data.db")
  db = client.database_connection
  
  cursor = await db.cursor()



  await cursor.execute("""CREATE TABLE IF NOT EXISTS money_data(
                       num INTEGER NOT NULL PRIMARY KEY, user_name TEXT,
balance INTEGER,                     
user_id INTEGER NOT NULL
)""")
  await db.commit()
print("Ready!")


@client.command(aliases=["balance", "bal"])
async def show_balance(ctx):
  db = client.database_connection
  cursor = await db.cursor()
  USER_ID = ctx.message.author.id
  USER_NAME = str(ctx.message.author)
  
  await cursor.execute("SELECT user_id FROM money_data WHERE user_id = ?", ('USER_ID',))
  
  result_userID = await cursor.fetchone()


  if result_userID == None:
    await cursor.execute("INSERT INTO money_data(user_name, balance, user_id)values(?,?,?)",(USER_NAME, START_BAL, USER_ID))
    await db.commit()

    await ctx.send("Hi, you're a new user and you need to register. To register execute the command one more time!")

  else:
    await cursor.execute("SELECT user_id FROM money_data WHERE user_id = ?", ('USER_ID',))
    result_userBal = await cursor.fetchone()


    await ctx.send("{USER_NAME}'s balance!/n/nMoney:/n$(result_userBal[0])")```

when i do this it doesnt work

this is what it says in the file

[delicate field]

Hey @compact marlin!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[compact marlin]

bruh i cant paste it

[delicate field]

Hey @compact marlin!

It looks like you tried to attach file type(s) that we do not allow (.pdf). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[waxen finch]

use the paste bin it linked

[compact marlin]

pastebin doesnt even work

https://paste.pythondiscord.com/vowirufibu

this isnt all of my stuff

[waxen finch]

also this might be more suitable in #discord-bots if its related to integrating your database with dpy

although how does it not work

[compact marlin]

it is but what is wrong with this is about the database and sqlite

not much the bot

i think atleast

[waxen finch]

well i would suggest, as an improvement, creating the connection in an async main function rather than on_ready since the event can fire multiple times

but regardless what is it doing that you didnt expect it to do?

[compact marlin]

its doing what its supposed to do

but then it doesnt register

which is wrong

[nova cove]

what's going on?

[compact marlin]

i need help with my sqlite database

[nova cove]

what about it?

[waxen finch]

youve quoted your USER_ID as a string and passed it to the query instead of their actual user id

[compact marlin]

hmmm

also it paste the wrong id

it said my username was selon#71822

[waxen finch]

yeah, you asked it to send your username at the end

[compact marlin]

i know but thats the wrong username

i cant have 5 numbers

[waxen finch]

not sure how it added a fifth digit

you should still fix the quoting of user id in your value substitution

[torn sphinx]

Using Sqlite rn I really dont like it should i switch to MongoDB

[waxen finch]

also a better table structure would be using the user_id as the primary key since that's what uniquely identifies their data

[waxen finch]

a good choice depends on what your data looks like, although im not too knowledged on appropriate database types

[torn sphinx]

Okay thanks

[peak lion]

which datebase is better? sqlite or mongo

[waxen finch]

i would answer with the same response as above

[peak lion]

so mongo db?

@waxen finch

[waxen finch]

afaik mongo with its documents will give you more flexibility in your data format (you arent forced into tables with limited columns), while an sql database with well designed tables reduces data redundancy

[peak lion]

Okay

Can you help me

i have some problems with it

[waxen finch]

ask away

[peak lion]

#help-cookie

[peak lion]

If you can help me

[waxen finch]

mongodb i havent actually used before

though SQL would be preferable for dpy since you'd very likely have data structured the same way for each user and guild

[peak lion]

but is it not the same (nearly)

[waxen finch]

your data you mean? how so

[peak lion]

can you try to help?

[waxen finch]

as for #help-cookie i cant suggest any concrete methods for mongo

[wintry ridge]

Hi guys, I am seeking help with a college project set by my work: essentially I need to get .job files data into an excel document using Idle if possible can someone please point me in the right direction

[trim lintel]

What is a .job file and idle ?

Never heard of this for databases

[lean cosmos]

ALTER TABLE trips
ADD CONSTRAINT fk_clienst foreign key (driver_id) references users(users_id);

error
ERROR: insert or update on table "trips" violates foreign key constraint "fk_clienst"
DETAIL: Key (driver_id)=(11) is not present in table "users".
SQL state: 23503

Help me

[torn sphinx]

Arent u supposed to have an insert or update statment

[lean cosmos]

i haven't inserted any values

[lean cosmos]

i haven't inserted any values

[grim vault]

It means there is no entry in the users table with users_id = 11 but the trips table has an entry with 11 in the driver_id column.

[lean cosmos]

SELECT t.Request_at Day, ROUND(SUM(CASE WHEN t.Status LIKE 'cancelled%' THEN 1 ELSE 0 END)/COUNT(*), 2) 'Cancellation Rate'
FROM Trips t JOIN Users u ON t.Client_Id = u.Users_Id AND u.Banned = 'No'
WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY t.Request_at;

ERROR
ERROR: syntax error at or near "'Cancellation Rate'"
LINE 2: ...1.00 else 0.00 end)/count(*) as decimal(10,2)) as 'Cancellat...
^
SQL state: 42601
Character: 133

help me

[grim vault]

Use double-quotes " for sql identifiers and single quotes ' for string literals.

[torn sphinx]

whats wrong is this

can anyone help me out

[grim vault]

No parenthesis around the table name. INSERT INTO table_name VALUES(...) or INSERT INTO table_name(colname1, colname2, ...) VALUES(...)

[grizzled wadi]

Let me know when you've made it public, I'd be love to take a look at it

[runic escarp]

Can anyone give some advice on whether or not it is wise to store large amounts of JSON data in a PostgreSQL cell. I heard it's not recommended, but this seems to be a mixed argument based on my research

Would you recommend it? Why/why not? If not, what alternative would u use?

[sharp panther]

Hi, is here someone who can help me with Pandas and excel table? Also tab completing search... Or at least with SQL and python and this tab completing search? I'll be very glad. Thanks

[teal shale]

I'm writing a discord bot to manage a Magic: the Gathering league, and I need a database to keep track of various information about the players in the league, things like their name, rating, how long they've been a member, etc. There a lots of different database libraries available, and I don't know how to tell which one is right for my project. I've looked into Pandas a little, but it's billed as more a data analysis tool than a general database management library, do you think it would work well for my project? Or perhaps I should stick with the sqlite3 in the standard library.

[proven arrow]

You should start by deciding which database to use. Then picking the library is easy.

[grim vault]

Pandas is not a database. It can work with data from a database but you still need a database. SQLite should be fine.

[nova cove]

If it’s a small bot use SQLite3. But if your bot gets bigger I would use PostgreSQL as SQLite doesn’t really scale well

[harsh pulsar]

i agree, sqlite is a good choice for something small

and i also second postgres as a recommendation for a bigger project, as it is easy to administer and has a lot of really useful features

[soft gorge]

Is it bad practice to use SQLite3 to hold onto a list of dictionaries? I think it would be much easier for me to query the lists that way instead of using a json file to add new users

[proven arrow]

,,Bad’’ depends on your data and use case. At first it would seem to be a smell of bad practice yes. Although in some cases it’s perfectly valid.

[balmy scaffold]

If there are someone with mysql -> python knowlegde i would appreciate it alot if you could check out #help-honey

[uneven stream]

if I have only one column in db, is there a way to getpy [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] instead of this?```py
[(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]

sqlite

[brave bridge]

With a list comprehension
[x for (x,) in xs]
[x for [x] in xs]

[uneven stream]

wouldn't that take longer?

[brave bridge]

?

[grim vault]

[row[0] for row in curs.execute(...).fetchall()]

[brave bridge]

yeah this is what I meant ^

or ```py
[value for [value] in curs.execute(...).fetchall()]

[uneven stream]

oh

any efficient way if I have too many rows?

[grim vault]

What's inefficient about it?

[uneven stream]

wouldn't it take time for 1k+ rows?

[brave bridge]

What would take time? extracting an element from a tuple?

that would be insignificant compared to reading them from a file

[uneven stream]

oh

thanks!

[brave bridge]

@uneven stream You shouldn't worry about these micro-optimizations too much.

First, you need to identify what volume of data your application is supposed to work with. Then you identify what the acceptable time for a complete action (such as computing the move of an NPC in a game or ordering a pizza, depends on what your ).

Then you measure the time it actually takes. If it's within the expected range - good, you win.

If it's not - you need to profile the application and find where the bottleneck is. Very often there is a bottleneck - for example, the database might be locked all the time because lots of users are writing something. In that case you need to solve the bottleneck - for example, batch the writes if it makes sense in your application (e.g. it's a chat service and you don't need strong consistency guarantees)

[gleaming herald]

any DB2 users here? i need some help lol

[uneven stream]

oh

thanks!

[trim lintel]

Afternoon people,
if i have the EAV table like below, then how to find duplicate rows where multiple keys are the same grouped by the user. I want to find basically any potential duplicates. Columns are:

id | key | value | user_id

So if a user has more than 1 same key value entries, for X number of keys then i want to get those duplicates.

[ionic pecan]

use a HAVING together with GROUP BY:

postgres=# TABLE eav;
 id | key  | value  | user_id 
----+------+--------+---------
  1 | key  | value  |    1234
  2 | key  | value2 |    1234
  3 | key  | value2 |    2334
  4 | key2 | value5 |    2334
(4 rows)

postgres=# SELECT key, user_id FROM eav GROUP BY (key, user_id) HAVING COUNT(*) > 1;
 key | user_id 
-----+---------
 key |    1234
(1 row)

also, probably think about creating a unique index to prevent this from happening if it's "wrong" from your app's point of view

[gleaming herald]

is there a way to execute a global temporary table statement in db2 as well as inserting into it within the same statement? i'm running into issues where i have to select and run the commands one by one
e.g.

declare global temporary table session.tmp (var int) replace on commit preserve rows;
insert into session.tmp values(1);
select * from session.tmp;

with replace***

[trim lintel]

The result im looking for if this was the table there should be no rows returned. Since there is no duplicate key value pair here for user 1234.

[ionic pecan]

so where multiple keys and values are the same?

[grim vault]

Just add the value column to the select and group by list.

[trim lintel]

i will give example hold on

[ionic pecan]

postgres=# INSERT INTO eav VALUES (5, 'key', 'value', 1234);
INSERT 0 1
postgres=# SELECT key, value, user_id FROM eav GROUP BY (key, user_id, value) HAVING COUNT(*) > 1;
 key | value | user_id 
-----+-------+---------
 key | value |    1234
(1 row)

[trim lintel]

Not that either, the dataset you have does not have duplicates im looking for. i will give example 2 mins

@ionic pecan where i can show example sql code?

[grim vault]

!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.pythondiscord.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.

[trim lintel]

i cant execute this in discord

ok

[grim vault]

or use https://www.db-fiddle.com/ to make an example.

[trim lintel]

ok i check that now

create table my_data (
id int,
  `key` varchar(255),
    `value` varchar(255),
    `user_id` int
);


insert into my_data values 
(1, 'name', 'Sofia', 100),
(2, 'email', '[e-mail]', 100),
(3, 'age', '20', 100),


(4, 'name', 'Sofia', 100),
(5, 'email', '[e-mail]', 100),
(6, 'age', '20', 100),


(7, 'name', 'Ekaterina', 500),
(8, 'email', '[e-mail]', 500),
(9, 'age', '20', 500)
;

@ionic pecan @grim vault

So in example above i want the result to have the user_id of 100

I want to make the match on to see if the following columns (name, email) are duplicate for each user_id

[ionic pecan]

see this query

[trim lintel]

@ionic pecan gives error, https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/0

[grim vault]

remove the parentheses in the group by list

https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/1

[trim lintel]

Hmm, but then if i chane Ekaterinas name to Sofia, for row id 7, i do not get the user_id both 100 and 500

[ionic pecan]

do you want duplicate values across users, or per user?

[grim vault]

Because the name is the same but not the user_id.

[trim lintel]

Across the whole table

but i will specify which user_id i want in the where clause

but it should consider all rows, matching the where filter

if that makes sense

[proven arrow]

It would be easier if your table was conventional and not EAV. But it is, so in this case easiest would be to make the table look like a normal relation through a query then you can query that.

It would help if you say what database you are using. I would look at group_concat for MySQL. Not sure what the equivalent is in Postgres if that’s what your using.

[trim lintel]

uhhh i dont know man, this is giving me head pain, dont know how to start with that

maybe will try later

[proven arrow]

The way I imagine this to work would be to use the group_concat function or the relevant one for your db to contact the values of the columns you are trying to find duplicate of and then you can group by that concatenated column.

[proven arrow]

It’s not super complex. If I get chance later I will try to send an example of what I mean.

[grim vault]

something like: https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/2

[trim lintel]

ok thanks let me know please if you can

[trim lintel]

hmm no because this returns id 500, but Ekaterina does not have any duplicates.

[grim vault]

the email is the same?

[trim lintel]

It is when the same values for the keys name and email appear more than once

It has to consider both name and email. Think of it like this. A user submits a form for an order. I want to see if they have submitted placed a duplicate order. To do this i must check the name and email are the same across different orders.

[ionic pecan]

ohhhhhhhh

so you want to return only users where all the keys and values are the same compared to other users?

[trim lintel]

yes 😬

[ionic pecan]

https://dba.stackexchange.com/a/186869

this seems to be the way to go

you first concat together the values by user, then you can group those

[ionic pecan]

like this

[trim lintel]

@ionic pecan thanks for the help, i will take a look at that.

[terse storm]

help

[ionic pecan]

what have you tried already?

[terse storm]

i just got this as a question

wht do u think should be out put of the code

[brave bridge]

What do you think?

[terse storm]

idk

have no idea

[brave bridge]

What part are you confused about?

Do you have any materials this question is based on?

[grim vault]

This maybe: https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/3

[terse storm]

i just randomly got this question on a text (this text is 100% optional and wont have any impact on academics) do i was curios

[trim lintel]

no not that either. Because if i create a new entry for katerina but just change her email slightly, it shows as a duplicate when it is not. I appreciate your help until now though and for this long. But i will try to stick with the group concat suggested by others, im playing around with it and getting a little closer to the end.

[terse storm]

i just started python so idk much

[trim lintel]

you got the question on a text message?

[terse storm]

sry if u find this a little weird

[terse storm]

online

classes

[trim lintel]

oh i thought someone sent you SMS with that question

[terse storm]

i just took ss

could u help me tho

[trim lintel]

just run the code in python compiler you will get the answer.

[terse storm]

okayy

[brave bridge]

Anyway, this is off-topic in the channel

[terse storm]

which channel should i go to

[kindred juniper]

Hak5

[brave bridge]

If you want to ask for resources for learning Python or have a concrete question, see #❓｜how-to-get-help and claim a help channel. Or ask in #python-discussion

[stark sparrow]

How can I add a json object in PostgreSQL using AsyncPG?

[grim vault]

my last guess: https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/5
The problem is how to group the name/email together it's not really possible with the given schema.

In the guess it will pair id 1,2; 1,5; 4,2; 4,5 together.

[trim lintel]

But with the data here, that shows as duplicates when there are none, as you can see here for user_id 600 https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/8

[grim vault]

That's becasue the same user_id has different values, which should not happen.

As I wrote above, in the given schema you cant group the name/email besides if you look at it with the empty line seperators, which SQL does not see.

[proven arrow]

In this case user_id is more of a tenants so it can for this

From his example above if you check

So it’s certainly possible

[lunar pier]

Hello, little question about FK in sqlite3. "ON DELETE CASCADE" will work only for main table from what all other tables reference or it'll delete all entries in all tables?

[proven arrow]

Is this for Postgres or MySQL ?

[grim vault]

There is no way to tell which name and email belongs together.

Except if you say the ID of the name entry must be matched with the ID+1 of the email entry.

[proven arrow]

I hate EAV when it comes to queries like this

[grim vault]

if the IDs are always one apart you can use that as the join instead of the user_id: https://www.db-fiddle.com/f/rUk7qvHEToGCynT64DFkWd/9

[trim lintel]

it can be random at times

[true swift]

Hi #databases
What is the place where a database lives?
I don't know if is host or what

[proven arrow]

@trim lintel Actually looking back at the messages, and the answers you have gotten it seems @grim vault solution should solve your question. If it doesn't work then you should show an example with some more test data and what your expected result is.

[nova cove]

SQLite is locally. Postgres, MySQL and SQL Server are on servers. You can deploy your database to cloud services

[true swift]

Nice. Thank'u

[lunar pier]

I don't understand, why syntax error here?

sqlite3

[grim vault]

unique is a keyword you should not use as column name.

[lunar pier]

oh...

thanks

[grim vault]

maybe use is_unique if you use it as boolean.

[lunar pier]

meh, i just deleted unique, no real need in it anyway :D

[rocky arch]

(SQL Alchemy for python of course)

is there a way to print the results (in this case a simple table) easier then this:

s = testtable.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
    print(row)

Do i have to do this every time I want to know the index from a table?

[empty hinge]

not if you put it in a function or class you can import

[sour ore]

How can I get the rowid and find a row using the rowid in sqlite3/asqlite?

[nova cove]

SELECT something FROM table_name WHERE id = the_id

[sour ore]

how can I get the id?

[nova cove]

SELECT id_col_name FROM table WHERE some_condition

I would learn some SQL syntax

[soft gorge]

It's for a discord bot to give servers an option to save certain questions (strings) in the bot basically

[empty hinge]

You can probably save that type of data in an easier to use way by defining multiple tables and relations between them

[soft gorge]

I have a table for just the qustion data and each row just contains the guild id and the list of questions

idk if creating a table for each one would make a difference

[stark sparrow]

Anyone?

[gritty finch]

pls run this code

from timeit import repeat
from turtle import delay
import time

print("Do you know what i love batman")
time.sleep(3)
print("Minecraft Sex!")
time.sleep(3)
print("Gone Wrong!?!")
time.sleep(20)
print(" ")
print(" ")
print(" ")
print(" ")
print(" ")

damn

[stark sparrow]

Bruh

Wrong channel

[noble bramble]

When ever I'm trying pip install psycopg2 in mac
it gives me following error

ERROR: No matching distribution found for psycopg2

https://pypi.org/project/psycopg2/

[brave bridge]

@noble bramble What is your Python version?

Try python -c 'import sys; print(sys.version)'

[frosty stone]

Hello! I have some error while trying to add values into the sqlite3 tables..

This is what the table looks like:

cursor.execute("""CREATE TABLE test (c1 real, c2 real)""")

This is what the executor looks like:

c1={};c2=[]
cursor.execute("INSERT INTO test VALUES ({}, {})".format(c1,c2))

Error:

unrecognized token: "{"

Can anyone help me, because It's giving me an error trying to add dict and lists to a real datatype

[rare fog]

c1={};c2=[]
cursor.execute("INSERT INTO test VALUES ({}, {})
can you check second parameter which is curly brackets
Should'nt it be normal brackets ? these : []

[frosty stone]

the first one should be a dictonary

[rare fog]

i think when you change ("INSERT INTO test VALUES ({}, {}) -> ("INSERT INTO test VALUES ({}, [])

Problem will be solved

[frosty stone]

thats giving me even more errors

look, the .format is to set the values and the defenition are these {}

[waxen finch]

you cant directly store python objects into sqlite3, they have to be serialized into some form

also to avoid SQL injection you should use placeholders in your query, which for sqlite would be the ? symbol: py values = ('a string column2 should equal to',) conn.execute('SELECT column1 FROM table WHERE column2 = ?', values)

[frosty stone]

@waxen finch It's giving me this error (=> near "?": syntax error) now

[waxen finch]

are you still trying to insert a dictionary and list into your table?

[frosty stone]

yes

[waxen finch]

if you really want to, you should add an adapter for lists/dicts as explained here https://docs.python.org/3/library/sqlite3.html#sqlite-and-python-types, and then python will automatically use the adapter to convert your list/dict type into something sqlite can store

though storing lists is usually not preferable over better table designs

[frosty stone]

I'm using the real datatype

[waxen finch]

then why do you want to store a list/dict into a column meant for real numbers?

[frosty stone]

because it said It's storing the data as it is given

[waxen finch]

you mean BLOB?

[frosty stone]

so if I'm saying It's a dict/list/int/.. it will store It as that type

[frosty stone]

hmm

[waxen finch]

the link above has a table showing what each python type is converted to in sqlite

[frosty stone]

I found that on some website (forgot wich one) It's probably outdated or smth

[waxen finch]

there's a particular website that mentions what you said along those lines, that being the official sqlite website: https://sqlite.org/datatype3.html

[frosty stone]

lemme try..

[waxen finch]

but it actually means the column won't try converting the type of whatever you pass to it

so integer, float, text, or bytes, blob doesn't care what it is

[frosty stone]

hmmm. so It's giving me the same error again

[waxen finch]

but its still restricted to those four types

as i said before:

you cant directly store python objects into sqlite3, they have to be serialized into some form

[frosty stone]

so does this mean I have to convert the dict?

[waxen finch]

you could use the json or pickle lib along with the adapters/converters offered by python to serialize your data as strings/bytes respectively

why do you need to store dicts/lists?

there's usually a better way to do it in sql

[frosty stone]

It's just because I'm interested in how to store whole dicts instead of single strings

[waxen finch]

hm fair enough

[frosty stone]

found something! It is possible to convert strings into dicts

[waxen finch]

using adapters and converters?

[robust current]

getting this error while connecting to mysql : 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) what do i do?

[sour ore]

I'm confused on why this does not work. Can someone help? py await c.execute("SELECT rowid from info") rowid = await c.fetchall() rowid = rowid[0] await c.execute("SELECT timeSent from info where rowid = ?", (str(rowid),)) msgtime = await c.fetchone() msgtime = msgtime[0]``````Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/discord/ext/tasks/__init__.py", line 101, in _loop await self.coro(*args, **kwargs) File "/Users/coop/Desktop/UPower/UPower_bot/main.py", line 319, in get_info msgtime = msgtime[0] TypeError: 'NoneType' object is not subscriptable

im using asqlite

[harsh pulsar]

@sour ore read the error message and look at the last line of code where the error occurs. msgtime is None, which obviously doesn't support indexing. this means that your fetchone operation did not return any rows

[sour ore]

I can see that

I’m just wondering why it didn’t get anything

[burnt turret]

fetchall would return a list of tuples, so rowid = rowid[0] makes rowid a tuple

after which you just turn it into a string (and that would be something like '(1,)' for example, with the parentheses and the comma and everything), so your query has no matches

at least that's what i'm guessing it is 🙂 if that's not it, it's likely your table just doesnt have any matching records

[frail vector]

Hi guys, does any of you know how to do pydantic database relations?

iam trying this way but its not working out

[paper flower]

How do you expect these to be serialized in json format?

You can't have circular references here

[plush orchid]

[
  {
    "id": 1,
    "farm_id": 1,
    "description": "test",
    "createdTime": "date",
    "coords": [
      {
        "long": 0,
        "lat": 0
      },
      {
        "long": 1,
        "lat": 2
      },
      {
        "long": 2,
        "lat": 2
      },
    ]
  }
]

[frail vector]

this is the json format

[paper flower]

But coordinates should have plot 🙂

And plot should have coords

[frail vector]

hm

[paper flower]

Unless you have infinite amount of memory you can't serialize that

[frail vector]

i create them like this

idk what to put here to make the connection

[paper flower]

If using REST you should create base model for Plot and Coodinates then inherit them and add relationships

e.g.

class PlotWithCoords(Plot):
    coords: list[Coordinates]

Graphql for example supports querying data with circular references

query Posts {
  getPosts {
    id
    title
    comments {
      text
      post { ... }
    }
  }
}

[frail vector]

query Plot{
  getPlots {
    id
    title
    Coordinates {
      lat
      long
    }
  }
}

i wanted something like this

i dont want a circular reeferences

[paper flower]

Just create model like this then

[frail vector]

so i delete plot from coordinates?

[paper flower]

Yep

[frail vector]

okay then i gonna try it

ty

i get this error

when i try to get all plots

[paper flower]

How did you query your data?

[frail vector]

like this

[paper flower]

Hm, i didn't use sqlalchemy core that much, why don't you use orm?

It would be possible to load related entities

e.g.

stmt = (
    select(Plot)
    .options(selectinload(Plot.coordinates))
)

[frail vector]

i saw one using orm but it wasnt async functions

[paper flower]

It is async

[frail vector]

so i ignored it

[plush orchid]

just add this to the plot model?

[paper flower]

https://docs.sqlalchemy.org/en/14/_modules/examples/asyncio/async_orm.html

[frail vector]

this one is wrong cause it isnt async right?

[plush orchid]

we create the tables like this

[paper flower]

It's not wrong but it could be async

[frail vector]

i going to try this way then

ty

[paper flower]

from __future__ import annotations

import asyncio

from sqlalchemy import Column, Integer, ForeignKey, Float, select
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import declarative_base, relationship, sessionmaker, selectinload

engine = create_async_engine("sqlite+aiosqlite:///:memory:", future=True, echo=True)
Session = sessionmaker(bind=engine, class_=AsyncSession, future=True)
Base = declarative_base()


class Plot(Base):
    __tablename__ = "plot"
    
    id = Column(Integer, primary_key=True)
    coordinates: list[PlotCoords] = relationship("PlotCoords", back_populates="plot")


class PlotCoords(Base):
    __tablename__ = "plot_coords"
    
    id = Column(Integer, primary_key=True)
    lat = Column(Float, nullable=False)
    long = Column(Float, nullable=False)
    plot_id = Column(Integer, ForeignKey("plot.id"))
    plot: Plot = relationship("Plot", back_populates="coordinates")

async def main():
    async with engine.connect() as conn:
        await conn.run_sync(Base.metadata.create_all)

    async with Session() as session:
        plot = Plot()
        plot.coordinates.append(PlotCoords(lat=0, long=0))
        plot.coordinates.append(PlotCoords(lat=42, long=42))
        session.add(plot)
        await session.commit()
        await session.refresh(plot)
        plot = await session.scalar(select(Plot).options(selectinload(Plot.coordinates)))
        print(plot, plot.coordinates)

if __name__ == '__main__':
    asyncio.run(main())

Here's a small example

Using async is a bit more challenging since lazy loading doesn't work

You could use immediate loading for some relationships though

[plush orchid]

where can we put the routes ?

[paper flower]

?

[plush orchid]

for eg this

@app.get("/plots", response_model=List[models.Plot], tags=["Plots"])

[paper flower]

Anywhere you want really

It's up to you how to structure your project

I usually have db-related modules and models in separate package and structure my project into smaller modules/apps

src/
  db/
    base.py
    models/
      user.py
      plot.py
  apps/
    plots/
      router.py
      schema.py
      services.py
    more_apps/
      ...
  app.py
tests/

app.py would contain FastAPI instance or create_app function (i prefer latter)

Then you can just run it with uvicorn

def create_app() -> FastAPI:
    app = FastAPI()
    app.include_router(plots.router)
    # Do more setup
    return app

# main.py at root level for debug purposes/to run app locally
import dotenv
import uvicorn

if __name__ == "__main__":
    dotenv.load_dotenv(".env")
    uvicorn.run("app:create_app", factory=True, reload=True)

[shrewd jetty]

I need to fix my warns it now only warns in like all servers. for example I warn someone and I will check in another server it will still do the same if you get me. This is discord.py and mongo db. What info will you guys need to help me try and fix this

[ionic pecan]

you probably want to add a guild_id field pointing to the guild where the warning was created, and when querying warnings, filter by guild id

[shrewd jetty]

Can you help me to that on mongo/discord.py

[ionic pecan]

i don't know anything about mongodb

but you probably have some field for the warning reason already, right?

[shrewd jetty]

Yeah

[subtle cove]

How to make tables in postgresql? (heroku and git)

[empty hinge]

https://www.postgresql.org/docs/9.1/sql-createtable.html heroku and git are irrelevant

[nova cove]

^ told him that

[sour ore]

But I do rowid = rowid[0]. Making it so it would just be a number

[grim vault]

!e .fetchall() returns a list of tuples:

rowid = [(1,),(2,),(3,),(4,)]  # maybe result from .fetchall()
print(rowid[0])
print(rowid[0][0])

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | (1,)
002 | 1

[sour ore]

Oh I see now

[grim vault]

And I hope that's just test code because it doesn't make much sense what you are doing.

the error shows: await c.execute("UPDATE info SET ifsent = 1 WHERE rowid = ?", (rowid,))
not the code you pasted?

and why are you converting it to a string? rowid is a number.

[sour ore]

oh wait its a different row

I though it was the same thing nevermind

[random arch]

How would I make a step by step setup thing?

like an user does a command and it makes it step by step

[north rover]

guys i need help in a part of the code

i would like to know how i can reflect the changes made in database(mysql) n python treeview using tkinter ,without having to refresh

is there any particular method or function

[violet token]

why cant i open

databse file

[valid jewel]

I want to have accounts for my website where I store some information (roughly 70 ish characters per user)
What database should I use for my website. I would also like if its possible to transfer the data to another database in the future when expanding. What database should I use?

[nova cove]

postgres

[valid jewel]

how much data could I reasonably store with postgres

oh my god I can store so much

alright thank you so much

[heady elk]

Hello I am using python flask for apache2 but 10 mysql connections would be too much? Should I change the app.route to include independant db connection every time as common practice? becuase it crashed my ubuntu with
Out of memory: Kill process 24978 (mysqld)

@app.route('/')
def index():
    cursor = db.cursor(buffered=True)
    cursor.execute('SELECT id,name,author,date FROM db')
    result=cursor.fetchone()
    cursor.close()
    return render_template('index.html',result=temp)

+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+
| Id | User            | Host            | db        | Command | Time | State                  | Info             |
+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL      | Daemon  |  114 | Waiting on empty queue | NULL             |
|  8 | root            | localhost:58296 | db        | Sleep   |   94 |                        | NULL             |
|  9 | root            | localhost:58298 | db        | Sleep   |   39 |                        | NULL             |
| 11 | root            | localhost:58302 | db        | Sleep   |   64 |                        | NULL             |
| 12 | root            | localhost:58304 | db        | Sleep   |   64 |                        | NULL             |
| 13 | root            | localhost:58306 | db        | Sleep   |    4 |                        | NULL             |
| 14 | root            | localhost:58308 | db        | Sleep   |   39 |                        | NULL             |
| 15 | root            | localhost:58310 | db        | Sleep   |   38 |                        | NULL             |
| 16 | root            | localhost:58312 | db        | Sleep   |   37 |                        | NULL             |

| 17 | root            | localhost:58314 | db        | Sleep   |    4 |                        | NULL             |
| 18 | root            | localhost:58316 | db        | Sleep   |    4 |                        | NULL             |
| 19 | ubuntu          | localhost       | NULL      | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+```

[ionic pecan]

how big is that table?

[heady elk]

db is 2.02 MB, also the ubuntu is t3.micro so only 1GiB ram

[torn sphinx]

what is the best way to save to database dict

[nova cove]

a dict isn't a database

[torn sphinx]

i mean dictionary into a database

[abstract rapids]

Which do you guys recomend more? Sqlite3 module or sqlAlchimy?

[nova cove]

they are different

sqlite is an rdbms, sqlalchemy is an ORM

[ionic pecan]

hmmm okay that is very, very strange, I really don't think an OOM for that single query should be a thing. what is buffered=True? i've never seen that before & can't find it in mysqlclient either. are you running any other queries meanwhile, or does that query on its own cause the out of memory?

[nova cove]

you use sqlalchemy along with a rdbms

[ionic pecan]

also regarding connection scaling, mysql uses threads for connetions, so 10 connections is absolutely no problem

[ionic pecan]

json / jsonb column types

[torn sphinx]

okay thanks

[grim vault]

look harder ;) : https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorbuffered.html

Doesn't seems to be a good idea to use a buffered cursor if you execute a select without where and then only fetch one row.

[ionic pecan]

ahhhhh, thank youu

I looked at the mysqlclient docs, i think this is a differeent one

[heady elk]

No other queries, the apache2 are only services access mysql

[onyx burrow]

Is there a way I can access the 'cur' and 'conn' variable outside the class?

[nova cove]

dynamically creating classes

not good

[harsh pulsar]

"yes", but this is such a convoluted XY question that it's pretty difficult to say more

[nova cove]

1. you don't need commas between all the instance variables.
2. initialize the class if yopu want to access stuff i guess

[harsh pulsar]

1. you are creating a class inside a try/except block? seems weird

2. what do you mean by "outside the class"? you might have some fundamental misunderstandings about what classes are and how they work in python

3. this seems only tangentially related to #databases . you might want to ask a more general question ("how do i do X? here is my current code, but i am struggling to make it work") in a help channel #❓｜how-to-get-help

[sour ore]

can I store a list in a sqlite3 table?

[harsh pulsar]

not "natively", but there are a few options:

1. use a delimited-text format, like comma-separated
2. use the json1 extension to store json arrays (or store json as plain text, but you can't query into the array without the json extension)
3. change your data model

however the question is why you want to store a list

[sour ore]

list of discord channels for a server.

[sour ore]

I was thinking of doing it with , in between

if I did do , in between could I format that into a list? Take it out of the table and turn it into a list. Would that work?

[harsh pulsar]

you can, but this sounds like something you will want to be able to query in the database

you might want to read about database normalization

[empty hinge]

You could do this better using two tables and foreign key relations between them

Aka “database normalization” as @harsh pulsar said

[uncut flint]

how do pydantic models compare to database models? do you need a database to use/store pydantic models or are they their own "database"

[uncut flint]

nvm figured it out

[torn sphinx]

are there any resources on how to utilize schemas when working with django

[grand wren]

Is it possible to check if a pragma key exists when using SQLCipher?

[pure mortar]

google's bigquery: "just denormalize everything"

/s

[harsh pulsar]

do they discourage joins?

analytics workloads are a different thing

[harsh pulsar]

you might be interested in the SQLModel package which uses Pydantic to build an ORM

!pypi sqlmodel

[delicate field]

sqlmodel v0.0.6

SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness.

[uncut flint]

ill check it out. thanks 😄

[pure mortar]

essentially and yeah.

its a columnar database

[harsh pulsar]

aha

[pure mortar]

i just found it funny that they said that phrase so often back when bigquery was a thing

but dif tools, dif use cases

[harsh pulsar]

are columnar databases bad at joins? i never really thought about it

[pure mortar]

theyre just not optimized for it, at least according to the data engineering podcast i listen to

there are many traditional queries you shouldnt run on columnar databases

otherwise it will be expensive

[harsh pulsar]

makes sense i guess. you can't easily slice off rows and pull in an arbitrary number of columns

[pure mortar]

technically, you can still do it but its expensive is all 💸

also

[harsh pulsar]

right. and that seems like what a lot of join queries intend to do

basically columnar databases are more like dataframes

[pure mortar]

apparently theres saying theres a new third type of database built specifically for real-time analytics

[harsh pulsar]

is there a name for it

i mean there are streaming databases, idk what the underlying data model is but maybe thats already a third kind

[pure mortar]

idr but its what https://rockset.com/ uses

[harsh pulsar]

and of course key-value is a separate thing

Unlike traditional OLAP databases that use columnar stores, Rockset indexes every field in your structured, semi-structured, geo or time series data. Power low latency, high concurrency modern data apps at a fraction of the cost, simply using SQL.

idk what this means

[pure mortar]

they combined the good parts of transactional databases + good parts of scalable ones

[harsh pulsar]

All fields, including deeply nested fields, are automatically indexed in a Converged Index™ which includes an inverted index, columnar index and row index. A Converged Index™ compiles indexes of information and allows analytical queries on large datasets to return in milliseconds.

it looks like they actually just index the living shit out of every field to cover a lot of common olap query use cases

how is that not super slow to insert data

[pure mortar]

yeah thats what their "secret sauce" is

the way the guy talked about it on the podcast was it was essentially two databases architected into one

[harsh pulsar]

https://rockset.com/blog/converged-indexing-the-secret-sauce-behind-rocksets-fast-queries/ this is the actual technical explanation

[pure mortar]

he said there havent been any innovation in the database architecture space since forever ago

back when storage was expensive and dbs were trying to optimize for it

now storage is cheap due to cloud

and compute is expensive

[harsh pulsar]

i see, they literally make 3 copies of the data, and use a different kind of index tree that is optimized for faster writes

[pure mortar]

so they redid the database architecture to account for that; he also said you cant employ this type of db on-prem

it doesnt work bc it is so tied to the cloud

[harsh pulsar]

is this database architecture? it's just throwing compute and disk space at the problem

not that its not clever

[pure mortar]

idk if it counts as db architecture tbh, but it def works for real-time analytics

[harsh pulsar]

so you have 3 copies of the data, each with one write-optimized index that you can update in parallel

[pure mortar]

thanks for finding the whitepaper, im bookmarking it

[harsh pulsar]

yeah i guess if i needed super super fast queries and money was no object this would make sense

$33/mo isnt even bad for 100 gb of data

presuming you dont have that much data but you just need ridiculously fast reads and dont want to pay a db admin

[pure mortar]

it is super duper fast

like you could build real-time analytics data apps on this type of fast

if thats what you need

[harsh pulsar]

so is this company profitable or are they just funneling vc money directly into heating data centers

[pure mortar]

theyre actually one of the profitable ones

and theyre one of the few that have a rockstar team apparently

[harsh pulsar]

Rockset is built by experts with decades of experience in web-scale data management and distributed systems. The team comprises engineers who founded Hadoop Distributed File System (HDFS) at Yahoo, scaled the online data platform at Facebook and created RocksDB, the open source embedded storage engine used by CockroachDB, Kafka, Flink and many modern databases today.

i guess if they created hdfs and rocksdb i trust them

[pure mortar]

yeah theres a lot of brain power there

especially in the db space lol

at the very least itll be interesting to see

[harsh pulsar]

might be something to keep bookmarked for a future job

free tier is smart

[pure mortar]

very true

i can imagine real-time analytics becoming more popular in the future as data proliferates

ah the use-case they provided in the podcast had to do with logistics and supply chain

seems important there

[harsh pulsar]

its going to be more popular in industries that already push a lot of data

and i guess theres an iot use case

i dont think the companies that are first developing data strategies will have any use for this

[pure mortar]

this is an interesting feature

https://rockset.com/docs/query-lambdas/

Working with raw SQL is dangerous and tedious enough as-is, especially when embedding it into application code. Avoid SQL injection security risks and potential schema exposure by saving your queries and turning them into REST endpoints. Once a Query Lambda is created, a REST endpoint is automatically generated which you can then query directly with your query parameters as HTTP parameters. Collaborating on SQL queries is now as easy as sharing a link, while your application code remains unchanged.

[jagged flame]

Ok so I'm running sqlite for a discord bot on a pi0

Ofc the pi0 has a micro sd card for storage

I'm worried about database rw eating the cycles on the card and leading to a closer death

Is that a reasonable concern and if so how can I make sure my db is using the fewest disk cycles possible?

[dull dust]

This is not a python related question but when we install SQL Server 2019, and we decide to download the media in the iso format, what exactly is happening?

[zealous rain]

Hi, I have json output problem.
Whenever I'm making a curl

--header 'X-API-KEY: ai'
(bert_db) trener2@nlpv2dev:~/bert_trainer$ curl --location --request POST 'http://127.0.0.1:5000/api/labels' \cation --request POST 'http://127.0.0.1:5000/api/labels' \
--header 'X-API-KEY: ai' \
--header 'Content-Type: application/json' \
--data-raw '{
    "name" : "test"
}'
{"message": "Internal Server Error"}
(bert_db) trener2@nlpv2dev:~/bert_trainer$ curl --location --request GET 'http://127.0.0.1:5000/api/labels' --header 'X-API-KEY: ai'
{"message": "Internal Server Error"}
(bert_db) trener2@nlpv2dev:~/bert_trainer$ 

I have this output problem, but for example POST is correct

 label_id | label_name 
----------+------------
        1 | test

This is my code:

    @api.expect(resource_fields)
    @token_required
    def post(self):
        
        if not request.is_json:
            return handle_404_error(json.dumps({'message' : 'The request payload is not in JSON format'}))

        if not request.method == 'POST':
            return handle_404_error(json.dumps({'message' : 'Something went wrong, check if  method is POST'}))

        data = request.get_json()
        
        if not "name" in data:
            return handle_400_error(json.dumps({'message' : 'Wrong json input'}))

        new_label = Labels(label_name = data['name'])
        label_check = db.session.query(Labels.label_name).filter(Labels.label_name==data['name']).first()

        if label_check != None:
            return handle_409_error(json.dumps({'message' : 'This Label already exists'}))

        db.session.add(new_label)
        db.session.commit()
        data = db.session.query(Labels.label_name, Labels.label_id).filter(Labels.label_name==data['name'])
        output = {"new label":[]}
        for item in data:
            output["new label"].append(item)
        result = jsonify(output)
        return make_response(result, 201)```

This is an error:

  File "/home/trener2/bert_trainer/app/routes.py", line 65, in decorator
    return f( *args, **kwargs)
  File "/home/trener2/bert_trainer/app/routes.py", line 98, in get
    result = jsonify(output)
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/site-packages/flask/json/__init__.py", line 370, in jsonify
    dumps(data, indent=indent, separators=separators) + "\n",
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/site-packages/flask/json/__init__.py", line 211, in dumps
    rv = _json.dumps(obj, **kwargs)
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/json/__init__.py", line 234, in dumps
    return cls(
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/site-packages/flask/json/__init__.py", line 100, in default
    return _json.JSONEncoder.default(self, o)
  File "/home/trener2/miniconda3/envs/bert_db/lib/python3.9/json/encoder.py", line 179, in default
    raise TypeError(f'Object of type {o.__class__.__name__} '
TypeError: Object of type Row is not JSON serializable
2022-03-14 10:38:40,428 INFO sqlalchemy.engine.Engine ROLLBACK

locally this is fine, but on the server it isn't

also locally I have ubuntu, server has debian

[grim vault]

Maybe different Flask / Flask-SQLAlchemy versions installed?

Or are you using different databases locally and server side?

[zealous rain]

postgres locally and at the serve

r

[torn sphinx]

Can anyone who is good with pandas and dataframes pm me

[ionic pecan]

you‘re adding the raw sqlalchemy Row objects to „output“, you need to turn them into lists or tuples first

[zealous rain]

Isn't it already in tuple?

[empty hinge]

considering the error is saying the object in question is type Row, looks like no, it is not already a tuple

[worn flame]

Hello (SQLAlchemy, FastAPI)
I am trying to update a row, i don't get any errors and the row doesn't update.
can someone take a quick look?

from sqlalchemy import update
from sqlalchemy.orm import Session
from .model_msc import CompanyDB as DBMSC

def update_row(db: Session, msc: CompanyBase):
    apply_changes = update(DBMSC)\
        .where(DBMSC.id == msc.id)\
        .values(name=msc.name, msc_id=msc.msc_id, api=msc.api)\
        .execution_options(synchronize_session="fetch")
    result = db.execute(apply_changes)
    return result

https://docs.sqlalchemy.org/en/14/orm/session_basics.html#orm-expression-update-delete

edit: I tried .values(dict(name=msc.name, msc_id=msc.msc_id, api=msc.api))\
which made no changes.
print(apply_changes) variable returns:

UPDATE companies SET msc_id=:msc_id, name=:name, api=:api WHERE companies.id = :id_1

[zealous rain]

Ok it's working, but columns name are missing

    "labels": [
        [
            "string",
            4
        ],
        [
            "strsdsdsdng",
            5
        ],```

Any idea why?

[empty hinge]

most likely you need to use a different database cursor type or query execution function

most database libraries have different options that you can use for cursors - some return tuples, some namedtupes, some dictionaries, some return custom objects

[harsh pulsar]

do you need to commit a transaction? or does sqlalchemy work in "autocommit" mode by default?

[harsh pulsar]

show your code

!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.pythondiscord.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.

[zealous rain]

I have figured it out already, thanks. I did it like this:

        output = {"new label":[]}
        for item in data:
            d_tmp = {"label_id": item[1], "label_name": item[0]}
            output["new label"].append(d_tmp)
        result = jsonify(output)
        return make_response(result, 201)```

similiar in GET

[worn flame]

I have just resolved it. i tried the syntax of:
db.query(DBMSC)
.filter(DBMSC.id == msc.id)
.update({"name": msc.name, "msc_id": msc.msc_id, "api": msc.api}, synchronize_session="fetch")
db.commit()

not sure why this worked and the previous didnt.

[harsh pulsar]

maybe it's a matter of filter vs where... might have to check the sqlalchemy docs

[worn flame]

nope its versions: (scroll down a little for the v1 and v2 examples)
https://docs.sqlalchemy.org/en/14/orm/session_basics.html#update-and-delete-with-arbitrary-where-clause

The SQLAlchemy I am working on is of version 1.x
SQLAlchemy==1.4.27 as said in the the requirements.txt file.

[harsh pulsar]

ah, noted

[jolly heron]

https://paste.pythondiscord.com/imiduforon someone help trying to split image into 100 vertical sections and save each section as separate image but image saved at end is same image as inputted

[spice trout]

how do i use json data from different file with python

[empty hinge]

!d json.loads

[delicate field]

json.loads

json.loads(s, *, cls=None, object_hook=None, parse_float=None, parse_int=None, parse_constant=None, object_pairs_hook=None, **kw)```
Deserialize *s* (a [`str`](https://docs.python.org/3/library/stdtypes.html#str "str"), [`bytes`](https://docs.python.org/3/library/stdtypes.html#bytes "bytes") or [`bytearray`](https://docs.python.org/3/library/stdtypes.html#bytearray "bytearray") instance containing a JSON document) to a Python object using this [conversion table](https://docs.python.org/3/library/json.html#json-to-py-table).

The other arguments have the same meaning as in [`load()`](https://docs.python.org/3/library/json.html#json.load "json.load").

If the data being deserialized is not a valid JSON document, a [`JSONDecodeError`](https://docs.python.org/3/library/json.html#json.JSONDecodeError "json.JSONDecodeError") will be raised.

Changed in version 3.6: *s* can now be of type [`bytes`](https://docs.python.org/3/library/stdtypes.html#bytes "bytes") or [`bytearray`](https://docs.python.org/3/library/stdtypes.html#bytearray "bytearray"). The input encoding should be UTF-8, UTF-16 or UTF-32.

Changed in version 3.9: The keyword argument *encoding* has been removed.

[blissful ether]

Hello, does anyone know how I can read XML files in python?

[torn sphinx]

    @commands.command(
        name="test",
        description="Setup the review system.",
    )
    async def test(self, ctx: Context, member: disnake.Member, *, rating : int = 0):
        async with aiosqlite.connect("ratings.db") as db:
            async with db.cursor() as cursor:
                await cursor.execute("SELECT userId FROM users WHERE guildId = ?", (ctx.guild.id,))
                data = await cursor.fetchone()
                if data:
                    await cursor.execute("UPDATE users SET userId = ? WHERE guild = ?", (member.id, ctx.guild.id))
                else:
                    await cursor.execute("INSERT INTO users (guildId, userId, ratingStars) VALUES (?, ?, ?)", (ctx.guild.id, member.id, rating))
            await db.commit()

This is my command, but nothing shows up in the database with no errors

help

[grim vault]

                await cursor.execute("SELECT userId FROM users WHERE guildId = ?", (ctx.guild.id,))
                data = await cursor.fetchone()```
This could be any user of the guild, I don't think this is what you want.

```py
                    await cursor.execute("UPDATE users SET userId = ? WHERE guild = ?", (member.id, ctx.guild.id))```
Different columnname? `guildId` != `guild`
Also this will set the userId of ALL entries of that guild to the one given (`member.id`). Again, I don't think this is what you want.

[torn sphinx]

Im confused...

So basically what i want is like Each user can have a different rating on each server

like this

So how would i search for the user, then for the guild and then take the rating so i can display it

[grim vault]

You would just use both columns in the where clause with an AND:

..."SELECT rating FROM users WHERE guildId = ? AND userId = ?", (ctx.guild.id, member.id))
...
..."UPDATE users SET ratingStars = ? WHERE guildId = ? AND userId = ?", (rating, ctx.guild.id, member.id))
...

[tawdry kelp]

I have a doubt

In sql

||create table tab1(
Id serial primary key,
Name varchar (200) not null

);

Create table tab2(
IDs serial primary key
Foreign key (id) references tab1(id)
);||

Is that right

||create table tab1(
Id serial primary key,
Name varchar (200) not null

);

Create table tab2(
IDs serial primary key
Foreign key (mail) references tab1(mail)
);||

[tawdry kelp]

Is this also correct?

@grim vault

[grim vault]

Please don't ping random people, if you are not in a conversation already.
As for your SQL:

• the column of a foreign key must exists in the table and the referenced column must also exists and be unique (this could be a primary key or a unique index).
  so, eg Foreign key (mail) references tab1(mail) means that the table tab2 must have a column named mail and the referenced table tab1 also must have a column named mail which must be unique within that table.

[tawdry kelp]

This is a table

In customer table there is no column names of customer_payment_method and order_status_code

MY friend said the table created

[grim vault]

How does the customers table look like?

[tawdry kelp]

Customer id customer name customer email and other customers details