Python-Paginierung von Postgres

Einführung

Dieser Artikel ist ein Tutorial zur Python-Paginierung von Postgres-Daten. Aus vielen Gründen möchten Sie möglicherweise die Rückgabe von Recordsets auf kleine Blöcke beschränken, im Gegensatz zur Rückgabe großer Recordsets, die sowohl in Bezug auf Bandbreite, RAM und Zeit auf dem Server als auch in Bezug auf die Wirtschaftlichkeit für den Benutzer teuer sein können. Unsere Methodik:

  • Was und warum? Was ist Python-Paginierung in PostgreSQL? Warum sollten wir unsere Postgres-Daten mit Python paginieren wollen und unter welchen Umständen?
  • Wie? Wie paginieren wir PostgreSQL-Ergebnisse mit Python? Was sind die schnellsten und einfachsten Möglichkeiten, dies für kleinere Datensätze zu tun, und was sind die robusteren und effizienteren Möglichkeiten für große Datenbanken und / oder Situationen, in denen Server- oder Client-Ressourcen begrenzt sind und Genauigkeit von größter Bedeutung ist?

Voraussetzungen

  • SQL: Grundlagen zum Schreiben von SQL für Postgres. Wir verwenden DBeaver wegen seiner Leichtigkeit und Anzahl der Funktionen.
  • SELECT: Wir verwenden den Befehl „SELECT“ in diesem Artikel großzügig, um eine Postgres-Datenbank mit einer Python-Anwendung abzufragen.
  • Tutorial zu Namenskonventionen, das zeigt, warum Sie Ihren Variablen, Spaltennamen, Tabellennamen usw. ein Präfix voranstellen möchten. wie Sie in diesem Artikel sehen. Benennen Sie beispielsweise „tvariable“ mit dem Präfix „t“, um es als „Text“ -Objekt (Zeichenfolge) und „tbl_“ vor Tabellennamen zu definieren, um diese Objekte eindeutig als Tabellen zu unterscheiden. Das Tutorial geht auch etwas tiefer und spricht darüber, wie Variablen basierend auf einem Hierarchiesystem benannt werden.

Erstellen Sie eine Postgres-Tabelle zum Paginieren

Angenommen, wir erstellen ein Forum oder Content Management System (CMS) in Python und Postgres und möchten eine Tabelle zum Speichern von Forenbeiträgen.

1
2
3
4
5
6
7
8
9
10
TABELLE public ERSTELLEN.page_contents (
id serial NICHT 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 jetzt (),
EINSCHRÄNKUNG page_contents_pkey PRIMÄRSCHLÜSSEL (id)
) ;
ERSTELLEN SIE EINEN EINDEUTIGEN INDEX page_contents_id_idx FÜR public.page_contents MIT btree (id);

Jetzt, da wir eine PostgreSQL-Tabelle haben, füllen wir sie mit Daten, damit wir etwas testen können:

1
2
3
4
5
6
7
8
9
10
11
EINFÜGEN IN
page_contents
(
id_author
, t_title
)
SELECT
RANDOM()*100::INTEGER + 1 ALS id_author
, array_to_string(array(SELECT substr(‚ ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘,((zufällig()*(36-1)+1):: INTEGER),1) FROM generate_series(1,32)),“) AS t_title;
FROM
generate_Series(1,10000);

Übersicht: Wir fügen 10000 Datensätze in unsere Tabelle „page_contents“ ein. Für jeden Datensatz füllen wir das Feld „id_author“ mit einer Zufallszahl zwischen 1 und 100, einschließlich dieser beiden Zahlen. Wir füllen auch das Feld „t_title“ mit einer zufälligen Zeichenfolge von 32 Zeichen aus dem Alphabet.

Hinweis: Wenn Sie einen größeren Datensatz benötigen, erhöhen Sie einfach die „10000“, die Sie oben sehen, auf eine größere Zahl.

Analysis

  • INSERT: Verwendete die „random“ -Funktion von PostgreSQL, um eine Zufallszahl zwischen 0 und 99 zu generieren und sie als Ganzzahl umzuwandeln („::integer“), und fügen Sie dann „1“ hinzu, so dass das Ergebnis zwischen (einschließlich) 1 und 100 liegt und einen ausreichenden Datentyp hat, um in die Spalte „id_author“ eingefügt zu werden.
  • array_to_string: Wir wählen zufällig 32 Buchstaben („generate_series(1,32)“) aus einer 36 Zeichen langen Zeichenfolge von A bis Z und 0 bis 9 aus, um eine Zeichenfolge zu erstellen, die in das Feld „t_title“ eingefügt wird.
  • generate_Series(1,10000): Hiermit wird die Postgres SQL-Engine angewiesen, 10.000 Zeilen in der Tabelle zu generieren.

Nun, da wir einen großen Datensatz haben, mit dem wir arbeiten können, schauen wir uns zwei verschiedene Methoden zum Abrufen von Daten aus dieser Tabelle mit Python an.

