Python Pagination av Postgres

introduktion

denna artikel är en handledning om Python Pagination av Postgres data. Av många anledningar kanske du vill begränsa returen av rekordmängder till små bitar, i motsats till att returnera stora rekordmängder som kan vara dyra både när det gäller bandbredd, RAM och tid på servern, samt ekonomi för användaren. Vår metodik:

  • vad och varför? Vad är Python pagination i PostgreSQL? Varför skulle vi vilja paginera våra Postgres-data med Python och under vilka omständigheter?
  • hur? Hur paginerar vi PostgreSQL-resultat med Python? Vilka är de snabbaste och enklaste sätten att göra detta för mindre datamängder och vilka är de mer robusta och effektiva sätten för stora databaser och/eller situationer där server-eller klientresurser är begränsade och noggrannhet är av största vikt?

förutsättningar

  • SQL: förstå grunderna för att skriva SQL för Postgres. Vi använder dBeaver på grund av dess lätthet och antal funktioner.
  • välj: vi använder liberalt kommandot” välj ” i den här artikeln för att fråga en Postgres-databas med en Python-applikation.
  • handledning om namnkonventioner som visar varför du kanske vill prefixa dina variabler, kolumnnamn, tabellnamn etc. som du ser gjort i den här artikeln. Till exempel namnge ”tvariable” med prefixet ”t” för att definiera det som ett ”text” (sträng) objekt och ”tbl_” före tabellnamn för att tydligt skilja dessa objekt som tabeller. Handledningen går också lite djupare och pratar om hur man heter variabler baserat på ett hierarkisystem.

skapa en Postgres-tabell för att paginera

låt oss säga att vi skapar ett forum eller content management system (CMS) i Python och Postgres och vi vill ha en tabell för att lagra foruminlägg.

1
2
3
4
5
6
7
8
9
10
Skapa tabell offentliga.page_contents (
id serial inte NULL,
id_session int4 NULL standard 0,
id_author int4 NULL standard 0,
t_title VARCHAR(256) NULL,
t_contents text NULL,
D_CREATED datum NULL standard nu(),
begränsning PAGE_CONTENTS_PKEY primärnyckel (id)
);
skapa unikt index page_contents_id_idx på allmänheten.page_contents använder btree (id);

nu när vi har ett PostgreSQL-bord fyller vi det med data, så vi har något att testa med:

1
2
3
4
5
6
7
8
9
10
11
infoga i
page_contents
(
id_author
, t_title
)
välj
slumpmässig()*100::heltal + 1 som id_author
, array_to_string(array(välj substr(’ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’,((slumpmässig()*(36-1)+1)::heltal), 1) från generate_series (1,32)),”) som t_title;
från
generate_Series(1,10000);

översikt: vi sätter in 10000 poster i vår” page_contents ” – tabell. För varje post fyller vi fältet ”id_author” med ett slumpmässigt tal mellan 1 och 100, inklusive de två siffrorna. Vi fyller också fältet ”t_title” med en slumpmässig sträng med 32 tecken från alfabetet.

Obs: Om du behöver en större dataset, bara öka ”10000” du ser ovan till ett större antal.

analys

  • infoga: används PostgreSQL s” random ” Funktion för att generera ett slumptal mellan 0 och 99, kasta det som ett heltal (”::”1″, så att resultatet är mellan (inklusive) 1 och 100 och är av tillräcklig datatyp för att placera i kolumnen” id_author”.
  • array_to_string: vi väljer slumpmässigt bokstäver 32 gånger (”generate_series (1,32)”) från en 36 tecken lång sträng från A till Z och 0 till 9 för att bygga en sträng som sätts in i fältet” t_title”.
  • generate_Series (1,10000): detta berättar för Postgres SQL-motorn att generera 10 000 rader i tabellen.

nu när vi har en stor dataset att arbeta med, låt oss titta på två olika metoder för att hämta data från denna tabell med Python.

Python pagination av Postgres snabb och smutsig metod

den minst effektiva men mest ofta använda är vanligtvis ett objekt eller bibliotek som-under – använder ”limit – offset” pagination. För situationer där din databas är liten eller medelstor, bör detta fungera bra, med få nackdelar, om några. Senare, när vi använder en mer robust metod, talar vi om skillnaderna.

för det första fördelarna med någon metod för paginering:

  • den första sidan av dina data laddas snabbare. För användarens uppfattning om hur snabbt din app är, är detta ovärderligt!
  • snabbare vyer av stora datamängder.
  • Potential för affärslogik inkapsling (beror på hur du gör det).
  • klienten får mindre data åt gången, vilket kan vara super användbart, om inte ett krav i vissa miljöer.

Därefter inkluderar metoderna vi använder total, limit, offset och skip.

låt oss nu titta på någon 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 += ” välj *”
s += ”från page_contents”
s += ”Beställ efter id”
s += ”begränsa” + records_per_page
s += ”offset” + offset
# resulterande ”limit” del av frågan: ”limit 10”
# resulterande ”offset” del av frågan: ”OFFSET 20”

analys

  • page_current: för teständamål ställer vi in vår nuvarande sida för att vara 3.
  • records_per_page: vi vill bara returnera 10 poster per sida.
  • offset: detta är parametern som berättar Postgres hur långt man ska ”hoppa” i tabellen. Väsentligen, ” hoppa över så många poster.”
  • s: skapar en frågesträng för att skicka till PostgreSQL för körning.

det största problemet med ovanstående” limit offset ”- metod för paginering är att” under omslaget ” Postgres lägger till den totala mängden poster, räknar sidor baserat på det numret och skannar indexet för att räkna hur många rader. Med de första sidorna kan denna kostnad inte vara uppenbar. Men med större skivor och till och med medelstora, ju högre du ökar ”page_current”, desto LÅNGSAMMARE blir det. Lyckligtvis finns det många sätt vi kan göra paginering av Postgres-data.

en bättre metod: Sök och keyset pagination

en lösning som lindrar några av nackdelarna med att använda ovanstående metod är att skicka in det unika ID för den sista posten på föregående sida.

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 behövs inte längre!
limit_optional_offset = (page_current – 1) * records_per_page
s = ””
s += ” välj *”
s += ”från page_contents”
s += ”där id >” & id_page_previous
s += ”Beställ efter id”
s += ”limit” + records_per_page
# s += ”offset” + offset
# resulterande ”limit” del av frågan: ”limit 10”

Observera, med den här metoden behöver vi inte längre OFFSET-kommandot. Genom att lägga till ” var ” – klausulen för att berätta för PostgreSQL att lämna ut alla tidigare poster, så det vet var man ska börja visa poster, fortsätter vi att använda LIMIT för att beordra hur många poster som ska returneras.

även om det säkert finns mer effektiva metoder för att paginera ett Postgres-bord, skördar ovanstående metod enorma fördelar i hastighet och effektivitet. En mycket viktig övervägning är: ”id” måste indexeras.

slutsats

i denna handledning lärde vi oss två metoder för att använda Python för paginering av Postgres-data. Vi började med att skapa en PostgreSQL-tabell, sedan lade vi till 10 000 poster i tabellen, så vi hade något att basera testet på, och sedan jämförde vi två metoder för paginering i Python, och slutligen analyserade vi för-och nackdelar med båda metoderna. Under denna process använde vi PostgreSQL: s” random ” – Funktion, Python string concatenation för att bygga SQL för att skicka till Postgres och variabel skapande.

Lämna ett svar

Din e-postadress kommer inte publiceras.