databases

[paper flower]

If it's single player you don't really need to use max here

Why not simply order by score?

That's what i was asking 😅

So it's like a game session then?

e.g. i play game A two times, two Game records are created

Mhm, could you remind me what you want to select? 😅

Got you

Well, i'm not sure what sqla does under the hood but

stmt = (
    select(Game, func.max(Game.score))
    .group_by(Game.user_id)
    .limit(10)
)

selects max(Game.score) into score field 🤨

from sqlalchemy import create_engine, Integer, Column, String, TIMESTAMP, CheckConstraint, ForeignKey, select,func
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from sqlalchemy.sql.functions import current_timestamp
Base = declarative_base()
engine = create_engine("sqlite://", echo=True)
Session = sessionmaker(bind=engine, future=True)

class User(Base):
    """User model for database"""
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False, index=True)

    games = relationship(
        "Game",
        backref="user"
    )

class Game(Base):
    """Game model for database"""
    __tablename__ = "games"

    id = Column(Integer, primary_key=True)
    score = Column(Integer, CheckConstraint("score >= 0"))
    user_id = Column(Integer, ForeignKey("users.id"))
    date = Column(TIMESTAMP, default=current_timestamp())

    def __repr__(self):
        return f"Game(id={self.id}, score={self.score}, user_id={self.user_id})"

def main():
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)

    user_1 = User(username="Doctor")
    user_2 = User(username="eval at parse time")
    with Session.begin() as session:
        session.add(Game(score=2, user=user_1))
        session.add(Game(score=1, user=user_1))
        session.add(Game(score=2, user=user_2))
        session.add(Game(score=1, user=user_2))

    stmt = (
        select(Game, func.max(Game.score))
        .group_by(Game.user_id)
        .limit(10)
    )
    with Session() as session:
        result = list(session.scalars(stmt))
        print(result)

if __name__ == "__main__":
    main()

Should run as-is

[paper flower]

@slow cove Btw you don't have to use index with primary_key, it should be already indexed by db

[torn sphinx]

how do i do that? could u recommend a tutorial or something?

[paper flower]

You should look into web/rest api's ig

[devout olive]

hi

to be able to select average distance by car type

do I have t write two left joins?

or this would be enough

query='''
        SELECT AVG(trip_distance),car_type
        FROM trips
        LEFT JOIN cars ON service.service_id=service.service_car
        '''

query='''
        SELECT AVG(trip_distance),car_type
        FROM trips
        LEFT JOIN service ON trips.trip_service=service.service_id
        LEFT JOIN cars ON service.service_car=cars.car_id
        '''```

which one would work?

I don't have the database so I can't try unless I write a sample database and try it :/

[dapper eagle]

Does anyone know how to connect database with SQL?

[paper flower]

I don't think this would work since you didn't join service table with trips 🤔, second one should work i think

You probably have to group by car type too? if you want to select average trip distance per car type

[nova cove]

What type of sql database

[brave fossil]

Hi

Me and my friend thought to host a discord bot which was disconnected few months ago
So we asked the devs for the code and we got it but we ran into an huge error of db mongo uri , it needs 4 uri's but we do not know it how to solve as we are new to mongo things
please help us brothers

[fluid glen]

Would y'all know how to change whats in the column of a table after its been created?

I'm using sqlite3

I can't find information online.

[wooden condor]

SQL databases generally use UPDATE or INSERT statements. Can you be more specific about what you're trying to do with the table?

[fluid glen]

Alright, so the column I'm trying to have updated is named "deliverystatus"

And so I'm trying to get it so the one column can be changed into whatever the updated status is

[wooden condor]

so the SQL would look something like:
UPDATE tablename SET deliverystatus = 1 WHERE rowid = 123456;
but you are likely using some kind of python tool that could make that easier.

https://www.w3schools.com/sql/sql_intro.asp

[torn sphinx]

Hi everyone! I'm taking a database implementation course in university and would love to try and teach some of the material to somebody else to get a better understanding of the material.

[fluid glen]

~ Thank you :D

[fluid glen]

How would I have the SET DELIVERYSTATUS = 1, bit of that be an argument?

I'm getting an error message o.o

cur.execute("UPDATE orders SET deliverystat = (?) WHERE = (?)", (str(msg), (str(ordernum))))

is there something wrong I did with the arguments here?

[grim vault]

You are missing the column name in the where clause.

cur.execute("UPDATE orders SET deliverystat = ? WHERE ordernum = ?", (msg, ordernum))```

[fluid glen]

ayy thank you :)

[stark sparrow]

Hello, I have a PostgresSQL database with a table called economy. In that table, I have a few columns. One of them is times_worked. How can I make it so that all the data in the times_worked column gets set to 0? Please ping me when you reply. Thanks

[wooden condor]

@stark sparrow similar to the previous question-- UPDATE economy SET times_worked=0 but many SQL engines might throw an error/warning without a WHERE clauses, so you can also add WHERE 1=1 to explicitly state you'd like to update all rows.

[zealous rain]

Hi I need help with project structure and database relations

I'm using flask and flask-sqlalchemy

[random widget]

I'm using replit db and I made a code so every time i do ,quit Test it will leave the given server e.g. in that example it would leave Test server, all of the code seems to work I've tried it with all my keys but for some reason one of them it doesn't want to work can anyone help?

[left igloo]

yeah, some engines might suggest an error, but that is correct

[torn sphinx]

could u share a tutorial pls @paper flower

[stark sparrow]

i'll try it out during morning

[sterile pelican]

How to add item to an array in postgres

Or I overwrite the array

With SET

[hazy mango]

See 8.14.4 Modifying Arrays on the docs: https://www.postgresql.org/docs/9.1/arrays.html

Tl;dr; you overwrite array like this:

[median wave]

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.```
This error is raised from
```py
async def get_member_from_channel(channel_id):
    async with bot.db.execute("SELECT dm_channel from testing WHERE modmail_channel = ?", (channel_id,)) as cursor:
        data = await cursor.fetchone()
        if not data:
            return False
        return data[0]```

My database is

[hazy mango]

modmail_channel is of type TEXT (string) but ids in dpy are ints (INTEGER)

So my guess would be you need to either update the database to have all the id fields as INTEGER (the better solution) or pass (str(channel_id),) (crude solution)

[median wave]

I changed the data types to

and it's raising sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

lemme see which line

[hazy mango]

Make sure you saved the db (ctrl+s on the db browser) and re-established the connection (i.e. restart the python program)

[median wave]

oh okay i forgot to restart the bot

[hazy mango]

If that doesn't work, then you need to show what channel_id is (the value you're passing into it)

[median wave]

yeah it's raising
File "c:\Users\tenuk\Desktop\Coding\ModMail\modmail.py", line 40, in get_member_from_channel
async with bot.db.execute("SELECT dm_channel from testing WHERE modmail_channel = ?", (channel_id,)) as cursor:

I'm passing

await bot.db.execute("INSERT INTO testing (dm_channel, modmail_channel, member_name, member_id) VALUES(?, ?, ?, ?)", (message.author.id, channel_to_send.id, str(message.author), message.author.id))
await bot.db.commit()

[frosty halo]

Hi guys ```py
select NOW() - betinittime as difference, * from full_bets
where horse IN (SELECT horse from full_bets
where NOW()::timestamp - betinittime < '00:16:47.354828')
order by difference asc

