databases

[opal aspen]

I am using DB browser

[harsh pulsar]

but in your particular case, it's probably because you entered the data wrong

[opal aspen]

to just manually populate

[harsh pulsar]

it's mismatched between the data you entered in the subsupplier table, and the actual supplier

that's the cause of the error

[opal aspen]

yaa

[harsh pulsar]

it'd be nice if it said which field was mismatched, but so be it

i suppose if you must use all those fields in the table, then yes the foreign key constraints help you catch errors

but keeping that all up-to-date would be a nightmare, and i seriously question the competence level of your instructor (or whoever wrote this assignment)

(unless this table is expected to be automatically generated from some other source and not written to "by hand")

[opal aspen]

nope we are expected to create our own sadly

and run bunch of queries on it

[foggy owl]

firstly, decide which db you wanna learn

[storm mauve]

from the pinned messages

https://sqlbolt.com/ , it is a series of interactive lessons that you can try right in your browser, to quickly get started with SQL.
you'll probably want to start with SQL, such as SQLIte, but might want to look into NoSQL such as MongoDB or Neo4J later

[dry crag]

Cheers everybody. I'm writing an finance app which queries DB for specific transactions (described by columns like 'amount', 'date', 'place', 'category', etc), extracts transactions from this DB, translates these transactions to separate class objects, and sends it to the user through some frontend. Now let's assume I'd like to give user a possibility to modify specific 'columns' of these transactions, e.g. manually assign transaction a category. User therefore modifies said data, and sends it back to my server. Is there some sexy way how I can easily recognize in DB which transaction was modified? Currently, the only idea I have is to build a transaction class object from the user's response, and query the DB with the unchanged columns. It feels like a very complicated way to achieve it, with complex dynamic query and a lot of data transformation.

I thought about attaching identity index of my DB to the transaction data i send to user, but it feels like a security loophole

[vapid hawk]

is there a function in sqlite3 to get the product of multiple items?
[1, 2, 3] -> 1*2*3?
if not, how do i make one in SQLite3

[nova cove]

multiply a value of each column?

[vapid hawk]

yeah

no

i select specific items

[nova cove]

SELECT some_column_name, one_column * other_column AS some_name FROM table_name;

[vapid hawk]

i have a collection of floating point numbers, and i want the product of it

[nova cove]

I just showed you

[vapid hawk]

ok I'll try

[nova cove]

I messed up @vapid hawk

[nova cove]

Do this

first table_name to the column name where you want to put the product

[shut cypress]

what is the max number of tables i can create? or is it infinite? (sqlite3/MySQL)

[storm mauve]

it might be finite, but it is very high

[shut cypress]

ty ^

[empty hinge]

Pretty sure you can change it in Postgres

From very high to obscenely high

[balmy folio]

I have binary data png image blobs stored in an sql db, and want to convert them to base64 and direct them to a frontend where they can be displayed.

When I encode the binary data to base64, turning it into a string includes the b' part which breaks the formatting required to display it to the frontend (data:image/png;base64,XXXXXXX is what I want, but data:image/png;base64,b'XXXXXXX is what I am getting)

I could just manually do string manipulation to get rid of that part, but is there a better built in way to grab just the string part without the b'?

I'm asking mostly due to my own ignorance (what if casting bytes objects to strings introduces other issues aside from just the beginning b'? etc)

[harsh pulsar]

turning it into a string
there are 2 kinds of "turning something into a string":

1. producing a textual representation for humans to inspect
2. converting the data to a string

i assume you tried to use str() on the b64-encoded data, which achieves (1) and not (2). for (2), you need to use the .decode() method on the binary data, which is an object of type bytes

