Uso do PostgreSQL em Python

Os dados estão no centro de quase todos os aplicativos digitais que você possa imaginar. De aplicativos móveis e jogos de computador a ferramentas bancárias e veículos autônomos, tudo é baseado em dados. Onde todos esses dados são armazenados? A resposta é: bancos de dados. 

Um banco de dados é uma coleção organizada de dados estruturados, normalmente armazenados eletronicamente em um sistema de computador. Apesar do recente desenvolvimento de novos tipos de bancos de dados (projetados para lidar com o aumento do volume e da variabilidade dos dados), uma quantidade considerável de dados em todo o mundo ainda é armazenada nos chamados bancos de dados relacionais. Os bancos de dados relacionais armazenam dados como coleções de tabelas predefinidas com linhas e colunas que são conectadas por meio de um ou mais relacionamentos. 

Ser capaz de manipular e extrair dados desses bancos de dados é uma habilidade essencial no setor de dados e uma habilidade com demanda crescente. A maneira padrão de criar e gerenciar bancos de dados é a SQL (Structured Query Language, Linguagem de Consulta Estruturada). O SQL é o bloco de construção de alguns dos bancos de dados relacionais mais populares do mercado, como PostgreSQL, Microsoft SQL Server, MySQL e SQLite

Neste tutorial, vamos nos concentrar nos bancos de dados baseados no PostgreSQL e em como você pode criar, conectar-se a eles e gerenciá-los usando Python. O conjunto SQL-Python é uma das habilidades obrigatórias que você deve dominar em sua jornada de ciência de dados. Para conectar o Python ao PostgreSQL, usaremos o pacote psycopg2, o adaptador de banco de dados PostgreSQL mais popular para Python. Pronto para o desafio? Vamos começar!

Entendendo o PostgreSQL

O PostgreSQL é um banco de dados relacional leve, gratuito e de código aberto. Devido à sua arquitetura comprovada, confiabilidade, integridade dos dados e integração suave com outras linguagens de programação populares, como Python e R, o PostgreSQL é extremamente bem aceito pelo setor, com empresas de todos os tamanhos e regiões que o utilizam. 

O PostgreSQL usa e amplia a linguagem SQL combinada com muitos recursos que armazenam e dimensionam com segurança as cargas de trabalho de dados mais complicadas. 

Para começar a usar o PostgreSQL, primeiro é necessário instalá-lo em seu computador. No site oficial do PostgreSQL, você pode encontrar vários pacotes e instaladores prontos para uso do PostgreSQL e um arquivo de código-fonte disponível para diferentes plataformas e casos de uso.

Para este tutorial, usaremos o Postgres App, um aplicativo simples e nativo do macOS que inclui todos os recursos necessários para começar a usar o PostgreSQL, mas lembre-se de que todos os outros pacotes disponíveis são igualmente válidos, e a sintaxe do PostgreSQL para executar consultas é a mesma em todos os pacotes.

Se você quiser saber mais sobre o PostgreSQL, consulte nosso Guia para iniciantes no PostgreSQL e o bom número de cursos de SQL.

Entendendo o psycopg2

Para se conectar a um banco de dados já criado em seu sistema ou na Internet, você terá de instruir o Python a detectá-lo. Em outras palavras, você terá que informar ao Python que o banco de dados de seu interesse é um banco de dados PostgreSQL. 

No Python, você tem várias opções que pode escolher. Nesse caso, usaremos o psycopg2, provavelmente o adaptador de banco de dados PostgreSQL mais popular para Python. O Psycopg2 requer alguns pré-requisitos para funcionar corretamente em seu computador. Depois de instalá-los (leia a documentação para obter mais informações), você pode instalar o psycopg2 como qualquer outro pacote Python:

pip install psycopg2

No entanto, se quiser usar o psycopg2 diretamente, você também pode instalar o psycopg2-binary, uma versão autônoma do pacote, que não requer um compilador ou bibliotecas externas. Essa é a instalação preferida para novos usuários.

pip install psycopg2-binary

Por fim, se você estiver usando o Python em um ambiente Conda, deverá instalar o psycopg2 usando o comando Instalação do Anaconda:

conda install -c anaconda psycopg2

Agora que você está pronto, vamos criar sua primeira conexão com a sessão do PostgreSQL com o psycopg2!

Conexão do Python ao PostgreSQL

Para usar o Python para interagir com um banco de dados PostgreSQL, precisamos fazer uma conexão. Isso é feito com a função psycopg2 connect(), que cria uma nova sessão de banco de dados e retorna uma nova instância de conexão.

