databases

[fervent charm]

The world if duckdb allowed concurrent writes

[wise goblet]

the world without JS being primary language in browser 😏

[somber iris]

World without JS*

[atomic moon]

Messing around with SQLite3 for the first time and I have a question

# create connection
con = sqlite3.connect("tutorial.db")
# create cursor
cur = con.cursor()
# create table
cur.execute("""CREATE TABLE movie(
            title TEXT, 
            year TEXT, 
            score TEXT)
            """)

# check that the table exists
table_check = cur.execute("SELECT name FROM sqlite_master")
print(table_check.fetchone())
print(type(table_check.fetchone()))

Why is the type of the last command NoneType? I expected a tuple based on the print line above it

[dawn shard]

fetchone() gives you one row and moves the cursor to the next row

if there is no next row, you get None

[atomic moon]

ah got it. that makes sense

think i ran into something similar with writing to files (forgetting where the cursor was)

[torn sphinx]

What is the best way to get TTL in postgres? I am using SQLAlchemy

[topaz widget]

What do you mean 'TTL in Postgres' exactly?

I didn't think pg had a built-in expiry system?

[torn sphinx]

It doesn't, I am looking for something similar to redis where you can set a ttl and the value is deleted after that

My app already uses a postgres database, I do not and can not afford to setup a redis server just for this one thing

[wise goblet]

as an option u could just add "deleted_after" field
And make sure to query db with accounting for Now being before Deleted After. (That it will be same as quering as still TTL not deleted records)
Run some while true process to delete periodically deleted completely if desired

This combo requires no extra libs 😄

[terse viper]

Can't you just run a psql function on insert and other actions combined with the field mentioned above

[wise goblet]

so many problems can be solved, with while true in the background

[wise goblet]

i used them... and in my opinion they are overly nasty experience a bit.
Difficult to write PSQL
Wrongly written PSQL function works per each row, while u wish efficient bulk one. Easy to screw up performance basically with those PSQL functions
With some effort u can probably write properly bulk deleting one

keeping code logic in general purpose real programming language, even if it is scripting language python, i think better idea
(Pushing all those triggers into Celery to run for example, or just as code logic to run in code after your queries)

[terse viper]

Fair

[stiff plume]

I am learning fastapi. It gives me an error relative import error
How can I solve it?
I'm trying to import my models, schemas to my main.py file. but throws an relative import with no known parent package error

[worthy loom]

try running with python -m path.to.your.main instead of python path/to/your/main.py, this treats it as a module

[stiff plume]

What does -m stand for?

Why do we create a virtual env for this?

[worthy loom]

   -m module-name
          Searches sys.path for the named module and runs the corresponding .py file as a script.

virtual env? use it if you want, it's pretty useful

[stiff plume]

Yeah, in the tutorial it mentioned to create a uvicorn virtual env project and running the project from main folder

How is it useful? I'm pretty new to this. I have worked on node.js and express.js it was easy except for the middleware thing. Are they any middle ware here?

[worthy loom]

virtual env lets you install deps in an isolated place instead of to your entire system, node npm does exactly the same thing just with less steps

[stiff plume]

OH yeah got it

I just started with FastAPI should I do something else before or am I in the right place

[worthy loom]

their tutorial recommends putting everything in main.py, if you do that it should be fine

otherwise i think if you put your other .py files in a subdirectory with an empty __init__.py you will be able to relative import them, but not sure about that

[stiff plume]

I was following this tutorial which follows putting everything in an empty init.py
https://www.youtube.com/watch?v=7t2alSnE2-I&list=PLe30vg_FG4OSKH_8zpLlnf4WpNlzL526E&index=7&ab_channel=Bitfumes

[worthy loom]

yeah i always just use python -m but fastapi wants you to run some other command to start things up

[stiff plume]

Yea

[torn sphinx]

thank you, I think I will go with this, a follow up if you don't mind

where can I run a process in the background? I am using fastapi

[wise goblet]

make separate entry point file. background_loop.py
and just have while true there with cleaning up db periodically 😄

[wise goblet]

if u don't use multoprocessing to run fastapi, u can launch background_loop from the same process as fastapi app
just sending it to work in a background process

fire and forget process

[wise goblet]

multiprocessing in python looks like this

be careful in not launching multiple of them

if u will be using fastapi with multiprocessing way to launch it, u may spawn more than one not intentionally

[timid brook]

Hmm confusing myself trying to update my sqlite executes with placeholders
writeCursor.execute("INSERT INTO ? (charge_data, charge_name, amount, tag_id, notes) VALUES (?,?,?,?,?)", expenseBatch)
throws an OperationalError near "?"
Ive tried wrapping the first in () as well it just changes to Error near "(". the tuple has 6 items and should be working but I guess im not using the placeholder right but it seems to match the docs

[versed shore]

I don't think you can put a qmark placeholder for the table name. But I might be wrong

[timid brook]

maybe that was why I ended up doing this instead I think that is right
insertString = "INSERT INTO {0} (charge_date, charge_name, amount, tag_id, notes) VALUES ({1})" .format(mytable, expensedata)

[versed shore]

Might be more readable with an fstring

[timid brook]

yea but I didnt actually need to 'read' it perse, this was just done because you also cant do the string format within the cursor.execute() iirc.

so you have to build the string, then give it to the execute to run which is indeed not best practice for sql injection reasons. But I suppose I could just sanitize data before doing this since id need to parse it out anyways

[versed shore]

You can combine those two techniques. Use an fstring to fill the table name, use a tuple for the qmark parameter for .execute()

[timid brook]

hmm I guess I could try that

[versed shore]

Then at least you only get the injection risk for the first replacement, which is something most fuzzing techniques won't hit I think

[timid brook]

well the table name isnt user controllable so Im not worried about it. Its simply generated dynamically at first setup because I wanted to make my handlers more universal for the future

and that actually seemed to work. need to check the DB if it got written properly

[versed shore]

i just tested this:

import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE "testtable" ("id" INTEGER, "field" TEXT, PRIMARY KEY("id"))')
<sqlite3.Cursor object at 0x000001DBDE0C9AC0>
con.commit()
con.execute('insert into testtable (field) values ("lala")')
<sqlite3.Cursor object at 0x000001DBDE0CA4C0>
a = ('testtable',)
con.execute('insert into ? (field) values ("lala")', a)
Traceback (most recent call last):
File "<input>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error

I know SQLite accepts table and field names between quotes, and I know that qmark substitution using a string generates the quotes around the value (probably uses repr()) so I thought it might be that but:

con.execute('insert into "testtable" (field) values ("lala")')
<sqlite3.Cursor object at 0x000001DBDE0CA9C0>

[timid brook]

this worked

insertString = "INSERT INTO {0} (charge_date, charge_name, amount, tag_id, notes) VALUES (?,?,?,?,?)".format(expenseTable)
  try:
    writeCursor.execute(insertString, expensdata)
    dbconn.commit()```

[versed shore]

you can try to find your dynamically created table name in the sqlite_master table. just select * from sqlite_master

[timid brook]

yea I have all that handled elsewhere when I initialize the table and db and thats where my expenseTable comes from so all good there.

[versed shore]

that's what i meant yes!

how do you paste python code with syntax highlighting?

(new here)

[timid brook]

Yea now it looks like I need to fix some of my fields again and do some more formatting. This is a CLI -> TKinter conversion of my program heh

!code

[delicate field]

Formatting code on Discord

Here's how to format Python code on Discord:

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

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

For long code samples, you can use our pastebin.

[versed shore]

ahaw! i knew about the backticks for discord, but i didn't know it supported py

[timid brook]

you can also do !e to eval/execute snippets too

its not a default pretty sure it was custom on this server among other things. the bot here is next level. can pull docs from py docs too

!d functools

[delicate field]

functools

Source code: Lib/functools.py

The functools module is for higher-order functions: functions that act on or return other functions. In general, any callable object can be treated as a function for the purposes of this module.

The functools module defines the following functions:

[versed shore]

i just tested it on my own server, works the same. it's baked into the discord client pretty sure

[timid brook]

ah cool, I know Git has it too since its part of markdown so maybe its all universal like that

[versed shore]

alright glhf with your sqlite journeys, imma go write out the post to request help which I joined the server for 😅

[timid brook]

haha, gl as well

[timid brook]

hmm now to decide how I want to handle duplicates

[atomic moon]

i'm new to sqlite and just learned about placeholders in that exchange above

so is the idea that you can only use placeholders for data within the table and not the table name?

hence using string formatting

[versed shore]

correct, i don't know if there's a way to use qmark substitution except in values(), set clauses, join clauses or where clauses

on a tangential topic, since it's also database related, I was originally planning to post a question here or in #1035199133436354600and in writing the code example I think I accidentally wrote a functioning baseclass to add a .persist() method to any (data)class and generate the necessary insert and update statements. sort of like the super ultra thin version of SQLAlchemy or something (it mostly uses

type(self).__dict__['__static_attributes__'] 

)

[unkempt widget]

whats the best database in yalls opinion, i absolutely love using postgresql locally for my projects, pgadmin is crazy good

[dawn shard]

For general use, postgres. If you have a more specific workload, there's probably a more specific recommendation to be made.

[unkempt widget]

i use postgres with redis for caching its pretty good for social media app, website and discord bot use cases

[wise goblet]

i prefer to just use Sqlite3 for pet projects/discord bots 😏

it is just a simple file, no extra libs

<@&831776746206265384> scam

[rich trout]

!ban 1370205530685182004 scam

[delicate field]

:incoming_envelope: :ok_hand: applied ban to @full vine permanently.

[unkempt widget]

i mean i use them on a production level n tie them to my websites or apps but ive never used sqlite tho

[dawn shard]

In a key-value transactional database, what does the C part of ACID transactions refer to?

[versed shore]

That a transaction should leave the database in a consistent state, ie conforming to the rules that define the format of the data and the internal relationships

[dawn shard]

I guess it is just this trivial in a kv context.

[lean walrus]

Imagine an app which we can buy items, which has multiple shops (table shops) and each shop has multiple items, should I store the item details as a JSON in the database:

{
  "store1": [{
    "name": "Hamburger",
    "description": "..."
    "price": 5.00
  }]
}

or create a new database table (shop_items) which has a foreign key pointing to the shops table?

[craggy jungle]

sad to see that prisma python client has been archived 💔 looking at some alternative libraries like sqlmodel, peewee and tortoise orm, any input from anyone?

[versed shore]

Both would work, but I prefer to only use json fields when I don't know the exact format of the data. For instance, for a logging table where the log context needs to be saved but can wildly vary, it's a lot of work to make tables for all those different formats. Rather just dump it into one field. For situations where the data design is clear-cut like yours, I'd rather leverage the consistency granted by relational databases.

Another thing, what if you want to select a list of all products with a price of more than 10 dollars? If your products are in json, you'd have to first go through all shops and get the json out and filter it programmatically. On table items you can do that in sql in one go

[quaint atlas]

Excuse me, sir. I'm really stressed out because my code is acting strangely — the data retrieved by the code is different from the actual data, even though the database URL is correct

I tried printing the data using code; here's the code:

if user_data := UserModel.objects(email=email).first():
            if account_active_data := AccountActiveModel.objects(
                user=user_data
            ).first():
                print(account_active_data.token_email)
            else:
                account_active_data = AccountActiveModel(
                    token_email=token_email,
                    token_web=token_web,
                    created_at=created_at,
                    updated_at=created_at,
                    expired_at=expired_at,
                    user=user_data,
                )
                account_active_data.save()
                return account_active_data```

Please help me, I feel like my head is going to explode — I'm really confused.

[somber scaffold]

if you would be so kind as to Dm me i could help

[quaint atlas]

output: eyJ1c2VyX2lkIjoiNjgxZjg1NDZlZGYwNjM3MWY2ZTIzZjEyIiwiY3JlYXRlZF9hdCI6MTc0Njg5NzU1N30.Vl_rkcdrkvHgsKQYSIIXqGiIJFM

[quaint atlas]

What do you mean by DM? Why not just talk here?

[hexed estuary]

what's the difference, though?

[versed shore]

At first glance it looks identical up to the CI6MT part

[cunning plover]

what you guys think of nosql databases with no relations? when to use? any projects examples and why?

[dawn shard]

There are sort of three categories AFAIK

• performance -- ScyllaDB, e.g. discord messages. No SQL DB can feasibly handle that much data if discord is to be believed. In general write - heavy usecases
• simplicity -- SQL is really complex, both to implement and to use, rethinkDB comes to mind as a DB that chose to not use SQL.
• data is fundamentally not relational -- sort of performance again, but here is where I'd put graph databases like neo4j.

[cunning plover]

I went to website rethinkdb and it is for live updates like a chat app or leaderboard in games.

interesting never heard of them

nice they have an api for python

[dawn shard]

Yeah, I find their choice of type system (literally just JSON) a little cursed, but it's clear a lot of engineering went into it.

Zero and electric are in the same category of databases (and of course firebase and such).

Well, neither is a database, they're layers on top of a database.

[cunning plover]

how does this technology work? how is it like a socket connection, or how can this database deliver real time data ?

[dawn shard]

Websockets or long polling

[cunning plover]

ah what I thought

I am glad you mentioned rethinkdb never heard from them

just new mongo, postgres, mysql, ....

[dawn shard]

There's been something of an explosion in specialized databases in the past years

[drifting frost]

I am trying to write an update statement for sqlalchemy however I dk how to do it properly. I am getting the following error: TypeError: sqlalchemy.sql.dml.ValuesBase.values() argument after ** must be a mapping, not ArrangementDTO

    async def update(self, arrangement: Arrangement):
        arrangement_dto = self._dto(
            **arrangement.model_dump(exclude={"author", "file"})
        )
        arrangement_dto.author_id = arrangement.author.id
        if arrangement.file:
            arrangement_dto.file_id = arrangement.file.id
        stmt = (
            update(self._dto)
            .where(self._dto.id == arrangement.id)
            .values(**arrangement_dto)
        )
        await self._session.execute(stmt)
        return await self.get_one(id=arrangement.id)

[real kraken]

Cool

[timid brook]

