databases

[cedar tiger]

But your error is saying

raise StatisticsError('no mode for empty data') from None

Its saying complex_keys is None

After printing this out, are you still getting errors?

[spiral pebble]

yes

[cedar tiger]

Can you show the full log?

!e

import statistics
data = None
print(statistics.mode(data))

[delicate field]

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

001 | Traceback (most recent call last):
002 |   File "/home/main.py", line 3, in <module>
003 |     print(statistics.mode(data))
004 |           ^^^^^^^^^^^^^^^^^^^^^
005 |   File "/snekbin/python/3.12/lib/python3.12/statistics.py", line 761, in mode
006 |     pairs = Counter(iter(data)).most_common(1)
007 |                     ^^^^^^^^^^
008 | TypeError: 'NoneType' object is not iterable

[cedar tiger]

Thats a different error

I guess None is not the same?

!e

import statistics
data = []
print(statistics.mode(data))

[delicate field]

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

001 | Traceback (most recent call last):
002 |   File "/home/main.py", line 3, in <module>
003 |     print(statistics.mode(data))
004 |           ^^^^^^^^^^^^^^^^^^^^^
005 |   File "/snekbin/python/3.12/lib/python3.12/statistics.py", line 765, in mode
006 |     raise StatisticsError('no mode for empty data') from None
007 | statistics.StatisticsError: no mode for empty data

[spiral pebble]

look before calling the api i get nothing

[cedar tiger]

There we go. To get the same error, the complex_keys is an empty list

[spiral pebble]

hmm

so is it error in my fast api code

which is not retruning my mode code correct parameter

which is resulting in

no fetching of data

from the api

and hence

emty list

[cedar tiger]

Its hard to read if you don't indent correctly. What happens inside this for loop? Like what if _.real is None? Then complex_keys is an empty list, right?

[cedar tiger]

That didn't answer my question at all

[spiral pebble]

it is iterating a list of complex numbers

and comsiedring only real numbers

[cedar tiger]

I understand the for loop, but its saying that if _.real is None (i.e. False to your if statement), nothing gets appended to your complex_keys list, correct?

[spiral pebble]

is the real part is none

nothing gets stored in complex_keys

[cedar tiger]

hence an empty list

[spiral pebble]

but then i am doing mode_key_price=0 direct

not using statistic.mode

checking if len(complex_keys)>0

then only i use statistic.mode

[cedar tiger]

What is complex_keys before the for loop?

An empty list or not?

[spiral pebble]

none

empty

yes

[cedar tiger]

Something doesn't add up. Can you print out complex_keys and let it error out and share the full output here please? @spiral pebble

print it before the if len(complex_keys)>0

[spiral pebble]

ok

here right ?

[cedar tiger]

while you're at it, can you also label and print out complex_numbers too?

print(f"Complex numbers: {complex_numbers}")
print(f"Complex keys: {complex_keys}")

And yes, at that line

[spiral pebble]

new error

[cedar tiger]

Can you also print out complex_metal and if it comes from another list, print that too, with label please?

[spiral pebble]

ok doing

[spiral pebble]

looks good ? wdym by label ?

[cedar tiger]

Like add "Complex Metal: " before the variable in your print statement

[spiral pebble]

alr

now looks good

[cedar tiger]

Run it and see

[spiral pebble]

nothing got printed

[cedar tiger]

Scroll up

[cedar tiger]

Did you save the file before reloading your FastAPI app?

[spiral pebble]

yes

[cedar tiger]

what line numbers are these?

[spiral pebble]

80 81 and 82

ok i fixed it

[cedar tiger]

The error hit at line 66. Can you move the complex_metal print to before line 66?

[spiral pebble]

now new eroor

[cedar tiger]

What was the problem?

[spiral pebble]

i did the same as complex_keys to complex_metal

and it not gives same error now

[cedar tiger]

I don't understand but OK I guess

[spiral pebble]

this is module error right ?

thanks man u helped alot 👌

[cedar tiger]

What happened before this line ?sku.name_to_sku(name1)

[spiral pebble]

all the above stuff

[cedar tiger]

Are you using tf2autobot?

[spiral pebble]

i am integrating my tool with tf2autobot

making a custom pricer basically

for autobot

[cedar tiger]

I'm not familiar with that package and can't find the docs for it. I guess you'll have to contact the devs who made it and ask

[spiral pebble]

https://github.com/purplebarber/tf2-sku

[brave ingot]

thank you!!

[fallen vault]

How complicated can sql statements get? Im starting to dip my toes into more complex statemtns via joining data from multiple tables. This isnt exactly what im looking for but im being lazy at the moment and dont feel like typing out all the columns i actually need and it already seems kinda long

FROM production
left JOIN colors ON production.color = colors.cid
left join substrates on production.substrate = substrates.sid
left join users on production.tech = users.eid```

[ornate ibex]

hello :) any good web UIs for postgre? condition is: not pgadmin4, good & simple gui

[brazen charm]

Very, expecially when you start looking at recursive statements

[brazen charm]

dbeaver?

[ornate ibex]

ye found already but thanks

[main elm]

This is a pretty simple statement. What are you trying to do?

[fallen vault]

It worked. I just need to go back and add the specific columns I need from production instead of grabbing all the columns. But I’m breaking out some list to reference an id rather than a full name to save on space.

I’m fairly new to using clauses outside of the basics e.g insert, update, delete and select.

[fleet vapor]

how do i fetch all table names from sqlite3 db?

[waxen finch]

use the sqlite_schema table and filter by type, e.g. SELECT name FROM sqlite_schema WHERE type = 'table'
https://sqlite.org/schematab.html

[elfin dome]

Guys I have no idea what that is but I just got an MongoDB Developer's Toolkit: CRUD Mastery with Node.js, Java, Python, C# for free, which normally cost money, whats that, should I do it?

[slow temple]

.

Hello guys, i installed Oracle database, but this service didn't want to start how can i fix this error

[cedar tiger]

Have you tried this? https://stackoverflow.com/a/76726949/1431104

[fleet vapor]

is it good if i keep my sqlite3 connection alive if i dont use it? so i dont have to create it everytime

[pastel vale]

Use contextlib to automatically open and close your splite3 connection

[drowsy sedge]

def loginaccount():
password = password_field.get()
username = username_field.get()
cursor.execute("SELECT * FROM bms WHERE VALUES = username, password")
if password == password_id and username == username_id:
accessgranted = true

how can i change this to make the sql's syntax correct?

function of this is to find an account with matching details

[spiral breach]

def loginaccount():
    password = password_field.get()
    username = username_field.get()
    cursor.execute("SELECT * FROM bms WHERE username = %s AND password = %s", (username, password))
    result = cursor.fetchone()
    if result:
        accessgranted = True

[spiral breach]

Make sure to use a secure way to store passwords, such as hashing and salting. Storing plaintext passwords is a security risk.

[drowsy sedge]

how?

def createaccount():
password = password_field.get()
username = username_field.get()
print(username, password)
cursor.execute("INSERT INTO bms VALUES(?, ?)", (username, password))
print("Account Created")

heres my create account how would i store it like that?

[spiral breach]

using hashlib and other secret library

[drowsy sedge]

I’m using SQLite

[spiral breach]

salt = secrets.token_bytes(16)

# Hash the password with the salt
hashed_password = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), salt, 100000)

# Store the salt and hashed password in the database
cursor.execute("INSERT INTO bms (username, salt, hashed_password) VALUES (?,?,?)", (username, salt, hashed_password))
print("Account Created")

like this.

[drowsy sedge]

Does this make it check if password and username is right?

[spiral breach]

def login():
    username = username_field.get()
    password = password_field.get()
    print(username, password)

    cursor.execute("SELECT salt, hashed_password FROM bms WHERE username =?", (username,))
    stored_salt, stored_hashed_password = cursor.fetchone()

    input_hashed_password = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), stored_salt, 100000)

    if input_hashed_password == stored_hashed_password:
        print("Login Successful!")
    else:
        print("Invalid username or password")

[spiral breach]

why did you not input " ` "

[drowsy sedge]

?

what does salt mean and do i need to import a module?

[spiral breach]

OMG
import hashlib

[spiral breach]

this is basics of password security.

[drowsy sedge]

Okay thanks

from where?

Or just import

[spiral breach]

wdym

[drowsy sedge]

Nvm

[fallen vault]

Everyone doesn’t have the same starting point. At one point or another, everything is a new concept to someone.

[spiral breach]

Yes, sir

[fiery escarp]

Stare

Can u dm me

I need help

[steady shard]

Check out this developer guide from the Azure Cosmos DB team that focuses on Azure Open AI and Python - https://github.com/AzureCosmosDB/Azure-OpenAI-Python-Developer-Guide/tree/main/00_Introduction

if you need any other details on cosmos/py feel free to get in touch

[spiral breach]

If you need my help, dm.

[gray idol]

def connect(config):
    """ Connect to the PostgreSQL database server """
    try:
        # connecting to the PostgreSQL server
        with psycopg2.connect(**config) as conn:
            print('Connected to the PostgreSQL server.')
            return conn
    except (psycopg2.DatabaseError, Exception) as error:
        print(error)

def create_tables(connection):
    """ Create tables in the PostgreSQL database"""
    commands = (
        """
        CREATE TABLE vendors (
            vendor_id SERIAL PRIMARY KEY,
            vendor_name VARCHAR(255) NOT NULL
        )
        """,
        """ CREATE TABLE parts (
                part_id SERIAL PRIMARY KEY,
                part_name VARCHAR(255) NOT NULL
                )
        """,
        """
        CREATE TABLE part_drawings (
                part_id INTEGER PRIMARY KEY,
                file_extension VARCHAR(5) NOT NULL,
                drawing_data BYTEA NOT NULL,
                FOREIGN KEY (part_id)
                REFERENCES parts (part_id)
                ON UPDATE CASCADE ON DELETE CASCADE
        )
        """,
        """
        CREATE TABLE vendor_parts (
                vendor_id INTEGER NOT NULL,
                part_id INTEGER NOT NULL,
                PRIMARY KEY (vendor_id , part_id),
                FOREIGN KEY (vendor_id)
                    REFERENCES vendors (vendor_id)
                    ON UPDATE CASCADE ON DELETE CASCADE,
                FOREIGN KEY (part_id)
                    REFERENCES parts (part_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
        )
        """)
    try:
        with connection.cursor() as cur:
            # execute the CREATE TABLE statement
            for command in commands:
                cur.execute(command)
    except (psycopg2.DatabaseError, Exception) as error:
        print(error)

if __name__ == '__main__':
  conn = connect(config)
  create_tables(conn)

Is the issue that I'm looping through multiple statements?

I don't have autocommit on.

With this code, I'm able to return the connection object. But according to the docs commit should automatically be called within a with block.

I'm going to add connection.commit() after the for loop and see what happens.

[gray idol]

Adding that line made it work.

[robust grotto]

Hi, I try to run mysqlclient v2.2.x in my pipenv project , currently it is installed without issues, but python shell failed to load the package MySQLdb with external shared library linking issue , allow me to link the detail here and ask in this discord server , thanks .

[keen ravine]

anyone used vector db please help me

I have problem setting up

[coral wasp]

Which vector db?

[fleet vapor]

do i need to create my own context manager?

[pastel vale]

Nah. Just use the inbuilt contextlib module. You can google how to use contextlib for automstically opening and closing database connections

[old cliff]

I need to implement a login/sign up system for my application, and am storing user details in a MySQL database.
What is the best hashing system to use to store passwords for each user in a MySQL table?

Please ping me when replying

[wise goblet]

bcrypt is good default for password purposes at least. Super secure https://pypi.org/project/bcrypt/
And uses random salts per each password, so it is protected against rainbow attacks (against situation where people have databases of common md5 hashes for example). Password hashes will be very useless even if leaked.

[gray idol]

How are you storing data to insert into postgres?

Do you use a dataclass?

If so, how are you unpacking the values in the dataclass to create the insert statement?

I got something to work but I'm converting the dataclass to a dictionary and filtering out fields that are empty.

[still trail]

with alembic, are the model classes supposed to be updated with each revision, or do they stay unmodified at the inital schema?

[lean olive]

if you mean the SQLAlchemy models, the revisions are mostly created, based on the changes in the models, not the other way around

[still trail]

huh? i thought i'm supposed to write downgrade() and upgrade() in my revision files myself

[lean olive]

well, you can, but that's tedious and unnecessary
alembic --autogenerate generates revisions from the model changes

[still trail]

huh

are there situations where it can't figure out the migration path?

[lean olive]

hmmm, if the changes are not in the models, in cases such as when the SQLAlchemy ORM doesn't support the underlying DB features

[still trail]

what if the migration requires transformation of data?

[lean olive]

those changes aren't in the models

[willow orchid]

