databases

[paper flower]

async_sessionmaker.begin already does that

class _AsyncSessionContextManager(Generic[_AS]):
    __slots__ = ("async_session", "trans")

    async_session: _AS
    trans: AsyncSessionTransaction

    def __init__(self, async_session: _AS):
        self.async_session = async_session

    async def __aenter__(self) -> _AS:
        self.trans = self.async_session.begin()
        await self.trans.__aenter__()
        return self.async_session

    async def __aexit__(self, type_: Any, value: Any, traceback: Any) -> None:
        async def go() -> None:
            await self.trans.__aexit__(type_, value, traceback)
            await self.async_session.__aexit__(type_, value, traceback)

        task = asyncio.create_task(go())
        await asyncio.shield(task)

[narrow prawn]

yeah i got that function from someone else i talked to in this server and then without thinking i tried to adopt it to sqlalchemy

@paper flower so im a bit confused what youre suggesting. its kinda late and i should go soon tho but this channel is quiet so i guess i can continue tomorrow

with the logic i have is concurrency going to be an issue?

[paper flower]

It may be an issue, depending on how likely it is to run into concurrent requests with your bot

But single session can't handle concurrent requests, since most dbs don't support running concurrent queries on a single connection

(Including postgres)

[narrow prawn]

thats what i was thinking a connection pool would solve

and cant a pool have many sessions open at once?

with each message itll make a new session so with that in mind it a session cant have concurrency issues, right?

like even if i run a command that uses the get() method while a MessageCreate event is updating a member's message count, at the same exact time. cause the command logic will still open with:

async with self.pool.connect() as query:

updated connect()

@classmethod
@asynccontextmanager
async def connect(cls) -> AsyncGenerator["QueryManager", None]:
    async with AsyncSession(cls._engine, expire_on_commit=False) as session:
        try:
            yield QueryManager(session)
            await session.commit()
        except:
            await session.rollback()

[paper flower]

Just use session maker instead

[paper flower]

It can, but you assign session to the class

So multiple queries/tasks may use the same session

[narrow prawn]

but how if each query creates a new class instance?

i mean like thats how its planned to be integrated

[paper flower]

But you assign to the class? 🤔

If we're talking about your QueryManager

[narrow prawn]

yield QueryManager(session)

[paper flower]

    @classmethod
    def set_session(cls, session: AsyncSession):
        cls.session = session

cls is class

[narrow prawn]

so yield QueryManager(session) doesnt init a new session?

[paper flower]

It doesn't make sense to use a QueryManager like you want to

It does, but

Globally

You essentially have a global variable here

This could be distilled to

global_session = None

    @classmethod
    @asynccontextmanager
    async def connect(
        cls,
        *,
        transaction: bool = True,
    ) -> AsyncGenerator["QueryManager", None]:
        global global_session
        async with AsyncSession(cls._engine, expire_on_commit=False) as session:
            ...
            global_session = session


class MessageCount:
    @classmethod
    async def get(cls, member_id: int) -> int:
        query = await global_session.execute(
            select(cls).where(cls.member_id == member_id)
        )
        result = query.scalar_one_or_none()
        return result.count if result else 0

[narrow prawn]

so youre saying asyncsession makes it a singleton basically?

[paper flower]

No

I'm just saying that you made a global variable

[narrow prawn]

im so confused how

[paper flower]

cls.session

[flat pelican]

is anyone familiar with flameprof??
https://pypi.org/project/flameprof/#description
here is it's link to flameprof
I am trying to just get a normal graph working nothing special
I am giving it .prof files that look good to me and I see know issue
but the svg files do not work no matter the browser

[paper flower]

Class is in a global namespace, you just assign your session to it's field, like Class.session = something

It's the same as having a global variable

[narrow prawn]

oh

yeah i didnt give that much thought

the main purpose of everything is to just achieve the design of using the table name as an attr

inside the context managers like that

i need to go to bed before i stop making sense and stop comprehending anything

[paper flower]

I didn't use any discord wrapper for a while but what I'd do:

class SomeCog:
    def __init__(self, bot: Bot, session_factory: async_sessionmaker[AsyncSession]) -> None:
      self._bot = bot
      self._session_factory = session_factory

    
    async def some_command(self, user: User) -> None:
        async with self._session_factory() as session:
            repository = MessageRepository(session)
            count = await repository.count(member_id=user.id)
        await self._bot.send_message(user, str(count))

You can use session_factory.begin if you need to commit transaction too

[paper flower]

Don't try to make sqlalchemy look like django, it's a bad idea

You can use a different orm like Tortoise 🤔

[narrow prawn]

i spend way too much time designing code lol. but it pays off sometimes cause it increases readability (for me) a lot more than if i did stuff more standard

tbf i never used django

[paper flower]

sqlalchemy is just not designed to be used the way you want to use it, imo

You probably could make it work, but it's not worth it

[narrow prawn]

sounds right. someone mentioned ORM to me and that was the first thing i found that was up to date

[worthy coral]

I'm trying to use flask_sqlalchemy and I'm just utterly failing. I have this code

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///book-collection.db"
db = SQLAlchemy()
db.init_app(app)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False)

with app.app_context():
    db.create_all()

