databases

[rain field]

is the one receiving

the msg

so?

@thorn jolt

you have any idea of why this is happening

[thorn jolt]

see buddy

let's make another table

called logs

with a relationship to the users

this is a cleaner way to keep track of messages instead of having a log text

@rain field

[rain field]

ahahah

i just asked my dad

he said the same thing

he said that first of all updating a table is expensive on the performance end

inserting

is more efficient

and that I should have another table with a none clustered thingy

and just have it with the username as the primary key

and then the logs

[thorn jolt]

historiquesender = self.server.Cursor.execute("select logs from users where username = ?",(self.username,))
historiquereceiver = self.server.Cursor.execute("select logs from users where username = ?",(User.username,))

historiquesender = str(historiquesender)+', '+"from "+str(self.username)+" to "+str(User.username)+": "+message if historiquesender else "from "+str(self.username)+" to "+str(User.username)+": "+message

historiquereceiver = str(historiquereceiver)+', '+"from "+str(self.username)+" to "+str(User.username)+": "+message if historiquereceiver else "from "+str(self.username)+" to "+str(User.username)+": "+message

self.server.Cursor.execute("update users set logs = ? where username = ?",(historiquesender,self.username))
self.server.Cursor.execute("update users set logs = ? where username = ?",(historiquereceiver,User.username))

@rain field if you will use inline if please use () for order

[rain field]

()?

[dusk canyon]

Guys, i have a question....

which is better:

