databases

[delicate oxide]

Trying to use sqlite3 as a database for storing items to an inventory how would i add that kind of capacity to the table is it just the "?" placeholder ```py
sql_create_table = """
CREATE TABLE userTable (
userID TEXT PRIMARY KEY,
class TEXT NOT NULL,
transfigurationLevel INTEGER,
charmsLevel INTEGER,
potionsLevel INTEGER,
herbologyLevel INTEGER,
dadaLevel INTEGER,
hp INTEGER,
magicPoints INTEGER,
level INTEGER,
exp INTEGER,
inventory ?,
galleons INTEGER
)
"""

cant find any decent videos to help learn about sqlite in a game setting if you know any id appreciate ty for reading

[torn sphinx]

@boreal kernel So I will do research before I start developing but I was hoping you could suggest a map / guideline on where to start with this so I'm not too lost while doing research.

I need front end users to insert their weekly targets into a DB then use that data to be displayed on Dashboard apps like P-BI for example.

I was thinking this process should look something like this

Flask/django --> Rest Api --> Database --> Power Bi

[boreal kernel]

So the first thing to consider is, do you need separate UI and API servers? For a simple app, you could probably get away with having everything in the same process

[boreal kernel]

Generally you'd separate that out into separate tables; one table that maps one inventoryID to one userID (presuming each user has exactly one inventory), and a second one that maps one inventoryID to many itemIDs

[torn sphinx]

its a small app for now

from what I understand I need to develop a front end user interface that accepts the data

this can be done on flask or cURL?

[boreal kernel]

Flask will probably be the easiest

[torn sphinx]

what is the difference between flaks and cURL

[boreal kernel]

Flask is a web framework for python, it can be used to build sites, APIs, and webapps. cURL is a utility for downloading webpages from the command line

[delicate oxide]

Thanks so you mean 3 databases essentially have the table accept the userID and then one that stores item ids to the user id and then one that stores the strings of the item ids?

[boreal kernel]

Three tables within the same database, or 4 if you don't already have an item table.

Table 1: user table (table for your basic user data with each row having a unique userID)
Table 2: inventory table (table where each row has a unique inventoryID and a related userID)
Table 3: item table (table where each row has a unique itemID and associated item data (name, price, etc.))
Table 4: inventoryToItem table (table where each row maps one inventoryID to many itemIDs)

[boreal kernel]

@torn sphinx Check out Flask's quickstart guide,

https://flask.palletsprojects.com/en/2.3.x/quickstart/

here's a really rough idea of how you could lay things out (this is psuedocode)

import backend

# frontend.py
@app.route('/', methods=["GET", "POST"])
# This endpoint is the root of your website. It can receive GET and POST requests.
# When you visit the page in a browser, that's a GET request
# When you submit a form on this page, that's a POST request
def homepage():
  # We use Flask's universal request object to learn what kind of request we're responding to
  if request.method == "GET":
    # When the user visits the page, we send them the form to put their targe data in
    return "<form><input>.....</input></form>"
  if request.method == "POST":
    # When they submit that form, we read the data and handle it here
    form_data = request.form
    username = form_data['username']
    target_text = form_data['target']
    was_successful = backend.store_target(username, target_text)
    
    # Now we send some more HTML back to the user to tell them what happened:
    if was_successful:
      return "<b>Your input has been accepted</b>"
    else:
      return "Something went wrong :("

#backend.py
# This is where we handle the actual logic and interact with our database
def store_target(username: str, target_text: str)->bool:
  # First, you'll store the data in your database
  my_database.store_target(TargetModel(username=username, target=target))
  
  # Then, you can send that data to powerBI, perhaps through their API
  # Though you can also do this at some other time if you like
  powerbi_api.store_target(username, target)

[torn sphinx]

this is very nice thank you

[boreal kernel]

Obviously there's a lot missing here, but flask's docs can tell you how to do things like user login and such (just be careful about security)

[torn sphinx]

Oh okay so I will still need cURL with flaks anywya

[boreal kernel]

you shouldn't need cURL I don't think, just somewhere to host your site

[torn sphinx]

if I'm using flask I don't need rest api

because flask has api right?

[boreal kernel]

Well, you kind of are writing an API, though not in the way you usually think of it. backend.py is basically your API. That is, it handles all the 'business logic" of your application. You could put this on a separate server and use requests (instead of cURL) to access it, but an app this size probably doesn't need that. Usually they're separated so that it's easier to scale one or the other, and more people can work on it at the same time without conflict

Generally, you want to handle as little logic as possible in those flask functions, doing only what you need to do to get user input ready for your backend to handle

[torn sphinx]

ok thank you so much

now I understand what I need to do.

[boreal kernel]

Good luck! This stuff can take some time to get your head around, but it'll click at some point

[torn sphinx]

what subject is all of that falls under?

like what area of CS

is it web development?

[cinder plume]

yep you can call it backend or generally web dev

[torn sphinx]

Hi everyone, I'm running into an error while trying to use the update_password and delete_password functions below

error is sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type. (while trying the update_password)

this is my first time using sql in python so any help would be appreciated

[paper flower]

What type are you trying to use with your query?

You're probably trying to write a python class for example instead of str

[torn sphinx]

sorry, code is in #1136448588470308864

[pseudo steeple]

Hey there. Im new to programming. Is anyone here, that has knowledge about SQL-Injection? Wanted to ask a few small things in dm ^^ (pymongo)

[paper flower]

mongo isn't sql?

Also just ask your questions here

[eager tide]

I got this 8000 error for mongodb and express.js
I checked my mongodb url, its fine and its in environment variable. so its in string format. why its coming ?

{
    "success": false,
    "message": "error in registration",
    "error": {
        "ok": 0,
        "code": 8000,
        "codeName": "AtlasError"
    }
}

[torn sphinx]

The "8000 error" you mentioned is not a standard error code or error message associated with MongoDB or Express.js. It is possible that the error you encountered is specific to your application or environment.

[rapid folio]

Hi folks,
Can someone pls tell me whats the issue with this line
mongoose.connect("mongodb://127.0.0.1:27017/wikiDB?replicaSet=rs0&directConnection=true",{useNewUrlParser:true, useUnifiedTopology: true});
I only have one instance created in replicaset
and its giving me error like this
{
"error": {
"reason": {
"type": "ReplicaSetNoPrimary",
"servers": {},
"stale": false,
"compatible": true,
"heartbeatFrequencyMS": 10000,
"localThresholdMS": 15,
"setName": "rs0",
"maxElectionId": "7fffffff0000000000000002",
"maxSetVersion": 1,
"commonWireVersion": 0,
"logicalSessionTimeoutMinutes": null
}
}
}

[eager tide]

data in payload

{
    "success": false,
    "message": "error in registration",
    "error": {
        "errors": {
            "answer": {
                "name": "ValidatorError",
                "message": "Path `answer` is required.",
                "properties": {
                    "message": "Path `answer` is required.",
                    "type": "required",
                    "path": "answer"
                },
                "kind": "required",
                "path": "answer"
            }
        },
        "_message": "users validation failed",
        "name": "ValidationError",
        "message": "users validation failed: answer: Path `answer` is required."
    }
}

[rapid folio]

Anyone?

[eager tide]

My mistake was, database created but access was not granted to anyone, include me that's why error was atlas: 8000 and adding one field answer in model but not use in req.body

issue resolved

[wicked kiln]

I wrote some code last week that generates a rather long list of Q parameters for exclude() . This is django btw.

from django.db.models import Q

def get_bad_words_query():

    BAD_WORDS = [
        'bad', 'b a d',
        'words', 'w0rd', 'w o r d'
    ]

    def generate_permutations(word):
        if not word:
            return ['']
        
        first_char = word[0]
        rest_chars = word[1:]
        
        permutations = []
        for p in generate_permutations(rest_chars):
            permutations.append(first_char.lower() + p)
            permutations.append(first_char.upper() + p)

        return permutations
        
    bad_words = [
        p for word in BAD_WORDS 
        for p in generate_permutations(word)
    ]

    bad_words_in_title = Q()
    bad_words_in_descrip = Q()

    for word in bad_words:
        bad_words_in_title |= Q(title__icontains=word)
        bad_words_in_descrip |= Q(description__icontains=word)

    return bad_words_in_title | bad_words_in_descrip


# Then I use this like this in queries
things = Thing.objects.filter(x=x).exclude(get_bad_words_query())

While there isn't a noticable performance hit when its just me using it on localhost, it will eventually go live. I also have to keep the number permutations under 1000 or it will error out.

I'm thinking about taking this out, since I suspect is highly inefficient for a solution that doesn't even fully solve the problem it sets out to solve. Thoughts?

[craggy swallow]

anyone know of a vscode extension that formats SQL contained in triple quotes (or automatically detects SQL, would be even better) in python code? Or just something that could format selected SQL text?

[vital vale]

im trying to make a custom user model using AbstractUser to add a few cols i need to the model, but some reason after doing migrations and no errors and i do see the migrations in terminal happening, i open db.sqlite3 and i dont see the table there, any clue why? https://codesandbox.io/p/sandbox/condescending-archimedes-h7lx22?file=%2Fsettings.py%3A166%2C1 thanks alot in advance! 🙂

[viral furnace]

I need a help with a database deadlock issue.

I am working with a postgres database and in the logs (remote) i see logs corresponding to a DB deadlock. but i am trying to recreate the same kind of deadlock using python locally.

These is the error that i see in the remote db logs

2023-08-63 09:09:31.285 CEST [11245] DETAIL: Process 11245 waits for Sharelock on transaction 117323835; blocked by process 9806.
Process 9806 waits for Sharelock on transaction 117323839; blocked by process 11245.
Process 11245: INSERT INTO input value quarter (id, input_uuid, read time, value, interpolated) VALUES ((SELECT nextval('hibernate sequence')), SI, $2, $3, $4 ) ON CONFLICT(input_uuid, read tine) DO UPDATE SET value = $5, interpolated = $6
Process 9896: INSERT INTO input value quarter (id, input uuid, read time, value, interpolated) VALUES ((SELECT nextval('hibernate sequence’), $1, 52, $3, $4 ) ON CONFLICT(input uuid, read time) DO UPDATE SET value = $5, interpolated = $6
2023-08-63 09:09:31,285 CEST [11245] HINT: See server log for query details.
2023-08-63 09:09:31,285 CEST [11245] CONTEXT: while inserting index tuple (74930,138) in relation *_hyper 1 276_chunk"

Do you know what i can do to recreate this same deadlock using python. (or any other tools) ?
I need the query causing the deadlock to be an INSERT query
ex: INSERT INTO input value quarter (id, input_uuid, read time, value, interpolated) VALUES ((SELECT nextval('hibernate sequence')), SI, $2, $3, $4 ) ON CONFLICT(input_uuid, read tine) DO UPDATE SET value = $5, interpolated = $6

[low flower]

Hello

does anyone know about why I am having issues between deleting duplicates over different dataframes one from excel and one from a list dynamically created through a script. It appears the index columns of the datasets are in different formats(scientific vs standard notation) and upon trying to delete the duplicates python doesn't recognize them.

I am able to get rid of the issue if I first change format of index cells as numbers, or I save the appended dataframes into the excel and subseqently read and remove duplicates

Should I switch to csv, use writer from open pyxl instead of pandas

I tried this but this to change format of scientific notation index but doesnt work df.style.format({'index': '{:.0f}'})

[unkempt prism]

I am able to get rid of the issue if I first change format of index cells as numbers,

This seems like a good solution.

Should I switch to csv,

CSV is just a specific type of text file so exporting to csv is format agnostic and whatever you read your text file / csv file as will parse the data as it sees fit. You should have a look at the output csv in a text editor to see.

[low flower]

Yeah i fixed it just put it as a column instead of index, defined column as numeric and it worked

[unkempt prism]

Can you have a look at the query doing the blocking

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE pid = 9806;

[limber locust]

2023-08-06T12:34:09.493028

nevermind, my db have 4 columns

[brazen charm]

you have 4 ? but proivided 5 things

[limber locust]

yes sir

ik

[vital vale]

i try to upload image for my user model with the entry point i made in views.py, register function, but its not working, and i noticed it prints image_file as none https://codesandbox.io/p/sandbox/cool-http-49fxjp?file=%2Fbase%2Fviews.py%3A1%2C1

[vague hornet]

Hello i'm kinda dum 👋 and i have a question

can I use a postgresql database like a excel sheet

I want to record session control and create actions based on data in the database

not rly like a keylogger but more like a status that a user left the system in so the steps to refresh the setup can be done

[glacial current]

so what is the setup ? python app connected postgres and you want to know if user kills python app it logs that to the db ?

[fading patrol]

It has tables with columns and rows, so that part is like an Excel sheet. It might be overkill for your use case if this is just a local application rather then a multiuser web app. SQLite would probably easier

[vague hornet]

not rly its a bit more complicated than that, this app will be a gateway to physical hardware which ppl can work with and if the teardown process isnt done correctly, which most of the time it isnt it won't work for the next user that logs in, so I would like to store some info about the last state and thinsg like that, and keep a history as well if the same user logs twice on the same machine

[vague hornet]

yeah thats what i was thinking, i just thought of using just 1 type of storage and putting this in the DB seemed kinda obvious to me instead of just bootstrapping an excel to the application

[glacial current]

For me Its hard to visualize that so its hard to tell you best option. But what i was thinking of is that python class has the def __del__(self) method. What i use that for is if the program goes belly up or ctrl c was hit, the __del__ function can record state to the database before it disconnects. Ive used that in workers that have a task and if they die, they can record the last operation, so the next worker can repair state.

so its possible to wrap that into the system and have it leave incomplete state

so simplified class to get the idea

class Overlord:
      def __init__(self):
            self.complete = False

     def shutdown(self):
           shutdown()
           self.complete = True

     def __del__(self):
           if not self.complete:
                 record_state()

ol = Overlord()
setup()

# you can spawn a process here and watch for exitcode.

ol.shutdown()  #if this dosnt happen then the __del__ will take care of it.
normal_shutdown_process()

kill -9 all bets are off though

[paper flower]

Why not use context managers instead of del?

[glacial current]

__del__ worked. What would be the benifit of context manager? Also you could do this with try block but i really dont like them that much

[paper flower]

context managers are the prefferred way fo dealing with resource-like objects in python

python decides when to execute __del__, so it's unpredictable

e.g.

try:
    file = open("text.txt")
finally:
    file.close()

with open("text.txt") as file:
    ...

[glacial current]

I see, i may look at that next time. what do you mean decides to call it? doesnt it call it when it goes out of scope ?

[paper flower]

nope

[glacial current]

hmm i have not seen it do unpredictable things yet.

oh those context managers. i thought you meant some db context managers.

[paper flower]

It is not guaranteed that __del__() methods are called for objects that still exist when the interpreter exits.

https://docs.python.org/3/reference/datamodel.html?highlight=__del__#object.__del__

__del__ shouldn't be used in any cases

And almost all exceptional cases

[glacial current]

it must be very rare then. so why not deprecate __del__

[paper flower]

James Murphy (mCoding) has a great video covering __del__ https://www.youtube.com/watch?v=IFjuQmlwXgU

I myself didn't really have to use it but I guess there are use cases for it

[glacial current]

Yeh that is a good video thanks, i have to say ive been using __del__ for years and its been solid. But i see the edge cases to consider. The with block and try blocks i think are huge functions that anything can happen in which is why i dislike that pattern. cyclic references can be avoided with them but they introduce their own problems. particulary the try blocks.

[paper flower]

What's the problem with using with?

[glacial current]

for example say i have

with directory('some_dir'):
   #large block of code

thats usually bad to look at. so you break that up into functions. the functions are in the context of that dir but you cant see that in the function itself. while they are good for small tasks they introduce coupling down the line that is not obvious.

[paper flower]

Well, you don't have to have a "large block of code here", just close the file when you're done

And that's what most people would recommend you

[glacial current]

so what about the try catch. Is that a good pattern in your opinion?

[paper flower]

It's not when you can use context managers

[glacial current]

i think they suffer the same readability issues they are basically goto's.

sqlalchemy uses this patten of thowing everything which is really hard to program

[paper flower]

Any blocks like if, with, try should be as short as possible
And what does sqlalchemy throw?

[glacial current]

it throws many different types of exceptions that you end up catching with except Exception as e: at the end because there are so many that it can throw. this form of programming is very tiring to write.

[paper flower]

And in most cases they're not meant to be handled 🙂
If you have something like unique constraint violation - query your db first and handle that gracefully

I use sqlalchemy daily and never had to use try-except with it 😅

[glacial current]

I have asked this question before so im interested in the answer 🙂 example?

[paper flower]

Example for what?

[glacial current]

example of how you write without having to catch code in sqlalchemy

[paper flower]

Well, depends on what you want to handle

Table does not exist? Probably let it throw
SQL Syntax error? Probably let it throw
DB is inaccessible? Let it throw
These are really unexpected errors

There's nothing you can do about them

In case of an expected error, for example if user with an email already exists you can simply query your db:

def create_user(session: Session, dto: UserCreateDTO) -> User:
    if session.scalar(select(select(User).where(User.email == dto.email).exists())):
        # Handle your case here
 
    user = User(email=dto.email)
    session.add(user)
    session.flush()
    return user

[glacial current]

IMO (and googles) I think it would be preferable to be able to perform a call back on error rather than a
try
thow anything.
except.
pattern.

        except sqlalchemy.exc.DataError as e:
            db.session.rollback()
            traceback.print_exc()
            self.retry_queue(msg, e)
            raise e
        except sqlalchemy.exc.StatementError as e:
            log('except rollback', e)
            db.session.rollback()
            traceback.print_exc()
            self.retry_queue(msg, e)
            raise e
        except Exception as e:
            log("unhandled error type", type(e))
            traceback.print_exc()
            log('except', e)
            self.retry_queue(msg,e)
        return True

because you actually dont know what could be thrown. code works and one day it fails because the exception and action on that catch does not match. Couple that with anything else that could be thrown in any api that you use with sqlalcmeny its not trivial to get it right.

so this possibly is better orm.execute(f"delete from email where id = {row.id}", on_error=handle_delete)

[paper flower]

Or you can rollback automatically using context managers provided by sqlalchemy 😃

Retrying is pointless IMO, just let your API respond with 500, if you're talking about http based apis

Also callbacks are awful

[glacial current]

so are try throw anything patterns 😂

[paper flower]

All sqlalchemy exceptions inherit from common class 😐

[glacial current]

point is that if you have call to orm.execute then you have to put a try catch directly around that and capture and handle anything that can happen with the db. so that is pretty messy IMO and better use would be a handler to interpret just a db error. without the try stuff.

no big deal just an opinion.

[paper flower]

Why would you actually handle these exceptions?

[glacial current]

you would but just in the handler. if the handler is provided then it should pass the error to the handler instead of forcing you to write the try boiler plate.

so you end up with cleaner code in general

[paper flower]

I mean, why do you need to handle them? I see no point in handling any exceptions from your example

[glacial current]

no point in handling them? you mean because of the context managers you say are available? I dont know about them actually. i call orm.execute and handle the errors which is why i was asking for an example of how i can avoid that pattern. I also use core execute too.

to answer this though i have at least one other API that can bust out an exception at any time. But you have no idea that is possible in python. And you may need to recover them. with this pattern you are forced to make a function inside the except block to clean up the other api. Also you tend to write a few tasks execute calls without putting try catch around each function call. if you had to make a try around every function call of every api that would be awful.

[paper flower]

I don't understand why you need to handle exceptions that execute can raise 😐

[glacial current]

one example is you have consumed an email you need to unread it relating to that exception.

[paper flower]

What do you mean?

[glacial current]

this app read email and saves then im a db for easy indexing. that needs to be backed out in the mailer app. so that operation needs to know a failure to save happened

a persist to db is multi stage though. so a handler to handle all failre to save or delete would be better than try catch everywhere

[paper flower]

Maybe you can share some code?

[glacial current]

i cannot 😂 . proprietary

i could write a handler myself though to wrap it

def db_write(conn, *args, **kwargs, on_error=None):
        try :
           conn.execute(**args, **kwargs)
        except Exception as e:
              log(e)
              if on_error:
                  on_error(*args, error=e, **kwargs)
              else:
                  raise e

[glacial current]

i asked chatGPT to give me a good example of context management in sqlalchemy.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create the engine and connect to the database
engine = create_engine('sqlite:///users.db')
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

# Define the User model
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

try:
    # Create a new user and add it to the database
    new_user = User(name='John Doe', age=30)
    session.add(new_user)
    session.commit()

    # Query the database to retrieve the user with ID 1
    user_id = 1
    queried_user = session.query(User).get(user_id)
    print("Queried User:", queried_user.name, queried_user.age)

    # Update the user's age and commit the changes
    queried_user.age = 31
    session.commit()

    # Delete the user and commit the change
    session.delete(queried_user)
    session.commit()

except Exception as e:
    # Handle any exceptions that might occur during the transaction
    print("Error:", str(e))
    session.rollback()

finally:
    # Always close the session to release resources
    session.close()

to a large degree this is what i do. Am i wrong ?

[paper flower]

Conclusion: Don't ask chatgpt

Read the docs

[glacial current]

The docs are very confusing. Thanks

[wise goblet]

https://youtu.be/Od66PuUYUU4 😅

[glacial current]

I've tried to understand the sqlalchemy docs for years. And they are an example of how not to write docs.

[brazen charm]

Idk, the docs seem pretty good to me, everything you wanted to know is described, has detailed examples and describes the different ways of doing things and how they work

[glacial current]

Wana answer my question then 😄. Coz idk

It's starting to look like that video that was just posted

[brazen charm]

https://docs.sqlalchemy.org/en/20/orm/session_basics.html#using-a-sessionmaker would be a good place to start

[glacial current]

Does not answer my question which was how to you safely do that. There is no error checking. How do you structure a commit so that you can roll back other stuff

[brazen charm]

look at the context managers, as it describes further down. That is the point of them

Generally speaking, it's a bad idea to try and manually manage your commits and rollback except for some specific scenarios.
https://docs.sqlalchemy.org/en/20/orm/session_basics.html#framing-out-a-begin-commit-rollback-block

[glacial current]

Thanks that helps.

[trim mica]

Django now supports databases: Postgreesql, MariaDB, Mysql, SQLITE and Oracle. Which database do you recommend me to use? And why this choice compared to the others? Thank you, your opinions matter a lot

[brazen charm]

Sqlite for local Dev and testing basic stuff. Postgres for everything else

[wise goblet]

Sqlite => for small pet projects too 😅

usage of MongoDB is a root problem. 😅 switch to Postgres.

[storm star]

can yomeone help me with mongodb

py -m pip install pymongo

Requirement already satisfied: pymongo in path

but if i run i get:

from pymongo import MongoClient

ModuleNotFoundError: No module named 'pymongo'

[pearl lodge]

guys, am i doing it right?
https://github.com/HirukaRogue/RP-Utilities/tree/main

i don't know the loop thing

kinda coonfuse

leaning from this:
https://github.com/mongodb/motor/blob/master/doc/tutorial-asyncio.rst#id88

[storm star]

Can I safe this to the mongodb database

Keyword = []

In it are random values

[pearl lodge]

okay... i don't know if i'm doing it right

[fathom star]

Hi! Is it possible to ROLLBACK to before a commit? I'm writing some integration tests for my FastAPI endpoints which all call session.commit() at the end (using SQLAlchemy here)

In my tests, I'd like to start a transaction at, run the endpoint, and rollback to that transaction (even though there was a commit). Is that possible or is there a more idiomatic method to do this?

[steady saffron]

the hacky solution would be patch the commit method to be a no-op, but I'm not sure if that would work with Postgres

[coral wasp]

Not sure exactly what you mean, sounds kinda like you want a save point rather than rolling back the transaction https://sqlite.org/lang_savepoint.html

[fathom star]

Ah, sorry. Should've been more clear. I pretty much want to rollback all the changes that happen in each test, apparently it's not possible to rollback to a savepoint once it's already been committed?

[coral wasp]

Oh, like completely undoing any changes from the test? I guess I’d clone or snapshot/CoW the db for each test, perhaps. Some db’s have snapshot features too.

[rare hull]

Hello, I'm working with 5 pretty big datasets and I just figured out how to merge them with pandas, now I'm trying to figure out how to work with it as a whole.
This is the code I used to combine them;

print('After merging:')
pd.concat([data15,data16,data17,data18,data19],axis=0)

I was wondering if anyone knew how run an analysis after its created. Is there a command/ code I can run to name the new data set and work with it like that?

[steady saffron]

Are you using SQLite?

if so, yeah then restoring a copy of the db would probably be the least bad option

[fathom star]

Postgres, I think that's one of the larger problems I have haha 😅
I need to spin up a whole postgres container for each test session, and interacting with postgres is much slower than an in-memory SQLite

[steady saffron]

Thought so, yeah then I don't really have any good ideas

Postgres has nested transactions of some sort, but I don't think they'll work here

[fathom star]

Well, that's unfortunate. Trying to get the runtime of our test suite down haha

[coral wasp]

Maybe a pitr? https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY

[fathom star]

haha.. wasn't even the database
Apparently I was creating the FastAPI TestClient each time for each test, caching that brought the runtime from ~25 seconds to ~1 seconds

[steady saffron]

LOL

fastapi isn't so fast anymore

[paper flower]

I'd install postgres into your system directly if you need it in multiple projects

[paper flower]

Are you committing multiple times in whatever you'se testing?

[fathom star]

No, most (if not all) endpoints interface with the DB, and commit once at the very end
I could probably even get away with committing at the end of the get_db dependency (injects the session)

[paper flower]

Also creating http client for each test should be fine

[paper flower]

Eh, don't

You need a middleware for that (to properly commit)

Dependencies are shut down after response is sent, so you could return 200 OK and then fail to commit your changes to db

[fathom star]

yeah would probably not be a good idea. Besides, I do have a few random endpoints scattered about that dont interface with the database, better off just committing where I need it

[paper flower]

https://gitlab.com/ThirVondukr/fastapi-cookiecutter/-/blob/main/src/adapters/api/middleware.py?ref_type=heads
https://gitlab.com/ThirVondukr/fastapi-cookiecutter/-/blob/main/src/db/dependencies.py?ref_type=heads#L12

I'd use a middleware

As of transactional tests setup - you can do something similar:
https://gitlab.com/ThirVondukr/sqlalchemy-pytest/-/blob/main/sqlalchemy_pytest/database.py?ref_type=heads
https://gitlab.com/ThirVondukr/fastapi-cookiecutter/-/blob/main/tests/plugins/database.py?ref_type=heads

[fathom star]

I didn't think so either, but setting the scope to session on pytest for the TestClient drastically improved runtime performance
Maybe the TestClient is starting a new instance of the ASGI app for each test?

[paper flower]

You're probably creating an app in the same fixture

[fathom star]

I'm importing it and passing it into TestClient (used in a context manager)
Not sure how the internals of the TestClient work

[paper flower]

I have ~800 tests, with default scope they take 13.19s to execute, 12.53s with session scope

Hm, you're using fastapi's TestClient?

starlette's to be precise

[fathom star]

Ya

[paper flower]

I'd use httpx directly 🤔

But it doesn't have websockets support, that's up to you really

Ah, and it's synchronous? 🤔

[fathom star]

I suppose I'll give that a try too, but setting the scope to session has brought the runtime to ~0.85s so any more performance improvements will probably yield diminishing returns

[fathom star]

Yup

[paper flower]

If you still need transactional tests, take a look at my plugin, this section to be precise:

@pytest.fixture(autouse=True)
async def session(
    _sqlalchemy_run_migrations: None,
    sqlalchemy_pytest_engine: AsyncEngine,
    async_sessionmaker: AsyncSessionmaker[AsyncSession],
) -> AsyncGenerator[AsyncSession, None]:
    async with sqlalchemy_pytest_engine.connect() as conn:
        transaction = await conn.begin()
        async_sessionmaker.configure(bind=conn)

        async with async_sessionmaker() as session:
            yield session

        if transaction.is_active:
            await transaction.rollback()

We can create a transaction and patch sessionmaker with that connection

It's autouse, but you don't really have to do that

Maybe a good idea to "break" your sessionmaker on purpose to make sure you don't make db calls by accident:

async_sessionmaker.configure(bind=None)

[fathom star]

Yeah right now I've resorted to TRUNCATE-ing all tables, I'll give yours a try tomorrow, thanks!

[paper flower]

Also with this approach you don't have to override anything in fastapi DI, etc

[fathom star]

I'm spinning up an ephemeral Postgres container along with the test container, so calls to the DB are fine

[paper flower]

Still

You'd see where you didn't use session fixture but it was needed

[wise goblet]

U could also copy django ORM behavior

Run all migrations only once to setup database structure for testing session. (Fixture scope session)
Only cleanup tables and refill them between tests with fixtures

[paper flower]

That's what they're doing atm

Using transactions is a cleaner approach imo

[dawn vector]

Hey I have a very simple MongoDB query but it seems to be working incorrectly.

In NodeJS I print out the resulting final query right before printing the results.

  console.log(query.getQuery())
  const results = await query
  console.log(results)

OUTPUT:

query: { status: 'public', subscribers: { '$gte': 30000000 } }
results:     {description: '',
    thumbnailUrl: 'no-photo.jpg',
    subscribers: 17100000,
    channelLink: 'https://www.youtube.com/@Aphmau',
    lastUpdated: '2023-06-06 12:53:44',
    keyword: 'a',
    filter: 'This week',
    status: 'public',
    dislikes: 0,
    likes: 0
  }

As you can see I expect it to return subscribers with more than 30m but in the results I have 17.1m. In fact there is a lot of wrong results. What might be is causing this problem?

[paper flower]

This is a python server 🙃

[glacial current]

The JS server is a wasteland over there. That's why they come. They are drawn to the warmth.

[glacial current]

We love you. we just dont speak js.

Just messing. (not about the JS server that is a wasteland) but it would be pretty hard not knowing the data to answer you.

just one sample of a JS question over there.

If someone is volunteer, I will sacrifice him.```