!e ```python
data = b'abc1234'
print(str(data))
print(repr(data))
print(data.decode())

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | b'abc1234'
002 | b'abc1234'
003 | abc1234

[harsh pulsar]

!d bytes.decode

[delicate field]

bytes.decode

bytes.decode(encoding='utf-8', errors='strict')``````py

bytearray.decode(encoding='utf-8', errors='strict')```
Return a string decoded from the given bytes. Default encoding is `'utf-8'`. *errors* may be given to set a different error handling scheme. The default for *errors* is `'strict'`, meaning that encoding errors raise a [`UnicodeError`](https://docs.python.org/3/library/exceptions.html#UnicodeError "UnicodeError"). Other possible values are `'ignore'`, `'replace'` and any other name registered via [`codecs.register_error()`](https://docs.python.org/3/library/codecs.html#codecs.register_error "codecs.register_error"), see section [Error Handlers](https://docs.python.org/3/library/codecs.html#error-handlers). For a list of possible encodings, see section [Standard Encodings](https://docs.python.org/3/library/codecs.html#standard-encodings).

By default, the *errors* argument is not checked for best performances, but only used at the first decoding error. Enable the [Python Development Mode](https://docs.python.org/3/library/devmode.html#devmode), or use a [debug build](https://docs.python.org/3/using/configure.html#debug-build) to check *errors*.

[balmy folio]

Thanks I'll give the documentation a thorough read 🙂 yes you're right I did try str, (or rather I tried throwing it into an f string, which probably type casted it the same way str(x) would do)

[modest pivot]

is this the right place to ask questions related to pandas and numpy?

[tribal vale]

Hey, so im wondering how data is saved in a table, and how i would say save two integers (INT 1 = INT2) as the INT1 is the way to find the file, and the INT2 is the saved data that will need to be retrieved, and a ton of people i have been talking to suggest i use databases, so here i am now. i have the ability to save to a database down pat, but everything else is not very efficient.

[delicate kite]

Does anyone here use influxdb? What's the best way to ingest high-throughput streaming data into influx v2?

[torn sphinx]

guys guys, which are some good cloud databases (excluding Atlas)

[fading patrol]

You can spin up any DB on a cloud provider of your choice. If you want something easy and free I like Postgres on Heroku

[torn sphinx]

oh I see, thanks a lot

[nova cove]

there’s firebase

https://firebase.google.com/docs/database

[torn sphinx]

oh eyah completely forgot about fb

[foggy shore]

hey ! i'm having an issue with a postgresql database :
basically all my queries towards one specific table take forever and never end, like they're blocked or i don't know
everything else on any other table works fine.
Do you know what could cause that ?

[nova cove]

Are there multiple transactions occurring at the same time or any errors

[foggy shore]

no errors, and multiple transactions can occur at the same time yes but i made sure to kill most of them

when i run

select * from pg_stat_activity where datname='my-db';

I see a ton of empty queries with state idle and some selects on the table that's locked with idle in transaction

[wary glacier]

guys, why dis

it's stuck

okay, I might have made a typo while changing the regedit...

[fading patrol]

Check a query plan: https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan

[orchid plover]

should i update from postgresql 11 to 14?

[wary glacier]

        await db.execute(
            """
                INSERT INTO users (id, birthday) VALUES ($1, $2)
                ON CONFLICT (id) WHERE (id = $1) DO UPDATE SET birthday = $2;
            """, message.author.id, var
        )

asyncpg.exceptions.InvalidColumnReferenceError: there is no unique or exclusion constraint matching the ON CONFLICT specification
why does this happen? can I even use $1 and $2 multiple times like this?

[topaz stump]

Could someone with knowledge of pandas help me with my problem?

I want to create a scatterplot that contains data from a bunch of different (but very similar) panda dataframes.
All of the panda dataframes I wish to use contain 3 columns (A,B,C).
Column A and B contain values (1,2,3, or 4) I would like these columns to be the Axis's of my scatterplot
Some dataframes I want the points to be all red, for others, all black

[brave bridge]

The error is not about $1 or $2, it's telling you that id doesn't have a unique constraint

can you show your schema?

[wary glacier]

I got through that

        await db.execute(
            """
                INSERT INTO users (id, birthday) VALUES ($1, $2)
                ON CONFLICT (id) DO UPDATE SET birthday = $2 WHERE (id = $1);
            """, message.author.id, var
        )
```asyncpg.exceptions.AmbiguousColumnError: column reference "id" is ambiguous

and now, google isn't being my friend with this

and my table is just

CREATE TABLE users (id bigint, birthday timestamp);

if that's what you asked for @brave bridge

[brave bridge]

what about this?

create table users (
  id BIGINT primary key,
  birthday timestamp
);

[wary glacier]

not sure what that does

I'll try

no, I still get the same error

[brave bridge]

https://www.postgresql.org/docs/14/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS

hmm, I don't know then, sorry

[wary glacier]

no problem ❤️

[grim vault]

There is no need for the WHERE (id = $1) because the ON CONFLICT (id) already defines which row will be updated. That's why a unique constrain is needed (or a primary key).

And the error occurs because the id column in the WHERE part can refer to the real table column users.id or the pseudo table column EXCLUDED.id (which holds the values of the insert statement).

[wary glacier]

it solved a problem for me

[wary glacier]

so check where id is not the excluded.id?

[nimble needle]

TypeError: 'Collection' object is not callable. If you meant to call the 'remove_one' method on a 'Collection' object it is failing because no such method exists.

can someone explain to me why i'm getting this error using Pymongo ?

[nova cove]

error is pretty telling.

Try delete_one?

!d pymongo.collection.Collection.delete_one

[delicate field]

pymongo.collection.Collection.delete\_one

delete_one(filter, collation=None, hint=None, session=None)```
Delete a single document matching the filter.

```py
>>> db.test.count_documents({'x': 1})
3
>>> result = db.test.delete_one({'x': 1})
>>> result.deleted_count
1
>>> db.test.count_documents({'x': 1})
2

[nova cove]

you see that remove_one doesn’t exist

[nimble needle]

Ohhh

thanks🗿

[violet token]

!f-stringsql

[delicate field]

Creating a Python string with your variables using the + operator can be difficult to write and read. F-strings (format-strings) make it easy to insert values into a string. If you put an f in front of the first quote, you can then put Python expressions between curly braces in the string.

>>> snake = "pythons"
>>> number = 21
>>> f"There are {number * 2} {snake} on the plane."
"There are 42 pythons on the plane."

Note that even when you include an expression that isn't a string, like number * 2, Python will convert it to a string for you.

[nova cove]

!sql-fstrings

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[violet token]

omg i tried it and it worked

tysm

[wraith trail]

Does anybody know how i can match only one single whitespace in a string using regex? i know \s will match but it will match all whitespaces. i only want to match the very first whitespace in my string

[harsh pulsar]

is this a database question or a python question?

[brazen socket]

so i was gonna create a table with an int and i want to add a column thats like a list

how do i do that

[nova cove]

you can do like datatype[] I believe

It might depend on the flavor of sql rdbms you are using

like Postgres has bit[], bit varying[], char[], etc

[brazen socket]

i use postgres

[nova cove]

yeah so there u go

[brazen socket]

ok

[nova cove]

https://www.postgresql.org/docs/9.5/datatype.html

[brazen socket]

thx

[nova cove]

Np

[brazen socket]

can you give me the direct docs to the list datatype?

[fading patrol]

There isn't a list datatype in Postgres and if you think you want one you probably aren't designing your schema properly. What exactly are you trying to do?

If you really need to, you can use the JSONB datatype, but I don't recommend it. If you're not trying to store data on tables use a NoSQl DB instead

[brazen socket]

My database has a table called rooms

Right now it has a column called room_id

I want to add another column that holds every user in that room

[fading patrol]

A user can only be in one room at a time? If yes, add a column like current_room to the user table and put the corresponding room_id there.

If users can be in multiple rooms at once, this is called a many-to-many relationship and it means you need an intermediate table like users_rooms

Either way, no need to put a list in a SQL db

[brazen socket]

Okay thanks

[grim vault]

No, there is no need for an where clause. The update of an on conflict only refers to the row where the conflict happens, so it already has an implicit where useres.id = excluded.id.

        await db.execute(
            """
                INSERT INTO users (id, birthday) VALUES ($1, $2)
                ON CONFLICT (id) DO UPDATE SET birthday = $2;
            """, message.author.id, var
        )

That's all what you need to either insert a new user with an birthday or update an already existing user with a new birthday.

[dry crag]

Does sharing a IDENTITY column number in a server response pose a vulnerability?

I mean, i query the db based on clients request and provide them also my IDENTITY number to track any changes between the data sent and in the DB

[warm cradle]

hi

how to make a database

[torn sphinx]

#help-cupcake

[haughty linden]

i don't get it. i've been throwing bs at it since this morning but whatever i do, it just does not want to. i'm sure of my token, because i'm using the same with the geocoding api and it works. and it is activated on the right scope. any ideas?

[rigid furnace]

hey everybody, I need help with direction in what to search for to restrict a viewset based on foreign tables.

I'm trying to restrict products, specifically fetching single product, to logged in user only.

So i've been scatching my head at this for a big and my google-fu's subpar.

Simplified versions of my models Setup:

AppUser
Container
Booking:
    agent = foreignkey=(AppUser, . . )
    container = foreignkey(Container, . . )
Product:
    container = foreignKey(Container, ...)

Issue:

I am trying to retrieve onlys products assoicated to the logged-in user when hitting the /products/1 endpoint.

For /products i've been using

products = Product.objects.filter(id__in=Booking.objects.filter(agent__user=request.auth.user))

but now I think even that's wrong.

Keywords i've used are viewset, retreive, foreign tables, "logged in user".

[prime kelp]

this syntax highlighting is scaring me

have i done anything wrong?

[fading patrol]

It's always a bit wonky in VS Code with SQL. I have no idea why user_id is in yellow but you're probably ok there.

[prime kelp]

okay, thats reassuring

thanks

[brave bridge]

@prime kelp If you want to test some SQL stuff, https://www.db-fiddle.com/ is a great thing

[prime kelp]

sql injects the website:

[prime kelp]

how do i execute a schema.sql in aiosqlite?

with open("./schema.sql") as file:
            await self.execute(file.read())
``` when I do this it gives

sqlite3.Warning: You can only execute one statement at a time.

but the warning is killing it

[nova cove]

async with connect(r"path_to_db_file") as db:
  BUILD_PATH = r"path_to_sql_file"

  if os.path.exists(BUILD_PATH):
    with open(BUILD_PATH, "r") as f:
      await db.executescript(f.read())

  await db.commit()

this is my statement i use for my discord bot

[prime kelp]

ah cheers

[nova cove]

np

[lunar cargo]

hey guys
how can I do list of tuples in column and is it even possible

like [(123, 34), (231, 32)]

[nova cove]

my_list = [(1, 2, 3),
           (4, 5, 6)]

?

[lunar cargo]

yup

[nova cove]

don't think tuple types are supported

tuples in SQL relate to rows

not columns i think

[lunar cargo]

I have data which contains price and size of pizza and I want to make "relationship" easier

my previous idea was "30/40/50" in the string column and do the same to prices

like "5.99/6.99/8.99"

and when I want to use it just split it

[lunar cargo]

but okay

[prime kelp]

there is sql INSERT INTO and UPDATE

do i have to make a check like ```py
if not result:
return await self.db.update(
"INSERT INTO prefix VALUES (?, ?)", (user_id, prefixes)
)
await self.db.update(
"UPDATE prefix SET prefixes = ? where user_id = ?", (prefixes, user_id)
)

[nova cove]

why db.update instead of db.execute

[prime kelp]

i made a database class

ignore it

[nova cove]

oh

ok lmao

[prime kelp]

hm

[nova cove]

!e

from typing import Awaitable, Union
import asyncio

async def foo() -> str:
  return 'bar'

async def bar() -> Union[Awaitable[str], int]:
  return await foo()
  return 10 + 10

async def main():
  print(await bar())

asyncio.run(main())

[delicate field]

@nova cove :white_check_mark: Your eval job has completed with return code 0.

bar

[nova cove]

seems like the 10 + 10 is not reachable so i don't think so @prime kelp

!e

from typing import Awaitable, Union
import asyncio

async def foo() -> str:
  return 'bar'

async def bar() -> Union[Awaitable[str], int]:
  # return await foo()
  return 10 + 10

async def main():
  print(await bar())

asyncio.run(main())

[prime kelp]

k

[delicate field]

@nova cove :white_check_mark: Your eval job has completed with return code 0.

20

[nova cove]

!e

from typing import Awaitable, Tuple
import asyncio

async def foo() -> str:
  return 'bar'

async def bar() -> Tuple[Awaitable[str], int]:
  return await foo(), 10 + 10

async def main():
  print(await bar())

asyncio.run(main())

unless you do something like that @prime kelp

[prime kelp]

m

[grim vault]

An UPDATE will not raise an error, it will just update no rows.

[acoustic thunder]

in mongo db, using the update_one method, im trying to do addition for the updated value, like do += 22 but it errors, how could i add numbers to a value using update_one?

[pure mortar]

you will need to use an update operator https://docs.mongodb.com/manual/reference/operator/update/#std-label-update-operators

in this case $inc

[acoustic thunder]

ok

ty

[torn sphinx]

Is anyone else having weird name resolution issues with pymysql lib, even within the same datacenter? I know this might be a broad question, but the library gave me quite a lot of issues with connections in the last few weeks.

[nova cove]

I am connecting to an SQLite DB using aiosqlite. When I specify the path as the whole absolute path starting from my C drive, the database is successfully created, but when I use an relative path like ./db/db_name.db and run the code, it says unable to open database file.

The file structure for this area is as follows:

src
|-> bot
    |-> db
        |-> build.sql 
        |-> __init__.py
    |-> __init__.py
    |-> bot.py (this is where I have my function that connects to the database)

async def connect(self, reconnect) -> None:
  DB_PATH = "./db/bot.db"

  async with connect(DB_PATH) as db:
    BUILD_PATH = r"my_path_here"

    if os.path.exists(BUILD_PATH):
      with open(BUILD_PATH, "r") as f:
        await db.executescript(f.read())

    await db.commit()

  await super().connect()

this is the function

[late moat]

Hello

Sry to interrupt but i was sent over to this channel to ask about pandas

I was curious about what pandas has to offer

So far i know you can read and write prewritten data

I was curious if someone could give me a bit more insight on what it’s about

[grim vault]

Take a look of the current working directory before the connect try with print("cwd:", os.getcwd())

[haughty perch]

Hey so I am having some issues with gino, anyone that could help me out with this?
https://github.com/python-gino/gino/discussions/807

Ping on response please :)

[ionic smelt]

@sinful rivet

https://sqlbolt.com

[sinful rivet]

hi

[ionic smelt]

learn sql here

[sinful rivet]

ok

[ionic smelt]

and move to aiosqlite/asqlite/postgre after you're done learning

[sinful rivet]

i still friggin need naswer to my problem

a n s w e r

[ionic smelt]

learn sql first

[sinful rivet]

if u mean basic like select or update data, ik

[ionic smelt]

alr tell me how to update data

[sinful rivet]

wait

[sinful rivet]

cur.execute("UPDATE table set idk = value [or "?"] where [statement] = value", (use dis when "?))

sad

[ionic smelt]

nope

not "?"

[sinful rivet]

just ? without ' " "'

[dry crag]

Hey guys, can anyone hint me if there's a better way how to achieve similar without these repeating if statements? I don't like how this code is structured but i dont have a better idea how to do it.

    def filterRepo(self, **kwarg) -> list[Transaction]:
        """Filter the DB for specific records\n
        amount = (min: float, max: float)\n
        currency = ('CZK': str, ...)\n
        srcAmount = (min: float, max: float)\n
        srcCurrency = ('CZK': str, ...)\n
        date = (min: datetime.datetime, max: datetime.datetime)\n
        category = ('grocery': str, ...)
        """

        filters = []
        filterValues = []

# amount - 'amount BETWEEN %s AND %s'
        if 'amount' in kwarg.keys():
            amountFilter = SQL("{} BETWEEN %s AND %s").format(
                Identifier(self.columnMap['amount']))

            filters.append(amountFilter)
            filterValues.extend(kwarg['amount'])

 # currency - 'currency IN (%s, ...)'
        if 'currency' in kwarg.keys():
            currencyFilter = SQL("{} IN ({})").format(
                Identifier(self.columnMap['currency']),
                SQL(', ').join(Placeholder() * len(kwarg['currency'])))

            filters.append(currencyFilter)
            filterValues.extend(kwarg['currency'])

        # srcAmount - 'src_amount BETWEEN %s AND %s'
        if 'srcAmount' in kwarg.keys():
            srcAmountFilter = SQL("{} BETWEEN %s AND %s").format(
                Identifier(self.columnMap['srcAmount']))

            filters.append(srcAmountFilter)
            filterValues.extend(kwarg['srcAmount'])

        # srcCurrency - 'src_currency IN (%s, ...)'
        if 'srcCurrency' in kwarg.keys():
            srcCurrencyFilter = SQL("{} IN ({})").format(
                Identifier(self.columnMap['srcCurrency']),
                SQL(', ').join(Placeholder() * len(kwarg['srcCurrency'])))

            filters.append(srcCurrencyFilter)
            filterValues.extend(kwarg['srcCurrency'])

     # date - 'transaction_date BETWEEN %s AND %s'
        if 'date' in kwarg.keys():
            dateFilter = SQL("{} BETWEEN %s AND %s").format(
                Identifier(self.columnMap['date']))

            filters.append(dateFilter)
            filterValues.extend(kwarg['date'])

        # category - 'category IN (%s, ...)'
        if 'category' in kwarg.keys():
            categoryFilter = SQL("{} IN ({})").format(
                Identifier(self.columnMap['category']),
                SQL(', ').join(Placeholder() * len(kwarg['category'])))

            filters.append(categoryFilter)
            filterValues.extend(kwarg['category'])

        temp: list[Transaction] = []
        query = self.cur.mogrify(SQL("SELECT * FROM {} WHERE {};").format(
                                                Identifier(self.tableName),
                                                SQL(' AND ').join(
                                                    [Composed(filter) for filter in filters])),
                                            filterValues)                                   
        self.cur.execute(query)
        print(query.decode())

        for i, row in enumerate(self.cur.fetchall()): 
            temp.append(copy.deepcopy(Transaction()))
            temp[i].id = row[0]
            temp[i].name = row[1]
            temp[i].title = row[2]
            temp[i].amount =  row[3]
            temp[i].currency = row[4]
            temp[i].srcAmount = row[5]
            temp[i].srcCurrency = row[6]
            temp[i].date = row[7]
            temp[i].place = row[8]
            temp[i].category = row[9]

        return temp

[tropic kelp]

@dry crag don't use Kwargs for this and it'll be a LOT simpler

[dry crag]

Could you elaborate? I wanted this filtering to be easily scalable and pass only amount of filtering parameters which are necessary

[tropic kelp]

use optional parameters with a default value of none

def myquerybuilder(currency=None, date=None):

  query = """select <relevant columns> from xxx 
             where currency = {curr} and date = {date}
          """.format(date=currency or "ANY", date=date or "ANY")
  return query

or somesuch, my SQL syntax is probably awful

[dry crag]

oh, this is very elegant syntax

format(date=currency or "ANY", date=date or "ANY")|'

[tropic kelp]

tbh, fundamentally it's broken, because they need to give some args at some point yeah?

[dry crag]

Yes, this is partially helpful, but my problem is im filtering in ranges

So im passing a tuple (min ,max)

[tropic kelp]

yeah well, do some more string formatting haha.

[dry crag]

And for that I need to have a full SQL syntax like 'amount > AND amount<=

[tropic kelp]

Basically you should always move your problems down to SQL syntax

[brave bridge]

maybe you want a proper query builder?

and not roll your own

[dry crag]

What do you mean?

[tropic kelp]

There are applications for object oriented query building with fancy features

[dry crag]

I'm googling right now 😄

[brave bridge]

There are libraries that help you build SQL queries.
For example:

• SQLAlchemy https://docs.sqlalchemy.org/en/14/orm/query.html
• pypika https://github.com/kayak/pypika

[dry crag]

ahhh, you mean ORM?

[brave bridge]

no

A query builder just builds queries

[dry crag]

Thanks for these, I'll have a read

Yeah, for education purposes I wanted to write queries by myself and dynamically fill them, but it sometimes starts to feel like I'm fighting with tricky syntax structuring more

[tropic kelp]

^^^^

Rule of thumb, if you have less than 20 SQL queries in your codebase, don't bother with SQL alchemy

It's not that it's a bad thing, but it's like maintaining jenkins or such because it has database connections configs yadadada

it just takes mental bandwidth to keep the show on the road

[brazen charm]

Personally I do prefer raw queries

[prime kelp]

await bot.db.update("INSERT INTO overall_score VALUES (?,)", (25842,))

    result = function()
sqlite3.OperationalError: near ")": syntax error

.update is just execute

[brave bridge]

trailing commas are not allowed in SQL

just do (?)

[prime kelp]

oh right, thought i tried that - thanks

[sinful rivet]

.

ok

def update_farm(user, i, waht):
    user = str(user)
    gni = check_farm_decoded_all(user)
    test = list(gni)
    i = int(i)
    test[i] = waht
    statement = "UPDATE farm set DECODED = ? where NAME = ?"
    cur.executemany(statement, test, user)
    conn.commit()```
executemany expected 2 arguments, got 3

[nova cove]

cwd is up to the src folder which contains everythjing

fixed it. DB_PATH = "../src/bot/db/bot.db" did the trick. thanks @grim vault

[ionic latch]

I am confused on this

https://gyazo.com/6165e9391428bc0b947b49cc54514c6d

What trigger should i use?

before, after or instead?

[nova cove]

well there are 3 types of triggers:

Data Manipulation Language (DML) Triggers: INSERT, UPDATE, DELETE
Data Definition Language (DDL) Triggers: CREATE, ALTER, DROP
Logon Triggers: LOGON events

this is for MS SQL Server

[ionic latch]

I have only learned insert, update, delete, drop

we use sqlite

[nova cove]

not sure what flavor you are usinmg

ahh ok

[ionic latch]

but it says it require more than one trigger

i got confused there

[nova cove]

in that case, it is relatively the same for SQLite.

[ionic latch]

yes, its mostly the same only that sqlite has less features and are easlier

[sinful rivet]

?

[nova cove]

SQLite triggers happen when an INSERT, UPDATE, or DELETE statement happens against the table

CREATE TRIGGER [IF NOT EXISTS] trigger_name 
   [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] 
   ON table_name
   [WHEN condition]
BEGIN
 statements;
END;

this ones better. gives the all the options you could provide

[sinful rivet]

new prob

def update_farm(user, i, waht):
    user = str(user)
    gni = check_farm_decoded_all(user)
    test = list(gni)
    i = int(i)
    test[i] = waht
    statement = "UPDATE farm set DECODED = ? where NAME = ?"
    cur.executemany(statement, (test, user))
    conn.commit()```
Incorrect number of bindings supplied. The current statement uses 2, and there are 498 supplied.

[ionic latch]

im trying to understand the question i got, because they want the correct TotalAmount for the OrderItem

[nova cove]

what table is OrderItems in

[ionic latch]

https://gyazo.com/d7874d6ba7c66b08cfee323a47c6092a

heres the model

They want the TotalAmount from the Order table atleast, i dont know what they meant with orderitem

because thats a whole table

do they want those to be connected?

[nova cove]

it seems like ID from Order is the same as OrderID in OrderItem

both tables are related

[ionic latch]

Ah but isnt it the same since OrderID is the same as ID, but its just renamed to not confuse the table name

because its a foreign key

from the Order table

[sinful rivet]

help?

[nova cove]

ye the naming is kinda odd but whatev

[ionic latch]

In orderitem the orderid is the primarykey in Order table called id only

[nova cove]

put the statement in executemany

don't separate it like that

[sinful rivet]

ok wait

[sinful rivet]

def update_farm(user, i, waht):
    user = str(user)
    gni = check_farm_decoded_all(user)
    test = list(gni)
    i = int(i)
    test[i] = waht
    statement = None
    cur.executemany("UPDATE farm set DECODED = ? where NAME = ?", (test, user))
    conn.commit()``` still same

[nova cove]

ye PK in order is ID, which is related to the FK in OrderItem, OrderID which means they will be the same. lets get to the trigger though

[nova cove]

ik that wouldn't fix. just wanted to make sure your statement was in the executemany() but

[ionic latch]

should it be a before and a after trigger?

[sinful rivet]

and what u mean by "dont separate it like that"

[nova cove]

@sinful rivet it prolly has to do with test and user and how they look to SQLite

since it says that there are "498" bindings supplied

[sinful rivet]

test is list-

maybe thats da prob?

[nova cove]

i would say a AFTER trigger so we can check the given OrderItems

[nova cove]

likely

[sinful rivet]

but it can work last time-

[sinful rivet]

i mean in other code

[grim vault]

If the cwd is the src directory I would use DB_PATH = "./bot/db/bot.db"

[ionic latch]

only after?

[sinful rivet]

def add(name):
    name = str(name)
    farm = [
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b",
    "b", "b", "b", "b", "b", "b", "b", "b", "b", "b"
    ]
    sql_statement = 'INSERT INTO farm (NAME, I, DECODED) VALUES (?, 0, ?)'
    cur.executemany(sql_statement, name, farm)
    conn.commit()``` like dis one, dat work

[nova cove]

i've tried that and it didn't work

but lemme see again

oh well it works

[grim vault]

Actually I would use a config file where I define the path to the database.

[ionic latch]

@nova cove would you only use After?

[nova cove]

i am not publishing this code to GitHub. if I was I would have a .env file

[nova cove]

uhh well it says you might need more than 1 trigger. but one would prolly have to be an AFTER trigger

[ionic latch]

This is hard, im struggeling to see what to write

[nova cove]

i won't spoonfeed code, but i will help along the way

check out the docs for it

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

and think about your table relationship

[grim vault]

If one insert a new item to the order you need to add, if one deletes an item you need to substract, if one updates the item you neeed to ...

[ionic latch]

check if it exists already?

[grim vault]

You can only update existing itmes.

[nova cove]

i am assuming that there can be multiple OrderItem tables, correct?

[ionic latch]

There is only one table named OrderItem

[grim vault]

You need to adjust the value when it goes from 3 to 1 you need to -2, it could also go the other way.

[ionic latch]

Ahh

But in the exercise case i'm making a trigger when they try to update?

they want the totalamount to always be correct to the orderitem

wont i need a insert there, so if they insert something wrong it wont work

[grim vault]

You need a trigger for all possible cases where the count can change.

[ionic latch]

I'm allowed to use 2 trigger

[nova cove]

only 2?

[ionic latch]

No it says "This may require more than just one trigger!"

[grim vault]

Three is more than one.

[ionic latch]

So update is one possiblity, insert is another, and delete maybe?

[grim vault]

Most likely if the quanty and the price is != 0.

[ionic latch]

is quantity involved?

[grim vault]

I tought TotalAmount is the total sum of the prices?

[ionic latch]

https://gyazo.com/151547e8ebda78e0657b4e2c6cbd9134

this is how it looks

[grim vault]

The UnitPrice is per item, so if there are 3 items á 10 you'll need to add 3*10 to the total.

[ionic latch]

yes correct

but is UnitPrice inside that table?

Order table

[grim vault]

You have the schema.

[ionic latch]

yes it on a different table

am i suppost to use the Unit Price

[grim vault]

Well, the order changes if the items of that order changes, so the trigger is the chnage of the table OrderItem which needs to update the TotalAmount of the Order table.

[ionic latch]

So i need a connecting between these two tables?

[grim vault]

There is one already.

[ionic latch]

If the TotalAmount changes the whole Order changes?

[grim vault]

The TotalAmount is calculated, you need to change it if the items of the order changes.

[ionic latch]

Ahh i dont need to spesify a connecting when making a trigger? since they already is connected in the database

[grim vault]

Of course you need to specify it.

[ionic latch]

with a inner join?

[grim vault]

But the link is part of the schema, the FK in the OderItem links to the PK of the Order.

[ionic latch]

How many Trigger would you use?

[grim vault]

In this case it seems like 3 are needed.

[ionic latch]

what would those types be?

before, after or instead?

@grim vault ```sql
CREATE TRIGGER validate_TotalAmount
BEFORE UPDATE ON [Order], OrderItem
BEGIN
SELECT CASE WHEN TotalAmount != UnitPrice * Quantity THEN RAISE (ABORT, "The TotalAmount is wrong")
END;
END;

could this work

[ember quail]

hello?

[dusky cypress]

Quick question, what's preferred, fewer tables but more joins or more tables?

[queen panther]

Hi guys....I wanted to use firestore as my database in my express app but I am unable to learn anything about firestore properly...Could anyone suggest few resources that could get me started in learning firestore? And thanks for the help

[nova cove]

https://cloud.google.com/firestore/docs/how-to

[grim vault]

I don't think so. As I understand it, you should update the TotalAmount of the Order table if something chnages with the OderItem table.

CREATE TRIGGER add_item_to_order
  AFTER INSERT ON OrderItem
  BEGIN
    UPDATE "Order" SET TotalAmount = TotalAmount + NEW.UnitPrice * NEW.Quantity
     WHERE "Order".Id = NEW.OrderId;
  END;

something like that.

[bleak bough]

neatest way to unpack a row?

this is a mess

[random arch]

what should i start with, MySQL or MongoDB

[eager warren]

Here

Ok

I'm not quite sure where to start to be completely honest (Other than the code of the command)

Ok, tysm

[torn sphinx]

imagine using tutorials for mongoDB

[nova cove]

💀

[torn sphinx]

mongodb is easy to setup smh

imagine using pymongo

huh?

no, I never said pymongo is bad, its the easiest to use too

[eager warren]

does there have to be a specific cluster name?

[bleak bough]

bad or good solution to unpack neatly 🤔

probably not working code just brainstorming, using column names as dictionary keys

[torn sphinx]

idk, I use txt files

[bleak bough]

anyone? how do you do it

[naive sandal]

You can set Connection.row_factory to Row

!d sqlite3.Row

[delicate field]

sqlite3.Row

class sqlite3.Row```
A [`Row`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Row "sqlite3.Row") instance serves as a highly optimized [`row_factory`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory "sqlite3.Connection.row_factory") for [`Connection`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection "sqlite3.Connection") objects. It tries to mimic a tuple in most of its features.

It supports mapping access by column name and index, iteration, representation, equality testing and [`len()`](https://docs.python.org/3/library/functions.html#len "len").

If two [`Row`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Row "sqlite3.Row") objects have exactly the same columns and their members are equal, they compare equal.

[bleak bough]

landed here on how to unpack, it's a little better than a long single thing

[grim vault]

I define a dataclass (or namedtuple) like the table and just use *row.

from collections import namedtuple


# like the table in the database
Player = namedtuple("Player", ["player_id", "name", "whatever"])

...

row = curs.execute("SELECT * FROM Player WHERE player_id = ?", (bobs_id,)).fetchone()
if row is not None:
    player = Player(*row)

[celest zodiac]

Side note for all using SQLite:
The pragma "temp_store": "MEMORY" provides a tremendous increase in performance on every application I've used it with.

[mental monolith]

I have python 3 installed and want to run some sqlite3 commands from the cmd. How can I do this? Running sqlite3 by itself returns error messages saying that sqlite3 isn't recognized as an internal or external command.. Any help?

[grim vault]

ctx.author is a class, you'll need user_name = str(ctx.author)

[violet token]

ohhhh

wait is it because

it contains intehers

integers

[violet token]

?

[grim vault]

No, ctx.author is a class object which sqlite doesn't know what to do with it.

[violet token]

oh

so it doesnt understand

so you convert it

to a string

which it can

understand

[grim vault]

From the docu:

str(x)

Returns the user’s name with discriminator.

[nova cove]

What’s up

what’s the issue?

[brazen socket]

i have this logging thing that sends a message to a channel if a message is deleted, is it bad to keep getting values from the database everytime someone deletes a message? (this is a discord bot)

[fading patrol]

Sounds like you need to install sqlite3... The application itself and the Python library are two different things

[sinful rivet]

ok, so basically i get a list that hav dis https://www.toptal.com/developers/hastebin/busokahule.json value, is there any way to kinda get individual data value? (the individual list value is (id, index, value), i just wanna get the "value", how?)

[pure mortar]

really cool tool i wanted to share https://dbdiagram.io/home

[severe coral]

Looks great I used to use Visio for this

[harsh pulsar]

pony orm also has a db designer tool

its very useful, although i dont actually use pony (but i would if it supported async)

https://editor.ponyorm.com/

it'd be pretty cool to fork this and make different "backends" for it: django, sqlalchemy, etc.

it seems like the prisma orm (for node, not python) has its own database schema specification language that could theoretically be used in a manner like this

and i believe there are tools to use it with python already? not sure though

interesting project though https://www.prisma.io/

[fathom star]

^ Actually use that in one of my own projects and it's awesome, give it a try and you won't feel like you're even dealing with SQL

[nova cove]

prisma has a python version

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

[fathom star]

looks like their schema thing is the same even in the python version which is awesome

[harsh pulsar]

ah that was it, thanks @nova cove

[nova cove]

np

[harsh pulsar]

and yeah that's what's so cool about it: it's totally language-agnostic

i especially like that it can generate graphql

[nova cove]

yeah

[harsh pulsar]

which is kind of "low-level" for when you want to define sophisticated types

graphql interfaces are pretty limiting

[nova cove]

but graphql hype has died down. it didn't really do what everyone thought it would

[harsh pulsar]

no generic collections etc

indeed, although i still think it has its place

[nova cove]

yeah its a niche tech

[pure mortar]

oh this looks cool. saving this. thanks

[harsh pulsar]

the problem imo is that most orgs don't have the problem that graphql solves: lots of data sources distributed across several separate services, which are all needed at runtime

[nova cove]

mhmm.

[harsh pulsar]

most applications really should be a monolith app that his a database and the cms, behind nginx or whatever

[nova cove]

like how much benefit does one endpoint really have over multiple?

[pure mortar]

yeah i think i might use this one to share some stuff with some team members since its good for prototyping

[harsh pulsar]

i think graphql has a lot of benefit, if that is what your org has

e.g. a microservices framework

then you can expose your whole system as a single unified api, even if there's complicated logic in the unification

so it makes sense if you're facebook

[pure mortar]

different tools for different use cases

[nova cove]

^

[pure mortar]

one of the most trying challenges if you were a CTO or something

deciphering between hype and noise

and useful

[harsh pulsar]

CTOs should also make time for their contributors to build proof-of-concept systems to actually try new stuff once in a while 😉 otherwise you're stuck guessing and never have any data on what works and what doesn't

[warm hound]

Damn, how can someone spit so many facts in such a short time

[simple robin]

Hey, I'm using SQLite 3 and when I do this :

exists = ctx.author.id in conn.cursor().execute("""SELECT id FROM members where id = :uuid""",{"uuid" : ctx.author.id}).fetchone()

The code returns the error " argument of NoneType is not iterable", even though the variable exists is set to True... Any ideas ?

[foggy shore]

heyo there, still having the same problem with my pgsql database :/
does someone have an hint about what i could do ?
#databases message

[foggy shore]

also this :

File "path\to\file.py", line 330, in func
    pg_con.rollback()

psycopg2.OperationalError: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly

how can i get that error on a rollback ? what ?

i rollback to not have that error

[grizzled wadi]

Hey, I created the Python client for Prisma, so if you have any questions or need any help I'd be more than happy to help :)

There are tools like https://www.prismabuilder.io that can help with writing the schema

Although I think you don't really need it if you have the VSCode extension as that provides autocomplete and syntax highlighting for the schema file

And if you want to use the DBML tool mentioned earlier you can also generate a DBML diagram from your Prisma Schema

https://github.com/notiz-dev/prisma-dbml-generator

[foggy shore]

i'd like to know what do you mean by "first fully type-safe ORM for python"
aren't ORMs like SQLAlchemy, pony or tortoise already type-safe if you implement them with schemas and models ?

[torn sphinx]

lel

[grizzled wadi]

Good question, no they are not, when you use ORMs like pony and SQLAlchemy you get very limited (if any) query type safety. What this means is that a type checker can't identify when you've written a query with a field that doesn't exist

[foggy shore]

that's wrong tho

[grizzled wadi]

What do you mean?

[foggy shore]

i used those 3 ORMs for 2 years already and they can all know which type a field is

and reading your doc, prisma doesn't do anything more

meaning, if we'd use Mypy to compile a code using any of those ORMs, it would be statically and strongly typed just as finely as any java ORM for instance

[grizzled wadi]

The type definitions in the other ORMs either provide very limited types or just fall back to using Any for querying which means type checkers won't actually report an error if you write an incorrect query

[foggy shore]

well that's only if you don't implement them well

reading prism docs, i don't see any mention about enforcing type checks with assertions or such, so it isn't different.
it is maybe a bit better for beginners if you check specifically if type hints are present (which i don't know if you do)

[grizzled wadi]

Sorry, I don't follow, the actual implementation in ORMs like SQLAlchemy can't type hint the query methods correctly, how can you fix this as a consumer?

[foggy shore]

they can type hint correctly ? just use schemas ? i don't see the problem

if you want typed queries you should use schemas anyway

[grizzled wadi]

What do you mean by schemas? Something like this? ```py
class User(Base):
name = Column(String)

[foggy shore]

no, something like this

class UserBase(BaseModel):
    name: str```

which is used on top of models

for SQL alchemy it uses pydantic

then, all your CRUD uses theses schemas, so you have type checks everywhere

you even have runtime schema checks, which is even better than type errors

[grizzled wadi]

Yes that will give you object creation type safety but that still will not give you query type safety

[grizzled wadi]

How are runtime checks better than type checks?

If you can statically analyse your code to report errors isn't that better than only having them revealed at runtime?

[foggy shore]

because they are made even if you make queries from outside of your application

like, if your developing an API

and make queries using it with a CRUD

you get instant schema errors before the query even executes

and of course, you have analysis of the code because everything is type hinted

[foggy shore]

why wouldn't it give query type safety if you make queries with schemas ?

i don't get your point, the principle of a schema is to have type checks for queries, you can even make several schemas for the same table, if you need to have optional stuff while inserting but everything while selecting for instance

[grizzled wadi]

Yes that is true but with Prisma you get both, there are runtime type checks as well as static type checks

[grizzled wadi]

Can you give a code example please?

Say you write a query like this in SQLAlchemy: ```py
user_by_email = (
session.query(User)
.filter(Address.email_address == '[e-mail]')
.options(joinedload(User.addresses))
.first()
)

You have very little static type safety with this query:
- The Address model may not have a relation to the User model
- The options method does not have type hints at all
- The return type is not known

And most of all pyright doesn't understand this code at all, maybe there are better ways to write this type of query to make it more compatible with tools like pyright but I'm not deeply familiar with Sqlalchemy

[foggy shore]

for example, given those schemas :

class ExperimentBase(BaseModel):
     pass

class ExperimentCreate(ExperimentBase):
    value: int
    optional_value: Optional[int]

class ExperimentUpdateOptionnal(ExperimentBase):
    optional_value: int

class ExperimentSelect(ExperimentBase):
    creation_date: datetime
    update_date: datetime
    value: int
    optional_value: Optional[int]

you can have some functions with user inputs and type checks such as

def create_experiment(values: schemas.ExperimentCreate):
    crud.experiment.create(db, obj_in=values)

def update_experiment(id, values: schemas.ExperimentUpdateOptionnal):
    crud.experiment.update(db, id=id, obj_in=values)

def select_experiment(id) -> schemas.ExperimentSelect:
    return crud.experiment.select(db, id=id)

if the schema isn't fulfilled, it errors out, whether it is as input or output

cruds and schemas are made from pydantic.BaseModel with sqlalchemy.ext.declarative.as_declarative decorator

this might effectively be a bit more boring to do than with prism because you separate typing from your model, adding a layer to your code architecture

[grizzled wadi]

I see, this works but seems very cumbersome and also limiting, how do you write a complex query using this setup?

[foggy shore]

but that actually has an advantage of separating actual database typing from python typing

[foggy shore]

exactly the same way, for relations you can just call a schema as a type to a property in another schema

[grizzled wadi]

What do you mean? You write a Prisma Schema file that is then the one source of truth for your models

[foggy shore]

so you have schemas separated from your models ?

[grizzled wadi]

In both the python representation and in the database

[foggy shore]

in sqlalchemy with pydantic, the model is the source of truth for the database
and if you use stuff like alembic you can automatically synchronize tables and types between your code and the database
and the schema is the source of truth for the python code

[grizzled wadi]

You can define your own schemas based off of your models so you don't have to duplicate type definitions https://prisma-client-py.readthedocs.io/en/stable/getting_started/partial-types/

[foggy shore]

ye so that's exactly the same thing as what i've showed earlier

[grizzled wadi]

You can do the same with prisma db push and Prisma Migrate

[foggy shore]

prisma seems just to be sqlalchemy, pydantic and alembic bundled together tbh

which is already what's used in the state of the art of backend nowadays

i'm not trying to say your work is bad dw ^^ just that i wouldn't state that it's the "first fully type-safe ORM" as it's not true

it is a type-safe ORM

oh and you should read about tortoise ORM

it's a very cool type safe ORM too

[grizzled wadi]

I said that it's the first as you don't get built-in type safety with other ORMs as you have to write abstractions like what you've mentioned and then that is the only part of the ORM that is actually type safe

[grizzled wadi]

I've heard of tortoise ORM, how is it type safe?

Also with your schemas method for defining queries you no longer have one source of truth, you're duplicating the type definitions

[foggy shore]

tbh, it's only a way of writing it, if you inherit your schemas better than how i did it, the only type you change are optional ones

[foggy shore]

it uses pydantic (or a similar type-based schema maker, depending on how you wanna use it) internally, so you have type checks on your query directly

• it is asynchronous natively

which is also great (offtopic of what we're talking about but i figured i would tell it as it's a great advantage of tortoise imho)

[grizzled wadi]

Sorry I should've clarified, by type safety I mean static type safety, not runtime type safety (although that is built into Prisma too)

[grizzled wadi]

Yeah that is good but imagine being able to just switch to synchronous if you wanted to ;) https://prisma-client-py.readthedocs.io/en/stable/reference/config/#interface

[foggy shore]

but how is prisma doing static type safety then ? are you doing type assertions ?

or are you compiling with mypy ?

(cause that can be done with other ORMs)

[grizzled wadi]

By automatically generating type hints

Which is not done by any other ORM AFAIK

[foggy shore]

that isn't static ?

[grizzled wadi]

yes it is as it's a build step

[foggy shore]

ye but to make sth static you need to have type checks at compile time, which is done by mypy

(and could be done with any of the ORM i talked about too)

[grizzled wadi]

You have to run prisma generate before you're able to write queries

Once you've done that you can run mypy or pyright on your code and it will catch any errors

[foggy shore]

ye well, if you're searching for an automatic schema generation, tortoise has that

Tortoise.generate_schemas()

[grizzled wadi]

Yes but that doesn't generate type hints for you as far as I'm aware

[foggy shore]

but to be fair, prisma still seems like a bundle of stuff that already existed, a nice one for sure, but it isn't really new to me still

[foggy shore]

i'd have to check back my code, i'm on my work laptop now so i don't have my personnal projects running tortoise

but i'll go back to you when i'll be home

[grizzled wadi]

The new feature is the static type safety, it means you can write queries like these: https://prisma-client-py.readthedocs.io/en/stable/reference/model-actions/#finding-multiple-records

With full confidence that they are correct

It also means you get autocomplete which is a game changer for me

[foggy shore]

oh i don't like the fact that you have to call .prisma() on each model :c types should be dynamically added to the models themselves or to instances of the models imho

why don't you put .prisma() inside the __call__() method of your models ?

[grizzled wadi]

What do you mean? You have to call .prisma() so that there aren't any naming conflicts between query methods and model fields

[foggy shore]

or even __init__ actually

[grizzled wadi]

__init__ wouldn't work as that would override pydantic

prisma.models.Post is a pydantic model and .prisma() is a classmethod that contains the query operations for that model

[foggy shore]

ye i get that, but i find the syntax meh :c

[grizzled wadi]

What about this syntax? https://prisma-client-py.readthedocs.io/en/stable/reference/operations/#multiple-records

[foggy shore]

in tortoise, any method you call on a model gets you a QuerySet object, from which you can end by calling all() or first() if you wanna fetch one result
then you get model schemas object with all fields

[foggy shore]

ye this is better

[grizzled wadi]

You can use either or mix and match, it's up to you

[grizzled wadi]

I could add an option to include the query methods in the model as well then?

So you wouldn't have to call .prisma()

The reason I didn't do that in the first place is that you are then limiting the available names for fields

[foggy shore]

na, the fields should be in the model object anyway

[grizzled wadi]

And then adding a new query method would be a breaking change

[foggy shore]

or what you could do is have static methods to make queries

and object attributes for fields, then no problem

[grizzled wadi]

Like there should be separate classes for querying a model and for the actual model itself?

[foggy shore]

that doesn't limit the available names for fields

[foggy shore]

either that, or as i said, separating static stuff from object stuff

having different classes for querying and the model itself isn't a bad idea tho

allows you to chain queries

[grizzled wadi]

With the current setup you wouldn't actually be able to chain queries unfortunately (if we're thinking of the same meaning for chaining)

