databases

[paper flower]

https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#the-select-sql-expression-construct

[glacial current]

oh i see. that is not what i think of naked queries. Naked to me is direct query strings. But i see now why the distinction that Core is a hybrid object of direct communication (what i call naked) and full ORM.

[paper flower]

It's not naked/raw sql, but eventually it would have to be translated to raw sql

[sturdy lily]

i have this data in mongodb

const user = {
  // ...other info,
  email: "[e-mail]",
  userInfo: null,
  userDetails: [
    {
      docType: 'some-type',
      dob: 'dob',
      joinDate: 'some date',
    },
    {
      address: 'address',
      street: 'some street',
      pin: 'pin',
    },
  ],
};

so now i want to update this user and want to take the value of usersDetails[0] element and want to put in userInfo. can someone help with this query?

users.updateOne({ email: "[e-mail]" }, { $set: { userInfo: userDetails[0} } )

[queen rose]

I'm not sure what is ORM vs Core, but what I hate the most is this kind of queries:

    query = (
        db.query(User)
        .filter(
            User.user_id == user_id,
        )
        .update(update_model, synchronize_session=False)
    )
    db.commit()

I prefer using conn.execute() with either plain text, or select(Table.col).where(table.col == ...) (using the with statement which handles any exceptions and does a rollback if necessary)

if I recall correctly, there is also a way that by modifying tihe column of an instance of a table class it will modify the DB directly, i.e.: Table(...).col = 32, but I despise that.
I like making it really clear when I'm modifying the DB, which records I'm modifying, and which columns.

[delicate field]

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

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

[fringe sundial]

guys is this sql create correct for this sqlalchemy column?

id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)

CREATE TABLE IF NOT EXISTS users(
    id UUID PRIMARY KEY NOT NULL
)

[graceful widget]

what is this and how do i fix it =.=

this is pgadmin btw

just created a server group discord bot and trying to add a server VIKINGGGGG for my viking discord bot

[obtuse magnet]

check ure using the correct port

usename and pw too ofc

[graceful widget]

wait what

shouldnt i be defining them here?

[obtuse magnet]

try a diff one then, something else may be using that port already

[graceful widget]

ok

ok thanks its working

[graceful widget]

the spiking of database sessions means its connected right?

[obtuse magnet]

ye shud be

[graceful widget]

but where do i do the SQL tho

the SQL page is blank

[obtuse magnet]

right click -> query tool iirc

i wud use dbeaver instead of pgadmin

just better in general

[graceful widget]

there is so many datatypes 😭

i want string or varchar

but there is only cstring (which is for c ig?)

[obtuse magnet]

there shud be varchar

[graceful widget]

character varying is varchar right?

[obtuse magnet]

shud be

i suggest writing DDL instead

https://dbdiagram.io/

this tool can generate sql for u too

[graceful widget]

but why

i can just fill in the table

[obtuse magnet]

repeatability

[graceful widget]

but how do i apply it

copy the SQL next to it?

[obtuse magnet]

u can do that, or write/generate the DDL and run it

[normal bronze]

Also, the habit of planning these out prior to implementing will save a ton of minor mistakes and things down the line.

It also helps keep the idea separate to the implementation, which is a good practice for planning both large and small projects.

[graceful widget]

thanks for the tips!

but how do i do the SQL in code tho
like put values into the table

main.py

import discord, asyncio, random, os, asyncpg, psycopg2
from discord.ext import commands
import discord.ext.commands
from dotenv import load_dotenv

intents = discord.Intents.all()
intents.members = True
prefixxx = ['v.', 'V.']
bot = commands.Bot(command_prefix=prefixxx, case_insensitive=True, activity=discord.Game(name="m.help"), intents=intents)
load_dotenv()

###########################################################################################################
################################################MAIN_CODE##################################################
###########################################################################################################
    
# Connect to your PostgreSQL database
conn = psycopg2.connect(
    database=os.getenv("db_database"),
    user=os.getenv("db_user"),
    password=os.getenv("db_password"),
    host=os.getenv("db_host"),
    port=os.getenv("db_port")
)

@bot.event
async def on_ready():
    print('We have logged in as {0.user}'.format(bot))
    
bot.remove_command('help')

######################################################
#######################COMMANDS#######################
######################################################

async def main():
    async with bot:
        [await bot.load_extension(f"commando.{file[:-3]}") for file in os.listdir("commando/") if file.endswith(".py")]
        await bot.start(os.getenv('TOKEN'))

asyncio.run(main())

how do i do something like this

discord.utils.setup_logging()
@bot.event
async def setup_hook() -> None:
    bot.db: asyncpg.Pool = await asyncpg.create_pool(os.getenv('DATABASE_URL'))
    bot.database_handler = Database(bot)

i used this when im on railway

[floral surge]

does 'validation' refer to a determinate well known concept? is it what we refer to when e.g., entries in a column are string, and we need them to be strings of a specific format otherwise they constitute inadequate column entries?

[languid dew]

this isn’t how you use setup hook

it’s not an event it’s an internal method that dpy is supposed to call for you when you subclass a Bot class

[spiral lichen]

what is a database??

[supple comet]

the "event" decorator justs sets an attribute internally https://github.com/Rapptz/discord.py/blob/master/discord/client.py#L1990

[delicate field]

discord/client.py line 1990

setattr(self, coro.__name__, coro)```

[supple comet]

are you using psycopg2 or asyncpg?

[scarlet arch]

~~```sql
CREATE TABLE IF NOT EXISTS todo (
todo_id INT PRIMARY KEY,
relative_todo_id INT NOT NULL,
todo_time INT NULL,
description INT NULL,
by_user_id INT NOT NULL
);

I am using SQLite. If I have a table like this, and want to give the users some friendliness by giving them IDs that start from 1 then increments by 1 for each todo inserted that belongs to `by_user_id` ; what can I do in the database such that I can index these only-unique-per-user numbers to do stuff like delete or update the todo row/record? I don't even know how to handle the IDs to be honest. Any advise?~~
achieved it with create trigger (i think) thanks!

[faint jewel]

!code

[delicate field]

Formatting code on discord

Here's how to format Python code on Discord:

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

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

For long code samples, you can use our pastebin.

[paper flower]

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

[scarlet arch]

I don't think that would've quite worked for my case, relative_user_id has to be started from 1 for each user, that one can auto increment on each insert on the table itself

[paper flower]

That's completely normal for a primary key though

[scarlet arch]

nono, the primary key is different

[paper flower]

I think firstly I'd make sure you even need a todo number, but yeah, in this case you can either use a trigger or do that in your application logic

[scarlet arch]

todo_id is the primary key, relative_todo_id won't be unique

[paper flower]

I see

[scarlet arch]

if I do it in the application logic, it won't be persistent, I'd have to fiddle around a lot

[paper flower]

Not really

You still can persist it in db and it's not that complicated to do

I usually prefer to put things like that into application to not put any logic into db, though it would be more performant

It wont' really matter though

[scarlet arch]

well yeah, I'm not worried about the db part, I'm worried about keeping the relative ids reference the same primary key persistently

[paper flower]

And why do you think you can't persist them?

When adding a new todo just fetch the current max id for that user and do whatever you want with it

[scarlet arch]

so like, in the db relative ID defaults to 0, then whenever I add a todo record, I increment current max relative id then put it in the new record?

[paper flower]

Yes

[scarlet arch]

it's basically what my triggers are doing, https://github.com/eeriemyxi/todo-bot/blob/b6bc054c3e1f53328e76a56b28c131e19b4b9527/bot/ext/db/scripts/setup.sql#L20-L50
and I think that's much cleaner and faster than doing the same thing with Python, so I'd rather keep it this way

[torn sphinx]

hey guys, im new to python and i need to make a server can someone help?

[torn sphinx]

create database trial;
use trial;
create table Student(
rollno INT,
Name varchar(100) NOT NULL);
insert into Student values(1, "Rohan");
insert into Student values(2, "Jaya");
insert into Student values(3, "Teena");
insert into Student values(4, "Diksha");

create table Fees(
rollno INT,
Fee INT);

insert into Fees values(4, 4500);
insert into Fees values(2, 5500);
insert into Fees values(3, 5000);

select A.rollno, A.Name, B.Fee from Student A, Fees B where A.rollno = B.rollno;

Can someone explain me how join works

I've been struggling real bad

[obtuse magnet]

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

[scarlet arch]

people will have when you have a specific question

maybe see https://stackoverflow.com/help/how-to-ask and welcome to the Python ecosystem.

[fading patrol]

#❓｜how-to-get-help

[graceful widget]

psychopg2

[pure mortar]

oh hey i use this site

[tiny hearth]

Help with subqueries...
I have two log tables that hold similar data but are for different purposes and a comments table with several support tables. My current goal is to display the latest comment (if there is one) with its appropriate log entry. The comments table uses foreign keys for IDs for both log tables to keep them separated.

This code just produces a query for the latest comment for each unique ID for the ccslog table.

  ccslog_last_comment_subquery = ccslog_last_comment_query.subquery()

This code uses the previous query to retrieve all relevant data from the same table. I realize that they could probably be combined to accomplish the same thin in one query, but I separated it so I could hopefully work it out properly

    comments.entry_id, comments.comment_id, comments.comment_date, comments.operator, comments.comment, comments.reference
    ).where(comments.comment_date == ccslog_last_comment_subquery.c.last_comment_date
    )
  ccslog_comments_subquery = ccslog_comments_query.subquery()

My thought, and I am willing to admit it may be wrong, is that I would like to add the previous query to the following query that is used to populate the page.

    ccs_log.entry_id, ccs_log.entry_date, ccs_log.operator, ccs_log.status, ccs_log.status_date, buildings.building_name, rooms.room, systems.system, equipment.equipment, alarms.alarm, contacts.contact_name, ccs_log.work_order
    ).join(buildings, ccs_log.building_id == buildings.building_id
    ).join(rooms, ccs_log.room_id == rooms.room_id
    ).join(systems, ccs_log.system_id == systems.system_id
    ).join(equipment, ccs_log.equipment_id == equipment.equipment_id
    ).join(alarms, ccs_log.alarm_id == alarms.alarm_id
    ).join(contacts, ccs_log.contact_id == contacts.contact_id
    ).order_by(desc(ccs_log.entry_date))```

I have been working with a copy of the last query to work out the proper way to merge the two queries, but nothing I have come up with works.

To be completely transparent, I had to step away from the code for a bit due to other priorites so I don't remember what I tried and what I haven't.

[tiny hearth]

nevermind, happy accident, I just figured it out...apparently I just needed to talk it through. 😄

my only problem now is that only entries with comments show up. I don't want to assume that every entry has a comment. And I want them to show up together

For those curious, here is my working query...

    ccs_log.entry_id, ccs_log.entry_date, ccs_log.operator, ccs_log.status, ccs_log.status_date, buildings.building_name, rooms.room, systems.system, equipment.equipment, alarms.alarm, contacts.contact_name, ccs_log.work_order, ccslog_comments_subquery.c.entry_id, ccslog_comments_subquery.c.comment_id, ccslog_comments_subquery.c.comment_date, ccslog_comments_subquery.c.operator, ccslog_comments_subquery.c.comment, ccslog_comments_subquery.c.reference
    ).join(buildings, ccs_log.building_id == buildings.building_id
    ).join(rooms, ccs_log.room_id == rooms.room_id
    ).join(systems, ccs_log.system_id == systems.system_id
    ).join(equipment, ccs_log.equipment_id == equipment.equipment_id
    ).join(alarms, ccs_log.alarm_id == alarms.alarm_id
    ).join(contacts, ccs_log.contact_id == contacts.contact_id
    ).join(ccslog_comments_subquery, ccs_log.entry_id == ccslog_comments_subquery.c.entry_id
    ).order_by(desc(ccs_log.entry_date))```

[coral wasp]

Glad you figured that out... yah, someone tell me why we use ORMs again?

[pure mortar]

software devs are allergic to SQL?

jk

in all seriousness, they do do a lot for us. but idk about in this case...

[paper flower]

Maybe select models directly? 🤔

[tiny hearth]

To be fair, I am no expert in sql or flask webapp, but I get by 😄 This is my first full blown application...oh, btw, I am a Linux Admin by trade but I haven't slept at a holiday inn recently 😄

