Paginacja Postgres w Pythonie

wprowadzenie

ten artykuł jest tutorialem na temat paginacji Postgres w Pythonie. Z wielu powodów możesz ograniczyć zwrot zestawów rekordów do małych części, w przeciwieństwie do zwracania dużych zestawów rekordów, które mogą być drogie zarówno pod względem przepustowości, PAMIĘCI RAM i czasu na serwerze, jak i oszczędności dla użytkownika. Nasza metodologia:

  • co i dlaczego? Czym jest paginacja Pythona w PostgreSQL? Dlaczego mielibyśmy chcieć paginować nasze dane Postgres za pomocą Pythona iw jakich okolicznościach?
  • jak? Jak paginować wyniki PostgreSQL w Pythonie? Jakie są najszybsze i najłatwiejsze sposoby, aby to zrobić dla mniejszych zbiorów danych i jakie są bardziej niezawodne i wydajne sposoby dla dużych baz danych i / lub sytuacji, w których zasoby serwera lub klienta są ograniczone, a dokładność jest najważniejsza?

  • SQL: Zrozumienie podstaw pisania SQL dla Postgres. Korzystamy z dBeaver ze względu na jego łatwość i liczbę funkcji.
  • SELECT: używamy polecenia „SELECT” w tym artykule do odpytywania bazy danych Postgres za pomocą aplikacji Python.
  • samouczek na temat konwencji nazewnictwa pokazujący, dlaczego warto przedrostkować zmienne, nazwy kolumn, nazwy tabel itp. jak widać w tym artykule. Na przykład nazywanie „tvariable” przedrostkiem „t”, aby zdefiniować go jako obiekt „text” (string) i „tbl_” przed nazwami tabel, aby wyraźnie odróżnić te obiekty jako tabele. Samouczek jest również nieco głębszy, mówiąc o tym, jak nazywać zmienne w oparciu o system hierarchii.

Utwórz tabelę Postgres, aby paginować

powiedzmy, że tworzymy forum lub system zarządzania treścią (CMS) w Pythonie i Postgres i chcemy tabelę do przechowywania postów na forum.

1
2
3
4
5
6
7
8
9
10
Utwórz tabelę publiczną.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 klucz podstawowy (ID)
);
Utwórz unikalną stronę indeksową_kontents_id_idx na publicznie.page_contents USING btree (id);

teraz, gdy mamy tabelę PostgreSQL, wypełnimy ją danymi, więc mamy coś do przetestowania:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO
page_contents
(
id_author
, t_title
)
SELECT
RANDOM()*100::INTEGER + 1 AS id_author
, array_to_string(array(SELECT substr(’ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789′,((random()*(36-1)+1)::INTEGER), 1) FROM generate_series (1,32)),”) AS t_title;
FROM
generate_Series(1,10000);

przegląd: wstawiamy 10000 rekordów do naszej tabeli „page_contents”. Dla każdego rekordu wypełniamy pole” id_author ” losową liczbą z zakresu od 1 do 100, włącznie z tymi dwiema liczbami. Wypełniamy również pole” t_title ” losowym ciągiem 32 znaków z alfabetu.

Uwaga: Jeśli potrzebujesz większego zbioru danych, po prostu zwiększ „10000”, które widzisz powyżej, do większej liczby.

Analiza

  • INSERT: Użyj funkcji „random” Postgresqla do wygenerowania losowej liczby z zakresu od 0 do 99, podaj ją jako liczbę całkowitą („::integer”), a następnie dodać „1”, tak aby wynik był pomiędzy (włącznie) 1 A 100 i był typu danych wystarczającego do umieszczenia w kolumnie „id_author”.
  • array_to_string: losowo wybieramy litery 32 razy („generate_series (1,32)”) z 36-znakowego łańcucha od A do Z i od 0 do 9 w celu zbudowania łańcucha, który jest wstawiany do pola „t_title”.
  • generate_Series( 1,10000): to mówi silnikowi SQL Postgres, aby wygenerował 10 000 wierszy w tabeli.

teraz, gdy mamy duży zbiór danych do pracy, spójrzmy na dwie różne metody pobierania danych z tej tabeli za pomocą Pythona.