[grizzled wadi]

Yeah I considered that before but couldn't decide on a naming convention

[foggy shore]

ye like User.filter(field=1).exclude(fields).all()

i'd really advice you to check tortoise, their syntax is really nice for me

https://tortoise-orm.readthedocs.io/en/latest/query.html

here's their query API

[grizzled wadi]

Yeah, unfortunately not supported right now, you have to define the whole query in a single step at the moment

[grizzled wadi]

Yeah, I've considered changing the API in the past but I decided against it due to it being a massive change and meaning you would compromise some type safety https://github.com/RobertCraigie/prisma-client-py/issues/7

That issue was never fully fleshed out and I don't know if that syntax proposal was the best anyway

@foggy shore I still think the biggest benefit for using Prisma is the autocomplete support: https://github.com/RobertCraigie/prisma-client-py#what-is-prisma-client-python

Do you know of any other ORMs that support this kind of autocomplete?

Also Prisma Python supports both SQL databases and MongoDB

[torn sphinx]

is there a way that can make a cluster in mongodb?

[nova cove]

the website

[torn sphinx]

without the website

[nova cove]

you can't?

[torn sphinx]

mmm

i see

[torn sphinx]

i just want the bot to create a cluster when it joins a guild

[nova cove]

oh

i mean it could be possible but ive never messed with mongo