can anyone tell me why sqlalchemy doesnt return any record even if he database hase few recordes that satisfy the condition query = query.filter(and_(
self.model.cost >=cost_threshold,
self.model.working_days >=days_threshold
)) thnx

[lean olive]

you have to call a method like .first, .one, etc. on the result of .filter to actually get the rows

[willow orchid]

but it works for search_filter = or_(
self.model.cost.ilike(f'%{search_term}%'),
self.model.working_days.ilike(f'%{search_term}%')
)
query = query.filter(search_filter)

[lean olive]

wdym by "works" here

share the code that uses the query variable here

[delicate field]

Pasting large amounts of code

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

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

[lean olive]

please delete your message and share a pastebin link instead

[willow orchid]

here https://paste.pythondiscord.com/XDHBL4K74YZV3FKWETGWXVRK6U

[lean olive]

okay, that's returning an object of type Query

[lean olive]

I was asking about this

[fleet vapor]

my aiosqlite database connection is always closed, why?

[willow orchid]

i mean when i use only cost or 'working_days' i will see records but if both are used i got always no rsults even if the inserted values are 100% in the table

[lean olive]

what are the types of these fields?

[willow orchid]

here is a screen shot of the result even if i am 100% sure it exists for 2500 10

[lean olive]

you've used them pretty differently in the paste as well as your messages above

[willow orchid]

look at line 36 in the paste this for more than one input but in line 60 for on input and both of them return query (no first(), all() etc). when i add (all() , first() ) i get an error. and by the way when i use one input i got the results corrctly

my answer is why the comparison not working in my code but ilike is working and i got results even if i dont add all(), first() ..etc

[lean olive]

you might be iterating over the Query object somewhere else
but anyway, the comparison where you were doing >= is not working probably because you're trying to do that comparison with a text column

[willow orchid]

it is integer that is why it makes me crazy when i use ilike for string it works but when i use comparison for integer it does not is there any reason for that

[lean olive]

self.model.cost is a string
not sure what you're expecting to happen when comparing that to an int

[willow orchid]

working_days= db.Column(db.Integer, nullable=False)
cost= db.Column(db.Integer, nullable=False) even if my model has integer values?

[lean olive]

huh

[willow orchid]

as u see here they are stored in the db as integer for sure

[lean olive]

is cost_threshold and days_threshold here strings or ints?

[willow orchid]

in the paste lines 38 41 int (in the paste it is float by on my pc it is int)

[lean olive]

hmm, I see

[willow orchid]

the only reason i found that it is working for ilike is in sqlalchemy string compare webpage ilike makes both sides of the comparison lower then it search in the table but it should be working here for < = as they are both int

[lean olive]

<= or >=?

[willow orchid]

numbers comparison

less and greater than

[lean olive]

does an input of 0 0 not work with this filter?

[willow orchid]

it will not work because i am using less or equal to it cant be less than 0 but i tried it any way and 100000 100000 both did not work

[lean olive]

you're using >= in that message

[willow orchid]

in the paste 46 47 <= , the message was after a zillion tries i was trying anything

[lean olive]

oh, okay

did you try with just one of the fields?

[willow orchid]

if i try one field the else block will be activated and in that block i use ilike as mentioned before ilkie is working

[lean olive]

I mean, in the query, not in the input

[willow orchid]

what do u mean by the query exactly here?

[lean olive]

the query.filter line

[willow orchid]

still no results

[willow orchid]

when i use query=query.filter(or_(
self.model.cost <= int(search_parts[0])))
print(query) it will print 'SELECT offer.id AS offer_id, offer.working_days AS offer_working_days, offer.cost AS offer_cost, offer.use_id AS offer_use_id, offer.damage_id AS offer_damage_id
FROM offer
WHERE offer.cost <= ?' and the results are not correct

[lean olive]

you mean that SQL is not correct?

[willow orchid]

i mean it is correct, but how comparing self.moel.cost with int has the same result as if i compare str with str not int with int

[craggy canyon]

I'm trying to fetch some data that needs to go through a couple different tables. I have User, Provider, UserProvider, UserProviderNotes, and Note.

Right now, User has a relationship with Provider via UserProvider so I'm able to call user_record.providers to get all of the providers. But now I have a second layer to this, where a Note can be added to a user_provider via user_provider_notes. What I would really like to do is call user_record.provider_notes in to retrieve all of the notes that are associated with that user's user_providers records, but I'm really struggling how to make that query by utilizing the defined relationships.

Here's a slimmed down version of my models.

class User(BaseModel):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
        
    providers = relationship("Provider", secondary="user_provider", back_populates="users", lazy=True)
    provider_notes = relationship("UserProvider", back_populates="user", lazy=True) #?????????????

class Provider(BaseModel):
    __tablename__ = "provider"

    id = Column(Integer, primary_key=True, autoincrement=True)

    users = relationship("User", secondary="user_provider", back_populates="providers", lazy=True)


class UserProvider(BaseModel, HasNotes):
    __tablename__ = "user_provider"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id"))
    provider_id = Column(Integer, ForeignKey("provider.id"))
    
    user_provider_notes = relationship("Note", secondary="user_provider_notes", lazy=True)

class Note(BaseModel):
    __tablename__ = 'note'
    id = Column(Integer, primary_key=True)

class HasNotes(object):
    @declared_attr
    def notes(cls):
        note_association = Table(
            "%s_notes" % cls.__tablename__,
            cls.metadata,
            Column("note_id", ForeignKey("note.id"), primary_key=True),
            Column(
                "%s_id" % cls.__tablename__,
                ForeignKey("%s.id" % cls.__tablename__),
                primary_key=True,
            ),
        )
        return relationship(Note, secondary=note_association)

[craggy canyon]

ultimately, the raw query would look like this. But I'd like to consolidate it by making use of the relationships or proxy associations as much as possible.

SELECT note.* from user
INNER JOIN user_provider ON user_provider.user_id = user.id
INNER JOIN user_provider_notes ON user_provider.id = user_provider_notes.user_provider_id
INNER JOIN note ON user_provider_notes.note_id = note.id

[pseudo tusk]

I'm having a problem with the pyinstaller library, using oracledb, I've already tried all possible alternatives for resolution, cryptography and oracledb are installed, but when I try to run the code after generating the .exe with pyinstaller, it breaks due to cryptography , has anyone had a similar error? thks

[coral wasp]

Probably best to open a help thread and paste the error: #❓｜how-to-get-help

[pseudo tusk]

srry

[fleet vapor]

I've encountered a new issue from aiosqlite where i can't exit my program using ctrl+c which raises KeyboardInterupt when a connection is not closed, this makes the terminal completely stuck unless i restart or make a new one

[open rapids]

This is my MRE it works and it uses real files. I would like to have a DB created for every test separately and in memory. Do you know how to do it?

from contextlib import contextmanager
from sqlite3 import Connection, connect
from pathlib import Path

import pytest

CREATE_HAPPY_LOG_TABLE = """
CREATE TABLE IF NOT EXISTS _happy_log (
    id_happy_log integer primary key autoincrement
);
"""

CREATE_HAPPY_STATUS_TABLE = """
CREATE TABLE IF NOT EXISTS _happy_status (
    id_migrations_status integer primary key autoincrement
);
"""

def _create_happy_status_table(conn: Connection) -> None:
    conn.execute(CREATE_HAPPY_STATUS_TABLE)

def _create_happy_log_table(conn: Connection) -> None:
    conn.execute(CREATE_HAPPY_LOG_TABLE)

class SQLiteBackend:
    def __init__(self, db_path: Path | str, mig_dir: Path | str) -> None:
        self._db_path = db_path
        self._mig_dir = mig_dir

    @contextmanager
    def _connect(self) -> Connection:
        conn = connect(self._db_path)
        try:
            yield conn
        finally:
            conn.close()

    def happy_init(self) -> None:
        with self._connect() as conn:
            _create_happy_status_table(conn)
            _create_happy_log_table(conn)


@pytest.fixture()
def db():
    mig_dir = Path().resolve().parent / "migrations"
    db_path = Path().resolve() / "test.db"
    db = SQLiteBackend(db_path=db_path, mig_dir=mig_dir)
    yield db
    db_path.unlink()


def test_happy_init(db):
    db.happy_init()
    with db._connect() as c:
        res = c.execute("""
            SELECT name
            FROM sqlite_master
            WHERE type='table' AND name='_happy_log' OR name='_happy_status';
        """).fetchall()
    assert res == [('_happy_status',), ('_happy_log',)]

[storm mauve]

for context https://canary.discord.com/channels/267624335836053506/1301590198014185507
and to clarify:

Each time you call db._connect(), it is effectively creating a separate in-memory database.
You must reuse the same Connection object to persist changes to the database

[fleet vapor]

should i use asyncio.Lock when using aiosqlite anyone?

[coral wasp]

Can you explain more about what you're trying to Lock? The answer depends very much on that.

[fleet vapor]

on execute to prevent race-conditions?

[coral wasp]

Are you familiar with database transactions?

That's how we avoid data races with databases.

[fleet vapor]

im still new

[coral wasp]

The simple answer is: databases are built to handle "locking" (using Transactions) so that you don't need to lock for database updates in your code.

One reason this is important is: while you might be able to solve a race condition within a single process... what if you had many processes / apps using the same database? It's important for the database to prevent inconsistent updates.

[fleet vapor]

what about on connect? im trying to implement a connection pool so

[coral wasp]

Transactions are "below" connections.

[coral wasp]

But... that's why I started with: "Can you explain more about what you're trying to Lock"

So, perhaps start at the beginning and explain what you're trying to do.

[fleet vapor]

what does that mean? i dont get it

[coral wasp]

https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions

It's a little much, but:

The idea is that transactions are created within connections

[fleet vapor]

so a lock isnt needed right?

[coral wasp]

Depends what you're using a lock for.

But, if you're just worrieda bout consistent updates across multiple connections, Transactions are what you want.

[fleet vapor]

a connection pool

[coral wasp]

You might use a lock or similar mechanism to serve connections to users.

Since a connection should only be used by one thread or task at a time.

But, I think you'd use an asyncio.Queue

[fleet vapor]

i used queue.Queue

what is the difference tho?

[coral wasp]

asyncio.queue (and lock) are for async work.

[fleet vapor]

do i still need to use a lock when using asyncio.Queue?

[coral wasp]

It can get confusing, but I just remember it as: threads and asyncio don't like each other.

[coral wasp]

I don't know your code. But, if you are putting connections into a pool and taking them out, a Queue makes sense to me.

[fleet vapor]

yeah im doing that exact thing i just wonder if i should use a lock

[coral wasp]

If you want a better answer, I'd suggest opening a help thread and sharing your code.

It's dangerous for me to give advice around locking without actually knowing.

[fervent charm]

What should I use for data versioning my duckdb file

Just so if I accidentally ‘drop table’ its not a disaster

[wise goblet]

you could periodically backup it to S3 bucket for example.

or just zip archive and copy to some another server

the trick is in storing backup not at the same server

[fervent charm]

Is there a local option

[wise goblet]

tar/zip archive to local 😉

just some regular cron job for that

[fervent charm]

True

[pastel vale]

i use context managers to open and close my databases automatically. is this safe to do or should I manually open and close my database connections?

[wise goblet]

usually database connections are meant to be opened for app once and then reused

reopening them many times can involve dangers of bad performance (because connection is not reused)
and also can lead to super memory leaks

[pastel vale]

so I should stop using context managers or am i alright to keep using them?

[wise goblet]

what kind of web framework do you use?

and sql lib

Django already opens for you database connection in advance and dictates how all should work
https://fastapi.tiangolo.com/tutorial/sql-databases/#create-a-hero i checked fastapi database tutorial, it hints for correct way to reopen connections within their code as well.

[pastel vale]

just using sqlite. not even doing anything web releated. just all databases

[wise goblet]

i would only question session: SessionDep stuff they show there you
I think it should be equvalient to use session somehow more directly correctly perhaps

if i guess it right, the most correct action would be creating Engine once

creating Connection once

and then creating sessions on need, using them in context manager to create and close automatically

Engines/Connections in any lib should be theoretically fine to be always opened only once

[wise goblet]

sqlite3 as in std library for sqlite3?

[waxen finch]

SQLite's the exception, connections are much cheaper that case

[pastel vale]

    sqlite3.connect(
        filename, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
    )
) as ufc_conn:
    with closing(ufc_conn.cursor()) as ufc_cur:

thats how i do it

[pastel vale]

yes

[wise goblet]

well, as person above said, sqlite3 is exception and fine to use contextmanager even with connection 😉

i guess its local file nature makes a difference enough for that

there probably could be a different danger though

if u will at any time make that u opened multiple times connections, i think it can corrupt sqlite3 db i think
Best to turn on WAL, thing, it makes it more stable and friendly in case of multithreading/multiprocessing usage i think. not 100% sure