sounds like a type error as the values are supposed to be a dict

[amber owl]

check the type of arrangement_dto, is it a class that is a mapping? maybe don't unpack it (remove the double **) when you pass it to .values

[drifting frost]

yes but I dk how to pass it all of the arrangement_dto values __dict__ does not help because it contains extra fields

[drifting frost]

it is a class yes but the function wants dictionary specifically

[amber owl]

It expects a mapping, a dictionary is a mapping because it suports key lookups, you can have other classes that can be passed

what you need to investigate, is how to get the values of that _dto method as a mapping

[paper flower]

Why are you doing an update manually instead of updating your orm object? 🤔

[drifting frost]

Because thats the only thing that came to my mind

because I can like select a an orm object then update it

but if I create it manually from the fields I get

it has no association with the session

unless there is a way to associate it

[paper flower]

I meant that you could just get said object from session and mutate it's fields, then call session.flush()

It would generate an update statement and will use RETURNING to refeftch any fields that are generated on serverside

[drifting frost]

I would need some kinda for loop that goes through model_dump and mutates the fields then?

[paper flower]

Yes, you could use setattr for that, but I personally prefer to just map all the fields manually, since that could be checked by type checkers (e.g. mypy)

[heady zinc]

I ran into error with mongodb database. I ran into "ServerSelectionTimeoutError pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.nz9zg.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1006)" error when trying to run my flask app from another system (mac os). Please what's the cause of this error? Thanks

I need help

[drifting frost]

I see ya that would make sense 😁 to bad there is no convenience method for that already

thank you

src/routers/user/dto.py:41:33: F821 Undefined name `FeedbackDTO`
   |
39 |         back_populates="author"
40 |     )
41 |     reviews: MappedColumn[list["FeedbackDTO"]] = relationship(
   |                                 ^^^^^^^^^^^ F821
42 |         back_populates="author"
43 |     )