[paper flower]

Also I would update flask-sqlalchemy: https://flask-sqlalchemy.palletsprojects.com/en/3.1.x/queries/#select

You should be able to select all fields on a certain model

e.g. select(User)

[tiny hearth]

I believe my problem is a little more complicated then that, but I will let you be the judge. the ccs_log table has over 100 records in it and the original query will retrieve them all and send them to the template as intended. once I "solved" my problem, another popped up in that only the 14 or so records that have comments attached to them are now being sent to the template. The intended action is that all ~100 records are sent to the template and the ~14 or so that have associated comments show the last comment, instead of just the ~14 or so.

[paper flower]

Could you explain your db schema to me and tell what you want to do? 🤔

[tiny hearth]

I have two log tables that hold similar data but are for different purposes and a comments table with several support tables. My current goal is to display the latest comment (if there is one) with its appropriate log entry eventtually for both log tables. The comments table uses foreign keys for IDs for both log tables to keep them separated.

[paper flower]

You seem to have a lot more tables than that

[tiny hearth]

yes, I have the two log tables: ccs_log and work_board, a comments table, and several smaller support tables mostly for lookups within the ccs_log and work_board tables

the comments table uses foreign keys that point to the log entry IDs in both tables to associate the comment with the entry. Ultimately, I want to list all valid log entries with their latest comment.

Currently, I am only recieving log entries that have comments associated with them...not all log entries

[paper flower]

Can you share your models or db schema?

It's still somewhat hard to undertand how to query that data

[tiny hearth]

  __tablename__ = 'ccs_log'
  entry_id = db.Column(db.Integer, primary_key=True)
  entry_date = db.Column(db.DateTime)
  operator = db.Column(db.String(30))
  building_id = db.Column(db.Integer, db.ForeignKey('buildings.building_id'))
  room_id = db.Column(db.Integer, db.ForeignKey('rooms.room_id'))
  system_id = db.Column(db.Integer, db.ForeignKey('systems.system_id'))
  equipment_id = db.Column(db.Integer, db.ForeignKey('equipment.equipment_id'))
  contact_id = db.Column(db.Integer, db.ForeignKey('contacts.contact_id'))
  status_date = db.Column(db.DateTime)
  status = db.Column(db.Integer) 
  alarm_id = db.Column(db.Integer, db.ForeignKey('alarms.alarm_id'))
  work_order = db.Column(db.String(10))

class work_board(db.Model):
  __tablename__ = 'work_board'
  job_id = db.Column(db.Integer, primary_key=True)
  job_date = db.Column(db.DateTime) 
  operator = db.Column(db.String(30))
  building_id = db.Column(db.Integer, db.ForeignKey('buildings.building_id'))
  room_id = db.Column(db.Integer, db.ForeignKey('rooms.room_id'))
  system_id = db.Column(db.Integer, db.ForeignKey('systems.system_id'))
  equipment_id = db.Column(db.Integer, db.ForeignKey('equipment.equipment_id'))
  contact_id = db.Column(db.Integer, db.ForeignKey('contacts.contact_id'))
  archive_date = db.Column(db.DateTime)
  archived = db.Column(db.Boolean)
  issue = db.Column(db.Text)
  work_order = db.Column(db.String(10))

class comments(db.Model):
  __tablename__ = 'comments'
  comment_id = db.Column(db.Integer, primary_key=True)
  job_id = db.Column(db.Integer, db.ForeignKey('work_board.job_id'))
  entry_id = db.Column(db.Integer, db.ForeignKey('ccs_log.entry_id'))
  comment_date = db.Column(db.DateTime)
  operator = db.Column(db.String(30))
  comment = db.Column(db.Text)
  reference = db.Column(db.Text)

there are the two log tables and the comments table

[paper flower]

What db are you using?

[tiny hearth]

flask and postgresql

[paper flower]

@tiny hearth Do you need your work_board in that template?

[tiny hearth]

no there is a separate work_board template that will do the same thing. right now I am just concentrating on getting ALL of the data and associated comments to show up in the ccs_log template.

[paper flower]

So you just need to fetch latest comment for each log?

[tiny hearth]

yes?

This is the query code that is used as a subquery for the main ccs_log query. that I have pulling last comments with their associated log entry_id.

    comments.entry_id, comments.comment_id, comments.comment_date, comments.operator, comments.comment, comments.reference
    ).where(comments.comment_date == ccslog_last_comment_subquery.c.last_comment_date
    )
  ccslog_comments_subquery = ccslog_comments_query.subquery()```

[paper flower]

from __future__ import annotations

import os
from datetime import date, datetime, timedelta

import dotenv
from sqlalchemy import create_engine, ForeignKey, select
from sqlalchemy.orm import sessionmaker, DeclarativeBase, Mapped, mapped_column, \
    relationship, aliased

dotenv.load_dotenv()
engine = create_engine(os.environ["DATABASE_DSN"], echo=True)
session_factory = sessionmaker(bind=engine)


class Base(DeclarativeBase):
    ...


class CCSLog(Base):
    __tablename__ = "ccs_log"

    id: Mapped[int] = mapped_column(primary_key=True)
    date_: Mapped[date]

    comments: Mapped[list[Comment]] = relationship(back_populates="log")


class Comment(Base):
    __tablename__ = "comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    ccs_log_id: Mapped[int] = mapped_column(ForeignKey("ccs_log.id"))
    created_at: Mapped[datetime]
    text: Mapped[str]

    log: Mapped[CCSLog] = relationship(back_populates="comments")


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

    with session_factory.begin() as session:
        session.add_all(
            [
                CCSLog(
                    date_=date.today(),
                    comments=[
                        Comment(
                            created_at=datetime.now() - timedelta(days=i),
                            text=f"{i=} {log_number=}"
                        ) for i in range(10)
                    ]
                )
                for log_number in range(10)
            ]
        )

    cte = (
        select(Comment)
        .distinct(Comment.ccs_log_id)
        .order_by(Comment.ccs_log_id, Comment.created_at.desc())
        .cte()
    )
    comment_cls = aliased(Comment, cte)
    stmt = select(CCSLog, comment_cls).join(cte, comment_cls.ccs_log_id == CCSLog.id)
    with session_factory() as session:
        for log, comment in session.execute(stmt):
            print(f"{log.id=}", comment.id, comment.text)
main()

You can use CTE to select latest record, this is a pretty common problem

I guess it's a similar/same problem https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group

@tiny hearth

[tiny hearth]

wow, you whipped that up fast...

sorry alsy tackling a postgres admin issue as well 😄

okay, brain is melting, I am not sure I understand what is happening in your code

[paper flower]

Basically we select latest comment per ccs_log_id:

    cte = (
        select(Comment)
        .distinct(Comment.ccs_log_id)
        .order_by(Comment.ccs_log_id, Comment.created_at.desc())
        .cte()
    )

And then join to that

[tiny hearth]

what is cte?

[paper flower]

Other code is just creating tables, seeding data, etc

[paper flower]

Common table expresson, google is your friend here 🙂

[tiny hearth]

right gotcha...sorry...late on friday night working postgres issues from both sides 😄

[paper flower]

(It's already saturday)

[tiny hearth]

I got 30 min yet, but close enough

okay, I believe I understand what the cte code is doing

definitely something to look into. i would think this would be fairly simple problem to solve, but as python dev is more of a hobby/additional duty I obviously am not super experieinced...just doing what I can

[coral wasp]

I don't do ORMs (I'm a SQL guy) but I can't see how that'd translate to the right SQL.

[paper flower]

Because it's almost the same 😃

I only use raw sql for ad hoc queries, and practically never in code

You can use something like a db driver directly + pydantic for example but it doesn't provide any big performance improvements and is just more code overall

[indigo scroll]

db.add(new_alley)
db.commit()
db.refresh(new_alley)
sql = select(Alley).where(Alley.path == new_alley.path)
return db.scalars(sql).one_or_none()
``` Can I somehow get the created object when committing without the need for the select?

[paper flower]

new_alley would already be that object

And you shouldn't need the refresh

[indigo scroll]

Tried that at first but got some error, let me check again

new_alley is of type AlleyCreate which is not the Alley (ORM) object though

And now it works, no clue what was the problem earlier then 🙂 thank again!

[paper flower]

That doesn't seem like a sqlalchemy thing

[indigo scroll]

  "AlleyCreate" is incompatible with "AlleyGet"PylancereportGeneralTypeIssues``` that is the sqiuggly error I have on the return statement if I make the function -> AlleyGet

seems to work anyway

AlleyCreate only has 4 properties, those needed to create a new "Alley". AlleyGet has all the database columns which is what I would want to return

def db_create_alley(db: Session, new_alley: AlleyCreate):
    new_alley.new_sync = True

    if new_alley.brunswick_country:
        new_alley.brunswick_customer = True
        new_alley.viking = False
        new_alley.path_waitlist = f"{new_alley.path}-waitinglist"
        new_alley.monitor_sync_status_english = True
        new_alley.login_token = uuid.uuid4()
        new_alley.dashboard_url = new_alley.path
    else:
        new_alley.brunswick_customer = False
        new_alley.viking = True
        new_alley.sync_password = secrets.token_urlsafe(10)

    db.add(new_alley)
    db.commit()
    return new_alley

@router.post("/", status_code=status.HTTP_201_CREATED)
def create_alley(alley: AlleyCreate, db: Session = Depends(get_db)) -> AlleyGet:
    new_alley = Alley(**alley.model_dump())
    db_alley = db_get_alley_by_path(db, new_alley)
    if db_alley:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail=f"Alley with path {new_alley.path} already exists",
        )
    return db_create_alley(db, new_alley) <-- red squiggle on this one

[paper flower]

new_alley is Alley, not AlleyCreate

[indigo scroll]

Ah nice catch"!

[plush quartz]

i want make a command that the database will store the user’s friend code , and if user signed again, delete the old one and store new one

@client.tree.context_menu(name='Save NNID')
async def save_nnid(interaction: discord.Interaction, message: discord.Message):
  user = message.author.id
  key = {'ussr': user}
  data = {
      "NNID": message.content,
      "user": message.author.id,
  }
  user_file = {f'nnid_id.{secrets.token_hex(6)}': data}
  db.nnid_log.update_one(key, {'$set': user_file}, True)
  await interaction.response.send_message("NNID saved!")
@client.tree.context_menu(name='Find NNID')
async def find_nnid(interaction: discord.Interaction, message: discord.Message):
    user = message.author.id
    key = {'user': user}
    mes = ''”
    for m in db.nnid_log.find(key):
        mes = mes + "\n" + m["NNID"]
        print(m['NNID'])
    await interaction.response.send_message(f"NNID found\n{mes}")```
there’s no error but it isnt working

(mongodb)

[torn sphinx]

what does the last question mean

[fading patrol]

For SB-1, you would display '24'

For Sb-3, 04, etc.

At least that's my guess for what a "month day" is

[torn sphinx]

Ohh

How'd we do that anyways?

[fading patrol]

#❓｜how-to-get-help

[hearty siren]

is there a way i can easily delete all tables from database(this is just to make testing easy)
i am using postgresql and psql

i can write something in sqlalchemy so on everytime fastapi shutsdown it deletes tables but then i am using --reload so any chanegs made to code will delete the table

[wise goblet]

easy peasy way to drop everything.
DROP DATABASE and create database again

postgres is not just physical db

it has multiple logical databases inside

which you can create and drop as you wish

[hearty siren]

Thanks

Y didn't i think that 😭😭

But yeah that should make it alot smoother

[fringe sundial]

Guys which database should I use for learning SQL , I don't prefer sqlite, can anyone recommend from postgres or MySQL or cockroachdb

[simple barn]

Why not SQLite?

[fringe sundial]

My choice

[storm mauve]

sqlite is a pretty good choice since it does not requires any setting up, but I guess that Postgres
it is pretty good and often recommended as the 'default' to pick if you don't know which database might be more suited for a certain task

[paper flower]

+1 for postgres

[fringe sundial]

Kk

Ty all

[boreal ridge]

Of course posgres prefer https://www.postgresqltutorial.com/ and for training https://pgexercises.com/

[gentle zinc]

Could someone explain the group by function to me like im 5