[torn sphinx]

nvm i got it

there is createCollection

but ye tnx

^^

[grizzled wadi]

@foggy shore also just to clarify my original point about type safety, just because you can write abstractions over an ORM to make it type safe, does not mean the ORM itself is type safe. Prisma Python is the first ORM (that I'm aware of) that is built for type safety as a first class feature and the only ORM that can support strict type safety due to it being auto-generated.

[nova cove]

The type safety is one of its core features

[grizzled wadi]

For Prisma Client Python? Yes it is

It's the main reason I decided to build it but another core feature that comes from type safety is the autocomplete which I think many more people will find beneficial

[brave bridge]

EdgeDB + TypeScript seems impressive 👀

[grizzled wadi]

Yeah I heard about that 👀

They have a python client don't they?

[burnt turret]

yeah but i feel like the python client is just like every other raw-SQL python client libraries out there (no special type-safety or anything)

at least from what i understood from the project readme

[grizzled wadi]

Yeah I just had a look

That's a shame

I haven't dived that deep into the TypeScript Client but it does look really good

[torn sphinx]

how do I use postgressql? i only know about sqlite3 and in postgressql i cannot understand how to use, like any library i gotta install or api cuz i see hosting and stuff i am trying to use my project and connect postgresql with it any doc link will be appreciated

[storm mauve]

You must either host it yourself or connect to an external database, provided by some company that sells cloud computing stuff

You can follow this tutorial if you want to host it yourself (on your own PC):
https://www.postgresqltutorial.com/install-postgresql/

[torn sphinx]

thanks

[empty hinge]

To access your Postgres database from Python you’ll as well probably want to use a specialized library like SQLAlchemy

[grizzled wadi]

Or asyncpg or Prisma Python ;)

