DataEngineering/MakeaSmallDBWithSQLandPython/HowToMakeaSmallDataBaseWithAnAPI.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "13160a95",
   "metadata": {},
   "source": [
    "# Como criar um pequeno banco de dados SQL através de uma API usando Python?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92fed91a",
   "metadata": {},
   "source": [
    "Neste notebook, você poderá aprender como criar um pequeno banco de dados através de uma API, a fim de acessá-lo para fins como indexar dados em um app ou website, ou mesmo apenas organizar seus dados de forma mais estruturada do que um arquivo .csv."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18b55c36",
   "metadata": {},
   "source": [
    "Para este tutorial, vamos utilizar a API dos dados abertos da Câmara dos Deputados Federais do Brasil. Você pode conferir a documentação completa da API aqui: https://dadosabertos.camara.leg.br/swagger/api.html"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba9979e0",
   "metadata": {},
   "source": [
    "Primeiro, vamos importar todas as bibliotecas que iremos utilizar para, primeiramente, conseguir os dados através da API:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61749c14",
   "metadata": {},
   "outputs": [],
   "source": [
    "from requests import get\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22cb8848",
   "metadata": {},
   "source": [
    "Vamos explorar um pouco da API. Vamos puxar alguns dados gerais dos deputados da câmara, conforme a documentação da API. Utilizando a função *get*, podemos dar uma olhada na forma dos dados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3a36923c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'{\"dados\":[{\"id\":204554,\"uri\":\"https://dadosabertos.camara.leg.br/api/v2/deputados/204554\",\"nome\":\"Ab\\xc3\\xadlio Santana\",\"siglaPartido\":\"PL\",\"uriPartido\":\"https://dadosabertos.camara.leg.br/api/v2/partidos/37906\",\"siglaUf\":\"BA\",\"idLegislatura\":56,\"urlFoto\":\"https://www.camara.leg.br/internet/deputado/bandep/204554.jpg\",\"email\":\"dep.abiliosantana@camara.leg.br\"},{\"id\":204521,\"uri\":\"https://dadosabertos.camara.leg.br/api/v2/deputados/204521\",\"nome\":\"Abou Anni\",\"siglaPartido\":\"PSL\",\"uriPartido\":\"https://dadosabertos.camara.leg.br/api/v2/partidos/36837\",\"siglaUf\":\"SP\",\"idLegislatura\":56,\"urlFoto\":\"https://www.camara.leg.br/internet/deputado/bandep/204521.jpg\",\"email\":\"dep.abouanni@camara.leg.br\"},{\"id\":204379,\"uri\":\"https://dadosabertos.camara.leg.br/api/v2/deputados/204379\",\"nome\":\"Ac\\xc3\\xa1cio Favacho\",\"siglaPartido\":\"PROS\",\"uriPartido\":\"https://dadosabertos.camara.leg.br/api/v2/partidos/36763\",\"siglaUf\":\"AP\",\"idLegislatura\":56,\"urlFoto\":\"https://www.camara.leg.br/internet/deputado/bandep/204379.j'\n"
     ]
    }
   ],
   "source": [
    "print(get(\"https://dadosabertos.camara.leg.br/api/v2/deputados\").content[:999])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "896a52f0",
   "metadata": {},
   "source": [
    "Para facilitar a visualização, vamos gerar um DataFrame através do Pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9ac507ef",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>uri</th>\n",
       "      <th>nome</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>uriPartido</th>\n",
       "      <th>siglaUf</th>\n",
       "      <th>idLegislatura</th>\n",
       "      <th>urlFoto</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>204554</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>Abílio Santana</td>\n",
       "      <td>PL</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/part...</td>\n",
       "      <td>BA</td>\n",
       "      <td>56</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.abiliosantana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>204521</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>Abou Anni</td>\n",
       "      <td>PSL</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/part...</td>\n",
       "      <td>SP</td>\n",
       "      <td>56</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.abouanni@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>204379</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>Acácio Favacho</td>\n",
       "      <td>PROS</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/part...</td>\n",
       "      <td>AP</td>\n",
       "      <td>56</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.acaciofavacho@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>204560</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>Adolfo Viana</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/part...</td>\n",
       "      <td>BA</td>\n",
       "      <td>56</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.adolfoviana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>204528</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/depu...</td>\n",
       "      <td>Adriana Ventura</td>\n",
       "      <td>NOVO</td>\n",
       "      <td>https://dadosabertos.camara.leg.br/api/v2/part...</td>\n",
       "      <td>SP</td>\n",
       "      <td>56</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.adrianaventura@camara.leg.br</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                                                uri             nome  \\\n",
       "0  204554  https://dadosabertos.camara.leg.br/api/v2/depu...   Abílio Santana   \n",
       "1  204521  https://dadosabertos.camara.leg.br/api/v2/depu...        Abou Anni   \n",
       "2  204379  https://dadosabertos.camara.leg.br/api/v2/depu...   Acácio Favacho   \n",
       "3  204560  https://dadosabertos.camara.leg.br/api/v2/depu...     Adolfo Viana   \n",
       "4  204528  https://dadosabertos.camara.leg.br/api/v2/depu...  Adriana Ventura   \n",
       "\n",
       "  siglaPartido                                         uriPartido siglaUf  \\\n",
       "0           PL  https://dadosabertos.camara.leg.br/api/v2/part...      BA   \n",
       "1          PSL  https://dadosabertos.camara.leg.br/api/v2/part...      SP   \n",
       "2         PROS  https://dadosabertos.camara.leg.br/api/v2/part...      AP   \n",
       "3         PSDB  https://dadosabertos.camara.leg.br/api/v2/part...      BA   \n",
       "4         NOVO  https://dadosabertos.camara.leg.br/api/v2/part...      SP   \n",
       "\n",
       "   idLegislatura                                            urlFoto  \\\n",
       "0             56  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "1             56  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "2             56  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "3             56  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "4             56  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "\n",
       "                              email  \n",
       "0   dep.abiliosantana@camara.leg.br  \n",
       "1        dep.abouanni@camara.leg.br  \n",
       "2   dep.acaciofavacho@camara.leg.br  \n",
       "3     dep.adolfoviana@camara.leg.br  \n",
       "4  dep.adrianaventura@camara.leg.br  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(get(\"https://dadosabertos.camara.leg.br/api/v2/deputados\").json()['dados']) \n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3d63231",
   "metadata": {},
   "source": [
    "Vamos dar uma olhada em algumas informações da tabela. A Câmara de Deputados tem, atualmente, 513 deputados. Vamos olhar se nossas informações estão completas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a682bbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 513 entries, 0 to 512\n",
      "Data columns (total 9 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   id             513 non-null    int64 \n",
      " 1   uri            513 non-null    object\n",
      " 2   nome           513 non-null    object\n",
      " 3   siglaPartido   513 non-null    object\n",
      " 4   uriPartido     513 non-null    object\n",
      " 5   siglaUf        513 non-null    object\n",
      " 6   idLegislatura  513 non-null    int64 \n",
      " 7   urlFoto        513 non-null    object\n",
      " 8   email          513 non-null    object\n",
      "dtypes: int64(2), object(7)\n",
      "memory usage: 36.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "937a781e",
   "metadata": {},
   "source": [
    "513 deputados! Nossos dados estão completos e não há dados nulos."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df305461",
   "metadata": {},
   "source": [
    "Alguns dados podem não ser interessantes para nós, e por isso iremos retirá-los da tabela. Mais especificamente, iremos retirar uri, uriPartido, e idLegislatura."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d0207ba4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>nome</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>siglaUf</th>\n",
       "      <th>urlFoto</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>204554</td>\n",
       "      <td>Abílio Santana</td>\n",
       "      <td>PL</td>\n",
       "      <td>BA</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.abiliosantana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>204521</td>\n",
       "      <td>Abou Anni</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SP</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.abouanni@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>204379</td>\n",
       "      <td>Acácio Favacho</td>\n",
       "      <td>PROS</td>\n",
       "      <td>AP</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.acaciofavacho@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>204560</td>\n",
       "      <td>Adolfo Viana</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>BA</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.adolfoviana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>204528</td>\n",
       "      <td>Adriana Ventura</td>\n",
       "      <td>NOVO</td>\n",
       "      <td>SP</td>\n",
       "      <td>https://www.camara.leg.br/internet/deputado/ba...</td>\n",
       "      <td>dep.adrianaventura@camara.leg.br</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id             nome siglaPartido siglaUf  \\\n",
       "0  204554   Abílio Santana           PL      BA   \n",
       "1  204521        Abou Anni          PSL      SP   \n",
       "2  204379   Acácio Favacho         PROS      AP   \n",
       "3  204560     Adolfo Viana         PSDB      BA   \n",
       "4  204528  Adriana Ventura         NOVO      SP   \n",
       "\n",
       "                                             urlFoto  \\\n",
       "0  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "1  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "2  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "3  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "4  https://www.camara.leg.br/internet/deputado/ba...   \n",
       "\n",
       "                              email  \n",
       "0   dep.abiliosantana@camara.leg.br  \n",
       "1        dep.abouanni@camara.leg.br  \n",
       "2   dep.acaciofavacho@camara.leg.br  \n",
       "3     dep.adolfoviana@camara.leg.br  \n",
       "4  dep.adrianaventura@camara.leg.br  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(\"uri\", axis=1, inplace=True)\n",
    "df.drop(\"uriPartido\", axis=1, inplace=True)\n",
    "df.drop(\"idLegislatura\", axis=1, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53fd9188",
   "metadata": {},
   "source": [
    "Se queremos um banco de dados interno, ter as fotos dos deputados em URLs externas pode ser indesejável, e podemos, ao invés disso, utilizar fotos dentro do nosso próprio servidor para alimentar alguma aplicação. Para fazer isso, vamos baixar cada foto de cada deputado em uma pasta chamada *source*. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "28ce6775",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "if not os.path.exists(\"source\"):\n",
    "    os.makedirs(\"source\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8e2a332d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for url in df[\"urlFoto\"]:\n",
    "    photo_name = url.split(\"/\")[-1]\n",
    "    img_data = get(url).content\n",
    "    with open(\"source/\"+photo_name, 'wb') as handler:\n",
    "        handler.write(img_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d93ec98",
   "metadata": {},
   "source": [
    "Agora, podemos substituir a urlFoto pela URL em nosso próprio servidor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "66afb605",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>nome</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>siglaUf</th>\n",
       "      <th>urlFoto</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>204554</td>\n",
       "      <td>Abílio Santana</td>\n",
       "      <td>PL</td>\n",
       "      <td>BA</td>\n",
       "      <td>source/204554.jpg</td>\n",
       "      <td>dep.abiliosantana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>204521</td>\n",
       "      <td>Abou Anni</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SP</td>\n",
       "      <td>source/204521.jpg</td>\n",
       "      <td>dep.abouanni@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>204379</td>\n",
       "      <td>Acácio Favacho</td>\n",
       "      <td>PROS</td>\n",
       "      <td>AP</td>\n",
       "      <td>source/204379.jpg</td>\n",
       "      <td>dep.acaciofavacho@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>204560</td>\n",
       "      <td>Adolfo Viana</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>BA</td>\n",
       "      <td>source/204560.jpg</td>\n",
       "      <td>dep.adolfoviana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>204528</td>\n",
       "      <td>Adriana Ventura</td>\n",
       "      <td>NOVO</td>\n",
       "      <td>SP</td>\n",
       "      <td>source/204528.jpg</td>\n",
       "      <td>dep.adrianaventura@camara.leg.br</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id             nome siglaPartido siglaUf            urlFoto  \\\n",
       "0  204554   Abílio Santana           PL      BA  source/204554.jpg   \n",
       "1  204521        Abou Anni          PSL      SP  source/204521.jpg   \n",
       "2  204379   Acácio Favacho         PROS      AP  source/204379.jpg   \n",
       "3  204560     Adolfo Viana         PSDB      BA  source/204560.jpg   \n",
       "4  204528  Adriana Ventura         NOVO      SP  source/204528.jpg   \n",
       "\n",
       "                              email  \n",
       "0   dep.abiliosantana@camara.leg.br  \n",
       "1        dep.abouanni@camara.leg.br  \n",
       "2   dep.acaciofavacho@camara.leg.br  \n",
       "3     dep.adolfoviana@camara.leg.br  \n",
       "4  dep.adrianaventura@camara.leg.br  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"urlFoto\"] = [\"source/\"+i.split(\"/\")[-1] for i in df[\"urlFoto\"]]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "861ab290",
   "metadata": {},
   "source": [
    "Vamos testar se nosso link funciona:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b5019d1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/jpeg": "/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/2wBDAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQH/wAARCACYAHIDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD+/iiiigAooozQBGdgzxz6c1+Pn7dH/BbH9gD/AIJ7/wDCS6f8f/ijOnxB0OF/7J+Dng+1XxN8X/FbsXiU6V4ejZbbRdCl/s1nbxL4u8S+FrX7QpyWKwF/xk/4L9/8HBmmfs96O/7Hv7BvxPt9T/aS8TahNpnxF+K/hK0Oo6f8HPCzsNDTRPBusQxCPxF8SfEOvHylk8LvIfB0NsJ5ZGn2Bf46fAn/AATC/ap/aEv9Q+I3jiy8S3PiDxjq/wDbWr+IPG95rOpeJtd1PXST/bWt654g/wCQ/wBc/XJ55rizDNMFlSvjJKO2766Pa62/rQ9TLcjzXNrPBU7ra9t9bb6+d9Oi2P3Y/aX/AODwP9rvxLCzfsv/AAE+DHwe0O+1TUE0u9+IUuvfFn4kHwxGgTQtc1eAP4R8I2zNyzfZ08W7m+ZiW5rmfgF/weLftxeFb7T9I+Pv7L/wE+MunW12wv7rwZe+Mvgx458RK/3otFab/hM/DX9unn5F8IKpOQAATn41+Hv/AARy8Y2cX2HVYNNtrj7Z/plr/wAwz/uCc/pXoGvf8EhL6GK3g0qC2tv9E/5e/wDoKfp+VfLf6+8Or/mZr7lpt5W/p+dvtMP4XcRtXabdvx08uvbbZdNP6/8A9gH/AIOPP+Ccv7cEmneDtb8az/ssfGjVNtlafDP9oO90jw3puvvGirJ/whHxRMy/DnxBvym3w5dXPhTxo8m8DwWShI/oGiaGZBLAoKnuD788Hn8self5UfxI/wCCRfiP+y7iex0q2ubi1/59P+w1+X51+ln7BH/BQ/8Abj/4Jsa/8Ovhn+0l408ZfFD9jB30rwHpR1XVVHib4FaeqJHoOu6NruPm0ARokY8MeLc+DAiIoO1QK9nLuI8nzZ2wmaRb2s2tdlbp17nzuZcE8R5X/veVy01vHVq1n56aJ+vU/wBDumFPQ49q+FvBP7b/AMHfEHww0H4rt4r0zVPBOqWisPFGn3a4IWOVfLbSEQTP4hkfSHd/DXhw3bB5JHVCWAH1d4N8Z6N490SHXtClvfsF0gCwarpeqaTqNiWzuj1TSdYEE6EY5Zoo+OFJGSPoZ4XEUEuZXW9+nyavv/V3v8y9HZ7rod9RRRWYBRRRQAUUUUANIPHXvz6ZIP8A9avyo/4Kr/tVa/8As4fAqDRPAmuJo3xP+Jt7JZ+GbwBf9B8MaJLoZ8a6yVx8rwQa5GobPzCVvqf1YzxnB+nQ1/Hb/wAFffFE/wC0R+2L4n+H32+1/wCEW+DXgPTPh9Y2d0u0L4m10/29431fA4wi65oK+/8Awj+cDgVpQfLzYlaKEObXutVp6pdLNLUuGG+uTjhrv35K9tNG0n99j8av2V/2P/BHjz4jaz+1f8TbK38beMNU8Sape+G9W1azP9mf2p21nQ9C78/8iRX7QaDZ30MVvBpUH6Hp26f5/WvH/hv4bsdHsNP0PSoLa10/QdH0uys7Tn05H+fzr7I0Gax0eL/l2/488f5Gfp/Sv5T4jzPGZ9nGa4nFydk3GMeZ2smtUn+S39W2f2TwzlmEyHJ8qw2EV3ypttJ62W7eun+Zz+m+G9V/5foLn/wD/wA/X/6/Ar6/oM/2X9xBc3P/AG5/17f/AF+K9g03WILy68j7D9m4z+eM4H+etbGsWdj5X+ouf8fy/wAB+deWstuk+Xc+o/tHpc+J9Y+3ad/r4P8ARz/n9PavD/iRo/hzxh4X8QaHrllpupafdWf2K8tLv/l//D/P+P1h48l0qH9x/pP+lXn+mZzXzN42h8nS9Y8j/j3tf/r+tc2FlLBYzRvvo30tbbfZL+rkYpLGxalFO6s72e+mn3a+h+XHwH+M3j/9hv4l3Hg7XL7xJ42+C+g2eqXvw38J6t4P/wCFkDwJpeu9vA+h+If8/jxX9lf/AASZ/avg+Pnw/wDHHg7V21pde8L+JVu9I/tfwqfC2pt4afSfDiRLrOh5VfD05Zt1r4b3K8XhZbdkJy2P5NPjNoMGpaN4f1yfSrbW/wCy9Y/sXxJaf8xM+F9dzoOu/wBh/wCPqa/Xn/g3N+IHjJvHvxw+EfinxGnjbT/AdtpOifab461qHiTQ/FWhnXwmtf2/rys+ueH/ABT8PToHhuN48OJPDqbWXkj+suC86/tXhNfWtZRty33TTSX/AAXva9j+POP8kwmRcQ/7LtPddNWttbedttz+u+iiivTPkQooooAKKKKAKNz81rcMP+eLD8gQPw/qa/h1+J39lal+0t8YPFU99c3OoWviTxRousXniGzP/IU/tn/kNf8AUwf8gP8Az0r+2nxX4s8N+BfD2q+KvFus6f4e8M6JaPe6vrOq3IsNO0+wj+/LNIxCjAOPm+csQqAcKP4/vjl4JsdS+L/xY+JvhXVdO1LwP4o8eap4n8H/ANkn/iWX+l/2127flivLzvFLC5RmbvyuUfdV0nslovW+nc93hrLsXi84y2ST5IzSnLlbipLl0vtfyv52PN9B16CHVNQgnn+zW9rZ/wCmXf8AzDLD/P8AOvtjwfN4OFhbzz65ptt9qs/+Pv7Z/kj+g/X8B/2hNe8R6xdax4Vg8Val4S8H2v8AZl7efZLP/kbdTPXRR09O2K/Pfx58JfDnirx54f8AA8/x3+Otx8SPFF7petWfh3wpZ+MvEnhmw1TXf+QF/wATzH/YD/5FP/oP4r8EyXh3C4tvG4t3bk20teq+/vr80f0/WzHFYTkjhP7q187K/lv56n9uGj6b4O1KK3n0rxHbXP8A2+f56n6/nWxeWelWcVx9uvrb/Rf89fz6Cvw3/YV8Sar4J1nT/AGueONR1vR/sYvtHu7uy/4/8Z/5DnX/ABr7I/br+Kk/gP4X3F94V1W4tvEGqWf2LR/7I/6Cn19s/wCeyxH1RYvlXpb89PuPcX11pO3RdD6A8VQ+Drz9xPfaJ3+x2l3q/wBP5f5NfH/xUs7Hyrix0q+trm4+x/bfsn2zr6f5+vWv59/jN+z34wh0+4+O3x+/aE+Pv9j2t7pei7vh7o51I6B4n13RP7d0LRPT/hIj4c9QT0wcda/w9mn03VPC+q+Dvjv8Y7nUNUs9U1r4b+IfiEP7N1PXv+Q/oP8AYuuaH4g/7AeuDivSxHDmDxOEWMUtUltvfS2mnq90+mh85/bWbYXF/U7ael7LTXr3fnp5n6ceMPI1LwR4ggng6Wg/Hp/n+dfoJ/wb6X2k237cf7QFzcj7PL4y+BK3nh60tcf2Xe6hofjbQF152yQv9vrndKWPEQcjJAB/NvwHrGq+NvBmn6rfQf8AE4urP7beWn+Oe/8AOvsr/gnx8RfDX/BPT9qC6+Jfxxeaz+GWq+BdR0fV/FtvY6Qy+E9T8c674ebRdXbYyMvh2VA0W8E+XvD7JNpjb7/gHE4PCZVmccW+XWKTvovhS676X/4c/JPEjLcZmua5bLBQc+WLk4xWrsrtJ+b033P7hqKzrO9t720t72ylW6tri2W6t7i3K7btSvBXj+IFce5x2IGjX3y2VtrK3ofkzum0907fcFFFFMQUh6HPTFLSHofof5UAfEf/AAUH+Atz+03+xf8AtEfAuxubi11Xx78PNU0/SLqyu/sV9/bGmyJrejeXMACC97pUQySM/NhepX+U34TalBoPwM8L+HL6e5trj/hG9L1q80nVrz/iZWA0L/iQ67+nX3r+4EY8vy3BI2kt75Y47duMfnxmv5Gv2u9Bi+Hn7XPxl8DP4XuvD1jc3OpeJvCTmyA0zUdN1zWRrrHRFT5VJYHIUAK3AAxivznjjANrLsWpNcsnCXRbJqTtp9pt3s10P3HwlziOIy/NuHMXBOCa4hpzsubmpqMJU77pNKnypbtSejav8ZeNv2afB3xg8L3HhzXPDltqWn3Vn9tvP89f8ms/Xv2P77xV8X/C/wAfv+Fc6lc/GjwHZ+F7Lw343u7z+zNMsP8AhBdE/sHQta/sP/hJP+EY8QeIvCZ0PQ/+ZP8A+YB4Y/H9EPhjeaVNFo8EEH2nULU/6Yev9f8AP1r6ghs4Ba/uILa2t/8APf61+W5OsXhef/hTdtbrXrbR9tP6tY/Xo0MJi2v+EzW6s733tv8Ai/8AgI/J/wAH/DHVYfiNo/8AwmP2bUtZ+2arrWsXdp3/AA+ldT+1p4Kvrz+x557G2/s/S7P7b2/5gXfp/k19D2ej/bPirqEFv/pNta3n/H2OP89Pp713H7VHgmeHTLefyMfZf9C/6/8A8fT+fSuZZdi8VL65fr6dUvK2v9aM+jr4lYa2D06W+5WW/ofnj4o/Z9uvjT8If+Fd+J9M0/4n/CjXbzTPE3/CE3JK6d/aeiDa2s5185/4SEgkMPCXUHB4OK8t179i2x1ew8D2Oq+Fbm28P/CXR/7F+G+k6tZ/8ilpf/IeOi6H/wAI/wD9TH/9ev0g/Z7+w2fhe3gnntrnH/Hnaf8APgf8f844r2D4heRDpfnwf8e9rZ8Wn4fh+GP/AK9e3iHjVg0ruyd7X+9/gtVrouyPnMRhU8apWtf7SSvb5+vnvufk/qXhuHw3Lb+RBb232q05tLSz5/x4/wA9q5fx7oVh+05pVh4B8Ow3PiPxP4o+NfgT4S2h6f2f/wATrQNB14k9h/xPPpgfl6B8TtY/tLXvP/0a26/nxzkV9F/8EnvBl74z/b4tLqx0O7t/A3hu48V+Mb0XFrs086pomhnw/wD2yjHG1/8AhIzoe0kgBsHIxx15Dh3iJRwl5WziSu10s1dvt+B59XH4TLaWb5tyxcslpTUVKzUpOOitr5bbdtLH9iXhrRrbQ9C0XQrY/wCi6NpOk6Tbg8Ero8KQqf8AxxPfg9hW3P2/D+tTgYAHoAPypa/eorlio9kl9ysfyFKTlKUnvKTk/Vu7Kw6D6D+VFWaKZIUUUUANwMcfgR/jX4r/APBSn4GeN/G/jnwZ4h8H/Dzxh43GqeFJfD2fBulnVfsHibSNcabQm1pif+KeizrriPxPwI1TywNqDH7UDp0x+v8AKhgCpJHb+fT+deZmOXYbNMK8Hi9nZ6Wumuq6PT+tD2MkznF5HjoY3Bv94k4NO6Uovo7X8nbXofyE/CXWP7NutQ0q++zW1xa9j0/r/OvUNe+J0E32jSrG+Ft9q/0LWOv+f84p37Tfw7svhh+1v8S/DMNxa29jq/iBfFGk2g4U6b46H9vPooHUDd/buOvHr1r59+IVnqngn+0P7D0rUvEn9q3n237JpP8AyEx/OvxXE4VYTGZngl0aWq6XSTX4dPuP6SynNHUwFPHxbftFGUdfi5lFrutdLPXpr1NHWJviro/xQ8D658OdV0628L6X/an/AAmHhO70f+0v7e/6AX/E8H/Ivnv+g6V5t8ePjB+0n8WvCXiCx8OaHrfw31C18Sf8jDq1no/jb7f/AMTrn/uXQfTH868/0H9uT4SaDLb2PjGD4o6JcWt5/Yv+mfDbxjpv2DVP+w54g/z1FbHjb9tL4A6bpdx/ZWu+NvG2sanZ/bfsnh7w3rXP+f8AqU/xr2sLl2DaWvRfktfvX9devEPiLGYq6yt20d9rWt126X29DsPh74k1XTYtP8/7MNQ+yf6Z9k/4lv2//wCuK9wu/iF52g6h5/8A2+fas9/p7V+X+g/Hjxx8QvG9vofwy+DvxS0S3/5DV54h8b6Po3gnTNBGP+xk/wCEn/4SL/I7V9cS2f8AY/gPUPEfiO+/4nF1/oNn/pv+f/1V5uYa6f8AD9P8zow2Jxi/2TGaPun1uur10f4nC/DP4c+If2k/j1oPwu8E/wBjW2r+L9Wa0tLvxAutvp1gNA0T/hIP+J2qAuzY0LhFDMxwFBNf0r/8E+P2N/Hn7MGp/FLXfigfBM+sa7PpfhXwLeeDbvXb0f8ACDaNv1ONQNfHmaEslxNHGPDkQeNRoazB2TaW/Mb/AIIwfC1PEf7SnxI+Kl1b5034Y+Bo9PtLo/8AQ0+PXWNeO4/4R7RdaB9M5xX9QpUHKHgZ47cjOPwIJ7e9ffcJ5NhFhIYyzc38NnstLva6+W68t/xDj3iPGPHZnk+FlFULxVS6u204tWd7W06p9Vpo1PRRRX25+YhRRRQAUUUUXAKRuh+hpc18bftb/t0/svfsOfDvUviL+0l8V/D3grTrexe80zwu17b6j488VOoC/wBm+DfBEUg8Q+IriQhgqW9s6hiweSJdqiKNCWJ+GMpSutIpt67v8b+a19Gt1tv12PxC/wCCm4nsv2qvH2t2+CNL8OfDI3f0OkMR/T/GvHfB/jb+3tG0/VZ4PtP+h/04x+v+TXhHhL9svU/+Cgtnrn7VPiH4dH4YeDvid4j8VeGfAnh+73alqZ+GPgjXD4G0XW9a1osw1zX9+jN/wmcSHy4E2xRKsSIo8e0Hxhrnwx+IP/COeI5/tPg+6/5A+rf9Qv8A/X/UivwbiT62uJM0wj0d29td18/8vkf0pwsuXI8nxV17kIRfZ8sYq/yte7/Q+2LzTb6z+0X0Hhy21u3uv/BZjrnNcfNoM+j2txPB4V0S2+1c2dp4es/x59//AK1e8ab488Kw6Xbzz6rb+R9jz9r+uP8AP881j+MPid4Oh0bz55/+vP8A+v8Aj39+1b5bXaWuZ7K33W2v5XS9ND9D/tHE/VP+Rok79l5ed9FdbemyZ8vnTZ9B+0arPpVtpv2r/P8AnnrXz/8AGbx551ro+hQT4+y/6b/on/L/AP04z/LrXqHxs+M3hX/hEtQ8jVftNva4/wCPQf8AH/8A/qz1r89/Dc2ua7qmoeOPEc5tv+gNade3+f8ACsGm39afWVtb7X/ryPkcxxWKxWJ+qqSdot83ok+mvX/gM/rr/wCCJfh3w9pn7MHi7xFYatp+oeJ/E3xf8Rnxda2t62oajoh0LR9E0TR9B1lVJbR5jDA3ijydnyr4g3BjuIX9oT/FjORjn24P58H8/ev8tX4B/wDBR742f8Eiv+Cxfibx18Q9A8ZaF+zf8frb4XW/xo8Aatp7tp/jb4P/APCG6Gvh740eCdDZ4w/iDwqgk8T+DhBgtBN4m8AGb9/KIf8ATu8BePvBnxX8E+FviH8OvFOjeMvAPjjw/pviPwp4t8O3g1HSfEHh3XYvO0bWdI1ZDslWaBxIjDe4LYYRyLiv3HLsLLC5VljaaTgls99HazS1s92vR6O38z5ziHic4zKV2/3l0/ny6O2ytZb2t2segUUUV1nmhRRRQB8L/tLf8FF/2Hv2NtPku/2mP2pfg78Lb+2U7vC+s+MdI1Px1eNt+QaV4H0OWfxZckZwXj8MlScoSowT/Mv+1p/wePfs4eBo7zQv2MP2f/Hvx11tVK2nj/4r3I+E3w3VmI2smip/wk3xH8QoiBhsa38GsXZT5oVSD/nvzWc+pXVxfX89zdahdY+13d3ef2lqd/z0zWfe6PNDF9o8jn/P1/L8q++wvCWF+1JzenkuiTSSv57u+vS1vOeL8l6+9fZLf5H7sfH7/g4n/wCCwH7X9/qGlw/tFD9nnwPdZ+2eHf2cfDZ+G4sNMOef+E4B8W/E/v8A9DiOeuQcV+Z+sa94k8Vapcar4j8R+JPFviDVP+Qx4s8WeJNZ8SeJr/21zXPEH/FTZ9a848IXmif8I5BBpc+P+fz7V/yE/wC1P+o3g4xnp2qfUvEn/LjpWba3/wCXy75/0/2/HP4V9tl+WYPKsH8KvpbRb2SXe/R/oebiMS9reun9f8Pr2v8A6Pv/AATu/ZOsPiL/AMETf2HP+EIt7e5+I/hn4R6n8StIbKhdbbx54y1/XvHHg1izLwzARKFDEyaIigYJI+QvEvgOx8YRfYdcg+zW/wDy53f/AD4fh+lfr/8A8EDbPXNB/wCCbH7P/gHXP9H1D4c2fifwxZ2n2v8A48dL/tr+3u3trg9uvSvbv2sP2Frnx9d+IPib8CotJ0vx9qrSXni/4e6zdDTPDnjrU1famuaHrak/8IF8QJFKuCM+EPFwYLdRxSGTxjF/LvibwnisVmss3yjTN+aV0tnHmutFbXl0s9W1oftfhxxthcHho5RnF3lDaSl/K7JJt3bSu9/s6uVo7fzJeNvBPjHQbX+ytE8R63pv2X/jztPsf9pev+PevF5vBPx+mtfsOq+I7m20f/j9/wCRbOm84r9frnwlrGitN4Z8aeENQ8K+JdOXdc+FvGmkjT9QslJ4bQWXIIPbxV4Sz7cV4V4w0fxV4kuv7D0nSv8Aj6vPsVnaWn9talqd/wD9QXQ9D/z+lfk+Gq47CNRlB817O8dU1ZO/z6M/acRSp4qCng5x9m0mnzJx5XZ3Tu07rz7H5n/8IfquvSfYb6+1LUtP0v8Az9fz/wD1/qf+w3+wfqnxg1C38ffE3SrjTfhB4XvP9MtP+Qb/AMJbqehk/wDFF6IPqD/wm3TOODxX3X+yt/wTA1vVYdN8Z/Hmy1DwrojXYv7PwFZkaf4115WGV/tkgn/hBPDznAAU/wDCZbSSX8GsFVv101DQNK8N6Vp/hXw5pWm6dp+g2f2LR9K0mzGm6boelYx/Y2ibeMnHYD0wOlfp/CXCmLzPHLF5vZZVaMlF2Ula1pW33+999j8m4s42weXYN4Phx82a/BKSd4vZNJ31s/l0vdM/iR/4Ob/g14d1f4fH4mf2HbW2v/DDxH8MB4c1W0tDiw0rx0df0LXdFOOvh3OhaEwByMgHFfmp/wAEVv8Ag4H+I3/BMQ3Xwe+LvhjxN8dv2TNQu/teleDtF1zRo/Gfwb8Q6xqi/wBq6x8MbbWo4ln0DxE32ic/DO5fwpiaU3EQhn864n/YL/g528SWOhfs56fod9P/AKf8Wv2nPC/hjw3af8//AIX+Bfwx1/Xtd1vn/qY/HGh1/B/ND5N1cf5x3xX9DYjBQxeCjFRVo2SVltstVr9x+HYec23Jt3d3Lvdybf4/Lc/2Ef2Qf+C4v/BML9s+20u1+FH7U/gXw742v4kU/DP4x3g+E3xLE4ZwdLTRfHZhg8Qycb/+KP8AEPinJfKTYBQfriksF3EJopFngmAwVIK46Eg8enORnvg5Ff4SumwwXkVxBPB9p+y/5/yO5r9TP2Jf+Cvn/BQf9gK+sLf4CftCeJbnwBa3Lrd/BT4rXzfET4S3gdcOI9J1wrJ4aYjHz+D38FuMAhgRmvDxPCT0eCnzaLSTVvvS06f5WatusxXWPzV/L+vu2P8AYrwfU/p/hRX8A2mf8HlPxNXTtPXWf2IPhPPq62VouqzwfFzxJFBNqQgjF9LDEfh/IY4pLrzXjjMjlEIUu2NxK8R8N5xf+G9/7v8Ad8l+Xy112+v4b+f8P+D5/wBWZ/FjDD5X+f8APHXvXYabNBNF5Hkfac/5/wDrmtDUtBxF58H9Of8APX6fpy+hQz2Ws6hYz/8AHvdWf2z/ACO4H+evP7d9V+q4vun2/r0+/wBDyff/ALv4heaPYxXX27yLf7R+X+f8/iQw/vf6f54z+h61sXkP+f5c/wAh+fes/TYfOluP+vP/APX/AJHNN4dW6eXr/wCAh7/938T/AFtv+CVXw8n8Lfs36Le2OqaZdeFfGWj+A/iF4EFrzqdg2t/DLw9ofjfR9bzwNviTQicjBHBz8or9V7WQtEIZouR27foOPwxX5cf8Et/HerXv7KHwI0PxJpVrpt/bfCn4X3ukarpef7L17TNc8F+HyMkHnxB13A9fbnP3r8YPG2p+C/hz4x8TeG7G01Pxja6Pqdn4H0i6b/QNe8clWXQdIbjcS/iJNmTtbAba2VGPxfPI4mWbSi7+/JadFsly26X9522blfqe1hm1g1qlp8Xno9n1/r1/Bz/gpd/wWx/4J1/s/fGTV/2YPjFba98ZPGHw3i0vUPGvhfwT4FPia+0fxPrw/tnRPC6eNEmaDwLrvhvQI/M8ZLFs8Xwrr/htI0hle6DfVf8AwSD/AGrv2K/23/gz4m+Nv7M3wxg+FPi3R/GGq+Dvih4G1+5i1Dxv4F1CEu+iCXV1Lzv4a8V+HTF4k8LTEwWrGTWraKHd4buAP83X/goT+zT8VP2WP2+fjh8M/jT4yHj/AOIHij+zP2gPEnjcWYH9var8c9FHjvXsjX8Y/wCKi1vWxyDlgcktmv04/wCDYDx9+1b4K/b2vviR4B8D+LNc/ZD8eWo+DX7RviAWh/4RrT9T1vdr3gTW2IIOu+I/CviTjadx8HeB/EHijcFQjzvUxHCmUPJ1jrReb6S2TUlZOydtrXa13s9Vo0s5zZP6mszccod/cTcdZWvdLR3dt0/d022/0x5poP8Alh07c/Xt/nH6V5Pr3kWf9oT+eLa/1T/QrT1/tTn0/Xp+NesWdnBDFP59x7H2xz/9b9etcN4lh0qG6t7/APss3GoWtn/of/Thwfb3rxssr2xXKk7Kyt53Wjvp6/i+/L9Wu78yb73lf77n+dt/wdX/ABCsr39tL4AfALQ8/wBj/BL9n0+JryzN5wPFHxy8aa9rmu/21x/yMB8O6DobDOclsjg4H8pOsab/AMtz/wAuv1z6f59ffrX6wf8ABYX4y33x3/4KWftj+P73Vf7RtrT4wap8PtHuv+oV8KP+KD4/8Ef4cdK/L+b/AK4eg6/j1/P+VfrGEw3LgI80knZczuknJ6yere7bfT/Lg97+7+J5haaiZb+3FkDcQD/j9ujx745Hf/PXjt/J838PfH+cZ/Wq8Om2Om3XnwQe95af/W/z0HFdzDDYzRefAen+fw//AFd6xwuHvqne+1ne+z0t8unyOo5X7Hceo/L/AOtRXQ0V6P1XzX4/5nJ739z8fL/gfge4Y/6Yfr/9auY1izgh1DT54M23Sy9/U478/wCfWuv/ANTN7fl1/p+Q+lY+vQ/6L5//AD6/6b+Q+v4f0r3MR8Xz/VnEcPqf8P8An0qDSP8AWz/X+la15/qh9D/M1Q0H/XXP4Vyv/e36fLYD/VQ/4I2+JJ9Y/Yo+A/8Aav8ApNv/AMKH+Dd7Z89dL/4QvQe1fqh4phg+y2898Bcm1wdH1bP0/wD1V+L/APwQT8YQa9+wL+yf58H+kD9nzwvovH/UC1rX9A7dP+QHzwOfzr9Fv2u/2gvCf7IPwE+K37QPxGnH/CsvhZ4O1Xxl4jtgOb4aJj+xND0M4/5GHxb4kC+GCvQnXVY5KivybM8O3m/f3lpf4k+W9ttbJtaWa7N3Pdw/+5r0X6n+cl/wcT/Fmx8ef8FTv2n73Sobj/iznwg+DnwKvLw2f/H/AOKNE8Gf29rhz0/5jnPsPav7UP8Agix+z94J+Gf7A37K/gLwxpmn6fcaD8KvCvjLxhaklv7d8d/FTQ18c+N9YGuc8KuthUUYCKoUAAAV/mpftA/GLxT8ffFXxf8Aj748e3Pjb4/eO/HfxZ8W2tozHS7DVPHGtbm0aPKxlU8LaAX8NquwMu/DZYsx/wBFP/g3G+ONv+0j/wAE+/hv4juNVF14v+F9mPgX8SM3mdT/ALV8CHOg63zznxX8Odb0LnsRX0WYR+qZOlHSySW7tokl36Wve/5nn4ZrFYtLs7vqnpFbare+vY/o6im/dW8E8H/Hr9Mf0r5d/an+M2l/A34QfFL4t38/2bTvhf8ACvx58QdWu7v00LRNe17nrnnQz6ce1fRMOmzwx/uL65uP+nS79u3+fQfU/wA83/Byx8YZ/gz/AMEwPijpOlXv2bWPjb4k8B/BeztP+Yn/AGZrutHXte/8tzQtcz3+lfJ5Jh44nGxT0vJ2XRv4nfR6PVvp57X9rEaR8kv80f5qGva9qvirWdY8Va5P9p1jxRrGqeJ9ZvM/8f8Aqmu61/buu8f5/Sv0V/4JEeBf2LviB+3b8MvC/wC3nrOj2XwA1LRfFTWVh401nVvDXgnxF8UQCfAmheNdd0Nkj8PeH3IAUu6ICfndVyR+bVH/ACz/AH+OvHr1r9JzDLnmeU5lg02naNmnbolvo9Wt/noeJ9Z/r+on6cf8Fqfgz+xf8Df26fG/gr9hLxFoWtfBbTPAvhnWvEdn4I8Y/wDCbeCPCXxP1z+3j458F+CfGpHi1l8PFG0End4tA8Ft4gdFL7Gr8l/Cs1xNpfT/AJfD7+9aHjCaCz0G4gg/5ev9CtO/H9cfn/OrPhazNpYW8H/Ptkn889Pr/n05sly95Wsswjd9Hrq9kurv28wxGJ7en/B6edvnror9IIZ8D6e/9DiitIQ8D/SOw7e1FfWfL+v6SOM9Im/134j+dL5P2yK4Pkf8ufv/APW+vaiiugDz8efNYf6j/SPfv/j/AJ9zVfR8/av9R/8AW5/+t0/DvwUVyci8/wCvkB/pIf8ABu7NNr3/AATd/ZYvrKC5uBoP/C0PBd4bSz6f2F8TvH3+f8K+Z/8Ag7u+PuqfD39g/wCFHwCsZ7m21D9oz476WNY7fb/Afwn0U+O9dwPbxIfA/wCWMUUV+Y5g/wDjJEulk/nZI9TDN/U/u/I/gX8bWfk+HfsP/Pr4b8MZ/wDrc/59K/qf/wCDO/8AaLvfDX7S37S/7LF/f40b4tfCDSvi14b0knH/ABVPwq1v+wNcHPdvDvjlR3z/AGCABkg0UV9FxN/yL3onaN9e6s1+KRhle8PVH+hB9jn/AOeNz+S/41/Cv/wd0fGzVJtZ/ZH/AGbZ4bi3uLW98efGjWLQdL/S86D4D8DHHrk+Oc/hRRXxfCL9pnNpdpPTf7J6OZf7rfXp+h/GBD5/XyPw/HH09/r60eT/ANMP8/8AfdFFfq/so9t99tfwPBOI1eI6v4nt7EQ/uNCs/tl576n159OR24Pcmu302G4hi5/PP4fj/n8SiuXDQV09b9/v/wAv60s7/wBamuOg+g/lRRRXvHMf/9k=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import Image\n",
    "Image(filename=df[\"urlFoto\"][150]) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d697469",
   "metadata": {},
   "source": [
    "Pronto! As fotos estão prontas em nosso sistema local."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd093975",
   "metadata": {},
   "source": [
    "Agora, nossa tabela está pronta para ser inserida em um banco de dados SQL. Para isso, vamos utilizar o SQLite 3, uma versão mais simples do SQL para pequenos bancos de dados como o nosso."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5bb14964",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cbaf545",
   "metadata": {},
   "source": [
    "Vamos chamar nosso banco de dados de *deputadosDB*. Primeiramente, precisamos conectar a biblioteca SQLite3 com este banco num arquivo. Para isso, usamos o método *connect* da biblioteca:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d1326474",
   "metadata": {},
   "outputs": [],
   "source": [
    "connector = sqlite3.connect('deputadosDB.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f309d9a2",
   "metadata": {},
   "source": [
    "Agora que conectamos nosso banco, precisamos de um cursor. Um cursor é uma instância que possibilita que criemos cláusulas SQL e a processemos no nosso banco."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "238c37c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = connector.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0b8376b",
   "metadata": {},
   "source": [
    "Agora, vamos criar uma tabela no nosso banco de dados. Para isso, vamos usar nosso conector, porém escrevendo cláusulas SQL. Vamos criar uma tabela chamada *deputados* com as colunas correspondendo ao nosso DataFrame, porém indicando os tipos de dados adequados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c5690b5a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>nome</th>\n",
       "      <th>siglaPartido</th>\n",
       "      <th>siglaUf</th>\n",
       "      <th>urlFoto</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>204554</td>\n",
       "      <td>Abílio Santana</td>\n",
       "      <td>PL</td>\n",
       "      <td>BA</td>\n",
       "      <td>source/204554.jpg</td>\n",
       "      <td>dep.abiliosantana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>204521</td>\n",
       "      <td>Abou Anni</td>\n",
       "      <td>PSL</td>\n",
       "      <td>SP</td>\n",
       "      <td>source/204521.jpg</td>\n",
       "      <td>dep.abouanni@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>204379</td>\n",
       "      <td>Acácio Favacho</td>\n",
       "      <td>PROS</td>\n",
       "      <td>AP</td>\n",
       "      <td>source/204379.jpg</td>\n",
       "      <td>dep.acaciofavacho@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>204560</td>\n",
       "      <td>Adolfo Viana</td>\n",
       "      <td>PSDB</td>\n",
       "      <td>BA</td>\n",
       "      <td>source/204560.jpg</td>\n",
       "      <td>dep.adolfoviana@camara.leg.br</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>204528</td>\n",
       "      <td>Adriana Ventura</td>\n",
       "      <td>NOVO</td>\n",
       "      <td>SP</td>\n",
       "      <td>source/204528.jpg</td>\n",
       "      <td>dep.adrianaventura@camara.leg.br</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id             nome siglaPartido siglaUf            urlFoto  \\\n",
       "0  204554   Abílio Santana           PL      BA  source/204554.jpg   \n",
       "1  204521        Abou Anni          PSL      SP  source/204521.jpg   \n",
       "2  204379   Acácio Favacho         PROS      AP  source/204379.jpg   \n",
       "3  204560     Adolfo Viana         PSDB      BA  source/204560.jpg   \n",
       "4  204528  Adriana Ventura         NOVO      SP  source/204528.jpg   \n",
       "\n",
       "                              email  \n",
       "0   dep.abiliosantana@camara.leg.br  \n",
       "1        dep.abouanni@camara.leg.br  \n",
       "2   dep.acaciofavacho@camara.leg.br  \n",
       "3     dep.adolfoviana@camara.leg.br  \n",
       "4  dep.adrianaventura@camara.leg.br  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5ec34ebd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1dc68fde3b0>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"CREATE TABLE IF NOT EXISTS deputados (id int not null primary key, nome varchar(99), \"+\n",
    "               \"siglaPartido varchar(99), siglaUF varchar(2), urlFoto varchar(50), email varchar(199))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "122e1497",
   "metadata": {},
   "source": [
    "Já criamos nossa tabela, e agora precisamos inserir os dados. Para isso, vamos iterar as linhas (rows) do nosso DataFrame, executando uma cláusula SQL de inserção de dados na tabela a cada iteração. Aqui, é importante que a ordem das colunas escritas na cláusula SQL seja a mesma ordem das colunas do nosso DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "77d1a4f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in df.iterrows():\n",
    "    cursor.execute(\"INSERT INTO deputados (id, nome, siglaPartido, siglaUF, urlFoto, email) \"+\n",
    "    \"VALUES ({})\".format(','.join(['?']*len(df.columns))), tuple(row[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "949f85e0",
   "metadata": {},
   "source": [
    "Aqui, a cada iteração rodamos uma execução SQL para inserir os dados nas colunas especificadas. Ao invés de escrever os valores, usamos um *placeholder* para preencher os dados referentes a cada linha iterada. O *.format(', '.join(['?']*len(df.columns))* irá preencher o placeholder *{}* com pontos de interrogação separados por vírgulas. Isto é um \"coringa\" em SQL, e os dados passados estão em *tuple(row[1])*."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8349e14e",
   "metadata": {},
   "source": [
    "Nosso trabalho está quase terminando. Por último, tudo que precisamos fazer é dar um *commit* em todas as alterações que fizemos no banco de dados, e ele estará pronto!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ece8d565",
   "metadata": {},
   "outputs": [],
   "source": [
    "connector.commit()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}