[coral wasp]

Imagine you're having a party and invited everyone in your entire school over. You're trying to plan your activities, and wanted to find out the favorite activity for everyone in each grade. Group By allows you to divide all the students by grade and then favorite activity, so you can figure out how many people in each grade like which activity.

[gentle zinc]

ooh

could you give me a practical example please? like with a table

[coral wasp]

ie: select grade, activity, count(*) num_students from student_favorite_activity_survey group by grade, activity

[gentle zinc]

Interesting

[coral wasp]

If you want to learn more, https://selectstarsql.com/longtail.html dives right into group bys

[gentle zinc]

thanks mate

[coral wasp]

select grade, age, count(*) from students group by grade, age is a simpler example

[gentle zinc]

so the data retrieved by the group by clause is being applied onto the aggregate function?

[coral wasp]

think of it as: the aggregate function is calculated separately for each "group"

select grade, count(*), avg(height) from students group by grade gives you the number of students in each grade (count) and the average height per grade.

[gentle zinc]

so the group function takes the identical records from all the rows and adds them?

OH

[coral wasp]

group just divides into groups. Nothing more. The aggregate function decides what to do with the group.

Another example is: imagine you had a box of marbles. group by just divides the marbles into smaller containers. Then, the aggregate function is applied to the smaller containers.

[gentle zinc]

I think i have a peanut brain i just dont understand the concept of groups ugh its so frustrating its like the only part of sql im having trouble understanding

[gentle zinc]

ah okay

[coral wasp]

SQL is weird at first, because it's unlike other programming languages or how we usually think. We tend to think procedurally, not declaratively.

Let's go back to a simple example: select grade, avg(height) from students group by grade

[gentle zinc]

mhm

so my question here is what is group by doing to the grade column

like what do you mean by group?

the examples in my textbook dont really delve into that

it says it "combines" all the identical records

of that column

[coral wasp]

"group by grade" says: Separate all the students into a separate "group" for each grade.

So, imagine a bunch of marbles on the floor... and I said: "group by color"

That means, put all the red marbles together, and the blue marbles together and so on.

[gentle zinc]

mhm

[coral wasp]

I haven't done anything with the marbles yet... I've just reorganized them

then let's say I wanted to know how many marbles there were... or the average diameter of the marble

select color, count(*), avg(diameter) from marbles group by color

note that: I always repeat the "group" columns in the select clause

[gentle zinc]

Let's say we group multiple columns

how would that work out

[coral wasp]

Give me an example?

[gentle zinc]

So here i've got a table I would like to retrieve the number of tracks which have their album id as 3 and composer as AC/DC

how would i do that?

do i even need a group by function to accomplish that?

[coral wasp]

What do you think? Think it out for a sec, I'll help, I just think you might be able to reason this through now

[gentle zinc]

I'll be back in a few minutes mate gotta take my dinner and btw i really appreciate you guiding me through this 😄

[coral wasp]

Just tag me when you're back, I might miss

[gentle zinc]

sure

[vapid fog]

I want to learning database.

[coral wasp]

https://selectstarsql.com/ is an excellent place to start

[vapid fog]

oh thanks.

[gentle zinc]

I'm back

so

[gentle zinc]

@coral wasp select AlbumId , Composer , count(*) from Track t group by AlbumId , Composer
having AlbumId=3 AND Composer='AC/DC';

returns nothing

:/

i mean it seems to be correct

im confused but i've figured out how grouping works finally

https://www.youtube.com/watch?v=-KANeeMUbMo

@coral wasp got it mate

turns out the table didnt have any records with my condition

this worked out though

i gotta thank ya mate for not spoon feeding it to me and letting me figure it out by myself

so the column specified in the aggregate function doesnt matter?

[coral wasp]

In this particular case, since you're looking for an exact count of a single group, you don't need a group by or having.

select count(*) from trace t where composer = 'AC/DC' and albumId=4 is all you needed.

But, for practice purposes, what you're doing is fine.

[coral wasp]

In the aggregate, it does matter what column you use. Generally, if you're counting rows, it's just count(*). But, avg(height) or avg(weight) obviously will give you diff results.

[gentle zinc]

yeah thought so just wanted to try the group function out

[coral wasp]

count(*) counts the number of rows in the result. But, count(height) tells you how many rows have a height (since some might be null)

[gentle zinc]

ah

[coral wasp]

You can even do: count(distinct color) to get the number of different colors in a table.

[gentle zinc]

👍

[coral wasp]

To get fancy, you can even nest the groups: sql select composer, avg(num_tracks), min(num_tracks), max(num_tracks) from ( select composer, albumid, count(*) as num_tracks from track group by composer, albumid ) t_num group by composer

The inside query counts the number of tracks per album per composer. Then, the outer query tells you how many tracks each composer has (avg, min and max)

[gentle zinc]

ooh that's the last concept of my chapter then I've finished mysql (for my curriculum)

quick question is SQL_lite and mysql the same thing?

using dbeaver for the first time

[brazen charm]

SQLite is a different DB

[gentle zinc]

as i found mysql workbench's light theme to be harrowing

[gentle zinc]

I mean i havent really run into any issues so far

[brazen charm]

they use the same core language, but there a differences in deployments and drives, and also subtle differences in the query syntax

[gentle zinc]

no variance in the syntax

[gentle zinc]

ah

[brazen charm]

yes, for the most part the syntax is identical

[gentle zinc]

then i probably ought to switch to mysql to be on the safer side

[brazen charm]

But things like placeholders are different between DBs (although both MySQL and Sqlite use ?) things like Postgres use $n and they have some differences like i believe MySQL's limit and offset syntax is different

[gentle zinc]

I mean as long as the basic syntax remains the same like select create insert alter i should be fine right

i'm only taking 12th grade mysql

[brazen charm]

For what you are doing, they should be identical really

in terms of syntax

[gentle zinc]

👍

[gentle zinc]

@coral wasp sorry to bother you again

but could you explain the cartesian product of two tables

to me

[coral wasp]

Imagine you had 3 pairs of pants and 3 shirts, how many outfits could you make?

[gentle zinc]

9?

wait

i mean 9 yeah

[wise goblet]

Ergh? it is very procedural to me 😅

• (Plugging SQL query problems with CTE)
• (Using transactions)

A single query is a bit more declarative though

[gentle zinc]

ooh i see that you've got a penguin on your bio what distro do you use

[wise goblet]

Kubuntu 22.04 LTS 😉

• Because it is stable
• easy to install everything
• same as servers i use
• and has nice defaults for everything.
• Adequate start bar at the bottom
• multi window terminal out of the box
• and even ability to put links to desktop 😅

i like it.

[gentle zinc]

solid and cute wallpaper

id give it a 7/10

Just works ™️

[gentle zinc]

wayland or x11?

[wise goblet]

default x11

[gentle zinc]

could i get ur neofetch

if u dont mind

[gentle zinc]

ooh intel graphics

[coral wasp]

So, that’s a Cartesian product; every shirt with every pant.

[coral wasp]

Yah, I also mentally translate sql to procedural equivalents, but sql is often critiqued as having been written backwards. Some sql variants include ‘from’ first (from table select columns), or things like prql https://prql-lang.org

Prql is intriguing to me, I think it’s indeed a better syntax.

[potent spire]

this MySQL

why it is show this when I use WHERE?

0    23    12:54:14    UPDATE bank SET have_t = 'true' WHERE id = 559723820978536449    Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
 To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.    0.000 sec

[wise goblet]

Have you tried reading the error? 😅

i would assume it asks you using Primary Key in Where condition
unless you disable Safe mode in your Graphical Interface u use to connect db

Safe guards against dummies provided by your GUI Client i would assume 🤷‍♂️

made to ensure you will not drop tables by accidents

[potent spire]

Im using
anyway, why in python show this error when I already have id-field

  File "C:\Users\Hadi\PycharmProjects\Balance system\venv\Lib\site-packages\mysql\connector\connection_cext.py", line 633, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Unknown column 'true' in 'field list'

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

Traceback (most recent call last):
  File "C:\Users\Hadi\PycharmProjects\Balance system\venv\Lib\site-packages\discord\ui\view.py", line 427, in _scheduled_task
    await item.callback(interaction)
  File "C:\Users\Hadi\PycharmProjects\Balance system\main.py", line 146, in buy_members
    cursor.execute("UPDATE bank SET have_t = `true` WHERE id = %s", [interaction.user.id])
  File "C:\Users\Hadi\PycharmProjects\Balance system\venv\Lib\site-packages\mysql\connector\cursor_cext.py", line 330, in execute
    result = self._cnx.cmd_query(
             ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Hadi\PycharmProjects\Balance system\venv\Lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 77, in wrapper
    return method(cnx, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Hadi\PycharmProjects\Balance system\venv\Lib\site-packages\mysql\connector\connection_cext.py", line 641, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'true' in 'field list'```

[wise goblet]

i am not sure you are allowed using `true` in SQL.
Perhaps 'true' is allowed i think
Check quotation symbol

[slender atlas]

Why aren't have_t and have_ts of type INTEGER? That would be more memory efficient. In any case, you meant to do 'true' in the query string (or better yet, pass it as a query parameter if you aren't sure how to use SQL strings)

[potent spire]

its VARCHAR

[slender atlas]

I know

And?

[potent spire]

😅

``
''

[slender atlas]

Passing these things as query parameters would also make sure this developer error wouldn't reoccur

[coral wasp]

Yah, so this is a quoting thing that bites Python programmers where single and double quotes are interchangeable

[slender atlas]

They used backticks for quotation marks

[coral wasp]

Oh? Then JavaScript developer

🙂

[slender atlas]

Ye

[wise goblet]

@potent spire `` is especially dangerous quotation mark btw.
in shell language it is equvalient to $(another nested shell command)
Executes any sub commands in those ``. Like echo `echo "your subcommand"`

used in some nasty scripts 😅 to obfuscate some nasty command that does bad things (through hiding stuff in base64 format in it)

[coral wasp]

Backticks are valid in MySQL tho, they behave like double quotes, for escaping column and table names

[potent spire]

I know it was just a weird mistake

[coral wasp]

(Iirc)

[slender atlas]

And... Unfortunately PowerShell treats "" differently than what would be a regular string like with '', and C/C++/C# and potentially other languages use only "" for strings and '' for single characters

[coral wasp]

See identifier quote mark here: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

[floral surge]

book recommendations about python and databases?

[keen minnow]

DIA book and the database internals are pretty good

if that's what you are looking for

[lucid flower]

are there any workarounds to create a unique constraint in a mysql table with more than 16 columns?
Error Code: 1070. Too many key parts specified; max 16 parts allowed

[paper flower]

Why do you need an unique constraint on 16 columns?

[lucid flower]

i request data from an api every couple min, and i don't want to have duplicate records if nothing changed

ADD CONSTRAINT no_changes_constraint UNIQUE (
    characterUuid, username, level, xp, wars, playtime, 
    mobsKilled, chestsFound, logins, detahs, discoveries, 
    alchemism, cooking, jeweling, scribing, tailoring, 
    weaponsmithing, woodworking, mining, woodcutting,
    farming, fishing
);

[paper flower]

Don't they have an id or other unique identifier?

Character uuid in your case 🙂

[lucid flower]

charcteruuid is an identifier for each character of a player, so it wont work. also, i don't want to have duplicate records of afk players since their playtime also goes up

[paper flower]

Why won't it work?

Maybe explain what you want to achieve first

[lucid flower]

I want to store player data from an api every couple min, and store it into a database along with their timestamp

i might just cache the latest data and do the unique constraint there instead

[paper flower]

Do you need historic data or only the latest one?

[lucid flower]

historic

[paper flower]

Just stringify all the values and compute a hash

Make a unique different constraint on it

[lucid flower]

ic, gonna look into that ty

[distant meteor]

First time using firebase,
how can I hook cloud firestore database to auth users?

rules_version = '2';

service cloud.firestore {
  match /databases/{database}/documents {
    // Allow read and write access to the user's own data
    match /users/{userId} {
      allow read, write: if request.auth.uid == userId;
    }
  }
}

made rules json

idk how to make it all work

[forest kernel]

Hello, I have a Database with a table, in this table there is a column name "number". I have a PyQt5 GUI where are buttons.
Create button: Creates a new row, if first row it starts by 0, when its not the first row it looks which number is the last row and number will be +1.

The Create button works perfectly the problem comes when pressing the delete button.

Delete button: When clicked on one row in the GUI it gets selected and the right row_number is getting back with this row_rumber I can delete the row with mycursor.execute(f"delete from table where number='{row_number}'")
and after this I will get all the other rows and make every number -1, but this doesnt work with -1 when deleting.... I will put the code for that under this. I would love if someone knows how to do this. I didn't thought It would that big of a deal but I cant fix this problem.

def delete_row(number):
    mycursor.execute(f"delete from table where nummer='{row_number}'")
    mydb.commit()

    mycursor.execute("select number from table")
    for current_number in mycursor:
        print(f"current_number = {current_number[0]}")
        new_number = current_number[0] - 1
        print(f"new_number = {new_number}")
        mycursor.execute("UPDATE table SET number = %s WHERE number = %s", (new_number, current_number[0]))

    mydb.commit()

[brittle lava]

hey

somebody here work with amazon aws?

and how do you work?

How long does it take to get a job?

[floral surge]

Thanks for the answer, what's the DIA book?

[gentle zinc]

guys isnt the syntax for the modify clause

alter table <tablename>
modify <column_name> <new_definition>;

why isnt this working? :/

[glacial current]

I don't do much with cursors but you are iterating though a sequence of numbers with a cursor select and updating with the same cursor. You need a reader and writer sessions if you want to do that.

[coral wasp]

Your definition isn't valid. "number(50)" is not valid for mysql. See https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html for the valid types.

[gentle zinc]

still aint getting the answer if i replace it with int

[fading patrol]

That's for #career-advice but nobody can really answer that for you without a lot more information about where you are starting from, where you are trying to get to, and how you plan to get there.

[coral wasp]

Show the exact command and response plz

[gentle zinc]

nvm works now

dumb mistake on my part

its just sad how my textbook is riddled with mistakes and my teacher doesnt even give a shit

the alter command is depicted with brackets around the arguments in my book but that's the incorrect syntax

they've given 4 examples 2 of which they use brackets and the other two where they dont

tf

[coral wasp]

Yah, that's pretty normal. Just remember to always go first to the vendor docs, like https://dev.mysql.com/doc/refman/8.0/en/alter-table.html. It'll save you time in the long run.

[gentle zinc]

👍

[keen minnow]

"designing data intensive applications"

[floral surge]

thanks a lot!

and by database internals you meant "Database Internals: A Deep Dive into How Distributed Data Systems Work" right? @keen minnow

[keen minnow]

yeah

but again, these won't be the "let me teach you sql" kind of book

[coral wasp]

https://selectstarsql.com/ for learning SQL from basics

[floral surge]

thanks to you both for the resources!

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) foreign key mismatch - "pomodoros" referencing "timer_entries"
[SQL: INSERT INTO pomodoros (dt_start, dt_end, duration, time_since_last_pom) VALUES (?, ?, ?, ?)]

