databases

[paper flower]

Why can't you scale with sql?

[keen minnow]

what makes you think relational does not scale?

[spiral mason]

It does but it's pain

[keen minnow]

not really

[jade wing]

i disagree with that statement, sql/relational databases scales just fine with the right database and cluster setup

[paper flower]

1 write and 1 read node is enough for majority of applications i'd say

[keen minnow]

even a single node can go pretty far. Most people are overestimating "scale"

[paper flower]

Yep

[jade wing]

or you might just use it for high availability

[keen minnow]

but I have had no problem taking a SQL db all the way to 500 million users or 6 digit op/sec

[jade wing]

yeah, mostly one node with another just for ha

[paper flower]

Replicas? 🙂

[jade wing]

that is mongodb's own marketing material

[paper flower]

NoSQL scales better than sql obviously but nosql is limited in querying

[keen minnow]

• https://shardingsphere.apache.org/
• https://vitess.io/ (doesn't seem happy tonight somehow)

[spiral mason]

My argument is not SQL is not scalable but if you know you gonna up scale your project why not use mongo which is better at it by default and doesn't need to do tools or put efforts in scaling it.

[paper flower]

Why not use both?

Some data is better to store in sql, some in nosql

[keen minnow]

my point is mongodb is in no way better at anything

Also on a related note, there is this great book which goes into the theory of distributed DBs: https://cs.uwaterloo.ca/~ddbook/

[paper flower]

I think it's arguably better if you need to store large amounts of data / unstructured data

[jade wing]

or binary blobs like files

[keen minnow]

we did try it at large scale (500 millions users) and it did not work out

[jade wing]

gridfs used by mongodb is quite nice for that

[paper flower]

I was about to write that i wouldn't store anything really important in it 😅

[spiral mason]

I agree both have their pros and cons but mongoDB is way better then sql

[keen minnow]

which was our conclusion as well, and thus ended up replacing it with DBs we could trust and rely on

[spiral mason]

Why not store data in Excel sheet

[paper flower]

come on

How else am i going to take down database with my monstrosity of a query?

It is a relational db, like sqlite

[jade wing]

we also have bad experiences with mongodb and reliability when it comes to the data in it

[paper flower]

I mean, sentry for example stores events data in postgresql, we accumulated ~100GB of events in 1-2 months, they're not really important and could be stored in NoSQL database

And we don't have much traffic either, so 100GB isn't that much

[jade wing]

much more so than sqlite is, much more relational features you can rely on

[keen minnow]

that's the point though. Why lower the capabilities if postgres handles it fine without a sweat?

there must be some benefit to use X

[paper flower]

Really because it would be easier to scale NoSQL database here 🤔, though i don't really have any experience with that

I'm not saying to use NoSQL everywhere, but for that event data

[keen minnow]

it ends up being pretty close. You have shards of data and replicas

If we were talking hbase/cassandra or even couchdb, then sure. They do have their use case.
But mongodb is in this weird space where they are not only proprietary but their got their lunch eaten by postgres/mysql

[paper flower]

Yeah, sharding should help 🤔

You shard individual tables, right?

[keen minnow]

even before sharding, partitionning would go a long way

[keen minnow]

you could

[paper flower]

It's mainly a problem with database taking too much space on current node where we host sentry

[keen minnow]

Also, I haven't kept up too much with the specifics of postgres, but they do have a postgres specific way of linking multiple DBs together. That may also be of interest to you

[jade wing]

yeah, we use quite a bit of portioning for tables with a lot of records in them

[paper flower]

self-hosted sentry is a big docker-compose file that i don't really want to get into right now, so periodically cleaning old data is enough currently 😅

And other projects aren't big enough to setup replicas or anything else

[keen minnow]

fair enough 😉

[paper flower]

Anything wrong with running postgresql in docker if it's pinned to a node?

[jade wing]

it's astonishing how many people are doing premature optimization in their software projects 😬

[keen minnow]

as always: it depends
It depends on how you insert the data, query the data and the trade of in terms of reliability/availability/retention

[paper flower]

We do periodic backups, in terms of availability i'd need a read-write replica

[keen minnow]

In terms of space, 100Gb isn't much nowadays.
But I would investigate partitioning (ex: by date)

[paper flower]

  database:
    image: postgres:14
    networks: [stack]
    deploy:
      mode: replicated
      replicas: 1
      resources:
        reservations:
          cpus: "0.25"
          memory: 512M
      placement:
        constraints:
          - node.labels.some-application.postgres == true
    volumes:
      - postgres-data:/var/lib/postgresql/data

It's pinned to a certain node, so there shouldn't be any problems really

[paper flower]

Again, don't want to dive into sentry db, if it works it works

[keen minnow]

the CPU may be worth increasing or at least comparing to the configured pools

otherwise, nothing special to mention

[paper flower]

You mean the 0.25?

[keen minnow]

yeah

[paper flower]

It's a reservation, not a limit

It doesn't see much traffic, so 0.25 should be fine

[keen minnow]

sure

[paper flower]

Anything worth mentioning from postgresql.conf?

[keen minnow]

not on the top of my mind

[neat parrot]

Hello, I would like to know how to apply my model "Plane" to controller ? using sqlalchemy, flask
My project request data from an api to insert to database. MVC

Thanks you in advance

planeController.py

from flask import jsonify, request
from flask_sqlalchemy import SQLAlchemy
from models.plane import Plane
from utils.connect import Database
import logging as log

db = SQLAlchemy()

def store():
    db = Database.connect()
    cursor = db.cursor()
    req = request.json["response"]
    key = []
    for i in req:
        try:
            if (key==[]) :
                plane = "INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
                key.append(i["reg_number"])
            else:
                if(i["reg_number"] not in key) :
                    plane+= ", ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
                    key.append(i["reg_number"])
          
        except Exception as e:
             print("failed")

    cursor.execute(plane)
    
    db.commit()
    cursor.close()
    return jsonify(req)

[paper flower]

This looks like sql injection to me 🙂

[spiral mason]

Idk your answer but you should use f string for sure

[jade wing]

you should use placeholders/bind variables instead of formating in the values into the sql string like that, otherwise you open your self up to sql injection attacks

[jade wing]

no, neither use .format() or f-strings or any other type of formatting for that matter, unless you want to be vulnerable to sql injection attacks

[spiral mason]

Yeah i remember we should use prepared statments

[quick fiber]

I'm new to databases

[neat parrot]

???

[paper flower]

You're using string formatting with sql, it's prone to sql injection

I can do all sorts of stuff with your database

[neat parrot]

How can I fixe this ?

and how can I apply my model to my controller ?

Im not sure about the role of sqlalchemy

Thi is my model

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Plane(db.Model):

    __tablename__ = 'plane'
   
    reg_number = db.Column(db.String, primary_key=True)
    aircraft_icao = db.Column(db.String(120))


    @property
    def serialize(self):
        return {
            'reg_number': self.reg_number,
            'aircraft_icao': self.aircraft_icao,
        }

[paper flower]

1. I wouldn't use flask_sqlalchemy wrapper but sqlalchemy itself

You can query models directly:

stmt = select(Plane)
planes = session.scalars(stmt).all()

It would give you list of Plane objects

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html

It could be run on the same server, it's overall better since you can use multiple application instances with same database server

And it's generally more performant than sqlite

However if connection between two servers is slow you'd experience a delay, it's expected

That could be somewhat solved using replicas

Yep, primary server could sync data with it's replicas

[neat parrot]

@paper flowerShould I use sqlite3 ?

[paper flower]

It depends

I'd use postgres, it's not that hard to setup

[neat parrot]

mysql azure

Its better ? @paper flower

from flask import jsonify, request, session
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import select
from models.plane import Plane
from utils.connect import Database
import logging as log
import sqlite3

db = SQLAlchemy()

stmt = select(Plane)
planes = session.scalars(stmt).all()

def store():
    db = Database.sqlite3.connect()
    cursor = db.cursor()
    req = request.json["response"]
    for i in req:
        try:

            info = (i["aircraft_icao"], i["reg_number"])
            cursor.executemany("INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('?', '?')", info)
            db.commit()
            cursor.close()
        except Exception as e:
             print("failed")

   
  
    return jsonify(req)

[jade wing]

i would say that you don't need replicas for most setups, instead host the database somewhere "close" (latency wise) to the application server if not on the application server it self to begin with

[paper flower]

You didn't change anything 🙂

I showed you how you can use sqlalchemy without writing raw sql as you did

There's decent tutorial in fastapi documentation on how to use it:
https://fastapi.tiangolo.com/tutorial/sql-databases/

[jade wing]

some things did change:

plane = "INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
...
plane+= ", ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
...
cursor.execute(plane)

is now:

info = (i["aircraft_icao"], i["reg_number"])
cursor.executemany("INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('?', '?')", info)

so i'd still call it a big improvement, even if there is more one can do 😉

[paper flower]

I think if you host your app in multiple datacenters you'd have to use replication

[paper flower]

I mean, it's still using cursors, raw sql and my piece of code was simply added on top without any second thought 🌚

[neat parrot]

I had not understood what was the role of sqlalchemy now yes thanks

[paper flower]

You don't have to write raw sql with sqlalchemy, it's an ORM

Meaning you can create python objects, add them to database and query them later

[neat parrot]

so sqlite3 is useless in this case ?

[paper flower]

You can use different databases with sqlalchemy

Sqlalchemy is just a toolkit/orm to work with sql easier

[neat parrot]

I just want to use on azure mysql database

[raw saffron]

https://youtu.be/b2F-DItXtZs

[paper flower]

from sqlalchemy import Column, Integer, select, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine("sqlite://", echo=False)
Session = sessionmaker(bind=engine)
Base = declarative_base()


class Plane(Base):
    __tablename__ = "plane"

    reg_number = Column(String, primary_key=True)
    aircraft_icao = Column(Integer, nullable=False)


def main():
    with engine.begin() as conn:
        Base.metadata.create_all(conn)
        conn.commit()

    plane = Plane(reg_number="1234", aircraft_icao="Not sure what goes here.")
    with Session() as session:
        session.add(plane)
        session.commit()
        session.refresh(plane)

    stmt = select(Plane)
    with Session() as session:
        print(session.scalars(stmt).all())


if __name__ == "__main__":
    main()

[paper flower]

You can use it with mysql

[paper flower]

@neat parrot You can use this snippet to experiment with sqlalchemy ^

[neat parrot]

Whats mean this line ?
plane = Plane(reg_number="1234", aircraft_icao="Not sure what goes here.")

[paper flower]

You can also pass echo=True to engine to see what sql it sends to the database

[paper flower]

I'm creating a model

and then inserting it into database

[neat parrot]

its this before ?

@property
    def serialize(self):
        return {
            'reg_number': self.reg_number,
            'aircraft_icao': self.aircraft_icao,
        }

[paper flower]

You don't really need that

[neat parrot]

I scrapp data from an api to insert my database

import requests
import json
class ScraperSQL:
    def __init__(self):
        self.headers = {'Content-Type': 'application/json'}
        self.urlConnectStr = "http://localhost:5000/create"
        self.req = requests.get('https://airlabs.co/api/v9/flights?api_key=7-9fa2-41ac-82dd-6&_fields=reg_number,aircraft_icao')

    def response_s(self):
        response = requests.post(self.urlConnectStr, json = json.loads(self.req.content))
        


execute = ScraperSQL()
execute.response_s()

[paper flower]

You can use something like pydantic to serialize and validate your data, it's widely used in FastAPI framework

[neat parrot]

Could I use this with flask ?

[paper flower]

Yes

I'd recommend fastapi though

They're quite similar

[neat parrot]

In my crud I can erase the Insert and put what ?

def store():
    db = Database.connect()
    req = request.json["response"]

    for i in req:
        try:
        
          
        except Exception as e:
             print("failed")

  
    return jsonify(req)

[paper flower]

class PlaneCreate(BaseModel):
    reg_number: str
    aircraft_icao: str


def create_planes(
    planes: list[PlaneCreate],
    session: Session = Depends(get_session),
):
    for plane in planes:
        session.add(
            Plane(
                reg_number=plane.reg_number,
                aircraft_icao=plan.aircraft_icao,
            )
        )
    session.commit()

[neat parrot]

@paper flower pydantic work with mysql ?

[jade wing]

pydantic has nothing to do with databases specifically, so yes

[neat parrot]

okay

@paper flowerwith my crud how could I request data from external api ?

Before I put this

req = request.json["response"]

Doctor ?

[paper flower]

From external api? You can just send http request to it using library you prefer

requests, httpx, aiohttp

[neat parrot]

yes

I scrapp some data from airlabs api to insert to database

