{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dbd6eae9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import ipywidgets as widgets\n",
    "pd.options.plotting.backend = \"plotly\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8b447b09",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('01_Stammdaten_Unternehmen.csv', sep=';')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5fc7b7d2",
   "metadata": {},
   "outputs": [
    {
     "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>Name</th>\n",
       "      <th>Straße</th>\n",
       "      <th>PLZ</th>\n",
       "      <th>Stadt</th>\n",
       "      <th>Branche</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Volkswagen</td>\n",
       "      <td>Berliner Ring 2</td>\n",
       "      <td>38440</td>\n",
       "      <td>Wolfsburg</td>\n",
       "      <td>Automobil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mercedes-Benz Group</td>\n",
       "      <td>Mercedesstraße 120</td>\n",
       "      <td>70372</td>\n",
       "      <td>Stuttgart</td>\n",
       "      <td>Automobil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Allianz</td>\n",
       "      <td>Reinsburgstraße 19</td>\n",
       "      <td>70178</td>\n",
       "      <td>Stuttgart</td>\n",
       "      <td>Versicherung, Finanzdienstleistung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BMW Group</td>\n",
       "      <td>Petuelring 130</td>\n",
       "      <td>80809</td>\n",
       "      <td>München</td>\n",
       "      <td>Automobil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Deutsche Telekom</td>\n",
       "      <td>Landgrabenweg 151</td>\n",
       "      <td>53227</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Telekommunikation, Informationstechnologie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Deutsche Post DHL Group</td>\n",
       "      <td>Charles-de-Gaulle-Str. 20</td>\n",
       "      <td>53113</td>\n",
       "      <td>Bonn</td>\n",
       "      <td>Logistik</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bosch Group</td>\n",
       "      <td>Robert-Bosch-Platz 1</td>\n",
       "      <td>70839</td>\n",
       "      <td>Gerlingen-Schillerhöhe</td>\n",
       "      <td>Kraftfahrzeugtechnik, Industrietechnik, Gebrau...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BASF</td>\n",
       "      <td>Carl-Bosch-Straße 38</td>\n",
       "      <td>67056</td>\n",
       "      <td>Ludwigshafen</td>\n",
       "      <td>Chemie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>E.ON</td>\n",
       "      <td>Arnulfstraße 203</td>\n",
       "      <td>80634</td>\n",
       "      <td>München</td>\n",
       "      <td>Energie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Munich Re Group</td>\n",
       "      <td>Königinstr. 107</td>\n",
       "      <td>80802</td>\n",
       "      <td>München</td>\n",
       "      <td>Versicherung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Siemens</td>\n",
       "      <td>Werner-von-Siemens-Straße 1</td>\n",
       "      <td>80333</td>\n",
       "      <td>München</td>\n",
       "      <td>Automatisierung, Digitalisierung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Deutsche Bahn</td>\n",
       "      <td>Potsdamer Platz 2</td>\n",
       "      <td>10785</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Transport, Logistik</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Name                       Straße    PLZ  \\\n",
       "0                Volkswagen              Berliner Ring 2  38440   \n",
       "1       Mercedes-Benz Group           Mercedesstraße 120  70372   \n",
       "2                   Allianz           Reinsburgstraße 19  70178   \n",
       "3                 BMW Group               Petuelring 130  80809   \n",
       "4          Deutsche Telekom            Landgrabenweg 151  53227   \n",
       "5   Deutsche Post DHL Group    Charles-de-Gaulle-Str. 20  53113   \n",
       "6               Bosch Group         Robert-Bosch-Platz 1  70839   \n",
       "7                      BASF         Carl-Bosch-Straße 38  67056   \n",
       "8                      E.ON             Arnulfstraße 203  80634   \n",
       "9           Munich Re Group              Königinstr. 107  80802   \n",
       "10                  Siemens  Werner-von-Siemens-Straße 1  80333   \n",
       "11            Deutsche Bahn            Potsdamer Platz 2  10785   \n",
       "\n",
       "                     Stadt                                            Branche  \n",
       "0                Wolfsburg                                          Automobil  \n",
       "1                Stuttgart                                          Automobil  \n",
       "2                Stuttgart                 Versicherung, Finanzdienstleistung  \n",
       "3                  München                                          Automobil  \n",
       "4                     Bonn         Telekommunikation, Informationstechnologie  \n",
       "5                     Bonn                                           Logistik  \n",
       "6   Gerlingen-Schillerhöhe  Kraftfahrzeugtechnik, Industrietechnik, Gebrau...  \n",
       "7             Ludwigshafen                                             Chemie  \n",
       "8                  München                                            Energie  \n",
       "9                  München                                       Versicherung  \n",
       "10                 München                   Automatisierung, Digitalisierung  \n",
       "11                  Berlin                                Transport, Logistik  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5c6c68d",
   "metadata": {},
   "source": [
    "---------------------------------\n",
    "# Schreibe Unternehmensdaten in PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6c09bdca",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "383fb9a9",
   "metadata": {},
   "source": [
    "### Verbinde zur Datenbank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8675c8bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database connected successfully\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(\n",
    "    host=\"localhost\",\n",
    "    database=\"transparenz\",\n",
    "    user=\"postgres\",\n",
    "    password=\"postgres\")\n",
    "\n",
    "print(\"Database connected successfully\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22b9ab1d",
   "metadata": {},
   "source": [
    "## Iteriere durch Dataframe und schreibe Datensätze in Tabelle *Company*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "961ac836",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()\n",
    "\n",
    "\n",
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    name=str(df['Name'].iloc[i])\n",
    "    street=str(df['Straße'].iloc[i])\n",
    "    zipcode=int(df['PLZ'].iloc[i])\n",
    "    city=str(df['Stadt'].iloc[i])\n",
    "    sector=str(df['Branche'].iloc[i])\n",
    "    \n",
    "    postgres_insert_query = \"\"\" INSERT INTO company (NAME,STREET, ZIP, CITY,SECTOR) VALUES (%s,%s,%s,%s,%s)\"\"\"   \n",
    "       \n",
    "    record_to_insert = (name,street,zipcode,city,sector)\n",
    "    cur.execute(postgres_insert_query, record_to_insert)    \n",
    "    \n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "attachments": {
    "grafik.png": {
     "image/png": "[encoded data, 255860 characters]"
    }
   },
   "cell_type": "markdown",
   "id": "6fb1ec01",
   "metadata": {},
   "source": [
    "Überprüfe mittels pgAdmin, ob die Datensätze vollständig geschrieben wurden:\n",
    "![grafik.png](attachment:grafik.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b80e0fc8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