Getting a foreign key mismatch when trying to insert into an sqlite3 db with sqlalchemy but can't find where since the parameters returned by the error statement is a huge list of all entries I'm trying to commit, any way to get more info on which values it failed to insert?

ended up commiting after adding each individual entry to figure out which one was causing the issue, is this the way to go?

[floral surge]

cerberus, pydantic, voluptuous, which one do you prefer for data validation?

[wise goblet]

• pydantic for the win ✅
• voluptuous is not mantained.
• cerberus is not type hint compatible solution (The horror. A lot of dictionaries for everything)

[floral surge]

i see, thanks for the comment!

[wise goblet]

usually when people compare choices like that, they select between pydantic and marshmallow btw.

don't get yourself complicated, go for pydantic though 😅

[floral surge]

didn't know about marshmallow! thanks for the reference

[kind path]

let's say i am making a music player.
and i want to store user history in a database.
Histroies can be on listening history, logging history, song added etc

(not python specfic)

are there any best practices here, or should i just create a table for each type of history and shove a row of user_id, song_id, time_stamp

[paper flower]

If they can have different data in them - yes, if data would be the same - no

[lavish moon]

#1166341417052950569

[indigo scroll]

I have a question on how to correctly map 3 tables in an existing database using SQL Alchemy so thought I would ask all you amazing people in here.
The 3 tables:

1. Alley - this is a physical location and there can only be one (I have mapped this one with full CRUD functionality)
2. Webuser - this is a user who has access to the system (I have mapped this one with full CRUD functionality)
3. WebuserAlley - this is a table that connects the first two tables:
   id
   alley_id
   webuser_id

A Webuser can have access to multiple Alley`s
If someone calls the endpoint to delete an Alley it should then also remove any WebuserAlley entries for that Alley

How should I setup the WebuserAlley table? Which relationsships do I define and in which models?

[barren marsh]

What is the best file format to use for creating a lookup table to be constantly referenced by python?

i was thinking .txt but i wanted to know if there was a better file type

for context, im trying to write 3n+1 (collatz conjecture) and i have the basic functionality down, but i wanna make the program run faster by referencing a lookup table generated from previous executions of the program to speed up the execution (since it already calculated those values in another execution)

[fading patrol]

If this is just a simple dictionary and not too large, I'd probably use a JSON file. If that's not adequate, maybe SQLite

[barren marsh]

ive never worked with JSON files and i dont even know what SQLite is lmao

[fading patrol]

If you can work with txt you can work with JSON. Just look it up and I'm sure you'll be fine

[barren marsh]

oh is it the same? just f=open(path, type) and then f.write("blablabla")?

[fading patrol]

There's a standard JSON library but it works similar to that

[barren marsh]

what is it called?

[fading patrol]

json

https://www.programiz.com/python-programming/json

[barren marsh]

alright thanks

[edgy yarrow]

hey all can i get help about import psycopg2 i already asked on python help

[forest kernel]

Thank you I will google for that

[shut tiger]

just ask

[edgy yarrow]

Hey thank you its fixed i downgrade the version of python from 3.12 to 3.11

[torn sphinx]

can someone plz tell me simply, if i wanted to scrape real-time numbers that change every few seconds, and pull that into a graph, what would be the steps i need to take?? anyone want to help thank you

[keen minnow]

What's the blocker?

[torn sphinx]

what do you mean blocker

[ivory relic]

anyone here good at mysql queries?

having trouble adding a where condition to an insert on duplicate update query

[keen minnow]

what's stopping you from doing it?

[white stirrup]

has anyone worked with pymongo here? I am new to using MongoDB with Python, and am coming from Mongoose where we used to define the schema in the code itself with all the constraints we needed.

But its different in Pymongo where we dont have a concept of a Schema as such and directly call mongodb functions wrapped in python, my main question is if I want to create something like an Unique Index, will MongoDB create duplicate indexes with the same field if the function is run repeatedly?

use case being, I am running a script and storing the results in MongoDB, and since I cant define the collection schema anywhere, I have to call db.collection.createIndex everytime in pymongo to make sure that the index is present,

my main concern is whether this will keep creating indexes with each run and since indexing is an expensive operation I dont want any duplicates, I know I can get around it by listing all indexes and then checking if an index exists with the contrainsts I need

but I was hoping for a better solution than this, from what I am seeing, it looks like I am going about PyMongo the wrong way, I tried asking around in the unofficial MongoDB discord but coudn't find any help there and the documentaion doesnt mention anything like this anywhere

[shut tiger]

Friends don't let friends run mongo. If you want a schema, you should use a db that has schemas, namely relational databases.

[white stirrup]

yeah agreed, mongo is serving as a data dump for us, we are building pipelines on top of it which will process and store the data in relational form, schema in mongoose was just to give structure to the data you are inserting and making sure its atleast in some semi coherent form, its like js vs ts, you can have all the flexibility of JS but have some control over it using TS

same concept

schema is just at mongoose level to ensure some kind of typing etc

anyways, I dived in the documentation and it seems like index wont be recreated if its already there, it'll instead be a no-op

so that should suffice for my usecase

[paper flower]

@white stirrup I didn't work with mongo, but if you can - maybe give sql a go for the same usecase? 🤔 Since your data is structured it seems like a good usecase for it, also migrations are easy to make/run

[white stirrup]

yeah I would have went with Postgres but the data is not actually structured, it's mostly json which we need to store with fields either being arrays or strings or sometimes both, only thing structured is the id and that's why I wanted indexing on it, so that future inserts and updates can be faster

[paper flower]

But fields are known?

[white stirrup]

yeah, kind of

[paper flower]

Then it's structured

For some edge cases you could use json in postgres

[lucid flower]

can I have two different python processes, one doing read and write, the other just doing reads with the same mysql table?

[paper flower]

Yes

[vocal hare]

Not sure if this is the place to ask but how should I set up a database that interacts with a website and a local app? Me and my group in this cs class are creating a website and local app (made in python) and we are putting the database on my VPS. how should i secure connections to the database? We are using postgres on Fedora linux

[paper flower]

What's a local app? Like a desktop app?

[vocal hare]

Yes

Basically a python exe

[paper flower]

So, you don't expose database to a web or desktop application directly

Usually you create an API, which in most cases uses http and json

And your clients (web and desktop app) use it instead

[vocal hare]

ahh thats a good idea

[indigo scroll]

Post the error?

[peak lion]

I lost my mind

  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 408, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\fastapi\applications.py", line 1115, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\middleware\errors.py", line 184, in __call__
    raise exc
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\middleware\errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\middleware\exceptions.py", line 79, in __call__
    raise exc
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\middleware\exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\fastapi\middleware\asyncexitstack.py", line 20, in __call__
    raise e
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\fastapi\middleware\asyncexitstack.py", line 17, in __call__
    await self.app(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\routing.py", line 276, in handle
    await self.app(scope, receive, send)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\starlette\routing.py", line 66, in app
    response = await func(request)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\fastapi\routing.py", line 274, in app
    raw_response = await run_endpoint_function(
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\fastapi\routing.py", line 191, in run_endpoint_function
    return await dependant.call(**values)
  File "C:\Discord\Maja Projekt\Maja-Beta\main.py", line 80, in guilds
    token, refresh_token, token_expires_at = session
ValueError: too many values to unpack (expected 3)```

[wise goblet]

Too many values to unpack. We can assume session has more than 3 values.
if u would have utilized logging... u would have noticed it easier 😉

or even good old putting breakpoint() before this moment is good too

running code and just checking print(session)

it is called pdb debugger (cli debugger)

[edgy yarrow]

hey all i hope u doing well,
i have an issue with db connect and idk why that happens

i tried psql -U your_username -d onepick -h localhost -p 5432 on cmd and all working well
but on my fastapi app i got that error sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: FATAL: database "/onepick" does not exist

[glacial current]

Usually small p for password upper P for port. So upper -P

[glacial current]

Usually upper P

[edgy yarrow]

the issue is only on fastapi app

i use the cmd just to check if db exist on local host and its exist

[glacial current]

But p is pass the cmd line is probably connecting to the default port which is not 5432?

Use -P 5432 to make the cmd line attach

[edgy yarrow]

i think -p

[glacial current]

Ah sorry my bad

[edgy yarrow]

no no mate its oki

the issue on fastapi app
when i use alembic upgrade head

[glacial current]

The sqlalchemy.url in the alembic.ini. it's set to a quoted string or not?

Needs to be unquoted

[edgy yarrow]

ah sorry mb wait plz

[glacial current]

That needs to be filled out to meet the connection with user pass and driver is going to be a postgrez driver

Sad as it may be I have never used posgres

[edgy yarrow]

its oki thank you mate

[glacial current]

Cheers 🍻

[foggy stag]

I get this when checking my connection to mongo:

``` This is one of my few times using mongo, so im not experienced enough to figure this out.

[torn sphinx]

Can you post the code causing that error

[foggy stag]

Yes

[torn sphinx]

if you're using one of MongoDB's python drivers such as PyMongo you might have to pip install certifi and pass tlsCAFile=certifi.where() in your client's constructor

[foggy stag]

Ahhh okay, let me try this

Ill come back once i got some results

Same error

try:
    client.admin.command('ping')
    print("Pinged your deployment. You successfully connected to MongoDB!")
except Exception as e:
    print(e)
``` I get this error when trying to ping the deployment.  