import requests
import json
class ScraperSQL:
    def __init__(self):
        self.headers = {'Content-Type': 'application/json'}
        self.urlConnectStr = "http://localhost:5000/create"
        self.req = requests.get('https://airlabs.co/api/v9/flights?api_key=7-9fa2-41ac-82dd-6&_fields=reg_number,aircraft_icao')

    def response_s(self):
        response = requests.post(self.urlConnectStr, json = json.loads(self.req.content))
        


execute = ScraperSQL()
execute.response_s()

@paper flowerso using httpx, its from controller or from model ?

[paper flower]

I'm not sure what you mean

[neat parrot]

@paper flower for this :

import httpx

r = httpx.get('https://www.example.org/')
r

I use httpx from the controller or from the model?

[paper flower]

I wouldn't put anything to your model besides some properties

[neat parrot]

so from controller ? or just external like I dId with scrapper?

[paper flower]

from your controller or from a separate service (scrapper in your case) is fine

[neat parrot]

ok thx for your help

[fluid lava]

I want to append records to another table from a temporary table if the record does not exist.

There is no unique index in the temporary table. How would I do this?

I tried "on conflict, do nothing" but it seems I need an index. How could I achieve this?

[grim vault]

You need a UNIQUE constraint/index or a PRIMARY KEY on the table you append to not the temp one.

[cerulean ledge]

How can I select and sort a table by its integer? For example 5,4,3,2,1. In sql

[frozen charm]

Im runnig a postgresql DB and i do 2 Primary Key Lookups (tortoise-orm) it usualy takes around 0.1 seconds.
But sometimes it randomly takes around 5 seconds.
I never had this issue while developing only in Produktion.

The table only has around 200 rows and as i said its a primary key lookup.
Does anyone know why this can happen ?
Both db and python script are on the same server.

[tacit cloud]

probably need to start by isolating whether the query is slow on the server or over the network or in python. I'd start by turning on slow query logging on the server @frozen charm

[frozen charm]

Okay thank you

[paper flower]

Maybe there's an issue with connection limit at your python process so it waits for new connection to be available?

I mostly use sqlalchemy and i've never had such problems

[frozen charm]

I have a connection pool of 10 connections and my app is used by less then 100 users and the db is not frequently queryd.
But since it only happends in production it must have to do with that the App ist used by more users.

I will write a test to test db connwction and timings also turn on logging as raylu said.

But still this is all pretty weird

[keen minnow]

Adding monitoring to the connection pool could also be handy for general o11y and monitoring in production

[torpid rover]

what do I have to change to make my mysql server allow connections from other computers? like I need to connect to it with the ip and so on

I have already phpmyadmin and so on set up, and can connect on localhost

but not on other servers

[hexed pollen]

what kind of python code that i can use to read the data in my database (m using SQLlite)

[oak oyster]

I have a table products which I can query with: ```sql
SELECT id, (quantity * unit_value) as total_value
FROM products

to get a result similar to this:

id | total_value |
-----+-------------+
1 | 250
4 | 300
4 | 150
1 | 50

My question is, how can I group by id? If I simply put a group by id in the orignal query I get an error that the calculated column `total_value` should be in the group by
The results should look like:

id | total_value |
-----+-------------+
1 | 300
4 | 450

[iron hull]

HIALL

Is the server running on host "xxxxxxx.amazonaws.com" (52.45.152.158) and accepting
TCP/IP connections on port 5432

does someon know why this error is ?

[keen minnow]

missing an aggregation over the values? Like how to deal with total_value if you have multiple entries for the product with id = 4?

[keen minnow]

Which error?

[jade wing]

looks like your code can't access the postgres database on the provided address in aws
is your code running in aws too?

[humble hamlet]

Silly question: how do I continuously check to see if a db table has had an insert or a delete done on it (I'm using the Django framework)?

[jade wing]

you can't continuously check other then to query the table over and over
well, there is also triggers with callbacks, but that's a pretty advanced topic which has some requirements
also, far from all databases has support for them
so, what database and version are you using and do you have full control over the database server as well?

[humble hamlet]

I'm using Django and sqlite

So this is my form, when the form is submitted, a new scheduled_item is entered into the db. Then printed below.

The way it's supposed to work is that

1. The program checks to see if there are any programs to be recorded today. (not implemented)
2. Filter on programs that need to be recorded today, sort by start time. (also not implemented, but this part is pretty easy)
3. Then, at the appropriate time, say 11:00 AM, the program on the backend will begin recording the radio program (this part is implemented)

[humble hamlet]

What about just checking it on Insert/Delete?

Yeah, I think I could make this work by only checking on Insert/Delete and at Midnight every day

[keen minnow]

You are trying to implement a task scheduler.
Rather than re-implementing it yourself, I would recommend to use an off the shelf library. Unfortunately, I am not that familiar with the whole python ecosystem. So others may have specific libraries in mind

[vast rock]

When you group by, other columns have to be aggregates. Try SELECT id, SUM(quantity * unit_value) as total_value FROM products FROM products GROUP BY id

[torn sphinx]

Hello I'm about to insert data into a database table. For simplicity, I reduce the content of the sql table so it would have the following structure:

CREATE TABLE users(
   user_id int8 PRIMARY KEY,
   user_name varchar(100) NOT NULL,
   insertion_time timestamp NOT NULL
);

I wonder if I should provide the timestamp within python code or let postgres handle that automatically so that it sets the current timestamp when I insert a new entry

[formal mica]

how do i link sql and html?

[tacit cloud]

@torn sphinx as long as your servers have ntp (ntpd, chrony), it shouldn't matter much. either way is fine

[hardy atlas]

hello

I am finding sqlalchemy (postgres)+tweepy example or talent can help me

[austere portal]

Durango has a thing called signals, it allows you to set a function which will be called before or after a row has been inserted/deleted

https://docs.djangoproject.com/en/4.0/topics/signals/

[tacit cloud]

(but you probably want a queue or something instead)

[mighty glen]

i have a MySQL table with four columns- a blob, two identifier columns, and a bit for compression. How can i select UNCOMPRESS() if the compression bit is 1, or just select if it's 0?

[pure mortar]

data warehousing is neat

[weak orbit]

what dummy database models are good to use for a demo

[tropic plank]

#discord-bots

[lean walrus]

Hello, are we able to somehow connect an sqlite3 to a io.StringIO or io.BytesIO instance (perhaps using sqlite3.connect())?

[brave bridge]

Is there a nice way to make a column non-nullable in PostgreSQL and replace nulls with a given default value? Or do I just need to replace the nulls first and then add the constraint?

[paper flower]

Yep, you'd have to first fill all the values and then add the constraint

e.g.

update some_table set some_column = now;
alter ...

[sharp lion]

createoptionaccounts = f"INSERT INTO {prefix}options (option_id, option_name, option_value, autoload) VALUES (NULL, 'shibboleth_create_accounts', '', 'yes');"
mycursor.execute()

My Problem is i don't wanna have an error is there a possible way for sth like "insert into if not exists" idk how to say. sth like a check

i work with mariadb

[mighty glen]

le bump

[vagrant burrow]

yo!
Can anyone recommend a good up-to-date sqlalchemy tutorial?
I have been trying to follow the official one but I have no idea what the creator is rambling about. I'm literally 20 mins in and he might as well speak Chinese to me, it'd be the same

[sage kettle]

you guys know how to use mysql

[frigid radish]

Is there any good guide for using/creating SQL in vscode?

[sage kettle]

I am unable to create a new connection...it says "unable to connect to ###"

[torn sphinx]

you can use SQL course of sololearn

it's free

and it covers.. at least the basic part

required to acquire the understanding of how SQL works

[frigid radish]

My issue isn't with sql itself

I don't know how to create a database in vscode

[torn sphinx]

..

wdym by 'database in vscode'

what r u using fo SQL?

[frigid radish]

There's an sql extension for vscode

And I don't know how to use it

[chilly canyon]

I'm new to databases, I have a simple postgre one set up and running this

SELECT sum(amount)
FROM db
WHERE member = "test#1234"

column "test#1234" does not exist

I'm trying to search for that member in the column labeled member.. I'm sure it's simple, but I can't get this to work

[paper flower]

Use single quotes for strings in postgres

[chilly canyon]

Wow... just.. 😆

Good to know to try little things like that, I never would've tried that

[paper flower]

Double quotes are for identifiers, for example your table name might have a dash in it: "my-table"

[chilly canyon]

Great info, ty

[storm mauve]

you should store data based on the user ID though, not the username

[chilly canyon]

That is the plan, I'm new & just doing a personal project. Just found last night you can convert from username to id & back, so will get that going at some point

Right now I'm just getting all the functions for crud with the db then I'll start to integrate it into discord. Just easier for me to test that functions make sense when I can specify user names for now

[tacit cloud]

@chilly canyon username + discriminators change (mine just changed because I got nitro). user IDs don't
IDs get sent with every payload. you can also turn on developer mode in the UI to get "copy ID"

[chilly canyon]

This is going to be used by people very afraid of coding & bots, so part of this is to work on UX & make it as user friendly as possible. I'll be diving into the discord library & trying to do all that conversion behind the scenes, but my first thought was to keep it simple (even getting people to turn on developer mode will probably be a challenge)

Good to know though, will definitely make the conversion to id and back to user names a priority

[storm mauve]

most discord related libraries will have ways to automatically convert the commands inputs into User / Member objects, which you can take the ID out of whenever they used the username, a mention or something else to input
(that is made even clearer|easier with slash commands)

[tacit cloud]

@chilly canyon yeah, of course your code converts the @mention (which you see as @<1234> anyway) into the ID behind the scenes)

[chilly canyon]

I haven't gotten that far yet, but good to know, ty

[delicate frigate]

MySQLdb._exceptions.OperationalError: (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.')```

[primal notch]

Is it a good idea to have a separate database migrator service, or I should include it into one of the bigger services, for example my backend API?

[delicate frigate]

Depends on what the goal is

What if your application for?

[keen minnow]

Postgres supports SQL, but it does not use it as it's just a language

Do you have a specific meaning behind the term use ?

[keen minnow]

Which parts confuse you specifically and how?

They are definitely related!
SQL is the language used to query, insert or modify the data.

[wicked kiln]

what does the LC_COLLATE and LC_CTYPE bit do?

and the connection limit set to -1...

Im spoiled by django orm

[rare valve]

    @commands.command()
    @commands.is_owner()
    async def joins(self, ctx, guild_id: int):
        #connect to the database
        async with aiosqlite.connect('Database.db') as db:
            #take every row in the database and put it in a list
            rows = await db.execute("SELECT * FROM users")
            data = await rows.fetchall()

        for i in data:
            print(i)

            r = api.refresh_token(i[1])
            print(r)
            access_token = r["access_token"]
            refresh_token = r["refresh_token"]
            print(refresh_token)
            print(access_token)

            api.add_to_guild(access_token,i[2])

            #connect to the database
            db = await aiosqlite.connect('Database.db')
            #update the access_token and refresh_token
            await db.execute("UPDATE users SET access_token = ? AND refresh_token = refresh_token WHERE UserID = {i[2]}", (access_token,refresh_token, i[2]))
            await db.commit()
            await db.close()

            for i in data:
                print(i)
        return None

the db is not getting updated

[grim vault]

UPDATE users
   SET access_token = ? AND refresh_token = refresh_token
--                      ^^^ wrong           ^^^^^^ this is like 1 = 1
 WHERE UserID = {i[2]}
                ^^^ remains from an f-string?

Your SQL statement should look like:
UPDATE users SET access_token = ?, refresh_token = ? WHERE UserID = ?

And why is there the second for i in data: inside the first one?

[primal notch]

It's gonna be a fancy-built discord bot

[exotic mirage]

    @commands.group()
    @commands.has_permissions(manage_guild=True)
    async def set(self, ctx):
        """*Set requirements for commands*"""
        if ctx.invoked_subcommand is None:
            await ctx.send_help(ctx.command)
            
    @set.command()
    async def afkrole(self, ctx, role: nextcord.Role = None):
        """Set access role for afk command"""
        if role is None:
            await ctx.reply('Enter a valid role id and try again.', mention_author=False)
        async with self.bot.afkdb.cursor() as cursor:
            await cursor.execute('SELECT role FROM afkrole WHERE guild = ?', (ctx.guild.id,))
            data = await cursor.fetchone()
            if data:
                if data[0] == role:
                    return await ctx.reply('That role is already set as requirement for afk command!', mention_author=False)
                    await cursor.execute('UPDATE afkrole SET role = ? WHERE guild = ?', (role, ctx.guild.id,))
            else:
                await cursor.execute('INSERT INTO afkrole (guild, role) VALUES (?, ?)', (ctx.guild.id , role,))
                await ctx.reply(embed=nextcord.Embed(description=f'Successfully set requirement role for afk command to `{role.name}`.', color=0x2F3136), mention_author=False)
        await self.bot.afkdb.commit()

