setup_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "02c95903-77f8-4bba-bc34-6200f120cfcd",
   "metadata": {},
   "source": "# Database Setup"
  },
  {
   "cell_type": "markdown",
   "id": "e27a0e05-1a56-44e0-8217-cf538560d987",
   "metadata": {},
   "source": "- Das Notebook erstellt die Datenbank und beläd diese mit den Demo Daten"
  },
  {
   "cell_type": "markdown",
   "id": "91c13d73-b455-4c2f-9f1b-5dfb783e355b",
   "metadata": {},
   "source": [
    "## Alle nötigen Libraries installieren und importieren"
   ]
  },
  {
   "cell_type": "code",
   "id": "c0e383b2-85a2-43a3-90c2-cb713eda5aef",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:08.226905Z",
     "start_time": "2024-08-08T11:44:04.011452Z"
    }
   },
   "source": [
    "!pip install soda-core==3.3.5\n",
    "!pip install soda-core-postgres==3.3.5\n",
    "!pip install pandas==2.2.2\n",
    "!pip install psycopg2-binary==2.9.9\n",
    "!pip install requests==2.32.3\n",
    "!pip install matplotlib==3.9.1"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: soda-core==3.3.5 in ./venv/lib/python3.12/site-packages (3.3.5)\r\n",
      "Requirement already satisfied: markupsafe<=2.1.2,>=2.0.1 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (2.1.2)\r\n",
      "Requirement already satisfied: Jinja2<4.0,>=2.11 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (3.1.4)\r\n",
      "Requirement already satisfied: click~=8.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (8.1.7)\r\n",
      "Requirement already satisfied: ruamel.yaml<0.18.0,>=0.17.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (0.17.40)\r\n",
      "Requirement already satisfied: requests~=2.28 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (2.32.3)\r\n",
      "Requirement already satisfied: antlr4-python3-runtime~=4.11.1 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (4.11.1)\r\n",
      "Requirement already satisfied: opentelemetry-api<1.23.0,>=1.16.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: sqlparse~=0.4 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (0.5.1)\r\n",
      "Requirement already satisfied: inflect~=7.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (7.3.1)\r\n",
      "Requirement already satisfied: pydantic<3.0.0,>=2.0.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5) (2.8.2)\r\n",
      "Requirement already satisfied: more-itertools>=8.5.0 in ./venv/lib/python3.12/site-packages (from inflect~=7.0->soda-core==3.3.5) (10.3.0)\r\n",
      "Requirement already satisfied: typeguard>=4.0.1 in ./venv/lib/python3.12/site-packages (from inflect~=7.0->soda-core==3.3.5) (4.3.0)\r\n",
      "Requirement already satisfied: deprecated>=1.2.6 in ./venv/lib/python3.12/site-packages (from opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5) (1.2.14)\r\n",
      "Requirement already satisfied: importlib-metadata<7.0,>=6.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5) (6.11.0)\r\n",
      "Requirement already satisfied: backoff<3.0.0,>=1.10.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (2.2.1)\r\n",
      "Requirement already satisfied: googleapis-common-protos~=1.52 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (1.63.2)\r\n",
      "Requirement already satisfied: opentelemetry-exporter-otlp-proto-common==1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-proto==1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-sdk~=1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: protobuf<5.0,>=3.19 in ./venv/lib/python3.12/site-packages (from opentelemetry-proto==1.22.0->opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (4.25.4)\r\n",
      "Requirement already satisfied: annotated-types>=0.4.0 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5) (0.7.0)\r\n",
      "Requirement already satisfied: pydantic-core==2.20.1 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5) (2.20.1)\r\n",
      "Requirement already satisfied: typing-extensions>=4.6.1 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5) (4.12.2)\r\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5) (3.3.2)\r\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5) (3.7)\r\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5) (2.2.2)\r\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5) (2024.7.4)\r\n",
      "Requirement already satisfied: ruamel.yaml.clib>=0.2.7 in ./venv/lib/python3.12/site-packages (from ruamel.yaml<0.18.0,>=0.17.0->soda-core==3.3.5) (0.2.8)\r\n",
      "Requirement already satisfied: wrapt<2,>=1.10 in ./venv/lib/python3.12/site-packages (from deprecated>=1.2.6->opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5) (1.16.0)\r\n",
      "Requirement already satisfied: zipp>=0.5 in ./venv/lib/python3.12/site-packages (from importlib-metadata<7.0,>=6.0->opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5) (3.19.2)\r\n",
      "Requirement already satisfied: opentelemetry-semantic-conventions==0.43b0 in ./venv/lib/python3.12/site-packages (from opentelemetry-sdk~=1.22.0->opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5) (0.43b0)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n",
      "Requirement already satisfied: soda-core-postgres==3.3.5 in ./venv/lib/python3.12/site-packages (3.3.5)\r\n",
      "Requirement already satisfied: soda-core==3.3.5 in ./venv/lib/python3.12/site-packages (from soda-core-postgres==3.3.5) (3.3.5)\r\n",
      "Requirement already satisfied: psycopg2-binary<3.0,>=2.8.5 in ./venv/lib/python3.12/site-packages (from soda-core-postgres==3.3.5) (2.9.9)\r\n",
      "Requirement already satisfied: markupsafe<=2.1.2,>=2.0.1 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (2.1.2)\r\n",
      "Requirement already satisfied: Jinja2<4.0,>=2.11 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (3.1.4)\r\n",
      "Requirement already satisfied: click~=8.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (8.1.7)\r\n",
      "Requirement already satisfied: ruamel.yaml<0.18.0,>=0.17.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (0.17.40)\r\n",
      "Requirement already satisfied: requests~=2.28 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (2.32.3)\r\n",
      "Requirement already satisfied: antlr4-python3-runtime~=4.11.1 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (4.11.1)\r\n",
      "Requirement already satisfied: opentelemetry-api<1.23.0,>=1.16.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: sqlparse~=0.4 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (0.5.1)\r\n",
      "Requirement already satisfied: inflect~=7.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (7.3.1)\r\n",
      "Requirement already satisfied: pydantic<3.0.0,>=2.0.0 in ./venv/lib/python3.12/site-packages (from soda-core==3.3.5->soda-core-postgres==3.3.5) (2.8.2)\r\n",
      "Requirement already satisfied: more-itertools>=8.5.0 in ./venv/lib/python3.12/site-packages (from inflect~=7.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (10.3.0)\r\n",
      "Requirement already satisfied: typeguard>=4.0.1 in ./venv/lib/python3.12/site-packages (from inflect~=7.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (4.3.0)\r\n",
      "Requirement already satisfied: deprecated>=1.2.6 in ./venv/lib/python3.12/site-packages (from opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.2.14)\r\n",
      "Requirement already satisfied: importlib-metadata<7.0,>=6.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (6.11.0)\r\n",
      "Requirement already satisfied: backoff<3.0.0,>=1.10.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (2.2.1)\r\n",
      "Requirement already satisfied: googleapis-common-protos~=1.52 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.63.2)\r\n",
      "Requirement already satisfied: opentelemetry-exporter-otlp-proto-common==1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-proto==1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: opentelemetry-sdk~=1.22.0 in ./venv/lib/python3.12/site-packages (from opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.22.0)\r\n",
      "Requirement already satisfied: protobuf<5.0,>=3.19 in ./venv/lib/python3.12/site-packages (from opentelemetry-proto==1.22.0->opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (4.25.4)\r\n",
      "Requirement already satisfied: annotated-types>=0.4.0 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (0.7.0)\r\n",
      "Requirement already satisfied: pydantic-core==2.20.1 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (2.20.1)\r\n",
      "Requirement already satisfied: typing-extensions>=4.6.1 in ./venv/lib/python3.12/site-packages (from pydantic<3.0.0,>=2.0.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (4.12.2)\r\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5->soda-core-postgres==3.3.5) (3.3.2)\r\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5->soda-core-postgres==3.3.5) (3.7)\r\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5->soda-core-postgres==3.3.5) (2.2.2)\r\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./venv/lib/python3.12/site-packages (from requests~=2.28->soda-core==3.3.5->soda-core-postgres==3.3.5) (2024.7.4)\r\n",
      "Requirement already satisfied: ruamel.yaml.clib>=0.2.7 in ./venv/lib/python3.12/site-packages (from ruamel.yaml<0.18.0,>=0.17.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (0.2.8)\r\n",
      "Requirement already satisfied: wrapt<2,>=1.10 in ./venv/lib/python3.12/site-packages (from deprecated>=1.2.6->opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (1.16.0)\r\n",
      "Requirement already satisfied: zipp>=0.5 in ./venv/lib/python3.12/site-packages (from importlib-metadata<7.0,>=6.0->opentelemetry-api<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (3.19.2)\r\n",
      "Requirement already satisfied: opentelemetry-semantic-conventions==0.43b0 in ./venv/lib/python3.12/site-packages (from opentelemetry-sdk~=1.22.0->opentelemetry-exporter-otlp-proto-http<1.23.0,>=1.16.0->soda-core==3.3.5->soda-core-postgres==3.3.5) (0.43b0)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n",
      "Requirement already satisfied: pandas==2.2.2 in ./venv/lib/python3.12/site-packages (2.2.2)\r\n",
      "Requirement already satisfied: numpy>=1.26.0 in ./venv/lib/python3.12/site-packages (from pandas==2.2.2) (2.0.1)\r\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in ./venv/lib/python3.12/site-packages (from pandas==2.2.2) (2.9.0.post0)\r\n",
      "Requirement already satisfied: pytz>=2020.1 in ./venv/lib/python3.12/site-packages (from pandas==2.2.2) (2024.1)\r\n",
      "Requirement already satisfied: tzdata>=2022.7 in ./venv/lib/python3.12/site-packages (from pandas==2.2.2) (2024.1)\r\n",
      "Requirement already satisfied: six>=1.5 in ./venv/lib/python3.12/site-packages (from python-dateutil>=2.8.2->pandas==2.2.2) (1.16.0)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n",
      "Requirement already satisfied: psycopg2-binary==2.9.9 in ./venv/lib/python3.12/site-packages (2.9.9)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n",
      "Requirement already satisfied: requests==2.32.3 in ./venv/lib/python3.12/site-packages (2.32.3)\r\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in ./venv/lib/python3.12/site-packages (from requests==2.32.3) (3.3.2)\r\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./venv/lib/python3.12/site-packages (from requests==2.32.3) (3.7)\r\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./venv/lib/python3.12/site-packages (from requests==2.32.3) (2.2.2)\r\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./venv/lib/python3.12/site-packages (from requests==2.32.3) (2024.7.4)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n",
      "Requirement already satisfied: matplotlib==3.9.1 in ./venv/lib/python3.12/site-packages (3.9.1)\r\n",
      "Requirement already satisfied: contourpy>=1.0.1 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (1.2.1)\r\n",
      "Requirement already satisfied: cycler>=0.10 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (0.12.1)\r\n",
      "Requirement already satisfied: fonttools>=4.22.0 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (4.53.1)\r\n",
      "Requirement already satisfied: kiwisolver>=1.3.1 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (1.4.5)\r\n",
      "Requirement already satisfied: numpy>=1.23 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (2.0.1)\r\n",
      "Requirement already satisfied: packaging>=20.0 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (24.1)\r\n",
      "Requirement already satisfied: pillow>=8 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (10.4.0)\r\n",
      "Requirement already satisfied: pyparsing>=2.3.1 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (3.1.2)\r\n",
      "Requirement already satisfied: python-dateutil>=2.7 in ./venv/lib/python3.12/site-packages (from matplotlib==3.9.1) (2.9.0.post0)\r\n",
      "Requirement already satisfied: six>=1.5 in ./venv/lib/python3.12/site-packages (from python-dateutil>=2.7->matplotlib==3.9.1) (1.16.0)\r\n",
      "\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m A new release of pip is available: \u001B[0m\u001B[31;49m23.2.1\u001B[0m\u001B[39;49m -> \u001B[0m\u001B[32;49m24.2\u001B[0m\r\n",
      "\u001B[1m[\u001B[0m\u001B[34;49mnotice\u001B[0m\u001B[1;39;49m]\u001B[0m\u001B[39;49m To update, run: \u001B[0m\u001B[32;49mpip install --upgrade pip\u001B[0m\r\n"
     ]
    }
   ],
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "id": "12cc2141-8f01-4816-bb30-b0d9b9394035",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:08.460395Z",
     "start_time": "2024-08-08T11:44:08.228146Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "import psycopg2\n",
    "from psycopg2 import sql\n",
    "import requests\n",
    "from io import StringIO"
   ],
   "outputs": [],
   "execution_count": 5
  },
  {
   "cell_type": "markdown",
   "id": "1fffd613-0301-4b8a-98da-39b042fed6ba",
   "metadata": {},
   "source": [
    "## Start des Postgres Server (siehe auch READM.md)\n",
    "\n",
    "- Führe PostgreSQL Server mit Docker aus:\n",
    "  ```\n",
    "  sudo docker run --name postgresql1 -e POSTGRES_PASSWORD=test -v ${HOME}/postgres-data/:/var/lib/postgresql/data -p 5432:5432 -d postgres:15.2\n",
    "  ```\n"
   ]
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Dowload der Demo Daten",
   "id": "270565306bbc5597"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:14.196587Z",
     "start_time": "2024-08-08T11:44:08.460941Z"
    }
   },
   "cell_type": "code",
   "source": [
    "CSV_URL = \"https://raw.githubusercontent.com/sodadata/tutorial-demo-project/main/data/ny_bus_breakdown/bus_breakdown_and_delays.csv\"\n",
    "\n",
    "# Download the CSV file\n",
    "response = requests.get(CSV_URL)\n",
    "if response.status_code != 200:\n",
    "    raise Exception(f\"Failed to download CSV file from {CSV_URL}\")\n",
    "\n",
    "# Read the CSV file into a pandas DataFrame\n",
    "csv_data = StringIO(response.text)\n",
    "df = pd.read_csv(csv_data)\n",
    "df"
   ],
   "id": "edb347a3c1ba3807",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       school_year  busbreakdown_id           run_type   bus_no route_number  \\\n",
       "0        2017-2018          1363818  Special Ed AM Run    19250         X414   \n",
       "1        2017-2018          1363819           Pre-K/EI    12495       B0203A   \n",
       "2        2017-2018          1363820  Special Ed AM Run    31338         X105   \n",
       "3        2017-2018          1363821           Pre-K/EI     1050         GRA1   \n",
       "4        2017-2018          1363822           Pre-K/EI     2604        NYM-2   \n",
       "...            ...              ...                ...      ...          ...   \n",
       "199993   2017-2018          1368830  Special Ed AM Run    5791D         Q690   \n",
       "199994   2015-2016          1213955           Pre-K/EI     9309            1   \n",
       "199995   2015-2016          1218292           Pre-K/EI      730            3   \n",
       "199996   2015-2016          1225246  Special Ed AM Run     1309         J737   \n",
       "199997   2015-2016          1226992  Special Ed AM Run  VN60196         C901   \n",
       "\n",
       "                    reason schools_serviced           occured_on  \\\n",
       "0            Heavy Traffic            12486  2017-08-01 07:15:00   \n",
       "1                    Other             C244  2017-08-01 07:21:00   \n",
       "2            Heavy Traffic            12084  2017-08-01 07:20:00   \n",
       "3                    Other             C956  2017-08-01 07:24:00   \n",
       "4            Heavy Traffic             E203  2017-08-01 07:31:00   \n",
       "...                    ...              ...                  ...   \n",
       "199993       Heavy Traffic            30502  2017-09-19 07:57:00   \n",
       "199994       Heavy Traffic             S188  2015-09-10 08:10:00   \n",
       "199995  Mechanical Problem             C037  2015-09-30 13:43:00   \n",
       "199996       Heavy Traffic            75003  2015-10-27 07:51:00   \n",
       "199997       Heavy Traffic            75666  2015-11-04 06:34:00   \n",
       "\n",
       "                 created_on          boro  ... how_long_delayed  \\\n",
       "0       2017-08-01 07:22:00         Bronx  ...        25-30 MIN   \n",
       "1       2017-08-01 07:24:00      Brooklyn  ...          15 MINS   \n",
       "2       2017-08-01 07:26:00         Bronx  ...        25-30 MIN   \n",
       "3       2017-08-01 07:27:00        Queens  ...        15-20MINS   \n",
       "4       2017-08-01 07:28:00         Bronx  ...          20 MINS   \n",
       "...                     ...           ...  ...              ...   \n",
       "199993  2017-09-19 07:58:00        Queens  ...        16-30 Min   \n",
       "199994  2015-09-10 08:19:00         Bronx  ...       15 minutes   \n",
       "199995  2015-09-30 13:47:00         Bronx  ...               25   \n",
       "199996  2015-10-27 08:08:00     Manhattan  ...               20   \n",
       "199997  2015-11-04 06:36:00  All Boroughs  ...           30mins   \n",
       "\n",
       "       number_of_students_on_the_bus  has_contractor_notified_schools  \\\n",
       "0                                  0                              Yes   \n",
       "1                                  0                              Yes   \n",
       "2                                  3                              Yes   \n",
       "3                                  8                              Yes   \n",
       "4                                  5                              Yes   \n",
       "...                              ...                              ...   \n",
       "199993                            12                              Yes   \n",
       "199994                             7                              Yes   \n",
       "199995                             0                              Yes   \n",
       "199996                             0                              Yes   \n",
       "199997                             0                              Yes   \n",
       "\n",
       "       has_contractor_notified_parents have_you_alerted_opt  \\\n",
       "0                                  Yes                  Yes   \n",
       "1                                  Yes                  Yes   \n",
       "2                                  Yes                  Yes   \n",
       "3                                  Yes                  Yes   \n",
       "4                                  Yes                   No   \n",
       "...                                ...                  ...   \n",
       "199993                             Yes                   No   \n",
       "199994                              No                   No   \n",
       "199995                             Yes                  Yes   \n",
       "199996                             Yes                  Yes   \n",
       "199997                             Yes                  Yes   \n",
       "\n",
       "                informed_on incident_number      last_updated_on  \\\n",
       "0       2017-08-01 07:22:00             NaN  2017-08-01 07:22:55   \n",
       "1       2017-08-01 07:24:00             NaN  2017-08-01 09:42:39   \n",
       "2       2017-08-01 07:26:00             NaN  2017-08-01 07:26:38   \n",
       "3       2017-08-01 07:27:00             NaN  2017-08-01 07:28:04   \n",
       "4       2017-08-01 07:28:00             NaN  2017-08-01 07:28:17   \n",
       "...                     ...             ...                  ...   \n",
       "199993  2017-09-19 07:58:00             NaN  1900-01-01 00:00:00   \n",
       "199994  2015-09-10 08:19:00             NaN  2015-09-10 08:19:28   \n",
       "199995  2015-09-30 13:47:00             NaN  2015-09-30 13:47:38   \n",
       "199996  2015-10-27 08:08:00             NaN  2015-10-27 08:32:23   \n",
       "199997  2015-11-04 06:36:00             NaN  2015-11-04 06:36:09   \n",
       "\n",
       "       breakdown_or_running_late school_age_or_prek  \n",
       "0                   Running Late         School-Age  \n",
       "1                   Running Late              Pre-K  \n",
       "2                   Running Late         School-Age  \n",
       "3                   Running Late              Pre-K  \n",
       "4                   Running Late              Pre-K  \n",
       "...                          ...                ...  \n",
       "199993              Running Late         School-Age  \n",
       "199994              Running Late              Pre-K  \n",
       "199995              Running Late              Pre-K  \n",
       "199996              Running Late         School-Age  \n",
       "199997              Running Late         School-Age  \n",
       "\n",
       "[199998 rows x 21 columns]"
      ],
      "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>school_year</th>\n",
       "      <th>busbreakdown_id</th>\n",
       "      <th>run_type</th>\n",
       "      <th>bus_no</th>\n",
       "      <th>route_number</th>\n",
       "      <th>reason</th>\n",
       "      <th>schools_serviced</th>\n",
       "      <th>occured_on</th>\n",
       "      <th>created_on</th>\n",
       "      <th>boro</th>\n",
       "      <th>...</th>\n",
       "      <th>how_long_delayed</th>\n",
       "      <th>number_of_students_on_the_bus</th>\n",
       "      <th>has_contractor_notified_schools</th>\n",
       "      <th>has_contractor_notified_parents</th>\n",
       "      <th>have_you_alerted_opt</th>\n",
       "      <th>informed_on</th>\n",
       "      <th>incident_number</th>\n",
       "      <th>last_updated_on</th>\n",
       "      <th>breakdown_or_running_late</th>\n",
       "      <th>school_age_or_prek</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1363818</td>\n",
       "      <td>Special Ed AM Run</td>\n",
       "      <td>19250</td>\n",
       "      <td>X414</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>12486</td>\n",
       "      <td>2017-08-01 07:15:00</td>\n",
       "      <td>2017-08-01 07:22:00</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>...</td>\n",
       "      <td>25-30 MIN</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2017-08-01 07:22:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-01 07:22:55</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>School-Age</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1363819</td>\n",
       "      <td>Pre-K/EI</td>\n",
       "      <td>12495</td>\n",
       "      <td>B0203A</td>\n",
       "      <td>Other</td>\n",
       "      <td>C244</td>\n",
       "      <td>2017-08-01 07:21:00</td>\n",
       "      <td>2017-08-01 07:24:00</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>...</td>\n",
       "      <td>15 MINS</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2017-08-01 07:24:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-01 09:42:39</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>Pre-K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1363820</td>\n",
       "      <td>Special Ed AM Run</td>\n",
       "      <td>31338</td>\n",
       "      <td>X105</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>12084</td>\n",
       "      <td>2017-08-01 07:20:00</td>\n",
       "      <td>2017-08-01 07:26:00</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>...</td>\n",
       "      <td>25-30 MIN</td>\n",
       "      <td>3</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2017-08-01 07:26:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-01 07:26:38</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>School-Age</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1363821</td>\n",
       "      <td>Pre-K/EI</td>\n",
       "      <td>1050</td>\n",
       "      <td>GRA1</td>\n",
       "      <td>Other</td>\n",
       "      <td>C956</td>\n",
       "      <td>2017-08-01 07:24:00</td>\n",
       "      <td>2017-08-01 07:27:00</td>\n",
       "      <td>Queens</td>\n",
       "      <td>...</td>\n",
       "      <td>15-20MINS</td>\n",
       "      <td>8</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2017-08-01 07:27:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-01 07:28:04</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>Pre-K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1363822</td>\n",
       "      <td>Pre-K/EI</td>\n",
       "      <td>2604</td>\n",
       "      <td>NYM-2</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>E203</td>\n",
       "      <td>2017-08-01 07:31:00</td>\n",
       "      <td>2017-08-01 07:28:00</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>...</td>\n",
       "      <td>20 MINS</td>\n",
       "      <td>5</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>2017-08-01 07:28:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-08-01 07:28:17</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>Pre-K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199993</th>\n",
       "      <td>2017-2018</td>\n",
       "      <td>1368830</td>\n",
       "      <td>Special Ed AM Run</td>\n",
       "      <td>5791D</td>\n",
       "      <td>Q690</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>30502</td>\n",
       "      <td>2017-09-19 07:57:00</td>\n",
       "      <td>2017-09-19 07:58:00</td>\n",
       "      <td>Queens</td>\n",
       "      <td>...</td>\n",
       "      <td>16-30 Min</td>\n",
       "      <td>12</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>2017-09-19 07:58:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900-01-01 00:00:00</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>School-Age</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199994</th>\n",
       "      <td>2015-2016</td>\n",
       "      <td>1213955</td>\n",
       "      <td>Pre-K/EI</td>\n",
       "      <td>9309</td>\n",
       "      <td>1</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>S188</td>\n",
       "      <td>2015-09-10 08:10:00</td>\n",
       "      <td>2015-09-10 08:19:00</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>...</td>\n",
       "      <td>15 minutes</td>\n",
       "      <td>7</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>2015-09-10 08:19:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-09-10 08:19:28</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>Pre-K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199995</th>\n",
       "      <td>2015-2016</td>\n",
       "      <td>1218292</td>\n",
       "      <td>Pre-K/EI</td>\n",
       "      <td>730</td>\n",
       "      <td>3</td>\n",
       "      <td>Mechanical Problem</td>\n",
       "      <td>C037</td>\n",
       "      <td>2015-09-30 13:43:00</td>\n",
       "      <td>2015-09-30 13:47:00</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>...</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2015-09-30 13:47:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-09-30 13:47:38</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>Pre-K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199996</th>\n",
       "      <td>2015-2016</td>\n",
       "      <td>1225246</td>\n",
       "      <td>Special Ed AM Run</td>\n",
       "      <td>1309</td>\n",
       "      <td>J737</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>75003</td>\n",
       "      <td>2015-10-27 07:51:00</td>\n",
       "      <td>2015-10-27 08:08:00</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>...</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2015-10-27 08:08:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-10-27 08:32:23</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>School-Age</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199997</th>\n",
       "      <td>2015-2016</td>\n",
       "      <td>1226992</td>\n",
       "      <td>Special Ed AM Run</td>\n",
       "      <td>VN60196</td>\n",
       "      <td>C901</td>\n",
       "      <td>Heavy Traffic</td>\n",
       "      <td>75666</td>\n",
       "      <td>2015-11-04 06:34:00</td>\n",
       "      <td>2015-11-04 06:36:00</td>\n",
       "      <td>All Boroughs</td>\n",
       "      <td>...</td>\n",
       "      <td>30mins</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2015-11-04 06:36:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-11-04 06:36:09</td>\n",
       "      <td>Running Late</td>\n",
       "      <td>School-Age</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>199998 rows × 21 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# Anlegen der Testdatenbank und Tabelle",
   "id": "d7492f8c15effdbc"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:14.200031Z",
     "start_time": "2024-08-08T11:44:14.197638Z"
    }
   },
   "cell_type": "code",
   "source": [
    "host = 'localhost'\n",
    "database = 'demo'\n",
    "user = 'postgres'\n",
    "password = 'test'\n",
    "schema = 'public'\n",
    "table_name = 'bus_breakdown_and_delays'\n",
    "\n",
    "def connect(database='demo'):\n",
    "    \"\"\"Connect to database\"\"\"\n",
    "    conn = None\n",
    "    try:\n",
    "        print('Connecting...')\n",
    "        conn = psycopg2.connect(\n",
    "            host=host,\n",
    "            database=database,\n",
    "            user=user,\n",
    "            password=password)\n",
    "    except (Exception, psycopg2.DatabaseError) as error:\n",
    "        #print(error)\n",
    "        # Connect to the default database (usually 'postgres') to create the new database\n",
    "        conn = psycopg2.connect(\n",
    "            host=host,\n",
    "            database=\"postgres\",\n",
    "            user=user,\n",
    "            password=password,\n",
    "        )\n",
    "        cur = conn.cursor()\n",
    "        conn.autocommit = True\n",
    "        # Create the new database\n",
    "        try:\n",
    "            cur.execute(sql.SQL(\"CREATE DATABASE {}\").format(sql.Identifier(database)))\n",
    "            print(f\"Database '{database}' created successfully\")\n",
    "        except (Exception, psycopg2.DatabaseError) as e:\n",
    "            print(f\"Failed to create database '{database}': {e}\")\n",
    "        cur.close()\n",
    "        conn.close()\n",
    "        return connect()\n",
    "\n",
    "    print(f'All good, connection to {database} successful!')\n",
    "    return conn\n"
   ],
   "id": "ea05ec755ddf9707",
   "outputs": [],
   "execution_count": 7
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Schema anlegen",
   "id": "d2b8b931411ccd75"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:14.387859Z",
     "start_time": "2024-08-08T11:44:14.200610Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# Define the table creation SQL\n",
    "create_table_sql = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS bus_breakdown_and_delays (\n",
    "    school_year VARCHAR(50),\n",
    "    busbreakdown_id INTEGER PRIMARY KEY,\n",
    "    run_type TEXT,\n",
    "    bus_no TEXT,\n",
    "    route_number TEXT,\n",
    "    reason TEXT,\n",
    "    schools_serviced TEXT,\n",
    "    occurred_on TIMESTAMP,\n",
    "    created_on TIMESTAMP,\n",
    "    boro TEXT,\n",
    "    bus_company_name TEXT,\n",
    "    how_long_delayed TEXT,\n",
    "    number_of_students_on_the_bus INTEGER,\n",
    "    has_contractor_notified_schools TEXT,\n",
    "    has_contractor_notified_parents TEXT,\n",
    "    have_you_alerted_opt TEXT,\n",
    "    informed_on TIMESTAMP,\n",
    "    incident_number TEXT,\n",
    "    last_updated_on TIMESTAMP,\n",
    "    breakdown_or_running_late TEXT,\n",
    "    school_age_or_prek TEXT\n",
    ")\n",
    "\"\"\"\n",
    "conn = connect()\n",
    "cur = conn.cursor()\n",
    "try:\n",
    "    cur.execute(create_table_sql)\n",
    "    print(f\"Table '{table_name}' created successfully or already exists\")\n",
    "    conn.commit()\n",
    "except Exception as e:\n",
    "    print(f\"Failed to create table '{table_name}': {e}\")\n",
    "finally:\n",
    "    cur.close()\n",
    "    conn.close()\n"
   ],
   "id": "c17e9cf4c18cc1bc",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting...\n",
      "Database 'demo' created successfully\n",
      "Connecting...\n",
      "All good, connection to demo successful!\n",
      "Table 'bus_breakdown_and_delays' created successfully or already exists\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## Daten importieren",
   "id": "861bb72b86890edd"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-08T11:44:16.027437Z",
     "start_time": "2024-08-08T11:44:14.388520Z"
    }
   },
   "cell_type": "code",
   "source": [
    "conn = connect()\n",
    "cur = conn.cursor()\n",
    "\n",
    "# Create a temporary CSV file in memory without the header\n",
    "csv_buffer = StringIO()\n",
    "df.to_csv(csv_buffer, index=False, header=False)\n",
    "csv_buffer.seek(0)\n",
    "\n",
    "# Copy the DataFrame to the PostgreSQL table\n",
    "try:\n",
    "    cur.copy_expert(f\"COPY {table_name} FROM STDIN WITH CSV\", csv_buffer)\n",
    "    conn.commit()\n",
    "    print(\"CSV file imported successfully into PostgreSQL\")\n",
    "except Exception as e:\n",
    "    print(e)\n",
    "    conn.rollback()\n",
    "    print(f\"Failed to import CSV file into PostgreSQL: {e}\")\n",
    "finally:\n",
    "    cur.close()\n",
    "    conn.close()\n"
   ],
   "id": "e86fe9e695b8258d",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting...\n",
      "All good, connection to demo successful!\n",
      "CSV file imported successfully into PostgreSQL\n"
     ]
    }
   ],
   "execution_count": 9
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}