1. take a time-penalty and read-write each and every operation from/to SQL database?
2. create a cached class (context manager) that, when created loads the content of the entire database into a single object, performs all operations from within Python (it's interpreter), and then upon end-of-life, uploads all the changes into the DataBase???

the #2 way seems to be faster, but it may lead to "data-race" conflicts
on the other hand, database (at least sqlite3) needs changes to be "committed" before the closing the DB connection.

Does it mean that data-race conflicts unavoidable in either cases??

Which idea is better???

[runic pilot]

#2 is better, but most databases automatically do that for you, or can be configured to do that

this is commonly referred to as a "transaction"

in raw SQL, you can start a transaction with a begin; statement, then run all your queries, inserts, and updates, then either rollback; your changes or commit; them and flush them to disk

most popular ORMs (like SQLAlchemy) encourage using transactions by making it the standard examples in their docs

some databases (like Mongo) will always have your statements be in transactions, and commit them when they want to, instead of allowing you to control it

[dusk canyon]

nice

Thank you very much!

[runic pilot]

happy to help!

[rain field]

how can I allow

multiple same values for columns

nvm

figured it out

[rain field]

quick question

i use special characters

such as

é

è

à

ç

etc

they appear

as ?

in tables

how do I fix that

[inner raven]

with asyncpg, is there an easy way fetch/execute with a list of unknown length?
conn.fetch('SELECT * FROM table WHERE name IN ($1, $2 ... $x)', List)
or would I have to create a new query and format IN ($1 ... $x) in myself then unpack the list?

[woven knot]

I'm on day 60+ with Flask (now on my second project) and keep getting stuck when taking user input on the webpage and then posting(?) to the DB or to be rendered on another webpage.

I'm at the point where I need office hours with God to explain this clearly. Can anyone provide a good article, video, person to explain this process, please? Thank you!

[torn sphinx]

Hello guys, Any ideas why I cannot read the sheet with code below :

import pandas as pd

file_excel_path = "../Desktop/Workbox_template.xlsx"
sheet1 = "AIC"
sheet2 = "APP"
sheet3 = "WP"
sheet4 = "Console"

exl_file_tab_1 = pd.ExcelFile(file_excel_path, sheet1)

exl_sheet_1 = pd.read_excel(exl_file_tab_1)

print(exl_sheet_1)

Error message : ValueError: Unknown engine: APP

Above, my sheet name

[vocal moon]

is there anyway to set a starting number for the SERIAL datatype is psql

[torn sphinx]

Had to use like this : exl_sheet_APP = pd.read_excel(exl_file_tab_1, sheet_name=APP)

[gaunt frigate]

I'm using sqlite3

    cursor.execute("SELECT * FROM WL")
sqlite3.OperationalError: no such table: WL

Program host

with connect(file_name) as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM WL")

        return cursor.fetchall()
    
    return False

Local Terminal

sqlite> .tables
WL

[tepid crow]

delete the table and create it again

or restart ur database

[gaunt frigate]

mk

[gaunt frigate]

Same error

[gaunt frigate]

Fixed it! Wrong dir path

[torn sphinx]

I have an issue, and really don't understand. I'am looping of rows and append() to a list id already looped to avoid to loop severals times on it. But my 'not in' doesn't work

for row in range(df_wp_cop.head(1000).shape[0]):

    value = df_wp_cop.iloc[row]['Id']

    if value not in wpArrAlrd:

        wpArrAlrd.append(value)

        appInfo = df_app_cop[df_app_cop['WorkPackage_Id'] == value]

I still have doubles

As you can see, create the variable 'value' with the id, check if my list contain it, and append it afterward

[glass gorge]

can someone briefly explain why there are so many different versions of databases

like I am trying to do a webapp and I'll need to host it on a server, and have persistent data stored there. But I'm having a hard time deciding which type of SQL server to use

[glass gorge]

I will just google it

[spark warren]

I am wondering if it's valid to return a database engine... E.g.

class Connect2DB():
  def __init__():
    DATABASE_URI = "postgres://" + credData["dbUser"] + ":" + credData["dbPass"] + \
        "@" + credData["dbHost"] + ":" + \
        credData["dbPort"] + "/" + credData["dbName"]
    engine = create_engine(DATABASE_URI)
    return engine

Asking because when I call it, I get the error AttributeError: type object 'Connect2DB' has no attribute 'execute'

[brazen charm]

init doesnt have a return statement

it returns a class instance

so no that wont return a db engine

it'll return a class instance of Connect2DB

[spark warren]

Thanks - I changed it to py class Connect2DB(): def returnEngine(): DATABASE_URI = "postgres://" + credData["dbUser"] + ":" + credData["dbPass"] + \ "@" + credData["dbHost"] + ":" + \ credData["dbPort"] + "/" + credData["dbName"] engine = create_engine(DATABASE_URI) return engine

[brazen charm]

no self

in the parameter

you probably want a static method deco on that function to make it valid

[spark warren]

Sorry.. Was looking up decorators. Only time I've used them is when designating paths in flask

Thanks @brazen charm

[scenic zinc]

Do you guys recommend using uuid/guid instead of ints to index users?

I do not know too much about the benefits

I know using int can save space, but I am running across an issue with inserting my data models to the db because I do not have a way to pull ids from the database

unless I query the latest id that was inserted

[blazing stag]

does sqlite3 and mysql have the same syntax, for python

[brazen charm]

no

[spark warren]

@brazen charm - Is programming your job?

[brazen charm]

im Still a student atm lol

[spark warren]

Do they get you to use multiple database types in your course work, or are you doing that for fun?

[brazen charm]

mostly for fun

Most of the time i use PostgreSQL or MongoDB depending

[spark warren]

I've used mongodb... Seems easier than SQL

[blazing stag]

is the syntax easier to understand?

[brazen charm]

for what Mongo?

[blazing stag]

yea

[brazen charm]

it doesnt really have a syntax

its essentially Json data types as a Database

with SQL you might have something similar to this to select a row:
SELECT * FROM table WHERE value=xyz
cursor.fetchall() for example

In mongo its:
db_object.find_one({'value': xyz})

Yes ik im missing the Union for that output
That is mongo ⬇️

w3schools have a good section on it https://www.w3schools.com/python/python_mongodb_getstarted.asp

[rain field]

real quick question

can I do this

abc = select * from table where value = a

for row in abc:

?

can I do that

[dusky siren]

ok this is both database and discord.py related but this seems more database related so I'm posting it here, please excuse my lack of database knowledge if this is something simple:
How would I save a variable to a value in a database? I'm currently using a code in this instance:
c.execute("INSERT INTO users VALUES (ctx.message.author.id, 3)")
That value is a variable but all examples I have seen do not use variables and instead just state the value already instead of using a variable with the value.

In case error message is important:

Ignoring exception in command start:
Traceback (most recent call last):
  File "C:\Users\ryanb\Desktop\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:/Users/ryanb/Desktop/SpawnerTester/AchievementBot.py", line 170, in start
    c.execute("INSERT INTO users VALUES (ctx.message.author.id, 3)")
sqlite3.OperationalError: near ".": syntax error```

it's sqlite3 btw, forgot to mention, apologies

[tame crown]

@dusky siren well I've never used sqlite3 but usually probably because you are directly trying to input (ctx.message.author.id, 3) when you should either use an f string and put the {ctx.message.author.id} in the query like that. Or don't use an f string and replace ctx.message.author.id with {} and use .format(ctx.message.author.id) at the end

oh shit I responded a little late there

[dusky siren]

it's fine, any help is help

alright, no more errors! Ty!

[torn sphinx]

the correct way to insert sqlite values is execute("INSERT INTO table VALUES (?, ?, ?)", (value1, value2, value3)), afaik

where the second arg is a tuple

[tame crown]

no as that is bad for sql injection

or so I've heard

also there's not really a 'correct' way, it still worked for them

[torn sphinx]

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)

https://docs.python.org/3/library/sqlite3.html

[tame crown]

hmmm I was told that was the thing to avoid

weird

either way, always scrub input and escape strings

[brazen charm]

The ? method is the correct way for string escaping for sqlite

Postgres uses a $n method for place holders and mysql uses % if I remember??

What you don't want to use is the normal python for matting for inputting data that have no control over or that can get changed

Because it won't be auto escaped

[hallow cloud]

in any case, don't just interpolate parameters directly into string which is unsafe, let the tried and tested tools do such things

[glad spoke]

Apologies if I should be in #discord-bots but this is more related to mySQL which I am learning as I go rn.

I have a database which stores all the levels of members and their XP. I want to sort the database so that people with the most XP are at the top and then be able to figure out their position and thus get their rank in the level list.

[tame crown]

ORDER BY xp; then with a for range you can get the rank

or index will get the 'rank'

[glad spoke]

thanks

[restive stone]

Whats the best way to go from json to a database?

What I have now is a json

[high bone]

@restive stone Maybe make a small script that migrates your data?

[restive stone]

@restive stone Maybe make a small script that migrates your data?
@high bone wdym?

[high bone]

You can write a python script that takes your data, formatis it properly for database consumption, and sends it to the db, then you don't use that script anymore

[restive stone]

Maybe?

[narrow flax]

Im working on a covid screening web app for my dad's buisness (using flask) and it is my first time doing any db work. I need a database of all his staff and I need to log their temperatures and their answers to the symptom checklist every day, would sqlite be suitable for this?

[torn sphinx]

@narrow flax how much data do you expect to handle.. say over a month and up to a year, how many transactions per day?

[narrow flax]

@torn sphinx Say 50 employees worth of results once per day, until he no longer has to screen his staff but let's call it a year. There is a chance I might have to add additional functionality such as adding a way for him to track hours for his wages, as he currently uses a wage book.

[torn sphinx]

so, you can create a small instance on a cloud service, say google cloud, some of them are free if it's small instances.. you can use postgresql because they offer launching of sql db instances with certain images and postgresql is one of them

and I suggested using a cloud service because backups can be automated there

and if ever you wanted to increase the size of your instance, you can do that too.. so you can initially start with the smallest one

but 50 employees per day, that's excel sheet level.. you can essentially use Google sheets as a DB too

[narrow flax]

I can just spin up postgresql in a docker container on the same vps I would host the app on... I am considering just using CSV files as a db...

[torn sphinx]

sure you could do that.. just make sure to back your files up somewhere like google drive

you can have a cron job that runs nightly to do this

or you know, the csv can be on the drive already

what's the best ORM for postgresql?

[runic pilot]

SQLAlchemy

[torn sphinx]

SQLAlchemy

[brazen charm]

depends on the driver if its async or not

[raven cargo]

Hello! I'm trying to figure out how to use the update ... from syntax with asyncpg.
I want to use this to update multiple rows in my table from the same query, and as it seems this saves a lot of resources VS running through each row individually.

If I have a query with already pre-filled values it works fine, so what I'm trying to figure out is how I can dynamically add more "rows (data)" into the array that I then query.

"UPDATE economy SET currency = c.currency FROM (VALUES (69, 472546414455685132, 1), (6969, 472546414455685132, 1)) as c(member_id, guild_id, currency) WHERE c.member_id = economy.member_id"

This will work fine. But how would I go about if I want to prepare the values from an array instead? (Something like this for instance)

"UPDATE economy SET currency = c.currency FROM (VALUES $1) as c(member_id, guild_id, currency) WHERE c.member_id = economy.member_id", [(69, 472546414455685132, 1), (6969, 472546414455685132, 1)]

Any tips are appreciated!

[torn sphinx]

does psycopg2 not have any orm features?

[brazen charm]

nope

[runic pilot]

it's not listed in their features page (https://www.psycopg.org/features/), it claims to be a driver

[brazen charm]

it also has some really weird methods of formatting

[runic pilot]

libraries like SQLAlchemy do the ORM layer and defer db specifics to the drivers (like psycopg2)

[brazen charm]

the fact it uses python string formatting as its place holders is also a real 🤔 Moment

[torn sphinx]

yea I noticed that too

I wonder how or if they stop injection

[brazen charm]

it doesnt lol

it just relys off replacing %s with $x behind the scene

and lets postgre deal with it

but in terms of the formatting for table names etc... that is litterally just formatted

Psycopg2 is the thing that stops me using postgres for sync based stuff

just because of how utterly weird it is

its random method of formatting, its executemany being slower than just a for loop, its prepare system being weird and just idk

[torn sphinx]

its executemany being slower than just a for loop
lol

oof

[misty zenith]

is there a way to use print('{0} {1}'.format()) from a query?

[23, 'Minha Historia', 'Chico Buarque', 27]
[141, 'Greatest Hits', 'Lenny Kravitz', 26]
[73, 'Unplugged', 'Eric Clapton', 25]
[224, 'Acústico', 'Titãs', 22]
[37, 'Greatest Kiss', 'Kiss', 20]
[21, 'Prenda Minha', 'Caetano Veloso', 19]
[55, 'Chronicle, Vol. 2', 'Creedence Clearwater Revival', 19]
[221, 'My Generation - The Very Best Of The Who', 'The Who', 19]
[39, 'International Superhits', 'Green Day', 18]
[54, 'Chronicle, Vol. 1', 'Creedence Clearwater Revival', 18]

this is my current result, but it needs to be formatted according to my teacher

all the commas need to be tabs or something

[pliant notch]

How to create database?

@hello

[torn sphinx]

@pliant notch what db are you using?

[pliant notch]

Sql

@torn sphinx

[blazing stag]

So I have a pygame game in a file, and I am using sqlite as a database for me to save someone's info after the game code is done. I put the sqlite stuff in a seperate file from the game code. I have used modules from different places, but I am not used to using modules or files of code that I have written myself. So I am kind of confused and have some questions.

So if I import the DB file into the game file with import 'dbfilename', will the code in the DB file start to run if I run the code in the game file

[dim hound]

Hey everyone I have a mongoDB document that looks like this:

[
  {
    "_id": "id",
    "ServerID": "12345",
    "otherStuff": "other values",
    "Favorites": [
      {
        "username": "disguisedtoast",
        "ID": "87204022",
        "Online": false
      },
      {
        "username": "pokimane",
        "ID": "44445592",
        "Online": false
      },
      {
        "username": "scarra",
        "ID": "22253819",
        "Online": false
      }
    ]
  }, ...
]

how can I update one object's value inside of favorites, for example, the one with username: Scarra, change the 'Online' value to true

[alpine ibex]

Hey guys, I am trying to make a search bar to search through my database for certain keywords. I am trying to figure out how to use elastic search with sql databases. Anyone have any experience?

[pliant notch]

How to make sqlite db?

[tepid cradle]

using Python? Just import sqlite3 or sqlalchemy and create a connection to a file. If the file does not exist, it will get created

[torn sphinx]

Hi i need help connecting to postgres db for my bot. I am on windows 10 and postgres is running but gives me error when connecting. I conenct like this:

bot.pgdb = await asyncpg.create_pool(user='discord', password='mypasword',
                                 database='discord', host='127.0.0.1', port=5432, command_timeout=60)

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

I have experience with mysql and now idk where i am going wrong with this.

I created the database discord and also add a table inside it which executed successfully

I tried with username postgres and also with sudo password but still same error

[scenic zinc]

Can any of you intelligent individuals explain if I am creating a table correctly. I wanted to use UUID's as primary keys because I can just create the uuid at the client level before sending it off to the database. Also what so you guys recommend? Use UUID, INT/BIGINT, or a combination of both. I've been reading about the advantages and disadvantages of each solution.

class User(Base):

    id = Column(
        UUID(as_uuid=True),
        primary_key=True,
        unique=True,
        default=uuid.uuid4,
        nullable=False,
    )
    user_id = Column(BIGINT, primary_key=True, unique=True, nullable=False, index=True, autoincrement=True)
    email = Column(String, unique=True, index=True)
    username = Column(String, nullable=True)
    password = Column(String, nullable=True)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    birth_date = Column(Date, nullable=True)
    is_active = Column(Boolean, default=True)
    created_date = Column(DateTime, default=datetime.utcnow, nullable=False)

[torn sphinx]

can someone help me connect my python to postgres please?

I have been trying all day but keep having the error

[proven arrow]

Maybe the error is not postgres related. It doesnt seem so anyways. In fact error says,
File "D:\Programs\Python\Python37\lib\site-packages\wavelink\websocket.py", line 75, in _connect @torn sphinx

The error you are seeing presumably at startup is related to wavelink most likely.

[torn sphinx]

How do I use Dbeaver, I just installed it and have almost no clue on how to get started

Oh yes i am dumb, i waste all day today try to fix this error and was because i forgot start wavelink server

[tepid cradle]

How do I use Dbeaver, I just installed it and have almost no clue on how to get started
@torn sphinx Click on Database > New database connection on the menu bar

Then select the type of database you are using. It will then ask you for credentials, which will depend on the Db you're using

[torn sphinx]

So how would I connect it to python?

do connect it to my python code

[blazing void]

dbeaver is a database browser, you connect to a database (or in the case of sqlite, a sqlite file). it doesn't connect to python

you'd use it to connect to the same database that your python code connects to to let you visually inspect the database

(or in the case of sqlite, open the same sqlite file that your python code does)

[tepid cradle]

A database is a separate thing, it's not a part of Python programming. You store your data in a database. Python provides ways to access and update those databases.
Dbeaver is just for connecting and viewing/updating the database. It has nothing to do with Python. You can't connect it to Python

[charred hedge]

hi guys, who can help me with that code? I will show error and code

Code:

cursor.execute(f"INSERT INTO users VALUES('{member}', '{member.id}', 0, 0, 1)")

Error:

  File "bot.py", line 99, in on_ready
    cursor.execute(f"INSERT INTO users VALUES('{member}', '{member.id}', 0, 0, 1)")
sqlite3.OperationalError: near "𝖘": syntax error

[blazing void]

where are your column names?

[charred hedge]

It worked fine on other bots (I'm talking about the code), but it doesn't work on mine

I did everything correctly, I moved everything and imported it

[blazing void]

no really, where are your column names in your query

INSERT INTO users (column_A, column_B, column_C, etc) VALUES (...)

column names!

[charred hedge]

second

[blazing void]

sqlite is telling you there is an error near the "s" which presumably is the final s in users, which is right next to where the list of columns should go

[charred hedge]

Hmm strange.. On another bot, this is not present, but it works perfectly there

[blazing void]

you can omit only if you specify values for all columns in the database

[charred hedge]

i can give you all code

[blazing void]

are you doing that? if not, can you output the full f-string, and check it doesn't have any odd characters like single quotes in it (which is an injection vector)

[charred hedge]

I don't have the columm names in this code

@bot.event
async def on_ready():
    cursor.execute("""CREATE TABLE IF NOT EXISTS users (
        name TEXT,
        id INT,
        cash BIGINT,
        rep INT,
        lvl INT

    )""")
    
    cursor.execute("""CREATE TABLE IF NOT EXISTS shop (
        role_id INT,
        id INT,
        cost BIGINT

    )""")

    for guild in bot.guilds:
        for member in guild.members:
            if cursor.execute(f"SELECT id FROM users WHERE id = {member.id}").fetchone() is None:
                cursor.execute(f"INSERT INTO users VALUES('{member}', '{member.id}', 0, 0, 1)")
                
            else:
                pass

    connection.commit()

[blazing void]

your id column is an int, but you're providing a quoted string literal

[charred hedge]

Well, I didn't write it. I am not a professional in sqlite and databases, I will try to fix it right now.

again ;|

[sullen heath]

Is it okay to run SQLALCHEMY methods like Session.add(Model), Session.commit() in assync ?

[blazing void]

it's a little sketchy if you've got any async awaiting because a different threadlet might try to use the session in progress

ORMs aren't really well suited for async use because their "unit of work" paradigm doesn't quite fit...but having said that you can probably use it if you're careful. there's a few SO questions/answers discussing this, I recommend googling for them

[sullen heath]

Thanks for the help @blazing void

[pliant spoke]

k so ik basic json now, like dumping and loading. how do i such knowledge to use json as an alternative to a db for for example my bot

[blazing void]

when the bot starts, json.load the file. when the bot stops, json.dump the contents

[pliant spoke]

ahhhh

ok

@blazing void but 1. what if the bot never stops 2. lets say im making an economy system how would i update everyones balance?

[blazing void]

then let me ask you this: why do you need json?

json is a string serialization format for structured data that includes key-value mappings (dicts) and arrays (lists). It's used for storage and exchange of data between programs

it is however not by itself a database. Very few programs (if any) will attempt to access a json file as a database, instead most will load it into whatever that language's native representation of k-v stores and arrays are. in the case of python - dicts and lists

and those will stay in memory

for an economy sim, there isn't anything stopping you from keeping that data in-memory. If your data is in a structure of dicts and lists, that's fine. you can have a shared object that holds this data that whatever entities represent the consumers of the system can use (better still, apply formal OOP concepts to help make this interaction cleaner)

and you can just periodically flush that data to disk (json is ok, but other formats exist) as a backup in case the program unexpectedly crashes, so that you don't lose any (or much) data. Next time the bot starts, it can re-load this data in

[pliant spoke]

guess i gotta go more in depth with dicts.

i just dont understand how i would do that lmao. how am i supposed to keep track of every persons balance without a db 🤣

[blazing void]

two options:

1. the user's ID is the key in a dict, now you can easily access that person's data
2. I mean...why not just go with a db?

if you're used to thinking in terms of databases, why not use a database? this seems like an appropriate use case for it

[pliant spoke]

cause im still learning python, and i cant be getting ditracted with learning sql

[blazing void]

ok that's fair

go with 1 then. depending on the relations in your data, if you just need each person having one balance, simply this:

{
  "394931333845090304": 99.9,
  "574212452196155402": 0.11,
  ...
}

where those keys are user IDs. The reason I've made them strings even though they're all numeric is because if you did export this to JSON, they'd need to be strings

I assumed you were making a discord bot, so that's what a discord user ID looks like

[pliant spoke]

k well idk much abt dicts but im assuming u can change the balance at any givent time super simply. but how would i set that up? wouldnt i need to get everyone from my servers id manually 1 by 1

?

[blazing void]

up to you. discord api will get you every user in a guild. but you could just create the entry as needed when they first use the bot

the latter is easier, because pre-creating everything implies you're going to keep that updated somehow as new users join. You can do this - discord bot API can run stuff when new users join, but it's becomes an unnecessary additional complexity if you can just get away with creating entries as and when needed

[pliant spoke]

so like on_member_join... get their id... put it in a var... then use that var as the key?

[blazing void]

that's an option. but the one I was saying you should do is only when a user interacts with their balance, do you say "is user in this dict? if not, create the user's entry in this dict with zero balance, then use it"

[pliant spoke]

ah ok ok.

and how would i keep updating the dict when someone for example gambles their money away

[blazing void]

I don't know what your bot does, but a dict can have its values read, and updated as needed

you should learn about dicts, it's a very important thing in python

[pliant spoke]

ya sounds abt right

well thx man!

been a ton of help

[torn sphinx]

can i have help with mysql

[void otter]

is there a way of serializing sqlalchemy models to json without too much hassle?

i found this but all the docs are a bit daunting
https://github.com/Martlark/flask-serialize

[void otter]

okay, got marshmallow to work

[brazen charm]

now we just have to add update and delete collections wew

    # Make a database manager
    test_db = SqlXMotor(
        db_host_addr=os.getenv("DB_HOST_ADDR"),
        db_port=os.getenv("DB_PORT"),
        db_user=os.getenv("DB_USER"),
        db_password=os.getenv("DB_PASS")
    )

    # Connect to a PostgreSQL database called "Test"
    db_conn = test_db['Test']

    # Make a table object called "hello_world"
    fields = {
        '_id': pg_types.BIGSERIAL,
        'name': pg_types.TEXT
    }
    table = db_conn.create_table('hello_world', fields)

Some nice async based postgre

this is such a meme lol

[torn sphinx]

anyone suggest any good books on where I can get started on sql and databases?

[unique gulch]

SQL Queries for mere mortals, I think that's the name

[torn sphinx]

does anyone know any good database managers for postgres?

@unique gulch this one?

[unique gulch]

@torn sphinx Yep, that's the one. It looks like it's the later edition, which is good. There are tons of books out there but I felt this one gave really thorough explanations

[torn sphinx]

does anyone know any good database management tools for postgres?

thank you!

[timber jasper]

Which database dashboard is better for PostgreSQL? (like create User accounts, databases etc)

[torn sphinx]

GUI basically

[timber jasper]

ok

but is there a alternative to pgadmin?

[torn sphinx]

Yeah i think

[unique gulch]

Try here: https://wiki.postgresql.org/wiki/PostgreSQL_Clients @timber jasper

[timber jasper]

Ok Thanks

[unique gulch]

The only other client I really use is DataGrip by JetBrains but it's unfortunately paid, and not really an admin client like pgadmin

[timber jasper]

oh ok

I have the Student License also yeah xD

[unique gulch]

😛 Not sure about your university but mine had education licenses for most jetBrains products

I definitely took advantage of those even after graduating lmao

[timber jasper]

oh ok

[torn sphinx]

how do i install pgadmin on linux

[blazing void]

you'l need to add the postgresql apt repos

see: https://www.pgadmin.org/download/pgadmin-4-apt/

[torn sphinx]

Tried that, im talking about on a chromebook if it's possible @blazing void

[blazing void]

ah, not sure about chromebooks

[torn sphinx]

Yeah, tried numerous times and it won't work | Are there any good databases you know for chromebook?

[tepid cradle]

pgadmin is not a database

[torn sphinx]

i know

[torn sphinx]

how can i 'loop' through a query so that it executes the same query but just changes up the where statement to something i specify

i have 21 iterations i need to run but im not sure if i have to query 21 times or if i can do it once and have each query entered as a new row

[torn sphinx]

i define database like this:

bot.pgdb = await asyncpg.create_pool(user=db_user, password=db_pwd, database=db_name, host=db_host)

And then use query like this:

async def on_guild_join(self, guild):
        try:
            async with self.bot.pgdb.pool.acquire() as conn:
                async with conn.transaction():
                    result = await conn.fetch("SELECT * FROM guilds WHERE guild_id = $1", guild.id)

                    if not result:
                        query = """INSERT INTO guilds(guild_id, guild_name, owner_id, guild_prefix) 
                        VALUES($1, $2, $3, $4)"""
                        await conn.execute(query, guild.id, guild.name, guild.owner_id, "!")
        except Exception as e:
            print(e)

But i get error: 'Pool' object has no attribute 'pool'

Anyone know why?

[brazen charm]

apparently your pgdb is the connection pool

[torn sphinx]

@brazen charm so I managed to fix this, thx. But now I have error says null value in column ID during insert operation.

I don’t why this is error. I don’t specify the ID in my code because is auto incremental

[past widget]

How do I use WHERE in INSERT? (Ping Me)

[shy bane]

you don't @past widget
the syntax goes:

   VALUES (values)```

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

[past widget]

What can I do then?

[shy bane]

details

[past widget]

I need to insert where

see why?

It is inserting each one in a different row

What can I do?

[torn sphinx]

I am getting error when inserting into postgres: null value in column "id" violates not-null constraint

[earnest parcel]

    guildInDB = collection.find({"_id": guild.id})
    for x in guildInDB:
        currentPrefix = guildInDB['prefix']``` im working with mongoDB and it's giving me this error on the 4th line `cannot set options after executing query` how can this be fixed? i've never worked with a database so sorry if this is a dumb question

[torn sphinx]

@past widget seems as you have like a for loop when you inserting

or you are increasing the row by 1 each time.

[past widget]

I want all in 1 row

But they're all in wait for

so waiting for response

and then inserting response

[torn sphinx]

show the code

execute in a single query

[timber jasper]

Is the PostgreSQL dashboard: OmniDB good or bad ?

[misty zenith]

can anyone explain why this says no such collum?

name = input('geef naam van de playlist: ')
cur.execute('''SELECT playlists.Name, playlists.PlaylistId FROM playlists WHERE Name = {};'''.format(name))
ha = cur.fetchall()

if i enter Music as input, which is one of the variables in the collum playlists.Name, he says no such collum 'Music'

please anyone? i am really bad at sql and it's super fucking frustrating

if anyone can explain this to a moron (myself), i'd be happy to listen

please? i'm desperate as f*ck

[blazing void]

put string in quotes

otherwise it'll try to match a column name

[nova hawk]

@misty zenith please don't use the r word in that manner

[misty zenith]

@nova hawk sorry, i'm just pissed that i'm stupid

[nova hawk]

Thanks for deleting it.

[misty zenith]

@blazing void didn't change a thing

[blazing void]

what's your code now

single quotes

[misty zenith]

cur.execute('SELECT Name, playlists.PlaylistId FROM playlists WHERE Name = {};'.format(name))

[blazing void]

that's not what I meant

WHERE Name = Music this will match rows where the value in the Name column matches the value in the Music column (which doesn't exist in your case

[nova hawk]

Don't use format on database queries.

It leaves you open to sql injection attacks.

[misty zenith]

but i need user input

[nova hawk]

You have prepared statements for that.

[blazing void]

WHERE Name = 'Music' will match rows where the value in the Name column equals the string Music

see the difference?

[nova hawk]

Which db are you using?

[misty zenith]

ahh i see

[blazing void]

now listen to Grote

[misty zenith]

@nova hawk SQlite, obligated to use that one (school)

sorry i bother you guys with these dumb questions

[nova hawk]

You use a question mark where you want user input to go.

cur.execute('SELECT Name, playlists.PlaylistId FROM playlists WHERE Name = ?', name)```

This make sure the user input is treated as text, if you use the format method it could be treated as code.

By convention all column and table names are usually lowercase btw

[misty zenith]

now i get this: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 5 supplied.

oh ok

like i said, i'm dumb with databases

[nova hawk]

Oh my bad.
Use (name,)

A tuple with the name variable in it.

Now it's seeing each character of the 5 letter name as an argument.

Note that the trailing comma is required for tuples with a single value, to distinguish it from regular parenthesis.

[misty zenith]

OH MY GOD

YOU GENIEUS

THANK YOU

i cannot thank you enough man!

[nova hawk]

No problem.

[misty zenith]

and my apologies again for my previous behaviour

[torn sphinx]

i need some help to plot a dashboard with python dash, with connection (real time or not) to a mysql database, any tips or material to study? i really need

anyone know how i can combine multiple queries into one where i'm just changing the 'where' condition?

i have 20 queries that are all the same, i would like to have them all as one query. the only difference amongst these 20 is the where condition changes

[obtuse iron]

you could use union in your sql query to combine multiple queries into one, but in this case i am not sure if it will be the most efficient method but aslong as the outputs are the same the results should work

[torn sphinx]

yeah im trying a union all but idk how to order it by the query number execution if that makes sense

for instance i want all of query execution 1 to show up first, then all of query execution 2, etc. in my union all

[obtuse iron]

maybe in your select statement add a new column with query_order and assign value 1 for query 1, assign 2 for query 2

[torn sphinx]

ah good idea

then order by that column

[obtuse iron]

and you can order on the query_order column

exactly

[torn sphinx]

ty

[timber jasper]

Is the PostgreSQL dashboard: OmniDB good or bad ?

[upbeat grotto]

Hey, anyone here have experience with SQL and embeds in Discord.py?

[timber jasper]

Embeds yeah SQL no xD

[upbeat grotto]

I'm having a big problem(I feel like it is, I can't find the obvious answer) where I'm not able to have \n in my text, or else it will just print it...

