caderno_sqlite.py

# -*- coding: utf-8 -*-
"""caderno_sqlite.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1WuzlT6ej8MSXRWlynYjt6ws5c2SdHZNB

Para trabalhar com bancos SQL podemos carregar a extensão de SQL e digitar comando SQL diretamente na interface do caderno.
"""

# Commented out IPython magic to ensure Python compatibility.
# %load_ext sql

"""Para indicar que um bloco de código é puramente SQL e não python, usaremos o comando %%sql. INicialmente vamos criar um arquivo de banco SQLite."""

# Commented out IPython magic to ensure Python compatibility.
# %%sql
# sqlite:///test.db

"""Como exemplo, vamos criar uma tabela que tem dados do cliente, como por exemplo, número de compras por ano, e o valor médio das compras."""

# Commented out IPython magic to ensure Python compatibility.
# %%sql
# create table cliente(id int, quant_compras int, valor_medio real);
# insert into cliente values (1,8,20.0),(2,50,300.0);
# select * from cliente;

"""Vamos apagar os registros que criamos."""

# Commented out IPython magic to ensure Python compatibility.
# %%sql
# delete from cliente;
# select * from cliente;

"""Para acessar o banco de dados por meio do Python, precisamos importar a biblioteca para o tipo de banco de dados que estamos usando. Nesse caso SQLite3."""

import sqlite3

"""No próximo trecho de código vamos conectar no banco acrescentar dados sintéticos, comitar a operação e depois desconectar."""

import numpy as np
# cria conexão com o banco
conn = sqlite3.connect('test.db')

#Vamos definir um valor médio de quantidade de compras e valor médio das
#compras, e criar 20 clientes com esse perfil, usando uma variação aleatória
quant=10
valor=20
for i in range(1,21):
  q=np.random.normal(quant, 5, size=(1, 1))[0,0]
  q=max(0,round(q))#arredenda e evita negativos
  v=np.random.normal(valor, 10, size=(1, 1))[0,0]
  v=max(0,v) #evita valores negativos
  conn.execute("INSERT INTO cliente (id,quant_compras,valor_medio) VALUES (?, ?, ?)",(i,q,v))
quant=100
valor=20
for i in range(21,41):
  q=np.random.normal(quant, 5, size=(1, 1))[0,0]
  q=max(0,round(q))#arredenda e evita negativos
  v=np.random.normal(valor, 10, size=(1, 1))[0,0]
  v=max(0,v) #evita valores negativos
  conn.execute("INSERT INTO cliente (id,quant_compras,valor_medio) VALUES (?, ?, ?)",(i,q,v))
quant=15
valor=250
for i in range(41,61):
  q=np.random.normal(quant, 5, size=(1, 1))[0,0]
  q=max(0,round(q))#arredenda e evita negativos
  v=np.random.normal(valor, 10, size=(1, 1))[0,0]
  v=max(0,v) #evita valores negativos
  conn.execute("INSERT INTO cliente (id,quant_compras,valor_medio) VALUES (?, ?, ?)",(i,q,v))

conn.commit()
conn.close();

# Commented out IPython magic to ensure Python compatibility.
# %%sql
# select * from cliente;

"""Para obter os dados conectamos e fazemos uma consulta select, que retorna um cursos para os dados. Com o cursor podemos acessar cada linha retornada."""

conn = sqlite3.connect('test.db')
#faz a consulta
cursor = conn.execute("select * from cliente")
#cria uma matriz com todos os resultados (fetchall)
M=np.array(cursor.fetchall())
conn.close();

"""A seguir vamos fazer um gráfico de dispersão.
M[:,1] são todas as linhas da segunda coluna, que é a coluna da quantidade de compras no ano.
M[:,2] são os valores médios de compra conforme cada cliente.
Ou seja, o gráfico de dispersão relaciona espacialmente as quantidades de compra e os valores.
"""

import matplotlib.pyplot as plt
plt.plot(M[:,1],M[:,2],'.')
plt.show()

"""A partir dos dados obtidos, podemos então classificá-los utilizando um algoritmo de aprendizado não-supervisionado, ou seja, aprendizado onde não é necessário ter dados rotulados. Um exemplo é o algoritmo K-Means, que busca encontrar centros de massa que separem os dados da melhor forma possível. Para esse algoritmo é necessário informar K=número de grupos que queremos, e partir desse parâmetros o algoritmo fará a separação. Exemplo: https://www.w3schools.com/python/python_ml_k-means.asp

"""

#importa a bliblioteca de k-means
from sklearn.cluster import KMeans
#cria o objeto kmeans considerando 3 grupos (clusters)
kmeans = KMeans(n_clusters=3)
#faz o aprendizado não-supervisionado
#M[:,1:] seleciona todas as linhas as colunas 1 em diante (1:),
#sendo que a coluna 1 é a segunda coluna (quantidade de compras)
#e a coluna 2 é o valor médio das compras
kmeans.fit(M[:,1:])
#vamos juntar na tabela R os dados da tabela M com os rótulos do Kmeans
R=np.concatenate((M,kmeans.labels_.reshape((M.shape[0],1))),axis=1)
print(R)

#o resultado do kmean é armazenado no atributo labels, que são os rótulos
#atribuídos a cada uma das linhas da tabela M, identificado a qual grupo(cluster)
#cada linha pertence.
#A partir disso é feito um gráfico de dispersão
#em que a cor do elemento corresponde ao seu rótulo, c=kmeans.labels_
plt.scatter(R[:,1], R[:,2], c=R[:,3])
plt.show()