which is returning this series ```
"00:18:44.103957"
"00:19:13.760957"
"00:39:34.016957"
"00:39:34.176957"
"00:39:34.410957"
"00:39:34.440957"

I'm trying to return only those rows where the difference between NOW() and betinittime is less than 00:15:00

sorry @median wave I misread the timestamps in your message. Didn't mean to intrude.

[median wave]

no worries

[hazy mango]

Not quite sure why you're passing message.author.id for dm_channel

Trypy await bot.db.execute("INSERT INTO testing VALUES(?, ?, ?, ?)", (message.channel.id, channel_to_send.id, str(message.author), message.author.id))

[median wave]

my variable names are probably bad
dm_channel is the author of the message

it's a modmail bot, the dm_channel is who I should send the message to. I should probably change the variable name

[hazy mango]

I should probably change the variable name
Yes, you should lol

[median wave]

yeah...anyways any idea why the error is raised?

[hazy mango]

Maybe try adding parenthesis around the subtraction, not sure though

[median wave]

i restarted the bot and saved the changes

[hazy mango]

The error means it's not getting the expected type

So just check the types ig 🤷

[median wave]

but

<class 'int'>
<class 'int'>
<class 'str'>
<class 'int'>

 print(type(message.author.id))
print(type(channel_to_send.id))
print(type(str(message.author.id)))
print(type(message.author.id))
                
#await bot.db.execute("INSERT INTO testing (dm_channel, modmail_channel, member_name, member_id) VALUES(?, ?, ?, ?)", (message.author.id, channel_to_send.id, str(message.author), message.author.id))

i have no clue now

wait, let me check something

when passing a int, do i need , after the var?

[worthy sapphire]

guys does anyone know why im getting this error? this is MongoDB

[topaz glen]

bruh im trying to make a new server in postgres

why tf is it telling me the connection is not found

what the fuck is that suppose to mean

[lilac glen]

Oh yeah so pretty much

[worthy sapphire]

i fixed it

[torn sphinx]

I want to add a constriant to my sqlite table that limits the amount of rows stored in a table with the same value in the "Name" column to 25. how do I do this?

[paper flower]

I don't think that's possible currently 🤔 Maybe your DBMS supports that, why not check on application layer btw?

[woeful torrent]

Is there a more efficient way to handle pagination with sqlalchemy than just using offset and limit?

What do I have to keep in mind for queries with an offset to be as efficient as possible?

[jovial nymph]

how can i prevent sql injection in python

[fast wolf]

Sqlite question: I would like to insert a row of data but if there is the same value (user id) already present then just update that row and set the collumn 2 to x

[nova cove]

https://realpython.com/prevent-python-sql-injection/

[jovial nymph]

thx

[nova cove]

np

[torn sphinx]

Any sql help?

I have these tables.

Users 
- id
- email

Articles
- id
- title

Article_Access
- user_id
- article_id 

By default all users have access to all articles. Some user accounts have access to specific articles only, and this is stored in article_access.

Given an article, I am trying to get a list of all the users and a column with true/false whether they have access to the article or not.

[paper flower]

@torn sphinx How do you determine if user has access only to specific articles?

[torn sphinx]

If it is in the article_access table

[paper flower]

That's kind of a bad way of determining if article is public or not imo

[torn sphinx]

How so?

[paper flower]

It's ambiguous, i'd assume if there's no entries in article_access no one should have access to that article

[torn sphinx]

That is because you have seen the domain knowledge first time, but it makes total sense. If i wanted to create an article but only give access to X users then this is the best way to do so.

[paper flower]

I'd add is_public flag to article 😉

[torn sphinx]

That is not the case here.

This is for granting access to specific users.

[paper flower]

Well, then why would articles without associated access objects would be public?

[torn sphinx]

Access control for granular user by user permissions.

[paper flower]

I'm familair with abac and rbac, i'm asking why would article with no access objects would be considered public? That does not make sense imo.

[torn sphinx]

By default articles are public. They are accessible by all users. If you want to then update the article so it only accessible by lets say 3 users, then you would add those records somewhere.

[paper flower]

I think if you have article but no entries in article_access associated with it it should be completely private?

Otherwise when creating article it would be puiblic

Check itself is not that complicated:

if article.is_public:
    return True
stmt = "select * from article_access where user_id = :user_id and article_id = :article_id"
return execute_sql_somehow(stmt) is not None

But if they're public by default (i.e. you don't have that flag) you'd probably have to write additional sql

[torn sphinx]

I need a list of all the users, and for each user to say if they have access to the article or not.

[paper flower]

Why do you need to fetch that info for all users though? 🤔

Just asking

[torn sphinx]

Just to make it simple when asking the question

In actual i will limit to x amount

[paper flower]

You usually work with request associated with a specific user, perhaps you want to check who has access to your article?

[torn sphinx]

It is for reporting

[paper flower]

But still for a specific article, right?

[torn sphinx]

one or more articles

[paper flower]

Something like this should work 🙂

select * from users
join articles_access on articles_access.user_id = users.id

You'd have to filter by your article id too

[olive reef]

Is there a way of automatically setting to a primary key a value?

[paper flower]

Yes, it depend on the type of your pk

[olive reef]

say for example everytime a row is added, the id goes up for one?

[paper flower]

postgres has serial type:
id serial primary key

Other databases should have similar way of defining integer ids

[olive reef]

I use sqlite3

[paper flower]

https://www.sqlite.org/autoinc.html

[olive reef]

What i did was id PRIMARY KEY

[fast wolf]

i tried doing:

await cur.execute("INSERT INTO points (points, userid) VALUES (?,?) ON DUPLICATE KEY UPDATE points = points", 0, ctx.author.id)

but i got: near "DUPLICATE": syntax error
(and yes, the userid has an unique constraint)

[paper flower]

I think it should be on conflict(id) in sqlite

[fast wolf]

await cur.execute("INSERT INTO points (points, userid) VALUES (?,?) ON CONFLICT(userid) UPDATE points = points", 0, ctx.author.id)

so like this?

[paper flower]

I think so, try it

but ON CONFLICT(userid) DO UPDATE

[fast wolf]

ah yes

now i tried ```py
await cur.execute("INSERT INTO points (points, userid) VALUES (?,?) ON CONFLICT(userid) DO UPDATE points SET points = points WHERE userid = ?", 0, ctx.author.id, ctx.author.id)

but for near "points": syntax error (also tried just DO UPDATE points = points but same error)

[paper flower]

I think you should reference the same row when using on conflict

INSERT INTO points (points, userid) VALUES (?,?) ON CONFLICT(userid) DO UPDATE SET points = points

[fast wolf]

i tried before that but it gave the same error

[hardy crown]

Helloo, i'm new in the django world and i want to create an app like instagram in the sense that each user can only upload the same type of information. Could you provide me with some tutorials on how to upload and access this information for each user in django?

[hearty nacelle]

#python-community message

[olive reef]

Oh wait so intead of doing

CREATE TABLE days (
  id PRIMARY KEY
)

PRIMARY KEY should be ROWID?

[paper flower]

I think just having id integer primary key should work:

create table users (
    id integer primary key, 
    username varchar(50) not null
);
insert into users (username) values ('Username');
select * from users;

1 Username

[harsh zealot]

Display the track name, genre name, and mediatype name for each track in the database would anyone be able to help me with this?

[fleet ibex]

hint.. the question is practically an sql statement

[lucid obsidian]

Ok so I need database advice

I need to be able to store small strings, large strings, and link several strings to one another. I'd like to be able to handle up to a few trillion total entries as quickly as possible. What's a good DB to use?

[brave bridge]

Triliions of strings will not fit on one computer. Do you have any particular distributed solution in mind?

What kind of "linking" do you need?

and why do you need this in the first place?

[lucid obsidian]

making a chat website

maybe trillions is the wrong number 😛

[brave bridge]

now that's a very different problem statement than strings linked to each other 😄

[lucid obsidian]

Mostly I want to be able to look up room IDs to find messages, user IDs by username to find user ID & PW salt/hash, etc

[brave bridge]

what number of messages do you expect, realistically?

[lucid obsidian]

at first, a few thousand over several hours

later, something on the scale of discord

[brave bridge]

Well, if you want to build something on the scale of discord, you'll need a team of developers familiar with distributed systems 🙂

[lucid obsidian]

mhm

that's (going to be) me

and anyone who contributes

I was thinking that for data distribution, I would have a method to look up which server/disk has what ID and fetch it from there. Sort of like DNS but for the DB.