[worthy pawn]

im using mysql and python through the mysql.connector here is my code:

mycursor.execute(f"DELETE FROM users WHERE userID = {self.userID}")
```, this doesnt remove the entry from the database.  I know that self.userID = userID in the database

[normal igloo]

wdym print it

[dire lark]

@worthy pawn You have to commit every time you make changes to the DB

[brazen charm]

@worthy pawn Dont use f strings to format queries

that is such an easy way to get injection attacked

[worthy pawn]

Ah yes ok

[unkempt basalt]

Hi! Can anyone proficient in Django say is it a single query or 2 separate queries?

# ... 
return self.organisations_lvl1.all() | self.organisations_lvl2.all()
# ... 

[blazing void]

might shortcircuit

oh wait no

ignore me

[brazen charm]

im going to hazard a guess and say probably 2 queries because they're different collections, the system it would need to cache them would be rather intense

tho im not 100% sure i would imagine that it would query

what db is it?

mysql, pg?

[unkempt basalt]

It's pg

[brazen charm]

ig you can always test it

if you go to pgadmin it should tell you the last interactions with it

[unkempt basalt]

Great, I'll check it, thanks

[pliant notch]

help me with discord.py

[brazen charm]

This is #databases not #discord-bots

[lucid wharf]

can i ask about pymongo here?

[brazen charm]

sure

[lucid wharf]

i'd like to change atk, matk, def, and res's names but idk how to do it. i've only been using update_one(query,newvalue) so far

[brazen charm]

update the keys or the values?

[lucid wharf]

the keys

[brazen charm]

you can use the $rename operator

[lucid wharf]

in update_one?

[brazen charm]

{'$rename': { <field1>: <newName1>, <field2>: <newName2>, ... } }

yeah

[lucid wharf]

so field1 has to match a key on the document

?

[brazen charm]

yeah

so in your case field1 could be atk

and then new name could be attack

[lucid wharf]

ohhh niceee that's really efficient

hold on let me try on currency collection to test hehe

{'$rename': { <field1>: <newName1>, <field2>: <newName2>, ... } }
@brazen charm i'd still need the query on the first param right?

[brazen charm]

im checking that now lol

[lucid wharf]

xD

[brazen charm]

i dont think you do, i think {}, {'$rename': { <field1>: <newName1>, <field2>: <newName2>, ... } } should work tbh

but pymongo's docks are such potato

https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html?highlight=update#pymongo.collection.Collection.update_many

there we go

update_many()

[lucid wharf]

i'm actually

doing this instead since i understand this more than update_many

xD

[brazen charm]

fairs lmao

[lucid wharf]

but it should work right?

[brazen charm]

tho that will nuke your db if the collection is pretty meaty

it should if theres a key daily

[lucid wharf]

it's very small >3<

lemme run rn

[brazen charm]

aha, ive tried todo that with 40,000 things in the database before lol

[lucid wharf]

what happened?

[brazen charm]

it crashed the entire server

[lucid wharf]

ripppp

what about the data?

are they fine?

[brazen charm]

datawas fine

[lucid wharf]

ohhh niceee

[brazen charm]

it didnt get past processing all the requests

[lucid wharf]

hmm, maybe if my db gets big, i'll try to add asyncio.sleep() after inserting, tho it probably would take time...

[brazen charm]

you shouldnt really update the keys all the time

but update_many would be a much better way as mongo handles it diffrently

[lucid wharf]

if i did collection.update_many({query}, {"$rename":{"daily":"streak"}}) would it replace every document with daily key in it?

[brazen charm]

i would guess so if query is an empty dict

{}, {"$rename":{"daily":"streak"}}

[lucid wharf]

empty dict means everything inside that collection?

[brazen charm]

yeah

[lucid wharf]

oooo, because i did find({}) that's why i had to for loop, didn't know i could just instantiate it by doing _many

i'll start using _many now xD

[brazen charm]

tis very useful for bulk stuiff

[torn sphinx]

why is postgres better than other database like mysql for discord bot?

for small bot is it matter?

i can use async library for both

[brazen charm]

small bots meh not really sqlite works

why not mysql?

postgre is basically better in every single way

More data types, open sources, generally faster than MySQL, hasn't been plagued by many security threats and other issues

the Async driver for Postgre and python is also alot faster and more efficient and maintained better than aiomysql

[torn sphinx]

I see, i just changed from aiomysql to asyncpg but was thinking if it was actually worth it. I am new first time to postgres and still getting used to it.

But you think is overkill for using asyncpg over sqlite?

[brazen charm]

i mean it depends

postgre's datatypes are very useful

its also alot more expandable compared to sqlite

[torn sphinx]

ah ok, but for now i didnt use any additional data types because i just wanted to migrate like sql code over.

although i found some confusing parts like postgres auto increment is through serial etc.

[brazen charm]

Serial types yeah

they are just helper types tho

rather than doing INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT

its just easier todo SERIAL PRIMARY KEY

[torn sphinx]

Yes i see also i find the $1 notation is easier than %s

[brazen charm]

also less likely to have someone miss interpret it as python string formatting

[torn sphinx]

Also another question. Is it any difference on memory speed if i do fetch vs fetchrow if i only want 1 record?

[brazen charm]

if you do fetchrow it will return 1 normally

which will be quicker on the db

because it wont need to get everything and then send it all back

it can just grab the first row and send that

[torn sphinx]

so it runs in like for loop or uses some other search algorithm?

[brazen charm]

its more just how SQL works

[torn sphinx]

oh ok

[brazen charm]

if you need to evaluate the performance side of stuff

you can prepare the statements and do await prepared_stamement.explain()

which returns the db's stats on that statement

[torn sphinx]

I see thanks

Last question

Currently i do like this each time i create table:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO discord;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO discord;

Is there automatic way to do this

Or is what i did not necessary and some other different way to do

If i dont add that it give permission denied error

[brazen charm]

you can normally set the user perms if you go onto the postgre server

for me i just create a user, set the password and then the perms for the database while logged in as a super user

[glass gorge]

good morning

I am trying to setup a DO droplet with NGINX

I'm having some issues setting up the firewall

im following this tutorial

https://www.digitalocean.com/community/tutorials/how-to-install-nginx-on-ubuntu-18-04

I've gotten up to this point ```You can enable this by typing:

sudo ufw allow 'Nginx HTTP'```

however when I check the status of my UFW it's stating it is inactive

i tried reseting the firewall and putting in the new rules, but that didn't seem to help

this is my first cloud deployment, and I'm stumbling about lol, anyone have any advice?

[brazen charm]

sudo ufw enable

[glass gorge]

lOL

thank you amigo

seems obvious in retrospect, however the tutorial seems to have skipped that step xD

[brazen charm]

yeah they kinda assume its setup

[glass gorge]

thank you again, I'm super new; first deployment

[somber matrix]

I'm creating a web app for geocaches but am getting a bit stuck on the backend and how to store the user generated listings. I've been researching MySQL but it seems like an endless rabbit hole. If anyone has any idea for anything better feel free to dm or say here

[blazing void]

my preferred option would be to do this with postgres and the postGIS extension. Yes, MySQL also has a GIS extension, but it's not as good (and I have a personal preference for postgres)

the reason for using GIS extensions is it's easier to do a geolocation search

but I think you're on the right track - an SQL database would be good, MySQL or Postgres would be your main choices

[somber matrix]

ok that sounds good

what is GIS sorry?

[blazing void]

geographic information system. used in this context, it means the database is able to do queries based on geographic information

[somber matrix]

ooo

[blazing void]

for example, you can query "everything within 5 miles of this GPS coordinate"

if you didn't use GIS, it would be harder to query by radius. you could do a square though (where Lat is between two values, and lon is between two values)

[somber matrix]

yeah i was planning on rounding the Lat and Lng to 3 decimals cos that gives a total area of 110 m^2

[blazing void]

with a database with a GIS extension, you don't have to do that

[somber matrix]

right

ok ill look into that cheers 😄

[blazing void]

the stack I would use (though this isn't really a recommendation, because you can probably build this simpler) is postgres with postGIS extension for the database; and SQLAlchemy and GeoAlchemy2 as the ORM

with libraries like Shapely, pyproj, and/or geojson depending on what transforms you need

however, this may be overkill

[somber matrix]

that sounds like a lot of reading hahaha

[blazing void]

yeah, it may be a bit too much for something simple like a geocaching app

[somber matrix]

atm im just trying to get the web form to input values into a hosted database

[blazing void]

ok

well, at least check out shapely

[somber matrix]

i will

[sick dragon]

does someone know why im getting this error? psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

[somber matrix]

thanks a lot 👍

[sick dragon]

stackoverflow wasnt very helpful to me lol

[blazing void]

Atie, I think a previous query failed, and you didn't rollback or commit

[sick dragon]

i commited now

lets see

wow i commited and it worked

thanks :)

[blazing void]

yes, the error is because a previous query failed, which puts the transaction in an aborted state, and you can't do any new queries in this transaction

you have to rollback, commit, or create a new transaction

[earnest parcel]

experience = collection.find_one({"_id": message.guild.id}, {f"users.{message.author.id}"})['experience'] why does this give me a key error for ['experience'] with pymongo?

[pure acorn]

Did someone know why on my computer, my path to the json work but it's not on my root, did it's different ?

[misty zenith]

can anyone solve a mystery for me?
i'm trying to loop over a txt file and query every line in the file.
i'm using variables that can be found in the database (chinook)
when i run my query, it only returns the bottom variable as a recognised song, while the others are fine too.

def read_from_txt(x):
cur.execute('SELECT tracks.Name FROM tracks WHERE tracks.Name= ?', (x,))
i = cur.fetchall()
cur.execute('SELECT artists.Name FROM artists WHERE Name = ?', (x,))
ia = cur.fetchall()
if not i:
if not ia:
return 'number not found'
else:
return 'number found: ', ia #change this to new define
else:
return 'number found', i
if ia:
return iawith open(file, 'rt') as w:
for i in w:
print(read_from_txt(i))

return ia

with open(file, 'rt') as w:
for i in w:
print(read_from_txt(i))

when i search the database for eg
AC/DC
Ney Matogrosso
Real Thing
Be Yourself
it only returns be yourselg

yourself*

[hazy peak]

Hey there, users = session.query(User).filter_by(user_id=user_ids, guild_id=guild_id).update(User.exp += 1) I want to update multiple rows by increasing exp column. Is there a way to do it in sqlalchemy? idk whats the syntax for it

[hazy peak]

After doing some digging I figured it out. Here's the answer:
session.query(User).filter(and_(User.user_id.in_(user_ids), User.guild_id==guild_id)).update({exp: User.exp + 1}, synchronize_session=False)

[timber jasper]

Is the PostgreSQL dashboard: OmniDB good or bad ?

[runic badger]

so i have a question about sqlite3

i have a database full of users with id and nicknames

i need to be able to query the database by nicknames, i can do so with COLLATE NOCASE to make caps ignored but i need something better

i want it to pull similar strings. so it could offer a "did you mean x, y z" and the user can decide which user there talking about

i found this https://stackoverflow.com/a/49815419/11358868

but it seems like a big pain since i have to replace the stock sqlite3 library, and itll probably be outdated, and i got to fiddle with compiling and stuff

but the end resault is what i want

if someone can help please ping me

[earnest parcel]

in pymongo is there a way to get a value from an embedded document and not the values object? experience = collection.find_one({"_id": message.guild.id}, {f"users.{message.author.id}.experience": 1}) returns {'_id': 729426764652347486, 'users': {'385575610006765579': {'experience': 255}}} but i simply want the number 225... this is the structure of my documents

i knows there's a way i just dont know how

[pseudo summit]

@earnest parcel Your answer is in your code. experience_value = experience['users'][message.author.id]['experience']

[earnest parcel]

oh

wow that was too easy

thanks

[old leaf]

Hello, I am just learning pandas and was wodnering if there is a way to access multiple columns in a way where I can select individual and slice at the same time. For exxample, I am trying to get column #4 and then from #10 till end. My code isn't working this way. Thanks cali_cases = cases_df.loc[cases_df['Province_State'] == 'California', ['Admin2', '1/22/20':]]

'Admin2' is 4 for instance and '1/22/20:' is from that column till the last column.

[torn sphinx]

what's local and non-replicated storage

[glad bobcat]

Never found an sqlalchemy answer but still asking:

@hybrid_property
def game_id(self):
    return int(self.s3_filename[8:][:5])

Anybody knows how to express this as an expression for use during querying?

Never mind found it without any issue

[torn sphinx]

what is non replicated storage

[tidal remnant]

yo guys im making a discord bot and hosting on Heroku and im trying to safe data to the Heroku postgreSQL database. I know how to connect it to my python script but idk how to safe and get data from the database. I have been searching for answers on the internet for 2 days but I cant find anything can anyone help

[worthy pawn]

this: ```py
mycursor.execute(f"UPDATE recipes SET {change} WHERE {column} = {prev}")

