Python-paginering van Postgres

Inleiding

dit artikel is een tutorial over Python-paginering van Postgres-gegevens. Om vele redenen, kunt u het rendement van recordsets te beperken tot kleine brokken, in tegenstelling tot het retourneren van grote recordsets die duur kunnen zijn, zowel in termen van bandbreedte, RAM, en de tijd op de server, evenals de economie voor de gebruiker. Onze methodologie:

  • wat en waarom? Wat is Python paginering in PostgreSQL? Waarom zouden we onze Postgres data willen pagineren met Python en in welke omstandigheden?
  • hoe? Hoe pagineren we PostgreSQL resultaten met Python? Wat zijn de snelste, makkelijkste manieren om dit te doen voor kleinere datasets en wat zijn de robuustere en efficiëntere manieren voor grote databases en / of situaties waarin server-of clientbronnen beperkt zijn en nauwkeurigheid voorop staat?

Prerequisites

  • SQL: inzicht in de basisprincipes van het schrijven van SQL voor Postgres. We gebruiken dBeaver vanwege het gemak en het aantal functies.
  • SELECT: we maken ruim gebruik van het” SELECT ” commando in dit artikel om een Postgres database te bevragen met een Python applicatie.
  • Tutorial over naamgevingsconventies laat zien waarom u uw variabelen, kolomnamen, tabelnamen, enz. wilt voorvoegen. zoals je ziet gedaan in dit artikel. Bijvoorbeeld, het benoemen van “tvariable” met het” t ” voorvoegsel om het te definiëren als een “text” (string) object en “tbl_” voor tabelnamen om deze objecten duidelijk te onderscheiden als tabellen. De tutorial gaat een beetje dieper, ook, praten over hoe variabelen te noemen op basis van een hiërarchie systeem.

Maak een Postgres-tabel om

te pagineren stel dat we een forum of content management system (CMS) in Python en Postgres aan het maken zijn en we willen een tabel voor het opslaan van forumberichten.

1
2
3
4
5
6
7
8
9
10
maak tafel publiek.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)
);
unieke INDEX aanmaken page_contents_id_idx in het openbaar.page_inhoud met btree (id);

nu we een PostgreSQL tabel hebben, vullen we die met gegevens, zodat we iets hebben om mee te testen:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO
page_contents
(
id_author
, t_title
)
SELECTEER
RANDOM()*100::INTEGER + 1 ALS id_author
, array_to_string(array(SELECT substr(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’,((willekeurig()*(36-1)+1)::geheel GETAL),1) VAN generate_series(1,32)),”) ALS t_title;
VAN
generate_Series(1,10000);

Overzicht: We plaatsen 10000 records in onze “page_contents” tabel. Voor elk record vullen we het veld “id_author” met een willekeurig getal tussen 1 en 100, inclusief die twee getallen. We vullen ook het veld “t_title” met een willekeurige tekenreeks van 32 tekens uit het alfabet.

Opmerking: Als u een grotere dataset nodig hebt, verhoog dan alleen de “10000” die u hierboven ziet naar een groter aantal.

analyse

  • invoegen: gebruikt PostgreSQL ‘ s “random” functie om een willekeurig getal tussen 0 en 99 te genereren, cast het als een geheel getal (“::integer”), en voeg dan ” 1 “toe, zodat het resultaat tussen (inclusief) 1 en 100 ligt en van voldoende gegevenstype is om in de kolom” id_author ” te plaatsen.
  • array_to_string: we selecteren willekeurig letters 32 keer (“generate_series (1,32)”) uit een lange tekenreeks van 36 tekens van A tot Z en 0 tot 9 om een tekenreeks te maken die wordt ingevoegd in het veld” t_title”.
  • generate_Series (1,10000): dit vertelt de Postgres SQL-engine om 10.000 rijen in de tabel te genereren.

nu we een grote dataset hebben om mee te werken, laten we eens kijken naar twee verschillende methoden voor het ophalen van gegevens uit deze tabel met behulp van Python.