another silly question is there a way to make lsp shutup about mapped column types (can't really import em coz of circular deps)

[paper flower]

Why is it called a dto if it's (most likely) inside of your orm model?

But also, just import it inside of an if-type-checking block

[drifting frost]

I call orm models dtos 🥺 is this wrong?

I make like dto.py and models.py

1st one sqlalchemy 2nd pydantic

[timid brook]

mixing and matching is probably just making it confusing tbh

[paper flower]

I personally don't add any suffixes for them since they're not DTOs

Also you probably want Mapped instead of MappedColumn 🤔

[drifting frost]

ya but if I import both

in a file

I need to differentiate

can't really call both User

[drifting frost]

hm ya wait whats the difference even

all my files have MappedColumn

I checked docs and they have Mapped

maybe missimported once and copied everywhere

it does work tho

[paper flower]

From my understanding DTO is just a set of data that needs to be transferred from one part of your application to another, e.g. to call a function/method or to perform a specific operation (e.g. ArrangementCreateDTO).
Pydantic models could be DTOs, but I mostly use them in the API part of my application, and name them Schema, so full path of the data looks like this:
Framework(e.g. FastAPI) -> UserCreateSchema -> Endpoint -> UserCreateDTO -> Service/Repository -> User

[paper flower]

I think Mapped has the correct type annotation that would return T from MappedColumn[T] when called on instance and InstrumentedAttribute[T] when called on class

if typing.TYPE_CHECKING:
    @overload
    def __get__(
        self, instance: None, owner: Any
    ) -> InstrumentedAttribute[_T_co]: ...
    @overload
    def __get__(self, instance: object, owner: Any) -> _T_co: ...
    def __get__(
        self, instance: Optional[object], owner: Any
    ) -> Union[InstrumentedAttribute[_T_co], _T_co]: ...

[drifting frost]

from typing import TYPE_CHECKING

if TYPE_CHECKING:
    from collections.abc import Sequence

def func(value: Sequence[int]) -> None:
    pass

you mean like this?

[drifting frost]

Ic, Mapped is indeed correct need to fix my stuff

if TYPE_CHECKING:
    from src.routers.user.dto import UserDTO
    from src.routers.feedback.dto import FeedbackDTO

this did indeed helped

never knew you could do this

so should I better call the files orm.py and the ORM models like UserORM respectively

I do use schemas notation for Create and what not

    async def create(self, user: UserCreate) -> UserWithPassword:
        user_dto = self._dto(**user.model_dump())
        self._session.add(user_dto)
        await self._session.flush()
        await self._session.refresh(user_dto)
        return UserWithPassword.from_orm(user_dto)

one of examples

[paper flower]

No need to call refresh by the way 🤔

[paper flower]

I think naming-wise that would be more clear

[drifting frost]

but I want to get the id

or does add update generated id

[paper flower]

Yep, if your db supports returning

[drifting frost]

here am using postgres

[paper flower]

then just refresh should work, check if it does

[drifting frost]

does indeed work 🤯

will this work with sqlite also?

[paper flower]

I think so

I don't remember if it supports returning, but it most likely does, and if sqlalchemy has any workarounds for DBs that don't have it

[grim vault]

SQLite 2021-03-12 (3.35.0)
...
4. Add support for the RETURNING clause on DELETE, INSERT, and UPDATE statements.

[bold aurora]

Hi I'm using mysql: how can I make it so that I can protect my database from being able to be edited or destroyed

[paper flower]

Edited or destroyed by who? 🤔

[bold aurora]

Anyone I want to implement a test mode too I know it seems odd but I have some friends if they got into the database like if I had a catalog of scenes and how many they were they might switch everything around delete Fields I want to make it so that if they get onto my computer or if I load it on to their computer so I can make a quick update of how my seeds I have they can't once I disembark the program can't edit it just as a precautionary measure you always have to have a precautionary measure in the North

[paper flower]

I think what you want is accounts with correctly set up permissions

[bold aurora]

So make a account login screen?

[paper flower]

You said access to a db, you meant direct access?

If you're making something like an API you shouldn't allow to execute SQL directly in the first place 🤔

same applies to a GUI app if that's the case

[bold aurora]

I'm just trying to make it to you worry now it's a pet registration database and instead of having to make it in case somebody moves and then have to transfer that specific part of viable to the program itself why not have it I know update using networking and the people they are said it would be right here also thank you

[timid brook]

That was a bit hard to follow. But for protecting a database that needs to be editable by multiple users you need to setup account permissions properly within your application/UI. What is the form of your application? is it a GUI program with a local database? client/server to a database? web page?

[bold aurora]

Im trying to make an account again in

A gui with a database

[trim aurora]

Sorry I'm gonna be that annoying guy that links his help post: https://discord.com/channels/267624335836053506/1371896139145678938

The general TLDR is: I'm somewhat new to databases, and have a project that requires them, but have pretty much no oversight (or mentors) to help with it, and I want to be sure I'm doing things in a somewhat logical way!

[timid brook]

and what is the gui written in, did you create it? how is it currently being used by users, etc

[bold aurora]

It's written in Python
Yes I'm I'm still working on it some safety so nobody ends up being able to add it without some type of verification

[fervent charm]

what do you like about pgadmin

[stiff plume]

I am running the command properly but the website keeps buffering. Now sure where things are not going right.

Is it the virtual environment ? I'm new to fastapi

[valid robin]

Hey guys!

I'm building a pure python implementation of a postgresql db. Our main use-case at Abstra is allowing users develop their automations locally without having to install any third party dependency

Beside testing mocks, I see many other applications of that, such as querying in json files easily (I'm assuming most ppl knows SQL more than jq), simulate transactions for operation based apps.. and fun! haha

It is still WIP, but as haven't saw anything similar yet, so I'm sharing just in case anyone find that useful

https://github.com/abstra-app/json-sql

Also, I'm accepting better repo names suggestions haha

[unkempt widget]

I mean the UI is great I hate that there’s no dark mode 😂 but it works locally query editor and the table viewer is nice it’s def nice I mean it’s just the ui for Postgres, also it’s free

[atomic moon]

Came across stored procedures in SQL, which appear to be SQL’s version of user defined functions

Is there a reason to use these in SQLite?

Actually google says SQLite doesn’t even support them

[dawn shard]

Generally, stored procedures are helpful to reuse SQL across queries. It also used to be common to put all of your business logic into stored procedures, and only have the application shimmy data from the db into JSON/xml

[atomic moon]

i assume that's faster than writing in say python and having it converted on the backend?

[dawn shard]

It's not really about performance, it's largely a philosophical choice.

[atomic moon]

i see

[atomic moon]

since you said this used to be the case, what is the standard now

[dawn shard]

what you're already familiar with. Use an ORM, and have the database largely work as a persistent data store.

all your logic goes into your application code, usually Python in this server.

[atomic moon]

I honestly have not used an ORM directly but I get the idea

SQLite is not an ORM right it is the database and SQLalchemy is the ORM?

[tepid basalt]

That's right

[atomic moon]

I have been learning SQL via MySQL and also some Postgres

And then the class introduced SQLite briefly in Python but most of the querying has been directly in SQL

I was wondering if I should look into sqlalchemy but it seems like it may be overkill

I just want to do basic CRUD to make a database on disk from some web data I scraped and SQLite seems sufficient. What would the use case for moving to sqlalchemy

[torpid plover]

What do you do when your building full stack website and then you want to finally start building the database i'm new to it and would like to know the steps on how to efficiently set it up without many problems, any advise?

[dusty blaze]

use msql workbench

[dawn shard]

The big advantage you get is having queries return Python objects rather than tuples.

which makes things like changing the schema easier (see migrations)

[torn sphinx]

Guys I need help with python pandas

This is my code for a login system, I'm using a csv as the database

from subprocess import call
import pandas as pd
import time

#func for opening files on command
def openfile(x:str):
    call(["python", x])


#setting up the dataframe and email variables
df = pd.read_csv("CSV_Files/logindata.csv")
df.set_index('Email',inplace=True)
emp_email_end = "@can.emp"
adm_email_end = "@can.adm"


#signup and login
print("***** Welcome To  Login Page *****")
choice_signin = input("Would you like to login or signup?: ")

if choice_signin == "login":
    mail = input("Enter your email:- ")
    pwd = input("Enter your password: ")

    act_pwd = str(df.loc[mail][0])

    if pwd == act_pwd:
        if str(mail).endswith(emp_email_end):
            print("Welcome Employee!")
            time.sleep(3)
            openfile("Python_Code/employee.py")
        elif str(mail).endswith(adm_email_end):
            print("Welcome, Admin")
            time.sleep(3)
            openfile("Python_Code/admin.py")
        else:
            print("Welcome To The Canopy!!")
            time.sleep(3)
            openfile("Python_Code/customer.py")

    elif pwd != act_pwd:
        print("Password is incorrect")   

elif choice_signin == "signup":
    new_mail = input("Enter Your Email ID:- ")
    new_pwd =  input("Enter Your Password:- ")

    df.loc[new_mail] = [new_pwd]
    
    if str(new_mail).endswith(emp_email_end):
        print("You cannot register with the company email!")
    elif str(new_mail).endswith(adm_email_end):
        print("You cannot register with the company email!")
    else:
        print("Welcome To The Canopy!!")
        time.sleep(3)
        openfile("Python_Code/customer.py")



#updating the csv file
df.to_csv("CSV_Files/logindata.csv")```

I'm getting this exception when I use the login choice as ''login''

FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]` act_pwd = str(df.loc[mail][0])

For reference, I set the index of the csv file to the email names
not an integer index
I want to get rid of the exception, since need to show this as a school project, and I'm trying to use the try, except method, but it's not working
Please help

[fading patrol]

LOL this is gross. Could you use SQLite instead maybe?

Putting that aside, the warning looks straightforward... So, did you try what it suggests (iloc in place of loc)?

[timid brook]

also dont post the same thing in multiple channels. and I responded in the other channel as well. heh

[torpid plover]

Is that free of cost

And also I keep getting recommanded to use PostgreSQL by my LLM

What are your thoughts on that type of data infrastructure

[dawn shard]

Postgres is probably fine for your usecase.

[torpid plover]

Yea

And what about using supabase, thoughts on this?

[dawn shard]

I have heard good things about supabase, but it is not the simplest thing around AFAIK

[torn sphinx]

Unfortunately no. This is for a school project, and we aren't allowed anything except python

[torpid plover]

How so? Im curious

[dawn shard]

It's built on a lot of open source tech, which all have their own quirks.

[torpid plover]

Ok I see is it more complex or no?

[torn sphinx]

Yes, i tried iloc, but it only takes integers as an argument. mail is an input class

[dawn shard]

It's probably more complex than using postgres with an ORM.

[torpid plover]

And would you say for a full stack website I use something else

[torpid plover]

Ok I see

[dawn shard]

Look at the supabase feature set and see how much of it is actually relevant to your website.

[torpid plover]

Ok

But what would you recommend

[dawn shard]

I'd expect you could toss a coin and end up with a good outcome either way. I'd probably go with postgres + Django for a fullstack website.

[timid brook]

also sqlite is a default package of python 😉

[brave bluff]

I've got a sqlalchemy issue. I have a model which I'm joining subsequently onto 3 other models:

        statement = select(Item).filter(Item.id == item_id)
        if include_purchases:
            statement = statement.options(
                joinedload(Item.purchases)
                .joinedload(Purchase.receipt)
                .joinedload(Receipt.store)
            ).order_by(Receipt.date.desc())
        else:
            statement = statement.limit(1)

However, it errors:

| sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.UndefinedTableError'>: invalid reference to FROM-clause entry for table "receipts"
| HINT:  Perhaps you meant to reference the table alias "receipts_1".
| [SQL: SELECT items.id, items.name, items.notes, stores_1.id AS id_1, stores_1.name AS name_1, receipts_1.id AS id_2, receipts_1.store_id, receipts_1.date, receipts_1.notes AS notes_1, purchases_1.id AS id_3, purchases_1.item_id, purchases_1.receipt_id, purchases_1.price, purchases_1.amount, purchases_1.notes AS notes_2 
| FROM items LEFT OUTER JOIN purchases AS purchases_1 ON items.id = purchases_1.item_id LEFT OUTER JOIN receipts AS receipts_1 ON receipts_1.id = purchases_1.receipt_id LEFT OUTER JOIN stores AS stores_1 ON stores_1.id = receipts_1.store_id 
| WHERE items.id = $1::INTEGER ORDER BY receipts.date DESC]

It's creating aliases for the joined loads, so the order by doesn't work directly, but I'm missing in the docs how to actually resolve it.

[torpid plover]

Ok thanks for the feedback!

[versed shore]

i think you might need to use df.loc[mail].iloc[0]

long shot but worth a try

[torn sphinx]

The actual fuck

this worked???!

How lol

Thanks so much man

[chrome finch]

hey

can you help me

[torn sphinx]

I'll read up on why this is a thing

[torn sphinx]

Sure, depends on the problem

[chrome finch]

i want to make a selfbot

on discord

which notifies me whenever a transaction is done on my ltc address

ion know single thing about python

but i got a code from github and im not sure how to use it

@torn sphinx

ltc is a crypto btw

[torn sphinx]

I'll try to help, but I won't be of much use. I only know the python basics like loops, ifs and all that

But I'll try

[chrome finch]

alr dm

imma give you the code

just help me how to run it

[deft apex]

potential scam? ^

[paper flower]

As far as I'm aware this is not allowed by discord ToS, make a regular bot.

@torn sphinx Also be careful with whatever code people share to you.

[versed shore]

You're welcome! I just did what the error suggested: replace the bare index access by iloc

[chrome finch]

He did help me

[chrome finch]

Ikr but I js wanted to know

[torn sphinx]

Yeah, Ik, I didn't download it

I just guided him

[torn sphinx]

I tried replacing the loc with iloc, but clearly that wasn't the answer

[versed shore]

the i in iloc probably stands for index. so iloc provides index access to the DataFrame. If you want key access (like you do with the key in mail), you use loc. i do see that iloc is being deprecated in pandas, so might want to look at the docs here a more future proof solution (or stick to your current working Pandas version) https://pandas.pydata.org/docs/reference/frame.html#indexing-iteration

[jolly pollen]

hi developer
I col

i couldnt connect to my database which is hosted on azure It couldnt connect with an error for timout even I have public ip access in azure

[timid brook]

Yea I think for me when I was looking over that I misunderstood that it meant replacing loc with iloc, not nesting separating the name and index with the two. interesting

[timid brook]

probably not something we can help with as that sounds more like an Azure issue unless you know the connection works and is possible manually but the issue is in your code. But you are going to need to share more details like what exact error you got, code you were using, etc (and remember not to post public IPs, passwords, keys, etc)

[dusty blaze]

Yes

[jolly pollen]

Even i have used pgadmin the same error

[timid brook]

Sure but have you checked the side of this like I suggested or provide the information I asked about? Have you verified the connection at least works, checked the Azure side, what error are you getting, etc. It still sounds like an issue between you and Azure unless you provide some more info

[jade aurora]

hmmm

[timid brook]

yea, not really a python problem 😉

[jade aurora]

[twin ridge]

Hi,I am deciding between a building a data warehouse project or a project that involves big data concepts, data lake, machine and basically data analytics for real-time recommendations. I'm unsure which to go for. Is there anyone who worked on either and can share their opinion on how their experience was like while working on on their work/project?
I am asking this because as soon as I choose my final year project then that is likely the field I will be going into as a junior developer (whatever u call it) since this would be the biggest project I ever produced (when I complete it).

(Idk if #data-science-and-ml was the right channel so I wrote my question here as well)

[timid brook]

data science is probably the right place and either way its best not to post in multiple channels because you end up with scattered replies and possibly repeated information

[timid brook]

@stark glacier So would you say I am reading the docs correctly in that regard? It seems like while neither ROWID nor primary key are technically required when you create a table like that it still generates something equivilent to that in order to save the table to disk. I think personally I have trouble thinking of a use case where you would specifically want or need a table the explicitly does not have either of those attributes.

[stark glacier]

SQLite in particular creates a ROWID column on all tables by default, and using it for lookups appears to be faster than using an index or primary key for that same lookup.
It's possible to designate a table WITHOUT ROWID, but it states in the docs that a primary key is required in that case.
Other than that, primary keys are not strictly needed by any SQL table (except for that specific instance in SQLite using WITHOUT ROWID).

[timid brook]

Ok then overall I was reading it right but I think in my head I certainly transposed the meaning of ROWID with primary key where the ROWID is functionally a primary key but not technically one because the key is optional unless you create the table without ROWID. And the other side where a primary key can be multiple columns I completely missed.

[stark glacier]

Further than this, in a standard table if you do:

CREATE TABLE foo(
    something INTEGER PRIMARY KEY
)
``` ...then `something` becomes an alias to that `ROWID` column. 🙂

https://sqlite.org/lang_createtable.html#rowid

[timid brook]

oh yea because of the integer keyword. I did see that. So yea you can use the ROWID as a primary key with a name of your choosing if you specific INTEGER along with it.

totally unrelated to that. Working on my project here I found a corner case on pdf imports into pandas' dataframes that I lose the - operator for some transactions to the neighboring column. This is my solution which so far has worked fine in testing. Is this a reasonable way to do this?

    for row in all_imports.itertuples():
        if row[3][-1] == "-":
            #print(f"Minus sign found in charge_name at index {row[0]}")
            all_imports.loc[row[0], "transaction_name"] = row[3][:-1].strip()
            all_imports.loc[row[0], "transaction_amount"] = "- " + row[4]
        else:
            pass```
The pdf format wont change though such modifications will be needed for other pdf sources but so far that is expected.

[brittle lion]

i need help with pillow and tkinter. if someone could guide me? its for school project and online resources aint helping

[timid brook]

Dont just ask for help, ask what you need. What is the project scope? What have you done so far? Share some code and more info

[unreal pumice]

You can try the #1035199133436354600 channel too

[brittle lion]

Its due in 4hrs and i have am only left with pillow module with tkinter, i have done everything else

I dont know how to use those, no idea at all

[topaz widget]

My buddy just sent me this cursed MySQL example.. Enjoy!

-- When you really don't want to send a bound parameter twice:
SELECT * FROM filtered_table NATURAL JOIN (WITH f(p) AS (VALUES ROW(?)) TABLE f UNION ALL SELECT unhex(p) FROM f) f(filter_column) LIMIT 1

[topaz widget]

Haha OMG, I think my buddy is having a psychotic break.. he also sent this:

CREATE FUNCTION JSON_KV (input JSON)
  RETURNS JSON
  DETERMINISTIC
  SQL SECURITY INVOKER
RETURN (
  SELECT JSON_ARRAYAGG(JSON_OBJECT('key', JSON_UNQUOTE(k), 'value', v)) FROM (
    SELECT k, JSON_EXTRACT(input, CONCAT('$.', k)) v
    FROM JSON_TABLE(JSON_KEYS(input, '$'), '$[*]' COLUMNS (k JSON PATH '$')) jt
  ) jkv
)

UPDATE images i
  CROSS JOIN LATERAL (
    SELECT
      JSON_MERGE_PATCH(i.metadata, JSON_OBJECT('formats', JSON_OBJECTAGG(jt.k, IF(JSON_LENGTH(JSON_MERGE_PATCH(jt.v, jt2.o)) = 0, CAST(NULL AS JSON), jt2.o)))) metadata
    FROM JSON_TABLE(JSON_KV(ANY_VALUE(metadata->'$.formats')), '$[*]' COLUMNS (k TEXT PATH '$.key', v JSON PATH '$.value')) jt
    CROSS JOIN LATERAL (
      SELECT JSON_OBJECTAGG(k, null) o
       FROM JSON_TABLE(JSON_KV(ANY_VALUE(jt.v)), '$[*]' COLUMNS (k TEXT PATH '$.key', v JSON PATH '$.value')) jt2jt
      WHERE k REGEXP '^(_.*|[0-9]+)$' OR JSON_LENGTH(v) = 0
      GROUP BY i.id, jt.k
    ) jt2
  GROUP BY i.id
) md
SET i.metadata = md.metadata
WHERE i.metadata->'$.formats' IS NOT NULL
``` I'm tempted to click `Report abuse` on his gist haha

The execution plan isn't even bad.

[topaz widget]

Huh.. PostgreSQL doesn't have unsigned ints? Somehow I didn't know that.

[trim aurora]

Hello smart database people, I was sent here from General. I'm working on a discord bot that runs a stock picking game. Multiple games can be created, so I am storing the games (and their configuration) in an SQLite database. The problem is, I keep thinking of new settings that games need to have, and then I have to add more columns. To get around this, I am thinking of having a single settings column of type BLOB, and then storing the actual rules in JSON. Is that stupid?

[paper flower]

What kind of settings?

If you tell about how they'll be used (e.g. if those settings are global, or maybe bound to a specific server/user) it would be more clear on if JSON is a good solution there.

[trim aurora]

Hi thanks for replying!
So the basic idea for the game is that users will pick X number of stocks, and then it will track their progress over a date range.
The settings will mostly be static after game creation, but would need to be checked semi often when updates to prices/users are made.
Rules will be things like

• Sell stocks after start (True/False)
• Pick same stock as another user (True/False)
• Stock blacklist (list)
• Stocks can be picked after start (True/False)
• Stock picks can have different amounts of money assigned (True/False)

But we keep thinking of more and more

[paper flower]

Wdym after game creation? You mean after starting or bot or game is started individually, multiple times per server? 🤔

If it's dynamic then storing your settings in db is the best option, JSON doesn't sound that bad as you could use pydantic or similar library to validate them and have some typing information too.

[trim aurora]

For now the plan is one bot per server, but the server can have multiple games running. The hope is that once it is released, we can add features later without fucking up existing games, so existing games can keep running even if we add a bunch of new settings

[paper flower]

Ideally after adding a new setting you'd migrate your db and retroactively add that setting to old games too, so you could get rid of pydantic's defaults:

from pydantic import BaseModel

class GameSettings(BaseModel):
    can_sell_after_start: bool

We add a new settings:

class GameSettings(BaseModel):
    can_sell_after_start: bool
    new_setting: bool = True # There has to be a default value so old games could be deserialized from JSON stored in the db

We must have a default there so old settings could be deserialized into your new model, ideally you'd run a DB migration that adds new option into old settings.

That's not a definitive way of doing this, but I'd do it that way 🤔

[trim aurora]

Got it, I'm new to databasesa and have no frame of reference on what is considered OK or NOT OK to do. It seems like adding new columns to existing tables is actually not bad as long as there is a default added?

I guess I just got it in my head that I shouldn't do that

[paper flower]

Yeah, just adding a new column is also ok, you can either set a default or retroactively populate old rows

e.g.

alter table mytable add column some_column boolean; -- <- Columns are nullable by default
update table mytable set some_column = true;
alter table mytable alter column some_column boolean not null; -- <- Making column not null after we populated old rows

[trim aurora]

So generally would the way to add new features just be to add columns? For example, someone requested we store the percentage gain/loss for each user and game in the database.

[trim aurora]

Actually this leads to another question I have, which is how to decide what data should be stored, vs just calculated as needed?

[paper flower]

This would probably be in player's profile/statistics, not game settings, but yes - you could track amount of games won/lost and calculate win % that way.

[paper flower]

You usually don't need denormalization, especially in small projects

[trim aurora]

So right now I have a games table that stores game information AND game settings. Would you splitthose?

[trim aurora]

IDK what that means, sorry :(

[paper flower]

Good example of denormalization would be instagram's likes: https://medium.com/@AVTUNEY/how-instagram-solved-the-justin-bieber-problem-using-postgresql-denormalization-86b0fdbad94b
Denormalization usually means storing redundant data that could be calculated instead

[trim aurora]

Got it! Thanks for the help. If you have a few minutes to spare, here is my current database creator (schema?). Feedback would be very welcome!
https://github.com/ItsJustAGitHubMichealWhosGonnaSeeIt5Ppl/StockGame/blob/pre-launch-dev/sqlite_creator_real.py

[paper flower]

It's actually very cool that you've set up migrations yourself, currently in python the most popular tool to do that is alembic btw, you could either do that or come up with your own solution which could be fun

And preferably all the changes should be inside migrations

You can keep current revision number/id in a separate table by the way

[trim aurora]

Oh cool! I code mainly as a hobby, so I like trying to solve things without imports first, but probably better to use something standard long term haha

[trim aurora]

As in store the actual database version somewhere? I was thinking that it would probably be a good idea to have a like backend_info table that stored information about the database itself

[paper flower]

Yeah, for example alembic keeps a list of migrations (as files) which point to previous revision:

# 2022-11-21-16-01-33_6a96534104bc_initial.py

revision = "6a96534104bc"
down_revision: str | None = None

# 2022-11-22-14-13-41_62db563d4f53.py

revision = "62db563d4f53"
down_revision = "6a96534104bc"

That way alembic orders them, then you could check the latest revision number in db table and apply migrations as needed

[trim aurora]

Oh tht is very cool

[trim aurora]

Now is that per table, or for the whole database?

[paper flower]

It works per db, multiple tables could be changed during one migration/revision

I'm not sure about how well that would work with sqlite, but you usually should have one database and segregate your data with something like guild_id to check to what discord server something belongs

[trim aurora]

Yeah I was actually thinking about this yesterday. Current set up expects thte bot to be active in only one server, which may not always be the case!

[paper flower]

i.e. instead of keeping two or more databases:

server-one.sqlite3
server-two.sqlite3

You have one db where all/most data has guild_id:

playerprofile
guild_id | user_id | wins
1          42        5
2          42        10

[trim aurora]

Yep, that makes way more sense!

For guild ID, would you directly reference a discord guild ID, or make a table to store guild IDs, and then have an internal number used in the database

[paper flower]

Just guild id is fine, they're static, same with user id

[trim aurora]

Im scarred from my work project, which is building a billing history tracker. The data from one of our vendors had a device_id column, BUT THE IDS CHANGED FROM MONTH TO MONTH FOR THE SAME DEVICE

I hope the person who did that get drop tabled

[paper flower]

Sound rough 😅 I'd just complain to the vendor

[trim aurora]

Oh I did

They did not help

We;re swapping away form them anyway so whatever.

But anyway, thank you so much for all your help, this gives me so much more clarity on how to do things

[paper flower]

lol

@trim aurora Btw, I'd look into alembic as a migration tool, and sqlalchemy as an orm, that's if you want to use one

It's very useful IMO, but SQLAlchemy specifically could be hard to learn

[trim aurora]

Will do, thaks again!

[brave bluff]

I'm using SQLA's ORM functionality, where I'm joining two tables that have a relationship on them. However, I want to filter the table being left joined. In SQL, I'd add another condition to the ON clause, but it doesn't seem like I can do that in SQLA, at least not directly?

select(Item).filter(Item.id == item_id).join(Item.purchases, Purchase.price.isnot(None), isouter=True)

In my Item model, there is a relationship to Purchase:

purchases: Mapped[list["Purchase"]] = relationship(back_populates="item")

And I get this error:

No 'on clause' argument may be passed when joining to a relationship path as a target

My goal is to do this:

SELECT (fields) FROM items LEFT JOIN purchases ON items.id = purchases.item_id AND purchases.price IS NOT NULL

Anyone know how I can achieve this?

[paper flower]

stmt = (
    select(Item)
    .filter(Item.id == item_id)
    .join(Item.purchases.and_(Purchase.price.isnot(None)), isouter=True)
)

@brave bluff

[brave bluff]

I'll give that a shot, thanks!

[bronze fulcrum]

Guys, how can I query for all the records with a specific field containing some text? I'm using sqlalchemy, but if you can't give your answer in sqlalchemy, plain SQL is also acceptable

[icy glacier]

I would imagine that the select function along with a filter of the "text" in Class.column might work.

[bronze fulcrum]

I've done ```py
.where(text in model.text)

For some reason it returns null

[cedar tiger]

@split roost I think your SQL statement needs more work

SELECT season_id
        FROM seasons
        WHERE ? BETWEEN starting_date AND end_date

should be

SELECT season_id
        FROM seasons
        WHERE season_date BETWEEN ? AND ?

[split roost]

no starting_date and end_date are columns in my table

there is only 1 variable being passed in, which is the ?

[cedar tiger]

I don't think that's a valid SQL syntax but sure. Test it out

[split roost]

it should be? itll select the season_id from whichever season corresponds to the date that falls between its start and end date

[cedar tiger]

If the user is only passing in one datetime input to query for all seasons that is happening around that user inputted datetime, I would test

SELECT season_id
FROM seasons
WHERE starting_date <= ? AND end_date >= ?

where the ? in each is the same user datetime input. So the full SQL statement would look like:

SELECT season_id
FROM seasons
WHERE starting_date <= '2025-05-25' AND end_date >= '2025-05-25';

But you gotta test these SQL statements out and see which one works

[split roost]

BETWEEN is an actual thing in SQLlite

[cedar tiger]

I am aware

[split roost]

so why wouldn't mine work

[cedar tiger]

As per the BETWEEN clause, you pass the actual values, not the column names

https://sqlite.org/lang_expr.html#the_between_operator

[split roost]

bro i actually give up with databases

this website is so damn difficult

why couldn't it just be easy

[cedar tiger]

well... have you tried going through any of the sql learning resources?

like sqlbolt.com?

[split roost]

it's not even that

its just trying to put all the python, html, sql together

such a pain in the ass and i cannot be bothered

[cedar tiger]

This is for that dart tournament webapp, right?

[split roost]

yeah it is

i can't even give up on it lmao

told my friend i would do it

if i didn't have to deliver then i would've given up by now

[cedar tiger]

alright. So, keep it simple and test either out

[cedar tiger]

Do this first and see if it works

[split roost]

i can't test it now since i gotta make all the other parts too

[cedar tiger]

oh ok

[split roost]

got a bunch of NOT NULL columns

so i need to finish those up

[cedar tiger]

alright np

[split roost]

thanks man

[tender oak]

Hi guys! I have a query, I am trying to add group functionality to my project where the users and groups have a many to many relationship. I have written the following code for flask-sqlalchemy

userGroupAssociationTable = db.Table(
    Column('user_id', Integer, ForeignKey('Users.id', ondelete='CASCADE'), primary_key=True),
    Column('group_id', Integer, ForeignKey('Groups.id', ondelete='CASCADE'), primary_key=True)
)

class User(UserMixin, db.Model):
    __tablename__ = 'Users'
    id = Column(Integer, primary_key=True)
    username = Column(VARCHAR(50), unique=True, nullable=False)
    fullname = Column(VARCHAR(50), nullable=False)
    email = Column(VARCHAR(100), unique=True, nullable=False)
    password_hash = Column(VARCHAR(255), nullable=False)
    profile_picture_url = Column(Text)
    is_confirmed = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now(tz=timezone.utc))

    # relationships
    spending_categories = relationship('SpendingCategory', back_populates='user')
    groups = relationship(
        'Group',
        secondary=userGroupAssociationTable,
        back_populates='users',
        cascade='all, delete'
    )

class Group(db.Model):
    __tablename__='Groups'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    created_at = Column(DateTime, default=datetime.now(tz=timezone.utc))
    created_by = Column(Integer, ForeignKey('Users.id'), nullable=False)
    
    users = relationship(
        'User',
        secondary=userGroupAssociationTable,
        back_populates='groups',
        passive_deletes=True
    )

the issue I am dealing with is deleting of users and groups, I think the set of these 3 rules are good enough

1. if a user is deleted, delete rows from the association table (handled by foreign key ondelete)
2. if a group is deleted, delete rows from the association table (handled by foreign key ondelete)
3. if a user that is to be deleted is the only member of the group, delete the group as well.

for the 3rd I was reading up sqlalchemy docs and in it,
it says that when using cascade='all, delete' in a parent (user) -> child (group) many to many relationship, the child entities associated with the parent entity is also deleted. is this behaviour correct though? should I be doing this in the application logic instead?

link to sqlalchemy docs I am referencing : https://docs.sqlalchemy.org/en/13/orm/cascades.html#using-foreign-key-on-delete-with-many-to-many-relationships

[dusty blaze]

i dont really understand what you need but i know that you should not assign values or things to id because its a builtin function

[tranquil aspen]

That’s a class member

[dusty blaze]

yes but id is also a builtin function

!d id

[delicate field]

id

id(object)```
Return the “identity” of an object. This is an integer which is guaranteed to be unique and constant for this object during its lifetime. Two objects with non-overlapping lifetimes may have the same [`id()`](https://docs.python.org/3/library/functions.html#id) value.

**CPython implementation detail:** This is the address of the object in memory.

Raises an [auditing event](https://docs.python.org/3/library/sys.html#auditing) `builtins.id` with argument `id`.

[terse viper]

Sure but it's being assigned as part of a class, not globally

[frail maple]

if theres anyone who worked with flask and could land me a hand trying to fix an issue id really appreciate it #1376603253885767680

[paper flower]

I think using id, filter, etc, as variable/parameter names is fine, if you don't use them in your code base much, plus static type checker would usually help you with that. Also if it's part of a class it's certainly fine, you're very very unlikely to need an id function there.

[paper flower]

I don't think you should be setting up cascades there? You don't want users/groups to be deleted if the other one is deleted, right? 🤨
You already seem to have ondelete="CASCADE" in your association table, and I think that should be enough.

Also I'd recommend using sqlalchemy 2.0 syntax: https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#orm-declarative-models

[weak notch]

guys

i created a py lib to integrate rag with llm apps and agents easier and faster, local first, perfect for validation and mvps. So if you're building saas and mvps and don't want to waste time creating accounts and dealing with pinecone config and etc. take a look here.

with 3 lines of code you can get a rag pipeline,
it has default config for split,,
just pass the chat model and embed model,

feedback is welcome.

https://github.com/softrag/softrag

[wise goblet]

!rule 6 10 , <@&831776746206265384> multi channel spam

[delicate field]

Rules

6. Do not post unapproved advertising.

10. Do not copy and paste answers from ChatGPT or similar AI tools.

[formal current]

!cleanban 350667589648646144 Spam

[delicate field]

:incoming_envelope: :ok_hand: applied ban to @weak notch permanently.

[bronze fulcrum]

query = select(func.dense_rank().over(order_by=model.someval), primary_key)
result = await session.execute(query)
return result.scalars().all()

What am I doing wrong?

I want to rank a user in the database

[bronze fulcrum]

(btw. it's sqlalchemy)

[sullen oyster]

Rank based on what? The syntax is very Microsoft T-Sql, you may wish to play with the native SQL query first to determine what query you want, and then once you have the results shaped, update your sqlalchemy query to generate the query.

[bronze fulcrum]

Yes, that's the problem. I want to rank it based on some filter. And also I'm not too proficient at SQL, so I can't write the query which meets all the criteria

[sullen oyster]

"Rank" is a bit loaded here, so it's helpful to spell out the requirements. Sometimes you can achieve an order based on another value. For example "10 most recent user signups" could be something like: select(User).order(User.created_at).desc().limit(10) and the resulting set will be "ranked" - since the most recent will be the first in the list.

[bronze fulcrum]

I want to make a leaderboard

[sullen oyster]

Then depending on how you can find the scores (either a column on User, or joined against some other table), you could use that to select the user and score, and order by score, descending, and limit to the top number. And you probably don't need the dense_rank function

[bronze fulcrum]

Column on users. If you can, please explain how to do it in sqlalchemy or show SQL query

[sullen oyster]

My previous example ought to be sufficient to adapt - something like select(User).order_by(User.total_score).desc().limit(10) - you'll have to update the names, and decide what to do with the results. The SQLAlchemy docs are pretty good - I'd recommend you spend some time with them to become familiar with the syntax. https://docs.sqlalchemy.org/en/20/orm/queryguide/select.html#selecting-orm-entities-and-attributes

[bronze fulcrum]

I want to get not the users, but user's rank in the table, place from the top. I know I could query and do it on python side via for loop, but wouldn't it be inefficient?

[sullen oyster]

This is more complex than originally presented. I'd recommend learning more about row_number and other SQL functions - potentially other ways to present this data, since efficiency will matter on calculating the entire users + scores to create a rankings table (or intermediate view), so that you can then ask the ranked user table what the user's current ranking is.
Alternately, you could add a column to the User table for current ranking, and periodically update that value, and then be able to efficiently retrieve the value for a given user.

[frail maple]

any one here familiar with flask and sql not cooperating ?

[bronze fulcrum]

First approach is good, but I don't know how to do it in SQLAlchemy. Second approach is not too relevant, cause every time I update the score that's O(n)

[bronze fulcrum]

Guys, what's wrong wih this query?

SELECT rank() OVER (ORDER BY users.score DESC) AS score_rank
FROM users
WHERE users.id = ? AND users.someval = 1

It always returns 1 for some reason

[storm mauve]

rank ranks the records within the subset you selected via the where filter, not relative to all records in the table

[bronze fulcrum]

Can you please explain and show the correct query?

(I can't get how to filter it correctly)

[bronze fulcrum]

Yes, that's what I want. I've checked, it shouldn't return 1

[storm mauve]

you have multiple rows for the same user with the same someval and different scores?
in the users table?

[bronze fulcrum]

Uhh... No

Different users

But I want to query for a specific user's place

[storm mauve]

you need to calculate it relative to all users first, and only then filter

[bronze fulcrum]

Can you please show the query?

I don't know how to apply it in the correct order

[bronze fulcrum]

This is the query SQLAlchemy produces

Etrotta is right

But how to make it produce the correct query?

[brave bridge]

Use a subquery #python-discussion message

well, it should be rank() and not dense_rank() probably

So something like ```sql
SELECT id, score_rank FROM (
SELECT users.id as id, rank() OVER (ORDER BY users.score DESC) AS score_rank
FROM users
WHERE users.someval = 1
)
WHERE users.id = ?

First rank all the premium users, then find among those rows the row where the current user is

[bronze fulcrum]

What's the difference?

I think dense rank will give the same place to users with same score

[brave bridge]

ah, maybe I misunderstood what it means

[bronze fulcrum]

So I need dense rank?

[brave bridge]

The opposite actually. See https://dbfiddle.uk/TTEsTtN_

Or rather, if there are multilpe rows with the same value, DENSE_RANK will only advance by 1 after the gap

[past raven]

Hey which database to use for web apps cloudinary AWS or Google

Or mongo postgresql

[forest sable]

depends on what you building and trying to store

[cedar tiger]

It depends™️ on a matter of preference. Some likes MySQL, some likes Postgresql, some would host their own database instance in a VPS on GCP or Azure or AWS or DigitalOcean or Hetzner. Some would use cloud-hosted databases like NeonDB or Postgresql or Appwrite, etc. Some would prefer a more NoSQL approach with MongoDB.

What kind of web app are you building?

[wise goblet]

it depends on matter of preference, and money budget, and if u doing it for work or pet projects

for Pet Projects best to run stuff at Hetzner, because it is freaking cheap.

Sqlite3 will work best for pet project

[frank imp]

you are right

[wise goblet]

but if your pet project for some reason needing network accessable db with proper parallelization, then Postgresql in container will work better

[frank imp]

Sqlite is so good

[wise goblet]

for work, if you are newbie / you don't know infrastructure as a code tools like Terraform Opentofu / Pulumi
Then good to consider Linode, OVH or Digital Ocean.
They are Simple to use providers, offering Managed databases of Postgresql (and some other ones)
reasonably cheap in their services and easy to configure

Managed databases should be prefered for work as provider takes care of issues to run them more reliable and with easier upgrading, backups

[wise goblet]

===
if you are serious organization that knows infrastructure as a code well
And needing maximum reliability of the provided features / and or having need in an array of 200+ managed servces provided by serious provider
U can be needing AWS/GCP stuff then.
Again Managed Databases like AWS RDS are preferable, since they take care of the issues for you

====
If database u run is not for mission criticial work, yet u need it HA/horizontal scalable and u are profficient with k8s enough
U can be running also kubernetes native dbs

[lean walrus]

INSERT INTO shop_items 
(`name`, `image`)
VALUES (%s, %s)
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `image` = VALUES(`image`);

With MySQL 8.2.0, how do you return the UPDATED rows?

[dull oak]

Hello People,
I am working on a project to migrate data from Cassandra to Alloydb
As part of this I was able to get Cassandra backup on to buckets and now want to load data into alloydb tables.
I did create a staging table and loaded data into that staging table. ( To handle conflicts in main table)

Now when I try to load data from the staging table to the main table using the insert statement , alloydb (GCP ) times out (5min)
table has hundreds of millions of rows .

Would appreciate if you can suggest a better way to go about this?

[polar remnant]

Hi I am trying to use firebird to create a database

[lean walrus]

that would require two queries, I'm trying to combine it to 1

[lean walrus]

please dont use ai

[lean walrus]

well, if AI could solve my problem I wouldn't ask here

[formal current]

!rule 10

[delicate field]

Rules

10. Do not copy and paste answers from ChatGPT or similar AI tools.

[formal current]

Maybe, but don't post LLM answers here.

[tender oak]

hey sorry for the late reply, I am not very active on discord nowadays 😦 but the ondelete='CASCADE' is for deleting entries in the association tables only right afaik because it is taking up the primary keys of the other 2 tables as the foregin key. the functionality I am looking for is suppose you are the last member of a group and you delete your acc before leaving, then that should delete that group as well or it will remain orphaned in the db. I might be wrong but I don't think the above code will suffice for that?🤔

[paper flower]

If you want to delete orphaned group that could be done with sqlalchemy's api, but I don't remember correctly what strategy you should use there

It's probably the best to consult the docs

[tender oak]

ohh okay, is there a particular section I can refer for this? whole documentation is very big 🥲

[paper flower]

http://docs.sqlalchemy.org/en/latest/orm/cascades.html

[tender oak]

thanks for the help!!

[paper flower]

I think in case of delete-orphan since Group would be a parent object of User, so User would be deleted 🤔

[tender oak]

wait have I defned the realtion wrongly? I meant for the user to be a parent of group!!

[paper flower]

You mean that a group would have an owner and members?

[tender oak]

I meant as in a group cannot exist without a user (child cannot exist without parent) so when the parent is deleted, the child should be deleted as well

[paper flower]

But shouldn't a group have multiple users?

Meaning the group is the parent?

There should actually be an error if you set delete-orphan on "many" side of the relationship: https://docs.sqlalchemy.org/en/20/errors.html#error-bbf0

Why don't you just add the group deletion into your business logic so it's cleaned up when last user leaves the group?

[tender oak]

yes, I actually thought about implementing it in the server logic but was thinking that in case of edge cases I should have a fail safe practice... I am a bit new to the sqlaclehmy orm and still learning! and yes groups should have multiple users, so Ig that makes group the parent (but what about user then it is also a parent right, since it can be part of multiple groups) and a group being deleted should not delete the users!! I'll give the delete-orphans a read! (I skipped over it when I was reading up the delete section lol)

[paper flower]

Worst case you'd need to write a query to delete empty groups which isn't hard and fix your business logic later 😉

[heavy swift]

I am faced with a tough situation. Is anybody there?

[floral imp]

halo

i need help

data base

[bronze fulcrum]

Can you guys check #1035199133436354600 for my post? I'm really struggling with it

[dusty blaze]

Have you got an answer?

Also, what was the question

[terse viper]

https://discord.com/channels/267624335836053506/1379101311420858399

[bronze fulcrum]

Guys, can you help me at https://discord.com/channels/267624335836053506/1379810509507006494 ? I'm stuck with it

[charred briar]

why should i use sqlalchemy instead of psycopg?

[fallow smelt]

Dm me if your a professional at databases

Willing to pay good money lol.

[fallow otter]

!rule pay

[delicate field]

Rules

9. Do not offer or ask for paid work of any kind.

[tepid basalt]

So you can write SQL using core or the ORM, so you can keep your statements dynamic and DRY

[fallow smelt]

hello chat

is this a vuln target URL appears to be UNION injectable with 9 columns

[fallow smelt]

@tepid basalt

[fallow otter]

What is the server side implementation?

[fallow smelt]

Check ur dms

[fallow smelt]

19:30:16] [INFO] POST parameter 'password' appears to be 'AND boolean-based blind - WHERE or HAVING clause' injectable is this a vuln?

@fallow otter

mybad

[fallow otter]

I do not read dm

and also show the how the server is implemented and people can see if their are any vulnerabilities and tell you

[fallow smelt]

[19:37:07] [INFO] target URL appears to be UNION injectable with 48 columns

[fallow otter]

for server side implementation I mean the server code

not some random log you got

[fallow smelt]

oh

[fallow smelt]

send in dm

[fallow otter]

I don't read DM request

Like literally never read them

Send it here

[paper flower]

@fallow smelt Why did you dm me?

[fallow smelt]

Is it this deep

Is ur ego this big to not check ur dms

Asking me in the server ''why did you dm me''

its fucking discord mate

What am i supossed to do message ur marj?

[paper flower]

<@&831776746206265384> @fallow smelt

[fallow smelt]

Guy cannot read dms

[fallow otter]

No I don't

Because I don't care what random people send me in DM
Probably not good things

I only check DM for people I know or my friend

[coral wasp]

Be civil, or go somewhere else. Thank you. Many people ignore DM's. If you want help in this server, see #❓｜how-to-get-help

[fallow smelt]

Ignoring dms as if they are some 9f bosses

Chill mate

[coral wasp]

Everyone is chill, except you. It's totally common to ignore DM's, in discord, Reddit, everywhere.

[fallow smelt]

No it aint

Lol

Acting like they got 100 dms

Ur infamous bro

Allow it

[coral wasp]

Ok, thanks. Now can we talk about your question?

[charred briar]

uhhh i cant do these things without orm?

like uh gimme example of something i can easily do in sqlalchemy and cant do in psycopg

[fallow otter]

Can you check modmail rq

[paper flower]

You don't have to use ORM part, sqlalchemy comes with some neat things like connection management and query builder

It's query builder works with both core and orm, you can always write raw sql too

[coral wasp]

There's some things that are better suited to direct sql, and some where an ORM is convenient.

I prefer Sql for analytics and reporting: stuff where I'm doing a lot of aggregation and manipulation

[fallow otter]

Honestly it may also sometimes helpful to just write your own orm for just a single table thing

I have one that is write by myself nicely for the specific purpose
Gonna soon replace with other orm since getting to multi table thing

[charred briar]

well isnt psycopg ez to use with multiple tables

[fallow otter]

I might have a look of it

I mean, it still haven't changed to multi table and just reviewing orm options rn

Currently is raw SQL wrapper -> custom object

I also have a fun project which is raw SQL multi-table
And most handling is on SQL level
However I still haven't managed to scrub the commit on GitHub activity (since I accidentally uploaded the sqlite db, oops) so not public yet

https://paste.pythondiscord.com/5IXWLV3EV2VAE2RSDZSLZZFLEI
so here my favourite(longest) sql from the code

[paper flower]

IMO if you're making a game it would probably be better to store things like recipes statically? 🤔

and not in db

unless they're meant to be dynamic

[fallow otter]

they should be statically normally

but I think it would be funny if I can put everything in the database except the path to the db and the bot token(credential)

[tepid basalt]

A very basic example is adding a variable number of conditions to the where clause.

[paper flower]

I mean it's up to you, but dealing with recipes in DB is probably just harder during development than with something like yaml, since it's human-readable

[fallow otter]

ye, just purely for fun sake and probably shouldn't be use for such purpose
the idea purely steam from the database and sql query itself is the game and the code is just executing it

[dawn shard]

Wouldn't you want to make some proper dev tool for managing them either way?

[fallow otter]

the only actual logic of the bot rn is calculate the user energy since I cannot find a way to get the current timestamp as integer/real in sql

[paper flower]

With something like yaml it would probably be fine to keep them as is, at least you'd be able to do that longer than with a db

e.g.

- outputs:
    - type: item
      id: pickaxe_stone
    - type: xp
      skill: crafting
      amount: 25
  inputs:
    - type: item
      id: "stick"
      amount: 2
    - type: item
      id: "cobblestone"
      amount: 3
  requirements:
    - type: skill_level
      skill: crafting
      min_level: 3

[fallow otter]

async def recharge(user_id: int) -> int:
    await make_user_exist(user_id)
    charge_rate = (await get_min_cooldown(user_id)).cd_recharge

    async def handler(conn: Connection):
        await conn.execute("BEGIN TRANSACTION;")
        cursor = await conn.execute(
            """SELECT energy, last_recharge FROM user WHERE id = ?;""", (user_id,)
        )
        energy, last_recharge = await cursor.fetchone()
        time_diff = datetime.datetime.now().timestamp() - last_recharge.timestamp()
        charge_amount = time_diff // charge_rate
        new_last_recharge = datetime.datetime.fromtimestamp(
            last_recharge.timestamp() + charge_amount * charge_rate
        )
        result = energy + charge_amount
        if result > 1000:
            result = max(1000, energy)
            new_last_recharge = datetime.datetime.now()
        await conn.execute(
            """UPDATE user SET energy = ?, last_recharge = ? WHERE id = ?;""",
            (result, new_last_recharge, user_id),
        )
        await conn.execute("COMMIT;")
        return result

    return await game_db.execute(handler, reuse_conn=False)
# give me few second as this device don't have backtick so I will use different device to add it

[paper flower]

You can validate this using something like pydantic, the only downside is the lack of foreign keys (you can mitigate that using tests) there and ease of querying

[fallow otter]

This is only place that have logic
Probably find some way to remove it in the future

[paper flower]

You should try to separate logic from your db where possible, also you can probably use a context manager instead of writing begin/commit manually? 🤔

[fallow otter]

Well, because transaction and commit is non-default
It is using its own context manager from .execute

As reuse_conn=False

Any change not committed is rollback

[fallow otter]

Well
The thing is
The whole project is put all the logic in the db🥴

If possible

[dawn shard]

Are you on SQLite or postgres or ...?

[fallow otter]

SQLite

[dawn shard]

Postgres has stored procedures

[paper flower]

If the library you're using doesn't offer an API that uses context managers you can probably make one yourself:

@contextlib.asynccontextmanager
async def transaction(conn: Connection) -> AsyncIterator[Connection]:
    await conn.execute("BEGIN TRANSACTION;")
    try:
        yield conn
    except:
        await conn.execute("ROLLBACK;")
        raise
    else:
        await conn.execute("COMMIT;")

[fallow otter]

This is how I accidentally commit my database and it's not public since I cannot scrub from GitHub activity

[dawn shard]

It's an occasionally decent architecture -- throw all business logic into stored procedures, application code just calls them and funnels data into more appropriate shapes.

[fallow otter]

@dataclass
class Database:
    path: pathlib.Path | str
    database: Optional[aiosqlite.Connection] = None
    operation_tracker: Optional[Operations] = field(default=OP)
    _lock: asyncio.Lock = field(default_factory=asyncio.Lock, init=False)

    @property
    def locked(self):
        if not self.database:
            return True
        return self.database._running

    async def execute[_T](self, func: Callable[[aiosqlite.Connection], Coroutine[Any, Any, _T]], *, reuse_conn: bool = True) -> _T:
        operation = None
        if self.operation_tracker:
            operation = self.operation_tracker.new_operation()
        new_conn = not reuse_conn
        if self.locked or (self._lock.locked() and not reuse_conn):
            new_conn = True
        if not new_conn:
            async with self._lock:
                try:
                    out = await func(self.database)
                except:
                    new_conn = True
                finally:
                    await self.database.rollback()
        if new_conn:
            async with aiosqlite.connect(self.path, detect_types=sqlite3.PARSE_DECLTYPES) as db:
                out = await func(db)
        if operation is not None:
            operation.complete()
        return out

Btw

[paper flower]

That's why it's easier to use a library that does connection pooling for you, also I'm not sure how well using multiple connections would work with sqlite

[fallow otter]

It should work fine
I do forgot how specifically it implemented

[fallow otter]

Also the reason I do this is I don't trust me or my friends dev to have the database connection done correctly so ye...

Having one code that guarantees using context manager, and both handles everything I needed is nicer for me

[fallow otter]

I do realise I forgot the rollback part of a new connection is used tho

[magic quail]

Go.

[fallow otter]

No. If you need help, #1035199133436354600
Please only keep topic related content here

[bronze fulcrum]

Guys, I have problem with extending sqlalchemy relationship. I posted the code and full traceback here -> https://discord.com/channels/267624335836053506/1380148442239598692

[bronze fulcrum]

from datetime import datetime
from typing import List, Optional
from sqlalchemy import BigInteger, Column, String, ForeignKey, Table, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine

engine = create_async_engine('sqlite+aiosqlite:///db.sqlite3', echo=True)

async_session = async_sessionmaker(engine)

class Base(AsyncAttrs, DeclarativeBase):
    __abstract__ = True

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())