And I could totally handle user IDs, room IDs, etc the same way (rooms would have their ID's prefixed with r, users with u, etc)

[balmy folio]

I have a DB for a client where there are images stored as what I believe to be binary data?

My question is... if I have django+djangorestframework, how do I ultimately show the images? My hunch is I should convert the binary data in my serializers to say, base64, then pass the base64 strings as a json response for the front end to deal with maybe?

[brave bridge]

@lucid obsidian
Try to keep it to a single machine for as long as possible. Distributed computing is pretty hard.
If you're building a chat app, you will probably will need to handle search, statistics etc., which brings additional challenges in a distributed system

So I would start with a "default" relational database (PostgreSQL) and then start thinking about distribution when you know what kind of system you have: what operations are used more often, which less often, which could use caching, which need to be more consistent, etc.
It will be impossible to do it right when you don't have an app with lots of actual users.

[lucid obsidian]

ok, thank you

I think wrapper functions will be the ticket to make migrations easier

[brave bridge]

wrapper functions?

[lucid obsidian]

Like calling getUserByID() instead of directly searching the DB. Then I can change getUserByID's source when/if I eventually move to a new DB.

[brave bridge]

well, yes, people usually don't write SQL directly in the HTTP handler 🙂

[lucid obsidian]

I'm talking the Flask backend

[brave bridge]

I suggest this book: https://www.cosmicpython.com/ if you want to learn about making a scalable application (in terms of the architecture itself, i.e. code, not distributing it and stuff)

[lucid obsidian]

I shall take a look

thanks!

[brave bridge]

If you're planning to scale to extreme sizes, like Discord, you should be prepared to take parts of your system and completely redesign them, probably in a completely different technology stack.

[lucid obsidian]

yep 😕

I've had smaller projects require such things a lot

[brave bridge]

Well, as always, you should start small to see if people actually like your application

develop the features that make it unique, and think about scaling it when you think it is actually a good idea

[lucid obsidian]

yep

[brave bridge]

Discord has tens of millions of active users, probably sending billions of messages per day. That's tens of thousands of messages per second. And there are other events, such as presence changes, pings, joining/leaving servers, etc. And while doing that, you need to maintain searching, detecting spam, storing images and videos.

A single machine running Flask will not handle this. So Discord has lots of machines, and uses different languages (like Rust and Elixir) and probably different kinds of databases for different purposes.

[lucid obsidian]

Right

One thing I am doing straight out the gate is making the test hosting environment in a dedicated user account. I've learned things now vs a year ago!

[supple birch]

heya what is the best way to setup an database structure system,

so i want to setup a small memberbase system, but i need to make sure the members have acces to the data stucture of the server?

hard to explain :b

[main oriole]

sqlite> CREATE TABLE Leaderboard(Team_ID INTEGER PRIMARY KEY Team_Name TEXT PRIMARY KEY Points INTEGER PRIMARY Leaderboard INTEGER PRIMARY KEY) 
          ...> ;
Error: in prepare, near "Team_Name": syntax error (1)

What's syntax error 1?

[torn sphinx]

Hello. Anyone know a way to automatically backup your python-made database with python code? 🙂

[worldly dawn]

u can chk module

or u can chk when u save. Python can track ur bug and will ask u to debug

[torn sphinx]

@worldly dawn thanks will check it out

[worldly dawn]

anythime 🙂

[paper flower]

You forgot commas , betwen your columns, also why everything is a primary key? 😅

[main oriole]

Because IIRC Primary Key means it needs to have a value. Each of the things needs a value

Idk

Never done databases before

[paper flower]

No, primary key means that a column / combination of columns is uniquely identifying row

Also they're not nullable

But you can just specify column as not null

create table users(
    id integer primary key,
    username varchar(50) not null
)

[main oriole]

Ah ok

Also how do I update a point counter through python code?

[paper flower]

You can either write raw sql or use an orm to update it

[late root]

use sqlite3 module or aiosqlite if you want to write the sql by yourself or use an orm like Tortoise ORM (courtine)

[main oriole]

I'm using a discord bot command to update it, I was just going to do like
/leaderboard update choice: points to: int
Then it'd update it to whatever to is

Internet is sort of dodgy in maths btw so if some messages send twice or send late, usually is that

[keen gust]

Can any send me ratelimits of postgresql if i connect using py ..

[fading patrol]

What database?

[paper flower]

What do you mean?

[keen gust]

Can i connect my Pg DB often? Will i get ratelimited if so?
And if not , any ratelimits on Operations i do on the db?

[paper flower]

Ehm, no, it's your database

[paper flower]

You could run your tests inside of a transaction

[brave bridge]

The endpoint should ideally start a transaction and commit/rollback depending on what it needs

I would make a fixture that cleans the database

If you want integration tests (tests that assemble lots of moving parts in your system), I would test a bigger scenario, such as 'A new user can buy and order ice cream'. Otherwise you're just testing the pieces, not the composition

And if you want to test just the endpoint logic in isolation, you could use a fake/a stub/SQLite, depending on what's easier in your architecture

[fleet ibex]

I roll a inmem sql side by side my diskdb.. for testing.. i hate having to recreate the disk db everytime a deletion or update fails 1/2 way thru a process or something else goes wrong.

[brave bridge]

Isn't that what transactions are for?

[fleet ibex]

probablly, i admit i have not looked into transactions.. my use case is creating and destroying new tables depending on the results of my program.. so in my diskdb i end up with lots of artifacts.. perhaps transactions would help... I'll look into it.

[golden dove]

I use mongodb as my db
Below is a data I save to the db.

 status = {
            "profile": {
                "name": "John",
                "age": 19,
                "hobbhy": "pc"
            },
            "server": "python"
        }
        await collectionTest.insert_one(status)```
And I want to change only the value of the ley: **age**.
But the code below changing the strucutre of the whole value of the key **"profile"**.
```py
profile = await collectionTest.find_one({"name": "john"})
        status = {
            "$set": {
                "profile": {
                    "age": "16"
                }
            }
        }
        await collectionTest.update_one(profile, status)```
How do I only change the age of here?

[burnt turret]

i think it should be something like

await collectionTest.update_one({"name": "John"}, {"$set": {"profile.age": "16"}})

https://docs.mongodb.com/manual/tutorial/update-documents/#update-a-single-document similar example here

[golden dove]

Would the key with hobby gets erased at this point?

[burnt turret]

it shouldn't, but i haven't tested it out

actually, let me try

[golden dove]

I'm 90% sure

It would

[burnt turret]

https://mongoplayground.net/p/MqSSRi-KyPi nope it works fine (the other keys arent erased)

[golden dove]

The doc itself says: only the specified part would be updated with update_one method. But I believe that was referring to values as not values in dict-value.

Let me check it rq

I don't get where is the misunderstanding between us but mine below completely erased everything inside of the dict-value (profile) even the name itself:

#inserted file
{
            "name": "a",
            "userid": ctx.author.id,
            "profile": {
                "name": ctx.author.name,
                "age": 19,
                "hobbhy": "coding"
            }
        }```
And below is what I do:
```py
doc = {
            "$set": 
            {
                "profile": {
                    "age": 16
                }
            }
        }
        await ctx.send("right before replacing")
        await collectionTest.update_one({"name": "a"}, doc)```
And this is what the result shows:
```py
{
    name:"a"
    userid: 759756236996083713
    profile: {
       age:16
    }
}```

[burnt turret]

this isn't the query I'm telling you to run, this is the same as the one you sent in your question first

you need to use dot notation to only updated the nested fields

[woeful torrent]

If I have an Author table and a Posts table with a relation from authors to posts is there a way to cache the number of posts belonging to each author until modified? Currently, I'm just recalculating all the counts which takes almost a second each time and seriously slowing things down...

[burnt turret]

i.e {"$set": {"profile.age": 16}} should be the update operator

[golden dove]

Ahh, my
brb rq

yay, it worked.
Moral: needs to read to doc very carefully
Thanks so much!@

[woeful torrent]

I've seen that sqlalchemy has a caching module called dogpile.cache but it doesn't seem to be intended for the same purpose

I guess I'd need some sort of way to store an id to frequency map and update that once on startup and then every time an entry is added or removed to the relationship

[paper flower]

@woeful torrent What kind of caching do you need and what for?

[woeful torrent]

As I mentioned earlier I'm trying to cache counts of e.g. number of Posts per Author since counting and sorting them everytime is very slow otherwise

[paper flower]

Ah, i see your message now 🤔

Is your app running in multiple instances?

[woeful torrent]

No, I'm running on a single instance

[paper flower]

I think you can use in-memory cache then, if i understand correctly you're ranking users by amount of posts?

(Just sorting basically)

[woeful torrent]

The thing I'm working on atm is autocompletion for authors and I want to use the number of posts of each author for ranking

Having post counts cached would also help with other pages that display post counts

[paper flower]

How are you doing it right now?

[woeful torrent]

Right now I'm querying the post counts each time which is obviously unsustainable slow (~600ms at best)

[paper flower]

How are you querying them?

[woeful torrent]

just using a regular session.query in SQLAlchemy

[paper flower]

Show me da query 😅

[woeful torrent]

hmm I don't have it configured to log the author post count query right now but I'm also counting tags per post in a similar way:
SELECT tag.id AS tag_id, tag.name AS tag_name, tag.category_id AS tag_category_id, count(tag.id) AS count FROM tag JOIN tag_relations AS tag_relations_1 ON tag.id = tag_relations_1.tag_id JOIN post ON post.id = tag_relations_1.post_id WHERE post.stored_locally = 1 GROUP BY tag.id ORDER BY count DESC

This is even slower since it's a many-to-many relation

[paper flower]

How many posts do you have? 🤔

[woeful torrent]

Right now about 26,000 - and it's only a tiny subset of the ones I have stored remotely

There are around ~700k+ relations though

[paper flower]

Can i get my hands on your dataset?

[woeful torrent]

I don't think I can share it

[paper flower]

I can try to seed some data then ig

Why do you need where posts.stored_locally condition check btw?

It costs you an extra join

[woeful torrent]

Since I have more posts in the database where only the metadata is available

since they can't actually be viewed I'm not including those in the count

but honestly even without the extra join it's still slow enough where I'd like to cache the results

So I only have to recompute the counts once new posts and/or authors are added or (very rarely) removed

[paper flower]

Cache is hell to maintain

[woeful torrent]

I mean the naive solution I was thinking of would be 1. query counts once and store them in a Counter in memory 2. listen for insert/remove events and rerun the query

I was just wondering if there's a better solution since I doubt I'm the first one who needs memory caching of some sort of aggregate count from a database

Sadly, there doesn't seem to be much information on caching query results online for some reason

[woeful torrent]

What did you use for caching?

[paper flower]

@woeful torrent

explain analyze select
    users.id,
    users.username,
    count(a.id) as articles_count
from users
         join articles a on users.id = a.author_id
group by users.id
order by articles_count;

This query runs ok for me, how many records do you have?

create table articles
(
    id serial not null
        constraint articles_pkey
            primary key,
    title varchar(255) not null,
    author_id integer not null
        constraint articles_author_id_fkey
            references users
);
create table users
(
    id serial not null
        constraint users_pkey
            primary key,
    username varchar(50) not null
);

[woeful torrent]

Like I said I have around ~700,000 relations currently

[paper flower]

so 700k articles? 🤔 I use one to many here, it's kinda not the same 😅

[woeful torrent]

yeah the full dataset is ~4.7M

[paper flower]

I think you can just avoid using joins here

@woeful torrent Could you explain how exactly do you want to use your data? Are you ordering by that count or just need to display it for individual users?

[woeful torrent]

Both - the most intensive applications is for auto completion since it requires the counts to be available each keystroke

[paper flower]

You should have debounce time on frontend too to reduce amount of requests to your server

[woeful torrent]

yeah definitely

[paper flower]

Could you also send your models/tables configuration?

I don't think it's possible to rank your users by amount of related articles without doing a full table scan 🤔

or at least index scan

[woeful torrent]

yeah I don't think so either - that's why I'm trying to cache the frequencies in memory

After some more thinking maybe I'll use a global singleton max heap for counts

and then use sqlalchemy events to update counts in the heap on insert/remove

[gilded coral]

I tried SQLBolt and have had good result so far, but I wanna learn more about SQL conventions and things, like prepared statement to prevent injections and what not. Is there a "Further Reading" list for this? The pins doesn't have it afaict

[paper flower]

Well, querying amount of articles for single or a list of users is not that hard, i think you could cache top n records for your order by query (you said you need one) 🤔

Also i think your query could be simplified a lot

[unborn orchid]

Is there a way to save a list containing classes to the sqlite3 table?

like

user1          [PizzaClass, FrogGlass]
user2          [WalletClass, HatClass]

If not, what DB should I use?

[paper flower]

Classes? You mean class instances?

[unborn orchid]

Yeah, I meant that

[paper flower]

You should use an orm

can*

[torn sphinx]

Hey everyone, I'm going through some SQL practice problems on HackerRank and need help with a problem I'm having...

[unborn orchid]

Sounds like something new to me, I'll try it.

Thanks

[paper flower]

@woeful torrent By the way if you filter your data first (e.g. only select articles that were created in the last month) it should be faster

[torn sphinx]

SELECT DISTINCT city FROM (SELECT DISTINCT city FROM station WHERE city LIKE 'a%' OR city LIKE 'e%' OR city LIKE 'i%' OR city LIKE 'o%' OR city LIKE 'u%') WHERE city LIKE '%a' OR city LIKE '%e' OR city LIKE '%i' OR city LIKE '%o' city OR LIKE '%u';

I tried appending AS table1 to the select subquery but I got a syntax error. Could somebody please help me?

The query is meant to select all cities that start and end with a vowel.

[frigid glen]

As long as you don’t shy away from async - Pydbantic makes this pretty easy - https://github.com/codemation/pydbantic

[brave bridge]

Why do you want to do that? And what do you have in those objects?

[paper flower]

@brave bridge Probably some sort of inventory system for a game

[uncut flint]

is there some other command besides .commit to update a database? ive modified the data in one of my tables and its reflected the changes in the variables but is not making the changes within the table itself

[paper flower]

Commands? Where?

[fleet ibex]

when your done with the db close connection?

[paper flower]

You didn't add your note to the session

[uncut flint]

it should just be modifying that current note. if that 1 note still needs to be added to the session to be modified how would i do that?

[paper flower]

session.add(your_object)

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

Also https://docs.sqlalchemy.org/en/14/orm/session_basics.html

[uncut flint]

that would create a new one wouldnt it? im trying to just change the current one

unless im dumb and not understanding

[paper flower]

It would only add it into database if it's not yet persistent

Otherwise it would update an already existing record

[valid needle]

hey

[uncut flint]

ah ok

[valid needle]

i need some help with my database project, what is the best way to modify/access records from iOS?

[paper flower]

What do you mean from ios?

[valid needle]

i have a sql lite3 database set up in a VPS, im thinking of accessing the database directly through my apple iphone

would that be easy to do?

[paper flower]

sqlite3 database can't be accessed remotely

And you shouldn't give random users direct access to your db

[valid needle]

maybe some sort of web interface? it's a personal project for myself

[fleet ibex]

it could be tho, but would require a session to the vm via other means

[paper flower]

I think you should create an api that would use the database and provide an interface to your mobile app (i assume you're making one)

[valid needle]

okay, i could take a look at that

thanks!

[fleet ibex]

your going to want to look into mysql or postsql.. as you will run into issues.. like no user accounts, etc

[valid needle]

can i use mysql with python?

[fleet ibex]

yes

[valid needle]

is there an api for that

okay, cool i will definitely have a look

[paper flower]

Yep, but you still have to create an api 🙂

Most orms support multiple databases (e.g. sqlite, postgres, mysql)

[valid needle]

i will have a look at that, python should be fine for that i assume?

[paper flower]

Yep

[fleet ibex]

sqlite tho, every user would be admin.. so really look into the others

[paper flower]

Depending on what you want to make look into Rest API's (you can use FastAPI or Django for that) or GraphQL (more complicated to begin with but is easier to query data with)

[paper flower]

You shouldn't give users direct access to your database

[valid needle]

that's a good point, i will have a look

[valid needle]

it's a personal project for myself but i'll definitely see if i can make it secure

[fleet ibex]

i wasnt really considering an orm when i typed that.. as Ive yet to make my first or use sqlite other then a direct script to db case.

[paper flower]

What kind of project? Depending on what features you need built-in you can choose between FastAPI / Django, at least these two are most suited for building api's, django having more built-in features but lagging behind fastapi in terms of dev experience

[valid needle]

i'm making a stock price tracker and it just reads the stocks i'm interested on from a db file

so it would be really convenient to write to the db and add stocks when needed from iOS

[paper flower]

Are you making an ios app too? 🤔

[uncut flint]

i got it figured out and working

[paper flower]

Or is it a web based app?

[valid needle]

most likely yes, or i can use Google spreadsheets to interact with my API and add it from there

[paper flower]

I think if you don't need auth right away you could use FastAPI + SQLAlchemy (or any other orm really)

[valid needle]

REST requests should probably be better for what im trying to do anyways

[valid needle]

i've heard about SQLAlchemy before, is it different than SQLite3?

[paper flower]

sqlite is database management system (aka just database), SQLAlchemy is a python library

You can build queries with it and map your table to python classes, that's called object relational mapping (orm), e.g.
If i have table users:

create table users(
    id serial primary key,
    username varchar(255) not null,
    registerd_at timestamp not null
);

I can map it to a python class:

@dataclass
class User:
    id: int
    username: str
    registerd_at: datetime = field(default_factory=datetime.now)

[valid needle]

so it converts entries from a table to python classes essentially?

[paper flower]

Yep, that's the orm part

sqlalchemy also supprots working with just "table" objects directly

[valid needle]

so it's a bit better to use SQL Alchemy than Python's SQLite3 library then?

i assume sqlite3 is a python library too?

[nova cove]

sqlire3 is specifically for sqlite3

Also instead of sqlite3, use aiosqlite. Sqlite3 is blocking

[paper flower]

He might not need this 😅

blocking != bad

[nova cove]

ik

but it would prolly be better to do aiosqlite

just a suggestion but it isn’t needed as you said

[paper flower]

sqlite3 is a library aka driver implementing DBAPI for sqlite3 rdbms i'd say 🤔
There's a pep describing dbapi: https://www.python.org/dev/peps/pep-0249/
Essentially you can implement driver for any database (sqlite, postgres, mysql, etc) and use it in sqlalchemy

Drivers are already implemented for you btw 😅

[brave bridge]

hot take: most programs (especially hobby programs) don't really need async 🙂

[paper flower]

You probably should use async if whatever library you're using is oriented more towards async

e.g. you won't use async with django, at least atm

[brave bridge]

Well if you don't need async, you probably shouldn't pick an asynchronous framework 🙂

although sometimes you don't have a choice, e.g. with discord bots if you're using the gateway

[nova cove]

^

[paper flower]

asyncio would be beneficial if, well, you have a lot of io 🤨

Who would have thought

[brave bridge]

Yep, hundreds of concurrent connections. Or if you need to do lots of concurrent stuff per request.

Sometimes async is just a bit more convenient, I guess. But it causes you the whole "coloured function" problem

[paper flower]

I have a service that performs db operations and also calls other services, it's quite beneficial to have it async

[brave bridge]

If it performs the DB operations and calls other services at the same time, then yes, it is worth considering, if just spawning two threads doesn't seem feasible.

[paper flower]

I do it sequentially anyway (in the context of a request)

[brave bridge]

so what's the benefit of async in your case?

[paper flower]

ig tasks are cheaper than threads 🙂

[brave bridge]

Why do you need them to be cheaper? Are threads too expensive in some way for your app?

[paper flower]

I'm planning on adding a graphql api to that app (using strawberry), it would be better to have it async

[brave bridge]

true, if there's some nice tool, it is very tempting to use async

I like Starlette a lot, so it would probably be my go to simple framework, even if I don't need async 😄

There's Werkzeug for the non-async world, but it does seem like it has almost no "batteries"

[paper flower]

I expect some concurrent requests here, so i don't think i'm losing anything by using async

[mortal rover]

can someone please tell me if i can keep .header on in sql by default

[paper flower]

Header?

[hard laurel]

I am attempting to merge a child into a family. Using sqlalchemy- working with UUID’s and postgresql. I just for the love of me can’t figure the query out or why it won’t merge. So basically what I’m trying to say here is that I need to merge one users account into another families user account

On postman it says that the merge is successful. But when I checked the database it actually wasn’t merged! Now I have tried to drop and then insert into that family but it’s just not working

Any ideas from anybody here would be great

? Please

[proven arrow]

Whats the error? What does merging mean? Creating a relationship?

[hard laurel]

So say you have a program that a family Member can create a family so you’ll have your parents and your kids, the issue that I’m running into though is what if one of the kids create an account outside of the family how do I merge that account into the family account replacing a spot already created

Using sqlalchemy postgresql

@proven arrow does that make sense

Or does anybody know what I need to do here,?

[dusty helm]

hey guys!

how are you guys doing?

It's been a while I don't come here haha,

I'm having a strange doubt, how to create a many-to-many relationship with 3 tables using flask-sqlalchemy? Strong kudos for who answers that, I'm not finding anything about it on Stackoverflow

[uncut flint]

https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html

[dusty helm]

i saw that doc

its n:n relationship example applies to 2 tables, not 3

the third table registers data as empty or null

not table, its third column

and its for flask-sqlalchemy

that doc is for the std lib

[uncut flint]

It does work for 3 tables. In a moment and I can show more. Just use the third table as the connector point between the other 2

This was the setup shown to me but here is a visual about how the connections and tables would be setup. You can use the many to many idea from the link I sent to tie them together

[dusty helm]

yeah, but that is 2 tables

the link is the 3 one

i have

resource | commander | match

what I was able to think about is to create a link between match and commander, then a link between this matchcommander link and resource

not sure if this is correct but really. fu** it, i'm tired of thinking about this

[uncut flint]

Can you draw out a model of how you want it connected? I’m having a bit of trouble following

[dusty helm]

yes, me too

hahahha

just a second

where did you draw that example of yours?

[dusty helm]

this bad boy

[uncut flint]

The guy who is mentoring me threw it together because i previously had a 1-many setup and needed a many-many setup in order to share notes between user accounts. I’m not sure what he made it in but if you just use ms paint or something to create 3 boxes and the contents of each one and then draw lines from them.

[dusty helm]

that is paint right there, be amazed with my drawing skills

i think my structure solves this, i'll draw this frankenstein for you to evaluate and give your opinion

[uncut flint]

Works well enough. Just for a bit more context what is each item? What is the commander, what are the resources and what is the match in context to the project. Are resources specific to commanders and does the match define the commanders?

[dusty helm]

Match 1:N Commanders
Commander 1:N Resources

but resources are referencial

it's a game i'm doing

it was already working, i was using a nonrelational structure

but I managed to f*** it, didn't consider concurrency, i was creating a major concurrency issue

[uncut flint]

Ah

[dusty helm]

I think this solves the issue

but, what a workaround

can anyone opine on this? to validate if this is semantically correct?

does it match the "data normalization" standards?

[uncut flint]

I don’t know enough about this to say for certain. I think that should work but something still feels a little off but I can’t quite put my finger on it atm

[dusty helm]

i feel the same thing

my cousin is a software architect in a major company in my country, we had a discussion about this case, he says that this is the best approach, you reduce the amount of data and don't cause the same redundancy as the first image

he has around 12 years of experience with dbs, but I could not understand at first

looking at the drawing, it kind of makes sense

this drawing makes more sense, that strange connection above match_commander was strange

[uncut flint]

The second one is better but I have a small feeling that resources should be tied to the commander instead of the
“Match_commander”

Not sure on that though

[dusty helm]

yes

but

a commander may play as many matches as he pleases

commander * match * resources

[uncut flint]

Do resources carry over?

Actually that was a dumb question I’m guessing not

[dusty helm]

no, those are unique for a match

you are a nice lad to talk about this stuff, can I add you?

[uncut flint]

No problem. I figured trying to be active in discussion can help me learn more as well. I’m gonna have to say no on the adding though, I don’t usually add people I don’t know

[dusty helm]

oh, got it

no problems, i understand your point

I've learn all i know about python in this group

it's the first time in about 9 months I come back here

this groups is a big deal for me, had a big career change because of a lot of people that helped me here, and as my friends don't share my enthusiasm for programming, i'm always compelled to come back here, it's always good

but, thanks for the help man, really appreciate your initiative

[fading patrol]

Do you actually need the match and commander tables at all? If id is the only row, you don't

Unless your list items correspond to columns I think you need to do it as a string and then convert back to list

[dusty helm]

hi @fading patrol

I need both columns :c

ops, tables

the doubt now is

checking the docs, I see that a many to many relationship has this:

# Refferencial Documentation found at https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/#many-to-many-relationships
tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key=True)
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags, lazy='subquery',
        backref=db.backref('pages', lazy=True))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

