Inserir arquivos CSV no banco de dados

Uma das formas de inserir o conteúdo de um arquivo CSV (ou vários) em uma tabela do banco de dados é usando o “adaptador” Psycopg do PostgreSQL no Python. Outra forma é usando o comando “psql” diretamente no terminal Linux. Além disso, é possível inserir linha por linha ou tudo de uma só vez, de apenas um ou de vários arquivos. Serão usados dois métodos: o primeiro usando python e lendo cada um dos arquivos linha por linha (com apensa uma conexão); o segundo, usando psql e inserindo o arquivo CSV diretamente (com apensa uma inserção).

Veja a estrutura da tabela criada para os exemplos:

Nesse primeiro exemplo, as queries são escritas em um arquivo .sql, depois executadas em apenas uma conexão com o banco de dados. A primeira query tem o objetivo de limpar o banco de dados, mantendo sua estrutura (TRUNCATE), mas também está comentada a opção para excluir somente as linhas que interessam (DELETE).

Cada query de inserção na tabela (INSERT) é montada a partir de uma linha de um arquivo dentre vários. Ou seja, primeiro o comando “glob” lista todos os nomes de arquivos da pasta “output” que tem o padrão “Out_*.csv”; depois, cada arquivo é lido linha por linha (exceto a primeira, que é do cabeçalho).

Uma desvantagem desse método é a quantidade grande de inserções realizadas na base de dados.

Já no segundo exemplo, é feita uma inserção de um arquivo CSV mais rápida e limpa usando a estrutura “COPY … FROM” do SQL. Como não há necessidade de ler o arquivo linha por linha, é possível fazer tudo com apenas uma linha de comando diretamente no terminal, usando o programa “psql”.

Como são vários arquivos contendo um cabeçalho e as linhas com os dados, primeiro será criado um arquivo temporário com o cabeçalho e todas as outras linhas (exceto a primeira de cada arquivo):

O comando do SQL para inserir diretamente um arquivo CSV em uma tabela tem a seguinte sintaxe:

No entanto, se não for executado pelo super usuário, pode aparecer o seguinte erro:

Seguindo a dica, é possível jogar a saída de um programa de visualização de arquivos (o cat, por exemplo) para a entrada (stdin) do comando psql usando um redirecionador de fluxo (o pipe). O comando recebe como parâmetros o endereço da base de dados (-H), o usuário (-U), o nome da tabela (-W), uma indicação de que não tem senha para acessar o banco e o comando (-c):

Note que o comando mudou um pouco se comparado à versão original. Primeiro, foi incluída uma query para apagar o conteúdo da tabela. Segundo, foi alterada a entrada da query para receber a stdin em vez do arquivo, que também indica que contém um cabeçalho, que os campos nulos estão vazios (isso evita erros do tipo “invalid input syntax for type timestamp”) e que tudo está delimitado por vírgula.

Como foram definidas as colunas “place” e “date” como chaves primárias, caso tenha algum registro duplicado, não será realizada NENHUMA mudança.