[empty hinge]

Prisma looks dope. I really look forward to seeing where it is in a year or two

Right now the migrations and performance story looks pretty weak

Also I’d love to not be forced to write async code

[burnt turret]

i believe there's a synchronous client as well

https://prisma-client-py.readthedocs.io/en/stable/getting_started/setup/#synchronous-client

[empty hinge]

Nice thanks. I hadn’t dug very far into prisma yet just looked at the promo material

[grizzled wadi]

Thanks :)

[grizzled wadi]

What exactly do you think looks weak about migrations?

[empty hinge]

Oh lol nice I didn’t realize you were involved

[grizzled wadi]

Yeah I created it lol

The only real downside to Prisma migrations that I've encountered is not being able to seamlessly perform data migrations at he same time

Otherwise it's really good imo

[empty hinge]

I mean that’s a database constraint, right? Can’t do that with Django or alembic either AFAIK

[empty hinge]

Okay maybe I just wasn’t aware- it wasn’t on the front page as a first-class feature so I assumed it wasn’t a thing. Guess I was wrong there

[grizzled wadi]

Yes in some situations but in other situations you might have some models that you store values in that should be updated at the same time as a migration, you can do this but you'd have to run the script yourself

[grizzled wadi]

Ah okay I should definitely document that more then

thanks

https://www.prisma.io/docs/concepts/components/prisma-migrate

@empty hinge would adding Prisma Migrate to the list of core features in the README be enough or should it have it's own section?

[empty hinge]

Por que no los dos?

At least to me as someone who does a lot of production backend work, the details of how migration works for a framework is about on par importance-wise with everything else put together

You could have the sexiest, fastest ORM on the planet but if my team has to write and deploy their own migrations the cost to productivity will never be worth switching away from alembic or Django

IMO

[grizzled wadi]

That is a very good point, thanks

The only reason I would shy away from extensively documenting Prisma Migrate is that there is currently nothing specific to the Python client

And it is documented in the official Prisma documentation

but I should definitely include more documentation on it

[grizzled wadi]

I should also document how to migrate from Django / alembic because migrating the database side is incredibly easy

All you have to do is create a Prisma Schema file with the database connection defined and then run prisma db pull

[empty hinge]

Can I run prisma without needing NPM/NPX?

[grizzled wadi]

Yes you do not need node

Prisma Python packages the node CLI into a single executable and uses that

[empty hinge]

Okay nice. And all the migrate commands work the same just without “npx” in front of them?

[grizzled wadi]

and the interface with the database is written in Rust

[grizzled wadi]

Yes

[empty hinge]

Cool cool cool

[grizzled wadi]

It's the exact same interface :)

[empty hinge]

Thanks for the info. I’ll add prisma to my team’s tech radar

[grizzled wadi]

Wow, that's great to hear :)

Let me know if you have any more questions or need any help, there is also a community discord server as well

link is in the readme (don't know if I'd be allowed to post it here)

[empty hinge]

I’m sure I can find it, thanks

[trim lava]

can someone suggest a good course for SQL on youtube

[limber dragon]

yo

[limber dragon]

How to create a college management system ... (u will also find a playlist)

at least that's what I've found and it was really helpful

[fading patrol]

Not on YouTube but the free introductions on DataCamp, DataQuest and Sololearn are all pretty good

[torn sphinx]

what are some downfalls to using sqlite with django

[storm mauve]

sqlite doesn't scales as well as other databases and has far less features, but for a small project it should be good enough
if you use django's ORM you should be able to migrate to some other database with moderate ease

[torn sphinx]

ok

sounds like i can use it for a starting point for a large-scale project

[foggy shore]

I'd advice you to go straight for bigger databases like pgsql

It's not really more difficult to setup

You'll only loose time having to switch from sqlite to another db

[swift crater]

@grim vaultWas able to get a working solution for the remapping of the position order with a single SQL execution. Thanks for your help as well

UPDATE SELECTION_GROUPS AS t1 SET SELECTION_POSITION = t2.rn FROM (SELECT rowid, ROW_NUMBER() OVER (ORDER BY SELECTION_POSITION, rowid) AS rn FROM SELECTION_GROUPS) AS t2 WHERE t2.rowid = t1.rowid;

[brazen charm]

databases and has far less features
Not strictly true

SQLite can do ALOT

Surprisingly more than Mongo can do

[storm mauve]

I mean, I'm pretty sure that Postgres supports a lot more things than SQLite? not sure if it's a fair comparison though, but neither is a SQL vs a NoSQL

[tropic kelp]

I think that's a very good point

SQLite definitely is missing some features

Why wouldn't you use Postgres?

unless your application is super micro, is SQLite that much easier to setup?