Can I, instead of declaring it that way, creating the data myself using inheritance? like this:

class LinkTable(db.Table):
    __tablename__ = "tags"
    tag_id = db.Column("tag_id", db.Integer, db.ForeignKey('tag.id'), primary_key=True)
    page_id = db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key=True)

And instead of using that "secondary=tags", to use "secondary=LinkTable()"??

because I want to include a relationship inside this link table

is that possible?

I have a tip for you my man

are you using python?

yes

i'll give you the wrong way that works

hope that helps

i'll write it, give me a few minutes

class ModelWithList(db.Model):
    _data = Column(String)
    
    @property
    def data(self):
        return json.loads(_data)
    
    def save_data(self, updated_list):
        new_data = json.dumps(updated_list)
        self._data = new_data
        # Add to Session & Commit

save the list as a json, json.dumps will serialize your data as it is into a string

save it into a private variable for storage, that is _data

don't use that data to read, only to write, to read you use the .data

this caused me a major problem

this whole implementation made me lost a month and a half of development

but i was storing big data structures inside a json

if you are storing a single list

be aware that if your application is data intensive, if this list is going to be frequently updated, this is not a good way out

you can have concurrency problems

that was what happened to me

but now, for the mages of flask-sqlalchemy, please help me solve my issue hahaha

