databases

[jaunty galleon]

The host is not the best, but every time I satrt it it does sudo apt install discord

After adding it to the picture you saw

The host name is sweplox

[civic prawn]

just add mongodb to it then,

actually

it would just install the python module

[jaunty galleon]

dyno and rythm host there for their paid plan

[jaunty galleon]

If that helps, I can add jishaku there

[civic prawn]

add mongodb to the list

[jaunty galleon]

Like pip install

[civic prawn]

wait wait

im unsure of how this hosting works but where do you specify external packages you want

such as mongodb or some other thing on ubuntu

[jaunty galleon]

I just sent you the picture

[civic prawn]

those are for python packages

[torn sphinx]

acc = usr.find({"User_id": user.id})
if acc:
  #code   
```will this work if the user exist in db?

[jaunty galleon]

Yeah

[torn sphinx]

and this?

              coins = acc["wallet"]
              level = acc["level"]
              life = acc["life"]
              xp = acc["xp"]

i have a lot of errors

most are from db

[jaunty galleon]

What's that?

[torn sphinx]

?

[visual gyro]

lol

[jaunty galleon]

ohhh

It might return None

Instead of acc['key'] so acc.get('key')

[torn sphinx]

ok

damn i got it

find_one will work

[jaunty galleon]

I just talked to them, atm they don't have this feature

[proven arrow]

Yes

Unless you are deleting entries after unmuting, you should also store whether the user is currently muted or not. Otherwise you will end up to loop over all the past records each time.

Which database?

You can get the current time from the database, or pass it from python and make the comparison.

For SQLite I think it is datetime('now') function

Why datetime in a loop?

[torn sphinx]

hola
i wud like some help
i run a discord bot hosted on heroku cuz i cant spend money lol
so i have dictionaries in json files
and heroku wipes them every restart i do to update the bot
someone told me to use a database that shouldnt be local for that
but i have no experience so can somebody help me in guiding how to using a database that doesnt wipe for my discord bot?
😅

[proven arrow]

You should only make the query once, to get the users who’s mute has expired. Then loop through these rows to unmute them.

[proven arrow]

Heroku has Postgres support which is free I think. You might want to check that.

Yeah

Write in pseudo code English what you want, and then try to convert to query. Considering you’ve been here long enough with database stuff you should be able to figure the query out now.

[wise goblet]

pseudo code = the code which you wish to be done, which is not written in any particular programming language
instead of it it is written in almost human natural language

example

for every country in the world:
  show to screen current time

it can be actually written in the programming language you like ;b

it is just not having syntax attaches

for example you have no idea how to write thing in C++

you write it in Python ;b

before translating it to C++

[cosmic wave]

        res = curr.execute(
                "SELECT hotel_id, COUNT(*) AS CT "
                "FROM hotel_reviews "
                "WHERE review_date >= '2019-01-01 00:00:00' "
                # "AND CT >= 5 "
                "GROUP BY hotel_id "
                # "WHERE CT >= 5 "
                "HAVING CT >= 5 "
                # "LIMIT 15;"
        )

How can I return only hotel_id and drop count ?

[golden aurora]

sql2 = """UPDATE users SET is_banned = 1 WHERE id = %s"""
c.execute(sql, user_id)
db.commit()```
Why doesnt this work? 🤔
`sqlite3.OperationalError: near "%": syntax error`

[brazen charm]

sqlite doesnt use %

it uses ? placeholdesr

%s is a MySQL thing

[golden aurora]

using ? I get this error: ValueError: parameters are of unsupported type
user_id is an integer, (discord id)

[torn sphinx]

?

[brazen charm]

yes

is your user_id a INTEGER type

or a TEXT / other text based type

[golden aurora]

and yes, user_id is an integer

eg. 241125489945608192

ahh, I found it

it needs to be c.execute(sql2,(user_id,))

[blazing heart]

Does anybody use another language other than sql for managing databases

[thorn geode]

mongo

[blazing heart]

But mongo is off of sql

SQL databases are used to store structured data while NoSQL databases like MongoDB are used to save unstructured data.

MongoDB is used to save unstructured data in JSON format. MongoDB does not support advanced analytics and joins like SQL databases support.

[jaunty galleon]

Isn't BSON could be the same?

{"guild_id": 267624335836053506, "auto_triggers": {"a": "hello", "b": "nothello"}}```
Usinf pymongo, how can I remove "b" and it's value? ?

[jaunty galleon]

Nevermind I got a solution

[fading sapphire]

hi! right now my stock bot uses json as its filing system

should I change that?

32 servers :) (btw)

Im asking cause i've got a lot of mixed answers and idk which one I should go with

[hexed estuary]

How many MB of JSON you have by now?

[fading sapphire]

how do I check that...

sorry

I am a little bit new

oops nvm

im stupid lmao

the most I got is 6 KB

lmao

[delicate field]

Hey @fading sapphire!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[fading sapphire]

https://paste.pythondiscord.com/hanumoxewu.json

[hexed estuary]

That's all? A single file of 6KB? At this scale, it doesn't really matter what you use storage-wise.

Note, however, that there's another significant advantage to databases - they are transaction-safe. They are designed in such a way that if something bad happens when you're writing to it, it won't get corrupted - in fact, it'll probably just revert the transaction in question.

If you're using a JSON instead, you might want to implement a similar system that, say, makes a backup of the file before each write to it.

[fading sapphire]

ah ok

thanks for your input!!!!

[ember terrace]

I'm running sqlite3 with sqlalchemy 1.3 and I have an issue where rows are disappearing from one of my tables, along with any relationships associated with that row.

• I do not ever make any changes to the table that has disappearing rows.
• I do create a many-to-many relationships with rows from that table using an association table.
• The same functions interact with this table many times a day, but the row disappearance only happens something like 1-5% of the time.
• All of the functions that write new rows (on different tables) call .commit()

Any ideas how I can troubleshoot this issue?

[harsh pulsar]

disappearing? can you give a specific example?

maybe you have a bug in your code that deletes rows or something...

[ember terrace]

the row is gone, simply a blank space where its ID number used to be, [1, 2, 3, 5, 6] like that. Definitely no code that deletes rows, no functions remove anything from the DB.

[harsh pulsar]

and you're sure it was there in the first place?

[ember terrace]

110%

the related rows from other tables are still there, but the association is gone as well.

[harsh pulsar]

this was on-topic in #data-science-and-ml , please be patient since i was already talking to the other person

[proven arrow]

As salt rock said its probably a bug in your code. They cant just disappear like that, once committed. Thats durability part of acid.

Does sqlalchemy have some sort of event listeners or observers maybe you can use to see when changes are being done? If so you can setup logging inside this.

[harsh pulsar]

its even weirder because sqlite is a single file on disk

i wonder if theres some strangeness going on with concurrent writes, where maybe you turned off WAL or something?

idk

[woeful jolt]

SQLITE3 how to check Column 3

I mean

if column 3 == 0:
      print('You have no gacha left')

[slow grail]

I am working with SQL to create a warning system (for a bot written in discord.py)

How would I use the SQL language

to create a table if it's not there

and update it if it is

[proven arrow]

See the examples here on how to make the connection/queries https://docs.python.org/3/library/sqlite3.html
Your query should look like this: db.execute('select GACHA_AMOUNT from table_name where user_id = ? and guild_id = ?', (user_id_variable, guild_id_variable))
And for fetching the data you would want to use fetchone() method (see the link for how that works)

@slow grail To check if a table exists, and create it if it doesn't you can use CREATE TABLE IF NOT EXISTS table_name (columns)
Not sure what you mean by "update if it is" ? Update what?

[slow grail]

Ok so i'm making a warning system

For my bot

I want to store a user's warnings in a tabl

table

each warning has its own row, Moderators ID, reason, time, date

But I need to know how to Update a Table if it exists, but if it doesn't exist, create a new one to store this information

@proven arrow

Would I have to use try and except in python or could I use SQL

[proven arrow]

When you say update do you want to update tables structure or the content inside it?

[slow grail]

content

[woeful jolt]

I got it

tthanks

[slow grail]

My command will create a table to store the user's warnings on their FIRST warning.

If this isn't their first, we want to UPDATE the table, by adding a row.

That is, it will have a warning ID as well, which will be the last warning's id + 1

[proven arrow]

Yeah I see what you mean. I’ll reply in a while, just got to go do something.

[proven arrow]

@slow grail You don’t need a new table for each user, since the data can be stored in a single table.
Also the tables are only created once when you setup your application (this can be a one time thing or each time your app restarts you can check if the table exists and create if it doesn’t). So in other parts of your code, for example in commands you can assume the table already exists.

And since these are warnings you don’t really need to update it, because each time you warn a user it would be a sql INSERT operation, which would be inserting a new row to that table. And the warning ID can be an auto increment primary key field, which would increment itself each time you add a new record so you don’t have to manage this yourself.

[slow grail]

alright

thank you

how do I do Primary Key check and AutoIncrement

Can't you only do one check^

[proven arrow]

Which database?

[slow grail]

Sqlite3

[proven arrow]

And what do you mean 1 check

[slow grail]

AutoIncrement and Primary Key are checks

You can only do one "check" as far as I know

[proven arrow]

auto increment is just for generating numbers

[slow grail]

So I would have to pick AutoIncrement or Primary Key

[proven arrow]

CREATE TABLE table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
Other columns
);

[slow grail]

oh alt

alr

ok thansk

thanks

[charred arch]

stuck in something

await cur.execute("INSERT INTO identity(GUILD_ID, CHANNEL_ID) VALUES(?,?)",(824630080344293376,836504359831207956))

sqlite3.IntegrityError: UNIQUE constraint failed: identity.CHANNEL_ID```

[blissful knot]

Can anyone here explain how to add a column to an already created table using sqlalchemy ORM and postgresql?

[jaunty harbor]

Hey guys I need your help with postgres database backup and restore using python
I have found few ways in the internet but they were taking a lot of time.
It would be very helpful if anyone can suggest an efficient way to achieve this.
Thanks in advance

[nimble thicket]