And yes

never use Mongo

[midnight bison]

Hello everyone! i created password manager as learning project.
I'm having trouble fetching data from database I'm using sqlite3.
Here is my code.

import random
import sqlite3


class Password:
    def __init__(self):
        lower = "abcdefghijklmnopqrstuvwxyz"
        upper = lower.upper()
        numbers = "1234567890"
        symbols = "!@#$%^&*()_+<>?"
        self.char_pool = lower+upper+numbers+symbols

    def generate(self):
        pass_len = random.randint(8, 32)
        char_list = [random.choice(self.char_pool)
                     for char in range(pass_len)]
        self.password = "".join(char_list)
        return self.password


password = Password().generate()


class PasswordDatabase:
    def __init__(self):
        self.con = sqlite3.connect("pass.db")
        self.cur = self.con.cursor()

    def execute(self, *args):
        self.cur.execute(*args)

    def commit(self):
        self.con.commit()

    def close(self):
        self.con.close()

    def create_table(self):
        self.execute(
            """CREATE TABLE IF NOT EXISTS passwords(username, password)""")

    def fetch_all(self):
        self.cur.fetchall()


passdb = PasswordDatabase()


class Manager:
    def __init__(self):
        passdb.create_table()
        print("Enter username:")
        self.user = input()
        print(f"Generated password: {password}")

        passdb.create_table()
        passdb.execute("INSERT INTO passwords VALUES(:username, :password)",
                       {"username": self.user, "password": password})
        passdb.commit()

    def find_pass(self):
        try:
            passdb.execute("SELECT * FROM passwords")
            user_pass = passdb.fetch_all()
            return user_pass
        except:
            print(f"No user found by the name {self.user}")


print(Manager().find_pass())

[surreal loom]

any1 here familiar with mysql?

and mysql connector in python?

[fringe gull]

supposing I need the sum of a certain field of a certain table in my database, is it better to search the records with the ORM and then sum them in python, or to sum them using ORM or sql query and return directly the sum form the database, which solution is more optimal ?

[bleak gazelle]

Send phone imu data through local network then to 3d objects (phone modes) green background. With help of HIMUServer and hyperimu app on phone to 3d phone mode on browser or on python. I did mobile with arduino tap to mobile cover https://www.youtube.com/watch?v=RPOkVIm1M9I&t=18s but there is no video on how 2 with just mobile

[grizzled wadi]

It's more efficient to sum them in the ORM / SQL query

[grizzled wadi]

Prisma Python is an ORM but it supports MySQL https://github.com/RobertCraigie/prisma-client-py

But you can still send raw SQL queries so you could use it like a connector you would just have less control

[fading patrol]

What's wrong with Mongo? Sometimes a document store is what you want, SQL isn't always a good fit

[tropic kelp]

When do you want a document store exactly?

[supple comet]

when you need to store documents

[empty hinge]

nailed it

[fading patrol]

For example, if you're trying to store a bunch of complex JSON and there's no point in restructuring it into tables

[empty hinge]

Right. Pretty much all data can be handled as tables in a relational database, but it's not always the best use case. Relational and nonrelational databases have fundamentally different read/write performance tradeoffs and operational costs, and neither is going to be the optimal tool for every job

[pure mortar]

i just used Mongo for hospital records since it was a bunch of crazy JSON

so it really depends on your use case

even built a dashboard with it using Atlas

[glad spindle]

hey guys so like how do i install sqlite?

the module

its giving me this:

[empty hinge]

sqlite3 is in the standard library: https://docs.python.org/3/library/sqlite3.html

[glad spindle]

?

so

sqlite is already downloaded

[empty hinge]

yes

[harsh pulsar]

fwiw you can do this nowadays with sqlite and postgres pretty easily. but mongo is probably better if you need to do lots of queries deep into the json data

[pure mortar]

yeah it was a live dashboard on Atlas

but thats good to know

thanks

ah i think the fuzzy search feature was helpful too

free text is sometimes a real mess

[bleak bough]

which do i use?

oh the last one is missing a unique id as well

and the first one

last one would be the simplest for me to use but had the most redundancy with user ids repeating 50ish times and rows used per person being extremely high

[warm hound]

Can you explain each structure more? What is your data, even

[bleak bough]

amount of items in player inventory and a variable price to buy them with

items are types represented by a letter, a = artifacts

then there are x number of items in each type

when fetching default values for things i use this for example

the item number will correspond to the tuple position

so knowing which number item and type im getting from a table is useful

[bleak bough]

Just realised i can't use database no.2 because it'd make adding new items too difficult

[bleak bough]

my solution was to use the 2nd table structure and generate a ordered string for the object names so it's always in a correct order, then when grabbing a single row i can slice it easily into what i need without worrying about wrong info

[torn sphinx]

can anyone tell me the syntex to add a coloum of foreign key

[tropic kelp]

Generally speaking, I find it's really really important to unjsonize your json into SQL asap.

Storing JSON files is a tech debt mountain. JSON files can have inconsistent schema which breaks shit, because some API client of yours sends you some dodgy JSON.

Arguing over "JSON contracts" stored in confluence is the bane of my existence. JSON schemas not being enforced in code is a huge bugbear.

Hence I think MongoDB itself is an antipattern, for any application except a big ole super low latency key value store.

Which is a very uncommon thing to be building in this day and age.

[tropic kelp]

Querying nested JSON files is: https://tvtropes.org/pmwiki/pmwiki.php/Main/AndIMustScream I have so many horror stories.

[granite pine]

Hi all 👋 Can anyone here help me with an SQLAlchemy query at all? I want to effectively use an EXCEPT SELECT from SQL within SQLAlchemy for my SQLite database. I want to query a bunch of movie IDs in a joined table but exclude certain movie IDs if they are present in another table.

Here is what I got but it's throwing an error - "SELECT statement has a cartesian product between FROM element(s)"

full_movies = db.session.query(movies).join(Sometable, movies.id == sometable.id).subquery()
user_has_seen = db.session.query(User.movie_id).filter(user.seen == True).subquery() 
final_results = db.session.query(full_movies).filter(~Movies.movie_id.in_(user_has_seen))

If I remove the subqueries I get an error - "Column expression or FROM clause expected"

[empty hinge]

This sounds like an outer join. There’s an example in the official tutorial https://docs.sqlalchemy.org/en/14/orm/tutorial.html#querying-with-joins

[sinful rivet]

🗿 Python int too large to convert to SQLite INTEGER

alternative thing so i can store infinite number? 🗿

[harsh pulsar]

this 100%

although it is actually proving useful in one unexpected way: we can pre-compute a bunch of joins and turn them into a single collection of deep json objects

the alternative would be a big flat table with multiple rows per "entity", which would require postprocessing in the application

[proven arrow]

Strings

[sinful rivet]

waht

[blissful finch]

maybe store the number as a string

is what they meant

[torn sphinx]

I am trying to make a website called quipswap. Basically the functionality is that you can create a quip which is a small image and you can send that to someoen on your friends list, so basically in the app, everyone has a list of quips they created, a list of quips they can see because it was shared to them, and a page to make a quip
how would I set up the database for this?

the page to make a quip would allow them to add an image, and a multiple select element to select the friends they want to send the quip to

[strange nebula]

Can anyone help

[torn sphinx]

SELECT category.name, ( SELECT AVG(film.length)
                        FROM film) AS gem_duur_alle,
                        AVG(film.length) AS gem_duur, (gem_duur_alle - gem_duur) as diff
FROM film
JOIN film_category on film.film_id=film_category.film_id
JOIN category on category.category_id=film_category.category_id
GROUP BY category.name
HAVING gem_duur_alle > gem_duur

im getting this error: unknown column 'gem_duur_alle' in 'field list'

does anyone know how to fix this?

[proven arrow]

Syntax is incorrect. Your column is defined as an alias and not available to use yet.

The gem_duur_alle column I refer to

[proven arrow]

You may start by deciding what tables you need and what data needs to be stored. Then it will be easier to visualise the schema. But what you describe is what you would find in a simple messenger application, so you will find schemas online for this.

[noble oriole]

Anyone uses mongodb?

there is a function updates an array

arr_new = [1, 2, 3]
collection.update_one(query, {"$set": {"content": arr_new}})
print(arr_new) #it prints [1, 2, 3]

But when I check the database or make another command to check if the file is updated:

def test():
  print(query['content'])
  #it prints []```
What could be problem here?
It could be seem obvious error at line `collection.update_one(query, {"$set": {"content": arr_new}})` But literally I find no issue here

[torn sphinx]

how can i add values to marks

[proven arrow]

Use 'UPDATE' to update data in existing rows

Insert is for adding new row

[torn sphinx]

thnks

[torn sphinx]

only update or update into?

[proven arrow]

Update

[proven arrow]

By the way I didn’t mean to just replace insert with update.

Your query is still incorrect as to how you are trying to update the values.

[torn sphinx]

to use update i have to specify (use where )

[proven arrow]

Yes, and the new mark value you want to set

See example, https://www.w3schools.com/sql/sql_update.asp

You can check documentation of the database you use for more options available.

[torn sphinx]

oh thanks a lot great help

[torn sphinx]

how should i define it then?

[torn sphinx]

Hello is there a column data type in mysql to store time ranges? I was about to set a varchar() field and throw it all in there😅

This are some examples of what I have right now in an excel column:
13:00-15:00
9:00-12:00 18:00-22:00

[empty hinge]

you can split on the "-" and store that time range in two Time columns, start_time and end_time? that would make sense to me and it'd be fairly easy to write queries to check if something is inside the interval

[torn sphinx]

Yeah that would fit perfectly for the use case where I only have one time range, let's say: 13:00-15:00. But some rows have more than one time range: 9:00-12:00 18:00-22:00.

[empty hinge]

you could have multiple rows in the time_ranges table that share a common ID

then when querying the time ranges, group by that ID column

[torn sphinx]

You mean duplicate rows for each time range

I get your point

[empty hinge]

well not duplicate, since the values in the start_time and end_time columns would be different. but cool

[torn sphinx]

Yeah create a new table containing all the different time_ranges for a particular identifier

Or in the same table

I'll give it a try and see how it works

I could always go back to the starting point where I store those ranges as varchar() and then I have figure out how to handle those values.

[empty hinge]

I would really advise against that. There is a Time data type in MySQL and associated functions that operate on it for a reason.

if you store it as varchar and process it yourself, you're just reinventing the wheel... and you're probably not going to do it as well as people whose only job was making great wheels

[torn sphinx]

You are right, I will follow your lead

Thank you very much

[weak pier]

can someone help me

with TRIGGER

in sqlite

[nova cove]

I create an aiosqlite connection in my bot's overriden start method, which is the following:

async def start(self, token, reconnect) -> None:
  DB_PATH = "./bot/db/bot.db"

  async with aiosqlite.connect(DB_PATH) as self.db:
    BUILD_PATH = "./bot/db/build.sql"

    if os.path.exists(BUILD_PATH):
      with open(BUILD_PATH, "r") as f:
         await self.db.executescript(f.read())

    await self.db.commit()

  await super().start(token, reconnect=reconnect)

I want to reuse this connection in my cogs as I don't want to have to connect each time I want to execute a query. How could I go about this?

[empty hinge]

initialize the connection as part of the startup of the app, then re-use it elsewhere by using it to spawn cursors for querying instead of using the connection object to perform queries (if applicable, depends on if your database supports cursors).