Traceback (most recent call last):
  File "C:\Users\Akai\AppData\Local\Programs\Python\Python38\lib\site-packages\nextcord\ext\commands\bot.py", line 1048, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Akai\AppData\Local\Programs\Python\Python38\lib\site-packages\nextcord\ext\commands\core.py", line 1487, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "C:\Users\Akai\AppData\Local\Programs\Python\Python38\lib\site-packages\nextcord\ext\commands\core.py", line 933, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Akai\AppData\Local\Programs\Python\Python38\lib\site-packages\nextcord\ext\commands\core.py", line 177, in wrapped
    raise CommandInvokeError(exc) from exc
nextcord.ext.commands.errors.CommandInvokeError: Command raised an exception: InterfaceError: Error binding parameter 1 - probably unsupported type. 

[grim vault]

My guess would be you need role.id as parameter in the INSERT and UPDATE binding argument. It also looks like your UPDATE statement has the wrong indention (same as the return, which means it will never be executed).

The if data[0] == role: my also need the role.id

[rare valve]

The data:
('FH5JyHTu7zb1ZW7xtssuPK4QwP6w0c', 'sqcxKnJq6ITzm9PalUhaBUm0P22WY1', 840315621073223703)

What we want to change i[0] to
YxdIIcfVLgYmgOM5sqVMk1mWqRPHyn

the data after the change:
('FH5JyHTu7zb1ZW7xtssuPK4QwP6w0c', 'sqcxKnJq6ITzm9PalUhaBUm0P22WY1', 840315621073223703)

The code

        
async with aiosqlite.connect('Database.db') as db:
            #take every row in the database and put it in a list
            rows = await db.execute("SELECT * FROM users")
            data = await rows.fetchall()

        for i in data:
            print(i)

            r = api.refresh_token(i[1])
            print(r)
            access_token_ = r["access_token"]
            refresh_token_ = r["refresh_token"]
            print(refresh_token_)
            print(access_token_)

            api.add_to_guild(access_token_,i[2])

            #connect to the database
            db = await aiosqlite.connect('Database.db')
            #update the access_token and refresh_token
            await db.execute("UPDATE users SET access_token = ?, refresh_token = ? WHERE UserID = ?", (access_token_, refresh_token_,i[2]))
            await db.commit()
            await db.close()

[hearty solstice]

hi

[torn sphinx]

is this true?

I don't know how to use them this way

[whole ivy]

py3test

[prisma jolt]

Hey guys, does any one have experience with working with python+oracle that could give me some guidance? I am new in that area and have been playing with adding/querying data from oracle db. Ugly code works, but I would like to create proper validations before adding data to the db, possibly using validations like we have with sqlmodel and validator decorator to properly create and validate objects creation and making sure I don´t add bad data to the db. Any help or guidance with books, links, videos is appreciated. I have been searching for this material and couldn´t find much really.

[rapid ginkgo]

How to display the rank of each user?

[torn sphinx]

how can i add lists in pymongo

            qy = { "guild_id": ctx.guild.id }
            new = { "$set": { "blacklist": [word] } }
            collection.update_one(qy, new)

this is my code so far it works, but when i already have a value it replaces it with the new one
how can i add multiple values?

[storm mauve]

you'll want to $append $push instead of setting
https://www.mongodb.com/docs/manual/reference/operator/update/push/

[torn sphinx]

thank you so much

[torn sphinx]

can postgresql reject a query due to the size of a query? I've not found anything to be configurable, but I think there's a hardcoded limit of aprox. 1gb

[unkempt prism]

This error at import indicates you have not installed it. Perhaps a different virtual environment / python version etc

[untold spear]

What's the problem here?

import discord
from discord.ext import commands 
import asyncio
import datetime
import sys
import sqlite3

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

