databases

[rain remnant]

thanks for the tips, ima try and fix

[waxen finch]

a locked sqlite database should mean that there's another connection to the same database file, more specifically a connection that's holding an exclusive (i.e. writing) lock on the database (assuming its configured with the default rollback journal mode)

where that other connection comes from could either be an external program, e.g. a graphical program you're using to edit the database, or in your same script, such as by accidentally connecting to the same database twice
here's a demonstration of the latter: ```py

import sqlite3
conn = sqlite3.connect("test.db")
conn.execute("CREATE TABLE test (x)")
<sqlite3.Cursor...>
conn.execute("INSERT INTO test VALUES (1)")
<sqlite3.Cursor...>

Creating a second connection without having closed the first:

conn = sqlite3.connect("test.db")
conn.execute("INSERT INTO test VALUES (2)")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: database is locked``` and extra resources if you want the details:
https://sqlite.org/atomiccommit.html
https://sqlite.org/lockingv3.html

[rain remnant]

tyvm 🙂

[frank cloak]

https://www.postgresql.org/docs/current/sql-altertable.html

New owner should help, right?

[ancient meteor]

new owner sounds good. You also could grant permissions to another user if you don't want to change the owner. And if you want to copy a database, pg_dump can help you with that.

This is aiosqlite? Did you maybe forget to commit?

[chrome temple]

Solved it already, thanks anyway!

[ancient meteor]

👍

[chrome temple]

Problem was that it was getting the data before the check

[frank cloak]

I have nothing in the database

no data rn

[agile flame]

So I have a list of students I want to capture data for.
Each one has the same number of fields/parameters
I'm trying to create a flowchart for this

If I want to use an ERD, can I? If so, how would you layout the dataflow of this
if I want to make an ERD diagram of a nested python dictionary, are python dictionary "keys" really "fields"?

{"ben": 
  {"height":12, "points":[1,2,3] }, 
"john":
  {"height":15, "points":[23,10,1,2]} 
}

[distant matrix]

So I'm trying to extract data from a file using the ezdxf module. Does anyone know if with the pandas dataframes you must clarify the positions the data is stored in the resulting dictionary?

[queen rose]

Hey guys, How can I check if 10 records exists in a table? (MySQL)
Ideally I would want to get back mapping with all the records, and a boolean if they exists or not (the table has a column id PRIMARY KEY which I will use to check if its present or not)

[lyric owl]

hey, i'm writing data to a database using sqlite3. I'm not sure what datatype a time is
Let's say I want YYYY-MM-DD HH:MI:SS.

is this a null?

I read that somewhere

[queen rose]

What do you mean a null?

[lyric owl]

exactly

that's what I thought

I suppose I could store it as a blob and then try to convert it to datetime for python

[queen rose]

when you add the data your supposed to pass it as a parameter in conn.execute(query, params),
and you pass the date object in python, i.e: datetime.datetime(2012, 6, 25, 17, 32, 46)

dont pass the date as a string

[queen rose]

nah too much work

besides blob is for VERY LARGE text

like 50k+ characters

[lyric owl]

Oh I see

[thin valley]

hey can ask here for some help to get sql qurey with parametrs in oracle db ?

[queen rose]

Are you using SQLAlchemy by chance?

[river wyvern]

Im trying to wrap my head around the basics of databases and how they work.
When doing a query to a database, the result, is that sort of like a list of dicts?

Depending ofcourse what is grabbed.

[brave storm]

Yo, I'm currently using sqlalchemy to connect to my mysql database, i created a table wich worked pretty well and now I inserted something into it, but after refreshing the databse in DBeaver there was no entry made. But when the same programm process did 2 entries with the same primary key I got an duplicate Entry Exception, as if the Data was in the Database but looking in DBeaver it still isn't

My insert statement then looks like this INSERT INTO users (uid, username, fullname, premium) VALUES (:uid, :username, :fullname, :premium)

Already checked if permissions are granted

Ok, I fixed that - I don't know why it behaves this way, but I just had to add a COMMIT statement (db.execute(text('COMMIT;'))

[grim vault]

db.commit() should also do it. Sorry, didn't see you use sqlalchemy. That's using something else.

[brave storm]

thought that too, but didn't, thats why I'm wondering about it

[brave storm]

don't worry - thanks for your effort anyways 🙂

[waxen finch]

i believe the Session object has a commit() method too, you'd probably want to call that instead since thats part of their internal state management

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

oh wait, you're using the core so the actual equivalent would be Connection.commit()
https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection.commit

[torn sphinx]

+1

[lavish iris]

hello, im in need of much help. I've been trying to figure out how to fix this for like 2 hours. Let me explain, so im creating a Python/JSON database where you can enter your name, age and address then after that it will give you a custom ID number to you, (1 number after the previous given out) PLEASE PLEASE PLEASE HELP ME.

I cant send my code here cause it'll clog up the channel.

heres a screenshot of little specs you wont be able to read

[indigo citrus]

I made an TV Series/Shows/Sitcom AI Video mini search engine). You can find the name of the show (episode and season) and also links to stream it from. Now shorts only works the video input not yet
anyone wants to try? I tested it with ricky and morty shorts and family guy and its pretty good sometimes it does get results for other shows. But as long as transcript/dialogue is clear and not an edit it how be food 🙂 Try it out?
Where should i post the link? https://sulynajimsj-testseriesavid-main-obefxn.streamlit.app/
try this link? https://www.youtube.com/shorts/v3IS1ikLDJQ
for some reason it works really well for family guy and rick and morty haha

Demo

[final moat]

wassup

[torn sphinx]

Good

[frank cloak]

CREATE TABLE  SINCRONI_BLACKLIST(
   server_id BIGINT NOT NULL,
   user_id BIGINT NOT NULL,
   pub boolean DEFAULT false
   dev boolean DEFAULT FALSE
   private boolean default false
)

Like

CREATE TABLE  SINCRONI_GLOBAL_BLACKLIST(
   user_id BIGINT NOT NULL,
   pub boolean DEFAULT false
   dev boolean DEFAULT FALSE
   private boolean default false
)

Would this good for a chat_type blacklist and with the server_id being something for guilds?

to prevent the user talking ?

the global global design:

CREATE TABLE SICRONI_GLOBAL_CHAT(
   server_id BIGINT NOT NULL,
   channel_id BIGINT NOT NULL,
   webhook_url TEXT,
   chat_type SMALLINT DEFAULT 0 NOT NULL,
   UNIQUE (server_id, channel_id),
   PRIMARY KEY (server_id, chat_type)
)

class ChatType(enum.Enum):
    public    = 1
    developer = 2
    private   = 3

With this

and whitelisting user ids or channel ids

I use need to be able to blacklist guilds or channels

[whole torrent]

Hello I am making an Employee Scheduling Program. I need to store my Employee, School, Camp and Schedule Information to a database as I plan on making a web application with flask as my backend.

Here is my UML Class diagram. Where would you recommend I have a method for pushing each object info to the database?

Ping for replies please 🙂

[wise goblet]

if you are using static typing and wishing clean architectured solution:
I would suggest having Repository Abstraction being basis for database connections and some shared utility stuff
and from it through inheritance or composition, creating Repository objects for reach necessary... basically your class object
so EmplyeeManagerRepository
SchoolManagerRepository
and etc.

so... if i would have been trying to add it to your UML diagram, i would have made it 3D and added them right behind each class needing DB interactions 😆
or may be even simplifying and having those EmployeeManager class being those repositories

if i would have been rushing for quick solution in Django, i would have just used Python and did not bring any new classes at all (for anything)

So.... in the end we are having kind of even three choices how to go with it

[wise goblet]

umm, in the end i would be probably going for
We keep employeeManager abstraction, and input and output simple pydantic.BaseModel out of it
And under the hood in those methods, it is depending on something database connection if necessary, and through ORM of Django or SQLAlchemy directly creates and etc does CRUD actions No point to introduce new abstractions.

In more dirty silliest solution, we can be outputing, for example Django ORM objects instead of neutral pydantic BaseModels or dataclasses I would be very wishing not to do it though

in local dev env, we work through SQLALchemy or Django ORM, and CI in github actions for example is written to rerun tests in postgresql and confirm functionality of app on a normal database (if we plan to use it instead of sqlite. If we plan sqlite in prod, then it is not necessary)

[whole torrent]

Thats alot of things im gonna be honest, I just dont understand. I will be using MySQL as our database. Flask will be used as the framework for backend.

[wise goblet]

SQLalchemy it is then

[paper flower]

Are you making an api or are you rendering temlates on your backend with jinja? 🤔

[whole torrent]

api

[paper flower]

I would recommend to use fastapi instead of flask 🤔

[whole torrent]

Is it easy to learn?

[paper flower]

It's similar to flask

[whole torrent]

Mhm ill take a look then, ive only got 4 weeks to make it rip

[soft gorge]

I am creating a database to store my shopify orders to be accessed by my software to handle subscriptions. My store has multiple products and can be purchased together and will result in the order having the same subscription_id should I create a subscriptions table to store these subscription_ids with the email or will that be a waste of time?

CREATE TABLE IF NOT EXISTS orders(
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    email_address VARCHAR(64) NOT NULL,
    product_name VARCHAR(64) NOT NULL,
    product_sku BIGINT NOT NULL,
    purchase_date TIMESTAMP NOT NULL,
    subscriptin_id BIGINT DEFAULT NULL,
    FOREIGN KEY (email_address) REFERENCES customers(email_address),
    FOREIGN KEY (product_name) REFERENCES products(product_name),
    FOREIGN KEY (product_sku) REFERENCES products(product_sku)
);

ignore the VARCHAR(64) for the email I will change it soon

[frozen grotto]

Before you move on, I'd think about why you have a first_name and last_name column in your orders table, shouldn't those be in your customers table instead ?

[soft gorge]

So I do have them in the customers table already but it's my understanding that if i want to have a trigger that will automatically add the first and last name to the customers table I need to reference them using NEW.first_name and NEW.last_name Here is my trigger

  
CREATE OR REPLACE FUNCTION order_handler() RETURNS TRIGGER AS
$$ BEGIN 
    INSERT INTO customers(first_name, last_name, email_address) VALUES (NEW.first_name, NEW.last_name, NEW.email_address) ON CONFLICT DO NOTHING;
    INSERT INTO products(product_name, product_sku) VALUES (NEW.product_name, NEW.product_sku) ON CONFLICT DO NOTHING;
    RETURN NEW;
END $$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS order_handler_trigger ON public.orders;
CREATE TRIGGER order_handler_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE order_handler();

The data will have the first name and last name regardless so if there is a way for me to send it to customers table without having to save it I will do it

I could call different methods in my flask app to do .add_customer() and .add_product() instead but I think handling it with a trigger would be easier

[formal cosmos]

How does one create a factory for ORM-mapped tables in SQLAlchemy? I have a query that may be called multiple times per session, that depends on a temporary table. I need some way to make the name of the temporary table unique each time the query is invoked

So I put the definition of the table in an ORM-mapped table class (to be more readable), but the problem comes in when I try to dynamically subclass from it each time the query is invoked - in particular, I can't simply override __tablename__ in the subclass

[paper flower]

Is the parent class abstract? How do you override __tablename__?

[formal cosmos]

this is how I'm doing it right now

class TempTableContext:
    def __init__(self, model_cls):
        self._model_cls = model_cls

        class TempTableMixin:
            @declared_attr
            def __tablename__(cls):
                return f'{model_cls.__tablename__}_{id(self)}'

        self._temp_model_cls = type(f'Temp{model_cls.__name__}', (TempTableMixin, model_cls), {})

    def __enter__(self):
        self._temp_model_cls.__table__.create(bind=db.session.bind)

    def __exit__(self, exc_type, exc_value, exc_tb):
        self._temp_model_cls.__table__.drop(bind=db.session.bind)
        return exc_type is None

class MyTempTable(Model):
    __tablename__ = '#my_temp_table'
    __table_args__ = {'prefixes': ['TEMPORARY']}

Its usage would be something like:

with TempTableContext(MyTempTable) as ctx:
    # insert into ctx.temp_model_cls
    ...

    # perform some queries
    ...

[paper flower]

Is db.session static? 🤔

[formal cosmos]

yes, I'm using flask-sqlalchemy

[paper flower]

Shouldn't you subclass them in this order? (model_cls, TempTableMixin, )

[formal cosmos]

aren't mixins supposed to be earlier in the MRO?

[paper flower]

Also maybe you could modify ddl and actually make that table temporary? 🤔

[paper flower]

I don't actually remember

Hm yep, they should come earlier

Is model_cls abstract?

[formal cosmos]

that wouldn't solve the problem, since I can make multiple such queries within the same session (so I can create a new temporary table before the previous one is dropped)

[formal cosmos]

no, it subclasses from Model which is the ORM base class

[paper flower]

You would still be able to create multiple such tables, but I think using dedicated functionality for that would be "better"

Can you just override __tablename__ in new class namespace?

self._temp_model_cls = type(f'Temp{model_cls.__name__}', (model_cls, ), {"__tablename__": ...})

[formal cosmos]

I tried that too, the subclass ends up having the same __tablename__ as the parent class

[paper flower]

How can I reproduce that? 🤔

[formal cosmos]

I thought __table_args__ = {'prefixes': ['TEMPORARY']} was supposed to make it a temporary table in the DLL

[formal cosmos]

well, you can follow the template of this

I tried converting MyTempTable into a mixin but that didn't work either

i.e.

class MyTempTableMixin:
    @declared_attr
    def __tablename__(cls):
        return '#my_temp_table'

    @declared_attr
    def __table_args__(cls):
        return {'prefixes': ['TEMPORARY']}

class TempTableContext:
    def __init__(self, mixin_cls):
        self.mixin_cls = mixin_cls

        class TempTableMixin:
            @declared_attr
            def __tablename__(cls):
                return f'{mixin_cls.__tablename__}_{id(self)}'

        self.temp_model_cls = type(f'Temp{mixin_cls.__name__}', (TempTableMixin, mixin_cls, Model), {})

    def __enter__(self):
        self.temp_model_cls.__table__.create(bind=db.session.bind)

    def __exit__(self, exc_type, exc_value, exc_tb):
        self.temp_model_cls.__table__.drop(bind=db.session.bind)
        return exc_type is None

hmm, actually now it seems that it managed to subclass properly

but when I try to insert entries into the table, I get an error saying that the table doesn't exist

[paper flower]

How do you define your mixin_cls?

from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql.ddl import CreateTable

from db import Base


class Book(Base):
    __abstract__ = True

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]


temp_model_cls = type(
    f'Temp{Book.__name__}',
    (Book,),
    {
        "__tablename__": "temp_book",
    },
)
print(temp_model_cls.__tablename__)
print(CreateTable(temp_model_cls.__table__))

CREATE TABLE temp_book (
    id INTEGER NOT NULL, 
    title VARCHAR NOT NULL, 
    CONSTRAINT pk_temp_book PRIMARY KEY (id)
)

[formal cosmos]

As MyTempTableMixin except with actual columns

I'll take a look into the actual emitted SQL later today (I have class) and see if I can spot the issue

[marsh jewel]

Does anyone here know how to convert excel to xml?

[formal cosmos]

ok this is weird lol

So I'm printing out the status of the table before and after the query that resulted in the error...

class AncestorsContext(TempTableContext):
    def __enter__(self) -> Self:
        ctx = super().__enter__()
        print('-' * 20)
        print(ctx.temp_model_cls.__tablename__, ctx.temp_model_cls.__table__.exists(bind=db.session.bind))
        try:
            assert issubclass(ctx.temp_model_cls, _HashToDistanceMixin)
            ctx.temp_model_cls._batch_add(self.commit.find_ancestors_with_distance())
        except:
            import sys
            print('fail', *sys.exc_info())
            raise
        else:
            print('success')
        finally:
            print('-' * 20)
        return ctx

Output:

--------------------
hash_to_distance_140012520883680 True
fail <class 'sqlalchemy.exc.ProgrammingError'> (psycopg2.errors.UndefinedTable) relation "hash_to_distance_140012520883680" does not exist
LINE 1: INSERT INTO hash_to_distance_140012520883680 (last_edit_at, ...
                    ^

[SQL: INSERT INTO hash_to_distance_140012520883680 (last_edit_at, hash, distance) VALUES (%(last_edit_at)s, %(hash)s, %(distance)s)]
[parameters: {'last_edit_at': datetime.datetime(2023, 2, 20, 9, 7, 2, 419652, tzinfo=datetime.timezone.utc), 'hash': UUID('674c38ac-c0ba-481e-84b9-ea8174c88b02'), 'distance': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405) <traceback object at 0x7f5734890dc0>
--------------------
--------------------
hash_to_distance_140012517597296 True
success
--------------------
--------------------
hash_to_distance_140012517483760 True
fail <class 'sqlalchemy.exc.ProgrammingError'> (psycopg2.errors.UndefinedTable) relation "hash_to_distance_140012517483760" does not exist
LINE 1: INSERT INTO hash_to_distance_140012517483760 (last_edit_at, ...
                    ^

[SQL: INSERT INTO hash_to_distance_140012517483760 (last_edit_at, hash, distance) VALUES (%(last_edit_at)s, %(hash)s, %(distance)s)]
[parameters: {'last_edit_at': datetime.datetime(2023, 2, 20, 9, 7, 4, 453335, tzinfo=datetime.timezone.utc), 'hash': UUID('3688a100-377a-4318-aaf3-e8c8296b6647'), 'distance': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405) <traceback object at 0x7f57345d5300>
--------------------

seems that even if the table "exists", executing the query against the table fails

@paper flower

[paper flower]

Is it in correct schema?

[formal cosmos]

the table creation succeeded, so yes?

[paper flower]

Alse do you have to commit when creating a temporary table? 🤔

What ddl does it produce?

[formal cosmos]

let me echo the SQL output in the __enter__ function

also I forgot to call __exit__ in case the batch_add fails

that's probably why the next test succeeded

[sick perch]

when business logic is a bit involved with the orm, but the orm can do unit of work + transactions, it tends to be surprisingly easy to have reasonably fast integration tests on top of a transaction that will roll back after a test and/or on top of in memory sqlite

[paper flower]

In a lot of cases you need other features of your db 🤔

E.g. geospatial data, some specific types

[formal cosmos]

the new code for __enter__ is

        def __enter__(self) -> Self:
            db.session.bind.engine.echo = True
            ctx = super().__enter__()
            print('-' * 20)
            print(ctx.temp_model_cls.__tablename__, ctx.temp_model_cls.__table__.exists(bind=db.session.bind))
            try:
                assert issubclass(ctx.temp_model_cls, _HashToDistanceMixin)
                ctx.temp_model_cls._batch_add(self.commit.find_ancestors_with_distance())
            except:
                import sys
                print('fail', *sys.exc_info())
                super().__exit__(*sys.exc_info())
                raise
            else:
                print('success')
            finally:
                print('-' * 20)
                db.session.bind.engine.echo = False
            return ctx

[paper flower]

🤔 Since it commits won't it be deleted?

When temporary table gets deleted?

[formal cosmos]

oh

that must be why, didn't realize SQLalchemy autocommits when a temp table is made.........

wouldn't that like defeat the whole point

[paper flower]

https://www.postgresql.org/docs/current/sql-createtable.html

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below).

[formal cosmos]

I set autocommit to false btw

so not sure why it even does that

[paper flower]

DDL statements have to commit iirc

[formal cosmos]

is there a way to avoid that?

[paper flower]

By default it doesn't do anything on commit

Are you sure the same connection is used for the lifetime of flask's request?

Maybe pass your session explicitly into context manager?

with TemporaryTable(session, TempTable) as temp_table:
    ...

[sick perch]

which is why there is a and/or, at the same time, db feature needs strongly depend on the field - i haven't needed geodata in the last 20 years, i always needed json tho, thankfully pq is intensely powerful

[formal cosmos]

I printed the value of session.autocommit, it is set to false

[paper flower]

Commit shouldn't matter

It doesn't do anything by default

[formal cosmos]

I also didn't set any flags for the temp table to drop on commit or anything

literally just

    @declared_attr
    def __table_args__(cls):
        return {'prefixes': ['TEMPORARY']}

[sick perch]

wrt commit on ddl, i recall that python dbapi will autocommit on ddl (at least its an intense issue with sqlite)

[paper flower]

@sick perch The best part is that you don't need a lot of code to actually create transactional tests and run db migrations in your tests: https://gitlab.com/ThirVondukr/sqlalchemy-pytest/-/blob/main/sqlalchemy_pytest/database.py

[formal cosmos]

I printed the session before the table create and batch_add query, it's the same object

[paper flower]

The issue is still the same - you're not able to query your table? 🤔

[formal cosmos]

yeah, just outright says it doesn't exist

[paper flower]

Can you copy your TempTable context manager here?

Will test it on my project

[formal cosmos]

oh, and btw

I also tested it on SQLite

and it runs without problem

so it's a postgres thing

[paper flower]

https://tenor.com/view/cat-lick-conk-conky-kitty-gif-18021368

[formal cosmos]

sure

!pastebin

[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 floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[formal cosmos]

sorry for the wait, here it is
https://paste.pythondiscord.com/zanozayebe

[paper flower]

https://paste.pythondiscord.com/oqiyecolaj

INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] 
CREATE temporary TABLE "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed" (
        id SERIAL NOT NULL,
        title VARCHAR NOT NULL,
        CONSTRAINT "pk_dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed" PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00066s] ()
INFO  [sqlalchemy.engine.Engine] INSERT INTO "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed" (title) VALUES ($1::VARCHAR) RETURNING "dcaf2c22-1a2a-4ba
4-9edc-837fa10bdaed".id
INFO  [sqlalchemy.engine.Engine] [generated in 0.00054s] ('Temporary Book!',)
INFO  [sqlalchemy.engine.Engine] SELECT "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed".id, "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed".title
FROM "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed"
INFO  [sqlalchemy.engine.Engine] [generated in 0.00045s] ()
INFO  [sqlalchemy.engine.Engine]
DROP TABLE "dcaf2c22-1a2a-4ba4-9edc-837fa10bdaed"
INFO  [sqlalchemy.engine.Engine] [no key 0.00036s] ()
.INFO  [sqlalchemy.engine.Engine] ROLLBACK

🤔

Perhaps it closes the connection/uses a different one?

Or clears it's state

[formal cosmos]

hmm, your version is also missing the commit statement after the table is created

[paper flower]

Yep ...

Here's test engine setup:

@pytest.fixture(scope="session")
def sqlalchemy_pytest_engine(sqlalchemy_pytest_database_url: str) -> AsyncEngine:
    return create_async_engine(sqlalchemy_pytest_database_url)

And actual engine and session (engine isn't used during tests):

engine = create_async_engine(
    _settings.url,
    pool_size=20,
    pool_pre_ping=True,
    pool_use_lifo=True,
    echo=_settings.echo,
)
async_session_factory = async_sessionmaker(
    bind=engine,
    expire_on_commit=False,
)

[formal cosmos]

flask-sqlalchemy uses a scoped_session

would that be why?

[paper flower]

I don't know 🤷

I'm not a fan of flask-sqlalchemy to be honest

Didn't use it, but it's not hard to integrate sqlalchemy into any app

And people seem to have weird issues with it

[sick perch]

scoped session can be trouble, it makes "concurrent" safe at the expense of control, i suspect the exact control needed for the test transaction is whats stopping the details

[formal cosmos]

also, apparently when I initialize the table as a regular table in this way, the table actually exists, but attempting to drop the table before the commit is done will infinitely block the process

[formal cosmos]

oh

I fixed it

I had to execute the DDL statement directly instead of calling table.create()

so instead of

self.temp_model_cls.__table__.create(bind=db.session.bind)

I have to run

db.session.execute(CreateTable(self.temp_model_cls.__table__, bind=db.session.bind))

this is weird, but it works, so I'm not going to complain xD

[paper flower]

🤔

Can you try to execute it on connection instance too?

[formal cosmos]

you mean db.session.connection.execute instead of db.session.execute?

nvm

[ornate willow]

hi, can someone help me?

[paper flower]

Yep 🤔

[formal cosmos]

so

with db.session.bind.engine.connect() as conn:
    conn.execute(CreateTable(self.temp_model_cls.__table__, bind=db.session.bind))

it fails

[paper flower]

No, session should have a connection method or property

Maybe not in this version of sqlalchemy though...

[formal cosmos]

with db.session.connection() as conn:
    conn.execute(CreateTable(self.temp_model_cls.__table__, bind=db.session.bind))

I guess you mean that?

[paper flower]

self.temp_model_cls.__table__.create(db.session.connection)

[formal cosmos]

hmm I'm using SQLAlchemy 1.4, doesn't seem like that exists

connection() is a method, not a property

[formal cosmos]

this one doesn't work, when I drop table it says the connection is closed already (it did manage the create the table though)

[paper flower]

call it 😃

https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.Session.connection

[formal cosmos]

it works

I guess I just have to pass in the connection instead of the engine

Thanks for the help!

[paper flower]

Yep, that would make more sense to use current connection instead of executing it using an engine 👀

Not sure which connection it would use in this case

[torn sphinx]

At what point should I move away from using a JSON file with CRUD functions and turn to a database solution? I only have like 20 small entries to manage max.

[wise goblet]

i have a pet project, which has pretty much very small amount of entries too(like 20-50 entries probably). Yet i put it into Sqlite3

which is simple solution, being a file in filesystem

yet way more superior than json

[wise goblet]

you need to choose Sqlite3 or something more from the point, when u understand your application will have multiple users with different saved settings
and your application will be migrated between versions with changed data structure

SQL makes very easy to enforce... structure that will be migrated

first version of my application used JSON. i got kind of lost eventually with trying to put all the new features into this data structure. SQL is way more superrior, even if it is very lightweight minimalistic sqlite3

[torn sphinx]

Hi,

Does anyone understand why I keep getting more records inserted into my SQLITE database?

import sqlite3

conn =  sqlite3.connect('employee.db')
c = conn.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS employees (
    first text,
    last text,
    pay integer
)""")

c.execute("INSERT OR IGNORE INTO employees VALUES ('Corey', 'Schafer', 50000)")
c.execute("SELECT * FROM employees WHERE last='Schafer'")
print(c.fetchall())
conn.commit()
conn.close()

[storm mauve]

add a primary key or unique constraint if you want to avoid duplicated rows

[torn sphinx]

I added a unique constraint (according to the docs PK's should be avoided unless really necessary and you know what you're doing??)

Amazing, it works!!

[agile hornet]

great

[waxen finch]

i dont recall their docs saying primary keys should be avoided, maybe you were looking at something like AUTOINCREMENT which sqlite does advise against?
https://sqlite.org/autoinc.html

pks are like, pretty important in designing a schema, though in reality sqlite also uses a unique index to implement them anyway (with the exception of WITHOUT ROWID tables)

[torn sphinx]

dm me if u can help me

[weak depot]

Anyone used sqlalchemy on unix server with jdbc ?

[paper flower]

I didn't use it with jdbc, but I use sqlalchemy a lot, what's the problem?

[drowsy geyser]

from:

pg_dump: (PostgreSQL) 14.4 (Debian 14.4-1+b1)
pg_dumpall: (PostgreSQL) 14.4 (Debian 14.4-1+b1)
pg_restore: (PostgreSQL) 14.4 (Debian 14.4-1+b1)
psql: (PostgreSQL) 15.2 (Debian 15.2-1)

to:

pg_dump: (PostgreSQL) 15.2 (Debian 15.2-1+b1)
pg_dumpall: (PostgreSQL) 15.2 (Debian 15.2-1+b1)
pg_restore: (PostgreSQL) 15.2 (Debian 15.2-1+b1)
psql: (PostgreSQL) 15.2 (Debian 15.2-1)```

