Paginación Python de Postgres

Introducción

Este artículo es un tutorial sobre Paginación Python de datos Postgres. Por muchas razones, es posible que desee limitar el retorno de conjuntos de registros a trozos pequeños, en lugar de devolver conjuntos de registros grandes que pueden ser costosos tanto en términos de ancho de banda, RAM y tiempo en el servidor, como en economía para el usuario. Nuestra metodología:

  • ¿Qué y por qué? ¿Qué es la paginación de Python en PostgreSQL? ¿Por qué querríamos paginar nuestros datos Postgres con Python y en qué circunstancias?
  • ¿Cómo? ¿Cómo paginamos los resultados de PostgreSQL con Python? ¿Cuáles son las formas más rápidas y fáciles de hacer esto para conjuntos de datos más pequeños y cuáles son las formas más robustas y eficientes para grandes bases de datos y/o situaciones en las que los recursos del servidor o del cliente son limitados y la precisión es primordial?

Requisitos previos

  • SQL: Comprender los conceptos básicos de escribir SQL para Postgres. Utilizamos DBeaver debido a su facilidad y número de características.
  • SELECCIONAR: Hacemos un uso liberal del comando «SELECCIONAR» en este artículo para consultar una base de datos Postgres con una aplicación Python.
  • Tutorial sobre las convenciones de nomenclatura que muestra por qué es posible que desee anteponer sus variables, nombres de columnas,nombres de tablas, etc. como ves en este artículo. Por ejemplo, nombrar «tvariable» con el prefijo «t» para definirlo como un objeto «text» (cadena) y «tbl_» antes de los nombres de tabla para distinguir claramente esos objetos como tablas. El tutorial va un poco más profundo, también, hablando de cómo nombrar variables basadas en un sistema de jerarquía.

Crear una tabla Postgres para paginar

Digamos que estamos creando un foro o sistema de gestión de contenido (CMS) en Python y Postgres y queremos una tabla para almacenar publicaciones en el foro.

1
2
3
4
5
6
7
8
9
10
CREAR TABLA público.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 CLAVE PRIMARIA (id)
);
CREAR ÍNDICE ÚNICO page_contents_id_idx EN público.page_contents USANDO btree (id);

Ahora que tenemos una tabla PostgreSQL, la rellenaremos con datos, por lo que tenemos algo con lo que probar:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO
page_contents
(
id_author
, t_title
)
SELECT
RANDOM()*100::INTEGER + 1 COMO id_author
, array_to_string(array(SELECT substr(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789″, ((aleatorio()*(36-1)+1)::ENTERO),1) DE generate_series (1,32)),») COMO t_title;
DE
generate_Series(1,10000);

Descripción general: Estamos insertando 10000 registros en nuestra tabla «page_contents». Para cada registro, estamos rellenando el campo» id_author » con un número aleatorio entre 1 y 100, incluidos esos dos números. También estamos rellenando el campo «t_title» con una cadena aleatoria de 32 caracteres del alfabeto.

Nota: Si necesita un conjunto de datos más grande, simplemente aumente el «10000» que ve arriba a un número mayor.

Analysis

  • INSERT: Se utilizó la función «random» de PostgreSQL para generar un número aleatorio entre 0 y 99, convertirlo como un entero («::integer»), y luego agregue «1», de modo que el resultado esté entre (inclusive) 1 y 100 y sea del tipo de datos suficiente para colocarlo en la columna» id_author».
  • array_to_string: Estamos seleccionando aleatoriamente letras 32 veces («generate_series (1,32)») de una cadena de 36 caracteres de la A a la Z y de 0 a 9 para construir una cadena que se inserta en el campo» t_title».
  • generate_Series (1,10000): Le indica al motor SQL de Postgres que genere 10,000 filas en la tabla.

Ahora que tenemos un conjunto de datos grande con el que trabajar, veamos dos métodos distintos para recuperar datos de esta tabla utilizando Python.

Paginación en Python del método rápido y sucio de Postgres

El menos eficiente, pero más utilizado, suele ser un objeto o biblioteca que, por debajo, usa paginación de «desplazamiento límite». Para situaciones en las que su base de datos es pequeña o mediana, esto debería funcionar bien, con pocos inconvenientes, si los hubiera. Más tarde, cuando utilicemos un método más robusto, hablaremos de las diferencias.

En primer lugar, los beneficios de cualquier método de paginación:

  • La primera página de tus datos se cargará más rápido. Para la percepción del usuario de lo rápido que es su aplicación, ¡esto es invaluable!
  • Vistas más rápidas de conjuntos de datos grandes.
  • Potencial de encapsulación de lógica empresarial (depende de cómo lo haga).
  • El cliente está recibiendo menos datos a la vez, lo que puede ser súper útil, si no es un requisito en ciertos entornos.

A continuación, los métodos que usaremos incluyen total, límite, desplazamiento y salto.

Ahora veamos un poco de código:

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 += «DESPLAZAMIENTO» + desplazamiento
# parte «LÍMITE» resultante de la consulta: «LÍMITE 10»
# parte «DESPLAZAMIENTO» resultante de la consulta: «DESPLAZAMIENTO 20»

Análisis

  • page_current: Para fines de prueba, configuramos nuestra página actual para que sea 3.
  • records_per_page: Queremos devolver solo 10 registros por página.
  • desplazamiento: Este es el parámetro que indica a Postgres hasta qué punto debe «saltar» en la tabla. Esencialmente, » Omita estos muchos registros.»
  • s: Crea una cadena de consulta para enviar a PostgreSQL para su ejecución.

El mayor problema con el método de paginación de «desplazamiento de límite» anterior es que» debajo de las portadas » Postgres suma la cantidad total de registros, cuenta las páginas en función de ese número y escanea el índice para contar cuántas filas. Con las primeras páginas, este costo puede no ser evidente. Pero con conjuntos de registros más grandes e incluso de tamaño mediano, cuanto más aumente «page_current», las cosas se volverán más lentas. Afortunadamente, hay muchas maneras en que podemos hacer la paginación de los datos de Postgres.

Un mejor método: búsqueda y paginación de conjuntos de claves

Una solución que alivia algunos de los inconvenientes asociados con el uso del método anterior es pasar el ID único del último registro en la página anterior.

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 ya no es necesaria!
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
# parte «LIMIT» resultante de la consulta: «LIMIT 10»

Observe que, con este método, ya no necesitamos el comando OFFSET. Al agregar la cláusula «WHERE» para indicarle a PostgreSQL que omita todos los registros anteriores, para que sepa por dónde empezar a mostrar registros, seguimos usando LIMIT para ordenar cuántos registros devolver.

Aunque seguramente hay métodos más eficientes para paginar una tabla Postgres, el método anterior cosecha enormes beneficios en velocidad y eficiencia. Una consideración muy importante es:» id » debe ser indexado.

Conclusión

En este tutorial aprendimos dos métodos para usar Python para la paginación de datos Postgres. Comenzamos creando una tabla PostgreSQL, luego agregamos 10,000 registros a la tabla, así que tuvimos algo en lo que basar la prueba, y luego comparamos dos métodos de paginación en Python, y finalmente, analizamos los pros y los contras de ambos métodos. Durante este proceso, hicimos uso de la función «aleatoria» de PostgreSQL, la concatenación de cadenas de Python para crear SQL para enviar a Postgres y la creación de variables.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.