Although when i print my db, it prints fine.

[torn sphinx]

Can you post your client definition

[foggy stag]

client = MongoClient(uri, tlsCAFile=certifi.where())

[torn sphinx]

Strange, not sure what's caused it nor how to fix it in that case, sorry

[foggy stag]

No problem, thanks for your efforts!

[crimson atlas]

Still need help?

[foggy stag]

No, i resolved my issue now.

[crimson atlas]

Good good.

[deft ridge]

transaction rollback not working for sequence while using peewee , the row doesnt get created but the BigAutoField sequence value increases, is this an orm level problem or db

[wise goblet]

!rule 6 , please delete. 🙈 Unapproved advertisement.

[delicate field]

Rules

6. Do not post unapproved advertising.

[wise goblet]

<@&831776746206265384> can we remove adds?

||for not relational databases recommendations 🫣||

[lusty igloo]

!warn 793191669306359849 we do not allow unapproved advertisements. We are not an ad board.

[delicate field]

:incoming_envelope: :ok_hand: applied warning to @celest pebble.

[glacial current]

I'm confused by the sqlalchemy documentation on lazy loading vs Eagar loading. If I have a one to many table defined how do I form the conn.query(MyTable).all() query so that it does the Eagar loading?

[craggy gust]

So I have a question. Can I get a relation from another table to fill a field automagically with sqlalchemy. As in table 1 has a field a, the table two has a relationship to table 1 and then table 3 has a relation to table 2 and a 'a' field again. Is there a way to populate the table 3 a field with table 1's a field if nothing was given to table 3's a field. ( by field I mean column )

[glacial current]

Sounds similar to my question

[craggy gust]

I guess. I haven't gotten to eager and other forms of loading yet. I just see lazy load errors when I forget to refresh 😄

[glacial current]

Yeh the child table has not populated rows. So i think we are asking the same thing. How do we make a query that populates child relationships? It's spelled out in the relationship loading techniques. But it seems so complex that I am lost.

[potent spire]

I have a json file that haves too many keys in diffrent dicts has the same names

can I change all of them ? (names)

or I need to change one by one?

[floral surge]

i need to analyze some logs on a weekly basis, does it make sense to stop using text files for logging and log everything in some database instead? the most important column would be datetime

[paper flower]

query method is deprecated

[paper flower]

Can you share your models? 🤔

[craggy gust]

Not at the moment, they are on my work laptop at work. The basic gist of it was

class TableOne(DBBase):
    my_field:str = Column(String) 
    table_two: Mapped["TableTwo"] = relationship("TableTwo", backpopulates="table_two.my_relation", uselist=false)
    table_three: Mapped["TableThree"] = relationship("TableThree", backpopulates="table_three.my_relation", uselist=false) 

class TableTwo(DBBase):
    table_one_id:UUID = Column(ForeingKey("table_one.id")
    my_relation:Mapped["TableOne"] = relationship("TableOne", backpopulates"table_one.table_two")

class TableThree(DBBase): 
    table_two_id:Mapped[UUID] = Column(ForeingKey("table_two.id"))
    table_two:Mapped["TableTwo"] = relationship("TableTwo", backpopulates="table_two.table_three")
    
    t2_my_relation:Mapped["TableOne"] = relationship() 

Basically that t2_my_relation is what I want to "automagically" fill when adding for example

t1 = TableOne(my_field="cool value") 
t2 = TableTwo(my_relation=t1)
# t3.t2_my_relation gets it's value from t2.my_relation.my_field
t3 = TableThree(table_two=t2)

they all are as far as I recall one to one relation.

[paper flower]

How is table 3 and table 1 related?

[craggy gust]

wait now

[paper flower]

Through table 2?

[craggy gust]

yes

it's missing that relationship there

but yes

[paper flower]

I believe you want this

https://docs.sqlalchemy.org/en/20/orm/extensions/associationproxy.html

[craggy gust]

oh cool I'll check it out.

thanks

btw, what did you mean when you said that query method is deprecated?

[paper flower]

Session.query is literally deprecated

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

Deprecated/Legacy

Use unified select instead + various session methods

execute, scalars, scalar

[craggy gust]

ok neat. I guess I'll have to rewrite that logic then I guess. I thought it was the other way around that it was the select statements that were obsolete now. Welp. I just understood it backwards.

[paper flower]

They're pretty similar

[craggy gust]

Yeah looks like it

[paper flower]

Also sqlalchemy core uses select too

[floral surge]

To properly insert a data entry in my database, I have to reach one of the endpoints of the following diagram. A box represents either a flag or a data structure required to fully determine a single entry.

1. what is the name of this type of diagram
2. how do I implement this

[fading patrol]

Sure but don't reinvent the wheel, there are many tools already, here are a few open source ones: https://search.library.berkeley.edu/discovery/fulldisplay?docid=alma991086125843406532

[paper flower]

Sure what?

[paper flower]

Honestly this is super confusing to look at, maybe you need some "protocol" to describe your data?

e.g. python classes/pydantic models or something similar to openapi

But I'm sure there are other solutions

[floral surge]

it's confusing but it's the only way to fully determine a data entry :/ without knowing the actual Python implementation. I'll look into pydantic and openapi, thanks!

[floral surge]

nice! thanks for the reference

[brave bridge]

Question about PostgreSQL: https://stackoverflow.com/a/66458412
This answer says that losing up to 32 values of the sequence is no big deal. How so?

[brave bridge]

Right now I'm trying to understand how a sequence for an autoincrementing ID could get out of sync with the actual IDs...
The next sequence number for a table is 1068, but the maximum ID of a record in the table is 1095

So when I insert a new record, I get a unique constraint violation because I'm trying to create a new record with ID 1069

[glacial current]

I see. I think i got that from the orm examples section which do query . https://docs.sqlalchemy.org/en/20/orm/examples.html. thanks for pointing that out i will probably will start playing with select soon

[frosty sinew]

in this query, i'm trying to change duplicate values. but there are no duplicates at the row level.

for example:

one customer id could have three records like such

customer id: 10000, ship_to_id: 20001, flag: N
customer id: 10000, ship_to_id: 20002, flag: N```
i need to change the second to flags to `y` and keep the first one `n` 

using sql

[paper flower]

Sequence would be 32 greater than max id, not vice-versa, no?

[floral surge]

how do i decide what parts of data validation i do in sql and what parts i do in python?

e.g., should I use SQL CHECK or do that sort of thing in Python?

[paper flower]

Depends on the check

[grim vault]

Just add the ship_to_id column to your where clause.

[frosty sinew]

Tis a good thought. I might be able to do something in excel

Theres way too many ship tos to manually do it.

I have about 4000 records to sort through

[glacial current]

Ive played a little bit with the current method of updating with SQLA but no object i get back from select is updateable unless i actually use update which might be the wrong direction for SQLA ORM? The query method which is what ActiveRecord and Django type ORM, seems more likeable than the select update method. Going to be more code to do the same thing. Is there much pushback on ditching the query style ORM?

[paper flower]

Yes, query is legacy

What's the problem with select?

[glacial current]

When I get an obj from select it has immutable attributes. So the pattern of doing obj.fiels1 = "foo" then doing conn.commit() at some point to persist is not possible anymore? (Appolojies in my phone doing this)

[paper flower]

How are you getting it?

If you're selecting individual fields then it's not an orm object and sqlalchemy wouldn't manage it for you

You have to select an acutal orm model for that

[glacial current]

Obj =Select(MYTable).where(...).first()

I'm getting the full object though

I'll have anoter try when I get info the office

[paper flower]

I don't think you can use first here...

It's like, not a thing

[wise goblet]

How about actually using Update query, filtered by object pk?
Directly updating necessary field without extra operations

Going to be more simple straightforward operation

[glacial current]

ah what im doing is this

row = session.execute( select(DBTable).where(DBTable.state =='dumping') ).first()

if i try to assign row.state = 'waiting' then i get the error cant set attribute

[glacial current]

Yeh that was my question. if selects and updates must be written to do a task that used to be just an attribute assignment on a seleced obj would this be more like a translator than a traditional ActiveRecord or Django style ORM ?

[wise goblet]

Having hard time following your explanation.

May u still formulate your thought what u wish to achieve at a raw SQL?
Or will write what u wish to achieve in a plain English without technical details?

Then if u specify desired ORM/lib used for interactions, we will be able to translate correctly to end desired code?

I usually follow thought train, translating raw SQL ideas into ORM syntax

[paper flower]

DBTable is an orm model?

[paper flower]

sqlalchemy never worked like an active record orm btw

[wise goblet]

Oh, yeah I got your problem. U think SqlAlchemy is heavy abstraction one like Django ORM in this logic

SqlAlchemy has orm module, but I am unsure if it supports such operations

Need to dig through it

row = session.execute( select(DBTable).where(DBTable.state =='dumping') ).first()

update to new value(state=waiting).where(pk=obj.pk)

stuff like that is presumed to be used default

[paper flower]

It's not really used with orm

[wise goblet]

ergh, really? i thought sqlalchemy core 2.0 with embedded ORM into Core lib is the way to go like that now
where ORM objects are just used to agument regular SQL query building syntax (to parse or input stuff easier)

[paper flower]

You rarely use update with orm objects

[glacial current]

Yes it is a model and has standard definition but selecting those objects are immutable. So the easy method of 'row.state = whatever' is not possible anymore?

That I what I mean by active record django style like orms

[paper flower]

It is possible... Just share your model

I don't understand what you mean by immutable, sqlalchemy models aren't immutable by default

[paper flower]

import uuid

from sqlalchemy import create_engine, select
from sqlalchemy.orm import sessionmaker, DeclarativeBase, Mapped, mapped_column

engine = create_engine("sqlite:///db.sqlite3", echo=True)
session_factory = sessionmaker(bind=engine)

class Base(DeclarativeBase):
    pass


class Book(Base):
    __tablename__ = "book"

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

def main() -> None:
    with engine.begin() as conn:
        # This should only be used for demo purposes :)
        Base.metadata.drop_all(conn)
        Base.metadata.create_all(conn)

    with session_factory.begin() as session:
        session.add(Book(title="A Byte of Python"))

    with session_factory.begin() as session:
        book = session.scalars(select(Book)).one()
        print(book.title)
        book.title = str(uuid.uuid4())

    with session_factory() as session:
        book = session.scalars(select(Book)).one()
        print(book.title)

if __name__ == "__main__":
    main()

I'm creating session multiple times here but usually you use one session per operation (e.g. an http request to your api)

All models added to sqlalchemy session are updated on next flush

commit implicitly calls flush

And it should be updated in db too

[glacial current]

sure this was my simple test

class MyTable(Base):
    __tablename__ = 'db_table'
    id = Column(Integer, primary_key=True)
    state = Column(String(60))
    tablename = Column(String(60))
    dbname = Column(String(60))

and here is the use case that would be applied .

     with Session() as session:
        row = session.query(MyTable).filter(MyTable.state=='dumping').first()
        row.state = 'waiting'
        session.commit()

        row = session.execute( select(MyTable).where(MyTable.state=='dumping') ).first()
        row.state = 'waiting'
       

so is thre any way to make the 2nd use case work like query used to work?

[paper flower]

You don't commit anything to db here

After the second select

[glacial current]

that is because it throws an error saying it cant set attribute at the last line

[paper flower]

Also are you sure there's only one row that has state = "dumping"?

What version of sqlalchemy are you using?

[glacial current]

2,.0

[paper flower]

Use mapped_column instaed of Column

(Btw it should still work) 😅

What error are you getting?

[glacial current]

AttributeError: cant set attribute

[paper flower]

Copy the whole stacktrace

[glacial current]

ok one min im behind a firewall 🙂

class 'sqlalchemy.engine.row.Row'>
Traceback (most recent call last):
  File "./dumper.py", line 1559, in <module>
    main()
  File "/home/oper/.test/lib64/python3.8/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/home/oper/.test/lib64/python3.8/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/home/oper/.test/lib64/python3.8/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/oper/.test/lib64/python3.8/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/oper/.test/lib64/python3.8/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "./aws_dumper.py", line 1386, in test
    row.state = 'waiting'
  File "/home/oper/.test/lib64/python3.8/site-packages/sqlalchemy/engine/row.py", line 81, in __setattr__
    raise AttributeError("can't set attribute")
AttributeError: can't set attribute

[paper flower]

like 1300 👀

Is on first assignment or on the second one?

Ah, wait

You're not querying it correctly, I think

execute returns rows

[glacial current]

yeh

thats what i was thinking too

[paper flower]

row = session.scalars(select(MyTable).where(MyTable.state=='dumping')).one()

[glacial current]

ok thanks will try that

[fallen vault]

How easy is it to migrate from SQLite to a cloud Postgres database?

Found out it only cost 15 bucks a month for a Postgres database through vultr and want to switch to that.

[frosty sinew]

update ship_to
SET delete_flag='Y'
FROM (SELECT ship_to.customer_id as current_customer_id, LAG(ship_to.customer_id) over (ORDER BY ship_to.customer_id) AS prev_customer_id, ship_to.delete_flag, CASE WHEN SHIP_TO.customer_id=LAG(ship_to.customer_id) over (ORDER BY ship_to.customer_id) THEN 1 ELSE 0 END as test from ship_to
    JOIN address on
    ship_to.ship_to_id=address.id
    JOIN customer ON
    ship_to.customer_id=customer.customer_id where (address.name LIKE '%will%call%' or address.name LIKE '%will%advise%' or address.phys_address1 LIKE '%will%call%' or address.phys_address1 like '%will%advise%') and ship_to.customer_id != 10088) 
    as innerQuery    
WHERE current_customer_id = prev_customer_id``` why isn't my where clause being picked up at all

[frosty sinew]

oh the where clause has to compare something from the original table (ship_to) to the inner table, but hte problem is idk what any of the values are from the original table (ship_to). so if i change the where clause to ```sql
where ship_to_ip /from original ship to table/= prev_customer_id

it'll pick it up but it doesn't work how i want it to

[deft ridge]

i am using peewee
with db.atomic():
create_reports() i am running this in parallel
def create_reports():
with db.atomic():
'''do stuff''
this behaves weirdly when one of the report creation breaks , how do we manage something like this

[fading patrol]

It's doable. Many different approaches. https://stackoverflow.com/questions/4581727/how-to-convert-sqlite-sql-dump-file-to-postgresql

[fallen vault]

Thank you.

[native valve]

anyone got a mocking tutorial? I spent the last 8 hours trying to do that in tens if not hundreds of ways, and I failed each and every time. I'm getting beat over here. I'm reaching that point where I'm thinking maybe not testing is just a requirement for things to start happening again in my codebase. This sucks. pls send help

I want to test, and have that good coverage. But my god every time I try to do that in a new language things are brought to a total halt.

maybe it just isnt meant to be that way

imma get back to just writing low quality code that I can fix, f that

[native valve]

nevermind i got it yay imma take a walk

[deft ridge]

anyone ?

[paper flower]

How are you running it in parallel?

[kind ibex]

am currently working on the Python Django Microservices Project and I continue to get this error on MySQL:
"Error during module session creation: Shell.open_session: SSL connection error: error:0A000102:SSL routines::unsupported protocol"
I had written:
database: admin
user: root
port number: 33066 as instructed on the video but I still get the above error

[red lance]

Do I need to run con.commit()
after running cur.execute("VACUUM;" )

[paper flower]

Why are you manually running vacuum?

[red lance]

Does sqlite3 do it automatically for me?

[paper flower]

How large is your db?

[red lance]

Almost 2000 rows, but recently sqlite3 deleted all those rows i couldn't figure out why, the rows basically contain integer and i delete some rows and add new ones, i am trying to fix that

I am sure there is storage being wasted

[paper flower]

Honestly don't use vacuum if you have such a small db

[red lance]

Storage is getting wasted because i delete and add new stuff all the time

[paper flower]

I'm not sure but I think vacuum may be transactional, you should commit

[red lance]

Just wondering, do i have to commit the connection, after using vacuum

[paper flower]

How much storage?

[red lance]

It's like 30k right now, used to go like 50mb but those are wasted storage

It could even go over 500 mb

[paper flower]

Ok, try running vacuum on it

But I don't think you should do it from python, generally

It's quite an expensive operation for sqlite from what I'm reading

[red lance]

It's easy to automate with Python so i use Python's built in one, i have sqlite command line installed aswell

[red lance]

Should i commit the connection after that?

[paper flower]

I just told you

[red lance]

Ok

[paper flower]

I don't work with sqlite so I can't really be sure

[red lance]

I just tested my fragmented database when i used python's sqlite3 and executed vacuum. It worked without commit

24kb turned into 8kb

[paper flower]

16 kb 😅

[deft ridge]

using concurrent futures -> threadpoolprocesser

[subtle bough]

pls help mysql not opening

heplll

it opens for like a millisecond and then closes

plses

[fading patrol]

This is not the place for general tech support but you can try #❓｜how-to-get-help

[stable summit]

does anyone know how I can get the least 5 repteated rows in a column in polars? I got the number of each repeated thing in the row but I need to to find the least repeated

groupby = df.groupby('JobTitle').count()

this is my code but idd polars so idk where to go from here

[fading patrol]

Sort, then limit

[stable summit]

Thanks!

[hearty siren]

what is the difference between sqlalchemy's BIGINT and BigInteger?

[foggy tartan]

need help with a code rn

def checkCard():
cr.execute(f'select cardnum from card where username = {input_username}')
data = cr.fetchone()
print(data)
if data != ('{}',):
existsCard = True
useexistcard = input('Do you wish to use your exisiting card (Yes/No): ')
if useexistcard.title() == 'Yes':
cardnum = data[0]
usecard = True
if useexistcard.title() == 'No':
cardPayment()
else:
print('Incorrect option please enter (Yes/No)')
return

This is for my Car rental high school project

pay = int(input("How would you like to pay?\n1.Cash on delivery\n2.Card Payment\nEnter your choice (1/2): "))
if pay == 1:
print(f'Thank you for using CruiseWave, your car will be arriving {start_date} at estimated time {formatted}')
cr.execute('insert into previousordcar values("%s","%s","%s","%s")'%(input_username,choosecar,start_date,end_date))
print('The delivery driver will call you if required')
time.sleep(5)
os.system('cls')
print('Now you will be returning to the Menu Screen')
time.sleep(2)
elif pay == 2:
checkCard()

This is where i execute my payment for card

in handle_unread_result
raise InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
PS C:\Users\

this error keeps popping whenever I execute card payment

[foggy tartan]

prolly incorrect password or u havent started the server

[glacial current]

I think its saying that the cursor still has values in it and have to be flushed or read before you can use the same cursor for the insert.

[foggy tartan]

mhm so any solution to flush old value or should I use different variable other than data?

[glacial current]

I use context managers instead of cursors but i would think that if a cursor goes out of scope then results on it will be destroyed. how are you making the cursor obj ?

[foggy tartan]

cr = con.cursor()?

[glacial current]

then cr = None would trash that cursor if you no longer need results

[foggy tartan]

mhm

[foggy tartan]

but im using cr throughout my entire 200 lines of code

[glacial current]

does the con obj have an execute method ?

[foggy tartan]

this is my connector

[glacial current]

is this using sqlalchemy or mysql directly ?

[foggy tartan]

python mysql interfacing

[glacial current]

well if you dont want to consume all values from the cursor and instead you want to loop though the cursor, you have to open a 2nd cursor to write the values.

because one will be busy reading values while you are inserting with the other

just open a write cursor same as you did the read cursor

you can also do this
https://www.w3schools.com/python/python_mysql_select.asp

unless you have a million results it wont matter that you fetchall()

[foggy tartan]

hmmm ok thanks

[potent halo]

ive been recently running through learning MySQL; currently i am using MySQL Workbench for the exercises but id be looking to implement this into my python project later.
What im wanting to know, is there a standard when it comes to using backticks (`) when writing in MySQL? I've spent quite a while googling but ive come across people who say its good practice to use them for all table and column identifiers, and some who strongly advise against it.