@app.route('/')
def home():
    books = db.session.execute(db.select(Book).order_by(Book.title))
    print(books)
    print(books.scalars())
    print(books.scalars().all()
    for book in books.scalars():
        print(book.title, book.author, book.rating, "ok")
    return render_template("index.html", books=books)

@app.route("/add", methods=["GET", "POST"])
def add():
     if request.method == 'POST':
         with app.app_context():
            book = Book()
            book.title = request.form['name']
            book.author = request.form['author']
            book.rating = request.form['rating']
            db.session.add(book)
            db.session.commit()
     return render_template("add.html")

if __name__ == "__main__":
    app.run(debug=True)```
and I can't get the objects to look like the tutorial I'm following in home. The query looks exactly the same, and according to this the `books.scalers()` should give me a list of usable objects that I can just iterate through and pull values, but it doesn't. I put the "ok" at the end of the `for` print for identification, but it apparently doesn't output anything. Here's the entire output for all those print statements when I go to `/`
```<sqlalchemy.engine.result.ChunkedIteratorResult object at 0x000001B2236CF600>
<sqlalchemy.engine.result.ScalarResult object at 0x000001B2236D2980>
[<Book 1>]
127.0.0.1 - - [21/Dec/2023 10:07:33] "GET / HTTP/1.1" 200 -```

this is driving me up a wall, but if anyone has any idea how I can just get my list of books out of the table, I'd be super thankful

(Also I've put books.scalars().all() in the for loop as well, and it still doesn't do anything btw)

[torn sphinx]

if anyone can help just ping me

[torn sphinx]

how do i find the host name? the localhost aint working so i assume its somthing else

[delicate field]

:incoming_envelope: :ok_hand: applied timeout to @torn sphinx until <t:1703182404:f> (10 minutes) (reason: burst spam - sent 8 messages).

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

[torn sphinx]

you can get timed for that?

dang

[next shadow]

For spamming messages? Yeah

[fading patrol]

We don't know the hostname of your DB or if you even have one set up yet. Do you?

[left siren]

can someone help me with this please

[waxen finch]

sqlite3 is preinstalled with python so you don't need to pip install it

also a bit off-topic but you probably want to run py -m pip ... instead, assuming you installed python from python.org

!windows-path

[delicate field]

PATH on Windows

If you have installed Python but forgot to check the Add Python to PATH option during the installation, you may still be able to access your installation with ease.

If you did not uncheck the option to install the py launcher, then you'll instead have a py command which can be used in the same way. If you want to be able to access your Python installation via the python command, then your best option is to re-install Python (remembering to tick the Add Python to PATH checkbox).

You can pass any options to the Python interpreter, e.g. to install the numpy module from PyPI you can run py -3 -m pip install numpy or python -m pip install numpy.

You can also access different versions of Python using the version flag of the py command, like so:

C:\Users\Username> py -3.7
... Python 3.7 starts ...
C:\Users\Username> py -3.6
... Python 3.6 starts ...
C:\Users\Username> py -2
... Python 2 (any version installed) starts ...

[left siren]

ohh, okayy, thank you so much

[torn sphinx]

I do, just gotta connect it. heres what i have
db = mysql.connector.connect(
host= ,
user= ,
password= ,
database= ,
)
this works? right?

[torn sphinx]

for connecting the db right?

[fading patrol]

Right, you need to fill the details for those four fields. If you set up the database then you should know what they are.

[torn sphinx]

i got the rest, but what is the host? as in what is it represnted by? ip? url?

[fading patrol]

The docs say either the hostname or the IP should work.

[torn sphinx]

gotcha

thanks

[fading patrol]

The host field is optional if you're running the DB locally it will default to localhost

[torn sphinx]

i tried that, the localhost didn't seem to connect for some reason

any other advice?

[fading patrol]

What is the error message?

Can you manually connect to the database?

[torn sphinx]

_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'localhost:3306' (61)

I dont think so

its a 61 error so there might be somthing wrong with the database itself

im using a mysql provided by serv00

the host should be %.devil but that dosen't seem to work

[torn sphinx]

would the code change depending on the site you use?

[dapper vigil]

I am available to help

[torn sphinx]

ah thank ya

[torn sphinx]

so heres the thing, me and a bunch of guys are working on a bot to add likes to posts to replace an older bot thats being shut down, its my job to connect the bot to a database and transfer the leaderboard data. im using a site called serv00, it supports a mysql database thats already been set up my another of our group, i have the host, the password user, ect. (at least i hope its the right one) but im still encountering these errors (see image) im not experienced with databases and im not really sure how to proceed. any advice?

and heres the code im working with

[dapper vigil]

An image does not open. Did you send me a text written in the image?

[torn sphinx]

yea lemme get the text

[dapper vigil]

ok

[torn sphinx]

Exception has occurred: DatabaseError X 2005 (HY000): Unknown MySQL server host '%.devil' (8)
_mysql_connector.MySQLInterfaceError: Unknown MySQL server host '%.devil' (8)

[dapper vigil]

He tells you that he cannot recognize a database on the confirmation server from Hostname

[torn sphinx]

what does that mean

sorry im smoothbrained when it comes to this

[dapper vigil]

This issue indicates that a connection to the MySQL database was configured using an incorrect hostname. In the error you provided, it appears that a wrong hostname '%.devil' was used which appears to be incorrect.

[torn sphinx]

so its just the hostname thats wrong? alright

[dapper vigil]

What is your project

yes

[torn sphinx]

meaning?

[dapper vigil]

Try adding a localhost for something in my life that needs a connection to a server, but it must be on the same server.

[torn sphinx]

localhost failed for some reason

[dapper vigil]

Can I log you into your computer?

[torn sphinx]

im already logged in

[dapper vigil]

install anydesk

[torn sphinx]

oh you mean like that

uuuuh sorry but no thanks

im not exactly a trusting person

i can share you photos and such

[dapper vigil]

ok Bro

[torn sphinx]

we cool?

[hidden creek]

 
                # if medicine is new, create a new one
                    cur.execute(
                        "INSERT INTO records_medicine(medicine) VALUES (%s)",
                        (sec_form.cleaned_data["medicine"],),
                    )
                    cur.execute(
                        "SELECT id FROM records_medicine WHERE medicine = %s",
                        (sec_form.cleaned_data["medicine"],),
                    )
                    medicine_id = cur.fetchone()
                    cur.execute(
                        "INSERT INTO records_condition_medicine(condition_id, medicine_id) VALUES (%s, %s)",
                        (condition.id, medicine_id[0]),
                    )

[hidden creek]

@coral wasp i hope the query is readable

[coral wasp]

What's your question about this tho?

[hidden creek]

That if i should keep the queries like this or combine them into one big query

I have a couple more like this btw

[coral wasp]

FYI, in postgres, you can return the new record's id using "returning id" at the end of the first statement.

https://www.postgresql.org/docs/current/dml-returning.html

Which eliminates the second query

If I were inserting a single medicine and single record, I'd probably write it like this (as two inserts).

[hidden creek]

Well that solves a lot 🙂
Interesting thing

I hope Django's postgres thing supports this

[coral wasp]

using that "returning", you could do something a bit more clever. Not my favorite syntax but:

[hidden creek]

and if i insert multiple med in one record?

[coral wasp]

with q1 as (INSERT INTO records_medicine(medicine) VALUES (%s) returning id)
insert into records_condition_medicine(condition_id, medicine_id) select %s, medicine_id from q1

Not a big fan of this syntax, just showing that it's possible.

[coral wasp]

In that case, yah, if I'm bulk inserting, it's important to batch them together.

You can use executemany for this: https://www.psycopg.org/docs/cursor.html#cursor.executemany

[hidden creek]

What Is the with q1 as part?

[coral wasp]

That's called a "CTE". It's a way of putting multiple statements together.

In this case, i think I'd: insert the new medicines, and then insert the records, as batches

[hidden creek]

it's just one record each time
And the orm handles that

Thank you a lot🙌
I definitely have to use that many times

[dapper vigil]

**I am available to work for you on your projects at a very cheap price **

[storm mauve]

!rule 9

[delicate field]

Rules

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

[fallen vault]

Are any free database managers online? Like vultr or digital ocean but stripped down like heroku use to be before they became paid?

[storm mauve]

There is a reason why Heroku became paid, to say the least

[fallen vault]

I'm not entirely sure why, I can only assume people only used to free version and never used the higher levels. But like I also don't want to pay 15/month to learn and play with an online database in my current situation.

[fading patrol]

AWS Free Tier is an option for one year per account.

If you're really new, maybe just focus on SQLite until you've really exhausted you can do with it. If you're not building successful projects with lots of concurrent users it's probably enough

[fallen vault]

I’m currently waiting on one project but the database is being funded by my job. This is a personal project and I wanted to play with a managed database because that seems easier than creating an update for software that doesn’t wipe the SQLite database if needed.

[paper flower]

Managed db would be idential to whatever you can host locally

Just spin a postgres or something else that you want

[fallen vault]

Thank you. Only issue is I’m not typically programming from my PC with how much time I’m at work 😅

[paper flower]

Just install it at whatever pc you're using

[fading patrol]

You might want to self-host at home and access remotely though there is a little more to it.

[fallen vault]

Alright. Thanks.

[idle zodiac]

Do any of you guys know how to setup a local dbpedia triple store? (not trying to spam btw, also asked this in data-science, but it seemed relevant here as well)

[fading patrol]

I've never done it but I think this is what you're asking for? https://github.com/dbpedia/virtuoso-sparql-endpoint-quickstart

[idle zodiac]

I found that one as well

But it was lacking

but I figured it out just now between me and my team

I think

but thank you so much for answering :3

Also answer: https://databus.dbpedia.org/dbpedia/collections/latest-core

[torn sphinx]

I keep getting this error, "Can't connect to MySQL server on 'localhost:3306' (61)" any fixes?

[fading patrol]

Is your server running on localhost? Is that the right port?

[delicate field]

:incoming_envelope: :ok_hand: applied timeout to @native zinc until <t:1703271200:f> (10 minutes) (reason: newlines spam - sent 107 newlines).

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

[opal basin]

these lines of code are also executed when i say import a function from this file is there a way to stop that

ps i need db for execution in my fuctions

[tacit acorn]

Hello, I'm wondering if I can get some guidance on why a query isn't returning what I expect. I'm using sqlalchemy.
My query is:
lowtags = len((await db.exec(select(Img.id).group_by(Img.id).having(func.count(Img.tags.any()) <= 3))).all())
its returning every item in the db, when i know there is some that is more then 3. Not sure why its doing what its doing and how to fix it to what I want.

[hexed estuary]

hmm, i'm a bit confused what func.count(Img.tags.any()) does - shouldn't it just be func.count(Img.tags) <= 3?

[tacit acorn]

i get an error if i remove the any()

im just trying to have it return every img whos count of tags are less then or equal to 3, where tags are a many to many

[hexed estuary]

ah, interesting. it seems to me like it shouldn't need a groupby if it's a many-to-many; maybe db.exec(select(Img.id).where(Img.tags.count() <= 3))?

[tacit acorn]

got
AttributeError: 'Comparator' object has no attribute 'count'

[sweet escarp]

Hey i am new dev and working fastapi with databases but to much relationship are making my code slow how can i tackle this to make my code more optimized

[hexed estuary]

hmm, experimenting locally and having trouble with this too. one can write a query like this on the association table itself, association_table.select().group_by("image_id").having(func.count("tag_id") <= 3), but that's ugly (uses literal column names, etc).

[tacit acorn]

bit of redo. but ya this works
lowtags = (await db.exec(select(ImgTag.img_id).group_by(ImgTag.img_id).having(func.count(dbmap.ImgTag.tag_id) <= 3)).all())
thank you so much

[fading patrol]

Link to the code

[harsh pulsar]

of course, just put it all inside the main function

[left siren]

Hey guyss, is it better to use MySQL or SQLite for db for discord.py bot?

[ionic pecan]

sqlite is simpler. can't go wrong with either

[whole roost]

what's the verdict on dagster vs mage AI these days

[harsh pulsar]

completely different products, no?

[whole roost]

both orchestrators, def competitors

[left siren]

Thank you so much for your advice <3

[radiant elbow]

!cban 957661229076344905 advertising spam

[delicate field]

:incoming_envelope: :ok_hand: applied ban to @dapper vigil permanently.

[zealous marsh]

Anybody use SQL Alchemy 2.0 ?

[wooden crystal]

what labs do yall use?

[paper flower]

What's the problem?

[near sphinx]

Is this where I can ask about pandas?

What is the difference betwwen difference between df.loc['column'] and df.loc[df['column']]

[inland dust]

df.loc['column']: This syntax is used to select a specific row or rows based on the index label. If 'column' is an index label, it will return the corresponding row(s) for that label.

df.loc[df['column']]: This syntax is used to filter rows based on a condition applied to a specific column. It returns all rows where the condition (df['column']) is True. If 'column' contains values or a condition that evaluates to , this expression will filter the rows where the condition is True.

[near sphinx]

ok makes sense actually

thanks!

[near sphinx]

I have a question. Why is the logic behind working like that?

[narrow prawn]

need some help with concurrency issues related to sqlalchemy+asyncpg

https://paste.pythondiscord.com/66QQ

this is the output of test.py

[Task 123] Using session ID: 1544886485392
[Task 456] Using session ID: 1544885909072
[Task 456] Initial message count: 34
[Task 456] Final message count: 35

so one task is getting locked i think

[torn sphinx]

can any of you tell me how to set up sqlite?

[torn sphinx]

anything helps, ping if answering

[coral wasp]

To get started? There’s no setup: https://docs.python.org/3/library/sqlite3.html#sqlite3-tutorial

[torn sphinx]

thanks, when connecting it to a bot do i just run it with the token?

[coral wasp]

I’m not sure what you mean. Any Python code can connect to a SQLite db using the example code in that link. Don’t know what a token would have to do with that.

[torn sphinx]

Im just asking if i use sqlite for my discord bot to track messages and such how would i do that if possible

[coral wasp]

Yah, so you’d open the database (see example) and make any changes. No different than any code that opens a db.

[narrow prawn]

you should use aiosqlite since the bot is async https://pypi.org/project/aiosqlite/

[iron oriole]

Does anyone know if SQlite would work for a telegram bot. i want my bots services to be a keyauth system with single user use, and subscription expiries.

@coral wasp ? sorry if it i wasen't supposed to ping you

but you seem capable

[narrow prawn]

@paper flower do you have any ideas. i guess i was wrong about the logic. did use your repo idea tho

what the heck it works properly now. i didnt do anything

[narrow prawn]

like i ramped up the test and it handles flawlessly. i wonder if restarting tailscale fixed something with the connection

either way still down for more critique

[fading patrol]

If you're not expecting massive traffic that's generally fine

[ancient delta]

Why is this:

SELECT DISTINCT F2CaseNumber AS CaseNum,
        CONCAT('f2t://case/', InternalCaseid) AS Link
FROM [F2_PROD].[dbo].[caseinfo]
WHERE F2CaseNumber NOT IN (SELECT CaseNum FROM [F2_PROD].[Dim].[Tinyurls])

So much slower than this:

SELECT DISTINCT F2CaseNumber AS CaseNum,
        CONCAT('f2t://case/', InternalCaseid) AS Link
FROM [F2_PROD].[dbo].[caseinfo] AS CaseInfo
LEFT JOIN [F2_PROD].[Dim].[Tinyurls] AS Tinyurls
ON CaseInfo.F2CaseNumber = Tinyurls.CaseNum
WHERE Tinyurls.CaseNum IS NOT NULL

For reference the first one has now been running for 4+ minutes on a table with 5,7k rows (wtf?) while the second is instantenous. Why?

It's like the first one gets stuck

Wait

It's the other way around

[hexed estuary]

so it's the second that's faster?

[ancient delta]

The second one is the one that is slower, sorry

No it's the first one that is faster, I mixed them up

accidentally pasted in the wrong order

There we go

The left join is faster

The first one that uses NOT IN is still running

It finally finished after 6 minutes

[hexed estuary]

i'd try an EXPLAIN QUERY PLAN and stare at it and pretend I know what all of this means see if one of them uses an index or someting

[ancient delta]

Oh right like execution plan on SQL Server, right?

I forgot that existed

Isn't that only for stored procedures?

Ohhh "explain query plan" is a command

I've never heard about it before

[hexed estuary]

EXPLAIN QUERY PLAN is how you make sqlite tell you the details of the strategy it'll use to execute this query; i don't know the equivalent for other databases but there should be one

[ancient delta]

I'll see if I can find a T-SQL equivalent

This is apparently the closest thing: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

I'll give it a shot

Oh cool

I don't have perms 🤦‍♂️

Any other ideas?

It's weird because this SO answers suggests the IN type query should be faster https://stackoverflow.com/a/1200337

[hexed estuary]

hmm, dumb question, but aren't these two doing opposite things?

it looks to me like the first does NOT IN whereas second finds rows which do have a match

[ancient delta]

As far as I understand they should be doing the same thing. The second query is an excluding left join. It's not the most intuitive query, but apparently this is what actually happens:

Which as far as I can tell is the same as "case number that is in A but not in B" just like the NOT IN in the first query

I just followed the template on this image

[hexed estuary]

I am still confused; shouldn't it be WHERE Tinyurls.CaseNum IS NULL to make it an excluding one?

[ancient delta]

Oh damn

You're right

Wait

Yeah you're right. Nice catch.

It produces the same result though which... Makes me question whether it's the same as the first query at all.

It seems odd that NOT NULL and NULL would produce the same result?

Also replacing NOT IN () with IN () makes gives a result instantenously

Not the correct result obviously, but still it executes instantenously, which is also a little odd

I mean surely in either case NOT IN vs IN it checks whether something is or isn't in a table?

So the execution time should be similar if not exactly the same?

[hexed estuary]

hmm, the number of rows returned can affect the execution time

[ancient delta]

True

IN returns 5.7k rows (all of them) while NOT IN returns 0 🤔

Really a bummer I don't have perms for showing the execution plan

[hexed estuary]

anyway, you're saying that in the second query, changing IS NOT NULL to IS NULL changes nothing?

[ancient delta]

Yes

Both return an empty table, which is what I expected (people on holiday, new cases not being created, so no tinyurls need to be generated)

Uhh...

I just tested it again

[hexed estuary]

huh, that's weird, because the results of these two queries together should always combine to the entire [F2_PROD].[dbo].[caseinfo] I think - it's like IN vs NOT IN.

[ancient delta]

I don't understand why it's giving me a different result now. There is a difference between NULL and NOT NULL now. I honestly don't know why, I literally ran the exact same query a few minutes ago. 🤷‍♂️

It's as expected now. Using NULL gives us an empty table, NOT NULL gives us the full table.

One 'mystery' solved I guess

I proabably ran the wrong version of the query without realizing

This answer (https://stackoverflow.com/a/57425433) says that the IN operator maps each row in table A to the entirety of table B. Is this true?

[hexed estuary]

as said in comments to that question,

A well designed database should be able to handle an in statement though. Like for any database performance, we need to know the bigger picture to provide useful advice.
- databases can and should optimize queries, and this one is pretty common and really should get optimized.

[ancient delta]

I'm not a database designer, I wouldn't know if this database is well designed or not unfortunately

Not sure what constitutes a well designed database anyway

I think I'll probably just use the join for now and then when people come back to work I'll ask for execution plan perms

To see what happens in each query

[hexed estuary]

looks like it's indeed commonly restricted for data security reasons: https://dba.stackexchange.com/questions/10406/are-there-any-risks-to-granting-users-sql-server-showplan-permission
but if you have query access that's quite silly

[ancient delta]

agreed

[hexed estuary]

also I stumbled upon this nice thread that may help you: https://stackoverflow.com/questions/49058299/how-can-i-optimize-a-sql-query-without-access-to-a-showplan

[ancient delta]

I'll have a look. Thanks! 😄

[hexed estuary]

(you could also try, like, adding an index on [F2_PROD].[Dim].[Tinyurls].CaseNum and seeing if that makes the query go faster, if you can modify tables and aren't afraid to do so)

[steady saffron]

SQL joins visual. (source: https://www.codeproject.com/articles/33052/visual-representation-of-sql-joins)

[steady saffron]

thanks @ancient delta for the image!

[grim vault]

Here is the original: https://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
It's from this article: https://www.codeproject.com/articles/33052/visual-representation-of-sql-joins

[steady saffron]

Added the links 👍

[left siren]

hey guyss, can someone please tell me whats wrong with my code

[harsh pulsar]

look in the traceback, it says which line of code caused the error. and the "context manager protocol" refers to with syntax

[ancient delta]

You're welcome!

Glad I could help the server that has helped me so many times 😄

[versed needle]

hi, I have an algorithm that detects trendlines the "low" and "high" levels of a signal in a dataframe, for a time window that I can set.

After I have the trendlines' slopes saved into the dataframe, I try to find which trendline pairs are parallel and: ascending, descending or horizontal.

Can anyone hop on a call with me to look through a screen share of the code? It's a bit long to explain

[coral wasp]

Best bet is to open a help thread: #❓｜how-to-get-help

[torn sphinx]

confused again, how do i set up my sqlite database to work with discord?

and how can i transfer a csv file on it?

I got a browser and everything setup

to put it simply i have the clay now how do i make bricks?

ping with answer, anything helps

[waxen finch]

well its like you have the bricks (sqlite + discord.py), the work is in making them do what you want - in other words, what do you want your bot to do? in context of our previous discussion #databases message , you might have your bot listen for reactions and when it receives one, it opens a connection to your database and inserts/updates the total # of reactions from that user
After you think of that, you might ask yourself "how do i listen for reactions?" or "how do i connect to my database?", and those questions are easier for other people to give concrete answers for

[waxen finch]

as for this, you can use the built-in csv module to read your file, and then work with whatever data it has to insert the corresponding values into some table in your sqlite database

[torn sphinx]

i think i get it, i just hope what im doing is right

[fading patrol]

Doesn't matter if you don't the first time, that's how learning works 😉 You can link to code if you have specific doubts

[torn sphinx]

truth

[glacial canyon]

Hi guys… I was just hoping to get basic help on databases as I am creating an app for my school and I need it for the login and sign up page. If anyone thinks they can help me with this please do ;-;

[civic rock]

may i

[glacial canyon]

Yes

Please

🥹

[civic rock]

say how can i

[glacial canyon]

Um

So

I need to like..

[civic rock]

inorder to use your system's database you can use mysql-connector-python

library

[glacial canyon]

Not locally

[civic rock]

oh

[glacial canyon]

I can do that but

[civic rock]

but

[glacial canyon]

Everyone in my school

When they sign up their username and password should get added to a cloud database and when they login their account should be validated by the same

Get what I’m saying?

[civic rock]

seems cool

you know how to connect to the cloud database?

[glacial canyon]

Nop

[civic rock]

ic

[glacial canyon]

That is why I need help

[civic rock]

what cloud database you have

[glacial canyon]

None

Haven’t started

Need help

Drowning in confusion

[civic rock]

oh

first you need to choose your cloud service

once your choose and signup you will get some access credentials

and based on that you need to connect

[glacial canyon]

Can you please suggest?

[civic rock]

umm i never used any cloud databases before but i can check and suggest if you want

or if you making a paid database then you can say

[ionic pecan]

why not just use sqlite?

[glacial canyon]

How?

Can you help me please?

[harsh pulsar]

did your school provide a cloud database? you say "everyone", but need to provide additional context

[glacial canyon]

I requested for it

Waiting on it

This way the login details could be automatically set to their school emails and passwords

[glacial canyon]

Students and teachers

[harsh pulsar]

in general you need two things:

1. a database, which is just a program that manages data, and sits around waiting to respond to your queries. a program that runs forever waiting for input is often called a "server". this program can run on your own machine, or on a cloud machine.

2. some way to send requests to the database server. this usually means a library that you install, and can use from inside your python code. a program that makes requests to a server is often called a "client".

it sounds like you don't have item 1 yet, and are waiting for that from your school

do you know what kind of database you will get access to? mysql? postgresql? something else?

usually 2 is easy, you just connect with a username and password. however the details will vary depending on the client library you use, and the client library you use will depend on the type of database you have access to

however you can start practicing now with a database called sqlite, which does not run in a separate program, and the data itself is just stored in a file. sqlite is already included in python, so you can get started learning SQL and practice working with the related python code, without needing a separate database server

!d sqlite3

[delicate field]

sqlite3

Source code: Lib/sqlite3/

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

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

This document includes four main sections:

[harsh pulsar]

just import sqlite3 and follow the instructions above, no need to install anything other than python and no need for a server or a username/password

specifically, start with the tutorial https://docs.python.org/3/library/sqlite3.html#sqlite3-tutorial

[glacial canyon]

Oh wow

@harsh pulsar

Thx

[glacial canyon]

Yes Ik how to use SQLite

Just

Not cloud databases

[glacial canyon]

No 😢

[fading patrol]

They're not hugely different. The libraries for Postgres, MySQL etc. work similar to those for SQLite, you just point them at a DB server instead of at a file

[glacial canyon]

Oh

[harsh pulsar]

you'll need to know in order to get the correct python library

other than that, it's going to be very similar to sqlite

[torn sphinx]

lol what can i do if i forgot mysql user password but i have the root password?

[fading patrol]

I googled it for you: https://www.cyberciti.biz/faq/mysql-change-user-password/

[torn sphinx]

o tysmm

[glacial canyon]

Pro

Wish I could google like that ;-;

[narrow prawn]

blah this was a bad test. was so confident i was done but i shouldve tested the same number on both tasks. it doesnt do what i wanted it to do

-[Task 2] Initial message count: 0
-[Task 2] Final message count: 0
+[Task 1] Initial message count: 0
+[Task 1] Final message count: 1
-[Task 2] Initial message count: 1
-[Task 2] Final message count: 2
+[Task 1] Initial message count: 1
+[Task 1] Final message count: 2
-[Task 2] Initial message count: 2
-[Task 2] Final message count: 3
+[Task 1] Initial message count: 2
+[Task 1] Final message count: 3

any pointers?

[torn sphinx]

Alright here’s the rundown. I have MySQL db/PHPadmin using this
I need to get the bot connect to the database, then have it create tables for the servers it is in OR if it already has a table simply connect to it.
Then Have the bot store the required variables for every user separately for every server they are in. In the database import the table/data for 2 different servers. And also pointers on how the bot will retrieve data for commands, these are commands that have variables that need to be separate for each server. Like previous the bot will retrieve and store these variables for every server separately, such commands and variables need to be stored separately for every user for separate servers
Anything helps, ping with answers

[torn sphinx]

fr

[glacial canyon]

real

[harsh rover]

This is my table created with flask-sql alchemy
class Ebook(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(255))
link = db.Column(db.String(255))
category_id = db.column(db.Integer, db.ForeignKey('category.id'), nullable=False)
How I can retrieve category_id and linked title and link to category_id(title and link in one variable) ?

Can anyone help with it

[tough bough]

Does anyone knows how to not lose connection with mysql when sending 1M rows from one database to another using sqlalchemy? The biggest table is the only one I'm having problem. I'm using chunk_sizes, pool_timeout and pool_recycle but it does not work

[patent quarry]

Where is the timeout happening? Is the sender unable to contact the receiver or is the sender just slowing down and dying?

You may be maxing out the connections on the receiving DB side.

[lament parcel]

Hey, can you show your code and the exception you're getting ?

[torn sphinx]

anybody got anything?

[fading patrol]

I doubt anyone can understand what you're even asking without seeing the code. Can you link to a repo maybe? Might make sense to use a help channel #❓｜how-to-get-help

[torn sphinx]

yea i think that would work for both of us

[fading patrol]

Both of us? You may have misunderstood me.

[torn sphinx]

us as a community

Idk english

[rain kettle]

I am trying to get data into my mariadb database which is hosted on a raspberry pi, so far most of it works I have made tables through a .py file but I cant get any data in there.

 try:
    con = mariadb.connect(
        user="xxx",
        password="xxxx",
        host="xxxx",
        port=3306,
        database="testDB",
    )

    cur = con.cursor()

    # Disable auto-commit
    con.autocommit = False

    cur.execute(
        "CREATE TABLE IF NOT EXISTS codes(Plattform TEXT, Codes TEXT, `Gueltig bis` INT)"
    )
    cur.execute(
        "CREATE TABLE IF NOT EXISTS CodeRedeemLogs(`User` TEXT, Code TEXT, `Redeemed at` INT)"
    )
    cur.execute(
        "CREATE TABLE IF NOT EXISTS RoleReceiveLogs(`User` TEXT, Role TEXT, `Received at` INT)"
    )

    con.commit()
    print("Test 1 passed")
    con.close()

except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

This is a db code

@bot.tree.command(name="make", description="Make a code. Only for Admins")
@app_commands.default_permissions(administrator=True)
async def code(
    interaction: discord.Interaction,
    arg: str,
):
    Codes = open("src/dotxml/Codes.xml", "a")
    await interaction.response.send_message(
        f"Ok, **{interaction.user.name}** der Code ist: `{arg}`", ephemeral=True
    )

    # Input von user (arg) wird in Liste DATA temporär gespeichert (bis bot neu gestartet wird).
    print("Code: " + arg)

    """
    Input von user (arg) wird in .txt file ./Codes.txt gespeichert. Sodass code genutzt werden kann auch wenn der Bot neugestartet wird. 
    """

    Codes.writelines(f"User Code: {arg}\n")
    Codes.close()

    con()
    print(DATA)
    print("test 1 passed")
    cur.execute("INSERT INTO codes VALUES (?, ?, ?)", (DATA[0], DATA[1], DATA[2]))
    con.commit()
    

The code gets until line 22 and doesnt print line 23 or 24

It worked just fine with a local sqlite3 database.

[glacial current]

where is print(DATA) coming from, and what is that con() doing all by itself above it?

[rain kettle]

The DATA is a list the input the appended to. Should be on there isn’t the con() needed to connect to the database and write ?

[rain kettle]

I copied the wrong code, I cant believe it.

@bot.tree.command(name="make", description="Make code. Only for Admins")
@app_commands.default_permissions(administrator=True)
async def code(
    interaction: discord.Interaction,
    arg: str,
):
    Codes = open(CODES_FILE_PATH, "a")
    await interaction.response.send_message(
        f"Ok, **{interaction.user.name}** Code für die Rolle: `{arg}`",
        ephemeral=True,
    )

    DATA = []
    # Input von user (arg) wird in Liste DATA temporär gespeichert (bis bot neu gestartet wird).
    DATA.append("Code")
    DATA.append(arg)

    one_month_from_now = datetime.now() + relativedelta(months=1)
    DATA.append(one_month_from_now.strftime("%H:%M:%S %d/%m/%Y"))

    print("Code: " + arg)

    """
    Input von user (arg) wird in .txt file ./Codes.xml gespeichert. Sodass code genutzt werden kann auch wenn der Bot neugestartet wird. 
    """

    Codes.writelines(f"Code: {arg}\n")
    Codes.close()

   # print(DATA)
    #con()
    print(DATA)
    cur.execute("INSERT INTO codes (Plattform, Codes, `Guiltig bis`) VALUES ( ?, ?, ?)", (DATA))
    print('test 1')
    con.commit()
    print('test 2')
    con.close()
    print('code passed to database')

this is the correct all the data needed is appended to the DATA List

Something must be wrong with the connection

[quiet fox]

Hello! I am implementing an app in which the user has a profile picture,
What is the best way to store the image? also is there a way to normalize the image between different devices?

[grim vault]

In your create table you named it Gueltig bis not Guiltig bis

[fading patrol]

What's best for you I can't say, but my default option would be object storage, like an S3 bucket or equivalent. But if you want to store it in your database (or maybe even on the local disk) that's fine.

Your front end should probably take care of the display issue

[quiet fox]

I dont think the frontend should take care of the issue since it can be bypassed by using the api directly

storing the image on the disk tho is very interesting, i forogt its even an option

[rain kettle]

Thanks, how could I oversee this. Sadly this still doesnt fix the problem.

[grim vault]

Another problem is that it's defined as INT but your last DATA.append() is using strftime() which will be a string.

SQLite doesn't care about the datatype but MySQL/MariaDB will.

[rain kettle]

that fixed it thanks

[glacial current]

If you want to store it in db you can use the BLOB type in the create table. Then use insert and select as normal. "Insert into avatar (name,image) values (%s, %s)" % (name, img)

[glacial current]

Reply to you

[quiet fox]

Oh I know how to insert it to the DB as a blob but i was thinking if there is a better format for it, like you might tell me base64 is better than binary or something like that

[full shuttle]

how do i save simple user input? im using streamlit as my website.

[glacial current]

Depending on what want to improve/optimize, I think you can do some simple tests to find this out. Better speed or better size. For size I know that binary is better than uuencode for size, but not sure if the db stores it in binary ir not. But you can approximate how good it does though. Do a du -s /var/lib/mysql to find size of db. Then do a bunch if inserts with the binary form and check size after. You can calculate if dbdriver is encoding the binary or storing raw binary data if raw then you cant get better unless you compress it. For speed do same test but time it.

[torn sphinx]

trying to brainstorm a database structure for a side project that will involve scraping YouTube video titles. what platforms or dbms should i look into?

[low fable]

Is there any way to get datasets that are paid like ths one https://www.statista.com/statistics/937094/average-annual-consumer-expenditure-on-footwear-by-age-us/ but without paying for them

[harsh pulsar]

possibly through a university subscription

[slender atlas]

YouTube might not like it

[opal dawn]

Have you checkout out kaggle?

[torn sphinx]

hey as you may know i have a mysql database that im using for my bot and i want it in other servers, so how do i get a discord bot connect to the database, check for the already existing tables, check the servers the bot is part of, then make tables for the servers there are no tables for?

ping with answers, anything helps

[rain kettle]

I am sorry, but I am about to lose my shit. I had to setup my DB again. And now I cant create Tables with STRING or STR.
These are my Tables but I cant make them with STR, STRING, DATETIME or what not. What am I missing ? It worked just fine yesterday

I need to pass in 2 Text and this

    one_month_from_now = datetime.now() + relativedelta(months=1)
    DATA.append(one_month_from_now.strftime("%H:%M:%S %d/%m/%Y"))

[ionic pecan]

what's the error you get when creating the table?

[rain kettle]

Uknown Data Type "STRING"

But I swear yesterday I did it with string

[torn sphinx]

Hey, I am new to databases, I use python and I want to try and create a login:password system. The only issue is I want to connec to my server using an IP:Port

[rain kettle]

like this ? host="192.168.10.183",

[torn sphinx]

something like that yeah

[rain kettle]

con = mariadb.connect(
user="xx",
password="xxx",
host="your ip",
port=3306,
database="your db",
)

[torn sphinx]

Thanks

Any idea on what I should do here?

[rain kettle]

what kinda db is this ?

[torn sphinx]

That's the server I want to connect to. I don't know much about databases and all of that but it's my friends server, it's ran by Pterodactyl.

[rain kettle]

As it says there you will create a Database

[rain kettle]

this is for connecting to the db in your code

[torn sphinx]

I go to select the database host and nothing appears to be there.

[rain kettle]

because you need to enter the ip of your server where your database will be hosted

like a raspberry pi or ubuntu server

[torn sphinx]

It's a selection, not a input.

[rain kettle]

can you show me

[torn sphinx]

Indeed.

One moment.

Drop down section.

[rain kettle]

what even is this programm you are using ?

[torn sphinx]

https://pterodactyl.io/

A buddy of mine bought a server from it and I am trying to see if databases can be connected and hosted from there.

[rain kettle]

Pterodactyl® is a free, open-source game server management panel

[torn sphinx]

Oh, it's a game panel?

[rain kettle]

I mean I guess you can host a Database maybe. But I dont think its the best solution

If you/he has access to a rasperry pi id say its the way to go.

[torn sphinx]

It's my best option, as of right now I am low on funds and can't buy a real server.

[torn sphinx]

I'm an admin on it.

[rain kettle]

tbh for a small maybe medium Database a raspberry pi should do the trick

[torn sphinx]

Alright, thanks!

[fossil sequoia]

Thanks!

[torn sphinx]

it keeps telling me its undefined, what should i define it with?

[ionic pecan]

what have you tried already?

[torn sphinx]

nothing so far since im not sure

any suggestions?

I haven't found anything online

[mild spindle]

For host, you just put the host name of your account, in mysql it's set to 'localhost' by default.

[mild spindle]

Trust me, I had problems with it when I first started using it as well lmao.😂😂😂😂😂

[torn sphinx]

how would i get my database to update automatically?

ping if you have a suggestion

[coral wasp]

Explain?

[torn sphinx]

so i get my bot connected to mysql, do the tables update automatically or do i have to write and update code

im not really good at wording things

sorry

[coral wasp]

Something (some code) has to write to the database, so it depends on what the existing bot code does.

[torn sphinx]

my bot adds reactions and I already got the table to track them per user, how would the code update the reaction number for that user.

do you get what i mean?

[coral wasp]

In database terms, you’d do an ‘insert’ or an ‘update’. Here’s a few examples: https://www.sqlitetutorial.net/sqlite-python/insert/

[torn sphinx]

ty so much

[rustic stone]

Can someone help me out with this code, if u want to help me pls dm me

I really am stuck

[fading patrol]

Why not use a help channel? #❓｜how-to-get-help

[delicate field]

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

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

[mystic plover]

Trying to have a standalone GUI made with python, idividual users that are made in mysql database and then have different ip addresses assigned by wireguard
i currently have a script that makes new wireguard client configurations and a script for creating new users to login on the GUI for.
any of you have an idea of how i can make this work?

[mystic plover]

Just look for pterodactyl eggs there are mariadb and other database eggs to choose from.

https://github.com/parkervcp/eggs

[harsh minnow]

anyone know what this mean and how can i set up a database with this terminal?

[ionic pecan]

with psql you can't setup a database, it's a client and it's asking you for the server hostname to connect to

if you just keep pressing enter until you get a query prompt it's installed properly

otherwise maybe something like https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql/ could help?

[harsh minnow]

i don't get it

[harsh minnow]

my api wont run

from flask import Flask

app = Flask(__name__)

@app.route("/")
def home():
    return "Home"

@app.route("/RumbleTD/Add",methods=["GET"])
def Add(val1,val2):
    return val1 + val2

if __name__ == "__main__":
    app.run(debug=True)

[wise wind]

error message?

[torn sphinx]

im trying to pull data from youtube API for analysis, what's the best way to store this data? i'm considering google cloud platform

[mystic plover]

Use aws

[ionic pecan]

that's like saying use a house for eating lunch

what do you want to store? is it structured data? is it hierarchical data?

depending on your data volume, storing it on the filesystem (e.g. one file per video, or one file per X videos) or in sqlite (one row per video) might be a good start

[fair shale]

I'm currently wording with MariaDB as database. When i try running my webpage on localhost i see my page clear an i can do everything. When i run it in a python:3.8-slim docker i recieve this error when i try to add something to the database i recieve this error.

        cur = mysql.connection.cursor()
        cur.execute("INSERT INTO sensor_types (type) VALUES (%s)", [value])
        mysql.connection.commit()
        cur.close()

[nova hawk]

The part where you make the connection is missing from your example

con = mysql.connector.connect(your credentials go here)
cur = con.cursor()```

[fair shale]

Yes I do it in this way. Or is this not the right way?

app.config['MYSQL_HOST'] = 'host'
app.config['MYSQL_USER'] = 'username'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'sensoren'
app.config['MYSQL_PORT'] = 7170

mysql = MySQL(app)

[nova hawk]

Perhaps you are using a different connector from the one I know?\

[fair shale]

I'm using this plugin: flask_mysqldb

[nova hawk]

Ah, my code was for the mysql's connector.

[fair shale]

but when i do it localhost it works. On my raspberry pi it don't works

[nova hawk]

can you check what mysql is?

it seems that mysql exists but mysql.connection is None

[fair shale]

i will check it

[fair shale]

after long waiting i recieve this

[glacial current]

Either the hostname of the db can't be resolved, or you have not set permission to the user to allow from host. select * from mysql.user. should show 'user'@'%' to allow auth from any host.

[fair shale]

The database is not on my pc or pi. But with my pc i can access it and with my pi i can't

[glacial current]

When you ping the host is the ip the same on PC and pi

[fair shale]

i need to check on it when I'm home. But normally they are the same. Bu it is also long before i see result of my prints in the console

[glacial current]

2 minutes long?

[fair shale]

on my pi more then that i think

but i also give it 1/2 of the cores

[mystic plover]

I am working on a standalone GUI application using Python. This application involves individual users that are stored in a MySQL database. Each of these users is assigned a unique IP address via WireGuard.

Currently, I have one script for generating new WireGuard client configurations, and user logins for the GUI.

I am looking for ideas on how to integrate these components and make the system work seamlessly. Any suggestions or guidance would be greatly appreciated

Really need help with making the GUI connect in the first place with the vpn at all...

[glacial current]

It sounds like a connect timeout which lasts for 2 minutes and that usually means the ip is dead

[fair shale]

when i ping from my pi it has a ping time from 44ms

now it print it in the console when i restart the webserver

[glacial current]

You should install the mysql client and do a simple check if the connection can be made. The stack trace is saying it has not got a connection. You need to also check user permission and grant perms accordingly

[fair shale]

is that a package that i need to install or something extra on my webserver?

requirements ```txt
Flask==2.0.1
Flask-MySQLdb==2.0.0
Werkzeug==2.0.1

linux webserver installed 
Image (from docker hub) python:3.8-slim
extra packages```
pkg-config
default-libmysqlclient-devgcc
python3-dev

[glacial current]

Yeh its the command client mysql. if it's Debian. Run apt-file search mysql if rpm based dnf whatprovides mysql to find the package of any exe.

[fair shale]

apt-file: command not found

[glacial current]

https://www.google.com/url?q=https://www.cyberciti.biz/faq/equivalent-of-rpm-qf-command/&sa=U&ved=2ahUKEwiQ86G6pL-DAxW1HjQIHf9sC6cQFnoECAAQAw&usg=AOvVaw3W23zAgvaeh4zN-ypHaAUg

[fair shale]

https://paste.pythondiscord.com/D32Q
This is what i get now

[glacial current]

It looks like mysql-common is the package

[fair shale]

I also searched a lot online and asked ai but at this moment without any results

[rain kettle]

So I have been on this quite some time now and I can't figure why it wont delete anything.

@bot.tree.command(name="delete")
@app_commands.default_permissions(administrator=True)
@app_commands.describe(input="Delete a code")
async def delete_code(interaction: discord.Interaction, input: str):
    cur.execute("SELECT CODES FROM Codes")
    all_codes = cur.fetchall()
    
    sanitized_list = [str(x[0]) for x in all_codes]
    print(sanitized_list)


    if input in sanitized_list:
        cur.execute("DELETE FROM Codes WHERE CODES = ?", (input,))
        con.commit
        
        print(cur.rowcount)
        
        print("commited to database")
        print(sanitized_list)
        

I can write to the db, and stuff I just cant delete for some reason

[mystic plover]

1. Ensure that the input code is correct and present in the database.
2. Ensure that you have established a proper connection to the database.
3. Ensure that the SQL query is correct and is deleting the code from the table.

[rain kettle]

Asked Bard, and it was actually helpfull However, the code is not successfully deleting the code. This is likely due to the cur.execute() statement not being updated to reflect the updated code list after the deletion. apparently it was this

but thanks

I am not gonna question it just pray it works

xd

[glacial current]

Have you set permission to allow connect from all host. select * from mysql.user. And find the username you are using and make sure you have % in the list

[fair shale]

yes

[mystic plover]

Anyone got an idea of how i can have my python GUI establish a connection with my wireguard vpn server via a button?

[coral wasp]

Not a database question, maybe ask in #python-discussion

[mystic plover]

It has to do with databases

SQL

altho the question i had asked didnt mention anything about it

[glacial current]

When you ping the db machine from pi box, did you do that from inside the container or just on the pi?

[fair shale]

from the cotainer

[glacial current]

apt install any and all things relating to mysql and mariadb client packages because you need to test this out with the mysql client. Or with a direct mariadb connection from python. You can have firewall or selinux preventing the comm to the mysql default port. You need to find out if that is blocked or not

[glacial current]

@grote suggested this. Which is a good suggestion did you get that working?

con = mysql.connector.connect(your credentials go here)
cur = con.cursor()```

[fair shale]

i will try it. But how do i need to add the credentioals there?

[glacial current]

It's in the documentation

https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

[fair shale]

i will try that

[tranquil nexus]

I have a normalization question:

• CarModels come with a set of possible colors

• Each such color has an RGB color value and a color name

• The CarModel ID and the RGB color value uniquely determines the color name

• The CarModel ID and the color name uniquely determines the RGB color value

• Car is an entity that is some CarModel, and has some color

How to model this relationally, using the highest normalization form possible?

[paper flower]

Why would you want the highest form?

IIRC they're not very practical

[tranquil nexus]

Alright, then what would be a good way to model this?

[paper flower]

Maybe we could start from your schema if you tried to model that?

I think in order to not have any data anomalies you'd have to use composite FKeys here:

create table car_model (
    id int primary key,
    name varchar not null
);

create table car_model_color(
    id int,
    car_model_id int not null references car_model(id),
    color varchar not null, -- Whatever type(s) you want
    primary key (id, car_model_id)
);

create table car(
    id int primary key,
    model_id int not null references car_model(id),
    color_id int not null,
    foreign key (color_id, model_id) references car_model_color(id, car_model_id)
);

Otherwise we would be able to reference colors from different models

@tranquil nexus ?

[fair shale]

import mysql.connector
from mysql.connector import errorcode

try:
  cnx = mysql.connector.connect(user='user', password='password', host='host', database='sensoren')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cnx.close()

Traceback (most recent call last):
  File "/app/app.py", line 164, in connect_2
    cnx = mysql.connector.connect(user='user', password='password', host='host', database='sensoren')
AttributeError: 'MySQL' object has no attribute 'connector'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2088, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.handle_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/app/app.py", line 177, in main
    connect_2()
  File "/app/app.py", line 165, in connect_2
    except mysql.connector.Error as err:
AttributeError: 'MySQL' object has no attribute 'connector'

[fair shale]

it finally works now with chatgpt's help

[tough bough]

How can I use relationship in SQLALchemy 2.0 without having problems with circular import if the models are in different files?

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

[paper flower]

Use place your imports in if TYPE_CHECKING 🤔

[tough bough]

Sorry, but I did not understand

[fading patrol]

I googled it for you, it's in the docs: https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html

[lament zenith]

it can be a little tough connect our DB to real server using mysql i think

[undone spire]

who wants to practice matplotlib with me?

[fair shale]

thanks everyone for helping me with my database problem

[lament zenith]

🙂

[manic gyro]

What would be the best beginner friendly database I can use for storing a medium-large amount of data?

[worn panther]

My dms

[neat tulip]

Hello Everyone, Hope you are doing well.
i am trying to run a simple find query on my MongoDB database,
shop_data = shop_collection.find_one(filter={'shopName': shop})
which works fine in my localhost but when i try to run this on server management system like cPanel i get error saying
No replica set members found yet, Timeout: 30s, Topology Description: <TopologyDescription id: 65977b0fd18e0425b0b6121, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('ac-z20zvcf-shard-00-00.h1qt8hb.mongodb.net', 27017) server_type: Unknown, rtt: None>, <ServerDescription ('ac-z20zvcf-shard-00-01.h1qt8hb.mongodb.net', 27017) server_type: Unknown, rtt: None>, <ServerDescription ('ac-z20zvcf-shard-00-02.h1qt8hb.mongodb.net', 27017) server_type: Unknown, rtt: None>]>
Correct me if i am wrong, i think we have to whitelist my cPanel's ip address in MongoDB Atlas which i have done in Atlas UI and the other part is there is firewall protection in cPanel as well which prevents connection from third party until made them authorized.
so my question is if i am correct about whitelisting my MongoDB ip address in cPanel firewall then where can i find my MongoDB cluster's ip address or do i need to find my Replica set's ip address and make them whitelist in cPanel OR i am encountering completely other error here?

[fading patrol]

SQLite is the easiest SQL DB for anything that doesn't need to handle massive traffic

If you don't want to bother with a schema you can just dump your data in Mongo or Redis which is also very easy (but generally a bad idea)

[rain kettle]

Does anyone have an idea on why I cant connect to my mariadb no more ? I can access it via phpmyadmin, but I cant access it with dbeaver. And It apears my Bot times out when I start the code with my connect stuff

import discord
from discord.ext import commands
from discord import app_commands
import logging
from datetime import datetime
from dateutil.relativedelta import relativedelta
from dotenv import load_dotenv
import os
import mariadb
import sys
from colorama import Fore
from helpcommand import MyHelp


load_dotenv()
token = str(os.getenv("TOKEN"))

logs_dir = "./Logs"
if not os.path.exists(logs_dir):
    print("Made Logs Folder and file!")
    os.makedirs(logs_dir)
# Logging Handler to log info
handler = logging.FileHandler(
    filename="./discord.log", encoding="utf-8", mode="w"
)

bot = commands.Bot(command_prefix="&", intents=discord.Intents.all())


async def load_cogs(): 
    print('loading cogs')
    for file in os.listdir("./src/cogs"): # lists all the cog files inside the cog folder.
        if file.endswith(".py"): # It gets all the cogs that ends with a ".py".
            try:
                name = file[:-3] # It gets the name of the file removing the ".py"
                await bot.load_extension(f"cogs.{name}") # This loads the cog.
            except Exception as e:
                print(f'error: {e}') 


print("@ ready event now ")

@bot.event
async def on_ready():
    print(f"{bot.user.name} is ready to rumble!")
    print("Published by Moritz Reiswaffel")
    try:
        synced = await bot.tree.sync()
        print(f"Synced {len(synced)} commands!")
    except Exception as e:
        print(e)
    # print(f'{discord.__version__}')
    print("------------------------------")
    # await bot.change_presence(
    #     activity=discord.Activity(
    #         type=discord.ActivityType.watching, name=f"{bot.command_prefix}help"
    #     )
    # )
    
                


try:
    con = mariadb.connect(
        user="x",
        password="x",
        host="x",
        port=3306,
        database="x",
    )
    
except mariadb.Error as mariaErr:
    print(f"Error connecting to MariaDB Platform: {mariaErr}")
    sys.exit(1)

[lyric reef]

throw that try except statement there away, show us the error if one occurs. If no error occurs, your actually connected to db without knowing it

[rain kettle]

made things better, but the ready event takes ages to print to the console

[lyric reef]

wanna go in #751592231726481530

[rain kettle]

lets give it a shot

[lyric reef]

can't connect to the call

[rain kettle]

rip gg

[lyric reef]

better i can't speak

[rain kettle]

no talking right

[lyric reef]

call me

[thin tundra]

hey does anyond know abt flask

and masters it ?

[merry lotus]

Anyone experienced with SQLAlchemy?
I got these kinds of errors sys:1: SAWarning: Object of type <Rule> not in session, add operation along 'Author.rules' won't proceed (This warning originated from the Session 'autoflush' process, which was invoked automatically in response to a user-initiated operation.)
I think I understand the issue, but I can't find a solution that won't break something.

I am creating a ORM database model with deeply nested relationships.
A has list of Bs, B has list of Cs, Cs has list of Ds, (Here Rule is C and Author is D)
There are constraints, name of a rule and name of an author has to be unique. When creating rules and authors I have to add them to the session and its gets auto flushed - I guess due to the nature of the unique name constraint, which it has to validate. However, because their parent objects are not created yet, they are not in the session before flushing and this results in the warning.

If I disable autoflush, I get violation of the unique constraint, a real error. I actually need the flush before I fully create the object, so should I just accept the warning or is there another workaround?

[fading patrol]

You should just ask your actual question

[fossil dust]

Yo I really need help using mysql db for server prefixes

it be tragic rn

please

[fossil dust]

Hello?

[west hill]

just use guild id to as primary key and create a table

[fossil dust]

I tryed

idk

[west hill]

CREATE TABLE IF NOT EXISTS prefixes (
    guild_id BIGINT PRIMARY KEY,
    prefix VARCHAR(10) DEFAULT '?'
);

then use ur guild id to access the current prefix

SELECT prefix FROM prefixes WHERE guild_id = <id_here>;

[fossil dust]

ok ill try it

how would I implement per server prefix's? thats what I am trying to do, like changing the prefix, removing the data of the server if the bot leaves, etc. @west hill

[west hill]

u would use sql queries and discord.py events

[fossil dust]

ngl

im an sql nub, need help with that as well

[west hill]

good time to read up on sql then

[fossil dust]

right 😐

[cosmic trout]

is there a library that will take all of SQLite functionality such as PRAGMA commands and create tables from given set of classes dynamically? Then given object of those classes populate the rows?

[fading patrol]

Not sure I follow. DBeaver can generate DDL to recreate a table, and also INSERT statements to recreate the data.

Is that what you need? Do you specifically need to do the same in Python?

[cosmic trout]

im not in a position to send you the src right now but if you see what I am trying to do It may already exist

Later I will

[fossil dust]

is there a forum or something for mysql and discord.py? Like prefixes and stuff?

[fossil dust]

??

[fossil dust]

im stuck in tutorial hell

[gentle mural]

Anyone good with Firestore

[coral wasp]

I’m not sure what you’re looking for. Can you clarify?

[fossil dust]

I want to use a database to store and use prefixes per guild using mysql

[coral wasp]

Ok, what’s the question though? What are you unsure about?

[fossil dust]

how to do it 😐

[coral wasp]

Gotta be more specific than that. Otherwise I’d say; learn Python? Then learn discord.py. Then learn sql.

[fossil dust]

well Ive tried it multiple times with bad results, the prefixes get stored but arent used for example. I just need the code on how to do it from an exististing bot at this point

[waxen finch]

other than looking for reference code, i would think through what the bot needs to do in order to manage and use its prefixes, and then go over the code and make sure it does what it should be doing, e.g.
is discord.py calling my get_prefix function correctly?
does my get_prefix function return the desired value?
is it making the correct SELECT query to retrieve the prefix for the appropriate guild?
if you're not sure, add some print statements to see what's going on and what your variables are being assigned to, and double check the documentation

btw mysql-connector-python is a synchronous library, meaning it does not support async/await and it will block your bot's event loop whenever you're making queries with it, so i suggest using a different async library like aiomysql or asyncmy

[fossil dust]

ok

thank you

[delicate field]

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

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

[edgy onyx]

guys i am working on a bank managment system with python with mysql but i cant verify the password using the account number .the account number is in string format .

Transfer_From = str(input("Enter the Account Number: "))
Verification = str(input("Enter the Password: "))
type_of_transaction = str(input("enter the type of Transaction: "))
Amount = float(input("Enter the Amount: "))
Transfer_To = str(input("Enter the Destination Account Number: "))
Success1 = "Success"
Success2 = "Failed"
sqlSearchCmd = f"SELECT ClientPass FROM bankclientpass WHERE AccountNumber = {Transfer_From} "
myCursor.execute(sqlSearchCmd)
result = myCursor.fetchone()
if result:
cell_data = result[0]
print(cell_data)
if cell_data == Verification:
sqlMoneyTransferCMD = "INSERT INTO TransactionLog (Acct_Sent_From,Acct_Sent_To,TransactionType,Amount,TransactionSuccess) VALUES (%s,%s,%s,%s,%s)"
Transaction_data = (Transfer_From,Transfer_To,type_of_transaction,Amount,Success1)
myCursor.execute(sqlMoneyTransferCMD,Transaction_data)
mydb.commit()
elif cell_data != Verification:
sqlMoneyTransferCMD = "INSERT INTO TransactionLog (Acct_Sent_From,Acct_Sent_To,TransactionType,Amount,TransactionSuccess) VALUES (%s,%s,%s,%s,%s)"
Transaction_data = (Transfer_From,Transfer_To,type_of_transaction,Amount,Success2)
myCursor.execute(sqlMoneyTransferCMD,Transaction_data)
mydb.commit()
else:
print("Account not Found")

[true tendon]

    cursor.execute(f"SELECT * FROM RecipeFilter WHERE RecipeID IN {IDlist}") # change later

Anyone understand why this doesn't work? IDlist is a list of ids and RecipeID is a valid column in RecipeFilter however I get the error

sqlite3.OperationalError: no such column: '2'

[coral wasp]

Print that string and look at it

Also, you should use parameters and not string formatting, but first look at it

[true tendon]

print which string?

IDlist?

[coral wasp]

The f string

The entire query that you’re running

[true tendon]

"SELECT * FROM RecipeFilter WHERE RecipeID IN [1, 2]"?

[coral wasp]

And is that valid sql? (Its not)

[true tendon]

why is it not?

because of the square brackets instead of curved?

[coral wasp]

Yes. And, you shouldn’t use formatted strings like that, use sql parameters

[true tendon]

what do you mean

[coral wasp]

!sql

[delicate field]

SQL & f-strings

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

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

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

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

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

See Also

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

[true tendon]

thanks i will try this.

[sturdy lily]

command keys * in Redis will give us all the keys but if we want to exclude some keys then how can we do that?

[sturdy lily]

nevermind this command worked keys [^prefix1:|prefix2:]*

[strong adder]

hello guys, im trying to connect my postgres database to dbeaver but everytime i try, it just says "Connection attempt timed out." i double-checked the info to make sure it was correct so idk what else could be the problem

[fading patrol]

If the details are correct then this is probably some sort of network issue. Is there a firewall between you and the DB? Is it hosted on a cloud platform, your local machine, or what?

[west hill]

what would be a cool dbms project i dont wanna do stuff like x management system feels too repetitive I was thinking like a db provider and visualizer like maybe create a postgresdb and a web app which creates users when they login and a db for them giving them api creds visualise tables models relations in gui etc what do u guys think? rn in ideation phase i am open to all ideas something novel would be cool

[strong adder]

its hosted on a vps

[hardy blaze]

hi

can someone give me a roadmap on how to study database

and stuff

related to it

[torn sphinx]

DBA career?

[hardy blaze]

nah

just for the knowledge

[torn sphinx]

browse oracle blogs and youtube videos

[serene tinsel]

hey guys

can anyone recommend a good database for twitter automation?

like idk which database would work perfectly for automating a bunch of twitter accounts

[fading patrol]

And you're trying to connect from outside? Then you probably need to open a firewall would be my first guess

[fading patrol]

Impossible to say based on that I fo alone. If you have to ask, maybe SQLite would be easiest

[serene tinsel]

hmm k

[swift chasm]

Hey, idk where to ask this as it might be very specific but :
I'd like to make an aggregation per window, like a rolling average, but with the added constraints of the aggregation being calculated in two ways depending on a condition
So for instance, if each rolling window had a window id i'd do that :

SELECT CASE WHEN aggregated_condition THEN agg(stuff) WHEN other_condition THEN agg2(stuff) ELSE default_value END AS computed_rolling_average
FROM table
GROUP BY window_id

I can't group by window though because I only have a continuous value to order by and to do my window on (a timestamp column)
So i'd like to use over (partition by group order by timestamp between x preceding and x following).
Problem is that this excepts a single aggregation function, i can't have several ones like in my group by example, even if they're on the same level. (syntax is : function(args) OVER(partition_by_clause order_by_clause [window_clause]))

Do you have any idea on how to solve that ?
(engine is mariaDB, database is apache doris, but this is standard sql so it shouldn't matter much)

Oh, little precision : the condition itself is applied for each window, so i can't just do my partitionning inside the then clause

[rain kettle]

I have been fighting with Chatgpt for like 10 min now after I had ran out of ideas. Everytime I try to insert into the database I get an error: ↓↓↓

#MAIN.PY DATABASE CONNECTION

try:
    con = mariadb.connect(
        user="ole",
        password="QrsoL82",
        host="192.168.10.101",
        port=3306,
        database="BunnyDB",
    )
    
except mariadb.Error as mariaErr:
    print(f"Error connecting to MariaDB Platform: {mariaErr}")
    sys.exit(1)



# # con.autocommit = False

cur = con.cursor()


        # cur.execute(
        #     "INSERT INTO Codes (`USE_CASE`, CODES, `GUELTIG_BIS`) VALUES ( ?, ?, ?)",
        #     (DATA_TWO),
        # )
cur.execute(
    "CREATE TABLE IF NOT EXISTS warns(WARN_AUSGESTELLT_VON TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT, AUSGESTELLT_AM DATETIME)"
)
con.commit()
con.close

#WARN.PY FILE DATABASE CON

            print(f'this is reason: {reason}')
            print('----------------------------')
            #print(f'this is user: {user}')
            print(f'this is true_id: {type(true_id)} »» {true_id}')
            print('----------------------------')
            print(f'this is interaction name: {interaction.user.name}')
            print('----------------------------')
            print(f'this is interaction id: {type(interaction.user.id)} »» {interaction.user.id}' )
            print('----------------------------')
            print(f'this is warn: {type(warn)} »» {warn}')
            print('----------------------------')
            print(f'this is warn id: {type(warn.id)} »» {warn.id}')
            print('----------------------------')
            now = datetime.now()# ausgestellt am
            print(f'this is now: {type(now)} »» {now.strftime("%Y-%m-%d %H:%M:%S")}')
            #print(true_id_list)
            
            #if int(warnee) in true_id_list:
            DATA = []
            DATA.append(interaction.user.name) # warn ausgestellt von
            DATA.append(interaction.user.id) # id 
            DATA.append(str(warn)) # warn ausgestellt an
            DATA.append(warn.id) # id
            if reason == None:
                await interaction.response.send_message(f'**{interaction.user.name}** warned user {warn} with no reason given')
                DATA.append('N/A') # warn grund
            else:
                DATA.append(reason)
            now = datetime.now()
            DATA.append(now.strftime("%Y-%m-%d %H:%M:%S")) # ausgestellt am
            
            print('↓↓↓↓↓↓↓↓↓↓↓↓ DATA ↓↓↓↓↓↓↓↓↓↓↓↓')
            print(DATA)
            print(' ____________________________________')
            
            guild = interaction.guild
            warn_one = discord.utils.get(guild.roles, name="warn 1")
            # warn_two = discord.utils.get(guild.roles, name="warn 2")
            # warn_three = discord.utils.get(guild.roles, name="warn 3")

            cur.execute(
            "INSERT INTO warns(WARN_AUSGESTELLT_VON TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT, AUSGESTELLT_AM DATETIME) VALUES ( ?, ?, ?,  ?, ?, ?)",
            (DATA),
            )
            
            con.commit()
            

# ERROR
[2024-01-09 15:17:30] [INFO    ] discord.client: logging in using static token
[2024-01-09 15:17:32] [INFO    ] discord.gateway: Shard ID None has connected to Gateway (Session ID: 91e983a151aff626b196d0f054fb471c).
[2024-01-09 15:17:56] [ERROR   ] discord.app_commands.tree: Ignoring exception in command 'warn'
Traceback (most recent call last):
  File "/home/ole/.local/lib/python3.11/site-packages/discord/app_commands/commands.py", line 827, in _do_call
    return await self._callback(self.binding, interaction, **params)  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ole/Dokumente/VscProjekte/DiscordBot/WarnBot/src/cogs/warn.py", line 101, in warn_user
    cur.execute(
  File "/home/ole/.local/lib/python3.11/site-packages/mariadb/cursors.py", line 314, in execute
    self._execute_binary()
mariadb.ProgrammingError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT,...' at line 1

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

Traceback (most recent call last):
  File "/home/ole/.local/lib/python3.11/site-packages/discord/app_commands/tree.py", line 1248, in _call
    await command._invoke_with_namespace(interaction, namespace)
  File "/home/ole/.local/lib/python3.11/site-packages/discord/app_commands/commands.py", line 853, in _invoke_with_namespace
    return await self._do_call(interaction, transformed_values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ole/.local/lib/python3.11/site-packages/discord/app_commands/commands.py", line 846, in _do_call
    raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'warn' raised an exception: ProgrammingError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT,...' at line 1

I am at my last straw.

[hollow oar]

i wouldn't bother with using chatgpt unless you have some idea roughly what is happening and would like to skim out on some typing yourself.

in this case you have

INSERT INTO warns(WARN_AUSGESTELLT_VON TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT, AUSGESTELLT_AM DATETIME) VALUES ( ?, ?, ?,  ?, ?, ?)

as the error indicated there is a syntax error near TEXT, ID_ONE BIGINT, WARN_AUSGESTELLT_AN TEXT, ID_TWO BIGINT,WARN_GRUND TEXT
more specifically is that you do not need to specify the type of the column when you are inserting a row. i.e. you don't need the TEXT, BIGINT, etc.

have a read in the official doc page here https://dev.mysql.com/doc/refman/8.0/en/insert.html if you want to know more about the insert statement and all the things you can do with it.

[rain kettle]

I had the exact same statement with my other bot, and I worked.

[rain kettle]

works like a charm

[obtuse magnet]

Chatgipitty

[rain kettle]

ja

[swift chasm]

ye i also tried asking chatgpt about this but he took in account only half of the constraints at all time

finally i found a solution by mixing both methods, and by using over for each and every aggregation function i have

it works great, and by adding filters and grouping, it is pretty efficient,
but it is very heavy to read (a single query of 50 lines with over 3k characters, 3 layers of subqueries, and a total of 7 uses of partitionning, i can't even paste the full query as text on discord)

I'll try indenting it and adding understandable names to each computed column so that it's more maintainable

[coral wasp]

What db are you using

[swift chasm]

it's said at the very bottom

[coral wasp]

You good now then?

[swift chasm]

well i have something that works, but if you have a better idea than just duplicating the over clause for every aggregation function, i'd take it

[coral wasp]

I don’t think Mariadb supports window clauses (reusing a window), some databases do.

[swift chasm]

i searched in doris docs and it doesn't, even if it has specific syntax on top of mariadb for specific things.
I can't even store statements in variables :/

i feel like the only way of making it better would be to have an aggregated table (or view) of windows i guess

[coral wasp]

What kinds of windowed aggregates are you using? You can sometimes rewrite using simple aggregation instead of windows, but if you’re doing a rolling calculation, window is preferred

[swift chasm]

im using maxs and mins inside the windows

[coral wasp]

And what window are you using? What’s the frame

[swift chasm]

rows between 3 preceding and 3 following

[coral wasp]

Yah, so you -could- join the table with itself where row_number between the range (-3 and +3) and do a simple aggregate. Thats how we’d do it before windows existed

[swift chasm]

oh ye i guess i could do it like that, it seems quite a bit more expensive tho, doesn't it ?
i'll give it a try

[coral wasp]

Yah, with db’s it’s often hard to predict what’s cheaper or expensive

[swift chasm]

oof, the row_number operation itself is already taking quite a long time

the table i'm working on has several billions records

[coral wasp]

Oh. Mariadb isn’t my first choice for data crunching

[paper flower]

What's your go-to db?

[swift chasm]

wait i can't use row_number in a join clause anyway 🤔

[coral wasp]

DuckDB.

[paper flower]

I see

[swift chasm]

ye i'd use spark, the problem is that i have to use a BI tool that only has RDBMS connectors

(jaspersoft, idk if you know about it)

[coral wasp]

The ability to seamlessly alternate between pandas, DuckDB, pyarrow and polars is a real superpower

[coral wasp]

Is it a one off or needs to be live?

[swift chasm]

it needs to be live, unfortunately

but it's okay, i'll manage

apache doris is within the spark ecosystem tho so maybe i'll be able to convince my hierarchy to use another tool

[strong adder]

i did but its still saying connection attempt timed out

[forest coyote]

what is the general use of triggers in database ?

[fading patrol]

I googled it for you: https://www.ibm.com/docs/en/informix-servers/14.10?topic=triggers-when-use

[torn sphinx]

Hello, its been a while since I used SQLite, I've been mainning Potsgres as my main DB for around 2 years now. I've heard that SQLite got rid of their locking mechanism that occurred due to concurrent database writes & instead replaced it with a queue, is that true?

[waxen finch]

they what? i'm looking in the release history and fossil VCS, but i don't see any mention of the locks being replaced by a queue

perhaps you are referring to their now-deprecated asynchronous I/O extension:
https://sqlite.org/src/doc/trunk/ext/async/README.txt

With asynchronous I/O, write requests are handled by a separate thread running in the background. This means that the thread that initiates a database write does not have to wait for (sometimes slow) disk I/O to occur ... Asynchronous I/O works by creating a special SQLite "vfs" structure and registering it with sqlite3_vfs_register(). When files opened via this vfs are written to (using the vfs xWrite() method), the data is not written directly to disk, but is placed in the "write-queue" to be handled by the background thread.
but as noted at the top of the readme:
The functionality implemented by this extension has been superseded by WAL-mode.
the write-ahead log has a different way of managing locks so only writers block other writers and readers can work concurrently with writers, in contrast to the rollback journal which has to interrupt readers before writing

[torn sphinx]

It wasn't that async related ext, from what I heard. However I was pretty intoxicated while having said convo, perhaps saving dev talk for clearer days is a better idea.

Regardless, ty.

[fringe sundial]

Guys any recommended resources to learn postgres SQL? Similar to rust book?

The postgres docs are really dirty looking, I can barely read

[prisma oriole]

Hey I am trying to develop an online multi-restaurant food delivery project. In my SQL database, I have business table (for restaurants, cafes etc.) which has 'name', 'address', 'logo' ... I also have products table with columns 'name', 'description', 'price' ... My question is how i am supposed to associate products and business. Should I store products inside of business table or add a new column named belongs_to which one represents for business id in products table? Or may I create a new table named business_products_relationships and add row like Business1, Product1, Business1, Product2 ... ?

[waxen finch]

as i understand it, usually you have three kinds of relationships which have different ways of being modelled in a relational database:

• one-to-one: one X is referenced only by one Y, and is normally represented by making Y have the same primary key as X

CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE user_currency (
    user_id INTEGER PRIMARY KEY REFERENCES user (id),
    wallet INTEGER,
    bank INTEGER
);
-- Triggers would be useful for keeping the user_currency table in sync with users

• one-to-many: one X can be referenced by many Ys, and is normally represented by a foreign key on Y

CREATE TABLE item (
    id INTEGER PRIMARY KEY,
    creator_id INTEGER REFERENCES user (id),
    -- One user can be the creator of many items
    name TEXT,
    description TEXT
);

• many-to-many: one X can be referenced by many Ys and one Y can be referenced by many Xs, and is normally represented with an association table

CREATE TABLE user_inventory (
    user_id INTEGER REFERENCES user (id),
    item_id INTEGER REFERENCES item (id),
    PRIMARY KEY (user_id, item_id)
    -- One user can have many items, and one item can be given to many users
    -- (assuming that these items don't have to be unique to a user)
);```
so in your case, you should first decide which relationship is best suited for your business products, and then apply the appropriate structure to model that relationship

[prisma oriole]

Thanks!

[coral wasp]

Python includes a very good free database: https://docs.python.org/3/library/sqlite3.html

And there are many free databases out there, including postgresql & mysql.

Databases speak SQL. You'll want to learn SQL too. The link above has a few examples.

[fading patrol]

GitHub isn't a database but suit yourself 😉

[nova owl]

i think barti_0110 meant some sort of cloud storage service

[torn sphinx]

noob question: if this pipeline uses snowflake to connect to to powerBI for visualization, what are some (free) alternatives that i should consider, if any??

[fading patrol]

You could do something similar with any DB (like Postgres, maybe even SQLite) but if you're following a tutorial for Snowflake it's going to be tough

[torn sphinx]

if you're following a tutorial for Snowflake it's going to be tough
what makes you say that ? (relative snowflake beginner here)

[fading patrol]

I'm not that familiar with Snowflake but it seems pretty different. It has a web interface. You're going to need to figure out how to do things in a different way

[torn sphinx]

indeed, i am following a tutorial hehe

[torn sphinx]

for example, why use Postgres over snowflake? just so i understand main principles

im aware it depends based on the project at hand but still

[torn sphinx]

ok so i am learning that as an alternative, i can host my postgresql databse on amazon redshift and then from there connect my PowerBI to it for visualization stage

[fading patrol]

You asked for a free alternative to Snowflake. Postgres can be free.

[fading patrol]

Redshift might not be a must, depends on exactly what you need

[wise goblet]

Postgresql is free and open source at its core engine.
U can still receive easier to use and maintain and scale solution as cloud databases like AWS RDS if u want (you aren't obligated to go full self hosted)
but your attachement to the cloud object in terms of code will remain... mm easily replaceable.
U will still have remained capability to migrate your solution to any cloud provider or even local infra.
Also due to popularity of postgres, there are a lot of mature solutions to interact with it easily

as open source most popular solution, community grew around it to support it as much as possible

in entire ecosystem of interconnected solutions

thus u will be able faster to write ready to use product

P.S. never heard about support for Snowflake in common frameworks. I suspect it is vendor only supported

[errant sinew]

yes as you said, Snowflake support connectors and drivers (JDBC,ODBC, Spark, Go

if need to go with a fully managed cloud platform and easy of use, can consider Snowflake

on other hand Postgresql we gain more control, customisation and also manage db infra

[strong pumice]

sqlite

json files 😂

[torn sphinx]

bruh 💀

[blissful narwhal]

Hellooo, this has been bothering me all day:
If I have a definition like this using sqlalchemy (this is from version 1.4, very sad)

class User(Base):
  __tablename__ = "users"
  id = Column(BigInteger, primary_key=True)

class Article(Base):
  __tablename__ = "articles"
  id = Column(BigInteger, primary_key=True)

  user_id = Column(BigInteger, ForeignKey(User.id))
  user = relationship(User, primaryjoin=user_id == User.id, lazy="joined")

Is there a convenient way to reference the articles that belong to a user without adding anything else to the models? i.e with syntax like user.articles? I know I can add a relationship with back_populates, but I am wondering if that is not necessary?

[paper flower]

I think you could use backref, but that's less obvious

Also sqlalchemy introduced a new syntax in 2.0 which you probably should use

class User(Base):
  __tablename__ = "users"

  id: Mapped[int] = mapped_column(BigInteger, primary_key=True)

[blissful narwhal]

Yeah, I saw backref as well. Someone was claiming that they believed the explicit relationship/backref/back_populates was not required for this, but they could not provide a reference and I couldnt find an alternative.

And yeah, ty. Sadly I am on an old version of sqlalchemy 😔 Though I will look at using the more modern syntax wherever possible

[paper flower]

Upgrade 🤨

difference between backref and back_populates is that backref creates property on linked model automatically, hence it's less explicit

[blissful narwhal]

I wish I could 😔 Sadly not always an option.

[blissful narwhal]

This is good to know, thank you!

[shut tiger]

I don't think you know what any of those words mean. Try to instead explain why you are asking the question, and we can see where we can help you understand.

[formal lintel]

does python support sqlite's WAL mode yet? i might be blind but i haven't found a sort of definitive answer yet

[ionic pecan]

wasn't that set through a pragma? if you run the pragma statement it should just do it?

[formal lintel]

yes it's set to a pragma, but afaik python vendors sqlite3 right? so i don't know if the version it comes with even supports it depending on the version

oh wait nvm, i see the wal file on this test run

[uncut glade]

hii

this q

In a table in MySQL database, an attribute A of datatype varchar(20) has the value "Karishma Mathur". The attribute B of datatype char(20) has the value "Meena Nigam". How many characters are occupied by attribute A and attribute B?
a) 14,10
b) 20,20
c) 15,20
d) None of the above