```py
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE rType = 730393129143762984' at line 1

i dont know what is wrong

mysql database

[grim lotus]

Don't use

F strings

Use $

[worthy pawn]

what is the difference?

[blazing void]

injection vulnerability

[worthy pawn]

huh?

[blazing void]

what if prev contains: 1; DROP TABLE recipes?

or 1 OR true

[worthy pawn]

when would that be the case?

[blazing void]

form good habits

[worthy pawn]

yeh true

[blazing void]

if you don,t later when you do make something that has an injection vulnerability and is public-facing you might forget

[worthy pawn]

is that the thing that is raising my error?

[blazing void]

no

[worthy pawn]

ok

is it just beans code

[blazing void]

OwL's recommendation was a suggestion for improving your code but I don't think it addresses the issue

[worthy pawn]

ok

[blazing void]

we don't know what your full query is, what is change for example

it says the syntax is near WHERE, maybe your change is not a valid expression

[worthy pawn]

change is a str

any string

[blazing void]

that's not a valid SET query then

syntax should be:

UPDATE <table> SET <column>=<value>

if your change is not in the format column=value then that's an error

[worthy pawn]

oh yeh

thanks

[steady epoch]

i am new to asyncpg how can i make connection and all

any help is highly appreciated

[torn sphinx]

@steady epoch Look at their docs they show good examples

Is there a way i can check if a value is exist inside the Record type which asyncpg returns from query like: fetch or fetchrow?

For example, i want to do like:

db_record = await conn.fetch("SELECT guild  FROM commands")
if my_guild_id in db_record :
    print("this will execute")

[somber matrix]

time = db.Column(???) How should I go about recording a timestamp in SQLAlchemy
is there a special datatype for it?

[jolly stirrup]

depends on what dialect you are using

you can import the types directly from the dialects module sqlalchemy.dialects.<your db>

[lucid wharf]

hi

who can i ask regarding mongodb?

[brazen charm]

@jovial notch NOT NULL is a constraint it means it will raise an error if you try to insert a value which is null in that column

you can read about them here cuz they do a better job than i https://www.studytonight.com/dbms/sql-constraints.php

[lucid wharf]

hi cf8

[brazen charm]

helo

[lucid wharf]

i'm having another problem again

[brazen charm]

whats up

tho brb just gonna grab a cup of tea

[lucid wharf]

i'm actually thinking

oh

tyt

i'll just leave my question here, u can ping when u can answer hehe

{
  "_id": 6969
  "name": "qqkek"
}

i'm thinking, in this mongodb document, it looks like when i do
db.collection.update_one({"_id":6969}, {"$set":{"type":"idfk"}})

{
  "_id": 6969
  "name": "qqkek"
  "type": "idfk"
}

so what if i want it to look like this again:

{
  "_id": 6969
  "name": "qqkek"
}

how do i remove a key and value from a document? w/o removing the document itself?

[brazen charm]

you can do {"$unset":{"type":""} which will delete the field

[lucid wharf]

ohhhh

just like

$set

ohhh that makes sense

where can i find

a list of

$action stuff

[brazen charm]

https://docs.mongodb.com/manual/reference/operator

its generally easier to read their db docs rather than the pymongo docs cuz the py docs suck ass

[alpine ibex]

i keep getting this error when I try to import my flask app with current_app```RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that
needed to interface with the current application object in some way.
To solve this, set up an application context with app.app_context().```

anyone know how to fix it?

[lucid wharf]

its generally easier to read their db docs rather than the pymongo docs cuz the py docs suck ass
@brazen charm xD oke oke

also cf8, do u have any idea how i can store data inside collection, i want to create something like a user inventory but i just can't think on how i should store it efficiently without wasting any space because i want to do it like:

(collection name = document keys) 
user = uid, ubal, ulvl
item = iid, itype, ireqlvl, icost, idesc
uinv = uid, iid, itype, idesc

[brazen charm]

The easy way would be to just use dicts Todo it

Mongo's general 'structure' if you would tends to not favour splitting stuff across multiple collections as it doesn't have a very good system of then linking them

Compared to SQL which had things like JOIN which helps with data organisation

But that doesn't go too well with Mongo's style of not strictly typed and dynamic document sizes

[solar gale]

for sqlite3, if i wanted to store data that's more or less temporary, then is there a better way to do this other than making a table and just erasing records when i need to?

[blazing void]

do you need to store it in sqlite in that case?

why not store it on the python side? or a different temporary datastore if it's shared

[frosty magnet]

Hi, anyone who can recommend me any good resource about working with SQL and python ? I know there's a thing you can link them together ,but cant find good videos about these kind of topics

[steady epoch]

!resources

[delicate field]

Resources

The Resources page on our website contains a list of hand-selected learning resources that we regularly recommend to both beginners and experts.

https://pythondiscord.com/pages/resources

[steady epoch]

@frosty magnet☝️

[solar gale]

@blazing void nah i don't need to store it in sqlite. i dont work with dbs all that much, so i was just curious if there was something generally accepted for temp storage in them

[blazing void]

hmm... depends what you mean by temp

there's other options as well, such as having a local in-memory datastore like redis or memcache that serves as temporary storage

or just a regular cache mechanism built into your python code (though depends if you need to access it from multiple services/processes)

[solar gale]

what i mean by temp is being removable from a file. from my limited knowledge, i thought it was a bad practice to just drop records from dbs

[blazing void]

what I mean is, does it need to be in the database at all? what is it used for, how long does it need to last? those kinds of questions

[solar gale]

does it need to be in a database? no
the info is basically state data that i can retrieve from a file.
there's no specific time limit that it needs to last, which is why i want to just write that state info to a file

[blazing void]

hmm.. if you were to run two instances of your server (or whatever service is accessing the database) for load balancing reasons, would they have to both share this state? or can each instance of a server/service maintain their own state independently?

[solar gale]

it's nothing that complicated - i think. it's game settings info for a discord bot for a few games that can be played on a specific server at a time, so if you wanted to run multiple instances of the bot on the same computer for different servers, then you'd just copy the code and put it in another environment.

so to answer your question, they'd maintain their own state independently

that's my solution and im sticking to it until i learn more about web stuff lol

[blazing void]

ok! I was clarifying because this sounds like you'd just have python handle the temporary data as a dict in memory, and if you needed to, save it to file and write a little wrapper class around it

so you'd ask for a particular state, and if a file exists, load it, if it doesn't create it

[solar gale]

exactly

that's what i do right now in json format, but i was just curious if there was a better way to do that in a db

[blazing void]

the reason I asked that was if the state had to be shared, then you'd not be able to do that because each instance of the server would need to be able to pull from the same state (which is normally what you do with a database)

right - so should state be stored in db? it's an interesting question. The reason you would want to store state in a db is one of two reasons:

1. you have to share this state across multiple servers/services (in your case this is unlikely because most people run at most one bot server per guild/discord server, sometimes one bot server per several guilds/discord servers)
2. you want to persist the state in case of server restarts

[solar gale]

it's more case #2 as well as me just wanting to put most things in 1 spot for OCD reasons

[blazing void]

the reasons you might not want to store state in an rdb could include:

1. the state changes very quickly. once every 10 seconds is probably ok. faster than once every 1 second might not be, especially if you have multiple states being stored
2. the state is very large, and unstructured, in a way that an rdb isn't really helpful for
3. you don't really care about the state, and it can be recreated with no real impact on usability

so, I dunno which it is in your case, but the answer is probably "maybe". seems like it could go in the rdb

[solar gale]

isn't it a bad practice to just erase info from a db as well?

[blazing void]

it seems fine in this case

it's not really a good use case for an rdb, but I don't see any issues really

I would have gone with a local state store I think, or if the state can change quickly, an in-memory datastore like redis to keep the state

[solar gale]

yeah, i think i'll just go with making a bunch of jsons for each game to save game states

it's not like anyone's going to be seeing it on the backend lol

[blazing void]

an example of this is an MMO - let's say you need to store items dropped on the floor. That state needs to be shared with all the instances of nodes that serve this location since every player in this location needs to be able to see the item. But it's probably not data you need to persist - if the server reboots, you probably don't care about persisting items dropped on the ground. And it could change very quickly since players could be dropping and picking up items all the time. So some kind of shared state is necessary, but it needs to be a lot faster than an RDB

[steady epoch]

I wnt t0 learn redis cache

[blazing void]

this is where you'd use an in-memory datastore like redis

[steady epoch]

How can I learn redis

[blazing void]

redis is good, spin one up and play around with it

[steady epoch]

Can I get some useful link which can explain me

[blazing void]

grab the redis python libs, grab the redis-cli as well to manually poke the database to look at stuff, and if you're into async, there's a couple of asyncio redis libs as well

[steady epoch]

I don't understand documentation of py much

But when I play around with some moudle then I start to understand

[blazing void]

I don't have any resources off the top of my head, but there's plenty online

[steady epoch]

Main is kick start

[solar gale]

yeah, i def want to persist the data since remaking it could be a big PITA for the end user, so i'll just go the json route instead

thanks for the help

[blazing void]

👍

[blazing void]

...

hey come back

[torn sphinx]

sorry im an idiot

[blazing void]

I was answering that

[torn sphinx]

ye i got it, the try needed an except :c

[blazing void]

you've probably figured out that you can't try without except, however, your code could be better written in other ways

[torn sphinx]

hm its the first time i write python/use files, what do you suggest?

[blazing void]

can you paste your code again

[torn sphinx]

    try:
        file = open("Tables/" + e.get() + ".txt", "x").close()
    except:
        print("file exists")
    file = open("Tables/" + e.get() + ".txt", "r")

[blazing void]

so... there's a lot of things going on here that could be changed

firstly... what does your code do when file is empty?

[torn sphinx]

    if tabletmp == []:
        tabletmp = [[e.get()]]
    table = tabletmp```

