Python stránkování Postgres

Úvod

tento článek je návod na Python stránkování Postgres dat. Z mnoha důvodů, možná budete chtít omezit návrat sad záznamů na malé kousky, na rozdíl od vracení velkých sad záznamů, které mohou být drahé jak z hlediska šířky pásma, RAM, a čas na serveru, stejně jako ekonomika pro uživatele. Naše metodika:

  • co a proč? Co je stránkování Pythonu v PostgreSQL? Proč bychom chtěli stránkovat naše data Postgres pomocí Pythonu a za jakých okolností?
  • jak? Jak stránkujeme výsledky PostgreSQL s Pythonem? Jaké jsou nejrychlejší a nejjednodušší způsoby, jak to udělat pro menší datové sady a jaké jsou robustnější a efektivnější způsoby pro velké databáze a / nebo situace, kdy jsou zdroje serveru nebo klienta omezené a přesnost je prvořadá?

předpoklady

  • SQL: pochopení základů zápisu SQL pro Postgres. Používáme dBeaver kvůli jeho snadnosti a počtu funkcí.
  • SELECT: používáme liberální příkaz „SELECT“ v tomto článku pro dotaz na databázi Postgres pomocí aplikace Python.
  • výukový program o konvencích pojmenování, který ukazuje, proč můžete chtít prefixovat proměnné, názvy sloupců, názvy tabulek atd. jak vidíte v tomto článku. Například pojmenování „tvariable“ s předponou „t“, která jej definuje jako objekt“ text „(řetězec) a“ tbl_ “ před názvy tabulek, aby bylo možné tyto objekty jasně rozlišit jako tabulky. Výukový program jde také trochu hlouběji a mluví o tom, jak pojmenovat proměnné na základě hierarchického systému.

vytvořte tabulku Postgres pro stránkování

řekněme, že vytváříme fórum nebo systém pro správu obsahu (CMS)v Pythonu a Postgres a chceme tabulku pro ukládání příspěvků na fóru.

1
2
3
4
5
6
7
8
9
10
vytvořit veřejnou tabulku.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 PRIMARY KEY (id)
);
vytvořit jedinečný INDEX page_contents_id_idx na veřejnosti.page_contents pomocí btree (id);

nyní, když máme tabulku PostgreSQL, vyplníme ji daty, takže máme s čím testovat:

1
2
3
4
5
6
7
8
9
10
11
vložit do
page_contents
(
id_author
, t_title
)
vybrat
RANDOM()*100:: INTEGER + 1 jako id_author
, array_to_string (array (SELECT substr (‚ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘, ((random()*(36-1)+1)::INTEGER), 1) z generate_series (1,32)),“) jako t_title;
z
generate_Series(1,10000);

Přehled: do tabulky „page_contents“ vkládáme 10000 záznamů. Pro každý záznam vyplňujeme pole „id_author“ náhodným číslem mezi 1 a 100, včetně těchto dvou čísel. Také vyplňujeme pole „t_title“ náhodným řetězcem 32 znaků z abecedy.

Poznámka: Pokud potřebujete větší datovou sadu, stačí zvýšit „10000“, které vidíte výše, na větší číslo.

analýza

  • vložit: použitá funkce PostgreSQL „random“ pro generování náhodného čísla mezi 0 a 99, obsazení jako celé číslo („::integer“) a poté přidejte „1“, takže výsledek je mezi (včetně) 1 a 100 a je dostatečného datového typu pro umístění do sloupce“ id_author“.
  • array_to_string: náhodně vybíráme písmena 32krát („generate_series (1,32)“) z 36 znaků dlouhého řetězce od A do Z A 0 až 9, abychom vytvořili řetězec, který je vložen do pole“ t_title“.
  • generate_Series (1,10000): to říká Postgres SQL engine generovat 10,000 řádků v tabulce.

Nyní, když máme velkou datovou sadu pro práci, podívejme se na dvě odlišné metody pro načítání dat z této tabulky pomocí Pythonu.

Python stránkování rychlé a špinavé metody postgres

nejméně efektivní, ale nejčastěji používaný je obvykle objekt nebo knihovna, která-pod-používá stránkování“ limit-offset“. V situacích, kdy je vaše databáze malá nebo střední, by to mělo fungovat dobře, s několika nevýhodami, pokud existují. Později, když použijeme robustnější metodu, budeme hovořit o rozdílech.

nejprve výhody jakékoli metody stránkování:

  • první stránka vašich dat se načte rychleji. Pro uživatelské vnímání toho, jak rychle je vaše aplikace, je to neocenitelné!
  • rychlejší zobrazení velkých datových souborů.
  • potenciál pro zapouzdření obchodní logiky (záleží na tom, jak to uděláte).
  • klient přijímá méně dat najednou, což může být velmi užitečné, ne-li požadavek v určitých prostředích.

dále použijeme metody total, limit, offset a skip.

nyní se podívejme na nějaký kód:

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
# výsledný „LIMIT“ část dotazu: „limit 10“
# výsledná část „offset“ dotazu: „OFFSET 20“

analýza

  • page_current: pro účely testování jsme nastavili naši aktuální stránku na 3.
  • records_per_page: chceme vrátit pouze 10 záznamů na stránku.
  • offset: toto je parametr, který říká Postgres, jak daleko „skočit“ v tabulce. V podstatě, “ přeskočte tolik záznamů.“.“
  • s: vytvoří řetězec dotazu, který odešle PostgreSQL k provedení.

největším problémem výše uvedené metody stránkování „limit offset“ je to, že“ pod kryty “ Postgres sčítá celkové množství záznamů, počítá stránky na základě tohoto čísla a skenuje index, aby spočítal, kolik řádků. U prvních několika stránek nemusí být tato cena zřejmá. Ale s většími sadami záznamů a dokonce i středně velkými, čím vyšší zvýšíte „page_current“, tím pomalejší se věci dostanou. Naštěstí existuje mnoho způsobů, jak můžeme provést stránkování dat Postgres.

lepší metoda: Hledat a keyset stránkování

řešením, které zmírňuje některé nevýhody spojené s použitím výše uvedené metody, je předat jedinečné ID posledního záznamu na předchozí stránce.

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 již není potřeba!
limit_optional_offset = (page_current – 1) * records_per_page
s = „“
s += “ SELECT *“
s += „FROM page_contents“
s += „WHERE id >“ & id_page_previous
s += „ORDER BY id“
s += „LIMIT“ + records_per_page
# s += „OFFSET“ + offset
# výsledný „limit“ část dotazu: „limit 10“

Všimněte si, že s touto metodou již nepotřebujeme příkaz OFFSET. Přidáním klauzule „kde“, která PostgreSQL řekne, aby vynechal všechny předchozí záznamy, takže ví, kde začít zobrazovat záznamy, používáme LIMIT k příkazu, kolik záznamů se má vrátit.

i když existují jistě účinnější metody stránkování tabulky Postgres, výše uvedená metoda sklízí obrovské výhody v rychlosti a účinnosti. Velmi důležitá úvaha je:“ id “ musí být indexováno.

závěr

v tomto tutoriálu jsme se naučili dvě metody pro použití Pythonu pro stránkování dat Postgres. Začali jsme vytvořením tabulky PostgreSQL, poté jsme do tabulky přidali 10 000 záznamů, takže jsme měli na čem založit test, a pak jsme porovnali dvě metody stránkování v Pythonu a nakonec jsme analyzovali výhody a nevýhody obou metod. Během tohoto procesu jsme využili funkci PostgreSQL „random“, zřetězení řetězců Pythonu pro vytváření SQL pro odeslání do Postgres a vytváření proměnných.

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.