Para este tutorial, vamos nos conectar a um banco de dados chamado "datacamp_courses" que está hospedado localmente. 

conn = psycopg2.connect(database = "datacamp_courses", 
                        user = "datacamp", 
                        host= 'localhost',
                        password = "postgresql_tutorial",
                        port = 5432)

Os parâmetros básicos de conexão necessários são:

• banco de dados. O nome do banco de dados.
• usuário. Nome de usuário necessário para autenticação.
• senha. Senha usada para autenticação.
• anfitrião. Endereço do servidor do banco de dados (no nosso caso, o banco de dados é hospedado localmente, mas pode ser um endereço IP).
• porto. Número da porta de conexão (o padrão é 5432 se não for fornecido).

Criação de uma tabela no PostgreSQL

É hora de criar sua primeira tabela no banco de dados "datacamp_courses". Queremos criar uma tabela com informações sobre alguns dos cursos do catálogo de cursos da DataCamp. A tabela tem o seguinte esquema: 

A especificação nos fornece várias informações sobre as colunas da tabela. Achave primária da tabela deve ser course_id (observe que apenas essa está em negrito) e seu tipo de dados deve ser um número inteiro. Uma chave primária é uma restrição que impõe que os valores da coluna sejam não nulos e exclusivos. Ele permite que você identifique de forma exclusiva uma instância específica ou um conjunto de instâncias presentes na tabela. 

As colunas restantes fornecem informações sobre o nome do curso, o nome da instrução do curso e o tópico do curso.

Antes de criar a tabela, é importante explicar como funciona a instância de conexão que você acabou de criar. Em essência, a conexão encapsula uma sessão de banco de dados e permite que você execute comandos e consultas SQL, como SELECT, INSERT, CREATE, UPDATE, OR DELETE, usando o método cursor(), e faça alterações persistentes usando o método commit() .

Depois de criar a instância do cursor, você pode enviar comandos para o banco de dados usando o método execute() e recuperar dados de uma tabela usando o método fetchone(), fetchmany()ou fetchall().

Por fim, é importante fechar o cursor e a conexão com o banco de dados sempre que terminar suas operações. Caso contrário, eles continuarão a manter os recursos do lado do servidor. Para isso, você pode usar ométodo close() .

Abaixo, você encontra o código para criar a tabela datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""CREATE TABLE datacamp_courses(
            course_id SERIAL PRIMARY KEY,
            course_name VARCHAR (50) UNIQUE NOT NULL,
            course_instructor VARCHAR (100) NOT NULL,
            topic VARCHAR (20) NOT NULL);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Este é um exemplo muito básico de como criar tabelas no PostgreSQL, mas as coisas podem ficar muito mais complexas. Se você quiser saber mais sobre como criar um banco de dados PostgreSQL e explorar a estrutura, os tipos de dados e como normalizar bancos de dados, confira nosso artigo Criação de banco de dados PostgreSQL curso.

Execução de consultas básicas do PostgreSQL em Python

A tabela datacamp_courses está pronta; agora é hora de usar o SQL para realizar algumas consultas básicas!

INSERIR 

Você deve ter notado que a tabela não tem valores até o momento. Para criar registros na tabela datacamp_courses, precisamos do comando INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

A tabela resultante tem a seguinte aparência:

SELECT 

Ler dados em bancos de dados SQL é provavelmente algo que você fará muito em sua jornada de ciência de dados. Isso geralmente é chamado de consulta SELECT. Por enquanto, vamos ver como a tabela datacamp_courses está se comportando.

Chamaremos o comando clássico SELECT * FROM database_name para ler todos os dados disponíveis na tabela. Em seguida, usaremos o método fetchall() para buscar todas as linhas disponíveis. Observe que o PostgreSQL cria automaticamente um índice numérico para a coluna course_id .

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

ATUALIZAÇÃO 

Os dados geralmente vêm com erros. Você deve ter notado na seção anterior que o tópico associado ao curso "Introdução ao SQL" é Julia. Depois de verificar as informações sobre o curso, descobrimos o erro. Precisamos alterá-lo e escrever "SQL" em vez disso. Isso pode ser feito com a instrução UPDATE, como segue:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

DELETE 

Por fim, talvez você queira excluir um dos registros da tabela. Por exemplo, vamos excluir o curso Introdução à estatística em R:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Consultas avançadas do PostgreSQL em Python

Na seção anterior, examinamos as consultas SQL mais básicas. Mas há muito a aprender. Vamos ver algumas consultas mais avançadas.

ORDER BY

Digamos que você queira classificar seu banco de dados pelo nome do instrutor. Você pode usar a declaração ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GRUPO POR