association_table = Table(
    "association_table",
    Base.metadata,
    Column("left_id", ForeignKey("left_table.id"), primary_key=True),
    Column("right_id", ForeignKey("right_table.id"), primary_key=True),
)


class Parent(Base):
    __tablename__ = "left_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List[Child]] = relationship(
        secondary=association_table, back_populates="parents"
    )


class Child(Base):
    __tablename__ = "right_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    parents: Mapped[List[Parent]] = relationship(
        secondary=association_table, back_populates="children"
    )
async with engine.begin() as conn:
    await conn.run_sync(Base.metadata.create_all)
session = async_session()
parent = Parent()
await session.add(parent)
child1 = Child()
child2 = Child()
await session.add(child1)
await session.add(child2)
parent.children.append(child1)
parent.children.append(child2)
await session.commit()

Do you know why this generates no query? Like it doesn't append the children

[waxen burrow]

I haven't used Sql alchemy, but in Django's ORM it's necessary to call .commit() (or something) on an instance to write it to its table in the DB, not on the session.

[paper flower]

But it does?

from datetime import datetime
from typing import List, Optional
from sqlalchemy import BigInteger, Column, String, ForeignKey, Table, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
import asyncio

engine = create_async_engine('sqlite+aiosqlite:///db.sqlite3', echo=True)