thats cold.

[dawn vector]

🙃

[dawn vector]

thanks for the response. you can assume the db is collection of the documents in results variable above. It is the most bizarre problem about database so far now for me.

[glacial current]

Can you query it without node.js and get different result?

[dawn vector]

yes with python it works fine :\

[glacial current]

There is a lot of layers . server . browser . web framework.. i would just start pealing back each layer like using curl instead of all that to get to the root of the problem

oh

[dawn vector]

I am gonna have to assume node is just doing magic man

[glacial current]

it must be buffering. look for flush in node

[dawn vector]

because even the resulting query is correct

[dawn vector]

let me just check that. thank you

[hollow oasis]

Hmm isn't there is a driver called pymongo? 🤔 🙃

Wait my bad I just realize he is using JavaScript
But still .....

[hollow oasis]

💀

[hollow oasis]

Since I only use JavaScript for web development and I use python for my backend ( including database mongodb )
I don't know the answer in js

[hollow oasis]

Also is that the full script?

Or full function?

[pearl lodge]

https://github.com/HirukaRogue/RP-Utilities

Traceback (most recent call last):
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 59, in <module>
    Bot().run(bot_token.return_token())
    ^^^^^
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 19, in init
    command_prefix = self.prefix_setup(),
                     ^^^^^^^^^^^^^^^^^^^
TypeError: Bot.prefix_setup() missing 1 required positional argument: 'ctx'

guys i need help

why am i receiving this error

of self.prefix setup missing something?

