#databases

1 messages · Page 30 of 1

torn sphinx
queen rose
#

anybody has recognizes this query before? I need to find the database that uses this syntax

{
   "operator":"and",
   "operands":[
      {
         "operation":{
            "operator":"or",
            "operands":[
               {
                  "operation":{
                     "operator":"and",
                     "operands":[
                        {
                           "expression":{
                              "left":"type",
                              "operation":"equal",
                              "right":"stock"
                           }
                        },
                        {
                           "expression":{
                              "left":"typespecs",
                              "operation":"has",
                              "right":[
                                 "common"
                              ]
                           }
                        }
                     ]
                  }
               },
               {
                  "operation":{
                     "operator":"and",
                     "operands":[
                        {
                           "expression":{
                              "left":"type",
                              "operation":"equal",
                              "right":"stock"
                           }
                        },
                        {
                           "expression":{
                              "left":"typespecs",
                              "operation":"has",
                              "right":[
                                 "preferred"
                              ]
                           }
                        }
                     ]
                  }
               },
unique fern
#

It could be anything really, may even be elasticsearch query

inner hinge
#

I am devloping my first web app and i am stetting up my first database and its working very fine. So far so good.
I felling a bit unsure with my database scheme. I need to answear following questions:

1. Which job got the most scrap/bad parts?
2. Which article got the most scrap parts?
Normally the job and the article should be on the same layer in the pov of the Scrapbooking, but a Scrapbooking can only exist with a registered Job.

My solution is to give my ScrapBooking two foreign keys article_id and job_id. I think its not best practice, but i can't figure out a smarter solution. Maybe someone got an better solution.

image.png
paper moon
#

Hey guys im having trouble predicting some values with my lstms

#

and im lost

#

Basically im trying to predict the number of people (n_termin_dist_quad_c1) in a certain time frame(DateTime) from a specific Grid_ID

image.png
#

The tutorials on lstms dont really help me in this problem

queen rose
acoustic depot
#

Spark question: I know I can create a database/schema/etc in PySpark SparkSql, but I don't see any equivalent PySpark methods. Am I missing something?

edited to fix a typo; sorry for the confusion

shut tiger
cosmic hedge
#

If anyone can help me my server and client server license key checks out and send to client yet

!pithink

acoustic depot
olive reef
#

Hey guys, I’m currently using SQLite and SQLAlchemy on a local database for an expense tracker app I’m making. I was wondering any good practices on storing “a database version” so I can keep track wether there are some migrations to do or not, I was thinking between the lines of having a version table with just one row but I’m not sure if that’s the best approach

#

Also, since I’m using SQLAlchemy, in the event of having to migrate or to update some schemas, migrations have to be hard coded in plain SQL and execute them with SQLAlchemy, right? Are there other good approaches for managing local DB migrations?

waxen finch
#

cant say much more about it cause ive only used alembic in one project (and ive forgotten how to use sqlalchemy after not touching it for a while)

olive reef
#

Looks very useful — I’ll give it a try

#

Thanks a lot!

spice burrow
#

how can i set the "innodb_force_recovery = 3" to "innodb_force_recovery = 0"

#

i alreadddy tried openinng my my.ini file and trying to change it from there but there isnt any "innodb_force_recovery"

lethal bronze
#

This might be a pretty dumb question, but do I need an AWS access key to download public files hosted on AWS?

rare epoch
lethal bronze
#

I have an S3 link from where I need to download some data (which is public), in order to access the link and download the files do I need a ID/Secret? - I am used to using Linode, where I have an ID and key to download from my private repo, but there I create my own credentials @rare epoch

#

this might sound really dumb, but I am a bit lost

waxen finch
spice burrow
#

look i get this error everytime

rare epoch
lethal bronze
#

What do I write in aws_acccess key etc then?

spice burrow
rare epoch
waxen finch
spice burrow
lethal bronze
#

@rare epoch I get unable to locate credentials then

rare epoch
lethal bronze
rare epoch
#

correct me if im wrong but what u are trying to do is to access an s3 bucket that u have created in aws? if thats the case it should be quite simple if your s3 is set to to public. just need to copy and paste the link for that s3 bucket and you are able to access

lethal bronze
#

Yeah, but how do I download it? Suppose I can just use requests?

fading patrol
obsidian heart
#

not sure if this is super related to python, but my goal is to build my own ebook hosting server for myself and have a front end so i can access my books wherever. i know i could use calibre but i think it would be fun to do it on my own. anyone have any tips on best way to store epub files and their metadata if i want to use a python backend?

spice burrow
#

guys how can i change the innodb_force_recovery from 3 back to 0?

Screenshot_2346.png Screenshot_2347.png
waxen finch
spice burrow
#

cant find them

#

im actually so lost, dont know if i cccan ever fix that trash

#

dont even know why it changed 0 to 3

#

damn

waxen finch
mint coral
#

Abstracting postgresql foreign tables in flask-sqlalchemy in a dataclass-esque way

I'm working on a webserver that's supposed to configure a testing tool. For this, I have two databases, the on for it's config, and the one for the data to run the tests on. Said second database contains tables with data I need to constrain config values with (both in that config db, as well as in the web backend). I got the foreign table setup working no issue, but I would like to query the foreign table in a similar way than I do with @dataclass.
Could someone please explain to me, how I'd go about doing that?

The most I found myself was a guide on how to handle creating/deleting those tables via DDLElement to have them called in db.drop_all() or db.create_all(), but that doesn't handle the querying of those tables data

mortal jewel
#

How do I fix this error?
"C:\Users\kasperb\AppData\Local\Programs\Python\Python312\Lib\site-packages\pymysql\connections.py", line 644, in connect sock = socket.create_connection( ^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\kasperb\AppData\Local\Programs\Python\Python312\Lib\socket.py", line 852, in create_connection raise exceptions[0] File "C:\Users\kasperb\AppData\Local\Programs\Python\Python312\Lib\socket.py", line 837, in create_connection sock.connect(sa) TimeoutError: timed out

image.png
obsidian heart
waxen finch
# obsidian heart yeah i figured id potentially have to store metadata separately but wasn’t sure ...

yeah, in opposition to storing them in the database

this seems to be a decent comparison between either choice:
https://softwareengineering.stackexchange.com/a/150787

IMO, deeming the storage of files in the database or not as "bad" requires more information about the circumstances and requirements. Are the size and/or number of files always going to be small? Are there no plans to use cloud storage? Will the files be served up on a website or a binary executable like a Windows application?

In general, my experience has found that storing paths is less expensive to the business even accounting for the lack of ACID and the possibility of orphans. However, that does not mean that the internet is not legion with stories of lack of ACID control going wrong with file storage but it does mean that in general that solution is easier to build, understand and maintain.

oak dust
#

Any chess databases you guys recommend?

near tapir
#

Can anyone recommend resources to learn SQL and C# for developing Windows Forms applications?

muted mango
#

should i use redis as a in memory database(without needing to connect to a server) for storing multimedia files(can be video, image and any other file)?

#

i want to optimise both speed & memory

#

or keydb?

thorny anchor
#

redis and keydb both run as a server, though

muted mango
#

mhm

#

would communicating to the server add overhead?

#

even if its localhost

thorny anchor
#

yes, compared to doing something in-process. the benefits of having a server are for when you need caching across processes

muted mango
#

well i don't wanna use a server, instead i want the keyDB's speed but locally(just like sqlite3)

#

if there is none

#

how can i convert it to a file that contains the data?

shut tiger
muted mango
#

mhm

shy rampart
#

Hello fellow programmers! I have an issue with sqlite3: my program works perfectly fine, while the same program doesn't work on customers PC.
The issue appears in a SELECT query, specifically in


       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

sqlite3.OperationalError: no such column: username```

The username column IS presented in table, query for table creation:
```CREATE TABLE IF NOT EXISTS user_configs
                 (user_id TEXT, return_timestamps INTEGER, talkers TEXT, is_admin INTEGER DEFAULT 0, can_use INTEGER DEFAULT 0, username TEXT)```

Can anyone tell me a possible reason why such thing happens?
shut tiger
shy rampart
thorny anchor
#

if the table already existed without that column, it would not be dropped and recreated, and the new column wouldn't be added

shy rampart
thorny anchor
#

how did you test that the column is in the table?

shut tiger
#

And how are you sure you recreated the db?

#

Or that you are looking at the right one?

waxen finch
#

perhaps two unique indices on (company_id, name) and (company_id, barcode) is what you're looking for? either could be a compound primary key too

thorny anchor
#

would (company_id, barcode) uniquely identify an item? that could probably be a composite key

waxen finch
#

generally CREATE UNIQUE INDEX ix_something ON (col_a, col_b);, plus NOT NULL constraints if you want to fully mimic a primary key

#

although a sidenote, do databases normally handle primary keys differently from unique indices + not null? i know in sqlite, WITHOUT ROWID tables cause the data to be arranged according to the primary key which wouldn't be true with a unique index, but are there similar impacts in other databsase systems?

thorny anchor
#

i wouldn't recommend it in this case; clustered indexes are best when only one index is needed

waxen finch
thorny anchor
#

would name be unique for a given company_id? or is it only the combination of (name, barcode) that is unique

thorny anchor
waxen finch
#

ah ok, thats what i was thinking

#

i thought you were suggesting to avoid a primary key where clustering would be implicit

#

TIL postgres doesn't cluster rows by default until you run CLUSTER with an index, possibly in a background job

thorny anchor
#

i think in this case it would make sense to include all 3 columns in the primary key, since all 3 are needed to uniquely describe an item (unless i interpreted wrong ?), and create a separate index for the other ordering of name and barcode

waxen finch
#

like, (company_id, name, barcode) and (company_id, barcode, name)?

thorny anchor
waxen finch
thorny anchor
#

yeah

waxen finch
#

o wait i didnt notice the third column in your message, so its slightly different

waxen finch
#

or in other words the same name can be given two barcodes

thorny anchor
#

the indexes don't need the third item. a primary key should be a minimal set of columns that uniqely defines the row

waxen finch
#

hmm i dont get it

#

if i inserted (1, 'foo', 'xyz'), wouldn't i still be allowed to insert (1, 'foo', 'zyx') afterwards?

(assuming PK and unique index defined by your message #databases message , for clarification)

thorny anchor
#

also i just read this, which is incredibly amusing

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or a STRICT table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

waxen finch
#

ah yes, good ol sqlite compatibility

thorny anchor
#

huh. i thought you wanted only one item to have specific name for a given company id

#

nevermind. it seems you have what you want

lone oriole
hexed estuary
hexed estuary
#

(oh, and if that dataset didn't exist, you could scrape the same data from the big table on the LoL wiki)

torn sphinx
#

As a beginner, I'm working on a python project where I need to use Redis as the primary database (for speed) and AstraDB as the persistant database (to permanently store all the data). Is there any easy way or workflow of doing this? If some data was not found in Redis, it should load the data from AstraDB into Redis. Similarly, it should write new data instantly into Redis and then also write the data in AstraDB before it vanishes from Redis. Is there a built in way or do I have to manually put if/else conditionals everywhere?

ionic pecan
#

how much speed are we talking about

lethal bronze
#

Im getting an "Error converting column "issue" to bytes using encoding UTF8. Original error: bad argument type for built-in operation" error when dumping my pandas df into parquet, anyone know how I can find the issue? Also is it possible to just replace all non-ut8 chars with " " or something?=

torn sphinx
ionic pecan
# torn sphinx I mean, currently, the speed Redis is providing is perfectly working for me. I j...

I see. I'm asking because a lot of times people ask how to combine Redis and their database when they have like 5 queries a second where it's kind of unnecessary effort.
I think if you want to prevent duplicate effort in form of if / else conditionals you could write a helper function. Assuming you'd be using a SQL database, you could do something like

result = query_or_cache(f"users:{user_id}", "SELECT * FROM users WHERE id = $1", (user_id,))

with the function being something like

def query_or_cache(cache_key: str, query: str, params: list[Any]):
    from_redis = redis_client.get(cache_key)
    if from_redis is not None:
        return json.loads(from_redis)  # assuming it's json formatted
    db_cursor.execute(query, params)
    results = db_cursor.fetchall()
    marshalled_results = json.dumps(results)
    redis_client.set(cache_key, marshalled_results)
    return results

Something like this would work, but the problem is that 1) you now need to have both the Redis client and the DB client available anywhere (doable and you should do it for easier testing) but 2) if you change the database format you need to invalidate your cache as otherwise your code will expect other things to be returned than that are actually returned (imagine adding a new column and then it fetches the old cache object).
These are all solvable but I think if as you say your current approach works for you then I would stick to it. How would the database be less expensive than Redis? They both need disk resources and I'm not aware of Redis being blissfully inefficient compared to disk

torn sphinx
# ionic pecan I see. I'm asking because a lot of times people ask how to combine Redis and the...

I can't thank you more. I appreciate your help from the bottom of my heart. This is all I needed. And yeah, I get it that you asked to save my efforts. The thing is my project is currently in beta, but we can expect a lot of users in a month or two, for which I need to prepare the database backend. I'm thinking around 70-200 requests per second right now. Also, would a CQL database work in this workflow? AstraDB seems to be a CQL database.

finite crow
#

If you're in 2nf, and the relation is like R(K, A, B, C), and B is dependent on A, and C is dependent on B, what's the better way to change it to 3nf? Like this?

R1(K, A)
R2(A, B)
R3(B, C)

Or like this?

R1(K, A, C)
R2(A, B)
#

Should it be done the latter way so that B doesn't need to be in two places?

ionic pecan
#

Also a thing to be aware of, as is the function above would also cache writes, which you probably don‘t want

#

Or you just call it on reads^^

thorny anchor
finite crow
# 
R1(K, A)
R2(A, B)
R3(B, C1, C2, C3)

vs

R1(K, A, C1, C2, C3)
R2(A, B)
#

Maybe I have it backwards what's redundant and what isn't

#

I'm not really sure

thorny anchor
#

i don't mean multiple columns, i mean

create table books (
  title varchar(300) primary key,
  author text,
  author_age int
);
``` if you have this table, you could have multiple records `('CLRS', 'C', 10)`, `('CLRS', 'L', 5)`, and so on, if a book has multiple authors. if you use the second version, then you could not represent this
finite crow
#

But if C is functionally dependent on B then doesn't that mean each B maps to one specific C?

thorny anchor
#

ah. yeah that is true

finite crow
#

I'm now trying hard to figure out which option results in both less columns and rows combined, because I'm pretty sure that's the actual goal.

#

Not just less columns.

# 
R1: every value of K times every value of A, plus
R2: every value of A times every value of B, plus
R3: every value of B times every value of C

vs

R1: every value of K times every value of A times every value of C, plus
R2: every value of A times every value of B

I think it's like this.

#

(k * a * c) + (a * b) vs (k * a) + (a * b) + (b * c)

thorny anchor
#

author_age is a fact about the author, not the book, so it shouldn't be present on the books table in 3nf

finite crow
#

I just randomized k, a, b, and c values and tested what had the better score in python, but is that cheating?

finite crow
#

In our practice exercises, they really are just called "A B C D etc"

thorny anchor
#

it's not about minimizing columns. you're probably going to create more columns when normalizing

finite crow
#

I'm trying to figure out how to minimize tuples now

thorny anchor
finite crow
#

Yeah you're right, it would just be the number of As

#

I made a mistake there

#

k + a maximum rows vs k + a + b maximum rows?

thorny anchor
#

you can't just count the rows though, because you're duplicating data in the example i gave

finite crow
#

(k * 3) + (a * 2) maximum pieces of data vs (k * 2) + (a * 2) + (b * 2) maximum pieces of data?

harsh pulsar
finite crow
#

Yes

harsh pulsar
#

is this for a software application, or a data warehouse?

#

do you have any practical goals/constraints here, or is it just a thought exercise?

finite crow
#

It's a thought exercise

#

I'm in a class

harsh pulsar
#

I think the original R(K, A, B, C) has the fewest distinct cells. All the other designs should be equivalent, but you have additional columns for the foreign key references

#

If they're all 1:1 you could just concatenate all tuples across with INNER JOIN + USING, resulting in the exact same output as the original "quadruple entity" design

finite crow
#

It's like I can barely think more than 1 or 2 levels deep if that makes sense

opaque sphinx
# 
from db.base_class import Base
from sqlalchemy import Boolean, Column, Integer, String, DECIMAL
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class Sport(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, index=True)
    matches = relationship("Match", back_populates="sport")
    player = relationship("Player", back_populates="sport", uselist=False)


class Match(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, unique=False)
    number_of_teams = Column(Integer)
    team1_id = Column(Integer, ForeignKey("team.id"))
    team2_id = Column(Integer, ForeignKey("team.id"))
    sport_id = Column(Integer, ForeignKey("sport.id"))
    sport = relationship("Sport", back_populates="matches")
    team1 = relationship("Team", foreign_keys=[team1_id])
    team2 = relationship("Team", foreign_keys=[team2_id])
    entry_fees = Column(DECIMAL, nullable=False)


class Team(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, unique=False)
    number_of_players = Column(Integer, nullable=False, unique=False)
    players = relationship("Player", back_populates="team")


class Player(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False, unique=False)
    team_id = Column(Integer, ForeignKey("team.id"))
    sport_id = Column(Integer, ForeignKey("sport.id"))
    sport = relationship("Sport", back_populates="players", uselist=False)
    team = relationship("Team", back_populates="players")
    is_active = Column(Boolean, nullable=False)
    is_substitute = Column(Boolean, nullable=False)
    is_inactive = Column(Boolean, nullable=False)
#

idk why alembic is trying to drop all tables in this schema upon running alembic revision command

# 
def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_team_id', table_name='team')
    op.drop_table('team')
    op.drop_index('ix_player_id', table_name='player')
    op.drop_table('player')
    op.drop_index('ix_sport_id', table_name='sport')
    op.drop_index('ix_sport_name', table_name='sport')
    op.drop_table('sport')
    op.drop_index('ix_match_id', table_name='match')
    op.drop_table('match')
    op.drop_index('ix_user_email', table_name='user')
    op.drop_index('ix_user_id', table_name='user')
    op.drop_table('user')
    # ### end Alembic commands ###```
finite crow
#

I think that's what he was trying to point out.

harsh pulsar
finite crow
#

A -> B
B -> C

#

I think then that he was saying, with R1(K, A, C) and R2(A, B), A may appear multiple times in R1 with different values of C. But if you split it into three different relations instead then that won't happen.

finite crow
#

I'm getting filtered by this really hard.

harsh pulsar
finite crow
#

I guess it's just K -> A, B, C

harsh pulsar
#

yes but 1:1 or 1:many or many:1?

#

the best way to deal with brain fog in my experience is to write everything out in as much detail as possible

#

I don't know about you, but the main issue with brain fog for me is that my working memory goes to absolute shit, so writing everything out and breaking it down into the smallest possible decision points can help

finite crow
#

I'll need to do that probably.

finite crow
thorny anchor
finite crow
thorny anchor
#

no

finite crow
finite crow
thorny anchor
finite crow
harsh pulsar
#

or could two different As be related to the same B?

finite crow
#

I fucked it up again, sorry. An A B C could go to multiple Ks.

#

There's no bidirectional stuff

#

Or whatever you call it when it's both surjective and injective.

#

I don't remember the terminology, but yeah, X -> Y but no Y -> X.

#

Anyways, I think I can try to finish this from here. Thanks for your help, @thorny anchor and @harsh pulsar, I appreciate it. I probably would have answered R1(K, A, C) and R2(A, B) and lost points otherwise.

finite crow
paper flower
wise goblet
#

High chance u will not be able to go beyond its limits

thorny anchor
#

even a hundred thousand is tiny for postgres

harsh pulsar
#

in my web app, the python event loop seems to saturate before the postgres connection does

#

the python-side database i/o latencies are like 10x what the postgres logs show, which indicates to me that the event loop is letting completed db operations sit around for a long time before being picked up

#

it's still good enough for what we need, but it's definitely kind of disappointing to see what looks like a severe bottleneck. i'd love to optimize it one day if possible

#

maybe 3.12 will help, not sure we can switch to pypy due to some data science dependencies

ionic pecan
#

this is all true, but keep in mind the original question was about adding a database to persist what's in redis, not to add redis to cache what's in the database :D

hollow oar
harsh pulsar
#

gunicorn with uvicorn workers, load-balanced across 4 relatively small ECS task instances

#

not sure if the gunicorn + uvicorn setup is suboptimal

hollow oar
delicate fieldBOT
#

uvicorn/loops/auto.py line 3

import uvloop  # noqa```
`docs/index.md?plain=1` line 130

--loop [auto|asyncio|uvloop] Event loop implementation. [default: auto]```

hollow oar
harsh pulsar
#

We were originally using asyncpg but migrated to psycopg3 to make testing easier without making all our db setup fixtures and everything async

#

I'm not even 100% sure my diagnosis is correct, but the difference between the db-side request handling time and the python-side request-handling time is really suspicious

hollow oar
harsh pulsar
hollow oar
harsh pulsar
wise wind
# 
class Base(DeclarativeBase):
    pass

class BaseMixin:
    @declared_attr.directive
    def __tablename__(cls):
        return cls.__name__.lower()
    id: Mapped[int] = mapped_column(primary_key=True)```
# 
class Dms(BaseMixin,Base):
    sender: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    receiver: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    def __repr__(self):
        return f"Dms(id={self.id},sender={self.sender},receiver={self.receiver})"```
#

Whenever I access Base.metadata I get: MetaData(). That's the reason the tables aren't being created.

#

Could someone explain to me why the Dms table isn't added to the Base.metadata?

finite path
#

hey i started learning coding recently and im kind of confused with some terms. could someone please explain me what does "data type" and type function in python means???

pseudo wyvern
#

Dose anyone know of a simple db like shelve that is hosted online?

#

For free specifically

paper flower
#

You don't run any sync operations in async endpoints, right?

#

Also as ry mentioned - try asyncpg, should be a simple pip install + a db url change

harsh pulsar
# paper flower Only if you have it installed

Yes I am using the "standard" extra which unless I misread pulls in uvloop

There is some sync code (unavoidable) but it's trivial, on the order of microseconds to complete at most

I'm seeing some people say asyncpg is faster and some people say it's not. Like I said I switched from asyncpg already, I can try a benchmark but I'm skeptical that somehow psycopg3 with the binary protocol is that much worse

paper flower
pearl lodge
#

is it possible to use SELECT IF EXISTS?

#

in sqlite?

harsh pulsar
# paper flower Just compare them 🤔

Haven't been able to reproduce in artificial load testing, doing actual proper load testing is on our roadmap but we are a DS team so we have actual DS work to do first

torn sphinx
#

have someone ever used async sqlmodel? I wanted to look at some project using it. For get some examples

harsh shadow
#

Hi! I'm using psycopg2 to connect to my PostgreSQL Database. However I don't want the user, key, database name and host to be hard coded. Is it safe to have them formatted in the connection function (psycopg2.connect()) like this ? con = psycopg2.connect( f"dbname={DB_POSTGRESQL} user={USER_POSTGRESQL} password={KEY_POSTGRESQL} host={DB_HOST}" ) The variables are stored in an .env by the way. Thanks!

thorny anchor
thorny anchor
pearl lodge
#

but i realize i don't need it

#

i'm trying to create a cache for my macro, but even changing the cache algoryth

#

it don't change anything

#

guess i need to add something to my lark code

#

like, something releated to leaves or something

#

but even making all the big fat ass code of lark

#

i still lack some knowledge

#

that is where stocking variables becomes a problem

#

if someone have Larlk library knowledge please tell me

#

i really need

#

that rare knowledge

#

i'm basically mining for ancients scraps (I.E Minecraft)

thorny anchor
#

you can ask precise questions to get answers for things you're confused about

thorny anchor
pearl lodge
#

few peoples knows that

#

library

thorny anchor
#

i can't imagine why you would need sqlite with lark

pearl lodge
#

sqlite don't need

#

but when you need cache

#

you need sqlite

#

but i made the test and changing the cache changes nothing

#

i noted that the program is executing the macro from left to right

#

and i don't know how i fix it

#

due to limited knowledge of Lark

harsh shadow
wise wind
# 
from sqlalchemy.orm import Mapped,mapped_column,declared_attr,as_declarative


@as_declarative()
class BaseMixin:
    @declared_attr.directive
    def __tablename__(cls):
        return cls.__name__.lower()
    id: Mapped[int] = mapped_column(primary_key=True)```
# 
class Dms(BaseMixin):
    sender: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    receiver: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    def __repr__(self):
        return f"Dms(id={self.id},sender={self.sender},receiver={self.receiver})"```
#

The table isn't being added to the BaseMixin.metadata because when I access BaseMixin.metadata, I get:

# 
MetaData()```
#

Can someone explain to me what I am doing wrong?

high berry
#

Can anyone help me with my tables on SQLAlchemy?

fading patrol
fading patrol
#

Depending on the DB, UPSERT may be an option

thorny anchor
#

what will you do if it doesn't exist?

wise wind
ornate panther
#

also, what version of sqlalchemy is this? You should be inheriting that base mixin from sqlalchemy.orm.DeclarativeBase

wise wind
ornate panther
#

so which version of sqlalchemy are you running?

#

oh yeah, I think you need to call configure_mappers() first

wise wind
ornate panther
#

anyways, try configure_mappers() first, then you should see something in the metadata

wise wind
#

Or if you want to reference the section in the documentation

ornate panther
#

that function fills in the metadata

wise wind
#

Do you have a specific example?

ornate panther
#

of what?

wise wind
ornate panther
#

I don't understand. I told you that you need to call configure_mappers() to get the metadata filled in. Do you need example on how to import that, or what?

wise wind
ornate panther
#

I already linked the full import to you, do you know how to import things in Python?

wise wind
#

Okay maybe I misread the documentation. Thanks for the help.

#

Im thankful

#

I have to test it out when I get back on my pc.

paper flower
#

Also it should inherit from Base to be added into metadata

#

Just to be sure - put both classes into the same file and see if that works, if it does - your Dms class just didn't exist

wise wind
# paper flower I think your `Dms` class just doesn't exist since you didn't import that module ... 
@as_declarative()
class BaseMixin:
    @declared_attr.directive
    def __tablename__(cls):
        return cls.__name__.lower()
    id: Mapped[int] = mapped_column(primary_key=True)

class Dms(BaseMixin):
    sender: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    receiver: Mapped[str] = mapped_column(ForeignKey("users.username", ondelete="CASCADE"))
    def __repr__(self):
        return f"Dms(id={self.id},sender={self.sender},receiver={self.receiver})"

print(BaseMixin.metadata)``` It still printed MetaData()
#

Any more context you need?

paper flower
# 
@as_declarative()
class BaseMixin:

->

class BaseMixin(DeclarativeBase):
wise wind
#

this is the file structure btw

paper flower
#

File structure shouldn't really matter

wise wind
paper flower
#

Did you try changing it to DeclarativeBase?

wise wind
paper flower
#

Can you make a reproduction of this in a single file?

wise wind
#

sure

wise wind
wise wind
#

I thought alembic created the tables but I thought wrong so I just added this: ```py
from app.db.base_class import BaseMixin
from app.db.database import engine
from app.models.dms import *
from app.models.friend_requests import *
from app.models.friends import *
from app.models.notifications import *
from app.models.servers import *
from app.models.user import *
import asyncio

async def create_tables():
async with engine.begin() as conn:
await conn.run_sync(BaseMixin.metadata.create_all)

asyncio.run(create_tables())```

paper flower
#

alembic should create tables

wise wind
paper flower
#

alembic upgrade head

#

You have to create your migrations/revisions first

wise wind
wise wind
#

sorry, I'm new to alembic

#

Like do I have to explicitly make all the tables in the versions directory?

glacial current
#

that will generate the change file

wise wind
glacial current
#

k cool

mint pasture
#

hey guys got a question , is it possible to create a local database without sql's involvment, like just a python data base (fyi i want to be able to get information from the data base)?

harsh pulsar
fading patrol
fallen vault
#

I'm wanting to make a database backup feature for an application. Would it be correct to just create a copy of the sqlite database and how the user pick where to save it and then delete the main database and replace it with this one when the user use the load database function?

storm mauve
#

SQLite databases are literally just a file so yeah that should be fine - the file contains all information about the database that exists

fallen vault
#

Alright, thanks. Makes my life a bit easier lol.

storm mauve
#

you might want to add options like exporting to/importing from common formats like CSV or Parquet though

fallen vault
#

I currently have export and import options that save to CSV for the main part fo the data saved. I want to add seperate export/import for the entire database to save config data instead of using a config.json for themes, user limits and uom.

storm mauve
#

json is actually a fairly descent way to save configurations tbh

fallen vault
#

I guess i could export to csv for the main data and package that with the json to create a zip maybe? I just wanted the export to be one file intead of multiple files for when the user updates the software to a new version.

shut tiger
fallen vault
#

Im not familiar with that process. Do you have any resources? I have the software packaged in an installer (which I only recently learned how to do properly) so ive offloaded the responsibility of migrating to the end user via two buttons (one for exporting and one for importing)

shut tiger
fallen vault
#

I’d probably prefer Django, but it currently isn’t worth migrating from desktop to web app, unless more than one company starts using it, imo currently. Will definitely keep in mind for my next project.

Alembic says it’s for SQAlchemy, so I guess it’s time to learn something new lol. Thanks for the advice.

shut tiger
paper flower
shut tiger
paper flower
#

And it just gives you less control compared so say sqla

shut tiger
paper flower
#

I didn't use django 10 years ago, but did around 2 years ago

#

Writing complex queries is confusing, connection management just isn't there

shut tiger
#

What does "connection management" mean?

paper flower
#

Explicit connection management, pooling, etc

shut tiger
#

But yea, complex queries can be annoying. But so is it in SQLAlchemy imo. Django makes joins way nicer than in SQLAlchemy and that's 90% of what you need to do.

paper flower
shut tiger
shut tiger
paper flower
#

Which uses magic strings essentially, honestly I just don't like it

shut tiger
#

The aggregation/group by is super confusing and bad in Django though, that's for sure. I have to read the docs every damn time.

shut tiger
#

Without language support you can't win.

paper flower
#

Model attributes in sqlalchemy are typed since 1.4-2.0

#

e.g. Model.id is InstrumentedAttribute[int]

#

model.id is int

shut tiger
#

select(foo.bar = 3) or something? I don't remember the syntax

paper flower
shut tiger
paper flower
#

But it looks exactly like sql

shut tiger
#

I mean... it's perfectly ok and pretty pythonic, but magic is magic

paper flower
#

By that logic strings are magic too?

#

i.e. filter(a__b__c==42)

shut tiger
#

sure, that's my point

#

You get magic a or magic b. Saying "magic is bad" when both are magic is not really a good argument either way.

#

kwarg juggling on one side, operator overloading on the other 🤷‍♂️

paper flower
#

For connection management - I don't think django can use connection pooling very well and using multiple databases could be a challenge

shut tiger
#

Multiple databases is well supported for ~15 years. Connection pooling I don't know though.

paper flower
#

For multiple dbs - depends on the usecase, with sqlalchemy you can just create as many engines as you want

shut tiger
#

Sure, and in Django you can configure as many dbs as you want. It's only useful for apps though, as you really REALLY shouldn't do it at runtime.

paper flower
#

e.g. if you're running a multitenant system and want to use dbs/schemas/whatever

shut tiger
#

Yea, then don't use Django for sure

paper flower
#

Well, yeah, you'd have to do that at runtime

shut tiger
#

But imo if you are doing that you are doing something very weird imo

paper flower
#

Db sharding is a thing

shut tiger
#

It's just not the 99% case

fallen vault
#

I… I think I’m gonna go with the save file method because all of that was way above my head. I need a dedicated Django class I’m sure at this point based on my learning style.

shut tiger
#

Version of the DB format that is, not the app.

fallen vault
#

Well the user will only be exporting and importing during version updates, and the database is set at this point, so it’s just moving the file itself.

paper flower
#

If you use standalone django orm it's quite vague when connection is created/closed/commited, I think in case of sqla it's quite clear.
Also django orm has some weird behaviors:

  • If you call model.save all properties are saved/updated
  • Not really a weird behavior, but since it doesn't use a UoW pattern it's generally less optimized for writes, at least probably have to handle that yourself with bulk_update/create
  • It doesn't handle creating nested models too
shut tiger
fallen vault
paper flower
#

@fallen vault What would you be using that DB for?

shut tiger
fallen vault
shut tiger
fallen vault
paper flower
fallen vault
#

And the function will handle the moving and such.

shut tiger
fallen vault
#

The location to grab it from, not the location to save it to.

#

The user picks the location to save to.

shut tiger
#

Well.. if it's an upgrade you will presumably already know the location as the user chose it in a previous version?

fallen vault
#

I don’t know how to communicate between version…

Currently it’s set up to uninstall the old version and run the new version installer. Then import the data from a csv file.

#

Which the software creates and the end user knows where they saved it.

#

I’m really just taking these ideas from software I currently use daily. One is quite literally just a bat file that copies the database to a user specified location.

#

Although, they hard coded F: so that caused issues when we got a new flash drive lol.

fast current
#

how your experience with creating abstract classes to handle database queries? Does it actually help?

fading patrol
fast current
#

@fading patrol yes, but I'm trying to cover the case where there is a change in the database technology, such as between postgres and sqlite or even psql to mongodb or whatever. I could create an ABC that needs to be implemented, but if I implement a class for the new DB then all I have to do is rename the import where I use it and it should work, right?

paper flower
#

Usually if you really need it you can create your abstaction layer on top of an orm, but you probably won't need to switch between dbs that much

fading patrol
paper flower
fading patrol
paper flower
#

Yep, that was kind of my point too

#

Just pick one, you can scale out pretty well with either option

#

And it's not like it would be a problem in their case

patent quarry
#

I use redis and MySQL together with a data layer abstraction. The app above has no idea.

#

As my app evolved some things tranisioned from redis to MySQL as the way the data was being used was more suitable in one than the other

paper flower
#

What exactly do you mean by "redis and mysql together"

#

Are you using redis just as a cache?

patent quarry
#

Some data is temporal and I don't want to keep it, just need it in one form for quick access then I'm done. This system ingests a lot of data and only some gets to the db

#

But also caching, but not for a web front end, but for services that use the incoming data for their purposes

#

It's like shared memory for my own micro services

fast current
#

thanks for the feedback, my initial feeling was that the abstraction might be unnecessary as well, but I thought perhaps I could make an ABC or something that has some simple find() insert() delete() methods where I could just implement a new class and do a simple find/replace to use the new db.

rigid relic
#

What’s best database for a discord bot, local database on my raspberry pi or an online one like MongoDB?

patent quarry
#

configurations?

rigid relic
#

Server specific warns, mutes lists, prefixes ect

#

And some custom stuff regarding a specific server

#

Users submit things it gets put in a database then mods accept/deny those things

#

Also some user specific ones as well

patent quarry
#

sqlLite would probably be fine. What is the scale? Like 10s of users or 1000s

paper flower
#

Sqlite would do fine for both 😅

paper flower
#

It doesn't make sense to just stuff everything into a single storage if different data is used differently

rigid relic
waxen finch
#

if you're using raspberrypi headless, i.e. without a desktop, that might be difficult, and last time i tried setting up xrdp for a remote desktop i think it didnt go so well (though i had once gotten VNC working)

#

i guess some other ideas to use the GUI in the case that you don't have a desktop:

  • set up a network drive to remotely access it, but sqlite's known to have issues being used over network https://sqlite.org/useovernet.html
  • copy the database file to your computer and then use the GUI

the latter would be simpler, but if you need to make any changes, you'll have to carefully repeat them on your raspberrypi's copy to avoid losing any new data (or turn off the bot until you're finished with any changes)

#

if network access is really necessary, a client-server database like postgresql would be more suitable, then you can use something like DBeaver to connect to it

grand lantern
#

!cban 1231095712083607612 ban evasion

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied ban to @errant axle permanently.

hallow field
#

I'm working on an AI powered discord bot where I'm using Redis as the primary database, and I've decided to pair Redis with AstraDB. Now, I need to build an efficient and optimized workflow that will put Redis in between AstraDB and the application. I need a workflow in which the application reads data in Redis, if it's not available in Redis, it's fetched from AstraDB to Redis for a period of time. For writing, My application should be able to write on Redis immediately and move on, while further writing can be done asynchronously without slowing down the application. As my application is a chatbot, database writing will be a crucial part. What are the solutions? How can I write code for an write-behind pattern? I'm a beginner, any kind of help will be appreciated. 🩷

patent quarry
mint coral
#

I would like to call a function in a postgresql check constraint, in a class I define via SQLAlchemy ORM.
I've found https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.CheckConstraint, but I don't understand how I'd go about using it

this is the function I want to check against

CREATE FUNCTION mytable_valid_name_constraint(identifier VARCHAR(20))
    RETURNS BOOLEAN

this is the current column definition, but I don't understand how I am supposed to craft the SQL statement

name: Mapped[str] = mapped_column(TEXT(), CheckConstraint(text("()")), nullable=False)
``` Any suggestions?
paper flower
#

Also nullable=False is redundant since that information could be taken from the type hint

mint coral
mint coral
#

yeah, that was it

#

thank you very much

paper flower
#

Nice 🙂

#

np

spice bobcat
#

i changed one param in my db an now it says that its locked

wise goblet
# fast current how your experience with creating abstract classes to handle database queries? D...

If u plan to support multiple databases for your program, it will help. Some do need that, if they offer running against multiple different databases for some reason
Otherwise it will not.
YAGNI, you aren't going to need it.
It increases code amount and complexity, so it will not be justified unless u indeed needing it

How to implement it easier in python? 😄 Using strictly configured mypy/pyright, abusing less any, and yeah ABC/Protocols will help

#

====
Some examples when it was justified
Well, i had a case of needing to create read only database user for multiple different database engines with all configurations to run datadog user
as aws lambda with automatic expiring in 24 hours.
since i needed to create user periodically for postgresql, mariadb, mysql. it was justified to me making this abstraction

Also it was justified for me during writing code for provisioning some parts during database replications from production to staging.
as this code needed again working for different database engines (postgres, mariadb, mysql)

#

=====
if u develop backend application meant for distribution to other developers for running, where each dev could be wishing to run it at different db, it will be justified too then
for example some application monitoring health of smth, or chat app and etc

#

=======
If you know you develop private backend app that will run always only on postgresql...

#

...it has no meaning to support anything but postgresql.

#

use as docker container postgresql locally too, to have it during local development

smoky mortar
#

Question is there a reason that sqlite3 in python is fetching an DATETIME collumn on my db table as str instead of datetime?

#

This is causing an issue in my c extnesion code when I go to process the sequence of values in each row for the collumn values 😅.

thorny anchor
#

sqlite doesn't have a DATETIME type, it uses either a number or a string to represent it

fading patrol
smoky mortar
grim vault
smoky mortar
pseudo robin
#

Do you know a good website to download Hierarchical series?

waxen finch
#

#python-discussion message @wheat thistle, cc @opal flint
could it be solved using an aggregate query? the sqlite documentation notes that using MAX() on one column causes other columns to return values from a row containing the maximum, so it could be applied to the datetime column to return just the latest reports, something like: sql SELECT id, location, type, MAX(created_at) FROM report GROUP BY location, type ORDER BY location, type; ┌────┬──────────┬─────────┬───────────────────────┐ │ id │ location │ type │ MAX(created_at) │ ├────┼──────────┼─────────┼───────────────────────┤ │ 3 │ 'loc1' │ 'type1' │ '2024-04-20 10:20:30' │ │ 6 │ 'loc1' │ 'type2' │ '2024-04-20 10:20:30' │ │ 21 │ 'loc2' │ 'type1' │ '2024-04-21 10:20:30' │ │ 24 │ 'loc2' │ 'type2' │ '2024-04-21 10:20:30' │ │ 33 │ 'loc3' │ 'type1' │ '2024-04-22 10:20:30' │ │ 36 │ 'loc3' │ 'type2' │ '2024-04-22 10:20:30' │ └────┴──────────┴─────────┴───────────────────────┘ MRE: https://paste.pythondiscord.com/463Q

(explaining the query shows ix_report_created_at was unused, so it might be better to combine that into the ix_report_location_type index)

wheat thistle
thorny anchor
fallen vault
#

I am wanting to delete the databse file while the software is running. Is there a way to ensure its not being used? I tired running con.close()

image.png
fading patrol
fallen vault
# fading patrol On Windows? Probably not 😅

Apparently it does it anyway, no idea why it’s throwing the error. Decided to wrap it in a try/except block and move on. Now im having issues with the installer due to write abilities, can’t remember how I solved this issue last time.

distant mural
#

i need help in sql

#

pls

#

:/

shut tiger
fair coral
#

in this er model, should there be 3 tables. an artist table that has a primary key for identifying artists, a song table that has a primary key for identifying songs, and a performs table that links the two together?

shut tiger
fair coral
#

oh many to many

shut tiger
#

The code isn't in English. You didn't describe the problem, or what tech you use.

simple ridge
native zinc
#

Guys, I'm trying to add a simple unique field to a model of mine, just for learning using SQLAlchemy plus alembic

I made a simple person model:

class Person(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

I made the Person.name field unique.

class Person(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    age = Column(Integer)

I added render_as_batch to my alembic.context.confuguration methods in env.py.

I'm getting this error that doens't take care of itself automatically:

File "C:\Users\zackp\.virtualenvs\migrations-learning-XL53Svsw\Lib\site-packages\alembic\operations\batch.py", line 669, in add_constraint
    raise ValueError("Constraint must have a name")
ValueError: Constraint must have a name

My metadata comes form a Base = declarative_base() line, so I don't directly instantiate metadata to add conventions.

How can I fix this?

#

Working with a simple sqlite database, and to re-emphasize, I already added render_as_batch...

wet verge
#

@split ore @waxen finch @torn sphinx

#

how to make blob

split ore
#

Take ink and splash it on paper

wet verge
#

alr grabbing paper

waxen finch
wet verge
torn sphinx
#

it takes bytes

waxen finch
#

low big do you expect your integers to be in terms of bytes?

wet verge
wet verge
#

like idk

#

20-30 digits?

torn sphinx
#

Idk :kekw:

wet verge
#

num.to_bytes()

#

yep

torn sphinx
wet verge
#

dumb af 😭

wet verge
#

ok so its .from_bytes()

split ore
wet verge
wet verge
#

fucks sake

#

"Overflow error" py n = 12345 a = n.to_bytes() b = a.decode() print(b)

#

😭

split ore
#

Shorten your integers

wet verge
#

how

#

i need them all

split ore
#

What are you doing with all those

wet verge
split ore
#

Ah

#

I'm clueless bye 💀

wet verge
#

so 4343 would be:
play 4, play 3, play 4, play 3

wet verge
waxen finch
wet verge
#

wait i've already broken it?

split ore
#

Yeah kinda

torn sphinx
#

I want to experiment a bit with it

wet verge
#

im a genius

# 
n = 12345
a = n.to_bytes(10, 'big')
b = int(a.hex(), base = 16)
print(b, type(b))```
wet verge
#

should be just under there

wet verge
#

easy

#

(im done for)

torn sphinx
#

do you actually need to turn it into hex?

#

I thought blob works already with bytes

wet verge
split ore
#

Just use TEXT at this point

#

You can convert that back to int easily

torn sphinx
wet verge
#

that was literally my first idea 😭

torn sphinx
#

you made it way harder 😭

split ore
#

Lmao

wet verge
torn sphinx
wet verge
#

me

west hill
wet verge
west hill
#

using text?

fiery narwhal
#

you can text to ai

torn sphinx
#

I need to have this exact functionality but if command is awaited twice in cache() function, it raises errors. How to have this exact functionality without awaiting command twice.

async def store_data(key, value):
    if isinstance(value, dict):
        await r.hmset(key, value)
    elif isinstance(value, list):
        await r.rpush(key, value)
    elif isinstance(value, (str, bytes)):
        try:
            json_data = json.loads(value)
            await r.json.set(key,'.', json_data)
        except json.JSONDecodeError:
            await r.set(key, value)
    else:
        await r.set(key, str(value))

async def cache(command, key):
    result = await command

    if result:
        print('DEBUG : Retrieved from Redis')
        return result
    else:
        result = db.get_collection(key.split(':', 1)[0]).find_one({'_id' : key.split(':', 1)[1]})
        value = result['data']
        print('DEBUG : Cached from AstraDB')
        store_data(key,value)
        print('DEBUG : Added to Redis')
        result = await command
        print('DEBUG : Cache working in Redis')
        return result



async def main():
    await cache(command=r.lrange('users:1',0,-1),key='users:1')


asyncio.run(main())```
Raised Error :

File "d:\Coding\Applications\LLM-Apps\Projects\Snow\database-read-backend.py", line 31, in cache
result = await command
RuntimeError: cannot reuse already awaited coroutine

shut tiger
torn sphinx
shut tiger
paper flower
torn sphinx
# paper flower You're trying to await coroutine twice

yes, and I'm asking about is it possible to have this functionality without getting this error or awaiting coroutine twice?

@shut tiger the first time if it returns none, the function retrieves data from the NoSQL database (the db object), stores it inside Redis. Then I need to read using the command again, to read the new cached value in Redis.

paper flower
#

Or move redis call to cache function if you'll always be using lrange

shut tiger
torn sphinx
torn sphinx
shut tiger
torn sphinx
shut tiger
shut tiger
#

You had the full dict before you read it anyway. You can just change the second result = await command to result = result and it would be the same. Or delete the line of course, which is my suggestion.

torn sphinx
shut tiger
torn sphinx
shut tiger
#

(an await is like a function call)

gleaming cipher
#

I'm making a project that is basically an atm system. I need to make a database for the cards and accounts. How should I best structure the cards and/or accounts database? As I am allowing for an account to have more than one card

upper sapphire
#

can somsone please explain to me how does the i in game work

image.png
#

and what does this do

fading patrol
shut tiger
shut tiger
upper sapphire
upper sapphire
thorny anchor
upper sapphire
upper sapphire
upper sapphire
dark ivy
#

so

wet verge
dark ivy
wet verge
#

"yeah bro i know sql"

dark ivy
#

I never said that? 😭

wet verge
#

didnt even write a url 😭

dark ivy
#

this things lying to me

wet verge
dark ivy
#

Idk Ion understand it so I gave up for now 💀

#

Ima get the part of calling the function to generate the vid first then ill worry abt charging a token for it

shut tiger
grim otter
#

Hi

#

So i found a csv file with all movie names from 2006 to 2016 (im a noob in dis)

#

so if i give genre name

#

i want to get a list of all movies from tht genre

#

dataset link

#

ping me

#

or dm me

shut tiger
grim otter
#

oh k

thorny anchor
shut tiger
coral wasp
# grim otter oh k

And, because: ```py
import duckdb
df = duckdb.sql("select * from 'myfile.csv' ").df()

grim otter
coral wasp
#

It runs sql against a csv file

plucky aurora
#

anyone up?

fading patrol
grim holly
#

Hey everybody, I'm trying to understand how storing audio and video files on a database would work. For learning purposes, I'm just trying to create a website that people can just upload videos on and then have those videos saved and played back to them later using Django. How could I save these files? I assume I wouldn't actually be storing the audio/video file on MongoDB or some SQL database, but I'm not sure. Any advice is appreciated that points me in the right direction

thorny anchor
#

the usual way to do this is to just store the files in some blob storage like s3 and store a file path or url to where you stored it

grim holly
#

oh i see, that seems much easier then i thought it would be 😅

paper flower
#

@grim holly If you're familiar with docker you can host Minio or SeaweedFS for S3 storage, if you don't want to use any cloud option

ionic pecan
#

you can also just use your local filesystem, django supports it natively. unless you see yourself storing terrabytes of files that‘s definitely the most pain free variant

peak gale
unique quail
peak gale
#

no nvm

#

i just had to restart visual studio

patent quarry
torn sphinx
#

why is a large file impossible to backup?

fading patrol
#

Whatever you're trying to do, it sounds like you're doing it wrong... But any key-value store will let you do that as long as the keys are all unique

#

No, but there's SQLite

shut tiger
torn sphinx
#

I think they are looking for a database service provider.

coral wasp
patent quarry
coral wasp
patent quarry
coral wasp
#

Have you looked at partitioning? What storage engine?

#

(Nowadays, I push as much as possible to parquet and duckdb when the use case allows, but I abused MySQL for many years)

#

For backups, you can do incrementals and do full backups less frequently. I've never replicated MySQL, but in past lives I'd backup off a replica (that was Oracle tho)

#

Also with sql server, we'd use filesystem snapshots and then backup off the snapshot. In AWS, could snap the EBS volume.

coral wasp
patent quarry
#

Using innodb. I looked a little bit into partitioning but it was more from a performance point of view. I haven't really thought about it as being part of a backup strategy.

coral wasp
#

Partitions aren't technically needed for the backup case, it just fits well with the strategy. You can always backups/dump data in ranges without partitions.

lethal bronze
#

I have to work with a postgres database that only supports bigint, however all my unique values are stored as sha256 hashes, which are naturally materially larger by quite a factor - whats the best hash function that returns in bigint format?

shut tiger
lethal bronze
#

Its a pretty odd system I only have 1 column to work with and that 1 column only supports bigint

#

or uuid but that doesnt help much

west hill
#

what are these unique values for?

lethal bronze
#

The database is structured with incremental id, the problem is I often need to go back and update individual elements

#

and incremental id is sketchy for that

west hill
#

why not generate uuids for primary keys if its readily available?

#

uuid is perfect for this

lethal bronze
#

two primary reasons 1) I have to delete and recreate the database as it is incremental ID atm, and it cant be changed to UUID; 2) All my data in my other tables identified using sha256 hashes

west hill
#

ah rip then ur best bet is to do as boxed says i think u can store the hashes in TEXT columns

#

or varchar

lethal bronze
#

Yeah that would have been my preferred solution as well, but I only have 2 columns to work with - ID (bigint or for new tables UUID) and json column (where all data is stored as a json object)

#

so I cant just create a new column make it text and use that as my effective unique key column

#

so my thinking is to take my sha256 and then hash that into a format that is bigin compatible

#

collisisions should still be extremely extremely rare

#

or I mean I could slice out the last digits of my sha256 hash, but that feels so hacky

west hill
#

perhaps using hashlib then using the hexdigest to get the hex number and finally convert it to int like this? int(digest, base=16)

lethal bronze
#

Yeah exactly

#

def f_sha256_to_8_byte(f_sha256_input_value):
return int(f_sha256_input_value, 16) % 10**8

#

it just hurts my eyes to effectively hack off the rest of my hash

#

just calculated risk of collision, with 10 million records the probability of one overlap stands at 5e-06

#

so should be alright

#

hmm its still 5% at 1 bil records tho

#

Okay minor mistake in the above it should not be mod 10**8, but something substantially higher

west hill
#

8 bytes iirc bigint is

lethal bronze
#

Yeah should be 2**63

#

def f_sha256_to_8_byte(f_sha256_input_value):
return int(f_sha256_input_value, 16) % 2**63

#

I could technically double it because it also uses negative values

#

but meh

west hill
#

roughly 10**19 i think

# 
BIGINT    8 bytes    -9,223,372,036,854,775,808    +9,223,372,036,854,775,807
#

!e

import math

x, y = (-9_223_372_036_854_775_808, 9_223_372_036_854_775_807)
z = y - x
print(math.log(z, 10))
delicate fieldBOT
#

@west hill :white_check_mark: Your 3.12 eval job has completed with return code 0.

19.265919722494793
west hill
#

or 2**64

grim vault
west hill
#

yeah

lethal bronze
#

True I will only be using positives though, so that would make it 63 no?

sleek bridge
#

Can I ask questions about pandas to study in this chat or will I be banned if I do that?

wooden parrot
#

Hi, I need a help with Mongodb. When I am updating data in db for a user I want that a particular field value should not be exceed 100, How to do that.

await cllection.update_one({"user_id": 111}, {"$inc": {'data': 30}}, upsert= True)   # now i want that this data value should not be exceed 100, From begining if data value was 80 it should become 100 not 110.
west hill
#

instead of increase set the value using something like this min(original + increment, 100)

wooden parrot
west hill
#

try this

slender atlas
wooden parrot
west hill
#

u just need to do min with 100 as an argument

wooden parrot
wooden parrot
west hill
#

using inc + min is probably faster than fetching the data i think

wooden parrot
native zinc
#

Let me get this straight...

So SQLAlchemy functions typically depend on the session object.

But that session object requires an engine that has been bound in some way to the Base.metadata.create_all(engine) method...

How on earth are you supposed to manage all of that, make sure that your engine has touched the Base.metadata.create_all() method, and that every session you're using has been created from that engine when you're designing your units of work?

scarlet zephyr
#

I don’t think you need the students.[column_name] at the start as you specified from Students. I’d recommend calling the table tblStudents, but it’s up to you

Is the screenshot your entire database or are there some columns missing? Just looks like you’re requesting stuff that doesn’t exist

#

Ah ok cool let me have a Quick Look again

#

What does the left join bit mean, I’ve not seen that before?

#

Ah cool cool

#

Ah

#

Your problem is you’ve only asked for two courses

#

It’s getting the FirstName, MiddleName, LastName, Description, and two names

#

I think that’s why

#

Potentially

#

Oh okok

#

So having Name1 + Name2 in one column? Is that what you mean

#

Oh ok. The only way I can think of doing that is with python but you mentioned you’re not able to do that.
I am not 100% sure if there’s a way to do it as one field within SQL 🤔

#

That’s alr no problem. Good luck with finding your solution ☺️

upper sapphire
#

The current function adds numbers between 0-9 into a queue named q
She gets a number that goes into x aswell
this function gets a number and checks if it’s in the queue then returns true if it is and false if not.
Can I get help with filling it?

IMG_0383.png
shut tiger
peak gale
#

how do i write a program that: when clicking a button it generates something from a determined list?

shut tiger
peak gale
shut tiger
peak gale
#

ok i have another question

#

if i want to put an image on tkinter

#

but i want it to be displayed as various symbols like (-,*,@)

#

how

shut tiger
# peak gale how

This is the wrong channel. Your question is not related to databases.

brisk stump
paper flower
#

And it wouldn't be used in production environment, you'd use a migration tool, typically alembic

native zinc
#

But when I wasn't using it I kept getting an error saying that the models didn't exist when I was testing 🤔

paper flower
#

Well, yes, because table didn't exist

native zinc
#

Ohhhhh

#

Wait

#

Maybe its because it was a memory database that wasn't instantiated yet

#

When testing...

#

Potentially?

paper flower
#

I don't know how you set it up so I can't tell

native zinc
#

Just wondering how you would make it work without using Base.metadata.create_all 🤔

#

The engine has to know at some point the models exist right?

paper flower
#

You have to create tables in your DB somehow

native zinc
#

Ah wait

paper flower
#

Otherwise how would you expect it to work?

native zinc
#

I'll make a little repo

#

I just always thought it needed the base.metadata.create_all to work 😅

paper flower
#

create_tables is only really useful for development or testing, otherwise you'd want to use alembic

#

No, if model in your application is compatible with table in your db it will just work

#

Sqlalchemy just generates sql and sends it to the server, if it can be executed you'll get the result

native zinc
#

I've been misunderstanding it this whole time then

#

rip

#

But ok nbd

#

Not much code to change

paper flower
#

inserts may fail depending on if email is nullable or if it has a default value

maiden pebble
#

does anyone know how to make a database to let only a few commands be used in a specific channel ?

fading patrol
maiden pebble
#

Someone said that i need a database for that

fresh stratus
#

!pastbin

#

!bin

#

!paste

delicate fieldBOT
#
Pasting large amounts of code

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

After pasting your code, save it by clicking the Paste! button in the bottom left, or by pressing CTRL + S. After doing that, you will be navigated to the new paste's page. Copy the URL and post it here so others can see it.

fading patrol
native zinc
#

Migrations aren't something you should include in a package. They're something you should include in a project / script that uses a Python package.

The database is sort of a separate thing.

You can define models in a package but you don't necessarily have to include all the migrations etc. for it, or even abstract the session creation part or the Base.metadata.create_all() piece.

#

If you need the database models you can just work with those in a package, and just use the other details on the scripts where you actually run the code and connect everything with the db_uri 🫠

#

makes sense

#

At least, I think that's how that works 🤔

#

Maybe it still makes sense to keep migrations in a package.

#

Just if you put the migrations in a package, and you pip install that package, how are you supposed to use alembic to migrate it inside the new project? 😅 Now it doesn't make sense.

fading patrol
native zinc
#

I'm not using flask though

#

just sqlalchemy and alemibc

native zinc
boreal wren
#

dont know if this is the right channel, but I am encountering an issue when using Python to refresh my SQL queries in Excel. I want to create a button in Excel that runs a pyhton script. This python script will firstly refresh all data in excel and should then do some additonal steps. However, for some reasons it will not load all the data before starting to do the next steps. The sleep function does not work as it still doesnt refresh the data. any suggestions to avoid this issue?

fading patrol
fading patrol
shut tiger
boreal wren
# fading patrol Show your code. <#704250143020417084>

the python code below. the code in excel to click the button is attached as picture.

import xlwings as xw
from datetime import datetime
from time import sleep
import win32com

def SQLOpdater():

app = xw.apps.active
wb = app.books.active
wb.api.RefreshAll()
    
sheet = wb.sheets['Afgrænsninger']

today_date = datetime.now().date()
sheet.range("C26").value = today_date

value = sheet.range("C7").value
sheet.range("C27").value = value

def RunQueryAndAppendResult():
app = xw.apps.active
wb = app.books.active

ws = wb.sheets["Afgrænsninger"]

last_row = ws.cells(10, "K").end("up").row

for i in range(3, last_row + 1):
    if ws.cells(i, "K").value is not None:
        ws.range("C4").value = ws.cells(i, "K").value
        ws.range("C5").value = ws.cells(i, "L").value
        ws.range("C7").value = ws.cells(i, "M").value
        ws.range("C9").value = ws.cells(i, "N").value
        ws.range("C11").value = ws.cells(i, "O").value
        ws.range("C12").value = ws.cells(i, "P").value
        ws.range("C14").value = ws.cells(i, "Q").value
        ws.range("G3").value = ws.cells(i, "R").value
        
        SQLOpdater()
        
        for table in wb.sheets['Finans Udtræk'].api.ListObjects:
            while table.QueryTable.Refreshing:
                sleep(1)
                pass
        
        AppendToFinalTab(i)

    else:
        # Exit the loop if a blank cell is encountered
        break
image.png
maiden pebble
#

when i put this code it it should create a new database file right ? conn = sqlite3.connect('All_agents.db')

wraith shell
#

Hi Everyone, has anyone achieved running an Oracle DB container natively on ARM (apple silicon specifically) without Colima as a runtime replacement?

maiden pebble
waxen finch
#

in this case, do you have data that needs to change dynamically? e.g. does the server admin need a way to allow/disallow the command in different channels?

#

or do you already know, at the time of writing your python script, which channels you want to allow your commands in?

#

if you don't expect the channels to change then it would be reasonable to hardcode the allowed channels into your script, saving you the time and effort

native zinc
#

Alembic feels really annoying to use 😅 How do you guys point your alembic file to different database urls? Like if you have a database url for production and development, how would you separate them and make the migrations with alembic?

waxen finch
potent spire
paper flower
native zinc
#

Gotcha, also just found a programmatic way to do it with the alembic config and alembic command (surprisingly thanks to Google Gemini I'll add haha).

Now the only thing is,

I created a new database (simply sqlite3) with the most recent Base.metadata.create_all(engine) since Alembic doesn't create databases from scratch I guess (?) and I'm getting Key Errors when I try to upgrade head OR errors saying this database isn't up to date with the latest version... which is true... when I try to make a new revision because I'm trying to create a new database lol.

So it looks like I'm missing something 🤔 maybe I should just make a new empty database first without any tables then let alembic upgrade it from scratch?

#

Trying stuff

#

I'm a bit confused why it wouldn't work. In Django you literally just have to migrate and it both creates a new db and applies all the history to it.

With alembic it's so... I mean I kind of hate it to be honest lmao. It's just the only option for SQLAlchemy, which is kind of the only option for ORMs outside of Django, primarily because it's the only one with a somewhat existing automigration system alembic, which ironically is pretty manual 😅 but not as manual as writing your own migrations.

ionic pecan
#

alembic is pretty good once you have it set up

#

can you share the key error

maiden pebble
# waxen finch or do you already know, at the time of writing your python script, which channel...

I already know while writing python code what channels i want to allow certain commands in. Let me explain what i mean:
I want to make multiple commands like !cypher_ascend_a and !cypher_ascend_ b. I want to make it that those 2 commands (i want to add more commands) can only be send in a channel named “Cypher” and i want to block all the other channels to that command. So eventually i will have multiple commands like those 2 and than that j can choose in my code what command can be send in witch channel. I asked it in #discord-bots but someone said i need an database for that so thats why im in this channel (Let me know if you understand what i mean otherwise i explain it a different way)

waxen finch
#

ill write an answer in the discord bots channel momentarily

maiden pebble
shut tiger
native zinc
# shut tiger Why not use Django then?

It's a webscraper, not really a website. I know there's a way to make it a Django project and just use the ORM, but it seems like a lot just for the ORM and all the extra setup.

shut tiger
native zinc
#

I want to use alembic and SQLAlchemy it's just hard for me to understand haha

native zinc
shut tiger
native zinc
#

Then I definitely misunderstood something haha

shut tiger
#

Lots of people are super confused about django... I hear so much nonsense all the time

native zinc
#

I remember you from the Django group haha

#

Nice 😎

shut tiger
#

"It forces your code to adapt to the framework" is the most common. Absolute nonsense. You have to put models in models.py (or import them into models.py). That's it. Everything else is just culture.

paper moon
slate fable
#

What is the best PostgreSQL driver for Python?

brazen charm
#

asyncpg or psycopg3

slate fable
earnest bane
frosty sinew
#

In SSRS. I have columns grouped together by company name but for some reason I can't get the title to appear ABOVE. it only appears to the side. How do i get it to appear above and take up less space. I'm really just trying to fit everything on one page

paper moon
#

Anyone can give me a hand?

image.png image.png
rough hearth
paper moon
#

@rough hearthhow do i send code to discord btw

rough hearth
delicate fieldBOT
#
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.

paper moon
#

ok thx

dapper jacinth
#

'''
print('hello, world! ')
'''

# 
print(‘hello, world!’)
#

IT ACTUALLY WORKS

dapper jacinth
storm mauve
#

!src

delicate fieldBOT
storm mauve
#

you can see the source code using the link above, but that is pretty offtopic for this channel - if you are just testing random stuff please use #bot-commands

fluid stump
# 
import aiosqlite
import tkinter as tk
import threading

class DatabaseUser:
    def __init__(self):
        self.conn = None 

    async def get_connection(self):
       
        if self.conn is None:
            self.conn = await aiosqlite.connect("nation.db")
            await self.create_table()
        return self.conn

    async def create_table(self):
        conn = await self.get_connection()
        async with conn:
            cursor = await conn.cursor()
            await cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                                    user_id INTEGER PRIMARY KEY,
                                    nation_id TEXT
                                  )''')
            await conn.commit()

    async def add_user_nation(self, user_id, nation_id):
        conn = await self.get_connection()
        async with conn:
            cursor = await conn.cursor()
            await cursor.execute("INSERT INTO users (user_id, nation_id) VALUES (?, ?)", (user_id, nation_id))
            await conn.commit()

    async def is_user_registered(self, user_id):
        conn = await self.get_connection()
        async with conn:
            cursor = await conn.cursor()
            await cursor.execute("SELECT * FROM users WHERE user_id=?", (user_id,))
            result = await cursor.fetchone()
            return result is not None

    async def remove_user(self, user_id):
        conn = await self.get_connection()
        async with conn:
            cursor = await conn.cursor()
            await cursor.execute("DELETE FROM users WHERE user_id=?", (user_id,))
            await conn.commit()
# 
async def get_user_nation_id(self, user_id):
        conn = await self.get_connection()
        async with conn:
            cursor = await conn.cursor()
            await cursor.execute("SELECT nation_id FROM users WHERE user_id=?", (user_id,))
            result = await cursor.fetchone()
            return result[0] if result else None```
#

An error occurred: threads can only be started once

fluid stump
rustic yew
#

Hi is there any free cloud database for mobile app development using python?

shut tiger
fluid stump
shut tiger
fluid stump
shut tiger
fluid stump
shut tiger
fluid stump
#

I got the answer and it got fixed. Thank you for your support

shut tiger
fluid stump
shut tiger
fluid stump
#

I had to remove all of them

shut tiger
#

Hmm.. you wrote that much code without testing the parts firsts?

fluid stump
pure eagle
#

hey guys. python newbie here. need some help with above

final totem
# 
class ORMFlops(Base):
    __tablename__ = 'Flops'
    id: Mapped[String] = mapped_column(primary_key=True, default=uuid.uuid4)
    cardoneid: Mapped[String] = mapped_column( ForeignKey('PossibleCards.id'))
    cardtwoid: Mapped[String] = mapped_column( ForeignKey('PossibleCards.id'))
    cardthreeid: Mapped[String] = mapped_column(ForeignKey('PossibleCards.id'))
    handid: Mapped[String] = mapped_column(ForeignKey('Hands.id'),index=True)

    # relationships
    # how can i populate cardone by cardoneid cardtwo by cardtwoid 
    # and cardthree by cardthreeid from PossibleCards?
    cardone:Mapped["ORMPossibleCards"] = relationship()
    cardtwo:Mapped["ORMPossibleCards"] = relationship()
    cardthree:Mapped["ORMPossibleCards"] = relationship()
#

how can i properly define relationship of each card to map to a row by the matching foreign key?

#

the code is using sqlalchemy lib

final totem
#

would appreciate your help , I find the documentation thin

wise wind
paper flower
solar summit
prime acorn
#

heyo, i got a question. whats the difference between mySQL and postgresql. im currently using postgres but dont know if i should switch to mySQL. my use case would be for a single server bot of 250 members with moderate growth and a leveling system

thorny anchor
#

mysql is simpler and has less features, postgres is more complex. they implement many of the same features, though

prime acorn
#

and does mySQL need a database to host like postgres?

waxen finch
#

for a single guild bot expected to serve a few hundred members, i imagine any relational database would be sufficient for your use case

last time i did a sqlite benchmark with 10 concurrent connections, it got CPU-bound and only managed like 140 select queries per second which was much lower than i expected, but that was still 140/s, or 12 million if you stretch it to a day, and when i switched to a single connection it managed ~2000/1s - so how much traffic do you expect, in terms of concurrent users and queries? doing some napkin math, maybe you run 3 queries on every message and you get 1000 messages daily, so thats 3000 queries daily / 1 query every ~30s, plus however many you get from users running your bot's commands

in other words, databases are really fast, so "scaling" in this context might mean going to hundreds or thousands of queries a second, which sounds like way more than you'll need for the situation you've described

thorny anchor
prime acorn
brazen charm
#

I am generally very skeptical of any "free" SAAS

#

because they are never truly "free" or without some serious catches

#

for the sake of running one local postgres or sqlite instance...

woeful coral
#

good?

# 
cursor.execute('''CREATE TABLE IF NOT EXISTS config (user_id BIGINT PRIMARY KEY, guild_name INTEGER, channel_name INTEGER, role_name INTEGER, message_content INTEGER, invite_content INTEGER)''')```
harsh pulsar
kindred nova
#

I am using PostgreSQL database with asyncpg.
How do I get the id of that column? the column id is auto increment.

result = await db.execute("INSERT INTO table(user_id) VALUES($1) RETURNING id", ...)
print(result)```
it prints `INSERT 0 1`
shut tiger
kindred nova
#

actually i need to use db.fetchval()

shut tiger
kindred nova
shut tiger
trail oriole
waxen finch
# kindred nova I am using PostgreSQL database with `asyncpg`. How do I get the `id` of that col...

fyi what you printed there is supposedly called a command status or tag, though i couldnt find more generic info about it in docs
https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.execute

Return str: Status of the last SQL command.
https://www.postgresql.org/docs/16/sql-insert.html
On successful completion, an INSERT command returns a command tag of the form

INSERT oid count
``` The count is the number of rows inserted or updated. oid is always 0 ...
slate fable
#

What is the best SQLite async driver for Python3 using asyncio?

paper flower
waxen finch
#

https://github.com/Rapptz/asqlite
i like asqlite's implementation and their convenient/ideal defaults, but it's not well known, has no test suite, must be installed with pip install git+https://..., and, if you're planning to use an ORM, sqlalchemy only supports aiosqlite

slate fable
#

And to write on filesystem, like as to write images (jpeg) in asyncronous wasy, are there any lib to write with Python using asyncio?

misty jackal
waxen finch
rotund pumice
#

I have a doubt.
I have a program that must always be turned on.
It connects to the database and then leaves the connection open and whenever you want to do a select and a commit you don't need to open the connection again.
However, this creates a problem, if you lose connection to WiFi, the code understands that it is the database that is not responding and is forever waiting for a response.
I'm using this connection in a try/Exception, but the program does not generate an exception.
How do I generate an exception if I don't have internet access?
I'm using mysql-connector-python==8.3.0

hexed estuary
#

Database libraries typically have an option for the connection timeout

rotund pumice
#

Yes, but this timeout only works when connecting to the database and not if the connection has already been opened.

shut tiger
rotund pumice
shut tiger
delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied timeout to @real raptor until <t:1715085858:f> (10 minutes) (reason: duplicates spam - sent 5 duplicate messages).

The <@&831776746206265384> have been alerted for review.

regal moss
#

I'm going through a difficult time, can anyone help me (conceptually)

#

I need to process a folder that has several csv files and generate 3 other files (also csv)

#

The problem is that the files are of varying sizes (some very large)

#

currently I try to treat the files by dask dataframe

#

build the code that accesses the files and generates dataframe but when exporting it keeps getting a memory error

shut tiger
#

I guess they are related in some way? Meaning you need to use data from them all at the same time?

regal moss
#

Has anyone ever gone through something similar?

regal moss
shut tiger
storm mauve
#

generally speaking you have to either do things in chunks or via streaming when it becomes larger than your memory

just concatenating things do not require having any more than a single line in memory at a time though, even pure python can do that with no problems if you don't needlessly collect all things into memory

regal moss
storm mauve
#

JOIN or CONCATENATE?

regal moss
storm mauve
#

literally ```py
for file in files:
for line in file:
output_file.write(line)

shut tiger
#

You can open files in append mode. You can open them memory mapped. There are many ways.

storm mauve
#

not sure about how it works in dask

with pandas you would have to manually work in chunks since it does not have a lazy api

with polars it would also be trivial, scan_csv -> concat -> sink_csv

paper flower
#

Repeat for each file 🤔

#

You don't have to keep everything in memory if you just want to concatenate them

regal moss
#

you gave me good ideas

#

thank you!

small moon
#

Hey, do you guys have any tips on how to look for weird data/value in our database that would cause slowness in connected applications? (MySQL)
For example: app executing queries is slower than directly executing app's queries in the database terminal. I'm not sure if it's because database caching is available for either case or not, but I'm 100% sure that the problem is with the weird data in our database, because I'm able to reproduce the problem in another instance.

velvet ridge
#

or would it make more sense to try something else, maybe trying to initialize the account in app startup event?

#

also, what about the SQL queries in general, should something be changed/done differently?

slate fable
#

Using the asyncpg/aiosqlite for non blocking use, is the same as to create a second thread and use a normal (blocking) postgresql/sqlite driver?

fading patrol
small moon
hollow oasis
#

can someone explain me what is sneak peek in sql database?

shut tiger
hollow oasis
shut tiger
hollow oasis
#

he gave me this code only:

        # Method to fill sneak peek number 1 data
        pass
#

the database is mysql

shut tiger
#

I think you should ask him. This is not a database thing.

hollow oasis
fading patrol
small moon
thorny anchor
#

it doesn't seem feasible to me that it's caused by only the data. if the query in the app is using parameterized queries, then the database can't optimize for the specific values of the parameters. but when you run the query directly, the DB does have access, and can optimize the query better. I would check the query plan to see if they are what i expect.

visual bane
#

In my program I'm attempting to use sqlite3 database but my program isn't doing what I want it to do. I've deeply analysed my programming and nothing is wrong with my code, but there is something wrong with the database, because when I click on the database file, at the top it says: "this file was loaded in wrong encoding utf-8 ". How do I fix this? (I use pycharm Community edition)

half gyro
half gyro
spare jolt
visual bane
visual bane
# half gyro or, there is something wrong with clicking it to open it. What application says...

import sqlite3

def find_user_by_name_and_pin(name, pin):
try:
# Establish a connection to the database
connection = sqlite3.connect('data.db')
cursor = connection.cursor()

    # Execute the query
    cursor.execute('SELECT name, pin FROM users')
    user_name_and_pin = [{'name': row[0], 'PIN': row[1]} for row in cursor.fetchall()]

    # Search for the user
    for user in user_name_and_pin:
        if user['name'] == name and user['PIN'] == pin:
            print('User found!')
            return user

    # If no user is found
    print(f'User with name "{name}" and PIN "{pin}" not found.')
    return None

except sqlite3.Error as e:
    print(f"Error accessing the database: {e}")
    return None

finally:
    # Close the database connection
    connection.close()

And this the file where I try to compare the arguments I receive from the user and compare them to all the usernames and passwords in order to find the user

half gyro
visual bane
#

What do you mean by application?

half gyro
visual bane
#

So after the database was created, I clicked on the database file.
On the now opened database file, at the top of the file (as a message) the message said this file was loaded in wrong encoding utf-8.

#

I'll send a picture

spare jolt
visual bane
#

I searched online if when the message "this file was loaded in wrong encoding utf-8" comes it can give you errors... is this true?

#

If not, please can you look at my code and see if there are any problems

half gyro
visual bane
visual bane
half gyro
half gyro
visual bane
visual bane
distant patrol
#

Agree it seems fine, that photo looks about like what you see when you open a sqlite db somewhere you aren't supposed to 😛

obsidian basin
#

Hey I am using dbeaver and I am using a sqlalchemy db. To connect to the db I use sqlite. When I try searching a column in the username in dbeaver I get the error below. Everything works in the code why am I getting the error in dbeaver?

Here is the error in dbeaver.
https://paste.pythondiscord.com/YO5A

#

I just want to add I view the the specific record piciho8411@ociun.com in the the user table but I cannot search it.

leaden bough
#

hi guys, do u know SAS programming language? there's a job with it in the description, I know c#, SQl, javascript, and Python primarily...does it work similar to one of those? which one? and how fast could i learn enough to mention it in my resume?..... what r your thoughts?

wise goblet
# leaden bough hi guys, do u know SAS programming language? there's a job with it in the descri...

Out of curiosity googled it.
SAS looks like Fortran/Cobol to me in terms of syntax.

Supposedly data management language for data scientists and some data engineers accordingly.

I have some questions regarding how much it is outdated and used.

https://www.reddit.com/r/datascience/comments/j2x2wp/sas_is_easily_one_of_the_worst_languages_i_have/
This thread is useful full of opinions on it.

I will summarize it as tech from 30 years into past, that has some usages among non developers (language for mathematicians) for its a lot of GUI drag and drop stuff.

Reddit
From the datascience community on Reddit

Explore this post and more from the datascience community

#

Tldr, prehistoric alternative to R/python looks like

wise goblet
#

as funny argument, if u will learn it, then u will posses negotiating skills similar to having learned COBOL i guess

wise goblet
leaden bough
wise goblet
#

shrugs. For some companies COBOL/FORTRAN/SAS can be indeed a thing they long used

#

because they are probably over 50-60 years old in average and that is a thing they got used to

#

like... u can hit every nail with a tool you got overly comfortable

#

they were not lucky to get comfortable to this, or it was a right decision at a moment of their time, or they learned it because of succesful SAS propaganda and or they had many ||ancient|| mathematicians in their ranks in general.

#

Considering that it is closed source ancient language of mathematicians with such... history. Tbh nothing comes to my mind for real reason except succesful subterfuge propaganda among mathematicians in a very long play (it was first released in 1972 year after all)

#

funny enough developed and owned by company in US as well. So it is a very close brother to COBOL.

shut tiger
shut tiger
native zinc
#

I'm finally less dumb about SQL now 🙂

image.png
subtle sierra
#

Database triggers

So basically in my application i have to inc or dec a few values in a few tables on creation / update / deletion of rows in some other table

Currently using sqlalchemy i query all required rows modify them and commit to database these are never used again, only queried to modify 1 column of said row

So i came across database triggers on postgres, i think with them this can be done faster and simpler

Am i in the right direction ? Does this sound like a good way to achieve my requirements ?

shut tiger
subtle sierra
#

Yeah instead of querying i could just execute a update statement on the database in sqlalchemy, now I'm not sure which way sounds better

#

If i do that in backend it's a bit simpler and explicit easy to understand how the value gets updated

But with triggers it's abstracted to the database side, might be quicker and backend code becomes simpler

shut tiger
subtle sierra
paper flower
#

update ... set field = field + 1 wouldn't cause any race conditions

shut tiger
#

I think if you DO have triggers, you want them written in your code and have code on deploy that makes sure the triggers that exists are exactly matching those in the code.

thorny anchor
#

with sqlalchemy, i believe you can define triggers in your code. i agree it's easy to get them out of sync (along with stored procs, rules, and so on), so you need to do some effort to keep them in sync

inner mulch
#

hey guys anyone knows how to work with couch base

tulip stump
#

Hello guys, little question here. In college while doing Databases I they told us that relational databases shouldn't have "lists" in a column.
I'm trying to do a very simple library database. I have topics, and authors. Each book can have more than one topic and more than one author and visceversa.

Should I make 2 different tables for the relation topic - book and author - book?

I was just wondering if this is generally considered "the best way". I'm not particularily concerned about speed or storage since it's gonna be a relatively small database but still it would be good to do something good.

#

I was thinking maybe I have a table for topic-book relationships and then I also have one "list" inside the books so that I can quickly fetch the topics from that book? That way if I want to quickly see all the information about a book I don't have to do another look up in that other table and if I want to search by topics I don't have to look inside every single book to see if they have that topic.

waxen finch
#

one association table with a compound primary key and an index with the keys in reverse should be enough to get efficient lookups for book->topics and topic-books while satisfying 1st normal form

tulip stump
waxen finch
#

CREATE INDEX ix_name ON table (col1, col2, ...); is the general syntax for it

tulip stump
#

I see, thank you very much :)

paper flower
#

If the column doesn't depend on any other piece of your database and nothing depends on it then it can be anything, e.g. json

tulip stump
#

Alright, I have the following query now:

SELECT book.isbn, book.title, book.place, book.publisher, book.dateIssued,
        book.edition, book.abstract, book.description, book.ddcClass,
    GROUP_CONCAT(DISTINCT author.name) AS authors,
    GROUP_CONCAT(DISTINCT topic.topicName) AS topics
FROM book
    LEFT JOIN bookAuthor ON book.isbn = bookAuthor.isbn
    LEFT JOIN author ON bookAuthor.authorName = author.name
    LEFT JOIN bookTopic ON book.isbn = bookTopic.isbn
    LEFT JOIN topic ON bookTopic.topic = topic.topicName
WHERE book.isbn = ?
GROUP BY book.isbn, book.title, book.place, book.publisher, book.dateIssued, book.edition;

Which returns authors as a single string, for example: "Bioy Casares, Adolfo,Borges, Jorge Luis,Ocampo, Silvina"
As you can see it's pretty hard to parse (authors are ["Bioy Casares, Adolfo" , "Borges, Jorge Luis", "Ocampo, Silvina"])

I could try to use a separator to make it easier to parse, but GROUP_CONCAT(DISTINCT author.name, "|") is not supported. I could try using subqueries like so:

SELECT book.isbn, book.title, book.place, book.publisher, book.dateIssued, book.edition,
       (SELECT GROUP_CONCAT(author.name, '|') FROM (SELECT DISTINCT author.name FROM author INNER JOIN bookAuthor ON author.name = bookAuthor.authorName WHERE bookAuthor.isbn = book.isbn)) AS authors,
       (SELECT GROUP_CONCAT(topic.topicName, '|') FROM (SELECT DISTINCT topic.topicName FROM topic INNER JOIN bookTopic ON topic.topicName = bookTopic.topic WHERE bookTopic.isbn = book.isbn)) AS topics
FROM book
WHERE book.isbn = '?';

But I think subqueries are less performant than joins? I want to retrieve the authors and topic strings and parse them to get a list of authors and topics. What would be the best way of doing this? (is using GROUP_CONCAT even advisable?)

#

(bookAuthor and bookTopic are indexed as was suggested earlier)

grim vault
#

Why would you need DISTINCT in the concat, the link should be unique already? And if you select the same column you are using to link with another table, you do not need the other table at all.

SELECT book.isbn, book.title, book.place, book.publisher, book.dateIssued,
       book.edition, book.abstract, book.description, book.ddcClass,
    GROUP_CONCAT(bookAuthor.authorName, '|') AS authors,
    GROUP_CONCAT(bookTopic.topic, '|') AS topics
FROM book
    LEFT JOIN bookAuthor ON book.isbn = bookAuthor.isbn
    LEFT JOIN bookTopic ON book.isbn = bookTopic.isbn
WHERE book.isbn = ?
GROUP BY book.isbn, book.title, book.place, book.publisher, book.dateIssued,
         book.edition, book.abstract, book.description, book.ddcClass;
tulip stump
# grim vault Why would you need `DISTINCT` in the concat, the link should be unique already? ...

the link is not unique:

('9789875662445', 'Antología de la literatura fantástica', 'Buenos Aires', 'Debolsillo', '2014', '9na. ed.', None, '407 p. : il.', 'A863 732ant', 'Bioy Casares, Adolfo|Bioy Casares, Adolfo|Bioy Casares, Adolfo|Bioy Casares, Adolfo|Bioy Casares, Adolfo|Borges, Jorge Luis|Borges, Jorge Luis|Borges, Jorge Luis|Borges, Jorge Luis|Borges, Jorge Luis|Ocampo, Silvina|Ocampo, Silvina|Ocampo, Silvina|Ocampo, Silvina|Ocampo, Silvina', 'ARGENTINA|CUENTOS|DOCUMENTOS TEORICOS O METODOLOGICOS|LITERATURA|LITERATURA ARGENTINA|ARGENTINA|CUENTOS|DOCUMENTOS TEORICOS O METODOLOGICOS|LITERATURA|LITERATURA ARGENTINA|ARGENTINA|CUENTOS|DOCUMENTOS TEORICOS O METODOLOGICOS|LITERATURA|LITERATURA ARGENTINA')

The separator is working correctly but it's very clear that authors and topics repeat multiple times :/

#

although it's true that I was using redundant LEFT JOINs

grim vault
#

What database is this? And the join should be unique. Why is the same author joined multiple times with the book?

tulip stump
#

sqlite

tulip stump
# grim vault What database is this? And the join should be unique. Why is the same author joi...

also this is what I get from this query:

sqlite> SELECT * FROM bookAuthor;
9789875662445|Borges, Jorge Luis
9789875662445|Bioy Casares, Adolfo
9789875662445|Ocampo, Silvina
9789875780620|Cortázar, Julio
9500420457|Borges, Jorge Luis
9879243501|Muslip, Eduardo
043527032X|Dickens, Charles
043527032X|Tarner, Margaret
043527032X|Wilson, Kay Mary
9500718227|Benedetti, Mario

Doesn't look like the same author is repeated in the same book, so it's not a problem with bookAuthor having repeated entries or anything