connect_to_database.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "connect_to_database.ipynb",
      "private_outputs": true,
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/LUMC/EfDS_RelDB_SQL/blob/main/connect_to_database.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Downloading the example database and connecting to it"
      ],
      "metadata": {
        "id": "rICKfoocWDl-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Packages needed for this project"
      ],
      "metadata": {
        "id": "S63MdwhqdDjo"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lANdl3_cV0dL"
      },
      "outputs": [],
      "source": [
        "import urllib.request    # needed for download of the example database\n",
        "import shutil            # needed for unziping of the example database\n",
        "\n",
        "from sqlalchemy import * # package providing unified access to various databases\n",
        "\n",
        "import pandas as pd      # for better table visualisation"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Downloading of the example database\n",
        "\n"
      ],
      "metadata": {
        "id": "a1IVRGNfaRtY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The SQLite tutorial example database is provided as a `zip` archive in `chinook.zip` file. Download it from the [SQLite tutorial web site](https://www.sqlitetutorial.net) (for example using the following Python command):  "
      ],
      "metadata": {
        "id": "rQ-8RU7YWvoa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "urllib.request.urlretrieve(\"https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip\", \"chinook.zip\")"
      ],
      "metadata": {
        "id": "HYx5ecCCV9at"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "*Checkpoint:* The current directory should contain now `chinook.zip` file. Next, unpack the `zip` archive (for example using Python the following Python command):"
      ],
      "metadata": {
        "id": "Cl35eRier4tY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "shutil.unpack_archive(\"chinook.zip\")"
      ],
      "metadata": {
        "id": "XO47sQFhr4QN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "*Checkpoint:* The current directory should contain now `chinook.db` file. This is the example database in SQLite format."
      ],
      "metadata": {
        "id": "EL-8SqxSsb7c"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Connect to the database and execute an SQL query"
      ],
      "metadata": {
        "id": "BFNVCOCqSFdV"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following text:\n",
        "\n",
        "> SELECT * FROM albums LIMIT 5\n",
        "\n",
        "is one of the simplest possible queries in SQL. It requests to provide all (`*`) columns and `5` rows from the database table `albums`. \n",
        "\n",
        "In a Python notebook there are several ways to connect to a database and execute the query. These methods differ in type of the object used to return the query result. Study the following examples."
      ],
      "metadata": {
        "id": "9R-K87ray1Sv"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Using SQLAlchemy engine object"
      ],
      "metadata": {
        "id": "sEfvAqozSXz2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Open the connection to the SQLite database in the `chinook.db` file by creating the database `engine` connector object as follows:"
      ],
      "metadata": {
        "id": "Cl9AvfYxaEUM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# To get the engine object import create_engine function from SQLAlchemy package:\n",
        "# \n",
        "# from sqlalchemy import create_engine \n",
        "#\n",
        "engine = create_engine(\"sqlite:///chinook.db\")"
      ],
      "metadata": {
        "id": "N8dC-SBSagfc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Using the following code you may send an SQL query `sql` to the database through the `engine` connector.  \n",
        "In the `arr` variable you will get the resulting table provided as a list of tuples."
      ],
      "metadata": {
        "id": "9BMBlwcCq_BH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "sql = text(\"SELECT * FROM albums LIMIT 5\")\n",
        "arr = engine.execute(sql).fetchall()\n",
        "arr"
      ],
      "metadata": {
        "id": "ByEJ5p4MqbYi"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following code will execute the same `sql` query through the `engine` but the result will be provided as a Panda's `DataFrame` object in the variable `df`."
      ],
      "metadata": {
        "id": "9EvD-rQ4qQpH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# For this method import pandas package:\n",
        "#\n",
        "# import pandas as pd\n",
        "#\n",
        "sql = text(\"SELECT * FROM albums LIMIT 5\")\n",
        "df = pd.read_sql(sql, engine)\n",
        "df"
      ],
      "metadata": {
        "id": "5rpAhpp7dqQ7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Using Python magic connector"
      ],
      "metadata": {
        "id": "-sHoQ3NXx2--"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "When the Python script works with a single database only and extra Python language extensions are allowed the following notation might be used. The following code creates the database engine connector object in a hidden variable and allows for the magic `%sql` to be used in the code chunks."
      ],
      "metadata": {
        "id": "1qOYal2pyLW-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%load_ext sql\n",
        "%sql sqlite:///chinook.db"
      ],
      "metadata": {
        "id": "uxnr5PHPw5cA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Using the magic sql connector this is a short way to execute a directly typed single line SQL query and print the result:"
      ],
      "metadata": {
        "id": "IYFtLDy1xW5g"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%sql SELECT * FROM albums LIMIT 5"
      ],
      "metadata": {
        "id": "GY6Pi6j1zRFh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following code may be used to convert the result to Panda's `DataFrame`:"
      ],
      "metadata": {
        "id": "M3GnpwvEVPcm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "res = %sql SELECT * FROM albums LIMIT 5\n",
        "df = res.DataFrame()\n",
        "df"
      ],
      "metadata": {
        "id": "3rwT47svvS9e"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "To print results of longer SQL commands which do not fit in a single line use double-percent notation `%%sql` as below:"
      ],
      "metadata": {
        "id": "qq9v_hmPAHa5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%sql\n",
        "SELECT trackid, composer, unitprice FROM tracks LIMIT 5"
      ],
      "metadata": {
        "id": "szQ147kf5Yb7"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}