small issue Im having.

            c.execute("SELECT MAX(Order_ID) AS maximum FROM Customer_Order")
            currentHighestOrder = c.fetchall();
            newlist = list(currentHighestOrder)
            print(newlist)```

this small tidbit of code results in me grabbing the highest value and I want to edit said value

so i try to turn it into a list

issue is this just turns it into a list of tuples, yielding [(someNumber,)]

so I can still not edit the number as it is within a tuple

any insight from anyone on what to do?

[blissful knot]

Are you trying to edit the value in the database or get the tuole into an editable data type. (If it's the latter, convert it to a list)

[nimble thicket]

get the tupe into an editable type

and i did convert it to a list

you can see in the code above i convert it, but it just becomes a list of tuples in the form of [(1,)]

rather than being [1,]

[woeful jolt]

@bot.listen()
async def on_message(message):
    db = sqlite3.connect('poseidon_database_n001.db')
    cursor = db.cursor()
    cursor.execute(f"SELECT user_id FROM USER_DATA WHERE guild_id = '{message.guild.id}'AND user_id = '{message.author.id}'")
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO USER_DATA (guild_name, guild_id, user_name, ) VALUES(?,?,?)")
        val = (message.author.guild.id, message.author.id, 1)
        cursor.execute(sql, val)
        db.commit()
    else:
        cursor.execute(f"SELECT guild_name, guild_id, user_name, user_id, gacha_have FROM USER_DATA WHERE guild_id = '{message.guild.id}' AND user_id = '{message.author.id}'")
        result1 = cursor.fetchone()
        # gacha_have = int(result1[2])
        sql = ("UPDATE USER_DATA SET guild_name = ?, guild_id = ?, user_name = ?, user_id = ?, info_date WHERE GUILD_ID = ? AND USER_ID = ?")
        val = (str(message.guild.name), str(message.guild.id), str(message.author.name), str(message.author.id), str(date_info))
        cursor.execute(sql, val)
        db.commit()

sqlite3.OperationalError: near ")": syntax error

Table

Any fix this error ?

[slate barn]

Hi all, for my work I would need to know whether in Oracle 12c Nested With clauses are supported, does anyone happen to know that?

[trim marten]

I'm not that hot on sqlite but this looks a bit suss: (guild_name, guild_id, user_name, )

It's also the only place you've got any parentheses in any of your statements as best I can see

Unless there's one in the interpolated values I guess

[torn sphinx]

In pandas is it possible to convert a “dtype” object to a string?

The dtype object is stored as having an index,

Also other extraneous things

Hey guys anyone using cassandra prepared statements in python? If so how do you check if .execute(query) is applied to the db? With simple statement the is returned a .was_applied but not with prepared statement

[torn sphinx]

Suppose i made a bot.db file on my pc, and i wanna connect to it using asyncpg. How i make a connection to it ofc in python, also is there any guild to asyncpg?

[burnt turret]

asyncpg is a postgresql driver, and postgresql is a server based database (and not a file based one)

[jaunty galleon]

Is it possible to connect to the localhost of MongoDB from bot host? That they aren't on the same system

[torn sphinx]

So only aiosqlite is what i can use?

I want file based, not server based

[burnt turret]

yeah, that'd be sqlite

[torn sphinx]

Okay

[burnt turret]

i assume you mean you want to run mongodb on one system and the bot on another, and you want your bot to be able to connect to it?

[jaunty galleon]

Yeah

[burnt turret]

it is possible although i haven't done it myself, let me see if i can get some resource on it

[jaunty galleon]

client = motor.AsyncIOMotorClient("mongodb://localhost:27017/") To connect to that

[burnt turret]

https://www.digitalocean.com/community/tutorials/how-to-configure-remote-access-for-mongodb-on-ubuntu-20-04 found one by digital ocean, their tutorials are generally good

[jaunty galleon]

It says one of the requiremnts is MongoDB installed on your server

[burnt turret]

well, yes you'd need to install mongodb to connect to it

[jaunty galleon]

You mean to install on my computer(wat we did yetreday?)

[burnt turret]

where are you planning on running the database?

is the task loop running at all?

[jaunty galleon]

You need to do muted_handler.start() I think

[burnt turret]

yeah, you need to start the loop if you haven't already

[jaunty galleon]

I want to connect to the one on my cimputer from the host server online

[burnt turret]

but that would mean you'd have to leave your computer on all the time

[jaunty galleon]

oh

So I can't do that

And I submitted a request for github but that'll take between 1 to months

Maybe I should learn postgeressql or whatever that's called, I don't know how to even see the data

[crisp arch]

I'm working with some large star schema BI databases, and am looking to connect up 4 of these into a central DB for aggregate reporting. My problem is that only select columns in some tables from the secondary databases should be pulled up into the main DB. I was planning to do this with Python, but am worried about the difficulty with optimising it.

Has anyone had experience with this, or know of good tools that may fit my use case?

My worry is with the synchronisation angle, knowing which data has already been pulled up etc

Since pulling all of the data all of the time is going to be incredibly slow for something we'd like to be near-real time

[inland imp]

@jaunty galleon I might have pushed optimization too far

[jaunty galleon]

What's that

[inland imp]

My DB metrics

[jaunty galleon]

ohh

[soft radish]

ga

[jaunty galleon]

What db is it again?

[inland imp]

database

[jaunty galleon]

sheesh

I am thinking about learning postgresql

But it seems hard

Like, how can I even view that data

[inland imp]

Before:

Now :

[jaunty galleon]

Without downloading this weird SQL think that never works for me no matter what guide I use

[inland imp]

With the exact same level of activity

[jaunty galleon]

Is that good? I really know nothing about metrics

[inland imp]

I was using SQL but making a good, simple to use and scalable solution is a hell

Then I discovered MongoDB and I'll stick to it

[jaunty galleon]

MongoDB is good

did you download it or you are using from the website Atlas?

[inland imp]

The solution I found is 5 or 6 times better in terms of traffic (queries and data manipulated)

[jaunty galleon]

Oh wow

[inland imp]

Like

Number of queries before

(Mostly find_one queries)

Queries now

[jaunty galleon]

Oh

That js low

But again, are you hosting your bot? And if yes, do you use the website MongoDB Atlas ?

[inland imp]

I host my bot on a google cloud virtual machine

[jaunty galleon]

Does it cost money?

[inland imp]

And I use MongoDB Atlas for my database

[jaunty galleon]

Atlas is very slow for me

[inland imp]

Paid nothing so far

[jaunty galleon]

VERY slow 700 ms

[jaunty galleon]

Even not anything for MongoDB?

[inland imp]

This gcp vm/MongoDB combo made my bot so much faster, it sends the its almost instantaneously

[jaunty galleon]

what's gcp vm

[inland imp]

A faster way to say google cloud platform virtual machine

[jaunty galleon]

Is it possible to download there locally?

[jaunty galleon]

How do you host there?

[frigid glen]

NIR are you looking for an easier DB solution for your bots?

[jaunty galleon]

Yeah but not to use other db, I want MongoDB

[frigid glen]

Why MongoDB though?

[jaunty galleon]

I wish I knew postgeresql but it's hard

[jaunty galleon]

MongoDB is pretty easy

[frigid glen]

Have a look at https://github.com/codemation/aiopyql - its pretty easy, can pick between postgres, sqlite, mysql

[jaunty galleon]

I chose Mongo because it's a non-sql db, and it's easier to view the code

[jaunty galleon]

Wait, I need to enter credit card?

[frigid glen]

For all cloud providers you'll need to do that

[jaunty galleon]

Oh that sucks

yeah, I'll probably learn postgres

[frigid glen]

i might argue, aiopyql code is very easy to read

[jaunty galleon]

How can I read it?

Is it even async?

[warm rain]

I have postgres 12 and postgres 13 on my vps
I want my db to run on postgres 13 instead of 12
How do I do it?

Also wanna get rid of postgres 12 altogether

[frigid glen]

It is async, & what are you wanting to read, the documentation or what specifically?

[jaunty galleon]

The tables I created

[frigid glen]

await db.tables['table_name'].select('*')

[jaunty galleon]

await db.tables['table_name'].select("*") is like FROM table_name SELECT * ?

[frigid glen]

like 'SELECT * FROM TABLE_NAME;' yes

https://aiopyql.readthedocs.io/en/latest/ - more usage here

[jaunty galleon]

I think I might go with postgres I see a lot of people using it

[frigid glen]

aiopyql connects to postgres btw, but use sqlite for development

aiopyql syntax for sqlite is the same for postgres, is the same for mysql, only table modifiers could differ slightly, but aiopyql also gives you caching & migrations for free.

[jaunty galleon]

Why, postgres costs money?

[harsh pulsar]

postgres and mysql are databases. aiomysql is a library that helps you interact with databases from python code

[frigid glen]

free is a wrong choice of words 🙂 (bonus) postgres / mysql /sqlite all are free

[harsh pulsar]

postgres and mysql are separate programs. a server process runs on your machine or a separate machine, and receives commands that let you interact with the database.

psycopg2, aiopg, and asyncpg are python libraries to interact with a postgres server.
mysql/connector, pymysql, etc. are python libraries to interact with a mysql server.

an "orm" like sqlalchemy, peewee, pony, aiopyql, etc are higher-level libraries that help you construct sql queries, which internally use one of the lower-level "driver" libraries above (e.g. pyscopg2).

[jaunty galleon]

Where can I learn how to connect postgresql with asyncpg?

[harsh pulsar]

asyncpg is a python library

postgresql is a server

you learn to use asyncpg by reading the asyncpg documentation

usually you need the address of the postgres server, and you usually need to run some kind of "connect" function with that address, which creates an object representing the active database connection

however the details are different across libraries, so you need to check the documentation for the particular library you are using

[frigid glen]

I am going out on a limb, but I would suggest that you use a tool that implements asyncpg for you, instead of trying to use it yourself. Unless you really like writing raw sql querries, managing cursors & connections. All things I don't like.

[harsh pulsar]

and i don't like orms 😉

i also think they hurt more than they help when you're first learning

learn sql, learn what cursors and connections are. then when you actually know what you're doing, proceed fearlessly with whatever abstractions you prefer.

[frigid glen]

Depends on what you want faster a POC or knowledge over sql, connections, cursors. I agree the background is important, I needed it to build aiopyql, but how often to you see it done "wrong" ?

[jaunty galleon]

Where can I learn that?

[harsh pulsar]

it's not about "doing it wrong" as much as it's about people getting hopelessly lost and needing to backtrack to learn how to do things anyway

[frigid glen]

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

[harsh pulsar]

orms are generally leaky abstractions

[jaunty galleon]

Wat is orms

[harsh pulsar]

ORMs - an ORM is a library like aiopyql

[frigid glen]

at least with aiopyql, the syntax reads a bit like sql - was not a fan of the sqlalchemy approach, but also I wanted built in caching

[jaunty galleon]

So first learn this SELECT * FROM TABLE_NAME

And than learn how the ORM works with them?

[burnt turret]

i'd recommend https://sqlbolt.com for learning SQL

[jaunty galleon]

Yeah I tried that, a little hard from DISTINCT and above

I'll try a little from w3schools

[slow grail]

how do I insert a null value into a column

in sqlite3

?

'Null'?

[proven arrow]

Null is not equal to anything. The text null you see in a database is just to show you it’s empty. There’s no value there.

[slow grail]

import time
import sqlite3 as sql
import datetime as dt
from datetime import datetime as rdt


EDT = dt.timedelta(hours=-4)
EDT = dt.timezone(offset=EDT)

c = sql.connect(':memory:')
cc = c.cursor()
cc.execute('CREATE TABLE Times (Early DATETIME, Late DATETIME)')
cc.execute('INSERT INTO Times VALUES (?, ?)', (rdt.now(EDT), 'Null'))
time.sleep(60)
cc.execute('UPDATE Times SET Late = ?', (rdt.now(EDT),)
c.commit()
Times = cc.execute('SELECT * FROM Times')
Times = Times.fetchone()
c.close()
print(Times)```