i understand the reason for using em (to avoid ending up with keywords in your queries), but i can also see the other side that you should really be avoiding the keywords in the first place

[grave eagle]

i'm having issues with some foreign key stuffs

i have 2 tables, one with an UUID, and the child one saving multiple rows with the same UUID, but when i delete the uuid from the parent table, even if i set the ON DELETE CASCADE, it doesnt delete from the child table

i don't know much about sql so i may be doing something wrong

[fading patrol]

#❓｜how-to-get-help

[grave eagle]

#"🌴｜Topical Chat/Help"

[coral wasp]

I rarely use backticks to quote identifiers; because I rarely use identifiers that require quoting. I think it's bad practice to use identifiers that need quoting, therefore backticks / doublequotes.

[coral wasp]

And I've never seen anyone (nor recommend myself) recommend quoting identifiers everywhere. Curious where you read that.

the rare case where I use quotes is when I'm dealing with CSV/XLSX inputs (with often terrible column names) or pivoting.

[open tundra]

Is there a name for table columns where all values are unique?

[storm mauve]

just "unique"?

formally a column with an unique constraint I guess

[open tundra]

I guess that's fine? If a method is named get_unique_columns and returns indices of columns where all values are unique, then does it get the point across?

[quick star]

hey guys need some help with my code anyone up

[paper flower]

Don't ask to ask

[quick star]

ummm

ok im quiet new

didnt get what u said

but i needed some help with database stuff

thought this would be the right place to be

ill go with the doubt wait

[paper flower]

Instead of asking if someone could help you just ask your question directly

[quick star]

ohh okay

wait ill post ti

so im trying to figure out how i can search something up in a database

like using user input

ill share a screenshot

[paper flower]

Better share code/text

[quick star]

not complete code

im trying to figure out the next portion

what i wanna do is

take user input as restaurant

or whatever place_type

and then display all the data that is linked to that place_type

the code might look basic af but its cus its my first time dealing with database

and its for a school assignment

[paper flower]

Can you share it as text instead?

[quick star]

okay wait

[paper flower]

Also what's the problem with the code?

[quick star]

code is okay

i want to know how to search a certain type of data

as in lets say i wanna search restaurant(place_type) then it will show all the restaurant

[paper flower]

Also don't use format to parametrize your queries

!sql

[delicate field]

SQL & f-strings

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

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

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

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

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

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

[quick star]

and then when i search hospitalthis is what is being thought to us rn

cant defy the teacher or she will deduct marks

[paper flower]

If you want to search by exact string then you can write a query like this:

select * from locations
where name = ?;

[quick star]

okay can u explain a bit in detail plzz

i cant understand very well

[paper flower]

What do you want me to explain?

[quick star]

like will this query work with text

wait

u said string

mb

and how do i take user input

(sorry if i sound a bit dumb iv been awake too long and i gotta submit this in the morning)

[paper flower]

It's more important to sleep than do an assignment, imo

You can take user input as you want, probably use input()?

[quick star]

ahh i already have a backlog

cant take this one

ill fail college

its a mini project

the plan was to use google places api to search for nearby places but i couldnt get my debit card to work for international purchases

to activate my api key

so i gotta create my own database and create a seperate file that can access the database

thats the best i could think of

now the database is created

the problem just lies with accessing specific datatypes

or string values ig

[quick star]

ohh nah that i know using input()

but like should i declare objects first

or is there any like sql function for that

[hearty siren]

in psql if i use i have a table named user
select * from user; i am connected to the db i want by dooing \c <db name>
it shows me 1 row with postgres name which i think is just the owner? of this db??
but if i do select * from public.user it giives me correct table info

[unkempt prism]

Expect issues if you use any reserved words in https://www.postgresql.org/docs/current/sql-keywords-appendix.html

[red lance]

How often should I commit in sqlite3, Should I commit after every insert? My current implementation have insert in a while loop.

It kinda looks like this

for i in iterable:
    If some_condition is True:          
        cur.execute("INSERT INTO dbname VALUES (?);",the_data)                          

should I do it like this way, like commit after every insert in the loop

for i in iterable:
    If some_condition is True:          
        cur.execute("INSERT INTO dbname VALUES (?);",the_data)
        con.commit()                   

