Impaginazione Python di Postgres

Introduzione

Questo articolo è un tutorial sull’impaginazione Python dei dati Postgres. Per molte ragioni, è possibile limitare il ritorno dei recordset a blocchi di piccole dimensioni, anziché restituire recordset di grandi dimensioni che possono essere costosi sia in termini di larghezza di banda, RAM e tempo sul server, sia in termini di economia per l’utente. La nostra metodologia:

  • Cosa e perché? Cos’è l’impaginazione Python in PostgreSQL? Perché dovremmo voler impaginare i nostri dati Postgres con Python e in quali circostanze?
  • Come? Come impaginare i risultati di PostgreSQL con Python? Quali sono i modi più rapidi e semplici per farlo per set di dati più piccoli e quali sono i modi più robusti ed efficienti per database di grandi dimensioni e/o situazioni in cui le risorse del server o del client sono limitate e la precisione è fondamentale?

Prerequisiti

  • SQL: comprendere le basi della scrittura di SQL per Postgres. Usiamo dBeaver a causa della sua facilità e il numero di caratteristiche.
  • SELECT: Facciamo un uso liberale del comando “SELECT” in questo articolo per interrogare un database Postgres con un’applicazione Python.
  • Tutorial sulle convenzioni di denominazione che mostrano perché potresti voler prefisso le variabili, i nomi delle colonne, i nomi delle tabelle, ecc. come si vede fatto in questo articolo. Ad esempio, nominando “tvariable” con il prefisso “t” per definirlo come oggetto “text” (string) e “tbl_” prima dei nomi delle tabelle per distinguere chiaramente quegli oggetti come tabelle. Il tutorial va anche un po ‘ più in profondità, parlando di come nominare le variabili in base a un sistema di gerarchia.

Crea una tabella Postgres per impaginare

Diciamo che stiamo creando un forum o content management system (CMS) in Python e Postgres e vogliamo una tabella per la memorizzazione dei post del forum.

1
2
3
4
5
6
7
8
9
10
CREA TABELLA pubblica.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 testo NULL,
d_created DATA NULL DEFAULT now(),
VINCOLO page_contents_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX page_contents_id_idx pubblici.page_contents USANDO btree (id);

Ora che abbiamo una tabella PostgreSQL, la riempiremo di dati, quindi abbiamo qualcosa con cui testare:

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

Panoramica: Siamo inserimento di 10000 record nel nostro “page_contents” tabella. Per ogni record, stiamo riempiendo il campo “id_author” con un numero casuale compreso tra 1 e 100, inclusi questi due numeri. Stiamo anche riempiendo il campo “t_title” con una stringa casuale di 32 caratteri dell’alfabeto.

Nota: se hai bisogno di un set di dati più grande, aumenta semplicemente il “10000” che vedi sopra a un numero più grande.

Analysis

  • INSERT: ha utilizzato la funzione “random” di PostgreSQL per generare un numero casuale compreso tra 0 e 99, lanciandolo come un numero intero (“::integer”), quindi aggiungere “1”, in modo che il risultato sia compreso tra (incluso) 1 e 100 e sia di tipo di dati sufficiente da inserire nella colonna” id_author”.
  • array_to_string: Stiamo selezionando casualmente le lettere 32 volte (“generate_series(1,32)”) da una stringa di 36 caratteri dalla A alla Z e da 0 a 9 per costruire una stringa che viene inserita nel campo “t_title”.
  • generate_Series(1,10000): indica al motore SQL Postgres di generare 10.000 righe nella tabella.

Ora che abbiamo un set di dati di grandi dimensioni con cui lavorare, diamo un’occhiata a due metodi distinti per il recupero dei dati da questa tabella utilizzando Python.

Impaginazione Python di Postgres quick and dirty method