alternatively may be opening connection only once could protect against it as long as u have shared memory between threads, a single process accessing file
A thing to check ^_^ but just beware about such possible stuff in case if will have unexpected sqlite3 db locking itself away

[pastel vale]

im not using databases with anything web related. tbf Ive had no problems my databases. all fully functioning

[waxen finch]

#databases message this previous benchmark i did had much worse performance when distributing queries across multiple SQLite connections

[still trail]

should i have an alembic revision for the initial schema?

and are autogenerated migrations for migrating from the metadata to the live database, or from the live database to the metadata?

[neon ferry]

What's the sqlite equivalent for nosql ?

one that doesn't require starting a separate server

[storm mauve]

either just a bunch of json files or something like shelve

[neon ferry]

json files feels messy with embeddings

[storm mauve]

embeddings?
use a vector database then, I recommend LanceDB

NoSQL covers an extremely wide range of databases from document storage to graphs, there isn't one single database you can use for everything that falls under nosql - I assumed you were just looking for a mongodb alternative but should've been clearer

[neon ferry]

Thanks . I did run a research session for vector dbs but there were too many options so i decided to go for something old and well established like nosql . I thought of storing embeddings as blobs would work

[storm mauve]

'nosql' is an umbrella term for everything that doesn't falls under "sql", personally I wouldn't call it well established

if you just wanted to store as blobs, you could even store it inside of sqlite, but the main point of vector databases is querying the embeddings in an efficient way, which requires creating an Index for the vector column

[fleet vapor]

is there any way i can make aiosqlite to use daemon thread for connection so i can exit my program without closing them?

[formal lintel]

aiosqlite should not be in another thread, daemon or not

[fleet vapor]

?? what does that mean

[formal lintel]

it sounds like you want to use aiosqlite from a different thread?
even if it's to close the connection, it should not be from any other thread

[fleet vapor]

no i only want it to make the thread daemon

[formal lintel]

then what does aiosqlite have to do here?

[fleet vapor]

aiosqlite uses non-daemon threads?

im not trying to use aiosqlite from a different thread the point is it uses non-daemon threads when creating connections which prevents me from exiting the program without closing them

[formal lintel]

that's good, otherwise the database could become corrupted

[fleet vapor]

all other libraries like the default sqlite3 or asqlite use daemon thread by default only this one is different

only*

[neon ferry]

I know vectordb are for quick indexing and searching . I will use it later . What i am working with is a dataset of tags in 10s of thousands . I turned them into embeddings and storing it into sqlite . Embeddings are stored as blobs . What i am doing now is cleaning the tags by clustering . First cluster step lets me separate tags like apple from nonsense like hfebk or bad camel case like redapple and assign cluster number to the db . Than i pick the cluster that has normal words and run clustering again to separate apple from plastic . After that i will index them in a vector db but i feel like a document based or nosql will be better suited for the first steps .

[drowsy sedge]

def loginaccount():
username = username_field.get()
password = password_field.get()
stored_password = cursor.execute("SELECT passwordid FROM bms WHERE usernameid =?", (username,))
print(username, password)
if password == stored_password:
print("Login Successful!")
else:
print("Invalid username or password")

how could i change this so it can find the password for the username in the database? i believe my sql statement is wrong

[storm mauve]

look up password hashing, you should never store passwords in plain text even for a learning project
the recommended best practices change a little bit over time (in particular, what is the best algorithm) - you could use the standard library's hashlib, but the most recommended algorithm is currently https://pypi.org/project/argon2-cffi/

to understand it see

• https://auth0.com/blog/hashing-passwords-one-way-road-to-security/
• https://auth0.com/blog/adding-salt-to-hashing-a-better-way-to-store-passwords/

[drowsy sedge]

My teachers weird

[neon ferry]

HI . blob vs file system / path for which is better storing embeddings in sqlite ? i am working with 1k and 50k record datasets.

[brazen charm]

Do you need it to be retrievable like a db?

If not, use safetensors

[neon ferry]

I need to retrieve it a few times for pre computing . I am clustering items hierarchically and manually. To later upload to a vector search db.

after that i don't need to

this is what i am doing #databases message

[silent flicker]

hello, i need help, been pulling my hair since 2 hours on this error
here's my code:

if chc==1:
        name=input(color.YELLOW + "Enter your name: " + color.END)
        date=input(color.YELLOW + "Enter the booking date: " + color.END)
        datend=input(color.YELLOW + "Enter the checkout date: " + color.END)
        email=input(color.YELLOW + "Enter customer email: " + color.END)
        phno=int(input(color.YELLOW + "Enter customer phone number: " + color.END))
        roomtype=input(color. YELLOW + "Enter room type: " + color.END)
        print()
        
        cur.execute(f"SELECT room, type FROM customer_table WHERE type='{roomtype.capitalize()}' AND vacant=false")
        for i in cur:
            print(i)
            time.sleep(0.2)

        print()
        room=int(input(color.YELLOW + "Enter the room: " + color.END))

        cur.execute(f"UPDATE TABLE customer_table SET name='{name}', checkin='{date}', checkout='{datend}', email='{email}', phone={phno}, vacant=true WHERE room={room}")
        print("Sucessfully added record")```

the error im getting is

  File "C:\Users\hp\Codes\mysql\hotel_management_system.py", line 76, in <module>
    cur.execute(f"UPDATE TABLE customer_table SET name='{name}', checkin='{date}', checkout='{datend}', email='{email}', phone={phno}, vacant=true WHERE room={room}")
    ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\hp\AppData\Roaming\Python\Python313\site-packages\mysql\connector\cursor.py", line 537, in execute
    self._handle_result(self._connection.cmd_query(stmt))
                        ~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "C:\Users\hp\AppData\Roaming\Python\Python313\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "C:\Users\hp\AppData\Roaming\Python\Python313\site-packages\mysql\connector\connection.py", line 872, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\hp\AppData\Roaming\Python\Python313\site-packages\mysql\connector\connection.py", line 648, in _handle_result
    raise get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE customer_table SET name='Friedrich Nietzsche', checkin='2024-10-10', check' at line 1```

[coral wasp]

Don't write queries like this. It's so bad, we even have the following for you:

!sql

[delicate field]

SQL & f-strings

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

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

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

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

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

See Also

• Python sqlite3 docs - How to use placeholders to bind values in SQL queries
• PEP-249 - A specification of how database libraries in Python should work

[coral wasp]

I assume your issue is because of quoting, but it's not worth fixing the f-string. Fix it by following the instructions above.

[silent flicker]

😭 im sorry lmao, was gonna change it but im just too lazy, let me do that real quick

oh no i think i dont quite get that, but let me try it anyways

query2="UPDATE TABLE customer_table SET name=%s, checkin=%s, checkout=%s, email=%s, phone=%s, vacant=true WHERE room=%s"
params2=name, date, datend, email, phno, room
cur.execute(query2, params2)```

im sorry im not sure if i did that right

i should probably sleep and try that again in the morning, its 3:20 am rn

its still giving the same error btw

[coral wasp]

Update doesn't take a keyword 'table'

Just update xyz set a=b

[tough marlin]

Hii

Does anyone know about the conceptual model of the database? I did it and I have some doubts about whether it is right or not.

[cedar tiger]

Hard to answer if we can't see what you have so far

[tough spindle]

Whats the best practice of hosting or using a database in a production server ?

I have never really used sql in a server , I have a vultr acc should I just use it to manage the database or should I make a whole docker file and download it manually there ?

Please ping me , thanks

[frank flax]

i'm getting this error when trying to post a comment on my site that uses unicode characters, any ideas? https://github.com/rr-/szurubooru/issues/688

i ask here in #databases because the traceback seems to indicate it's sqlalchemy related

[frank flax]

looks like the issue is psycopg2:

>>> conn = psycopg2.connect('postgresql:///')
>>> conn.encoding
'LATIN1'

i can use connection.set_client_encoding but i'm not sure how to do that from within sqlalchemy

hm even if i use that, i still get this:

psycopg2.errors.UntranslatableCharacter: character with byte sequence 0xef 0xbd 0xa5 in encoding "UTF8" has no equivalent in encoding "LATIN1"

[frank flax]

this person had the same error but their solution doesn't apply to me https://stackoverflow.com/questions/62073355/psycopg2-encoding-utf8-has-no-equivalent-in-encoding-latin1-even-when-set

[silent flicker]

oh welp, rookie mistake...

thanks though, fixed that error

[coral wasp]

Rookie mistake was not googling the syntax 🙂

[silent flicker]

my bad lmao, btw how much is the max int size

[coral wasp]

(I'm always looking up the exact syntax for a particular DB and query... too many variations)

[coral wasp]

For what db?

[silent flicker]

uh, i want to add a 10 digit phone number column but i cant insert it, i think i might need to use bigint?

because it gives
Out of range value for column 'phone' at row 1

[coral wasp]

What database are you using?

[silent flicker]

i am new to mysql, are there database types too? i quite literally just opened myql and created a database

didnt specify anything other than the name

[coral wasp]

For mysql: https://dev.mysql.com/doc/refman/8.4/en/integer-types.html

But maybe you want a decimal: https://dev.mysql.com/doc/refman/8.4/en/precision-math-decimal-characteristics.html

Or, just store the phone number as a varchar

[silent flicker]

oh alright, i think i need to use bigint

[silent flicker]

hmm i can do that but its a school project and i need to show variations so

again, thanks for your help

[fossil wagon]

accidently pushed creds to my db, its stuck in my history on github on some random detached head, have no idea how to remove it

please help before google smites me down

[coral wasp]

Invalidate the creds. First.

Then open a help thread and ask for help cleaning it up: #❓｜how-to-get-help

[keen minnow]

Has anyone tried https://www.singer.io/? Any feedback on it?

[random viper]

hello, if the database already populated
do i have to create migrations for it ?
lets say the database already have 10 tables and millions of rows, and i have to continue using the database and the database don't have the migrations table,
or should i just continue without using migrations ?

[tough marlin]

Thanks for trying to help...I managed to solve the problem

❤️

[last dock]

there is no general in this server?

[storm mauve]

we generally consider #python-discussion to be our 'general', for discussion about the main topic of this server (python programming)

for other topics use channels in the off-topic / general category ( e.g .#ot1-this-regex-is-impossible - their names change every day for fun)

[wise goblet]

-# on practice good portion of people use career-discussion as general 😄

[glacial ether]

what does -> mean
for example data ->

[wise goblet]

very out of context. provide more context

[dark warren]

im hosting my website frontend works fine but backend, run by node.js is unsupported and i need to pay like a significant amount more
can i use a VPS and connect it to my site as the backend?
i feel like theyre upcharging for no reason

[storm mauve]

what are you using to host the frontend?

if it isn't doing anything particularly expensive nor have a lot of visitors, you can probably host both the frontend and backend in a single 5$/month VPS

that is really not on topic for this channel though, at most #web-development, but seeing it's node rather than python... may as well just go straight to offtopic like #ot1-this-regex-is-impossible

[burnt maple]

How could I make my own data for a neral network

[storm mauve]

that's more of #data-science-and-ml than #databases...

it depends.
for some things you may want to use public datasets you can find on Kaggle or HuggingFace
for some things you can scrape information available online
for some things you can create a program that creates it for you
for some things you might need to manually create/label it yourself or pay someone to create/label it for you

[brave bluff]

SQLA/Alembic question. I'm getting an error that a table genres is already defined in the metadata, but I only have one table to which I've given the genres table name. Any thoughts on how I can figure out what's using that name space?

[paper flower]

Do you have any of your tables automatically generated?

E.g. via custom mixins

[brave bluff]

I've done nothing manually so far. I'm trying to get alembic to generate everything.

[paper flower]

I'm talking about generating models automatically, not migrations

[brave bluff]

Not sure what you mean there.

[brave bluff]

As I'm not sure how I'd do that, I'm gonna say no?

[paper flower]

Can you copy the error here?

[brave bluff]

sqlalchemy.exc.InvalidRequestError: Table 'genres' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

The trace shows the error coming up on

class Genre(Base, SoftDeleteMixin, TimestampMixin):
    __tablename__ = "genres"

But it's the only table I've named genres

The only thing I can think of is in one of my models, I have

if TYPE_CHECKING:
    from models import Genre, Publisher

So it could import twice if something funky is going on, but best as I understand Python, it shouldn't?

[paper flower]

Unless you're somehow messing with modules they should be cached.
Try removing your mixins? Also generally just search for all genres strings in your project

[brave bluff]

I'll try removing the mixins for now, but otherwise the only instances of genres is another model with the table name __tablename__ = "system_genres", a foreign key reference to Genre, and a key in a JSON schema.

I have other tables using those mixins, so I don't feel it should be them

Yah, same error with the mixins removed

[paper flower]

Honestly It's hard to tell what else may be wrong without looking at all the code

Is it opensource by any chance?

[brave bluff]

I can open the repo, sure

I'll push it up, one sec

https://github.com/rohitsodhia/GamersPlane/tree/dev/sqlalchemy

All the python stuff is under api

Please no criticisms on how bad it all is, I know I'm lacking in skill

[paper flower]

I'd add __pycache__ to gitignore

Where can I find the models?

[brave bluff]

api/src/models

And yah, I just noticed the pycache that got created

It's a temp commit

[paper flower]

Perphas the issue could be that you use src in some of your imports

but I'm not sure

Some of them don't use it and some of them do, e.g. from models.base import Base, SoftDeleteMixin, TimestampMixin does not

It's better to remove src from all of them

[brave bluff]

Didn't notice I was doing that. I think it's because of vscode. I'll try removing them

[paper flower]

I suspect that may be the cause

[brave bluff]

Wait, I only see src in one import, and that's the alembic one, which needs it as far as I can tell?

[paper flower]

You shouldn't really need it 🤔

You can try to add src to PYTHONPATH so it searches for modules there

[brave bluff]

Ok, I can try changing that. But even in that case, it's only one, and its in the alembic file. Why would genre be th eonly one to double up?

[paper flower]

I reproduced you issue, just gonna try to see what's wrong

Removing src fixed it 🫡

from src.models.base import Base -> from models.base import Base

[brave bluff]

Eeer, did not fix it for me... now I'm getting a different error that is really confusing. Did you add src to the pythonpath?

[paper flower]

I did add it

What error are you getting?

[brave bluff]

Oh, I'm being stupid. It's sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "{DATABASE_HOST}" to address: Name or service not known, but the value isn't coming from the os, it's coming from a .env file

So I need to run alembic from within the container, not locally

[paper flower]

engine seems to be misconfigured, that's all really

[brave bluff]

Oh... I didn't turn it into an fstring

wtf am i doing

How dumb am I

[paper flower]

Btw I'd recommend pydantic-settings for your env variables

[brave bluff]

I'll look at that. It shouldn't matter as in the container it should work

Ok, progress...

FileNotFoundError: [Errno 2] No such file or directory: '/usr/src/app/alembic/versions/0cfe1d4466a9_test.py'

Shouldn't it be creating that file?

[paper flower]

There seem to be no versions dir in your alembic dir for some reason

[brave bluff]

Yah, just noticed. Created it, and it worked

I'm honestly confused on why the genre error cam eup

But it's fixed

I really don't like errors being fixed without knowing why :/

Thanks for helping direct me

I actually changed prepend_sys_path = ./src in the alembic.ini, which did the job

[paper flower]

There's a setting like this in pytest too btw

in case you're using it

[brave bluff]

I will be

I started this project by shoe horning the django orm into fastapi

I'm instead now doing the right thing by learning sqla (though I have to say, it's documentation is terrible for learning) and implementing it.

If you have thoughts on why genres doubled because of a path issue, I'd love to know, otherwise I'll move on

[paper flower]

No idea on specifically why

[brave bluff]

That's frustrating. Thanks for helping me identify the issue

[wooden topaz]

i read this at some article, please comment on these 2 points
(i dont understand why so, but anyways please correct if something is wrong)

Why not use language like python instead of SQL?
Python for example, works on the data present inside the RAM, not DISK.
Where as the query written in SQL can directly executed on DISK.

SQL -- data stored on disk
No SQL -- data is stored in RAM

[wise goblet]

No SQL can be stored in Ram and on Disk
No SQL is very general term summarizing a dozen of completely different types of databases

[wise goblet]

SQL is in general on Disk, but it can be having heavy caching buffer of gigabytes size, making an illusion of speed similar to RAM stored database 🙂

[wise goblet]

Why not use language like python instead of SQL?
Python for example, works on the data present inside the RAM, not DISK.
Where as the query written in SQL can directly executed on DISK.

In reality people use both, python and SQL together.
If that have learned such CS basics like data structures and algorithms, and a bit aware enough about memory consumption of different types of data and complexity of operations
And they also read a short list of latency differences between different operations
And they had some good enough SQL training

They will realize to do majority of stuff to be handled by SQL quering engine, because it has plan optimizations and made to be quering fast even most complex data.

They could be doing some part in python itself, that have for making proper backend, but they will be doing it carefully in order to avoid easy RAM overflows, and overusing network traffic with cpu. Their custom code should be written in a way to use benefits of SQL engine to full capacity, otherwise they will face performance consequences.
We still use python and SQL together nevertheless, because SQL is not general purpose language, it can't do everything, it can do only good database operations, python can do everything (just unable to do good database related operations well)(technically it can do more with things like panda, numpy... But it is entirely different story not for regular mortals and regular web devs that just wish to maintain pleasant enough code in a long run)

[heady bronze]

for databases (sqlite3 in particular) is there a way to denote a value as a foreign key?

[zealous spire]

yes, though they are uneforced by default

CONSTRAINT FKName FOREIGN KEY (RowName) REFERENCES ForeignTable(ForeignRow)
``` and
```sql
PRAMGA foreign_keys = ON;
```, probably at the top of your file, to enforce them

