<jupyter_start><jupyter_text>Coleta e Transformação de Dados de <NAME> | Desafio 2 - Bootcamp Cientista de Dados IGTI<jupyter_code># Importando pacotes:
import json
import requests
import pandas as pd
import numpy as np
import datetime as dt
import warnings
warnings.filterwarnings('ignore')
# Conectando com a API:
response = requests.get('https://api-coleta-dados.herokuapp.com/api/pessoas')
# Verificando se a conexão encontrou a página:
response.status_code
# Salvando o conteúdo em um objeto 'json':
api_content = json.loads(response.content)
# Verificando as 'features' do primeiro registro:
api_content[0]
# Convertendo o objeto tipo json para um df do Pandas para fazer o tratamento de dados:
content_df = pd.DataFrame.from_dict(api_content, orient = 'columns')
# Verificando os primeiros registros do df:
content_df.head()
# Verificando o tipo dos dados:
content_df.dtypes
# Atualizando as datas de nascimento sem ano declarado através das idades:
for i in range(0, len(content_df)):
    if len(content_df.data_nasc[i]) != 10:
        content_df.data_nasc[i] = content_df.data_nasc[i]+str(int(content_df.idade[i])-2020)
# Convertendo a coluna 'data_nasc' para o tipo data:
content_df['data_nasc'] = pd.to_datetime(content_df['data_nasc'], infer_datetime_format=True)
# Atualizando as idades pela data de nascimento (referência 31 de dezembro de 2020):
for i in range(0, len(content_df)):
    content_df.idade[i] = int(int(str(pd.Timestamp('2020-12-31') - content_df.data_nasc[i]).split(' ')[0])/365.2425)
# Transformando a data de nascimento para o tipo string:
content_df['data_nasc'] = content_df['data_nasc'].dt.strftime('%Y-%m-%d')
content_df.head()<jupyter_output><empty_output><jupyter_text>Ingestão de Dados em DB MySQL Para a ingestão de dados, será respeitado o esquema criado para o banco de dados representado pela imagem que segue:<jupyter_code># Criando variáveis numéricas que servirão de código id:
# Variável cor:
cor_array = content_df.cor.unique()
content_df['cor_id'] = 0
for i_cor in range(0, len(cor_array)):
    for i in range(0, len(content_df)):
        if content_df.cor[i] == cor_array[i_cor]:
            content_df.cor_id[i] = i_cor         
# Variável tiposanguineo:
tsang_array = content_df.tipo_sanguineo.unique()
content_df['tipo_sanguineo_id'] = 0
for i_tsang in range(0, len(tsang_array)):
    for i in range(0, len(content_df)):
        if content_df.tipo_sanguineo[i] == tsang_array[i_tsang]:
            content_df.tipo_sanguineo_id[i] = i_tsang
# Variável estado:
estado_array = content_df.estado.unique()
content_df['estado_id'] = 0
for i_est in range(0, len(estado_array)):
    for i in range(0, len(content_df)):
        if content_df.estado[i] == estado_array[i_est]:
            content_df.estado_id[i] = i_est
# Variável cidade:
cidade_array = content_df.cidade.unique()
content_df['cidade_id'] = 0
for i_cid in range(0, len(cidade_array)):
    for i in range(0, len(content_df)):
        if content_df.cidade[i] == cidade_array[i_cid]:
            content_df.cidade_id[i] = i_cid 
content_df.head()
# Importando pacote para conectar ao MySQL:
import mysql.connector

# Conectando ao db_desafio criado no MySQL:
mydb = mysql.connector.connect(
    host='localhost',
    user='root',
    password='<PASSWORD>',
    database='db_desafio')

# Criando o cursor:
mycursor = mydb.cursor()
# Verificando as tabelas criadas no banco de dados: 
mycursor.execute("SHOW TABLES")

for tables in mycursor:
    print(tables)
# Inserindo dados da tabela estado:
query_estado = "INSERT INTO tb_estado (id, sigla) VALUES (%s, %s)"
estado_id_array = content_df.estado_id.unique()

for i in range(0, len(estado_id_array)):
    data_estado = (int(estado_id_array[i]), estado_array[i])
    print(data_estado)
    mycursor.execute(query_estado, data_estado)
    
mydb.commit()
# Inserindo dados da tabela cor:
query_cor = "INSERT INTO tb_cor (id, cor) VALUES (%s, %s)"
cor_id_array = content_df.cor_id.unique()

for i in range(0, len(cor_id_array)):
    data_cor = (int(cor_id_array[i]), cor_array[i])
    print(data_cor)
    mycursor.execute(query_cor, data_cor)
    
mydb.commit()
# Inserindo dados da tabela tipo_sanguineo:
query_tsang = "INSERT INTO tb_tiposanguineo (id, tipo) VALUES (%s, %s)"
tsang_id_array = content_df.tipo_sanguineo_id.unique()

for i in range(0, len(tsang_id_array)):
    data_tsang = (int(tsang_id_array[i]), tsang_array[i])
    print(data_tsang)
    mycursor.execute(query_tsang, data_tsang)
    
mydb.commit()
# Criando um df para as cidades com seus respectivos id:
cidade_df = pd.DataFrame(cidade_array, columns = ['cidade'])
cidade_df['cidade_id'] = content_df.cidade_id.unique()
cidade_df.head()
# Incluindo estado_id referente a cada uma das cidades:
cidade_df['estado_id'] = 0
for cid_i in range(0, len(cidade_df)):
    ind = 0
    while content_df.cidade.iloc[ind] != cidade_df.cidade.iloc[cid_i]:
        ind += 1
    else:
        cidade_df.estado_id.iloc[cid_i] = content_df.estado_id.iloc[ind]