shouldn't it be none of the above

and uh there's 2 spaces b/w the two words of attribute B

[shut tiger]

The question is a bit weird. It sounds like it wants to ask about SQL, but then the question is "how long are these strings?" which has nothing to do with SQL

And if they're really asking about how much data is stored on disk? Well that's implementation dependent...

[uncut glade]

which tests a person's knowledge abt varchar and char right?

[uncut glade]

noo

[shut tiger]

no, it doesn't

[uncut glade]

huh

this is a 12th grade question

[shut tiger]

that means nothing to me

[lament parcel]

Think answer C is the correct answer, reason being of how much space in disk both data types occupy, (check the table here for more info https://dev.mysql.com/doc/refman/8.0/en/char.html)

[shut tiger]

c? how?

[uncut glade]

according to my book it says varchar takes 1 byte per character and +2 to hold variable length

b will be 20 agreed

how is a 15

shouldnt it be 17

[lament parcel]

you can check the link ive sent, it's in the table

[lament parcel]

https://stackoverflow.com/questions/5782126/mysql-fields-length-does-it-really-matter here it says it can sometimes take 1 byte for variable length, so that is just a best guess

[ionic pecan]

the question asks "how many characters are occupied" though, does character = byte?

otherwise I would just say it's the amount of chars in the strings

[shut tiger]

c says "15, 20". The question says "varchar(20)" twice

[uncut glade]

yes

[lament parcel]

isn't second one char(20) ?

[shut tiger]

"an attribute A of datatype varchar(20) has the value "Karishma Mathur". The attribute B of datatype char(20) has the value"

how do you get 15 from that?

None of the strings are 15 long, and none are 20 long

[lament parcel]

Again, the question is unclear as others mentioned, so as i mentioned before i assume they mean the space taken by that string when stored. If that is the case, varchar would only allocate the string length space which is 15, but char would allocate the whole 20.

[uncut glade]

A is "Karishma Mathur"
B is "Meena Nigam"

[shut tiger]

>>> len('Karishma Mathur')
15
>>> len('Meena  Nigam')
12
>>> 

how do you get 15, 20? not 15, 12 mind

[lament parcel]

because it's different data types, the sql engine allocates space differently

[shut tiger]

ahh, it's me that's confused. I read both as varchar 🤣 My bad. Carry on.

[lament parcel]

Hahah no prob, so @uncut glade if the question means space allocated then C is the closest

[shut tiger]

yea, I'd guess C, but I could strongly argue for none of the above.

[uncut glade]

i was taught to add +2 for varchar

💀

[shut tiger]

Assuming 2 bytes for string length? That's just 16 bits. Seems not super intuitive on modern machines to me...

[uncut glade]

two bytes for variable length information

[lament parcel]

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data.

This is from https://dev.mysql.com/doc/refman/8.0/en/char.html

[uncut glade]

so how do i know if i add 1 or 2

[lament parcel]

I would say logically speaking, you can represent numbers from 0 to 255 with 1 byte. so anything in that range should only occupy that

[uncut glade]

oki

thanks

[shut tiger]

0 to 254 more like it. But less, because part of that data needs to be reserved to know if the code must read the next byte for length. So I'd guess 0 to 127, in a naive implementation at least.

In any case: implementation dependent FOR SURE

[uncut glade]

oki

[lament parcel]

Indeed youre right about the first part, this was mentioned next to it in the docs

A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

[shut tiger]

As I remember, recent postgres versions made varchar potentially unbounded too

[misty token]

Hi! I have an assignment for a course, I have to do a small data analysis in Python and I need 2 databases but I don't know where to find them easily, could you help me?

[errant sinew]

kaggle , datahub

for datasets

[misty token]

thanks : )