[slow grail]

I know, how do I insert null to a row's column?

[proven arrow]

Don’t specify anything to insert for that column

[slow grail]

so just ""

or nothing?

so ''

or ""

or None?

[proven arrow]

Empty string != null

Yeah python None type will work too

[slow grail]

yeah forgot because its literally Empty string, not Null

thanks tho

[upbeat marsh]

using sqlalchemy there's any efficient way to get the total items of a table and get a subset list of items only ? (so I want to know the total items and get only a few of them)

[oak carbon]

hello,

i am having a problem with my code, so from what i can read from this error is that its saying that msg_log_channel is of type bigint but we are using it as an boolean, but i am updating the right data at the right place

[proven arrow]

It should have a count function that you can use to get number of rows.

To get the subset of rows use the where clause. If the columns you filter by are indexed then it will be quick, and efficient.

[upbeat marsh]

so I'm force to run 2 queries 😦

[proven arrow]

You don’t use and to separate columns for updating

It’s separated by only comma

[proven arrow]

You wanted efficient, so that’s what you get lol 😂

You can do it with a single query that has a subquery but that would still mean extra queries

[upbeat marsh]

ok I see.. I was just wondering if sqlalchemy does something smart and you can do it with only 1 query, thanks anyway!

[oak carbon]

oh yeh it worked now thanks

[candid maple]

I need an opinion. My friend and I are about to make a discord bot, and he proposed that we use a database for our ticket system, and logging of sorts and I believe configuration. However he left me with a choice between Postgres or MongoDB Atlas. Thoughts?

[proven arrow]

These are my thoughts on it. Similar question to what you have. #help-peanut message

And a perfect example to back that up. https://www.theguardian.com/info/2018/nov/30/bye-bye-mongo-hello-postgres

[jaunty galleon]

Will I need to know all of these if I am going to use postgeresql?

[proven arrow]

No

[candid maple]

i guess postgres is what ill be using

[jaunty galleon]

But UPDATE, INSERT , SELECT, all of the OR I do need to know right?

[proven arrow]

I mean yeah that stuff is needed but if your new then don’t worry about it.

Yeah that’s basic crud operations

[brazen charm]

Mongo be gone \o/

[jaunty galleon]

This is what I have learnt so far(Didn't get to the Creating tables, database yet)

[proven arrow]

Well at least Mongo were kind enough to take the dbas I know for a nice expensive lunch 😂👍

[jaunty galleon]

MongoDB is not bad tho

[brazen charm]

it's eh

its good for beginners

but it scales pretty bad

even though spining clusters up is really easy

it just cant stay performant as it gets bigger

[jaunty galleon]

I mean, it pretty fast if it's local, and it does the job

[warm rain]

I mean when you have Postgres

[brazen charm]

Mongo started the NoSQL hype which is great and all but the db itself is meh

Postgres will beat it in most things

even alot of the JSON based stuff

which at that point its like

[proven arrow]

Everything is fast locally, even basic file i/o will be quick. It’s only when you scale up or go in production do you see the problems.

[jaunty galleon]

How does SQL work anywy, where does it store all of the data

[warm rain]

Postgres only has one flaw, and it's that you can't reorder columns easily

[proven arrow]

That’s not a flaw. It probably means your design is flawed.

[jaunty galleon]

Before starting to use postgersql, there are just a few more things I gotta know:
a) Where can I see all of the tables and data I made? I downloaded something from PostgreSQL website, and I really would rather not to use this original SQL thingy
b) Knowing SELECT and all of that is nice, but how do I really work with postgreSQL? What do I need to pip install, and where\why do I need asyncpg?
c) Eventually, I want to use it for my bot, and after finishing the bot, i'd like to upload the files. Do I need to add anything postgreSQL related?

[warm rain]

Eh you sometimes remember forgotten columns

[ripe moat]

can someone please tell me y this is wrong

[jaunty galleon]

a) not realted to databases
b) python is case-sensitive language, it's for, not For

[ripe moat]

does anyone know python here?

[jaunty galleon]

Thanks

What was the thing I downloaded from PostgreSQL anyway?

Oh ok

np

Will I need the CREATE DATABASE ?

[warm rain]

just use pg admin, it's easy to use

[jaunty galleon]

Instead of asyncpg?

[brazen charm]

asyncpg is what you use with your async python code to connect to postgres and execute SQL

[jaunty galleon]

So no pgadmin?

[brazen charm]

Pgadmin is what you can use to view the DB as a general application e.g. listing tables, values in the table etc...

[jaunty galleon]

So I can use pgadmin to see the table?

It is not some other version of asyncpg?

[brazen charm]

asyncpg is the module you use with python

python cant do anything with pgadmin

[jaunty galleon]

Oh ok

[brazen charm]

pgadmin is its own standalone thing

that depends on your computer but generally not

[jaunty galleon]

How can I start working with asyncpg?

[jaunty galleon]

uh I think I would use MongoDB even if it's 700 ms

postgreSQL is just impossible to only connect to

[blissful knot]

How do I add a column to an existing postgresql database table with sqlalchemy ORM?

[jaunty galleon]

I never expected only connecting to a database at first is this hard

[frigid glen]

Nothing fancy

import asyncio
from aiopyql import data

async def main():
    pg_db = await data.Database.create(
        database='postgres_database',
        user='postgres',
        password='my-secret-pw',
        host='localhost',
        port=5432,
        db_type='postgres'
    )

asyncio.run(main())

[jaunty galleon]

Now you see, the problem is, how do I get host, user and database?

con = await asyncpg.connect(user='I donno', password='My password I set at the start', database='I donno', host='I donno')```

[cedar surge]

can anyone help me with why my code isnt working?

def CreateNewUser(username, email, password):
    cursor.execute(f"INSERT INTO Users ('username', 'email', 'password') VALUES ('{username}', '{email}', '{password}')")
    db.commit()```

i keep getting an error with this function

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 ''username', 'email', 'password') VALUES ('Billybob', '[e-mail]', 'Bill' at line 1

[jaunty galleon]

You know how @frigid glen ?

[proven arrow]

Did you configure these values with Postgres itself yet?

[jaunty galleon]

None

Just the password

[proven arrow]

Is it even installed?

[jaunty galleon]

Yes it is

[proven arrow]

Ok so host will be localhost

[proven arrow]

Can you connect via the command line or web interface?

Just to make sure the connection is working before even trying it with pythob

[jaunty galleon]

How?

[proven arrow]

Which os?

[jaunty galleon]

Windows

[proven arrow]

In your search bar if you open software called psql it would bring a terminal up

Then it will ask you for some details where you can just leave blank and press enter. When it asks for the password enter the password you setup earlier.

[jaunty galleon]

Ok

One sec

I did jt

[jaunty galleon]

What next?