# Inserindo os dados na tabela tb_cidade:
query_cidade = "INSERT INTO tb_cidade (id, nome, id_estado) VALUES (%s, %s, %s)"

for i in range(0, len(cidade_df)):
    data_cidade = (int(cidade_df.cidade_id.iloc[i]), cidade_df.cidade.iloc[i], int(cidade_df.estado_id.iloc[i]))
    print(data_cidade)
    mycursor.execute(query_cidade, data_cidade)
    
mydb.commit()
# Transformando o caracter ',' em '.' na variável altura:
for i in range(0, len(content_df)):
    content_df.altura.iloc[i] = content_df.altura.iloc[i].replace(',', '.')
# Transformando a variável sexo para "M" e "F":
for i in range(0, len(content_df)):
    if content_df.sexo.iloc[i] == 'Masculino':
        content_df.sexo.iloc[i] = 'M'
    else:
        content_df.sexo.iloc[i] = 'F'
# Inserindo os dados na tabela tb_pessoa:
query_pessoa = "INSERT INTO tb_pessoa (nome, idade, data_nasc, sexo, signo, altura, peso, id_cidade, id_cor, id_tiposanguineo)\
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

for i in range(0, len(content_df)):
    data_pessoa = (content_df.nome.iloc[i], int(content_df.idade.iloc[i]), content_df.data_nasc.iloc[i], 
                   content_df.sexo.iloc[i], content_df.signo.iloc[i], float(content_df.altura.iloc[i]), 
                   float(content_df.peso.iloc[i]), int(content_df.cidade_id.iloc[i]), int(content_df.cor_id.iloc[i]), 
                   int(content_df.tipo_sanguineo_id.iloc[i]))
    print(data_pessoa)
    mycursor.execute(query_pessoa, data_pessoa)
    
mydb.commit()<jupyter_output>('<NAME>', 56, '1964-12-06', 'F', 'Gêmeos', 1.67, 50.0, 0, 0, 0)
('<NAME>', 27, '1993-10-12', 'M', 'Sagitário', 1.63, 110.0, 1, 1, 1)
('<NAME>', 29, '1991-09-21', 'M', 'Virgem', 1.86, 106.0, 2, 2, 2)
('<NAME>', 64, '1956-10-22', 'M', 'Libra', 1.87, 100.0, 3, 0, 3)
('<NAME>', 50, '1970-09-03', 'M', 'Peixes', 1.74, 83.0, 4, 0, 4)
('<NAME>', 19, '2001-12-04', 'M', 'Áries', 1.91, 64.0, 5, 3, 2)
('<NAME>', 71, '1949-01-23', 'F', 'Aquário', 1.54, 76.0, 1, 0, 5)
('<NAME>', 33, '1987-12-23', 'M', 'Capricórnio', 1.91, 54.0, 6, 4, 2)
('<NAME>', 52, '1968-10-20', 'F', 'Libra', 1.84, 59.0, 7, 0, 2)
('<NAME>', 50, '1970-12-05', 'M', 'Touro', 1.81, 85.0, 8, 4, 6)
('<NAME>', 52, '1968-11-07', 'M', 'Câncer', 1.87, 54.0, 9, 4, 2)
('<NAME>', 37, '1983-12-16', 'M', 'Sagitário', 1.86, 52.0, 10, 0, 1)
('<NAME>', 61, '1959-07-01', 'M', 'Capricórnio', 1.62, 106.0, 2, 2, 0)
('<NAME>', 50, '1970-06-22[...]<jupyter_text>Consultando o Banco de Dados<jupyter_code># Quantas pessoas possuem 50 ou mais anos de idade?
q1 = ("SELECT count(*) FROM tb_pessoa WHERE idade >= 50")
mycursor.execute(q1)

answ_1 = mycursor.fetchall()
for i in answ_1:
    print(i)
# Qual é a média de idade das pessoas que moram em <NAME>?
q2 = ("SELECT sigla, avg(idade) FROM tb_pessoa \
        INNER JOIN tb_cidade ON tb_pessoa.id_cidade = tb_cidade.id \
        INNER JOIN tb_estado ON tb_cidade.id_estado = tb_estado.id \
        WHERE sigla = 'MT'")
mycursor.execute(q2)

answ_2 = mycursor.fetchall()
for i in answ_2:
    print(i)
# Quantas pessoas nasceram nos anos de 1968 a 1978?
q3 = ("SELECT count(*) FROM tb_pessoa \
        WHERE YEAR(data_nasc) BETWEEN 1968 AND 1978")
mycursor.execute(q3)

answ_3 = mycursor.fetchall()
for i in answ_3:
    print(i)
# Qual é o nome da pessoa mais velha na base de dados?
q4 = ("SELECT nome, idade FROM tb_pessoa \
        WHERE data_nasc = (SELECT MIN(data_nasc) FROM tb_pessoa)")
mycursor.execute(q4)

answ_4 = mycursor.fetchall()
for i in answ_4:
    print(i)
# Qual é o nome da pessoa mais nova na base de dados?
q5 = ("SELECT nome, idade FROM tb_pessoa \
        WHERE data_nasc = (SELECT MAX(data_nasc) FROM tb_pessoa)")
mycursor.execute(q5)

answ_5 = mycursor.fetchall()
for i in answ_5:
    print(i)
mydb.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14826678869694543, 'fraction_numerical': 0.04084200892346414, 'mean_word_length': 4.402966625463535, 'pattern_counts': {'":': 1, '<': 29, '<?xml version=': 0, '>': 30, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '80864', 'license': 'MIT', 'n_tokens_mistral': 3787, 'n_tokens_neox': 3534, 'n_words': 859, 'path': 'Desafio02-IGTI_MySQL.ipynb', 'repo_name': 'evertonbin/python_mysql', 'size': '8863'}