Home > OS >  How get one record per each hundred of records - SQLAlchemy,
How get one record per each hundred of records - SQLAlchemy,

Time:09-28

Databases: mysql

ORM - SQLAlchemy in python

I have ten thousands records in table. For one of cases, I have to only one record per each hundred of records. What i should do?

I read about SET and LIMIT. For this moment i thought about something like below

!!!pseudocode!!

in loop
a = [1, 101, 201, 301, 401, 501, 601, 701, ..., 9001] 
table_of_records = []
for record_to_start in a 
    table_of_records.add(SELECT * FROM paczka LIMIT=1 SET=record_to_start)

!!!end of pseudocode!!!

exist something smarter than use limit and set in loop ?

CodePudding user response:

If your table has an integer primary key that autoincrements by 1 then you could return the rows where MOD(id, 100) equals 1:

import sqlalchemy as sa

paczka = sa.Table("paczka", sa.MetaData(), autoload_with=engine)

with engine.connect() as conn:
    results = conn.execute(
        sa.select(paczka).where(sa.func.mod(paczka.c.id, 100) == 1)
    ).fetchall()

That won't necessarily be perfect because autoincrement columns are not guaranteed to be perfectly sequential (i.e., there may be gaps in the values) but it may be "close enough" depending on your specific task.

  • Related