[rocky spruce]

what isthe async sqlite that is commonly used on discord that formats like

async with (SOMESQLITE3MODULE).connect("PATHE") as Database
async with Database.cursor() as cur

[waxen finch]

either aiosqlite or asqlite

[rocky spruce]

ty

[rocky spruce]

i havent used it in a while but how do i close it

[waxen finch]

the connection? when it exits the async with statement it gets closed

[rocky spruce]

I thought there were some await (something) you need to do to close it off

[waxen finch]

only if you open the connection by awaiting it instead of using the context manager, i.e. py conn = await asqlite.connect(...) ... await conn.close() i recommend using async with though, it's easier to avoid accidentally leaving them open (including if an error occurs)

[rocky spruce]

is it await.commit

[rocky spruce]

oh ok

thank you

[waxen finch]

if you want to commit a transaction then sure, await conn.commit()
https://aiosqlite.omnilib.dev/en/stable/api.html#aiosqlite.Connection.commit
https://github.com/Rapptz/asqlite/blob/master/asqlite/__init__.py#L383-L385

[paper notch]

hello guys, when using pyqt and qt threads for serial data collection and processing..
what is the best way to also store the data locally? -- data of the serial device ~1KHz
any inputs are appreciated:) thanks

[wise goblet]

https://docs.pydantic.dev/latest/
Pydantic comes as data structs with optional easy serialization to json.
I think by default as easiest way to store the data locally i will offer using Pydantic with serialization to json i guess
it supports nested serialization in one command

