pandas_sql.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "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.6.5"
    },
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "y9MtEPy24UQN",
        "IqXVeo4p4UQN",
        "DaxAtjnv4UQO",
        "mgG22pG-4UQP",
        "4jWO7vS84UQQ",
        "YDyE9xRF4UQR",
        "A2y8pnOY4UQT",
        "8OCbxuAI4UQT",
        "2E5a9L-H4UQU",
        "jywPRAzo4UQU",
        "KARMbO2E4UQV",
        "8_tfjwwX4UQV",
        "eKnmjkHy4UQW"
      ],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/euviniciusmendonca/estudo_python/blob/main/pandas_sql.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F9EfYGAy4UP-"
      },
      "source": [
        "#### Instalação das Bibliotecas necessárias."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W7nrgYNL4UQC"
      },
      "source": [
        "import pandas as pd\n",
        "import sqlalchemy"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xoa55iW24UQD",
        "outputId": "52f1e0a7-097f-410a-b993-cb21cfdfc37d"
      },
      "source": [
        "!pip install pymysql"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pymysql\n",
            "  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)\n",
            "\u001b[?25l\r\u001b[K     |███████▌                        | 10 kB 17.2 MB/s eta 0:00:01\r\u001b[K     |███████████████                 | 20 kB 11.8 MB/s eta 0:00:01\r\u001b[K     |██████████████████████▍         | 30 kB 9.0 MB/s eta 0:00:01\r\u001b[K     |██████████████████████████████  | 40 kB 8.3 MB/s eta 0:00:01\r\u001b[K     |████████████████████████████████| 43 kB 1.1 MB/s \n",
            "\u001b[?25hInstalling collected packages: pymysql\n",
            "Successfully installed pymysql-1.0.2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w1sO4f9X4UQE"
      },
      "source": [
        "#### Engine de Conexão ao Banco de Dados MySQL."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uKbOknGZ4UQF"
      },
      "source": [
        "Sintaxe:<br>\n",
        "engine = sqlalchemy.create_engine(\n",
        "'mysql+**drive**://**usuario**:**senha**@**ip-servidor**:**porta**/**banco-de-dados**')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BJk2BzaX4UQF"
      },
      "source": [
        "#### Documentação do SQLAlchemy."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "92AayPgj4UQG"
      },
      "source": [
        "https://docs.sqlalchemy.org/en/latest/core/engines.html"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gTQ8EM-j4UQH"
      },
      "source": [
        "#### Criando a Conexão."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2It7QnNO4UQH"
      },
      "source": [
        "engine = sqlalchemy.create_engine('mysql+pymysql://admin:DNCasdf1234!@pd-database-dataexpert.ccxrrioevqcs.us-east-1.rds.amazonaws.com:3306/databasedataexpert')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FaJ24D5i4UQI"
      },
      "source": [
        "## Método:  read_sql_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-ETv_fzo4UQJ"
      },
      "source": [
        "#### Paramentros do método read_sql_table"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zCsMrQed4UQJ"
      },
      "source": [
        "* **table_name** = Nome da tabela onde será feita a leitura dos dados.\n",
        "* **con** = Objeto conexão criado pelo SQLAlchemy.\n",
        "* **schema**= Schema onde a tabela está armazenada.\n",
        "* **index_col** = Coluna a ser definida como index.\n",
        "<br>..."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rM0boEf_4UQJ"
      },
      "source": [
        "#### Lendo toda a tabela Employees e transformando em DataFrame."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "fpTr2g8Y4UQK"
      },
      "source": [
        "df = pd.read_sql_table('Athletes',engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i_7n4ukM4UQK"
      },
      "source": [
        "#### Listando os dados e informações dos atributos."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "IxUlpZuC4UQK",
        "outputId": "bea5f5a4-24dd-415d-81e5-c9bfd46f162d"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                Name     NOC           Discipline\n",
              "0    AALERUD Katrine  Norway         Cycling Road\n",
              "1        ABAD Nestor   Spain  Artistic Gymnastics\n",
              "2  ABAGNALE Giovanni   Italy               Rowing\n",
              "3     ABALDE Alberto   Spain           Basketball\n",
              "4      ABALDE Tamara   Spain           Basketball"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d3e4a6ff-7d34-4d47-aa48-c6f7ac7780be\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Name</th>\n",
              "      <th>NOC</th>\n",
              "      <th>Discipline</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AALERUD Katrine</td>\n",
              "      <td>Norway</td>\n",
              "      <td>Cycling Road</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ABAD Nestor</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Artistic Gymnastics</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ABAGNALE Giovanni</td>\n",
              "      <td>Italy</td>\n",
              "      <td>Rowing</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ABALDE Alberto</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Basketball</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ABALDE Tamara</td>\n",
              "      <td>Spain</td>\n",
              "      <td>Basketball</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d3e4a6ff-7d34-4d47-aa48-c6f7ac7780be')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d3e4a6ff-7d34-4d47-aa48-c6f7ac7780be button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d3e4a6ff-7d34-4d47-aa48-c6f7ac7780be');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1kuAiIqp4UQL",
        "outputId": "810a5ae1-5110-4012-de34-4a9ee64471ed"
      },
      "source": [
        "df.info()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 30 entries, 0 to 29\n",
            "Data columns (total 6 columns):\n",
            " #   Column         Non-Null Count  Dtype         \n",
            "---  ------         --------------  -----         \n",
            " 0   GameId         30 non-null     int64         \n",
            " 1   Team1Id        30 non-null     int64         \n",
            " 2   Team2Id        30 non-null     int64         \n",
            " 3   ResultOfTeam1  30 non-null     int64         \n",
            " 4   URL            30 non-null     object        \n",
            " 5   Date           30 non-null     datetime64[ns]\n",
            "dtypes: datetime64[ns](1), int64(4), object(1)\n",
            "memory usage: 1.5+ KB\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xhpgJTAT4UQL"
      },
      "source": [
        "**Lendo apenas algumas colunas da tabela.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3zp4pyLK4UQM"
      },
      "source": [
        "df = pd.read_sql_table('nba_game', engine, columns=[\"Team1Id\",\"Date\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 195
        },
        "id": "LCHTI71W4UQM",
        "outputId": "1ff915a0-f2a1-4645-85f3-b08cd25e7621"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Team1Id</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>7</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>11</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>13</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>15</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Team1Id       Date\n",
              "0        7 2014-03-31\n",
              "1        9 2014-03-31\n",
              "2       11 2014-03-31\n",
              "3       13 2014-03-31\n",
              "4       15 2014-03-31"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I-cuTrEK4UQM"
      },
      "source": [
        "## Método:  read_sql_query"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y9MtEPy24UQN"
      },
      "source": [
        "#### Paramentros do método read_sql_query"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2lOyzXAj4UQN"
      },
      "source": [
        "* **sql** = String SQL Query que deverá ser executada para retornar o conjunto de dados.\n",
        "* **con** = Objeto conexão criado pelo SQLAlchemy.\n",
        "* **index_col** = Coluna a ser definida como index.\n",
        "* **params**= Lista de parametros para serem passados ao método.\n",
        "<br>..."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IqXVeo4p4UQN"
      },
      "source": [
        "#### Criando um DataFrame apartir de uma query ao banco de dados. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KoyL8T0j4UQN"
      },
      "source": [
        "df = pd.read_sql_query(\"select * from nba_game\",engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 195
        },
        "id": "Bnnq62JY4UQO",
        "outputId": "20d0b4ed-f5ec-4c20-c05d-e4e9dfd39ecf"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>GameId</th>\n",
              "      <th>Team1Id</th>\n",
              "      <th>Team2Id</th>\n",
              "      <th>ResultOfTeam1</th>\n",
              "      <th>URL</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>7</td>\n",
              "      <td>8</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/NYKUTA/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>9</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/MEMDEN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>11</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/SACNOP/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>13</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/LACMIN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>15</td>\n",
              "      <td>16</td>\n",
              "      <td>-1</td>\n",
              "      <td>http://www.nba.com/games/20140331/BOSCHI/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   GameId  ...       Date\n",
              "0       1  ... 2014-03-31\n",
              "1       2  ... 2014-03-31\n",
              "2       3  ... 2014-03-31\n",
              "3       4  ... 2014-03-31\n",
              "4       5  ... 2014-03-31\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DaxAtjnv4UQO"
      },
      "source": [
        "#### Criando um DataFrame apartir de uma query ao banco de dados utilizando a coluna emp_no com index."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lN4vFUOQ4UQO"
      },
      "source": [
        "df_index = pd.read_sql_query(\"select * from nba_game\",engine,index_col=\"GameId\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 225
        },
        "id": "JaXSsYIZ4UQO",
        "outputId": "136fb9fb-7d1f-4ba1-c3ba-ee3de1421ed2"
      },
      "source": [
        "df_index.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Team1Id</th>\n",
              "      <th>Team2Id</th>\n",
              "      <th>ResultOfTeam1</th>\n",
              "      <th>URL</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>GameId</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>7</td>\n",
              "      <td>8</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/NYKUTA/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>9</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/MEMDEN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>11</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/SACNOP/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>13</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/LACMIN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>15</td>\n",
              "      <td>16</td>\n",
              "      <td>-1</td>\n",
              "      <td>http://www.nba.com/games/20140331/BOSCHI/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        Team1Id  ...       Date\n",
              "GameId           ...           \n",
              "1             7  ... 2014-03-31\n",
              "2             9  ... 2014-03-31\n",
              "3            11  ... 2014-03-31\n",
              "4            13  ... 2014-03-31\n",
              "5            15  ... 2014-03-31\n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mgG22pG-4UQP"
      },
      "source": [
        "#### Criando um DataFrame apartir de uma query ligando várias tabelas."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "TUyZ_P5p4UQP"
      },
      "source": [
        "query = '''\n",
        "SELECT  play.PlayerId,\n",
        "        play.PlayerName,\n",
        "        sum(act.Points) as Score,\n",
        "        gam.Date\n",
        "FROM nba_player play\n",
        "INNER JOIN nba_actions act\n",
        "ON play.PlayerId = act.PlayerId\n",
        "INNER JOIN nba_game gam\n",
        "ON act.GameId = gam.GameId\n",
        "GROUP BY play.PlayerId;\n",
        "'''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XsYfxURT4UQP"
      },
      "source": [
        "df = pd.read_sql_query(query,engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 195
        },
        "id": "6-dXs_PW4UQP",
        "outputId": "6ef4357c-77a9-4ebf-821a-e17040b88ed4"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PlayerId</th>\n",
              "      <th>PlayerName</th>\n",
              "      <th>Score</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Nicolas Batum</td>\n",
              "      <td>16.0</td>\n",
              "      <td>2014-04-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>LaMarcus Aldridge</td>\n",
              "      <td>31.0</td>\n",
              "      <td>2014-04-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Robin Lopez</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2014-04-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Wesley Matthews</td>\n",
              "      <td>10.0</td>\n",
              "      <td>2014-04-01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Damian Lillard</td>\n",
              "      <td>34.0</td>\n",
              "      <td>2014-04-01</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   PlayerId         PlayerName  Score       Date\n",
              "0         1      Nicolas Batum   16.0 2014-04-01\n",
              "1         2  LaMarcus Aldridge   31.0 2014-04-01\n",
              "2         3        Robin Lopez   10.0 2014-04-01\n",
              "3         4    Wesley Matthews   10.0 2014-04-01\n",
              "4         5     Damian Lillard   34.0 2014-04-01"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4jWO7vS84UQQ"
      },
      "source": [
        "#### Criando um DataFrame apartir de uma query utilizando parametros dinâmicos."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vlsMCHJe4UQQ"
      },
      "source": [
        "query = 'SELECT PlayerId, PlayerName ' \\\n",
        "      'FROM nba_player ' \\\n",
        "      'WHERE PlayerName = %s'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "A9VFeMNP4UQQ"
      },
      "source": [
        "df = pd.read_sql_query(query, engine,params=[\"Wesley Matthews\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 77
        },
        "id": "mPzSqvZJ4UQR",
        "outputId": "ff54a8cc-8463-49fc-9851-a822fdaad0f4"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PlayerId</th>\n",
              "      <th>PlayerName</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>4</td>\n",
              "      <td>Wesley Matthews</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   PlayerId       PlayerName\n",
              "0         4  Wesley Matthews"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YDyE9xRF4UQR"
      },
      "source": [
        "#### Criando um DataFrame apartir de uma query utilizando parametros nomeados."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "biIg11TF4UQS"
      },
      "source": [
        "query = '''\n",
        "SELECT  play.PlayerId,\n",
        "        play.PlayerName,\n",
        "        sum(act.Points) as Score,\n",
        "        gam.Date\n",
        "FROM nba_player play\n",
        "INNER JOIN nba_actions act\n",
        "ON play.PlayerId = act.PlayerId\n",
        "INNER JOIN nba_game gam\n",
        "ON act.GameId = gam.GameId\n",
        "WHERE gam.Date >= %(data_inicial)s AND play.PlayerName = %(nome)s\n",
        "GROUP BY play.PlayerId;\n",
        "'''\n",
        "\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "osXuCXjVzUED"
      },
      "source": [
        "import http"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Mq0mh9Fp4UQS"
      },
      "source": [
        "df = pd.read_sql_query(query, \n",
        "                       engine,\n",
        "                       params={'data_inicial':\"2014-04-02 00:00:00\",\n",
        "                                'nome':\"Hidayet Turkoglu\"}\n",
        "                      )"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 77
        },
        "id": "QBZQETny4UQS",
        "outputId": "479d556d-ed0e-4b53-c687-5fbd57e8a465"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>PlayerId</th>\n",
              "      <th>PlayerName</th>\n",
              "      <th>Score</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>169</td>\n",
              "      <td>Hidayet Turkoglu</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2014-04-02</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   PlayerId        PlayerName  Score       Date\n",
              "0       169  Hidayet Turkoglu    2.0 2014-04-02"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YGTY_ahF4UQT"
      },
      "source": [
        "## Método:  read_sql"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A2y8pnOY4UQT"
      },
      "source": [
        "#### Faz o roteamento entre os métodos read_table e read_sql_query."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8OCbxuAI4UQT"
      },
      "source": [
        "#### Paramentros do método read_sql."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CKF1S-sK4UQT"
      },
      "source": [
        "* **sql** = String SQL Query que deverá ser executada para retornar o conjunto de dados.\n",
        "* **con** = Objeto conexão criado pelo SQLAlchemy.\n",
        "* **index_col** = Coluna a ser definida como index.\n",
        "* **params**= Lista de parametros para serem passados ao método.\n",
        "<br>..."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2E5a9L-H4UQU"
      },
      "source": [
        "#### Criando o DataFrame passando apenas o nome da tabela para o método."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tHqr1Snd4UQU"
      },
      "source": [
        "df = pd.read_sql(\"nba_game\",engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 195
        },
        "id": "vItCvwAF4UQU",
        "outputId": "c290132c-8f85-4b68-ac84-21fa19cc3b98"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>GameId</th>\n",
              "      <th>Team1Id</th>\n",
              "      <th>Team2Id</th>\n",
              "      <th>ResultOfTeam1</th>\n",
              "      <th>URL</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>7</td>\n",
              "      <td>8</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/NYKUTA/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>9</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/MEMDEN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>11</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/SACNOP/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>13</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/LACMIN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>15</td>\n",
              "      <td>16</td>\n",
              "      <td>-1</td>\n",
              "      <td>http://www.nba.com/games/20140331/BOSCHI/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   GameId  ...       Date\n",
              "0       1  ... 2014-03-31\n",
              "1       2  ... 2014-03-31\n",
              "2       3  ... 2014-03-31\n",
              "3       4  ... 2014-03-31\n",
              "4       5  ... 2014-03-31\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jywPRAzo4UQU"
      },
      "source": [
        "#### Criando o DataFrame através de uma query."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zwyHq24j4UQV"
      },
      "source": [
        "df = pd.read_sql(\"SELECT * FROM nba_game\",engine)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 299
        },
        "id": "9WEDH2Z74UQV",
        "outputId": "77e37d7b-e9c7-4981-8787-6969ac4a924e"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>GameId</th>\n",
              "      <th>Team1Id</th>\n",
              "      <th>Team2Id</th>\n",
              "      <th>ResultOfTeam1</th>\n",
              "      <th>URL</th>\n",
              "      <th>Date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>7</td>\n",
              "      <td>8</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/NYKUTA/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>9</td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/MEMDEN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>11</td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/SACNOP/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>13</td>\n",
              "      <td>14</td>\n",
              "      <td>1</td>\n",
              "      <td>http://www.nba.com/games/20140331/LACMIN/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>15</td>\n",
              "      <td>16</td>\n",
              "      <td>-1</td>\n",
              "      <td>http://www.nba.com/games/20140331/BOSCHI/gamei...</td>\n",
              "      <td>2014-03-31</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   GameId  ...       Date\n",
              "0       1  ... 2014-03-31\n",
              "1       2  ... 2014-03-31\n",
              "2       3  ... 2014-03-31\n",
              "3       4  ... 2014-03-31\n",
              "4       5  ... 2014-03-31\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 40
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F8m5MZ0x4UQV"
      },
      "source": [
        "## Método:  to_sql"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KARMbO2E4UQV"
      },
      "source": [
        "#### Escreve o DataFrame para o Banco de Dados Relacional."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8_tfjwwX4UQV"
      },
      "source": [
        "#### Carregando a base de dados e criando o DataFrame."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4dxPRzmwfKcp",
        "outputId": "d646759f-99f5-4cd3-b7ba-c2d2dbc6c3ae"
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "Nzi-q9Ty4UQV"
      },
      "source": [
        "df = pd.read_excel(\"/content/drive/MyDrive/Colab Notebooks/DS/materiais-de-apoio-pandas-sql-banco-dados/Teams.xlsx\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "id": "Z5r1PHVs4UQW",
        "outputId": "ac14b55d-26a1-4860-c36e-8a50c4ef46a2"
      },
      "source": [
        "df.head(20)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             Name      Discipline                         NOC         Event\n",
              "0         Belgium  3x3 Basketball                     Belgium           Men\n",
              "1           China  3x3 Basketball  People's Republic of China           Men\n",
              "2           China  3x3 Basketball  People's Republic of China         Women\n",
              "3          France  3x3 Basketball                      France         Women\n",
              "4           Italy  3x3 Basketball                       Italy         Women\n",
              "5           Japan  3x3 Basketball                       Japan           Men\n",
              "6           Japan  3x3 Basketball                       Japan         Women\n",
              "7          Latvia  3x3 Basketball                      Latvia           Men\n",
              "8        Mongolia  3x3 Basketball                    Mongolia         Women\n",
              "9     Netherlands  3x3 Basketball                 Netherlands           Men\n",
              "10         Poland  3x3 Basketball                      Poland           Men\n",
              "11            ROC  3x3 Basketball                         ROC           Men\n",
              "12            ROC  3x3 Basketball                         ROC         Women\n",
              "13        Romania  3x3 Basketball                     Romania         Women\n",
              "14         Serbia  3x3 Basketball                      Serbia           Men\n",
              "15  United States  3x3 Basketball    United States of America         Women\n",
              "16      Australia         Archery                   Australia    Men's Team\n",
              "17      Australia         Archery                   Australia    Mixed Team\n",
              "18     Bangladesh         Archery                  Bangladesh    Mixed Team\n",
              "19        Belarus         Archery                     Belarus  Women's Team"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-6b975678-bff1-4bde-b4a8-757731a7dee4\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Name</th>\n",
              "      <th>Discipline</th>\n",
              "      <th>NOC</th>\n",
              "      <th>Event</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Belgium</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Belgium</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>China</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>People's Republic of China</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>China</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>People's Republic of China</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>France</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>France</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Italy</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Italy</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Japan</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Japan</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Japan</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Japan</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Latvia</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Latvia</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Mongolia</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Mongolia</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Netherlands</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Netherlands</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Poland</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Poland</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>ROC</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>ROC</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>ROC</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>ROC</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Romania</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Romania</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Serbia</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>Serbia</td>\n",
              "      <td>Men</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>United States</td>\n",
              "      <td>3x3 Basketball</td>\n",
              "      <td>United States of America</td>\n",
              "      <td>Women</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Australia</td>\n",
              "      <td>Archery</td>\n",
              "      <td>Australia</td>\n",
              "      <td>Men's Team</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Australia</td>\n",
              "      <td>Archery</td>\n",
              "      <td>Australia</td>\n",
              "      <td>Mixed Team</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>Bangladesh</td>\n",
              "      <td>Archery</td>\n",
              "      <td>Bangladesh</td>\n",
              "      <td>Mixed Team</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>Belarus</td>\n",
              "      <td>Archery</td>\n",
              "      <td>Belarus</td>\n",
              "      <td>Women's Team</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-6b975678-bff1-4bde-b4a8-757731a7dee4')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-6b975678-bff1-4bde-b4a8-757731a7dee4 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-6b975678-bff1-4bde-b4a8-757731a7dee4');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eKnmjkHy4UQW"
      },
      "source": [
        "#### Paramentros do método to_sql."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DOnRT8KQ4UQW"
      },
      "source": [
        "* **name** = Nome da tabela que será criada no SGBD.\n",
        "* **con** = Objeto conexão criado pelo SQLAlchemy.\n",
        "* **schema** = Nome do Schema onde será criada a tabela.\n",
        "* **if_exists**= Comportamento caso a tabela exista no SGBD.\n",
        "* **index**= Escreve o índice do DataFrame como uma coluna da tabela.\n",
        "* **index_label**= Nome da coluna de índice."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jm5V9J1A4UQW"
      },
      "source": [
        "**Criando a tabela no Banco de Dados a partir do DataFrame.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2-OxBQuZ4UQX"
      },
      "source": [
        "df.to_sql(\n",
        "    name = 'ocorrencia',\n",
        "    con = engine,\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LXb8Ylc_4UQX"
      },
      "source": [
        "**Criando a tabela no Banco de Dados sem a coluna de índice.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EIqgmWeD4UQX"
      },
      "source": [
        "df.to_sql(\n",
        "    name = 'Teams',\n",
        "    con = engine,\n",
        "    index = False,\n",
        "    if_exists ='append'\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}