orm_1_create_database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The European Transaction Log (EUTL) is the backbone of the European Union Emissions Trading System (EUETS). It implements the transfer of emission allowances between parties active in the EUETS. The EUTL provides data on regulated installations, their emissions as well as transfers of allowances. Provided data extracted from the EUTL (see <a href=\"https://euets.info/background\">EUETS.INFO</a>), this notebook builds a postgres database to easily access the EUTL data based on a Object Relation Mapper (ORM). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prerequisits"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use a postgres database and assume that an empty local database exists. If the\n",
    "database does not exists, a new database will be created. If it exists and is \n",
    "already populated, the existing tables will be deleted.\n",
    "\n",
    "By default, we assume that a user *postgres* is used with the password *password*.\n",
    "You could however use every user that you created provided that it has the sufficient\n",
    "privileges (create database and write data to create the database and read data to\n",
    "do analytics).\n",
    "\n",
    "To be able to create the database, you have to download and install Postgres \n",
    "from https://www.postgresql.org/. During the installation process you will be asked\n",
    "for a password which will be the password of your *postgres* user. To use the default\n",
    "case, use *password* as password.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up your python environment using poetry. To do that, use the terminal\n",
    "to navigate to main directory (the one that also includes the main file) and run:\n",
    "\n",
    "```poetry install ```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Packages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The DataAccessLayer provides access to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyeutl.orm import DataAccessLayer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to specify\n",
    "\n",
    "1. Settings to access the database\n",
    "2. Path to the zip-file containing the eutl data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# database connection settings\n",
    "connectionSettings = dict(\n",
    "    user=\"postgres\", \n",
    "    host=\"localhost\", \n",
    "    db=\"eutl2024\", \n",
    "    passw=\"password\",\n",
    "    port=5432\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, create the database access layer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "dal = DataAccessLayer(**connectionSettings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Second, we create and populate the database.\n",
    "\n",
    "You can download the data from euets.info and provide the file address to the \n",
    "*create_database* function. If you do not do that, the data will be downloaded \n",
    "automatically (and deleted after database creation). If the database is already \n",
    "populated, all existing data are deleted (and you have to confirm this).\n",
    "\n",
    "**Note:** This can take up to about 10 minutes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No source file provided. Download data from euets.info\n",
      "0 transaction\n",
      "1 surrender\n",
      "2 compliance\n",
      "3 account\n",
      "4 offset_project\n",
      "5 installation\n",
      "6 account_holder\n",
      "7 unit_type\n",
      "8 transaction_type_supplementary_code\n",
      "9 transaction_type_main_code\n",
      "10 trading_system_code\n",
      "11 nace_code\n",
      "12 country_code\n",
      "13 compliance_code\n",
      "14 activity_type_code\n",
      "15 account_type_code\n",
      "Tables deleted\n",
      "---- Insert lookup tables\n",
      "---- Insert offset projects\n",
      "---- Insert installations\n",
      "---- Insert compliance data\n",
      "---- Insert surrendering data\n",
      "---- Insert account holders\n",
      "---- Insert accounts\n",
      "---- Insert transactions\n"
     ]
    }
   ],
   "source": [
    "dal.create_database()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.11.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}