Or store the changes in a list then insert in a single ```
cur.executemany("INSERT INTO optins VALUES (?);", the_data_modified_for_this_way)

[hearty siren]

In my limited experience u can do any but bulk commit is way faster so last one is better then others

[quick star]

@paper flower thnx man for helping me figure that thingy out I passed my subject

[dark oxide]

Is this just an error with my type checker or??

fastapi users is installed

the imports are autocompleted so the import is recognized i my environment

[fallen vault]

Im wanting to make my code reusable. Is there a way to have a limit cluase that doesn't affect the query? Like would limit -1 select all data?

[fading patrol]

There's probably a better way to do whatever you're trying to do, but you should show the code #❓｜how-to-get-help

[fallen vault]

Legally, i dont think im allowed to show the code unfortunately.

I guess i could share my sql query though.

select color, sum(tone_time)
from toning
where date between '2023-01-01' and '2023-12-31'
group by color
order by sum(tone_time) DESC
limit 10```

@fading patrol Im basically swapping the color for other items like people and rollers. For people, i dont want to limit it to just 10, i want all the people within the database.

[fading patrol]

So query what you want to query then, what is the problem?

[fallen vault]

So say I replace color with operator. I want to get all 12 operators without changing the format of the query. Is there a way to substitute the 10 with something to grab all records? I’m already passing the two dates and the limit value through a tuple so if I could pass a 0 or none or something so it returns all records, that would be perfect.

[fading patrol]

You could check the documentation for whatever DB you are using to see if LIMIT has some trick like that, but why? it should be easy enough to leave LIMIT out when you don't want it

[fallen vault]

I need it for other reports. I’m trying to have one file generate the 15 or so reports I need rather than 15 files with 98 percent of the same code.

[fading patrol]

Why would you need 15 files?

[fallen vault]

15 reports.

For various information.

[fallen vault]

So the file containers a PDF class to inset the header and footer into the pdf for fpdf. I have a SQL class to pull the information from sqlite3. Then there’s a chart class that creates the bar plot using mat plot. And lastly theres a report class that compiles the information into the pdf and saves it.

[fading patrol]

So your report class can do whatever you need it to do with conditional logic?

[fallen vault]

Exactly. The goal is to have the final sql statement be sql select ?, sum(tone_time) from toning where date between ? and ? group by ? order by sum(tone_time) DESC limit ?

[fallen vault]

And then I’ll just pass the 5 values through the function as arguments.

[oak flax]

Hi, I don't know if this the right place for this question but I am having trouble understanding parts of the solution in leetcode problem NO. 450 which is about delete a node in BST

NVM I think I got the answer I want

thanks

[fading patrol]

Right... So I'm saying add conditional logic (a simple if statement) that calls the same query but without the limit when that's what you want

[fallen vault]

Gotcha. Sorry, i forgot to mention I dont know the limit for techs and operators. Each facility has a different number of techs. Should i default to 100 and say it is what it is? lol

[glacial current]

On an SQLAlchemy query what method should be used to timeout an execute or scalars statment? All I can find is pool timeout.

[paper flower]

sync or async?

[glacial current]

Sync.

[paper flower]

Honestly I don't know if sqlalchemy provides that API

[glacial current]

I suppose you could raise sigalarm and catch that signal on the try catch

But a bit nasty

[paper flower]

Maybe turn it into a context manager if you can

[glacial current]

You mean make a thread ctx manager and cancel the thread at timeout? Not a bad idea

[paper flower]

Yeah, I'd rather use async instead 👀

But you probably have a lot of code, so that's not worth it

[glacial current]

Yeh maybe next time.

K thanks

[frosty mirage]

This is why dynamically-generated query strings can be a good thing. Don’t let your thinking be limited by the constraints of an ORM.

[paper flower]

What? 😅

[drowsy plaza]

https://paste.pythondiscord.com/XQ4Q

C:\Users\junej\AppData\Local\Programs\Python\Python310\python.exe C:\Users\junej\dev\NorthwindAuction\Main.py 
C:\Users\junej\dev\NorthwindAuction\Main.py:4: DeprecationWarning: Using `command_prefix=None` is deprecated and will result in an error in future versions. If you don't need any prefix functionality, consider using InteractionBot.
  Client = commands.Bot(
Bot is online
Traceback (most recent call last):
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\disnake\ui\modal.py", line 208, in _scheduled_task
    await self.callback(interaction)
  File "C:\Users\junej\dev\NorthwindAuction\Extensions\Auction.py", line 34, in callback
    if self.AuctionManager.PlaceBid(self.AuctionObject, Interaction.author, BidAmount):
  File "C:\Users\junej\dev\NorthwindAuction\Modules\Database.py", line 101, in PlaceBid
    self.Session.add(bid)
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2626, in add
    self._save_or_update_state(state)
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2639, in _save_or_update_state
    self._save_or_update_impl(state)
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3224, in _save_or_update_impl
    self._save_impl(state)
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3177, in _save_impl
    to_attach = self._before_attach(state, obj)
  File "C:\Users\junej\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 3303, in _before_attach
    raise sa_exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: Object '<Bid at 0x20c56473040>' is already attached to session '1' (this is '2')

why is this happening

database is sqlite through sqlalchemy

im using it in combination with a discord bot

[paper flower]

You're trying to add object that belongs to one session to another

[drowsy plaza]

i have declared a common sesssion

self.Session = sqlalchemy.orm.sessionmaker(bind = Engine)()

[paper flower]

Apparently not

[drowsy plaza]

how??

[paper flower]

I don't know, it's your code after all

How are you using the session?

[drowsy plaza]

to manage the db

[paper flower]

I mean - how?

Share the code

[drowsy plaza]

its in the pastebin

https://paste.pythondiscord.com/XQ4Q

[paper flower]

Didn't see the pastebin 🙂

Hm, I don't know what may be wrong

How do you create AuctionBidValue instance?

I think because you pass Auction object into Bid it may be automatically associated with Auction's session

Also you usually just create a new session for each interaction/request with your application

And also you're calling sync code from async, this may make your bot slow, if that matters

[drowsy plaza]

not really

if it becomes an issue ill fix it

rn its important i need something working

[drowsy plaza]

so i just make a new session for the bid function

dude int isnt working

[drowsy plaza]

    def PlaceBid(self, AuctionObject: Auction, Bidder: disnake.User, BidAmount: int) -> bool:
        Session = sqlalchemy.orm.sessionmaker(bind = Engine)()


        if (not AuctionObject.Bids and (BidAmount >= AuctionObject.MinimumBid)) or (
                AuctionObject.Bids and AuctionObject.Bids[0].BidAmount < BidAmount):
            Serial = f'{AuctionObject.Serial}-{Session.query(Bid).filter(Bid.Auction == AuctionObject).count()}'

            Session.add(Bid(
                Serial=Serial,
                BidderId=Bidder.id,
                BidAmount=BidAmount,
                BidPlacedTime=datetime.now(),
                Auction=AuctionObject
            ))
            Session.commit()  # Commit the changes to the database

            return True
        else:
            return False

i made a new session

[glacial current]

That is interesting that you make Engine as a class. then i tested to see if it was a class and it is
<class 'sqlalchemy.engine.base.Engine'>

most examples tend to have the lowercase and i always assumed it was an instance. thanks for pointing that out (inderectly)

I think it makes a difference to know its a class.

[drowsy plaza]

how do i fix it tho

[glacial current]

Oh though you fixed it. Let me read up on what's going on

Do you know if the table has the same schema as your model?

[drowsy plaza]

hold on ill get the schema

[glacial current]

Dude I'm not chat gpt 🙂 I'm asking if you made changes to your model after your db was created

Just to rule out that the model is not fitting db

[drowsy plaza]

ye but i always change it by running the code

like

i init the class

so the schema updates

then i run the code

if __name__ == '__main__':
    Manager = AuctionManager() # Update Database Metadata

[glacial current]

Can I recomend that you try alembic to do migrations. It's easy to use and when you change models you just run elembic upgrade heads to go to current. However if the table is created no updates will happen from the create. You would have to drop the db

[drowsy plaza]

ok i can try that

but how does that fix my problem because its a session issue not a schema issue

[glacial current]

But just try move your db and restart

[glacial current]

Ok looking further

[hexed magnet]

print("Hello World")
end

[glacial current]

Look at Session.add. that is wrong I think. It should be with Session() as session

Then later session.add(blah)

Apologies on phone so can't make code block

[drowsy plaza]

i can add other classes fine

[glacial current]

In the same block of code?

[drowsy plaza]

yes!

well not the same

actually

lemme check

[glacial current]

Session maker made a class obj I think from docs

So you have to call it to make a session

[glacial current]

finally : at a computer with a mouse ! to me your problem stems from the class var and how you named it. because its confusing to other people (like me) who read it.

self.Session = sqlalchemy.orm.sessionmaker(bind = Engine)()

i would instead write that as

self.Session = sqlalchemy.orm.sessionmaker(bind = Engine)

or even

self.engine = create_engine(blah....)

because self.Session implies that you have a class. but instead you have an instance. so when i read Session.add() i think its the problem but its elsewhere

onward....to the problem i think... the whole class has one session because you did sessionmaker(bind = Engine)() instead of sessionmaker(bind = Engine) so all instances have one instance and that is what the error is saying.

[drowsy plaza]

intriguing info... works normally without discord

im gonna look into this

[glacial current]

just change the code and see ... change line to

class AuctionManager:
   Session = sqlalchemy.orm.sessionmaker(bind = Engine)

then change every line with Session to

with self.Session() as session: 
      session.add(this)

[pale perch]

hi everyone I want some basic suggestion on creating a pydantic schema to validate a difficulty column in my database it has only three valid option 'easy', 'medium' and 'hard' so it has to be enum but which one should i use IntEnum or Enum.

[glacial current]

I have not use it myself ( but i always want to ) but have you read https://github.com/pydantic/pydantic ?

there is an example there that seems like your question

[pale perch]

could you share the example link. I am just worried that while entering the data the user has to put numbers in intenum but intenum has the pro that i can compare the difficulty by using integer and do queries on the data.

[glacial current]

if you search for enum in the doc (which is very good doc BTW) enum stuff is here https://docs.pydantic.dev/latest/api/standard_library_types/#enumintenum

[pale perch]

thanks for the help i am going to stick with enum for now and test intenum on side if that is right for it or not.

[glacial current]

Oh now i read the enum stuff on it i understand your question. So i think that ints are faster to lookup and index in a database. But you need to be talking about a lot of rows for this to make a real difference. Perhaps space is a concern so int will be smaller. so all in all int is better. except if you want to read the enum because select will be string

[pale perch]

yes thats what i am also concerned so i am using it with fastapi and when it sends the response the user may be able to see what the intenum is but while entering the data is it possible or not just may be i can map the numbers to some value and return to user or any other way.

[glacial current]

Yah that makes sense. you know adding an entry is probably much less frequent an operation. So you can do anything there. even have a select from lookup on the enum.... but i think as with most things like this... just code it so it works and if its slow. make it faster.. by then you will know the problem better and might have a better way to index or to do a lookup table type thing.

1st pass is always messy imo. its an exploration.

[pale perch]

yes and thank you.

if i encounter any problem i will ask again.

[cerulean ledge]

guys, ive got a question: i am coding a discord bot and there should be a list in sql where the inventory stands, although i dont know what to use, or how to use it: I have got type "Array" in my sql table now but i dont know how to insert stuff into it, as it says for example "type 'list' is not supported"

or if anyone could give me tips what to use instead would also be appreciated

[drowsy plaza]

Sounds good 👍🏾

[fallen vault]

If you’re doing inventory, why not make a table with three columns: User, item and amount. When you query the table you can select item, amount where user = ? To get a list of tuples and convert it to a dict.

Originally asked in python discussion but realized it would be better to ask here so i deleted my first post

Does anyone have experince with online database services like vultr or digital ocean? Specifically postgres if that matters.?

[fading patrol]

Of course many people do, what is your actual question?

[fallen vault]

I have a managed database and im wanting to figure out the best way to set it up for multiple locations across the states. Should i have all the data loaded into a single database with a column specifying the location or should i create separate database for each location as I have the option to create new databases.

This is mostly a proof of concept. If this project progresses, I'm sure my job will take over creating the database while I focus on the desktop application.

[drowsy plaza]

made it worse

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Auction at 0x1b0d81018d0> is not bound to a Session; lazy load operation of attribute 'Bids' cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)

