Bevezetés
ez a cikk egy bemutató Python oldalszámozás Postgres adatok. Számos okból érdemes korlátozni a rekordkészletek visszatérését kis darabokra, szemben a nagy rekordkészletek visszaküldésével, amelyek drágák lehetnek mind a sávszélesség, a RAM, mind a szerveren töltött idő, valamint a felhasználó gazdaságossága szempontjából. Módszertanunk:
- mit és miért? Mi a Python Lapozás a PostgreSQL – ben? Miért szeretnénk Paginálni a Postgres adatainkat Pythonnal és milyen körülmények között?
- hogyan? Hogyan pagináljuk a PostgreSQL eredményeket a Python segítségével? Mi a leggyorsabb és legegyszerűbb módja ennek kisebb adathalmazok esetén, és melyek a robusztusabb és hatékonyabb módszerek nagy adatbázisok és/vagy olyan helyzetek esetén, ahol a szerver vagy kliens erőforrásai korlátozottak és a pontosság a legfontosabb?
előfeltételek
- SQL: megértése az alapokat az írás SQL Postgres. A dBeaver-t könnyűsége és számos funkciója miatt használjuk.
- SELECT: ebben a cikkben a “SELECT” parancsot liberálisan használjuk a Postgres adatbázis lekérdezésére egy Python alkalmazással.
- bemutató az elnevezési konvenciókról, amely megmutatja, hogy miért érdemes előtagot adni a változóknak, oszlopneveknek, táblázatneveknek stb. ahogy ebben a cikkben látja. Például a “tvariable” elnevezése a “t” előtaggal, hogy “szöveg” (string) objektumként és “tbl_” – ként definiálja a táblanevek előtt, hogy egyértelműen megkülönböztesse ezeket az objektumokat táblákként. Az oktatóanyag egy kicsit mélyebbre megy, arról is beszél, hogyan lehet A változókat hierarchikus rendszer alapján megnevezni.
hozzon létre egy Postgres táblát a
lapozáshoz tegyük fel, hogy fórumot vagy tartalomkezelő rendszert (CMS) hozunk létre Pythonban és Postgres-ben, és egy táblát akarunk a fórumbejegyzések tárolására.
|
1
2 3 4 5 6 7 8 9 10 |
Táblázat létrehozása nyilvános.page_contents (
id serial NOT NULL, id_session int4 null DEFAULT 0, id_author int4 null DEFAULT 0, T_TITLE VARCHAR(256) NULL, t_contents text NULL, d_created DATE null DEFAULT now(), CONSTRAINT page_contents_pkey elsődleges kulcs (id) ); hozzon létre egyedi index page_contents_id_idx nyilvános.page_contents a btree (id használatával); |
most, hogy van egy PostgreSQL táblánk, kitöltjük adatokkal, tehát van mit tesztelnünk:
|
1
2 3 4 5 6 7 8 9 10 11 |
beszúrás
page_contents ( id_author , t_title ) SELECT RANDOM()*100::INTEGER + 1 AS id_author , array_to_string(array(SELECT substr(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’,((véletlen()*(36-1)+1)::egész szám),1) generate_series (1,32)),”) mint t_title; generate_Series(1,10000); |
áttekintés: 10000 rekordot illesztünk be a” page_contents ” táblázatba. Minden rekord esetében az “id_author” mezőt egy 1 és 100 közötti véletlen számmal töltjük ki, beleértve ezt a két számot. A “t_title” mezőt az ábécé 32 karakterből álló véletlenszerű karakterláncával is kitöltjük.
Megjegyzés: Ha nagyobb adatkészletre van szüksége, csak növelje a fent látható “10000” – et nagyobb számra.
analízis
- INSERT: használt PostgreSQL “random” függvény generál egy véletlen szám között 0 és 99, leadott, mint egy egész (“::integer”), majd adja hozzá az “1” – et, hogy az eredmény (inclusive) 1 és 100 között legyen, és elegendő adattípusú legyen az “id_author” oszlopba helyezéshez.
- array_to_string: véletlenszerűen 32-szer választunk ki betűket (“generate_series(1,32)”) egy 36 karakter hosszú karakterláncból A-tól Z-ig és 0-tól 9-ig, hogy létrehozzunk egy karakterláncot, amely a “t_title” mezőbe kerül.
- generate_Series(1,10000): ez azt mondja a Postgres SQL motornak, hogy 10 000 Sort generáljon a táblázatban.
most, hogy van egy nagy adatkészletünk, hogy dolgozzunk, nézzünk meg két különböző módszert az adatok lekérésére ebből a táblázatból a Python segítségével.
Python oldalszámozás Postgres quick and dirty method
a legkevésbé hatékony, de leggyakrabban használt általában egy objektum vagy könyvtár, amely-alatta – “limit – offset” oldalszámozást használ. Olyan helyzetekben, amikor az adatbázis kicsi vagy közepes méretű, ennek jól kell működnie, kevés hátránnyal, ha van ilyen. Később, amikor robusztusabb módszert alkalmazunk, beszélünk a különbségekről.
először is, a lapozás bármely módszerének előnyei:
- az adatok első oldala gyorsabban töltődik be. A felhasználó megítélése, hogy milyen gyors az alkalmazás, ez felbecsülhetetlen!
- nagyobb adatkészletek gyorsabb nézetei.
- az üzleti logika kapszulázásának lehetősége (attól függ, hogyan csinálod).
- az ügyfél egyszerre kevesebb adatot kap, ami szuper hasznos lehet, ha bizonyos környezetekben nem követelmény.
ezután az általunk használt módszerek közé tartozik a total, limit, offset és skip.
most nézzük meg néhány kódot:
|
1
2 3 4 5 6 7 8 9 10 11 12 13 |
page_current = 3
records_per_page = 10 offset = (page_current – 1) * records_per_page s = “” s += ” SELECT *” s += “FROM page_contents” s += “id szerinti sorrend” s += “LIMIT” + records_per_page s += “Offset” + Offset # a lekérdezés eredményező “limit” része: “limit 10” # a lekérdezés eredményező “offset” része: “OFFSET 20” |
Analysis
- page_current: tesztelés céljából az aktuális oldalunkat 3-ra állítjuk be.
- records_per_page: oldalanként csak 10 rekordot szeretnénk visszaadni.
- eltolás: ez az a paraméter, amely megmondja a Postgres-nek, hogy meddig kell “ugrani” a táblázatban. Lényegében, ” hagyja ki ezt a sok rekordot.”
- s: létrehoz egy lekérdezési karakterláncot, amelyet elküldhet a PostgreSQL-nek végrehajtásra.
a fenti “limit offset” módszer legnagyobb problémája az, hogy az “Under the covers” Postgres összeadja a rekordok teljes mennyiségét, az oldalakat e szám alapján számolja, és beolvassa az Indexet, hogy megszámolja, hány sort. Az első néhány oldalnál ez a költség nem biztos, hogy nyilvánvaló. De nagyobb, sőt közepes méretű rekordkészleteknél minél magasabbra növeli a “page_current” értéket, annál lassabbak lesznek a dolgok. Szerencsére számos módon meg tudjuk csinálni a Postgres adatok lapozását.
jobb módszer: seek and keyset oldalszámozás
a fenti módszer használatával kapcsolatos hátrányok enyhítését szolgáló megoldás az előző oldal utolsó rekordjának egyedi azonosítója.
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ID_page_previous = 200
page_current = 3 records_per_page = 10 # limit_optional_offset már nincs szükség! limit_optional_offset = (page_current – 1) * records_per_page s = “SELECT *” s += “FROM page_contents” s += ” ahol id>”& id_page_previous s += “id szerinti sorrend” s += “limit” + records_per_page # s + = “Offset” + Offset # a lekérdezés “limit” része: “limit 10” |
figyeljük meg, hogy ezzel a módszerrel már nincs szükségünk az OFFSET parancsra. A” hol ” záradék hozzáadásával, hogy a PostgreSQL hagyja ki az összes korábbi rekordot, így tudja, hol kezdje meg a rekordok megjelenítését, a LIMIT használatával továbbra is parancsot adunk arra, hogy hány rekordot adjon vissza.
bár biztosan vannak hatékonyabb módszerek a Postgres táblázat lapozásához, a fenti módszer hatalmas előnyökkel jár a sebesség és a hatékonyság terén. Nagyon fontos szempont: az” azonosítót ” indexelni kell.
következtetés
ebben az oktatóanyagban két módszert tanultunk a Python használatához a Postgres adatok lapozásához. Először létrehoztunk egy PostgreSQL táblát, majd hozzáadtunk 10 000 rekordot a táblához, így volt mire alapoznunk a tesztet, majd összehasonlítottuk a Python két lapozási módszerét, végül elemeztük mindkét módszer előnyeit és hátrányait. A folyamat során felhasználtuk a PostgreSQL “random” funkcióját, a Python string concatenation-t az SQL létrehozásához a Postgres-hez való küldéshez és a változó létrehozásához.