I want to insert a relationship inside a link table (many to many)

this n:n must have a one:n inside it

[fading patrol]

I would probably use MongoDB or something instead. You don't want to stuff all that into SQLite without normalizing it

[dusty helm]

as i've told you: i'll give you the wrong way that works

not you actually, it was @clever gate

but hey @fading patrol

enlighten me, please

how to make it work

i'll try to explain it as clear as possible

[fading patrol]

I have no clue, try it I guess?

I'm going to bed, good luck :)

[dusty helm]

i'm making a game.

a user can have N commanders (1:N)
a commander can participate N matches (1:N)
a commander will have N resources (1:N)

resources have amounts.

this is the issue

but yeah, good night dude, thanks for trying to help

[thorn rivet]

i need help with mongodb i have data that looks like so i have duplicates with same AvatarID is there any way to remove all entries that are duplicates leaving just one of each AvatarID?

{
    "_id": "61e49b87d11732f4b8fb76c7",
    "Avatarid": "259b2525-85de-44f3-b055-148dc303f694",
    "name": "examplename"
}```

[faint jewel]

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[uncut flint]

    var i = 0;
    db.collection.find({key: num}).forEach((doc)=>{
      if (i)   db.collection.remove({key: num}, { justOne: true })
      i++
    })
  });```
i am unsure if this is exactly what you are needing but i found this on here: https://stackoverflow.com/questions/14184099/fastest-way-to-remove-duplicate-documents-in-mongodb

[thorn rivet]

i really hope so ive been trying days to figure this out

thats for c# tho itsnt it?

[unborn orchid]

I'll try use SQLAlchemy (ORM) instead of asqlite. Will be SQLAlchemy async?

[uncut flint]

my bad, looking back at it it looks like javascript

[uncut flint]

https://stackoverflow.com/questions/51240581/check-and-remove-duplicates-in-python-mongodb#:~:text=Duplicate records can be removed,and specifying the dropDups option.
try this one

[thorn rivet]

this stuff doesnt seem to work either not a thing no more it seems im looping through the data but its 2.2 mil and taking forever i seen this https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.duplicated.html
but i never use pandas would this be any faster then just looping through the list?

[torn sphinx]

How many "columns"?

[thorn rivet]

2249080 documents and 14 items in each

[torn sphinx]

How many lines in the documents?

[thorn rivet]

14

[torn sphinx]

Are they delimited?

[thorn rivet]

its json

[torn sphinx]

14 objs at a 1:1?

[thorn rivet]

yes

[torn sphinx]

... Have you tried ast.literal_eval?

[thorn rivet]

i honestly dont know what that is lol

[torn sphinx]

Can you spit out a sanitized version of the 14 objs here?

[thorn rivet]

{"_id":"61e49b87d11732f4b8fb76c7","TimeDetected":"1642371975","AvatarID":"avtr_259b2525-85de-44f3-b055-148dc303f694","AvatarName":"HonKai โว้ยยยย","AvatarDescription":"None","AuthorID":"usr_8b662ddc-66d9-497b-9769-97f211adcaf3","AuthorName":"KuroKaew","PCAssetURL":"https://api.vrchat.cloud/api/1/file/file_a86433b1-a438-4b70-b8e2-f073f1b01819/2/file","QUESTAssetURL":"None","ImageURL":"https://api.vrchat.cloud/api/1/image/file_51e7c53c-e0ba-4c2a-8993-11b9d99abc8d/2/256","ThumbnailURL":"https://api.vrchat.cloud/api/1/image/file_51e7c53c-e0ba-4c2a-8993-11b9d99abc8d/2/256","UnityVersion":"None","Releasestatus":"public","Tags":"None"}```

[torn sphinx]

Give me a sec

[thorn rivet]

okay

edited it as it shows different from api then just copy from mongo

[torn sphinx]

#!/usr/bin/python3

import json

fName = 'input.file'

with open(fName) as iFile:
    rows = iFile.read().splitlines()

for row in rows:
    x = json.dumps(row)

y = ast.literal_eval(x)
z = json.loads(y)

[thorn rivet]

what does ast.literal_eval do?

[torn sphinx]

Theres a docstring

It also depends on how things encode, etc.

But with luck, you can loop that

[thorn rivet]

i mean its all valid json just its taking ages am already looping it on 10 threads and still so slow lol

[torn sphinx]

python3?

[thorn rivet]

yes

[torn sphinx]

Version?

[thorn rivet]

3.8.9

[torn sphinx]

Know of pypy3?

[thorn rivet]

nope

[torn sphinx]

Get it installed, run the same code 🙂

I use it for TCP injection because its that much faster for the routines I use.

[thorn rivet]

a new interpreter?

this looks interesting

[torn sphinx]

A variation on cpython

Makes a huge diff for libs like scapy

[thorn rivet]

does this work in windows?

[torn sphinx]

Not sure, maybe?

If you have a bit of processors, use multiprocessing

[thorn rivet]

ye 8 core 16 threads

idk how to use multiproccessing tho lol i usually use threading

[cosmic spindle]

How can i add multiple images to sqlite3 database and retrieve them?

[torn sphinx]

blobs mayb

[cosmic spindle]

ya. But how can i add multiple images ?

[thorn rivet]

@torn sphinx have you heard of numba?

[torn sphinx]

numpy?

[thorn rivet]

no numba

[torn sphinx]

In the same cell as it were starman?

I havent

[cosmic spindle]

kl

k

[thorn rivet]

it like compiles code to make it run faster i think i watch a vid a while ago seems a lot faster ill give it a try and see what happens

its like pypy it seems

https://numba.pydata.org/

[torn sphinx]

Ok

Try pypy3 first, easier learning curve

Instead of python3 ./foo.py

pypy3 ./foo.py

[thorn rivet]

i gotta try and figure out how to install on windows lol

[torn sphinx]

🙂

[thorn rivet]

i found it lets give it a try

[torn sphinx]

json and ast will be built in modules, so same code would run. Have fun and good luck.

[thorn rivet]

how do i pip install with this lol

got it ill let you know if its any faster

[unborn orchid]

Is there a way to create a new column in the table?

[uncut flint]

Wdym? Just another column?

I’m pretty sure just defining another column in the table should be all it needs. But it would have to be defined in the database before use

[torn sphinx]

Not necessarily......... Use sqlite3 and a gui, eheh.

Otherwise yes, agreed.

[ashen meadow]

how to insert/update (without droping the table) whole dataframe to mssql with orm? and without df.to_sql ? anyone know?

[plain moat]

How can I use the same column in a SELECT, when one time the column is from a JOIN of table1 and table2 and the other is just from table1?

[pure sleet]

without dropping the table? bro what? were you dropping the table before?

[ashen meadow]

df.to_sql('tablename', if_exist...
if_exists{‘fail’, ‘replace’, ‘append’}, default ‘fail’
How to behave if the table already exists.
fail: Raise a ValueError.
replace: Drop the table before inserting new values.
append: Insert new values to the existing table.

[pure sleet]

what is df?

[ashen meadow]

pandas dataframe

[pure sleet]

ok, im not familiar with pandas

[ashen meadow]

yea, so this is my main point, i dont want to convert dataframe to list or tuples to be able to replace the records in db

[frigid glen]

you can do this with pydbantic, but no support for mssql , just postgres /mysql / sqlite

[ashen meadow]

😦 my db is mssql...

[frigid glen]

and I suppose you are not able to use anything else?

[ashen meadow]

unfortunately not... 😦

[paper flower]

@ashen meadow Afaik SQLAlchemy supports mssql

[static zealot]

Hi I see Unable to locate package mssql-server error while installing mssql-server on wsl2

Not sure whats causing the issue

I am using wsl2 on windows10 machine

[neon bronze]

Someone knows what happen if I try to a save a list of objects in a single row?

[paper flower]

You can't store python objects in a single row, unless you're using a database that supports array types

Even then they should be primitives (e.g. ints, string)

[fading patrol]

You can serialize it (convert it to a JSON string) and someone posted an example of how in the last 12hrs or so. Not ideal but can work ok for a simple project

[paper flower]

It depends on what they want to store, sometimes it's better to create a separate table and relationship between them

[gusty path]

how to check if a table exists ?

[fading patrol]

We need some context here... Sqlite3 with Python or what?

In any case, check StackExchange and you should see the answer

[latent fable]

what is the sql query to create a table if it is not existent?

i am using mssql

i have tried create table if not exists

[paper flower]

Why do you want to do that? Usually you use a migration tool (e.g. alembic for sqlalchemy or django orm (it has it's own migration system)) to ensure that you have tables you need

[gusty path]

I'm using this

SELECT COUNT(*) FROM information_schema.tables WHERE table_name = %s

[paper flower]

and to actually migrate your schema - to add or remove columns, rename them, etc

[gusty path]

I need the python script to create tables if those tables don't exist in the database already

[paper flower]

Well, why do you want to do that?

As i mentioned you might need a migration tool

[gusty path]

Well you cant interact with the tables like add rows delete rows if it doesn't exist

[paper flower]

Yeah, so migration tool would create them for you 😅

[gusty path]

I have 0 data

[fading patrol]

Is tables a table in your database? Are you using SQLite or what?

[gusty path]

I want to do it with python scripts not use some other app

[paper flower]

It's not really about having or not having some data, you would eventually need to migrate your schema that has data.

[gusty path]

I think information_schema.tables lists the tables in the database, then it checks for the matching table name

I took it from here: https://stackoverflow.com/questions/17044259/python-how-to-check-if-table-exists

[fading patrol]

There are different answers there for different DBs, and none look like your code. What database are you using? If SQLite I believe the table you need is called sqlite_schema

[paper flower]

@gusty path Are you using pure sql? i.e. no orm or something?

[gusty path]

No I'm using postgres it looks the same too

[latent fable]

for mssql how to check if a table exist? what is the query? I kind of need the same thing too

[paper flower]

What do you want to do? 🤔

[latent fable]

I want to check if the table exists already. If it does I do not want to do anything. If it does not then I would like to create the table through a python script.

[paper flower]

Would you like to change that table later?

[latent fable]

no

[paper flower]

Is it one-off thing then?

[latent fable]

but data will be added later. the structure of the table will remain the same

[paper flower]

Are you sure about that?

[latent fable]

yes

[paper flower]

you can use create table if not exists statement, but i'd rather use a migrations tool

[latent fable]

I tried that the problem is if not exists throws error in mssql

[paper flower]

Yeah, i think mssql does not support it

[latent fable]

my question is then, how can I do this thing in mssql then? is there any option?

[paper flower]

Yep, you could google something like create table if not exists mssql
Are you using an orm or just raw sql?

[latent fable]

did that. had no solutions. that is why came to discord

[fading patrol]

Did you try SHOW TABLES ? https://stackoverflow.com/questions/3913620/get-all-table-names-of-a-particular-database-by-sql-query

[latent fable]

show tables is for mysql.. I want something for mssql

[wooden condor]

for mssql you need to look in information_schema.tables:
https://www.tsql.info/ex/sql-check-if-table-exists.php

you could also just run your create table statement and suppress the error that comes back if it is "already exists"

[latent fable]

That is a good idea. Thanks. I will try that.

[torn sphinx]

Is it ok to ask about php here?

[clear stirrup]

not sure how much help you'll get in a python discord's database channel

[uncut flint]

what does it mean when an object is not subscriptable?

[brave bridge]

!e

x = 42
print(x[0])

[delicate field]

@brave bridge :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 2, in <module>
003 | TypeError: 'int' object is not subscriptable

[brave bridge]

You're using an [...] operator on something which does not understand it

[uncut flint]

oooo, ok. thank you

[sterile pelican]

What's wrong with this (asyncpg)

[sinful jungle]

hello, anyone is avaiable to explain me n:n relation in access ? i want to make simple database, but not sure is it working well

[bitter oak]

I have no idea, but if you could give me more information such as using Microsoft Community Sql, or MySql i will gladly search it

[torn sphinx]

Can someone provide some ideas/projects where i can use mongoDB?

[torn sphinx]

whats the error return code what does the consple say

[sterile pelican]

Syntax error

[sinful jungle]

there are these relations, prof want from me to change relation by use proxy table many2many (n:n), and change logic to possibility that one client can rental many films in one order

[sterile pelican]

Somewhere near "TEXT DEAFULT '"

[torn sphinx]

where does the < point to? which line or word

@sterile pelican

ah aight wait

[sterile pelican]

To the single quote

[torn sphinx]

did u try TEXT DEFAULT true, ...

[sterile pelican]

Uh what

Isn't true a bool

[torn sphinx]

i mean

after text default

[sterile pelican]

I believe the issue is in some symbols inside of a string

Maybe the curly brackets

You escape the quote in the string by doubling it right?

[torn sphinx]

wait

is it for discord?

[sterile pelican]

Yes

[torn sphinx]

for a dc bot

do ()

not {}

[sterile pelican]

So the curly brackets are the issue?

[torn sphinx]

could be cuz

u usually use () in dc.py

[sterile pelican]

I mean it's more convenient for me to format it when the curly brackets are there

[torn sphinx]

u gotta use ()

cuz

[sterile pelican]

Ok I will try that

[torn sphinx]

the same prob would occur if u said channel.send{member_count}

cuz u always use ()

[sterile pelican]

You didn't get what I mean probably

[torn sphinx]

well

[sterile pelican]

!e

s = '{user} has joined'
print(s.format(user='man'))```