Il meno efficiente ma più spesso utilizzato è di solito un oggetto o una libreria che-sotto – utilizza l’impaginazione “limit – offset”. Per le situazioni in cui il database è di piccole o medie dimensioni, questo dovrebbe funzionare bene, con alcuni inconvenienti, se presenti. Più tardi, quando utilizziamo un metodo più robusto, parleremo delle differenze.

In primo luogo, i vantaggi di qualsiasi metodo di impaginazione:

  • La prima pagina dei tuoi dati verrà caricata più velocemente. Per la percezione dell’utente di quanto velocemente la tua app è, questo è inestimabile!
  • Viste più veloci di set di dati di grandi dimensioni.
  • Potenziale per l’incapsulamento della logica di business (dipende da come lo fai).
  • Il client riceve meno dati alla volta, il che può essere super utile, se non un requisito in determinati ambienti.

Successivamente, i metodi che useremo includono total, limit, offset e skip.

Ora diamo un’occhiata a qualche codice:

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 += “DA page_contents”
s += “ORDER BY id”
s += “LIMITE” + records_per_page
s += “OFFSET” + offset
# conseguente “LIMITARE” la parte di query: “Il LIMITE di 10”
# conseguente “OFFSET” la parte di query: “SCOSTAMENTO 20”

Analisi

  • page_current: Per scopi di test, abbiamo impostato la nostra pagina corrente per essere 3.
  • records_per_page: vogliamo restituire solo 10 record per pagina.
  • offset: Questo è il parametro che indica a Postgres fino a che punto “saltare” nella tabella. In sostanza, ” Saltare questo molti record.”
  • s: Crea una stringa di query da inviare a PostgreSQL per l’esecuzione.

Il problema più grande con il metodo di impaginazione “limit offset” di cui sopra è che “under the covers” Postgres sta sommando la quantità totale di record, contando le pagine in base a quel numero e scansionando l’indice per contare quante righe. Con le prime pagine, questo costo potrebbe non essere evidente. Ma con recordset più grandi e anche di medie dimensioni, maggiore è l’aumento di “page_current”, le cose più lente otterranno. Fortunatamente, ci sono molti modi in cui possiamo eseguire l’impaginazione dei dati Postgres.

Un metodo migliore: seek and keyset pagination

Una soluzione che allevia alcuni degli svantaggi associati all’utilizzo del metodo precedente consiste nel passare l’ID univoco dell’ultimo record nella pagina precedente.

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 non più necessari!
limit_optional_offset = (page_current – 1) * records_per_page
s = “”
s += ” SELECT *”
s += “DA page_contents”
s += “WHERE id >” & id_page_previous
s += “ORDER BY id”
s += “LIMITE” + records_per_page
# s += “OFFSET” + offset
# conseguente “LIMITARE” la parte di query: “LIMITE 10”

Avviso, con questo metodo, non è più necessario il comando OFFSET. Aggiungendo la clausola” WHERE ” per dire a PostgreSQL di lasciare fuori tutti i record precedenti, quindi sa da dove iniziare a mostrare i record, continuiamo a usare LIMIT per comandare quanti record restituire.

Mentre ci sono sicuramente metodi più efficienti per impaginare una tabella Postgres, il metodo di cui sopra raccoglie enormi benefici in termini di velocità ed efficienza. Una considerazione molto importante è: “id” deve essere indicizzato.

Conclusione

In questo tutorial abbiamo imparato due metodi per l’utilizzo di Python per l’impaginazione dei dati Postgres. Abbiamo iniziato creando una tabella PostgreSQL, quindi abbiamo aggiunto 10.000 record alla tabella, quindi abbiamo avuto qualcosa su cui basare il test, e poi abbiamo confrontato due metodi di impaginazione in Python, e infine, abbiamo analizzato i pro ei contro di entrambi i metodi. Durante questo processo, abbiamo fatto uso della funzione “casuale” di PostgreSQL, della concatenazione delle stringhe Python per la creazione di SQL da inviare a Postgres e della creazione di variabili.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.