If you want to be fancy, you could create the database connection as a singleton: https://www.tutorialspoint.com/python_design_patterns/python_design_patterns_singleton.htm

[nova cove]

idk bout that one🗿

[nova cove]

can you show an example?

[weak pier]

anyone wants to help me with trigger in sqlite?

[nova cove]

what about them?

[weak pier]

i have to ensure that unitprice and quantity from table 1 is the same as totalamount form table 2

and its so confusing

have to do it in trigger

[empty hinge]

I'm not an expert in async, but the pattern would like something like so.
in app.py:

def do_some_work(db, where_value):
    cursor = await db.execute('SELECT * FROM some_table where value = %', where_value)
    row = await cursor.fetchone()
    rows = await cursor.fetchall()
    await cursor.close()

if __name__ == "__main__":
    db = aiosqlite.connect(DB_PATH)
    for v in range(100):
      await do_some_work(db, v)
    await db.close()

you initialize the db connection once, then instead of closing it

open and close cursors

[nova cove]

ok

ty

[empty hinge]

I think this is a little bit less impactful for sqlite than say, mysql or postgres

where this pattern has more significant advantages

it would also be normal to set db as a global variable instead of passing it around.

[nova cove]

the only thing is the method that creates the db connection is part of a class, and I would need to access the self.db variable in classes in other files

[empty hinge]

Maybe something like this? I'm not an expert in sqlite syntax:

CREATE TRIGGER ensure_unit_price_quant_match 
   BEFORE INSERT ON table1
BEGIN
   SELECT
      CASE
    WHEN 
            ( 
             SELECT unitprice*SUM(quantity)
             FROM table1
             GROUP BY unit_id
             WHERE unit_id = NEW.unit_id
            ) != (SELECT totalamount FROM table2 WHERE unit_id = NEW.unit_id)
        THEN
         RAISE (ABORT,'Insert breaks expected totalamount')
       END;
END;

It sounds like maybe your situation might be more complicated b/c you need to update both tables at the same time? Adjust quantity or unitprice in table1, then expect adjust totalamount in table2 to match? I'm not sure if doing both operations in the same commit would satisfy the trigger or no. Is there a reason not to use a view or function to define totalamount as a calculated quantity, instead of using triggers to enforce it to be accurate? I may have misunderstood

[empty hinge]

do the initial db connection outside of that class, then pass it into the class initialization. Or create the db variable in a higher scope.

[nova cove]

okl

[empty hinge]

OK I understood correctly. what you want is a view, not a trigger. https://sqlite.org/lang_createview.html.

then it will automatically update when you insert data into orderitem

Ah

well consider my example above, but think about how you could change the CASE-WHEN-THEN-END logic to update values in a different table

instead of raising an error.

the trigger documentation is a bit spooky at first, but once you get the hang of reading it, it's very helpful. https://sqlite.org/lang_createtrigger.html

but like, outside of school assignments... that behavior is what a view is for lol

[storm mauve]