as can be seen in those docs, you don't need CONSTRAINT FKName, but it lets you name them

[flint thistle]

folks, best type of value to save currency values? was thinking a big integer elevated to 4 (this will only work in one country) and then treat it with decimal on code. Im using django with postgres

[paper flower]

Maybe you could just use a numeric type instead of dealing with int -> Decimal conversions on app side

[cedar tiger]

We store them as BigInt and then do decimal conversions on app side. Configurable through app settings on how to display on different currencies but thats only because we have to make this configurable for different clients globally

[crisp ravine]

Milvus, Weviate, and Qdrant are 3 open source vector stores. I would like to know which one is the best.

[cedar tiger]

pgvector, IMO

[glacial ether]

graphql Vs Realm which one you choosing and why?

[coral wasp]

Apples and oranges

[glacial ether]

so both?

[coral wasp]

For what?

[glacial ether]

to use both?

for an app

[coral wasp]

What's the question

[glacial ether]

mobile app

[coral wasp]

What does the app do? What do you need a db for?

My usually answer is 'no' if the question is 'graphql?'

[glacial ether]

to store users information

you dont like graphql?

[glacial ether]

?

[coral wasp]

Context matters. You're not giving much.

Where will the db live?

[glacial ether]

no worries

[coral wasp]

Is this running as a central db? Or an on device db?

[glacial ether]

i think remotely

[coral wasp]

Remote as in running on a server somewhere?

If so, Realm doesn't sound like what you're looking for. I haven't used it, but it appears to be for mobile devices, oh I guess it'll synchronize to a central db. Just sounds like overkill for many use cases

Me, I'd start with Postgres behind a rest api and keep it simple

[flint thistle]

I use pgvector, best so far

[flint thistle]

I was thinking doing that for better scalability, but maybe it is too much of a premature optimization but since there are transactions im sticking with this

[glacial ether]

nice billy your pretty smart

thanks

billy i was trying to downlaod gradle but i get this whenever verify the gradle install

[coral wasp]

I don't know gradle, the you could open a help thread and ask #❓｜how-to-get-help

[rare jewel]

hey guys im new to programming..
I recently just downloaded pyrebase with pip install pyrebase
however, when i use it, it gives
"pyrebase" is not accessedPylance
Unable to import 'pyrebase'PylintE0401:import-error
does anyone understand this error?

[wise goblet]

#type-hinting subject technically

you have turned on strict typing mod

for higher code quality writing

there are three solutions to this problem

1. Disabling pyright/pylance (somewhere in python extension or in pylance should be)
2. installing pyrebase types if they are available somewhere (may be such lib exists), pyrebase stubs smth like that could be named
3. i use my favourite hackish method, python3 turn_types.py name_of_libs1 anotherlib2

import importlib.util
import pathlib
import sys


lib_names = sys.argv[1:]

for lib_name in lib_names:
    try:
        lib = importlib.util.find_spec(lib_name)  # type: ignore[attr-defined]
        for loc in lib.submodule_search_locations:  # type: ignore[union-attr]
            typed_file = pathlib.Path(loc) / "py.typed"
            print(f"{loc=}, {typed_file.exists()=}, touching file")
            typed_file.touch()
    except Exception as err:
        print(f"{err=}")
        continue

which does it just by adding py.typed file to the lib 😄

to consider it is typed (not recommended if library is having badly written and runs code on import)

4. fourth option could be just using pyright/pylance ignore instruction onto this import, but it will make propagating Any too much happily, turn_types method i think is better

[drowsy sedge]

def loginaccount():
username = username_field.get()
password = password_field.get()
stored_password = cursor.execute("SELECT passwordid FROM bms WHERE usernameid =?", (username,))
print(username, password)
if password == stored_password:
print("Login Successful!")
else:
print("Invalid username or password")

STATEMENT TO CREATE ACCOUNT
def createaccount():
inputpassword = password_field.get()
inputusername = username_field.get()
print(username, password)
cursor.execute("INSERT INTO bms (usernameid, passwordid) VALUES (?,?)", (inputusername, inputpassword))
print("Account Created")
ALLOWS SIGN IN TO SUCCEED
if accessgranted == True:
print("Login successful")

does anyone know why it wont insert anything i input into the database?

[coral wasp]

Where's the commit?

[drowsy sedge]

Do I need?

[drowsy sedge]

Is this why it wasn’t storing?

[coral wasp]

You only showed a snippet, but I'm assuming you weren't committing. Try adding a commit.

[drowsy sedge]

how do i put it as a text thingy in the discord

[coral wasp]

!code

[delicate field]

Formatting code on Discord

Here's how to format Python code on Discord:

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

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

For long code samples, you can use our pastebin.

[drowsy sedge]

i can show you my whole code, in theory it should work, it has no errors however it just does not store to the db

[coral wasp]

Open a help thread plz... #❓｜how-to-get-help

And there you can use the pastebin

[glacial ether]

is this going to show the backend stuff like servers side stuff and database and stuff?

[limpid wolf]

#jany

[formal lintel]

maybe if you went into the link you would be able to tell?

sending a link via a screenshot that you expect us to browse for you is a special kind of hell in itself

[normal sentinel]

would this be a good way to do cursors in python

nvm no it wont

[pliant pewter]

Hi All,

Would anyone be able to help me understand why my script is immediately repeating itself rather than waiting for 1 minute?

Main script

def job():
clans_list = fetch_clans_list()
for clan_name in clans_list:
clan_data = fetch_clan_data(clan_name)
create_and_insert_data(clan_data)

#Schedule the job to run every 1 minute
schedule.every(1).minutes.do(job)

Keep the script running

while True:
schedule.run_pending()
time.sleep(10)

[paper flower]

You have a for loop inside, no? 🤔

Seems to be working as intended

[pliant pewter]

i dont think so, this is the start of the code

Function to create and insert data into the SQL database

def create_and_insert_data(clan_data):
conn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}')
cursor = conn.cursor()

[paper flower]

for clan_name in clans_list:?

Your function executes once every minute

But you just run a for loop inside

[pliant pewter]

ahh okay, i'll try and work out where the loop is. Thank you!

[paper flower]

It's in your job function

[ocean apex]

Say I'm designing a library website, where users can take different actions on books, like borrow or edit for example.
Each action has different accompanying attributes.

• For borrowing, there's a due_back_date.
• For edit, there's nullable new_name and new_author.

How should I store the user-book association table? Right now I'm doing

UserID | BookID | ActionType | ActionID
```and have 1 table for each `ActionType` like `Borrow` and `Edit`, each with an id to be joined with `ActionID` (thus `ActionID` can be non-unique). 

Is this a good idea? Are there alternative approaches I can learn?

[glacial ether]

explain to me routing and http rquest methods in simple words

[storm mauve]

that has nothing to do with databases
also

• https://developer.mozilla.org/en-US/docs/Web/HTTP/Overview
• https://developer.mozilla.org/en-US/docs/Web/HTTP/Methods

[glacial ether]

thanks bro

[paper flower]

hm, in case of borrow/edit why do you want to have them in a single table?

With borrowing it's pretty easy to just make another table that would signify what book is borrowed and when

[ocean apex]

that's what I'm doing, or at least I think so
I have the following tables right now

Users
| UserID | Name | ...

Books
| BookID | Title | ...

BooksUsers
| BookUserID | BookID | UserID | ActionType | ActionID

Borrow
| ActionID | DueBackDate |

Edit
| ActionID | NewName | NewAuthor |

and I'm asking whether this is a good idea

[paper flower]

Explain why would you need BookUsers

also why would you need ActionID, i think you can have everything that you need in concrete tables

e.g.

Borrow
user_id, book_id, created_at, due_back_date, ...

[ocean apex]

ah wait I see what you mean

the table you just typed out
yeah yeah... tf was I doing

[paper flower]

And for edits maybe you just need some kind of audit/action log, people tend to use json for that

but honestly not sure what would be the best solution for that

[alpine glacier]

I want to hire some to find bug in my website

[cedar tiger]

!rule 9 says not here

[delicate field]

Rules

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

[alpine glacier]

Thanks for the information

[marsh glacier]

can we discuss?

[shell escarp]

!rule9

!rule 10

[delicate field]

Rules

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

[gloomy monolith]

not in this server please

[marsh glacier]

Sorry

Thanks

[normal sentinel]

what could possibly be the syntax error here?

(this is sqlite3)

[tranquil aspen]

Look at the syntax for order by

It doesn't involve writing ORDER BY twice

[normal sentinel]

oh lordy lord

yeah no nvm i still dont get it

ohhh

thank you

[normal sentinel]

my parameters are of the wrong type?

  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/home/leo/tuido/tuido/__main__.py", line 25, in <module>
    main()
  File "/home/leo/tuido/tuido/__main__.py", line 16, in main
    database.mark_done(connection, 7222)
  File "/home/leo/tuido/tuido/database.py", line 37, in mark_done
    cursor.execute(
sqlite3.ProgrammingError: parameters are of unsupported type```