There are some alternatives.. going pickle with any arbitary data structures, or going sqlite3
but within python capabilities i think Pydantic into json is best default way may be.

for some usage cases sqlite3 can be more preferable

[harsh pulsar]

pydantic for 1k req/sec? no thanks

sqlite would be great if it can keep up

otherwise you can do something like just append lines to a file and rotate files occasionally

[paper notch]

yeah that seems effecient

thanks a ton for answering guys

[paper notch]

currently im just writing the sampels into a CSV file. but i could write them into a text and have a sub process to dump themm into a db. or buffer the data and store them into csv in chunks. need an efficient and loss less way of doing it.

deciding to find the safest approach considering a threaded env

[wise goblet]

Oh. If 1k req/s in threaded... Pydantic is going to make a lot of memory and CPU noise.

Well... Sqlite3 if doing creating in bulk could keep up probably

Alternatively bufferization is good idea as well to write in chunks... Problem will be only that u will be somewhat limited in how efficiently allocate this buffer of memory in python (due to lack of python control for it)

Actually there is memory allocation in bulk in python. If u will use Numpy as far as I remember it is offered then

Alternatively pydantic could be still used, u will just need to make sure... Reusing existing objects probably (same for any other data structures)

Zeroing present values instead of creating new ones (🙈)

This way u can escape extra memory allocations and highly likely keeping up with necessary performance