[mint mauve]

I want to create a model right after user signs up and its not doing it, please help

            db.session.add(new_user)
            db.session.commit()

            notification = Notification(to=new_user.id, action="Sign Up", message="Welcome to CarSpace")

            db.session.add(notification)
            db.session.commit()

[frank cloak]

CREATE TABLE  SINCRONI_BLACKLIST(
   server_id BIGINT NOT NULL,
   user_id BIGINT NOT NULL,
   pub boolean DEFAULT false
   dev boolean DEFAULT FALSE
   private boolean default false
)

Like

CREATE TABLE  SINCRONI_GLOBAL_BLACKLIST(
   user_id BIGINT NOT NULL,
   pub boolean DEFAULT false
   dev boolean DEFAULT FALSE
   private boolean default false
)

Would this good for a chat_type blacklist and with the server_id being something for guilds?

class ChatType(enum.Enum):
    public    = 1
    developer = 2
    private   = 3

I am also looking to whitelist guilds and users

And blacklist guilds

[weak depot]

@paper flower While trying to connect to DB i am getting error: Class com.microsoft.sqlserver.jdbc.SQLServerDriver is not found

[brittle pendant]

Hi

@Helpers

[paper bluff]

hi, please don't ping roles for help. you can use #1035199133436354600 to ask questions

