#databases

1 messages · Page 27 of 1

exotic drift
#

are you talking about this??

Screenshot_2024-01-19_at_17.26.54.png
exotic mantle
#

In SQLAlchemy, how would I go about creating multiple foreign key relationships between the same tables? For example, I currently have this:

class Question(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    asked_by: Mapped[int] = mapped_column(ForeignKey("user.id"))


class User(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    questions_asked: Mapped[list["Question"]] = relationship()
```But I'd like to have multiple, like this:```py
class Question(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    asked_by: Mapped[int] = mapped_column(ForeignKey("user.id"))
    answered_by: Mapped[int] = mapped_column(ForeignKey("user.id"))


class User(Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    questions_asked: Mapped[list["Question"]] = relationship()
    questions_answered: Mapped[list["Question"]] = relationship()
```However, I'm not sure how I'd accomplish that
waxen finch
exotic mantle
#

sqlalchemy is really nice, but i find the documentation hard to read, probably because it's so large

waxen finch
#

i think the docs are quite decent for it being a very complex library, but i barely remember how to use it since i havent had to touch my sqlalchemy code in a while

obtuse berry
#

Can you change the data type of a field across all documents in MongoDB?

craggy swallow
craggy swallow
fading patrol
waxen finch
craggy swallow
waxen finch
# craggy swallow Huh.. indeed. I'm guessing instead of PK of (value_id, member_id) and FK on memb...

that was my first thought too, but is it possible to have multiple foreign key constraints apply to the same column? alongside that, i would remove the value table so the key_id can be included in both variable tables, perhaps changing their name to guild_value/member_value, and then you can additionally enforce that the value's guild ID matches the key's guild ID: ```sql
CREATE TABLE guild_value (
key_id BIGINT,
guild_id BIGINT,
data JSONB NOT NULL,
PRIMARY KEY (key_id, guild_id),
FOREIGN KEY (key_id, guild_id) REFERENCES key (id, guild_id)
);

CREATE TABLE member_value (
key_id BIGINT,
guild_id BIGINT,
member_id BIGINT,
data JSONB NOT NULL,
PRIMARY KEY (key_id, guild_id, member_id),
FOREIGN KEY (key_id, guild_id) REFERENCES key (id, guild_id),
FOREIGN KEY (guild_id, member_id) REFERENCES member (guild_id, id)
);``` though in this case you'd probably need key's primary key to be (id, guild_id)

#

sidenote, if you find it necessary to store rows for a user irrespective of a guild, it might be better to rename member.id to member.user_id to avoid confusion between member_id and user_id, which are two different names for the same kind of ID

craggy swallow
#

Also, isn't it guaranteed that the value will correspond to the correct guild as is, since the value table points to the key table, which points to the guild table?

waxen finch
waxen finch
craggy swallow
craggy swallow
#

so a value can have only 1 key which has only 1 guild?, and that value can then belong to any guild/member, having come from a key, which came from a guild?

waxen finch
craggy swallow
waxen finch
#

oh wait it'll already be easier to de-duplicate after moving that key_id column, in which case having a unique index on data would be all you need

waxen finch
craggy swallow
craggy swallow
waxen finch
waxen finch
craggy swallow
#

And shouldn't the guild_variable table also reference the guild table?

waxen finch
#

the concept of a member in discord's API is a user that is in a guild, so it makes more sense to make member have both IDs rather than to have a separate association table just for it

#

oh hold on, there are issues with using nulls in primary keys/unique indices and you need to support global keys alongside guild-specific keys, right?

craggy swallow
craggy swallow
#

does this look to be on the right track? (ignore the disconnected tables on the right)

schma.png
#

it's pretty late and i'm only getting more confused

waxen finch
craggy swallow
waxen finch
craggy swallow
waxen finch
# craggy swallow from the point of modeling a database, how does this affect things? I mean, the ...

well, what does the member's name, username, and join_date describe? or rather, are they unique to a guild, or the same regardless of guild? what if you wanted to store the global_name of a user (their display name) but also the nick name that they have for a particular guild? which tables would you put both columns in? that distinction is why the API conceptualizes "users" and "members" separately

craggy swallow
craggy swallow
waxen finch
#

i guess you would still have the same key/guild/user ID columns in your variable tables, but you would need another way to enforce that a guild-specific key can't have values from other guilds...

#

also it would be nice if the guild_variable and member_variable tables could be merged into one for simplicity, where guild-specific values are represented with null for the user ID

craggy swallow
craggy swallow
waxen finch
#

i think someone else will have to weigh in on how the constraints should be implemented, but it would be really nice if you could have a single variable table like: sql CREATE TABLE variable ( key_id BIGINT NOT NULL, guild_id BIGINT, user_id BIGINT, value_id BIGINT ); CREATE UNIQUE INDEX ON variable (key_id, guild_id, user_id); where guild-specific values only have (key_id, guild_id), member-specific values have (key_id, guild_id, user_id), and user-specific values have (key_id, user_id)

#

if it's not easy to implement, i guess you'll have to suffice with guild_variable, member_variable, and user_variable tables each with their respective primary key above

craggy swallow
#

And instead of including the key_id in the PK, I should include the value_id?

waxen finch
craggy swallow
craggy swallow
#

or should I actually be doing smth like this? with 3 foreign keys, the first two to enforce that the value is indeed of the key, which is of the guild, and the last to indicate to who the value (variable) belongs? And the value_id included in the primary key?

CREATE TABLE guild_variable (
    key_id INT,
    guild_id BIGINT,
    value_id INT,
    PRIMARY KEY (value_id, guild_id),
    FOREIGN KEY (key_id, guild_id) REFERENCES key (id, guild_id),
    FOREIGN KEY (key_id, value_id) REFERENCES value (key_id, id),
    FOREIGN KEY (guild_id) REFERENCES guild (id)
);

CREATE TABLE member_variable (
    key_id INT,
    guild_id BIGINT,
    member_id BIGINT,
    value_id INT,
    PRIMARY KEY (value_id, guild_id, member_id),
    FOREIGN KEY (key_id, guild_id) REFERENCES key (id, guild_id),
    FOREIGN KEY (key_id, value_id) REFERENCES value (key_id, id),
    FOREIGN KEY (guild_id, member_id) REFERENCES guild_member (guild_id, member_id)
);
drawSQL-db-export-2024-01-20_2.png
craggy swallow
waxen finch
#

there are some inconsistencies with that SQL and it doesn't match up with the ERD, but i think you've got the idea

craggy swallow
craggy swallow
craggy swallow
waxen finch
craggy swallow
#

that is, i need to know which values correspond to which keys

waxen finch
#

isnt that what the variables are for? or is it like a predefined set of values that variables can be given?

craggy swallow
waxen finch
#

that means each key can have many values as desired, and foreign keys can uniquely point to values with it

waxen finch
craggy swallow
craggy swallow
#

or wait, wouldn't I also need to do the same similarly for the key table? set PK to (id, guild_id), for the same reason?

waxen finch
craggy swallow
#

(i'm fine with duplicating the key if it needs to be used multiple times by multiple guilds, so it's like a row of the key table is a key per guild)

#

(this also allows for different guilds to have differnt value options for the same key (which would be a different key.id))

waxen finch
craggy swallow
#

and then guild_variable would contain the value_id that is actually chosen for the key

waxen finch
#

oh then yeah, that's fine for a primary key

#

note that the order of the primary key (and indices in general) affects whether queries can use them to make faster selects

#

for example if key's primary key was (guild_id, id), a select query like SELECT * FROM "key" WHERE guild_id = ? can take advantage of the primary key's index because that's its first column

craggy swallow
waxen finch
#

seems just about right, for cleanliness i suggest moving the primary key columns to the top of the table, and for both variable tables the key_id should be part of the primary key and not the value_id, otherwise you'd be allowing multiple variables to have values from the same key, rather than just one value for one key

#

in SQL form that would be: sql CREATE TABLE guild_variable ( key_id INT, guild_id BIGINT, value_id INT, -- For every key, a guild can submit a value for that key PRIMARY KEY (key_id, guild_id), -- Values can only be given to keys that are part of their guild FOREIGN KEY (key_id, guild_id) REFERENCES key (id, guild_id), -- Values can only be given to their associated key FOREIGN KEY (key_id, value_id) REFERENCES value (key_id, id) );

craggy swallow
waxen finch
#

oops, forgot we discussed that

#

so yeah non-unique index, and also it might be nicer to order the columns the same as the key's PK, e.g. ```sql
CREATE TABLE guild_variable (
guild_id BIGINT,
key_id INT,
value_id INT,
-- For every guild key, the guild can submit a value for that key
FOREIGN KEY (guild_id, key_id) REFERENCES key (guild_id, id),
-- Values can only be given to their associated key
FOREIGN KEY (key_id, value_id) REFERENCES value (key_id, id)
-- Without a primary key, multiple values can be submitted for the same guild key
);
-- Allow fast lookup of values for a key using its PK
CREATE INDEX ON guild_variable (guild_id, key_id);

-- Expected usage:
INSERT INTO key (guild_id, id) VALUES (100, 200);
INSERT INTO value (key_id, id) VALUES (200, 300);
INSERT INTO value (key_id, id) VALUES (200, 301);
INSERT INTO guild_variable (guild_id, key_id, value_id) VALUES (100, 200, 300);
INSERT INTO guild_variable (guild_id, key_id, value_id) VALUES (100, 200, 301);```

craggy swallow
waxen finch
craggy swallow
waxen finch
#

we had a lot of back-and-forth for what amounted to renaming a table, adding a missing member_variable.guild_id column, and reordering a few columns... there were a lot of considerations though, so it's probably a good idea to write a checklist in case you have to change it again

obtuse magnet
waxen finch
# obtuse magnet Wait what?

you know how a list can be sorted by multiple values? for example lets say you have a list of numbers, first sorted by the 1st digit of that number in ascending order, then sorted by the 2nd digit in descending order, that might look like: [11, 29, 26, 23, 21, 58, 57, 53, 53, 78, 91] you can use binary search to find numbers starting with 2, or to find numbers starting with 5 and ending with 3, but you can't use binary search to find the numbers that end with 1 because they're spread across the entire array

the same concept applies to most indices and primary keys as they typically use a B-tree structure under the hood to do the same binary searching
see also https://www.postgresql.org/docs/current/indexes-multicolumn.html

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.

torn sphinx
#

Library: Asyncpg
Language: Python

query = ("UPDATE Guild_Settings SET $1=$2 WHERE Guild_ID=$3")
await connection.execute(query, str1, False, str(ctx.guild.id))

How would I go about constructing this query, so that I can pass a variable for the table name?

ionic pecan
#

I'm not sure if this works, but I remember using something similar to UPDATE quote_ident($1) SET ... a while ago

#

that was in psycopg2 though, afaik asyncpg does server side parametrization so I'm not sure that works

torn sphinx
#

I could format it with an f-string, but doesn't that open up the possibility of SQL injection?

ionic pecan
#

yep it does

#

this feels a bit hacky, but you could call SELECT quote_ident($1) for the table name, then use the result of that in an f-string for the table name

torn sphinx
#

Damn, that's unfortunate. Ahh well, ty for the help!

meager veldt
waxen finch
hearty fjord
#

hey i am begginer on flask.... i follow tuttorial and i run into this error. basicly i have to import my db but its keep saying that module dont found.. can anyone help me and thank a lot

Screenshot_from_2024-01-21_18-40-21.png
green cypress
#

Hi guys, I need your help to install stsynphot package in my VS code environment (I'm using a windows machine). There is some package that I must add externally. I need help with that. Here is the link to it: https://stsynphot.readthedocs.io/en/latest/

fading patrol
torn sphinx
exotic drift
#

hiiiii, i still have this issue, can someone pls help

coral wasp
ionic pecan
open ginkgo
#

I am getting problem to connect css file I am using flask framwork, I used url_for() to get the path, the css file stored in the static file

image.png image.png
#

I wonder why the css modification isnt being displayed then?

signal bolt
#

is someone awake?

#

i need help in python inter face with mysql

#

this is the code for displaying movies from database

#

and it is giving wrong output

#

but it is being stored in the database right

#

how should i solve this?

waxen finch
#

!e py import datetime release_date = datetime.date(2010, 9, 29) print("{:>15}".format(release_date)) print("{:%Y-%m-%d}".format(release_date)) print("{:>15}".format(str(release_date))) (there's also the conversion flag syntax, "{!s:<15}".format(d) if you prefer that over str())

delicate fieldBOT
#

@waxen finch :white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | >15
002 | 2010-09-29
003 |      2010-09-29
signal bolt
#

i will try this thank you @waxen finch

signal bolt
#

@waxen finch now it is a new error i don't under stand

#

def add_booking():
display_movies()
cust_id = int(input("Enter the ID of the customer: "))
movie_id = int(input("Enter the ID of the movie you want to book: "))
customer_name = input("Enter your name: ")
booking_date = input("Enter the booking date (YYYY-MM-DD): ")

query = "INSERT INTO bookings (id, movie_id, customer_name, booking_date) VALUES (%s, %s, %s, %s)"
values = (cust_id, movie_id, customer_name, str(booking_date))

try:
    cursor.execute(query, values)
    db.commit()
    print("Booking added successfully!\n")
except mysql.connector.Error as err:
    print(f"Error: {err}")
    db.rollback()
#

by far it is giving me this error

#

Error: 1054 (42S22): Unknown column 'booking_date' in 'field list'

waxen finch
#

does booking_date exist in the bookings table?

signal bolt
#

no

#

@waxen finch

image.png
waxen finch
#

well, did you mean to insert into the show_time column? or is that table column misnamed?

signal bolt
#

no

#

the columns name are not misplaced

waxen finch
#

what do you want to do about it then? bookings.booking_date doesn't exist so you have to either make it exist (i.e. altering the table), or insert the booking date into a different column

signal bolt
#

ok i will tell you after trying it out

#

thank u for your help

#

def create_bookings_table():
query = """
CREATE TABLE IF NOT EXISTS bookings (
id INT PRIMARY KEY,
movie_id INT,
customer_name VARCHAR(255) NOT NULL,
booking_date DATE,
FOREIGN KEY (movie_id) REFERENCES movies(id)
)
"""
a.execute(query)
db.commit()

#

it is a new one yet it is still giving an error

#

@waxen finch

waxen finch
#

the same error?

signal bolt
#

yep

waxen finch
#

the CREATE TABLE IF NOT EXISTS construct only creates the table... if it does not already exist

#

if you dont mind deleting the old table and its data, you can do DROP TABLE bookings; beforehand

plain wagon
#

Greetings to everyone, I am testing our database with Supabase's Python client library, but I have a problem that I cannot solve. When I insert a row into the Review table and then delete that row and try to insert it again, it gives me this error. Even though the table is empty, postgrest returns such an exception. What could be the reason for this?

Do I have to enable realtime to prevent this ?

Screenshot_2024-01-22_at_19.10.25.png
rich socket
#

Hello everyone, I am a beginner and have been programming for about a week and I just had a question if I did this correctly and if so, how can I make it more efficient?

image.png
#

Is sys.stdin.readline() really necessary or can I use input?

fading patrol
rich socket
#

@fading patrol

fading patrol
thorny marsh
tender ruin
#

How do i get the "donationer" to give me information. because it is nummers. I get the "name" but not the donation.

check = sponsorcol.find()
for x in check:
print(x)
await ctx.respond(x["name"] + x["donation"])

buoyant aurora
shut tiger
rocky spruce
#

im coding a discord bot and ill like to know how do i update a null value where table is alr created and has a primary key?

shut tiger
tender ruin
shut tiger
#

You have to describe the problem! We can't help you otherwise.

tender ruin
#

I just get a error

shut tiger
#

Errors DESCRIBE the problem. YOU need to describe the problem TO US. COPY PASTE the error.

#

https://kodare.net/2021/05/19/how_to_ask_for_help.html

En kodare
How to ask for help

Ask something specific Before asking for help, try to make the smallest example you can of the problem. Don’t just show us your entire code base. If you don’t understand the advice given, say so!. Don’t just ignore it. Don’t tell us error messages in your own words. Copy paste them. In full. “Doesn’t work” is not an error message. How does it no...

#

Read this. Several times.

tender ruin
#

raise ApplicationCommandInvokeError(exc) from exc
discord.errors.ApplicationCommandInvokeError: Application Command raised an exception: UnboundLocalError: local variable 'response' referenced before assignment

shut tiger
ionic pecan
# shut tiger Read this. Several times.

asking questions well is a skill people have to learn, like speaking english or writing python code. When I started with programming I would also ask questions that didn't make any sense. 10 years ago I made a stackoverflow post about writing a calculator in Java and I had no idea how to ask the question, no idea how to write proper Java, and the 200 infoboxes on SO were intimidating more than anything else. I had someone very friendly give me a solution (it was a one-liner) and explain how I could find it, and he even had the patience to help me out in the comments. If he would have came to me sending all caps messages because I didn't know how to program computers or use help forums I'm not sure I would still be doing a career in IT to this day. I remind myself of the same thing when I help my older family members figure out how to print attachments in emails or similar, which are all straightforward to me but other people have to learn it with a lot of effort, and that's okay. If I get mad at them, they will be discouraged, frustrated, and maybe even will start feeling excluded in the wonderful digital world we have built up. So maybe consider this the next time you lose patience because someone doesn't know how to ask the perfect question yet.

shut tiger
#

Yea, sorry. I am having a very bad day for unrelated reasons. @tender ruin sorry about me going off the rails. Did you understand what I wrote above?

digital coral
#

Hello, how can i load the data that is dependent on the related table using SQLAlchemy?

class User(Base):
    posts: Mapped[list[Post]] = relationship(backpopulates="author")
    post_count: Mapped[int] = ...

Is there a single query to fetch the post_count? i dont store the post_count on the table.

or should i just store the post count on the table as a cache? and just increment it after every post?

shut tiger
digital coral
tender ruin
torn sphinx
#

Can someone pls help me out...I'm new to pandas. So, basically I'm doing a college project in python using pandas library where I've generated a basic healthcare database consisting of 100 entries using ChatGPT. And I wanna use pandas to read the csv file and filter out patients satisfying some required parameters for clinical trials . I'm using a dictionary to store the parameter names as keys and parameter values as values.How to I use these to filter out the patients satisfying the required criteria?Is it possible to use Classes and objects to model the patients , if so how...Thanks once again

strange pine
#

Hey guys can someone suggest some sites where i can practice questions for python and YouTube channel to learn it

shut tiger
shut tiger
strange pine
rich socket
azure fox
# 
self.cursor.execute("SELECT participants FROM giveaways WHERE guild = %s AND time = %s AND prize = %s",(self.guild, self.time, self.name,))
        print("Guild:", self.guild)
        print("Time:", self.time)
        print("Name:", self.name)
        participants = self.cursor.fetchone()
        print(participants)
        if participants:```

I have problem where my participants always equals to None (shows in the print)
#

what should i do

#

the print: ```
Guild: 1191037474340884620
Time: 1706029268.2506127
Name: test giveaway
None

grim vault
#

Does price equals name? Because you have price in the select and self.name in the argument.

azure fox
#

yes as you can see the in print

grim vault
#

Do you have a database browser where you can take a look at the data and try the SQL directly?

azure fox
#

yeah sure

#

wait

#

here

grim vault
#

Your time looks like an integer but you give a float.

azure fox
#

hmm

#

self.cursor.execute("CREATE TABLE IF NOT EXISTS giveaways (time BIGINT, prize TEXT, message BIGINT, channel BIGINT, guild BIGINT, participants TEXT, winners INTEGER, finished BOOL)")

#

my create table

#

oh

#

damn

#

it should be float right?

grim vault
#

Only if you need the fractions.

azure fox
#

lemme try something

#

tried it as float too still None

grim vault
#

Tried what exactly? Same game again, take a look at the data and try the SQL.

azure fox
#

I deleted the table

#

and created again but time this time as float

shut tiger
azure fox
# shut tiger You haven't shown the insert statements.

self.cursor.execute("INSERT INTO giveaways (time, prize, message, channel, guild, participants, winners, finished) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", (epochEnd, prize, "", channel.id, interaction.guild.id, "", winners, 0,))

shut tiger
azure fox
azure fox
shut tiger
azure fox
#

I'm using a different time theme

azure fox
shut tiger
#

Try removing a part from the select until you get a result.

azure fox
#

so i have import time as pyTime

#

then

# 
epochEnd = pyTime.time() + time```
coral wasp
coral wasp
stable reef
#

I'm going to use redis for cache in my django application

#

but I'm a bit confused on when should I cache something

#

because everything from what i saw on the internet said to see if it's cached and if it isn't then cache it

#

but that's it? There's no more specific criteria?

#

and how about expiration, how much time should I persist something in cache?

shut tiger
versed needle
#

Hello, could anyone suggest a method on how I could merge the rows here in a way that the DrugNames A and B(for instance) will become A+B for the overlapping period and if there are any non-overlapping periods, the periods for rows containing A and B will be updated so that they are not overlapping with the period of row A+B (or they will be deleted if their periods result in something <=0 days).

image.png
storm mauve
# versed needle Hello, could anyone suggest a method on how I could merge the rows here in a way...

last I checked, pandas doesn't have many tools for working with ranges

I would probably try something like

  • apply to create a column containing a pandas.date_range (strictly speaking DateTimeIndex iirc?) based off startdate, enddate
  • explode on that so that you have one row per date
  • groupby date and apply a custom function that joins all DrugName from rows in the group

which should work fine if you have at most a few tens of thousands of rows, but if you absolutelly need of performance, no clue - good luck

somber mauve
#

woops, that bugged out

#

egh whatever, it's there

torn sphinx
#

Hello all, does anyone know how to append a pandas dataframe into an existing excel sheet that already has data?

So far, with openpyxl I have only managed to:
Delete all sheets
delete all data in a single sheet with none of the previously existing data

torn sphinx
#

Ok, I found out how

open ginkgo
#

I have developed a CRUD application by using flask. Everything works accordingly now I wonder how can I host that application to the web server?

fading patrol
open ginkgo
median crypt
#

Good morning guys!
I need to mine some data in order to build my DB, the problems is that most of the things i want are hidden in hrefs. I'm trying to use Selenium and Tor to scrape the data i need (Frequency Lists asbtraction for natural languages) but i keep getting block in some domains even with tor and tor server installed through th terminal.
Is there any other way, aside from ip rotation, that can work for data-mining. I do research.

#

never thought data-mining would be this hard 😄

storm mauve
#

do not data mine places that don't want for you to mine their data, it's that simple

median crypt
#

yep... i'm aware of it. bu there is no other way i believe.

#

in order to make frequency list i need to gather data from the net. Especially social media.

storm mauve
#

we are not going to help with it,

#

!rule 5

delicate fieldBOT
#
Rules

5. Do not provide or request help on projects that may violate terms of service, or that may be deemed inappropriate, malicious, or illegal.

median crypt
#

i mined to like 60-70% of my projects needs... can't do the rest.

median crypt
#

ok.. didn't know that.

storm mauve
#

just use a publicly available dataset

median crypt
#

can't... i have to make my own. uni demands it :/

fair coral
#

just say it's against tos

wise goblet
#

😅 as for web scrapping and doing it in this illegal way, u could just raise this topic with your professor regarding how much it is legal/ethical
Optionally issue could be escalated to your faculty department regarding this and asking to pass this exam/work whatever in automated way for this reason

#

people can communicate. just having discussed this issue, you can remove it.
University highly likely does not want extra problems as well.

wise goblet
delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied timeout to @valid widget until <t:1706217741:f> (10 minutes) (reason: duplicates spam - sent 4 duplicate messages).

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

coarse compass
#

Im trying to get into fullstack development and I asked chatgpt to give me a realistic business problem and it wants me to create an inventory tracking system which allows a user to view/update inventory quantities and to send notifications to a user when a product falls below a specified amount. I dont have much experience making a database so if someone could review this and give me thoughts/concerns that would be greatly appreciated.

image.png
harsh pulsar
#

also, the problem with fake projects is that you don't have any real business requirements

#

so for example that ER diagram looks fine, except for the fact that there doesn't seem to be a way to represent a partially-shipped order in a way that keeps track of which items on the order have been sent out in which shipment

#

is that an important omission for your fake project for your fake business? it's hard to say

west hill
#

all depends on the parameters and restrictions u set for urself and ur project 👀 but considering as it is something to familiarize with a tech stack its prolly trivial

harsh pulsar
#

right. the exercise of clearly defining requirements before starting work might be valuable here

shut tiger
#

I think you shouldn't draw a big diagram before starting. Just go and fix stuff later.

brave bridge
#

In an RDBMS, I have a table of Users, and every user has zero or more Widgets (one to many relation).

When adding a new widget, how do I ensure that a User has no more than 8 widgets? Is there a way to do it without altering the schema?

waxen finch
hexed estuary
#

add columns widget1, ..., widget8 to Users and use these :p

brave bridge
harsh pulsar
#

What I think you're getting at, and what I agree with, is that it's very hard to design an entire system upfront, and it might be better to focus on individual pieces of it as you build out incrementally

waxen finch
harsh pulsar
#

what happens as a result of that rollback will depend on the transaction isolation level and any other synchronization controls

coral wasp
harsh pulsar
coral wasp
#

Or, as Mike Tyson said; everyone has a plan until they get punched in the mouth. </end quote mode>

coral wasp
harsh pulsar
#

but i like the addendum that planning is what helps you survive the event in which your plans become irrelevant or useless

coral wasp
harsh pulsar
#

otherwise it's not really actionable except as a way to quickly get past your surprise when things don't go your way

shut tiger
regal cairn
#

How could I extract the name from here?

Screenshot_2024-01-26_at_9.26.18_PM.png
#

I've tried adding ...["name"], but here's what it does

Screenshot_2024-01-26_at_9.26.34_PM.png
#

and if I apply str over it, I just get, literally

'13 Household & Comfort\nName: name, dtype: object'

regal cairn
hexed estuary
regal cairn
coarse compass
coral wasp
coral wasp
harsh pulsar
harsh pulsar
#

is there naturally an L:M:N relationship between stores, orders, and products? maybe you don't want to model it that way in the database, but if that's the reality of the task then you can't avoid contending with it somehow.

light rapids
#

yo so im trying to import my db module into main filr

#

do i have to do the connection?

#

or i can just import db file

#

db written in sqlite3

#

and has 1 table so its not really complicated

# 
def connect_to_database(database_file):
    connection = sqlite3.connect(database_file)
    return connection```
#

im asking abt this part gpt tells me^ cause in my db i just did a normal connection without making it a function

#

i dont wanna write smt i dont understand

west hill
light rapids
#

oh, cause in the db file i just had a normal conn = sqlite3.connect("quanda.db")

#

why make it a function\

grim vault
#

It's not needed but you are more flexible if you have it in a function. I for one also set some pragmas after connecting to the database, eg:

        ...
        conn = sqlite3.connect(db_filename)
        conn.execute("PRAGMA foreign_keys=ON")
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA temp_store=MEMORY")
        ...
        return conn
fierce thunder
#

sqlite3 is good for pc software with tkinter? and what for flet app ?

steady saffron
fierce thunder
steady saffron
#

pc software would run on the machine so yes it's local

fierce thunder
#

oh

#

local mean 1 device

#

what they use in genral as database for tkinter and pc software

steady saffron
#

sqlite is a common choice

carmine dragon
#

anyone here familiar with redis? my redis server is crashing and im pretty sure its due to it just being flodded with req. i looked into redis cluster but i dont think im at that scale, i believe im just mishandling my redis connections

#

it's a discord bot with 6 clusters, and about 5 shards per cluster. using redis insights i notice it peaks about 6.5k connected clients before just crashing.

paper flower
carmine dragon
# paper flower Never had to deal with something like this, but did you check redis logs? Also a...

yeah console never shows anything but this

1:C 29 Jan 2024 00:15:18.328 * oO0OoO0OoO0Oo Redis is starting oO0OoO0OoO0Oo
1:C 29 Jan 2024 00:15:18.328 * Redis version=7.2.4, bits=64, commit=00000000, modified=0, pid=1, just started
1:C 29 Jan 2024 00:15:18.328 * Configuration loaded
1:M 29 Jan 2024 00:15:18.329 * monotonic clock: POSIX clock_gettime
1:M 29 Jan 2024 00:15:18.329 * Running mode=standalone, port=6379.
1:M 29 Jan 2024 00:15:18.329 * Server initialized
1:M 29 Jan 2024 00:15:18.334 * Reading RDB base file on AOF loading...
1:M 29 Jan 2024 00:15:18.334 * Loading RDB produced by version 7.2.3
1:M 29 Jan 2024 00:15:18.334 * RDB age 442291 seconds
1:M 29 Jan 2024 00:15:18.334 * RDB memory usage when created 15.15 Mb
1:M 29 Jan 2024 00:15:18.334 * RDB is base AOF
1:M 29 Jan 2024 00:15:18.406 * Done loading RDB, keys loaded: 1851, keys expired: 0.
1:M 29 Jan 2024 00:15:18.406 * DB loaded from base file appendonly.aof.2.base.rdb: 0.074 seconds
1:M 29 Jan 2024 00:15:18.635 * DB loaded from incr file appendonly.aof.2.incr.aof: 0.230 seconds
1:M 29 Jan 2024 00:15:18.635 * DB loaded from append only file: 0.304 seconds
1:M 29 Jan 2024 00:15:18.641 * Opening AOF incr file appendonly.aof.2.incr.aof on server start
1:M 29 Jan 2024 00:15:18.641 * Ready to accept connections tcp
1:M 29 Jan 2024 00:16:19.090 * 10000 changes in 60 seconds. Saving...
1:M 29 Jan 2024 00:16:19.091 * Background saving started by pid 33
33:C 29 Jan 2024 00:16:19.446 * DB saved on disk
33:C 29 Jan 2024 00:16:19.447 * Fork CoW for RDB: current 1 MB, peak 1 MB, average 0 MB
1:M 29 Jan 2024 00:16:19.492 * Background saving terminated with success
#

and i should prob mention im using aiocache as the lib

paper flower
#

Logs seem fine

carmine dragon
#

so it's handling for me

paper flower
#

I think libs like these abstract a lot of implementation details which could lead to situations like this, though, I'm not sure

carmine dragon
#

probably right.

ionic pecan
#

whats the error you see when it crashes?

paper flower
#

Can you share a couple of functions that you use aiocache with? I assume you use it as a decorator?

paper flower
carmine dragon
#

i just understand how its creating so many connections lmao

paper flower
#

aiocache readme mentions caches.set_config method/function

carmine dragon
#

oh wait

#

i think ur right

#

lmao

#

i had that when i was using simple mem cache

#

and removed when i switched it to redis

#

so each time thats called it prob creating a new client 💀

paper flower
#

You can also pass a client to it

carmine dragon
#

to the cache?

paper flower
#

Yeah, I guess there's a lot of ways ot do that

carmine dragon
# 
@cached(ttl=12400, cache=plugin.bot.redis_cache,
#

?

#

cause im creating a connecting in my bot class that i use else where

#

that may just be where i was fucking up

paper flower
#

If you use cache in just a couple of places then creating a simple abstaction is quite easy

#

But yeah, that's more code in general

carmine dragon
#

im kinda confused

#

i do this

caches.set_config(
    {
        "default": {
            "cache": "aiocache.RedisCache",
            "host": "192.168.1.11",
            "port": 6379,
            "serializer": {"class": "aiocache.serializers.PickleSerializer"},
            "plugins": [
                {"class": "aiocache.plugins.HitMissRatioPlugin"},
                {"class": "aiocache.plugins.TimingPlugin"},
            ],
        },
    }
)
#

but for the decorators, i just leave right?

#

and then change where im using cache.get to represent the default cache rather than connecting to redis again

unreal hemlock
#

Can I use autoincrement in sqlite at different rates based on another value?

#

For example, I have a table with properties id and guild_id, could I make so each id autoincrements based on the guild_id

#

id 0, guild 111
id 0, guild 999
id 1, guild 999
...

paper flower
# carmine dragon i do this ```py caches.set_config( { "default": { "cach...

Then I don't think you have to specify that info in your decorators?

@cached(ttl=12400, cache=Cache.REDIS, key_builder=lambda f, *args: f"{f.__name__}{args[0]}", serializer=PickleSerializer(), endpoint="192.168.1.11", port=6379, namespace="main", password="")

@cached(ttl=12400, cache=Cache.REDIS, key_builder=lambda f, *args: f"{f.__name__}{args[0]}", serializer=PickleSerializer())
#

Maybe the namespace

carmine dragon
paper flower
#

probably read docs a bit more or look for similar issues?

jaunty oxide
#

Hi, need some help in mongoDB with AWS lambda. I am using pymongo to connection with dedicated Atlas (M10) database. Mostly I do write operations. This is a cronjob (triggered weekly once) where we fetch data and store it in MongoDB. write operations happens up to 100,000 objects. Duration for the operation to write is not in priority as I this is background job. But Atlas throws connection limit exceeds 1.5k (M10 max limit is 1.5). MongoDB doesn't accept any more write/read actions. I get ServerSelectionTimeoutError from PyMongo client. Not sure what should I do. I am not using MongoClient as global variable, I tried adding pyMongo in with statement, but still I get MongoDB timeout error.

config:

python-3.9

MongoDB atlas - M10 series DB

ionic pecan
#

do you spin up a lambda for each object or something like that? or how does your app access it?

jaunty oxide
#

yes, another server triggers a message to sqs and it connects to lambda. single lambda writes around 1000 objects. in cron, it triggers around 350-400 messages to sqs as separate message.

ionic pecan
#

can you configure some kind of concurrency limit for the lambdas?

wind flax
#

how do i connect to mysql using cogs??

# 
# Load MySQL 
mysql = mysql.connector.connect(
    host= os.getenv('MOMO_HOST'),
    user= os.getenv('MOMO_USER'),
    password= os.getenv('MOMO_PASS'),
    database= os.getenv('MOMO_NAME'),
)
ionic pecan
#

which library are you using

wind flax
jolly whale
#

can I ask a plain postgresql command line question here?

fading patrol
bright hound
#

How would I set up this database:
Each day there is a variety of foods being offered in the cafeteria, and the foods will change on a rotating basis. I want to track all the different types of food, how many times they have been offered, and on which dates they were offered. Sometimes I will want to query by food type, and sometimes by date to make different graphs and whatnot. Users should be able to log in and save favorite food items to their profile etc

#

I'd like to use a SQL db

fading patrol
bright hound
#

ChatGPT is telling me I need: Foods, Dates, Users, and User Preferences in separate tables

#

Seems like there will be so much redundancy

#

Maybe I should use NoSQl

waxen finch
bright hound
#

Like if I had a Date table, wouldn't I have multiple records for each date since I'd have to have a record for each type of food offered on that date?

#

In my programming brain I think of a dictionary like food_dates = {'1/1/24':['Pizza', 'Burger', 'Salad']}

#

But I guess I need to think like a database

#

instead of a program

waxen finch
# bright hound Like if I had a Date table, wouldn't I have multiple records for each date since...

right, association tables store both primary keys of entity A (date) and entity B (food item) in one row to represent a relationship between them, in this case being "this food was offered on this date"

if you made your schema store a list of foods in a single column for each date, this would violate the "first normal form" in the process of database normalization which generally makes it harder for relational databases to apply constraints to your data, especially foreign keys here (to validate that each food item exists in your food table)

bright hound
#

So with a database like this where say 10 foods are being offered each day, how long does that take before the database gets super large

#

I mean like, how long does it take a simple database like this to get to 1GB? 10GB?

#

I guess there's probably some calculation I could do

#

number of columns times size of some theoretical data per column per row

#

I'm seeing now you can compress Sqlite

#

So I guess it probably won't be an issue

waxen finch
# bright hound So with a database like this where say 10 foods are being offered each day, how ...

assuming the database is sqlite, you're storing 10 food items each day for 10 years, and the schema stores each date in text form and references each food using an integer ID: ```py
import datetime
import sqlite3

YEARS = 10
FOODS_PER_DAY = 10
TODAY = datetime.date.today()

conn = sqlite3.connect("test.db")
conn.execute("DROP TABLE IF EXISTS food_selection")
conn.execute("CREATE TABLE food_selection (date TEXT, food_id INTEGER)")

for i_day in range(365 * YEARS):
date = TODAY + datetime.timedelta(days=i_day)
for i_food in range(FOODS_PER_DAY):
food_id = FOODS_PER_DAY * i_day + i_food
conn.execute(
"INSERT INTO food_selection (date, food_id) VALUES (?, ?)",
(date, food_id),
)

conn.commit()
conn.close()``` the resulting database will need ~744KB to store those rows

bright hound
#

oh lol

#

So a long time

waxen finch
#

to my understanding, relational databases can typically tolerate millions of rows, but your query performance will be affected by how well your schema is designed (e.g. do I use the smallest primary keys? do I have indices on the columns I'm using to filter? am I making a lot of joins / does my schema need to be denormalized?)

#

but someone else can offer a deeper insight into that cause i just write stuff for a hobby

bright hound
# 
cur.execute('CREATE TABLE food(food, date)')
cur.execute('CREATE TABLE users(user_id, email, password, food, status, location)')

Pretty sure there's something not good about this, but what are the downsides to this type of setup?

#

From what I understand I could define both columns of the food table as primary keys

#

Ty for taking the time to explain this btw

waxen finch
bright hound
#

I like my passwords how I like my potatoes... Salted and hashed

#

😎

waxen finch
#

associative tables should* often use a compound primary key, that being: sql CREATE TABLE food_date ( date TEXT, food_id INTEGER, PRIMARY KEY (date, food_id) ); the primary key guarantees those set of values to be unique for each row which makes it easier to say, store the number of offers for a particular food at a particular date - the table will only have 0/1 row for that combination, so you only need to insert/update 1 row (or upsert if you're fancy)

* ||in some cases you might omit the primary key constraint to better represent your data, but as a result you'll probably need to add your own indices to make up for the lack of an implicit one provided by the primary key**, for example CREATE INDEX ix_food_date_date ON food_date(date);***||
** ||but even with a primary key, it's a good idea to create an index for food_id to help sqlite with foreign key checks***||
*** ||these points aren't that important so it's fine if you don't get them right now, but for a better understanding you can do further research on primary keys, indexes, and how their column order matters (SQLite docs can help with this too)||

bright hound
#

Hmm. My brain is starting to get fuzzy. I think I'll need to revisit this tomorrow after I sleep. Thank you for your input. This is all stuff I really should know, but I never use it so I'm feeling like a noob all over again

waxen finch
bright hound
#

lol for sure

fading patrol
torn sphinx
#

what database would you recommend for a few tables, i just need something i can learn quickly.

shut tiger
harsh minnow
#

I need to make a login database for my game and i am unding sqlite3. Is there any way to compare two values togther from two different tables?

fading patrol
harsh minnow
sleek vessel
harsh minnow
#

is there a place i can publish my api for testing?

fading patrol
native zinc
#

Any Pydantic + SQLAlchemy users in here?

lean olive
native zinc
#

Running into an annoying error, I don't see any fixes and their discord isn't so active 😅

lean olive
#

Show your code and your error.

native zinc
lean olive
native zinc
#

V2

native zinc
lean olive
#

Have you tried posting in the GitHub Discussions of pydantic?

brave bridge
#

speaking of sqlite...

#

I wish there was a similarly file-based database, but with a more traditional static type system. And some other stuff, like having foreign keys on by default

wise goblet
brave bridge
#

hmmmm

#

looks interesting

wise goblet
coral wasp
#

plus1 🦆

coral wasp
harsh minnow
#

And I want it in the cloud

foggy stag
# 
def set_purple_team(self, authorid, purple_id) -> None:
        db = accountDB()
        collection = db[f"wagers"]
        collection.update_one({"authorID": f"{authorid}"}, {
                              "$set": {f"purple_IDs{[0]}": str(purple_id)}})
    
    def set_yellow_team(self, authorid, yellow_id) -> None:
        db = accountDB()
        collection = db[f"wagers"]
        collection.update_one({"authorID": f"{authorid}"}, {
                              "$set": {f"yellow_IDs{[0]}": str(yellow_id)}})```

When i call these functions, the 0 index for both purple and yellow ids remain as "0". The function itself raises no errors, is this proper syntax?
image.png
waxen finch
fading patrol
brave bridge
#

Concurrent updates maybe, like in sqlite?

shut tiger
#

There are libraries that abstract away the data source. But the details of what kind of app you'd be writing matters. Is this just for export/backup?

coral wasp
solemn void
#

Hello
I am getting this error
can anyone help?
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'transaction_ibfk_1' in the referenced table 'bank'
I was creating a table and I got this

coral wasp
solemn void
#

but

#

when I created ```create table Transaction(credited int , debited int , username1 varchar(30), foreign key(username1) references bank(username));

coral wasp
#

bank.username doesn't have an index.

#

The normal approach here is:

#

Create table bank with an id column, that's a primary key and auto incremented.

#

And, use foreign key(user_id) references bank(id)

#

Note that Primary keys are automatically indexed, so you don't need to add an index

solemn void
#

Bro, Can you write me the code, Please. I need to submit this tommorrow ?

#

@coral wasp

coral wasp
solemn void
#

ok

nova umbra
#

Hello #databases members.

In an UPDATE query with a subquery in SQL Server, do you know to reference the outer query table ?

UPDATE 
  MY_TABLE 
SET 
  isactive = 0 
WHERE 
  MY_TABLE.isactive = 1 
  AND MY_TABLE.id NOT IN (
    SELECT 
      TOP (1) I_PD.id 
    FROM 
      MY_TABLE AS I_MT 
    WHERE 
      I_MT.driverId IS NOT NULL 
      AND ???.driverId = I_MT.driverId 
    ORDER BY 
      I_MT.id DESC
  );

I need ??? to be MY_TABLE from the outer query.

Suppose we have the table MY_TABLE like this :

id  |  signature |  isactive  |  driverId  |
1      sign#1          1            12
2      sign#2          1            12
3      sign#3          1            34
4      sign#4          1            34

The query I am trying to write needs to update the table so that a driver cannot be found active multiple times. So if the query works, the result should be this :

id  |  signature |  isactive  |  driverId  |
1      sign#1          0            12
2      sign#2          1            12
3      sign#3          0            34
4      sign#4          1            34

For this to work I have to find a way in the subquery to compare the driverId from the currently processed row of the outer query with the driverId of the currently processed row in the subquery.

What would be the way to achieve this ?

coral wasp
#

edit: You'd just need to write MY_TABLE.driverId. If that gives you an error (been a while since I touched sql server), let me know

languid pebble
#

You can use an alias I believe, wrap the inner in brackets and keyword after?

thorny remnant
#

heya, doing some sql (with mysql) for a course and our teacher asked us to have a column named "name", but it understands it as some kind of syntax, how do I get around this?

image.png
astral onyx
thorny remnant
grim vault
#

That's just the syntax highlighter from your editor. In this context it should work without the backticks.

#

And you'll need a length for your varchar(#length)

quasi kestrel
#

Hi all, how would I store task references in an SQL database? What type would I use?

#

So storing asyncio.create_task() reference into the database

west hill
narrow jetty
#

hey all I am trying to make a simple connect to my mongodb client but it doenst work could anyone land a hand with that?

image.png
narrow jetty
fading patrol
quasi kestrel
pulsar sky
#

If you may need help in python and database feel free to contact me

spiral tapir
#

Guys, I have 64bit python installation at work but only 32bit ODBC connection (ora12). Is there a way to set up a connection or do I need to install 32bit python?

west hill
spiral tapir
west hill
#

hmm that seems weird

quasi kestrel
#

What library would you recommend to create an access control database for user permissions? Seems there are a few RBAC ones what would you recommend is a good one to work with?

shut tiger
shut tiger
#

Or compile it yourself.

shut tiger
west hill
paper flower
arctic frost
#

i'm building a python app that needs to be accessible online (from multiple devices) and offline (i still need to be able to do everything offline, not just a few things) that's using supabase for db services, but i don't wanna have the same data twice. how would you go about it?

shut tiger
upbeat stirrup
#

Does anyone here have an idea on creating a simple login page (in the Python console) using MongoDb if so, kindly reply to my message by turning on mentions. Thanks.

fading patrol
arctic frost
obsidian basin
#

Using the newest version of flask-sqlachemy how do I update a search query?
Here is an example of what I am using.

search_results = Posts.query.filter(Posts.content.like('%'post_searched_form + '%')).order_by(Posts.title).all()

fluid obsidian
#

Hi, in mongoDB- which query can I run to check if the values in one column are different from the values in another column?
I tried {column1: {"$ne": column2}} but that gives everything including equal and unequal values

patent quarry
#

what is everyone's favorite pooled and async lib for mysql? Or combo there of.

olive reef
#

I'm currently making a DB that holds accounts and account transactions, I also have an option to create an account with a starting balance.

I was wondering whether having a balance column is a good practice, because, that could be calculated by adding all transaction amounts.

#

Also, what's the best approach to add the starting balance? To automatically create a transaction with the starting amount?

hexed estuary
#

Not sure about the best practices, but transaction for opening an account sounds reasonable. As for the balance, perhaps you can create an INDEX on the expression you use to calculate the balance from transactions, and get it automatically "cached" that way?

last oasis
#

Hello.
Does anyone have experience with Snowflake? I am wondering, is it possible to emit some event when a specific records appears there? Haven't explored snowflake_connector package in details yet.
Thanks.

glacial current
#

But to do an update on any row obj you just assign it new vals row.name = "bob"

#

Then do conn.commit() when you have updated all the rows you need to update

still cairn
#

Can somebody help me? I just can't install sqlalchemy on virtual environment because of "ERROR: Could not build wheels for greenlet, which is required to install pyproject.toml-based projects"

#

i tried to install by only the binaries, also tried to install a .whl file but it doesn't work

paper flower
wispy pawn
#

Hello, short question.
What error exception is best to use when user (email) already exists in database ?

paper flower
wispy pawn
paper flower
still cairn
#

im on windows using pip install flask_sqlalchemy

paper flower
still cairn
#

and this is the error

image.png
#

Python 3.11.7

obsidian basin
#

@glacial current Thanks but how do I use order_by?

paper flower
still cairn
#

and i'm also in a virtual environment

#

i just did py -m pip install wheel and nothing changed

paper flower
still cairn
#

Yeah it returns Python 3.11.7

paper flower
#

Can you try to pip install greenlet manually?

still cairn
#

yes that also didn't work for the same reason, but i'll try again

#

It happened the same problem

still cairn
#

Is it bcs i don't have greenlet? But i can't install it

paper flower
#

Honestly I don't know what's the problem here, maybe you could create an issue on their github?

#

I'm on windows and python 3.11 too, but it works fine for me

#

Can you copy full error somewhere?

#

e.g. to pastebin

still cairn
#

You mean here?

glacial current
obsidian basin
#

@glacial current Where can I find this information ? Also if possible I would prefer to use flask-sqlalchemy

glacial current
#

The key parameter is used to send each item, in this case a row, to the function and the function can return the name or date or any oter value in the list to be sorted on

#

If you return a tuple of values as I show above, that works too.

glacial current
still cairn
#

I did it! but it still doesnt work on venv

#

What a nightmare

obsidian basin
#

@glacial current How would I make the entire query using flask sqlalchemy?

#

Assume I am using flask-wtf forms as the search form

glacial current
waxen finch
obsidian basin
#

@glacial current I am sorry to be a bother but I looked at the documentation and can't find anything for search query . Did you find any. Also I looked at the documentation before and still could not find anything. Am I missing something?

glacial current
obsidian basin
#

Okay thank you for the prompt response I will take a look

obsidian basin
#

@glacial current this is uses an older version of flask-sqlalchemy. Any advice?

glacial current
manic ibex
#

How do you structure a "3 layer" / service->repository->domain application using sqlalchemy. The imperative mapper is a little bit deceiving, since it "instruments" the domain class constructor with extra parameters. On the other hand using the declarative style couples domain with persistence/sqlalchemy. 3rd option is to rely only on sqlalchemy core and develop a bespoke application specific mapper (reinvent the wheel). It seems that using sqlalchemy (or orms in general) is it's own "pattern". You delegate the persistence agnosticism to sqlalchemy and you build on top of it. There's a certain level of coupling you have to tolerate to work with sqlalchemy. However I acknowledge that it's a tradeoff between "pattern purity" and being stuck reinventing the wheel. What I like about the 3 layer arch is that I can take my services and test them in isolation. What are some best practices regarding "design patterns" when working with sqlalchemy.

#

Maybe offtopic for this channel, but what are some other ways to architect a service/module apart from the 3 layer arch. It's a vv old pattern, but it shows up everywhere with slightly different names and implementation.

paper flower
rare ore
#

Can someone recommend me some best practice for handling database calls in an object oriented code?
The issue is the following:
I have one object. This object contains a lot of other objects (lets call them leaves).
For each leaf, I want to find a neighbor in neo4j (graph database).
So from an object oriented perspective, it would make sense, that each leaf has a function "find_neighbor", so the responsibility is at the leaf where it belongs to. This would result in a few hundred db calls though. Instead I could just create one query in which I get the neighbor for each leaf and return that. But I would move that somewhere else and that's not nice from a modeling perspective.

#

Obviously I could place the query at the parent object and have a function "find_neighbor_for_each_leaf" but this wouldn't be "nice" from a modeling perspective I think

rare ore
#

Ok, it looks a little like the N+1 Query problem

harsh pulsar
#

actually, why can't that just be a method on the top level object?

#

root.find_leaf_neighbors()

#

alternatively, root.leaves.find_all_neighbors() where root.leaves is a LeafCollection or something like that

rare ore
shut tiger
paper flower
coral wasp
#

The idea is: can you modify the structure such that a single sql query can retrieve the hierarchy.

#

The second approach (again, in traditional databases) is to use a recursive CTE call, but those aren’t truly recursive or as efficient as the first idea.

#

Your question really is: how do I write a query in neo4j that returns a hierarchy with a certain pattern. I think you can ignore the Python part initially: the idea is, load the objects you need into memory via a single graph query. then traverse them in Python. That’s the point of a graph database.

#

This was a long answer just to say, what Doctor said.

true jungle
#

Hi

quick rock
#

bro

#

the question is simple
why doesnt work sql sql ALTER TABLE tictactoeLOGIN MODIFY column UserID int(11) AUTO_INCREMENT PRIMARY KEY;

fading patrol
harsh pulsar
#

that's why i suggested the opaque LeafCollection

real forum
#

Hey, I have a problem with MongoDB. When I install docker, it works fine. But as soon as I try to access the shell, I get the following error:

{"t":{"$date":"2024-02-10T17:23:37.251+00:00"},"s":"I",  "c":"CONTROL",  "id":23285,   "ctx":"main","msg":"Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'"}
{"t":{"$date":"2024-02-10T17:23:37.256+00:00"},"s":"I",  "c":"NETWORK",  "id":4915701, "ctx":"main","msg":"Initialized wire specification","attr":{"spec":{"incomingExternalClient":{"minWireVersion":0,"maxWireVersion":21},"incomingInternalClient":{"minWireVersion":0,"maxWireVersion":21},"outgoing":{"minWireVersion":6,"maxWireVersion":21},"isInternalClient":true}}}
{"t":{"$date":"2024-02-10T17:23:37.267+00:00"},"s":"I",  "c":"NETWORK",  "id":4648601, "ctx":"main","msg":"Implicit TCP FastOpen unavailable. If TCP FastOpen is required, set tcpFastOpenServer, tcpFastOpenClient, and tcpFastOpenQueueSize."}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationDonorService","namespace":"config.tenantMigrationDonors"}}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"REPL",     "id":5123008, "ctx":"main","msg":"Successfully registered PrimaryOnlyService","attr":{"service":"TenantMigrationRecipientService","namespace":"config.tenantMigrationRecipients"}}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"CONTROL",  "id":5945603, "ctx":"main","msg":"Multi threading initialized"}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"TENANT_M", "id":7091600, "ctx":"main","msg":"Starting TenantMigrationAccessBlockerRegistry"}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"CONTROL",  "id":4615611, "ctx":"initandlisten","msg":"MongoDB starting","attr":{"pid":66,"port":27017,"dbPath":"/data/db","architecture":"64-bit","host":"81437-52344.pph-server.de"}}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"CONTROL",  "id":23403,   "ctx":"initandlisten","msg":"Build Info","attr":{"buildInfo":{"version":"7.0.5","gitVersion":"7809d71e84e314b497f282ea8aa06d7ded3eb205","openSSLVersion":"OpenSSL 3.0.2 15 Mar 2022","modules":[],"allocator":"tcmalloc","environment":{"distmod":"ubuntu2204","distarch":"x86_64","target_arch":"x86_64"}}}}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"CONTROL",  "id":51765,   "ctx":"initandlisten","msg":"Operating System","attr":{"os":{"name":"Ubuntu","version":"22.04"}}}
{"t":{"$date":"2024-02-10T17:23:37.290+00:00"},"s":"I",  "c":"CONTROL",  "id":21951,   "ctx":"initandlisten","msg":"Options set by command line","attr":{"options":{}}}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"E",  "c":"CONTROL",  "id":20568,   "ctx":"initandlisten","msg":"Error setting up listener","attr":{"error":{"code":9001,"codeName":"SocketException","errmsg":"setup bind :: caused by :: Address already in use"}}}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"REPL",
# 
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"REPL",     "id":4794602, "ctx":"initandlisten","msg":"Attempting to enter quiesce mode"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"-",        "id":6371601, "ctx":"initandlisten","msg":"Shutting down the FLE Crud thread pool"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"COMMAND",  "id":4784901, "ctx":"initandlisten","msg":"Shutting down the MirrorMaestro"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"SHARDING", "id":4784902, "ctx":"initandlisten","msg":"Shutting down the WaitForMajorityService"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"NETWORK",  "id":4784905, "ctx":"initandlisten","msg":"Shutting down the global connection pool"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"NETWORK",  "id":4784918, "ctx":"initandlisten","msg":"Shutting down the ReplicaSetMonitor"}
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"I",  "c":"SHARDING", "id":4784921, "ctx":"initandlisten","msg":"Shutting down the MigrationUtilExecutor```
real forum
wise goblet
#

optionally sequence of regular docker commands to achieve that

unkempt prism
# real forum Hey, I have a problem with MongoDB. When I install docker, it works fine. But as... 
{"t":{"$date":"2024-02-10T17:23:37.291+00:00"},"s":"E",  "c":"CONTROL",  "id":20568,   "ctx":"initandlisten","msg":"Error setting up listener","attr":{"error":{"code":9001,"codeName":"SocketException","errmsg":"setup bind :: caused by :: Address already in use"}}}

I had some luck specifing the network as host so I could use localhost

docker run --name some-mongo --network host -d mongo:latest

docker run -it --network host --rm mongo mongosh --host localhost test

Does this work for you?

pseudo yew
#

hey! i need some help. Is there any website where i could upload my data and download subsets of it using filters (filters i can make)? It should be real simple, the people dealing with this has 0 python knowledge. What are my options here?

fervent charm
#

Anyone used deephaven, dolphindb, or kdb+?

thin arrow
#

Anyone know oracle

I have a doubt

opal condor
#

hello is anyone familiar with InfluxDB Cloud Serverless? My API call queries are always empty with a 200 response, but the data is actually there

coral wasp
ionic pecan
fading patrol
fading patrol
dawn dirge
#

Hi, does anyone have a good example of implementing asynchronous connection to a MySQL database (mysql+aiomysql) using FastAPI and SQLAlchemy? I have one project implemented in this way, but it seems to me that the database session is being closed too quickly and I'm getting an error (OperationalError) MySQL Connection not available. I don't close the session manually, I did everything as simply as possible. I would like to compare it with something worth comparing it to.

paper flower
dawn dirge
# paper flower How are you doing it currently? 
engine = create_async_engine(
    settings.SQLALCHEMY_DATABASE_URI,
    echo=settings.SQLALCHEMY_ECHO,
    isolation_level="READ COMMITTED"
)

AsyncSessionLocal = async_sessionmaker(
    bind=engine,
    autoflush=False,
    expire_on_commit=False
)

class Base(DeclarativeBase):
    pass

@asynccontextmanager
async def get_db_context() -> AsyncGenerator[AsyncSession, None]:
    try:
        async with AsyncSessionLocal() as session:
            yield session
    except SQLAlchemyError as e:
        logger.error(f"Error during database session: {e}")


async def get_db() -> AsyncGenerator[AsyncSession, None]:
   try:
       async with get_db_context() as db:
           yield db
   except SQLAlchemyError as e:
       logger.error(f"Error during get_db: {e}")

It's done this way so it can be used in path operations using DI and outside of the application flow. I need a database connection in a separate process to handle a RabbitMQ queue

paper flower
dawn dirge
# paper flower It looks completely fine though 🤔

In path operations it looks like this

db: AsyncSession = Depends(get_db)

In message consumer it looks like this:

async with get_db_context() as session:
   try:
      ....
   except Exception as e:
     self.handle_error(e, message)
paper flower
#

You want to use DI in your consumer?

dawn dirge
# paper flower You want to use DI in your consumer?

No, I don't want to use DI in the consumer. I wanted to compare this code with something that works correctly. Randomly, I encounter errors like this: (OperationalError) MySQL Connection not available. I am using MySQL as a cloud service. On the MySQL side, I don't see anything concerning.

paper flower
dawn dirge
paper flower
#

Maybe you close it twice somewhere accidentally?
e.g.

async with get_db_context() as session:
    ...
    # somewhere else
    async with session:
        ...
#

Or use it outside of that context manager block:

async with get_db_context() as session:
    ...
await session.execute(...)
pseudo yew
# fading patrol Uh, Google Sheets?

my csv sheet currently holds 500K records. its big. Some websbite where i can upload all these easily and which would let me download the parts of the data as i/ they need it.

pseudo yew
fading fern
#

can somebody help me and my friend im not that good at coding

fading patrol
#

Check your local job market but I suspect you'll find there are no "database certifications" that any employers actually care about

fading patrol
glacial current
#

I noticed that if i make a mistake where i do a select

select(MyTable).where( (MyTable.str_entry=='') or (MyTable.str_entry.is_(None)))

instad of

select(MyTable).where( (MyTable.str_entry=='') | (MyTable.str_entry.is_(None)))

i just get wrong result set. Like no error is raised. Is it possible for the or to be overloaded or is that outside the dunder operators?

#

or ... maybe am i doing this the wrong way? like i know there is an or_ function

select(MyTable).where( or_(MyTable.str_entry=='', MyTable.str_entry.is_(None)) )

but i think that becomes less readable.

upper heath
#

can someone give me some database code so i can get started? all the code i found online from other places dosent work

naive arch
upper heath
#

ty

dawn dirge
grim vault
coral wasp
last pivot
#

How i can migrate my database from sqlite3 to postgres?? Because i want to use Spatial database extension postgis and gdal ?

desert glen
#

greetings any idea what is this issue here
('Invalid parameter type. param-index=0 param-type=dict', 'HY105')

desert glen
#

ok found the issue, needed to use json.dumps(properties) as it was modifying the null values to None which broke the valid json format. thanks

warm igloo
#

I just noticed something. I appear to get less data than I should from the SQL query in Python when I run it from my Dash callback function when compared to the initial run of the program before the app goes live. Like say I hit run and input the initial date. The SQL that time will give me around 60 rows of what I need for that specific date, but when I run that same exact function again where the SQL query executes, but with the same exact date entered in a text box and click the button, I only get around 30 rows. What could be causing this?

#

This is for a Dash App that I'm building. Basically my SQL query uses a date entered through tkinter when opening the program and that gets placed in the SQL query under the variable dte. Initially I would get all the data I need, but when my Dash callback runs the code uses the same exact date in the SQL query, I get less data than before. The SQL query would run the code from earlier under the new dte variable for every time a date is entered into a text box and the button is clicked. My question is could my issue be related to the SQL query?

#

My program uses the Pyodbc library to execute the query.

desert glen
#

can you see what the actual query running in python is? @warm igloo

#

also what does the where clause look like in both instances?

pearl lodge
#

okay guys what am i doing wrong while register stuffs in my database?
i didn't got why but for some reason my database is not registering my macros, instead it's going into an eternal loop

async def register_macro(
        self, *, prefix: str, args: str, macro_type: str, macro_attr: str, id: int
    ):
        macro_data = {
            prefix: {
                "prefix": prefix,
                "cmd": args,
                "type": macro_type,
                "attribute": macro_attr,
                "id": id,
            }
        }

        print(f"Macro data: {macro_data}")

        database = await self.db["macros"].find_one({"id": id})

        if not database:
            await self.db["macros"].insert_one({"id": id, "macros": dict()})
            database = await self.db["macros"].find_one({"id": id})

        print(f"database = {database}")

        macros = database["macro"]

        if macros[prefix]:
            return "ERROR"

        else:
            macros = macros | macro_data
            await self.db["macros"].update_one({"id": id}, {"$set": {"macros": macros}})
            print("registered")
            return "SUCESS"

this is my debug:

entered
Macro data: {'+>attack': {'prefix': '+>attack', 'cmd': '!roll 1d20', 'type': 'user', 'attribute': 'private', 'id': 1130268405333753857}}
database = {'_id': ObjectId('65cc143a9cf299679a581f81'), 'id': 1130268405333753857, 'macros': {}}
#

i'm using mongodb

grim vault
pearl lodge
#

when i print it

unreal hemlock
#

are you ok?

pearl lodge
#

is macro = database["macro"] receiving a dict or an object of a dict?

grim vault
pearl lodge
grim vault
#

You didn't provide your new code, so I don't know.

pearl lodge
# 
async def register_macro(
        self, *, prefix: str, args: str, macro_type: str, macro_attr: str, id: int
    ):
        macro_data = {
            prefix: {
                "prefix": prefix,
                "cmd": args,
                "type": macro_type,
                "attribute": macro_attr,
                "id": id,
            }
        }

        print(f"Macro data: {macro_data}")

        database = await self.db["macros"].find_one({"id": id})

        if not database:
            await self.db["macros"].insert_one({"id": id, "macros": dict()})
            database = await self.db["macros"].find_one({"id": id})

        print(f"database = {database}")

        macros = database["macros"]

        if macros[prefix]:
            return "ERROR"

        else:
            macros = macros | macro_data
            print(macros)
            await self.db["macros"].update_one({"id": id}, {"$set": {"macros": macros}})
            print("registered")
            return "SUCESS"
#

when i get into macros = macros | macro_data it seems to lock there

#

and don't print macros

#

after it

grim vault
#

You sure it goes into that part of the if statement? Make a print("ERROR") before the return "ERROR" to be sure.

#

BTW, code like that: print(f"database = {database}") can be written as print(f"{database = }")

pearl lodge
# 
entered
macro_data = {'+>attack': {'prefix': '+>attack', 'cmd': '!roll 1d20', 'type': 'user', 'attribute': 'private', 'id': 1130268405333753857}}
database = {'_id': ObjectId('65cca88b1c0d09dcc2f98e16'), 'id': 1130268405333753857, 'macros': {}}

async def register_macro(
        self, *, prefix: str, args: str, macro_type: str, macro_attr: str, id: int
    ):
        macro_data = {
            prefix: {
                "prefix": prefix,
                "cmd": args,
                "type": macro_type,
                "attribute": macro_attr,
                "id": id,
            }
        }

        print(f"{macro_data = }")

        database = await self.db["macros"].find_one({"id": id})

        if not database:
            await self.db["macros"].insert_one({"id": id, "macros": dict()})
            database = await self.db["macros"].find_one({"id": id})

        print(f"{database = }")

        macros = database["macros"]

        if macros[prefix]:
            print("ERROR")
            return "ERROR"

        else:
            macros = macros | macro_data
            print(f"{macros = }")
            await self.db["macros"].update_one({"id": id}, {"$set": {"macros": macros}})
            print("registered")
            return "SUCESS"
pearl lodge
#

it enters in else statement but when attempt to unite macros with macrodata

#

it stops working

#

i don't know if macros is an object or an actual dict

#

like, probably it's an object of a dict

grim vault
pearl lodge
#

if that's the case

#

macros.update

#

will it rewrite the values inside macros?

#

or will it insert values inside macros?

grim vault
#

Yes, it does update the dictionary itself.

pearl lodge
#

like

grim vault
#

It does update values of existing keys and adds new keys.

pearl lodge
grim vault
#

!e

x = {'a': 'b'}
print(x)

y = {'a': 'upd', 'b': 'new'}
x.update(y)
print(x)
delicate fieldBOT
#

@grim vault :white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | {'a': 'b'}
002 | {'a': 'upd', 'b': 'new'}
pearl lodge
#

oh

#

that is much better

#

!e

x = {'a': 'b'}
print(x)

y = {'c': 'd'}
x.update(y)
print(x)
delicate fieldBOT
#

@pearl lodge :white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | {'a': 'b'}
002 | {'a': 'b', 'c': 'd'}
pearl lodge
#

oh i see

#

so it won't change a hash if gets new values

#

even with macro.update():

entered
macro_data = {'+>attack': {'prefix': '+>attack', 'cmd': '!roll 1d20', 'type': 'user', 'attribute': 'private', 'id': 1130268405333753857}}
database = {'_id': ObjectId('65cca88b1c0d09dcc2f98e16'), 'id': 1130268405333753857, 'macros': {}}
type(macros) = <class 'dict'>
# 
async def register_macro(
        self, *, prefix: str, args: str, macro_type: str, macro_attr: str, id: int
    ):
        macro_data = {
            prefix: {
                "prefix": prefix,
                "cmd": args,
                "type": macro_type,
                "attribute": macro_attr,
                "id": id,
            }
        }

        print(f"{macro_data = }")

        database = await self.db["macros"].find_one({"id": id})

        if not database:
            await self.db["macros"].insert_one({"id": id, "macros": dict()})
            database = await self.db["macros"].find_one({"id": id})

        print(f"{database = }")

        macros = database["macros"]

        print(f"{type(macros) = }")

        if macros[prefix]:
            print("ERROR")
            return "ERROR"

        else:
            macros.update(macro_data)
            print(f"{macros = }")
            await self.db["macros"].update_one({"id": id}, {"$set": {"macros": macros}})
            print("registered")
            return "SUCESS"
pearl lodge
grim vault
#

If macros is empty than macros[prefix] should rise an KeyError exeption?

#

!e

prefix = '+>attack'
database = {'id': 1130268405333753857, 'macros': {}}
macros = database['macros']
if macros[prefix]:
    print("ERROR")
else:
    print("SUCCESS")
delicate fieldBOT
#

@grim vault :x: Your 3.12 eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "/home/main.py", line 4, in <module>
003 |     if macros[prefix]:
004 |        ~~~~~~^^^^^^^^
005 | KeyError: '+>attack'
pearl lodge
#

oh i see

#

how it would be the right way to check if there is a macro with said prefix?

grim vault
#

if prefix in macros: will check if the value of the variable prefix is a key in the dictionary named macros.

pearl lodge
warm igloo
warm igloo
#

Alright so I just noticed something strange. I saved both dataframes to a CSV file to examine the IDs, and I get said IDs I'm examining when running it through the Dash App, but it looks like the program is for some reason not adding those specific rows in question to df_2 when they should be. Keep in mind this is all of the functions from earlier are being called from the Callback function in my Dash App for every time the button is clicked.

storm mauve
#

to whoever it was that deleted their message: it would always return 0 since the order of the arguments passed to coalesce was wrong

sour latch
regal cairn
#

how can I read the whole string in pandas?

Screenshot_2024-02-14_at_8.01.20_PM.png
#

even if I do .to_string() it won't show

#

so that I'm not crazy, I just checked in the sqlite db – indeed, there's smth after the dots

hexed estuary
storm mauve
#

you can also use .item(), which checks that it contains exactly one element before returning

hexed estuary
#

a nice, I forgot how that was called

stoic stream
austere bronze
#

Hey if anyone here is proficient in JSON data merging please take a look at my post in the help system channel!

noble herald
#

can someone tell me why mysql is not workin in my project:

CREATE USER IF NOT EXISTS 'RO'@'%' IDENTIFIED WITH caching_sha2_password BY 'pass';
-- comment removed
GRANT SHOW DATABASES, SELECT, SHOW VIEW ON *.* TO 'RO'@'%';

CREATE USER IF NOT EXISTS 'RW'@'%' IDENTIFIED WITH caching_sha2_password BY 'pass';
GRANT UPDATE, INSERT, SHOW DATABASES, SELECT, SHOW VIEW ON *.* TO 'RW'@'%';

CREATE USER IF NOT EXISTS 'upcheck'@'localhost' IDENTIFIED WITH caching_sha2_password;```

also, everything is in a nonexposed docker container, so dont worry abt hardcoded passwords in the startup.sql


it just gives me 
```ERROR 1064 (42000) at line 9: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS' at line 8```

server:
```MySQL Server 8.3.0-1.el8 started```


also anyone know why
```root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.``` is appearing? I have ```MYSQL_ROOT_PASSWORD_FILE: /run/secrets/db_root_password``` in my compose, and the secret being passed....

The upcheck is there for a 
```    healthcheck:
      test: ["CMD", "mysqladmin", "-u", "upcheck" ,"ping", "-h", "localhost"]
      timeout: 20s
      retries: 10```

so my python dose not start before its healthy

please help
coral wasp
dawn dirge
strong plover
#

x

#

c

grizzled vector
#

whats the most used database

#

and what is the difference between mysql and postgre

fading patrol
fading patrol
fading patrol
stoic stream
#

Among other things Postgres has a brilliant Spatial add on, PostGIS. I'm not sure MySQL has this sort of functionality, it is a more lightweight database I think.

#

And you can write Postgres UDFs in Python, which is pretty cool. Again, I don't know much about MySQL to compare

grizzled vector
fast belfry
#

Does anyone here have knowledge about Firebase in Python? I need to ask a question xd

#

well, help

fading patrol
fast belfry
#

Not that help, just an advice of how to proceed

naive arch
#

Im tryna acsess this tuple ```
data = await cur.execute(f"SELECT credits FROM gen WHERE id = {ctx.author.id};")
msg = await data.fetchall()
await ctx.reply(msg[1])

it just returns with (1,)
fringe cave
#

also did you check what's actually stored for the user?

naive arch
naive arch
fringe cave
#

how's the insert part to know which value credits has?

fringe cave
naive arch
sturdy lily
#

chatgpt gave me this Postgres function and trigger, but it's not updating the value of timestamp_field. can someone help me what's the issue? though i am getting the logs from notice inside the if check.

CREATE OR REPLACE FUNCTION reset_timestamp_to_null()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger reset_timestamp_to_null() fired';
    -- Log the value of timestamp_field
    RAISE NOTICE 'Current value of timestamp_field: %', NEW.timestamp_field;
    
    IF NEW.timestamp_field < CURRENT_TIMESTAMP - INTERVAL '5 minutes' THEN
        RAISE NOTICE 'Resetting timestamp_field to NULL';
        NEW.timestamp_field := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reset_timestamp_to_null_trigger
BEFORE UPDATE ON "my_db_schema".table
FOR EACH ROW
EXECUTE FUNCTION reset_timestamp_to_null();
paper flower
delicate fieldBOT
#
Rules

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

paper flower
#

Though, not directly related, I doubt anybody would help you if you use chatgpt

#

What are you trying to do?

sturdy lily
#

I have modified the script it not complete copy and paste

sturdy lily
paper flower
paper flower
#

Can you create a reproduction in some sort of online environment? There are some sites that let you to do that, it would be easier to help that way

sturdy lily
#

Yeah even the log event inside the if check is showing the logs

sturdy lily
paper flower
sturdy lily
#

Installing postggress in docker is ye easiest way i guess

paper flower
#

I'll try to reproduce your issue locally

#

I'm having the same issue 😅

hollow oar
paper flower
#

Settings field to now() seems to work

#

Hm, no, it worked for me too 🤔

# 
-- create table users (
--     id serial primary key,
--     timestamp_field timestamp with time zone
-- )

CREATE OR REPLACE FUNCTION reset_timestamp_to_null()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger reset_timestamp_to_null() fired';
    -- Log the value of timestamp_field
    RAISE NOTICE 'Current value of timestamp_field: %', NEW.timestamp_field;

    IF NEW.timestamp_field < CURRENT_TIMESTAMP - INTERVAL '5 minutes' THEN
        RAISE NOTICE 'Resetting timestamp_field to NULL';
        NEW.timestamp_field := null;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

drop trigger if exists reset_timestamp_to_null_trigger on "public".users;
CREATE TRIGGER reset_timestamp_to_null_trigger
BEFORE UPDATE ON "public".users
FOR EACH ROW
EXECUTE FUNCTION reset_timestamp_to_null();

truncate table users;
insert into users (timestamp_field) values (now() - interval '10 minutes');
update users set id=1 where 1=1;
sturdy lily
#

opps 😢 , timestamp was wrong

sturdy lily
#

i am user prisma and passing new Date() in this feild? is this valid?

#

i guess i should take a break now, and retry after some time, i might be making some stupid mistake

hollow oar
#

post your code and the messages you see from the trigger, can't tell what's wrong from here without more information

craggy swallow
#

anyone know how to configure json(b) parser for asyncpg pool?
Right now I'm doing the following each time I acquire a connection from the pool:

await con.set_type_codec("jsonb", encoder=json.dumps, decoder=json.loads, schema="pg_catalog")

Can't figure out how to configure the type codec for the entire pool

craggy swallow
#

if anyone's interested, essentially u make a coroutine that accepts a connection, set the type codec there, and pass ur coroutine as the init argument when creating the pool:

async def set_jsonb_codec(con) -> None:
    await con.set_type_codec(
        "jsonb",
        encoder=json.dumps,
        decoder=json.loads,
        schema="pg_catalog",
    )

await asyncpg.create_pool(
    *self.args,
    **self.kwargs,
    init=set_jsonb_codec,
)
balmy monolith
#

hey guys, I made a webapp to generate fake data using your relational database's DDL script. I made it to prevent myself from manually entering dummy data for my SQL tables during development. Please do try it and tell me if it makes your life a bit easier :)
https://alles-tools.com/fakemydb
(I hope this doesn't break any rules, feel free to delete if it does so)

west hill
sturdy lily
# hollow oar post your code and the messages you see from the trigger, can't tell what's wron... 
CREATE TABLE "test-db".users (
 "id" TEXT NOT NULL,
 "amount" INTEGER DEFAULT 0,
 "dailyTransactionLimit" INTEGER NOT NULL DEFAULT 0,
 "firstTransactionTimestamp" TIMESTAMP(3),

 CONSTRAINT "users_pkay" PRIMARY KEY ("id")
);

INSERT INTO "test-db".users ("id", "dailyTransactionLimit", "firstTransactionTimestamp") VALUES (1, 1, NOW());
INSERT INTO "test-db".users ("id", "dailyTransactionLimit", "firstTransactionTimestamp") VALUES (2, 2, NOW() - INTERVAL '10 minutes');

CREATE OR REPLACE FUNCTION "test-db".reset_transaction_limit()
RETURNS TRIGGER AS $$
BEGIN
   RAISE NOTICE 'Trigger reset_timestamp() fired';
   -- Log the value of timestamp_field
   RAISE NOTICE 'Current value of firstTransactionTimestamp: %', NEW."firstTransactionTimestamp";
   IF NEW."firstTransactionTimestamp" < CURRENT_TIMESTAMP - INTERVAL '5 minutes' THEN
       RAISE NOTICE 'Resetting firstTransactionTimestamp to NULL and dailyTransactionLimit to 0';
       NEW."firstTransactionTimestamp" := NULL;
       NEW."dailyTransactionLimit" := 0;
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE trigger reset_transaction_limit
AFTER UPDATE ON "test-db".users
FOR EACH ROW EXECUTE FUNCTION "test-db".reset_transaction_limit();

SELECT * FROM "test-db".users;

UPDATE "test-db".users SET amount = 5 WHERE id = '2';
image.png
balmy monolith
grim vault
sturdy lily
sturdy lily
delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied timeout to @calm trail until <t:1708199160:f> (10 minutes) (reason: duplicates spam - sent 4 duplicate messages).

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

dawn shard
#

I would like to store a traditional system for categories in a database (machinery/blenders/immersion) - how do I do that - the obvious solution is for each category to have a 0..1 FK to itself - parent, but that seems inefficient and inexpressible in most usual ORMs. Is there a better way?

coral wasp
dawn shard
# coral wasp Explain more? Sometimes just denormalizing is just easier/better (one flat table...

I am making system, for this purpose lets call it an eshop. I want to categorise each item (for example food->fruit->apple->jonagold), and I need that in the database. of course, I want to be able to display these breadcrumbs on each page, so I don't want to do the lazy solution of 1:[0-1] and 4 sql statements. I actually didn't check if e.g. django ORM will do better here than just 4 separate lookups, but still, it is a linked list, which is probably not a great data structure to use.

Previously,I used varchar fields and wrapped it in an API that made it appear hierarchal, but that was a bit too janky.

coral wasp
#

?

dawn shard
#

dynamic depth

coral wasp
#

Yah, so in that case, you need a parent-child lookup table, which would be 1:many

#

Recursive (arbitrary depth) hierarchies are a bit annoying in sql.

#

I sometimes resort to encoding the hierarchy in a list-like identifier (ie; a.b.c) because it makes certain queries easier.

#

But that’s not 1:[0|1] anyway, it’s one to many single each food has multiple children and so on

dawn shard
#

ah, fair, I was wrong about that

#

yeah, that's sort of my issue, the most common query is "find the entire breadcrumbs to the current thing", so traversing even a short linked list seems like a bad decision

coral wasp
dusk stream
#

Hi

#

I have a great project and I don't know who is interested

dawn shard
#

Yeah, IK about recursive queries, it just seems like a hilariously complex thing to invoke for such an unremarkable feature

dusk stream
#

I have a great project and I don't know who is interested

coral wasp
coral wasp
dawn shard
#

yeah, I'll probably just do a list

dusk stream
#

Sorry

#

How can a shopping platform quickly grab items? Manual clicking cannot complete the operation!

candid lagoon
#

The sqlite3 shell would really be perfect if it weren't for it displaying things as tuples rather than tables

torn sphinx
#

I am learning Python well enough to do automated testing. When you programming in the real world do you ever retrieve data from a List or Dictionary? Or will it usually be from a database? My guess is database.

coral wasp
torn sphinx
coral wasp
coral wasp
torn sphinx
coral wasp
torn sphinx
coral wasp
torn sphinx
weary wadi
#

Hello world!

twilit marsh
#

i made a system that for the most part allows one to provide some python data which is automatically converted to some database format and is reconverted to the python format upon loading.
Some people said that was not a good idea but it is working ok for me currently, the alternative approach would be to write custom code to convert to and from some long term data format for each unique data structure. often called serialisation and deserialisation

torn sphinx
torn sphinx
versed thunder
#

im assuming im in the right place since i have a question about SQlite

#

can i pass multiple values to be filled into 1 column, i know you can do it with 2 columns with a list of tuples but if i just wanted to do 1 column how would i go about that

harsh pulsar
versed thunder
#

ah, because everytime i tried to run a list without the () separating each value it said it couldnt bind 5 values

#

i didnt know a tuple could be single valued

#

thats interesting

harsh pulsar
versed thunder
#

yea

#

lemme look, i mightve taken our the many bit

harsh pulsar
#

executemany only accepts one parameter tuple/list

versed thunder
#

ah

#

i took out the many

harsh pulsar
#

conn.execute('insert into xyz values (?, ?, ?)', (x, y, z)) or conn.executemany('insert into xyz values (?, ?, ?)', [(x, y, z)])

#

of course you should always double check w/ the docs if you aren't sure what a function accepts as input

versed thunder
#

this is what those lines look like rn

# 
cursor.executemany('''
    INSERT INTO python_programming(id) VALUES(?)''',students_ids)
db.commit()
#

im not sure, since the doc i was given for this task, some of the stuff doesnt work the way they have it

harsh pulsar
#

!d sqlite3

delicate fieldBOT
#
sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

This document includes four main sections:

candid lagoon
#

How would one check for all the available tables in a file opened with the py -m sqlite3 prompt?

grim vault
candid lagoon
#

Works great, thanks

image.png
#

I wish there was something like .tables though

#

Much like mysql has show tables;

grim vault
#

pragma table_list; might works, but is more info.

#

If you install the sqlite3 tool from their homepage the shell dos have the .tables command.

candid lagoon
#

Hm, does that work with the Python sqlite3?

grim vault
#

It is its own tool, no python required.

candid lagoon
#

Well, pragma table_list; is good enough regardless

candid lagoon
grim vault
#

It does work with databases which are created with the sqlite3 module of python, if that is what you mean.

candid lagoon
#

Darn, I guess that's just objectively better

#

I'll stick with the py -m sqlite3 prompt until I need something better though, I like my terminal

manic ibex
#

Im using sqlachemy and Protocols, but I get this error "Mapped[str]" is incompatible with "str". How can I coerce Mapped[str] to str

dusk stream
#

Does anyone know how to use app crawlers?

#

Does anyone know how to use app crawlers?

wise wind
#

Hey, I'm using the sqlalchemy async engine and I'm getting this error: sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s) and I'm pretty sure this line is causing the error: await db.refresh(new_server) Do ya'll know any solutions to the problem?

west hill
pearl lodge
#

okay my database is not returning me any traceback
and neither is registering characters
what did i did wrong?
like, it have nothing registered:

image.png
#

but i still receive this:

image.png
naive arch
wispy pawn
#

I have a question regarding FastAPI. When I do a commit, an error can happen (generally). How do I handle this. Even at the response an error can habben. If an error happens i should do an rollback. Whats the best way to do this? Thanks 🙂

whole otter
#

anyone with experience using painless in elastic?

#

im having an issue with a working script that performs as it should in painless lab but it fails to add a field inside a pipeline

fading patrol
frank cloak
#

Trying to migrate a sqlite database to psql for use in asyncpg

fading patrol
frank cloak
#

and I modifited my sqlite dump from integer to bigint

#

and if I do

SELECT Service from watched_videos; SELECT Service from to_watch; SELECT Service from music;

ERROR: column "service" does not exist
LINE 1: SELECT SERVICE FROM MUSIC;
^
HINT: Perhaps you meant to reference the column "music.Service".

#

I don't get it

#

psql musicfinder < exported.sql
BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 848
INSERT 0 6234
INSERT 0 188
COMMIT

#

should I just use /copy csv?

frank cloak
#

I think I fixed it

#

with a few moving stuff around

versed thunder
#

i have an issue where it appears my database and python file dont want to interact with each other in my DB browser for SQlite, as far as im aware my code is fun, it runs without issue but within the DB browser its like it doesnt exist despite me opening the same database file that the python file is working with

#

maybe im just being stupid when im trying to connect the 2 or my db browser is just broken im unsure, im new to this and havent been taught how to do this but i need it to complete my task

wise goblet
#

by default drivers do not "save" results of their executions until u ran "commit" command

#

that is fixable by using commit command, or turning on autocommit=True in connecting settings

#

p.s. if u don't turn it on by default, u are left with option to rollback all your changes in case of encoutnering problems at any step

versed thunder
#

i have commit on all my actions / changes ive done to that data base and have used rollback just incase something arises

versed thunder
wise goblet
versed thunder
#

yes sir e'-e'

wise goblet
#

Alterantive option, may be u opened in python sqlite version in memory 😅

versed thunder
#

hrmm

wise goblet
#

but i would bet on commit problem

versed thunder
#

how would i know e'-e'

#

all my commits are in place and ive double checked it

wise goblet
versed thunder
#

DB browser just hates me

#

fair enough

#

okay

#

smol problem

#

there is a field repeating itself for whatever reason

# 
import sqlite3

db = sqlite3.connect('student_db.db')

cursor = db.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS python_programming (
        id INTEGER PRIMARY KEY,
        name VARCHAR(25),
        grade INTEGER,
        UNIQUE(id))  -- Enforce unique constraint on id
    ''')
db.commit()

students_data = [
    (55, "Carl Davis", 61),
    (66, "Deniss Fredrickson", 88),
    (77, "Jane Richards", 78),
    (12, "Peyton Sawyer", 45),
    (2, "Lucas Brooke", 99)
]

try:
    cursor.executemany('''
        INSERT OR REPLACE INTO python_programming(id, name, grade) VALUES(?, ?, ?)''', students_data)
    db.commit()
    print("Data inserted successfully.")
except sqlite3.Error as e:
    print("Error:", e)
    db.rollback()

cursor.execute('''
    UPDATE python_programming SET grade = 65 WHERE name = 'Carl Davis'
    ''')
db.commit()

cursor.execute('''
    DELETE FROM python_programming WHERE name = 'Deniss Fredrickson'
    ''')
db.commit()

cursor.execute('''
    UPDATE python_programming SET grade = 80 WHERE id > 55
    ''')
db.commit()

cursor.execute('SELECT * FROM python_programming')
rows = cursor.fetchall()
print("\nAll records in the table:")
for row in rows:
    print(row)
    if row == (78, 'Carl Davis', 80):
        break

cursor.close()
db.close()
#

theres a double up of me laddy carl over there

#

idky he does but its weird

#

hold on

#

easy fix

#

done

#

i just mixed some things up on the if statements condition

#

but my problem still exists

versed thunder
#

:(

#

@wise goblet i showed you my code the least you could do is take me out to dinner e'-'e

wise goblet
#

huh 🤔

wise goblet
#

i opened your code results through sqlite browser and can see all stuff correctly

#

i would assume u have smth wrong with your browser
(or may be smth with filesystem / fs permissions)

versed thunder
#

:(

#

ty

#

it narrows it down

#

ill get my dad to help when he gets home

#

just had to make sure it wasnt the code itself

wise goblet
west hill
#

darkwind mind helping me with a db review its for one of my college projects 👀

wise goblet
# versed thunder it narrows it down

i made a check with using DB Browser for sqlite version 3.12.99 (May 24 2021)
at Linux Kubuntu 22.04 LTS
at a path /home/naa/repos/temp/experiments
with Python 3.10.12

wise goblet
west hill
wise goblet
#

😅 too much to review.

west hill
#

project objective: include every sql feature available even if its redundant -by professor

coral wasp
#

Why arrays for artists?

west hill
versed thunder
coral wasp
#

Or, remove artists from albums, since it’s derivable from tracks

#

Just giving you ideas, there’s ma y ways to solve a problem

west hill
#

mhm

#

what about in schema 2 i think thats close to what u said i removed the array and created external relations to enforce foreign keys thats more of a many many relation ig

#

reason i put artist in album is convenience of query and displaying in ui

#

saves an extra aggregate query

coral wasp
west hill
#

mhm I'll look into it

west hill
#

or atleast the base

versed thunder
#

so i can finally see my database stuff in my DB browser

#

but 1 small issue

#

theres meant to be 4 records

#

but theres like closer to 90

#

idk why it keeps repeating the same things into it

pliant hound
#

Hi! Im having a weird issue and I am completely lost on how to solve it.
My database has multiple columns, one of them being 'password'. It says in the data structure that this value is stored as TEXT. I hash the password using a very simple algorithm and it changes 'admin' to '831'. 831 is stored as the password for admin. In my code, when I put 'admin' as the username and 'admin' as the password (correct details), it tells me that the account is not found and the SQL statement returns none. I have tried the SQL query in the actual database management program and it works perfectly fine (SELECT * FROM user_information WHERE username="admin" AND password="831"). However, when my code runs, it doesnt work at all and I dont know why. Here are the images in order (Database, login query that is broken, hash algorithm, output).

image.png image.png image.png image.png
#

ive tried converting 'hashed_password' to a string object using str but the same thing happens

coral wasp
#

What datatype is password in the db?

#

I assuming db field is an int, so you may need to convert to int. To test, just change hashed_password in your parameters to 831

#

No quotes

coral wasp
pliant hound
grim vault
# pliant hound

!e it should work

import sqlite3

def hash_password(password):
    hash_key = 7
    return sum(ord(char) * hash_key % 256 for char in password)

conn = sqlite3.connect(":memory:")
conn.execute("create table user_information(username text, password text)")
for user in ("admin", "teacher", "student"):
    conn.execute("insert into user_information values(?, ?)", (user, hash_password(user)))

conn.commit()

for user in conn.execute("select * from user_information"):
    print(user)

username = "admin"
hashed_password = hash_password(username)

query = "select * from user_information where username=? and password=?"
print(f"SQL: {query = }; Params: {(username, hashed_password) = }")

curs = conn.cursor()
curs.execute(query, (username, hashed_password))
login_detail = curs.fetchone()
print(f"{login_detail = }")
delicate fieldBOT
#

@grim vault :white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | ('admin', '831')
002 | ('teacher', '1028')
003 | ('student', '561')
004 | SQL: query = 'select * from user_information where username=? and password=?'; Params: (username, hashed_password) = ('admin', 831)
005 | login_detail = ('admin', '831')
grim vault
#

You sure you are using the same database file?

wispy pawn
#

Hello,
I need to write an update with sqlalchemy and fastapi. My problem is, that i get n (max 5) key value pairs from the frontend. I have also 5 tables in my db, like author, title, genre, album etc. How would I write the update sql because i dont know what tables to update. Maybe I get key value pairs for author and title, or only album etc. Whats the best practice for that ?

Thanks a lot 🙂

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied timeout to @stiff stirrup until <t:1708459585:f> (10 minutes) (reason: duplicates spam - sent 4 duplicate messages).

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

topaz aurora
#

Does a read replica db impact transactions on the primary server on setup?

coral wasp
topaz aurora
pliant hound
#

it used to work too

#

idk what changed

coral wasp
#

Simplify: select * from table, then select * from table where username = ‘admin’, etc

#

Narrow it down

pliant hound
#

okok

pliant hound
coral wasp
#

What about select *?

pliant hound
#

database looks like this:

image.png
coral wasp
#

No condition

pliant hound
coral wasp
#

Then you’re looking at different databases. Or your code to display is wrong.

pliant hound
#

i dont understand because its worked before. and now it doesnt when i implemented the has

coral wasp
#

But I think you’re connecting to a different db

pliant hound
#

but now its broke

#

this is the connection:

image.png
#

this is the file:

image.png
coral wasp
#

What happens when you run that code?

pliant hound
#

well previously it would just not run it at all, skip to the next part of the code. but now it runs it, but it returns none every time

coral wasp
#

Then you’re not running it

#

Because that code should throw an exception

pliant hound
#

but like it outputs though

coral wasp
#

show what it outputs

#

That wasn’t the last version of code you showed

pliant hound
#

okok hold on then

coral wasp
#

And, that version should be ‘831’, not 831: the column type is text not int

pliant hound
pliant hound
#

wait

#

its running the code in the comments???

#

wtf

#

that should be ignored what

#

maybe its not saving actually

#

ffs

#

its that i think

#

nope

#

this code outputs the EXACT same as the last image, i saved everything and it still broke

image.png
#

@coral wasp new update, this is what happens with no argument

image.png
coral wasp
#

Yes, you can’t pass parameters with a query that takes 0 parameters

#

Remove the parameters

pliant hound
#

i need them though

#

like it checks if the account exists

#

using the login entries

#

oh shit nvm i see wym

coral wasp
#

🙂 just debug, then worry about making it correct

pliant hound
#

still none

coral wasp
#

Ok, then you’re connecting to a different db file, I think

pliant hound
#

okok let me try specifying the exact path

#

WORKS

pliant hound
#

idk why, path didnt change but once i specified it worked

coral wasp
pliant hound
#

yeah lmao code hurts, thank you! Now i gotta wrap my head around 1,2 and 3NF and how to implement it in databases😭

storm mauve
#

that is what you get for using enums 🤷

#

try using BankEnum.SANTANDER.name, BankEnum.SANTANDER.value or str(BankEnum.SANTANDER) depending on what you need to do

#

also I hope that this CPF is fake/mocked?

tough bough
#

It is

tough bough
#

Do you know if there is another way to solve this without usin ENUM then?

storm mauve
#

!e ```py
from enum import Enum, StrEnum, auto
class Test(Enum):
FOO = auto()
class TestString(StrEnum):
BAR = auto()

for thing in (Test.FOO, TestString.BAR):
print(thing.name)
print(thing.value)
print(str(thing))

delicate fieldBOT
#

@storm mauve :white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | FOO
002 | 1
003 | Test.FOO
004 | BAR
005 | bar
006 | bar
storm mauve
#

I actually still haven't used SQLAlchemy at all so idk which purpose enums serve for what you are doing, but a lot of people just use normal classes kinda like namespaces - e.g. py class Test: FOO = "FOO" BAR = "BAR" in which case Test.FOO just gives you the string "FOO" with no magic involved

tough bough
#

That's what I am doing. But somehow, although string works perfectly in the database

#

When I try get it from the database to use in the code, it work as I showed in the screenshot

naive arch
# 
CREATE TABLE IF NOT EXISTS linkvertise(id INTEGER, auth TEXT, credits INTEGER)
``` Somewhy when i sumbit credits and auth dosnt exist it works but then it dosnt.
topaz aurora
#

Hi, I want to get familiar with big data and databases fast. Any resources you guys would recommend?

stoic stream
topaz aurora
#

Yes

stoic stream
#

for relational databases you want to learn SQL. Some Big Data systems can be queried in python with dataframes, e.g. PySpark

topaz aurora
stoic stream
#

do you have a particular database or big data platform in mind?

topaz aurora
#

I'm preparing for an AWS event in march

stoic stream
#

I did a Coursera specialisation "From data to Insights with Google Cloud", you'd want to find the equivalent course for AWS

#

Are you using Redshift?

topaz aurora
#

No, idk what Redshift is

stoic stream
#

Redshift is the data warehouse on AWS

#

SQL but works with big data

#

are you using any conventional databases like PostgreSQL?