[delicate field]

@sterile pelican :white_check_mark: Your eval job has completed with return code 0.

man has joined

[sterile pelican]

I can replace ofc but this is better

[torn sphinx]

mm

i mean

the

thing is

user is something referred to

i mean like

{user}

u cant so (user)

mention is not a big thing to be like {user}

its a command for dc.py

so try (mention)

mention = user....

smth with user.thingy idunno

u gotta reffer mention with somethin too

[torn sphinx]

!e import commands

[delicate field]

@torn sphinx :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | ModuleNotFoundError: No module named 'commands'

[torn sphinx]

!e import discord
from discord import commands

[delicate field]

@torn sphinx :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | ModuleNotFoundError: No module named 'discord'

[torn sphinx]

hm

[torn sphinx]

!e pip install discord

[delicate field]

@torn sphinx :x: Your eval job has completed with return code 1.

001 |   File "<string>", line 1
002 |     pip install discord
003 |         ^^^^^^^
004 | SyntaxError: invalid syntax

[torn sphinx]

rip

lol

[dry crag]

Hello guys, I'm building an app which will hold all my expenses downloaded from bank statements. It categorizes them, pulls the most important data about each expense (e.g. name, where, amount, currency) and allows me to interpret that data - where I lose money, where i spend the most, etc. Currently I save all that in CSV file, and program keeps the data during runtime in one class representing expense and second class being listOfExpenses. I'd like to implement SQL database to keep that data, but I have no clue how do I link the current design with a database. Do I keep the classes and attach database, create separate methods to query the database? Do I modify currently used functions to query the database directly and eliminate currently used classes?

Most of tutorials tell how to implement database, how to implement classes, etc, but im having a hard time understanding architecture of the program

How would each piece of functionality interact with each other

[paper flower]

You can work with SQL directly or use an orm which would map your rows in the database/table to python classes

@dry crag I can help you with that, ask any questions 🙂

[dry crag]

As i do this project as a learning process, to get the basics right id prefer to stick to SQL, and try ORM later on. Does it make sense to add database and add additional functions to query the database, or should i refractor the current code?

Thanks, I would appreciate it a lot!

E.G. right now in my code I do importing data from bank statements (.xmls), exporting it to CSV, importing from CSV, data cleaning, and a lot of these things I feel could be performed with SQL

[paper flower]

If your current classes encapsulate access to your csv file then you should be able to migrate to sql by only changing the code that's accessing it

[dry crag]

And how should I query the DB? The best, probably (?) would be to query DB each time i input/change data (importing xml, manual changes, etc), but then it means refactoring most of the code.

To my understanding second option would be to save all changes during runtime to classes and then query the DB and save all the changes

second option means i only redo 'CSV save' function to DB

first option means a complete overhaul

[paper flower]

Well, yep, if you want to get data from the db or change something you issue an sql query to your database
e.g.

select * from users
where users.id = 42;

Would query user with id 42

update users
set username = 'New Username'
where users.id = 42;

Would update a username of user with id 42

[dry crag]

Sorry, to clarify - specifics and syntax I can deal by myself, thank you, I'm trying to understand the proper behaviour of my app

[paper flower]

Sql is pretty straightforward (most of the time) 😅

[dry crag]

if the DB should be queried each and every time a change happens, or if it could be queried with all the changes during a program 'shutdown'

[paper flower]

Ah, i see, you could encapsulate your database access into special classes that would deal with it:

class ExpensesRepository:
    def save_expenses(self, expenses: list[Expense]) -> list[Expense]:
        # Save expenses somehow
        return expenses

Then if you need to do some other operations you would use that repository and do your operations in a service:

import dataclasses
import datetime


@dataclasses.dataclass
class Expense:
    date: datetime.date
    expenses: float


class ExpensesRepository:
    def save_expenses(self, expenses: list[Expense]) -> list[Expense]:
        # Save expenses somehow
        return expenses
    
    def list(self, after: datetime.date, before: datetime.date) -> list[Expense]:
        ...


class ExpensesService:
    def __init__(self, repository: ExpensesRepository):
        self.repository = repository
    
    def expenses_over_period(self, after: datetime.date, before: datetime.date) -> float:
        expenses = self.repository.list(after=after, before=before)
        return sum(expense.expenses for expense in expenses)

Repository would deal with querying your data from the database and mapping it to classes, service would be responsible for business logic

[dry crag]

All right, i think i start to get it

So, e.g. I import expense data to a list of Expense dataclass objects, maybe do some modifications on this list of Expenses. Then I can run ExpensesRepository methods to query the DB and update in with new values

[paper flower]

Exactly! If you're using an orm you probably won't need a repository in most cases though

class ExpensesService:
    def __init__(self, session: Session):
        self.session = session
    
    def expenses_over_period(self, after: datetime.date, before: datetime.date) -> float:
        stmt = select(func.sum(Expense.expenses)).filter(Expense.date >= after, Expense.date <= before)
        return self.session.scalar(stmt)

[dry crag]

This way my import, expense modifying, cleaning, whatever functions is only dealing with the data manipulation and quering the database is completely detached to a separate class object

[paper flower]

Yep, if you're importing your data from some source and cleaning it it could be a separate class or two

Then your business logic and db manipulation could live in other classes

[dry crag]

Great, got it! One more question - for a web app, I would assume that the DB has to be queried with changes after each and every Expense update, to keep the DB up-to-date for each and every user, right?

[paper flower]

Yep, you should update your database, and, generally in web apps you don't keep anything in memory

[dry crag]

But I can still keep these functions separated, right? Import .xml -> update DB (right away) ... Do a manual change to expense -> update DB

So i still keep functionality of the app separated, but the DB is queried after every change

[paper flower]

Yep, it's generally how things are done

[dry crag]

Yeah, that's this obvious general knowledge i dont have 😄

[paper flower]

I'd suggest using sqlalchemy if you need an orm Though there are other orms out there

class Expense(Base):
    __tablename__ = "expsneses"

    id = Column(Integer, primary_key=True)
    expenses = Column(Float, nullable=False)


def main():
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)

    expenses = [
        Expense(expenses=random.randint(0, 250))
        for _ in range(20)
    ]
    with Session() as session:
        session.add_all(expenses) # List of expenses is added to the session
        session.commit() # Changes are persisted in the database 

        total_expenses = select(func.sum(Expense.expenses))
        print(session.scalar(total_expenses))

I like it because queries are generally translated to sql as-is:

select(func.sum(Expense.expenses))

SELECT sum(expsneses.expenses) AS sum_1 
FROM expsneses

[dry crag]

If I'm just getting to know DBs, wouldn't it make sense for me to get to know the SQL first? I understand ORMs simplify querying a lot - you basically do it straight from python, but to not skip on any basics, I wanted to use PostgreSQL and write Queries in SQL

[paper flower]

Try using sqlalchemy without the orm part 😉

[dry crag]

All right, I have to read on that a little bit more!

[dry crag]