Python paginering van Postgres snelle en vuile methode

de minst efficiënte maar meest gebruikte is meestal een object of bibliotheek die – onder – Gebruik maakt van “limit-offset” paginering. Voor situaties waarin uw database klein of middelgroot is, moet dit prima werken, met weinig nadelen, indien van toepassing. Later, wanneer we gebruik maken van een meer robuuste methode, zullen we praten over de verschillen.

Ten eerste, de voordelen van elke pagineringsmethode:

  • de eerste pagina van uw gegevens wordt sneller geladen. Voor de gebruiker perceptie van hoe snel uw app is, dit is van onschatbare waarde!
  • snellere weergaven van grote datasets.
  • potentieel voor inkapseling van business logic (afhankelijk van hoe u het doet).
  • de client ontvangt minder gegevens per keer, wat super nuttig kan zijn, zo niet een vereiste in bepaalde omgevingen.

volgende, de methoden die we zullen gebruiken omvatten total, limit, offset en skip.

laten we nu eens kijken naar enkele code:

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 += “VAN page_contents”
s += “ORDER BY id”
s += “LIMIET” + records_per_page
s += “OFFSET” + offset
# resulterende “LIMIET” een deel van de query: “LIMIT 10”
# resulterende “OFFSET” een deel van de query: “OFFSET 20”

analyse

  • page_current: voor testdoeleinden stellen we onze huidige pagina in op 3.
  • records_per_page: we willen slechts 10 records per pagina retourneren.
  • offset: Dit is de parameter die Postgres vertelt hoe ver hij in de tabel moet” springen”. “Sla zoveel platen over.”
  • s: maakt een query string aan om naar PostgreSQL te sturen voor uitvoering.

het grootste probleem met de bovenstaande” limit offset “methode van paginering is dat” under the covers “Postgres is het optellen van het totale aantal records, het tellen van pagina’ s op basis van dat aantal, en het scannen van de index om te tellen hoeveel rijen. Met de eerste paar pagina ‘ s, Deze kosten misschien niet evident. Maar met grotere recordsets en zelfs middelgrote degenen, hoe hoger u verhogen “page_current”, hoe langzamer dingen zal krijgen. Gelukkig, er zijn vele manieren waarop we kunnen doen paginering van Postgres gegevens.

een betere methode: seek en keyset paginering

een oplossing die een aantal nadelen van het gebruik van de bovenstaande methode verlicht, is het doorgeven van de unieke ID van de laatste record op de vorige pagina.

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 niet langer nodig!
limit_optional_offset = (page_current – 1) * records_per_page
s = “”
s += ” SELECT *”
s += “VAN page_contents”
s += “WHERE id >” & id_page_previous
s += “ORDER BY id”
s += “LIMIET” + records_per_page
# s += “OFFSET” + offset
# resulterende “LIMIET” een deel van de query: “LIMIET 10”

Kennisgeving met deze methode, hoeven we niet meer in het OFFSET-commando. Door de “WHERE” – clausule toe te voegen om PostgreSQL te vertellen alle eerdere records weg te laten, zodat het weet waar te beginnen met het tonen van records, blijven we LIMIT gebruiken om te Commando hoeveel records moeten worden geretourneerd.

hoewel er zeker efficiëntere methoden zijn om een Postgres-tabel te pagineren, levert de bovenstaande methode enorme voordelen op in snelheid en efficiëntie. Een zeer belangrijke overweging is: “id” moet worden geïndexeerd.

conclusie

In deze tutorial hebben we twee methoden geleerd voor het gebruik van Python voor het pagineren van Postgres data. We begonnen met het maken van een PostgreSQL-tabel, en voegden 10.000 records toe aan de tabel, dus we hadden iets om op te testen, en we vergeleken twee methoden van paginering in Python, en tenslotte analyseerden we de voor-en nadelen van beide methoden. Tijdens dit proces maakten we gebruik van PostgreSQL ‘ s “random” functie, Python string concatenation voor het bouwen van SQL te sturen naar Postgres, en variabele creatie.

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.