databases

[fiery anvil]

I think it’s how they keep referring to the same thing using different terminology

[barren minnow]

if anyone cares, i fixed it by flushing privileges 🙂

[paper flower]

What are you having troubles with? I'm willing to help

[fiery anvil]

@paper flower I’ll look at it more tomorrow, kind of worn out from reading the docs and playing with it all day

[paper flower]

Sqla docs are actually quite good, there's a lot of solutions for common problems, but yeah, it can be hard to understand some things

Plus certain features lack documentation

[soft abyss]

Udemy has courses on MySQL which u can use to help teach yourself. I'm doing that rn. Maybe I could help u if needed

[zealous burrow]

hey

how can i setup database?

[torn sphinx]

using mySQL or Python: https://www.guru99.com/python-mysql-example.html#creating-database-in-mysql-using-python

[torn sphinx]

how many of you here use sqlite3 like me?

[fading patrol]

However you want to. What are you trying to accomplish?

[fading patrol]

A lot!

[vapid mist]

what if i have list like [<Record guild_id=927928652409233419>, <Record guild_id=921162601260527618>] and want [927928652409233419, 921162601260527618]

[brave bridge]

Then you can use a list comprehension: [record["log_channel"] for record in records]

[crude ginkgo]

Hey... Would anyone happen to know a good ORM reference for SQLAlchemy 2.0 (1.4)? The documentation is confusing the heck out of me

[languid hull]

Any one who uses mongodb know how I can do this?

I get this error

[ashen drift]

can anyone help me with this error in mongodb ?

[coarse pewter]

    ("test", "123", "lust"),
    ("apple", "343", "fruit")
    ]

con = sqlite3.connect("data.db")
cur = con.cursor()

cur.execute('''CREATE TABLE tblData(
    id INTEGER PRIMARY KEY,
    app TEXT,
    password TEXT,
    e-mail TEXT)
    ''')
cur.executemany('INSERT INTO tblData VALUES (?,?,?)', daten)

for row in cur.execute('SELECT * FROM tblData'):
    print(row)

con.commit()
con.close()

