09_sql/connect_to_database.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rICKfoocWDl-"
      },
      "source": [
        "# Downloading the example database and connecting to it"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Packages needed for this project"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import urllib.request    # needed for download of the example database\n",
        "import shutil            # needed for unzipping of the example database\n",
        "\n",
        "import sqlalchemy as sa\n",
        "import pandas as pd      # for better table visualisation"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "\n",
        "### Note on package problems\n",
        "\n",
        "Currently there exists an incompatibility between:\n",
        "- the latest version (>=2.0) of `sqlalchemy` package,\n",
        "- the package `ipython-sql` which provides the `%sql` magic keyword used in the examples below.  \n",
        "\n",
        "This incompatibility causes the `%sql` magic command to fail.  \n",
        "A current workaround (which probably will not be necessary in the future) is to install the `jupysql` package (which also downgrades `sqlalchemy` to an older version (1.x.y)).\n",
        "\n",
        "If you encounter problems with `%sql` try the following (and remember to restart your python kernel):\n",
        "\n",
        "```python\n",
        "!pip install jupysql\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "This is how you can check the versions of your packages:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "pd.__version__ # Not guaranteed to be fully portable, but works for now."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "sa.__version__ # Not guaranteed to be fully portable, but works for now."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a1IVRGNfaRtY"
      },
      "source": [
        "## Downloading of the example database\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rQ-8RU7YWvoa"
      },
      "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/sqlite-sample-database/) (for example using the following Python command):  "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HYx5ecCCV9at"
      },
      "outputs": [],
      "source": [
        "#urllib.request.urlretrieve(\"https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip\", \"chinook.zip\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cl35eRier4tY"
      },
      "source": [
        "*Checkpoint:* The current directory should contain now `chinook.zip` file. Next, unpack the `zip` archive (for example using Python the following Python command):"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XO47sQFhr4QN"
      },
      "outputs": [],
      "source": [
        "shutil.unpack_archive(\"chinook.zip\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EL-8SqxSsb7c"
      },
      "source": [
        "*Checkpoint:* The current directory should contain now `chinook.db` file. This is the example database in SQLite format."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BFNVCOCqSFdV"
      },
      "source": [
        "## Connect to the database and execute an SQL query"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "9R-K87ray1Sv"
      },
      "source": [
        "The following text:\n",
        "\n",
        "```sql\n",
        "SELECT * FROM albums LIMIT 5\n",
        "```\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."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sEfvAqozSXz2"
      },
      "source": [
        "### Using SQLAlchemy engine object"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "Cl9AvfYxaEUM"
      },
      "source": [
        "Open the connection to the SQLite database in the `chinook.db` file by creating the database `engine` object as follows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "engine = sa.create_engine(\"sqlite:///chinook.db\")"
      ]
    },
    {
      "attachments": {},
      "cell_type": "markdown",
      "metadata": {
        "id": "9BMBlwcCq_BH"
      },
      "source": [
        "Using the following code you may send an SQL query `sql` to the database through the `engine`.  \n",
        "In the `arr` variable you will get the resulting table provided as a list of tuples."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "sql = sa.text(\"SELECT * FROM albums LIMIT 5\")\n",
        "with engine.connect() as conn:\n",
        "    arr = conn.execute(sql).fetchall()\n",
        "arr"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "sql = sa.text(\"SELECT * FROM albums LIMIT 5\")\n",
        "df = pd.read_sql(sql, con=engine)\n",
        "df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-sHoQ3NXx2--"
      },
      "source": [
        "### Using Python magic connector"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1qOYal2pyLW-"
      },
      "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."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uxnr5PHPw5cA"
      },
      "outputs": [],
      "source": [
        "%load_ext sql\n",
        "%sql sqlite:///chinook.db"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IYFtLDy1xW5g"
      },
      "source": [
        "Using the magic sql connector this is a short way to execute a directly typed single line SQL query and print the result:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GY6Pi6j1zRFh"
      },
      "outputs": [],
      "source": [
        "%sql SELECT * FROM albums LIMIT 5"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "M3GnpwvEVPcm"
      },
      "source": [
        "The following code may be used to convert the result to Panda's `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3rwT47svvS9e"
      },
      "outputs": [],
      "source": [
        "res = %sql SELECT * FROM albums LIMIT 5\n",
        "df = res.DataFrame()\n",
        "df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qq9v_hmPAHa5"
      },
      "source": [
        "To print results of longer SQL commands which do not fit in a single line use double-percent notation `%%sql` as below:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "szQ147kf5Yb7"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "SELECT trackid, composer, unitprice FROM tracks LIMIT 5"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "include_colab_link": true,
      "name": "connect_to_database.ipynb",
      "private_outputs": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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.12.1"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}