[grim vault]

The second parameter to execute() must be an iterable even if it is only one value, so (id,) to make it a tuple or [id] for a list.

[normal sentinel]

oh

thx

[brave ingot]

I'm working on implementing reusable filters in an SQLAlchemy application, and I've encountered an issue with type checking in Pylance. Here's the code I've written:

from sqlalchemy.orm import Session, Query

from app.db import Quote, ENGINE


class QuoteQuery(Query[Quote]):
    def filter_by_foo(self) -> Query[Quote]:
        return self.filter(...)


session = Session(bind=ENGINE, query_cls=QuoteQuery)
session.query(Quote).filter_by_foo()  # Attribute "filter_by_foo" is unknown Pylance(reportAttributeAccessIssue)

Even though the query class is set to QuoteQuery, Pylance indicates that filter_by_foo is an unknown attribute. The code runs without issues, but how can I resolve this typing issue to make Pylance recognize the method correctly?

[fleet pine]

hi im trying to delete data for sqlite3 but the deleting isnt permanent. everytime i restart my server the data "returns". until that it cant be found. any1 have any idea what could cause this?

[waxen finch]

are you committing after you run your queries?

by default, sqlite3 implicitly opens a transaction when running an INSERT, UPDATE, or DELETE query, so nothing gets saved to the database until you conn.commit() it

and any changes that aren't committed by the time you close your connection/program will be rolled back

[fleet pine]

Aaaah didn't know you had to do it adter deleting 2, 1st time doing anything with data bases

Thanks

[paper flower]

Query API is deprecated in sqlalchemy 2.0

I'd recommend creating your own wrapper/repository/dao/whatever you want to call it to query your specific models

e.g.

@dataclasses.dataclass
class QuoteFilter:
    text_contains: str | None = None
    ...


class QuoteRepository:
    session: Session

    def list(self, filter: QuoteFilter) -> Sequence[Quote]:
        stmt = select(Quote)
        if filter.text_contains is not None:
            stmt = stmt.where(Quote.text.icontains(filter.text_contains))
        return self.session.scalars(stmt).all()

[brave ingot]

oh, thanks!

[brave ingot]

Yeah, I'm using something similar. I'm also looking to extract the filtering logic to a lower level, such as Query, so that I can reuse it. I want to adhere to the principle that if a function modifies something, it should modify its own object. Is there any way to achieve this in SQLAlchemy?

[paper flower]

It's kind of unsafe to do that since there's no guarantee you're calling it on a correct object

and I already mentioned that Query api is deprecated 😅

[brave ingot]

So the best way would be to create a function to filter the statement? So it would accept statement as an argument and return it?

[paper flower]

I think so

[brave ingot]

I wish there were extension methods like in C# xD

[brave ingot]

What do you think about making a class for filtering the statement? There will be a class that accepts the statement during initialization and filters it using methods. Is this already overengineering? I'm just not sure how to do it in the most convenient way. Ideally, I would like to build it as a builder pattern, but we already discussed that this is impossible

[paper flower]

How many filters would you have though 🤔

[paper flower]

I think just making a filter like that is fine for most applications ^

My colleague wrote this library to define and apply filters to sqla queries, I think you can take some ideas from it: https://pypi.org/project/sqla-filter/

[brave ingot]

For example filtering each sequence by common search parameters (q, offset, limit), assuming it's a dataclass. Also some filtering based on the relationships (created_by_user_id for example), filtering based on the type of a quote (all, saved, created). Maybe also ordering it. Ideally it should be a chain of functions so it's like a constructor

[brave ingot]

Looks interesting, thanks! I'll look into it

[brave ingot]

Oh I guess it's not supported there to do filtering on multiple fields by one parameter (q for example)

[paper flower]

Hm, I don't think so 🤔

I can ask them though, I didn't use the library myself

[paper flower]

I actually proposed making API more extensible, so you can supply anything to the FilterField, e.g.

class AnyOf:
    def __init__(self, *fields: list[SqlalchemySomethingSomething], op: Callable):
        self.fields = fields
        self.op = op
        
    def apply_value(self, value: object, stmt: Select[Any]) -> None:
        clause = or_(
            op(field, value) for field in self.fields
        )
        return stmt.where(clause)
    
    
search_stmt: Annotated[
    str | Unset,
    FilterField(
        AnyOf(Article.title, Article.body, op=lambda field, value: field.icontains(value))
    ),
]

[white jewel]

Hi, I just started getting some random db locked errors out of the blue. This doesn't happen when viewing or selecting data, but when it comes to writable data, it flips out. Write, update, delete, once it writes to the db it fails.

Context, I am using aiosqlite, and my code has been the same since last week or longer, so any idea on why the sudden error?

Also, I do understand the error and I was suggested on some typa pool management or allowing multiple writing to the db, but I want to make sure I'm actually solving the problem and not just slapping a bandage on it that might later on fall off.

[waxen finch]

"database is locked" means you have an uncommitted transaction from an open connection - make sure all your transactions are short-lived by committing or closing your connection as soon as feasible so it releases the lock on your database: py async with aiosqlite.connect("...") as conn: xyz = do_something_quick() await conn.execute("INSERT INTO ...", (xyz,)) await conn.commit() of course depending on your usage it might be hard to achieve that, but that's the general idea

you can additionally enable WAL mode to allow concurrent readers with one writer: https://sqlite.org/wal.html

that might resolve your contention issues if the connections timing out are only readers, but it won't resolve contention between multiple writers

[white jewel]

yep, I remember being suggested this. But I'm worried about data integrity with this.

[white jewel]

nope, readers are fine, it's the writers that keep screaming at me even when nth else is writing to it

[waxen finch]

ah yes, iirc it sacrifices durability by not syncing upon commit

[white jewel]

also, i a db locked right here aswell

which shouldn't be possible bc that is always the first query to run as soon as the bot starts

meaning it should have failed after this, not before or during.

[waxen finch]

do you have any other processes accessing the database? for example, a db viewer?

[white jewel]

yes, I do. But I've had it closed for some time, and I get the same issue.

granted I don't get the same error in that same place but I get it some where else

and I could easily chop it up to say, yh, maybe I have some loose connection somewhere, but if that was the case, it would've caused an issue a long time ago

I also considered my recent additions could have been the cause, but i disabled and removed those and the issue persist

and again, everything im doing now, i've been doing the whole time, so why is it bitching about it now is what's cnofusing me

[waxen finch]

hmm some more basic guesses, do you ever use conn = await aiosqlite.connect() in your code? and have you verified that you only have one bot process running? (does your bot show as offline when you interrupt it?)

if all else fails, you can restart your system to kill whatever process is holding their uncommitted transaction and then review your code for any long-lived connections that might not have been committed, or if you have other programs accessing the database that might not have fully closed (on windows, the PowerToys project has a File Locksmith utility that can tell you what processes are using your database file)

[white jewel]

Sorry, I went afk for a bit.

I I've used conn before. And there are no visible processes running. But what I'll do is restart my PC just after killing every program since I had my computer running for a couple days especially vscode.

And I was also thinking of setting up some type of log that can determine what had accessed the db where and if they were closed off. But your suggestion seems close enough, so how do I do it?

[white jewel]

@waxen finch hey, turns out the db was corrupted. I assumed as much, but normally the db does notify you when it's corrupted. I simple changed the db and the issue resolved itself.

[waxen finch]

that is a pretty unusual thing to happen, did you try recovering the data with the official CLI? or does it not have anything important to begin with?
https://sqlite.org/cli.html#recover_data_from_a_corrupted_database
https://sqlite.org/download.html

[white jewel]

Tbh, I have a state of the db in my git rep that I could fallback on. I wouldn't go as far as to say it doesn't have useful data. But i can manage to lose the data i've recently obtained since it's just testing data and nth concrete.

but i wanted to know more than anything how it became corrupted to begin with

and I would need to determine how to resolve if aswell, so when i do have data I need to keep, i can fix it.

But the good part is, I can read an access all the data, my concern stems from whether it's the data that corrupted it or just the db itself got corrupted.

[waxen finch]

did you find any extraneous files like *-journal, *-wal or *-shm?

[white jewel]

wdym?

where would I look for such files?

[waxen finch]

next to the database file, sqlite normally creates one/two of those during operation

i guess if you're not using WAL (i.e. only *-journal is created) then it doesn't matter too much

[white jewel]

I mean, I don't see that. Idk if it defers since it's created from my bots code when trying to access it.

Also reverted the db to a previous state and trying it out now, so imma see how it goes

[waxen finch]

in the case of rollback journal mode (default), the database file gets copied to *-journal whenever a write transaction begins, that's how it can rollback the database after a power loss or crash

[white jewel]

ohh, nice

[waxen finch]

in the case of WAL mode, *-wal and *-shm are created during a write transaction with the first file recording all the changes you're doing, and only disappear after the last connection is properly closed

[white jewel]

that would be useful

[waxen finch]

in other words, *-wal can contain part of the actual contents of your database, so deleting it or only copying the original .db file means you'll lose that data

though if i understand correctly the .db file itself shouldn't be corrupted...

[white jewel]

so it might be there data then

but that then makes it dangerous cause I can't transfer anything cause I don't know what could possibly causing the issue

[waxen finch]

was the corrupted database's filesize 0 bytes or close to that? maybe a program other than SQLite tried writing to the file

[waxen finch]

huh, turns out separating the *-wal file can corrupt the original .db file too:
https://sqlite.org/wal.html#the_wal_file

The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted.

[frank flax]

when i try to filter out explicit posts from my booru, i get this error: https://paste.pythondiscord.com/2BDQ
you can find the source code here https://github.com/rr-/szurubooru
i'm not really familiar with SQLAlchemy so i'd appreciate some help debugging this

[paper flower]

This means that session is already closed and you're trying to access attributes on model that was attached to it

[frank flax]

how do i fix it? i'm not familiar with this codebase

[paper flower]

simple hack would be to add expire_on_commit=False there https://github.com/rr-/szurubooru/blob/master/server/szurubooru/db.py#L11

[delicate field]

server/szurubooru/db.py line 11

_sessionmaker = sa.orm.sessionmaker(bind=_engine, autoflush=False)  # type: Any```

[paper flower]

but... it's a hack in this situation, there shouldn't be any problems like that in a rest api

[frank flax]

what are the downsides of that approach?

[paper flower]

https://docs.sqlalchemy.org/en/20/orm/session_api.html#sqlalchemy.orm.Session.params.expire_on_commit

you will be potentially reading stale data from memory, but if after commit object is just returned from the API then it shouldn't be an issue.
Also why not just create an issue in that repo so they can fix it?

[frank flax]

i think i will

and your hack fixed it for now, thanks

hm i think it might have been caused by some tags that lost their names

i added back the names, following a backup, and it worked without the hack

[peak summit]

hi

anyone try supabase b4?

[cedar tiger]

Don't ask to ask. Just ask.

[acoustic dagger]

i dont know if images are allowed in here but if it isnt im sorry. im trying to install the sql server developer option. after following youtube and going through the 1.GB download, i got this error when i click the first option in installation. help anyone

[wise goblet]

👀

consider to rethink your choices if u need SQL server if u can first

using normal db like postgresql or mariadb can be more convinient choice

[wise goblet]

if u wish to pursue this SQL server path (or installing any other relational db pretty much)
i would recommend you figuring out how to run WSL (or just switch already to linux)
And launching dev server in a single command like it is described at appropriate docker page https://hub.docker.com/r/microsoft/mssql-server
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -e "MSSQL_PID=Evaluation" -p 1433:1433 --name sqlpreview --hostname sqlpreview -d mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04

single command to run database is very convenient choice over manual shmagic of graphical clicks in Windows
simplifiable further to docker compose up without any args if having recorded arguments to yaml file

[acoustic dagger]

thank you so much

[acoustic dagger]

alright, i think i will go with this