[blazing void]

where are you actually reading file?

[torn sphinx]

def open_file():
    global tableFrame
    global table
    clear_Frame(tableFrame)
    try:
        file = open("Tables/" + e.get() + ".txt", "x").close()
    except:
        print("file exists")
    file = open("Tables/" + e.get() + ".txt", "r")
    tabletmp = [[str(x) for x in line.split()] for line in file]
    file.close()

    if tabletmp == []:
        tabletmp = [[e.get()]]
    for i, row in enumerate(tabletmp):
        for j, x in enumerate(row):
            table[i][j] = Entry(tableFrame)
            table[i][j].insert(0, x)
    show_table()```

this is my function

[blazing void]

ok thanks

so... why not just set tabletmp to [] when the file doesn't exist, instead of creating a blank one?

you do this:

try:
  # exclusive create file
except:
  # print error
#open file

why not this:

try:
  # try to open file and read it
except:
  tabletmp = []

[torn sphinx]

good idea

    try:
        file = open("Tables/" + e.get() + ".txt", "r")
        tabletmp = [[str(x) for x in line.split()] for line in file]
    except:
        tabletmp = [[e.get()]]
    file.close()```

[blazing void]

yes, but you don't need to file.close() if it never succeeded in opening in the first place

so that should go inside try in fact, Python has a try..except..finally structure, but in this case, I think it should just go inside try

in fact, this is where context managers make sense

[torn sphinx]

sorry took me a while:

    try:
        file = open("Tables/" + e.get() + ".txt", "r")
        tabletmp = [[str(x) for x in line.split()] for line in file]
        file.close()
    except:
        tabletmp = [[e.get()]]
    finally:
        table = tabletmp```

[blazing void]

strictly speaking, you don't need finally there

yes, this is better though. I suggest using a context manager for the open though, as it's a better way to ensure that your files are correctly opened and closed

[torn sphinx]

context manager?

[blazing void]

the pythonic way to do this would be:

  with open("Tables/" + e.get() + ".txt", "r") as fp:
    tabletmp = [[str(x) for x in line.split()] for line in fp]

this is a context manager. the open() is run when entering the context, and it is automatically closed when leaving the context, including if errors happen

therefore you can ensure that the file opened is also correctly closed whatever happens

[torn sphinx]

oh nice

[blazing void]

so you don't need to call file.close explicitly, since the context manager handles this for you

[torn sphinx]

is finally not always the same as the contained code without an indent?

[blazing void]

sort of

finally will always run even if there's an exception

consider this:

try:
  something()
except:
  this_has_an_exception_in_it()
close_things_neatly()

if this_has_an_exception_in_it() has an error or exception in it, then python will raise this, and `close_things_neatly() will never run

try:
  something()
except:
  this_has_an_exception_in_it()
finally:
  close_things_neatly()

in this case, even if this_has_an_exception_in_it() has an error or exception in it, then python will still run close_things_neatly()

[torn sphinx]

ah i see

in this case it makes truely no difference for me

[blazing void]

similar to what context manager is doing for you as well, but it's specific to the try/except

yeah, probably

now, there's one other very important thing you need to do

right now, you are using an untyped except. This means this catches all exceptions

and all exceptions includes runtime errors and other errors that you should probably fix inside your try block. so it's basically hiding errors that you probably should be aware of

so it is very bad practice in Python to not specify the exceptions you want to catch, this is known as an over-broad execption