async_session = async_sessionmaker(engine)

class Base(AsyncAttrs, DeclarativeBase):
    __abstract__ = True

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())


association_table = Table(
    "association_table",
    Base.metadata,
    Column("left_id", ForeignKey("left_table.id"), primary_key=True),
    Column("right_id", ForeignKey("right_table.id"), primary_key=True),
)


class Parent(Base):
    __tablename__ = "left_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    children: Mapped[List["Child"]] = relationship(
        secondary=association_table, back_populates="parents"
    )


class Child(Base):
    __tablename__ = "right_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    parents: Mapped[List[Parent]] = relationship(
        secondary=association_table, back_populates="children"
    )

async def main() -> None:
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    session = async_session()
    parent = Parent()
    session.add(parent)
    child1 = Child()
    child2 = Child()
    session.add(child1)
    session.add(child2)
    parent.children.append(child1)
    parent.children.append(child2)
    await session.commit()


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

Also you should be able to only add parent istance into the session, child objects should be automatiaclly tracked

e.g.

async def main() -> None:
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    async with async_session.begin() as session:
        parent = Parent()
        session.add(parent)
        parent.children.append(Child())
        parent.children.append(Child())

[native violet]

Please, any expert in vps (virtual machine), I need your help, write to private .

[fallow otter]

