Paginação Python de Postgres

introdução

este artigo é um tutorial sobre paginação Python de dados Postgres. Por muitos motivos, você pode limitar o retorno de conjuntos de registros a pequenos pedaços, em vez de retornar grandes conjuntos de registros que podem ser caros em termos de largura de banda, RAM e tempo no servidor, bem como economia para o usuário. Nossa metodologia:

  • o quê e porquê? O que é paginação Python no PostgreSQL? Por que gostaríamos de paginar nossos dados Postgres com Python e em que circunstâncias?
  • como? Como paginamos os resultados do PostgreSQL com Python? Quais são as maneiras mais rápidas e fáceis de fazer isso para conjuntos de dados menores e quais são as maneiras mais robustas e eficientes para grandes bancos de dados e/ou situações em que os recursos do servidor ou do cliente são limitados e a precisão é fundamental?

pré-requisitos

  • SQL: Entendendo os fundamentos da escrita SQL para Postgres. Usamos o dBeaver devido à sua facilidade e número de recursos.
  • SELECT: fazemos uso liberal do comando” SELECT ” neste artigo para consultar um banco de dados Postgres com um aplicativo Python.
  • Tutorial sobre convenções de nomenclatura mostrando por que você pode querer prefixar suas variáveis, nomes de colunas, nomes de tabelas, etc. como você vê feito neste artigo. Por exemplo, nomear “tvariable” com o prefixo ” t “para defini-lo como um objeto” text “(string) e” tbl_ ” antes dos nomes das tabelas, a fim de distinguir claramente esses objetos como tabelas. O tutorial vai um pouco mais fundo, também, falando sobre como nomear variáveis com base em um sistema de hierarquia.

Criar uma tabela Postgres para paginar

Vamos dizer que estamos criando um fórum ou sistema de gerenciamento de conteúdo (CMS) em Python e Postgres e queremos uma tabela para armazenar as mensagens do fórum.

1
2
3
4
5
6
7
8
9
10
CRIAR TABELA 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 texto NULL,
d_created DATA PADRÃO NULL, agora(),
RESTRIÇÃO page_contents_pkey PRIMARY KEY (id)
);
CREATE UNIQUE INDEX page_contents_id_idx EM público.page_contents usando btree (id);

agora que temos uma tabela PostgreSQL, vamos preenchê – la com dados, então temos algo para testar:

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO
page_contents
(
id_author
, t_title
)
SELECIONE
ALEATÓRIO()*100::INTEGER + 1 COMO id_author
, array_to_string(array(SELECT substr(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’,((random()*(36-1)+1)::INTEIRO),1) a PARTIR de generate_series(1,32)),””) COMO t_title;
DE
generate_Series(1,10000);

Visão geral: Estamos inserindo 10000 registros em nosso “page_contents” da tabela. Para cada registro, estamos preenchendo o campo “id_author” com um número aleatório entre 1 e 100, inclusive desses dois números. Também estamos preenchendo o campo “t_title” com uma string aleatória de 32 caracteres do alfabeto.

Nota: Se você precisar de um conjunto de dados maior, apenas aumente o “10000” que você vê acima para um número maior.

Analysis

  • INSERT: usou a função “aleatória” do PostgreSQL para gerar um número aleatório entre 0 e 99, lançá-lo como um inteiro (“::inteiro”) e, em seguida, adicione “1”, de modo que o resultado esteja entre (inclusive) 1 e 100 e seja de tipo de dados suficiente para colocar na coluna” id_author”.
  • array_to_string: estamos selecionando aleatoriamente letras 32 vezes (“generate_series(1,32)”) de uma string longa de 36 caracteres de A A Z e 0 a 9 para construir uma string inserida no campo “t_title”.
  • generate_Series(1.10000): isso informa ao Postgres SQL engine para gerar 10.000 linhas na tabela.

agora que temos um grande conjunto de dados para trabalhar, vejamos dois métodos distintos para recuperar dados desta tabela usando Python.

paginação Python do método Postgres quick and dirty

o menos eficiente, mas mais frequentemente usado, é geralmente um objeto ou biblioteca que – abaixo – usa a paginação “limit-offset”. Para situações em que seu banco de dados é pequeno ou médio em tamanho, isso deve funcionar bem, com algumas desvantagens, se houver. Mais tarde, quando utilizarmos um método mais robusto, falaremos sobre as diferenças.

Primeiro, os benefícios de qualquer método de paginação:

  • A primeira página de seus dados serão carregados mais rapidamente. Para a percepção do usuário de quão rápido seu aplicativo é, isso é inestimável!
  • visualizações mais rápidas de grandes conjuntos de dados.
  • potencial para encapsulamento de lógica de negócios (depende de como você faz isso).
  • o cliente está recebendo menos dados de cada vez, o que pode ser super útil, se não um requisito em determinados ambientes.

em seguida, os métodos que usaremos incluem total, limit, offset e skip.

agora vamos dar uma olhada em algum 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 += “a PARTIR de page_contents”
s += “ORDER BY id”
s += “LIMITE” + records_per_page
s += “OFFSET” + offset
# resultante de “LIMITE” parte da consulta: “O LIMITE de 10”
# resultante “OFFSET” parte da consulta: “OFFSET 20”

Análise

  • page_current: Para fins de teste, montamos nossa página atual para ser de 3.
  • records_per_page: queremos retornar apenas 10 registros por página.
  • offset: este é o parâmetro que diz ao Postgres até que ponto “pular” na tabela. Essencialmente, ” pule esses muitos registros.”
  • s: cria uma string de consulta para enviar ao PostgreSQL para execução.

o maior problema com o método de paginação “limit offset” acima é que “under the covers” Postgres está somando a quantidade total de registros, contando páginas com base nesse número e digitalizando o índice para contar quantas linhas. Com as primeiras páginas, esse custo pode não ser evidente. Mas com conjuntos de registros maiores e até de tamanho médio, quanto maior você aumentar “page_current”, mais lentas serão as coisas. Felizmente, existem muitas maneiras de fazer a paginação de dados Postgres.

um método melhor: seek and keyset pagination

uma solução que alivia alguns dos contras associados ao uso do método acima é passar o ID exclusivo do último registro na 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 não mais necessários!
limit_optional_offset = (page_current – 1) * records_per_page
s = “”
s += ” SELECT *”
s += “a PARTIR de page_contents”
s += “WHERE id >” & id_page_previous
s += “ORDER BY id”
s += “LIMITE” + records_per_page
# s += “OFFSET” + offset
# resultante de “LIMITE” parte da consulta: “o LIMITE de 10”

Observe que, com este método, não é mais necessário o DESLOCAMENTO de comando. Ao adicionar a cláusula “WHERE” para dizer ao PostgreSQL para deixar de fora todos os registros anteriores, para que ele saiba por onde começar a mostrar registros, continuamos usando LIMIT para comandar quantos registros retornar.

embora certamente existam métodos mais eficientes para paginar uma tabela Postgres, o método acima colhe benefícios maciços em velocidade e eficiência. Uma consideração muito importante é: “id” deve ser indexado.

conclusão

neste tutorial, aprendemos dois métodos para usar Python para paginação de dados Postgres. Começamos criando uma tabela PostgreSQL, depois adicionamos 10.000 registros à tabela, então tínhamos algo em que basear o teste e, em seguida, comparamos dois métodos de paginação em Python e, finalmente, analisamos os prós e contras de ambos os métodos. Durante este processo, fizemos uso da função “aleatória” do PostgreSQL, concatenação de string Python para construir SQL para enviar para Postgres e criação de variáveis.

Deixe uma resposta

O seu endereço de email não será publicado.