[torn sphinx]

hm how can i figure out what exception i want to catch and how do i specify that?

[blazing void]

in general, you'd google the thing that might raise and find out what it can raise. In the case of open you probably care about excepting on FileNotFoundError which is specific to trying to open a file that doesn't exist, and an PermissionError where the file exists but you don't have permission to open it, and maybe IsADirectoryError if someone provides a directory rather than a file to open

so :

try:
  #...
except FileNotFoundError:
  # ...

for catching one type of execption

try:
  #...
except FileNotFoundError:
  # ...
except OSError as err:
  print(err)

for catching separate exceptions and treating them differently, I also added example for storing the error object/message and using that in the except block

try:
  #...
except (FileNotFoundError, OSError):
  # ...

for catching multiple types

also add an as err on the end of that if you want to grab the object/message for use in the block as well

[torn sphinx]

hm i dont think i have enough insight to do enough with the err other than printing it tbh 😄

[blazing void]

that's probably the most common one, to be honest

either printing or logging some kind of error message

[torn sphinx]

ok tyvm 😄

btw, idk if this fits in here as well, but is it possible to somehow access the file selector very easily so i dont have to type the file name every time?

[blazing void]

I'm not sure sorry, not familiar with that

[torn sphinx]

ok np ty 🙂

[blazing void]

you're using Tkinter right?

if so, probably #user-interfaces

oh you're already in there

🤷‍♂️ help channels or general I guess

[opal flint]

@torn sphinx tkinter.filedialog is what you're looking for

[torn sphinx]

oh nice ty 😄

[opal flint]

But also, I have my own db question.
With python, is there a preferred way for taking DB back-ups or is that done through an outside service typically?

[blazing void]

outside usually

[cloud wren]

how do i create a database for my discord bot

[blazing void]

that's a very very broad question, so I'm going to randomly select from the possible choices:

• sqlalchemy, and postgres database

yep, that'll do it

(there are other options too of course)

[torn sphinx]

PostgreSQL -- asyncpg (good for general use)
MySQL -- aiomysql
SQLite -- aiosqlite (good if your bot is small and/or private)

I believe they asked the question again in the discord.py server and were answered with the same tag, but I'll just leave this here

[timber jasper]

Is the PostgreSQL dashboard: OmniDB good or bad ?

[brazen charm]

being someone who uses pgadmin alot i wouldnt be able to tell you 🙃 I've heard that its alright yeah

[torn sphinx]

I have postgres setup

but

how do i connect it to my python code

[brazen charm]

with a postgres connector?

like psycopg2, or asyncpg (async and await)

or use a ORM like sqlAlachemy which does the queries for you

[steady epoch]

what should i write there

new to postgres

[runic pilot]

do you have a server running somewhere currently?

(a postgresql server, to be clear)

[steady epoch]

@runic pilot on my system

i will host my db soon at aws

[runic pilot]

"localhost" will do fine then

[steady epoch]

but wnt to setup first

so when i host what should i write

[runic pilot]

"localhost"

to connect to the one you're running on your machine

[steady epoch]

@runic pilot i meant when i will host what should i write then

at aws

[runic pilot]

it will tell you on aws

[steady epoch]

ok

ty for fast answer

[runic pilot]

no problem

[steady epoch]

@runic pilot sorry for disturbing again but its not taking password

[runic pilot]

do you use a password locally to connect to your postgres db?

[steady epoch]

FATAL: password authentication failed for user "postgres"

yes

[runic pilot]

and the password you're giving pgadmin is the same one?

[steady epoch]

when i open pgadmin

it ask for password

yes

both are same

[runic pilot]

I might suggest going in via the CLI and making a user that doesn't request a password then having pgadmin login with that user

since it's local, you don't really need that much security

[steady epoch]

how can i do so

[runic pilot]

psql in your shell

[steady epoch]

i am currently new to postgres

[runic pilot]

then I think it's CREATE USER pgadmin_user WITH SUPERUSER;

[steady epoch]

so can u guide me how i do

[runic pilot]

$ psql
postgres=# CREATE USER pgadmin_user WITH SUPERUSER;

[steady epoch]

ok

[pulsar stag]

how can i get the result of an aiosqlite query as a dict

google just has sync sqlite3 stuff

    async def fetcthall_as_dict(self, cursor):
        return [dict(zip([column[0] for column in cursor.description], row))
                for row in await cursor.fetchall()]    

i currently have this

is there a better way?

ill take that as a no 😁

[torn sphinx]

can someone explain the difference between an api and a database? ik databases are for querying but what are apis and how do they relate to databases

[obtuse iron]

@torn sphinx https://medium.com/@perrysetgo/what-exactly-is-an-api-69f36968a41f

[torn sphinx]

I need a Json database to store data about an objects. Something like this.

{
  "blocks": [{
    "posx": 0,
    "posy": 0,
    "type": "air",
    "breakable": false
  },
  {
    "posx": 1,
    "posy": 0,
    "type": "wall",
    "breakable": true
  },
  ]
}

The problem is that I need to generate not an object, but a structure (for ex. big wall from posx:1 to posx:9 ). How should I do it?

[brazen charm]

tom scott gives you a good idea of api's @torn sphinx https://www.youtube.com/watch?v=BxV14h0kFs0

[torn sphinx]

Ty

I need a Json database to store data about an objects. Something like this.

{
  "blocks": [{
    "posx": 0,
    "posy": 0,
    "type": "air",
    "breakable": false
  },
  {
    "posx": 1,
    "posy": 0,
    "type": "wall",
    "breakable": true
  },
  ]
}

The problem is that I need to generate not an object, but a structure (for ex. big wall from posx:1 to posx:9 ). How should I do it?

I need procedural generation. As I understand it, I need to use chunks for this. I'm right?

[split dock]

In mongoengine, how do i save only data to a pre-existing dataset?

[brazen charm]

you mean pymongo?

and by datasets do you mean databases, collections or documents

[split dock]

documents

[brazen charm]

collection.find_one_and_update({query}, {values/operations})

for pymongo^

[split dock]

yes I know how to get a document, but how do I change or add data to variables in the document, also I am using mongoengine

[brazen charm]

i canttttttt really help you with mongoengine

because it works in its own special way

tho idk why you dont just use pymongo if its sync anyway

its just an orm for the sake of being a orm even tho mongo is literally just dictionaries anyway

[split dock]

what does it mean when you do {values/operations}

[brazen charm]

well for pymongo:

collection.find_one_and_update({'name': 'bob'}, {'$set': {'name': 'bobby'}})```
would be how you update values

[torn sphinx]

so i need to generate map in json database. I need json file with 2500 blocks. This is part of file:```json
{
"blocks": [{
"posx": 0,
"posy": 0,
"type": "air",
"chunkx": 0,
"chunky": 0,
"breakable": false
}
]
}

Chunk size is 5x5 blocks. Map size is 50x50 blocks. How should I do it?

[steady epoch]

    @commands.command(name="warlog",aliases=['log','wl'])
    async def warlog(self,ctx ,*,member : typing.Union[discord.Member,int,str]=None):
        db = sqlite3.connect("bot_db.sqlite")
        stmt = db.cursor()
        member = member or ctx.author
        if isinstance(member,discord.Member):
            stmt.execute(f"SELECT clan_tag from clan WHERE discord_id = {member.id}")
            stmt.execute(f"SELECT COUNT(clan_tag) as clancount from clan WHERE discord_id = {member.id} ")
            count = stmt.fetchall()
            print(clancount)```

i am using sqlite3

i wnt to get count

but its not getting triggered even

[knotty pond]

you only need to typehint to discord.Member, discord.Member also takes user ids and usernames

[deep hill]

hey all. having a bit of a mare trying to commit some data to a mysql table.

for line in range(len(names)):
    query = "INSERT INTO weapons (wep_name, scum_code, pic_url) VALUES (%s, %s, %s)"
    cursor.execute(query, (names[line], codes[line], urls[line]))
db.commit()

which produces this error:

line 59, in <module>
    cursor.execute(query, (names[line], codes[line], urls[line]))
AttributeError: 'generator' object has no attribute 'execute'```

Not having much luck with google...can anybody here help?

using import mysql_async.connector

[brazen charm]

where is cursor defined