Not the channel to ask for #ot0-fear-of-python
And also many people don't prefer to help in pm

[astral dragon]

i hope i can just ask this here. why do i get b'\x00\x00\x02*' when i read in 0000 022a

why is the 2a gone and what does x02* mean

[woeful ruin]

python formats a byte as an ascii character if it's a printable character, otherwise it's the hex representation "\xYZ"

and "reading" 0000 022a -> [00, 00, 02, 2a]

err 0x2a that is

[astral dragon]

when i convert it to a in im getting the wrong number though

def read_uint32(file):
    bytes = file.read(4)
    print(bytes)
    return int(bytes[0]+bytes[1]+bytes[2]+bytes[3])```

[woeful ruin]

ok, that's the sum of the bytes, probably not what you want

[astral dragon]

lol

ok

[woeful ruin]

That's saying "I read 123 and it returned 6"

[astral dragon]

thanks

[woeful ruin]

You want the function int.from_bytes

[astral dragon]

it was working for other numbers by luck

[woeful ruin]

>>> int.from_bytes(b'\x00\x00\x02*', 'big')
554
>>> int.from_bytes(b'\x00\x00\x02*', 'little')
704774144

But you need to know if the first or last digit is the most significant

[astral dragon]

big

[woeful ruin]

>>> a = 554
>>> a.to_bytes(4, 'big')
b'\x00\x00\x02*'
>>> a.to_bytes(8, 'big')
b'\x00\x00\x00\x00\x00\x00\x02*'

And there's a method to reverse, but you need to provide how many digits it requires

and, for completeness, the original would have worked if you multiplied the bytes by 256^index

def read_uint32(file):
    bytes = file.read(4)
    print(bytes)
    # equivalent
    return int(bytes[0]+ 2**8 * bytes[1] + 2**16 * bytes[2]+ 2**24 * bytes[3])
    return int(bytes[0] + 256 * (bytes[1] + 256 * (bytes[2] + 256 * bytes[3]))))
    return sum(256**i * byte for i,byte in enumerate(bytes))

[astral dragon]

ok thanks i got it reading sensible values now

time to close the 20 tabs i had open

[cedar tiger]

Offsite, I have a small portable app that have a sqlite database. The portable app is supposed to stream its local data to the server. Now the machine that the portable has installed has some hardware issue, but luckily the data is backed up with the sqlite db. What is a proper way of handling this backed up data to the remote server? I don't think a data dump into the remote server db is appropriate.

My guess, is to set up a separate portable app on another machine, restore the backed up sqlite db and have it stream to the server and then delete after double checking all of the data is there.

I can imagine this is gonna happen again in the future. Can anyone advise?

[keen minnow]

why not uploading to a blobstore like s3? How big is it?

[cedar tiger]

They're not very big but there are multiple clients installed, and in each has their own local database (because "offline-first" support). Roughly each is about 1MB

And there are about 90 clients to support

[keen minnow]

doesn't sound too bad to upload to s3 then

[cedar tiger]

Do you think that should be run on a Windows Task Scheduler or something?

So far I've seen litestream and rqlite

[keen minnow]

it will depend on the client app, who controls it, who manages the backup and restore, etc.
In general, I would avoid handing out full S3 access to multiple distribution points, so presigned URLs might be of use here

[rapid flare]

I wanna to learn phytht in 15 days

[strange wyvern]

Ive been using PostgreSQL for mt website and its so sick

it was kinda hard to learn tho

eh

Super useful tho

it allows me to be able to make connections faster and simpler

would highly recommend

i tried mysql but idk

not for me ig idk

[gloomy olive]

hello, is there anyone who could help me in choosing topic for master thesis ? My specialization is related to software engineering with databases

[charred briar]

when i should use async driver?

and whats diff between asyncpg and psycopg3

[dawn shard]

asyncpg has a non-standard API, whereas psycopg3 is DB API 2.0. (I find asyncpg has a better API overall).

[coral wasp]

tbh, pro tip is to check your advisors recent papers and try to find something that's somewhat related.

[lavish oriole]

Would you happen to know how you resolved that? Google is being particularly unhelpful with this and I am having the exact same issue as you.

[pastel stirrup]

You just need to describe column like activated_on: Mapped[datetime.datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

[lavish oriole]

-# thank you for responding, I will attempt to act upon this when I wake up in the morning
-# I should've been asleep hours ago lol
-# please have a good day

[spring furnace]

Hey all. Wondering if someone has used pymongo's async version and has potentially made a wrapper for interactions with the database. Would like some feedback

[dawn oak]

anyone interesting in python for ms excel?

[floral flame]

guys im new to databases

what should i look up or where can i learn about stuff

[dusty blaze]

first learn what it is

[coral wasp]

See #databases message

[versed shore]

regarding sqlite3 transactions and database locking.

I am trying to figure out if executing a SELECT statement acquires a lock on the database and if so, which kind of lock. I am always executing my statements in autocommit = False mode (PEP-249 compliant) and within the context manager of a sqlite3.Connection.

I am specifically referring to this paragraph of the plain SQLite3 docs, which would suggest a SHARED read lock is acquired, which prevents other processes from writing to the database after my application executes a SELECT statement (which is good!) (https://www.sqlite.org/lockingv3.html)

The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. In this way, the system delays blocking read access to the file until the last possible moment.

On the other hand, I don't know if the python context manager is messing with the above. I know that entering the context manager does not start a transaction, but that autocommit = False should mean that the connection is always inside a transaction (a new transaction is always opened after a commit/rollback).

I would like to see the lock state of the database at each point of my application, to make sure that my operations are atomic (I'm using async so I'm worried that my critical paths are crossing). Please ping me when you reply.

[timid brook]

Hmmm curious about thoughts on a conundrum I have. the short version is I am using a pandas dataframe as a template for importing/modifying/saving data to a database. However the column headers match the database to make the import more straightforward. However they are not very nice user friendly strings. I see that I can easily rename headers using a dictionary but it seems like then I would need to swap them back on import which feels kind of hacky to me.

It seems like I have a couple options, first being what I described and just flip the columns back and forth, or perhaps I could just leave them as user readable assuming that it doesn't cause issues with the import to the DB. which it appears like it wont be an issue. But I'm curious about other's thoughts on it.

There are no special characters or anything in the headers, its mostly a difference like shorthand vs long hand like "transact_date" vs "Transaction Date"
edit I decided to just redo the database with readable column names so it is unified across the board. This seems like it will work just fine

[timid brook]

hmm doing this broke ON CONFLICT clause for some reason
Ok fixed that too. that felt very hacky but basically an issue with single vs double quotes lol...
apparently they are interchangable when creating the table or even writing data but the ON CONFLICT() unique keys must be wrapped in double quotes if they have spaces

[blazing shuttle]

Helloo, I have a database assignment in which I plan to make an app with a GUI that uses the database from a previous assignment.
The database is supposed to be a relational model of a student management app. I plan to make the app using tkinter and I'm wondering if it would be good practice to represent the tables from the database as classes in the program.

[cedar tiger]

yeah that's the norm. We define tables and its methods as models. Will you be using SQLAlchemy ORM or something?

[blazing shuttle]

Sorry I'm a beginner in handling databases, the RDBMS I use is MySQL, what do you recommend?

Like I've never used a database before in a program

[cedar tiger]

I assume there's no limit in your assignment in what you can't do, so I'd try to interface with the database via SQLAlchemy and follow its guide. Creating a table as a class, inheriting the model, should help define the database table fields, and gives you the flexibility to write queries the ORM way.

Read the official docs for a quick tutorial on how to use it https://sqlachemy.org

[blazing shuttle]

Hmm sure I'll check that out, thank you for the info

[torn sphinx]

Can anyone give me a path to learn DB, like how should I start

[lean gate]

hey

[torn sphinx]

What is your current level in programming? What are you able to do?

[torn sphinx]

I have learnt basics of python at home, basics of c in college & completed html css and js

also have tried next api until it starts using sql alchemy

[torn sphinx]

That's good. So, what are you trying to learn: how databases work on a deep level, how they work in general, or just the syntax for implementation?

[torn sphinx]

I want to learn at that level like if I want to develop something like a website or app I don't need to learn new things

as I hate tutorial hell, in starting its good but then I have to watch tutorials for everything

[torn sphinx]

You wouldn't mind reading, would you? In my opinion, nothing beats a good book and some practical application of what you learn, but some people don't have the focus for it.

[torn sphinx]

I will read and understand, but from where should I start?

[torn sphinx]

I got you, I'll find a good book for you.

thanks!!

[torn sphinx]

Designing Data-Intensive Applications by Martin Kleppmann is a great starting point—it builds foundational knowledge so you can move beyond tutorials and understand core concepts across any database. After that, you can dive into a specific one. Most people choose PostgreSQL, so I recommend PostgreSQL: Up and Running by Regina O. Obe and Leo S. Hsu for that, or High Performance MySQL by Baron Schwartz et al. if you go with MySQL.

I have a PDF of Designing Data-Intensive Applications if you want it.

[pseudo ore]

If you already know basics of Python and webdev, then you can learn Pandas (Python module) easily for database management. Alternatively, you could also learn SQL (my recommendation) which is a micro language dedicated to specifically managing databases with integration available in Python and web development.

[torn sphinx]

I will buy a hard cover, I can't read from PDFs lol it's so annoying

[torn sphinx]

Alr!! My college will start teaching sql from oct ig

[pseudo ore]

Alright, good luck!

[torn sphinx]

Thanks mate!

[next hearth]

guys i m new to databases(learnt the basics of py) and i havent learned some modules so idk pandas idk feels like SQL>PANDA so should i start sql what are yall thoughts about this and any advice

[pseudo ore]

I would personally agree with SQL being much more powerful than Pandas. However, both are very easy to learn. Honestly, it's just personal preference. I like SQL better because it's a standalone micro language specifically dedicated for data analysis, but Pandas is much more friendly with Python integration which can be nice if you want to build scalable projects without going through a few hurdles.

[opal mountain]

i mean i caught on to SQL pretty quick

what i like about sql is that you can control it from the python code, idk about panda tho

its my first time hearing it

[timid brook]

heh amazon just started pushing that book on me this week.
And regards to pandas and SQL I do agree that SQL has some advantages but I personally have started embracing both together in my app and I love the interaction and power of both together. utilizing pandas dataframes gives you basically a great set of tools for handling, displaying and modifying data and easily able to go to and from a SQL database for the storage.

[timid brook]

pandas is a python library, quite powerful for things that you normally use xcel for but even more-so and easily handles data pulled from a SQL-like database, or other sources.

[opal mountain]

ooh

[sonic mesa]

Umm hello?

Question

Has anyone here tried connecting a gitpod environment to run locally on vscode?

[opal mountain]

not me

[timid brook]

Well google says it supports direct integration with vscode but never done it or used gitpod myself

[sonic mesa]

Yeah. I read the documentation and the process is straight forward but for some reason the environment I want doesn't launch in vscode.

I wonder if it has something to do with linux cuz I'm using ubuntu.

[opal mountain]

yall is there anything in sql to like "select * but varible?"

[cedar tiger]

you mean instead of the *, its a variable that the user can select which column to get? Why not fetch the whole columns and then return which column that the user wants?

That is, if you're doing it with some SQL ORM in Python.

But if you're talking about strictly in SQL, there's DECLARE

[timid brook]

Yea it depends on what you mean by 'variable'

[north bolt]

i have a question

[wooden elbow]

framework

[umbral pasture]

what framework?

[white galleon]

hey guys , so i was just playing around with fastapi and this is just a connection to my local postgresql db , so initially , i enter the wrong password(postgress) and it prints the error , now when the server is running itself , i change the password to the original password(postgres) and it does show detected changes but it still get the error message , even after entering the right password . Any suggestions?

and if you see the error properly , you can still see the same password being used evn after updating it

[celest isle]

should the password be "postgres" with a 's'? , you can print password to see whether it's changed after reload

[white galleon]

Yes the correct password is postgres, so that warning which you can see is after i changed the wrong password , which is postgress , to the right password , postgres

but the files were reloaded , the api uses the wrong password itself

[celest isle]

can u put ur password in a variable and print it ?

log only say postgres user's password is not right, but exact value is not given

[white galleon]

I think i got the error

[white galleon]

so the while statement is the error

coz when updated the reload helps to realod the files but the while statement is still stuck in the previous execution

[celest isle]

that's right

[regal imp]

🚀 Exploring AI + SQLAlchemy — Community Feedback Welcome!

Hi Python community! 👋 We’re building tools at GenAI-Logic.com that combine Generative AI with popular frameworks like SQLAlchemy, to make tasks like model generation, schema discovery, and query building faster and smarter.

If you use SQLAlchemy or care about AI-powered dev tools, we’d love your feedback, suggestions, or collaboration ideas.

Check out what we’re working on: https://www.genai-logic.com/product/architecture

Let’s make Python development smarter, together. 🔧🐍

#sqlalchemy #python #generativeai #devtools #opensource

[floral forge]

wwhat the fuck

[storm mauve]

guess you messed up your configurations and got hacked by some bot that automatically scans stuff?
if you can, delete everything and load back from a backup

then make sure you are not exposing your credentials in any public way, are not using default/common values, your packages are updated to avoid known vulnerabilities and so on

[dawn shard]

damn, 450 usd is a bargain. A family friend had to pay 10k.

[queen laurel]

yo, I am at a stage of app-making where I am converting csv data into postgres tables

and I extract the csv rows into dataclass objects that correspond to DB tables

but how do I convert the object into the table, vis-a-vis foreign keys?

e.g. I read a Customer, then I read 5 Orders belonging to it

and I make Customer(id = 0, 'Mike'), Order(1, 'Burger'), Order(2, 'Fries')...

and then I could do CustomerOrder(0,1), CustomerOrder(0,2)...

but in DB I already have those keys, if I try to link them like that, I will get an error and I'll have to skip duplicate keys

basically how to translate app-assigned id relation to DB, so that it adds unknown, but new ids to correct relations?

+I don't want to use an ORM, I am practicing raw SQL and don't mind writing it

is the correct way to insert Orders, insert Customers, then read Customers and their ids and manually update the orders to reference the correct customer?

[silent bison]

make two tables
customer with

id SERIAL INT , name, number, etc

order with
id SERIAL INT , customer_id reference customer, other order details

[queen laurel]

I did that already

[silent bison]

whats the problem now

id is serial so no dups

[queen laurel]

it's the joining of 2 items before I actually create them

[silent bison]

you have to do it one by one make user then make order

[queen laurel]

I know

but lets say I make them

how do I join them

how do I make customer_id FK of Order point to correct Customer?

[silent bison]

you will save order like this

User = get_user(id=1)
Order(customer_id=User.id)

[queen laurel]

yes, but I don't know which id the user got assigned, since it's auto-incrementing

I got this from chatgpt, seems correct?

[queen laurel]

ok, so apparently I am to use RETURNING

but idk how to batch it

like I can go in a for loop, row-by-row, insert a Customer, get its id via RETURNING, insert Order using that id - all good

but it's row-by-row, but the only association of a Customer and Order is the row that they belong to

I don't see a way to batch insert all Customers, then somehow correctly batch insert all orders

[pastel charm]

Hello

[silent bison]

you can do in batch assume

[
(userdata1), (userdata2), .. ........
]
it will return list of ids for userN then you can use it to add in Order ID

[silent bison]

or write custom sql to process everything at once

[silent bison]

hello

[pastel charm]

@silent bison
Look I am new here

[silent bison]

No problem

[pastel charm]

Are you the admin?

[silent bison]

no

just average user

[pastel charm]

Cool

Let me tell you

I am completely a new user of discord

Someone told me about this

That I'll get the good friends the supportive one

[silent bison]

it depends on server

[pastel charm]

For my coding world

[pastel charm]

How you feel like about this server

[silent bison]

it is good server for new people in programing

[pastel charm]

Oh!

Will you be my friend?

To help me , how to use this application

[silent bison]

sure

[silent bison]

you can use youtube for that there are many tutorials

[pastel charm]

They are just gonna confuse me

I think you are a good person

Well, where are you from

[silent bison]

it is simple

[silent bison]

no personal questions plz

[pastel charm]

Ok man!

Will the other group members gonna fed up with our chats here!

Or they do not bother about this

[silent bison]

i dont think so discord has server which has channels which can have threads

these are all for topics

[pastel charm]

Oh

[rose gust]

Can someone please guide me on how to get started with Databases using Python

[umbral crag]

I can help you

[rose gust]

Let's talk on personal

[rigid mica]

I'm connecting to a mariadb database on Cloudways, however after a few minutes when executing a query, I get _mysql_connector.MySQLInterfaceError: Lost connection to MySQL server during query

db_config = {
    'host': 'myip',
    'user': 'myuser',
    'password': 'mypassword',
    'database': 'mydatabasename',
    'autocommit': True
}


conn = mysql.connector.connect(**db_config)
cursor = conn.cursor()```