paginacja Pythona Postgres szybka i brudna metoda

najmniej wydajna, ale najczęściej używana jest zwykle obiekt lub biblioteka, która – pod spodem-używa paginacji „limit-offset”. W sytuacjach, w których baza danych jest mała lub średnia, powinno to działać dobrze, z kilkoma wadami, jeśli w ogóle. Później, kiedy wykorzystamy bardziej solidną metodę, porozmawiamy o różnicach.

po pierwsze, zalety każdej metody paginacji:

  • pierwsza strona danych ładuje się szybciej. Dla postrzegania przez użytkowników, jak szybka jest Twoja aplikacja, jest to nieocenione!
  • szybsze wyświetlanie dużych zbiorów danych.
  • potencjał enkapsulacji logiki biznesowej (zależy od tego, jak to zrobisz).
  • klient otrzymuje jednocześnie mniej danych, co może być bardzo przydatne, jeśli nie jest to wymagane w niektórych środowiskach.

następnie metody, których użyjemy, obejmują total, limit, offset i skip.

teraz spójrzmy na jakiś kod:

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
# wynikowa część zapytania „limit”: „limit 10”
# wynikowa część zapytania „offset” : „OFFSET 20”

Analiza

  • page_current: dla celów testowych ustawiliśmy naszą bieżącą stronę na 3.
  • records_per_page: chcemy zwrócić tylko 10 rekordów na stronę.
  • offset: jest to parametr, który mówi Postgresowi, jak daleko ma „przeskoczyć” w tabeli. Zasadniczo ” Pomiń tyle rekordów.”
  • s: tworzy ciąg zapytania do wysłania do PostgreSQL w celu wykonania.

największym problemem z powyższą metodą paginacji „limit offset” jest to, że „pod okładkami” Postgres sumuje całkowitą ilość rekordów, liczy strony na podstawie tej liczby i skanuje indeks, aby policzyć, ile wierszy. Przy pierwszych kilku stronach koszt ten może nie być widoczny. Ale z większymi zestawami rekordów, a nawet średnimi, im wyżej zwiększysz „page_current”, tym wolniejsze będą rzeczy. Na szczęście istnieje wiele sposobów na paginację danych Postgres.

lepsza metoda: seek and keyset pagination

rozwiązaniem, które łagodzi niektóre z wad związanych z użyciem powyższej metody, jest przekazanie unikalnego identyfikatora ostatniego rekordu na poprzedniej stronie.

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 nie jest już potrzebny!
limit_optional_offset = (page_current – 1) * records_per_page
s = „”
S += ” SELECT *”
s += „FROM page_contents”
s += „WHERE id >” & id_page_poprzedni
s += „ORDER BY id”
s += „limit” + records_per_page
# s += „offset” + offset
# wynikowy „limit” część zapytania: „limit 10”

zauważ, że dzięki tej metodzie nie potrzebujemy już polecenia OFFSET. Dodając klauzulę „WHERE”, aby nakazać Postgresqlowi pominięcie wszystkich poprzednich rekordów, aby wiedział, od czego zacząć wyświetlanie rekordów, używamy limitu do polecenia, ile rekordów ma zostać zwróconych.

chociaż istnieją z pewnością bardziej wydajne metody paginacji tabeli Postgres, powyższa metoda przynosi ogromne korzyści w szybkości i wydajności. Bardzo ważną kwestią jest:” id ” musi być indeksowane.

wniosek

w tym tutorialu nauczyliśmy się dwóch metod używania Pythona do paginacji danych Postgres. Zaczęliśmy od stworzenia tabeli PostgreSQL, a następnie dodaliśmy do niej 10 000 rekordów, więc mieliśmy na czym bazować, a następnie porównaliśmy dwie metody paginacji w Pythonie i wreszcie przeanalizowaliśmy zalety i wady obu metod. Podczas tego procesu użyliśmy funkcji „losowej” Postgresqla, konkatenacji łańcuchów Pythona do budowania SQL do wysyłania do Postgres oraz tworzenia zmiennych.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.