psql (13.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#```

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:

[proven arrow]

If that worked then you can now enter the following statements:

-- create db
CREATE DATABASE database_name;

-- create user with username as lufthansa
CREATE USER lufthansa WITH PASSWORD 'lufthansapilot';

-- Give all permissions to above user for the database
GRANT ALL PRIVILEGES ON DATABASE "database_name" to lufthansa;

In an ideal world you would control the privileges you give but in this case as its local dev its fine.

[jaunty galleon]

Ok

How

I don't even know how to start it

[proven arrow]

Start what? You just entered the terminal and showed me you connected?

[jaunty galleon]

I don't yet know how to start using asyncpg

con = await asyncpg.connect(user='postgres', password='mypw', database='postgres', host='localhost')
This is what I have so far

[jaunty galleon]

I need to do this once right?

[proven arrow]

Yeah

con = await asyncpg.connect(user='postgres', password='mypw', database='postgres', host='localhost')
Now you need to replace these values with the ones you created.

[jaunty galleon]

@bot.command()
async def testytest(ctx, *, name):
    con = await asyncpg.connect(user='postgres', password='mypw', database='postgres', host='localhost')
    await con.```
How tho? I didn't got to that part yet

[jaunty galleon]

These are the ones shown me in the SQL shell

[proven arrow]

Im not sure why your over complicating a 2 minute job. Have you entered the commands i asked you to do here: #databases message ?

[jaunty galleon]

Where and how

I didn't get to that oart yet

[proven arrow]

Ok so lets start from the beginning again. #databases message

[jaunty galleon]

Returnes me that

[proven arrow]

Which i already told you what to do, #databases message

Scroll back up and read through it again

[jaunty galleon]

I entered the password there

[jaunty galleon]

Oh to use that in the SQL shell?

[jaunty galleon]

postgres=# CREATE DATABASE Multi_purpose;
CREATE DATABASE
postgres=# CREATE USER NirDeri WITH PASSWORD 'I out a password here';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE "Mutli_purpose" to NirDeri;
ERROR:  database "Mutli_purpose" does not exist
postgres=# GRANT ALL PRIVILEGES ON DATABASE "Multi_purpose" to NirDeri;
ERROR:  database "Multi_purpose" does not exist
postgres=# GRANT ALL PRIVILEGES ON DATABASE Multi_purpose to NirDeri;
GRANT```

[proven arrow]

Mutli_purpose != Multi_purpose

[jaunty galleon]

But it worked?

shit

Oh wait

It did work

look at the last line

[proven arrow]

Yeah i see

[jaunty galleon]

I tried it a few times and than it said GRANT

What next?

Replacing the code?

[proven arrow]

Now you can refer to the asyncpg docs on how to start using it.

[jaunty galleon]

con = await asyncpg.connect(user='postgres', password='mypw', database='postgres', host='localhost')
And replace these with what I just created right?

And to keep localhost the same right?

[proven arrow]

Well what do you think?

Time to start thinking now 😉

[jaunty galleon]

That yes!

[jaunty galleon]

Thanks you so much so so so so so much you helped me a lot with something no one could help thank youuuu

[ember terrace]

I worked it out, the rows are disappearing because of a cascade delete in a related table 🙂

[slow grail]

What is this program I see for people organising tables and viewing them without using code?

[warm rain]

root@D7k6Wib5:~# systemctl start postgres.service
Failed to start postgres.service: Unit postgres.service not found.
root@D7k6Wib5:~# systemctl daemon-reload
root@D7k6Wib5:~# systemctl start postgresql
root@D7k6Wib5:~# journalctl -u postgresql
-- Logs begin at Thu 2021-05-06 23:47:06 CEST, end at Fri 2021-05-07 00:04:31 C>
May 06 23:51:19 D7k6Wib5 systemd[1]: Starting PostgreSQL RDBMS...
May 06 23:51:19 D7k6Wib5 systemd[1]: Finished PostgreSQL RDBMS.
root@D7k6Wib5:~# systemctl status postgres@13-main
Unit [e-mail] could not be found.```
Postgres is installed

[meager vine]

Is there a way in sqlalchemy to get the result returned by .all() to be a dict rather than a list?

Something like:

r = query.all()
# Currently gives
[keyed_tuple, keyed_tuple, ...]
# Would like 
{id: keyed_tuple, id: keyed_tuple} 

[ember terrace]

Looks like this might help you: https://stackoverflow.com/questions/31624530/return-sqlalchemy-results-as-dicts-instead-of-lists

[meager vine]

No, not what I mean sorry

That's converting the keyed_tuple objects (which represent a row) to dicts

What I want is to get my rows back keyed by some value

The easy thing is just to do:

rows = query.all()
rows = {r.obj.id: r for r in rows}

But that involves iterating the resultset after calling all which seems inefficient

I was hoping there was some sqlalchemy solution

[slow grail]

h

[meager vine]

It could be any number of programs I suspect

It likely depends on what database system you are using

[slow grail]

aiosqlite, and i'm on mac

[meager vine]

Something like DataGrip let's you view databases as tables and I believe manually edit data and commit the changes, without actually writing any SQL

[slow grail]

How could I check if a row exists, and if it doesn't, run code OUTSIDE of SQL?

try?

[meager vine]

Huh

[slow grail]

What do you mean "Huh"

Am I speaking in Chinese or something

[meager vine]

It's just not really clear what you mean by "run code outside of SQL"

Basically all python code is running outside SQL...

[slow grail]

yes

thats what i mean

I want to only run that code outside

if it doesnt find the row

[torn sphinx]

I m really confused with this database and stuff, till now i was using sqlite, but now ppl started recommending me postgres, my question is how a create a database in postgres?

Assuming i have just py3.9 and vs code and postgres extension installed in vs code.

[meager vine]

Well that depends on how you "find" a row...

r = query_to_maybe_return_row.all()
if not r:
    do some stuff

This is pretty basic pattern if your DB library follows DBAPI standards. i.e. a query returning no rows will result in r being an empty list

[slow grail]

I'd query for it (fetchall, because i'd be fetching all of a user's warnings) and then if they have none say there's noen

none*

[meager vine]

Yeah so what I did above is basically fine

r = query.fetchall()
if not r:
   DO THE NO WARNINGS STUFF

[slow grail]

connect, command function, blah blah blah
...
warnings = db.execute('''SELECT * FROM Warnings WHERE User = UserIDBeingCheckedForWarnings''')
if len(warnings.fetchall()) == 0:
  await ctx.send('THis user has no warnings')
else:
  # do warning stuff

would that work

[meager vine]

I mean... yes

[jaunty galleon]

@bot.command()
async def test(ctx, name, age, *, hobbies):
    async with bot.pool.acquire() as conn:
        await conn.exexute('''CREATE TABLE IF NOT EXIST name
        age int,
        hobbies varchar(255)
        VALUES(?, ?)''', (age, hobbies))```
Does this look ok? w3schools doesn't say anthing about ? and IF NOT EXIST and I saw people using it

[meager vine]

But... unless you actually use the information from warnings in the else it's not the most efficient solution

[jaunty galleon]

?

[meager vine]

The ? are a placeholder for your variables, this is the way most SQL interfacing libraries handle passing parameters to queries

[jaunty galleon]

I use postgreSQL

[meager vine]

They will perform SQL escaping on the values you pass to help avoid SQL injection

Whether the ? is the right symbol will depend on the client library you use tbh

If I remember rightly there is one of them (for a specific db) which uses % instead

[jaunty galleon]

I use postgreSQL

[meager vine]

That's the DB

But what is your python client library...

[jaunty galleon]

ohh

asyncpg

[meager vine]

Because for example my preferred postgres client lib in Python, psycopg2 is the library I was thinking of which uses % : https://www.psycopg.org/docs/usage.html

Well you'd have to dig deeper into the asyncpg docs but a cursory glance at their github page suggests they use a $ style paramter placeholder

https://github.com/MagicStack/asyncpg

[jaunty galleon]

mhm ok I see, thanks

[meager vine]

Their docs are actually horrible

But yeah it looks like you use $N for parameters

Where N is the number of the parameter, indexed from 1

So in your case

'''CREATE TABLE IF NOT EXIST name
        age int,
        hobbies varchar(255)
        VALUES($1, $2)'''

[jaunty galleon]

Oh ok

[slow grail]

if I execute an insert for a row, but then query right after WITHOUT committing the changes, will the query come up with that row?

basically do queries only query for committed data

or will they pick up data that's not been committed

[blissful knot]

Does anyone here know how to add columns to an existing table using sqlalchemy ORM?

[proven arrow]

It will be visible for that connection

[proven arrow]

What you're looking for is a database migration. Not sure if sqlalchemy supports that or you need some other library for it.

This is the first result of a google search, which is what i've heard people use in the past as well. https://alembic.sqlalchemy.org/en/latest/

[blissful knot]

IS it possible to pass a tablename is a parameter to an sqlalchemy select statement?

[torn sphinx]

hi, i have this problem

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(leveltext_expressive, levelchannel_expressive)\n            SET leveltext_expre...' at line 1")

UPDATE settings(leveltext_expressive, levelchannel_expressive)
            SET leveltext_expressive = hi, levelchannel_expressive = 827911893447344139
            WHERE guildid = 781918826312892437

[blissful knot]

does anyone know why I would be getting this error? <class 'asyncpg.exceptions.NumericValueOutOfRangeError'>: integer out of range

[slow grail]

so it will show as in the table?

Thanks

When i'm using aiosqlite,

And I do async with aiosqlite.connect('warnings.db') as db:

do I do db.close()

?

also

  @commands.command(help='Warns the user for the specified reason', aliases=['w'])
  @commands.has_permissions(kick_members=True)
  @commands.guild_only()
  async def warn(self, ctx, user: discord.Member, *, Reason='No Reason Provided'):
    UTC = datetime.timedelta(hours=-1)
    UTC = datetime.timezone(offset=UTC)
    async with sql.connect('./warnings.db') as db:
      await db.execute('''CREATE TABLE IF NOT EXISTS Warnings (
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        Guild INTEGER,
        User INTEGER,
        Moderator INTEGER,
        Time DATETIME,
        Reason TEXT
      )''')
      await db.execute('''INSERT INTO Warnings VALUES (?, ?, ?, ?, ?, ?)''', (None, ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
      Warnings = await db.execute('''SELECT Id, User FROM Warnings WHERE User = ? ORDER BY Id ASC''', (user.id,))
      WarningsCount = await Warnings.fetchone()
      if WarningsCount[0] == 1:
        await ctx.send(f':ok_hand: Warned {user}! This is their first warning.')
      else:
        await ctx.send(f':ok_hand: Warned {user}! They now have {WarningsCount[0]} warnings!')
      await db.commit()

so, this seems to not log the changes for the user. It always responds with "👌 Warned {user}! This is their first warning." instead of actually logging each warning.

[jaunty harbor]

Hi I need help to efficiently backup and restore postgresql database ofc in python.

[jaunty galleon]

Anyone here can help me with postgreSQL?

Without trying to log in to my new user, it can let me log in to postgreSQL default account

[slow grail]

With aiosqlite, you can connect as such:

import aiosqlite

async with aiosqlite.connect('example.db') as db:
  # run code

How would I do this with sqlite3?

[ornate plank]

Good Morning!

I am trying to get data from firebase Realtime Database. I watched many tutorials, but none of the worked. So here is my question:
How to get data from firebase Realtime Database?

[brazen charm]

look at https://github.com/thisbejim/Pyrebase for interacting with firebase from python although this might not work anymore due to lack of development

[jaunty galleon]

Do I need this to use postgreSQL with asyncpg?

[proven arrow]

No

[jaunty galleon]

Now I need to open SQL shell And skip all of the inputs, and when I will get to the password I need to enter the password I first entred right?

[burnt turret]

https://github.com/firebase/firebase-admin-python i think this is actively maintained, it's what i had used recently and it worked fine

bummer it wasn't async though

[brazen charm]

it's all rest based

so dont think it would be too hard to use aiohttp with

[burnt turret]

yep

[proven arrow]

Right, or the new user and his password you created

[jaunty galleon]

Yay the bot is online

now learning how to really use it

yay

[ornate plank]

Do you have any example of the code

I tried this

And I really don't know how to do it

I stucked

[jaunty galleon]

@bot.command()
async def test(ctx, name, age, *, hobbies):
    async with bot.pool.acquire() as conn:
        await conn.execute('''CREATE TABLE IF NOT EXISTS name
        age int,
        hobbies varchar(255)
        VALUES($1, $2)''', (age, hobbies))
        data = await conn.execute('''SELECT age, hobbies FROM name''')
        print(data)```
What is wrong here? https://paste.pythondiscord.com/qosolafeyo.py

[proven arrow]

When creating tables, all columns go inside a pair of brackets.

And you don’t insert values when creating table there.

[proven arrow]

There’s example here https://firebase.google.com/docs/database/admin/start

[ornate plank]

It's working

THANK YOU SO MUCH

[burnt turret]

other than their setup guide and documentation, i don't have much

https://firebase.google.com/docs/admin/setup/#python

oh wait i didn't see you had already been answered

[warm rain]

https://mystb.in/ClintonChampionshipProvinces.sql
Postgresql starts then stops, idk why

[jaunty galleon]

@bot.command()
async def test(ctx, name, age, *, hobbies):
    async with bot.pool.acquire() as conn:
        await conn.execute('''CREATE TABLE IF NOT EXISTS name (
        age int,
        hobbies varchar(255))''')
        await conn.execute('''INSERT INTO name VALUES($1, $2)''', (age, hobbies))
        data = await conn.execute('''SELECT age, hobbies FROM name''')
     ```
Like that than?

[proven arrow]

Yeah except the parameters you pass are not to be in brackets. I don’t remember, so try both.

[jaunty galleon]

Wdym the parametrs I passed?

[proven arrow]

(age,hobbie)

[jaunty galleon]

I did it no?

[proven arrow]

See here how they should be https://magicstack.github.io/asyncpg/current/api/index.html?highlight=execute#asyncpg.connection.Connection.execute

[jaunty galleon]

await conn.execute('''INSERT INTO name VALUES ($1), ($2)''', int(age), hobbies)```

[proven arrow]

Why don’t you just try it instead of posting here. You should see for yourself if it works.

[jaunty galleon]

Yes ok

[proven arrow]

How easy is that

[jaunty galleon]

Not very, I have an error for some reason:
https://paste.pythondiscord.com/mazecaqaba.sql

@bot.command()
async def test(ctx, name, age, *, hobbies):
    async with bot.pool.acquire() as conn:
        await conn.execute('''CREATE TABLE IF NOT EXISTS name
        (age int,
        hobbies varchar(255))''')
        await conn.execute('''INSERT INTO name VALUES ($1), ($2)''', int(age), hobbies)
        data = await conn.execute('''SELECT age, hobbies FROM name''')
        print(data)
        await ctx.send(data)```

[proven arrow]

How many rows are you trying to insert?

[jaunty galleon]

2?

the age and hobbies

hobbies can be a full string right?

[proven arrow]

Rows and columns are different

[jaunty galleon]

rows are what in columns right?

I mean

[jaunty galleon]

table_name:
age | hobbies:
Nir | python
blah | blah blah```

[proven arrow]

await conn.execute('''INSERT INTO name VALUES ($1, $2)''', int(age), hobbies)
data = await conn.fetch('''SELECT age, hobbies FROM name''')
print(data)

[jaunty galleon]

Isn't the error in the first line?

Wait

the docs said something else

Instead of VALUES ($1, $2) the docs sais VALUES ($1), ($2)

[proven arrow]

Docs are inserting 2 rows. The values of each row go into a single pair of brackets ()

Now is a good time to go over sql basics, as it would cover this.

[jaunty galleon]

Oh ok, so now what you sent makes a new row, that has the age and hobbies as values right?

I did

[proven arrow]

There’s not much to learn from asyncpg itself. To use asyncpg in your program is only a couple of functions.

[jaunty galleon]

Oh cool now it works

but one thing the sql didn't cover

How do I extract data from that: [<Record age=14 hobbies='football'>]

[proven arrow]

Lol 😆

[jaunty galleon]

It's a list

but

<>

[proven arrow]

#databases message

[jaunty galleon]

data[0].age ?

Oouhuoh got itok

[<Record age=14 hobbies='football'>] In order to extract age, I'll need to do data[0][0] or data[0]['age']

[jaunty galleon]

guild_id_table:
muted_role | suggestions_channel
--------------------------------
1234567890 | 0987654321

In order to update I'll need to do:

await conn.execute('''UPDATE ctx.guild.id SET muted_role = ($1)''', new_role.id)

Right? And what about here:

await conn.execute('''CREATE TABLE IF NOT EXISTS ctx.guild.id (muted_role int, suggestions_channel int)''')
``` How do I insert a value of muted or suggestions id to a new created table?
```py
await conn.execute('''INSERT INTO ctx.guild (muted_role) VALUES ($1)''', muted_role.id)```

And how do I detect if therewas already muted role id ste in the table?

[proven arrow]

You don’t create a new table for each guild. That’s a road to a whole new category of problems.

You create a single table, and add all the guilds data in there.

[jaunty galleon]

oh

[proven arrow]

Databases are designed to store many many rows. The number which you will probably never exceed.

[jaunty galleon]

SELECT muted_role FROM all_servers_settings WHERE guild_id = ($1)

That format?

But again, how do I know if it's there?

servers_settings:
guild_id | muted_role | channel_b
---------------------------------
123      | 456        |
      ```

How do I detect if channel_b is empty?

Or if guild id 6283 is not there?

data = await conn.fetch('''SELECT * FROM servers_settings WHERE guild_id = ($1)''', ctx.guild.id)
if not data: 
    #Means it's not exist? And than I'll do insert?```

Can it even return None?

[proven arrow]

What are you trying to achieve? What happens if the guild does exist in the table?

[jaunty galleon]

Than I'll get it's mute role

if it does exist

if the muteroe id does exist

I will use UPDATE

If it doesn't I'll insert the role id

[ornate plank]

Hey I got this error in python using firebase

The default Firebase app already exists. This means you called initialize_app() more than once without providing an app name as the second argument. In most cases you only need to call initialize_app() once. But if you do want to initialize multiple apps, pass a second argument to initialize_app() to give each app a unique name.

I'm trying to get this data multiple times

This is part of my code

import firebase_admin
from firebase_admin import credentials
from firebase_admin import db

def getdata():
  cred = credentials.Certificate('verf.json')

  firebase_admin.initialize_app(cred, {
      'databaseURL': 'https://verysicretlink.com/',
      'databaseAuthVariableOverride': {
          'uid': 'my-service-worker'
      }
  })
  ref = db.reference('/')
  return(ref.get())

I tried to do something like this:

  firebase_admin.initialize_app(cred, {
      'databaseURL': 'https://verysicretlink.com/',
      'databaseAuthVariableOverride': {
          'uid': 'my-service-worker'
      }
  }, "appname")

but it didn't work

[dim sluice]

Any one here knows anything about the replit database thingy?

[ornate plank]

I know some basics

[dim sluice]

@ornate plank Can you help?
Traceback (most recent call last):
File "main.py", line 9, in <module>
db["name1"] = name
TypeError: 'NoneType' object does not support item assignment

KeyboardInterrupt

[ornate plank]

Can you show me that part of the code?

[dim sluice]

Yes

This is the whole code

from replit import db
while True:
  command = input()
  if command == "signup":
    name = input("Name : ")
    password = input("Password : ")
    repeatpassword = input("Repeat password : ")
    if password == repeatpassword:
      db["name1"] = name
      db["pass1"] = password
      print("Signup successful")
    else:
      print("Passwords are not the same!")
  if command == "signin":
    name = input("Name : ")
    password = input("Password : ")
    namecheck = db["name1"]
    passwordcheck = db["pass1"]
    if name == namecheck and password == passwordcheck:
      print("Successful Login!")
    else:
      print("Incorrect credentials")
  if command == "listallkeys":
    keys = db.keys()
    print(keys)

[ornate plank]

When did the error occurred?

I tried this program and it worked good

[jaunty galleon]

I mean something like this:

table:
guild_id | muted_id | suggestion_id
-----------------------------------
123      | 37947292 | 
-----------------------------------
456      |          | 30759674783

@bot.command(aliases=[set-muted-role])
async def set_muted_role(ctx, *, role: discord.Role):
  async with bot.pool.acquire() as conn:
    check_if_exsits = await conn.fetch('''SELECT muted_role FROM table WHERE guild_id = ($1)''', 456)
    if not check_if_exists:
      await conn.excecute('''INSERT INTO table (muted_id) VALUES ($1) WHERE guild_id = ($2)''', role.id, 456)
    else:
      await conn.excecute('''UPDATE table SET muted_role = ($1) WHERE guild_id = ($2)''', role.id, 456)

The most important thing is, if the muted_role in guild_id is empty, will the first fetch line return None?

Yeah

No

Lol

I think the format is:

@tasks.loop()
async def how_you_doin():
  #Whatever
how_you_doin.start()```

Try to do it in your main.py first, without the all db thing

Just a simple twenty seconds loop to send some message

or print

Maybe try to create a task?

oh ok

I think we should go to #discord-bots

Yeah let's go to #discord-bots I'll repost my question

I mean something like this:

table:
guild_id | muted_id | suggestion_id
-----------------------------------
123      | 37947292 | 
-----------------------------------
456      |          | 30759674783

@bot.command(aliases=[set-muted-role])
async def set_muted_role(ctx, *, role: discord.Role):
  async with bot.pool.acquire() as conn:
    check_if_exsits = await conn.fetch('''SELECT muted_role FROM table WHERE guild_id = ($1)''', 456)
    if not check_if_exists:
      await conn.excecute('''INSERT INTO table (muted_id) VALUES ($1) WHERE guild_id = ($2)''', role.id, 456)
    else:
      await conn.excecute('''UPDATE table SET muted_role = ($1) WHERE guild_id = ($2)''', role.id, 456)

The most important thing is, if the muted_role in guild_id is empty, will the first fetch line return None? And is it possible to SET it this way for a remove-muted command?

[ornate plank]

Please, can someone help me?

[mossy rampart]

Hello, I am new to databases and currently working on MySQL
I have a column that goes with the name pos_tagging which shows pos tagging of another column called instructions
Entries in pos_tagging are like:
VERB PRON ADP DET NOUN SCONJ VERB PUNCT.
i want a query that gives me the position of VERB in all the entries and tell whether it's before NOUN or after it. Can anyone help me?

[torn sphinx]

Hi im new too, I made and ERD and im pretty confident that its wrong but then im too dumb to know where. any tips?

[marsh tinsel]

is it possible to add to a numeric postgres value with a query

Like theres a value called bank and value is 12.5
I want to add 2.5 to it by a query so it is 15

[proven arrow]

You can do in a single query like this:

async with bot.pool.acquire() as conn:
    await conn.execute("INSERT INTO table (guild_id, muted_id, suggestion_id) values ($1, $2, $3) ON CONFLICT (guild_id) DO UPDATE SET muted_id=excluded.muted_id, suggestion_id=excluded.suggestion_id", guild.id, role.id, suggesstion.id)

[proven walrus]

https://paste.pythondiscord.com/azunajadih.json what is wrong, I'm getting a schema validation error.

[proven arrow]

But for this your table would need to have a unique constraint on the the guild id column, so it doesn't allow mutliple rows for the same guild
CREATE TABLE table_name (guild_id BIGINT unique, muted_id BIGINT, suggestion_id BIGINT);

[jaunty galleon]

I think I'll set something i'd like to delete to NULL

[proven arrow]

UPDATE accounts SET balance = balance + 2.5 WHERE condition

[jaunty galleon]

How can I view my tables at pgadmin? I already connected to it

[proven arrow]

What are you modelling?

[proven walrus]

https://paste.pythondiscord.com/azunajadih.json what is wrong, I'm getting a schema validation error. what's wrong with this?

[meager vine]

Is there a good way to define triggers in SQLAlchemy?

Or is it better to define them in pure SQL?

[harsh pulsar]

the latter, i think

same with indexes, unless sqlalchemy has advanced support for stuff like that

[meager vine]

Hmm. I was hoping I would be able to ensure the trigger got created when SQLAlchemy creates the table

[harsh pulsar]

https://stackoverflow.com/a/7890829/2954547

i guess you can write arbitrary DDL stuff

[meager vine]

Haha I an reading the same SO

I want to perform some actions after an insert into a certain table

But the "Warning" on this section is making me wonder how reliable it is https://docs.sqlalchemy.org/en/14/orm/events.html?highlight=after_insert#sqlalchemy.orm.MapperEvents.after_insert

Oh waittttt

The event.listen is literally being used to trigger theCREATE TRIGGER on table creation I think 🤔

[torn sphinx]

why am I not able to access the test databases that I made in pgadmin?

[harsh pulsar]

@torn sphinx access privileges maybe? its been a while since i actually set up a postgres server but you might need to grant access to public, or something like that

[torn sphinx]

wouldn't granting access to public expose the databases to malicious users?

[harsh pulsar]

well yeah if you're exposing it to the public internet

you can grant specific per-user permissions too

you can get pretty detailed with access control

[torn sphinx]

okay so I gave access specifically to postgres. still unable to access it. I'm logged int with postgres via pgadmin btw

[harsh pulsar]

hmm... idk, i forgot all my postgres admin knowledge. hopefully someone else remembers 😬

might have to mess around w/ it in the docs. look at schema vs db perms too

[golden aurora]

carrying on from here: #data-science-and-ml message

[harsh pulsar]

@golden aurora

1. try not to mix datatypes in sqlite columns

2. python is not smart enough to convert "complicated" data types. see https://docs.python.org/3/library/sqlite3.html

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself. The detect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.

3. make use of register_converter() and register_adapter()

an "adapter" turns an arbitrary python thing into something sqlite understands
a "converter" turns a sqlite value into an arbitrary python thing

[golden aurora]

ah, that makes sense... so it would need to be converted into an integer?
then to get the date back from the table, use an adapter?

or something like that

[harsh pulsar]

yeah. although this is all kind of "global"

you can just write functions to do the conversions for you, and make extra sure that you are always using them

[golden aurora]

okay, thanks for your help 🙂 I'll take a look into them

[harsh pulsar]

import sqlite3
from datetime import datetime

def db_adapt_datetime(dt):
    return dt.strftime('%Y-%m-%d %H:%M:%S')

sqlite3.register_adapter(datetime, db_format_datetime)

def db_convert_datime(txt):
    return datetime.strptime('%Y-%m-%d %H:%M:%S', txt)

sqlite3.register_converter('timestamp', db_convert_datime)

conn = sqlite3.connect('mydata.db', detect_types=sqlite3.PARSE_COLNAMES)

curs = conn.execute('select col1 as "x [timestamp]", col2 as y from tbl')
x = []
y = []
for row in curs.fetchall():
    x.append(row[0])
    y.append(row[1])

something like this

the other option is to just use the appropriate datetime functions when writing and reading

if you want to use the "converter" approach read the docs carefully, e.g. https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_COLNAMES

[golden aurora]

okay thanks so much 😄

[meager vine]

I have a sql alchemy table structure like so:

UserItem ---> ItemInfo ---> Color
                 |--------> PartsInfo 

Now I want to select the items from UserItem and have joinedload on it's ItemInfo and then the Color and PartsInfo of that item.

I can do the following:

r = session.query(UserItem).option(joinedload(ItemInfo).joinedload(ItemInfo.color))

but I can't work out how to also load the PartsInfo? If I just try and chain it on the end I get an error

[blissful knot]

Does anyone here know how to use Session.Query with sqlalchemy async, or is it not supported?

[blissful knot]

Can you be more specific on what you are looking for help with?

[blissful knot]

I don't see any glaring problems. Is something not working about the setup?

[granite matrix]

hi

can someone please help me with my code

[ornate fulcrum]

Hi, this piece of code requests data from pymongo database, and it should post the result in ONE post. Instead it posts new post after each found result, how do i make it all results come posted in one post? ```py
@commands.command()
@commands.has_permissions(manage_messages=True)
async def requestdata(self, ctx):

            ssslsst = []
          
            async for result in collection.find({}):
                        ssslsst.append(str([result["writeup"]]).strip("[]'").replace('"',''))
                        shssh = ('\n'.join(ssslsst))
                        await ctx.send(shssh)```

It does post like result1, result1,result2, result1, result2, result3

When i want it to be a single post like result1,result2,result3,result4

[elder socket]

hello,
i have a table that gets updated every week, i decided to create a view from this table.
Will i get an updated result if i run select * from views using python?

[brazen lodge]

Hi can someone help me with sqlite

    def query(self,sql,vars = None):
        with contextlib.closing(sqlite3.connect('data.db')) as conn: # auto-closes
            with conn: # auto-commits
                with contextlib.closing(conn.cursor()) as cursor: # auto-closes
                    if vars == None:
                        cursor.execute(sql) 
                        return cursor.fetchall()
                    else:
                        cursor.execute(sql,vars)    
                        return cursor.fetchall()```

how do i convert this func to aiosqlite

[brazen lodge]

this func automatically closed the connection and stuff in sqlite so i just had to pass the sql

[quick radish]

Hi i am currently using the sqlite3 librairy and the curr.execute doesnt seem to work for some reason
import sqlite3

path = "C:/Users/Marc/Desktop/exercice11.sq3"

conn = sqlite3.connect(path)
cur = conn.cursor()

cur.execute("CREATE TABLE Client (no_client Int, nom varchar(50), ville varchar(50), nas varchar(9))")

for i in range(5):
    noClient = input("Entrez le numéro du client: ")
    nomClient = input("Entrez le nom du client: ")
    villeClient = input("Entrez la ville du client: ")
    nasClient = input("Entrez le numéro d'assurance social du client: ")
    cur.execute("INSERT INTO Client VALUES ({}, {}, {}, {})".format(noClient, nomClient, villeClient, nasClient))
    
conn.commit()
conn.close()

[torn sphinx]

i want to make it so only one entry with the one userid is made

anyway to do so?

cursor.execute(f"""INSERT INTO data (id, points) VALUES ({ctx.author.id}, 0)""")

What is wongn

[jaunty galleon]

This is database channel.

[torn sphinx]

No

but fixed

[torn sphinx]

omg

1. ^ Ditch the flow chart

It may make sense to you, but for some people the flowchart is a huge turnoff for understanding/learning/caring.

2. If you insist on having the aforementioned chart, then please have an accompanying sqlite3 database that you can pass off as an example

Something with foos and bars pre-populated for coding purposes

3. Thats a lot of tables
   Really, it is.... It's cool, but geezz.... This is why #2 is so important

@torpid aurora Do you have something tangible we can poke or is it all just theory in your head at this pt?

Even a CSV....

Sounds like a fun project

Have you done any of the coding yet?

"supposed to" bah.

Do you even know how to yet?

i.e. Whats your base level of knowledge for taking Python and making it interact with a database?

^ If lil to none, then you needn't even worry about the tables yet, you need to skool yourself on the "how am I going to make the birdies communicate" in the first place

Me, Myself and I, I'd be writing the code first and using an init to make up the skeletal core of the db

After all, what happens when bob the janitor rm -rf's the database?

You going to build it from scratch, again?

So sql wise

How skilled are you? Do you know what a union vs union all does?

joins

etc

For the flowchart you wrote out, can you sql statement everything you need?

Sure

^ good

So now you need to figure out how to make python do those examples

What db lang?

[jaunty galleon]

table:
guild_id | prefix
-----------------
123      | !

If I'll do :

prefix = await conn.fetch('''SELECT prefix FROM table WHERE guild_id = ($1)''', 123)

Will it return "!", or a list and than do prefix[0]['prefix']

[torn sphinx]

Sure, you can do it right in Python

But what lang of db?

sqlite3, postgresql, mongo, dynamo, mysql............

I ask because the syntax for each is diff from one another

bleh

But okay

[jaunty galleon]

What for?

[torn sphinx]

@jaunty galleon Without having the context I'd guess sqlite3?

Also, it seems it would pick !

you selected prefix after all

@torpid aurora In all the provided materials, was there an example on how to connect python to mysql with some further examples on how perhaps to select from it, etc?

[jaunty galleon]

TL;DR: MySQL is a bad database. Don't use it unless you have literally no other option.

Why shouldn't I use MySQL?

• MySQL has no transactional DDL. This means that if you fuck up creating a table in the middle of a migration, you cannot rollback easily.
• MySQL has a lot of security issues. New zero-day vulnerabilities are found regularly.
• MySQL is owned by Oracle.
• MySQL is slow. It beats Postgres etc at some things, but once you move beyond basic queries it begins to slog.
• MySQL disconnects you randomly. Unless you setup your connection specially, it will break regularly.
• MySQL allows all kinds of insane data to be inserted. '0000-00-00' is valid in MySQL.
• MySQL is not ANSI compliant. You have to turn it on; even then it isn't fully compliant.
• MySQL has no feature advantage over other databases; PostgreSQL has many more useful features (such as RETURNs).

There is no reason to use MySQL over PostgreSQL, or even sqlite3. See also:
https://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/
https://grimoire.ca/mysql/choose-something-else
Credit to discord.py member: MrKomodoDragon#7975

[jaunty galleon]

oh ok, thanks

I'll try it

[torn sphinx]

@torpid aurora Doesnt seems to have a choice as it is a school assignment

but yeah, I stick with postgresql and sqlite3 in that regard.

I don't even have any mysql code, anywhere, eheh

Try this

^^^ Those are backticks....

Now paste

^^ end with backticks

From the looks though that is a nice skeleton of sorts @torpid aurora

` != ^ but i digress 🙂

Do you know what a class is in Python @torpid aurora ?

A class is going to be your very best friend for DB work

Ok

So what is the question @torpid aurora ?

You'd need handlers to the tables, you need code mate

I could print the screen, circle with a red marker, but that doesnt accomplish your goal

Code up a skeleton that populates and interacts with the DB and info will naturally be implemented.

[burnt turret]

#databases message

MySQL used to bad many years back doesn't mean it is bad now; both the articles you've linked are very old

One of them even says

I wrote this article in 2013, in what amounts to a fit of pique, and never revisited it. Much of this information is outdated, and you rely on it at your own risk. I restored it at the request of a reader.

The tone and structure of this article also reflects an angrier and much less understanding person than the one I try to be today. Don't let my anger be your cudgel.

[torn sphinx]

@torpid aurora 1-3 is accomplished after the code actually works, just make a pretty ddiagrsam as you code it out.

4 can be done in pure python, just connect to mysql and populate.

5 is the meat and potatoes

^ Just my two cents is all. I think differently I suppose 🙂

[south ruin]

Heya, is it possible to ignore duplicate entries on pymongo using insert_many()?

For example if {"user_id":1, "name" "foo"} exists just continue to the next item, do not insert it or error

[sharp marsh]

Collection:

[{
  "_id": 1,
  "ImageUrl": "http://domain.com/image.jpg",
  "Age": 28,
  "Gender": "M",
  "Hour": 3,
  "CollectionID": "testcollection1,testcollection2",
  "Date": {
    "$date": "2021-05-04T00:00:00Z"
  },
  "MatchedTimeStamp": "2021-05-04 15:01:44.006",
  "FaceCode": []
}]

Code:

from pymongo import MongoClient
client = MongoClient('localhost')
db = client.database
collection = db.collection
collection.find_one()

Output:

$ b'{null}'

[sharp marsh]

help me with this issue

[burnt turret]

what is going wrong?

wait one second

[jaunty galleon]

Ov yeah

Why is there a ) at the end?

After the ladt duration

In the SQL syntax part

[burnt turret]

no it doesn't

yes

[jaunty galleon]

Yes

It's inside the SQL syntax string

Lol

Tell me if it worked the entire temp kute thing

Show it

It seems like you already did it

I mean if time is the correct one

But you should store it with guild id's and muted role id as well

And get them all

Yes

And the muted role is to get it

Oh ok

I will probably do it tommorow

Yeah?

PostgreSQL I got help from someone on discord.py python help

Wdym invoke command?

Sure

[north ridge]

Some data i have in my database

How can I update this row to make it ('Joe papa', 2.0)

[desert imp]

Using SQL you might do:

UPDATE table_name SET col_name = col_name + 1 WHERE name = 'Joe papa';

[zealous nova]

i have this query

UPDATE prefixes
SET prefix = c!
WHERE id = {guild.id}

and i get this error

psycopg2.errors.UndefinedColumn: column c! does not exist
LINE 3:                 SET prefix = c!

[zealous nova]

what does t represent in ur case?

[desert imp]

an alias for table - it can be omitted

[zealous nova]

so u need to to update table table_name?

[proven arrow]

Dont use f strings to write queries. Use the proper method to pass your values as parameters https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

[desert imp]

No - just my poor choice in sample table name - let me update

[zealous nova]

that worked, but, like why would that be the issue

[zealous nova]

oh ok lmao

[proven arrow]

Because you didn’t escape the value so it treats it as a column name

[zealous nova]

ooooooooh

i get it

ty

[north ridge]

Thx 🙂

[jolly spire]

prob a dumb question:

when using gspread, is it ok to update area of about 3k rows and 3 cols?

because I use it as database (ik im not supposed to) and I wanna know if I should bother checking which rows have changed and update only them

it would be easier for me to just update it all...

[marsh tinsel]

How could i backup all of mongodb databases.
Theres a lot of them and they are added pretty frequently.

[south ruin]

Heya, I have an issue with pymongo or MongoDB itself.
I have a list of 100 JSON objects out of which at least 61 have unique IDs.
When I tried inserting those IDs to the DB using insert_many() for some reason it only fits 54 inside.

When I try to insert ~about 48K, at least 47.4K unique - it inserts only 480~ values.

That's the code I'm using:

def insert_many(collation: pymongo.collation, items: list):
    try:
        collation.insert_many(items)
    except pymongo.errors.BulkWriteError as e:
        panic = list(filter(lambda x: x['code'] != 11000, e.details['writeErrors']))
        if len(panic) > 0:
            print("really panic")

It happens no matter if I set an index or not.
P.S. seems like it always pushes the same amount after I drop the collation

P.S.S. the insert is used that way so it will only insert values which were not previously inserted

Almost forgot to add that the unique IDs in those JSON objects are called _id in order to use them as collation IDs

[slow grail]

  @commands.command(help='Warns the user for the specified reason', aliases=['w'])
  @commands.has_permissions(kick_members=True)
  @commands.guild_only()
  async def warn(self, ctx, user: discord.Member, *, Reason='No Reason Provided'):
    UTC = datetime.timedelta(hours=-1)
    UTC = datetime.timezone(offset=UTC)
    con = await sql.connect('./warnings.db')
    cur = await con.cursor()
    await cur.execute('''CREATE TABLE IF NOT EXISTS Warnings (
      ID INTEGER PRIMARY KEY AUTOINCREMENT,
      Guild INTEGER,
      User INTEGER,
      Moderator INTEGER,
      Time DATETIME,
      Reason TEXT
    )''')
    Warnings = await cur.execute('''SELECT Id, User FROM Warnings WHERE User = ? ORDER BY Id DESC''', (user.id,))
    WarningsCount = await Warnings.fetchall()
    if WarningsCount[0] == 0:
      await cur.execute('''INSERT INTO Warnings VALUES (1, ?, ?, ?, ?, ?)''', (ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
      try:
        await user.send(f'You were Warned in {ctx.guild.name} by {ctx.author}. This is Your First Warning;\n**Reason/Message:**\n{Reason}')
        await ctx.send(f':ok_hand: Warned {user}! This is Their First Warning.')
      except discord.Forbidden:
        await ctx.send(f':ok_hand: Warned {user}! This is Their First Warning - I couldn\'t DM Them.')
    else:
      await cur.execute('''INSERT INTO Warnings VALUES (?, ?, ?, ?, ?, ?)''', (None, ctx.guild.id, user.id, ctx.author.id, datetime.datetime.now(UTC), Reason))
      try:
        await user.send(f'You were Warned in {ctx.guild.name} by {ctx.author}. You now have {WarningsCount[0]} Warnings;\n**Reason/Message:**\n{Reason}')
        await ctx.send(f':ok_hand: Warned {user}! They now have {WarningsCount[0]} Warnings!')
      except discord.Forbidden:
        await ctx.send(f':ok_hand: Warned {user}! They now have {WarningsCount[0]} Warnings - I couldn\'t DM Them.')
    await con.commit()
    await con.close()

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/runner/Tests-Bot/Cogs/Moderation.py", line 238, in warn
    if WarningsCount[0] == 0:
TypeError: 'NoneType' object is not subscriptable

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

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

It's supposed to warn them with the first number as 1

If its their first warning

[pliant tiger]

I'm using aioSqlite and i was wondering why I can't do this ```cursor = await bot.db.execute("INSERT OR UPDATE INTO guildSettings (guild_id, channel) VALUES (?,?)", (ctx.guild.id, channel.id))

basically i want it to update or insert

but its giving my errors

thats the error

[brazen charm]

thats not valid sql

in SQLite it's called an UPSERT

in postgres it's a conflict handle

but it follow's postgre's logic

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

[wise goblet]

is it possible to make SQLite working without blocking reading/writing?

is it working properly?

I actually found some advices in google about it, just doubtful about the end result, and which way is better

WAL mod perhaps it is?

[wise goblet]

curious, found easy method to enable

is left to write some test, making sure it works

[wise goblet]

could anyone help?
I am trying to write a test which will block Sqlite

N = 1000
@pytest.mark.django_db
def test_block_db1():
    for i in range(N):
        Commodity.objects.using('double').create(name=secrets.token_hex(32))


@pytest.mark.django_db
def test_block_db2():
    for i in range(N):
        Commodity.objects.using('double').all()


@pytest.mark.django_db
def test_block_db3():
    for i in range(N):
        Commodity.objects.using('double').all().update(
            name=secrets.token_hex(16))


@pytest.mark.django_db
def test_block_db4():
    for i in range(N):
        Commodity.objects.using('double').all().update(
            name=secrets.token_hex(16))

trying different ways. but so far did not reach state of blocked Sqlite access

which supposed to be happening due to limitations of read/write at the same time

even launched multicore

[south ruin]

Help anyone?

[lapis crane]

can anyone link me to the MongoDB learning course?

[burnt turret]

by "the mongodb learning course", do you mean the one that they have on their own site?

[lapis crane]

Ye

i can't find it

[burnt turret]

https://university.mongodb.com/courses/M001/about this is the one i know about

[lapis crane]

Thank you!

[wise goblet]

Interestng, thanks

curious to try it

in order to evaluate

if I can learn with usage of high quality video materials and I just had bad teachers in uni

or video materials just aren't my thing and I better stick to text materials as I do already

[burnt turret]

haha

i also prefer text content more than video

[torn sphinx]

How could I design a categories section for my site?

What tables I would need?

[wise goblet]

what for your web site?
in general every web site needs database for users
but it would be better done NOT with MongoDB ;b

[torn sphinx]

I already have database

Now need to design the categories part

[wise goblet]

answer what for web site

[torn sphinx]

What?

[wise goblet]

yup

what for you are building web site?

what is it going to have?

products/user comments, what?

[torn sphinx]

My shop, to sell products

[wise goblet]

then it probably would have tables
Users
Sessions
Payments
Products

how would you wire it all together, is up to you

[torn sphinx]

No I have most other stuff figured out. I am now working on the categories and I want so a product can be in many categories and a category can belong to many other categories

I am unsure how to do this

So like you have. Mobile -> Apple -> iPhone -> 128GB

Like this there is 4 category

[wise goblet]

table Category, table Product, and table with each record having ID to Category and Product?

it would tie your products to many categories

do the same to tie category to other categories

[torn sphinx]

Yes ok but for the categories table how to store which level the category is?

For example iPhone would be level 3

[wise goblet]

well, just have int attribute level with number ;b

in each record about category

although no

or yes? hmm

I am lazy to think about it tbh

plan your db models on your own

there are some great instruments for that

[torn sphinx]

Well I’m stuck which is why I ask here

[wise goblet]

all right, lets think a bit

we wish having categories of different levels being tied between thsemselves

plus we wish products being tied to lower level categories

[dense barn]

would anything happen if i edited any pragmas in my sql db

[torn sphinx]

Ok

[wise goblet]

lets, think
Category : it has name, and supposedly it can have level. Each category wishs to have usually one parent, and multiple children, where each child is another category or product

[torn sphinx]

Right

[wise goblet]

in minimum amount of tables to perform that

we would have a next thing...

[wise goblet]

wait a second

do you wish to have amount of... category... levels in height

being dynamic or static is enough?

Mobile -> Apple -> iPhone -> 128GB
so 4 levels of category would be enough for you?

[torn sphinx]

It could be more

For example Samsung might have more phone variety so it has more

[wise goblet]

I mean levels in heght!

not in width

shrugs, lets suppose static is enough then

your db model would be of 5 tables then

Table Type of Product (Mobile?) - name

Table Company of Product (apple), name, Foreign ID to (Table Type of Product)

Table Phone Models, name, Foreign ID to (Table Company of Product)

and etc

[torn sphinx]

No, but there are not just mobiles. It’s many things, TVs etc. I already have the tables setup for products and product models.

[wise goblet]

All right

lets have more simplistic way

actually perhaps even smarter

Table Type of Product, Primary ID, Name
Table Company Primary ID, Name
Table Model Primary ID, Name

Table Product Mobile, name, attributes which only mobile products have, plus Foreign key to Type of Product, Foreign key to Company, Foreign key to Model

Table Product TV, has its own unique attributes, plus Foreign key to Type of Product, Foreign key to Company, Foreign key to Model

[proven arrow]

Probably not the best way to have a table per product type

[wise goblet]

shrugs, we can have one table of Product with shared attributes

with links to tables with unique to this product stuff

[proven arrow]

What they need for the categories is a multi level hierarchy. Like a nested set model.

[torn sphinx]

So how would this work?

[wise goblet]

there will be always unique parameters for product from different categories

I think it is only the matter of having them united with one Product table or not

they will have separate tables anyway

[proven arrow]

Thats what product attributes are for

[wise goblet]

to fit their own attributes

[proven arrow]

Atrributes is its own Model. A product can have many attributes.

[wise goblet]

hmm, this can be a way too I guess

Nice enough to have as little tables as possible

[proven arrow]

For the categories you can have a single table, with a self referencing key. Its basically each row in the category table points to its parent category. If the category has no parent (meaning its the root category) then we leave the category_id out as null.
Thats the simple way. If you want to get more complex look at, https://en.wikipedia.org/wiki/Nested_set_model

[wise goblet]

I am slightly worried with performance though for both choices
Attributes to each product, looks like overbloating a little

[proven arrow]

Overbloating how?

[wise goblet]

Attributes will have records = Amount of Products * Amount of Attributes to each product

[torn sphinx]

Ok let me try this then, is there an example ?

[wise goblet]

the biggest table

[proven arrow]

And so? Databases are designed to store many rows

[wise goblet]

I guess so
this choice is the best in terms of easily adding multiple types of new products

without changing database models

[meager vine]

Is there some functionality for generating DB URLS (needed for sqlalchemy) from individual settings variables?

i.e.

DB = 'dbname'
USER = 'username'
HOST = '127.0.0.1'

dbconnstr = mk_db_url(DB, HOST, USER)

[proven arrow]

________________________________________________________________________
| id              |  parent_id     | name
————————————————————————————————————————————————————————————————————————
| 1               |  NULL          | Mobiles
| 2               |  1             | Apple
| 3               |  2             | iPhone
————————————————————————————————————————————————————————————————————————

[wise goblet]

I just don't like a bit

how many different JOINs it would have ;b

each product query would have a hell of JOINs to query ;b

[proven arrow]

Again relational databases are designed to make joins. They have many optimised algorithms and ways of handling these kinds of things.
Let the database at least do some work lol.

[wise goblet]

a man can dream though about faster quering speed
https://www.youtube.com/watch?v=Fv6jSUkCGJ0

[proven arrow]

And there is also premature optimisation.

[wise goblet]

I saw for speeding up used Searching Engines, Sphinx

but it looks scary

or at least it looked scary in the project I saw

it had nothing set up for development

it was a big mess of bugs glued together

[proven arrow]

select `products`.*, `category_product`.`category_id` as `pivot_category_id`, `category_product`.`product_id` as `pivot_product_id` 
from `products` 
inner join `category_product` on `products`.`id` = `category_product`.`product_id` 
where `category_product`.`category_id` = ?```

```sql
select * from `sku_products` where `product_id` in (2) limit 10 offset 0

If your interested, this is the query i have to get products of a categery, for my app. Not so many joins.

[wise goblet]

I guess I'll need to read some book about it

to know some better ways

how would it be named though

[proven arrow]

Name what?

[wise goblet]

the book about databases
I guess mask like "O'Reilly * Database *" would do

[proven arrow]

Not sure, ive never read any books. Just stuff i've learnt from my work.

[wise goblet]

I feel like work is enough only to get myself rushed through essential things to get something done
Books give width and depths to each sphere of knowledge

[proven arrow]

I meant books only cover the theory. They dont cover all edge cases, or the weird requirements clients give you.
Database books will normally just talk the theory, and how things are supposed to be done. It doesn't mean its always the best way, and you learn that when you start working on a range of real world problems.

For example, sure normalisation is good, but in some cases its not the best approach, and you may end up having to denormalise.

[wise goblet]

Without books people apply only the knowledge they have, which is usually reinventing the wheel
Why to reinvent wheel, when you could get the best practices from books

Shrugs, I think books are essential. Good book should be covering most cases
explaining when it is good to apply or when it is not, including the case you mentioned

Experience of many years of different developers, in just hundreds pages to read

[proven arrow]

Im not sure what your trying to get at here, but my point wasn't anything against books or anything. So lets leave it at that.

[wise goblet]

all righty

[torn sphinx]

So I did the categories table, and made the relationship. Now next concern is,
Currently I have products. Each product can have different variant like for example iPhone 11 64Gb have different colours Gold, Black

[proven arrow]

One way is to have 2 tables. A base Product table, and a SkuProduct table. Each product has many SkuProduct, which are just variants.
The SkuProduct describe specific product variations, and also store specific details like the price, description, images. For example, a product may be a iPhone 11 64GB, whereas a specific SKUProduct entity represents the colours for each version of that product like Blue, Black, Grey.

This is the same model that Stripe uses.

[torn sphinx]

Right. Thats actually a really simple way to visualise this ahah. I was overthinking it.

So the product model will only have a two columns? For ID and name? Also is this one to many relation?

Oh nvm you said 1 to many already

[proven arrow]

id and name is enough for it to work yes. But if its an web shop, i would expect a little more columns like the status, manufactuer/brand foreign key etc.

[torn sphinx]

Oh ok

[dense loom]

In my SQLite DB how can i stop duplicate data being created?

Currently im using ```py
queryset = Contact.objects.all().distinct()

[wise goblet]

use constraints

there one which makes only Unique data in columns allowed

each columns can be forbidden having not unique data

[dense loom]

Like this

queryset = Contact.objects.all().UniqueConstraint('phone_number')

?

[wise goblet]

no no no

show me your db model

is it as class?