[brittle pendant]

ok

[torn sphinx]

Can someone explain to me whether this is the correct way of using sqlite?

# Create a connection in database_module.py
conn =  sqlite3.connect(DB_FILE_PATH)
c = conn.cursor()

# Have some functions that you import into modules to use that connection
# imported by some_module.py
def create_user_config_table(conn):
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS user_config (
        name text UNIQUE,
        value text,
    )""")

# Close the connection at the end of your script, import to root file and add an atexit?
def close_database(conn):
    conn.commit()
    conn.close()

What if I import conn and c twice??

[waxen finch]

on first import, the module object is cached in sys.modules so any subsequent imports will return the same module rather than running it again (in other words you still get the same connection back)

globally defining your connection in some module has the same caveats as any other global variable you define, but if your program is limited in scope you can probably get away with it

[torn sphinx]

even if i then subsequently import it in some_other_submodule.py? and some_really_different_module.py?

[waxen finch]

import it all you want: py import some_module import some_module import some_module import some_module if you ain't screwing with sys.modules, you'll never run the same code more than once

[torn sphinx]

amazing

[waxen finch]

but personally i would manually pass the connection around as function parameters or encapsulate it in a class

[torn sphinx]

"globally defining your connection in some module has the same caveats as any other global variable you define" -> is there a better / more proper way?

[torn sphinx]

isn;t that what I'm doing now? passing the conn object as a function param

[waxen finch]

but how are you retrieving the connection object in the other modules to give them to your function?

[torn sphinx]

just importing it from a database.py module

[waxen finch]

exactly, you're referencing a global variable

[torn sphinx]

how else can I make sure the same conn is used across modules?

[waxen finch]

create the connection in your main script, give it around to everything that needs it ```py

my_other_module.py

def initialize_schema(conn):
conn.executescript(...)

main.py

import my_other_module

def main():
conn = sqlite3.connect(...)
my_other_module.initialize_schema(conn)

if name == "main":
main()```

the above also makes it easy to know when to close your connection cause your program is structured to end when the main() function ends

[torn sphinx]

that is exactly why i wanted to use sqlite, to stop passing in some JSON loaded dict object into every single module and submodule, I think I'll just stick to the global variable

[waxen finch]

you could just as easily have a globally loaded JSON file, as the specific design problem here is related to global state
see this article which explains the various problems/advantages of global variables here: https://wiki.c2.com/?GlobalVariablesAreBad
(per my initial point, if your program isnt going to get too complicated then it can be good enough, i just dont see it being that difficult to write it in a cleaner approach well, implying that its "easy" is probably a wrong outlook, but i mean that your code's flexibility overall can benefit from a more controlled data flow)

[torn sphinx]

thank you

ill try to make it happen

[chrome temple]

anyone proper experience with connecting a discord bot that's on a vps to postgresql?

currently switching from aiosqlite to postgresql because preformance is better i heard

[waxen finch]

i believe there are a lot of factors that can go into performance, such as the amount of data you're storing, the kinds of queries you're making, how many connections are being made, etc.
you should look over sqlite's appropriate uses https://sqlite.org/whentouse.html to see how it describes itself, and switch if you think it doesnt meet your criteria

[chrome temple]

i know, but it's for long term aswell. so i'd rather have it with psql then aiosqlite

hence why im making the switch

[waxen finch]

fair enough, as for your question i dont know if there are any quirks you should know besides the parameters to asyncpg.connect()

[chrome temple]

yeah i've got no idea, idk if i have to make the connection to the vps or not, if not then it wont be a problem, but if so ive got no idea where to start with it properly

and also wondering if it's also good to use asyncpg then or not

because it's a database interface library designed specifically for PostgreSQL and Python/asyncio

[waxen finch]

presumably the discord bot library you're using is built on asyncio, so you are basically forced into using a library that can interface with postgresql asynchronously

if you chose a sync library like psycopg, you'd end up blocking your bot's event loop which can cause its own problems

[chrome temple]

im using discord.py but since asyncpg is an efficient, clean implementation of postgresql server binary protocol for use with python’s asyncio framework

idk wether it'd be better to use asyncpg or not lol

but let's first work on connecting it to the vps (if needed)

[waxen finch]

i say decide on the library first, then look into the parameters you need to connect with that library like the host address, port, user and password, etc.
https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.connect

that is unless you havent set up the pg database, in which case figure that out first

[paper flower]

I believe psycopg3 now supports asyncio

[icy oyster]

which database should I use? definitely not sqlite right? 😄 I say this because my idea is to work with lots of both sensitive and non sensitive data
there are lots of options (MariaDB, MySQL, MongoDB, PostgreSQL, etc) but I don't know which one to choose

seems like I need a relational database, like postgre or mysql, right?

[wicked pumice]

Why not? SQLite is very powerful, you will have plenty of time to think about it before SQLite shows some weakness.

[icy oyster]

idk to be honest, I just dont know businesses that use SQLite so I supposed maybe MySQL or PostgreSQL would be better, just a thought.

[wicked pumice]

Depends of your needs honestly.

PostgreSQL is definitely the best SGBD in the world.

But the main advantages to take PostgreSQL over SQLite are tooling and infrastructure.

[icy oyster]

what wont I find in SQLite that I will in Postgre? 🤔

[wicked pumice]

Pooling, parallelism, tweaking, mirroring, backuping, etc...

[icy oyster]

just trying to understand, I'm not into databases at all I, I have a really basic understanding but I'd like to pick the right database for what I'm building 🙂

[wicked pumice]

I suggest you to read some documentation.

And evaluate your needs accordingly.

[icy oyster]

fair

okay

[wicked pumice]

SQLite is a nice not-so-little database.

[icy oyster]

I like sqlite tbh but I havent worked with other db's so its not like I've a lot of experience 🤣

sqlite doesnt have parallelism?

[wicked pumice]

Not in the way of SGBDs with server side process.

SQLite is built to be light, without long pooling process and all the advantages/drawbacks which come with it.

[icy oyster]

Ok I think I start to understand the differences. You gave me a good overview, now I understand a bit more. I'll read docs as you recommended and make a final decision 🙂

[wicked pumice]

Sure, go ahead! I hope you will find what you need.

[icy oyster]

Thanks a lot

[ebon skiff]

DO
$$
DECLARE
    ele text;
BEGIN
    FOR ele IN SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_name != 'alembic_version'
    LOOP
    raise notice 'Deleting table data %', ele;
    DELETE FROM ele;
    END LOOP;
END;
$$;
``` for some reason the ele var isn't picked up as a valid table inside DELETE FROM.

NOTICE:  Deleting table data checkpoint

ERROR:  relation "ele" does not exist
LINE 1: DELETE FROM ele
                    ^
QUERY:  DELETE FROM ele
CONTEXT:  PL/pgSQL function inline_code_block line 10 at SQL statement
SQL state: 42P01

[high nest]

Hello, i have table where is 5000+ record, but i got excel list with 2400 record, then i copy all id and selected in DB but i get 2370 record. i couldn't write query which gets me these 30 record from 2400. do you have a idea? select id from test where id not in (select id from test where id in(1,2...2400)) doesn't work

[bright sigil]

Would a local sqlite3 database be enough for a library book record system
That logs which books we're borrowed and who borrowed
And track if it was already returned

[ebon skiff]

Fixed it ```sql
SELECT * FROM "user";

DO $$
DECLARE ele text;
BEGIN
FOR ele IN
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name != 'alembic_version'
LOOP
raise notice 'Deleting table data %', ele;
EXECUTE FORMAT('DELETE FROM %I', ele);
END LOOP;
END $$;

[wise goblet]

if it is small application, SQLite is actually very nice option 😛
otherwise, choose PostgreSQL for fully fledged DB, it is way more superrior than MariaDB/MySQL (and nicer to use in general)

[icy oyster]

itll start small, but it could go huge 😄

[icy ruin]

Hey guys, I have a Django model called UserActions and another called let's say x. That have a ManyToManyField with each other. It's called actions in model x. I know I can do x.actions.set(new_user_action) but I believe that will override the "old" user actions. Is there a way to "append" them?

[paper flower]

I believe there's an .add method

[chrome temple]

To be honest, try with postgresql then. since the rewriting of it is a pain in the ass. i’m doing it right now and it’s not going like i want.

[icy oyster]

just checked maria db

seems like the best option 🤔 but I'll go throug postgre again

[prime cypress]

how do I take a snapshot of a database in sqlite3?

[chrome temple]

psql is a bit more complicated to setup, especially with asyncpg (im trying to rewrite my stuff from aiosqlite to psql) and ive been stuck on it for 2 says. theres still nobody who can help me

[coral sparrow]

postgres is intalled using apt-get but connections are coming from kubectl. what to whitelist in pg_hba for this?

[torn sphinx]

hey, I ve had this problem, where I get a response that the task was completed but when I look in the database nothing was stored, can anyone tell me what might have gone wrong please?

import discord
from discord import app_commands
from discord.ext import commands
import mysql.connector
from typing import Literal
from utils.functions import *
from utils.constants import *
from utils.embeds import *


    # Connect to the database
mydb = mysql.connector.connect(
            host="127.0.0.1",
            user="root",
            password="DBmsau271312",
            database="one_piece"
        )
cursor = mydb.cursor()



class Drops(commands.Cog):
    def __init__(self, bot: commands.Bot)-> None:
        self.bot=bot

    @app_commands.command(name="drop_shards", description="drop shards")
    @app_commands.describe(amount="the amount you want to drop", element="Which element do you want shards from?")
    async def drop_shards(self, interaction: discord.Interaction, amount: int, element: Literal['Fire', 'Water', 'Dark', 'Earth', 'Light', 'Lightning','Grass', 'Neutral']):
        if amount < 0:
            await interaction.response.send_message("Mehh provide a number over 0 smh")
            return
        else:
            element = element.lower()
            cursor.execute(f"UPDATE {element}_shards SET amount = amount +%s WHERE player_id =%s", (amount, interaction.user.id))
            mydb.commit()
            await interaction.response.send_message(f"Successfully added {amount} {element} Shards to your profile")



async def setup(bot: commands.Bot)-> None:
    await bot.add_cog(
        Drops(bot),
        guilds=[discord.Object(id=1052225383065456752)]
    )

[wild pelican]

I'm using mysql-connector and I just have a question.

I initialize the DB connection by doing this once in my main file and storing it as a bot variable: ```py
mydb = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)

client = RecordsBot()
client.remove_command('help')
client.db = mydbHowever, this seems to not update any external data that is inserted after I initialize the connection... For example, if I `fetchall/fetchone` using something likepy
sql = "SELECT * FROM Bans WHERE uuid = %s"
mycursor.execute(sql, (s,))```

How would I fix this issue with it not updating... if that makes sense?

[sonic aurora]

Where are you updating? Also , have you committed after updating?

[wild pelican]

I'm updating on a Minecraft plugin and it's committing there

[sonic aurora]

Can you share some more code using a link please? I am not getting enough context with what you have shared

[wild pelican]

The code is really unorganized, but https://paste.pythondiscord.com/cagiwuyiri but line 147 is where I select it... if I insert something into the DB from Minecraft, I can view that inserted data on my PHPMyAdmin, but the SQL on line 147 doesn't view it for some reason.

[grim vault]

First: you should reset your password and not include it in the future.
An UPDATE only works if there is already a row which matches the WHERE clause. So if there is no entry for the player the UPDATE will do nothing.

[torn sphinx]

thank you very much, and sorry for including the password 😅 .

[sonic aurora]

You are fetching it but where are you printing it?

[wild pelican]

I figured it out... I needed mydb.commit() at the top of the command... no idea why, but it works :P

Thank you for your help!

[ebon skiff]

Hi! Any way to do something like this? -> If table does not exists, don't insert data.

Or is it better to just make and if statement in Python? Since it is executemany and I don't want it to check x*n

[sonic aurora]

What dialect of SQL are you using?

[ebon skiff]

postgre

[sonic aurora]

Here you go

https://stackoverflow.com/questions/1874113/checking-if-a-postgresql-table-exists-under-python-and-probably-psycopg2

Most probably you are using psycopg2

[ebon skiff]

asyncpg

But doesn't really matter since I just want a query and need to know it doesn't run x*n

[sonic aurora]

Just thought of a hacky solution as well. If there is atleast one row with the said table, the table exists. So you could just try to do a 'SELECT' with 'LIMIT 1' in which case it def not gonna go over n rows. You can wrap the whole inside a try-except clause.

[ebon skiff]

Yeah but then I'd still need to make a call each time, right now I just fetch all the table names that are public and running python if statements.

    tables = [x["table_name"] for x in (await conn.fetch("""
        SELECT table_name 
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_name != 'alembic_version'
    """))]

[sonic aurora]

I see, then the link above should help you

[tulip garden]

Who does create a Model on the fly and why?

[craggy swallow]

What's better, Yugabyte or Cockroach? I'm sure "it depends", but one can literally answer every question with that, not very informative.
If anyone could share their personal experience and opinions, that would be very helpful.
Sorry for the tag @wise goblet and @brazen charm , I did a quick search, and it seems you two might know something on the matter. Feel free to ignore If ur busy, sry.

I've also been looking into Neon DB, which "separates storage and compute and substitutes the PostgreSQL storage layer by redistributing data across a cluster of nodes", allows to create a serverless Postgres instance and supports Postgres syntax 100%. Any thoughts?

What I actually need this for rn is just a relatively small pet project, and I can't host Postgres on my machine. But in the future, I'll likely be making some big projects, and may *or may not" end up hosting on my own machine. I would like to choose one and just stick to it, so even tho my requirements rn aren't very big, I want to make the best choice for the future.

If one does choose to host on bare metal (their own machine), would you recommend just sticking to pure Postgres, or one of these, or something else?

Any and all info would be very helpful, thanks in advance!

[mint mauve]

nvm

[tulip garden]

NVM?

[lost iron]

someone knows how to use a database date entry to trigger an event at this date ?

for me the looping mechanism sounds not good

example: i want to kick someone after cancel date automatically and the date is written in the db

[tulip garden]

Well, what I can think of is long pooling or websockects.

Or some sort of cron jobs.

You can have cron jobs for that, I guess. However, I'm not sure.

[lost iron]

i like it, thank you

[brazen charm]

Just stick with postgres, for 99% of use cases thats all you need, there's a reason why Postgres is pretty much the most popular SQL db around.

[coral sparrow]

this

unless you need something for very very very specific purpose postgres is way to go

[craggy swallow]

Is there any way to host a Postgres server for free somewhere? That's what started my search in the first place. Cus I can't host on my machine atm, and didn't want to pay for GCP or AWS or anything..

[coral sparrow]

aws has free tier

[craggy swallow]

:0 didn't know that, didn't even check lol. Time to google

And separate question. What's the best (fastest) DB for time series?

[coral sparrow]

timescaledb postgres extension

if you want to go with completely other database then clickhouse is good alternative

[craggy swallow]

Amazon RDS is only free for 12 months, but ig I can just make a new account every month and migrate.. 🤔 or do they ban for that?

[brazen charm]

Clickhouse

but you probably dont need that much performance

[ebon skiff]

Do you handle data inside a migration or add data to it afterwards? Seems to me like migrations are just for the structure right?

[craggy swallow]

If I ever decide to make a high frequency trading bot I'll need all the performance I can get)

[brazen charm]

If you're making a HFT bot though you wouldnt be using a DB

you'd have most if not all of it in memory

[icy oyster]

you cant win to institutions that invested millions into first finding a profitable algorithm then developing it.

dont lose your time there please 😄

[obsidian owl]

Is there a Postgres library that does not require writing SQL commands? I'm currently using psycopg2. Just wanted to know if there is something friendlier to use

[brazen charm]

SQLAlchemy

[wise goblet]

DjangoORM 🙂 even database migrations are handled out of the box

[obsidian owl]

Thanks! Will check them out

[craggy swallow]

People somehow trade by hand and end up profitable.. 😅

[icy oyster]

who ends up profitable? only the exchange hahah

[mint mauve]

I meant to do this:

            db.session.add(new_user)
            db.session.flush()

            notification = Notification(to=new_user.id, action="Sign Up", message="Welcome to CarSpace")

            db.session.add(notification)
            db.session.commit()

I fixed it my self

[tulip garden]

Good

[chrome temple]

Anyone in here who can help me proper setup my pool connection for postgresql & asyncpg using a setup_hook?

[errant ruin]

k

[cloud badger]

Hey everyone, newb, just saying Hi and I'm sure soon I will be needing help. Just started learning python 3 months ago, and heard this is the place. Bayarian here 👋

[torn sphinx]

could someone help me think of a good question for this

Think of an area that interests you: sports, arts, entertainment, international relief efforts, government, careers, or the weather.

Think of a question you would like to answer. Is it better to wait until the battery is low before you recharge your smartphone? How does a streaming service like Netflix or Hulu decide which shows to offer?

Look for a website where someone already collected a large quantity of data to answer that question. While commercial big data sets may have tens of thousands of values, for this project, look for a data set with at least 100 values.

You are not going to write a program to analyze these data because you have not yet covered how to read data files. You can describe how the researcher(s) who collected the data used them to answer your question.

[fleet gull]

I have records in sql lite database: metal-id, date, price; is it possible to select all metal id in single row. like: date. metalid - 1. price, metalid-2, price

or sql lite is too weak for that

[wise goblet]

Failed to comprehend request. Specify better

[prime cypress]

SELECT DISTINCT Date_Added FROM Statement WHERE 1=1 AND Date_Added >= '2022-01-03 00:00:00' AND Date_Added < '2022-01-20 00:00:00' ORDER BY Date_Added ASC

I dont know why, but when I run this command it does not include '2022-01-03 00:00:00' but include '2022-01-20 00:00:00'

can anyone explain what I am doing wrong

[real laurel]

Hello people, can I get a good recommendation for a database I can use for a discord bot base for a game. I was trying SQLite, but is for light db. Thank You.

[slender atlas]

If metal-id is a column, you can select the entire column, but it is a column, not a row

[slender atlas]

What's wrong with SQLite?

[real laurel]

is to light for my database

[slender atlas]

What do you mean by light

[real laurel]

hmmm I'm trying to do a simulation game, but is to heavy for it.

[slender atlas]

The light part about it is that you don't need a database server and can use a local database file

[real laurel]

So, I can still used ??

It's just the amount of database I would need it will be heavier for my database.

[slender atlas]

I don't see a problem with having one database for a game

[real laurel]

Oh man, I hope your right. Cause I'm so familiar with SQLite. I loved.

[slender atlas]

It's going to be as heavy as the database file you're going to be working with

[real laurel]

well users will have a balance in currency, employees, storage and ranks. '

oh and contracts.

[slender atlas]

Ok

It can handle that

[real laurel]

thanks man! are you a teacher or something. How I can trust your resources ?

[slender atlas]

I worked with SQLite databases in my projects

[real laurel]

ah, fine enough. One more question. How many users it can handle?

[slender atlas]

I haven't tested the limits of it

[real laurel]

hmm, would that's a problem I would have to solve in the future and I will put a ping on it to have the solution in hand. Thanks mate.

[slender atlas]

Pretty sure it can handle millions if not more

[real laurel]

dang, I just need thousands, but millions is something more secure.

[keen minnow]

The main point is whether you need multiple servers connecting to the same DB or not

[real laurel]

aaahhh, the water is getting more clear. Thank You'll. I was asking ChatGPT this questions, but its answers kind of suck.

[wise goblet]

And having workload requiring multiple writers in.
SQLite3 has WAL of course, but it is still one thread of scheduled writers.
Postgresql allows automatically multiple writers

[real laurel]

MySQL can't do that?

[keen minnow]

like https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_write_io_threads ?

You may also want to read https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html

[wise goblet]

It can, but choose Postgresql if u want fully fledged dB. It is more superior than mysql/MariaDB in almost every way

[keen minnow]

for some opinion

[real laurel]

I was debating between MySQL and Postgresql earlier. I wanted to use SQLite so bad, but I was worrying about it since I know it works with small mechanism. structure .

Well Thank You'll For your time all this information helps.

[wise goblet]

How many users u a going to have? If it is pet project, may be sqlite indeed all u need

[real laurel]

as many they join. It could be thousands if they like the game.

[keen minnow]

The typical way to go about it is to run some load test: how many users to do you expect to join? And in what load does that translate for your app and DB

In addition, what happens if your server crashes?

so what level of resiliency do you want to provide

[real laurel]

I was going to use replit. and its a discord bot. I'm experimenting here before I take the step to go further. So, If the game gains popularity I can say it will get around 2 thousand users, but in reality idk. Since I will be promoting it.

Now, it can go the other way around, but my main goal is to experiment and take the risk of actually doing something that I would like to live of.

[keen minnow]

I wouldn't live off something on replit.
That will probably require you to transition to a real cloud provider like GCP or AWS

[real laurel]

Yeah I know its just for this first small project. I tested tho. I have a bot running and I used daily. and it hasn't crash. It has already 2 months.

or more I believe

[narrow saffron]

Inserting "NULL" into sqlite sets the value to string "NULL" rather than null type, how do i do this?

[wise goblet]

Or using Hetzner/Digital Ocean for more beginner friendly choices (with even cheaper prices for regular Linux machine at Hetzner)

[keen minnow]

for your first iteration(s), I would suggest to not worry about performance. Get popular first and that will be a great problem to have!

[real laurel]

then what would you suggest?

[slender atlas]

Try inserting None

[wise goblet]

😁 @real laurel no point to make super scalable Microservice architecture with Kafka, Cassandra and etc for something so early.
It is pain in the ass to finish overcomplicated projects.

If u want to make a more complex start, better be zealot in code quality and make sure project is well unit tested, had good testing coverage metrics shown, and fully typed with verifying typing by Mypy in professional configuration
https://careers.wolt.com/en/blog/tech/professional-grade-mypy-configuration

That will make big room for code scaling later, and just nicer to work with it from the start

[keen minnow]

Then I suggest to come back for suggestion 😉

No need to worry about super scalable and super resilient architecture when you have nothing yet

[real laurel]

Then SQLite it is ! 😎

Thanks again mates!

[sweet gorge]

can someone help me with my code?

[keen minnow]

it depends on the code and problem

[sweet gorge]

well the problem is my code seems to cannot run i've searched all over the internet and can't find any solution
i'm working on database management with mysql
i can send the full code if needed

[keen minnow]

this is just a screenshot of a filename?

why is there a & in front of your call to python?

[sweet gorge]

umm no it's the terminal, as i said the code won't run it keeps giving the same result as the screenshot i showed

[sweet gorge]

i have no idea, i'm just copying my professor's code

[keen minnow]

that has nothing to do with #databases though.
You may want to open a help thread as described on #❓｜how-to-get-help

And you may also want to add some print statements to your code to see what is going on

[sweet gorge]

oh i'm sorry, i was working with database related i thought i'd ask here

[keen minnow]

no worries.
But right now, you don't even have a database problem. You have a "how to run a python script problem"

[grim vault]

The & is the call operator of the windows powershell and perfectly normal. But I don't see any error printed out?

[jolly shuttle]

this is my data, i want to get the latest entry based on time for each id, does anyone know what would sql query look like for this?

[grim vault]

Something like:

select *
  from table as a
 where a.time = (select max(b.time)
                   from table as b
                  where b.id = a.id)

or with a window function.

[hollow oar]

i used to think this as well, but then i read https://www.uber.com/en-RO/blog/postgres-to-mysql-migration/ which is quite interesting imo.
but then again, for pet projects, these caveats can probably be safely ignored

[wise goblet]

Void most of Cons. For production it is first assumed u will use Cloud Provider Managed Databases like AWS RDS or Aurora.
Replication, backups, upgrades, everything is simplified

[paper flower]

Here's a video that covers this article https://www.youtube.com/watch?v=_E43l5EbNI4

[nova tapir]

does someone whats wrong there ?

and as a second question, does someone know how to use pgAdmin4

[hot narwhal]

Hello, I'm trying to access a MongoDB database with pymongo like this:

database = client["TESTDB"]
group = database["TESTSERVER"]
prl = group["341"]

But it keeps returning the same values. even if the "prl" value changes, why? i thought subcollections existed in MongoDB

[frozen grotto]

I think the mistake is earlier on, can you send all the code (as text) ?

[frozen grotto]

!paste

[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 floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[hot narwhal]

holy shit i'm so stupid, fixed, sorry

i was using

database.group.prl.insert_one(entry)

i fixed it by just doing

prl.insert_one(entry)

[nova tapir]

@frozen grotto yes but its so much it´s tah a document. Its cool if someone can fix the bugs i have bow

now

[delicate field]

Hey @nova tapir!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[nova tapir]

ore dm me ande i send it to you

[frozen grotto]

just use this, I don't use DM's

[long rock]

General question, would thread locks be the ideal way to handle calls to the database to avoid database locked errors or is there a more efficient way of handling this?

For more detail I have a sqlite database but could switch to mysql or anything else... just a small project; trying to learn best practices for efficiency

[waxen finch]

in sqlite, "database is locked" means there's another connection holding a write lock on the database which is preventing your current connection from doing anything
(the locking process is detailed in https://sqlite.org/lockingv3.html and https://sqlite.org/atomiccommit.html#_single_file_commit)

a threading lock by itself wouldnt do much to solve the issue, though if your program was made to use a single connection, a lock would be helpful in preventing concurrent access to it

or alternatively if you're creating mostly reader connections, you can set the database to use a write-ahead log which allows readers to work in parallel with one writer https://sqlite.org/wal.html

and/or you can keep your connections short-lived so they dont lock the database for too long

[long rock]

Oh yeah this is sort of a personal data science project. My pc is the only machine connecting to the db, it’s more just using threading to speed up calculations. Simple selects and inserts that are maybe a couple rows at a time max

I can probably rewrite to load the selects into memory then each thread just needs to insert their couple rows of data when they’re done doing calculations

[waxen finch]

yeah its usually only one computer using the database, but a single process can still make many connections to the same file (and other processes too) which can lead to it being locked when one of them needs to write

as a sidenote, if you want to use one connection across multiple threads you should verify that sqlite is compiled with SQLITE_THREADSAFE=1
since 3.11 you can simply check that with sqlite3.threadsafety == 3, but for older versions you would want to run a query to check the threadsafe compile option https://docs.python.org/3.10/library/sqlite3.html#sqlite3.threadsafety

though given that your intent is to speed up your program, multiple connections would be desirable to run select queries in parallel

if you dont want to use WAL mode then you could aggregate all the insert queries into a queue somewhere and then process it once your threads have finished reading

[long rock]

Good idea. The problem is I’m calling an api that has a limit to a couple calls a minute. Instead of just doing a sleep I start doing calculations for each call and the Calcs would take longer than I’d need to wait to make another api call so that was the thought behind adding multithreading to prevent this from running over night. As a first iteration I have 4 classes that do their own calculations. Each load and save from the db as necessary. I guess best solution would be load everything upfront, call the api and select whatever data corresponds with it, make a data frame of new data to save but keep it all in memory to avoid selects and inserts between class calls. I guess the new data data frame would need to be saved at the end of each thread? Idk if there’s a good way of combing all them at the end and calling a final save

[waxen finch]

if you cant feasibly design that, short-lived connections + WAL mode might be a simpler choice

e.g. ```py

run "PRAGMA journal_mode=wal;" on database at least once beforehand

(all subsequent connections to said database will respect this choice)

in each thread, read the necessary data and optionally close the connection

(WAL prevents locking here):

with sqlite3.connect(...) as conn:
data = conn.execute("SELECT ...").fetchall()

... # do work

once results are ready, open a connection to write and close asap:

with sqlite3.connect(...) as conn:
conn.executemany("INSERT INTO ...", ...)
conn.commit()```

[waxen finch]

oh i forgot, using with on an sqlite connection merely starts a transaction 😔

[shut kindle]

Hi, im a little new to doing projects on my own and not following a guide or course, im working on a personal project and have no idea how far it will go, is there any tips for what should i look when im modelling a database expecting to not need to change EVERYTHING if i decide to include something new in the project in near future, i think im very lost with what is the "correct way" to create everything even if i know the all the practical technology related stuff

[torn sphinx]

can anyone tell me why the logging.db is not working for storing the data ?

https://paste.pythondiscord.com/olewajibom

[languid flint]

Hi I am having trouble with a AWS postgresql database. I have this one particular table in my db that when I try to run an update or create on it. It just hangs and my lambda function times out do you know what causes this? it seems to only happen to this one table
Also this is whats going on. I'm not sure about the sessions on writer instance if that will affect anything.

where do I begin to trouble shoot this?

[granite storm]

💀 (it ended at 61 min)

[subtle robin]

anyone knows how to call store procedures created in the postgres pgadmin in the django API ?

SELECT * FROM senderra.note

CREATE OR REPLACE PROCEDURE schema.update_note(IN _note_id bigint)
LANGUAGE 'plpgsql'
AS
$$
DECLARE
    BEGIN
        UPDATE senderra.note SET note_text='Hello World' WHERE id=_note_id;
    END;
$$;

CALL senderra.update_note (152);```
This is my SP.

[lavish iris]

HOW DID YOU DO THE ID

TELL ME PLEASE

[queen rose]

if I have a query with both ORDER BY and WHERE clause, which gets executed first?

[static dragon]

Order by

[slender atlas]

The WHERE clause filters, the ORDER BY orders/sorts, no?

[static dragon]

Yeah

[slender atlas]

So it doesn't matter at all for the end result, does it?

[static dragon]

Nope

[queen rose]

unfortunately it does, because when I order trough multiple columns and I have WHERE col > x it messes up everything

[slender atlas]

What becomes of it?

[queen rose]

this is the code, the issue is that the same PRIMARY KEY path_hash is appearing in multiple chunks:

    def nodes_iterator(size: int = 2000) -> Iterator[list[Category]]:
        last_pk = 0
        while True:
            with Session() as session:
                stmt = select(Category).where(Category.path_hash > last_pk).order_by(
                    Category.category, Category.path_hash).limit(size)
                data: list[Category] = [x[0] for x in session.execute(stmt).all()]
                yield data

                last_pk = data[-1].path_hash
                if not data:
                    break

do you guys know how I can fix it?

the goal of this is to iterate trough a very large table without bringing all the records in memory.
an alternative might be to remove the WHERE clause and just use LIMIT and OFFSET, but I'm not sure if it is more efficient?

for context the path_hash column is a PK, and category is indexed but not unique

[slender atlas]

If you don't care about the order, LIMIT with OFFSET is what I would go with

[paper flower]

You can use streaming result

[idle ferry]

my database does not update when i click save on the adminGUI. idk y. https://pastebin.com/Jp8PfZg5

[idle ferry]

anyone?

[queen rose]

I tried SQLAlchemy's yield_per() and it was giving me some weird errors

my hypothesis is that MySQL doesent support that feature

[queen rose]

I need the output sorted by category (and path_hash to make it consistent since categories can be duplicates)

[native ibex]

I'm using sqlachemy with pg, and I'm trying to filter ARRAY colum, i need any element of an array to be in range, between, but so far I can't find any solution online, could really use some help here 🙂

[somber ember]

when you download some tables that's in other format (like upl) and copy and save it as csv or parquet, is there chance for data loss for not converting through 3rd party library?

[icy oyster]

I'm reading a book now to know how to correctly setup dbs and stuff, but for the meantime I'd like to start building my DB for a side project I have. Is there any youtube tutorial or something that can explain me about relational DB's? How to prepare tables correctly, etc. I say etc cause idk all the things that can be done in sql, so yeah just something for me to have a solid idea on how to setup relational db's

even though I'll now setup the DB in sqlite, in the future it'll be MariaDB

[somber ember]

if you haven't already searched it up, this looks good.

https://youtu.be/ztHopE5Wnpc

and as you said, when you search on youtube, there are plenty vids about relational vs non-relational

[icy oyster]

Based on previous research, I know I need a relational DB, I just dont know how to set it up correctly. So thanks! I'll watch it!

[idle ferry]

https://pastebin.com/NMvr8hFn

cant edit the rows in database once they are created. pls help

[torn sphinx]

What oo database with python on win10 api is robust and easy to use ?

[ebon skiff]

postgresql

[paper flower]

It's not an OO database though

But generally postgres would be a good choice

[ebon skiff]

oo he meant oo

[queen rose]

do indexes slow down INSERT queries by alot?

and when does MySQL index the new inserted record, on a batch, so like once a day, or immidiately as soon as you insert it?

[queen rose]

best way to use LOAD DATA from Python? (pandas.dataframe.to_csv? does SQLAlchemy have anything builtin?)

[thorny anchor]

what's an OO database

[flint loom]

hey! is there a way to prevent code execution when deserializing an Exception? I thought it would make sense to store exceptions in a DB, but then I deserialize them, the code gets executed and the code goes right to the place where it failed, which is not what I want

[paper flower]

Why would you want to store your exceptions in db?

[flint loom]

Do you see no reason why? I thought it would be useful to have a "history" which would show me what and where did exactly go wrong? my app is automating a desktop application and is using a queue to go through "process items". When something goes wrong I want to retry completing that item, so I need to see what was the problem. I also want to be able to log that information so that when the retry fails, I can see the exact reason for that.

[thorny anchor]

a DB is not the right tool for that. you would just dump that into a text file

[thorny anchor]

what's an OO database?

[paper flower]

Object Oriented database I think?

Apparently it's a thing

[thorny anchor]

like, documents like mongo?

[paper flower]

Honestly I don't know 😅

https://en.wikipedia.org/wiki/Object_database

[thorny anchor]

interesting

[patent portal]

Anyone worked with geospatial data in bigquery

[torn sphinx]

hello

[queen rose]

anybody familiar with MySQL and sqlalchemy?

I want to pass OPT_LOCAL_INFILE=1 to the driver to execute when it creates the connection so I have permission to upload files to the DB. In MySQL Workbench its very easy to add this flag on startup, but I cant figure out how to do it in SQLAlchemy??

[formal cosmos]

!pastebin

[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 floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[formal cosmos]

Based on the discussion a few weeks ago, I managed to get temporary tables to work with SQLAlchemy ORM, as follows: https://paste.pythondiscord.com/owudofekig

This works well enough, until recently, I found that SQLAlchemy very rarely raises an error when such temporary tables are made in a multithreaded context. The reason can be traced back to SQLAlchemy's mapper registry changing size when another thread is iterating through it (as shown in the following stack trace, sorry I only have a screenshot). Is there any way to prevent this from happening, or do I have to just deal with it and retry the operation whenever this occurs?

[frigid mulch]

my code

[paper flower]

Maybe it would be better to create new registry when you make a table? 🤔

[formal cosmos]

hmm, not sure how that would help

since that registry would also have to be iterated through

I looked more into the internals and it appears that sqlalchemy.orm.mapper._do_configure_registries is executed under the scope of sqlalchemy.orm.mapper._CONFIGURE_MUTEX

maybe I can add/remove the table from the MetaData inside the scope of the mutex?

not sure whether that would break other stuff though xD

also not sure how would I even get access to that mutex

seems that it isn't being exported by SQLAlchemy

nvm I managed to import it with from sqlalchemy.orm.mapper import _CONFIGURE_MUTEX even though the code linter thinks it doesn't exist

but I doubt that this would be only instance of accessing the registry

hmm I looked inside weakref and apparently there is an _IterationGuard that's supposed to protect against this?

https://github.com/python/cpython/blob/3.8/Lib/weakref.py#L438

[delicate field]

Lib/weakref.py line 438

for wr in self.data:```

[simple panther]

i am currently using aiosqlite for database . so for security i am trying to encrypt database (either the database file or its contents) but i dont know where to start. is there any suitable method for it

[paper flower]

Why? 🤔

[formal cosmos]

from my understanding, sqlalchemy iterates through the registry to find the table that corresponds to the ORM type, right?

so why would using a new registry remove the need for iterating, since the temp table still needs to be found somehow

[paper flower]

But orm types themselves contain tables

[formal cosmos]

hmm true. In that case idk what the iteration in list(self._managers) is for

what would be relation between that and creating a new registry?

[wary vine]

need a help

[formal cosmos]

looks like a connection issue rather than a problem with your code

[unique gulch]

DB has a public IP oh dear

[formal cosmos]

I hope the DB is behind a firewall or smth xD

but still

[wary vine]

i didn't get

[formal cosmos]

where is your DB server set up? how are you connecting to it?

[wary vine]

i used elephant sql

[wary vine]

postgres://xxxxskk:***@arjuna.db.elephantsql.com/xxxxskk if i'm right this is my hostname arjuna.db.elephantsql.com

[formal cosmos]

oh, so your database is hosted externally?

[wary vine]

yes

[formal cosmos]

did the service provider give you instructions on how to connect to the server?

[wary vine]

https://www.elephantsql.com/docs/

[formal cosmos]

maybe a bit more specific than that....

[wary vine]

it working fine on locally

they already managed everything

[formal cosmos]

what do you mean by "locally", since your database server is located outside?

[wary vine]

see I was trying that db in my Django app in my system.

i used pgadmin to connect to elephant db and then the server i created on pgadmin i used it to connect my django app

hey @formal cosmos are u getting?

[formal cosmos]

so in which case is it working? and in which case can't you connect to the server?

[elder elk]

is it possible to wright select statement in case

?

[wary vine]

i guess if i'm not wrong

[wary vine]

i tried this way

[elder elk]

case when var = 'val' then select * from table ?

[wary vine]

it's working on dev mode

[formal cosmos]

so you were able to access the server on <host>/<port> from pgAdmin, but not when connecting through your django app?

[wary vine]

https://www.guru99.com/sql-server-if-else.html

[wary vine]

yes

[formal cosmos]

are you running pgAdmin and your django app from the same machine?

[wary vine]

yes

[elder elk]

Thank you

[formal cosmos]

can you try pinging the server from the command line and see if you can get a response?

[wary vine]

ok wait

i will try

[formal cosmos]

I'm not really into networks but it seems that your machine/server has some restrictions on how you can access other locations

[wary vine]

same bro i am thinking too

[wary vine]

@formal cosmos thanks for giving ur precious time. I will try to find a solution and I will definitely share it with u

[formal cosmos]

np, sorry I couldn't assist you further

[waxen finch]

there are three different libraries im aware of for encrypting the entire sqlite database:

1. the official SQLite Encryption Extension which is proprietary
   https://sqlite.org/see/doc/release/www/readme.wiki

2. SQLCipher which has an open source community edition and is one ive experimented with myself, but the latest SQLite version they have right now is 3.39.4
   https://github.com/sqlcipher/sqlcipher
   https://www.zetetic.net/sqlcipher/
   (compiling for linux was simple enough but i had a rough time compiling on windows, ended up writing this guide for it: https://gist.github.com/thegamecracks/bfda25960f89f41fe8bdf70a13c4eb2d)

3. SQLite3MultipleCiphers which is the encryption bundled into wxSQLite3 library, i havent tried it before but it seems to:

• support SQLCipher's encryption along with four other schemes
• stays up-to-date with sqlite versions
  (they updated to SQLite 3.41.0 just two days after it came out last week)
• and has prebuilt binaries for windows which is convenient
  https://github.com/utelle/SQLite3MultipleCiphers
  https://utelle.github.io/SQLite3MultipleCiphers/

for integrating any of the above into python (allowing them to be accessed by aiosqlite), on windows you can replace the sqlite3.dll in the python installation, but im not sure about linux because i remember when i used make install, sqlcipher compiled under the name libsqlcipher which python wouldnt link to (though maybe thats an issue with my python install as i cant upgrade the regular sqlite3 version either...)

edit: in linux i think renaming the installed libraries to libsqlite3.so.* is good enough for substituting sqlite

[waxen finch]

^ if anyone knows the answer for the issue im having thatd be cool, the sudo make install process works fine on my raspberrypi but WSL is stuck on an older version
(rpi has python 3.11.0 while my system has 3.11.2, both were installed using pyenv)

[waxen finch]

turns out libsqlite3.so.0 kept symlinking to a backup i had made, whoops

[quick chasm]

Can i get into the topic data bases without hours of watchtime and a bad ass Laptope that i code on? Moreover are there like projects a private person can do? Cause i could imagine most stuff is based on some companys databaee or smth

[old marten]

with sqlite3, i have a user account system. so basically every user has a random id, and then 3 different types of data attached to them. how would i test to see if a specific id is in the db already to prevent the same id from being used twice?

[waxen finch]

for SQL in particular, sqlite is a pretty easy introduction since its part of python's built-in libraries and is exceptionally a file-based database, removing the need for any server setup

you can learn SQL from one of the resources like sqlbolt in the pinned messages and then start using sqlite in your projects for data persistence / analysis (reading sqlite's docs is also pretty helpful too https://sqlite.org/index.html)

[quick chasm]

Hmm, thing is, the last time i heard about databases and sql qas back i 8th grade so ig i need an general tutorital for like what that stuff is and how it works theoretical

[old marten]

still need help, how would i check to see if an id is in the database already

[waxen finch]

not sure if there's a conventional approach for avoiding collisions in the first place, but where collisions are possible, you can either try/except sqlite3.IntegrityError (EAFP) when running your insert statement, assuming your ID column has a unique constraint on it, or use SELECT EXISTS(SELECT * FROM table WHERE id=...) to check beforehand (LBYL)

[old marten]

im confused, what do you mean SELECT EXISTS

[waxen finch]

the front page of https://sqlbolt.com/ and this 2min video https://youtu.be/zsjvFFKOm3c gives a sufficient summary about the base concepts of SQL
(though afterwards you might want to delve further into those concepts like primary/foreign keys and database normalization)

[old marten]

no like what order. like this?

c.execute SELECT EXISTS("SELECT * FROM players WHERE user_id = '???'")

?

i dont assume its before the c.execute

[waxen finch]

EXISTS is an SQL operator that evaluates the select statement following it and returns 0/1 depending if that subquery returns a row

[quick chasm]

Ok ty

[old marten]

so would i need to set that to a variable then?

[waxen finch]

you wouldnt write SELECT or EXISTS outside the parentheses because thats part of the sql statement itself

[old marten]

ok

is_in_db = c.execute ("SELECT EXISTS * FROM players WHERE user_id = '???'")
```so would this work then?

[waxen finch]

!e ```py
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE example (id INTEGER PRIMARY KEY)")
conn.execute("INSERT INTO example VALUES (1234)")

c = conn.execute("SELECT EXISTS (SELECT * FROM example WHERE id=?)", [1234])
print("result:", c.fetchone())```

[delicate field]

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

result: (1,)

[waxen finch]

see also https://sqlite.org/lang_expr.html which is where EXISTS is documented for sqlite

[old marten]

thx

[uncut steeple]

db = client.user_data

db.user_bal.insert_one(
    {
        "author": "123",
        "balance": 10
    }
)

print(db.user_bal)```

How do I call the data within my mongodb. Specifially the user_bal db.

[alpine dagger]

`#Guess the output of each print statement before you click RUN!
python = 'I am PYHTON'

print(python[1:4])#am
print(python[1:])#am Phyton
print(python[:])#I am Phyton
print(python[1:100])# am Phyton
print(python[-1])#N
print(python[-4])#H
print(python[:-3])#I am PYH
print(python[-3:])#TON
print(python[::-1])#NOTHYP ma`

[torn sphinx]

Never used sqlite3 before any idea how to fix?

[torn sphinx]

yo do people use access in a professional environment?

[wise goblet]

You mean, Microsoft Access?

Gods, no.

[torn sphinx]

Thank goodness for that

What do they use mostly? Mongo db?

[ivory turtle]

postgres

at least, the majority of new applications will be using postgres

obviously there are tons of legacy systems using other databases

[torn sphinx]

How do you guys test if the data that returns from your SQL statement is correct? Cus damn. I do not trust myself

[ivory turtle]

You can use a gui tool to display the data, then manually perform the calculations and verify that way

i.e. mysql workbench, table plus, postico, etc.

[green shuttle]

Anyone good in sql

lite

[stoic pewter]

I work with mongodb. What is better to use, in a separate file a record about the user or in the command itself to make a record?

[oak fulcrum]

can anyone help me with my django project

[uncut steeple]

@bot.command()
async def addinfo(ctx, token, password):
    if user_info.find_one({"_id": ctx.author.id}):
        user_Data = {
            "_id": ctx.author.id,
            "token1": token,
            "password1": password
    }
        
    data = user_info.find_one({"_id": ctx.author.id})
    data['token1'] = token
    data['password1'] = password
    user_info.update_one({"_id": ctx.author.id}, {"$set": {"token1": data['token1']}})
    user_info.update_one({"_id": ctx.author.id}, {"$set": {"password1": data['password1']}})

    embed = discord.Embed(title="Added Information", description=f"Token: {data['token1']}\nPassword: {data['password1']}")
    await ctx.send(embed=embed)```

Error: ```py
Traceback (most recent call last):
  File "C:\Users\cyt\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\ext\commands\core.py", line 229, in wrapped
    ret = await coro(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\cyt\Desktop\(4) Auto Flag\Username Swapper\main.py", line 133, in addinfo
    data['token1'] = token
    ~~~~^^^^^^^^^^
TypeError: 'NoneType' object does not support item assignment

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\cyt\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\ext\commands\bot.py", line 1350, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\cyt\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\ext\commands\core.py", line 1023, in invoke
    await injected(*ctx.args, **ctx.kwargs)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\cyt\AppData\Local\Programs\Python\Python311\Lib\site-packages\discord\ext\commands\core.py", line 238, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object does not support item assignment```

[uncut steeple]

Thanks gang

[uncut steeple]

Is there a way I can have a list so like for example I have a collection called fruits then inside that I have these values py 'id`: "1", 'fruits': ['f1', 'f2', 'f3']

If I did list = data['fruits']
would I be able to loop through?

Alright thank you

[rare escarp]

Any help please?

[agile flame]

There are a lot of database design tools for designing a schema w/ entity-relationship diagrams, from lucidchart to "quick database diagrams" to "pgModeler" (for postgre).

Does it really matter which I use?

[keen minnow]

nope

[torn sphinx]

help me

please

please

oops guys
how do i search for a specific element within an array in
pymongo.

[uncut steeple]

Is there a way to get all the documents inside of a collection and loop through them?

[broken cargo]

hi this is very simple so itll be quick

Im trying to make a currency Converter the problem is i dont want usd and aud and any other currency i want to make my own numbers like 1 A = 0.166 B something like that with my own fake currency

[uncut steeple]

Store the name and I think it would be better to have a value that they all use. So for example you have a main currency like say 1BB. 1BB could = 22.6161 CB. Then all you have to do is, BB * CB

[untold dust]

how do i do this in redis-om

find = await Recipe.find(
        ingredients in Recipe.ingredients or Recipe.ingredients in ingredients
    )```

[uncut steeple]

How would I grab the last x amount of documents in a collection with mongo

[pseudo rose]

fun fact i lanched 23456 times random choice, i got these results(2 items) A: 11903
B: 11553

[queen rose]

Hey guys, is it more efficcient to do SELECT * FROM table WHERE pk_col IN {set_of_integers} or SELECT * FROM table WHERE pk_col BETWEEN {x} AND {y}?

and is there a large overhead for doing WHERE col IN {set} for very large sets, like 100k+ elements?

[west shell]

any reason whenever i use a pymongo func it breaks

[west shell]

if anyone could assist me, please do

im gonna commit some kindoff warcrime LOL

[plucky tundra]

Hey I have an excel sheet containing nutritional breakdowns of over 2700 foods. Each food has 40 components tracked. What would be the best way to store and interact with this in python?

[versed rover]

Someone asked me to do this, im not really good at coding as its a friend who did most of it so Imma need someone patient to help me. This is my script: https://paste.pythondiscord.com/yidukekala

[waxen finch]

there's pros and cons to using multiple connections vs just one, but neither approach is necessarily bad

though given the context of your previous question
(#discord-bots message), the actual cause is more likely due to the fact that you havent added any checks to know if the user has a TicketButtons view which is still in-progress

[versed rover]

That’s fine, someone already did it for me :)

Oh wait

My previous question

Yes

[versed rover]

So if I add lines to make sure that user has TicketButtons view, it’s not gonna send the same text everytime someone types in the bot’s dms?

[waxen finch]

sure

[versed rover]

How does that work

[waxen finch]

in your case its not a persistent view so an entirely in-memory solution is sufficient (i.e. no database required), just keep a dictionary of users to their ticket views and look in that to see if a user has one still running (this is getting more related to #discord-bots)

[versed rover]

Should we transfer there then?

Or I may repost my question there with an updated script tomorrow

That makes more sense

Anyways im about to head to sleep, sorry

[waxen finch]

if you're interested in the differences for SQLite in particular:

multiple connections used concurrently:

• may allow for increased concurrency in workloads, depending on the database's journal mode*
• may cause a deadlock when one connection stays alive for too long, typically leading to a sqlite3.OperationalError: database is locked

single connection used concurrently:

• does not necessarily resolve the above problems since another program can still make its own connections to the database
• is always throughput-limited because all queries must be serialized in one connection, though one connection can still make most queries very quickly
• if you don't implement a lock around the connection, it will break ACID transactions within your program (simply meaning two concurrently running functions making queries can interfere/corrupt each other)
• if you do add a lock around your connection, your program may be significantly slowed if portions of your program hold onto that lock for too long

* for the first point, the default is a rollback journal which locks the database whenever a connection is reading or writing, but if PRAGMA journal_mode=wal; is used then only writing connections will lock each other, readers can keep reading in parallel https://sqlite.org/wal.html

[versed rover]

As said, im a beginner and most of this was written by a friend, you totally lost me lol

[waxen finch]

well personally ive been starting to lean towards using multiple sqlite connections for the increased performance, and looking at your older code i see nothing wrong with your usage
(more specifically the connections are all short-lived so none of them could excessively lock the database, and the transactions/function calls seem to be correctly handled for now)

[versed rover]

I see

So it wasn’t bad

Should I switch back to the one I had?

@waxen finch

Like my old script

[waxen finch]

if you want to sure

[versed rover]

Kk

[versed rover]

So your saying the script I sent back up there was perfectly fine?

Basically

[waxen finch]

if anything using multiple connections is easier because you're letting SQLite handle concurrent transactions for you (getting the same guarantees with a single connection requires much more manual labour)

[versed rover]

I see

Would you mind helping me to code a bit?

The thing I sent above is still an issue lol

[waxen finch]

ill bring the discussion to #discord-bots

[versed rover]

Kk

[sand swan]

Hi guys i need help , how i can insert data output from my code to Table in Oracle SQL Developer ?

I try to pip cx_Oracle but it's failed

[west shell]

how do i fix the "ServerSelectionTimeoutError" in mongodb ?

[celest steeple]

To insert data output from your code to a table in Oracle SQL Developer, you can use the INSERT INTO statement. Here are the basic steps:

Connect to your database in Oracle SQL Developer.
Open a SQL worksheet.
Write a SQL query that generates the data output you want to insert into a table. For example: ```SELECT column1, column2, column3
FROM your_table
WHERE column1 = 'some_value';

After you run the query and generate the output, modify the query to include an INSERT INTO statement at the beginning of the query. For example: ```INSERT INTO your_destination_table (column1, column2, column3)
SELECT column1, column2, column3
FROM your_source_table
WHERE column1 = 'some_value';

Run the modified query. This will insert the data output into the specified table.

I hope this helps! Let me know if you have any further questions.

[west shell]

^?

[uneven gulch]

I need to make a relationships between Publication & Authors and Publication & Courses. How would I do that without Salalchemy producing an error?

`publication_authors = Table('publication_authors', Base.metadata,
Column('publication_id', Integer, ForeignKey('publication.id')),
Column('author_id', Integer, ForeignKey('author.id'))
)

publication_courses = Table('publication_courses', Base.metadata,
Column('publication_id', Integer, ForeignKey('publication.id')),
Column('course_id', Integer, ForeignKey('course.id'))
)

class Publication(Base):
# some code...
authors = relationship('Author', secondary=publication_authors, back_populates='publications')
courses = relationship('Course', secondary=publication_courses, back_populates='publications')

class Author(Base):
# some code...
publications = relationship('Publication', secondary=publication_authors, back_populates='authors')

class Course(Base):
# some code...
publications = relationship('Publication', secondary=publication_courses, back_populates='courses')
`

[uncut steeple]

db = client.sample_airbnb
info = db.listingsAndReviews


data = info.find().sort({"_id":1}).limit(3)

print(data)```

Error: ```py
TypeError: if no direction is specified, key_or_list must be an instance of list```

I'm trying to make it give me the last 3 documents of a collection (mongoDB)

[real forum]

POSTGRESQL

Hey, is there a way to set a column to null?

QUERY

@staticmethod
async def remove_channel(guild_id: int):
   return await Database.execute(("UPDATE bump_reminder SET chid = NULL WHERE gid = %s", [guild_id]))```

it does not work

[dense badger]

hey guys ! i am doing the Titanic Challenge on Kaggle, and going through it tutorial, copying the code directly from the tutorial i get this error. (i can't format the code, because the website wouldn't allow me to copy and paste)

[storm mauve]

what does train_data.columns returns?

maybe double check if you separated whenever or not they survived into another variable (and removed from the original dataframe)

[thorny anchor]

chatgpt moment

[hidden creek]

hi so i want to use postgres for a django project

and i want to change the postgres port to 8000

i've changed it in config file and inside pgadmin settings

but when i try to use it it gives this error:

connection to server at "localhost" (::1), port 8000 failed: connection refused (0x0000274D/10061) is the server running on that host and accepting TCP/IP connections?connection to server at "loclahost" (127.0.0.1), port 8000 failed: connection refused (0x0000274D/10061) is the server running on that host and accepting TCP/IP connection?

[waxen finch]

@stuck flicker
#discord-bots message

why would it break acid? wouldn't one of the transactions just wait for the other?
a single connection can only have one transaction, so even attempting to start another transaction would be equivalent to: sql sqlite> BEGIN TRANSACTION; sqlite> BEGIN TRANSACTION; Runtime error: cannot start a transaction within a transaction

[stuck flicker]

so creating multiple connections to handle different transactions is better

and by lock you mean waiting on executing a transaction until the ongoing one has finished

[waxen finch]

not necessarily until its finished, just when its ready to commit
https://sqlite.org/lockingv3.html

When a writer is ready to commit its changes, it executes the following steps:
4. Obtain an EXCLUSIVE lock on the database file ...
5. Flush all database file changes to the disk. ...
7. Drop the EXCLUSIVE and PENDING locks from the database file.

[stuck flicker]

but either way it has a lock on the file

with one connection or multiple

[waxen finch]

yea both will through the same process

[waxen finch]

the locking mechanism of sqlite is a bit complex so holding a lock wont always mean all other connections cant do anything

[real forum]

[hidden creek]

hi so i want to use postgres for a django project

and i want to change the postgres port to 8000

i've changed it in config file and inside pgadmin settings

but when i try to use it it gives this error:

connection to server at "localhost" (::1), port 8000 failed: connection refused (0x0000274D/10061) is the server running on that host and accepting TCP/IP connections?connection to server at "loclahost" (127.0.0.1), port 8000 failed: connection refused (0x0000274D/10061) is the server running on that host and accepting TCP/IP connection?

[upbeat depot]

I have a question regarding implementing DDD principles while working with sqlalchemy. I'm not sure if I can ask it here.

Lets say I have an aggregate root 'Utility' and a value object 'UtilityBill' and lets say the business rule dictates that Utility has many UtilityBills.
I've read somewhere that "creating domain models as persistence models is an anti-pattern in DDD", so to implement it in sqlalchemy, I made use
of its map_imperatively approach to mapping.

Althought, when Utility's relationship with UtilityBill has become a large collection it is not good to load all those data in memory.
Sqlalchemy's (v2.0) documentation explains the use of "WriteOnly" relationships to efficiently deal with it, i.e:

map_imperatively(
    models.Utility, 
    utility_table, 
    properties={
        'bills': relationship(models.UtilityBill, lazy='write_only', etc..)
    })

The problem is, if I have a method inside Utility that accesses the UtilityBills collection, i.e. self.bills[0], I would have to use WriteOnly api to access the data i.e. self.bills.select(). This falls as an anti-pattern as it would not make the entity persistence-agnostic.
It would also make it difficult to unit tests Utility entity since it would require triggering the class mapping during test initialization.

Has anybody encountered a similar dilema in your projects? Do you have any insights/approach on this?

[deft saffron]

Guys who is familiar with redistimeseries?

[uncut steeple]

how would I append fruits? For example say I have 3 fruits and I want to add another being f4 how would I do that

[queen rose]

is MySQL just shit

[uncut steeple]

imo yes

[queen rose]

everything is so slow smh

[paper flower]

Mysql isn't slow, you're probably doing something wrong

[warped turtle]

Is there a decorator for checking multiple roles before a command can be run?

for example "@commands.has_roles"?

nvm it's @app_commands.checks.has_any_role(*roles)

[winged steeple]

This is #databases

[warped turtle]

yes. i clicked wrong channel.

lol

I swear I clicked #discord-bots

oh well

¯_(ツ)_/¯

[queen rose]

nah Its kinda shit

also SQLAlchemy's docs mention that its very slow for large inserts

anyways, whats the story with indexes in MySQL, can you INDEX a column using a HASH algorithm?

it seems like it works only for memory tables, also wtf are memory tables?

[steep sage]

    id = db.Column(db.Integer, primary_key=True)
    display_name = db.Column(db.String, unique=True)
    attr_name = db.Column(db.String)
    group = db.Column(db.String)

class CharacterAbilities(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ability_id = db.Column(db.String, db.ForeignKey(RPGAbilities.id))
    character_id = db.Column(db.Integer, db.ForeignKey(Character.id))
    ability = db.relationship('RPGAbilities')
    ability_rating = db.Column(db.Integer)
    ability_pool = db.Column(db.Integer)``` I'm trying to join these two tables that will give me a characters full list of abilities. Ideally I'd like a full list of all abilities with the rating/pools for the character and zero in both if there is nothing in the characterabiltiies table

I've tried the following: select(Abilities).join(CharacterAbilities).filter_by(CharacterAbilities.character_id==1)

but it doesnt give me the Character ability rating/pool for some reason.. I'm still learning with SQL/DBs

Any help appreciated

[torn sphinx]

What are the pros/cons to using a MongoDB remote connection with the pymongo library (or in my case motor) to read/write data? As opposed to, for instance, the aiosqlite library with a locally-saved .db file?

What are the main differences/use cases? I have been using aiosqlite for my Python database stuff but I've been heavily considering switching.

[waxen finch]

it sounds like the main differences would be SQL vs NoSQL, of which i am not qualified to elaborate beyond what i can read online
https://www.ml4devs.com/articles/datastore-choices-sql-vs-nosql-database/#difference-between-sql-and-nosql
but also important is how much your database can scale, because a client/server database will offer better performance for larger workloads over a single file protected by sqlite with a bunch of locks
https://sqlite.org/whentouse.html
not to mention you can host a client/server database on a different computer, while sqlite doesnt natively support it (if you wanted network support, you'd need to run a webserver providing an interface to your sqlite db)

[torn sphinx]

Thanks :)

[fading patrol]

I would just add to what's been said that if you want the advantages of a full-scale database without giving up the structure of SQL, go for something like Postgres instead of Mongo

[torn sphinx]

Thank you, I'll give it some thought

[karmic trout]

Hi all! I am now taking programming courses, now we have a sqlite3 database topic. How can I send my teacher the queries that I made to my tables? Do I need to send him a console?

[fading patrol]

I'm not sure what it means to "send him a console" and it sounds like you need to ask your professor what they want if that's somehow not clear from the assignment, but SQL queries can be shared in plain text.

[gentle hedge]

Hi I have a sqllite3 database with jsons stored inside how do I filter correctly?
something like that:
c.execute("SELECT json FROM DestinyInventoryItemDefinition WHERE json["inventory"]["tierTypeName"] = "Legendary"")

[waxen finch]

you could use the JSON operators provided by sqlite https://sqlite.org/json1.html#the_and_operators

[cursive rampart]

how to check if bit in blob is equal 0 or 1? I tried:

substr(bitmap, i / 8 + 1, i / 8 + 1) & (1 << (i % 8))

how ever it doesn't work. Always returns 0. Even `cast (substr(bitmap, i, i) as integer) always returns 0

[grim vault]

Try: unicode(substr(bitmap, i / 8 + 1, 1)) & (1 << (i % 8))
The third parameter of substr() is the length which in this case is always 1 and unicode() will convert the character into the needed integer.

[civic gorge]

any SQL expert here?

[cursive rampart]

@grim vault unicode() - that is what I need. Thanks!

[remote aurora]

ok so Im using Aiosqlite and I wanted to get the row number of a row I selected

[wise goblet]

Google request: SQL enumerate rows sqlite
https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

[remote aurora]

Thanks

[willow sentinel]

Hey guys, i have a big problem
im working on a database of a project of charity web site
where and how i can host my database for free to work in it with my co-workers in parallel ?

[storm mauve]

which database exactly are you using (if you have decided it yet)?

[willow sentinel]

MySQL database

[storm mauve]

haven't used either of them myself, but https://aws.amazon.com/rds/free/ and https://www.oracle.com/cloud/free might be options - not sure

alternatively, self-host it

if the charity website has any sponsors, you can see if they offer it | you can try to find someone to sponsor it

e.g., our own infrastructure is (mostly?) sponsored - you can find a list of out sponsors in https://www.pythondiscord.com/, though I don't really know how the process to get sponsored works

[gentle hedge]

hi got a a questions about json: is there any to only sort certain things?
for example:
here I only want to sort the names of the weapons but not the properties inside of them
currently I use json.dump(data, file, indent=4, sort_keys=True) but that sorts everything

[willow sentinel]

Thank you so much for the informations
I will try to find a solution

[paper flower]

Dictionaries/Objects aren't really meant to be sorted, why do you need properties to be unsorted? 🤔

{"a": 1, "b": 2}

And

{"b": 2, "a": 1}

Are practically identical

[gentle hedge]

easier reading from the json file

[paper flower]

Reading?

[gentle hedge]

not program reading, human reading

[paper flower]

There's sort_keys parameter that you can use, but it would affect whole document

I'd say sorted keys are quite readable, especially if you only have 5 properties

[fading patrol]

Storing data is one thing and displaying it is another. If you're going to be rewriting this JSON programtically, I would let go of what it looks like but write a simple function to display the data however you want it to be displayed

[tough ibex]

hello, i have a file in GridFS on MongoDB, but pymongo won't show anything with filters:

filename = "1084178107730239638-b-nZBJAsK.jpg"

for i in gridfs.find({"filename": filename}):
    print(i.read())

this does nothing
but if i call find without any filters, it reads all files (which in this case is just: 1084178107730239638-b-nZBJAsK.jpg)

[pure mortar]

@wise goblet from kleppman's famous book — designing data-intensive applications

[restive cargo]

At a high level, how are you supposed to structure a sqlalchemy project given that your table classes inherit from objects that you need to instantiate?

[pure mortar]

the whole book has these which make it absolutely great

heres the one at the beginning of the streaming chapter

[fading patrol]

https://stackoverflow.com/questions/51756650/using-proper-file-structure-with-sqlalchemy-and-how-to-add-data-to-db

[restive cargo]

Thanks, I am also realizing that sqlalchemy 2 doesn't seem to have the same issues so I just need to use the updated docs

[dire yarrow]

should i use psycopg2 or psycopg

[waxen finch]

i believe psycopg is the latest version (v3), but its kind of weird that they maintain two packages for the same project

[rough zephyr]

Hello,
can anyone help, pls?

I am trying to update a User table. It also has a Foreign key to the Address table. My statement/exec looks like this:

    stmt = (
        update(UserModel)
        .where(UserModel.record_id == record_id)
        .values(**update_data)
        .options(selectinload(UserModel.addresses))
    )

    async with async_session() as session:
        q = await session.execute(stmt)
        await session.commit()
        return {}

This works, but I can't figure out how to get updated User back? And if I don't use "session.commit()" explicitly, it doesn't work. How so? I thought that it is committed as it is in context manager.

Thank you

[runic nest]

Which book?

[hollow oar]

Designing Data-Intensive Applications (aka DDIA)

[paper flower]

You have to use returning method, but I'm not sure if it works with orm models 🤔

import anyio
from sqlalchemy import update
from sqlalchemy.ext.asyncio import AsyncSession

from db.engine import async_session_factory
from db.models import Book


async def main():
    stmt = (
        update(Book)
        .values(title=Book.title + "_")
        .returning(Book)
    )
    session: AsyncSession
    async with async_session_factory() as session:
        result = await session.execute(stmt)
        print(result.scalars().all())



anyio.run(main)

This should work

I think you can use loading strategies like selectin and subquery too

But I'm not use about joined 🤔

[torn sphinx]

does anyone know how to create user in python colab?

[glacial current]

https://stackoverflow.com/questions/62065617/how-to-read-write-local-mysql-server-8-from-google-colab-with-pyspark i think this is what you need but ive never done it myself.

this one looks promising except try CREATE
Here's how I install and setup MySQL on Colab

# install, set connection
!apt-get install mysql-server > /dev/null
!service mysql start
!mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'"
!pip -q install PyMySQL
%load_ext sql
%config SqlMagic.feedback=False 
%config SqlMagic.autopandas=True
%sql mysql+pymysql://root:root@/
# query using %sql or %%sql
df = %sql SELECT Host, User, authentication_string FROM mysql.user
df

if that does not work i would get a connector object and just do conn,execute(' ... statemt ...')

[warm spindle]

how do i work on a table where there is an id column and each id can like, have 20 items in inventory (maximum)

and like, these 20 items will have description, price and all that details as well

[warm spindle]

how does this even make sense 😭

duh, i know i dont have inventory THATS WHY IM MAKING IT

Operation failed: There was an error while applying the SQL script to the database.
Executing:
CREATE TABLE `bank`.`inventory` (
  id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  price INT,
  quantity INT NOT NULL,
  PRIMARY KEY (id, item_name),
  FOREIGN KEY (id) REFERENCES users(id),
  CONSTRAINT max_items CHECK (id IN (SELECT id FROM inventory GROUP BY id HAVING COUNT(*) <= 20))
);

ERROR 1146: Table 'bank.inventory' doesn't exist
SQL Statement:
CREATE TABLE `bank`.`inventory` (
  id BIGINT UNSIGNED NOT NULL,
  item_name VARCHAR(255) NOT NULL,
  description VARCHAR(255),
  price INT,
  quantity INT NOT NULL,
  PRIMARY KEY (id, item_name),
  FOREIGN KEY (id) REFERENCES users(id),
  CONSTRAINT max_items CHECK (id IN (SELECT id FROM inventory GROUP BY id HAVING COUNT(*) <= 20))
)```

[hollow oar]

what kind of database is this? or alternatively, what dialect of SQL is this?

have you tried not adding a constraint and see if your query works?

[warm spindle]

yep it worked but then i tried adding constraint after creating table

so it says that adding constraint is disallowed

[hollow oar]

what kind of database is this?

and does it support triggers?

[warm spindle]

mysql

[warm spindle]

no clue honestly, im a novice

[torn sphinx]

Thank you I'll try this✨✨

[hollow oar]

yeah mysql has triggers as far as i can tell from the docs.

you can use triggers (specifically a before insert trigger) to check if your constraint is being satisfied and reject the insert if it's not

have a look here @warm spindle
https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions
note that this is only part of the puzzle, you probably need another trigger for update as well as you don't want to have a state where the constraint is breached via a UPDATE query.

[warm spindle]

thank you very much

also btw i stumbled across another issue

@bot.command()
async def steal(ctx, victim: discord.Member):
    # Get the user ID of the person using the command
    user_id = ctx.author.id
    
    # Check if the victim is also a user in the database
    mycursor.execute("SELECT id, amount FROM users WHERE id = %s", (victim.id,))
    victim_result = mycursor.fetchone()[0]
    db.commit()```

at this part the code always fails to read victim_result = mycursor.fetchone()

mysql.connector.errors.InternalError: Unread result found

The above exception was the direct cause of the following exception:

[hollow oar]

post your full traceback for more context next time

i don't really use mysql. but i think https://stackoverflow.com/questions/29772337/python-mysql-connector-unread-result-found-when-using-fetchone illustrates exactly what went wrong

[warm spindle]

okay wait

  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 229, in wrapped     
    ret = await coro(*args, **kwargs)
  File "C:\Users\SALMAN\OneDrive\Desktop\Coding Shenanigans\Bots\SimCal Bot\bank.py", line 148, in steal
    mycursor.execute("SELECT id, amount FROM users WHERE id = %s", (victim.id,))
  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 303, in execute   
    self._cnx.handle_unread_result()
  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 921, in handle_unread_result
    raise InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\bot.py", line 1349, in invoke      
    await ctx.command.invoke(ctx)
  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 1023, in invoke     
    await injected(*ctx.args, **ctx.kwargs)  # type: ignore
  File "C:\Users\SALMAN\AppData\Local\Programs\Python\Python310\lib\site-packages\discord\ext\commands\core.py", line 238, in wrapped     
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InternalError: Unread result found```

[hollow oar]

yeah have a read at the SO post i quoted, it's really comprehensive

[icy oyster]

I'm trying to create a database for my project. My project consists on the following:
The user gives a list of lists. The nested lists contain numbers, which will be passed to a function.
This function using these values, and using an url, it'll pass these and get the response of the request.

There are two types of possible responses (for now, there could be more in the future):

1. dict with 5 keys, each key tells you something about the numbers passed to the url
2. dict with 2 keys, where one key contains the 5 keys mentioned above, and the other key contains more keys that contains more keys 😄

Knowing that each nested list has the same lenght, and I can give each index a name. I'd like to create a database so that it doesnt matter the lenght of the nested lists, the type of response the url gives, etc. Meaning, it's "dynamic" in a way. How can I come up with a structure for this database, what do you recommend?

Thanks in advance 🙂

[mellow trench]

Is there a simple append-only database that is replicated? I want to record some stuff and replicated it in multiple locations for safety, but it doesn't need to ever be updated or anything

[wise goblet]

Simple replicated, any managed by cloud provider database.

In addition I heard mongoDB is easy replicated, but it is noSQL dB which makes it potentially easy to mess up.

Cockroachdb and YugabyteDB are easy to replicate self hosted databases for distributed data. They remain kind of SQL.

Hmm, probably Apache Cassandra has same features.

Anyway:
I recommend first just choosing managed database, Postgresql or smth, provider stoves replication for you.

Then if you want self hosted, try Cassandra , otherwise , otherwise CocroachDb checking YugabyteDB replication

And as last resort to go MongoDB

[mellow trench]

Those seem really overkill, also they are consensus-based: they are a single coherent cluster for a single tenant

I just want to distribute my data to backup locations, essentially

[wise goblet]

mongodb probably then

very simple key-value database

• replication stuff

[mellow trench]

Seems complex, I'm leaning towards putting a CSV on Git, or building my own sync stuff on top of SQLite

[wise goblet]

managed postgresql with replication through cloud provider is an option too though.

[wise goblet]

wait a second. never updated

[mellow trench]

Records are never updated but there is appending

[wise goblet]

well, that is still mongodb. as simple database for replication
Available as managed and self hosted ones, in both choices rumoured to be easily replicated.
in the end mongodb is just a way to have key-value storage with jsons

[wise goblet]

🙂

[wise goblet]

although i would have just used probably managed postgresql for this 🙂
No point to learn new database, when we can use postgresql for this
DigitalOcean/AWS and etc make easy replication out of the box

[mellow trench]

That's just paying someone else to deal with the problem, not a solution

I just want to replicate a small number of records to a couple locations... paying for a full managed DBMS is not really my goal, also there is no guarantee of append-only or replication to other locations (the cluster just uses replication internally)

I'm wondering if the logging ecosystem might have what I need

[wise goblet]

a very small number of records?

how much small amount?

[mellow trench]

few hundreds probably

[wise goblet]

do you need it delivered to many locations for faster access from location X, or only for backups?

[mellow trench]

for backup I would say

[wise goblet]

should it be Cloud provider agnostic (completely agnostic from all proprietary tools?), or it can use some cloud provider features like Lambdas from AWS?

[mellow trench]

I'd rather no cloud, I don't see why this should need cloud services

I suppose it could involve the cloud

[wise goblet]

how records would be appended? over network request of some form?

[mellow trench]

yeah or locally

[wise goblet]

local network request?

[mellow trench]

sure or directly

[wise goblet]

what do you mean as directly?

[mellow trench]

no network

[wise goblet]

not really answering the question, what are no network ways you wish to have

[mellow trench]

I don't mind if it uses the network

adding a line to a CSV is an example of adding a record without involving the network

or calling a command, using other kind of IPC, etc

it doesn't matter to me whether this is networked

[wise goblet]

all right. So you wish version backups preferably then

just few hundreds records

ergh... answer result... how about just storing as CSV or JSON file
and dumping from time to time into AWS S3 bucket which has version control enabled?

[mellow trench]

How do I do replication? Have a cron job somewhere that reads from one bucket and writes to more buckets?

Checking that the change was append-only then?

[wise goblet]

just create X buckets and during backup save to multiple of them

during backup, save entire file of all records to same place in bucket

although to be fair, no point to have multiple buckets, one is supposed to be enough

version controlled AWS bucket is supposed to be already bucket with backups

[mellow trench]

this is a great solution for storing files, it's just neither append-only, replicated, or queryable

[wise goblet]

https://docs.aws.amazon.com/AmazonS3/latest/userguide/replication.html apperently S3 bucket support replication

[wise goblet]

shrugs. more reliable just each time storing entire file. you said you have only few hundreds records. It seems like it is good enough to be saved entirely each time

you know, Git saves each time not append, but changed files entirely too

queryable in same way as you do backups 🙂

wait, it can be actually queryable directly over http

S3 buckets make access available to its static content if necessary

so as any web site acess will be

you can read content of file directly over network if needed

[wise goblet]

so, it is replicatable, queryable, version controlled with backups :b It is exactly fitting your request for super reliability

not append only, but i don't really see value in append only for few hundreds records only, just save dump fully each time

[mellow trench]

Well I need it

[wise goblet]

read content of current of current data, append record, save back again.

[mellow trench]

I'm not saying it can't append I'm saying it's not append-only

[wise goblet]

make your API that makes it append only?

no database is append only after all.

[mellow trench]

...right...

[wise goblet]

btw, price for S3 buckets is close to zero or smth like that in such situation

it has pricing for large amount of data transfered in many requests per month

for small amount of data like few hundred records JSON + CDN = it is basically free solution may be 🤔

or at least below 0.01$ per month

[mellow trench]

Yeah it's great for serving files it's just not a database and not append-only

[wise goblet]

well, your requests are too much countradictive then.
You don't wish to pay for managed databases
Even simpliest mongodb database is too complex for you
You wish preferably going with CSV file (can be over network or in filesystem directly)
But rejecting just storing CSV/JSON in S3 buckets for your backups

within current defined requirements, there is nothing else to offer you. Everything else is against your defined requirements

[mellow trench]

I want a replicated append-only database, if none of the systems you know match that, then I'm sorry you can't help, but that doesn't make my requirements "undefined" or "contradictive"

[wise goblet]

ergh... nah, we can't offer anything else, because they would be too complex for you

if you rejected even mongodb, then everything of having database engine is too complex

[mellow trench]

MongoDB is not append-only!

[wise goblet]

define at your API level to be append only! simple.

[mellow trench]

I don't even think it's replicated lol

Loki seems append-only but not replicated

[wise goblet]

those are ridiculous impossible requirements. Let your software define interface, not be depended on software defining your interface (especially database engines)

[mellow trench]

Replicating a few hundred key-value pairs across three machines is ridiculously impossible? What has computer science come to

[wise goblet]

Requesting database engine with no ability to delete, update records is ridiculous requirement
Database with key/value stuff and easy replication you was already offered = MongoDB, works as managed and self hosted

[mellow trench]

I'm sorry you feel the need to call my use case ridiculous, now I'm just going to block you and ask again later

[wise goblet]

fits me. No point to explain anything to you further. Let the deaf be deaf. Feel free to implement your ridiculous anti patterns :b

[mellow trench]

So does anybody know a simple append-only database that can replicate to other locations? Like etcd but append-only? Small key-value data, like a few hundreds of records. I could store the data in Git and pull from other places (commit history is append-only at least) but something more bare-bones and possibly queryable would be best.

[waxen finch]

not my cup of tea, but a distributed append-only database kind of sounds like a blockchain

[mellow trench]

Yeah I suppose it does... I don't need consensus though, so I need the Git kind of blockchain, not the Bitcoin kind

each replica just needs to store what has been written, no need to agree or authenticate or anything

I just don't want to ever lose things

[wise goblet]

at this point just write to file of json. and save already to git
and let your three other servers pulling git as cron or smth 🙂 or push during saving to three locations (with single git push you can push to many remotes)
and have simpliest web server giving ability to query this json
(or the same with csv)

[waxen finch]

apparently blockchain doesnt have to be consensus-based
https://deepai.org/publication/abc-asynchronous-blockchain-without-consensus
but i guess a git repository with receive.denyNonFastForwards true would be suitable too

[wise goblet]

You know, we can actually cook very easily its own append-only database in golang or something quickly.
0) on database load, read records from filesystem

1. have database stored as json of enumerated records, or csv
2. add new records to this json (or csv)
3. provide ability to query record from json
4. have other instance of this database, which are connected with push or pull strategy to main one. probably makes sense push strategy in this case
5. on record append, try to save to filesystem and to push to other databases. If any operation failed, return error and database lock?

[dire yarrow]

https://blogs.oracle.com/cloud-infrastructure/post/using-cloud-native-tools-to-deploy-postgresql
i'm looking at the yaml file they give here...
https://paste.pythondiscord.com/kahewosaje

and ngl it seems kinda sus?

and when i put it in my shell it gives this:

error when retrieving current configuration of:
Resource: "/v1, Resource=serviceaccounts", GroupVersionKind: "/v1, Kind=ServiceAccount"
Name: "", Namespace: "default"
from server for: "oke-admin-service-account.yaml": resource name may not be empty
error when retrieving current configuration of:
Resource: "rbac.authorization.k8s.io/v1, Resource=clusterroles", GroupVersionKind: "rbac.authorization.k8s.io/v1, Kind=ClusterRole"
Name: "", Namespace: ""
from server for: "oke-admin-service-account.yaml": resource name may not be empty

[paper flower]

K8s objects should have name and namespace

[dire yarrow]

nvm turns out there was a previous part of the tutorial i didn't read

had to apply some stuff from a random github link

that's definitely safe, right? \s

[paper flower]

Random? No

[dire yarrow]

https://raw.githubusercontent.com/kubernetes/dashboard/v2.0.3/aio/deploy/recommended.yaml

this link

idk what it does

[mellow trench]

This is from the "kubernetes" GitHub organization so you can trust it if you trust them

Version 2.0.3 is 3 years old though

[dire yarrow]

https://blogs.oracle.com/cloud-infrastructure/post/using-cloud-native-tools-to-deploy-postgresql

uh

does anyone know how i can connect to this db from my own computer?

[mellow trench]

it's in the tutorial near the bottom, what's the problem?

[torn sphinx]

it worked! can you make me understand what this means?

[glacial current]

I didn't mean to follow that. but you seem to be following some instructions to make a user and you ran into problem with the create statement.. that was because you were not connected to the database. so to do that you need to be in the DB session but i never used the google service you are using but it looks like you can run a command with !command

so that would be done after starting the db up

!service mysql start

then you can do your commans like this
!mysql -e "CREATE USER ......"

the line in that guide showed an "ALTER USER" statement which was followed by !mysql -e " SQL STATEMENT"

[old pawn]

hi

where i have to
put mydb.commit command cause wherever i put it,the python dont save my code

[torn sphinx]

if you would have to create something like a cookie recipe for a friend and his task, to port a libreoffice calc sheet to a mysql database via python, then how would that look like?

cool, by finding expressions for the previous posting i found better keywords for google

haha, sorry

[brittle dagger]

hmm

[nimble umbra]

What would be the correct way to do something like this:

IF (NEW.type = 'FOO' AND (SELECT x.type FROM table_x x WHERE x.id = NEW.id)  IN ('FOO', 'BAR')) THEN ...

So inside IF, select a row value and do comparison on it

Or doing it in same line is not possible and i need to split it into separate lines?

[ionic pecan]

Ideally you do a join on the IDs beforehand. Then you can use a CASE statement to check the type

[nimble umbra]

Managed to do it by doing AND EXISTS(SELECT ...), and then just directly checking type

[next shadow]

!rule 6, hello I have removed your post

[delicate field]

Rules

The rules and guidelines that apply to this community can be found on our rules page. We expect all members of the community to have read and understood these.

[bright cloud]

google sheets is formatting the discord id like this
i want it to show the full number

i dont know how should i format it

[dire yarrow]

that only shows how to connect from the shell itself

[mellow trench]

Yes

[bright cloud]

can someone guide me?

[dire yarrow]

yeah i want to connect from my own personal computer

[mellow trench]

What does that mean to you?

[dire yarrow]

what part of my statement was unclear?

i have psycopg on my own computer and wanna connect to the oci thing

[mellow trench]

The OCI thing?

[dire yarrow]

yeah

the thing i set up in that tutorial

[mellow trench]

You want to connect from an application running on your computer to the database running in a remote cluster, over the postgres protocol?

[dire yarrow]

i believe so

[mellow trench]

If this is a test thing or development thing, you can use the kubectl port-forward command

Otherwise you should set up a LoadBalancer Service (or a NodePort)

[dire yarrow]

wait i was just looking it up

apparently the cloud shells don't even have a public ip...

[mellow trench]

I'm not sure why they would need one

[dire yarrow]

wouldn't i need the public ip to connect to it?

[mellow trench]

"it" is not running on the cloud shell

[dire yarrow]

just making sure, "it" = the postgres db?

[mellow trench]

I think?

You can't port-forward from your computer to the cluster if you don't install kubectl on your computer

[dire yarrow]

oh.

[mellow trench]

you will need to run something like kubectl --namespace default port-forward service/my-release2-postgresql 5432:5432

[dire yarrow]

ok i probably shoulda given more context

i want my django project to use postgresql

[fading patrol]

If the correct value is there, changing the formatting of the cells to text should display it correctly

[stuck flicker]

CREATE TABLE IF NOT EXISTS Tags (
    name VARCHAR(128),
    guild_id BIGINT NOT NULL,
    owner_id BIGINT,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'utc'),
    last_edited_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'utc'),
    uses INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (name, guild_id)
);

ALTER TABLE Tags
ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT FALSE;
UPDATE Tags SET deleted = FALSE;

i have this sql

but whenever i retrieve the created_at or last_edited_at and format it as a discord timestamp it always shows the UTC time when it should show the local time

i am using postgres btw

[magic mica]

how do i use tkinter to fetch data from a database such as log in info and stuff?

[stuck flicker]

what database are you using

[magic mica]

replit database, i dont have much knowledge on this so idk sorry

[stuck flicker]

i don't know about that

actually i don't think replit database is a database

it's just a key-value storage

[magic mica]

oh… let’s just say i was to be using VSCode or smth, how would i do it from there?