Guia para iniciantes em PostgreSQL

Conseguir consultar bancos de dados relacionais é uma habilidade indispensável para qualquer cientista de dados. SQL, ou Structured Query Language, permite fazer isso de forma muito eficiente. Com SQL, você não só consegue fazer perguntas relevantes aos dados, como também manipulá-los de várias maneiras. Sem bancos de dados, praticamente nenhum aplicativo do mundo real é possível. Por isso, conhecer bancos de dados e saber trabalhar com eles é parte essencial do kit de ferramentas de um cientista de dados.

Curiosidade: SQL também é chamado de SE-QU-EL. Isso tem um motivo histórico — o nome inicial de SQL era Simple English Query Language.

Geralmente, bancos de dados relacionais se parecem com o seguinte:

As relações também são chamadas de tabelas. Existem várias formas de representar bancos de dados. Esta é apenas uma delas — e a mais popular.

Este tutorial apresenta as quatro operações mais comuns feitas com SQL: Create, Read, Update e Delete. Juntas, elas são conhecidas como CRUD. Em qualquer aplicação que envolva interação do usuário, essas quatro operações sempre aparecem.

Você vai usar o PostgreSQL como sistema de gerenciamento de banco de dados relacional. O PostgreSQL é leve e gratuito. Neste tutorial, você vai:

• Instalar e colocar o PostgreSQL para rodar
• Conectar-se a um banco PostgreSQL
• Criar, ler, atualizar e excluir tabelas nesse banco
• Executar SQL no Jupyter Notebook
• Executar SQL em Python

Vamos começar.

Colocando o PostgreSQL para rodar

O PostgreSQL é um SGBDR open source e leve, amplamente adotado pelo mercado. Saiba mais no site oficial.

Para começar a escrever e executar consultas no PostgreSQL, você precisa instalá-lo na sua máquina. A instalação é bem simples. Estes dois vídeos curtos mostram como baixar e instalar o PostgreSQL em um Windows 7 de 32 bits:

• Baixar o PostgreSQL
• Instalar o PostgreSQL

Observação: durante a instalação, anote a senha e o número da porta que você definir.

Depois de instalar o PostgreSQL com sucesso, abra o pgAdmin. O pgAdmin é uma ferramenta prática que vem com a instalação do PostgreSQL e permite executar tarefas rotineiras de banco de dados por meio de uma interface gráfica. A interface do pgAdmin se parece com isto:

Ao abrir o pgAdmin, você verá um servidor listado com o nome "PostgreSQL 9.4 (localhost:5432)":

Observação: sua versão (e, portanto, a porta 5432) pode ser diferente.

Conecte-se ao servidor digitando a senha que você definiu durante a instalação. Para referência: https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Depois de se conectar com sucesso ao servidor local, você verá uma interface semelhante a esta:

Sua primeira tarefa será criar um banco de dados. Para isso, clique com o botão direito na guia Databases e selecione New Database no menu. Vamos criar um banco chamado DataCamp_Courses. Com o banco criado, você pode seguir para as próximas seções deste tutorial.

Operações CRUD no PostgreSQL

Criar uma tabela a partir de uma especificação -

Para operar em um banco, você precisa de uma tabela. Então vamos criar uma tabela simples (também chamada de relação) chamada datacamp_courses com a seguinte especificação (schema):

A especificação traz algumas informações sobre as colunas da tabela:

• A chave primária deve ser course_id (note que apenas ela está em negrito) e seu tipo de dado deve ser inteiro. Uma chave primária é uma restrição que força os valores da coluna a serem não nulos e únicos, permitindo identificar de forma exclusiva um registro (ou conjunto de registros) na tabela.
• O restante das informações na especificação agora deve ser fácil de interpretar.

Para criar uma tabela, clique com o botão direito no novo banco DataCamp_Courses e selecione CREATE Script. Você deve ver algo assim:

Agora, execute a seguinte consulta:

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

Para executar, selecione a consulta e clique no botão de execução na barra de menus:

A saída deve ser:

A estrutura geral de uma consulta de criação de tabela no PostgreSQL é:

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)

Nós não especificamos nenhum table_constraint ao criar a tabela — e tudo bem por enquanto. O restante é bem legível, com exceção da palavra-chave SERIAL. Em PostgreSQL, SERIAL cria uma coluna de auto-incremento. Por padrão, gera valores inteiros. O SERIAL nos livra do trabalho de lembrar o último valor de chave primária inserido/atualizado e é uma boa prática para chaves primárias. Você pode saber mais sobre SERIAL aqui.

Inserindo alguns registros na nova tabela

Nesta etapa, você vai inserir alguns registros na tabela. Cada registro deve conter:

• O nome do curso
• O nome do instrutor
• O tópico do curso

Os valores da coluna course_id serão gerenciados pelo próprio PostgreSQL. A estrutura geral de um insert no PostgreSQL é:

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);

Vamos inserir alguns registros:

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

Perceba que você não informou as chaves primárias explicitamente. Já já você verá o efeito disso.

Ao executar as duas consultas acima, você deve ver o seguinte resultado caso as inserções tenham sido bem-sucedidas:

Query returned successfully: one row affected, 11 ms execution time.

Lendo/visualizando os dados da tabela

Provavelmente você fará isso com frequência na sua jornada em ciência de dados. Por enquanto, vamos ver como está a tabela datacamp_courses.

Essa é a chamada consulta de seleção (select). A estrutura genérica é:

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

Vamos selecionar todas as colunas da tabela datacamp_courses

SELECT * FROM datacamp_courses;

E você obterá:

Note agora as chaves primárias. Se quiser ver apenas os nomes dos cursos, faça:

SELECT course_name from datacamp_courses;

E você obterá:

Você pode especificar quantas colunas quiser ver no resultado — desde que existam na tabela. Se você executar select course_name, number_particpants from datacamp_courses;, terá um erro, pois a coluna number_particpants não existe. Agora vamos ver como atualizar um registro específico na tabela.

Atualizando um registro na tabela

A estrutura geral de uma atualização (update) em SQL é:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;

Você vai atualizar o registro em que course_instructor = "Chester Ismay" e definir course_name como "Joining Data in SQL". Em seguida, vai verificar se o registro foi atualizado. A consulta é:

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';

Vamos verificar se a atualização teve o efeito desejado executando um select:

Como você pode ver, a atualização funcionou exatamente como esperado. Agora, vamos ver como excluir um registro da tabela.

Excluindo um registro na tabela

A estrutura geral de um delete em SQL é:

DELETE FROM table
WHERE condition;

Você vai excluir o registro em que course_name = "Deep Learning in Python" e depois verificar se ele foi removido. Seguindo a estrutura, a consulta é:

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';

Lembre-se: as palavras-chave não diferenciam maiúsculas de minúsculas em SQL, mas os dados diferenciam. Por isso, você vê uma mistura de maiúsculas e minúsculas nas consultas.

Vamos confirmar se o registro desejado foi excluído:

E sim, o registro foi removido.

As estruturas genéricas de consultas citadas no tutorial foram referenciadas do PostgreSQL Tutorial.

Agora você já sabe fazer consultas CRUD básicas em SQL. Se você usa bastante Jupyter Notebooks e pensou que seria ótimo executar essas consultas direto do Notebook, a próxima seção é para você.

SQL + Jupyter Notebooks

Para executar consultas SQL a partir do Jupyter Notebook, o primeiro passo é instalar o pacote ipython-sql.

Se não estiver instalado, use:

pip install ipython-sql

Depois disso, carregue a extensão sql no seu Notebook executando:

%load_ext sql

O próximo passo é se conectar a um banco PostgreSQL. Vamos usar o banco que você criou: DataCamp_Courses.

Para se conectar a um banco já criado no seu sistema, você precisa instruir o Python sobre o dialeto — em outras palavras, dizer que é um banco PostgreSQL. Para isso, você vai precisar do psycopg2, que pode ser instalado com:

pip install psycopg2

Depois de instalar o psycopg, conecte ao banco usando:

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses

'Connected: postgres@DataCamp_Courses'

Note o uso de %sql. Isso é um comando mágico que permite executar instruções SQL a partir do Jupyter. O que vem após %sql é a URL de conexão, onde você especifica:

• Dialeto (postgres)
• Nome de usuário (postgres)
• Senha (postgres)
• Endereço do servidor (localhost)
• Número da porta (5432)
• Nome do banco (DaaCamp_Courses)

Agora você pode fazer no Jupyter tudo o que fez na interface do pgAdmin. Vamos começar criando a tabela _datacampcourses com o mesmo schema.

Mas antes, será preciso eliminar a tabela, já que o SQL não permite duas tabelas com o mesmo nome. Você pode eliminar uma tabela assim:

%sql DROP table datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

A tabela _datacampcourses foi removida do PostgreSQL e, portanto, você pode criar uma nova com esse nome.

%%sql
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
);

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Note o uso de %sql e %%sql aqui. Para executar uma única linha, use %sql. Para executar várias consultas de uma vez, use %%sql.

Vamos inserir alguns registros:

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] 

Visualize a tabela para confirmar que as inserções foram feitas como esperado:

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Vamos manter o ritmo. Em seguida, você vai atualizar um registro na tabela:

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

Preste bastante atenção ao trabalhar com strings em SQL. Diferente de muitas linguagens de programação, valores de string precisam estar entre aspas simples.

Agora, vamos verificar se a atualização teve o efeito desejado:

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Agora vamos excluir um registro e conferir:

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Agora você já tem uma visão clara de como executar operações CRUD no PostgreSQL e como fazer isso via Jupyter Notebook. Se você domina Python e quer acessar seu banco direto pelo seu código, também dá. A próxima seção mostra como.

Introdução ao SQLAlchemy e como combiná-lo com comandos mágicos de SQL

Para esta seção, você vai precisar do pacote SQLAlchemy. Ele já vem, em geral, com a distribuição Anaconda. Você também pode instalá-lo via pip. Com ele instalado, faça a importação assim:

import sqlalchemy

Para interagir com seus bancos usando o SQLAlchemy, você precisa criar um engine para o SGBDR onde seus bancos estão. No seu caso, é o PostgreSQL. O SQLAlchemy permite criar um engine do SGBDR com uma única chamada a create_engine(), que recebe a URL de conexão — a mesma que você já viu.

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Mostra os nomes das tabelas presentes no banco

['datacamp_courses']

Você vê a tabela _datacampcourses, confirmando que o engine foi criado com sucesso. Vamos executar um select simples para ver os registros da tabela _datacampcourses e guardar o resultado em um DataFrame do pandas.

Use o método read_sql() (fornecido pelo pandas), que recebe uma string SQL e um engine.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()

Você também pode combinar o comando mágico %sql dentro do seu código Python.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Você chegou longe!

Parabéns — manda um high five para você mesmo! Neste tutorial, você não só começou com o PostgreSQL, como também viu várias formas de executar consultas SQL. Como próximo passo, assista a este webinar do David Robinson (Chief Data Scientist na DataCamp), onde ele mostra como usa SQL no dia a dia. Confira também nosso tutorial Como instalar o PostgreSQL no Windows e no Mac OS X. Se você quer aprimorar suas habilidades em SQL, estes são ótimos recursos para começar:

• Introdução ao SQL para ciência de dados
• SQL no Kaggle Learn
• Joining Data in SQL