Huge thanks for an in-depth explanation, this clarified a lot! And such a broad overview is never mentioned in any guides/tutorials

[paper flower]

sqlalchemy can be used without the orm part, there's a long tutorial on the docs, but documentation expects you to be intimate with sqlalchemy already 😅

https://docs.sqlalchemy.org/en/14/tutorial/index.html

[dry crag]

But do I understand that I can use PostgreSQL as my DB and SQLAlchemy as a way of accesing it?

[dry crag]

And then I can pick whether I do it 'pythonic' or i still query with SQL?

[paper flower]

Yep, it supports postgresql, i think you would essentially learn sql if you'd work with sqlalchemy core

Queries are structured the same 😉 Plus you can see what sql it produces too

[dry crag]

All right, I need to dig deeper on this topic

[paper flower]

Using core would be similar to working with raw sql:

expenses_table = Table(
    "expenses",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("expenses", Float, nullable=False)
)


def main():
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)

    with engine.connect() as conn:
        conn.execute(
            insert(expenses_table),
            [
                {"expenses": 250},
                {"expenses": 125},
            ]
        )

        stmt = select(func.sum(expenses_table.c.expenses))
        print(conn.execute(stmt).scalar()) # 375.0

CREATE TABLE expenses (
    id INTEGER NOT NULL, 
    expenses FLOAT NOT NULL, 
    PRIMARY KEY (id)
);
INSERT INTO expenses (expenses) VALUES ((250.0), (125.0));
SELECT sum(expenses.expenses) AS sum_1 FROM expenses;

If you know how to define tables and query your database with either sqlalchemy or raw sql you'd be fine using any of them

Plus you could later migrate to using an orm 😉

[harsh pulsar]

i really like sqlalchemy core

[sacred sail]

So I have been looking and haven't found an answer that felt clear to me. I am trying to loop through a folder of excel workbooks and grab specific values from each one (and the location on each wookbook is identical). I did find a way to accomplish this with clunky loops but I dont think it is a really good way to replicate this action for other items. Curious if anyone has any suggestions? I am okay exporting them to another excel book that has all the data in the same sheet or using a dataframe. Basically I have a years worth of energy/water data and instead of opening each book and ctrl+c and ctrl+v, I am hoping to make the process a bit more streamlined. Thanks!

[harsh pulsar]

is there anything wrong with looping over files? that seems like a simple and straightforward solution to me

[sacred sail]

I guess the problem that I have is indexing such that my data frame responds correctly to it. I was having an issue where I was getting Nan for values that were legitimately numbers formatted as such in Excel also I should mention that if I pulled up one instance it gave me the numbers it was the iteration that caused the Nan's

[sacred sail]

And also just not sure if what I am doing is best practice. I am fairly new to any programming but I have dug in to many resources as I didn't want to ask and obvious question

[harsh pulsar]

you might not even need pandas here

if you are just extracting individual fields you probably want to just use openpyxl directly

can you be more specific? can you show your current code?

!paste

[delicate field]

Pasting large amounts of code

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

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[harsh pulsar]

it was the iteration that caused the Nan's
this is basically not possible. so more likely it's a bug in your pandas code

[sacred sail]

How would you recommend that I perform that without looping with indexing? Or do you recommend that I have Excel wings export it to a different workbook instead while looping?

[harsh pulsar]

you never mentioned excelwings. i think you need to share your current code, because it's not clear what you are actually doing

[sacred sail]

I didn't mention XL wings because it was just one out of many options but I guess that's what I meant when I said that I wouldn't mind having it export to another workbook without throwing it in successive data frame columns. Thank you and I'll share that once I get back to the office

[sacred sail]

My main goal is to consolidate all those values into one location without copying and pasting through each Excel workbook manually. I'm not picky, as I don't feel like I have that privilege being a novice

[harsh pulsar]

i was envisioning something as simple as this:

from pathlib import Path
import openpyxl  # pip install openpyxl

c12_values = []
for path in Path('data').glob('*.xlsx'):
    workbook = openpyxl.load_workbook(path)
    worksheet = workbook['Very Important Data']
    c12_values.append(worksheet['C12'])

@sacred sail ☝️

of course you can get more advanced/detailed, or use pandas to process each book/sheet if you benefit from pandas

[sacred sail]

Thank you so very much

[harsh pulsar]

when you share your code, feel free to ping me. i can take a look at it. if you are a novice, there is a very good chance that your code is much more complicated than it needs to be

so we can help simplify it

[sacred sail]

👍

[delicate field]

Hey @sacred sail!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[sacred sail]

Here is my code that did work (albeit maybe not the best way to accomplish this) https://paste.pythondiscord.com/onahuwabaz.apache

[harsh pulsar]

this looks more or less fine, although i recommend you follow PEP 8 formatting conventions

!pep 8

[delicate field]

**PEP 8 - Style Guide for Python Code**

Link

Status

Active

Created

05-Jul-2001

Type

Process

[harsh pulsar]

rindex looks unused entirely

also i personally recommend using full filenames instead of chdir

e.g. instead of this:

os.chdir(r"C:\Users\rpan92\Documents\GitHub\2022_Working_Directory\Tower Makeup")
with pd.ExcelWriter('output2.xlsx') as writer:
    ...

you can just do this:

with pd.ExcelWriter(r"C:\Users\rpan92\Documents\GitHub\2022_Working_Directory\Tower Makeup\output2.xlsx") as writer:  

[sacred sail]

https://paste.pythondiscord.com/edikojuvar.py this is the one that didnt work and gave me NaNs

[harsh pulsar]

or my personal favorite:

from pathlib import Path

output_path = Path(r"C:\Users\rpan92\Documents\GitHub\2022_Working_Directory\Tower Makeup")

with pd.ExcelWriter(output_path/"output2.xlsx") as writer:
    ...

[sacred sail]

Awesome

[harsh pulsar]

i question the value of saving this into a numpy array

what kind of data is this?

i'd just use a list

then you can convert to array, series, etc. after

also you will definitely need to be careful about data types when reading data from excel

https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html see the explanation of the dtype parameter here

[sacred sail]

so avoid numpy for this operation all together and then throw it in a dataframe?

[sacred sail]

It worked!!!!! got rid of the numpy

[unborn orchid]

ASQLITE**

async def user_request(ID, request):
    async with asqlite.connect('planetary.db') as conn:
        async with conn.cursor() as c:
            await c.execute("""
            SELECT ?
            FROM users
            WHERE user_id = ?
            """, 
            (request, ID)
            )
            data = await c.fetchone()
            if data is None:
                await c.execute("""
                INSERT INTO users
                VALUES(?, 100000, 1, 100)
                """,
                (ID)
                )
                data = await c.execute("""
                SELECT ?
                FROM users
                WHERE user_id = ?
                """, 
                (request, ID)
                )
                *Commit*
                data = await c.fetchone()
            print(data)
            return data[0] 

Function call:

balance = await db.user_request(user.id, 'balance')

ID was used for search:

876578215370575893

sqlite3.Row was searching for:

[user_id, balance, level, energy]
|||||
[876578215370575893, 100000, 1, 100]

print(list(data)):

['balance']

Why so?
100000 is expected

[grim vault]

You can't use the binding parameters as column names, only for values. That's why you get the value of your parameter, which is the column name itself.

Your SQL was SELECT 'balance' FROM users WHERE user_id = 876578215370575893, so you selected the string you gave as parameter.

[amber thorn]

Somebody, please help me.. I am in a serious trouble it is 2 AM in the midnight here rn

I am stuck on work

I have a list of genes

is there any webapp or something, where if I type in gene name and disease name, it can give literature evidence??

hey all, somebody please help me

is anyone here??

[fading patrol]

This is not the best place to find help with biology homework I'm afraid

[harsh pulsar]

good! yes, i think numpy was an unnecessary complication here. you can go right from a list to a pandas dataframe

[prime kelp]

cursor = await self.bot._db.cursor()  
        await cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        e = await cursor.fetchall()
        print([t[0] for t in e])
        if 'blacklist' not in [t[0] for t in e]:
            await cursor.execute('''
                CREATE TABLE "blacklist" (
                    "guild_id"    INTEGER,
                    "channel_id" INTEGER,
                    "user_id"  INTEGER,
                    "commands"    TEXT
                );
            ''') 
            await self.bot._db.commit()
            await cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            e = await cursor.fetchall()
            print([t[0] for t in e])

[prime kelp]

so im trying to create a table if it doesnt already exist, but whenever i shut off my bot i lose the tables

[prime kelp]

the first print prints [] (no tables found) but then the second one prints ['blacklist'] meaning it created the table but it didnt store or something?

i'm not closing the connection when i end the code, is this why?

[harsh pulsar]

don't re-use cursors for multiple queries. it causes things to break in weird ways

one cursor per query

[prime kelp]

i make a new cursor instance each time?

[harsh pulsar]

correct

[prime kelp]

shall give this a whirl

unfortunately same response

[harsh pulsar]

since it looks like you are using sqlite, you might want to use CREATE TABLE IF NOT EXISTS instead of checking first

[prime kelp]

[] followed by ['blacklist']

[prime kelp]

i do probably want this

[harsh pulsar]

https://stackoverflow.com/q/4098008/2954547

what sqlite library are you doing? you should check to make sure you are committing the transaction correctly, e.g. maybe you need to start a transaction first

[prime kelp]

im using aiosqlite