what was the best way to check "and"-style things in a many-to-many relationship table? ( #help-rice for context )
For example, given the table ```
WEAPON_ID PROPERTY_ID
1 1
1 5
2 2
2 4
2 5

edit; I found <https://stackoverflow.com/questions/38954934/how-to-query-data-based-on-multiple-tags-in-sql/38955049>

[brazen socket]

...
    sock = await self._connect_sock(
  File "/usr/lib64/python3.8/asyncio/base_events.py", line 924, in _connect_sock
    await self.sock_connect(sock, address)
  File "/usr/lib64/python3.8/asyncio/selector_events.py", line 494, in sock_connect
    return await fut
  File "/usr/lib64/python3.8/asyncio/selector_events.py", line 526, in _sock_connect_cb
    raise OSError(err, f'Connect call failed {address}')
ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432)

i tried to connect ti my database but this happened

i completely given up on googliing this

after like 20 searches nothing

[cobalt cedar]

What db would be better for a eco bot?

[nova cove]

it shouldn't matter

postgres would be a solid choice

[cobalt cedar]

What do you know about mongo?

[nova cove]

mongo is good too, but it might be overkill for a small bot

[brazen socket]

should i open a help channel using this?

[cobalt cedar]

I can’t figure out how to get either lmfao ig ima just work on my main bot finish it then do eco

[tropic kelp]

Making a materialised or exporting a materialised view to do exactly this is super normal behaviour in the RDBMS world

still sounds like it'd be a whooooole lot easier to do it in RDBMS land. But what would I know, maybe your data is huuuuge.

[sterile herald]

Does anyone know how to transfer a json based code to mongodb?

[past berry]

does anyone here have experience with cassandra / django ?

[brazen socket]

how do i make/find a pg_hba.conf file in heroku?

nvm fixed

[fading patrol]

https://pymongo.readthedocs.io/en/stable/tutorial.html

[sterile herald]

Thank you

[harsh pulsar]

right, exactly. also i agree that this is not a good reason to start using mongo, but at least it's kind of a nice thing if you are already stuck using it

i definitely dont understand why my current org uses it - when i asked our senior engineer, he said "it's easier to deal with because there's no schema, i love it", but we are now using ODMantic and we have migrations anyway

so the schema is there, it's just ad-hoc instead of enforced by the database

literally the only benefit is that we can store our arbitrary json blobs as "json" and not "blobs"

we dont ever query into them anyway so it doesnt matter, but i guess theoretically it saves time re-parsing the json

but they are in fact json documents, so i guess it's a good match

but only some things are "documents" along those lines - most of our data is still basically relational and tabular

[empty hinge]

I will never understand this opinion

It's so much harder to write code to handle schema-less data IMO

[grizzled wadi]

Yeah then you'll never know what structure the data will actually be in

[harsh pulsar]

or you do control the schema from start to finish, at which point the schema "exists" but there are no static guarantees against people making mistakes

[grizzled wadi]

Yeah I never understood the appeal of MongoDB

If you can't model your data to a consistent schema then something is wrong

[harsh pulsar]

one real-world use case is our CRM, which stores text as a kind of rich text json thing

so if you want to put that in a database, either you have to serialize it, use a database that has native json support, or use mongodb

[grizzled wadi]

Ah I see, although by definition isn't MongoDB a database that has native JSON support? So by choosing MongoDB it's the same constraint as having to choose a database that has native JSON support?

I don't know if that makes sense or not

[empty hinge]

AFAIK all major RDBMSs support JSON datatypes now so this is no longer really a question.

[harsh pulsar]

pretty much, although idk how good it is

[harsh pulsar]

i meant "rdbms with json support"

[empty hinge]

in postgres it's great, tho if you want to index based on stuff in the JSON column that gets tricky

but easy to implement and write queries against.

[harsh pulsar]

yeah that's when stuff like mongo makes sense

e.g. you have some super high volume of documents with inconsistent schemas

mongodb has pretty good "null safe" behavior around missing fields for example

[empty hinge]

Like, I totally get the need for full-text-search databases. And I totally get using a non-relational database when you don't need relational data. I don't get how somehow "non-relational" got conflated with "no-schema'

[harsh pulsar]

that is fair too

if you could do something like have mongodb enforce a jsonschema upon writes and updates to a collection, that'd be really nice

but i think the argument is that "your app can/should do that anyway"

[empty hinge]

right. like typescript but for nonrelational dbs

[harsh pulsar]

the problem is that then the dev team totally lacks the discipline to actually do that in a lot of cases (e.g. mine)

[empty hinge]

right

[harsh pulsar]

and then they realize it fucking sucks and have to go slap on something later

or you just leave it and say "oh well"

i dont know how any senior engineer could think that's a good tradeoff

but here we are

only reason im still in this job is the good benefits 🙄

[empty hinge]

this ain't career discussion but good benefits are far from rare in software. Even a lot of startups these days have full benefit packages

[grizzled wadi]

Switch to Prisma Python and that'll validate writes for you ;)

MongoDB support is only at Preview at the moment but it's getting close to general availability https://www.prisma.io/docs/about/prisma/releases#preview

[harsh pulsar]

i want to do this anyway 🙂 but i have 0 chance of getting anyone to use it at my current org

if i ever write my own project from scratch i will definitely use prisma. i love the idea of it and i hope that they move to a more "cross language" approach w/ a spec for the prisma language itself

[grizzled wadi]

Yeah I hope so too, I know that they are definitely much more focused on their Node client at the moment unfortunately

They recently deprecated their Go Client :/

It just wasn't getting enough usage for it to be worth them working on apparently

The only real blocker for proper cross language support is https://github.com/prisma/prisma/issues/10527

Of course actually implementing the clients in multiple languages is a lot of work but because the core is written in Rust it isn't that difficult to implement a minimal POC client

[harsh pulsar]

that's nice, i didn't realize it was a rust library

it's too bad they deprecated the go client, seems like some 3rd party could have adopted a project like that

[grizzled wadi]

Yeah, they did say anyone was welcome to fork the repo and continue development but still

Actually the most difficult feature for cross language support is normalising the Prisma DMMF (AST) types so that they can be easily used for multiple languages, right now I'm writing my own type definitions so to speak even though they already exist in the DMMF

They've spoken about trying to change the internal representation so that it's easy to use for any language but that is incredibly difficult

[paper imp]

Hi, I'm using mariadb python connector to execute querries well I'm trying to. I keep getting not all arguments converted during string formatting error even if my sql is just select * from space has anyone else faced the same issue? my code ```py
import mariadb
....
conn = mariadb.connect(
user="...",
password="...",
host="...",
port=3306,
database="...."

)

...
part bellow is inside async function
and conn =.. is at the beggining of the file
...
cur = conn.cursor()
while True:
try:
re = json.loads(await websocket.recv())
ts = re["data"]["timestamp"]
price = str(re["data"]["price"])
ts -= ts % 60
cur.execute("SELECT * FROM candle;")
print(f"{ts} | minute {ts - ts % 60}")
...
except Exception as error:
print(error)```

[tropic kelp]

CRMs all use a consistent schema if that makes sense

So you can easily do a JSON to normal form transformation

really Mongo is just a crutch for lazy engineers who don't want to do SQL

[harsh pulsar]

it doesn't make sense to do this with e.g. rich text that is encoded as json

i'd argue that xml is actually a better format for this data than json, but everyone hates xml now so

but yes mostly it's relational data

and i agree, it's laziness, trading the illusion of developer efficiency against actual database efficiency and safety

[tropic kelp]

It's not so much about the database performance as such

it's more about the tech debt of Json schema mutation, and not integrating with SQL etc

etc etc

[harsh pulsar]

that is also true

[tropic kelp]

like ultimately, Mongo is secretly a very low level database

You're essentially coding a bunch of stuff yourself, which isn't good

[harsh pulsar]

that's a good way to explain it

[torn sphinx]

Hi everyone, I am trying to connect to my postgres DB but its saying failed password. this password works on pgadmin4 when logging into the db however, not sure why its failing

nvm idk wat i did it works

Wrong password.

And don't reveal your password mate , anyone would be able to use it

hey, can i ask for help on a channel?

if it's databases related you can ask it here
And yea you can always get a help channel #❓｜how-to-get-help

[torn sphinx]

well i already occupied a help channel, but idk if anyone checks those

People surely check them

[brazen socket]

why does this say it doesnt exist?

[kindred onyx]

i am trying to do cosine similarity in a mongo database

but not sure what the right approach is

if anyone can tell me how to do dot product that would be a big help

[river matrix]

Hey, when building a DB.

How necessary is normalization? Say given a large dataset, how would you scale it on a priority of 10

[empty hinge]

If you’re using a relational database, it’s very important. The entire functioning is designed assuming the user will have normalized data split up between multiple tables. Conducting schema migrations if your database is not normalized properly can be incredibly hard

[river matrix]

Thank you.

@empty hinge So question. Since normalization also affects space and by eliminating data redundancy. Why when linking the Primary key and foreign keys. One can find that foreign keys are repeated. Which means the redundancy returns.

So, does this mean int in (pk, fk) take up less space than other data types?

[empty hinge]

Normalization reduces data redundancy by replacing the more complex data - such as an email address, a big ol row of data representing a user profile, whatever - with just a single integer. Then say you have ten tables across your database that need information about user profiles - instead of having a full copy of all that data in every one of those ten tables, you just have a single column of integers.

Does that answer your question? I didn’t entirely understand your intent

A single integer column only takes a few bytes per row to store (depends on what kind of integer). That’s WAY less space than literally any other data type

[river matrix]

Ah, I see. Your explanation is very good. I can see it in my head what you mean. I'll note it down.

I see. Thank you.

Normalization is a good database designing step. But are there times when one could consider deformalized for queries. I assume normalized take up more time to query as well as to write/code. Given how many more tables I'd need to join in the lather.

Or is it always just better to normalize?

[empty hinge]

In relational databases, it’s almost always better to normalize your data. If you want a denormalized view to make querying easier… you use a view (or materialized view) that’s built as a query of the normalized tables. Then you only have to figure out that complicated query once.

And your assumption that it takes a relational database engine longer to query normalized data is mostly incorrect.

AFAIK it’s usually the reverse. Relational database are designed up from the “how bits get written into RAM” level assuming you will be normalizing your data. They are exceptional at efficiently working with it.

SQL itself is as well designed to express logic with normalized data efficiently, and you may find yourself writing very awkward queries with lots of case statements and huge where clauses if you try to make a complex query on denormalized data

[river matrix]

I see. Thank you very much, @empty hinge. Your help is greatly appreciated.

[lapis oriole]

Hello everyone

I have a table in PostgreSQL that's used for logging some stats over time.
However, some points are repeated. Thus, what I'd like to do is to remove those repeated points. Let me explain :

I have a column timestamp and a column count.
The behaviour of the logging program is to log an entry every 15 minutes, and on every change of the value to be logged in count.

That means that I may have many consecutive points that hold the same data.

What I would like to do is to drop all those useless points, keeping only the points that represent a change in the logged value, dropping the points that hold the same value as the point right before them

[fading patrol]

You could set a trigger to prevent this in the first place if you want to, but it shouldn't be difficult to design a query or set of queries to clean up the existing data

First result on Google, for example: https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/

[lapis oriole]

Thank you, but I don't want to delete all duplicate values, only consecutive ones

It's a bit more complicated than that

[fading patrol]

So just add that to your logic, based on the index value

[iron hull]

Hi there, I have this data set and i need to trasnpose it, so the dates become the index and the courier payments the columns respectively, the .transpose() does not work, any tips ?

[harsh pulsar]

is this a pandas question? you should be clear about what libraries you are using

[iron hull]

sorry. yes is a pandas question

[harsh pulsar]

also normally pandas questions get asked in #data-science-and-ml . "databases" are kind of a different thing - this channel is about interacting with external databases e.g. with sql

the distinction is a little hazy so it's ok

as for your question: "does not work" is meaningless and impossible to help with @iron hull. what exactly happens when you try it? does your computer get up and walk away? do you get an angry email from your mother? etc.

i believe that is how asyncpg Row objects work, but you should double check the docs (or just try it)

[iron hull]

I solved it thanks

[harsh pulsar]

check the postgres docs: https://www.postgresql.org/docs/current/ddl-default.html

postgres has great documentation

[raw blade]

i'm using python's google.cloud.firestore to to batch commit documents to firestore--right now, it's a vanilla

client = firestore.Client()
batch = client.batch()
# iterate and batch.set(document_reference, data)
batch.commit()

but i'm seeing a lot of 409 Too much contention on these documents. Please try again. what can i do to mitigate this? should i just blindly retry? this batch commit happens pretty often for me, is there a better approach than wrapping my batch.commit in a try/catch that retries?

[harsh pulsar]

check the docs!

[brave bridge]

try https://www.db-fiddle.com/

(don't forget to choose PostgreSQL and not MySQL)

[harsh pulsar]

there is a drop-down selection menu somewhere in the top left

and yes that site is excellent, i use it all the time

[brave bridge]

how did you install it?

Online playground to try SQL queries:
https://www.db-fiddle.com/
You can choose your database engine (MySQL, PostgreSQL, SQLite) in the top left corner.

[harsh pulsar]

i'm surprised we didn't have that pinned already

really it's the best way to ask sql questions

because you can actually show what your schema is and other people can run your queries

it will usually be translated to None

asyncpg has a table somewhere in the docs showing all the type conversions

[brave bridge]

it lets you put a naive datetime into a TIMESTAMP WITH TIME ZONE column 😩

[harsh pulsar]

it what? yuck

well yeah, infractions is a row here. asyncpg has a separate "fetch value" function for this. other database libraries you just need to get the value manually

[brave bridge]

yeah it uses your local timezone

[harsh pulsar]

i hate this

i hate timezone handling in the stdlib in general but

ugh

at least tzinfo is in the stdlib now finally

[brave bridge]

yeah, having the same type but with an optional tzinfo is 😬

maybe they should've checked out my article 🙂
https://decorator-factory.github.io/typing-tips/antipatterns/optional-attributes/

[paper imp]

I have a question regarding connecting to db is there a performance difference in creating multiple connections for executing a lot or inserts (if I have 10 runners each executing a lot of requests does it make sense to have a new connection to the db for each one?)

[brave bridge]

WHERE only applies to SELECT. It means roughly the same as if in a list comprehension

what do you want to do?

[drifting belfry]

create_table.py
from engine import engine
from Base import Base

Base.metadata.create_all(bind=engine)

Poll.py
from sqlalchemy import Column, Integer
from Base import Base

class Poll(Base):
    id = Column(Integer, primary_key=True)
    option_picked = Column(Integer, nullable=False)

Base.py
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from engine import engine 

db_session = scoped_session(
    sessionmaker(autocommit=True,
        autoflush=False,
        bind=engine
    )
)

Base = declarative_base()
Base.query = db_session.query_property()

[drifting belfry]

python create_table.py doesn't work, the engine is correct

the table isn't created

[drifting belfry]

fixed it by importing the model in create_table.py

[harsh pulsar]

fwiw you can capture these invariants with complicated overloads... but nobody wants to do that

[rocky arch]

Hey, I am basically a beginner and I already created a Game bot, you get a picture and have to guess some words (with a time limit etc).

Now I want to get this bot on multiple Server and I have to rewrite a lot. And I want to do it right this time.
Right now I am using the python List which look like this "[[user,[date, wins, etc...]], [user,[date, wins, etc...]...]]"
What kind of Database can you recommend and can you recommend any yt videos or anything like this?

(pls ping me)

[fading patrol]

Multiple servers need to access the same db simultaneously? If so I'd probably go for Postgres.

You can spin up a free instance on Heroku pretty easily

If you've never touched a SQL db before, maybe play with SQLite a bit first just to get the concept

[rocky arch]

Okay thanks I will have a look at it 👌

I want to safe stuff in the same db, yes. But all games are independent from each other

[fading patrol]

Postgres would handle that fine

[rocky arch]

Okay stupid question, I have to learn sql for this right?
What are possible boundaries?

And I think the panda modul in python is also somehow a Database right? I am just getting into all of this 🙈

[empty hinge]

Pandas is a way of working with data frames within python. Databases are a separate application, not running and storing data within the python process

[rocky arch]

Okay yea got it. Okay lets go learn sql...😅

[empty hinge]

It’s an easy language, you’ll pick it up quick.

[fading patrol]

You don't need to go crazy deep with it if you're just storing and retrieving simple rows of data. There's loads of extra stuff byou can do with SQL but most of it's not important for your use case

[rocky arch]

Okay thanks, I worked with it just a little bit back in school. But Anyways I think its important to know sql.

[patent raptor]

    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    for i in range(1000):
        i += 1
        i = str(i)
        name = 'bubblegum_pig_'
        if len(i) == 1:
            i = '000' + i
        
        if len(i) == 2:
            i = '00' + i

        if len(i) == 3:
            i = '0' + i

        name = name + i

        await cursor.execute(f"SELECT exist FROM shop WHERE image_name = {name};")
        exist = cursor.fetchone()
        if exist is None:
            sql = (f"INSERT INTO shop(image_name, rarity, exist) VALUES(?, ?, ?)")
            val = (name, 'common', 'yes')
            await cursor.execute(sql, val)

    db.commit()
    cursor.close()
    db.close()

why does this not work it returns the error no such column: bubblegum_pig_0001

[bleak bough]

there's gotta be a better way

[grim vault]

Use the binding form also for the select, not only for the insert.
And there is a simpler way to make a number with leading zeros:

    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    for i in range(1000):
        name = f"bubblegum_pig_{i+1:04}"
        cursor.execute("SELECT exist FROM shop WHERE image_name = ?", (name,))
        exist = cursor.fetchone()
        if exist is None:
            sql = ("INSERT INTO shop(image_name, rarity, exist) VALUES(?, ?, ?)")
            val = (name, 'common', 'yes')
            cursor.execute(sql, val)

    db.commit()
    cursor.close()
    db.close()

And are you using the standard sqlite3 module or an async version of it, because you can't use await for the standard module.

[patent raptor]

ok thanks

[torn sphinx]

Access denied for user 'root'@'localhost' i m constantly getting this error can someone tell me how to fix it
i m using mysql and using php to access data from the table in my database

@fringe mountain this

[fringe mountain]

What's the name of the database

[torn sphinx]

login_system1

[fringe mountain]

Idk php but if this is mysql I can help

Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
Add skip-grant-tables under [mysqld]
Restart MySQL
You should be able to log in to MySQL now using the below command mysql -u root -p
Run mysql> flush privileges;
Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Go back to /etc/my.cnf and remove/comment skip-grant-tables
Restart MySQL
Now you will be able to login with the new password mysql -u root -p

[torn sphinx]

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; i need to type this command?

[fringe mountain]

That's after u finish the first instruction

[torn sphinx]

i m not getting what u r saying coz i m using xampp

so ....

[torn sphinx]

oh wait , what after typing that command under mysqld

@fringe mountainhey how do i know my root passowrd?

[fringe mountain]

U should know? Idk how php works but u can check musql docs

[torn sphinx]

uhm

these technical glitch take hours 😭

[torn sphinx]

i m using is there any command to check the password?

[lapis oriole]

Thank you, it's a good idea, but from what I've seen, if I partition it just groups based on the value of the supplied columns. But I want to group locally repeated values.
Lemme explain : If I order by timestamp, let's say I have this :

7 7 7 7 6 6 7 7 6 5 5 5 6 6 6 6 6

I'd like to group it as :
[7 7 7 7] [6 6] [7 7] [6] [5 5 5] [6 6 6 6 6]

And keep the first value of each group
But I don't see how to do it with a partition

[proven arrow]

You can achieve this by using a window function like row_number. Allowing you to give each row in eacb partition a row id. Then it’s just a matter of deleting from all partitions where row id is larger than 1.

[grim vault]

I have never done this, but it seems to work: https://www.db-fiddle.com/f/qaSeDSYmxWMczUn3BYQ3pF/0