At this point in order to escape extra dependencies, makes sense to use regular class though

List of regular classes
Allocated in advance to the size of a buffer
Write to them and keep track of index (index to keep track which part of array is already used)
Flush (write to disk)
continue with reusing same already present classes

This choice is cool I think because u can use power of Mypy/pyright for this code properly

And having zero extra deps

Optionally replace custom classes to Numpy for ability to allocate memory in bulk

[harsh pulsar]

you could do something like buffer a few thousand records at a time in a queue, and write them to sqlite in a separate thread

(this might also be a good opportunity to try asyncio instead of threads)

[paper notch]

makes sense i could chunk the data and push them into a queue and the thread that runs the data storage could safely wrte chunk by chunk i e indexes
making the buffer a circular so i can re-use the indexes , if im understanding correctly.
still unsure what is the ideal format of writing tho. json, csv, hdf5, any thoughts?

[paper notch]

i did try that it caused data loss for some reason.
asyncio has also been tried but at the moment, real time analysis can not escape the threaded env, might aswell use it lol

[wise goblet]

there is option to use binary format of pickle too 😊

[paper notch]

yeah that too!
need to play around with these things a bit to see the bets format to store data in real time

[wise goblet]

json requires extra serializing effort, but nice due to removing from your hustle regarding serialization/deserialization completely.
pickle... removes hustle for serializing deserializing too... but not sure if u will manage to use it in a memory efficient way since it abstracts those actions
csv is certainly fastest method, often used by data scientists to write down megabytes of data, but u are going to have a lot of manual stuff
not familiar with hdf5.