Talvez você queira executar algumas funções agregadas em diferentes grupos de dados. Por exemplo, você pode estar interessado em calcular o número de cursos dos diferentes instrutores de cursos. Você pode fazer esse tipo de operação com a função GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Até este ponto, trabalhamos apenas com a tabela datacamp_course. Mas você só começa a aproveitar todo o potencial dos bancos de dados relacionais, como o PostgreSQL, quando trabalha com várias tabelas ao mesmo tempo. 

A ferramenta mágica para combinar várias tabelas é a operação JOIN. Imagine que temos uma segunda tabela em nosso banco de dados chamada programming_languages que contém informações básicas sobre as principais linguagens de programação para ciência de dados, incluindo o nome, a posição no Índice TIOBE e o número de cursos sobre a linguagem de programação no Datacamp. A tabela tem a seguinte aparência:

Imagine que você queira mesclar as duas tabelas para obter as informações dos cursos, juntamente com a posição do idioma no índice TIOBE. Usaremos um INNER JOIN para obter apenas as informações das linguagens de programação que aparecem na tabela datacamp_course. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Esse é apenas um exemplo, mas o potencial do SQL JOINS é infinito, tornando-o um tópico de conhecimento obrigatório. Nosso curso, tutoriale folha de dicas sobre JOINS ajudarão você a começar.

Primeiros passos com o SQLAlchemy

O Psycopg2 é uma excelente ferramenta para se conectar a bancos de dados PostgreSQL. Mas e se você tiver que trabalhar com bancos de dados hospedados em diferentes bancos de dados SQL, como Oracle e MySQL? Nesses cenários, talvez seja melhor usar uma ferramenta que possa se conectar a diferentes tipos de bancos de dados SQL. Para isso, o SQLAlchemy é um ótimo candidato. 

Em palavras simples, o SQLAlchemy permite que os usuários conectem bancos de dados usando a linguagem Python, executem consultas SQL usando programação baseada em objetos e simplifiquem o fluxo de trabalho. 

Embora a sintaxe varie em comparação com o psycopg2, você pode fazer praticamente a mesma coisa. Abaixo, você encontra um exemplo de como criar um mecanismo para se conectar aos nossos conjuntos de dados datacamp_courses e ler todos os dados disponíveis na tabela datacap_course

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

O SQLAlchemy é outra ferramenta excelente que vale a pena aprender. Para ter uma ideia de como a ferramenta funciona, dê uma olhada no nosso Tutorial do SQLALchemy.  

Usando o PostgreSQL no DataCamp Workspace

Na DataCamp, trabalhamos arduamente para tornar a ciência de dados mais fácil e acessível a todos. Essa foi a razão por trás da criação do DataCamp Workspace, um notebook baseado em nuvem projetado especificamente para a ciência de dados. Você pode pensar no DataCamp Workspace como uma mistura entre o Jupyter Notebook e o Google Docs, que permite que os usuários escrevam códigos, analisem dados individual ou coletivamente e compartilhem insights sobre os dados.

Conectar o Workspace a bancos de dados PostgreSQL, bem como a outros bancos de dados SQL, é extremamente fácil. O Workspace permite que você se conecte a fontes de dados de forma segura, enquanto continua sua análise de dados com Python ou R. Você só precisa criar uma integração, seguindo um processo simples e sem código, no qual precisará inserir suas credenciais de conexão. 

A imagem abaixo mostra como criar uma integração com o PostgreSQL. 

Por fim, se você quiser experimentar a integração, mas não tiver um banco de dados PostgreSQL ao qual se conectar, basta usar um de nossos bancos de dados PostgreSQL de amostra disponíveis!

Curioso sobre o DataCamp Workspace? Confira este artigo para saber mais sobre esse notebook de última geração para ciência de dados.

Conclusão

Bom trabalho! Neste tutorial, você se familiarizou com o PostgreSQL e aprendeu a gerenciar bancos de dados sem sair do Python, usando o pacote psycopg2. Mas esse é apenas o começo da jornada. O SQL é uma das linguagens de programação mais usadas na ciência de dados. Também é bastante fácil de aprender, portanto, você não tem desculpa.  

Felizmente, o Datacamp tem tudo o que você precisa. Aqui estão alguns recursos que podem ajudá-lo a se tornar fluente em PostgreSQL e SQL em geral:

• Tutorial de introdução ao PostgreSQL para iniciantes
• Curso de criação de bancos de dados PostgreSQL
• Melhorando o desempenho das consultas no curso PostgreSQL
• Curso de gerenciamento de bancos de dados em SQL
• Curso de funções para manipulação de dados no PostgreSQL