Pagination Python de Postgres

Introduction

Cet article est un tutoriel sur la pagination Python des données Postgres. Pour de nombreuses raisons, vous pouvez limiter le retour des ensembles d’enregistrements à de petits morceaux, par opposition au retour de grands ensembles d’enregistrements qui peuvent être coûteux à la fois en termes de bande passante, de RAM et de temps sur le serveur, ainsi que d’économie pour l’utilisateur. Notre méthodologie:

  • Quoi et pourquoi ? Qu’est-ce que la pagination Python dans PostgreSQL ? Pourquoi voudrions-nous paginer nos données Postgres avec Python et dans quelles circonstances?
  • Comment? Comment paginer les résultats PostgreSQL avec Python ? Quels sont les moyens les plus rapides et les plus simples de le faire pour les petits ensembles de données et quels sont les moyens les plus robustes et les plus efficaces pour les grandes bases de données et / ou les situations où les ressources du serveur ou du client sont limitées et où la précision est primordiale?

Prérequis

  • SQL: Comprendre les bases de l’écriture SQL pour Postgres. Nous utilisons dBeaver en raison de sa facilité et de son nombre de fonctionnalités.
  • SELECT : Nous utilisons la commande « SELECT » dans cet article pour interroger une base de données Postgres avec une application Python.
  • Tutoriel sur les conventions de nommage montrant pourquoi vous pouvez préfixer vos variables, noms de colonnes, noms de tables, etc. comme vous le voyez dans cet article. Par exemple, nommez « tvariable » avec le préfixe « t » pour le définir comme un objet « texte » (chaîne) et « tbl_ » avant les noms de table afin de distinguer clairement ces objets en tant que tables. Le tutoriel va également un peu plus loin en expliquant comment nommer des variables en fonction d’un système de hiérarchie.

Créez une table Postgres pour paginer

Disons que nous créons un forum ou un système de gestion de contenu (CMS) en Python et Postgres et que nous voulons une table pour stocker les messages du forum.

1
2
3
4
5
6
7
8
9
10
CRÉER UNE TABLE publique.page_contents(
id serial NOT NULL,
id_session int4 NULL PAR DÉFAUT 0,
id_author int4 NULL PAR DÉFAUT 0,
t_title VARCHAR(256) NULL,
t_contents text NULL,
d_created DATE NULL PAR DÉFAUT now(),
CONTRAINTE page_contents_pkey CLÉ PRIMAIRE (id)
);
CRÉER UN INDEX UNIQUE page_contents_id_idx SUR public.page_contents UTILISANT btree(id);

Maintenant que nous avons une table PostgreSQL, nous allons la remplir de données, nous avons donc quelque chose à tester:

1
2
3
4
5
6
7
8
9
10
11
DANS CE CAS, il EST POSSIBLE D’INSÉRER DANS
page_contents
(
id_author
, t_title
)
SELECT
RANDOM() *100::INTEGER +1 COMME id_author
, array_to_string(array(SELECT substr(‘ ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, ((aléatoire()*(36-1)+1):: IL EST POSSIBLE D’UTILISER LE nom DE LA série DE génération (1,32), « ) COMME t_title;
DE
Série de génération(1,32)), « ) COMME t_title;
DE
Série de génération(1,10000);

Vue d’ensemble: Nous insérons 10000 enregistrements dans notre table « page_contents ». Pour chaque enregistrement, nous remplissons le champ « id_author » avec un nombre aléatoire compris entre 1 et 100, y compris ces deux nombres. Nous remplissons également le champ « t_title » avec une chaîne aléatoire de 32 caractères de l’alphabet.

Remarque: Si vous avez besoin d’un ensemble de données plus grand, augmentez simplement le « 10000 » que vous voyez ci-dessus à un plus grand nombre.

Analyse

  • INSERT: Utilisé la fonction « random » de PostgreSQL pour générer un nombre aléatoire compris entre 0 et 99, le convertir en entier (« ::entier »), puis ajoutez « 1 », de sorte que le résultat soit compris entre (inclus) 1 et 100 et soit de type de données suffisant pour être placé dans la colonne « id_author ».
  • array_to_string: Nous sélectionnons aléatoirement des lettres 32 fois (« generate_series(1,32) ») à partir d’une chaîne de 36 caractères de A à Z et de 0 à 9 afin de construire une chaîne qui est insérée dans le champ « t_title ».
  • generate_Series(1,10000) : Indique au moteur SQL Postgres de générer 10 000 lignes dans la table.

Maintenant que nous avons un grand ensemble de données avec lequel travailler, examinons deux méthodes distinctes pour récupérer des données de cette table en utilisant Python.

Pagination Python de la méthode rapide et sale Postgres

Le moins efficace mais le plus souvent utilisé est généralement un objet ou une bibliothèque qui -en dessous– utilise une pagination « limit–offset ». Pour les situations où votre base de données est de petite ou moyenne taille, cela devrait fonctionner correctement, avec peu d’inconvénients, le cas échéant. Plus tard, lorsque nous utiliserons une méthode plus robuste, nous parlerons des différences.

Tout d’abord, les avantages de toute méthode de pagination:

  • La première page de vos données se chargera plus rapidement. Pour la perception de l’utilisateur de la rapidité de votre application, c’est inestimable!
  • Vues plus rapides de grands ensembles de données.
  • Potentiel d’encapsulation de logique métier (dépend de la façon dont vous le faites).
  • Le client reçoit moins de données à la fois, ce qui peut être super utile, sinon une exigence dans certains environnements.

Ensuite, les méthodes que nous utiliserons incluent total, limit, offset et skip.

Maintenant regardons un peu de 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+= »FROM page_contents »
s+= »ORDER BY id »
s+= »LIMIT » + records_per_page
s += « OFFSET » + offset
# partie « LIMITE » résultante de la requête: « LIMITE 10 »
# partie « OFFSET » résultante de la requête:  » DÉCALAGE 20″

Analyse

  • page_current: À des fins de test, nous configurons notre page actuelle pour qu’elle soit 3.
  • records_per_page: Nous voulons renvoyer seulement 10 enregistrements par page.
  • offset: C’est le paramètre qui indique à Postgres à quelle distance « sauter » dans la table. Essentiellement, « Sautez ces nombreux enregistrements. »
  • s : Crée une chaîne de requête à envoyer à PostgreSQL pour exécution.

Le plus gros problème avec la méthode de pagination « décalage limite » ci-dessus est que « sous les couvertures » Postgres additionne le nombre total d’enregistrements, compte les pages en fonction de ce nombre et analyse l’index pour compter le nombre de lignes. Avec les premières pages, ce coût peut ne pas être évident. Mais avec des ensembles d’enregistrements plus grands et même de taille moyenne, plus vous augmentez « page_current », plus les choses seront lentes. Heureusement, il existe de nombreuses façons de paginer les données Postgres.

Une meilleure méthode: pagination de recherche et de jeu de clés

Une solution qui atténue certains des inconvénients associés à l’utilisation de la méthode ci-dessus consiste à transmettre l’identifiant unique du dernier enregistrement de la page précédente.

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 n’est plus nécessaire !
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
# partie « LIMITE » résultante de la requête: « LIMIT 10 »

Remarquez, avec cette méthode, nous n’avons plus besoin de la commande OFFSET. En ajoutant la clause « WHERE » pour dire à PostgreSQL TM de laisser de côté tous les enregistrements précédents, afin qu’il sache par où commencer à afficher les enregistrements, nous continuons à utiliser LIMIT pour commander le nombre d’enregistrements à renvoyer.

Bien qu’il existe sûrement des méthodes plus efficaces pour paginer une table Postgres, la méthode ci-dessus récolte des avantages massifs en termes de rapidité et d’efficacité. Une considération très importante est: « id » doit être indexé.

Conclusion

Dans ce tutoriel, nous avons appris deux méthodes d’utilisation de Python pour la pagination des données Postgres. Nous avons commencé par créer une table PostgreSQL, puis ajouté 10 000 enregistrements à la table, nous avions donc quelque chose sur lequel baser le test, puis nous avons comparé deux méthodes de pagination en Python, et enfin, nous avons analysé les avantages et les inconvénients des deux méthodes. Au cours de ce processus, nous avons utilisé la fonction « aléatoire » de PostgreSQL, la concaténation de chaînes Python pour la construction de SQL à envoyer à Postgres et la création de variables.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.