[celest isle]

Maybe the connection is expired, you should set a larger connection timeout or use a connection pool

https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

[noble mountain]

👋

[onyx lintel]

You guys know anything about clickhouse

[paper flower]

You should acquire a new connection on each new atomic operation you want to perform

In case of a web application for example you'd acquire a new connection for each request

[rigid mica]

it's inside of a Discord bot, so a new connection in every function where I use it?

[paper flower]

Yes, pretty much, I'd say on each event/command

[olive fossil]

🚩

[mossy grotto]

Guys, any good Python SQL youtube course for beginners that you know?

[icy glacier]

A month or two ago I was struggling with getting the concept of joining stuff to use as part of SQLAlchemy and I used this site to practice until I got the concept of more stuff, if you spend a few hours on it along with other material I'm sure when you get to dealing with it through python you'll have no problem: https://www.sql-practice.com/

[mossy grotto]

It's for my uncle who is beginner to coding in general. So I was asking for any video course to quickly get some idea of how things work in SQL. His main work isn't coding. He needs to kind of supervise others who are working on SQL and python, so he needs basic ideas.

[hazy smelt]

What is the most reliable way to ensure consistent data without using transaction?
Eg. User deducts 100 -> balance 0
10second internal processing
Balance doubled (0->200)

If we use a transaction here then won't it hold the pool's connection for 10 second??? Any other method apart from storing a copy of the amount deducted and recovering on failure

[vagrant fossil]

how do you make a add function in python using sqlite?

async def add(path: str, table: str, column: str, value: str):
    async with asqlite.connect(path) as conn:
        async with conn.cursor() as cursor:
            await cursor.execute(f"""CREATE TABLE IF NOT EXISTS {table} 
                                 (username TEXT NOT NULL ON CONFLICT ABORT, amount INTEGER NOT NULL DEFAULT 0)""")
            await cursor.execute(f"INSERT OR ABORT INTO {table} ({column}) VALUES({value})")
            await conn.commit()

i did this and it said that it has the wrong input.

[coral wasp]

What exact error did you get?

!trace

[delicate field]

Traceback

Please provide the full traceback for your exception in order to help us identify your issue.
While the last line of the error message tells us what kind of error you got,
the full traceback will tell us which line, and other critical information to solve your problem.
Please avoid screenshots so we can copy and paste parts of the message.

A full traceback could look like:

Traceback (most recent call last):
  File "my_file.py", line 5, in <module>
    add_three("6")
  File "my_file.py", line 2, in add_three
    a = num + 3
        ~~~~^~~
TypeError: can only concatenate str (not "int") to str

If the traceback is long, use our pastebin.

[vagrant fossil]

wait

huh this is weird

see?

[coral wasp]

I'd assume you're looking at two different database files then.