[brave bluff]

@bitter forge My personal take is, if your public documentation requires you read how at least 4 separate references work to understand one component, then your documentation is bad. Sure, reading it can teach you a lot, but just like with code, if I write a method that requires you read 4 other classes/methods to understand, then you've written bad code. And with documentation, it's worse, because the point of documentation is to explain.

I'm now reading the select documentation references by the tutorial: https://docs.sqlalchemy.org/en/20/core/selectable.html#sqlalchemy.sql.expression.select. And from reading that, I am no closer to understanding how to use it. So I read the stuff linked in entities. And I'm now only slightly closer to understanding how to use it. If I have to spend half an hour to understand how select works, that's a problem.

I agree with you, it is informative. But it's not good.

[bitter forge]

this looks like API reference. have you clicked this link in the "See also" section in that reference doc?

[brave bluff]

Well, that's where I came from 😛

[bitter forge]

oh, ok, i see

[brave bluff]

But I was confused by the very first example: select(user_table). What is user_table?

It's referenced from the start, but I don't see it's definition

So I don't know what select can take.

So I decided to try reading the documentation that the tutorial references

And you can see how that becomes a confusing mess 😄

[bitter forge]

technically speaking, you can easily read it accepts _ColumnsClauseArgument[Any], but it doesn't tell me anything, and i would have to read it up in the source code.

[brave bluff]

Exactly!

If I need to read source, what's the point of documentation?

What good is a tutorial that shows you code that it doesn't define?

[bitter forge]

there is this though

which i overlooked

[brave bluff]

I saw that

[bitter forge]

