Gerenciando bancos de dados PostgreSQL em Python com psycopg2

Criando uma tabela no PostgreSQL

É hora de você criar sua primeira tabela no banco de dados datacamp_courses. Queremos que você crie 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 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 exclusivamente 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 ou 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 você 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ê pode encontrar 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()

Esse é 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 Criando banco de dados PostgreSQL para você.

Executando consultas básicas do PostgreSQL em Python

A tabela datacamp_courses está pronta; agora é hora de você 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:

SELECIONAR 

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 que você leia 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, você pode querer 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 você ainda tem muito a aprender. Vamos ver algumas consultas mais avançadas.

ORDENAR POR

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')

GROUP BY

Você pode querer 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)

JUNTAR

Até o momento, trabalhamos apenas com a tabela datacamp_course. No entanto, 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 das uniões de SQL é infinito, o que faz com que esse seja 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 você 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 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 DataLab

Na DataCamp, trabalhamos duro 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 DataLab, um notebook baseado em nuvem projetado especificamente para a ciência de dados. Você pode pensar no DataCamp DataLab como uma mistura de Jupyter Notebook e Google Docs, que permite aos usuários escrever códigos, analisar dados individual ou coletivamente e compartilhar insights de dados.

Conectar o DataLab a bancos de dados PostgreSQL, bem como a outros bancos de dados SQL, é extremamente fácil. O DataLab permite que você se conecte a fontes de dados de forma segura. Ao mesmo tempo, você pode continuar 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.

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

Você está curioso para saber mais sobre o DataCamp DataLab? 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 ajudar você 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
• Funções para manipulação de dados no curso PostgreSQL