Python-Paginierung von Postgres schnelle und schmutzige Methode

Die am wenigsten effiziente, aber am häufigsten verwendete Methode ist normalerweise ein Objekt oder eine Bibliothek, die – darunter – die Paginierung „Limit-Offset“ verwendet. In Situationen, in denen Ihre Datenbank klein oder mittelgroß ist, sollte dies gut funktionieren, mit wenigen Nachteilen, falls vorhanden. Später, wenn wir eine robustere Methode verwenden, werden wir über die Unterschiede sprechen.

Erstens die Vorteile jeder Paginierungsmethode:

  • Die erste Seite Ihrer Daten wird schneller geladen. Für die Wahrnehmung der Benutzer, wie schnell Ihre App ist, ist dies von unschätzbarem Wert!
  • Schnellere Ansichten großer Datensätze.
  • Potenzial für die Geschäftslogikkapselung (hängt davon ab, wie Sie dies tun).
  • Der Client empfängt weniger Daten gleichzeitig, was sehr nützlich sein kann, wenn nicht sogar eine Anforderung in bestimmten Umgebungen.

Als nächstes verwenden wir die Methoden total, limit, offset und skip .

Schauen wir uns nun einen Code an:

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 += “ WÄHLEN SIE *“
s += “ VON page_contents“
s += “ ORDER BY id“
s += “ LIMIT “ + records_per_page
s += “ OFFSET “ + offset
# resultierender „LIMIT“ -Teil der Abfrage: „LIMIT 10“
# resultierender „OFFSET“ -Teil der Abfrage: „OFFSET 20“

Analyse

  • page_current: Zu Testzwecken haben wir unsere aktuelle Seite auf 3 eingestellt.
  • records_per_page: Wir möchten nur 10 Datensätze pro Seite zurückgeben.
  • offset: Dies ist der Parameter, der Postgres mitteilt, wie weit in der Tabelle „gesprungen“ werden soll. Im Wesentlichen, „Überspringen Sie so viele Datensätze.“
  • s: Erstellt eine Abfragezeichenfolge, die zur Ausführung an PostgreSQL gesendet werden soll.

Das größte Problem bei der obigen Paginierungsmethode „Limit Offset“ besteht darin, dass Postgres „unter der Decke“ die Gesamtmenge der Datensätze addiert, die Seiten basierend auf dieser Nummer zählt und den Index scannt, um zu zählen, wie viele Zeilen. Bei den ersten Seiten sind diese Kosten möglicherweise nicht ersichtlich. Aber bei größeren und sogar mittelgroßen Datensätzen wird es umso langsamer, je höher Sie „page_current“ erhöhen. Glücklicherweise gibt es viele Möglichkeiten, Postgres-Daten zu paginieren.

Eine bessere Methode: seek and keyset pagination

Eine Lösung, die einige der mit der obigen Methode verbundenen Nachteile verringert, besteht darin, die eindeutige ID des letzten Datensatzes auf der vorherigen Seite zu übergeben.

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 wird nicht mehr benötigt!
limit_optional_offset = (page_current – 1) * records_per_page
s = „“
s += “ WÄHLEN SIE *“
s += “ VON page_contents“
s += “ WO id > “ & id_page_previous
s += “ ORDER BY id“
s += “ LIMIT “ + records_per_page
# s += “ OFFSET “ + offset
# resultierendes „LIMIT“ Teil der Abfrage: „LIMIT 10“

Beachten Sie, dass wir mit dieser Methode den Befehl OFFSET nicht mehr benötigen. Indem wir die „WHERE“ -Klausel hinzufügen, um PostgreSQL anzuweisen, alle vorherigen Datensätze auszulassen, damit es weiß, wo es anfangen soll, Datensätze anzuzeigen, verwenden wir weiterhin LIMIT , um zu befehlen, wie viele Datensätze zurückgegeben werden sollen.

Obwohl es sicherlich effizientere Methoden zum Paginieren einer Postgres-Tabelle gibt, bietet die obige Methode enorme Vorteile in Bezug auf Geschwindigkeit und Effizienz. Eine sehr wichtige Überlegung ist: „id“ muss indiziert sein.

Fazit

In diesem Tutorial haben wir zwei Methoden zur Verwendung von Python für die Paginierung von Postgres-Daten gelernt. Wir begannen mit der Erstellung einer PostgreSQL-Tabelle, fügten der Tabelle dann 10.000 Datensätze hinzu, sodass wir etwas zum Testen hatten, und verglichen dann zwei Methoden der Paginierung in Python und analysierten schließlich die Vor- und Nachteile beider Methoden. Während dieses Prozesses haben wir die „Random“ -Funktion von PostgreSQL, die Python-String-Verkettung zum Erstellen von SQL zum Senden an Postgres und die Variablenerstellung verwendet.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.