I ran ```py
cursor = await self.bot._db.cursor()
await cursor.execute('''
CREATE TABLE IF NOT EXISTS "blacklist" (
"guild_id" INTEGER,
"channel_id" INTEGER,
"user_id" INTEGER,
"commands" TEXT
);
''')
await self.bot._db.commit()

it created the table

it worked perfectly until i turned off the bot

and then it lost the data

[harsh pulsar]

it does look like you are supposed to be able to run .commit without any extra setup

https://aiosqlite.omnilib.dev/en/stable/index.html

[prime kelp]

ill try closing it

[harsh pulsar]

i assume that the database was not using the special in-memory database, right?

[prime kelp]

the what?

[harsh pulsar]

how did you define bot._db?

[prime kelp]

async def connect_database(self):
        database_path = get_database()
        try:
            self.bot._db = await aiosqlite.connect(database_path)
        except aiosqlite.OperationalError:
            raise MissingDatabase(f"{database_path} isn't a valid database!")
        except Exception as e:
            print(e)

        cursor = await self.bot._db.cursor()  
        await cursor.execute('''
            CREATE TABLE IF NOT EXISTS "blacklist" (
                "guild_id"    INTEGER,
                "channel_id" INTEGER,
                "user_id"  INTEGER,
                "commands"    TEXT
            );
        ''') 
        await self.bot._db.commit()

[harsh pulsar]

ok, and database_path is some path on your filesystem?

[prime kelp]

correct

[harsh pulsar]

that all looks fine to me... weird

try closing the database upon bot shutdown i guess

maybe there's some disk buffer that needs to be flushed

[paper flower]

Could you share path to the database?

[prime kelp]

just ./db/database.db

its relative

does sqlite use relative?

[paper flower]

🤔 You could try passing Path instance

[prime kelp]

okay

[prime kelp]

actually i dont think thats necessary

because when i delete the db

it autocreates the db

meaning its in the right place?

not sure

[paper flower]

Hm, could you check what's the error then?

at least do

except aiosqlite.OperationalError as e:
    print(e)

[prime kelp]

im not getting a single error

[prime kelp]

its not raising MissingDatabase though

[paper flower]

You're raising MissingDatabaseError here, perhaps it's not an actual issue?

[prime kelp]

even if i delete the database path

[paper flower]

Just check the actual error

[prime kelp]

what error?

try:
            self.bot._db = await aiosqlite.connect(database_path)
        except aiosqlite.OperationalError:
            raise MissingDatabase(f"{database_path} isn't a valid database!")

this isn't raising MissingDatabase

nothings erroring

it just loses the data

[paper flower]

        try:
            self.bot._db = await aiosqlite.connect(database_path)
        except aiosqlite.OperationalError as e:
            print(e) # Check the actual error!
            raise MissingDatabase(f"{database_path} isn't a valid database!")

[prime kelp]

dude

raise MissingDatabase(f"{database_path} isn't a valid database!") isn't triggering because self.bot._db is created without an error...

[paper flower]

I just want to check actual error message of OperationalError exception 🤷

[prime kelp]

you cant...

because self._db = await aiosqlite.connect(...) isn't erroring...

[paper flower]

Sorry, what's erroring then?

[prime kelp]

nothing

its just losing my data

but i think i might know the problem

oops

def get_database() -> str:
    try:
        database_path = os.environ['DATABASE_PATH']
    except KeyError:
        try:
            os.mkdir('./db')
        except FileExistsError:
            pass
            
        db = open('./db/database.db', 'w')
        database_path = './db/database.db'

    return database_path

[paper flower]

Doesn't aiosqlite create db automatically?

You don't have to write to it

Or even open the file

[prime kelp]

the issue is w mode truncates it

[prime kelp]

no

i just used open to create it

yeah w truncates the file

[sacred sail]

I did actually ended up converting it to a data frame after putting it in a float array in numpy because as a list some of the values were still giving NaN but when I specified d-type float before putting it into an np array then a data frame all went well

[prime kelp]

yikes

[paper flower]

You don't need to do that

import asyncio

import aiosqlite


async def main():
    async with aiosqlite.connect("db.sqlite3"):
        pass


if __name__ == '__main__':
    asyncio.run(main())

Creates db.sqlite3 automatically

[prime kelp]

oh

[harsh pulsar]

that's strange. must be some weird interaction with excel

[prime kelp]

ok

[prime kelp]

do you know if it creates directories?

i assume not?

[paper flower]

Nope, it would raise sqlite3.OperationalError: unable to open database file

[prime kelp]

awesome, cheers

[sacred sail]

For some reason it was that one sheet on that workbook that did weird things but after putting it into a list I threw it numpy array before putting in a data frame and all worked well as long as I specified the d-type. Much appreciated by the way!

[paper flower]

Use pathlib to create your directories:

async def main():
    path = pathlib.Path("./test/nested/dir")
    path.mkdir(exist_ok=True, parents=True)
    async with aiosqlite.connect(path.joinpath("db.sqlite3")):
        pass

[prime kelp]

can i not just do ```py
try:
os.mkdir('./db')
except FileExistsError:
pass

[paper flower]

pathlib is a preferred way of working with paths in python

[prime kelp]

meh

[glossy coral]

Hello, I am trying to use SQL within python. While connecting the db I have the error'error: 'Access denied for user 'root'@'localhost' (using password: YES)'' can anyone pm me and help would really be appreciated.

[grim pier]

Is anyone around to help? I cant seem to connect to my DB using 'localhost'

await aiomysql.create_pool(host='localhost', port='', user='root', password='', db='scumbot')```

it was working fine on my laptop, now ive moved over to my desktop PC. re-isntalled pycharm, XAMPP, everything. any ideas what else i could do?

[paper flower]

Are you sure your username and password are correct?

[delicate field]

Hey @grim pier!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[grim pier]

https://paste.pythondiscord.com/vojiyevupa.sql

Yeah, just default. :/

[paper flower]

Try using 127.0.0.1 instead of localhost

Also make sure your mysql server is running

[grim pier]

@paper flower Same error, both MySQL and Apache are running with XAMPP

i dont understand it was working just fine before i moved to my desktop 😄

[paper flower]

Also why your port is empty?

[grim pier]

@paper flower Just for default port 3306 ?

[paper flower]

3306 is the default port, i think you should try specifying it

[grim pier]

Tried that too :/

oh wait, i imported my DB from my laptop through PHPmyAdmin

[paper flower]

It should be an int, not sure if that matters though

[grim pier]

it didnt work, just tried changing the DB name to something so it gives me the error "db doesnt exisst." changed it back and its worked

weird

[glossy coral]

Could anyone help my db 'skies-db' wont commit to the table 'employees' and I am unsure where I am going wrong. I had it working in MySql and cant work my head around SqlLite

https://pastebin.com/XF0KUhp5

TypeError: argument 1 must be str, not sqlite3.Connection

basically this py self.mycursor.execute(self.connection, query)

the self.connection isnt a string but i need to use it to store it in the db, how do i go around this

[simple grove]

wait, the query should come first

the connection should already be part of the cursor

[glossy coral]

no such table: employees - Was the result of data provided. Try again.

[nova cove]

the table doesnt exist

[simple grove]

self.mycursor.execute(query) should work

[glossy coral]

how do i make sure it uses the table employees?

[nova cove]

INSERT INTO employees

when you are inserting the data

if thats what you mean

want it to use the table employees

[simple grove]

and CREATE TABLE IF NOT EXISTS employees (name TEXT) etc.. to create I think

[nova cove]

^

[glossy coral]

okay thank you

[torn sphinx]

what does "fast delivering" mean tho? it would be pretty concerning if your database of choice didn't fall into that category

[high geyser]

I think it's a document oriented database for the most part, you can model your schema as per your applications needs directly. With mongodb you use embedded documents

[gleaming jacinth]

how do I upload midi files to mongodb

[rare schooner]

do we have someone here who´s working with a python influxdb connector?

[civic lodge]

need that query or something equivalent to swap for transactions.BalanceChange

I'm not sure how it'd be done

select customerdata.CustomerID, customerdata.fullname, customerdata.accountnum, transactions.BalanceChange, balance.Balance,
transactions.BalanceChange + balance.balance As CurrentBal from customerdata
JOIN balance ON (customerdata.CustomerID = balance.CustomerID)
JOIN transactions ON (customerdata.CustomerID = transactions.CustomerID)
WHERE transactions.BalanceChange + balance.balance >= 5000
ORDER BY customerdata.CustomerID;

[paper flower]

Hello, wdym by swap?

[raw saffron]

What do you need?

[blazing dawn]

I'm using postgresql
I tried searching for jsonb functions but was unsuccessful in getting the complete answer to my question..

I have only 1 question:
How do I do an upsert statement which inserts a value of 1 with the key if key doesn't exist and on existence updates/increases that key's value by 1

for example in the image, tester_cards = {"1":0, "2":1}

I wish to add an upsert statement that can add key {"3"} with value 1 and if the key already exists then increase the value of that key by 1

From my searches on the net the only thing I've found is how to merge dictionaries which can be useful to add a key and value into the existing dictionary through ||

where I could do tester_cards || {"3":1} however I failed to find how to check whether 3 already exists or not and how to update increment the key's value instead if it already exists..

Any assistance would be helpful 🙇

[fading patrol]

If I follow I think this is what you're asking? https://stackoverflow.com/questions/25957937/how-to-increment-value-in-postgres-update-statement-on-json-key

[glossy coral]

how do i delete all data from a table?

[nova cove]

DELETE FROM table_name;

[glossy coral]

had this error

    self.commit()
TypeError: 'NoneType' object is not callable```

[nova cove]

wheres the rest of the traceback

[glossy coral]

Traceback (most recent call last):
  File "C:\Users\maxth\AppData\Roaming\JetBrains\PyCharmCE2021.2\scratches\scratch_4.py", line 112, in <module>
    main()
  File "C:\Users\maxth\AppData\Roaming\JetBrains\PyCharmCE2021.2\scratches\scratch_4.py", line 109, in main
    database.delete_table()
  File "C:\Users\maxth\AppData\Roaming\JetBrains\PyCharmCE2021.2\scratches\scratch_4.py", line 13, in delete_table
    self.commit()
TypeError: 'NoneType' object is not callable```

        self.mycursor.execute("DELETE FROM employees")
        self.commit()```

[nova cove]

this function in a class?

[glossy coral]

yes sir

[brave bridge]

@glossy coral Can you show the whole code?

!paste

[delicate field]

Pasting large amounts of code

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

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[glossy coral]

https://paste.pythondiscord.com/uzibecazuj.py

it might look shite but im just trying to get the basics in and make sure they work before continuing

[radiant elbow]

The line self.commit = self.connection.commit() should be self.commit = self.connection.commit

[glossy coral]

AHHH okay thank you let me fix that

i need to learn to re read my code properly i find it so hard

[torn sphinx]

@foggy owl

this is how you would manually add a record to your rows

or you can use aiosqlite

@commands.command()
async def newaccount(self, ctx) -> None:
    try:
        await self.db.execute(f"INSERT INTO bank (userid, money) VALUES ({ctx.author.id}, 0);")
        await ctx.send(f"{ctx.author.mention} new account has been opened")
        await self.db.commit()    

yes this is bad but im not getting user input so im fine

[foggy owl]

so let's say I have a db stored with some data, i can't read it?

[torn sphinx]

by read wdym

[foggy owl]

like

look what's inside the tables

[torn sphinx]

well youll use the browser rn bank is a table