Paginarea Python a Postgres

Introducere

acest articol este un tutorial despre paginarea Python a datelor Postgres. Din mai multe motive, poate doriți să limitați returnarea seturilor de înregistrări la bucăți mici, spre deosebire de returnarea seturilor de înregistrări mari care pot fi costisitoare atât în ceea ce privește lățimea de bandă, memoria RAM și timpul pe server, cât și economia pentru utilizator. Metodologia noastră:

  • ce și de ce? Ce este paginarea Python în PostgreSQL? De ce am dori să paginăm datele Postgres cu Python și în ce circumstanțe?
  • cum? Cum paginăm rezultatele PostgreSQL cu Python? Care sunt cele mai rapide și mai ușoare modalități de a face acest lucru pentru seturi de date mai mici și care sunt modalitățile mai robuste și mai eficiente pentru baze de date mari și/sau situații în care resursele serverului sau clientului sunt limitate și precizia este primordială?

cerințe preliminare

  • SQL: înțelegerea elementelor de bază ale scrierii SQL pentru Postgres. Folosim dBeaver datorită ușurinței și numărului de caracteristici.
  • SELECT: folosim liberal comanda „SELECT” din acest articol pentru a interoga o bază de date Postgres cu o aplicație Python.
  • Tutorial despre convențiile de denumire care arată de ce poate doriți să prefixați variabilele, numele coloanelor, numele tabelelor etc. după cum vedeți făcut în acest articol. De exemplu, denumirea „tvariable” cu prefixul „t” pentru a-l defini ca obiect „text” (șir) și „tbl_” înainte de numele tabelelor pentru a distinge clar acele obiecte ca tabele. Tutorialul merge un pic mai adânc, de asemenea, vorbind despre cum să denumiți variabile bazate pe un sistem de ierarhie.

creați un tabel Postgres pentru a pagina

să presupunem că creăm un forum sau un sistem de gestionare a conținutului (CMS) în Python și Postgres și dorim un tabel pentru stocarea postărilor pe forum.

1
2
3
4
5
6
7
8
9
10
creați tabelul public.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 data null DEFAULT now(),
constrângere page_contents_pkey cheie primară (id)
);
creați un index unic page_contents_id_idx pe public.page_contents folosind btree (id);

acum că avem un tabel PostgreSQL, îl vom umple cu date, Deci avem ceva de testat:

1
2
3
4
5
6
7
8
9
10
11
introduceți în
conținutul paginii
(
id_author
, t_title
)
selectați
aleatoriu()*100::INTEGER + 1 ca id_author
, array_to_string(array(SELECT substr(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’,((aleatoare()*(36-1)+1)::INTEGER),1) din generate_series (1,32)),”) ca t_title;
din
generate_Series(1,10000);

Prezentare generală: introducem 10000 de înregistrări în tabelul nostru „page_contents”. Pentru fiecare înregistrare, completăm câmpul” id_author ” cu un număr aleatoriu între 1 și 100, inclusiv cele două numere. De asemenea, completăm câmpul „t_title” cu un șir aleatoriu de 32 de caractere din alfabet.

Notă: Dacă aveți nevoie de un set de date mai mare, creșteți doar „10000” pe care îl vedeți mai sus la un număr mai mare.

analiză

  • INSERT: funcția „aleatoare” PostgreSQL folosit pentru a genera un număr aleatoriu între 0 și 99, aruncat ca un număr întreg („::integer”), apoi adăugați „1”, astfel încât rezultatul să fie între (inclusiv) 1 și 100 și să aibă un tip de date suficient pentru a fi plasat în coloana „id_author”.
  • array_to_string: selectăm aleatoriu litere de 32 de ori („generate_series(1,32)”) dintr-un șir lung de 36 de caractere de la A La Z și 0 la 9 pentru a construi un șir care este introdus în câmpul „t_title”.
  • generate_Series(1,10000): aceasta spune motorului SQL Postgres să genereze 10,000 de rânduri în tabel.

acum, că avem un set de date mare pentru a lucra cu, să ne uităm la două metode distincte pentru preluarea datelor din acest tabel folosind Python.