okay guess i will need to use asyncio

to make it work

okay this is a dylem

if i put this function as non-async this error happpens

if i try to async it shows the error of not being awaited

what do i do?

[sage moat]

hi, someone can told me the reason of the error 1064 when i try to syncronized the model to my databse ? model of EER diagram to make the foreign key

[dawn vector]

oh man. I think I figure it out today. Frigging. mongoose :(. It was using some weird Model API and even though the query was legitimate and should return correct results, because of missing fields in the model defintion, it was just doing this bizarre thing.

and honestly I don't know how mongoose code leaked into my project.

I mean why in the hell would missing fields cause everything to be queried everytime

[pearl lodge]

C:/Users/User/AppData/Local/Programs/Python/Python311/python.exe c:/Users/User/Documents/GitHub/RP-Utilities/bot.py
Traceback (most recent call last):
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 60, in <module>
    Bot().run(bot_token.return_token())
    ^^^^^
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 23, in __init__
    self.db_loop = self.database.dev_client.get_io_loop()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'get_io_loop'

[pearl lodge]

Traceback (most recent call last):
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 60, in <module>
    Bot().run(bot_token.return_token())
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\client.py", line 860, in run
    asyncio.run(runner())
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 190, in run
    return runner.run(main)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\asyncio\runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\asyncio\base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\client.py", line 849, in runner
    await self.start(token, reconnect=reconnect)
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\client.py", line 777, in start
    await self.login(token)
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\client.py", line 621, in login
    await self.setup_hook()
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 28, in setup_hook
    self.command_prefix = await self.prefix_setup(self)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\User\Documents\GitHub\RP-Utilities\bot.py", line 37, in prefix_setup
    prefix = await self.db_loop.run_until_complete(self.database.get_prefix(guild_id=ctx.guild.id))
                                                                                     ^^^^^^^^^
AttributeError: 'Bot' object has no attribute 'guild'. Did you mean: 'guilds'?

[queen rose]

When working with geo data, if I have a table with a bunch of 'Point's and I want to filter it to get all the points that are in a given city/country, how would I get the 'Polygon' of that city/country in the first place?

[paper flower]

You can estimate by radius, plus selecting such points would be easier, but it's not accurate 🤔

If you need precise polygons there are may be datasets that provide them

[zealous halo]

I am trying to connect to a postgres db using sqlalchemy and the create_engine() method keeps throwing this error. I printed out all the env vars and they show as the correct strings

import os
from dotenv import load_dotenv
import psycopg2
from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

load_dotenv()

db_name = os.getenv('DB_NAME')
user = os.getenv('DB_USER')
password = os.getenv('DB_PASSWORD')
host = os.getenv('DB_HOST')
port = os.getenv('DB_PORT')


DATABASE_URL = f'postgresql+://{user}:{password}@{host}:{port}/{db_name}'

engine = create_engine(DATABASE_URL)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Traceback (most recent call last):
  File "/Users/dev/Desktop/projects/backend/db.py", line 22, in <module>
    engine = create_engine(DATABASE_URL)
  File "<string>", line 2, in create_engine
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    u = _url.make_url(url)
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 838, in make_url
    return _parse_url(name_or_url)
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 901, in _parse_url
    return URL.create(name, **components)  # type: ignore
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 205, in create
    cls._assert_port(port),
  File "/Users/dev/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/url.py", line 215, in _assert_port
    return int(port)
ValueError: invalid literal for int() with base 10: ''

it's not even getting to make a network call, the create_engine() method throws an error before it happens

[lean olive]

try printing the DATABASE_URL to check whether it's as you want
from the error, it looks like the DB_PORT environment variable is empty

[zealous halo]

it's all there when I print it full url, port and everything

just tried with a local db, no issues

nvm it was regexed out bc I was using the aws console url not the db endpoint url

[queen rose]

I want to create a tree structure on my website where ppl can filter first by continent, then by country, and then by city.
Do you have any idea where I can find this data?

[paper flower]

Nope 🙂

[queen rose]

Ight thanks anyways

[fading patrol]

Continents and countries are here but it only includes capital cities. If you want to cover all the cities in the world, I've not looked I into that. https://github.com/samayo/country-json

[torn sphinx]

I've been trying to install postgreSQL locally on my windows 10 PC for the past day and I keep getting an error message at the end of installation: Failed to load SQL Modules into database Cluster

The rest of the installation ends normally and no further error messages pop up but when I try to connect to the server I get this

I've read some forums and it seems like people who have this same error message get a second error message saying the installation may not complete or whatever but I don't get that one. People also say when they get this error message that their PostgreSQL/15/data folder is empty but mine isn't empty. idk what to do

I'm not doing anything weird either i'm just installing default

So now I'm kind of just giving up and trying to install on a linux VM because I want to get on with my project and I don't really care to spend hours looking through my user permissions and antivirus flags to find the error. If anybody has solved this problem please reply or pm me i appreciate it

[wise goblet]

i can give easy advice how to setup it for linux 😄

lets assume u have some kind of ubuntu or debian based lts linux

apt update
apt install docker

can be sufficient, but preferably to follow official tutorial to install docker
https://docs.docker.com/engine/install/ubuntu/ (don't forget to follow post installation instruction for adding current user into docker group! if u are not root at least user all the time. https://docs.docker.com/engine/install/linux-postinstall/ )

then create anywhere docker-compose.yml file with text

version: '3.8'
services:
  postgres:
    image: postgres:13
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - 5432:5432
    healthcheck:
      test: [ "CMD", "pg_isready", "-U", "${PG_USER:-postgres}" ]
      interval: 3s
      timeout: 3s
      retries: 5
    volumes:
      - ./data:/var/lib/postgresql/data/

and then run docker-compose up -d

done. you made available to yourself postgres 😄 (feel free to verify with docker ps command)

data will be in relative folder data to where u launched it

upgrading to newer version? just change postgres:12 to postgres:13 or anything else

optionally feel free to disable data persistence by erasing volume part.

docker-compose down to disable it (run at same folder where docker-compose.yml file is located. folder name should not be changed)
delete ./data folder if u want to erase its data

docker-compose exec postgres to enter shell of container where database runs

of course u can always to use more conventional ways to install postgres in a baremetal way... but they are kind of bothersome in comparison to easy doing that with docker

[torn sphinx]

dude bet

ty

[fading patrol]

Yeah I dunno who all these internet masochists are trying to install Postgres directly on Windows but I've never met one. Darkwind has given you some civilized options, you can also just spin something up on the cloud provider of your choice (although having it local has its benefits depending on your use case)

[noble osprey]

I have two databases in different regions where data is replicated. What type should I use for an identity column? This is what I have thought so far:

1. uuid - this will guarantee global uniqueness, but takes more space than int
2. int with some sequence - takes less space but I'll have to use different sequence for each region, so I'll have two DDL scripts for creating table in different regions
3. creating a composite key

[torn sphinx]

So actually I upgraded to windows 11 just before i tried the VM thing as a hail mary to see if it would fix anything and it fixed everything. I wasn't able to install docker desktop on my old windows version either and after I installed windows 11 I was immediately able to just run the postgres and docker installers without a hitch and now I'm happy and everything is working

i guess maybe the installers had bugs on my version or maybe I just messed something up that changing OS versions fixed

idk

[rough zephyr]

Hello.

I'd like to use this SQLAlchemy event (from docs):

from sqlalchemy import event


@event.listens_for(SomeClass.some_attribute, 'remove')
def receive_remove(target, value, initiator):
    "listen for the 'remove' event"

    # ... (event handling logic) ...

I'd like to make another database request within that method to check something. Is there a way how to pass a Session to this method? I can get one directly from sessionmaker, but as it is a web-api application, I'd like to use the one which comes with the request.

Thank you.

[paper flower]

I think you can get associated session from target

[golden mauve]

anyone here has experience using Tableau Server with Oracle Wallet?

[ancient delta]

I have a question related to SQL but not Python so forgive me if it's not allowed.

I don't understand why I am getting NULL values for these dates. I have a function that converts 31 different date formats into datetime2. One of them is giving me trouble. The function is used like so:

dbo.svf_convert_date('99991231', 'YYYYMMDD') -- can also be 'YXYYMMDD' see link below

This should return 9999-12-31 00:00:00.0000000 but instead returns NULL. The function is rather long, but the only relevant part of it that pertains to the formats YYYYMMDD and YXYYYMMDD is this: https://paste.pythondiscord.com/KMPQ

More specifically something is failing here:

RETURN (CASE
           WHEN @year > 9999 OR @year < 1900 THEN CAST(NULL AS datetime2)
       WHEN @month > 12 OR @month < 1 THEN CAST(NULL AS datetime2)
       WHEN @day > 31 OR @day < 1 THEN CAST(NULL AS datetime2)
       ELSE CONVERT(datetime2, DATEFROMPARTS(@year, @month, @day))
    END)

I also tried this:

RETURN (CASE
           WHEN @year NOT BETWEEN 1900 AND 9999 THEN Cast(NULL AS DATETIME2)
           WHEN @month NOT BETWEEN 1 AND 12 THEN Cast(NULL AS DATETIME2)
           WHEN @day NOT BETWEEN 1 AND 31 THEN Cast(NULL AS DATETIME2)
           ELSE CONVERT(DATETIME2, Datefromparts(@year, @month, @day))
        END)

I don't understand why it would return NULL. The date 99991231 does not fulfill any of the conditions in any of the cases.

[ancient delta]

Oh... I found the issue. Wrong substring index for @month. There are only 12 months in a year, not 91 🤦‍♂️

[wise goblet]

...MongoDB based applications... that's it. 🙈 we can stop here and check no further 😅

[flat pawn]

Haha why do you think so?

[wise goblet]

!rule 6 , unapproved advertisting is forbidden. delete please from all channels

[delicate field]

Rules

6. Do not post unapproved advertising.

[flat pawn]

Oh I see. Sure will do it then.

[flat pawn]

already gone. A stupid question maybe but how do I can get advertising approved? Or were can I show my work?

[wise goblet]

Sure.

• Relational db structure is typed and fully enforced

• automatically verified data integrity and constraints.

• Relational dbs have mechanisms for data migrations, you can in a sane way migrate data between application versions to future ones

• Relational dbs have powerful quering language that allow to extract any sort of information complexity, while delegating all heavy lifting to db

• MongoDB is untyped

• with not enforced data structure

• you are doomed when u try to make next app version. Your data is not having any longer integrity between tables (if it had in the first place)

• lack of powerful quering language

[wise goblet]

A stupid question maybe but how do I can get advertising approved?
i can bet question regarding that can be asked here

[wise goblet]

@flat pawn For this reason, for simple minimal (pet) projects better to start with SQLite3 first
For real prod projects better to use Postgres

[flat pawn]

I see your points but integrity would be enforced by the framework. Dealing with the database schema separately can be very annoying. Especially in the beginning when you don't have a clear picture yet.

[wise goblet]

u can't enforce data integrity by framework for existing data. You had data that was not checked before for integrity and that's it. you are already doomed if u check it by application. And it wil not be fixed if your application checks integrity in its next version, because u have already data integrity not verified data accumulated.
Because u can't recheck in a sane way database massive table, rescanning each data all the time and validating its integrity (too much performance heavy)

[flat pawn]

Sure this is clear. I was thinking more of the case where you really start from scratch... I mean where you have no data.

[wise goblet]

So... MongoDB is useful only if you rewrite your application each time from zero

wishing new app version? drop all existing data 😄 (who would do that?) if u want keeping data integrity. Or feel pain of migrating stuff manually

[wise goblet]

where you really start from scratch
Sqlite3 is already answer to start from scratch for simple usage cases 😛 (if u don't use normal postgres or other fully fledged relational engines)

[flat pawn]

I wouldn't be so pessimistic 😉

[wise goblet]

it is not about pessimism, just a matter of sanity and insanity in choices 😛

[flat pawn]

Well then I would not be so certain 😜

[glacial current]

Is there a good alternative to sqlalchemy?

[wise goblet]

Django ORM is awesome.

fully presetup

including migrational system

which is far more robust with being sub multi application independent/yet interconnected one.

[glacial current]

Are you saying for web dev? I was just thinking of pandas guys and strait orm.

Guess I'm confused. Sometimes I want straight sql raw. Sometimes class is a table style

No web though

[wise goblet]

Django ORM can be used standalone like SQLAlchemy

can be used ORM, can be used sql raw

[glacial current]

Oh very interesting

[wise goblet]

https://docs.djangoproject.com/en/4.2/topics/settings/#calling-django-setup-is-required-for-standalone-django-usage

[glacial current]

Thank you

[paper flower]

Why are you looking for an alternative?

[patent kettle]

how useful is vector database?

[storm mauve]

they're pretty much a specialised tool, they're useful for the tasks they're designed to perform well in, specially things like information retrieval based on embeddings, but for things out of their scope: not a lot

quoting https://learn.microsoft.com/en-us/semantic-kernel/memories/vector-db:

The main advantage of a vector database is that it allows for fast and accurate similarity search and retrieval of data based on their vector distance or similarity. This means that instead of using traditional methods of querying databases based on exact matches or predefined criteria, you can use a vector database to find the most similar or relevant data based on their semantic or contextual meaning.

they're not going to kill SQL databases, but neither are they going to vanish

just like how other uncommon NoSQL database types like graph databases exist and are used in specific contexts

[rapid folio]

what is the best way to implement pagination in mongoDB? I have heard skip and limit method is not scalable as your database grows, so whats the best and efficient way? can anyone point me in the right direction?

[paper flower]

limit/offset or cursor based pagination

It depends on your requirements

[teal rampart]

I need a local database (like sqlite) but in No-SQL style (like MongoDB), what are some suggestions?

[wise goblet]

file in json format https://media.tenor.com/ciioUmN19eoAAAAC/furry-laugh.gif

[rocky tapir]

Can I ask question related to firebase here

[glacial current]

Largely just curious. Been testing out some different types of databases and wanted to see what alternative ways to connect and use those database are possible.

[glacial current]

Btw our discussion helped me with a few things. Thanks. A lot of the problems I'd been having was not sql alchemy. It was cleanup not happening the connection. Using autocommit true solved those issues. It was odd because these are just select with joins.

[hollow oasis]

Mongodb is my favorite database lol

[hollow oasis]

actually it is stored in "BSON" ( "BSON" means Binary JSON, and hope you know JSON format )

[ember axle]

I'm using postgre to insert data in a database using psycopg2.
Whenever I use the SQL command from the console it works fine: INSERT INTO public."Users"("Username", "Password") VALUES ('test', 'test');
But when I try to run that SQL code In my code nothing seems to get added.

conn = psycopg2.connect("dbname=UserDatabase user=postgres password=`******`")
cur = conn.cursor()
cur.execute("""INSERT INTO public."Users"(Username, Password) VALUES ('abc', 'abd');""")

I'm going to use bound variables once I get this to work, I'm aware of sql injection

This just makes it a bit easier to see what oging on

[harsh pulsar]

does anyone know what might cause sqlalchemy to implicitly start a transaction? i'm seeing the weirdest behavior where in my test suite a transaction is started and i get an error with async with session.begin() ("transaction already started for this session"), but in the app iteslf that works fine

i'm going to try sticking some db.in_transaction() calls to try to figure out where it might be happening. but open to ideas as well

[hollow oasis]

hey i do think you forgot to use "commit" i guess

[ember axle]

Commit? I don't understand

[hollow oasis]

fr.....

[ember axle]

Explain..

[hollow oasis]

commit is like a

ahh....

assume that was your entire order sheet
but someone needs to deliver that order sheet right?

that delivery guy is commit

[ember axle]

I thought execute does thart?

[hollow oasis]

commit and execute is differnet

[ember axle]

I don't see anything about commit in the examples

>>> SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes
>>> data = ("O'Reilly", )
>>> cur.execute(SQL, data) # Note: no % operator

this is an example

So

[harsh pulsar]

you might want to read this @ember axle https://www.psycopg.org/docs/usage.html#transactions-control

[ember axle]

conn.commit()?

[hollow oasis]

as long as i studied
execute is like telling him giving all the commands
and commit is like confirming the commands
if the user doesn"t verify his commands then the commands wont be execute

[hollow oasis]

Yes

[ember axle]

ok thats my bad didnt read far enough then

lol

[hollow oasis]

lol we all are like that

@ember axle
if commit wont work the one last suggestion is gotta check what your table structure is

fun fact: i studied the basics to how to use mysql in python but ended up with mognodb or pymongo xD

[ember axle]

Yeah well I changed it up a bit but still doesn't work

conn = psycopg2.connect("dbname=UserDatabase user=postgres password= **********")
cur = conn.cursor()
SQL = ('INSERT INTO public."Users" (Username, Password) VALUES (%s, %s);')
data = ("Falcoa, test123")
cur.execute(SQL, data)
conn.commit()
cur.close()
conn.close()

this is the DB

Im using DB first time as well

used to the microsoft database

this is a bit different though

[hollow oasis]

let me see the script

[ember axle]

That is basically it

But I might be doing something else wrong gotta read the entire docu page first lol

[hollow oasis]

@ember axle

umm did you check the data variable closely.....

[ember axle]

yeah i was a bit sus about that

[harsh pulsar]

@ember axle note that for new projects you should probably use psycopg 3 instead of 2 https://www.psycopg.org/psycopg3/docs/index.html

[hollow oasis]

i think it should be like this:

data = ("Falcoa" , "test123")

[ember axle]

oh lmfao

[hollow oasis]

xD

[ember axle]

Is it much of a difference?

[hollow oasis]

maybe it got better functions or more helpful features

[ember axle]

conn = psycopg2.connect("dbname=UserDatabase user=postgres password= SDJIHASDUOASHUIDH2389!")
cur = conn.cursor()
SQL = ('INSERT INTO public."Users" (Username, Password) VALUES (%s, %s);')
data = ("Falcoa", "test123")
cur.execute(SQL, data)
conn.commit()

Still nothing in my DB after running this code.
Must be still a typo somewhere or smth. Anyway I'll check it out later dinner is ready lol

Thanks anyway

I'll figure it uot

[hollow oasis]

@ember axle

here try this:

def create_table():
    try:
        conn = psycopg2.connect("dbname=UserDatabase user=postgres password= SDJIHASDUOASHUIDH2389!")
        cur = conn.cursor()

        SQL = '''
        CREATE TABLE IF NOT EXISTS public."Users" (
            ID SERIAL PRIMARY KEY,
            Username VARCHAR NOT NULL,
            Password VARCHAR NOT NULL
        );
        '''
        print("Hope working from here")
        cur.execute(SQL)
        conn.commit()
        print("Table created successfully!")

    except psycopg2.Error as e:
        print("Error:", e)
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()

def insert_data():
    try:
        conn = psycopg2.connect("dbname=UserDatabase user=postgres password= SDJIHASDUOASHUIDH2389!")
        cur = conn.cursor()

        SQL = 'INSERT INTO public."Users" (Username, Password) VALUES (%s, %s);'
        data = ("Falcoa", "test123")
        print("almost done i guess")
        cur.execute(SQL, data)
        conn.commit()
        print("Data inserted successfully!")

    except psycopg2.Error as e:
        print("Error:", e)
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()

if __name__ == "__main__":
    create_table()
    insert_data()

[fervent jay]

Someone can help me with a doubt about session in sqlalchemy?

I'm getting the follow error:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00

In my code i do many operations in database. And all my methods are structured like this:

@staticmethod
def register_candidate(list_candidates, call):
      to try:
          with Session() as session:

              for candidate in candidate_list:

                  new_candidate = CandidatesModel(
                  course_name = candidate['NO_COURSE'], course_shift = candidate['DS_SHIFT'], course_modality = candidate['DS_MODALITY'],
                  num_insc = candidate['CO_INSC'], name = candidate['NO_CANDIDATE'], cpf = candidate['NU_CPF_CANDIDATE'],
                  email = candidate['DS_EMAIL'], cod_course_insc = candidate['CO_CURSO_INSC'],
                  competition_modality = candidate['NO_COMPETITION_MODALITY'],
                  acronym_competition_modality = ModalityConvertionHelper.modality_convertion(candidate['NO_COMPETITION_MODALITY']),
                  acronym_competition_modality_approval = ModalityConvertionHelper.modality_convertion(candidate['NO_COMPETITION_MODALITY']),
                  candidate_note = candidate['NU_CANDIDATE_NOTE'], rating_number = candidate['RATING_NUMBER'],
                  call_approval = call)

                  session.add(new_candidate)

              session.commit()

              return true

      except Exception like and:

          # In case of exception, print the error and return False to indicate registration failure
          print(f"Error registering candidate: {str(e)}")
          returns false```

[hollow oasis]

@fervent jay first of all i cant
2nd of all no one can help you with this
because the variable names and strings are not written in english

im leaving bye

[fervent jay]

in english now

[pastel wren]

Does anyone know how to fix this error? I'm trying to setup a second machine for me to work on but what works on my laptop doesn't seem to be working when I try the same code on my mac mini.
I'm attempting to connect to a postgre db with psycopg and sqlalchemy

could not translate host name to address: nodename nor servname provided, or not known

[wise goblet]

potentially you have dirty connection openings 😄

u open many many connections and never close

and exhausted limit

[wise goblet]

also i wish to add, this is a horror code i would not have minded giving during interview and asking candidates to fix it 😅 Let me fix it for you

[fervent jay]

When I use "with" theoretically shouldn't the connection be closed automatically?

[fervent jay]

🤣 iam trying my best

[wise goblet]

from pydantic import BaseModel

class FailerRegisteringCandidateException(Exception):
    pass

class Candidate(BaseModel):
    NO_COURSE: str
    DS_SHIFT: str
    CO_INSC: int
    # you get the idea

@staticmethod
def register_candidate(candidate_list: list[Candidate], call: WhatIsThis) -> None:
    try:
        with Session() as session:

            for candidate in candidate_list:

                new_candidate = CandidatesModel(
                    course_name = candidate.NO_COURSE,
                    course_shift = candidate.DS_SHIFT,
                    course_modality = candidate['DS_MODALITY'],
                    num_insc = candidate.CO_INSC, name = candidate['NO_CANDIDATE'], cpf = candidate['NU_CPF_CANDIDATE'],
                    email = candidate['DS_EMAIL'], cod_course_insc = candidate['CO_CURSO_INSC'],
                    competition_modality = candidate['NO_COMPETITION_MODALITY'],
                    acronym_competition_modality = ModalityConvertionHelper.modality_convertion(candidate['NO_COMPETITION_MODALITY']),
                    acronym_competition_modality_approval = ModalityConvertionHelper.modality_convertion(candidate['NO_COMPETITION_MODALITY']),
                    candidate_note = candidate['NU_CANDIDATE_NOTE'], rating_number = candidate['RATING_NUMBER'],
                call_approval = call)

                session.add(new_candidate)
            session.commit()
    except (PreciseSQLException, AnotherPreciseSQLException) as err:
        raise FailerRegisteringCandidateException() from err

[wise goblet]

i have no idea how your code is written in this part 😉

[fervent jay]

thank you for that

[wise goblet]

s = Session()
objects = [
    User(name="u1"),
    User(name="u2"),
    User(name="u3")
]
s.bulk_save_objects(objects)
s.commit()

Also rewriting to bulk create will speed up code N times

[wise goblet]

https://docs.sqlalchemy.org/en/20/orm/queryguide/dml.html

>>> from sqlalchemy import insert
>>> session.execute(insert(CandidatesModel), list([ candidate.dict() for candidate in candidate list ]))

According to 2.0 version
(That is assuming candidate is still pydantic basemodel)

[misty lagoon]

how do I see what a select exist statement returns? I want to know if it returns true of false but I have no clue

i'm using mysql.connector btw

[paper flower]

You can just use add and flush/commit

[frosty relic]

Hi guys
I wrote a telegram bot by python for my online shop
I added a database to my source code to check users wallet
but i got a problem with my database
When the code is running and i change the value of the wallet in the database (i run this on my pc with zampp and using PHPmyadmin )
The inline button that show wallet values does not show it real value and shows the old value
How can i solve this problem??

[wise goblet]

three levels of choices

1. dummy polling script at client side to request new page/or section of it in interval. Vanilla JS version choice.
2. still polling script, but with proper client side application that requests and changes only necessary part of uploaded page in an easy way. Vue.js and etc choices.
3. web sockets to exchange information in a real time 😉 between front and back

first option does not scale at all almost. (in terms of performance and code quality)
second option does scale in terms of code quality more or less
third option is performant optimized version of those first two choices

[paper granite]

hey can someone help me

no coding just convo about differential privacy

is this the right channel

[cyan matrix]

Hi guys, ı want to get and change datas from an application in my computer. I know selenium library. Which library should ı use to do that?
(İt is an application not website)

[ember axle]

Isn't selenium used for Webbrowsers only?

[halcyon finch]

https://paste.pythondiscord.com/PLBQ

raceback (most recent call last):
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/discord/client.py", line 441, in _run_event
    await coro(*args, **kwargs)
  File "/home/runner/PrivateBot1/cogs/utility.py", line 170, in on_message
    data = collection.find_one({"_id": guild_id})
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/collection.py", line 1470, in find_one
    for result in cursor.limit(-1):
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/cursor.py", line 1251, in next
    if len(self.__data) or self._refresh():
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/cursor.py", line 1142, in _refresh
    self.__session = self.__collection.database.client._ensure_session()
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/mongo_client.py", line 1758, in _ensure_session
    return self.__start_session(True, causal_consistency=False)
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/mongo_client.py", line 1703, in __start_session
    self._topology._check_implicit_session_support()
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/topology.py", line 538, in _check_implicit_session_support
    self._check_session_support()
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/topology.py", line 554, in _check_session_support
    self._select_servers_loop(
  File "/home/runner/PrivateBot1/venv/lib/python3.10/site-packages/pymongo/topology.py", line 238, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: ac-wdsbot3-shard-00-01.lusopvo.mongodb.net:27017: connection closed,ac-wdsbot3-shard-00-02.lusopvo.mongodb.net:27017: connection closed,ac-wdsbot3-shard-00-00.lusopvo.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 64d779aa7ad14e228a9e958d, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('ac-wdsbot3-shard-00-00.lusopvo.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('ac-wdsbot3-shard-00-00.lusopvo.mongodb.net:27017: connection closed')>, <ServerDescription ('ac-wdsbot3-shard-00-01.lusopvo.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('ac-wdsbot3-shard-00-01.lusopvo.mongodb.net:27017: connection closed')>, <ServerDescription ('ac-wdsbot3-shard-00-02.lusopvo.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('ac-wdsbot3-shard-00-02.lusopvo.mongodb.net:27017: connection closed')>]>```

[frosty relic]

Can i use django to deploy database and making rest api to communicate with source code and the Webserver which is a host for my data?

[wise goblet]

Failed to comprehend your sentence. Restructure to more understandable, please 🙂

[frosty relic]

sorry , i wanna to use django for database and using rest api to communicate with my source code , i mean in my source code sending requests to the using api to the django framework and django do something to handle my requests during the api

[wise goblet]

Can u have Django as rest api? yes
Can u have Django interacting with database? yes
Can u send from requests to Django? yes

[frosty relic]

❤️ Got it tnks

[leaden urchin]

on sqlite If i want to get data that i just commited do i have to execute again or there is other way?

[waxen finch]

sounds like you're looking for a RETURNING clause?
https://sqlite.org/lang_returning.html ```py
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE xyz (x, y DEFAULT 2, z)")
c = conn.execute("INSERT INTO xyz (x, z) VALUES (1, 3) RETURNING x, y, z")
c.fetchone()

(1, 2, 3)```

[leaden urchin]

Basically i need to get the autoincrement id from the row that i just add

c = conn.execute("INSERT INTO xyz (x, z) VALUES (1, 3))
# this will generate autoincrement id for that row```

So how to get the id?

Right now I run execute again to get it

c = conn.execute("SELECT * WHERE x = ?), (1,))
c.fetcone()

[hexed estuary]

I think you can read this attribute instead: https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.lastrowid

[torn sphinx]

Hey I’m new here I have been wanting to learn python for some time nice to meet you all😊

[fading patrol]

Haven't tried but one answer here suggests RETURNING should indeed work for that https://stackoverflow.com/questions/3828098/how-to-return-autoincrement-value-in-insert-query-in-sqlite

[harsh pulsar]

yep RETURNING is super useful and a big performance improvement compared to SELECTing a 2nd time

[lucid crane]

So I have a SQL db for a bot, and currently I'm opening the DB each time a command is run, reading/writing data and closing it. Using aiosqlite would it be possible to keep only 1 connection open for the entire duration of the bot and just commit the data, then close it whenever the bot gets shut off? How would I do that?

My commands.Bot is subclassed if it matters

[soft sinew]

idk if this counts as databases, but is there a way to get the user password / user hash from firebase user authentication?

[wise goblet]

Technically nothing prevents u opening only single connection even without aiosqlite 🙂

Point of async is in ability to open more than 1, multiple connections at the same time and to with with them
Working will single connection and reusing it, should be fine as it is

[lucid crane]

I want to use 1 connection as theoretically it should take less resources and it'll keep the code cleaner

nvm, wrong table

[waxen finch]

i dont recommend using only one connection since you lose proper transaction handling if you attempt to use it concurrently in multiple tasks, and limit your performance if you protect it with a lock - if you care about resource consumption you should benchmark it to see if its outside your tolerances, and you can write a database pool class to put a maximum on allowed connections (setup/teardown with __aenter__/__aexit__)

[lucid crane]

Alr

I'll look into it

Why would "cursor.fetchone()" be returning None?

await cursor.execute("SELECT user_id, start_time FROM cooldown_data WHERE user_id=?",
                     (interaction.user.id,))
is_cooldown = await cursor.fetchone()

ID is mine

[wise goblet]

u can use 1 connection with regular sync library 🤷‍♂️

[lucid crane]

It's a discord bot

[wise goblet]

What? what transaction handling has to do with that?
U can pretty much fine to have separate transactions via single connection (right? 🤔 )

[waxen finch]

but not concurrent transactions

[wise goblet]

mm yeah. that would be troublesome then.

unless reusing pool of connections 🙂

which i am not sure if sync python library supports or not 🤔

https://pynative.com/psycopg2-python-postgresql-connection-pooling/#:~:text=PostgreSQL connection Pool is nothing,application while working with PostgreSQL.

psycopg2.pool.SimpleConnectionPool(minConnection, maxConnection, *args, **kwargs)
psycopg2.pool.ThreadedConnectionPool(minConnection, maxConnection, *args, **kwargs)
psycopg2.pool.PersistentConnectionPool(minConnection, maxConnection, *args, **kwargs)

quick googling yields psycopg2 sync lib supports connection pools 😉

[waxen finch]

hard to tell from that snippet but it could be due to your cursor being in a transaction, though a restart would likely rule that out - in which case, what does it return if you remove the WHERE clause?

[lucid crane]

Same situation

Here's the full code of the command

    @app_commands.command(name="suggest", description="Suggest changes and ideas for the bot and server")
    # @app_commands.checks.dynamic_cooldown(suggest_cooldown)
    async def suggest(self, interaction: discord.Interaction) -> None:
        connection = await aiosqlite.connect("SuggestionData.db")
        cursor: aiosqlite.Cursor = await connection.cursor()
        await cursor.execute(f"SELECT user_id FROM blacklisted_users WHERE user_id=?",
                             (interaction.user.id,))
        is_blacklisted = await cursor.fetchone()
        if is_blacklisted is not None:
            await interaction.response.send_message("You are not alloweed to use this command!", ephemeral=True)
        else:
            await cursor.execute("SELECT user_id, start_time FROM cooldown_data")
                                 # (interaction.user.id,))
            is_cooldown = await cursor.fetchone()
            print(is_cooldown)
            print(interaction.user.id)
            await interaction.response.send_modal(SuggestionModal())
        await connection.close()

The blacklist section works fine if it matters

[waxen finch]

hm doesnt look like a transaction issue, does that row appear on other connections? i.e. refreshing the data on your browser

[lucid trench]

I have an issue on both computers with the connection and MySQL Workbench.

First, I installed MySQL on one computer, connected to the server, but the Server Status window wasn't showing up.
Afterward, I did everything like creating a database, relationships, procedures, functions. When I wanted to export the database, even that window didn't allow me to choose my database.
Then, I installed MySQL again on another computer. Everything worked fine, both connection and exporting. Now, it's not working. The server status isn't showing, and I'm getting these errors.

[violet crater]

so if i have something like

CREATE TABLE
    IF NOT EXISTS genres (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);

CREATE TABLE
    IF NOT EXISTS studios (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);

CREATE TABLE
    IF NOT EXISTS anime_db (
        id INTEGER PRIMARY KEY,
        source TEXT,
        title TEXT,
        synonyms TEXT,
        english TEXT,
        japanese TEXT,
        type INTEGER,
        status INTEGER,
        episode_count INTEGER,
        episode_length INTEGER,
        date_start TEXT,
        date_end TEXT,
        image TEXT,
        age_rating INTEGER,
        genres INTEGER,
        studios INTEGER,
        score REAL,
        popularity INTEGER,
        synopsis TEXT,
        FOREIGN KEY (genres) REFERENCES genres (id),
        FOREIGN KEY (studios) REFERENCES studios (id)
    );

can i insert in all of them in one statement?
that thing is that i get the data from an api

@staticmethod
def parse_node(data: dict[str, Any]):
    node = data["node"]
    return (
        node["id"],
        'myanimelist',
        node["title"],
        str(node["alternative_titles"]["synonyms"] or '') or None,
        node["alternative_titles"]["en"] or None,
        node["alternative_titles"]["ja"] or None,
        node["media_type"],
        node["status"],
        node["num_episodes"],
        node["average_episode_duration"],
        node.get("start_date", "0000-00-00"),
        node.get("end_date", "0000-00-00"),
        node.get("main_picture", {}).get("large", None),
        node.get("rating", "Unknown"),
        str([genre['name'] for genre in node.get('genres', '')] or '') or None,
        str([studio['name'] for studio in node.get('studios', '')] or '') or None,
        node.get("mean", 0),
        node["popularity"],
        node["synopsis"] or None,
    )

ig i have to just duplicate my code

[paper flower]

You can insert them in one statement, since they're different tables

You can do everything in one transaction though

[deep compass]

How does this look for a chat server? I messed up the "is_admin" tag. Its supposed to be in the users table

[paper flower]

Why would message need to reference both server and channel?

[deep compass]

Just easier to filter if I need something

well nvm

I guess it doesnt need it

Since I could just compare from the channels Id in that server

[deep compass]

Other than that is there any suggestions you would make? Im new to DBs

[paper flower]

I think same things applies to permissions, you either need a channel or server here

Also what's the point of hash and salt in channel?

[deep compass]

Well each server can hold channels. Channels can be password protected..nvm

[paper flower]

For password, correct?

You could use a single field e.g. password_hash

Most recent password hashing algorithms store parameters and salt in a single string

e.g. argon2 or bcrypt

[deep compass]

oh, did not know that

very cool

[paper flower]

Don't reinvent hashing, try something like bcrypt or argon2 I'd recommend using passlib for that

Also I would generally name primary key just an id

[deep compass]

Yeah I'll for sure use that. I just didnt know that existed. I used hashlib like 2 yrs ago

[paper flower]

users.user_id -> users.id

[deep compass]

Sure

thanks for the help

other than that does the model look good?

[paper flower]

Schema itself looks good, but it depends on your requirements

[deep compass]

Well, ill have multiple servers probably running around 100 channels each

[paper flower]

Permissions probably need more work if you want to dynamically create and require them

Because there could be individual permissions and permissions assigned to specific users for example

[deep compass]

Yeah i was unsure about that

Since a single user id would be defined multiple times for each channel or server

Like
Channel1 : RedRiver 2
Channel2: RedRiver 2
etc

or something

[paper flower]

class Permission:
    id: int
    server: Server
    name: str
    level: int

class UserPermission:
    permission: Permisson
    user: User
    ...

Then channel can have something like required permissions'

I guess you can look more into how discord works and deduce your schema/requirements from it

[deep compass]

Wouldnt each channel need a table though?

or server

[paper flower]

No, that's almost never the case

Think about table as a class and a row in that table as a class instance

One table can have millions of rows

e.g. channels or messages

[deep compass]

Yeah but if a user was in multiple servers or something wouldnt it look like this?

class Permission:
    id: int
    server_id: int
    channel_id: int
    permission_level: str
    user_id: int

1 123 1 "Mod" 37828
2 654 1 "Mod" 37828
3 754 1 "Mod" 37828

So it wouldnt matter?>

Its just better than having multiple tables

[paper flower]

I would bind permission to specific server, like discord does

Maybe just name it "role"?

you don't really need a channel_id here

[paper flower]

Also this looks fine, but you need to normalize this table a bit

[deep compass]

Well the perm level will actually based on integers

I just used strings for the example

[paper flower]

class Permission:
    id: str
    server_id: int
    name: str
    level: int

class UserPermission:
    permission_id: int
    user_id: int

And you can add a separate table to require specific permission or set of permissions to access a channel

But I guess in this case it's possible for an "anomaly" to occur, e.g. if permission_id and channel are actually in different servers

class ChannelRequiredPermission:
    channel_id: int
    permission_id: int

[deep compass]

Yeah so each server has different channels. They dont interconnect at the moment

Thats the issue

Sorry I should have said that

[paper flower]

I think you can either use composite primary keys or enforce that on application level

[paper flower]

I understand that this shouldn't be the case

[deep compass]

Sholdnt be the case of what?

[paper flower]

That they shouldn't be able to interconnect

[deep compass]

The issue, or them not interconnecting

ah

So try and add it or no?

[paper flower]

Add what?

[deep compass]

Interconnecting /clustering servers

[paper flower]

No, this just sounds complicated 👀

[deep compass]

hmm

1 moment, Ill make a picture

[deep compass]

is that better?

[paper flower]

What's better?

Hm, again

It depends on how you want to organize your permissions and if they're dynamically created

[deep compass]

They are

[paper flower]

if perm_level is only defined in code and users can't create new permission levels, then yes

[deep compass]

Its up to the owner of the channel to determine permissions in the format of integers

[paper flower]

Otherwise you need something akin to discord

[deep compass]

The higher the int level the higher the perms

Its up the channel creator

They could also assign names to integers if I wanted. Would be easy to add

[glacial current]

I would recommend a different software for databases. its debever just because it supports more than mysql. https://dbeaver.io/ where is the server? Hopfully it is not on windows

[wise goblet]

💯 postgresql on linux for the win 😄 in docker-compose for easy dev env setup

[wise goblet]

version: '3.8'
services:
  postgres:
    image: postgres:13.6
    environment:
      POSTGRES_USER: ${PG_USER:-postgres}
      POSTGRES_PASSWORD: ${PG_PASSWORD:-password}
    healthcheck:
      test: [ "CMD", "pg_isready", "-U", "${PG_USER:-postgres}" ]
      interval: 3s
      timeout: 3s
      retries: 5

  redis:
    image: redis
    healthcheck:
      test: [ "CMD", "redis-cli", "--raw", "incr", "ping" ]

  shell:
    build:
      context: .
    depends_on: &depends_on
      redis:
        condition: service_healthy
      postgres:
        condition: service_healthy
    environment:
      PEASANT_REDIS_HOST: 'redis://redis:6379/0'
      CELERY_LOGLEVEL: DEBUG
    command: bash

@lucid trench here is docker-compose to raise it for dev env with docker-compose run shell command 😄

it downloads and raises postgres and redis. awaits them to become healthy and then enters shell of application container (assuming Dockerfile is defined for app)

optionally always an option to forward them to host machine for local access outside of container app

[stuck elm]

Hey I need some help setting up relationships with flask-sqlalchemy can anyone help?

[fading patrol]

#❓｜how-to-get-help

[rancid void]

i give up

[leaden urchin]

Hi, do you know why my string from sqlite return that content \n wont translate as a new line on discord?

[fading patrol]

Probably this I guess. If not, share a paste of your code in a help channel. https://stackoverflow.com/questions/23930865/new-line-character-n-in-sqlite-concatenate

[leaden urchin]

Got it. Im using string replacement to fix that. Thanks

[torn sphinx]

Hello

So I am learning sql

1.) First I runned insert into command
2.) then i run command to view the table
I saw my first command runned two times, and i found a video 0n youtube in which he running these commands without duplicating the table.
How can i do this ?

[oak pumice]

Each time you execute your query command it will insert whatever values you have entered. That's the reasoning behind the duplicate values in your table

[wise goblet]

U can enforce primary key onto id. It will automatically make it unique indexed

Which in its turn will fail records inserted with same id

Primary key is set during table creation
Or alter table command

[tidal mica]

I have a list of ~200 companies with their products and a bot that receives the name of the company and returns the list of it's products+other info. What would be the best way to store that data?

Now I'm setting up an sql table, but maybe it would make more sense to store it elsewhere

Given the small amout of data and the fact that it won't really need to be updated

[waxen finch]

i think regardless of how small your data is or how infrequent it updates, the main benefits of SQL are enforcing a structure on your data and being able to write complex, declarative queries instead of coding it by hand, hence Structured Query Language

if you dont find those worthwhile, a simpler format might be better

[tidal mica]

Okay

[silk raft]

I'm a bit confused on how exactly to handle redis caching in my program (like in a very generic manner), is this how one normally handles caching in applications ? or is there any other better method to do so ?

def get_data_from_cache_or_source(key):

    cached_data = redis_client.get(key)

    if cached_data is not None:
        return cached_data.decode('utf-8')
    else:
        # Fetch data from database
        data = fetch_data_from_source(key)

        redis_client.setex(key, 3600, data) 
        return data

A little bit more context is, I want to retrieve a user object and I was trying to avoid doing db queries by having a redis layer

[waxen finch]

if by "normal handling" you mean just using a cache, then returning a cached value if it exists and fetching otherwise is a pretty normal way to do it - if you're looking to write more maintainable code for caching, for example being able to switch cache implementations quickly, its probably a better question for #software-architecture

[silk raft]

Ah gotcha, thanks

[waxen finch]

@silk raft fwiw this is how ive written caching once, though i consider myself fairly novice at design
https://github.com/thegamecracks/github-release-downloader/blob/a82edd6ee9e8080c5f3b25d110eb3a4d7288b87a/src/grd/client/base.py#L58-L63

to summarize:

• the API client caches responses with their ETag headers so future responses can be skipped without affecting ratelimits
• it takes a response cache object with CRUD methods for interacting with the cache
• the response cache internally uses an sqlalchemy sessionmaker to handle writing to the database
• sqlalchemy abstracts the underlying database so i can use any implementation i want (though SQLite is my most preferable solution)
• i could swap the response cache for a new implementation (e.g. NoSQL), though i haven't written interfaces to abstract the existing Response / ResponseCache classes

[delicate field]

src/grd/client/base.py lines 58 to 63

key = self._get_cache_key(method, url)
cache = self.cache.get(key)
if cache is not None:
    self._add_cache_headers(headers, cache)

response = self.client.request(method, url, *args, headers=headers, **kwargs)```

[deep compass]

@paper flower How does something like this look?

These are all server owned channels so the owner_id is NULL

[paper flower]

Looks good 🤔

[deep compass]

Okay cool

[plucky ice]

if i started a mysql server with xampp on the local internet and i have a python app that use mysql the app will work on another computer? (not the computer where i started the mysql server)

will this work?

[torn sphinx]

Hello

[silk raft]

ooo I was looking for an example like that, very helpful thanks a lot!!

[fading patrol]

By "local internet" I assume you mean "local network"... yes, in theory that should work if you have the right IP, there's no firewall blocking it, etc.

[pastel wren]

I'm reading the owasp page for preventing sql injection and I came across weirdly worded paragraph(weird to me). Could someone help me understand what this is trying to say?

Note: Even when parameterized, stored procedures can still introduce SQL injection if PL/SQL or T-SQL concatenates queries and data or executes hostile data with EXECUTE IMMEDIATE or exec().

[fading patrol]

I wouldn't overthink it. It's basically just saying that you don't want raw user input going straight into to SQL queries without a safe API. If you need more concrete examples and details, check out other documentation like https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html The details depend on your implementation.

[pastel wren]

ah i see. i'll look into that then. thank you

I looked into it and my use case is postgres sql and i'm using sql alchemy to generate my queries. I haven't seen anything in sqlalchemy that says it supports prepared statements but I could be wrong.
postgres sql has support but I would like to know if i'm doing it right if anyone knows.
below is the docs

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
    AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

below is mine

PREPARE planName (text,int) AS
    SELECT * FROM people name, age  WHERE people=$1
    AND age = $2;
EXECUTE planName(John, 15);```
I think I'm doing it right but want some reassurance before I go further down this line of thinking that could be wrong

[fading patrol]

I'm confused... if you're using SQLAlchemy, why are you pasting SQL statements? SQLAlchemy is an ORM so you have that part of the OWASP guide covered

[pastel wren]

I wrote the ones above because I wanted to get an understanding of prepared statements since I couldn't find out how to do prepared statements in SQLAlchemy

[fading patrol]

We don't know where $1 and $2 are coming from so it's impossible to say if they're clean or not.

[pastel wren]

hmm okay. If we don't know if it's clean then it's best to assume it's not clean then. thank you @fading patrol

[fleet pebble]

I get this error when trying to run a long query in excel:
[DataSource.Error] ODBC: ERROR [HY00] [Simba][Presto] (1060) Presto Query Error: The optimizer exhausted the time limit of 180000 ms (65556)

I can't post the query due to privacy, but it's multiple chained sub-queries in one. Would it help if this query was switched to CTEs?

Does this mean the original query is taking too long? (Or are there other things that might cause this error?)

I only found https://github.com/prestodb/presto/issues/9362 and https://github.com/prestodb/presto/issues/15060 but they talk about a IterativeOptimizer which I'm unfamiliar with and not sure if it applies.

[paper flower]

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#prepared-statement-cache

[obtuse magnet]

anyone worked with airflow? https://github.com/BasPH/data-pipelines-with-apache-airflow/tree/master/chapter07

code for this chapter in the book has some errors and i cant seem to find out what it is

seems like something is wrong with the dockerifle pathing

[hollow oar]

yes what's up? could you post error and more context if you still got issues?

[obtuse magnet]

chapter 7 in the code for that book acting up

normaly i just run docker-compose up -d in the directory of the chapter i want

but im getting failed to solve: failed to read dockerfile: open /var/lib/docker/tmp/buildkit-mount2644118272/Dockerfile: no such file or directory now

checked Dockerfile name already

[hollow oar]

that's just an issue with the repo itself, the build in the docker compose yaml is pointed to a path that doesn't exists - let alone the Dockerfile that's supposed to be present inside.

(in https://github.com/BasPH/data-pipelines-with-apache-airflow/blob/d5b18a70d3be23d64e4ec2d3134db9d12445688e/chapter07/docker-compose.yml#L4)

[obtuse magnet]

yep

then theres this PR

https://github.com/BasPH/data-pipelines-with-apache-airflow/pull/54/commits/2e799a7ebf34daf249d26476a050950833d1d112

tried but doesnt work too

[hollow oar]

yes because then this line https://github.com/BasPH/data-pipelines-with-apache-airflow/blob/d5b18a70d3be23d64e4ec2d3134db9d12445688e/chapter07/docker-compose.yml#L13 fails

[delicate field]

chapter07/docker-compose.yml line 13

build: insideairbnb```

[obtuse magnet]

im not very familliar with docker so im not sure what lines should/will fail

[hollow oar]

similiar issue, it's pointed to a directory without Dockerfile

[obtuse magnet]

ive replaced it with insideairbnb/airflow

[hollow oar]

 /tmp/data-pipelines-with-apache-airflow/chapter07   master !1                                                                                                                                                                   13:48:56
❯ find . | grep Docker
./insideairbnb/numbercruncher/Dockerfile
./insideairbnb/airflow/Dockerfile
./insideairbnb/insideairbnb/Dockerfile

i guess it's

diff --git a/chapter07/docker-compose.yml b/chapter07/docker-compose.yml
index f1b467e..b8ef0c0 100644
--- a/chapter07/docker-compose.yml
+++ b/chapter07/docker-compose.yml
@@ -1,7 +1,7 @@
 version: "3.7"
 services:
   airflow:
-    build: ../../docker
+    build: insideairbnb/airflow
     image: manning-airflow:latest
     ports:
       - "8080:8080"
@@ -10,7 +10,7 @@ services:
     volumes:
       - ./dags:/root/airflow/dags
   postgres:
-    build: insideairbnb
+    build: insideairbnb/insideairbnb
     ports:
       - "8080:8080"
     networks:

i.e. note that there are two wrong build args

[obtuse magnet]

wait yeah

makes sense

get this now Error response from daemon: driver failed programming external connectivity on endpoint chapter07-airflow-1 (d5ea818fa35b40fc6c5d271ea306bb9d7b05459a03e40972ffd4fbfb9e95a195): Bind for 0.0.0.0:8080 failed: port is already allocated

oh

hmm

i just replaced line 15 with some other port

[hollow oar]

you need to shutdown things that have been bound to port 8080 first
or change the - "8080:8080" bit, have docker bind it to something other than 8080 on your host, something like 8081:8080

[obtuse magnet]

which 8080:8080

[hollow oar]

what the hell.. now that you said it, why is it both 8080:8080 😑 this makes no sense.

changing either one is fine assuming you don't have something bound to 8080 on host already

[obtuse magnet]

shouldnt be

so it runs

but i cant access airflow on 8080:8080

[hollow oar]

how are you attempting to access airflow?

[obtuse magnet]

http://localhost:8080/ on browser

[hollow oar]

post output of docker ps please

[obtuse magnet]

 PORTS                              NAMES
1e7965f85c1b   chapter07-postgres   "docker-entrypoint.s…"   15 seconds ago   Up 13 seconds  
 5432/tcp, 0.0.0.0:8082->8082/tcp   chapter07-postgres-1```

[hollow oar]

yeah airflow is not running at the moment

docker ps -a to list all docker containers including exited ones

then docker logs <the-container-id-of-airflow>

[obtuse magnet]

                         PORTS                              NAMES
7cae5923bb6c   manning-airflow:latest   "/usr/bin/dumb-init …"   About a minute ago   Exited 
(0) About a minute ago                                      chapter07-airflow-1
1e7965f85c1b   chapter07-postgres       "docker-entrypoint.s…"   About a minute ago   Up About a minute               5432/tcp, 0.0.0.0:8082->8082/tcp   chapter07-postgres-1```

docker logs 7cae5923bb6c doesnt work

[hollow oar]

yeah i don't think this tutorial repo is great with this many broken things 😓

[obtuse magnet]

hahaha was working fine till this chapter, and its accompanaiment for a book

[hollow oar]

docker logs $(docker ps -a | grep manning-airflow | cut -f1 -d' ') this works for me

it just shows the airflow manual page, this implies the Dockerfile for that airflow image is wrong (or the docker compose file)

[obtuse magnet]

operable program. Check the spelling of the name, or if a path was included, verify that     
the path is correct and try again.
At line:1 char:31```

hmm

im running it from powershell terminal in vscode

[hollow oar]

ohhhhh

what does docker logs 7cae5923bb6c show for you?

[obtuse magnet]

positional arguments:
  GROUP_OR_COMMAND

    Groups:
      celery         Celery components
      config         View configuration
      connections    Manage connections
      dags           Manage DAGs
      db             Database operations
      kubernetes     Tools to help run the KubernetesExecutor
      pools          Manage pools
      providers      Display providers
      roles          Manage roles
      tasks          Manage tasks
      users          Manage users
      variables      Manage variables

    Commands:
      cheat-sheet    Display cheat sheet
      info           Show information about current Airflow and environment
      kerberos       Start a kerberos ticket renewer
      plugins        Dump information about loaded plugins
      rotate-fernet-key
                     Rotate encrypted connection credentials and variables
      scheduler      Start a scheduler instance
      sync-perm      Update permissions for existing roles and DAGs
      version        Show the version
      webserver      Start a Airflow webserver instance

optional arguments:
  -h, --help         show this help message and exit```

[hollow oar]

yeah same - that is working as expected

it showed you what were the logs from the container that exited (exited successfully i might add)

this implies the Dockerfile for that airflow image is wrong (or the docker compose file)
this is still true then

[obtuse magnet]

hmm yeah airflow exited

weird

thing is i dont even know where to begin to debug this hahah

[hollow oar]

do you know how to read a dockerfile and the docker compose yaml already?

i know what the issue is but i don't know how to explain it properly if you don't already know how to read dockerfile and docker compose yaml

[obtuse magnet]

roughly i get what it does

[hollow oar]

right okay let me type something up then!

this is what you have built for airflow
https://github.com/BasPH/data-pipelines-with-apache-airflow/blob/master/chapter07/insideairbnb/airflow/Dockerfile#L1-L2C7
docker hub link is here https://hub.docker.com/r/apache/airflow
i can't really quickly find out which dockerfile is it at the moment, but i wager it's based on this https://github.com/apache/airflow/blob/main/Dockerfile
note the

ENTRYPOINT ["/usr/bin/dumb-init", "--", "/entrypoint"]
CMD []

the command that actually is ran in the docker container is
/usr/bin/dumb-init /entrypoint
based on the logs we are seeing, it's extermely likely /entrypoint is just some stuff on top of airflow $@ (i just confirmed this is true - https://github.com/apache/airflow/blob/main/Dockerfile#L772-L1071)

and since CMD is unset or rather set to [], the end result is basically that you have invoked airflow by itself, and airflow in this scenario show its man page to you .

the fix here is to define CMD, you can do this via dockerfile or docker compose yaml
i recommend the later, see https://docs.docker.com/compose/compose-file/compose-file-v3/#command

as to what value should command take, i am not sure, it's likely speific to this tutorial which i don't have time to look through at the moment

and since CMD is unset or rather set to [], the end result is basically that you have invoked airflow by itself, and airflow in this scenario show its man page to you .

to clarify this point:
image we have

ENTRYPOINT ["/usr/bin/dumb-init", "--", "/entrypoint"]
CMD ['lalala']

then we would be doing
/usr/bin/dumb-init -- /entrypoint lalala
which is akin to airflow lalala

[stable ocean]

Hello

[rapid folio]

Hi I have query regarding mongoDB database, the situtation is I have large dataset (~1 million dcouments). Now I want the user to give the freedom to filter and sort the data anyhow they want. what would be the best solution for a situation like this? My first thought was to create indexes for each field in my collection and use index intersection? Lets have a small discussion regarding what would be the best practice for that.

[vale fractal]

Hey guys,

Does anyone know the password resetting process using Supabase in Python ?

My code for now sends an OTP to the registered email, once that OTP is verified, changes the view to where the user can enter a new password and from there I don't know how to proceed, I tried to follow the examples given inside the documentation but they are not working for some reason.

It's a desktop application.

[fading patrol]

The best practice for allowing efficient and flexible queries is to ditch MongoDB and use SQL instead

[rapid folio]

Hahaha right now I am too deep in the show 😂 can't ditch mongo right now

[turbid quarry]

Hello Wich Date base good?

And easy lern?

[silk patio]

MySQL

[turbid quarry]

I want it to work for HTML and be easy

[storm mauve]

"work for HTML"
will not be easy

[turbid quarry]

wich easy>

[turbid quarry]

man can you help me? https://discord.com/channels/267624335836053506/1142162952418504744

[storm mauve]

you might as well just try to use a website builder instead of coding yourself if you need that much information feed to you, but in general you would need to create a web server that receives data from the web browser then stores it in a database

if your server will only run in one machine, you could use SQLite which requires nearly no setting up, but depending on where it'll run you might need to use an external database and connect to it over the internet, which can be annoying to setup

[turbid quarry]

ty

[fading patrol]

Unless you're building a website with large numbers of concurrent users I would stick to SQLite

[wise goblet]

Postgresql is best in average but not easy
SQLite is good enough to start with, has many similarities but far more easier in nature

[turbid quarry]

JUST HELP ME ERROR PYTHON I BROCKE PYTHON HLP

[plucky gale]

How can I get started with data base

Basic of example code

To use python n sql? Django etc

anyone backend n frontend guru? Here done project with it

[wise goblet]

Best to start from fundamentals how to use it. Learning raw SQL and making sense of normalizations

[wise goblet]

Official docs are good for Django
They have tutorial to get started -> https://docs.djangoproject.com/en/4.2/intro/

[wise goblet]

To use python n sql?
in the end databases in python can be used in different ways 😉 depends on what u desire.

I recommend learning SQlite3 and then Postgresql in a raw SQL first before becoming ORMer
With jumping straight to ORM your quality can suffer a lot

[lucid trench]

https://stackoverflow.com/questions/76936186/issue-with-sommand-prompt-workbench-server-status-and-path-variables

[pure mortar]

what i just came across

[delicate field]

:incoming_envelope: :ok_hand: applied timeout to @lucid trench until <t:1692465387:f> (10 minutes) (reason: attachments spam - sent 8 attachments).

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

[wise goblet]

Excellent

[gray surge]

!unmute 904702178265022524

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction timeout for @lucid trench.

[pure mortar]

next up is vector db

[gray surge]

try spacing your messages out next time >.>

[pure mortar]

bet

since ML use cases need fast retrieval of vectors/embeddings

[brazen charm]

Vector DBs are in a bit of a meh state

I havent been very impressed by any of them really, Qdrant is alright but still annoying to scale to larger setups

[pure mortar]

i have an absolute headache with one of the open source ones atm but thats mostly bc we are trying to fit a square peg into a round hole

if anyone is actually interested (lurkers), this guy did a 3-part deep dive into them. good reference imo https://thedataquarry.com/posts/vector-db-1/

[brazen charm]

Are you using a vector db for work?

[pure mortar]

trying to

chroma

might look at lance too

[brazen charm]

interesting

At work I imagine we will at some point look into them

and I am going to largely object to it 😅

[pure mortar]

honestly just like in the first blog post

if your use case allows for it

[brazen charm]

Because it's going to burn through money in order to be useful

[pure mortar]

it might be better to use something like redis search/postgres pg vector/etc.

[brazen charm]

None of them scale well enough

[pure mortar]

yes, its essentially an add-on but hopefully you dont need that scale

oh jk

rip

[brazen charm]

What we'd need is something akin to a Scylla/Cassandra of the vector DB world

but that's a hard, and expensive ask

[pure mortar]

bro that doesnt exist yet

lol

[brazen charm]

just on a 1:1 mapping, i'd expect a vector DB to be about 10x slower or more which is about right

[brazen charm]

I've been painfully watching the research space hoping that with all this investor money we might see some attention on the re-iterations of HNSW

[pure mortar]

ah yes

[brazen charm]

HNSW is cool, but there are definitely more compact indexes now which build on it, although the index is only half the problem, I'm not hugely convinced incremental building is the way, rather incremental batch building

[pearl lodge]

okay so... i'm missing something here:

like i want to seach if name contain what is in name variable

[brazen charm]

PyNNDescent proved itself that indexing of vectors is just miles faster in batches than incrementally doing it with HNSW

[pearl lodge]

so it won't be literally name

[brazen charm]

needs to be text

[pearl lodge]

like a literal IS

[pure mortar]

yeah agreed

[brazen charm]

as in like '$regex'

[pearl lodge]

well, name is a string variable

i don't know how i adapt it

[brazen charm]

another day, another reason why mongo needs to not just rely on JS' way of object attributes

[pearl lodge]

i'm trying this:
https://stackoverflow.com/questions/63703434/find-documents-in-a-collection-that-contain-some-letters-in-a-variable-in-mongod

[pure mortar]

speaking of indexes, that was actually part 3 of the blog https://thedataquarry.com/posts/vector-db-3/#popular-indexes

[brazen charm]

basically JS lets you do this {foo: "abr"} for the equivalent {"foo": "abr"}, python doesn't, you have a syntax error.

[pearl lodge]

i see

thought how do makes motor search by content?

[brazen charm]

regex probably works

[pearl lodge]

i changed it to this:

[brazen charm]

It will be slow tho, relatively speaking

[pearl lodge]

will it work?

[brazen charm]

try it and see

Btw running user input based regex on your DB tends to not have a great time, unfortunately what you probably really want is a prefix search. But mongo doesnt offer that IIRC

[pearl lodge]

yay it works

actually not:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\ext\commands\hybrid.py", line 438, in _invoke_with_namespace
value = await self._do_call(ctx, ctx.kwargs) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\app_commands\commands.py", line 846, in _do_call
raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'search' raised an exception: IndexError: list index out of range

] File "c:\Users\User\Documents\GitHub\RP-Utilities\cogs\CharactersCog.py", line 47, in _character_search
if search_pivot and search_result[0] is None:
~~~~~~~~~~~~~^^^
IndexError: list index out of range

oh now i see

oh yeah baby:

I created an eternal loop
https://paste.pythondiscord.com/K3XA

[brazen charm]

Can't really help without you pasting the code

[pearl lodge]

https://paste.pythondiscord.com/JLCA

there

guess you probably need my db

https://paste.pythondiscord.com/UOFA

[pearl lodge]

i discovered why

i should had used sub_result instead of result

this idea made me frick myself up

okay so, i will need to check up how to send embed correctly

[brazen charm]

embed=embed

you pass it as a kwarg

also sorry I forgot to re-check the convo after getting food

[wanton citrus]

Hello, is anyone experienced with geojson files? im trying to write a script to convert a geojson file to a sql database using a python script

[hollow oar]

just ask your actual question and provide more context 🙂 i am sure someone will be able to help

[wanton citrus]

i have a database of storm water drains in a geojson file. I am trying to create a script that can convert the geojson file to an sql file but i have no clue where to start

[hollow oar]

first step is to clearly define what you want, what exactly is a sql file to you?

[wanton citrus]

i want to make it an sql file as i know how to work with them in python, i have no idea how to work with geojson files. If someone could teach me how to that would be nice

[silk raft]

This ones a little bit unrelated to python itself and more into the databases part. I've lately be facing this issue where my postgres database just shuts down on its own ?

This is on ubuntu PG 14 and when I go through the log files, I only find this in the end

2023-08-20 23:35:06.313 +08 [2702519] postgres@postgres FATAL:  password authentication failed for user "postgres"
2023-08-20 23:35:06.313 +08 [2702519] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 99: "host  all             all             0.0.0.0/0md5"
2023-08-21 00:01:34.005 +08 [2703082] postgres@bbbbbbb FATAL:  expected SASL response, got message type 0
2023-08-21 00:31:37.653 +08 [2703544] [unknown]@[unknown] LOG:  invalid length of startup packet
2023-08-21 01:39:31.566 +08 [2704571] [unknown]@[unknown] LOG:  could not accept SSL connection: unsupported protocol
2023-08-21 01:39:31.566 +08 [2704571] [unknown]@[unknown] HINT:  This may indicate that the client does not support any SSL protocol version between TLSv1.2 and TLSv1.3.
2023-08-21 05:52:18.765 +08 [2698202] LOG:  received smart shutdown request
2023-08-21 05:52:18.771 +08 [2698202] LOG:  background worker "logical replication launcher" (PID 2698209) exited with exit code 1
2023-08-21 05:52:18.839 +08 [2698204] LOG:  shutting down
2023-08-21 05:52:18.859 +08 [2698202] LOG:  database system is shut down

This has happened for a while and idk why its shutting down or whats shutting it down, Can anyone help me figure out the issue ?

[silk raft]

It would work fine for a while if I restarted it using systemctl and then die again

[hollow oar]

is there any resource pressure on your host?
have you check dmesg for example?

i would also check that your host is not actually compromised.. this looks a little suspicious..
check auth log and see if anyone ssh'd in for example..

[robust grotto]

I am working on a python app with Alembic , it requires interaction with 2 different databases and 2 distinct migration chains (one doesn't affect the other).

So far I know how to work with multiple bases / branches, tried to build 2 branches , each branch to different database, the first migration was successfully upgraded to database, but the second one failed with error alembic.util.exc.CommandError: Target database is not up to date. .

My question is : Is it possible to run migration steps to 2 different databases in a single setup ? (only one folder {project_home}/migrations in the app)

[the history and heads in my current migration setup]

000002 (branch123) (head)
00001 (branch456) (head)

app0000 (app0000) -> 000002 (branch123) (head), init-low-level-permissions
app0000 (app0000) -> 00001 (branch456) (head), init-app-tables
<base> -> app0000 (branchpoint), msg

[silk raft]

Nope, the servers idle most of the time. I looked through dmesg and couldnt find anything weird although I couldnt interpret the output properly as well

I've also looked into auth logs and everything seems fine, only the database service seems to shut down automatically for a weird reason and its not like the system reboots as well, theres an uptime of 90 days so far

[hollow oar]

do you have any other application running?

also have you tried using systemtap to find out what is causing the presumably SIGTERM signal being sent to postgres?
see a similiar example here: https://www.percona.com/blog/systemtap-solves-phantom-mysqld-sigterm-sigkill-issue/

[quiet orchid]

Hello! Just getting started with SQLite3, and while inserting some data from a json file i get this error

here is the first element of the json file

{"id":"2","departure":"Abu Dhabi","destination":"Chicago-O'Hare\r"}

and here is my code

import json
import sqlite3

db = sqlite3.connect("routes.db")
cur = db.cursor()

f=open("aaa.json","r")

data = json.load(f)
for i in data:
    name= f"AAA+ {i['id']}"
    cur.execute(f"INSERT INTO routes VALUES({name}, {i['departure']}, {i['destination']})")
    db.commit()

[hexed estuary]

Don't use f-strings to interpolate values into sql queries - as you just discovered, it's tricky to get right, but more importantly it's a massive security risk (that's how sql injections happen). Use placeholders instead, that's what they're for.

cur.execute(f"INSERT INTO routes VALUES(?, ?, ?)", (name, i['departure'], i['destination']))

[quiet orchid]

Thanks!

[hexed estuary]

Oh and by the way, you can do this in a single executemany, which might be more performant.

[quiet orchid]

Executemany returned an error: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 3, and there are 6 supplied.

[hexed estuary]

Sounds like you passed it 6 values in a list, whereas it wants a list of iterables each of which is 3-value.

like, [a,b,c,d,e,f] vs [(a,b,c), (d,e,f)].

[quiet orchid]

Right, ill look into it, thanks!

[pearl lodge]

ERROR    discord.ext.commands.bot Ignoring exception in command character default edit_name
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\app_commands\commands.py", line 827, in _do_call
    return await self._callback(self.binding, interaction, **params)  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\User\Documents\GitHub\RP-Utilities\cogs\CharactersCog.py", line 133, in _character_default_edit_name
    result = await ctx.bot.database.update_default_character(user_id = user, old_name = old_name, new_name=new_name)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\Users\User\Documents\GitHub\RP-Utilities\rpu_database.py", line 151, in update_default_character
    await self.db.characters.update_one({
  File "C:\Users\User\AppData\Local\Programs\Python\Python311\Lib\concurrent\futures\thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: Collection.update_one() missing 1 required positional argument: 'update'

okay this one is strange

i believe i will need to send my database and my code here

https://paste.pythondiscord.com/FQYQ

[rustic stone]

Can someone help me

If so dm me

[finite lily]

exactly how sacrilegious is this?

q = f"select {GuildSchema.fields_csv()} from {self.table};"

[storm mauve]

if you are being ultra careful to prevent injections deriving from the Schema / from self.table, not that bad

makes it sound like you have a table per guild though? which I cannot really recommend

(if so, just add guild_id as a column, having a single GuildSomething table instead of having multiple tables for each guild, and maybe add it as part of the Primary Key & any custom Indexes you may have)

[finite lily]

making kind of a pseudo repository

class GuildSchema(BaseModel):
    id: Optional[int] = None
    created: datetime
    updated: datetime
    guild_id: int
    guild_name: str

    @classmethod
    def fields_csv(cls):
        return ', '.join(cls.__fields__.keys())

I just wasn't sure if I should be explicit in the sql statements for the repository functions, or if I could do something like that and sort of guard against ensuring the schema class is updated

[pearl lodge]

okay soo, how do i do it correctly?

in my case here i want to update name:

                await self.db.characters.update_one({
                    'user_id': user_id,
                    'name': new_name,
                    'prompt_prefix': documents['prompt_prefix'],
                    'image_url': documents['image_url']
                })

[quiet orchid]

But of a stupid question but how do i output what i select with SQLITE3?

[pearl lodge]

SELECT (arguments) FROM (table)

or you can use

SELECT * FROM (table)

[quiet orchid]

I have the SQL command, i just dont know how i can see what i select

[pearl lodge]

try printing it

[quiet orchid]

import sqlite3

db = sqlite3.connect("routes.db")
cur = db.cursor()
cur.execute("SELECT DISTINCT departure, destination FROM routes")
db.commit()

printing it just said cursor at and then some adress

[pearl lodge]

oh you need to fetch

cus that is a cursor

you need a fetch

[quiet orchid]

is it the fetchall() command?

[pearl lodge]

yep, and there is also some other fetch command

if not, you can use fetchall

actually it's fetch all

since it's more than 1

[quiet orchid]

I got it to work

[pearl lodge]

also Ozzy, do you know mongodb?

[pearl lodge]

i need help on this

[quiet orchid]

Sadly no, im quite new to this

[wind beacon]

Try

update_one({"user_id": user_id}, {"$set": {'name': new_name}})

[harsh pulsar]

pymongo/motor is a surprisingly thin wrapper over mongodb itself

lots of $ in dict keys

[brazen charm]

tbh i actually quite like that it does that

it makes it very easy to work out what is and what isn't a DB operation vs some data

[harsh pulsar]

yeah it's not bad. a higher-level query builder wouldn't hurt either though

i like both

[slender atlas]

Use an independent database

[fading patrol]

A VPS with 1GB of storage is near-useless, so you could get a better one.

[slate basin]

Hmm

[fading patrol]

There are loads of free managed Postgres providers if you want to go that route

[slate basin]

I just use it for testing lol

[fading patrol]

Do you even need a DB that large for your testing then? Unless you have a ton of traffic I'm thinking you might storing a lot of uneccsary data in your DB

[wicked glen]

Hey anyone who knows notification/email sender system. I want to create message sender. I've some question. Can you help me? https://discord.com/channels/267624335836053506/1143559638688727151

[elfin leaf]

Hi, if we're using mongo db for commercial purposes on our servers, do we require a license for it? If yes, how much does it cost usually?

[hearty siren]

who can explain tables relationships as if ii were 5

[brazen charm]

no, you can run community edition on prem, the the edition grants some extra features and support.

[elfin leaf]

I kind of need to know about the enterprise license, it's for work

How much does the enterprise license cost?

[torn sphinx]

It’s hard to explain

[brazen charm]

A lot, it's normally priced by the GB and the size of the machine(s) your running it on

[elfin leaf]

Where can I check?

[brazen charm]

You have to contact them and request a quote after consulting with them

https://www.mongodb.com/contact

[elfin leaf]

Okay thanks, I'll check

[brazen charm]

I would assume if you're looking into 1TB of data, it going to be in the thousands of dollars range +

For reference their cloud offering pricing would start you on around $87k a year. More realistically though I think you'd be a bit bigger specs so probably close to $120k a year on their cloud

[elfin leaf]

Wow that's expensive

[brazen charm]

Yes that is what DBAAS pricing is like, or cloud in general tbh. In servers alone on AWS I think it makes up 1/3rd of that cost

[elfin leaf]

I'll let my team know

So we're pretty much well off with community edition too right?

What I want to know is do we need any license if we're using community version for commercial use?

[brazen charm]

No, you can use it as it fine for commerical use

it's if you want to say use their Kubernetes charts or have paid support etc...

thats when you would be looking at their enterprise stuff

[elfin leaf]

Okay, so I'll clear this up with my manager tomorrow

[brazen charm]

Also if you modify the source code, but I doubt that is ever going to happy for your

Realistically the way they get you is scaling the thing is harder than most expect, so people would rather pay to have them manage scaling and backups etc...

[elfin leaf]

Mongo db source code you mean?

[brazen charm]

yeah

[elfin leaf]

Yep, I've suggested using document db which is db server as a service
But we're probably gonna lean towards running mongo on an ec2 instance based on desired cpu and memory

[brazen charm]

it somewhat depends on what you're doing, but in general I still lean towards postgres over mongo, if that fails it tends to be ScyllaDB/Cassandra instead. It depends on your size and scale, really to affect whether document DB is worth it or running locally and accepting the extra dev work associated with that

Document DB also isn't the same as mongo really, AWS have a compatible API, but it might differ in subtle ways

[elfin leaf]

Right now we don't have plans for postgres since we've been using nosql based mongo since the start

But I'll keep this in mind

[brazen charm]

If your stuff is already all based around mongo, then might as well stick with it

[hearty siren]

😭

[grim haven]

I'm a beginner Python programmer. I'm working on a program that provides Italian verb forms. Typically, that means my data looks like this (for the verb "andare"):

{0: "vado", 1: "vai", 2: "va", 3: "andiamo", 4: "andate", 5: "vanno"}

But sometimes, there are multiple possible forms. For example, for the verb "dare", the past historic tense has two forms for the 0 key: diedi & detti. I want to include both forms and provide an explanation that diedi is the modern form and detti is the traditional form.

I think this is what I should do:

{0: {"diedi": "modern", "detti": "traditional}, 1 ...

Is this a reasonable approach? If so, when the code is pulling the data, it has to test whether the key has just a plain form in quotes (like vado/vai/vai above) or a subdictionary (like diedi/detti below). What's the best way to do that?

[real fractal]

.

[glacial current]

Typically there is no typical way 🙂 . but how about you have a structure of
{"topic" : [elements..], "topic": [elements..] }
so you may have
[{"alternatives":["a","b"], "modern":['diedi','whatever'], 'traditional':['detti']}, {...}] that way if there is no 'modern' examples then that is a simple test of

if 'modern' in data:
    do something modern

and you dont have to test if you are just displaying the results

for example in data_query:
  print("alternatives", ",".join(example['alternatives']))
  print("modern", ",".join(example['modern']))
  print("traditional", ",".join(example['traditional']))

[grim haven]

Thank you for that. I oversimplified the data structure in my question. The whole thing looks like this (all this data is made up):

  "ES": {##Spanish verbs},
  "IT": {
      "andare": {
          "translations": ["EN": "go", "ES": "ir"],
          "conjugations": {
            "present": {0: "vado", 1: "vai", 2: "va", 5: "vanno"},
            "past historic": {5: "andranno"}
    } 
  } 
}```

Only the irregular forms are included because the regular forms can be automatically generated. (The numbers refer to the pronouns.) So "andare" has only four irregular forms  in the present tense and one irregular form in the past historic tense.

Now I need to add alternative forms. Let's say that vado, vai and va each has an alternative form. Then I have to describe what vado, vai and va are (modern or whatever), and I have to give the alt forms and describe what they are. 

Using your suggestion, I think it would look like this: 
```..."andare": {
   "translations": {}
   "conjugations": {
      "present": {
         "forms": {0: "vado", 1: "vai", 2: "va", 5: "vanno"},
         "form descriptions": {0: "modern", 1: "Sicily", 2: "Northern Italy"}
         "form alternatives": {0: "vao", 1: "vate", 2: "và"}
          "form alternative descriptions": {0: "archaic", 1: "Lazio", 2: "Torino"}
      }
    }
}```

The pronoun numbers make it possible to link the forms with the descriptions, the alt forms and the alt form descriptions. 

Is that a reasonable way to handle all this? I'm worried I'm going down a rabbit hole and will never come back up again.

[foggy tartan]

does this channel help with Mysql with python?

[spice socket]

Hi everyone! I created an open source basic database module for python using python. I am open to feedbacks, forks, and pull requests since oldest line is just 1 days old and just finished first release.

https://github.com/islekcaganmert/baseofdata

Note: Currently I have no more time, I need to create a website using bevyframe (my python module for web) or flask. When I find some time, I will add support for more variable types. Currently only lists and dictionaries are in my plan but if you think more is needed, you can create issue, post on HereUS community of it, DM me from discord, or ping me from this server.

Note for moderators: I red rules and code of conduct again but it not says I cannot tell about my open source project. If this breaks rules, please warn me and I will remove this message.

[foggy tartan]

?

I needed help with one of my highschool project

so if anyone is willing pls ping me

[waxen finch]

this is the correct channel for such a topic, ask here and someone can help

[foggy tartan]

Hi so I have a highschool project of making a car rental system using mysql and python connectivity

and im making a login page rn

and what problem im having is

cr.execute('create database if not exists CruiseWave')
cr.execute('use CruiseWave')
cr.execute('create table if not exists userdata(email varchar(255), username varchar(255), password varchar(255), LicenseNo int(10))')
cr.execute('create table if not exists data(username char(255), Cars_ordered varchar(255), payment char(255))')

#Functions

def check_username(username):
cr.execute('select* from userdata where username = "%s"'%(input))
data = cr.fetchall()
con.commit()
if data != []:
new = False
return True

def check_password(ent_pass):
global ret_pass
cr.execute(f"select password from userdata where username='{input}'")
ret_pass = cr.fetchone()
str_pass = str(ret_pass)
print(str_pass)
con.commit()
if ret_pass == ent_pass:
return True
else:
return False

def login_screen():
global input
global ent_pass
print('Welcome to CruiseWave\nYour key to mobility and adventure')
input = input('Enter username to sign up or sign in: ')
check = check_username(input)
if check == True:
print('Welcome back',input)
ent_pass = pwinput.pwinput(prompt='Please enter your password: ')
check_password(ent_pass)
if check_password == True:
print("Hold on while we fetch your details....")
print("All done")
else:
print("Incorrect password, Please try again")

login_screen()

this is my code

when I enter the correct password as well it says incorrect password

i think the problem is cr.fetchall() gives u list of tuples

but my password which i enter is a string

[waxen finch]

seems about right, cr.fetchone() returns a tuple containing the columns you wanted from your query

[foggy tartan]

yea and i use ent_pass = pwinput.pwinput(prompt='Please enter your password: ')

so this is a string right

[waxen finch]

yup

[foggy tartan]

so can string == tuple possible?

[waxen finch]

no, you would have to index the tuple to get the value of the password column from your row

[foggy tartan]

mhm so what do I do?

('123',) is my test password rn

I prefer my password to be in letters

I tried converting my entered password to tuples but it just splits it

[waxen finch]

!e py row = ("your password here",) # as returned by cursor.fetchone() stored_password = row[0] print(f"Your password is: {stored_password}")

[delicate field]

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

Your password is: your password here

[waxen finch]

see also how to use lists because indexing tuples work similarly to lists - the main difference being that you can't add/remove values from a tuple

[foggy tartan]

oh so I could index the tuple and index the entered password to see if each letter is same?

[waxen finch]

if you compare two strings, python will check letter by letter for you

you only need to get the string out of your database row so you can compare it to the string from pwinput()

[foggy tartan]

but it is a tuple?

[waxen finch]

fetchone() returns a tuple containing a string which is the stored password, so you need to index your tuple to get that password string inside it

[foggy tartan]

mhm okay

[waxen finch]

btw we're getting off-topic now, this question is actually better suited for #1035199133436354600

[foggy tartan]

oh ok

[quiet orchid]

Can i use UTF-8 for SQLite3

If i cant, is there a python SQL DB i can use where Medellín-José María Cordóva doesnt become Medellín-José María Córdova

[slender atlas]

What are you doing with that text that it becomes corrupt

That shouldn't happen

[quiet orchid]

I do not know where it comes from, but it goes from "Medellín-José María Cordóva" in my JSON file to that abomination in the database

f=open("LTN.json","r")

data = json.load(f)
for i in data:
    name= f"LTN{i['id']}"
    cur.executemany(f"INSERT INTO routes VALUES(?, ?, ?)", [(name, i['departure'], i['destination'])])
    db.commit()

Have i missed a step here?

or is it just sqlite3

[slender atlas]

First, you can use execute instead of executemany

[quiet orchid]

I know

But it was much slower

I have over 65000 routes to enter so, it does make a difference

[slender atlas]

Just prepare many names and destinations and do executemany outside the loop

[quiet orchid]

Is that what causes it?

[steady saffron]

You should probably commit after the loop. Commits are incredibly expensive

[slender atlas]

with open("LTN.json", encoding="utf-8") as f:
    data = json.load(f)
params = [(f"LTN{i['id']}", i["departure"], i["destination"]) for i in data]
cur.executemany("INSERT INTO routes VALUES(?, ?, ?)", params)
db.commit()

[quiet orchid]

oh yeah i missed that

[slender atlas]

Something like this should at least make it somewhat faster

If I understood how it works

Also, you didn't make sure to open your file with encoding="utf-8" which might have explained the weird corruption

JSON is exclusively in UTF-8

[quiet orchid]

Ahhhh, ill give that a shot

[steady saffron]

JSON in the wild can be encoded with weird encodings, but UTF-8 is a safe bet

[slender atlas]

I thought JSON is exclusively UTF-8

[steady saffron]

You should print the loaded contents on Python and make sure it's right. That'll help you figure out where the characters are being messed up.

SQLite does support Unicode test storage and retrieval

[quiet orchid]

oh my god thats so much quicker

like 80x quicker

[steady saffron]

Yeah, people don't always follow the specifications

[slender atlas]

😦

[glacial current]

It dosnt look too bad but the more more nested it is , the more nested your code logic will be. I would keep it flat as possible. I'm also not sure what the number field is doing but these can be flatten out into tuples (0,"vado") that you can use as keys conjugations[(0,"vado")] = ["present"]. So that you can collapse each item into a lookup like tags rock, pop could be added to a song. Tuples can have many dimensions and you can build up a key from those dimensions to get the properties or tags relating to it. On phone so can't really make this post nice 🙂

[grim haven]

The numbers refer to the pronoun. These languages have six or seven verb forms. “I” is 0, “you” is 1, “s/he/it” is 2, etc.

For some verbs, a tense is completely irregular and I have to record each one. But usually not all the forms are irregular, so I just include the irregular forms.

But then I need descriptions, alternative forms and alternative form descriptions.

Can you suggest a way to do that? The tuple idea looks interesting but I’m not sure how to implement it.

[glacial current]

Sure, When I get back to a computer I will write some example

[grim haven]

I looked a little at tuples. There's no guarantee that the numbers will be in order, so they might not be of much help.

For example, I might have a verb like "andare": { "translations": {##data here##} "conjugations": { "present": { "forms": {2: "va", 5: "vanno"}, "form descriptions": {2: "Northern Italy"} "form alternatives": {2: "và"} "form alternative descriptions": {2: "Torino"} } } }

I've thought about using nested dictionaries, but that seems messy. But it seems like the data is messy no matter how I do it.

The other thing I've considered is combining items together with a separator. Using the example above:

"forms": {2: "va#Northern Italy#và#Torino", 5: "vanno"},

This way, I have to parse the data item, but it's probably less complicated than dictionaries or tuples.

[glacial current]

Discord swallowed my repy as it was too big. here is a way to add dimension to data though a tuple index.

data = [
    "Hungary/20170406/honeydew",
    "Albania/20170409/banana",
    "Hungary/20170403/fig",
    "Ecuador/20170402/elderberry",
    "India/20170405/honeydew",
    "Denmark/20170407/apple",
    "Germany/20170409/cherry",
    "Albania/20170405/imbe",
    "India/20170406/honeydew",
    "Albania/20170401/cherry"
]



data = {}
for item in data:
    cty_date_fruit = tuple(item.split('/'))
    data[ cty_date_fruit ] = item
    
# This is shorthand for the above
# data = {tuple(item.split('/')):item for item in data}

for key, val in data.items():
    print(key, val)
## data looks like this now.
# {
#  ('Hungary', '20170406', 'honeydew') : "Hungary/20170406/honeydew"
#  ('Albania', '20170409', 'banana')  : "Albania/20170409/banana"
#  ...
# }

#adding new dimensions to the data
fruit_color_mapping = {
    'apple': 'red',
    'banana': 'yellow',
    'cherry': 'red',
    'durian': 'green',
    'elderberry': 'purple',
    'fig': 'purple',
    'grapefruit': 'pink',
    'honeydew': 'green',
    'imbe': 'orange',
    'jackfruit': 'yellow'
}


def query_by_color(data, color_mapping, colors):
    result = []
    for key, val in data.items():
        if color_mapping[ key[2] ] in colors:
            result.append(val)
    return result

query_color = 'purple', 'green'

# query the data with this new dimension color
for item in  query_by_color(data, fruit_color_mapping, query_color):
    print(item)

i think you should do what you think is comfortable. if you start to get into trouble then you could try this technique. You should also try experimenting with classes when the data has more than 3 dimensions.

[glacial current]

what is swallowed was this. your data is similar in structure to a dir. but you need to link things from leg to leg and your code can get spegatti like (no pun intended) 🙂

"""
andare/translations/English/go
andare/translations/English/goes
andare/translations/English/going
andare/translations/English/gone
andare/conjugations/present/forms/va
andare/conjugations/present/forms/vanno
andare/conjugations/present/form descriptions/Northern Italy
andare/conjugations/present/form alternatives/và
andare/conjugations/present/form alternative descriptions/Torino
"""

so you can add a 2 or more datasets together by linking them with tuple indexes. And it isolates the complex stuff into that function. so its manageable if data changes, to alter the functions that query that data.

[grim haven]

This is how I'm inclined to do it, then.

  "ES": {##Spanish verbs},
  "IT": {
      "andare": {
          "translations": "EN/go/ES/ir",
          "conjugations": {
            "present": {0: "vado", 1: "vai", 2: "va#Milan#và#Rome", 5: "vanno/modern/vànno/archaic"},
    } 
  } 
}```

I don't like all the levels, but I don't think I can make it any flatter without making the data entry difficult. (Verbs can have more than 10 tenses, so I need some depth to capture that.)

I'm using classes to process and return the data to the main program.

[grim haven]

I didn't understand this part of your reply.

One worry I have is that when I have more than 10 Spanish verbs and 10 Italian verbs, the VERBS variable will be really long. Are you saying there's a way I could separate out sections of my database and then combine them when I need to?

[glacial current]

You have not talked about a database yet. You mentioned how to program this in python. So it looks like you have json data structure that came from someplace, and when you load that into a dictionary you are going to have to come up with logic to assemble it. What you have a structure of paths to the data right now. you can reverse that into a lookup table to the word though a series of tuples. so a tuple of
data[('andare','conjugations','present','forms')] = ['vanno', 'vano2', etc]
and you can add more to that from other tables.

you can also query values for 'vano' and list all the keys (the tuples) that match vanno you have flattened the data into a series of properties. and you can build different function to get at that data which can be broken down into manageable functions that build a result set in what everway you want to see the data.

My example shows how a color that is not in this dataset could be incorporated from a different dataset to give extra meaning to your data, so if later you think i need to reorg the data. well you can because you flattened all that data structure into a key val pair and you can reorg it into a new table later, that is better than your 1st attempt .

[glacial current]

So start whichever way you want and code it whatever way you want. and know that there is a way to unravel your data into a simpler form when or if that doesnt work out by using tuples to reorg.

[void fractal]

Is it common for data collection to be relational databases with analytics is non relational?

[harsh pulsar]

sort of. nowadays a lot of teams have a "data warehouse" that looks and feels relational but internally might be something else.

it's usually overkill for smaller organizations. but it is very common to have production data flow into one database, and for analysis you extract it and work on it in a completely separate system.

[coral wasp]

The divide you’re referring to is really: transactional (oltp) vs analytical (olap), not relational/non relational. OLAP databases today are columnar and optimized for vector operations, and often include distributed processing capabilities…. Whereas OLTP database are row oriented and optimized for low latency read and writes, along with higher availability and reliability.

[coral wasp]

And for data collection: all sorts of databases are used. Depending on the data. There’s also a trend for parquet/hive partitioned data feeds, which avoids (defers?) a db in the collection pipeline

[frosty mirage]

SQLite only does Unicode-based text encodings, according to this: https://sqlite.org/datatype3.html UTF-8, UTF-16BE or UTF-16LE.

[quiet orchid]

Yep i know, the issue came from the json parsing

[torn sphinx]

hello nice people, how to make the database commit instantly, because it only saves changes after the send_message() func ends (which doesn't happen because it's an infinite loop)

def send_message():
  while True :

    messageBox = input("Please Enter Your Message: ")

    cur.execute(f"INSERT INTO Messages VALUES ('{currentUser}', '{messageBox}')")
    conn.commit()

[grim haven]

I was actually intending this to be my database. I haven’t learned how to hook up to a real database yet.

[glacial current]

Then you might want to invest some time into looking at a database. Because SQL language may solve your problem better than json data structure.

[grim haven]

OK, thank you. I appreciate all the help. I didn't intend to go off on a wild goose chase. 😅

[carmine tiger]

anyone here very good with sqlite databses (in particular peewee)?

really need some help and noone knew the answer to my question in #1035199133436354600

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

[fathom star]

How do I enforce uniqueness in a one-to-many, but not across at a database level?
Say I have shopping carts, and each shopping cart can have N items
I don't want duplicate items in each shopping cart, but there can be duplicates across many shopping carts
I.e
Cart 1 has items X, Y, Z
Cart 2 has items W, X, Y
But cart 1 can't have X, X, Y

[coral wasp]

That's a many to many, tho. @fathom star

In that case, you just need a primary_key/unique_key on the cart_item table on both columns: (cart_id, item_id) . This would make sure that a cart can only have one of an item.

[coral wasp]

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE cart(id int);')
cursor.execute("CREATE TABLE item(id int, name varchar);")
cursor.execute("CREATE TABLE cart_item(cart_id int, item_id int, PRIMARY KEY (cart_id, item_id));")
cursor.execute("INSERT INTO cart (id) VALUES (1), (2);")
cursor.execute("INSERT INTO item (id, name) VALUES (1, 'X'), (2, 'Y'), (3, 'Z'), (4, 'W');")
cursor.execute("INSERT INTO cart_item (cart_id, item_id) VALUES (1, 1), (1, 2), (1, 3), (2, 4), (2, 1), (2, 2);")

try:
    cursor.execute("INSERT INTO cart_item (cart_id, item_id) VALUES (1, 1)")
except Exception as e:
    print(f"Couldn't add X into Cart 1 (again): {e}")

connection.commit()
connection.close()

[fathom star]

Gotcha, I'll give that a try. Thanks!

[harsh pulsar]

i'm not sure if something weird happens when you share a cursor across commits. try creating a fresh cursor for each query, and make sure that the commit is inside the while loop

[hexed estuary]

What's the good-practices for storing the full history of something (e.g. prices of goods at locations), and also having the last values quickly accessible? Would having a separate table for latest values be a good idea, or just add some index and do some messy groupby each time to get the last values for each item-location pair?

[coral wasp]

Just depends on update and access frequency. In many cases like this, I’d(previous life) keep a price table and a separate price history

In general, you want to keep history away from your primary tables.

[waxen finch]

should i be concerned with performance/readability when attempting to insert any row dependencies whenever possible? for example running a query like: sql WITH insert_guild AS ( INSERT INTO guild (id) VALUES ($1) ON CONFLICT DO NOTHING ), insert_channel AS ( INSERT INTO channel (id, guild_id) VALUES ($2, $1) ON CONFLICT DO NOTHING ), insert_user AS ( INSERT INTO "user" (id) VALUES ($3) ON CONFLICT DO NOTHING ) INSERT INTO message (id, channel_id, user_id) VALUES ($4, $2, $3) ON CONFLICT DO NOTHING; for the following schema:

[glad folio]

hey i am working with graphql

from gql import gql, Client
from gql.transport.aiohttp import AIOHTTPTransport


#select the endpoint

headers = {
    "Content-Type": "application/json"
}

transport = AIOHTTPTransport(url="graqlurlspaceholder, headers=headers)
# Create Graphql client

client = Client(transport=transport,fetch_schema_from_transport=False)

#Make Query
query = gql(
    """ 
   query MyQuery {
  events {
    id
  }
}
    """
)

#Execute

result =  client.execute(query)
print(result)

however i keep getting {'message': 'Invalid URL'}
if i would change things up and actually pass an invalid query i would get error telling me about what is wrong with my query instead of the url error
this tells me url isnt wrong but i am still getting url error, does anyone know what can i do about it? didnt find anything online that would help

[coral wasp]

Yes: that's a pretty expensive operation for every message... that's four operations per single insert. It may not matter, of course, but in a high throughput system, definitely wouldn't do that.

[coral wasp]

Also, I don't really like inserts in CTEs... just make that four separate inserts, easier to debug/read.

[waxen finch]

hmm, perhaps denormalizing might be more ideal for performance... well my bigger concern is ease of use and i dont think it'll be too annoying to work with, so ill stick with the schema (minus the PK on message.user_id)

[coral wasp]

wide is often easier than normalized. Why not one wide messages table?

Unless you're doing things per user or need to associate additional data/etc.

[waxen finch]

ill have a couple more tables afterwards for guild configuration and message reactions, and i figured getting it normalized now would save me the headache of cleaning up deleted messages/channels/guilds

[coral wasp]

normalization does add headaches too... deleting a guild and cascading the deletes/etc.

but yah, seems like you're thinking about the right things

[paper flower]

Your url is obviously a placeholder? url="graqlurlspaceholder"

Change it to an actual url

Also it's not related to databases

[glad folio]

yeah i did that to not expose the endpoint online, i dont know if i can do that

[glad folio]

where does graphql fall in?

[harsh pulsar]

is this any better than doing it one at a time inside a transaction?

[hearty siren]

class 'asyncpg.exceptions.ConnectionDoesNotExistError'>: connection was closed in the middle of operation

why did this happen suddenly

and y didn't the except Exception:
caught i t

[paper flower]

It's a query language, commonly used for APIs

It's not related to dbs

[keen minnow]

graphql fits in between the browser and the webapp. The db is behind the webapp

[white gyro]

hi, im new to this database stuff, how can i 'interact' with a database(such as Firebase) using python?

[foggy tartan]

yo

do yall know how do I put my data in paragraphs?

So imagine this is my table

I need my data like this