@bot.event
async def on_ready():
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS main(
        guild_id TEXT,
        msg TEXT,
        channel_id TEXT
        )
        ''')
    print("bot running")

@bot.command()
async def test(ctx):
    await ctx.send("test")

bot.run("token")

i has db sqlite installed

Im using vsc

also the bot doesn't get online

the code should create a main.sqlite file

[runic basalt]

im trying to use pymysql as a database system, but it connects to a db server. how to i create a db server?

[unkempt prism]

You need to install mysql.

[runic basalt]

how do i create the server

[paper flower]

You have a mysql server running? It's a separate program basically

[vernal pulsar]

Hi all, has someone experience with MongoDBs? I want to get the distinct files of a collection but also the number how often the are in the database:

{
"destinct_fields": [
{"name":"field1", "count": 5},
{"name":"field2", "count": 1}
{"name":"field3", "count": 10}
]
}

Is this possible with only one call against the database?

[runic basalt]

basically a host?

[limber minnow]

anyone has an idea how to create a docker container that runs a RDBMS

https://haseebmajid.dev/blog/simple-app-flask-sqlalchemy-and-docker there is a link for instructions but the last bash command not working for me no idea why 😦

[exotic mirage]

    @commands.Cog.listener()
    async def on_message(self, message):
        if "owo" in message.content:
            async with self.bot.owodb.cursor() as cursor:
                await cursor.execute('SELECT times FROM owotimes WHERE user = ?', (message.author.id,))
                data = await cursor.fetchone()
                if data:
                    await cursor.execute('UPDATE owotimes SET times = times+1 WHERE user = ?', (message.author.id,))
                else:
                    await cursor.execute('INSERT INTO owotimes (user, times) VALUES (?, ?)', (message.author.id , 1,))
            await self.bot.owodb.commit()

await cursor.execute('UPDATE owotimes SET times = times+1 WHERE user = ?', (message.author.id,))

how to make it add 1 to no. of times

oh nvm it worked

[jade wing]

you might find an answer to that in here (a little further down the page): https://www.cybertec-postgresql.com/en/postgresql-on-wsl2-for-windows-install-and-setup/

[median marten]

import disnake
import os
import sys
import json
import random
from pymongo import MongoClient
import pymongo
from disnake.ext import commands
import motor.motor_asyncio
import nest_asyncio


nest_asyncio.apply()


class test(commands.Cog):

  def __init__(self, bot):
    self.bot = bot
    self.connection_string = os.environ['CONNECTION_STRING']
    self.cluster = motor.motor_asyncio.AsyncIOMotorClient(self.connection_string)
    self.collection_name = self.cluster['Config_DB']



  @commands.Cog.listener()
  async def on_guild_join(self, guild):


    guild_info = {
      "_id" : f"{len(self.bot.guilds)}",
      "guild_name" : f"{guild.name}",
      "guild_id" : f"{guild.id}",
      "guild_prefix" : ">",
      "guild_no_command_channel" : [None],
      "guild_settings" : {"test":None}
    }

    await self.collection_name.insert_one(guild_info)

  async def get_guild_prefix(self, guild_id):

    cursor = await self.collection_name.find({"guild_id":f"{guild_id}"})
    for i in cursor:
      return i['guild_prefix']

  async def get_guild_blacklisted_channel(self, guild_id):

    cursor = await self.collection_name.find({"guild_id": f"{guild_id}"})
    for i in cursor:
      return i["guild_no_command_channel"]

  async def get_guild_settings(self, guild_id):

    cursor = await self.collection_name.find({"guild_id": f"{guild_id}"})
    for i in cursor:
      return i['guild_settings']
    
    

  @commands.command()
  async def db_test(self, ctx):

    cursor = await self.collection_name.find({"_id": "2"})

    for i in cursor:
      await ctx.send(i)

    await ctx.send(self.get_guild_prefix(944320976806617089))
    await ctx.send(self.get_guild_blacklisted_channel(944320976806617089))
    await ctx.send(self.get_guild_settings(944320976806617089))
    

    
def setup(bot):
  bot.add_cog(test(bot)) 

Traceback (most recent call last):
  File "/home/runner/disnake-testing-playground/venv/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 169, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/runner/disnake-testing-playground/cogs/tester.py", line 79, in db_test
    cursor = await self.collection_name.find({"_id": "2"})
  File "/home/runner/disnake-testing-playground/venv/lib/python3.8/site-packages/motor/core.py", line 771, in __call__
    raise TypeError(
TypeError: MotorCollection object is not callable. If you meant to call the 'find' method on a MotorCollection object it is failing because no such method exists.

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

Traceback (most recent call last):
  File "/home/runner/disnake-testing-playground/venv/lib/python3.8/site-packages/disnake/ext/commands/bot_base.py", line 570, in invoke
    await ctx.command.invoke(ctx)
  File "/home/runner/disnake-testing-playground/venv/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 920, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/home/runner/disnake-testing-playground/venv/lib/python3.8/site-packages/disnake/ext/commands/core.py", line 178, in wrapped
    raise CommandInvokeError(exc) from exc
disnake.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: MotorCollection object is not callable. If you meant to call the 'find' method on a MotorCollection object it is failing because no such method exists.

can anyone help me?

[prisma jolt]

Hey guys, what's your recommendation for python Oracle db insert. I have a cvs file that I will be adding as data. Single add/commit, grouped and single commit? Just looking from a best practice point of view for proper error handling too. Thanks in advance

[torn sphinx]

Hey all, I have a problem for which I have not yet been able to find a solution. My goal is to test a trading strategy I came across. The strategy works with 2 timeframes. 15 min and 1 hour. My problem is that I don't know how to combine these 2 timeframes. I have searched the internet and found some solutions, but none of them work for me. If someone has an idea, I would be very interested. You can write to me directly or here.

[keen minnow]

It's not clear what is stopping you. Can you expand a bit and describe your schema and queries?

[torn sphinx]

I do not know how to connect the two time frames. My 1 day time frame from the previous day gives me a number 1, 0 or -1 when the 15 min time frame can analyze to go long, do nothing or short. Now, how do I tell my 15-min time frame that it can analyze the stock?

[keen minnow]

that's the job of your code or query to fetch the data and compute whatever you need out of it. that depends very much of the specifics of your schema and queries

But there is nothing fundamental against query and joining or contrasting different data from different time frames

[jade wing]

when loading huge amounts of data as fast as possible into oracle one usually turns to a oracle tool called sql loader

[pliant spire]

from sqlalchemy.orm import declarative_base
from sqlalchemy import String

BASE = declarative_base()


class Animal(BASE):
    name = Column(String(255), primary_key=True)

    def __repr__(self) -> str:
        return '\n' +'\n'.join((f'{key:30s}:  {val}' for key, val in vars(self).items()
                          if not key.startswith('_'))) + '\n'


class Dog(Animal):
    breed = Column(String(255), nullable=False)

Hi fellas, wondering how to inherit functions with sqlalchemy declarative_base. Everytime I try to inherit like this, I get a foreign key exception.

[pure mortar]

neat stuff

[coral briar]

Can anyone tell me how to implement the built in plugin supports for PyQtWebEngine, with the Pepper Plugin API, how would you make a plugin, how would you add them to your PyQt Web Browser and how would you make an extensions GUI button for removing and disabling plugins? I found some documentation, I how this can help some of you guys to at least make it a bit easier to help me: https://doc.qt.io/qt-5/qtwebengine-features.html#pepper-plugin-api

[brittle bolt]

With motor you need to first connect to the client which corresponds to your line starting with self.cluster, then the database which corresponds to your line self.collection_name and then connect to a collection within the db. Which is what you are missing. Then db.collection.find will work.

    self.cluster = motor.motor_asyncio.AsyncIOMotorClient(self.connection_string)
    self.db_name = self.cluster['Config_DB']
    self.collection_name = self.db_name["my_collection"]

# skip ahead.
    cursor = await self.collection_name.find({"_id": "2"})

[keen minnow]

The choice of a technology is dependent on a lot of criteria that are not necessarily purely technical. While the properties of SQLite vs PostgreSQL do matter, there are also impacts due to what technology the people involved are familiar with, how easy it is to support/deploy/upgrade/maintain, how easy is it to get support for it or troubleshoot issues and even sometimes what is popular.
That said, one of the advantage of postgresql over sqlite is that postgresql is a networked service while sqlite works over files. This makes it easier to work with backend services. But the fact sqlite works over files makes it super easy in small applications that only need access to the local filesystem

I haven't used myelf sqlalchemy in a while, but they do support postgres (https://www.sqlalchemy.org/features.html). So why do you think it would be an either or choice?

[median marten]

I'm doing self.db_name = self.cluster[collectionname] this is not equal to do self.db_name[collectionname]?

anyway I'll try this, thx

[frozen charm]

I noticed that my psql database is doing a seq scan even if i do a primary key lookup like
select * from users where id=<user_id>; id is a primary key

the table is only 300 rows big so at the moment its not a big of a deal but i still wondering why the planer uses a seq scan since there is an index for the pkey.
Aswell when the table becomes bigger seq will be slow.
Does anyone know why the planer executes a seq scan?

[spiral mason]

What would be better to use with python

[torn sphinx]

which database/SQL dialect you mean ? I like mySQL personally, but most people prefer SQLite
so those two are both good options you can look into

[spiral mason]

What would you suggest to me who is beginner suck at everything

[torn sphinx]

mySQL definitely

[spiral mason]

Okay thanks

[wild flare]

Hi i'm using python with SQL
Trying to make it so when i do select * from my table it looks something like this:
With freq+url being a child from word
Word | freq| URL

---

Word | 20 | URL
| 15 | URL
Word2 | 3 | URL

Currently struggling with the formatting (particularly child/foreign key)ig if anyone can help * o *

[brittle bolt]

In one Mongodb instance you can have multiple databases. Thus you need call the database you wish to use, then call the collection from that db.

[meager hatch]

I don't understand what do you want do. Can you show your tables?

[median marten]

thx

[karmic gulch]

hii

[past sundial]

so i just learned this week that there is acutally a prisma client for python

would definitely recommend it 👍

[untold spear]

what's the difference from INTEGER and TEXT in sql?

[torn sphinx]

which SQL dialect ?

[untold spear]

db

for a discord bot

[torn sphinx]

I mean like mySQL, SQLite, T-SQL, postgreSQL etc.

[untold spear]

oh

Sqlite

[sage kettle]

need help ```import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class Main {

public static void main(String[] args) {
    try {
        Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306//sakila", "root", "password");
        Statement statement = connect.createStatement();
        ResultSet result = statement.executeQuery("select * from  actor");

        while (result.next()) {
            System.out.println(result.getString("actor_id"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}
I keep getting an error where it saysjava.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306//sakila
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
at Main.main(Main.java:10)``` Coding in java btw

[mint mauve]

I'm currently using SQLite and I want switch to something a little bit more serious/better, what would you suggest I should use?

[paper flower]

postgresql

[mint mauve]

ayyy its you again!!

[torn sphinx]

how can i find all fields that have the same value in pymongo?

[torn sphinx]

INTEGER holds whole numbers (integers), so 1, 2, 45 etc.
TEXT holds text strings ("a", "f", "3", "ace ventura") with an encoding https://www.sqlite.org/datatype3.html

[mint mauve]

help??

[worn plank]

hello, the solutions on the internet did not help do you know the solution and why

[mint mauve]

where?

[cinder token]

Hi guys! I was thinking about using JSON (and files) as a database for my next (very small) project, so that also non programmers can help easily.

So, I was trying to develop a sort-of orm, and I ended up with the following snippet of code (Python):

class JsonSerializer:

    @staticmethod
    def map_object_to_dictionary(object):
        output = vars(object)

        for key, value in output.items():
            if not JsonSerializer.is_jsonable(value):
                output[key] = JsonSerializer.map_object_to_dictionary(value)

        return output

    @staticmethod
    def map_dictionary_to_object(type_of_output, dictionary):
        output = type_of_output()
        update_dict = vars(output)

        for key, value in update_dict.items():
            if not JsonSerializer.is_jsonable(value):
                update_dict[key] = JsonSerializer.map_dictionary_to_object(type(value), dictionary[key])
            else:
                update_dict[key] = dictionary[key]

        output.__dict__.update(update_dict)
        return output

    @staticmethod
    def is_jsonable(x):
        try:
            json.dumps(x)
            return True
        except (TypeError, OverflowError):
            return False

This seems to be enough to store and reload any custom object... it seems too simple to me, am I missing something? How could this code go horribly wrong?

(basically I would map every custom object into a dictionary, that I can the store with json in a file and retrieve to rebuild the object when I need it)

[paper flower]

Basically json files aren't that reliable

[cinder token]

what do you mean exactly by not reliable? I might not be able to load them at some point for mysterious reasons?

Thanks for the answer!

[paper flower]

It's easy to corrupt your file if a program shutdowns unexpectedly

And RDBMS usually offer more features, more reliable and could be used by multiple processes

I mean, json would do as a storage for small project, but if you want to use it for more than some sort of configuration i'd use "real" database

[cinder token]

mmm, I will give it a thought; I need the files to be human readable.. but I haven't thought about the multiprocessing issues, thank you!

[acoustic thunder]

hey, how can i set a value to a specific row in psql? I tried somthing like

await conn.execute('''INSERT INTO foo(foo) VALUES($1) WHERE id = $2''', 2, 3343453)``` but that gave a syntax error for `WHERE`

[jade wing]

you would use something like await conn.execute('update foo_table set foo_column = ? where id = ?', (2, 3343453))

[jade wing]

a requirement of having the raw data storage for any kind of database being directly human readable is quite a limiting factor and introduces other problems instead

[brittle bolt]

Can you be more specific as to what you want to achieve. Do you want to group or count all the fields with the same value? Eg:

collection = [
    {name: "john", age: 50},
    {name: "jane", age: 25},
    {name: "juaqim", age: 5},
    {name: "jannet", age: 15},
    {name: "john", age: 35},
    {name: "jane", age: 15},
    {name: "john", age: 35},
    {name: "jeff", age: 50}
]
#return
names = [
    {_id: "john", count :3},
    {_id: "jane", count :2},
    {_id: "jannet", count :1},
    {_id: "juaqim", count :1},
    {_id: "jeff", count :1}
]

[pure mortar]

alternative version of those sql venn diagrams

i think i like it more tbh

[paper flower]

stop 👀

[pure mortar]

you mean yes

[worn plank]

Pls someone help me 🥲

[torn sphinx]

Well we have no information to help you besides logs that give no information.

So you won't get any help if you don't provide more information.

Just as a side note, I believe PostgreSQL uses the port 5432 by default.
That's a random guess based on the very little to no information you've given

[mint mauve]

I'm typing but it shows nothing

[jade wing]

it shouldn't show the password and sometimes password prompts doesn't even show anything else in it's place either for increased security
you just have to type it in blind and hope you got the password right, otherwise try again and see if you mistyped on previous tries

[mint mauve]

but I don't know what the password is

[jade wing]

where is the database installed, is it locally on your computer or on your server or hosted somewhere else?

[mint mauve]

I didn't ask me to set the password in the setup

[mint mauve]

localhost

[jade wing]

what os are you using on the host running the database?

[mint mauve]

windows 11

[jade wing]

from what i remember from installing postgres on a windows 10 computer a while back it required me to set the password during the installation wizard

[mint mauve]

I tried reinstalling it few times but it never asked me to set password

[jade wing]

i have not tried reinstalling it on windows for a long long time as i'm usually not on windows
so i don't know if it just tries to reuse what was set the first time or how that would behave

[mint mauve]

It asked me now

I tried uninstalling it and when it get uninstall it says bla bla files did not get deleted, and I deleted them manually and installed it again and it work

asked me to set a password

@jade wing Do you have any tool to view/edit databases?

[unkempt prism]

Its quite common to set localhost to trust as per https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

see

# TYPE  DATABASE        USER            IP-ADDRESS      IP-MASK             METHOD
host    all             all             127.0.0.1       255.255.255.255     trust

This should work if you are using IPv4 from the same computer using TCP. Otherwise ::1/128 for IPv6 I believe

[torn sphinx]

@graceful widget don't bump your question in other channels please

[graceful widget]

? ok

[mild goblet]

what is database

[sweet garden]

beluga

[high agate]

Is SQLite free?

[sweet garden]

Yes

[jade wing]

it depends on your definition of free, but mostly yes, and if you mean that you don't need to pay for it, it's yes again
https://www.sqlite.org/copyright.html
https://www.sqlite.org/purchase/license?

[high agate]

epic

[mint mauve]

why autoincrement=True don't work on sqlite? python

[paper flower]

How it doesn't work? Share your code and errors you're getting

[mint mauve]

I'm not getting an error, Its just not generating anything

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    follower_id = db.Column(db.Integer, unique=True, autoincrement=True) # HERE
    email = db.Column(db.String(150), unique=True)
    username = db.Column(db.String(150), unique=True)
    password = db.Column(db.String(150))
    first_name = db.Column(db.String(150))

[mint mauve]

I tried googling it for a long time now, I didn't find a lot of helpful stuff

[paper flower]

I'm not sure why it doesn't work in sqlite, also why do you want an autoincrement there?

[mint mauve]

to generate a unique number for every user

[paper flower]

id is already unique

Why do you need a follower_id?

[mint mauve]

I want to have two relationship with this model with another model and I can't have two with id

[paper flower]

Why not?

[mint mauve]

shows error

[paper flower]

What error?

[mint mauve]

nvm, its working

thanks anyway, last time I tried it it showed me an error

that I can't have two relationship with same column twice

[mint mauve]

got it here

again

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(150), unique=True)
    username = db.Column(db.String(150), unique=True)
    password = db.Column(db.String(150))
    first_name = db.Column(db.String(150))
    
    followers = db.relationship('Follow', backref='user', passive_deletes=True)
    following = db.relationship('Follow', backref='follower', passive_deletes=True)

[paper flower]

https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#handling-multiple-join-paths

[mint mauve]

thanks

[autumn moat]

Hey I need some help with sqlite3 with my discord bot. My table has the column Wallet, and i want it to send the Wallet number for the specified user id

"SELECT User_Id, Wallet FROM Database WHERE User_Id = 704839281838915675"

I have this for selecting what I want, i just dont know how to make the bot say the Wallet value

and sometimes the bot would just send the content in the image below, but now i just dont know how i can do anything

[dim bronze]

try this:
SELECT Wallet FROM Database WHERE User_Id = '704839281838915675'

[jade wing]

you don't need to select the user_id when you are using that in the where statement and already know the user_id
other than that, use placeholders/bind variables for the user_id in your query in your code

[zenith cove]

if I have a simple database table with two columns, full_urls and short_urls, should the short url be the pk?

[harsh pulsar]

the primary key should uniquely identify each row. does the short url always uniquely identify each row? if so, then it can be (and arguably should be) the primary key. if not, then it should not be the primary key.

[rigid yacht]

hello guys good morning

[graceful widget]

Good morning

Lol

[idle cedar]

is there a lightweight ORM that works with postgres db?

[paper flower]

Depends on what you mean by "lightweight"

[idle cedar]

well i have a small discord bot and i thought its about time i integrate a database

its about 4k lines long

[paper flower]

I mostly use sqlalchemy, wouldn't call it lightweight but it has a lot of features

[idle cedar]

i dont want the whole app to become very slow

[paper flower]

Most orms are pretty fast

[idle cedar]

yeah sqlalchemy is the only popular orm for python

aside from django db

[paper flower]

django orm is far worse than sqlalchemy 😅

[idle cedar]

i liked Prisma but its for js only :(

[paper flower]

There was a python client iirc

[idle cedar]

i like how the worst language gets the best stuff

[paper flower]

https://prisma-client-py.readthedocs.io/en/stable/

[idle cedar]

no way

[paper flower]

I personally would use sqlalchemy, it's pretty flexible

[harsh pulsar]

node isn't that bad. it's the node/javascript library ecosystem that is bad. so many overcomplicated underdocumented frameworks.

[paper flower]

Overcomplicated? Welcome to rxjs, we have map, concatMap, mergeMap, exhaustMap, mapTo, switchMap

[grizzled wadi]

If you want to try out the Python client I'd be more than happy to help :)

[harsh pulsar]

heh. to be fair, toolz has a few functions like this as well. it's the pain of not being able to "deforest" array operations.

[paper flower]

https://github.com/ReactiveX/RxPY

[harsh pulsar]

nice

[paper flower]

It's mainly about data streams, not about arrays, though, you can think of them this way

[harsh pulsar]

makes sense. you also don't have lazy/infinite "lists" in python either, and the performance characteristics of generator-based "streams" are really different from regular lists

[idle cedar]

you wont be happy when i whelm you with a million questions

ill try it out soon then, glad it exists for python

[high agate]

?

[jade wing]

you'll need to provide a bit of code as well

[high agate]

nvm i just figured it out sorry

i did role instead of role.id

@jade wing Can u maybe help with this

Im trying to get the id of a role, i got it as a channel as shown below

I just dont know how to get the other column value on the db

[jade wing]

first, don't interpolate variables in the sql

always use placeholders/bind variables instead

[high agate]

can you explain a bit more what u mean

[jade wing]

first use the following to post the last function of your code from the screenshot as code

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

it will make it much easier for us to help you

[high agate]

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[high agate]

!code db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}") result = cursor.fetchone() channel = bot.get_channel(id=int(result[0]))

@jade wing

This is the current value i get from the code at the top of the screenshot..

[jade wing]

cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")

should be:

cursor.execute("SELECT channel_id FROM main WHERE guild_id = ?", (ctx.guild.id,))

no f-string or format or anything like that

so that your code doesn't get "hacked" using sql injection attacks, that second version will protect you from that

[high agate]

Oh alr

That gives me an error now

let me look a sec

[jade wing]

and to get more columns you just add them to the query like:

cursor.execute("SELECT channel_id, channel_name FROM main WHERE guild_id = ?", (ctx.guild.id,))

or what ever you other column name you have

[storm mauve]

you may need to use (id,) instead of (id) for it to actually be a tuple

[high agate]

where?

[jade wing]

right, my bad

just add at least one comma in within the parentheses after your variable since you only have one variable in there

[jade wing]

i have updated my example above now

[high agate]

Alr

thanks

so this now looks decent?

!code db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute("SELECT channel_id FROM main WHERE guild_id = ?", (ctx.guild.id,)) result = cursor.fetchone() channel = bot.get_channel(id=int(result[0])) em = discord.Embed(color=0xf3e914, title="Configuration :wrench: ", description=f"**Setup channel**\n{channel.mention}\n") em.set_thumbnail(url=f"{ctx.bot.user.avatar_url}") em.set_footer(icon_url=f"{ctx.guild.icon_url}", text=f"Guild ID: {ctx.guild.id}") await ctx.send(embed=em)

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

yeah, you should go over your code and change all your sql statements in a similar fashion

and you don't need to type "!code", that was just to make the bot show you how to input code in the chat instead of screenshots

[high agate]

Alright

may i ask, why does doing = ?", () prevent sql injections?

[zealous spire]

correct, they're called prepared statements

[jade wing]

the ? is a placeholder and will use the first variable that comes after the sql statement in it's place but will do it safely
you must have the same number of placeholders and variables after the sql statement so that they match up
the next placeholder will use the next variable and so on

[high agate]

Alright

thanks

one more thing... sorry

How do i expand the data to 3 columns ? this current code gives errors
@jade wing

[jade wing]

skip the () around the sql statement string
and the first sql statement in your screenshot only have VALUES(?, ?) but it should be VALUES(?, ?, ?) since you specify three column names and also try to use three variables, then you have to have three placeholders as well

[jade wing]

you should also fix the first sql statement in that screenshot to use a placeholder as well instead of interpolating the variable in the f-string like you are still doing there

[high agate]

Yeah i need to fix those

Why do i skip the ()?

and use 3 variables? what for

im sorta confused sorry

[jade wing]

you just don't need the () around the sql = ("select ...") should be sql = "select ..." instead

[balmy totem]

Any suggestions for an embedded database, preferably file/json based for a very lightweight async app?

[jade wing]

it's easier to show you if i can copy paste the code and show you corrections instead of having to type it off from a screenshot

[high agate]

!code async def setup(ctx, channel : discord.TextChannel, staff : discord.Role): db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute("SELECT channel_id, msg FROM main WHERE guild_id = ?", (ctx.guild.id,)) result = cursor.fetchone() if result is None: sql = ("INSERT INTO main(guild_id, msg, channel_id) VALUES(?,?,?)") val = (ctx.guild.id, staff.id, channel.id) await ctx.send(f"Set") elif result is not None: sql = ("UPDATE main SET msg = ?, channel_id = ? WHERE guild_id = ?") val = (staff.id, channel.id, ctx.guild.id) await ctx.send(f"Updated") cursor.execute(sql, val) db.commit() cursor.close() db.close()

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

for embedded database i would mostly recommend sqlite, but then you don't get json and it's not recommended that you put in blobs of such things in it

[jade wing]

skip the "!code" in your message next time so that the bot doesn't react unnecessarily

[high agate]

    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute("SELECT channel_id, msg FROM main WHERE guild_id = ?", (ctx.guild.id,))
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO main(guild_id, msg, channel_id) VALUES(?,?,?)")
        val = (ctx.guild.id, staff.id, channel.id)
        await ctx.send(f"Set")
    elif result is not None:
        sql = ("UPDATE main SET msg = ?, channel_id = ? WHERE guild_id = ?")
        val = (staff.id, channel.id, ctx.guild.id)
        await ctx.send(f"Updated")
    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()```

I fixed it so it now works but im looking if it can be improved

and so i can learn what i can do better

im new to SQL

[hasty badger]

hi

im currently dealing with mysql

and was wondering if you can create a programme that creates an existing database

e.g. I have a database A

I create this programme

[high agate]

@jade wing sorry, does this look alright?

[jade wing]

but i would go with something like:

    if result is None:
        sql = "INSERT INTO main(guild_id, msg, channel_id) VALUES(?, ?, ?)"
        val = (ctx.guild.id, staff.id, channel.id)
        await ctx.send(f"Set")
    else:
        sql = "UPDATE main SET msg = ?, channel_id = ? WHERE guild_id = ?"
        val = (staff.id, channel.id, ctx.guild.id)
        await ctx.send(f"Updated")
    cursor.execute(sql, val)

i just didn't include the code around that i didn't change anything about

[hasty badger]

(sorry didnt realise there was an existing question, shall return when this is resolved)

[high agate]

Alright thanks for the help, appreciate it alot

[jade wing]

you can also change it around so that you can use the same order and shorten the code a bit like this:

    if result is None:
        sql = "INSERT INTO main(msg, channel_id, guild_id) VALUES(?, ?, ?)"
        await ctx.send(f"Set")
    else:
        sql = "UPDATE main SET msg = ?, channel_id = ? WHERE guild_id = ?"
        await ctx.send(f"Updated")
    cursor.execute(sql, (staff.id, channel.id, ctx.guild.id))

i just changed the order of the columns in the insert statement to be able to use the same order as in the update statement and reuse the val part and without using a variable for it

[jade wing]

are you using the results for anything other then to see if there was a match in the database or not?
otherwise you don't need to select multiple columns

[high agate]

in that command no

it keeps it so that when a user uses /suggest it will send their suggestion to the setup channel

but i already configured that

[jade wing]

if you just want to check for existence and don't really are going to use the data you can do it like this:

cursor.execute("SELECT 1 FROM main WHERE guild_id = ?", (ctx.guild.id,))

[jade wing]

if you have any other function where you select more than one column and actually use the data i can show you another trick with sqlite

[high agate]

alright

[jade wing]

no no, we can work on several questions at once in the normal channels, it's just the help-* channels that is dedicated to one persons question at a time, so go ahead, i just didn't understand your question fully

[hasty badger]

k

thanks

so

basically i got a database

is it possible to make a programme capable of making this database

so I could, for example, send it over to someone so they could create it without the original

[jade wing]

i just don't understand what you mean with "making this database"

[jade wing]

oh, you mean setup the tables and stuff?

[hasty badger]

yup

[jade wing]

but they will still need a mysql database installed and setup and they will need to configure the program to use the right username and password for their own database in that case

[hasty badger]

yup

idm that too much

[jade wing]

or you have to let them connect to a remote database that is already running
or you can embed a database directly in your program

[hasty badger]

possibly

im basically looking for something that can reverse what a database creating programme does

going from database to setup programme

is there a way of doing this without typing it all up?

[jade wing]

aha, now i think i understand what you want to do
you can export the database with both structure and data or just the structure if that's all you want to have

[hasty badger]

ah i see

how would one do that?

on mysql

ah wait

i see it

tysm that helps a ton

[jade wing]

for simple things like just one table you can use: https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html

[hasty badger]

oh yup i understand how to make them, was just wondering how to convert a large database into a suitable sending format

thank you again!

[jade wing]

aha, okay, i would use https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html and compress it so that the data is more compact before sending it

[hasty badger]

thank you!

yes this is exactly what i was looking for!

[jade wing]

oh, by the way, as your program is async you may want to take a look at using this instead: https://aiosqlite.omnilib.dev/en/stable/

[high agate]

is there any difference to what im using rn?

[jade wing]

it's async way to access sqlite just like the rest of your program is also async

[high agate]

what if i dont use that way?

just wondering

[balmy totem]

Thanks for responding, sorry... I had to jump on a call, just now reading.

One aspect is just that I don't see a point in using something SQL based

I already have my models in pydantic... most don't have references, so there's no referential integrity to worry about

These are basically just general persistence and transaction logging

most of what this is going to do is by nature ephemeral... if something dies, then it's good to know what was last completed... but it's not going to auto-resume and not going to be a long term query

Each run is actually going to be siloed... So, the only 'real persistence' is the users, basically the header tables.

The rest is designed to be archived and 'go away' so I don't want that in a central database.

That's part of why I'm leaning away from a true database... I could stand one up, but then I'd still have the issue of pulling that data out and archiving it

[jade wing]

i see, well that is a different kind of use case for sure

[balmy totem]

I looked at montyDB & mongita... but neither seems to handle concurrency

What I might do is use aiosqlite with an ORM for the general persistence and just dump the rest as yaml/json or simply flat log files

I'm trying to find a way to handle this consistently and avoid reinventing the wheel

I'd do yaml for everything, but then I have to handle concurrency for anything that is stateful

[jade wing]

yeah, sounds like an idea, you might also want to look at aiosqlite if you want async support

[balmy totem]

Well, there's also aiofiles... which would handle that for flat files to some extent

It's a bit of decision paralysis to be sure 🙂

I mean, the other option is just to use redis 🙂

I'm just trying to avoid external dependencies

[high agate]

@jade wing Do you know how i can get it to say the exact values in the column in the row of that guild id?

Atm it only sends this..

[jade wing]

you might be able to use an embedded redis version

[jade wing]

what values are you expecting instead?

[high agate]

The channel and role id

But like #bot-commands and @Role

[paper flower]

@high agate You're selecting two columns

so result is a tuple

[high agate]

eg

[high agate]

Ik i want to select two

But i want to get the data separate

[paper flower]

a, b = (1, 2)

[jade wing]

can you show the code as text instead of a screenshot?

[high agate]

    cursor = db.cursor()
    cursor.execute("SELECT channel_id, msg FROM main WHERE guild_id = ?", (ctx.guild.id,))
    result = cursor.fetchone()
    channel = bot.get_channel(id=int(result[0]))
    em = discord.Embed(color=0xf3e914, description=f"{channel.mention}")
    await ctx.send(embed=em)```

I know found out how to get the channel mention

Just not role

channel = bot.get_channel(id=int(result[0])) I used that to get channel

[jade wing]

adding the line con.row_factory = sqlite3.Row after connecting you can also access results as a dict: https://docs.python.org/3/library/sqlite3.html#accessing-columns-by-name-instead-of-by-index

    db = sqlite3.connect('main.sqlite')
    db.row_factory = sqlite3.Row
    cursor = db.cursor()
    cursor.execute("SELECT channel_id, msg FROM main WHERE guild_id = ?", (ctx.guild.id,))
    r = cursor.fetchone()
    channel = bot.get_channel(id=int(r["channel_id"]))
    # and do something else with r["msg"] somewhere else
    em = discord.Embed(color=0xf3e914, description=f"{channel.mention}")
    await ctx.send(embed=em)

that way you can restructure your select statement later and don't need to remember in which order which column was selected

[high agate]

Con?

It shows as it cant be found

[jade wing]

sorry, copy paste mistake, in your code it should say db there

[high agate]

Ah ok

It works

let me try for smg

msg

[jade wing]

i updated my code above to be correct

[high agate]

Works

[teal dome]

Why does sqlite3 create a separate file instead of writing it into my file that I already set up? and also do I have to set create table execution every time I’m accessing this data

[storm mauve]

if it's creating a new file each time, that means you are not pointing it to the one which already exists

[teal dome]

Oh

Here I’ll send path

For some reason it says the file is not a database when I put .sql in the string

So I just removed it and it worked but not really

[storm mauve]

iirc the .sql suffix is used to save queries, not databases

[teal dome]

I also tried it with the .db but still same thing

[storm mauve]

what does your Databases folder looks like?

[teal dome]

One sec

[storm mauve]

furthermore... is that some cloud host like replit?

[teal dome]

Yeah

[storm mauve]

see #965291480992321536 message

[teal dome]

It’s not free

It’s paid

I pay for it

@storm mauve

[jade wing]

a few cloud provides do not have persistent storage on their servers, Heroku is one such example
in those instances you need to use an external database

[storm mauve]

their "Hacker" rank?

[teal dome]

It’s paid and comes with its own database

[teal dome]

No

It’s XGamingServer

Not repl

[storm mauve]

ah

[teal dome]

Here is my string that I put in for the directory

[storm mauve]

either way, there's a chance they are deleting the files which are not part of the project you uploaded - if they do provide their own database, you should use it instead of sqlite files

[teal dome]

But I have no idea how to use it

Like editing on python files

I know how to create but not edit it in python

@storm mauve

[storm mauve]

I'm taking a look at their website - their focus on discord bots doesn't really shines though

https://xgamingserver.com/discord-bot-hosting

Extensive Documentaion

Our documentaion will already get you up and running with valheim servers. From basics to advance server configurations.

you can try asking in their discord server though

[teal dome]

Ok

Thanks

[teal dome]

Their server is literally dead lol

[jade wing]

if it comes with a database there's a chance you might have to use it for persistent data, that is exactly how Heroku does it

[wild pelican]

Here is my current code: https://paste.pythondiscord.com/ajecufemib

How could I use if d_id[0] in database basically? I'm not sure what to put instead of database in my instance.

The relevant database connection is: py db = await aiosqlite.connect('database.db') cursor2 = await db.execute('SELECT role, time_expired, user_ids FROM roles') b = await cursor2.fetchall()

Please ping me with a response if you have one :)

[jade wing]

i see your code but don't really understand the question

[ashen knoll]

Anyone worked on oracle fusion how can we export data to s3 directly using jdbc or any other tool without using fusion model and report

[crystal vapor]

https://paste.pythondiscord.com/ezeviyawij Please

[meager hatch]

how fetch data from foreign key in fastapi and tortoise? https://github.com/tortoise/tortoise-orm/issues/1126

[velvet coyote]

    async def __aenter__(self):
        self.is_in_use = True
        async with self.pool.acquire() as conn:
            self.conn = conn
        return  self.conn
    
    async def __aexit__(self):
        print("__aexit__ invoked.")
        if self.is_in_use:
            await self.pool.release(self.conn)
            self.is_in_use = False

I'm getting the error

Traceback (most recent call last):
  File "/Users/inheritanc-e/Desktop/The_Rationals/new_rationals/bot/extensions/moderation/infraction.py", line 23, in warn
    await ctx.send(conn.fetchrow("SELECT * FROM infractions;"))
  File "/Users/inheritanc-e/.local/share/virtualenvs/new_rationals-Kq0YaaEy/lib/python3.10/site-packages/asyncpg/pool.py", line 55, in call_con_method
    raise exceptions.InterfaceError(
asyncpg.exceptions._base.InterfaceError: cannot call Connection.fetchrow(): connection has been released back to the pool

In the error I can't find __aexit__ invoked but it still released the connection pool nonetheless. Why is that?

got it fixed

[velvet coyote]

Welp, ran into another issue. I keep getting UndefinedTableError relation "x" does not exist, even though it clearly exists in the database.
What would be the main cause of this? Btw the table is empty

[jade wing]

you might need to reinstall the python sqlite module on your system, it looks like it's broken
i also would advise against having the database file stored in cloud storage such as onedrive

[paper flower]

won't

async with self.pool.acquire() as conn:
    self.conn = conn

release the connection?

[velvet coyote]

ye

thats why i did

        self.is_in_use = True
        self.conn = await self.pool.acquire()
        return self.conn

this way the connection isn't released

[paper flower]

You might want to call __aenter__ and __aexit__ directly if they're more complicated on connection

[velvet coyote]

i fixed it , but thanks a lot for the concern.

[velvet coyote]

Wdym

[paper flower]

if there's something more complicated in __aenter__ and __aexit__ in connection class instead of copying the same code/logic you can call these magic methods on it directly

[velvet coyote]

oo

[amber zenith]

Hey

Does anyone know how to find difference in vectors between a set of scores in two table

[long cave]

#python-discussion

[torn sphinx]

how can i make python index through a 2GB database quickly efficiently

[paper flower]

What exactly you want to do?

2gb doesn't sound like much, you probably can do everything you want on database side

[torn sphinx]

Any fix?

I was wondering about a while loop as it makes a select * query every minute

but

would that be heavy on the dB server?

[paper flower]

why would you select *? 🤔

Also maybe your connection just timed out

[bitter summit]

One question about SQL's UNION:
what does "The columns in the same position in each SELECT statement should have similar data types" mean in unions
For example salary is an int, branch_name is a string/varchar yet it works fine

[paper flower]

They should be "compatible"

https://www.postgresql.org/docs/current/typeconv-union-case.html

[bitter summit]

alright, thank you

[crystal vapor]

Whats the installation of it called ? Like pip install…

[paper flower]

You probably need to completely reinstall python, sqlite is built-in

[crystal vapor]

Ohh maybe I need to install the SQLite extention on vsc

Cuz i dont think i have that installed

[paper flower]

Perhaps you changed some of the source files in your installation

[paper flower]

That's most likely not the case

[crystal vapor]

Nope all file names are the same and the path they are put in

[paper flower]

Well, installing vscode extension won't help

Reinstall python

[crystal vapor]

How do i install SQLite anyway?

[paper flower]

It's built in

[crystal vapor]

Wdym? Download the program it self

[paper flower]

sqlite is built-in into python

You don't have to install it

[crystal vapor]

Well it says i have to install it

[paper flower]

😐

[crystal vapor]

How do i install it then?

[paper flower]

^

[crystal vapor]

Pip reinstall discord.py or uninstall the program and redos load it?

[worn flame]

good morning guys
I have a mongodb / architecture question.
I have a mongodb with 3 tables, which i want to fetch data from, but only when updated -> get the recent new row/s.

I was thinking to add mosquito as a pubsub or something, but i do remember that i do that kind of stuff with mysqldb, so i was wondering..

[paper flower]

What do you mean by recent? You just want to select N most recent rows?

[worn flame]

yes. checking the db evey x seconds isn't an option btw (that's what i have now)

[paper flower]

Hm, i don't work with mongodb, but mysql and postgresql should have pub/sub mechanisms

But i'm not really sure how to solve your problem there

Maybe you can use another messaging solution there, but i think it depends on scale of your project

[frigid dock]

yo so i needed some help with db using sqlalchemy for the db and when i do:-

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///todo.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Todo(db.Model):
    sno = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    desc = db.Column(db.String(500), nullable=False)
    date_created = db.Column(db.DateTime, default=datetime.utcnow)
    
    def __repr__(self) -> str:
        return f"{self.sno} - {self.title}"

@app.route('/')
def hello_world():
    todo = Todo(title='First Todo', desc='start introduction to flask')
    db.session.add(todo)
    db.Session.commit()
    return render_template('index.html')


if __name__=='__main__':
    app.run(debug=True, port=8800)

i get the error

any idea?

fixed nvm lol

it was that the S was capital instead of lower

[tender salmon]

@bot.command()
@commands.has_any_role(692760082085183519, 940008547993927691, 863878825376743475, 902292952381001779, 863879069253894166, 902292952381001779, 863879304110276668, 940008547993927691)
async def пред(ctx, member: discord.Member = None, *, reason = None):
    
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild_id BIGINT, user_id BIGINT, warn INT, count INT, moderator_id BIGINT, reasons VARCHAR)""")
    base.commit()
    
    
    if member is None:
        await ctx.send("Выберите участника")
        return
    
    if reason is None:
        await ctx.send("Укажите причину")
        return
        
        
    warnings = cursor.execute("SELECT * FROM warning WHERE user_id = ? AND guild_id = ?", (member.id,ctx.guild.id)).fetchone()
    
    print(1)
    cursor.execute('INSERT INTO warning(guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1,ctx.author.id,reason))
    base.commit()
    print(2)
    cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
    base.commit()
    print(3)
    await ctx.send(f"**{ctx.author.name}** Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")

If a person has no warnings, then when you give out, the bot does not write anything to the chat, but he issued a warning, if you issue it again, the bot will already write, how to fix it?

[ionic latch]

https://gyazo.com/8c71e67589ef8a6ac21cdb722b1c26bd

def LargeSales(boundary):
      conn = sqlite3.connect('yeye')
      cursor = conn.cursor()
      SQL = '''SELECT ProductName, Quantity, LastName, FirstName, OrderNumber
               FROM
               WHERE OrderNumber >= boundary
               ORDER BY ProductName, LastName, FirstName, OrderNumber ASC;'''
      
      cursor.execute(SQL)```

is that correct?

[jade wing]

you need your table name in there after from and boundery in the SQL statement should be substituted with a ? and skip the ; at the end
then you have to run execute like: cursor.execute(SQL, (boundary, ))

[ionic latch]

what do you mean with after boundery too? do i have to name all the table names there too?

OrderNumber should be substituted with ? right

[blissful finch]

Hi there, quick context: I'm making a discord bot for artist to let them create cards of their OCs. I am using SQL to create database to store those data. The attachment shows what I want to make basically. Each artist have the possibility to add an oc associated to their name, then those characters have information to explain who they are. I already made that part as a test/alpha version of the bot. But after some feedbacks people were asking to add more categories in a form of sliding embed. Anyway, I thought of something more interesting: letting them add their own category.
But now I'm wondering: can I make that without creating several database. I don't want to have a database for the added category, and then another one for the oc with the additional information and so on. I'm looking for the most optimal way to do with my little knowledge of SQL. So would anyone know how to perform that ?

[jade wing]

what is the name of your database table (not columns), os it sales or something like that?

[hasty badger]

hi

i have a question about inserting data into tables

anyone available?

[keen minnow]

You don't need multiple databases for that. You can do this with multiple tables.
You would probably need at least a table to describe the category (id, name, creator?, etc.) and then have the thing that uses categories to refer to that

[jade wing]

just ask the question here in the channel

[blissful finch]

Then it would mean that for example, I can set various tables with a primary key

?

Or a key, I don't know exactly what the primary key exactly do

[keen minnow]

That would be your first order of business: figuring out what primary keys are

That will give you more insight into how to use them and how useful they are

https://database.guide/what-is-a-primary-key/ should be a good starting point

[blissful finch]

thank you very much ^^

[hasty badger]

thanks

is there a way to enter the rest of results into a table as null

e.g.

let's say i have a table with fields: OrderId, Name, Note1, Note2, Note3, Note4

is there a quicker way to do:

INSERT INTO orderer
VALUES (1, 'Coding Camel', 'a camel', null, null, null, null);

i.e. condensing all the nulls into a singular statement

or are default values required to do so (this is mysql btw)

[tacit cloud]

INSERT INTO orderer (OrderId, Name, Note1) VALUES (1, 'Coding Camel', 'a camel')
pretty weird to specify the ID, by the way

@hasty badger also, any time you see a field like Note4, it proabably means you want a one-many instead... (put notes in a separate table with a FK to orderer)

[hasty badger]

Ah yes ofc, thanks so much

[hasty badger]

Ye it was mostly just a random example, not an actual one lol

[jade wing]

yeah, just leave out the fields that should get their default value set, just like some of the above examples illustrate
you can even set another default value for a column when you create the table or you can even change it later with alter table

[coral briar]

In the web browser that I'm making whenever I try and go to almost any site (besides youtube) no videos will play. I don't know why this is. I'm making this in PyQt5.

[white ocean]

what does import main do PLEASE TELL ME

[oak oyster]

It imports "main"

[wild pelican]

Traceback (most recent call last):
  File "C:\Users\\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\client.py", line 375, in _run_event   
    await coro(*args, **kwargs)
  File "c:\Users\\OneDrive\Desktop\Code\Arcane Bot\main.py", line 1135, in on_message
    if message.channel.id in a[0]:
TypeError: argument of type 'int' is not iterable```

```py
    db = await aiosqlite.connect('database.db')
    cursor = await db.execute('SELECT * from channels WHERE channel_ids=?', (message.channel.id, ))
    a = await cursor.fetchone()
    if message.channel.id in a[0]:
        await message.channel.send('pp')``` How come this isn't working? I'm not sure what it really means.

Please ping me with a response, thanks!

[paper flower]

a[0] is an integer, it's not iterable

Maybe you meant to use ==

[wild pelican]

That worked, thank you!

[torn sphinx]

How do I get max value out of a column. I have generated that column using another query. In SQL

[paper flower]

max?

[paper flower]

e.g.

select max(column) from table;

[autumn violet]

Hi all, I am pretty new to Python programming and its best practices, and would like some input on my issue.

I have the following issue. I have 150k sentences that I split in words, and for each word I determined its lemma and grammatical purpose in the sentence. However, each word could have multiple lemmas. And then, each word could also have different grammatical purposes in the sentences. So a probability is calculated for each. This work I did 🙂

But now I want to save my work in a db, but here is an issue. My db looks like this:

table: words

• word_id

table: word_lemmas:

• wordlemma_id (primary key)
• word_id
• probability

table: word_lemma_grammars:

• wordlemmagrammar_id (primary key)
• wordlemma_id
• probability

I have the result of the sentence analysis in some List structure with dictionaries, as there are 1 to many relationships (not sure if I should have used some Pandas here, would that be appropriate for these nested structures?)

Anyway, I need to do the following in a loop:

1. INSERT the lemma into the word_lemmas
2. then, I need to get the wordlemma_id
3. and then I need to INSERT the grammar in the word_lemma_grammars table with the retrieved wordlemma_id

I am a bit uncertain how to do this. Currently I am trying to make it work with sqlAlchemy. But as far as I understood, retrieving the id of the inserted record can only be done using the session flush command using the ORM framework / design / thingy. Bur for using ORM, is it correct that I need to manually define the Table structure in my Python code first, before I am able to do an insert and retrieve the id of the newly created record? Or could I use also another approach? Should I just discard sqlAlchemy for inserts and rely on the mysql.connector? What is best practice here?

[paper flower]

Yep, you have to define your models in python code, your models look pretty simple

Can't really compare them

I wouldn't really use JSON files for any dynamic data

Database is better in this case

[autumn violet]

but what is the benefit of this? I mean, if I change my model, I need to update db, rewrite pieces of code etc. Can't I just create a model based on the table that is in DB?

[paper flower]

I personally would prefer doing that declaratively from code, though, you can do that too

[autumn violet]

how would you define the models dynamically?

[paper flower]

Also you would probably want to create some migrations later (adding/removing database objects), there's a tool for that too

[paper flower]

I'm looking for that atm 😅

https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#mapping-declaratively-with-reflected-tables
🤔

That's if you want to use orm

You can use core too

To be honest i would use orm, since it would be easier to save your objects

table: words
- word_id

table: word_lemmas:
- wordlemma_id (primary key)
- word_id
- probability

table: word_lemma_grammars:
- wordlemmagrammar_id (primary key)
- wordlemma_id
- probability

If i understand correctly lemma_grammars are nested in word_lemmas and lemmas are nested in words?

[autumn violet]

yes

[paper flower]

With correctly configured relationships sqlalchemy should take care of saving your objects

word = Word()
word.lemmas.append(Lemma())
word.lemmas[0].grammars.append(Grammar())

session.add(word)
session.flush()

[autumn violet]

that is cool

and the relationship is correcly configured like you wrote?

or should I also define a foreign key?

[paper flower]

I don't think that relationships would be configured if you use reflection 🤔

Probably would have to add them yourself

[autumn violet]

how to configure relationship?

[paper flower]

You should link your tables with foreign keys, yep

relationship is a sqlalchemy thing:
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html?highlight=relationships

In most cases it uses foreign keys to determine join conditions between your tables though

[autumn violet]

ok, pretty cool

[autumn violet]

so I have to define word, fill in all the properties and then link Lemma, etc.

and then with 1 "add" / "insert" the primary keys and links are auto taken care of.

But I need to do a little defining up front

that is pretty cool

thanks

[paper flower]

Mhm, i would personally recommend you defining your tables from code though, that would make your app easier to deploy and run

[autumn violet]

but I don't see how, because what if I want to add a column or change a column? At this moment, I am still in prototype mode, this happens a lot. So if I would define it in code, I get a lot of constraints to deal with.

[paper flower]

You just change the code 🙂

There's a tool for creating schema migrations, you can create and delete database objects with it

It also can detect most changes in your models and generate migrations based on them

Like adding a new column

Then you simply apply migrations to your database and it's up to date with your code

[autumn violet]

thanks, I will look into that

[past sundial]

hey, any good database to store about 1billion timeseries records? i only need to query by nearest to timestamp and contained in timeinterval

i know there are a bunch of dbs like timescaledb that support timeseries, but they are all sql and i don't need a whole sql db

[clear stirrup]

you don't want to use a sql db?

[past sundial]

well no i dont have to model any relationships

really it doesnt even need to be a database, a package the "open" a 100gb file and then return a pandas dataframe containing the entries between two timestamps would be fine aswell

ok so i actually found a solution that works, kinda.

import pandas
df = pandas.read_csv(..., skiprows=n, nrows=m)
``` which will read rows `]n,n+m]` and then i will use binary search with a simple `open(...)` to find the two line numbers

[high agate]

How would i give all embeds an ID, e.g
And store the data in my SQL db

[jade wing]

this might interest you: https://github.com/VictoriaMetrics/VictoriaMetrics

[past sundial]

thanks

[balmy oxide]

when you mine a bitcoin what server/computer gives the bitcoin to you?

[pure mortar]

@harsh pulsar we talked about columnar databases once upon a time https://www.startdataengineering.com/post/what-is-a-data-warehouse/

the example they have here is a clear demonstration why analytical queries perform better

now that i think about it

pandas dataframes stores data in a columnar orientation...this is probably why, right @rough hearth ?

those pandas series

[rough hearth]

what

why have I been summoned to the databases channel

[paper flower]

Because reasons

[rough hearth]

I don't actually know very much about pandas' underlying data model

[pure mortar]

but you do know that the dataframes are basically columns put together (series)

[rough hearth]

I think I remember something about that

[pure mortar]

could this be why?

i only bring this up because if i get this new job, they might ask me to make a data warehouse, which is like such a loaded term lol

[paper flower]

I don't think you would be able to use pandas for really large data sets though

[pure mortar]

because its dependent on business use case / data

[pure mortar]

pyspark

[rough hearth]

but to the extent that the dataframe is using numpy under the hood, I believe numpy uses contiguous blocks in memory, where the order that you'd get from reshaping the array to one dimension is the order of values in memory

[pure mortar]

or dask

dask is a bunch of pandas dataframes put together

[rough hearth]

pandas itself is all in memory, so you need at least three times as much RAM as the size of the data, or the garbage collector won't be able to keep up.

[paper flower]

I'm aware that it stores all in memory 🤔

[pure mortar]

they use lazy loading so the computations arent actually done until its actually run

[rough hearth]

I'm not a regular of this channel

[pure mortar]

ah this is what pytorch uses

for its tensors

[rough hearth]

tensors are just rebranded arrays
change my mind

[pure mortar]

at least from my understanding from the minitorch project. unless i mistakenly misunderstood everything

[paper flower]

@pure mortar Depending on amount of data you might be fine with something like postgres actually

[pure mortar]

tbh i want to talk about data engineering/data warehousing in the #data-science-and-ml but i might get yelled at

but like that type of stuff needs to be done

to enable DS/ML

[rough hearth]

wouldn't I be the one to yell at you for that? data warehousing is on topic for DS/AI.

*it is so ordered 🧑🏻‍⚖️ *

[pure mortar]

oh im sure i can get away with postgres for like everything but there are tradeoffs/use cases between relational vs. non-relational databases

[rough hearth]

the judge in that emoji on my computer has grey hair. so I guess they won't get appointed to the supreme court if they aren't on it already.

[pure mortar]

ok

next time i will talk about data engineering stuff there anyway

[paper flower]

I mean, postgres is fine, unless you have terrabytes of data

[pure mortar]

looks at chart...

[paper flower]

For most common applications postgresql would be perfectly fine

[pure mortar]

most def

its when you get into the big data space

that you run into trouble

[paper flower]

Less is more

😅

[pure mortar]

usually yes

but try telling that to the DL folks

[paper flower]

I think big data is quite niche, at least most companies don't actually have that much data to need specialized solutions

[pure mortar]

anyway ill bring my data engineering stuff into #data-science-and-ml since this is more of a relational DB channel anyways

[rough hearth]

you can also ask about graph databases and mongo here if you want.

[pure mortar]

what about vector databases

or serverless databases

[rough hearth]

idk

[pure mortar]

im just being contrarian

[rough hearth]

just don't talk about fight club.

[pure mortar]

[paper flower]

json is a nice database...

[pure mortar]

so...redis

key-value stores make nice caches

[paper flower]

Redis actually could be used as permanent storage

[pure mortar]

yes yes

ok bye for real now

have fun

[paper flower]

Hm, could someone actually help me with SQL?
I'm creating game inventory system and i'm not sure if my solution is ok:

class BaseItem(Base):
    __tablename__ = "item_base"

    id: Mapped[str] = Column(String(40), primary_key=True)
    type: Mapped[str] = Column(String(32), nullable=False)

    __mapper_args__ = {
        "polymorphic_on": type,
    }


class ItemLink(Base, Generic[T]):
    __tablename__ = "item_link"
    __table_args__ = (CheckConstraint("amount >= 0"),)

    item_id: Mapped[int] = Column(ForeignKey("item_base.id"), primary_key=True)
    item: Mapped[T] = relationship("BaseItem")
    account_id: Mapped[int] = Column(ForeignKey("account.id"), primary_key=True)
    account: Mapped[Account] = relationship("Account")
    amount: Mapped[int] = Column(Integer, nullable=False, default=0)

I have a specific item (Fish) that could be linked to accounts via ItemLink, but i guess i'd have to add another table if i want to have unique item instances, for example durability on specific items

class Fish(BaseItem):
    __tablename__ = "item_fish"

    id: Mapped[str] = Column(ForeignKey("item_base.id"), primary_key=True)
    name: Mapped[str] = Column(String(80), nullable=False)
    price: Mapped[int] = Column(Integer, nullable=False)
    spawn_chance: Mapped[float] = Column(Float, nullable=False)

    __mapper_args__ = {
        "polymorphic_identity": "fish",
    }

[harsh pulsar]

yes... kind of. pandas stores data in numpy arrays, which can be row major or column major

but yes, columnar databases are designed for vectorized columnwise operations

(also now pandas can use non-numpy arrays, eg backed by apache arrow arrays)

[pure mortar]

good to know, interesting

[primal maple]

is anyone good at mysql work bench

I have to create my own data base at least 4 tables

[bitter vault]

I believe I’ve used it before and am somewhat familiar with it

However I’m not as good with natural keys and creating relational tables

[vast epoch]

Im good with RDBMS, let me know what you need to know

[primal maple]

im doing EER diagram and this confused me

[vast epoch]

Dining table is the fact table rest are dimension tables. Id Columns in dim tables are primary keys which will be foreign keys in fact table

Line defines the relationship between each other and those symbols at the end depicts how are related 1:1 or 1:n or n:1 or n:n

[primal maple]

are u able to help me via call? if not thats fine

[torn sphinx]

How do I store timestamp objects using aiosqlite? because, I know that sqlite3's connect method can be passed options, but i cant find those options to be anywhere in the aiosqlite docs

[burnt turret]

you can just pass the sqlite3 option into aiosqlite.connect. the docs say that they "replicate sqlite3"

In [1]: import sqlite3, aiosqlite

In [2]: from datetime import datetime

In [3]: conn = await aiosqlite.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

In [8]: cur = await conn.cursor()

In [9]: await cur.execute("CREATE TABLE foo (bar INTEGER, baz TIMESTAMP)")
Out[9]: <aiosqlite.cursor.Cursor at 0x7f6a11b18ac0>

In [10]: await cur.execute("INSERT INTO foo VALUES(?, ?)", (1, datetime.now()))
Out[10]: <aiosqlite.cursor.Cursor at 0x7f6a11b18ac0>

In [11]: await cur.execute("SELECT * FROM foo")
Out[11]: <aiosqlite.cursor.Cursor at 0x7f6a11b18ac0>

In [12]: await cur.fetchall()
Out[12]: [(1, datetime.datetime(2022, 5, 14, 14, 34, 20, 576099))]

[torn sphinx]

ohhh thanks a lot

[high agate]

How would i remove the data if the result is found?

As ik INSERT just inserts more data, i want it to remove the selected data near the top of my screenshot

[vast epoch]

Write delete query…. delete from blocks where reason = result ; something like this

[jade wing]

shouldn't item_base.id be declared as an int rather then a str or the mapping be all wrong?
and item_base might as well be item_type with all the attributes that are common for all items like price and spawn chance as well as default durability and such
also, if you like to have attributes per item instance, wouldn't item_instances be enough to keep track of how worn each item is and to what user it belongs instead of having both a item_link table and a specific table for each item type like item_fish?

[high agate]

cursor.execute("DELETE FROM blocks WHERE user_id = ?", (user,)) ?

[vast epoch]

What record you want to delete accordingly write where clause .. so i think along with what you have written add reason filter as well

Where user_id=? And reason =?

Im on phone.. so not able to type it clearly.. but hope you got the point

[jade wing]

that is correct if you want to delete any and all rows in the blocks table for that user_id

[paper flower]

No, i want item id to be a string, for example fish__some_variety
Yep, i might rename ItemBase to ItemType
ItemLink is essentially an instance, Fish is a class/template

[gusty path]

Is this the correct way to do variable substitution in asyncpg?

await con.fetch('SELECT "$1" FROM canvas WHERE index = $2', x, y)

because it says column $1 is not defined

[paper flower]

I think i might leave it as is, but add json field to ItemLink so i can store some dynamic properties

[jade wing]

usually one use ? for placeholders, but with asyncpg it looks like you annotate placeholders like that ($1, $2 and so on)
but you can't use placeholders for column names, only for values and you don't stick them inside double quotes either

[gusty path]

Yep I figured you cant use $1 in column names, and double quotes were for columns with numbers as names, 5 doesnt work so I need to write "5" with the quotes to point to that column

[jade wing]

yeah, that's correct, but you might want to look into properly normalizing your data structure instead of having columns like that with just numbers, it's usually a sing that one should rethink the data structure

[gusty mulch]

to use the redis-py lib/package do I need to install anything additional to use the lib/package? I've pip installed redis-py but I haven't installed redis itself (partly bc I'm not sure where from or if I need to ) and the redis-py docs don't mention having to install anything else (it could be making assumptions you have a redis client installed i guess?) but when I try to run the redis-py connection in my code I get this Could not connect to redis: Error 22 connecting to localhost:6379. 22. error, jut wondering if anyone else has used redis and can offer advice.

Also does anyone know how to use async connection pools now that redis-py and aioredis are merged, they don't seem to have any docs for it https://redis.readthedocs.io/en/latest/connections.html#connectionpool-async

[mint mauve]

how can I get a column value in sqlite3?

def login():
    username = input('Enter username: \n')
    cur.execute(f"SELECT * FROM users WHERE username = '{username}'")
    
    if not cur.fetchone():
        print('Username does not exists')
        login()
    
    entered_password = input('Enter password: \n')

[ionic latch]

SELECT LastName, FirstName, SUM([o.Id]) AS 'OrderCount'
FROM Customer c, [Order] o
WHERE c.Id = o.CustomerId
GROUP BY LastName, FirstName, 'OrderCount'```

what is wrong here

[modest shore]

await self.bot.db.execute('INSERT guilds SET authorized = $1 WHERE "guild_id" = $2', True, ID)

what is wrong with that, : PostgresSyntaxError: syntax error at or near "guilds"

i barely use postgres

[burnt turret]

that query seems like you mixed an INSERT and an UPDATE

what are you trying to do?

[modest shore]

im trying to insert a value into guilds setting authorized to true with the guild_id

idk how to explain it

[burnt turret]

insert means you add a new row to the table, update means you change an existing row. which of these are you trying to do?

[modest shore]

add a new row

        @commands.command(hidden=True)
        @commands.is_owner()
        async def authorize(self, ctx, ID: int):
            authed_or_not = await self.bot.db.fetch('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID)
            if authed_or_not:
                return await ctx.send("that guild is already authorized")
            else:
                await self.bot.db.execute('INSERT guilds SET authorized = $1 WHERE "guild_id" = $2', True, ID)
                await ctx.send("that guild has been authorized")

[burnt turret]

right, so you've got the syntax for an insert query wrong there. you want something like

INSERT INTO guilds(authorized, guild_id) VALUES($1, $2)

https://sqlbolt.com/lesson/inserting_rows

[modest shore]

so like self.bot.db.execute('INSERT INTO guilds(authorized, guild_id) VALUES($1, $2)', True, ID)

?

[burnt turret]

try it and see

[modest shore]

what does this mean

[burnt turret]

it means that when you insert a new row into the guilds table, you HAVE to provide a value for the prefix column as well. if you see the query i just sent, it only inserts a authorized and guild_id, and tries to leave the rest of the fields as null

[modest shore]

so do I need to put prefix in there also?

[burnt turret]

yeah, if you need to insert a new row 🙂

[modest shore]

alright let me try

[modest shore]

ty ❤️

[modest shore]

@burnt turret hey i have another question, how could I update it to false instead? i am tired and can't figure ito ut

[burnt turret]

just put False instead of True in your insert?

[modest shore]

ok so basically just do the same thing but false instead of true on my unauthorize command?

or do i need to update the row

[burnt turret]

i dont quite get what you need rn

[sand trout]

A question about SQLAlchemy:
How do I create a sqlalchemy.Column object, that has a type that is given to me as a string - say "NUMERIC", and not sqlalchemy.NUMERIC?
Is there some dictionary, that maps datatype names to their respective classes?
There is always the option of using getattr on dialect modules, but it feels unholy:

import sqlalchemy
from sqlalchemy.dialects import mssql, oracle, postgresql, mysql #, ...

my_dialect = oracle  # or whatever other logic
my_type = "NUMERIC"  # or whatever other logic
try:
    type_obj = getattr(my_dialect, my_type)
    my_column = sqlalchemy.Column("my_column", type_obj)
except AttributeError:
    ...

I'd love to hear other ideas

[modest shore]

        @commands.command(hidden=True)
        @commands.is_owner()
        async def unauthorize(self, ctx, ID: int):
            authed_or_not = await self.bot.db.fetch('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID)
            if authed_or_not:
                return await ctx.send("that guild has been unauthorized")
            else:
                await self.bot.db.execute('INSERT INTO guilds(authorized, guild_id, prefix) VALUES($1, $2, $3)', True, ID, ",")
                await ctx.send("that guild is not authorized" )

so basically i wanna do the opposite of the code i sent u earlier, updating the row to false instead of true but i dont know how to

[burnt turret]

just to be clear: there's no update happening here

you're only inserting a new row

[burnt turret]

just put False there instead?

[modest shore]

yeah, i dont know how to update it

[slender atlas]

Don't these values need to be given as an iterable? So in one tuple or a list

[burnt turret]

asyncpg is quirky that way

accepts *args

[slender atlas]

Cool

[burnt turret]

https://sqlbolt.com/lesson/updating_rows take a look, the syntax isn't very hard

[modest shore]

await self.bot.db.execute('INSERT INTO guilds(authorized, guild_id, prefix) VALUES($1, $2, $3)', False, ID, ",") is this correct

[burnt turret]

that will insert a new row with those values, not update an existing one

[modest shore]

so is it ('UPDATE INTO guilds(authorized, guild_id, prefix) VALUES($1, $2, $3)', False, ID, ",")

?

[burnt turret]

no, i'd highly recommend clicking on the link i sent. it shows the right syntax at the very top of the page.

[modest shore]

so like db.execute('UPDATE guilds SET authorized $1', False)

[burnt turret]

yep

authorized = $1

[modest shore]

Kk

[modest shore]

didn't work sadly, ```py
@commands.Cog.listener()
async def on_guild_join(self, guild):
for guild in self.bot.guilds:
auth = await self.bot.db.fetch('SELECT authorized FROM guilds WHERE "guild_id" = $1', guild.id)
if not auth:
print("unauthorized")
return await guild.leave()
print("authorized")

[burnt turret]

can you give some more context? i don't understand what the code is supposed to be doing or what it is doing

[modest shore]

if guild is authorized, it stays in server, if not it leaves

basically

[burnt turret]

what's with the loop then

[modest shore]

it's to check if any other servers have the bot if the server isn't authorized, and then leaves

[burnt turret]

because of the return await it just stops at the first guild it finds that isn't authorized

[modest shore]

yeah, i just realized that, but it's something to do with this command not updating the value to False

        @commands.command(hidden=True)
        @commands.is_owner()
        async def unauthorize(self, ctx, ID: int):
            authed_or_not = await self.bot.db.fetch('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID)
            if authed_or_not:
                return await ctx.send("that guild has been unauthorized")
            else:
                await self.bot.db.execute('UPDATE guilds SET authorized = $1', False)
                await ctx.send("that guild is not authorized" )```

wait

hang on

[burnt turret]

use fetchval instead of fetch here

and your select query/the if condition needs to be flipped

[modest shore]

        @commands.command(hidden=True)
        @commands.is_owner()
        async def unauthorize(self, ctx, ID: int):
            authed_or_not = await self.bot.db.fetchval('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID)
            if authed_or_not:
                return await ctx.send("that server is not authorized")
            else:
                await self.bot.db.execute('UPDATE guilds SET authorized = $1', False)
                await ctx.send("that guild has been unauthorized" )```

like that?

[burnt turret]

now authed_or_not will be True if the guild has been authorized
then you've got

if authed_or_not:
  return await ctx.send('that server is not authorized')

you need to flip the condition there

[modest shore]

explain more am confused

[burnt turret]

            authed_or_not = await self.bot.db.fetchval('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID)
            if authed_or_not:
                return await ctx.send("that server is not authorized")
``` what do you want this code to do?

[modest shore]

check if the guild is not authorized

[burnt turret]

right, guide me through step by step what you've got there

[modest shore]

        @commands.command(hidden=True)
        @commands.is_owner()
        async def unauthorize(self, ctx, ID: int):
            authed_or_not = await self.bot.db.fetchval('SELECT authorized FROM guilds WHERE "guild_id" = $1', ID) ## Checking Authorization value
            if authed_or_not: ## Checks if guild is not authorized
                return await ctx.send("that server is not authorized")
            else:
                await self.bot.db.execute('UPDATE guilds SET authorized = $1', False) ## If guild is authorized, unauthorize it by setting value to False
                await ctx.send("that guild has been unauthorized" ) 

[burnt turret]

if authed_or_not: ## Checks if guild is not authorized
``` read this again, is `if authed_or_not` checking if the guild is NOT authorized, or is it checking if it is authorized?

[modest shore]

ohh, so it is always going to return False*

[burnt turret]

no it's not always True, it is True if the guild was already authorized -- that's what i've been saying you need to flip that condition there

we've gone far from the topic of this channel now. if you still don't get it, open a help channel and ping me

[gusty mulch]

anyone familiar with redis?

[brave tree]

redis-py is just the client, do you have the actual server installed?

[gusty mulch]

Nope, I should get that installed then. It's the docs for redis-py didn't mention having to install a redis server so I was assuming it created the server or something. I can now see that's a silly assumption but it would have been good for them to mention you need to actually install the redis server

[brave tree]

its surprisingly common, no worries

[pallid shard]

How to select schema in postgres like show create table in mysql

[ionic marsh]

I'm using PostgreSQL. Is there a way to have the PostgreSQL generate a random number that's not in a column? For example, I have 100 records and each record has an ID. From that list of ID's, generate a number between X and Y that's NOT already in the database. How would I go about doing that?

[pallid shard]

https://www.geeksforgeeks.org/postgresql-create-auto-increment-column-using-serial/ - you can use soemthing like auto_increment

[ionic marsh]

Thanks @pallid shard

[torn sphinx]

Hey im using Pymongo and im having a problem where when i try to warn someone then check their warns on another server it shows the amount of warns from the server they were warned in, is there any thing i could do to fix this ive tried a lot of things please help, and thank you. Here is the code.

@commands.command()
    @commands.has_permissions(kick_members=True)
    async def warn(self, ctx, user: discord.Member, *, reason=None):
        guild= ctx.guild
        if reason == None:
            reason="Not specified"
        id = user.id
        guildna= ctx.guild.name
        guildid= ctx.guild.id
        
        if not id:
            return
        
        if collection.count_documents({"memberid":id}) == 0:
            collection.insert_one({"memberid":id,"GuildID":guildid,"guild-na":guildna,"warns": 0})

        warn_count= collection.find_one({"memberid":id,"GuildID":guildid})
            
        count = warn_count["warns"]
            
        new_count = count + 1
            
        collection.update_one({"memberid":id},{"$set":{"warns": new_count}})

        embed=discord.Embed(description= f"**{user}** has been warned for: **{reason}**", color=0x6666FF)
        embed2=discord.Embed(title="Warned", description= f"You were warned in **{guild}**\n"
        "\n Tip: Don't Be a dumb!", color=0x000000)
        
        await ctx.send (embed=embed)
        await user.send(embed=embed2)```

[deft raft]

How do I create a database without phpmyadmin?

[modest shore]

someone good with sql help me in #help-cookie

[compact marlin]

Hi! I am new to python and I am using the pandas library, I want to do a table from an api that return a json file but I dont know how to make a table of the selected red stuff. Thanks in advance!

[torn sphinx]

ofc itll show the amount of warns they were in since you are querying the records of that user from that specific guild and this more or less belongs in #discord-bots

[earnest lily]

regarding sqlite3, is there any way to refresh table data (not an update query, data is added to the table externally) as opposed to creating a for loop, making a new connection/cursor to requery everytime?

[unkempt prism]

Take a look at the json_normalize docs below find the example with:

result = pd.json_normalize(
    data, "counties", ["state", "shortname", ["info", "governor"]]
)

see https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html

[viscid adder]

hi got an error while using pyrebase : AttributeError: module 'collections' has no attribute 'MutableMapping', does exist any way to fix it ?

[paper flower]

Is this error from a 3rd party lib?

[viscid adder]

yep pyrebase

[paper flower]

Which version of python are you using?

[viscid adder]

3.10.4

[paper flower]

Are you sure? I'm on 3.10.2 and collections has MutableMapping class

[viscid adder]

yes wait a minute

i'm gonna send you a screenshot but first i reinstall python

[paper flower]

Ah, wait, you're right

It probably got moved into typing but i'm not sure about details

It's also in collections.abc