paginarea Python a metodei rapide și murdare Postgres

cel mai puțin eficient, dar cel mai des utilizat este de obicei un obiect sau o bibliotecă care-dedesubt – folosește paginarea „limită – offset”. Pentru situațiile în care baza dvs. de date are dimensiuni mici sau medii, aceasta ar trebui să funcționeze bine, cu puține dezavantaje, dacă există. Mai târziu, când vom utiliza o metodă mai robustă, vom vorbi despre diferențele.

în primul rând, beneficiile oricărei metode de paginare:

  • prima pagină a datelor dvs. se va încărca mai repede. Pentru percepția utilizatorului de cât de repede aplicația este, acest lucru este de neprețuit!
  • vizualizări mai rapide ale seturilor de date mari.
  • potențial pentru încapsularea logicii de afaceri (depinde de modul în care o faceți).
  • Clientul primește mai puține date la un moment dat, ceea ce poate fi foarte util, dacă nu o cerință în anumite medii.

apoi, metodele pe care le vom folosi includ total, limit, offset și skip.

acum să ne uităm la un cod:

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 += „ORDER BY id”
s += „LIMIT” + records_per_page
s += „Offset” + Offset
# rezultat „limit” parte a interogării: „limit 10”
# rezultat „offset” parte a interogării: „OFFSET 20”

analiză

  • page_current: în scopuri de testare, am configurat pagina noastră curentă să fie 3.
  • records_per_page: vrem să returnăm doar 10 înregistrări pe pagină.
  • offset: acesta este parametrul care spune Postgres cât de departe să „sară” în tabel. În esență, ” săriți aceste multe înregistrări.”
  • s: creează un șir de interogare pentru a trimite la PostgreSQL pentru execuție.

cea mai mare problemă cu metoda de paginare „limită offset” de mai sus este că „sub coperți” Postgres adună cantitatea totală de înregistrări, numără paginile pe baza acelui număr și scanează indexul pentru a număra câte rânduri. Cu primele câteva pagini, acest cost poate să nu fie evident. Dar, cu înregistrări mai mari și chiar cu cele de dimensiuni medii, cu cât creșteți mai mult „page_current”, cu atât lucrurile vor deveni mai lente. Din fericire, există multe modalități prin care putem face paginarea datelor Postgres.

o metodă mai bună: caută și keyset paginare

o soluție care atenuează unele dintre contra asociate cu utilizarea metodei de mai sus este de a trece în ID-ul unic al ultimei înregistrări în pagina anterioară.

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 nu mai este necesar!
limit_optional_offset = (page_current – 1) * records_per_page
s = „”
s += ” selectați *”
s += „din page_contents”
s += „unde id >” & id_page_previous
s += „comanda după id”
s += „limit” + records_per_page
# s += „Offset” + Offset
# rezultat „limit” parte a interogării: „limit 10”

observați, cu această metodă, nu mai avem nevoie de comanda OFFSET. Prin adăugarea clauzei „unde” pentru a spune PostgreSQL să lase toate înregistrările anterioare, astfel încât să știe de unde să înceapă afișarea înregistrărilor, continuăm să folosim LIMIT pentru a comanda câte înregistrări să returneze.

în timp ce există cu siguranță metode mai eficiente pentru a Pagina un tabel Postgres, metoda de mai sus culege beneficii masive în viteză și eficiență. O considerație foarte importantă este:” id ” trebuie indexat.

concluzie

în acest tutorial am învățat două metode pentru utilizarea Python pentru paginarea datelor Postgres. Am început prin crearea unui tabel PostgreSQL, apoi am adăugat 10.000 de înregistrări în tabel, așa că am avut ceva pe care să ne bazăm testul și apoi am comparat două metode de paginare în Python și, în final, am analizat avantajele și dezavantajele ambelor metode. În timpul acestui proces, am folosit funcția „aleatorie” a PostgreSQL, concatenarea șirului Python pentru construirea SQL pentru a trimite la Postgres și crearea de variabile.

Lasă un răspuns

Adresa ta de email nu va fi publicată.