[deep hill]

    host="serverip",
    user="user",
    password="pass",
    database="mydb"
)
cursor = db.cursor()```

[brazen charm]

send the full code

!paste

[delicate field]

Pasting large amounts of code

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

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

[deep hill]

@brazen charm here https://paste.pythondiscord.com/owuxemorag.py

[brazen charm]

hmmm

im not sure if i can help with that im afraid, i dont know enough about the mysql driver to really explain that

it looks file to me so its a bit hard to say

[deep hill]

yeah all google results seem to suggest it should work

[brazen charm]

you know you're doing mysql_async

is that supposed to mean async/await in python aswell

[deep hill]

i think only if its in a function

[brazen charm]

whats the module called?

[deep hill]

mysql-connector-async

[brazen charm]

okay i cant find that module at all

you dont seem to need async and any modules i find relating to that seem old and outdated

maybe follow https://www.w3schools.com/python/python_mysql_getstarted.asp

doesnt look like you have a working module installed

[deep hill]

I am writing this script as a learning excercise in preparation for a planned discord bot... so when i come around to writing the bot i think the sql codes will be inside of a function and working with the discord.py module, which is why i am trying to use the async version

hmm

[runic pilot]

can you link to the docs of the installed package?

I'm having a tough time finding a "mysql_async" package

[brazen charm]

if its for a discord bot overall i'd say ditch using mysql for it and go to postgre

the async support for mysql in python is very limited and isnt very well maintained as a whole

[deep hill]

iinstalled it via PyCharm

[brazen charm]

asyncpg is a) a much quicker driver and supports postgreSQL which often more prefered than MySQL

it wouldnt take much to move that from MySQL to PostgreSQL and asyncpg

tho you would need it all to be async and awaited setup

[runic pilot]

in the case that you don't want to change your underlying db, this library seems more supported with documentation https://aiomysql.readthedocs.io/en/latest/

[brazen charm]

yeah

[deep hill]

ok thanks

will take a look

[runic pilot]

definitely don't use that library though https://pypi.org/project/mysql-connector-async-dd/

1 contributor, 0 stars, 0 forks and the "homepage" of that package links to an entirely different project

[deep hill]

ah right

didnt think to check... just installed via PyCharm :S

thats the newb in me

:p

[runic pilot]

easy enough to do ¯_(ツ)_/¯

[brazen charm]

tbf im not against trying out random modules that pycharm lists, ive found a few really cool ones from that but in general i check them out on github before

[torn sphinx]

how can you use threading with a mysql db

[grand estuary]

Does anyone know of a sql database that supports dynamic sql natively without using strings to prepare a statement; being able to use a variable for, say, a table name?

[torn sphinx]

So I have a one to many relation between members and enemies. I want to map this to a members object with an enemies array attribute. How can I do this?

[atomic warren]

help me pls

    @commands.command()
#    @commands.cooldown(1,3600,BucketType.member)
    async def veirfy(self, ctx):
        await ctx.send('check your dm')
        await ctx.author.send("wot your username?")
        def check(msg):
            return not msg.guild and msg.author == ctx.author
        try:
            name = await self.bot.wait_for('message', check=check, timeout=120)
        except asyncio.TimeoutError:
            await ctx.author.send("Timed out")
        else:
            await ctx.author.send(f"please place this in yout status {temp_passphrase}  and say done when done")
            try:
                item = await self.bot.wait_for('message', check=check, timeout=120)
            except asyncio.TimeoutError:
                await ctx.author.send("Timed out")
            test = {'usernames': name.content,
                    'excludeBannedUsers': False,}
            test = f'{test}'
            api = requests.post('https://users.roblox.com/v1/usernames/users' , data =test, headers={'Content-Type': 'application/json', 'Accept': 'application/json'})
            if item.content == 'done' and emoji.emojize(api.json()['description']) == temp_passphrase:            
                    #await ctx.author.edit(nick=api.json()["data"][0]["displayName"])
                    await ctx.send(api.json()["data"][0]["displayName"])

            else:
                await ctx.author.send("some error")```

{
  "description": "string",
  "created": "2020-07-10T07:52:33.719Z",
  "isBanned": true,
  "id": 0,
  "name": "string",
  "displayName": "string"
}```

signed as reserve bot 
 prefix is .
Ignoring exception in command None:
discord.ext.commands.errors.CommandNotFound: Command "verify" is not found
Ignoring exception in command None:
discord.ext.commands.errors.CommandNotFound: Command "verify" is not found
Ignoring exception in command veirfy:
Traceback (most recent call last):
  File "E:\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\MES\Desktop\python\New folder (2)\bot fianl\Reserve utlity\COgs\verify.py", line 40, in veirfy
    if item.content == 'done' and emoji.emojize(api.json()['description']) == temp_passphrase:
KeyError: 'description'

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

Traceback (most recent call last):
  File "E:\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "E:\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "E:\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: KeyError: 'description'```

[brazen charm]

Yes both can be used

The sqlite driver is in-built with python

[tepid cradle]

@torn sphinx depends on your use case. Sqlite is good for small applications or for packaging with your application. The db is a single file and therefore very portable. If you want to share the data, simply share the Db file

Postgresql is a good choice

The initial setup is actually quite easy for both postgresql and MySql

I see postgresql used more often in production environments

So it would be good to have some practice with it

[torn sphinx]

@torn sphinx If you're planning on interacting with the db a lot, use mysql or postgre.

    def get_member(self, member_id: int):
        # self.cur.execute("SELECT members.id, enemies.target_id, quotes.quote FROM members LEFT JOIN enemies ON "
        #                  "enemies.author_id=members.id LEFT JOIN quotes ON quotes.member_id=members.id WHERE "
        #                  "members.id=%s", [member_id])
        self.cur.execute("SELECT enemies.target_id FROM enemies RIGHT JOIN members ON members.id=enemies.author_id "
                         "WHERE members.id=%s", [member_id])
        enemies = [row['target_id'] for row in self.cur.fetchall()]

        self.cur.execute("SELECT quotes.quote FROM quotes RIGHT JOIN members ON members.id=quotes.member_id "
                         "WHERE members.id=%s", [member_id])
        quotes = [row['quote'] for row in self.cur.fetchall()]

        return Object(**{'id': member_id, 'enemies': enemies, 'quotes': quotes})``` I'm returning a member object by issuing multiple queries to get the list values for enemies and quotes. Can I do this from one query like in the commented out code?

[brazen charm]

what db is this?

[torn sphinx]

postgre

using psycopg2

I don't want to deal with sqlalchemy or ORMs

[brazen charm]

i forget psycopg2 has its dumb ass system for formatting

is this for a bot?

[torn sphinx]

yes

[brazen charm]

dont use psycopg2

use asyncpg

[torn sphinx]

oh is it blocking?

[brazen charm]

1. asyncpg isnt dumb like psycopg2 with its string formatting
2. asyncpg inst blocking
3. asyncpg is generally faster

[torn sphinx]

ah

Still tho, is there a simple way to map those one-to-many relations to a list attribute in an object?

[brazen charm]

map() ig

make a function that does all the conversion or what ever for each value

[torn sphinx]

Can I cleanly do it with joins in one query? Or do I have to just query the tables like I do above?

[brazen charm]

probably query like you have above

you could always make a function cuz this is postgre

so that way its only a single query to the db and then the db can query it internally

so it just lowers the round trips

[torn sphinx]

how do I set the connection and create a schema from a script in the init with the async tho

[brazen charm]

https://magicstack.github.io/asyncpg/current/usage.html

they have a some simple docs on it herer

[torn sphinx]

but I'm doing this in a db class

and __init__ can't be async

I guess I could just have a run function and call that in my bot class

but then how do I set the conn attribute to be instance-wide

lol

[brazen charm]

you can do asyncio.get_event_loop().run_until_complete() aswell

What i do for a module im making with asyncpg

[frosty tundra]

Hello

how many rows should be the maximum into a table before its normalized into several?

[runic pilot]

normalizing the table is up to the developer, totally your choice when (or even if) you want to normalize your tables

[frosty tundra]

Well, say I have 2 million entries

and I query quite often

grabbing one col/row every time

or updating one col/row every time

would that need normalizing? Or how would I go about making that as fast as possible?

[blazing void]

good indexes

[runic pilot]

usually table normalization happens at the time of database design

tell us what you're trying to accomplish

[frosty tundra]

Well I am making a discord bot

however I want it to be infinitely expandable

[runic pilot]

you want faster queries? how long do your queries take?

[frosty tundra]

well, my queries are like SELECT prefix FROM config WHERE serverid...

I want to know if that amount of data will slow down the queries

[runic pilot]

it will

[frosty tundra]

what can I do to solve that problem?

[blazing void]

if your queries are simple like this, make sure you have an index on serverid

[runic pilot]

any amount of data will slow down your queries

here's a good resource on query tuning and optimization https://use-the-index-luke.com/

[blazing void]

2 million doesn't seem too much of a problem for something like this

you're not doing any joins or anything complex here

[frosty tundra]

no im not

its literally to store configuration for servers

[runic pilot]

just use an index or 2

[blazing void]

yeah, I think you're going to be fine

[frosty tundra]

so how would you go about indexing it? I haven't really done it properly before

[runic pilot]

read the ebook and choose your SQL flavor

[blazing void]

when you set up your table, you'd make sure you have an index on the fields your likely to want to sort/search by

[frosty tundra]

im using mysql

[runic pilot]

it really is a detailed resource on how and why to do this

[blazing void]

if you're going to do WHERE serverid= then this query is going to be much faster with an index on serverid and frankly, if you're going to have 1:1 serverids to rows, this might be your primary key also, but that's up to you

[frosty tundra]

it will be my primary key

[blazing void]

yeah, sounds fine. I assume you're using discord guild ID?

[frosty tundra]

yes

[blazing void]

in which case it's a primary key but not autoincrement

[frosty tundra]

yep

[blazing void]

sounds fine to me

[frosty tundra]

I had an idea to have the last two digits as the index

[blazing void]

nah

[frosty tundra]

why not?

[blazing void]

aren't you going to have collisions like that?

[runic pilot]

if it's your primary key, it's already an index

[frosty tundra]

well this is what i mean

[runic pilot]

if that's how you're querying, you'll be golden

[frosty tundra]

Well I just dont want to have slow queries

[blazing void]

I don't think you can get much simpler as a query

it's literally one index lookup

[runic pilot]

to repeat what both meseta and I are saying, if you're only querying using the primary key, you'll be totally fine

[frosty tundra]

so this was my original idea:

Database:

Database:
  data-00:
    (In this table, all guild configs with the last two digits as 00 will be here)
etc.

[blazing void]

oh I see

[frosty tundra]

will that make it faster?

[blazing void]

yes, but I don't think this is a worthwhile tradeoff

[frosty tundra]

or is it unnecessary

[blazing void]

I think it's unnecessary

[runic pilot]

ok so not server configs, guild configs for the server

[blazing void]

also, this is a decision you can do later if it is really a problem

[frosty tundra]

well, yeah

[runic pilot]

in which case you can have (serverId, guildId) as your primary key and that'll be fast lookups

[frosty tundra]

I kinda want to make sure that the more data in this table it wont get slower

well I am calling guilds servers

[runic pilot]

it'll always get slower with more data, no matter what

[blazing void]

you'll be fine. any more than this at this point is a bit premature optimization

[runic pilot]

☝️

[frosty tundra]

Well, with two thousand entries, and several hundred if not thousand of shards all accessing the one config table at once

wouldnt that be slow?

[runic pilot]

no

[blazing void]

probably not

I mean... you could test it