I will add to this equation option to go besides sqlite3, to go duckdb https://duckdb.org/ too
as person with "The Duck" in my name i am obligated to mention it 😅
Sqlite3 with extra features pretty much, including imports and exports to different formats

[paper notch]

while we are at it,
is there anything inherently wrong with writing 1Khz readings samples by sample into a CSV file kept open? sounds silly saying out loud but would like to know the details

[paper notch]

i would believe if you said it is your org xD

but thats very useful info thanks for that, i will look into it

[wise goblet]

CSV file kept open?
i am linux user, probably could easy find answer for it...
..but i have a feeling your end users aren't going to be linux users 😅
it looks like very OS depended final answer
Since every OS manages file accesses in their own fashion

[paper notch]

makes sense, as per the end users that is not the primary concern now, the 1khz that im reading in real time is supposed to be un/least interrupted and stored into the machine as this data flow is time-dependent for real-time analysis application as that is the primary task

[paper notch]

ah agreed, windows timer is shit aswell. will try a couple of these. especially the chunk by storage in a separate thread with binary pickling or json.
havent heard about mypy/ pyright, i will have a look about them aswell

[wise goblet]

strict mypy or strict pyright are cool in enforcing static types across your code in the way similar to C#/Java/Golang code.
You can just easier... match your code to be correct one since... it is within one second validated to match correctly functions/classes/variables inputs outputs between code parts.
makes data... transformations at entirely different level.

[wise goblet]

https://docs.python.org/3/library/typing.html
As extra features it allows u to go Domain Driven Design at a full capacity, since your variables could be having very descriptive type across all your code

from typing import NewType
AWSDatabaseARN = AWSDatabaseARN("AWSDatabaseARN", str)

arn: AWSDatabaseARN = AWSDatabaseARN("database arn")

that increases self documentation to your code, since every function/class variable types will have this new custom type defined on top of simple type

Also... mypy enforces abstract clases and interfaces... and just allows u to write C#/Java/Golang styled code in general if desired, because safe net of typing works.

[harsh pulsar]

nothing wrong with this. in fact i think it's probably the best option

but yeah, that's a linux-specific assumption as duck said above

[gentle zinc]

could someone explain the cartesian product of two tables for me? I dont quite understand the concept

[untold stump]

hi

any one know how to send/resv data between devices with socket like one computer in a city and second computer is in another city not in the same wi fi

[gentle zinc]

here's the problem

[coral wasp]

If you have 5 shirt and 5 pants, how many combinations can you make?

[gentle zinc]

5

[coral wasp]

Nope. I said how many combinations can you make, not how many you can make at the same time.

[gentle zinc]

25

[coral wasp]

Right. 25 is a Cartesian product.

[gentle zinc]

mhm

erm but if i do recall we have to do something with the degree and cardinality of the two tables

I believe you helped me last time as well haha i recognize your name

i took that exam a year ago though

so i unfortunately forgot :/

[coral wasp]

Hah, cool

[gentle zinc]

i understand grouping/joins though!

thanks to you

do you remember? :p

[coral wasp]

5 vs 25 is important: sometimes 5 is what you want, and sometimes 25

Not exactly but that makes me happy!

[gentle zinc]

erm so what do i write if it does appear on my exam tomorrow

[coral wasp]

Depends on the question. A Cartesian product is simply everything in set 1 connected to everything in set 2

So if both sets have 1000 members, then the result is a million combinations

[gentle zinc]

ah alright

oh also

"____e%" does this return statements where the 5th letter is e or the 4th letter? do we include the e?

[coral wasp]

That you should test on your own, that’s easy to test: select ‘abcdef’ like ‘____e%’

[gentle zinc]

here's the problem btw ! also i hope im not being a pest but could you give me the answer to c?

[gentle zinc]

dont have any databases in hand rn + mysql server somehow broke :p

ill fix it though!

[coral wasp]

https://dbfiddle.uk

[gentle zinc]

ooh

thats actually really handy thank you so much!

[coral wasp]

Yup, it’s hard to learn anything without trying it hands on

[gentle zinc]

ah so it includes it

😮

[torn sphinx]

can anyone help

i cant fix

around 3 hours

idk what to do

[fervent lotus]

Hi is anyone good at implementing sqlite db in python flask🥲 ?

[formal lintel]

ask your question directly.

[fervent lotus]

okok

so how do i start using sqlite for my flask project?

like how do i create a table and then store information from a form?

[fading patrol]

There are different ways.

The docs mention that you can use a schema.sql file: https://flask.palletsprojects.com/en/3.0.x/patterns/sqlite3/

Another approach is with an ORM like SQL Alchemy: https://geekpython.in/connect-sqlite-database-with-flask-app

[fervent lotus]

omg thanks so muc

appreciate the helppppp

[harsh pulsar]

Error message is very clear. Whatever file you selected for the database, that file doesn't exist

[harsh pulsar]

At the very simplest level, you create a table by running an SQL command. then you can insert data into that table, and query for data out of the table, using other SQL commands.

usually people create tables in a separate script, and then they can insert or query as needed from inside their application

Often scripts to set up or modify the database are called "migration" scripts, because they help you "migrate" the data from one version of the database to another

[torn sphinx]

there is a file

but its it 0bytes

[fading patrol]

File/folder permissions issue?

[torn sphinx]

i dont think so

i'll acces via administrator

[harsh pulsar]

it's possible that you accidentally overwrote it

if you write open("myapp.db", "w") it will truncate the file. maybe you did that?

[rocky spruce]

how to download aiosqlite

[torn sphinx]

im using instagram reels scraper and poster from github

everyone's code works fine but mine is gives this error

[north yew]

does anyone know why access keeps telling me I am entering an invalid value for a field name, even though I am entering correct values?

[dense sage]

Does anyone want to practice data analysis?

[round stag]

Hello, I am trying to install Hadoop 3.3.6 on Windows 11, its my first time isntalling it, can someone recommend any issues I should take care of and where can I get the Hadoop native io libraries needed for Hadoop?

[rocky spruce]

Traceback (most recent call last):
  File "C:\Users\Naseer\AppData\Local\Programs\Python\Python312\Lib\site-packages\discord\client.py", line 441, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\Naseer\Desktop\That one server\GameLoop.py", line 35, in on_message
    await c.execute("SELECT * FROM status WHERE id = ? ", (message.author.id))
  File "C:\Users\Naseer\AppData\Local\Programs\Python\Python312\Lib\site-packages\aiosqlite\cursor.py", line 48, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\Naseer\AppData\Local\Programs\Python\Python312\Lib\site-packages\aiosqlite\cursor.py", line 40, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Naseer\AppData\Local\Programs\Python\Python312\Lib\site-packages\aiosqlite\core.py", line 133, in _execute
    return await future
           ^^^^^^^^^^^^
  File "C:\Users\Naseer\AppData\Local\Programs\Python\Python312\Lib\site-packages\aiosqlite\core.py", line 106, in run
    result = function()
             ^^^^^^^^^^