Does anyone know why this error message appears in SQLite?
Traceback (most recent call last):
PasswordManager\PasswordManager\db.py", line 13, in <module>
cur.execute('''CREATE TABLE tblData(
sqlite3.OperationalError: near "-": syntax error

[tired stratus]

e-mail
You should enclose this with quotes, but I suggest you use _ instead of - to avoid having to do that every time

And i'm not even sure if sqlite lets you enclose that with quotes, renaming the column is what you should do

[fading patrol]

Have you followed through any tutorials relevant to what you're trying to do? The documentation is a bit easier to understand when you have that practical context

[coarse pewter]

@tired stratus Thank you

[crude ginkgo]

To what I could find yes, currently I am trying to get the icontains() function to work since ilike doesn't seem to want to. Its just a DB of file paths to my QC files so I can search by metadata about the file, and i would like it to pull up any partial SN's i toss at it with the icontains but yeah. SQLAlchemy 2.0 notation is too new for many tutorials that are moderately easily found

[torn sphinx]

Sql injection

Lol

[barren minnow]

Sounds good, I might give udemy a go

oh, it's not free 🤔

[crude ginkgo]

with udemy

check if you like the way the instructor sounds in the sample before buying

its weird but your gonna be listening to 40-120 hours of their voice and trying to learn concepts. Nothing good will come of your $20 if you can't understand the instructor

[barren minnow]

that'd good advice

I'd just prefer to be less reliant on buying courses to learn things

[crude ginkgo]

well, mySQL is just a dbms over SQL so is it SQL your trying to learn or the syntax of the specific data structure?

[barren minnow]

I'm mostly learning for a personal project. I want to create an easier way to store my data. So I don't care about learning any specific implementation of SQL

I'm just using MySQL to get a grasp of the SQL language before I move on to using a python library

[crude ginkgo]

How do you like to program? Object oriented, functional, reactive, etc?

[barren minnow]

I don't have a preference really

[crude ginkgo]

Also for web apps or for standalones?

[barren minnow]

I havent written very complex programs anyway

[barren minnow]

either

at the moment its stand alone

at some point i might make the project available through a website

[crude ginkgo]

sqlalchemy and alembic would be your friend except that they just switched to 2.0 so the documentation is kinda messy and the tutorials are basic at best. Sqlalchemy is basically a driver wrapper overtop of dozens of different database dialects so you can switch between them decently easily, it also has two "modes" called Core which uses more SQL like syntax and ORM (object relational mapper) to make it object oriented and more "pythonic"

MongoDB also has an ORM for their specific DB syntax which is used just about everywhere

[barren minnow]

🤔

[crude ginkgo]

and PsycoPG2 is one specifically built for PostgreSQL which is a rather widely used enterprise grade DBMS

[barren minnow]

I'd like to learn a few different database libraries/languages anyway

I'm just starting with MySQL

[crude ginkgo]

Sqlite3 is built into python

[barren minnow]

Is there a good set of resources for each python-database language/library pair?

[crude ginkgo]

most of the time they dont change much, just some languages use DISTINCT instead of UNIQUE and sqlite3 doesnt have all the data types PostgreSQL has yadda yadda

SQL is the language, MySQL is the implementation

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

theres the mysql docs for the python connector

[barren minnow]

yeah, and even if they weren't that similar i assume learning any one would be worth it

like, instead of spending time trying to pick the "perfect" language

just learn a language, and the skills will transfer

[crude ginkgo]

Most people default to PostgreSQL since its free and fits the SQL specification the tightest (Fact may be dated)

its just overwhelmingly huge

[barren minnow]

I'll get to it when i get to it 😛
do you know of a good general server for databases?

I mean a good discord server 😛

[crude ginkgo]

nope lol

sorry

[barren minnow]

no problem

[torn sphinx]

is their any good tutorial for sqlite?

[soft abyss]

nah its acutally pretty good ngl. they do it step by step lol. you need to watch the preview videos and determine if the voice is good for u or not

[crude ginkgo]

^^^

[plain moat]

Is there a way to merge records with the same id, such that the record with the max value in the column will stay and all the other be deleted?

postgersql

[paper flower]

I don't think multiple records should have same ids

[plain moat]

@paper flower I know, I want to do this edit and then can add a constraint

[paper flower]

Aha 🤔

I have something in mind, let me test my query first

@plain moat How many records is in your table?

[paper flower]

I guess something like this should work:

create table table_name
(
    id integer not null,
    max_column integer not null
);

This would select all records that don't have max(max_column) grouped by an id

select * from table_name where (id, max_column) not in (
    select
        id,
        max(max_column)
    from table_name
    group by id
)

Data in the table:

id,max_column
1,50
1,99
1,100
2,50
2,101

Records selected by inner query (which wouldn't be deleted):

2,101
1,100

You just need to swap outer select for a delete

[sly pivot]

how do i make a python funtion that goes through exccel to serch and reaplce data

[nova cove]

you got a CSV file?

[sly pivot]

@nova cove .xlsx file

[nova cove]

oh that works

https://xlsxwriter.readthedocs.io/

[sly pivot]

@nova cove it just clers evrything

to type it in

i dont want that

that i just want to filll ceratain ccell

[hazy mango]

What would be the appropriate way to "cache" database query results? Something like caching all the valid login usernames (result of SELECT username FROM logins).

I'd also need the "cache" to be mutable, so that if p.e a new login is created I can update the cache without having to do another SELECT statement after my INSERT.

[paper flower]

🤔 Why you want to do that?

[hazy mango]

Really just a case of limiting database calls

The usernames p.e. gets updated like maybe once an hour or something, so it makes no sense to be fetching for each login request within that time frame when it's just the same data (which equates to ~40ish redundant calls per hour)

If it's not worth the hassle then I'll leave it, but I was hoping there is a smart way to do this

[paper flower]

It's not really worth the hassle as you said, if you have an index on your username selecting it (via = operator) wouldn't take THAT long

Also if you don't have that many rows in your table even without index it would be pretty fast

[hazy mango]

Yeah there's not many rows

In the logins it's just username, salt, hash, account_id

[hazy mango]

When would caching be "worth the hassle"?

[brave bridge]

well, if the database is remote, then latency could be a problem

[paper flower]

if you have multiple instances of your app then maintaining the cache across them wouldn't be worth it, okay, we could use something like redis here, but then we're back to the square one (because of the network latency)

[paper flower]

For complex aggregates that could potentially query your whole database you could use app-level caching (e.g. ttl cache in python), or something like redis

[paper flower]

By remote you mean another geolocation or a different machine?

If it's close then it shouldn't be that big of a problem

[brazen charm]

Not sure of the entire context

but err

why does this need to be cached in the first place?

Your databases' ability to cache is great, don't destroy it's the ability to cache by putting another layer in front of it.

if you're anticipating doing millions of OPS and need low latency then either have something like Postgres on strong enough hardware and actually setup to use the hardware correct, or use something like Scylla.

Neither system is designed to work with another system like Redis, their buffer pools are far more intelligent than what you can likely do yourself.

[paper flower]

I think @hazy mango wanted to cache taken usernames for login requests, which doesn't make a lot of sense to me

[brazen charm]

just hit the DB again

it doesnt care

[paper flower]

Yep

[brazen charm]

if its getting hit enough it'll just have it cached in the first place

[paper flower]

As i mentioned, you might want to cache some complex db operations/aggregates

That potentially could query whole tables

[brazen charm]

I mean even then postgres has alot of utilities that Redis doesn't for that

[hazy mango]

I didn't realise there already was caching tbh (I'm using postgres btw)

[brazen charm]

if you have these massive queries and aggregations postgres will JIT compile the query and cache it to begin with so there is that

[hazy mango]

I don't really have any big queries

The biggest would be a double foreign key lookup

[paper flower]

How would it really know if it needs to be re-cached? 🤔

[brazen charm]

realistically, postgres is going to be fine dishing out what ever you give it

[brazen charm]

i mean this the the entire point of the query planner and it calculating costs

[hazy mango]

A select on booking.center_id --> center.sport_id --> sport.name would be the biggest query

[hazy mango]

Gtk, thanks

[paper flower]

I mean, if i query some user with say id=42, and then query it again - db would still hit the ram/disk/whatever the dable data might be available atm

[brazen charm]

probably

[paper flower]

If your table fits into your memory it would query that

[brazen charm]

if the page isnt already still in cache on postgres' side the OS will probably still have it in the file cache

[paper flower]

I don't think there's some implicit caching going on for your queries

[brazen charm]

for the record though if you do run into perf issues, make sure you actually allow postgres to use the resources you give it. By default Postgres is set up to run on a few hundred MB of ram

[brazen charm]

You DB is caching all sorts

depending on what it's caching system is doing which differs from DB to DB

[hazy mango]

How would I increase it if I needed to?

[brazen charm]

your postgres.conf contains the general settings

[hazy mango]

[paper flower]

by sorts you mean indexes? 😅

Yeah, but no results would be cached by your db

[brazen charm]

indexes != cache

[paper flower]

Data, execution plans, but not the results of the queries

[paper flower]

Well, yes

But postgres caches indexes

[brazen charm]

it will priorities caching indexes

But the system is able to consider alot of things when it chooses what and what not to cache

this the whole basis of why putting a redis cache infront is often a bad idea

[paper flower]

What it has to do with querying results anyway?

[brazen charm]

because you disturb the decades of work going into the database's design

[paper flower]

Well, it depends

[brazen charm]

in 99% of cases you do not need redis

infact in some systems and databases like Scylla you're specifically recommended not to have an external cache at all

[paper flower]

If you have multiple instances of your app that would have to use the same version of cache you probably need to use it to get consistent results from the cache

[brazen charm]

because the database is built around having a high hit rate cache

[paper flower]

As i said, there's some things db can't and won't cache

[brazen charm]

you'd be suprised

In postgres' case, it can, literally potentially cache literally anything whether it chooses to or not because it utilises the OS' file cache and double buffers.

which is why theres a whole config section where you can tell it how much file cache it's probably going to get

[paper flower]

If you have a query that takes multiple seconds and you can't optimize it for whatever reasons, i'd probably stick cache in front of it and be done

be it redis or in-memory cache

[brazen charm]

I mean at work we have some queries that take minutes or even hours to run

but redis or a in-memory cache sure as hell aint gonna help that lol

mostly because the amount of data being processed just wont fit to begin with.

but also because it takes in so many different things that you'd spend most of your time invalidating the cache.

which is fun 🙂

[paper flower]

I'm talking about results of these queries anyway, they could be not that large

Say i want to rank certain items in some complicated way, query would take 30 seconds for example

You could just take ids of these items and cache them

afterwards you won't have to perform your complex query again, just take these id's and do a where id in (...ids) query

basically cutting time of your query from 30 seconds to less than a second

If you absolutely need fresh data, and not cached from say 5-10 minutes ago then any form of caching wouldn't help

But in most cases if you're doing simple filtering you don't need any caching, your queries would be pretty fast

[cunning dove]

guys, I am currently creating a text-based game from python
so for that I need a database connection
in which I can import the bank balance the player has
so any suggestions of database to connect with my game for that?

[paper flower]

If it needs to run locally then sqlite would be a good choice

[torn sphinx]

Yes I agree with you. Caching is actually useful and would be the correct thing to do where something takes long to process. Most of it depends on the environment your working with and the resources you have. For some applications a query taking 60 seconds is fine whereas for others a query taking 10 seconds is way too long. For example, with some projects any web requests we have longer than 10 seconds due to slow queries will just time out to avoid large aws costs. So in such cases caching is necessary.

[paper flower]

Some users would just leave after 5 seconds of waiting, but for management/internals apps even 30s could be ok

[torn sphinx]

But generally you won’t cache unless you are actually having performance issues. For example it’s common in some SPA to have a lot of caching to make the wait time less like you say.

[paper flower]

You generally don't need to use cache in spa applications, but if you could fetch something from cache automatically (e.g. using Apollo library when working with graphql) with no extra cost - why not? 😅

[torn sphinx]

Again it depends on the requirements and what the app is doing. For example we have an SPA which recommends products based on a series of questions. We could look up the question, answers and recommendations each time but it would result in a couple of seconds wait time. For a global company with users in every country this can be a lot of queries. The solution we found was to cache the product questions, answers and recommendations, and refresh the cache whenever there is a change to the underlying data. This makes sense as you’d not be losing anything rather gaining.

[crude ginkgo]

Im not sure of the behind-the-scenes on XLSX, but the way you load those into memory usually is using a delimiter of some form that "splits" the data into seperate pieces beacuse with open(filename, mode) as file: opens it like any other document and reads it line by line which could be just about anything in your case. Try to see if you can convert it to comma separated values (CSV) or find what delimiter excel uses internally to identify data. Then its either just a bit-length offset to the cell or counting the delimiter until you hit the cell you want to append. Just also note it may end up shifting or replacing data if theres already something in that cell.

good luck

[crude ginkgo]

so you basically did a summary table of the common queries and indexed it so it was a fast lookup or was it a specific in-memory caching system that generates based on the requests received and kills the cache when not accessed in x.time()?

[torn sphinx]

Get the data returned from the queries, transformed it into json format and cached that. Then whenever a product or question/answer flow is updated invalidate the cached data.

Redis used for this

[crude ginkgo]

and it handled that much load? Maybe I SHOULD learn redis...

[torn sphinx]

It’s more to do with the value size

[crude ginkgo]

Don't suppose anyone would know if there's an easy way to 'paginate' SQL queries so I only pull 1000 of the 10M records at a time from the actual DBMS or SQLAlchemy? Really trying to make as few requests as possible instead of doing a COUNT and constructor dividing count into equal parts and having to work off index or cursor pos... (thats my plan b at least)

[torn sphinx]

Generally pagination is achieved by using order by, with a offset and limit.

Offset is where to resume from for the next page of data, and limit would be how many items you want to get

[crude ginkgo]

Okay... Would it be an OrderBy subquery to gather the data and then the OFFSET && LIMIT query so it kept the data pulled between requests??

[torn sphinx]

But orms usually support this. You may want to check with sql alchemy

[crude ginkgo]

SQLA is a mess rn because they are trying to introduce 2.0 which has different constructs and idioms than anything before it

[torn sphinx]

Order by is to just order the data so when you next apply offset and limit you get the data that is next in line. This way you don’t get rows you have previously already gotten.

[crude ginkgo]

so OFFSET for the second query is just the position in the table of the last row plus one of the first query?

say LIMIT = 1000, if i want the next 1000 results after i would select OFFSET = 1001 right?

[torn sphinx]

No set offset to 1000

It will get from and including 1001

Also loading and hydrating 1000 records may be a bit too much. You should try with a lesser amount first. Check the memory usage if having that many.

[crude ginkgo]

The record size per unit is 512 bytes according to my math, so I should be okay with 32gb of ram

[torn sphinx]

But what is the app doing?

Does the user see those 1000 records?

[crude ginkgo]

it will go on a KivyMD.Pagination scrollview

[torn sphinx]

If so then you don’t want to be loading 1000 records for a user like that even if your server can handle it

[paper flower]

@crude ginkgo I think you could use Result to use db-side cursor 🤔

[crude ginkgo]

its a personal desktop project to pull up Quality Documentation metadata so I don't rely on windows shitty search

[torn sphinx]

Like you don’t see Google loading even more than 20-30 results even though they have such power to do so

Not good for UX and users device may not be able to handle it

[crude ginkgo]

True

most places (online stores) max out at 100 or 300 if they aren't using infinite vertical load scrolling

also the example was just for easy math, I have 6000 records max so I might get 30 hits total

[crude ginkgo]

I am intrigued, i'm assuming postgresql or mysql required? Do you use the same session or a different one? Does it count as a single transaction and locks that cursor until its finished or does it event schedule?

[paper flower]

https://docs.sqlalchemy.org/en/14/core/connections.html#using-server-side-cursors-a-k-a-stream-results
🤔

I'm not sure which DBAPI's support it though

[crude ginkgo]

any of the ones with "server-side" cursors it seems

[torn sphinx]

I think it refers to cursor pagination.

There’s 2 types. Limit and offset and cursor.

[paper flower]

No, not really

It doesn't refer to cursor pagination

[torn sphinx]

Limit and offset is generally with less complexity

[paper flower]

Cursor pagination is faster though 😉

[torn sphinx]

Then what are you referring to. Link to documentation?

[paper flower]

Yep. docs describe how to use server-side cursors

from sqlalchemy import create_engine, Column, Integer, String, select
from sqlalchemy.engine import Result
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()
engine = create_engine("postgresql+psycopg2://postgres:password@localhost/database-name", echo=True)
Session = sessionmaker(bind=engine)


class Book(Base):
    __tablename__ = "books"

    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)


def main():
    Base.metadata.create_all(bind=engine)
    with Session.begin() as session:
        for i in range(10_000):
            session.add(Book(title=f"Title - {i}"))

    with Session.begin() as session:
        result: Result = session.execute(
            select(Book).execution_options(stream_results=True)
        )
        # You can use zip + range in case you need to limit amount of partions you'd get
        # also you could use limit
        for index, partition in zip(range(10), result.partitions(10)):
            print(len(partition))


if __name__ == '__main__':
    main()

[crude ginkgo]

result: Result = session.execute(
    select(Book).execution_options(stream_results=True)```

how does that work?

the result: Result part i mean

[paper flower]

it's a type hint

You should visit #type-hinting 😅

[crude ginkgo]

YOU CAN USE THEM LIKE THAT?!?

[paper flower]

Seems like built-in sqlite driver doesn't support db-side cursors 🤔

[crude ginkgo]

I only ever used typehints for my function calls...

[paper flower]

Like?

I simply type-hinted it as Result for myself since my IDE doesn't know even what session is 😅

[crude ginkgo]

sqlite does not have an implementation of "server side", it has a single cursor that it uses to traverse everything

[paper flower]

Works with postgres though 😉

[crude ginkgo]

true

Enterprise technology ftw

THX @paper flower @torn sphinx for all the help

[paper flower]

sqlite is still great for small applications and using sql on client side

[crude ginkgo]

true

and it migrates to larger DBMS' cleanly usually

[paper flower]

So, anyway, cursor pagination usually refers to a different thing

[stuck hamlet]

why does my createdb not work in terminal

or brew createdb myDB

[rich anchor]

why I can't do this in a tuple but I can in a list?

[brazen charm]

thats a set

and probably because it's not hashable

The error python throws will probably give you more information

[rich anchor]

💀

lol true

[rich anchor]

what does that mean?

[paper flower]

 random.sample(population, k, *, counts=None)

    Return a k length list of unique elements chosen from the population sequence or set. Used for random sampling without replacement.

Return a list, lists are not hashable, it's done on purpose since they're mutable and hash would change

You can convert result of random.sample to tuple if you really want to hash it

[rich anchor]

tks

[limpid nexus]

not really python question but pretty simple (I guess)
I have an sql code like that. I want to count number of transactions over 10$ in each exchange.
How to add default zero count here, so that it shows all the exchanges?

    SELECT count(1), exchange
    FROM orders o 
    where price > 10
    group by exchange

[delicate field]

Hey @vestal trellis!

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

[round valley]

Does anyone know why trying to connect to a corporate MS SQL server using pyodbc on a local Windows install (MS Store Python via Jupyter/VS Code) works when using Trusted_Connection=yes, but not when specifying UID=user;PWD=pass? The server is configured in mixed authentication mode and I have tried all variations of domain credentials (domain\user, user@domain).

Only thing that I can think is that Windows store installs are semi-containerised, so perhaps there's something blocking it there on our corporate firewall. The UID connection method works fine via our Linux server using FreeTDS.

[round valley]

Should have mentioned, this is the error I'm receiving: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15 . UID and PWD are definitely correct.
Not getting the additional status code reported back unfortunately. Have tried servers on both MS SQL 2014 and 2016. Am using the {ODBC Driver 17 for SQL Server} driver.

[round valley]

Have also tried with pymssql and still getting the same error. Assuming it's gotta be something wrong with my personal setup/way Python is installed.

[gaunt surge]

HelloHi,
How to ignore case, with pymongo?

I know there is $regex, example:
"Username": {'$regex': f"{username}", '$options': 'i'}}

but that would find all names with they key word included

[woeful torrent]

Hi, is there any way I can query all instead of any with a relation in sqlalchemy? Basically I need this:

tag_ids = [self._db.query(Tag.id).where(Tag.name.in_(["a", "b", "c"]))]
self._db.query(Article).where(
            Article.tags.any(Tag.id.in_(tag_ids))
        )

Which gives me all articles that have either tag a, b, or c - however, I'm trying to get all articles that have all 3 tags at the same time

I think it should be possible by subselecting all tags for an article where Tag.id.in_(tag_ids) and the count is 3 for example

But I can't find a way to express that with the ORM API

In fact, I can't even find the part of the documentation that lists the methods that are available for a collection like Article.tags

[paper flower]

from sqlalchemy import Table, Integer, Column, ForeignKey, String, create_engine, select, func
from sqlalchemy.orm import declarative_base, relationship, sessionmaker, selectinload, aliased

Base = declarative_base()
engine = create_engine("sqlite://")
Session = sessionmaker(bind=engine, future=True)

article_tags = Table(
    "article__tags",
    Base.metadata,
    Column(
        "tag_id",
        Integer,
        ForeignKey("tags.id"),
        index=True,
        primary_key=True,
    ),
    Column(
        "article_id",
        Integer,
        ForeignKey("articles.id"),
        primary_key=True,
    ),
)


class Tag(Base):
    __tablename__ = "tags"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False, unique=True)
    slug = Column(String(255), nullable=False, unique=True)

    articles = relationship("Article", secondary=article_tags, back_populates="tags")


class Article(Base):
    __tablename__ = "articles"

    id = Column(Integer, primary_key=True)
    title = Column(String(length=255), nullable=False)
    slug = Column(String(length=255), unique=True, index=True, nullable=False)

    tags = relationship("Tag", secondary=article_tags, back_populates="articles")


def main():
    include_tags = ["tag-one", "tag-two"]
    query = select(Article).options(selectinload(Article.tags))
    article_alias = aliased(Article)
    tags_subquery = (
        select(func.count(Tag.id))
            .join(article_alias, Tag.articles)
            .filter(article_alias.id == Article.id)
    )
    include_tags_subquery = tags_subquery.filter(Tag.slug.in_(include_tags)).scalar_subquery()
    query = query.filter(include_tags_subquery == len(include_tags))
    print(query)


if __name__ == '__main__':
    main()

ig something like this should work

Generated sql:

SELECT articles.id, articles.title, articles.slug 
FROM articles 
WHERE (
    SELECT count(tags.id) AS count_1 
    FROM tags
        JOIN article__tags AS article__tags_1 ON tags.id = article__tags_1.tag_id
        JOIN articles AS articles_1 ON articles_1.id = article__tags_1.article_id 
    WHERE articles_1.id = articles.id AND tags.slug IN (__[POSTCOMPILE_slug_1])
) = :param_1

@woeful torrent

[woeful torrent]

Hmm what do options and aliased do here?

[paper flower]

you need options here if you want to eagerly load something, i used aliased to join our tags with an "aliased" articles table, so i could later compare it with the outer one

Otherwise where clause like articles.id = articles.id does not make sense

[woeful torrent]

uhm I also just noticed my backref doesn't seem to work

[paper flower]

I usually explicitly specify back_populates

[woeful torrent]

    tags = orm.relationship(
        "Tag",
        secondary=Table(
            "tag_relations",
            Base.metadata,
            Column("tag_id", ForeignKey("tag.id"), primary_key=True),
            Column("article_id", ForeignKey("article.id"), primary_key=True),
        ),
        backref="articles",
    )

Article.tags works but Tag.articles gives me an AttributeError

[paper flower]

try using back_populates and explicitly specify your relationship on Tag

[woeful torrent]

You mean back_populates instead of backref here?

[paper flower]

yep

[woeful torrent]

hmm I still get the attribute error

[paper flower]

But you'd have to explicitly specify a relationship on another model

Just look at my example

[woeful torrent]

yeah but the approach I used is taken directly from the sqlalchemy examples

with the secondary table defined in the relationship directly

association_table = Table('association', Base.metadata,
    Column('left_id', ForeignKey('left.id'), primary_key=True),
    Column('right_id', ForeignKey('right.id'), primary_key=True)
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

I mean I did basically exactly this

But all I get is AttributeError: type object 'Tag' has no attribute 'articles'

It's bizarre

OK, turns out it fixes itself if I call orm.configure_mappers() manually

not mentioned anywhere of course

[brazen salmon]

Hi, for example, I have these 2 tables:

MEMBERS:

UUID, NAME
1234  JOHN

GROUPS

UUID, NAME, MEMBERUUID(FK)
9876  STAF  1234

Does this make sense?

[fluid glen]

I'm using sqlite3 for a database, and I want it to log transactions, because it's going to be used for virtual money

how could I go by doing that

[woeful torrent]

Honestly for virtual money to keep track of all changes I'd probably use a blockchain (basically like a git repository). It keeps a full history and includes integrity checks

[fading patrol]

Yes, this looks pretty standard

[woeful torrent]

So I tried collecting tag frequencies with this query:

query = self._db.query(Tag.id, sqlalchemy.func.count(Tag.id)).join(Tag.articles).where(Tag.id.in_(tag_ids)).group_by(Tag.id)

which results in this SQL query:

SELECT tag.id AS tag_id, count(tag.id) AS count_1 
FROM tag JOIN tag_relations AS tag_relations_1 ON tag.id = tag_relations_1.tag_id JOIN article ON article.id = tag_relations_1.article_id 
WHERE tag.id IN (__[POSTCOMPILE_id_1]) GROUP BY tag.id

While this works, it also includes the unnecessary extra join with the article table that slows it down noticeably. Any idea how I could prevent that unnecessary join from being added?

[quasi river]

am i allowed to ask qs ab pandas and df in here?

[torn sphinx]

data-science-and-ai would be better, people here might not know what those are

[torn sphinx]

Hi guys, this is my database I use to record games.

p1,p2,p3,p4 = team 1
p5,p6,p7,p8 = team 2
s1 = team 1 score
s2 = team 2 score
elodiff = elo difference

I'm trying to create a command that loops through the database to see WINS / LOSSES versus EACH player in the database for a certain person! If you guys could help me in the right direction that would be great thanks.

[trail patio]

Hello

does anyone know how I can install TinyDB on a ubuntu serveR?

[vapid mist]

using asyncpg, whats the correct "data type" for an array? i tried anyarray and get asyncpg.exceptions.InvalidTableDefinitionError: column "verified_users" has pseudo-type anyarray

[paper flower]

What "frequencies" exactly you want to collect? 🤔

I guess amount of articles that have certain tags? e.g. 5 articles tagged with "python" and 2 tagged with "databases"

[paper flower]

@woeful torrent https://gist.github.com/ThirVondukr/48a7a19871497ff7d7bccb282895d711

[severe galleon]

nice#

[mystic trout]

hello how can I create a db with the name user and add there, a row is a user columns items (there are a lot of about 25 items and a column is needed for each) and then get someone specific and not everyone to display a ping in the embed if you are ready to help (db - sqlite3)

[mossy onyx]

how to store data online in mysql database and access it through python from any other machine

[torn sphinx]

the easiest would be to use cloud hosting for it: https://www.digitalocean.com/try/managed-databases-mysql
You could use a 2nd device, but then you need to set up the networking yourself to allow outgoing&incoming connections
Also: https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

[mossy onyx]

ok bro leme see

[dawn moss]

I have doubt with postgres

now I was wondering lets say computer 1 has database in pgadmin

then can I connect to that database from any other local machine?

using the username and password?

also why is username "postgres" same for every local machine?

[brazen charm]

because thats just the default user and password

the idea being that you change it to something else when you're ready to deploy or sharing it across the network not just localhost

[dawn moss]

like if other machine user wants to use my database then what will he have to do?

enter localhost and postgres etc?

I am really confused about this ;-;

[dawn moss]

also how to deploy my database for everyone with a password or share it?

a tutorial link will be really great ;-;

[brazen charm]

docker is probably the easiest option

https://towardsdatascience.com/how-to-run-postgresql-using-docker-15bf87b452d4

[brave bridge]

In PostgreSQL, how can I find all records where the id is one of a list?

Like, find apples with id being one of (1, 2, 6, 17).

It seems like you can't pass the (1, 2, 6, 7) as a parameter in a prepared statement, so that's what I'm having trouble with

You can do it with something like SELECT foo, bar FROM apples WHERE $1::jsonb @> id::text::jsonb, but it does a seq scan in that case...

oh, I can do id = ANY($1), interesting

[north rover]

GUYSSSS

I NEED HELP

this part of the code is throwing error

[fading patrol]

I don't know ttk but it's showing you that it's expecting an integer and getting a string

[dawn moss]

like I am already using asyncpg to connect to my database

now what I am confused about is the user

postgres is pg admin user , right?

the db connection is working very well

but I think I am confused about the user part

isnt user supposed to be unique

[fading patrol]

If your database is not publicly exposed I think that's pretty standard

[dawn moss]

oh so like only my local machine can access it if its only in pg admin?

[fading patrol]

Right, if it's running only on your local machine it's fine. If you're accessing it over the public internet you definitely need a secure username and password

[plain moat]

In the case of stored generated column, for example if we are adding to table_2 : column_ = table_1.column_1 / table_1.column_2, if table_1.column_1/column_2 are updated, does it effect the stored generated column?

[paper flower]

select id from some_table
where some_table.id in (1, 2, 3, 4, 5)

Django uses select in when loading related objects via prefetch_related and sqlalchemy does the same with selectinload

Can't you pass something like list/tuple when using prepared statements? Is there any errors?

[brave bridge]

I already figured it out -- you can do it with id = ANY($1) and pass a list as $1

I'm using asyncpg which uses prepared statement instead of manually formatting the query

[paper flower]

🤔 ig you could also cast it to int[]

Why don't you use something like sqlalchemy btw? 😅

[burnt turret]

i'd read somewhere that id in $1 wouldn't be valid syntax or something (im guessing it was asyncpg FAQs)

[brave bridge]

yeah

[brave bridge]

does it support asyncpg?

[paper flower]

Yes

[brave bridge]

idk, I just didn't consider using an ORM

[paper flower]

You could use sqlalchemy core 🤔

[brave bridge]

I don't have any complicated queries, I don't think I want an extra dependency for building queries

[paper flower]

Imo it's more convenient to build queries using code instead of sql

[brave bridge]

Maybe I'll try SQLAlchemy some time later

SQL is also code

[paper flower]

It is, just saying that python is somewhat easier to maintain 🤔

e.g. It's easier rename columns if you need too

[brave bridge]

How do you express something like this in SQLAlchemy?

UPDATE apples SET
  oranges = COALESCE($2, oranges),
  bananas = COALESCE($3, bananas),
  cherries = COALESCE($4, cherries)
  WHERE id = $1

[dawn moss]

Let's say I want another user to have the database .. so I will have to publically expose? And if yes then how?

[paper flower]

You can dynamically add filters:

def filter_users(username: Optional[str], registered_before: Optional[datetime]):
    stmt = select(User)
    if username is not None:
        stmt = stmt.filter(User.username.ilike(username)
    ...

[paper flower]

What would coalesce do in this case though?

Would make them the same as already present values ig?

[brave bridge]

It would update the fields to the passed in values which are not NULL

[severe coral]

I remember using an ORM with another language we ended up with a ton of stored procedures

[brave bridge]

I guess you can dynamically build a query, but that will force the database server to parse a new query every time. But with prepared statements, it can parse and optimize it only once

[fading patrol]

The database is running on your personal PC? Depending on your ISP it may not be feasible. Probably easier to spin something up on the cloud. Heroku free tier is good for a small project

[paper flower]

You could use orm for that 🤔 Also if you dynamically construct queries you wouldn't need coalesce, but

update(apples_table)
.values(oranges=oranges, bananas=bananas, cherries=cherries)
.filter(apple_tables.id = apples_id)

Or if you really want to use coalesce:

update(apples_table)
.values(
   oranges=func.coalesce(oranges, apple_table.c.orange), 
   ...
)
.filter(apple_tables.id = apples_id)

[brave bridge]

oh, so it does let you use SQL functions

[paper flower]

Ofc

[brave bridge]

interesting

does it have similar stuff for JSONB?

idk who thought of these operators ->> @> <*%@#(%@#%>?---

[severe coral]

It can have a performance hit thou the ORM will construct the query

[paper flower]

I think you can use any functions 🤔

hm

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB

[paper flower]

It would cache these queries though (not the db results)

plus it's pretty fast

[brave bridge]

nice

[paper flower]

class Apples(Base):
    __tablename__ = "apples"

    id = Column(Integer, primary_key=True)
    oranges = Column(String(length=255), nullable=False)
    bananas = Column(String(255), nullable=False)


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

    apples_table = Apples.__table__
    apple_id = 42
    stmt = (
        update(apples_table)
        .values(
            oranges=func.coalesce("Oranges!", apples_table.c.oranges),
            bananas=func.coalesce("Bananas!", apples_table.c.bananas),
        )
        .filter(apples_table.c.id == apple_id)
    )
    print(stmt)

UPDATE apples SET
    oranges=coalesce(:coalesce_1, apples.oranges), 
    bananas=coalesce(:coalesce_2, apples.bananas) 
WHERE apples.id = :id_1

[brave bridge]

I'm still not sure if I'd use a query builder if I don't have dynamically generated queries.

[severe coral]

Depends on the project

[dawn moss]

I am using pg admin /psql so I assume it's on personal pc...isn't there a way to use the database on other local machine without publically exposing it ;-;

[paper flower]

Yeah, sql could be fun, but that's basically sql 😅

just in python form

[swift ridge]

is it possible to set rowtype of 2 tables combined to a variable?

[severe coral]

I have seen a few people who prefer writing SQL over ORM ... I am neutral ... If the application works without ORM and it is a simple application I probably wont add a layer.. but if It is a complex application that will benefit from a layer above the database that has the business logic...I will suggest an ORM

[paper flower]

sqlalchemy is not only an orm 😉

You don't have to use orm part really

[brave bridge]

you can separate the business logic from data access without an ORM

and if you separate by using just an ORM, you're coupling it to the fact that you use a relational database, or rather a database that this particular library supports

[paper flower]

ORM is just mapping whatever data you have in your database to classes, database itself could be relational one
If i have a set of models that use say SQLAlchemy at this moment i can swap my orm/database/whatever to another one, i just have to change the access layer

[severe coral]

Ah true but at least ORM saves some time in setting up the objects and gives you less errors compared to hand coding the containers for data and CRUD

[paper flower]

For example in sqlalchemy you could map your classes this way:

@mapper_registry.mapped
@dataclass
class User:
    __table__ = Table(
        "user",
        mapper_registry.metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String(50)),
        Column("fullname", String(50)),
        Column("nickname", String(12)),
    )
    id: int = field(init=False)
    name: Optional[str] = None
    fullname: Optional[str] = None
    nickname: Optional[str] = None
    addresses: List[Address] = field(default_factory=list)

    __mapper_args__ = {   # type: ignore
        "properties" : {
            "addresses": relationship("Address")
        }
    }

What is stopping you from switching to another orm/database ? User is just a dataclass here

[swift ridge]

is it possible to set rowtype of 2 tables combined to a variable?

[brave bridge]

Can you add your own storage to SQLAlchemy?

[paper flower]

Probably only SQL one, it's an SQL toolkit after all 🤔

[brave bridge]

Well that was my point here, if you want to decouple your business logic from the way you access data, ORM will not completely solve it

[paper flower]

Yep, orm won't solve anything really

[severe coral]

Ah are you using a JSON or Nosql...

[brave bridge]

Ideally, the business logic should not really care

Well, if there isn't a lot of logic, it is debatable whether that's useful

[severe coral]

Yes ORM solves only a subset...not a silver bullet

[paper flower]

If you use orm in your business logic then it doesn't solve anything really

[severe coral]

It does facilitate it however ... you can attach custom methods in the objects that has the rules or use these data objects in another layer of business objects... sometimes thou performance tends to be slow so they migrate those operations back to the database as stored procedures lol... I have also seen some dba denormalize schema for performance... There is a cost to abstractions

Be pragmatic whatever works

[fading patrol]

You are saying you want to share access over your local network only? That's fine, up to you if you need secure accounts or not

[dawn moss]

Yes but how's it possible ;-; .. Idk how to do it ..

[fading patrol]

What are you doing currently?

[quick jolt]

Guys how do you name different items or objects for making an random data base?

[dawn moss]

Rn I have a database in pg admin with postgres user and I can access it's tables etc

[severe coral]

Start with an ER diagram... define the entities = table , relations = primary and foreign keys

[severe coral]

So design the database first

[fading patrol]

You can do the same over your local network. Just point PGadmin to the IP of the correct computer

[quick jolt]

ok bro

[plain moat]

In a case of values the I want to exclude for example ages = [10,20,30] using df[df[age] not in ages]] does not work.
returns The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

found: ~something.isin(somewhere)

[grim pier]

Hello quick question.

Im creating a script which downloads all the logs from a game and then uploads all the details onto a MySQL DB (This script i intend to be hosted by anyone who has a server in the game)

the discord bot ive made which will sit in multiple guilds will then post the data from the MySQL DB and into the relevant guilds.

My question is I'm going to need to have DB credentials in the logs downloader which other people will have. which they can then mess the DB up?

Is there a way to hash the DB password or someway round this?

Any information would be hugely appreciated 🙂

[nova cove]

I probably wouldn’t give your database information to other people UNLESS you absolutely trust them 110% percent

Even then it is risky

And hashing by it self will not necessarily cause full security

hackers can bypass it through brute force so I would salt it after hashing it if there is a way to hash the password

[fluid glen]

I'm kind of like really stupid, if I'm using

sqlite3, how can I have something setup where like I have something kind of similar to a banking system, where I have multiple tables with multiple rows of people's accounts

[fading patrol]

This is with MySQL but should give you a pretty good idea https://www.codersarts.com/forum/database-schema/creating-bank-database-tables-using-mysql

[fluid glen]

oml ty

<3

[ionic breach]

Hello guys, i have a problem with my administration panel using django version 2.1, i am not using the latest version because i am doing a specific project, anyways, after creating a table in admin.py ‘’’admin.site.register(Product)’’’ i went to the admin panel to add a product, everything is smooth but when i pressed save it gives me this error: no such table: main.auth_user__old

I think its related to the database somehow

Can someone help

I am currently using the default db.sqlite3 that comes with django 2.1

[torn sphinx]

I just started learning Python

[ruby flint]

Would anyone know the equivalent of this SQL statement in sql alchemy?

SELECT 
    station.StationID, 
    station.Name, 
    station.StationGroup, 
    station.isRealStation,
    equipment.*,
    lab.LabName
    
    FROM station
    LEFT JOIN equipment
    ON station.StationID=equipment.StationID
    LEFT JOIN lab
    ON station.LabID=lab.LabID
WHERE station.location="San Jose"
ORDER BY 2;

THis is what I have so far:

with session_scope() as db_session:
        station_inventory = db_session.query(Station.StationID, Station.Name, Station.StationGroup, Station.isRealStation, Equipment, Lab)\
            .select_from(Station)\
            .outerjoin(Equipment)\
            .outerjoin(Lab)\
            .filter(Station.Location == session['site'])\
            .order_by(Station.Name).all()

But Lab is always outputting None when it isn't None

[paper flower]

If you're using orm then you can just configure your relationships

And use loading

[plain moat]

is there an example of how to write a function with Series as arguments?

[azure narwhal]

x = my_series.apply(my_function, more_arguments_1)
y = my_series.apply(my_function, more_arguments_2)

[plain moat]

In the case that I have a function:
df func(x,y): pass
when x, y are columns?

[azure narwhal]

you make the colums below the func

[plain moat]

Sorry I will rephrase the question.
I have a function:
df func(x,y): ... reutrn z
I want to insert x as a column and y as a column and store the return value z in a column

[azure narwhal]

you can assign multiple values to x and y like

df func[('x'= 1,2,3,4,5,6),('y' = 7,8,9,10)]

for z also you can have
return z ( a,b,c,d,e) whatever the value might be

im new to python so i might be wrong about this

[plain moat]

I see the problem I am using a constructor in the function, he does not "eat" arrays

[azure narwhal]

[grave eagle]

is it possible to store a list (keeping it as list) in a database ?

like saving a list of words with an id

[plain moat]

I am new, but what about json?

[grave eagle]

im "scared" of coruption cause i open and close it a lot

[paper flower]

Some dbs support list / array data types, why do you want to store a list though?

[grave eagle]

need to save a blacklist for queries with server id ad key

[paper flower]

I don't really understand how you want your table to look but in most cases you can use relationships

[grave eagle]

id blacklist
123123123 [bla, bla, bla,]

like this

[paper flower]

And what's the blacklist? Just a list of words?

[grave eagle]

yas

[paper flower]

Ig you could use array here if your db supports it

Or json type

[grave eagle]

using sqlite3

it doesnt

idk if storing array as BLOB is a good idea

[fleet ibex]

i dont see a problem storing your list, you can easily find your answer by looking up the db you use and store strings

[sly pivot]

def main():
    def main():
    excel_file="demo.xlsx"
    df=pd.read_excel(excel_file)
    emails="[e-mail]"
    if  emails in   df["Email"]:
        print("bcc")
    else:
        print("hellll") ```

i am trying to pandas and numpy to serch for specific data
and replace it with other funtion

i dont understand whats wronh

wrong

[cunning geode]

does anyone know a good course to learn Pyspark?

[fading patrol]

I dunno if it's the best but I was thinking to try the one on DataCamp

[cunning geode]

Thanks, I'll try that.

[torn sphinx]

eh.. #help-coconut pls :'/

[tough tapir]

this is showing an error but i have sqlalchemy installed in my pc and I am using python 2.7.10

[torn sphinx]

is this VS Code ? If so, did you add the Python path to your workspace in the settings ? VS Code requires this sometimes to find the modules properly

[tough tapir]

Yes I did

[unique cradle]

https://thecybercode.com/

[vast canopy]

hey, does anyone has experience with pymongo ? i have problems inserting documents for some reason

[grave eagle]

i have to store a list of strings in a database, that's the problem to me x3 sqlite doesnt support array

[rain plank]

You can use a separate table that stores each item of the array with an FK (that points to the initial table), or you could just separate the strings by a comma or something like that

[grave eagle]

never done it, may you explain how?

i'm studying on my own, could not find any "tutorial" about doing something like that

[rain plank]

do you know foreign keys?

[grave eagle]

not sure x3

[sly pivot]

how to find location of data in excel file using python

[fading patrol]

Openpyxl is one way https://www.freecodecamp.org/news/how-to-create-read-update-and-search-through-excel-files-using-python-c70680d811d4/

[proven arrow]

Generally when you want to store a list in a column its a smell of bad design. The solution is to normalise and abstract
the list to store as individual elements in its own table, as you have seen from other responses here. However, if you really do want to store a list in sqlite, you can store a serialised string version of it. So you convert the list into a string, and then when reading back convert it back to an array.

[fluid glen]

I'm getting this error D:
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: 1 values for 2 columns

[ionic breach]

Hello guys, i have a problem with my administration panel using django version 2.1, i am not using the latest version because i am doing a specific project, anyways, after creating a table in admin.py ‘’’admin.site.register(Product)’’’ i went to the admin panel to add a product, everything is smooth but when i pressed save it gives me this error: no such table: main.auth_user__old , I am currently using the default db.sqlite3 that comes with django 2.1

[fluid glen]

getting this error with this bit of code:

    cur.execute("INSERT INTO orders(customer, customerid) VALUES (?), (?)", (str(user), (str(cust),)))

[grim vault]

cur.execute("INSERT INTO orders(customer, customerid) VALUES (?, ?)", (user, cust))

... VALUES (?), (?) means two rows with one value, you want (?, ?) one row with two values. Your naming is also sub-optimal. Your table column and variable names are confusing.

[fluid glen]

oop

alrighty, thank you

I'm not sure what to do about the variable names, though I've been thinking about that a lot

[proven arrow]

They should be more descriptive, and use some sort of consistency when having multiple words. Snake case or camel case, with snake case being more common and widely used.

Like "customer" means what?

[fluid glen]

if I use max(ID) to get the last row I created, is it a concern that somebody else might create a row at the same time and then instead of it getting the one its supposed to, it gets the neww one

I know what customer means :v

[proven arrow]

Primary keys exist for this reason. They are auto generated by the database, and will enforce uniqueness for each row.

[fluid glen]

okay so let me explain to give some context

[proven arrow]

At this point in time you understand. As code grows using shitty convention makes debugging and readability much more difficult. Also makes it difficult for people trying to help you.

[fluid glen]

cur.execute("INSERT INTO orders(customer, customerid, items) VALUES (?, ?, ?)", (str(user), (str(cust)), (str(items))))

is what creates the new row, but I want to get the id, which is using the primary keys

because I want the bot to then message the person their order id :P

[proven arrow]

There should be a lastrowid attribute on the cursor object.

Let me check

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.lastrowid

[fluid glen]

thank you

[brave bridge]

@fluid glen also, new versions of SQLite support the RETURNING clause

[torn sphinx]

is it preferable to keep connected to a Sqlite3 database for entire time. Or connect , read/write and close connection everytime? ( I'm using it in a Discord bot with the aiosqlite library)

what would be more memory efficient , and better way to use it

[paper flower]

I'm not sure but it think it would be ok
SQLAlchemy for example maintains a pool of db connections (exception being the sqlite 😅) so it won't have to reconnect to the database every time you need to acquire a connection

[torn sphinx]

so does it really matter in terms on making multiple connection ?
like if i make 2 continuous connections (at same time) to the same database , will it cause some concurrency issues?

[steady mural]

Hi

[paper flower]

Db would be locked if you modify data from one of the connections until the transaction is done

[torn sphinx]

and what would happen for the same case if we make connection only when we want to perform an operation and close it..

same i guess?

[paper flower]

Same thing really 🤔 What are you developing?

[torn sphinx]

im making a discord bot , and im saving server prefixes in a database , so basically every time a message is sent on discord the program gets the prefix from the database , so i wasnt sure if i should keep a single connection ,or different connection for each call

[paper flower]

If you're running a single instance of your discord bot then it's probably going to be ok, though, i'm not sure about concurrent operations on a single connection 🤔

[raven trail]

What is the best / most performant Async ODM for MongoDB?

You should probably cache the prefix so you don't constantly have to make database cause every time a command is executed

[torn sphinx]

!pypi motor

[delicate field]

motor v2.5.1

Non-blocking MongoDB driver for Tornado or asyncio

[paper flower]

If you don't mind making a db call to retrieve specific server's prefix and don't want to deal with caching then it's ok

[torn sphinx]

im planning to use redis cache with it

[paper flower]

What's the point really?

Retrieving a single row from your db by some id (discord guild/server id in this case) would be super fast

If you want to bring redis then why not use something like postgresql instead of sqlite?

[paper flower]

I guess that's correct, but, instead of has you might say subscribes/subscribed for 🤔

Also it would probably be many to many relationship

if users can subscribe to many animals but multiple users can't subscribe to same animal it's one to many relationship, if multiple users can subscribe to the same animal that would be many to many relationship

as an example:

one to many
U1 -> A1
U1 -> A2
U2 -> A3

many to one
U1 -> A1
U1 -> A2
U2 -> A2 // both U1 and U2 are subscribed to same animal

If we talk about implementation in sql one2m relationship here would be implemented as a foreign key to users
m2m is usually implemented via secondary table that would contain id's of both tables (animals and users in this example)

[inland maple]

anyone here knowledge about sql?

[nova cove]

yes

this is prolly the right channel

[inland maple]

okay

i need the 3 product categories which were sold the most in 2016

SELECT count(*) AS aantal_verkocht FROM sales.orders o INNER JOIN sales.order_items oi ON INNER JOIN production.products p ON INNER JOIN WHERE EXTRACT(YEAR FROM o.order_date) = 2016 GROUP BY ORDER BY count(*) DESC LIMIT```
i need to finish this sql query

help

[grim pier]

Hello, im curerntly using MySQL in my code.

At the minute everytime i need to do something on the DB i connect to the DB

    sql = await connect_to_sql_db()
    async with sql.cursor() as cursor:```

Is there a way to keep the connection open instead of connecting multiple times constantly?

[trim lintel]

Connection pooling is for this issue you describe

[grim pier]

oh really? Is it possible to just have it constantly open and then maybe just check its still connected everytime i update it?

[trim lintel]

It will keep a collection of connections and whenever you need one it will just give you one from the pool. This way you save the connect/disconnect overhead.

[trim lintel]

You don’t even need to check if it’s open. The pool pattern will handle that. You just request a connection as normal through something like pool.aquire() and it will handle everything for you.

[umbral mountain]

can i use OR in a query (sql)?
like
WHERE name = "jack" OR name = "lucas"

tag me if reply thx

[spiral tinsel]

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

[quiet nebula]

Are there any free database providers?

[carmine anvil]

I have an integer field primary key in my sqlalchemy model, if i submit a new record without specifiying this field it should autoincrement it correct?

[fading patrol]

I like Heroku's free tier Postgres for small projects. A little slow but not that bad

[spiral tinsel]

does azure allow you to assign less resources to a certain DB if both DBs on the same server?

[spiral tinsel]

why not sqlite?

if it is a small project then it is unlikely that you would need concurrent access

[fading patrol]

True, @quiet nebula

[spiral tinsel]

oh I was asking, I think sqlite is more lightweight, is there certain functionality of postgre that you need?

[quiet nebula]

alr

[fluid glen]

I'm using sqlite3 for context
When should a db be closed?

I hear its good practice but I have no idea besides that

[spiral tinsel]

sqlite is a flat file

[fleet ibex]

im experimenting with dicts and im not sure its going to work out.. I am attempting to construct a method for storing and changing relationships.. ie, master[slave1, slave2] master2[slave1,slave2].. every master/slave is a unique id.. and i want to move things around.. like slave 1 from master1 to master2... suggestions for a way to implement?

[torn sphinx]

i am trying to make a bot where if you dm it once, it will give you a response. but if you dm it twice, it will give you a different repsonse

``dm 1: hey, i am a bot

dm 2: you already dmd me!``

[fluid glen]

@bot.command()
async def deliverystatus(ctx,*,ordernum):
for row in cur.execute("SELECT deliverystat FROM orders where id = (?)", (ordernum.replace("#", ""))):
await ctx.send(str(row[0]))

for what ever god forsaken reason, this thing won't fucking work

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

I just get that

I figured out the issue:

@bot.command()
async def deliverystatus(ctx,*,ordernum):
for row in cur.execute("SELECT deliverystat FROM orders where id = (?)", (str(ordernum),)):
await ctx.send(str(row[0]))

removing string.replace() fixed the issue, this would be good by itself but this is problematic because the way the order id is and has been formatted is with # in front of it.

[fluid glen]

Do y'all know what I could change about this, so I don't get the multiple bindings error?

[proven arrow]

It is because you didnt pass in the correct format.

[fluid glen]

ayy I ended up solving the issue :D

[proven arrow]

You just passed a string. You need to pass something iterable

[brave bridge]

open a connection and a transaction at the start of a command, and close (commit/rollback as needed) it at the end.

[livid moss]

how to handle psql: FATAL: sorry, too many clients already?

[fading patrol]

If this is just a dev environment I would restart the database. Obviously if it keeps happening you need to figure out why

[livid moss]

whn im doing insert delete view and update and when i check the select count(*) from pg_stat_activity; it keeps increasing

[bitter fiber]

I need to learn DBs plz help

Hello,

I have recently exceeded what excel can handle. I have to use complex relationships and formulas to interacts between thousands of data points and try to make queries/reports. I need to move to a database but I know nothing so my questions are:

1. Do softwares like Access, Oracle and MySQL come with built in client/user side GUI or is that something I have to build from scratch?
2. What software would give me the best scalability bringing it from a single project DB to be a corporate/ multiproject DB?
3. Should I opt for open-source or should I spring for a product if I am looking for scalability?

Thanks so much in advance!

[paper flower]

What level scalability are we talking about? 🤔
Even sqlite can handle millions of rows just fine

If you're looking for something more advanced i'd suggest postgresql
Both have gui's, for example pgadmin for postgres and sqlitebrowser for sqlite

[spice finch]

conn = sqlite3.connect("main.db")
c = conn.cursor()
try:
    with connn:
        c.execute("""CREATE TABLE warn_data (
        user integer
        guild integer,
        warn string
        )""")
        print("Created table")
except:
    print("Prefix database already exists terminated creation job!")

@client.event
async def on_guild_join(guild):
    conn = sqlite3.connect("main.db")
    c = conn.cursor()
    with conn:
        c.execute("""INSERT INTO warn_data VALUES (:user, :warn, :guild)""", {
            "user":  guild.user.id,
            "warn": None,
            "guild": guild.id
        })

@client.command()
@commands.has_permissions(administrator=True)
async def warn(ctx, user:discord.Member, *, reason: str):
    try:
        reason=str(reason)
        conn = sqlite3.connect("main.db")
        c = conn.cursor()
        with conn:
            c.execute(
                """UPDATE warn_data SET user = :user, reason = :reason WHERE guild_id = :id""",
                {
                    "user": user,
                    "reason": reason,
                    "guild_id": ctx.guild.id

                })
            await ctx.send(f"Successfully warned user for {reason}")```

Error: File "main.py", line 208, in warn
c.execute(
sqlite3.OperationalError: no such column: user

[supple birch]

anyone know how to make a decent data structure :b

[paper flower]

What do you mean?

[supple birch]

So I wanna make a simple item management system, but im not sure what the propper structures would be

Should I store thr items in location or id

[paper flower]

First start by thinking about what entities you would have, e.g. Inventory, Item, etc, then establish relationships between them

You could lay out your entities using python classes and then translate them to orm models or sql

class Item:
    id: int
    name str:
    
    inventory_id: int
    inventory: Inventory

class Inventory:
    id: int
    location: str # Could be a geoposition for example

[spice finch]

conn = sqlite3.connect("main.db")
c = conn.cursor()
try:
    with connn:
        c.execute("""CREATE TABLE warn_data (
        user integer
        guild integer,
        warn string
        )""")
        print("Created table")
except:
    print("Prefix database already exists terminated creation job!")

@client.event
async def on_guild_join(guild):
    conn = sqlite3.connect("main.db")
    c = conn.cursor()
    with conn:
        c.execute("""INSERT INTO warn_data VALUES (:user, :warn, :guild)""", {
            "user":  guild.user.id,
            "warn": None,
            "guild": guild.id
        })

@client.command()
@commands.has_permissions(administrator=True)
async def warn(ctx, user:discord.Member, *, reason: str):
    try:
        reason=str(reason)
        conn = sqlite3.connect("main.db")
        c = conn.cursor()
        with conn:
            c.execute(
                """UPDATE warn_data SET user = :user, reason = :reason WHERE guild_id = :id""",
                {
                    "user": user,
                    "reason": reason,
                    "guild_id": ctx.guild.id

                })
            await ctx.send(f"Successfully warned user for {reason}")```

Error:   File "main.py", line 208, in warn
    c.execute(
sqlite3.OperationalError: no such column: user

[ashen meadow]

if I have 3 columns as a attribute, and whenever user want to query db with filter from one of this 3 columns, how I can make it smarter?
pseudo code:

def query_db(col1, col2, col3):
  if col1:
    select * from table where coluumn1 in col1
  if col2:
    select * from table where coluumn2 in col2
  if col3
    .....
  if col1 and col2:
    ....
  if col2 and col3:
    ...

anyone? 🙂

[paper flower]

Using an ORM / Query builder would be easiest solution imo

Otherwise i think you can use coalesce function:

select * from table
where
  table.col_1 = coalesce(:value_1, table.col_1)
  and table.col_2 = coalesce(:value_2, table.col_2),
  and table.col_3 = coalesce(:value_3, table.col_3)

Not sure how well it would work with indexes and stuff

[ashen meadow]

how to do it in ORM? because I have it.

[paper flower]

Which one?

Why are you writing raw sql then? 🤨

[ashen meadow]

because i need save the results to dataframe

[paper flower]

You still can use an orm here

[ashen meadow]

sqlalchemy

[paper flower]

It would probably look like this:

def query_db(first_name: Optional[str], last_name: Optional[str]):
    stmt = select(Person)
    if first_name:
        stmt = stmt.filter(Person.first_name == first_name)
    if last_name:
        stmt = stmt.filter(Perason.last_name == last_name)

[ashen meadow]

def query_db(*args):
   base_query = "SELECT * FROM table"
   if not args:
       return base_query
   base_query += " WHERE "
   base_query += " AND ".join([f"${idx} IN col" for idx, col in enumerate(args, 1))
   return base_query

this is better solution i think 😉

[arctic island]

Hello, does anyone know how to delete a value from a column after a certain time ?

[peak peak]

cluster0-shard-00-02.odlim.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host,cluster0-shard-00-00.odlim.mongodb.net:27017: connection closed,cluster0-shard-00-01.odlim.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 61e170d4b9bdab0d8248238f, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.odlim.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.odlim.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-01.odlim.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.odlim.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.odlim.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.odlim.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host')>]>

[peak peak]

do anyone know how do i solve this thing in mongodb

[paper flower]

I'd say that manually constructing your queries is prone to sql injection

Though this one seems to be ok, i'd still advise you to use your ORM instead of writing raw sql

[paper flower]

What do you mean after certain time?

[arctic island]

basically i wanna make licenses that expire after a certain time

like 1 day, 1 month etc

[paper flower]

Best solution would be to include a created_at or valid_until field

If current time is greater than time stored in valid_until then license would be considered expired

[arctic island]

alright, thanks

[peak peak]

@paper flower do you know how to work with pymongo

[paper flower]

No, i didn't work with nosql

[peak peak]

ok sorry to disturb

[paper flower]

It's ok

[main oriole]

I accidentally put .dir in sqlite, and now it shows an indented >...

How do I get out of that

.exit doesn't work

sqlite> dir
   ...> .end
   ...> .exit
   ...>
   ...> .

[fading patrol]

How about Ctrl+C ?

[main oriole]

Nope

[rough hearth]

Those of you who have used graph databases, which have you used?

[trail patio]

One question wich is the best database to start?

[pure sleet]

learn SQL

[trail patio]

thanks

[pure sleet]

you can use a sql based database while learning sql, like SQLite for example

[trail patio]

I was thinking of using sqlite3

is that a good one?

[grim vault]

Can you take a look at your database to check how the table really looks, just a fast look at your code:

conn = sqlite3.connect("main.db")
c = conn.cursor()
try:
    with connn:
--->     ^^^^^ to many NNN
        c.execute("""CREATE TABLE warn_data (
        user integer
--->                ^ missing comma, I don't know how your table looks like now
        guild integer,
        warn string
        )""")
        print("Created table")
except:
    print("Prefix database already exists terminated creation job!")

@client.event
async def on_guild_join(guild):
    conn = sqlite3.connect("main.db")
    c = conn.cursor()
    with conn:
        c.execute("""INSERT INTO warn_data VALUES (:user, :warn, :guild)""", {
--->                                               ^^^^^^^^^^^^^^^^^^^^ wrong order of columns
            "user":  guild.user.id,
            "warn": None,
            "guild": guild.id
        })

@client.command()
@commands.has_permissions(administrator=True)
async def warn(ctx, user:discord.Member, *, reason: str):
    try:
        reason=str(reason)
        conn = sqlite3.connect("main.db")
        c = conn.cursor()
        with conn:
            c.execute(
                """UPDATE warn_data SET user = :user, reason = :reason WHERE guild_id = :id""",
--->                                                  ^^^^^^                 ^^^^^^^^
--->                                                       columns not in table
                {
                    "user": user,
                    "reason": reason,
                    "guild_id": ctx.guild.id
---->                ^^^^^^^^ used binding name is ':id'
                })
            await ctx.send(f"Successfully warned user for {reason}")

[fading patrol]

Yes

[stark sparrow]

i have a db called vc_db but my code gives asyncpg.exceptions.UndefinedTableError: relation "vc_db" does not exist error. Can anyone help?

await self.client.vc_db.execute('INSERT INTO vc_db (guild_id, user_id, vc_time) VALUES ($1, $2, $3)', member.guild.id, id, 0)

[hallow holly]

Hello peeps

What would be the best way to store files in database

Im working on file management system as my college project
I wanted your insights on how to store the files

[fleet ibex]

your vc_db should point to vc_db.db right?

[fading patrol]

If the files are large it's generally better to store only the path in the DB and the actual files somewhere else, but especially if small you can use a blob data type

[hallow holly]

I actually tried blob
But it was causing error because the file data had ' (single quotes) in it, and it was throwing syntax error
Can someone help me with this

[shell sigil]

how can i set the debugger to open the Gerenciador.py? i run it and it just cant find the file

tried putting 'src/Gerenciador.py' but didnt work

[shell sigil]

ok

but are u implying that for big projects vscode is better? have heard many types pycharm is the preferable

just for curiosity

so pycharm community is not that good?

[shell sigil]

the software i mean

the maximum i want to go with python is pygame and panda3d, guess it wont need that much of complexity

anyway, much thanks

[torn sphinx]

this is my first time ever looking into databases. How would i create a database for messages sent by a user in dms?

[main oriole]

What does ...> mean?

I got it after doing this:

sqlite> attach database hopeful.db
   ...> attach database hopeful.db as hopeful
   ...> as hopeful
   ...>

Like how do I get out of the ...>? Ctrl+c doesn't work, .exit doesn't work

[harsh pulsar]

it's expecting you to end the command with ;

...> tells you that it is continuing from the previous line, in the same command

[harsh pulsar]

the pinned messages have some good intro material for databases

[fading patrol]

There are many ways, but look in to sqlite3 for starters

[brave bridge]

kind of like in the Python REPL you have ... to indicate the the statement isn't over yet

>>> def f():
...     x = 1
...     y = 2
... 
>>> 
``` As salt rock lamp said. you need to add `;` at the end of each statement in the REPL

[main oriole]

Ok

Oh, also

How do I actually create a database?

Like I have my .db file

Called hopeful.db

But how do I put shit into there

[brave bridge]

You mean, manually? Or programmatically, using Python?

[main oriole]

Either works.

Eventually I'll need all 3 through a Discord Bot, but just getting the info in to start with would be good probabyl

I know I can use update I think it is to like change values

(I'm using sqlite btw, idk if it changes across DBs, so postgre is different to mysql and then they're both different to sqlite)

[brave bridge]

If you want a tutorial on SQL: https://sqlbolt.com/
If you want a tutorial on how to connect SQLite and Python together: https://stackabuse.com/a-sqlite-tutorial-with-python/
If you want a reference about how to use the Python sqlite3 module: https://docs.python.org/3/library/sqlite3.html

There is a "standard SQL", and different relational databases implement it to some degree. But each has its own quirks (because the standard is extremely loose) and its own extensions.

i.e. each RDBMS has its own dialect of SQL. So while the knowledge of SQL is transferrable, the queries you write for PostgreSQL might now work for SQLite, and vice versa..

[brave bridge]

There are also tools https://sqlitebrowser.org/ for viewing the database and its structure. Very helpful

[main oriole]

Ahh, ok, I get what you mean.

[main oriole]

Can't use that, I'm on mobile :/

[brave bridge]

oof

[main oriole]

Yeah

Sqlite still works on mobile tho so I'm fine

[brave bridge]

Yeah, mobile apps frequently use it to store data

For example, an e-scooter app I'm using uses SQLite to cache chunks of the city map

This is also a useful tool, although for a completely different purpose: https://www.db-fiddle.com/

[main oriole]

Ah, ok

Oh also, for sqlite, what do files have to end in?

Is it just .db?

Like python files end in .py, JavaScript in .js, text in .txt, etc

[harsh pulsar]

usually people use .db or .sqlite. but remember that file extensions are just part of the name, and their usage is purely by convention

you don't have to give it an extension at all if you don't want to, although that makes it harder to remember / figure out what the file is

[main oriole]

So I could call it just leaderboard and it would work?

[leaden cloud]

Hey, I don't get why my mysql doesn't work on localhost. I am using xampp. Screenshot following

[brave bridge]

yeah, but if someone unfamiliar with your project sees a file called leaderboard, they might get confused

so I'd keep leaderboard.sqlite

[main oriole]

Ok

customers_sql = """
... CREATE TABLE customers (
...     id integer PRIMARY KEY,
...     first_name text NOT NULL,
...     last_name text NOT NULL)"""
>>> cur.execute(customers_sql)

What does NOT NULL and PRIMARY KEY mean?

I'm looking at https://stackabuse.com/a-sqlite-tutorial-with-python/

And that's on there

But idk what they mean

[fading patrol]

Both of those mean that the fields are required. They mean that you cannot add a row to this table without including all three of those values.

PRIMARY KEY further means that the value id must be unique (no row can had the same id as any other row)

Every table has to have a PRIMARY KEY but the NOT NULL constraint is always optional. It's a question of design... In the case it probably makes sense not to allow a customer record missing a first or last name

[main oriole]

Ahh, ok

So can multiple fields have primary key?

[fleet ibex]

you can also set default entries incase they are missing, like phones.. home/cell 867-5309/000-0000.. with,
homephone text default 000-0000
cellphome text default 000-0000

[main oriole]

Ok

Also

Can you have multiple tables in one db?

Like I'm a bit confused on that part

Like is it:

DB
- Table
-- Values
-- Values
- Table
-- Values
-- Values

[thick totem]

Yes, take a look at some entity relationship diagrams, they might help you get to grips with relational databases and primary keys

Think of a primary key as an attribute that is always unique and can be used to identify any record in the table… a lot of the time it’s an integer but can really be anything

[silent umbra]

Hello, I have an app which includes a login system ( auth system).
Typical stuff such as email and password.

However, I am using mongodb, specifically pymongo(module) for this.

Is it safe to include the authentication url in the pymongo connection on my .py?

I ask that because I plan to utilize pyinstaller, and release this on Github for my professor as an .exe
The source code will not contain my authentication url, because it has my password in it.

• Yes my professor probably won't reverse engineer my python project just to get my password lol

[torn sphinx]

how would i create a database for everyone who has dmd the bot, not just talked in regular channels?

[olive reef]

Hey there, I got a database where I store stats about some Moderators, I need to know how much tickets they've claimed, closed and opened, in all their lifetime, and also how many of them have been closed, opened or claimed in the same day, week and month.

So currently I have this table:

CREATE TABLE mods (
        discord_id int,
        claimed int DEFAULT 0,
        closed int DEFAULT 0,
        opened int DEFAULT 0,
        today_claimed int DEFAULT 0,
        today_closed int DEFAULT 0,
        today_opened int DEFAULT 0,
        week_claimed int DEFAULT 0,
        week_closed int DEFAULT 0,
        week_opened int DEFAULT 0,
        month_claimed int DEFAULT 0,
        month_closed int DEFAULT 0,
        month_opened int DEFAULT 0
        );

I think there's better ways of storing this, since I have also to keep log of past days/weeks/months. What would be a better option?

[rotund oxide]

aight so im using aiosqlite and its not allowing me to open a database file
sqlite3.OperationalError: unable to open database file

async def check_for_user(user: discord.Member):
        async with aiosqlite.connect("../db/monkeys.sqlite") as dbase:
            cursor = await dbase.execute(f"SELECT UserID FROM monkeys WHERE UserID = '{user.id}'")
            result = await cursor.fetchone()
``` here is the code

[peak peak]

mgclient = MongoClient("mongodb+srv://UtilityWharf:[e-mail]/HardikIsPro?retryWrites=true&w=majority")
db = mgclient["HardikIsPro"]
collection_tag = db["TagsDiscordBotPropix"]

pls help

its not working even the error is not helpful

[olive reef]

You sure the path to the database is correct?

[paper bluff]

it's a simple troubleshooting question, no need to react that way. additionally your message shows disrespect for those that have intellectual disabilities

[stark forum]

He does tho trust me, you are not in a vc with him rn

[rotund oxide]

i am smothbrain

[stark sparrow]

yes

[sterile pelican]

2 questions regarding asyncpg

I don't want to type, for example, whole 6 variables

await conn.execute('''INSERT INTO some_table VALUES ($1, $2, $3, $4, $5, $6)''', (1, 2, 3, 4, 5, 6)) #these just for an example```
Is there any faster method to do this besides using
```py
'$'+', $'.join(map(str, range(1, 7)))```

2.
Are there any ways to use dict factory like in psycopg2 or I have to define my own function for it?

[burnt turret]

2. asyncpg.Record (returned by fetchx functions) allows you to access values based on keys as well https://magicstack.github.io/asyncpg/current/api/index.html?#record-objects. for example:

row = await conn.fetchrow("SELECT name, age FROM users")
row["name"] # valid

as for 1. i don't think there's any other neat way to do it (but someone else here might have some ideas!)

[sterile pelican]

Thx a lot

[fleet ibex]

without seeing your full block it could be anything, as your posted block is not indented.. did you create your db connections in a function? and, did you return it?

[leaden cloud]

I am getting the error: Python 'timestamp' cannot be converted to a MySQL type

pandas.to_datetime(values[6])

I am using it in an insert statement

[lyric lark]

record = await self.client.db.fetchrow('SELECT * FROM vc WHERE user_id = $1', member.id)

In this code, I'm checking if the user_id is member.id. Is there any AND statement in PostgresSQL to check if the guild_id is member.guild.id in the same line?

[fleet ibex]

subqueries

[lyric lark]

what is that?

Ok got it

thanks

[torn sphinx]

Good morning to everyone, I'm working on a csv file and I would like to write another csv. In particular my csv is composed of persons and an associated measurements list ( person1|[m1,m2,m3....] person2|[m1,m2,m3...] ecc). I want obtain something like this: person1|m1|m2, person1|m2|m3, person1|m3|m4, person2|m1,m2|.... So I would loosen the list and write a row for each tuple. Any help? I hope I was clear

[granite kayak]

Hello! Is it bad if I use a single connection on my MySQL database for the whole instance of the project that I've been working on? I thought that this is a good idea because it provides less loading times in connecting to the database every time a query is to be executed compared to closing the connection every time a query is done. If this is a bad practice, is there any other way to reduce loading time just by connecting or is connecting and closing the only practice that can be done properly? Thanks in advance

[brave bridge]

Do you know what a transaction is?

[granite kayak]

In technical terms, I don't think I do

[brave bridge]

Suppose that you have these two functions: ```py
def add_funds(db, user_id, amount):
db.execute("UPDATE accounts SET funds = funds + :amount WHERE user_id = :user_id", {"user_id": user_id, "amount": amount})

def subtract_funds(db, user_id, amount):
current_funds = db.fetchval("SELECT funds FROM accounts WHERE user_id = :user_id", {"user_id": user_id})
if current_funds < amount:
raise NotEnoughFunds(user_id=user_id, current=current_funds, required=amount)
db.execute("UPDATE accounts SET funds = funds - :amount WHERE user_id = :user_id", {"user_id": user_id, "amount": amount})

and then you combine them into a more complex one that transfers some money from one account to another:
```py
def transfer_funds(db, from_user, to_user, amount):
    add_funds(db, to_user.uuid, amount)
    logger.info("Added $%s to %s", amount, first_user.name)
    subtract_funds(db, from_user.uuid, amount)
    logger.info("Removed $%s from %s", amount, to_user.name)

Do you see anything wrong with transfer_funds?

[granite kayak]

I think I'm supposed to see something wrong but I don't think I'm knowledgable enough to know what it is

[trim lintel]

@brave bridge they are asking about connections, not transactions ?

[brave bridge]

Let's consider different scenarios. What happens if you do ```py
transfer_funds(db, alice, bob, 69)

[brave bridge]

Transactions are important to understand why a single connection is not a good idea 🙂

[trim lintel]

Well but that depends on how their application is written. They have not given any details. A single connection may not be a problem for their case.

Like on a single connection in an sync environment it will just be queued.

[granite kayak]

Yeah, I think the query will be executed before any checks can be done properly and possibly ruin the balance of the database

[brave bridge]

So this is what will happen:

1. We add $69 to Alice's account
2. We try to subtract $69 from Bob's account
3. We get an exception! now Alice has $69 extra on her account, which is not good

Suppose that you want to fix this and do something like this: py def transfer_funds(db, from_user, to_user, amount): subtract_funds(db, from_user.uuid, amount) logger.info("Removed $%s from %s", amount, to_user.name) add_funds(db, to_user.uuid, amount) logger.info("Added $%s to %s", amount, first_user.name) This is still not ideal: what if add_funds fails because of some other new requirement, or because the network connection is a bit flaky? Or what if the logging fails (that is possible)

Moreover, if you call subtract_funds from two threads at the same time (or from two independent apps), this is what could happen:

1. First client checks if there are enough funds (say, 100 required but Bob has 110)
2. Second client checks if there are enough funds (say, 20 required but Bob has 110)
3. First client subtracts 110 from Bob's account
4. Second client subtracts 110 from Bob's account
   Now Bob has -10 dollars on his account...

[granite kayak]

Thank you for the very detailed explanation, I think I get it now

[paper flower]

I think to avoid such situations you have to perform these checks in application and database side(using constraints)
Also you should warp such operations in transaction so if something goes wrong no changed would be written to the database

[unborn pilot]

Hey do you guys know how to get all of the items of a mongodb database and turn it into a list?

[brave bridge]

...I got distracted a bit, sorry
So to solve this issue, relational databases often have "transactions". An action inside a transaction either completes fully, or doesn't complete at all.
MySQL on transactions: https://dev.mysql.com/doc/refman/8.0/en/commit.html
SQLite on transactions: https://www.sqlite.org/lang_transaction.html
PostgreSQL on transactions: https://www.postgresql.org/docs/current/tutorial-transactions.html

So ideally, you'll open a transaction at the beginning of a request (HTTP request/command invocation/event/etc.) and close it (commit/rollback) at the end. There can only be one transaction per connection,
To solve the latency problem, people usually use a connection pool. It holds several (e.g. 10) connections ready to go. When you need a connection, you acquire it from the pool and do something with it, and then you release the connection back to the pool.

How exactly to make a pool and work with it depends on your library. I haven't used MySQL, so I can't really say anything

[torn sphinx]

i try to explain you using pseudocode
FOR EACH ROW IN CSV
name= row[0]
surname=row[1]
list= row[2]
if list.size==1
write name,surname, list[0], null
else if list.size>1
for i=0, i<list.size-1, i++{
write name, surname, list[i], list[i+1]
}
so I want to read from a csv and write on another csv

[fleet ibex]

simon try a help channel instead of dropping your question in multiple catagories

[torn sphinx]

okok

[unborn pilot]

Hey can anyone tell me what kind of list will I get if I do this

import pymongo
client = pymongo.MongoClient("URL")
db = client['db']
collection = db['bd']

document = list(collection.find('{}'))

can anyone tell me

the collection has 3 things

{
  "_id": ObjectId("ID"),
  "question": "question",
  "answer": "answer"
}

So I think the list would be like this

["ID", "question", "answer"]

I am not sure can you help me

[paper flower]

<@&831776746206265384> 🤨

[calm grotto]

@woeful cairn We don't permit paid offers for work here sorry.

!rule 9

[delicate field]

Rules

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

[paper flower]

Also it's not the only channel he posted this in

[hexed estuary]

How are tags usually implemented on a database level?

Does one just have a string-type column for the tags, and searches are done using LIKE?

[proven arrow]

What kind of tags?

And how are they to be used? Support for mutliple entities or for just a single entity.

[hexed estuary]

Think a site of articles. Arbitrary number of tags on each article, and tags are custom, so their count is also not bounded in advance. The database needs to support queries for all articles with all of a specific set of tags

[proven arrow]

Then it would be your standard many to many relationship.

And searching can be done by LIKE. Full text searching might give better results but can increase complexity, and since tags are generally quite short in length LIKE will probably give you as good results.

[hexed estuary]

oh nice, I didn't realise there's a common recipe for this. So I'd have, like...

1. an articles table where each article will have a unique id
2. a tags table where each tag will have a unique id (or, maybe, I'll just use the tag text itself as an id and not have this table)
3. a junction table with (article_id, tag_id) rows (many for each article)
   ?

[proven arrow]

Yes exactly that

[hexed estuary]

Nice, thanks

[trim lintel]

@proven arrow you are actual pilot for Lufthansa ?

[proven arrow]

Was. For German Cargo.

It then became Lufthansa.

[brazen charm]

@hexed estuary Generally in most cases its handled by the search engine service rather than DB directly

[trim lintel]

Oh nice. Just wondered.

[hexed estuary]

Hmm, what do you mean by that?

[brazen charm]

Generally searches are handled by separate services like MeiliSearch, Lnx, TypeSense or Elastic search in alot of cases which offer better relevancy than things like DB text searches.

[proven arrow]

If you are referring to the searching, that would depend on the type of application and what kind of results are acceptable by the requirements. Also some databases support full text searching.

[brazen charm]

true, but for user-facing apis, you generally want a search service with varying levels of tollerance rather than a Full_text search

but yes many DBs do offer simple Full text search

[paper flower]

How do you want to search by your tags? 🤔

[hexed estuary]

oh, I maybe left a misleading impression - by tags, I mean that each article needs to be manually marked with them; it's not automatically derived from the text

[paper flower]

I understand, i'm asking how do you want your search to work?

e.g. simple include/exclude tags filter

[hexed estuary]

Yeah, just include/exclude is what I had in mind. What more advanced way are you thinking about, though?

[paper flower]

Some kind of querying language 😅 e.g. (tag1 & tag2) | tag3

[hexed estuary]

hmm, that's not too important, but I feel like I can implement that later by handling it on the application level

[hexed estuary]

That's about full-text searching, right? I probably don't need that, only search by tags.

[proven arrow]

From a UX point of view how does it work? Do you show all tags and user clicks which they select? Or they type and you show suggested tags?

[paper flower]

Yeah, i don't think it's really related to tags 🤔 though i think search engines might have such features

[hexed estuary]

Probably the latter. It seems also like something I can handle on an application level - I probably won't ever have so many tags that I can't afford to store a list of them all in memory

[proven arrow]

Ok, thats fine however if you do search suggestions how does a user know what tags exist in your system?

How do they know what to search for?

Similar to ecommerce sites, where they show you product attributes (tags) for you to select with the filters rather than the having you type them in.

[hexed estuary]

They may also read an article on a topic they enjoy, check what tags it has, and search for similar ones that way

but yeah, a list of all tags with counts would also be nice to have - should be possible to obtain from the junction table, though

[paper flower]

@hexed estuary I guess query like this should work:

SELECT articles.id 
FROM articles 
  JOIN articles__tags AS articles__tags_1 ON articles.id = articles__tags_1.article_id 
  JOIN tags ON tags.id = articles__tags_1.tag_id 
WHERE tags.slug IN (__[POSTCOMPILE_slug_1]) GROUP BY articles.id 
HAVING count(tags.id) = :count_1

You only really need second join if searching by tag slugs and not ids

from sqlalchemy import Integer, Column, create_engine, String, Table, ForeignKey, select, func
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

Base = declarative_base()
engine = create_engine("sqlite://")
Session = sessionmaker(bind=engine, future=True)

articles_tags = Table(
    "articles__tags",
    Base.metadata,
    Column("article_id", ForeignKey("articles.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)


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

    tags = relationship("Tag", secondary=articles_tags, back_populates="articles")


class Tag(Base):
    __tablename__ = "tags"

    id = Column(Integer, primary_key=True)
    slug = Column(String(255), nullable=False, unique=True)

    articles = relationship("Article", secondary=articles_tags, back_populates="tags")


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

    databases_tag = Tag(slug="databases")
    python_tag = Tag(slug="python")
    article_1 = Article(tags=[])
    article_2 = Article(tags=[databases_tag])
    article_3 = Article(tags=[databases_tag, python_tag])
    with Session.begin() as session:
        session.add_all([databases_tag, python_tag, article_1, article_2, article_3])

    tags_query = ["databases", "python"]
    stmt = (
        select(Article)
        .group_by(Article.id)
        .join(Article.tags)
        .filter(Tag.slug.in_(tags_query))
        .having(func.count(Tag.id) == len(tags_query))
    )
    with Session() as session:
        articles = session.scalars(stmt).all()
        print(articles)


if __name__ == "__main__":
    main()

[hexed estuary]

that's one scary query

thanks, that does look nice

[brave bridge]

hmmm that seems like one complicated query

why do you need the groupby?

[paper flower]

It doesn't really look complicated though

You can't use having withour group by

[brave bridge]

wait, I don't get what the query is doing

[paper flower]

You can use subquery though, i'm not sure about performance difference between the two

[brave bridge]

does it search for articles that have all the required tags?

[paper flower]

Yep

[brave bridge]

ah

well, it will not find an article with databases,python,sqlite

[paper flower]

Ofc, because there's no such tag as sqlite

[brave bridge]

what I meant is: is it useful to search for an article with an exact set of tags? 🤔

[paper flower]

Ah, i get what you mean

It should find that article

i.e. article with tag1,tag2,tag3 by tag1,tag2 query

databases_tag = Tag(slug="databases")
python_tag = Tag(slug="python")
sqlite_tag = Tag(slug="sqlite")
article_1 = Article(tags=[databases_tag])
article_2 = Article(tags=[databases_tag, python_tag])
article_3 = Article(tags=[databases_tag, python_tag, sqlite_tag])
tags_query = ["databases", "python",]

It would find both 2nd and 3rd articles

[brave bridge]

oh right, I see how it works now

it only includes the tags present in the query "in the first step"

and then, if the number of tags is exactly as needed, then we have at least those tags.

but if it's less, we don't have all the necessary tags

jesus, SQL is complicated

I don't think I'll ever understand it

[paper flower]

😅

Try rxjs

You could exclude tags too using alias

[robust vale]

SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(year) AS "count"
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               ELSE 'Not FR' END

Could someone please explain this

[paper flower]

Are you wondering about what case does here?

[robust vale]

yeah

[paper flower]

I author of that query wanted to select amount of players with year = "FR" and other ones

I think you can read about case in postgres docs: https://www.postgresql.org/docs/14/functions-conditional.html

[robust vale]

But then why can't i write GROUP BY year_group

[paper flower]

It's basically like an if in python:

def some_func(year):
    if year == "FR":
        return "FR"
    else:
        return "Not FR"

[paper flower]

You mean just group by?

without conditionals?

[robust vale]

SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(year) AS "count"
  FROM benn.college_football_players
 GROUP BY "year_group"

I meant this

[paper flower]

Because that won't work as expected

e.g.

"FR", "FR", "A", "B"

would return

"FR" 2
"Not FR" 1
"Not FR" 1

[robust vale]

But isn't that okay if I'm also using count(year)

[paper flower]

Ah, wait, you probably can 😅 i though you're grouping by year

[robust vale]

So why would someone put the 'when' clause again after group by

https://mode.com/sql-tutorial/sql-case/

I'm learning from this page

[paper flower]

Go ask them ig 😅

select
    count(person.id),
    case when person.name = 'A'
        then person.name
        else 'Not A'
    end as person_name
from person
group by person_name;

3,Not A
3,A

1,A
2,A
3,A
4,B
5,B
6,C

[robust vale]

How're you getting 'B' and 'C' ?

Shouldn't it be

1,A
2,A
3,A
4,Not A
5,Not A
6,Not A

[robust vale]

lol, I didn't mean to offend you. I just meant that you could go and look up that example in their page to understand my issue better

[paper flower]

I didn't say you offended me, i really don't know why that query has duplicate case stmt

[robust vale]

oh, okk 😅

[grim vault]

They did change to numbers and alias name later on if you keep reading.

The above query is an excellent place to use numbers instead of columns in the GROUP BY clause because repeating the CASE statement in the GROUP BY clause would make the query obnoxiously long. Alternatively, you can use the column's alias in the GROUP BY clause like this:

[robust vale]

My question was that why would they again use the same case statement in the group by clause?

[robust vale]

This was the example.

[dense musk]

Hi so I'm trying to do some formulae calculation on SQL and return the result.
The end goal is to calculate R and return that. Max and min are available in the DB.

Which is. P = max + min /2
R = 2* p

I get how to calculate p but how do I calculate r and have the query return that ?
Here is where I am at.

Select ( max+min/2) as value from math;

I would like to have p calculated seperately cause there are others calculations I would like to do with p.
As in
R = p * 2
R2 = p * 3
So on
Is this the right way to approach this ? Should I store this as a procedure because my program calls this often ?

[fleet ibex]

SQLite3,
assume I have column1 of data that has repeated values, and I am gathering only distinct values..
now I have column2 that I need the sum of everytime a match occurs for column1..
ie,
ab ab
ab ab
bc bc
bc cb
bc ab

what opperand or feature do i need to lookup to do this?

ab 3
bc 1

[leaden cloud]

I am getting the error: Python 'timestamp' cannot be converted to a MySQL type

pandas.to_datetime(values[6])

I am using it in an insert statement

[vale heart]

My data tends to change a lot. My project consists of an android app that sends data to the server. After sending it to the server, I run an algorithm that works on the data and changes it a lot before producing a final output that is sent back to the android app.

Does it make sense to use SQL to store such data if I am always going to be changing it after initial storage? Right now, I just store it in file storage. Should I continue with that or go the DBMS route?

[nova cove]

Well you need some sort of a database if you don’t want to keep it in local storage

the whole point of a database is to store data and check if it changes and then retrieve that data when it is needed

[vale heart]

but is SQL a recommended data storage for my use-case? A lot of data science people use pandas or just simple file storage when they do data projects like mine

[nova cove]

if you will eventually be making this a usable software where users can sign up, etc you prolly do want some sort of database

I guess you could probably get away with what you are doing

but SQL will be useful

could be good practice

[trim lintel]

There is no feature for this in sql. It would be very easy to do if only you had designed the database properly when creating it. Now your stuck due to this poor design you have chosen.

[brave bridge]

rows should be distinct

[grim vault]

Sure there is:

select column1, sum(case when column1 = column2 then 1 else 0 end)
  from tablename
 group by column1```

[brave bridge]

I guess that works

but aren't rows "supposed" to be distinct?

[grim vault]

They don't need to be.

Only if there is a primary key or some unique constraint.

[brave bridge]

hmmm well, for example, how do you delete a row if you have 3 of the same row?

I guess you can use rowid in SQLite, but isn't it a bit of a hack?

[grim vault]

Most DBs have some sort of rowid, if not than you can't.

[brazen charm]

wouldn't recommend using internal ids to do deletes

[brazen charm]

sqlbolt is a good site

[fleet ibex]

thanks, i will test it out when i can.. as to the others, the db isnt built wrong, there was a specific need for the design.. well as in, it couldnt be accomplished with lists, sets, tuples, or dicts that I could figure out. .. some suggested a dataclass but sadly, it failed also to corellate the data in realtime where in the example above ab ab can change to ab cb or ab bc etc on changing conditions and keep track of its pattern.

[unborn pilot]

Hey can anyone please help me

[fading patrol]

#❓｜how-to-get-help

[unborn pilot]

I tried that yesterday but no reply

My question is this when you get all of the items in a collection in mongodb using pymongo What kind of list will you get

import pymongo
client = pymongo.MongoClient('URL')
db = client.db
collection = db.collection
document = list(collection.find({})

The database is like this

{
  "_id": {
    "$oid": "ID"
  },
  "question": "question",
  "answer": "answer"
}```

Can anyone tell me how the list is going to be like

[fleet ibex]

print(document)
print(type(document))
and see what it gives you?
I dont use mongo

[unborn pilot]

I get a ServerSelectionTimeOutError

[fleet ibex]

prolly skip on the type, since you did list() .. guess imissed your question really.. should be a list.. dunno if its gonna give you list of lists.. but {} looks like a dict..

is it a discord bot?

[unborn pilot]

yeah it's for a discord bot

[fleet ibex]

id ask in that section..

[unborn pilot]

ok

[fleet ibex]

sweet it worked.. small changes tho.. and a follow up query to match those results now im back at it :)

[unborn pilot]

isn't that SQL?

[fleet ibex]

yes.. ?

[ebon edge]

hi

i wonder that can i insert a new line on bson by pymongo?

if yes, how?

like this:

{
  "_id": 1,
  "abc": "abc"
}

{
  "_id": 1,
  "abc": "abc",
  "def": "def"
}

[spice finch]

pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.ch1jh.mongodb.net:27017: connection closed,cluster0-shard-00-02.ch1jh.mongodber0-shard-00-01.ch1jh.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.ch1jh.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.ch1jh.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.ch1jh.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host')>]> What could be a possible reason for this?

[peak peak]

Using vpn?

[spice finch]

nope

i am not

client = commands.Bot(command_prefix="s.", intents=intents)
cluster = MongoClient("mongodb+srv://RetroDaCoder:{I PUT MY PASSWORD HERE}@cluster0.ch1jh.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
database = cluster["Cluster0"]
collection = database["SnippyBot"]


@client.event
async def on_ready():
  print("Bot is ready!")
  
@client.command()
async def warn(ctx, user: discord.Member, *, reason=None):
    id = user.id
    if collection.count_documents({"memberid": id}) == 0:
        collection.insert_one({"memberid": id, "warns": 0})
        
    if reason == None:
        await ctx.send("Whats the reason breh?")
    elif user == None:
        await ctx.send("Please mention a user to warn")
        
    warn_count = collection.find_one({"memberid": id})
    
    count = warn_count["warns"]
    
    new_count = count + 1
    
    collection.update_one({"memberid": id},{"$set":{"warns": new_count}})
    
    await ctx.send(f"Warned {user.mention} for {reason}. They now have {new_count} warnings.  ")```

Here's my code if that helps, pretty sure there's something wrong in the set up or something and nothing is wrong with the code

[peak peak]

Firstly remove brackets around the password. Secondly replace myFirstDatabase with the db name

And it will work

[tired stratus]

To modify your document/s like this you need to set a new field

.update/.updateMany(match, {
  "$set": {
    "def": "def"
  }
})

[left kraken]

Is it possible to shifting records date when there is no record on specific date in postgresql? For example I got records for 2022-01-17 and 2022-01-19 and got no records on 18th so shifting the records date field of 19th to 18th and I don't want to make this manual

[spice finch]

ohhh, so that was the problem, thanks man! Was looking for where i was going wrong from like 2 days. Thanks a lot! I appreciate it

[peak peak]

no problem i was stuck in the same problem but bcs of vpn can understand the pain lol

[torn sphinx]

helloww

i want to delete this info from my sql

how can i?

[tired stratus]

is this a row? what are the columns, looks like the second column is unique which can be used to delete

for example

DELETE FROM your_table WHERE id_column_name='that_uuid';

[torn sphinx]

okayy

lemme try

okay thanks it worked

[mystic thicket]

Having an issue where the 29th of February is detected as out of range due to it being a leap year.
error: dateutil.parser._parser.ParserError: day is out of range for month: 29/02/2
Wondering if there is a fix to this issue.
Thanks

[fading patrol]

https://stackoverflow.com/questions/68552526/day-is-out-of-range-for-month-february-29-2016-dateutil-leap-year-issue

[shadow fossil]

hello everyone, does some1 here have done the exercice about the count's organizations in the chapter 15 of Chuck Severance (Michigan University)

I really don't know how to do it, I did all the weeks and all the exercices of Chuck Severance but I don't arrive to do that

(it's about using databases with pytthon)

https://www.coursera.org/learn/python-databases/home/week/2

please mention me if u can help me

[fading patrol]

Maybe you can share a link to your code and describe what the problem is? If it's complex you may want to grab a help channel #❓｜how-to-get-help

[shadow fossil]

okay i will show u my code and what i have in my databases in first

there we go

so i have a texte file mbox3.txt and i have to count how many organizations sent me a message and what is these domain name

and i don't know too how to obtain just the domain name

I tried with" startswith" but I can just have the first letter with" startswith"

I will try with #❓｜how-to-get-help

#help-cookie

oh and i didnt tell what was the problem, the problem is that when i want to give my result, the site tells me : "data is incorect"

[fading aspen]

There are a lot of ways you can tackle this problem. But to answer your specific question of how to get the domain from an email address, this might help

[shadow fossil]

I will try that

@fading aspen

oh its working !

man just thank you you save me

it was the last day to give my answer

just thanks bro

[spice finch]

i tried the things u told me, but it didnt work :(((

still same error

[frosty stone]

how do I get every string from this byte?
'b["hello","world"]'

[spice finch]

client = commands.Bot(command_prefix="s.", intents=intents)
cluster = MongoClient("mongodb+srv://Retro:[e-mail]/myFirstDatabase?retryWrites=true&w=majority")
database = cluster["SnippyBot"]
collection = database["SnippyBot"]```

```ERROR : pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.ch1jh.mongodb.net:27017: connection closed,cluster0-shard-00-02.ch1jh.mongodber0-shard-00-01.ch1jh.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.ch1jh.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.ch1jh.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.ch1jh.mongodb.net:27017: [WinError 10054] An existing connection was forcibly closed by the remote host')>]>```

[hazy crystal]

Hey guys,

I got a situation where I need to get all tables which match a user_id.
Basically I have a few PaymentApi Tables (Paypal, Stripe) etc which have links to the User table.

Is there any way I can select Paypal, Stripe columns which have user_id=2

[fleet ibex]

subquery

[delicate karma]

Can somone teach me how to use sqlite3

Or how to use databases

[tired stratus]

You need an SQL query similar to this

SELECT player_id, max(score)
  FROM game
  group by player_id;

I don't usually use the ORM but it would look like this:

session.query(Game.player_id, func.max(Game.score)).group_by(Game.player_id).all()

edit: I'm not sure if that would work with the ORM, you might need to use on the core/query builder

[fading patrol]

https://www.datacamp.com/community/tutorials/sqlite-in-python

[peak peak]

you still didnt replaced your myFirstDatabase with SnippyBot

-_-

[stark sparrow]

can anyone tell me how to format it to send the wallet_amt and bank_amt only? I'm using postgres btw

wallet_amt = await balance(self, id)
wallet_amt = wallet_amt["wallet_amt"]
bank_amt = await balance(self, id)
bank_amt = bank_amt["bank_amt"]

[stark sparrow]

this is where i call it

async def balance(self, id):
    wallet = await self.client.db.fetchrow("SELECT wallet_amt FROM economy WHERE user_id = $1", id)
    bank = await self.client.db.fetchrow("SELECT bank_amt FROM economy WHERE user_id = $1", id)
    data = {"bank_amt": bank, "wallet_amt": wallet}
    return data

this is the func

[ancient fog]

What's the difference between SQLlite and MySQL?

[upper owl]

Hi guys, quick question.

I have a database with more than 100 tables with some of these table connected to Power BI to give insights on financial and day-to-day operational data so I am tryna add indexes to the tables more especially the tables with datapoints that are changing as opposed to the ones storing static datapoints. Is it good practice to add indexes to each and every column in the datapoints or just unique identifies to increase performance?... Please forgive me I am new to indexes

Thanks

[pure sleet]

well, sqlite produces a compact file while mysql is basically a full fledged dbms

[brave bridge]

fetchrow returns a Record object. You can get a column from it using []-notation

async def balance(self, id):
    wallet = await self.client.db.fetchrow("SELECT wallet_amt FROM economy WHERE user_id = $1", id)
    bank = await self.client.db.fetchrow("SELECT bank_amt FROM economy WHERE user_id = $1", id)
    data = {"bank_amt": bank["bank_amt"], "wallet_amt": wallet["wallet_amt"]}
    return data
``` actually, you just need one query: ```py
async def balance(self, id):
    row = await self.client.db.fetchrow("SELECT wallet_amt, bank_amt FROM economy WHERE user_id = $1", id)
    return {"bank_amt": row["bank_amt"], "wallet_amt": row["wallet_amt"]}

[grim vault]

Error checking for row would be nice.

[brave bridge]

yeah, you need to consider what to do if the row is not found

[torn sphinx]

hi everyone so i made a mysql app in python and i shifted the code to my other computer but its not able to connect with the server
i searched google and realised i have to login to my sql database on the other computer but im worried how will others be able to use the app if they all have to login to the database

could anyone pls help

im new to this stuff so can anybody pls help

[torn sphinx]

pls help me ASAP

[paper flower]

They would be able to do everything they have access to

i.e. read/write

You need to create an API to regulate access to your data

[ancient fog]

What happens if you build a big project on SQLlite?

[pure sleet]

be more specific

i dont know what you want me to answer there,

[ancient fog]

I'm asking if you can build big projects on SQLlite.

Like a big db containing a lot of user data for example

[pure sleet]

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

read here

maybe this to be specific

idk, depends on your use case really

[paper flower]

What do you want to query though?

You want to query Game here too?

Because i don't think it would be able to query concrete one

db.query(a, b) should return many rows, you can iterate through your result with for loop:

for a, b in db.query(A, B):
    ...

db.query returns a Result, result might contain many Rows
https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=result#sqlalchemy.engine.Row

Rows are also iterable, so you can unpack them

Weird 😅
Let me try to replicate that

Could you share your models?

Could you use datetime instead of timestamp btw?

Is game like a game session or a concrete game?

Wait, why does it has a single user_id then?