[solar cairn]

Hello everyone I'm trying to download Oracle 11G in my computer as I have started to learn Database management system..... So can you all tell me which 11G version should I use? The XE(EXPRESS EDITION),11G 2ND RELEASE,OR THE 1ST RELEASE

[atomic lion]

Hi everyone. Can someone helps with joins: I mean sources or explain briefly,

[plush quartz]

@client.tree.context_menu(name='Save NNID')
async def save_nnid(interaction: discord.Interaction, message: discord.Message):
 

  user = {"user": message.author.id}
  nnid_insert = {"NNID": message.content}
  nnid_update = {"$inc": {"NNID": message.content}}
  check = db.nnid_log.find_one(user)

  if check is None:
      insert = (
          user, nnid_insert
          )
      cursor = db.nnid_log.insert_one(insert)
      await interaction.response.send_message("NNID saved")
  else:
      update = (user, nnid_update)
      cursor = db.nnid_log.update_one({'user': message.author.id}, {'$inc': {'NNID': message.content}}, {"upsert": True})
      await interaction.response.send_message("NNID updated")```

Anyone please helped me fix this error

[fading patrol]

They probably won't make sense until you use them a few times but this looks like a good start: https://www.sqlitetutorial.net/sqlite-join/

If you prefer to work on Postgres, DataCamp has a similar tutorial for that

[fallen vault]

Im trying to run this query sql select ?, sum(tone_time) *? from toning where date between ? and ? group by lower(?) order by sum(tone_time) ASC limit ? But when i insert the value set: ('issue', 100, '2023-01-01', '2023-12-31', 'issue', '100') I get the following: [('issue', 970900.0)] Can i not pass variables for the select statement?

[paper flower]

Yep, you can't parametrize that part of the query

keywords, fields, etc

[fallen vault]

Well fudge. Alright. This is gonna be a long function with a lot of if statements lol

[paper flower]

You could use string interpolation here as long as that specific input doesn't come from the user

Or use a query builder

[fallen vault]

I guess i could replace the keyword (becuase sum needs to be changed to count for some queries) and the select and lower since that comes from a dict and not the user.

[hearty siren]

class Users(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    tg_id  = mapped_column(BIGINT,nullable=False, index=True, unique=True)
    settings = relationship(
        "UserSettings", backref="user", uselist=False, cascade="all, delete-orphan" #is backref correct or should i use user(s)?
    )```
```py
class UserSettings(Base):
    __tablename__ = "user_settings"

    id: Mapped[int] = mapped_column(primary_key=True)
    is_premium: Mapped[bool] = mapped_column(Boolean, default=False)
    is_banned: Mapped[bool] = mapped_column(Boolean, default=False)
    enable_notifications: Mapped[bool] = mapped_column(Boolean, default=True)

    user_id = Column(Integer, ForeignKey("users.id"),unique=True)
    )```
are these models configured properly ? if so how can i access the settings of a user? by using sql on psql terminal

[paper flower]

You could simplify some stuff here, also you're mixing mapped_column and Column, Column since 2.0 is only used with Table objects

And I would use back_populates instead of backref

class Users(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    tg_id: Mapped[int]  = mapped_column(BIGINT, index=True, unique=True) # I think uniuqe columns are already indexed
    settings: Mapped[UserSettings] = relationship(
         back_populates="user", uselist=False, cascade="all, delete-orphan" #is backref correct or should i use user(s)?
    )

class UserSettings(Base):
    __tablename__ = "user_settings"

    id: Mapped[int] = mapped_column(primary_key=True)
    is_premium: Mapped[bool] = mapped_column(default=False)
    is_banned: Mapped[bool] = mapped_column(default=False)
    enable_notifications: Mapped[bool] = mapped_column(default=True)

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), unique=True))
    user: Mapped[User] = relationship(back_populates="settings")

Maybe like this?

You don't have to specify nullable since sqlalchemy can get that info from annotation inside of Mapped

if it's | None then it's nullable

[fallen vault]

Using format worked. Thank you.

[hearty siren]

ohh i had this
tg_id: Mapped[int] = mapped_column(BIGINT,
i was using Mapped[int] but my lsp kept complaning that i can't use both! so i deleted it

is this somehow better?

[paper flower]

Bad lsp

This should be correct, really

I guess you could register a custom type in sqlalchemy's registry

int64 = Annotated[int, 64]

class Base(DeclarativeBase):
    metadata = meta

    registry = registry(
        type_annotation_map={
            int64: BigInteger,
        },
    )

Or maybe you could use NewType here

[hearty siren]

and doesn't backref do the same thing as back populates? we just have to use back_ref in one of the models?

[paper flower]

annotated with a random argument looks kind of ugly

[paper flower]

It does, but back_populates is more clear, imo

It's explicit

[hearty siren]

back_populates="user"
is user correct ? since table name is users

i changed it to users
it was working fine before

[paper flower]

You use property name and not a table name here

It gets model as first argument, or in our case - from the annotation

[hearty siren]

select users.* from users join settings..
but when i do this psql says settings relationship doesn't exist

[paper flower]

Did you create a table/run the migrations?

[hearty siren]

emmm no

i can access it using sqlalchemy

so it should be created?

[paper flower]

No, it's not created automatically

Either run Base.metadata.create_all(bind=engine) or use alembic

First option isn't good if you're going to migrate your db

[hearty siren]

user_settings = await s.execute(select(Users).options(joinedload(Users.settings)).filter(Users.tg_id == tg_id))
this works in sqlalchemy

[hearty siren]

oh yes i am doing this

i can see all the tables are being created alright

and all columns exisst just can't see the relationship columns

[paper flower]

Then it should work

[hearty siren]

or maybe it is this one
public | user_settings_id_seq | sequence | postgres

is this the relationship one?

bcz it is created automatically

[paper flower]

by relationship postgres may mean a table

What's the full error?

[hearty siren]

1 sec

relation "usersettings" does not exist

what it worked for this

SELECT *
FROM users
JOIN user_settings ON users.id = user_settings.user_id
WHERE users.id = user_id;```

user_id at end what is that???

shouldn't it be settins.user_id

[paper flower]

If the name isn't ambiguous there's no need to specify the table

I think

Ah, wait

What query did you write?

It looks a bit weird 😅

[hearty siren]

everything i write is working now lol

[paper flower]

That where condition looks completely redundant

[hearty siren]

before no wuery was working

[paper flower]

SELECT *
FROM users
JOIN user_settings ON users.id = user_settings.user_id

Should work

[hearty siren]

yeah i was just trying everything since nothing was working befoe but something happeend
maybe i saved the file!?

and it reloaded the server?

[deft ridge]

is there a support for polymorphic association in peewee like rails? I cant find any proper material online

[drowsy plaza]

https://paste.pythondiscord.com/XQ4Q

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Auction at 0x1b0d81018d0> is not bound to a Session; lazy load operation of attribute 'Bids' cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)

Need help with this code is in link

[glacial current]

Which line of the pastebin code is throwing the error?

[drowsy plaza]

hold on i made a discovery

its erroring with disnake

i did the same damn code in a seperate file

testlol.py

from Modules import Database

Manager = Database.AuctionManager()
MyAuction = Manager.CreateAuction(
    9,
    'milton rifle',
    15,
    'pounds',
    1000,
    200
)
Manager.PlaceBid(MyAuction, 1, 1000)

it works fine w/ the old code

[drowsy plaza]

fixed the issue!

i created two auciton managers in two seperate classes

i noticed it and just made it so 1 common auction manager class gets passed around

[frosty mirage]

You can substitute operators etc into a query string based on user input. The safe way to do it is to ensure the substituted string comes from a fixed list, e.g. an enum.

[fallen vault]

I ended up using format. The operator wasn’t user input dependent. A third of the reports needed sum(time) and another third needed count(*) so I just made two files and used a dictionary to fill in the rest since those are parameters I can pass normally.

[frosty mirage]

Dynamically generating query strings isn’t so scary, is it?

[fallen vault]

It was Moreso an issue of not thinking how I’ve been thinking for the past 2 days lol. I’ve had something working and was trying to stick to my guns.

Well minus the fact I had 15-18 files before and now I have 3-4.

[glacial current]

Cool glad you worked it out. @merry field Can I make a suggestion? The normal pattern in python is that classes have capital laters but variables functions and instances are lower case with underscore. It is pretty difficult for people to read code that uses a different pattern than that.

[paper flower]

Or you can just use an orm 😛

[fallen vault]

I’m come back through and do it a better look way with the flask version lmao

[cerulean ledge]

that sounds good, thanks man

[frosty mirage]

That was where this discussion started.

[wise goblet]

U could use lightweight query wrapper like Bun lib btw provides if to be more precise

[wise goblet]

Link

[frosty mirage]

This is all just adding extra layers of complexity to avoid something very simple.

[paper flower]

Honestly I would rather use a dedicated query builder rather than string manipulation

It exactly avoids complexity

[wise goblet]

More type safe solution, with easier to maintain

[frosty mirage]

Put this in your ORM/query builder and smoke it:

    condition = " and ".join \
        (
            list
                ( # free-text fields
                    "%(name)s like %(value)s"
                %
                    {
                        "name" : field[0],
                        "value" :
                            sql_string("%" + escape_sql_wild(params.getvalue(field[1])) + "%"),
                    }
                for field in
                    (
                        ("make", "search_make"),
                        ("model", "search_model"),
                        ("details", "search_details"),
                        ...
                    )
                if params.getvalue(field[1]) != ""
            )
        +
            list
                ( # exact-match fields
                    "%(name)s = %(value)s"
                %
                    {
                        "name" : field[0],
                        "value" : sql_string(params.getvalue(field[1])),
                    }
                for field in
                    (
                        ("class_name", "search_class"),
                        ("allocation", "search_allocated"),
                        ("location_name", "search_location"),
                    )
                if params.getvalue(field[1]) != ""
                )
        )

[paper flower]

Honestly this looks pretty much unreadable/unmaintainable

[frosty mirage]

Feel free to show us how you could make it easier to read/maintain.

[paper flower]

Could you explain what it does?

[frosty mirage]

It does lookups on all the fields that the user has entered data for.

[fallen vault]

I figured it out. Thanks.

[paper flower]

@dataclasses.dataclass
class BookFilter:
    title: str | None = None
    title_search: str | None = None

def create_stmt(filter_: BookFilter) -> Select[tuple[Book]]:
    stmt = select(Book)
    if filter_.title is not None:
        stmt = stmt.where(Book.title == filter_.title)
    if filter_.title_search is not None:
        stmt = stmt.where(Book.title.ilike(filter_.title_search))
    ...
    return stmt

[wise goblet]

Wrong server (recommended by accident lib from another lang). Replace sugestion with SQLAlchemy since it is Python server 😅

[cyan bay]

Where would you want/expect to look for a data dictionary for a gnarly old database? Not smart person here trying to comb the monster. Have been told "there's just not a data dictionary/here's my tribal knowledge on XYZ table" and since this may be many years in the making, I will need to make use of it.

[frosty mirage]

See, that’s another problem: there is no such thing as a language-independent ORM/query builder.

[paper flower]

Honestly I don't think this brings any value to developers if the code is not readable/maintainable

[fallen vault]

No ones looking at the back end. Once I get official approval I plan on rewriting it for Postgres. This is mostly a proof of concept.

[frosty mirage]

Fine. Show us how you could do it better.

[wise goblet]

how it is a problem? 🤔 the role of ORM to be language depended solution making it type/function building friendly for specific language

it is feature by design

[paper flower]

I just did?

[paper flower]

[fallen vault]

User isn’t passing anything. But it’s just a proof of concept. I plan on doing it different later with Postgres.

[frosty mirage]

Note also the use of “like” clauses to do partial matches on free-text fields. Query builders don’t seem to provide automatic escaping of wildcards.

[paper flower]

We already went over this

[wise goblet]

as long as u don't use Engine-Dialect specific stuff, your SQLAlchemy is universal for any db engine

[frosty mirage]

Precisely.

[paper flower]

And because of that building queries yourself is better?

[frosty mirage]

In this case, obviously yes.