sqlite3.ProgrammingError: parameters are of unsupported type

[grim vault]

The second parameter of execute must be an iterable (list, tuple). You're missing a , to make it a tuple:

  await c.execute("SELECT * FROM status WHERE id = ? ", (message.author.id,))
                                                                          ^

[normal dove]

can anyone help me? what am I doing wrong here?

[deep jetty]

You're already connected
psql is how you connect to what you're already connected to

[normal dove]

i want to connect to the test database

am I already connected to that?

[deep jetty]

The name of the database is your prompt

[normal dove]

is psql present ?

[deep jetty]

What?

[normal dove]

what should I write to connnect to the "test" database

[deep jetty]

\l to list your databases, and \c <database-name> to change which one you're connected to

https://www.atlassian.com/data/admin/how-to-list-databases-and-tables-in-postgresql-using-psql

[normal dove]

ohh ok ok thankyou

[dense sage]

anyone want ot practice data analysis on python (pandas, numpy, matplotlib, seaborn)?

[ripe viper]

hey does anyone here have a statista account ? pls help me download a dataset

[swift chasm]

hey ! i'd have a little query performance question :
I need to compare each column value with an aggregate of the whole column, to do that i do

column > agg(column) over (order by column rows between unbounded preceding and unbounded following)

it works like a charm but i was wondering if the aggregation is calculated for each row or only once for the whole query (since it'll always return the same thing) ?
I know that when we do a rolling window using similar syntax, it'll calculate the window for each row, but here since i use unbounded both ways, i wonder if the SQL engines optimize it or no ?

(my use-case is outlier detection, so i use standard deviations and averages as my aggregation functions)

[gray whale]

Hey guys, I'm having problems with accessing an sql database using the mysql-connector-library and a nice gui with tkinter. If anyone is willing to help me I'd gladly appreciate it. Preferably in a VC

[sinful swan]

hey guys, iam having a problem to integrate a GUI with a db, this is the application code:
from gui import *
import backend as core

app = None

def view_command():
rows = core.view()
app.listClientes.delete(0,END)
for r in rows:
app.listClientes.insert(END,r)

def search_command():
app.listClientes.delete(0,END)
rows = core.search(app.txtNome.get(), app.txtSobrenome.get(),app.txtEmail.get(),app.txtCPF.get())
for r in rows:
app.listClientes.insert(END,r)

def insert_command():
core.insert(app.txtNome.get(),app.txtSobrenome.get(),app.txtEmail.get(),app.txtCPF.get())
view_command()

def update_command():
core.update(selected[0],app.txtNome.get(),app.txtSobrenome.get(),app.txtEmail.get(),app.txtCPF.get())
view_command()

def del_command():
global selected
id = selected[0]
core.delete(id)
view_command()

def getSelectedRow(event):
global selected
index = app.listClientes.curselection()[0]
selected = app.listClientes.get(index)
app.entNome.delete(0,END)
app.entNome.insert(END, selected[1])
app.entSobrenome.delete(0,END)
app.entSobrenome.insert(END,selected[2])
app.entEmail.delete(0,END)
app.entEmail.insert(END,selected[3])
app.entCPF.delete(0,END)
app.entCPF.insert(END,selected[4])
return selected

if name == "main":
app = Gui()
app.listClientes.bind('<<ListBoxSelect>>',getSelectedRow)

app.btnViewAll.configure(command=view_command)
app.btnBuscar.configure(command=search_command)
app.btnInserir.configure(command=insert_command)
app.btnUpdate.configure(command=update_command)
app.btnDel.configure(command=del_command)
app.btnClose.configure(command=app.window.destroy)
app.run()

iam getting this message: NameError: name 'selected' is not defined

can someone please help me

iam using tkinter for the GUI

[coral wasp]

It’s a good question but not really a db question. Could you open a #❓｜how-to-get-help help thread with this code?

[torn sphinx]

i need some data pipeline consulting help: whats a relatively straightforward pipeline to ingest data (database) and then put it out on a dashboard? if you need more clarification. please ask

• (relatively cheap, bonus if free)

[storm mauve]

there are literally hundreds of ways to do that, depending on the volume of data, the type of database, which sort of interactions you need in the dashboard, and which level of control you need

[torn sphinx]

for reference, the volume of data is youtube uploads from a channel (i.e. text data)

[torn sphinx]

what tools would you approach each major step from this pipeline?

[fading patrol]

There are still endless ways to do this. Quick and dirty I would do everything right on my local machine with local files instead of object storage, and sqlite for the DB. For the dashboard at the end I would probably use Django. If you need more scale and complexity you could involve a DAG like Airflow, container orchestration like Kubernetes and who knows what else.

[wise goblet]

if i am restricted to Python only?
Then probably:

1. Celery task: from API to bucket (Celery Task Delay or Celery Beat depending on need)
   First step as last thing could be scheduling task 2
2. Celery task: From bucket to Postgres/Sqlite3
   P.S. step 1 and 2 can be merged into together stuff if desired
3. Viewing? 🤔 Ergh... I hate Django but i guess it will be Django if in python 😅
   P.S i am mostly not happy with Django Templating / Jinja Templating for this in Django. It would be nice using something better for templating
   https://wiki.python.org/moin/Templating stuff to search
   Probably easiest to go with Django Templating 🙈 https://docs.djangoproject.com/en/5.0/topics/templates/
   In order to be not adding too much dependencies

[wise goblet]

Optionally this stuff could be made serveless.
But i am more fan of approach writing application launchable locally
easier to debug and develop 🤔

[shut tiger]

just a little db, and Urd for scheduling

Honestly the entire graph looks like it should just be one function call?

[craggy swallow]

what's a good app/site for drawing schemas/er diagrams?

[fading patrol]

Endless options, use what you like. https://chartio.com/learn/databases/7-free-database-diagramming-tools-for-busy-data-folks/

[craggy swallow]

yeah, I stumbled upon that link myself before asking. That's the thing, I don't know what I like because I haven't used any. That's kinda the point of asking.. up until now I've been using Database Designer on android, but want smth I could use on PC. Everything on that site looks pretty meh. Currently I'm looking at drawsql and dbdiagram, but.. One would assume, considering how long dbs exist, there would be a convenient, entirely free, app/site that allows exporting the schema to sql (for postgres, mysql, etc) and back.. No way there isn't smth more popular and commonly used (by geeky forum db experts, for example) than what I'm getting with ads filled google searches

[fading patrol]

I've never seen such a bidirectional tool. The diagrams generated by DBeaver are ok I guess

[shut tiger]

For Django I'd recommend not. Just write your models and use the migration tool as intended (commit the migrations!!). Pretend it's all python.

[craggy swallow]

did u respond to the correct message? Cus i was just asking for a tool to draw schemas 🤔😂

[shut tiger]

Yea. And I was answering that. Especially about exporting and importing. That's not a good idea in the Django context. You want Django to manage the state of the schema 100%.

[craggy swallow]

Oh, I see. I don't really use Django. I usually design my db schema myself. Currently working on a discord bot

[shut tiger]

What's the stack you are targeting?

[craggy swallow]

I'm still deciding actually. Can't even make my mind up on whether I should use relational (postgres) or nosql (mongo)

[shut tiger]

Well.. if it's mongo then the tool question is answered: there is no structure, it's all chaos, and you will suffer 🤣

[craggy swallow]

Yeah, ik lol. I wanted to first try making a schema, to then be able to think about the possible performance advantages/disadvantages of using such a schema vs nosql

[shut tiger]

Performance is imo something people worry a lot about and then it's mostly irrelevant. Having consistent data is something people don't worry about and it hurts them an enormous amount even IF they worry about it if there is any deviation.

I see people in here thinking about "what if I get a billion users?! I need microservices!!" and in actual fact they have zero users. Like... worry about the entire project failing first!

[craggy swallow]

I'm actually more comfortable with postgres myself. The reason I'm thinking about this, is cus I'll be storing a number of variables for each user, and the number of variables is not fixed (it's unknown, can change, and may differ between users). This means that if I go with relational, I will most definitely have a decent number of joins. And yeah, I may be overthinking the performance issue, but I'd still like to do it the "right way", or at least be relatively confident in my decision

[craggy swallow]

Yeah, I get that, lol. But if this project does make it into production, it's guaranteed to have at least several hundred thousand users, as it's meant to be a one-in-all replacement for an existing number of bots in some relatively large servers

[shut tiger]

Bots that you don't have the code for?

[craggy swallow]

Yup, exactly correct 🥲

[shut tiger]

Have you successfully deployed a bot into production before?

[craggy swallow]

Smaller projects yes, like a bot that scrapes a site and uploads the contents regularly (for a different platform, not discord). Nothing at this scale

[shut tiger]

I'd recommend just starting and focusing on replacing one bot. You should be able to tell if you should throw away that and start over. And it's much faster and you learn more by doing than by planning ahead. And since this is programming and not architecture, that's also cheap, fast and non-dangerous.

[craggy swallow]

will think about it, come back later with some questions, and then get to it

[shut tiger]

Don't think, do :P

[craggy swallow]

The difficulty in that is intertwined with my personality. I'm aware of the drawbacks, but despite that, I prefer waterfall over agile methodology 🙃

[shut tiger]

Well that just doesn't work :P Like people who are visual learners who need to learn to ride a bike: it's irrelevant. They need to actually do it. Watching others ride bikes will help exactly zero.

[craggy swallow]

How tho does one explain to their boss that they've been doing smth in a suboptimal way and now needs to spend an entire week (or more) switching things around?

[shut tiger]

If the boss doesn't listen then you are totally screwed no matter what.

But the REAL answer is: "We are 4 months earlier to market because I started directly, and now you complain about one week? Please" :P

Another real answer is: "I am not a time traveller"

[karmic hearth]

hey people I am confused - I am reading about the active record pattern, and finding it to be identical to the data wrapper pattern. The crux of my confusion is easily exemplified by a simple update operation: everything I read seems to indicate that operations on the in-memory representation of active records update those records synchronously, instead of waiting until the in-memory construct has done with all it's mutations and is turned into another database transaction with an insert. However, after the operations are done on an in-memory active record, save is called, which to me seems identical to calling insert on a data wrapper object.
in all the examples I can find, they end with calling freaking save after a bunch of nonsense about how it's an active record. I don't fully get it. Are any of the mutations to the object applied to the database before save? and if not, what is the essential distinction between data mapper-pattern frameworks (like Ecto) and those like Django ORM (which is apparently active record)?

[wary plover]

does it come down to them being differences in approaches and what is responsible for consistency?

and their differences probably lend more to different use cases

[exotic drift]

hello!! i'm very new to programming so please be understanding if i made a silly mistake. I was following Corey Schafer's tutorial on using the Flask framework in python to create an app. I've had no issues that I couldn't resolve by myself until I reached the video talking ab databases....when i try to run the program using flask run it comes up with this error ``` Flask_Blog % flask run
Usage: flask run [OPTIONS]
Try 'flask run --help' for help.

Error: While importing 'Flask_Blog.flaskblog', an ImportError was raised:

Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/flask/cli.py", line 219, in locate_app
import(module_name)
File "/Users/fiwoe/Flask_Blog/flaskblog.py", line 4, in <module>
from forms import RegistrationForm, LoginForm
ModuleNotFoundError: No module named 'forms'```

but the thing is that i have a module named forms. this code was working like a week back and i got busy with other things and then i returned to this error..?

if you need other info to help me, i'll try to provide it ^^

[shut tiger]

What is your current working directory?

[wind grove]

Which of these would you say is better to use?

# Option 1
conn = sqlite3.connect(database)
cursor = conn.cursor()
cursor.execute(query)
cursor.close()
conn.close()

# Option 2
from contextlib import closing
with closing(sqlite3.connect(database)) as conn:
    with closing(conn.cursor()) as cursor:
        cursor.execute(query)```

I only recently learn that the sqlite3 context manager with sqlite3.connect(database) doesn't handle closing like I would have thought... is that common among sql libraries to provide context managers that only handles commit and not close?

[paper flower]

Obviously using a context manager is better for this 🤔

[storm mauve]

in the case of sqlite3 in particular, it probably doesn't matters that much whenever you actually close or not, as long as you commit?
the entire database process is running inside of the python process, so once python exists, there is nothing left to clean up, and I assume it only modifies the file when you actually commit
edit; looks like technically there can be a pending transaction to clean up? the default context manager takes care of that much though

I'd still rather using a context manager, but closing() should be unnecessary

[paper flower]

I personally meant using a context manager in general here, closing is one

Since default __exit__ doesn't behave as they want it to

[wind grove]

Even if I opening/closing the database elsewhere in the program? I'm not sure I'd want to open it as a global. Combining what I had before with the committing context manager, it should then look like this:

    with closing(sqlite3.connect(database)) as conn:
        with conn:
            with closing(conn.cursor()) as cursor:```
That is a lot of additional indentation towering.

[neat moat]

SQL Question:
If you are presented with a 2 to Many relation ship, what primary/foreign keys do you need and how would you implement that. (Question from a school exam, we are working in Microsoft Access, so please don't get too deep into SQL. My teacher told me its the same way you would implement a 1 to Many Relation -> Foreign key on the Many Side of the relation

[storm mauve]

usually I would prefer to only create the connection once, and just pass it around to the others. Depending on what you are doing, that could be done via dependency injection

but I guess that it could make sense to explicitly close if you want to open multiple times, even if just for peace of mind

[fading patrol]

No, I don't see how that would work... If it's more than one to many, that's many to many.

[neat moat]

I thought the same thing. I guess I will have to argue with my teacher now...

[grim vault]

Maybe the teacher means two one to many to the same parent table?

parent       child
------       -----
id (PK) <-+- parent_id1 (FK parent.id)
          +- parent_id2 (FK parent.id)