(this color scheme doesn't work well for me)

[brave bluff]

But it doesn't help me understand what user_table is in the tutorial

I read up on ColumnElements and the fromClause objects

And it makes reference to the .c objects

But unless you've already read documentation on what .c is, it's a TERRIBLE variable/method name

I'd get pretty annoyed at a junior dev who submitted a PR with a variable c 😛 I'd be outright mad at anyone who isn't a junior dev.

I'm definitely not trying to be a jerk about this

It's just very poorly written documentation

[bitter forge]

well, i assume a Table object

any

also judging by https://github.com/search?q=repo%3Asqlalchemy%2Fsqlalchemy+"user_table+%3D+"&type=code

from the perspective of a person that wrote all of this... it can be obvious

from the pov a third person... not really

[brave bluff]

Exactly!

This documentation seems designed for someone who already knows the ins an outs of sqlalchemy. In which case, do you need documentation?

Anyway, that has been my rant for the evening 😄

[bitter forge]

why not act on your rant and suggest some improvement in the project issue tracker?

[brave bluff]

I've had folks even in this channel tell me the documentation is just fine, and to that I say, bull. Honestly, I'm trying to learn it now, and I plan on writing a tutorial that actually walks you through stuff. Not saying it'll be good, as I'm sure I'll make mistakes, but hey.

Haha

I don't feel like I should add to the project itself until I feel like what I'm adding is useful

But yes, I do plan to

I'm hoping I can write a tutorial that makes some sense. Get people to point out where it's wrong and improve it

And then use that cycle to feed back to the core site

Because right now, I don't have enough an understanding to help with the documentation side for sure. The tutorial seems like the place to start.

[bitter forge]

lmk if i can help. i myself would like to have a deeper understanding of sqlalchemy, as i think it's a beautiful thing to dig into.

[brave bluff]

Oh, I love doing code dives. Until now, I've been on the django side of the world, and have dove into it's ORM code many times.

And I appreciate it

I'm hoping I can start writing stuff up in the next week or two.

[bitter forge]

sent you an invite, let's keep in touch

[brave bluff]

Thanks for the typing help earlier, and the discussion here. For now, bed!

[bitter forge]

\o

[paper flower]

It's an alias for columns iirc 😛

[brave bluff]

Yup, it is

[paper flower]

A lot of libraries that live long enough would have some shorthands like that

look at numpy, and related

[brave bluff]

Oh, for sure

But numpys docs are also clear about what the aliases are at multiple levels 😛

[paper flower]

Well, sqlalchemy docs aren't the best, but they're not terrible

it's a complex library, and you can get by with docs just fine

[brave bluff]

I was more poking fun at the c reference

As someone starting it for the first time, with a lot of orm/query building experience, I disagree.

[paper flower]

Wouldn't https://docs.sqlalchemy.org/en/20/orm/queryguide/index.html be enough for the start?

[brave bluff]

I haven't read that yet, starting with what they reference as the tutorial. But even with that. The first section gives a wordy explanation of what a select is, but doesn't even tell you what gets returned.

If you've never worked with an orm before, you'd be scratching your head.

You can read through and infer what gets returned.

[paper flower]

https://docs.sqlalchemy.org/en/20/orm/queryguide/select.html#writing-select-statements-for-orm-mapped-classes

select is... select

You can check what's returned with mypy if you use static typing, and your IDE should handle that too

SELECT statements are produced by the select() function which returns a Select object

Is that what you wanted to know?

[brave bluff]

Heh, no, because I've already read enough to understand what's going on. But docs shouldn't be written for people with experience.

The docs are great, if you already have an understanding of things.

[paper flower]

I think it goes pretty in-depth, explaining a lot of things https://docs.sqlalchemy.org/en/20/orm/quickstart.html

[brave bluff]

But the docs also often reference variables without explicitly stating what those variables are. If you know enough of what's going on, you can infer what they are, but that's not good documentation.

[paper flower]

But ORMs should expect users to have a lot of SQL experience though.

[brave bluff]

I agree to that

But again, why does a "quick" start go into such expansive detail? I started there... It was overwhelming with information.

The docs are very detailed

I appreciate that

But i stand by then not being good docs.

[paper flower]

I'd say django docs aren't short either when dealing with orm

[brave bluff]

It's not about length, but how you walk through the info

The Django docs often refer back to stuff, in case your forgot. They give you some info to start, then dive deeper later. It's sectioned much better imo.

Id even say that the Django docs don't go enough into detail in parts.

[paper flower]

There's no middle ground 😅

[brave bluff]

But they give you links to the relevant code, so you can see how it works for yourself.

You're given the opportunity to learn at your own pace

SQLA just throws you into the deep end and waves

There definitely is middle ground, and a lot of documentation hits it.

Specially big, old, widely used projects

[brave bluff]

Haha

[paper flower]

I thinks it's kind of warranted, sqla is very complex, after using it for 2-3 years I have to refer to docs from time to time. But other ORMs won't allow you to do things that you can with sqlalchemy

[brave bluff]

Agreed

But

[paper flower]

And in case of django orm I think it's clunky and unoptimized, especially when saving/updating objects

[brave bluff]

I think it's also a bit of a cop out. It's very powerful, but you can look online to find tutorials that do a much better job of explaining it

And given how old it is, if random folks can do a good tutorial, why can't the core site?

Powerful doesn't mean confusing.

Numpy is super powerful, and it's tutorial walks you into it's concepts and how it's used.

Again, I'm not just trying to whine.

[paper flower]

It's also a lot more popular imo

And a lot of people who use numpy aren't that familiar with programming or python

[brave bluff]

I think part of the lack of popularity of SQLA is its documentation.

If people can't figure it out they're not going to use it.

As i said before, I'm hoping I can learn it well enough to purpose changes to the tutorial at least.

[paper flower]

The fact that django can't properly track mutations still bugs me to this day:

def main() -> None:
    with session_factory.begin() as session:
        user = session.scalar(select(User).limit(1))
        user.email = "new email"

INFO sqlalchemy.engine.Engine UPDATE user SET email=? WHERE user.id = ?

[brave bluff]

But I'm an experienced dev and it's annoying to learn.

[paper flower]

If you just call save it will save all the fields 😫

[brave bluff]

Wait you don't have to commit that!?

[paper flower]

It does commit

session_factory.begin() .begin()

[brave bluff]

Explicitly I mean

[paper flower]

https://docs.sqlalchemy.org/en/20/orm/session_basics.html#using-a-sessionmaker

I agree that it's not very explicit but at the same time it is

session_factory() would just open the session

session_factory.begin() would open the session and commit at the end

[brave bluff]

I read that section 3 times and never noticed that

Wtf

[paper flower]

It mentions that in the text 👀

[brave bluff]

This documentation... Anyway, bed for now, back to learning tomorrow, and then hopefully writing it better later.

Yah, it's there, but it's just not easy to follow.

[paper flower]

Have you been spoiled by fastapi's docs? (the concurrency explanation) 😅

[brave bluff]

It took me a while to understand why session maker vs just a session for example

It's a simple enough concept, but explained poorly.

[paper flower]

Btw, design wise i'd agree that it would be great if sqlalchemy migrated to something else

mkdocs can embed code from files which helps with linting and keeping them up-to-date too

[brave bluff]

Anyway, thanks for the discussion. I learned stuff, got more view points, which only helps me.

I'm happy to continue/discuss more later!

[paper flower]

Feel free to ping me, I'm no sqlalchemy expert, but been using it for quite some time

[brave bluff]

I appreciate that!

[formal lintel]

burger docs ™️

[low bane]

Hi, I’m diving into databases and taking inspiration from Discord’s system. I read that in its early days, Discord used MongoDB, and they implemented their own Snowflake ID system.

In my case, I’m considering a similar approach for ID generation. My question is: Should I replace the default _id field (which uses ObjectId in MongoDB documents) with my own custom ID generation system, or should I keep the _id field as is and add a separate field named id for storing my custom IDs?

Also, are there any disadvantages or potential issues I might face if I replace the _id field with my custom ID, assuming my ID generation system ensures uniqueness?

[brave bluff]

I don't have a ton of experience on the nosql side, but from what I've done and read, the general recommendation is to keep the standard _id and add your own, unless you REALLY know what you're doing.

[still laurel]

Hello! I am doing a google spreadsheet lookup to find duplicates as part of a program and compare the database with the spreadsheet. I'm doing a raw row comparison but that program is too slow so I am thinking about doing a hashing comparison instead. Are there any other ways I could do this? If I'm not the correct channel, can someone direct me to the right one?

[paper flower]

Why do you feel like you need custom id generation? Default one seem similar to uuid7 which is gread

[brave bluff]

Back to confusion from sqlalchemy documentation. Two questions atm:

1. What is the proper/optimal way to pass sessions? I'm using FastAPI, and I have a sessionmaker instance created (session = sessionmaker(bind=engine)). Should I import it as a dependency and do a session.execute? Use a with statement? How do I actually implement the querying?
2. What is the point of scalars? Results wrap each element in a tuple, scalars don't. So it's a different method to work with the ORM component, instead of making the component intelligent/adaptive? I'm really hoping there's some usefulness here I'm missing, as the docs even say internally it's just taking the result and converting it, so it's really just adding a helper function to unwrap the tuples?

[brave bluff]

And I guess I'm now thinking about if I should be using session with commit, or Session.begin...

[paper flower]

1. If you didn't have fastapi the optimal way would be to just use it in a with block.
   With fastapi you could make a dependency and then use it in your router(s):

async def get_session() -> AsyncIterator[AsyncSession]:
    async with session_factory() as session:
        yield session

To use or not to use .commit with sessionmaker is up to you

2. scalars is just a shorthand to get results in case you select a single entity or column, e.g. select(User) or select(User.username)

execute always returns tuples, e.g. (User(...), )

to explain it a bit simpler - it just returns first elements from these tuples

[brave bluff]

For 2, that feels like a weird design choice... A helper method to unpack a single element tuple

[paper flower]

It's very convenient

I don't think it's weird

[brave bluff]

Why not bake that into the core results object I mean.

Or do it via a flag?

[paper flower]

Why do it via flag though?

session.execute().scalars() is a thing too

What session.scalars() does is basically session.execute().scalars()

[brave bluff]

Maybe it's a design choice thing...

Thanks for helping expand

[paper flower]

I certainly wouldn't want to do that by hand each time, since in a lot of cases you select just one column or entity

e.g. [t[0] for t in session.execute()] instead of just session.scalars()

[brave bluff]

I agree to that last part

And I guess it's because they've built it in a way that the execute method knows nothing about the query it's running, that it's orm built and should return singular results.

[paper flower]

Btw, In case of fastapi I'd guess that you usually want to keep the transaction open for the duration of the request, so I personally would prefer to use .begin() in a dependency

[brave bluff]

I was leaning that way. I had a sync version of the method you showed. But are you suggesting in the dependency method itself to use begin?

[wild nymph]

SELECT * FROM models ORDER by id asc, id asc;

If I do this, will postgres execute the order command twice?

[livid crystal]

i only see a single query in there, but the order by looks weird

[wild nymph]

Sorry, I meant the sort command

will postgres sort twice?

[livid crystal]

i'm not sure what it would do there, run it with explain and see what it says

clever=> explain SELECT * FROM tbl ORDER BY c1, c1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Sort  (cost=158.51..164.16 rows=2260 width=8)
   Sort Key: c1
   ->  Seq Scan on tbl  (cost=0.00..32.60 rows=2260 width=8)
(3 rows)

clever=> explain SELECT * FROM tbl ORDER BY c1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Sort  (cost=158.51..164.16 rows=2260 width=8)
   Sort Key: c1
   ->  Seq Scan on tbl  (cost=0.00..32.60 rows=2260 width=8)
(3 rows)

as best as i can tell, the 2 queries look identical

[wild nymph]

yes, thanks 🙂

[wild nymph]

Can someone share with me, based on their experiences, what advantage there is in using the "interval" type in Postgres, instead of simply saving an int or float, according to some defined measurement (seconds, minutes, etc.)

[paper flower]

int/float would rely on convention your application uses, interval clearly states that it's an interval of time

It's the same as using datetime vs int/float

[acoustic dagger]

hey guys, im having issues installing sql, any of em to be honest. microsoft, mysql, postgresql. i need help guys

[paper flower]

What problems?

[acoustic dagger]

for the microsoft sql, the developer installed fully but i couldnt install the stand alone version

[acoustic dagger]

here it is

so i decided to go with the express version. but its saying cant access the rules. so it didnt download the installation at all

most videos on postgre are outdated and they dont even talk about the installation

i just need to install this to start learning and up my data science sklills

and databases of course

[paper flower]

What is express version?

Also what rules are you talking about?

If you want to install postgres on windows just download it from the website that postgres website refers to:
https://www.postgresql.org/download/windows/
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

[acoustic dagger]

apparently microsoft has 2 free versions. the developer version and the express version.
about the rules, its something the go through before they begin the download. but it couldnt go through it

[paper flower]

I never heard of them, just download one from edb

[acoustic dagger]

alright, thanks Doc. hey, which one do you use?

[paper flower]

Just download the latest one

[acoustic dagger]

works on windows 10 yh?

[paper flower]

Yes

[acoustic dagger]

hey, you there

port number please?

its asking me to provide. theres a default one there but is that it?

[paper flower]

You can leave it as default

[acoustic dagger]

ok, thanks. installing already

[nocturne wren]

Any guides on how to learn databases? I was going through one of the resources here and it said to use the shelve module to store and edit data but I’ve heard the Json module is better

I was wondering which module would be better to learn

[pearl grotto]

I think json module is better

json is human-readable and interoperable with other programming languages

[nocturne wren]

So if I create a database in json then I can connect to it in front end as well?

[pearl grotto]

yeah

[wise goblet]

https://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
This manga guide is a good start into relational databases. you should aim for relational databases first, because they ensure satisfying 95% of app needs, bring higher quality into database related stuff, and because python world has things really mature to use them with least effort

JSON/Shelve databases is not really serious databases. Even not very serious pet projects for developers usually start with using sqlite3 first (or just storing data in memory of app without persistance)
There needs to be a serious justification (and understanding what you are doing), why you are not using relational database for a current project

[nocturne wren]

That’s not something I expected

I’ll check it out when I get paid

[wise goblet]

it is somewhat silly looking but it is very brain friendly. I think first book should be always the most brain friendly and easy to grasp material

since u are on the level of json/shelve, this book is actually pretty much at appropriate level for sure

https://sqlbolt.com/ add to it practice a bit to get basics

end usage expect in python with Django ORM or SQLalchemy+Alembic

[nocturne wren]

I’m just following the book, I’ve not learned Shelve completely

And never done json before

[sharp tinsel]

Hiya, I'm trying to get into async SQLAlchemy, got stuck on creating raw connections to aiosqlite. I run into sqlalchemy.exc.MissingGreenlet errors. Web search suggests this issue is due to ORM lazy loading, but I'm not using the ORM at all.

The errors make me believe this has something to do with SQLAlchemy using a different event loop to create the connection than the event loop used by the returned aiosqlite connection, but I can't substantiate that or find any online documentation or discussion for this.

Now I could just use Session objects with the text() contructor but I'd really like to be compatible with legacy, pure aiosqlite routines rather than migrating everything to SQLAlchemy sessions.

Anyone with ideas or pointers here? Maybe folks over in #async-and-concurrency would be able to decipher the internals?

[sharp tinsel]

... I may have misunderstood what "raw_connection" does: taking a raw connection and pulling out driver_connection gives me the object I expect. Problem solved, kind of. I still can't call raw_connection.close() with the greenlet error but I can .detach() and call close on the driver_connection instead, so I'm just gonna do that until alchemy takes its revenge on me

[pseudo creek]

who has experience with web scraping?

please help me

[coral wasp]

Ask in #python-discussion

[inland mica]

i have been trying to set up a connection between mariadb and python in a py virtual environment in my linux-kernel based system, but i am failing to do so.

i just did pip3 install mariadb in my venv and it installed with the following output:

Collecting mariadb
  Downloading mariadb-1.1.11.tar.gz (85 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Preparing metadata (pyproject.toml) ... done
Requirement already satisfied: packaging in ./lib/python3.12/site-packages (from mariadb) (24.2)
Building wheels for collected packages: mariadb
  Building wheel for mariadb (pyproject.toml) ... done
  Created wheel for mariadb: filename=mariadb-1.1.11-cp312-cp312-linux_x86_64.whl size=198255 
  Stored in directory: /home/user/.cache/pip/wheels/ac/85/7b/fe477f84509d861132bd70e16c9fc1056b5971a9b0e0e2c75f
Successfully built mariadb
Installing collected packages: mariadb
Successfully installed mariadb-1.1.11```

but when i run a test file, i get `no module named mariadb`

i will keep trying to fix it for now.

got it fixed, 
i was running my IDE in root environment, meanwhile mariadb was installed in virtual one, stupid of me.

[coral wasp]

Glad you figured it out. The other issue that can happen is two different versions of Python.

[brave bluff]

Anyone know, with async sqlalchemy, do I still need to call await on queries? I'm new to async, and I'm not positive where I should be using await vs not.

Ok, yes, looks like I should use await on anything that's async

[brave bluff]

With sqlalchemy, I have this method:

    @staticmethod
    async def get(user_id: Optional[int] = None) -> Optional["User"]:
        async with session_manager.session() as db_session:
            getUserQuery = db_session.scalars(
                select(User).where(User.id == user_id).limit(1)
            ).first()
            return getUserQuery

When I doing it sync, it didn't give me any pylance errors, but now on first(), it says Cannot access attribute "first" for class "Coroutine[Any, Any, ScalarResult[User]]". Anyone know what I've done wrong?

[coral wasp]

Await the async calls

Async calls return a coro, you have to await it to get the result

[brave bluff]

That's stupid of me, I just said that above that post, heh

Async really throws me, heh

[coral wasp]

You'll do it 1000 more times, like me

[brave bluff]

I just did getUserQuery = await db_session.scalars(, but same error

Did I put it in the wrong place?

[coral wasp]

I dunno, show your traceback

!trace

[delicate field]

Traceback

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

A full traceback could look like:

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

If the traceback is long, use our pastebin.

[brave bluff]

No traceback, it's a pylance error atm

Ah ha!

I can't chain .first()

[coral wasp]

Oh wait, await the call before... that

[brave bluff]

The scalar is async, first is not

[coral wasp]

Yup

See? 999 more times to go

[brave bluff]

So

getUserQuery = await db_session.scalars(
    select(User).where(User.id == user_id).limit(1)
)
return getUserQuery.first()

I thought I got away from this by ignoring JS for a while, but NOOOOOO, now it's in my precious python too....

[coral wasp]

I don't use it, but some people recommend anyio to simplify this stuff

!pypi anyio

[delicate field]

anyio v4.6.2.post1

High level compatibility layer for multiple asynchronous event loop implementations

Released on <t:1728916304:D>.

[brave bluff]

And I know I shouldn't just return first, I need to wrap that for errors, but I'm getting there

I'll take a look, thanks! For now, boardgame night!

[paper flower]

How does anyio helps there? 🤔

[coral wasp]

Not specifically here, but in dealing with concurrency, and all the other stuff that comes along once you move into asyncio land.

[paper flower]

Hm, I only really found anyio helpful with some of it's stream stuff (i.e. anyio.create_memory_object_stream), you don't really need anything else

Also in case of sqlalchemy you can't use it concurrently (at least the same connection/session)

[stiff radish]

A lot of those comments are from the time there was no TaskGroup in asyncio

[paper flower]

Yeah, but when it comes to sqlalchemy - it won't simplify much

We just need .await in python 🙏

[stiff radish]

Because you'd much rather do 1 good query than a taskgroup doing, I agree

[paper flower]

Hm, I don't mind using taskgroup, but you can't run multiple queries on the same session/connection concurrently

You could use multiple sessions concurrently but that's pretty much limited to something that doesn't need ACID or read heavy operations

[halcyon mason]

can someone here help me with using datasets?

idk if its in this category but

[paper flower]

Don't ask if someone can help you, just explain your problem so people can see if they can help you

[halcyon mason]

i need help with using a dataset for a neural network i made

i have no clue

how i import,read etc

mnist set to be specific

[paper flower]

I never dealt with that, but probably more relevant to #data-science-and-ml

[halcyon mason]

nobody there replies 😭

[thin jasper]

👨‍💻 Developer Looking for Exciting Opportunities!

Hi everyone! 👋 I'm a developer with a strong passion for building scalable and innovative solutions. Here are the skills I bring to the table:

🔹 Backend: PHP, Laravel, NestJS, Symfony
🔹 Frontend: React, Vue, Livewire, Stylius
🔹 Mobile: React Native
🔹 CMS/Headless CMS: Directus

I'm always eager to learn, collaborate, and tackle challenging projects. Whether it’s contributing to your existing team or starting something new, I’m ready to help bring ideas to life!

💡 Let’s connect and create something amazing together! Feel free to DM me or reply here! 🚀

[paper flower]

Please do not post advertising in this server

!rule 9

[delicate field]

Rules

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

[brave bluff]

Honestly, seeing that sort of thing makes me LESS interested in working with that person.

[solar pagoda]

guys, I am in love with mongodb because it is easy for beginners to discover the world of databases with its key-value "schema". What you guys have to say about that?

[thorny anchor]

no

[solar pagoda]

😠

[nocturne wren]

is sql part of python?

probably a dumbass question but oh well

[ember badge]

anyone familiar with hopsworks?

[ember badge]

nope

[cyan bay]

i am also a beginner, but trying to learn by answering questions like this.
no. sql is not part of python.

it is its own language with syntax and many "dialects" which correspond with the various database management systems (DBMS) that are out there. SQL is used to work with SQL databases, basically.

I use Python to work with tabular data, as might come from a database, so sometimes I will use libraries like SQLAlchemy to map database objects directly to Python, but for other scripts sometimes it makes more sense to query a database with the SQL directly. Then, I write a SQL statement to do that.

Does this make sense?

[nimble bronze]

what are other scripts

[cyan bay]

scripts as in short piece of Python or SQL code I write to gather or process data

[nimble bronze]

so, in a short talk, you mean that python can only used to push data to database

sql used separetely by python to sort out data inside db

then sorted db used as output?

[cyan bay]

Both Python and SQL can do both, actually. I just know how to use Python better, but SQL is better optimized (read: built for working with Databases with a long history with them), so sometimes it makes more sense to write something in SQL.

[nimble bronze]

what AWS brings new to depreciate query lang?

or is it just cloud storage bucket

[cyan bay]

I am not familiar with AWS in particular, sorry. A quick Google suggests that AWS can deploy Microsoft SQL Server (colloquially SSMS), which is a DBMS that uses SQL queries, so I am not sure it can depreciate them. Maybe it does though.

If I had to work with data stored in AWS, my first searches would revolve around compatibility with Python, including if there's a dedicated library to it or if SQLAlchemy supports all/parts of it. I would try to understand what others have done and why.

[nocturne wren]

So you can use Python and SQL togehter?

[cyan bay]

Yup!

Over time, best practice might be seeing what your organization/team does for certain scenarios. But you can send SQL scripts to the DBMS through Python. You pass the SQL code as a string into your Python function that has accessed the database (this involves signing in with some credentials, connecting to the database you want within the DBMS, then placing your "cursor", or starting point, inside that database.)

[nimble bronze]

but for that simple purpose of pushing ang pulling data, Nosql is better with python, right?

but to manipulate, sql is better in real i think

[cyan bay]

Not sure. Haven't used NoSQL in classes or for work yet. I know it's out there and reasonably popular.

[torn sphinx]

What is the best way to write Fast code , ALL Cap SQL key words , using a caps lock button every time or shift then character everytime

[cyan bay]

how forgetful are you

[torn sphinx]

I am beginner and I am not sure how you guys do it , but I want to practice on the best way so I can type SQL faster and get used to it in early stage 😄

[cyan bay]

the best way probably depends on the human typing! i am forgetful, so i use Shift so that i don't have to retype the inevitable caps after i click away from the SQL statement

[torn sphinx]

It feels slow for me , is it the same way for everyone?

[cyan bay]

also i use * a lot in SQL statements, if I use the shift method i'm primed for it

[torn sphinx]

Since when did u start learn SQL

[coral wasp]

I write messy SQL, then run it through a formatter. https://github.com/sql-formatter-org/sql-formatter#readme

[finite cloak]

Are there any standard practices (or at least some well-defined approach) to populate a PostgreSQL database using a Python script?

For context, I'm using SQLAlchemy to define the SQL tables in modules as such - src/database/staff.py. My thoughts are the script could import the table object (such as Staff) from the aforementioned module, connect to the db, run a session and do its thing when I run python scripts/seed-db.py. But I'm stumbling across a bunch of import error messages which I can fix but the solution does not feel clean and "Pythonic". Here's what the structure looks like:

.
└── project/
    ├── src/
    │   ├── __init__.py
    │   ├── dbschema/
    │   │   ├── __init__.py
    │   │   └── staff.py
    │   ├── ...
    │   └── main.py
    ├── scripts/
    │   └── seed-db.py
    ├── tests/
    │   ├── __init__.py
    │   └── ...
    └── pyproject.toml

[wise goblet]

Do u need to populate db for tests or not for tests

[finite cloak]

For the tests as well but I think if I can solve the issue with the script and the database gets populated with the appropriate data, fixing the test should be easy (at least I think so if I'm not missing out on anything)

[wise goblet]

https://factoryboy.readthedocs.io/en/stable/
https://factoryboy.readthedocs.io/en/stable/orms.html#sqlalchemy
For tests it is awesome to use Factory Boy, it has Sqlalchemy integration.

It makes sure your tests code has created relevant test data in single and bulk ways with most minimum amount of code usage and the most reusable (handling auto creation of nested by foreign keys objects too if necessary)

You fill in only important fields for your test code, the rest is auto generated/faked

[finite cloak]

Thanks for sharing! I briefly read through the docs, it looks like it wraps the Faker library to generate data as well?

I'm actually directly using Faker in the seed-db.py script itself to generate a batch of data, that is not a problem. My concern is how I'm importing the SQLAlchemy tables to the script. Is it a good practice or should I rewrite the tables in the script itself to keep it idempotent?

[wise goblet]

Thanks for sharing! I briefly read through the docs, it looks like it wraps the Faker library to generate data as well?
yes

[wise goblet]

I'm actually directly using Faker in the seed-db.py script itself to generate a batch of data, that is not a problem. My concern is how I'm importing the SQLAlchemy tables to the script. Is it a good practice or should I rewrite the tables in the script itself to keep it idempotent?
how do you import tables now? from src.dbschema.stuff into you seed-db.py ? #databases message

For context, I'm using SQLAlchemy to define the SQL tables in modules as such - src/database/staff.py. My thoughts are the script could import the table object (such as Staff) from the aforementioned module, connect to the db, run a session and do its thing when I run python scripts/seed-db.py. But I'm stumbling across a bunch of import error messages which I can fix but the solution does not feel clean and "Pythonic". Here's what the structure looks like:
yeah... i see

why you have import error msgs? your staff.py is supposed to be imported by everything, but it does not import anything, no?

[finite cloak]

Yeah exactly! Something like this:

# contents of the scripts/seed-db.py file
from src.database.dbschema.staff import Staff

...

which I know can have its own shenanigans and is not a good approach?

[finite cloak]

When I run python scripts/seed-db.py I get error messages like src module not found

I could potentially append to the sys.path value but is it a good idea?

[wise goblet]

ah. That's not Sqlalchemy problem at all. it is regular Python Package Importing problem... i struggled first 1-2 years in python with it 😅

[wise goblet]

I could potentially append to the sys.path value but is it a good idea?
DONT. Don't hack your pythonpath until u learned what u are doing. and in general avoid hacking sys.path later anyway

[finite cloak]

Yup exactly! I don't want to mess around with the Python path either

[wise goblet]

When I run python scripts/seed-db.py I get error messages like src module not found
python3 -m scripts.seeds_db 😋

that will make sure your absolute root remains Project Root and will run your scripts/seeds_db.py (ergh.. not sure how it will treat - sign, probably best to fix to underline)

so it will be able to import from src

python3 -m scripts btw is shortcut to run code from scripts/__main__.py

[finite cloak]

Got it! So invoking the script as a module is completely acceptable? The script has a shebang and my idea was to invoke it as an executable eventually though

It's not a problem if invoking the script as I intend to, goes against the design principles of Python itself though! I just wanted to know what are my options in this context lol

[wise goblet]

Ergh... i guess it is justifiable enough reason to hack sys.path. do it for scripts only, not impact main working code with it, it shoud avoid weird sys.path shenanigans

from pathlib import Path
import sys
project_root = Path(__file__).parent.parent
sys.path.append(str(project_root))

pathlib makes this hacking elegant enough

[finite cloak]

Got it! Yeah no way will I hack sys.path for the actual "main" code though!

I'm leaving that as is

[wise goblet]

if u will have too many scripts, consider using click btw. then you could be having from project root file entrypoint for different scripts and have all their arguments properly autodocumented on the level of its interface

argparse is std lib requiring no extra installations for CLI interface, but it is awkward in terms of code scale (it does support registering many commands, including grouping them nevertheless). Usually great solution for a single file script with any amount of args though as it helps properly documenting arguments

[finite cloak]

I don't have plans to scale up with the usage of scripts yet (I want to keep their usage as minimal as sanely possible) so I think argparse is the best candidate in this regards! I've used it before when we had to setup some GitHub runners on a server and it was a great experience using it!

[wise goblet]

the main argparse pitifal in my opinion that we extract all input from not typed Simple Namespace 😅

so... good enough for 1 script, but awkward for many

[finite cloak]

Oh yes yes that was a minor pita for me as well! I wish there were ways to deal with it and my editor could just autocomplete the inputs! So yeah I definitely know what you mean!

[narrow jewel]

https://discordapp.com/channels/267624335836053506/1310648270753763380 can anyone help? ❤️

[finite cloak]

What does the connection variable evaluate to? I've a feeling it's evaluating to "nothing"

[soft basalt]

I am creating backend with django
but somehow I don't know why there's issue of library 'psycopg'

  File "d:\resnet\resnet_backend\resnet_backend\test_db_connection.py", line 1, in <module>
    import psycopg2
  File "D:\resnet\resnet_backend\env\Lib\site-packages\psycopg2\__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
    ...<10 lines>...
    )
ImportError: DLL load failed while importing _psycopg: The specified module could not be found.

This is the message I am getting this error I don't know why is this happening

[unkempt prism]

what have you pip installed exactly?

From the snippet you provided with import psycopg2 you need pip install psycopg2-binary

You may have accidently used psycopg3 which will be perferable though your code seems to want v2.

[soft basalt]

well I first I uninstalled psycopg psycopg2 psycopg2-binary then I installed one (one of the above mentioned )then ran the script didn't worked so one uninstalled that one then installed another one with this approach I installed all three and then checked them the error was still the same
then I recreated Venv still it didnt worked for me then Claude.ai suggested me to install Vs C++ redistrubuted... somthing I installed it and restarted still the error was same
I probably thought that its my python which is giving me error so Re-installed it
well well well what do you knwo that error was still there
i thought I am giving wrong details of database so I tried prisma to connect same Database and it did connected I don't know what is going on +_+
I do want to mention one thing..
I have python3.13 installed but there is 3.12 which was installed form MIcrosfot Store. I can't uninstall it may be this could be problem ?

[forest stratus]

Nah same problem and idk why !! I tried every verson of pythom from 3.8 to 3.13 and i tried every psycopg2 , 3 , binary and together and spearated still didint work and i install c++ file too still have that issue !

[brave bluff]

Anyone know how I can get the number of rows returned from a sqlalchemy result? Or am I thinking about it wrong, and should be doing a count on the query itself? In this case, I want to see if there are any results, or if none returned.

[paper flower]

<@&831776746206265384> ^ this is not the first time

[coral wasp]

!warn 1175116099348607086 we don't allow advertising, recruiting or anything like that here. Don't post this again or you'll be removed from the server

[delicate field]

:incoming_envelope: :ok_hand: applied warning to @thin jasper.

[paper flower]

ty

[soft basalt]

so are you using different database for now or still searching for solution?

[trim ore]

When I use pymongo find_one does it retrive the entire document or only the fields I access?

[cedar tiger]

It gets a single document https://pymongo.readthedocs.io/en/stable/tutorial.html#getting-a-single-document-with-find-one

[nimble canyon]

Can some one tell me what databases is

Some one said that I need to use it

[thorny anchor]

a database stores data persistently, meaning after your program ends, it will still be there. it also has other nice properties that we like

[sullen token]

better to ask here ig

What are some good websites to host mysql for free. I only need it for a month, it's for a showcase

[nimble canyon]

i use pylexnodes

[fleet ibex]

quick question, pymsql or mysql-connector-python is better,, or another choice i havent come across yet?

[spare robin]

when i try to connecct database the shell restarts how to fix it

[cedar tiger]

how did you try to connect to the database?

[viscid karma]

which service on the internet offers the most amount of storage sql for free?

[rotund igloo]

Do you care about the specific DB? Postgres vs MySQL vs SQLLite (locally)?

[viscid karma]

MySQL

[rotund igloo]

If you don't need to keep it around for a really long time Digital ocean offers $200 of credit that you need to use in 60 days, and they have easy to use MySQL. But you will end up paying for it after.

GCP also gives $300 for new accounts. That can get you a decent amount of "free" DB.

[fervent charm]

Why is there no df.to_sql(..., if_exists='update')

[fervent charm]

now I have to use duckdb

[viscid karma]

hmmm.... i guess https://freesqldatabase.com is still the "Really free" thing, thanks for your suggestions anyways

[bright gust]

Hey, guys. I need some help figuring out how to insert millions of rows into an SQL database (PostgreSQL, to be specific) using a DataFrame, but if there's an ON DUPLICATE, update the existing values.

I've tried using SQLAlchemy for insert and update operations, but even when splitting into batches of 10,000, it uses a lot of RAM. The most efficient method I've found so far is using execute_values, but I wanted to open this up for discussion to see if there's an even better approach.

from pandas import DataFrame
from psycopg2.extras import execute_values
from sqlalchemy import Engine


def upsert(df: DataFrame, target_table: str, target_engine: Engine, on_conflict_columns: list[str], work_mem: str):
    # 'df' is already split into batches of 10_000.
    conn = target_engine.raw_connection()
    try:
        cursor = conn.cursor()
        cursor.execute(f"SET LOCAL work_mem = '{work_mem}';")

        columns = ', '.join(df.columns.tolist())

        if on_conflict_columns:
            conflict_cols = ', '.join(on_conflict_columns)
            update_cols = ', '.join(
                f"{col} = EXCLUDED.{col}" for col in df.columns if col not in on_conflict_columns)

            query = f"""
                    INSERT INTO {target_table} ({columns})
                    VALUES %s
                    ON CONFLICT ({conflict_cols})
                    DO UPDATE SET {update_cols}
                    """
        else:
            query = f"""
                    INSERT INTO {target_table} ({columns})
                    VALUES %s
                    ON CONFLICT
                    DO NOTHING
                    """

        execute_values(cursor, query, df.values.tolist())
        conn.commit()
    finally:
        cursor.close()
        conn.close()

[spiral breach]

from pandas import DataFrame  
from psycopg2 import connect  
from psycopg2.extras import execute_values  

def upsert(df: DataFrame, target_table: str, connection_string: str, on_conflict_columns: list[str], work_mem: str):  
    conn = connect(connection_string)  
    try:  
        cursor = conn.cursor()  
        cursor.execute(f"SET LOCAL work_mem = '{work_mem}';")  
        columns = ', '.join(df.columns.tolist())  

        if on_conflict_columns:  
            conflict_cols = ', '.join(on_conflict_columns)  
            update_cols = ', '.join(  
                f"{col} = EXCLUDED.{col}" for col in df.columns if col not in on_conflict_columns)  
            query = f"""  
                    INSERT INTO {target_table} ({columns})  
                    VALUES %s  
                    ON CONFLICT ({conflict_cols})  
                    DO UPDATE SET {update_cols}  
                    """  
        else:  
            query = f"""  
                    INSERT INTO {target_table} ({columns})  
                    VALUES %s  
                    ON CONFLICT DO NOTHING  
                    """  
        
        execute_values(cursor, query, df.values.tolist(), page_size=10000)  
        conn.commit()  
    
    finally:  
        cursor.close()  
        conn.close()

[wet fog]

Hey dose anyone know where i can find the documentation for the firebase python cloud functions

Cus i canr find the auth trigger for pythob