(or there's a space or something else "odd" in the table name)

[jade wing]

you really really don't want to use f-strings for SQL statements unless you want to open up your application to SQL injection vulnerabilities and attacks
or unless you control 100% what is in those variables that you have in the f-strings
instead you want to use bind variables (aka placeholders), which doesn't work with table names or column names
if you really have to have those dynamic you should sanitize them against a dictionary first for an exact match in the dictionary
and probably also use a good and secure SQL query builder library or an ORM and not do raw SQL statements yourself

[vagrant fossil]

I handle the functions

[jolly umbra]

how to fix

[warm ledge]

#1035199133436354600

[cosmic notch]

install the module trough pip (pip install cgi)

[supple shell]

go to command prompt and use this pip install cgi(this will install cgi allowing to import)

[supple shell]

this happens if you have 2 data bases with same name making prblems and errors as such

[supple shell]

the best course ican reccomend is python for everybody by charles severance

[supple shell]

ican help

[supple shell]

iam looking for database people if they wanna collab with me

[sage nacelle]

2

[arctic birch]

I've made a flask application, deployed 4 gunicorn workers with the default MongoClient configuration with Pymongo.

mongo_util = MongoClient("https://locahost:2717/test_db")
using this the single object in all of my application. Me and my friends are working on this,
But I'm getting connections limit exceeded than 1000 and we're using M0 clusters.

What should we do here?

[cedar tiger]

idk mongodb but have you looked at this? https://www.mongodb.com/docs/atlas/reference/alert-resolutions/connection-alerts/#:~:text=Connection alerts typically occur when the maximum number,of open connections drops down below the limit.?msockid=269001ec1c1a6761002014611d5f6601

[arctic birch]

I have. But I confused whether the connection pool in the default configuration returns the connection back to the pool after use.

[cunning bone]

anyone else use the nfl_data_py database for NFL work in python? I'm noticing players whose only stat is a target in a particular game are not in game logs. Is that accurate or am I just pulling it wrong?

[dry jewel]

What’s an example query? I can take a look.

[tame falcon]

anyone tell me the best db in general and for django both

[cunning bone]

import nfl_data_py as nfl
import pandas as pd

pd.set_option('display.max_columns', None)
weekly_data = nfl.import_weekly_data([2024])
Ml = weekly_data[weekly_data['player_id'] == '00-0024243']

thats for Marcedes Lewis- two games should show up, he had a target in one game with no catches and that doesn't appear in the DB

appreciate you taking a look

[coral wasp]

Looking at the data, it just looks like he's in that weekly_data once. It's not anything you're doing wrong, it's either nfl_data_py or their source.

[dry jewel]

I just took a look at it. So nfl_data_py is part of the nflverse organization, which scrapes data off ESPN and saves it to GH as csvs. Taking a look at Marcedes Lewis' game logs that year, he only had one target according to ESPN. If you just need his data, I would recommend scraping off pro football reference: https://www.pro-football-reference.com/players/L/LewiMa00/gamelog/2024/. This has the extra target you're looking for. (Just be careful with scraping as the site has rate limits) https://www.sports-reference.com/bot-traffic.html. Why is it that way? Probably due to how either categorize it. I assume ESPN has more tracking data to work with so they would be better able to gauge if the intended throw was to him or not.

[cunning bone]

i appreciate you both looking at this. As I see more, it looks like NFL_data_py weekly tables only record the week if they had a catch. So even if they played 20 snaps (making them eligible for betting outcomes) and had no targets and no catches, thats not in the dataset

annoying!

i appreciate both of your help- thanks again

[tribal wave]

I am a beginner in cloud and I just wanted to know which platform is best deploy backend for free AWS, Google GCP and AZURE please help me here to chose the best one.

[dusty blaze]

pythonanywhere

[bold saffron]

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side

"""1.0 Cria Um Novo Arquivo Exel"""
wb = Workbook()
ws = wb.active #seleciona uma planielha ativa 
ws.title = "Tabela Automatizada" #define o nome da planilha

"""2.0 Adicionar dados"""
dados = [
    ["conta", "preço", "tempo limite"],
    ["Agua", 346, 15],
    ["luz", 295, 23],
    ["Streaming", 300, 30],
    ["escola", 840, 90]
]
for row_index, row_data in enumerate(dados, start=1):
    for col_index, cell_value in enumerate(row_data, start=1):
        ws.cell(row=row_index, column=col_index, value=cell_value)

"""03 Formatar Tabela"""

# Definir fonte para o cabeçalho
header_font = Font(bold=True)
for col in range(1, len(dados[0]) + 1):
    ws.cell(row=1, column=col).font = header_font

#Definir bordas
thin_borders = Border(
    left=Side(style='thin'), 
    right=Side(style='thin'), 
    bottom=Side(style='thin'), 
    top=Side(style='thin')
)
for row in ws.iter_rows():
    for cell in row:
        cell.border = thin_borders

"""4.0 Salvar projeto"""
wb.save('tabelaautomatizada.xlsx')

print("tabela criada")```

what am i doing with myself

[paper flower]

You can pretty much use sqlalchemy, but it won't validate input types like pydantic would. It is type safe though

[pastel vale]

Is there a good tutorial to learn how to use sqlchemy properly? I heard if you dont know how to use sqlalchemy properly, you start getting into bad habits that can negatively affect your program

[tepid basalt]

The tutorial in the docs is great: https://docs.sqlalchemy.org/en/20/tutorial/index.html

[spiral wyvern]

hi

[paper flower]

Yeah, pretty much read through tutorial, but I have some opinionated tips:

• Don't use lazy=... on relationship, you can always add loading strategy to concrete queries, exception would be models that are almost always retrieved together, like User and UserProfile
• Use new Mapped/mapped_column syntax/API, instead of the old Column when declaring models
• Use MappedAsDataclass where possible
• You can wrap custom types into TypeDecorator to serialize and deserialize your custom types, like pathlib.Path or pydantic models
• Only open transaction and commit once, in most cases that's what you want
• You can create reusable field definitions for types that you use often https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html#step-five-make-use-of-pep-593-annotated-to-package-common-directives-into-types

2.0 Changelog pretty much describes some of the modern "best practices" in sqla when it comes to the ORM https://docs.sqlalchemy.org/en/20/changelog/whatsnew_20.html

[real timber]

Why do you use sqla? Is raw SQL not available/an option for some reason or do you prefer sqla?

[quartz dune]

hello, anyone had used MinIO before?

why can't i access minio bucket (like upload file) that's on a server with python code? whereas using dashboard console or cli it works like a charm. It always said AccessDenied

[paper flower]

It's more convenient and faster to use an orm for most of the queries, especially if they're built dynamically

[tepid basalt]

json is a format, not a database. So I'd say no

If you're looking for a file based database, try SQLite

[hoary badger]

hey i want some senior to guide or advice me through a problem

recently i build a simple blog project that uses sqlite for database, i used chatgpt in various places( just for debugging and nothing else )
when i deployed the project on render, i saw that when ever some error occured in the website render redeploy the project and because of that it got deleted every time, than chatgpt told me at the end that i have to do migration, and many more things i tried all but nothing worked

[floral dawn]

There is a beanie model which has field:

Client_id: Optional[Link[Client]] = None

And an id of client comes in params, how to set dbref to that Client_id field?.

I tried part.Client_id = Link(ref=DBRef(collection="name", id=params_id), document_class="name")
part.save()

[tepid basalt]

Are you using an in memory database?

[hoary badger]

you mean database in a server, then yes

[tepid basalt]

No, I mean are you storing the SQLite database as a file or in memory? SQLite can work as an in-memory database, but once the connection is terminated, the database is lost. This might explain why your data is getting deleted every time.

https://sqlite.org/inmemorydb.html

[pastel vale]

You can create an sqlite database similar to how you create new files with python. I prefer creating the actual db rather than storing it in memory so I can actually see if the changes I’ve made to the db, have been saved

[hoary badger]

no worries should i send my website link so you can check it out?

[tepid basalt]

I don't think I'll be able to tell much from looking at the website. Were you able to confirm whether or not you're using a file for the database?

[hoary badger]

yes i was, and it was on the server, due to any error or redeploy of project, the database got reset everytime

but now i am using a free sqlite database hosting

[tepid basalt]

Did that fix the problem?

[hoary badger]

yes

[obtuse cloud]

Hey guys Im creating a website that needs to have users therefore needs a data base (I am using MAMP) and just had a few questions about hashing and tokens. For hashing using some more simple hashing algorithms surely you can enter two different things and in a very rare case they will hash to the same thing, and is this something I need to worry about and if so should I double hash and secondally is creating a session token for a user a difficult thing to impliment to a MAMP database?

[paper flower]

What are you hashing?

[dusty blaze]

wdym they can hash to the same thing

[heavy wind]

hi guys

[dusty blaze]

if it is possible then it is not hash

hiu

[heavy wind]

i have a problem

can someone help?

[dusty blaze]

are you using your own hashing algorithm?

[dusty blaze]

yeah what is it?

[heavy wind]

i want to uninstall python and then download it again

but

[dusty blaze]

y

[heavy wind]

when i try using python uninstaller

it gives me this error

[dusty blaze]

why

[paper flower]

You should try running uninstaller with administrator privileges, also this is a wrong channel to ask this

[dusty blaze]

have you tried running the uninstaller as admin?
also this channel is for DB this is not DB

[dusty blaze]

lmao

[heavy wind]

i know i knwo

i didn't know where to go exactly

[dusty blaze]

#1035199133436354600 or #python-discussion

[paper flower]

@obtuse cloud If you're hashing user password you should hash them using algorithms that were specifically designed to handle passwords, such as argon2 or bcrypt

[dusty blaze]

yes

for example i use and recommand bcrypt

it is more secure cuz it uses seed

[paper flower]

That's not the reason it's more secure

[dusty blaze]

it is also a reason

[dusty blaze]

i meant more secure than other libraries, ofc not his algorithm

not all libraries uses seed for some reason

oh wait i meant salt

[paper flower]

They use "salt", which is basically a random string used together with the secret you want to hash.
The main difference between bcrypt and sha256 is that you could tweak how long it takes to hash (and in some alorightms like argon2 - how much memory it takes too). Compared to something like SHA256 it could be magnitudes slower, preventing brute-force attacks, while adding a salt also prevents rainbow tables from being used

[dusty blaze]

ik what is salt

[paper flower]

RTX4090 could compute something like 20,000 MH/s of sha256, compared to ~200 kH/s in case of bcrypt

which is like 10k times slower if I can do basic math

[dusty blaze]

hashlib doesnt use salt

[paper flower]

I didn't say that sha256 uses salt?

[grim vault]

You can't ;) 20,000,000 / 200 = 100,000

[paper flower]

yeah

[full ore]

what is a good database

[vapid bison]

MongoDB

[tranquil mirage]

Hi I'm trying to build a local school app which is simple the language that I'm using is python I couldn't set up my head around sequel at General I tried sequel like three but I couldn't make it work really well could you guys suggest a new language or a way to deal with is databases in local apps with python

[tranquil mirage]

Does it work with local apps well

[vapid bison]

Yes

[tranquil mirage]

Where can I learn it

A place that you suggest

[vapid bison]

You'd like to built Web or desktop app?

[tranquil mirage]

Desktop app

[cedar tiger]

It depends™

Offline and only handles 1 user's read and write operations, sqlite is pretty good.
Production-wise to handle multiple user queries, I personally and professionally use Postgresql.

[tranquil mirage]

Thank you I appreciate your help very much

[cedar tiger]

Np

[bright fable]

need guidance about data analyst

[fossil talon]

I can help

[fossil talon]

Good database is well-normalized database

[thorny anchor]

it depends!

[dusty blaze]

mysql

[full ore]

ok thx

[rough hearth]

!warn 1138580230144008393 Don't ask the same question in more than one channel, especially in a way that is overwhelmingly spammy. Everyone is a volunteer and asking your question in more than one place can duplicate volunteer effort.

[delicate field]

:incoming_envelope: :ok_hand: applied warning to @willow granite.

[rough hearth]

!unmute 1138580230144008393

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction timeout for @willow granite.

[willow granite]

sorry

noted

[fossil talon]

In case of "industrial scale" Teradata is the good one

[cedar tiger]

Is there an on-premise version ?

[fossil talon]

I think so but its better to check on their web-site

[cedar tiger]

Doesn't look like they do

I take that back

They do on-prem

[fossil talon]

I mean, in case of my ml project, postgres also worked more than fine

[cedar tiger]

How is Teradata better than Postgresql in your opinion and experience?

[fossil talon]

Teradata handles millions of transactions, can handle different servers at the time, and many more (my client experience, as it was used in my top-large countrywise company) ..

Postgresql.. The only time when I was using it was my personal project (ml project with the interface, in terms of TG-Bot)

So to be honest, it's hard for me to compare them personally😂

[smoky onyx]

Can anyone tell me what's wrong in those line which is exactly right in another line ?

[timid brook]

I am trying to get this dynamic 'like' query to work and for the life of me I get no results and I cannot figure out what I am doing wrong

    test_query = """
    SELECT * FROM transactions WHERE INSTR('Transaction Date', :year) > 0;
    """
    print(f"year match is: {year_match}")
    test_frame = pd.read_sql(test_query, dbconn, params={"year": year_match})
    print(f"test frame contains:\n {test_frame}")```

output:
```py
year match is: 2025
test frame contains:
 Empty DataFrame
Columns: [Transaction Date, Post Date, Charge Name, Charge Amount, Tags, Notes]
Index: []```
tried it with the sqlite ? replacement instead and with LIKE instead of the INSTR method but I just get nothing.  I know the code itself works because if I change the query to just `SELECT * FROM transactions` and remove any params I get the full table just fine

[timid brook]

the first elif needs a space after it before the conditions

[fossil talon]

Can you save it in github and share the link to repo?

[fossil talon]

What is ":year"? Make sure the value ":year" is the varchar (not integer) itself

[timid brook]

The :year is just the named placeholder for the param, the value is a string. In this case it is "2025" and I verified this as well by checking type()

[paper flower]

What database are you using?

[timid brook]

Sqlite3, also tried it with the usual ? params.eith the same result. I basically can't get anything other than just a full table query to return results and I am not sure why. It honestly feels like a case or format issue

[paper flower]

Are you checking against string literal 'Transaction Date' or against column with such name? Because:

• 'Transaction Date' is a string literal, not a column name
• If you want to reference a column name you should use double quotes "
• Generally you should avoid using column names that would need to be used inside quotes, so avoid using special symbols and spaces, usually using a-zA-Z0-9 and _ is preferred (so transaction_date in your case)

Also since your table name is already transactions you could simply name your field date or created_at

And you probably want to use database native datetime (timestamp) type instead of using strings

I don't see sqlite having a convenient way to extract year from datetime though, so I'd probably compare it with two dates instead:

where created_at >= 2024-01-01 and created_at < 2025-01-01

or use between operator

[solar summit]

Hey Guys. For a small local database app with sqlite3, how would u handle file locations being changed externally?

[timid brook]

It is a column name and I am aware of the space problems with column names but I opted to go that route rather than display renaming to simplify the display of data since by default the PyQt6 QTableView widget uses the column names in the display.
I did suspect it was a syntax issue in general though because this works for writting to the DB so I think I was mistaken in the syntax

            "INSERT INTO transactions ('Transaction Date', 'Post Date', 'Charge Name',"
            " 'Charge Amount', Tags, Notes)"

And regarding the sqlite date time issue is actually why I did not create the columns as true dates but simply as TEXT because I found it easier to parse the dates how I wanted depending on the situation vs trying to let sqlite do it. So in the case here I am checking a literal string against a TEXT column for content.
I may revisit the column name/ display name issue again in the future but I found this to be the simpler solution in the mean time and I am still learning.

edit changing 'Transaction Date' to "Transaction Date" fixed the problem. thanks! I was more used to python not technically caring about which quotes are used I didnt think to see if sqlite cared or not. or if its a functioned of pandas parsing the sql statements, or both. But that is good to know

[timid brook]

you mean like someone moving the database to another location without using your app? I would say you want a method to browse for a locate the database within your app which is saved in a settings file or env variable or similar.

[solar summit]

Mhm. I meant more like, if you have an attribute filepath in your table to a file in the OS file system and the file is moved or deleted, outside of your application (manually deleted in the OS file system), then how to make sure the database is updated accordingly.

[timid brook]

I see, I guess that would depend on what ramification there is to having that file deleted should be. Like is the file critical for the database to maintain it existing. Such that the DB should regularly validate files it is supposed to be aware of and throw an error? Or should it simply recreate that file based on some default template if it ever finds that it no longer exists?

[solar summit]

I dont think it would count as critical...its just storing Uni writings from lectures as a practice to get my feet wet with databases. I thought about not updating them and then perhaps give an error if the file path is invalid upon access to confirm the deletion or locate its new position, similar to what the git deskopt app does when it cant find a repo anymore. Would that be a good solution?

[timid brook]

Yea I think either of those options sound fine, IMO it comes down to how much burden you would want to put on the user and how critical missing files may be. Like simply an automatic thing that checks after starting the app to validate all the files are where it thinks they are and just recording an error if it is missing or wait until the file is accessed to note that it cannot be found, etc.

[fossil talon]

Guys, have you ever experienced a problem with ms sql server ignoring ORDER BY statement?

I mean whenever I try to ORDER the data selection, in fact it basically ignores it

[lime echo]

Send your SQL in here. It's probably because you're missing ASC or DESC at the end of the code.

[timid brook]

This is the fully working function and date handling that I was going for. yay

def load_db_to_dataframe(load_query: dict) -> pd.DataFrame:
    dbconn = sqlite3.connect(default_database)
    if "All" in load_query["year"]:
        year_match = "-"
    else:
        year_match = load_query["year"]
    if "Whole" in load_query["month"]:
        month_match = year_match
    else:
        month_match = datetime.strptime(load_query["month"], "%b").strftime("%m")
        month_match = f"-{month_match}-"
    db_query = """
    SELECT *
    FROM transactions WHERE INSTR("Transaction Date", :year) > 0
    AND INSTR("Transaction Date", :month) > 0 ;
    """
    loaded_frame = pd.read_sql(
        db_query, dbconn, params={"year": year_match, "month": month_match}
    )
    # print(loaded_frame)
    return loaded_frame

[paper flower]

Why did you say that working with string dates is easier?

you can format datetime object however you want with datetime.strftime

[timid brook]

Well due to difficulties I had with parsing data for import as well as how sqlite was formatting dates in the db vs the displayed value I found it easier to just leave them all as strings and parse them how I wanted.
I freely admit it can be due to lack of experience and knowledge but it is working well for now

[paper flower]

If you want to display the date in the format you want - just use strftime 🤔

Generally you shouldn't feed data directly to and from db, if you want to present data in certain way - just transform it

Your internal data format shouldn't rely on how you want to present it generally

[timid brook]

The difficulty came more from interaction and not display. I had trouble at the database level when trying to both input and parse through the date fields. I do agree that data display should not dictate data format I just am not very good at this stuff yet and it was the simplest method I could get things to work well and consistently

[fossil talon]

Never mind, man, thats a bug of the simulation website. Thank you for your attention.🙏🏻

[timid brook]

Parsing imports I had a helluva time with as well when it came to dates and I am admittedly not proud of the solution I got to work in that regard

[paper flower]

If you want to add a datetime into db (which all db drivers should support I think) is to just parse it into a datetime first and then insert it into the table you want

[fossil talon]

Do you guys want me to share with you great website with sql exercises and mock databases?

[timid brook]

Well I am knowingly committing taboo because I am actually parsing it into a formatted date using datetime...and then converting it to a string when entering it into the database... >.>

[paper flower]

not too late to just change it

[timid brook]

I mean fair point. It is probably the right thing to do though it would require refactoring the DB. I will have to see what I may break doing that but certainly less now than if I waited.

    formated_date = f"'{datestring}'"
    formated_date = year + " " + str(formated_date).strip("'")
    date_to_string = str(datetime.strptime(formated_date, "%Y %b %d").date())
    formated_date = f"{date_to_string}"
    return formated_date

fun with pdf parsing....

Well functionally I shouldnt have to redo very many things, though this method of course needs to be changed but the query itself should still work I just have to modify the parsing of the date string

I will move that up my todo list lol

[paper flower]

@timid brook Basically you can use adapters and converters to make custom types:

import sqlite3

from datetime import UTC, datetime


def utc_now() -> datetime:
    return datetime.now(tz=UTC)


def adapt_datetime(dt: datetime) -> str:
    return dt.isoformat()


def datetime_to_python(value: bytes) -> datetime:
    return datetime.fromisoformat(value.decode())


sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_converter("timestamp", datetime_to_python)

with sqlite3.connect("db.sqlite3", detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    cursor = conn.cursor()
    cursor.execute("create table if not exists example (created_at timestamp)")

    cursor = conn.cursor()
    cursor.execute("insert into example values (?)", (utc_now(),))

    cursor = conn.cursor()
    cursor.execute("select * from example")
    print(cursor.fetchall())

This is documented in python sqlite3 docs: https://docs.python.org/3/library/sqlite3.html#sqlite3-converters

I'd rather use an orm/query builder like sqlalchemy than deal with that to be honest, but it's certainly harder if you're just starting out