{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a768e970",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, PrimaryKeyConstraint, UniqueConstraint\n",
    "from sqlalchemy.engine import URL\n",
    "from sqlalchemy import Column, Integer, String, DateTime, Text, Float, null\n",
    "from sqlalchemy.orm import declarative_base\n",
    "from sqlalchemy.orm import relationship, backref\n",
    "from datetime import datetime\n",
    "from sqlalchemy.orm import Mapped\n",
    "from sqlalchemy.orm import mapped_column\n",
    "from sqlalchemy import ForeignKey, ForeignKeyConstraint\n",
    "from sqlalchemy.orm import ColumnProperty\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "import enum\n",
    "from sqlalchemy import Integer, Enum\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c345bbc4",
   "metadata": {},
   "source": [
    "# Verbindung zur Datenbank\n",
    "Vorbedingung: Datenbank muss vorhanden sein"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "61af408e",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = URL.create(\n",
    "    drivername=\"postgresql\",\n",
    "    username=\"postgres\",\n",
    "    password=\"postgres\",\n",
    "    host=\"localhost\",\n",
    "    database=\"postgres\"\n",
    ")\n",
    "\n",
    "engine = create_engine(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e0849ec8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#connect to database\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61f23c95",
   "metadata": {},
   "source": [
    "### Erstelle **Company** für Stammdaten\n",
    "Folgend wird ein Objekt erstellt, welches die Tabelle **company** repräsentiert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4d5a7e71",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create an object *district_court* which inherits attributes from Base-class\n",
    "Base = declarative_base()\n",
    "\n",
    "class DistrictCourt(Base):\n",
    "    __tablename__ = 'district_court'\n",
    "    \n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    city = Column(String(100), nullable=False)\n",
    "    name = Column(String(100), nullable=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aeef9fd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0f6a3866",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Company(Base):\n",
    "    __tablename__ = 'company'\n",
    "\n",
    "    hr = Column(Integer(), nullable=False, primary_key=True)\n",
    "    court_id = Column(Integer, ForeignKey(\"district_court.id\"), nullable=False, primary_key=True)\n",
    "    name = Column(String(100), nullable=False)\n",
    "    street = Column(String(100), nullable=False)\n",
    "    zip = Column(Integer(), nullable=False)\n",
    "    city = Column(String(100), nullable=False)\n",
    "    sector = Column(String(100), nullable=False)\n",
    "\n",
    "    __table_args__ = (\n",
    "        PrimaryKeyConstraint('hr', 'court_id', name='pk_company_hr_court'),\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3d649e2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('company', MetaData(), Column('hr', Integer(), table=<company>, primary_key=True, nullable=False), Column('court_id', Integer(), ForeignKey('district_court.id'), table=<company>, primary_key=True, nullable=False), Column('name', String(length=100), table=<company>, nullable=False), Column('street', String(length=100), table=<company>, nullable=False), Column('zip', Integer(), table=<company>, nullable=False), Column('city', String(length=100), table=<company>, nullable=False), Column('sector', String(length=100), table=<company>, nullable=False), schema=None)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check if table-object is created\n",
    "Company.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "21ff2ec4",
   "metadata": {},
   "outputs": [],
   "source": [
    "class Finance(Base):\n",
    "    __tablename__ = 'finance'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    company_hr = Column(Integer)\n",
    "    company_court = Column(Integer)\n",
    "    date = Column(DateTime, default=datetime.now)\n",
    "    total_volume = Column(Float)\n",
    "    ebit = Column(Float)\n",
    "    ebitda = Column(Float)\n",
    "    ebit_margin = Column(Float)\n",
    "    total_balance = Column(Float)\n",
    "    equity = Column(Float)\n",
    "    debt = Column(Float)\n",
    "    return_on_equity = Column(Float)\n",
    "    capital_turnover_rate = Column(Float)\n",
    "\n",
    "    company = relationship(\"Company\")\n",
    "\n",
    "    __table_args__ = (\n",
    "        ForeignKeyConstraint([company_hr, company_court], [Company.hr, Company.court_id]),\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1a87ffa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create own enumeration type and sentiment object\n",
    "sentiment_type=Enum(\"employee_voting\",\"sustainability\",\"environmental_aspects\",\"perception\", name=\"sentiment_type\", create_type=False)\n",
    "\n",
    "class Sentiment(Base):\n",
    "    __tablename__ = 'sentiment'\n",
    "\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    #company_hr = mapped_column(ForeignKey(\"company.hr\"))\n",
    "    #company_court = mapped_column(ForeignKey(\"company.court_id\"))\n",
    "    company_hr = Column(Integer)\n",
    "    company_court = Column(Integer)\n",
    "    date = Column(DateTime(), default=datetime.now)\n",
    "    type = Column(sentiment_type,nullable=False)\n",
    "    value =Column(Float(),nullable=False)\n",
    "    source=Column(String(100))\n",
    "    \n",
    "    sentiment = relationship('Company')\n",
    "    __table_args__ = (\n",
    "        ForeignKeyConstraint([company_hr, company_court], [Company.hr, Company.court_id]),\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8e958000",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create person object\n",
    "class Person(Base):\n",
    "    __tablename__ = 'person'\n",
    "\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    name=Column(String(100), nullable=False)\n",
    "    surname=Column(String(100), nullable=False)\n",
    "    works_for=Column(String(100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f8e998e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create own relation type and person_relation object\n",
    "rel_type=Enum(\"Executive\",\"Auditor\",\"Supervisory_Board\",\"Managing_Director\",\"Authorized_Representive\",\"Final_Auditor\", name=\"rel_type\", create_type=False)\n",
    "\n",
    "class Person_Relation(Base):\n",
    "    __tablename__ = 'person_relation'\n",
    "\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    #company_hr = mapped_column(ForeignKey(\"company.hr\"))\n",
    "    #company_court = mapped_column(ForeignKey(\"company.court_id\"))\n",
    "    company_hr = Column(Integer)\n",
    "    company_court = Column(Integer)\n",
    "    person_id = mapped_column(ForeignKey(\"person.id\"))\n",
    "    date_from = Column(DateTime(), default=datetime.now)\n",
    "    date_to = Column(DateTime(), default=datetime.now) \n",
    "    relation=Column(rel_type, nullable=False)\n",
    "    \n",
    "    #company = relationship(\"Company\")\n",
    "    #person = relationship(\"Person\", foreign_keys=[person_id])\n",
    "    #company = relationship('Company', foreign_keys=[company_hr,company_court])\n",
    "    __table_args__ = (\n",
    "        ForeignKeyConstraint([company_hr, company_court], [Company.hr, Company.court_id]),\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6e5665fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#create own relation type and company_relation object\n",
    "rel_type_comp=Enum(\"participates_with\",\"has_shares_of\",\"is_supplied_by\",\"works_with\", name=\"rel_type_comp\", create_type=False)\n",
    "\n",
    "class Company_Relation(Base):\n",
    "    __tablename__ = 'company_relation'\n",
    "\n",
    "    id = Column(Integer(), primary_key=True)\n",
    "    company1_id = Column(Integer,nullable=False)\n",
    "    company2_id= Column(Integer,nullable=False)\n",
    "    date_from = Column(DateTime(), default=datetime.now)\n",
    "    date_to = Column(DateTime(), default=datetime.now) \n",
    "    relation=Column(rel_type_comp, nullable=False)\n",
    "    \n",
    "    #company = relationship(\"Company\")\n",
    "\n",
    "    __table_args__ = {'extend_existing': True}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "901f33c0",
   "metadata": {},
   "source": [
    "# Erstelle die Tabellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2cfbe6a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f8ee6287",
   "metadata": {},
   "outputs": [],
   "source": [
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0d962f0",
   "metadata": {},
   "source": [
    "# Befülle die Tabellen *district_court*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "21c9db99",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('Amtsgerichte.csv', sep=';') \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "09fb47ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    court=DistrictCourt( \n",
    "    city = str(df['Stadt'].iloc[i]),\n",
    "    name = str(df['Name'].iloc[i]))\n",
    "    \n",
    "    session.add(court)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0efda914",
   "metadata": {},
   "source": [
    "# Befülle die Tabellen *company*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ba5c15ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('01_Stammdaten_Unternehmen_HR2.csv', sep=';',encoding=\"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "fc8afa09",
   "metadata": {},
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint \"pk_company_hr_court\"\nDETAIL:  Key (hr, court_id)=(12334, 2) already exists.\n\n[SQL: INSERT INTO company (hr, court_id, name, street, zip, city, sector) VALUES (%(hr)s, %(court_id)s, %(name)s, %(street)s, %(zip)s, %(city)s, %(sector)s)]\n[parameters: {'hr': 12334, 'court_id': 2, 'name': 'Volkswagen', 'street': 'Berliner Ring 2', 'zip': 38440, 'city': 'Wolfsburg', 'sector': 'Automobil'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mUniqueViolation\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1967\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1968\u001b[1;33m                     self.dialect.do_execute(\n\u001b[0m\u001b[0;32m   1969\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr_statement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0meffective_parameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    919\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 920\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    921\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mUniqueViolation\u001b[0m: duplicate key value violates unique constraint \"pk_company_hr_court\"\nDETAIL:  Key (hr, court_id)=(12334, 2) already exists.\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-18-33004f42f42f>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     10\u001b[0m     sector=str(df['Branche'].iloc[i]))\n\u001b[0;32m     11\u001b[0m     \u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcomp\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 12\u001b[1;33m     \u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1904\u001b[0m             \u001b[0mtrans\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_autobegin_t\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1905\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1906\u001b[1;33m         \u001b[0mtrans\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0m_to_root\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1907\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1908\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mprepare\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self, _to_root)\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\state_changes.py\u001b[0m in \u001b[0;36m_go\u001b[1;34m(fn, self, *arg, **kw)\u001b[0m\n\u001b[0;32m    135\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_next_state\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_StateChangeStates\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCHANGE_IN_PROGRESS\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    136\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 137\u001b[1;33m                 \u001b[0mret_value\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    138\u001b[0m             \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    139\u001b[0m                 \u001b[1;32mraise\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self, _to_root)\u001b[0m\n\u001b[0;32m   1219\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_state\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mSessionTransactionState\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mPREPARED\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1220\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_expect_state\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mSessionTransactionState\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mPREPARED\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1221\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_prepare_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1222\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1223\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_parent\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnested\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_prepare_impl\u001b[1;34m(self)\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\state_changes.py\u001b[0m in \u001b[0;36m_go\u001b[1;34m(fn, self, *arg, **kw)\u001b[0m\n\u001b[0;32m    135\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_next_state\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_StateChangeStates\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCHANGE_IN_PROGRESS\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    136\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 137\u001b[1;33m                 \u001b[0mret_value\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    138\u001b[0m             \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    139\u001b[0m                 \u001b[1;32mraise\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_prepare_impl\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1194\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_is_clean\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1195\u001b[0m                     \u001b[1;32mbreak\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1196\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1197\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1198\u001b[0m                 raise exc.FlushError(\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mflush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   4152\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4153\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4154\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flush\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobjects\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4155\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4156\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_flush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   4289\u001b[0m         \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4290\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4291\u001b[1;33m                 \u001b[0mtransaction\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0m_capture_exception\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4292\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4293\u001b[0m     def bulk_save_objects(\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\util\\langhelpers.py\u001b[0m in \u001b[0;36m__exit__\u001b[1;34m(self, type_, value, traceback)\u001b[0m\n\u001b[0;32m    145\u001b[0m             \u001b[1;32massert\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    146\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m  \u001b[1;31m# remove potential circular references\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 147\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    148\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    149\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m  \u001b[1;31m# remove potential circular references\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_flush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   4249\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4250\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4251\u001b[1;33m                 \u001b[0mflush_context\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4252\u001b[0m             \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4253\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\unitofwork.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    465\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    466\u001b[0m             \u001b[1;32mfor\u001b[0m \u001b[0mrec\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mtopological\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdependencies\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mpostsort_actions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 467\u001b[1;33m                 \u001b[0mrec\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    468\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    469\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mfinalize_flush_changes\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\unitofwork.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, uow)\u001b[0m\n\u001b[0;32m    642\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpreload_module\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"sqlalchemy.orm.persistence\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    643\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 644\u001b[1;33m         util.preloaded.orm_persistence.save_obj(\n\u001b[0m\u001b[0;32m    645\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapper\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    646\u001b[0m             \u001b[0muow\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstates_for_mapper_hierarchy\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapper\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\persistence.py\u001b[0m in \u001b[0;36msave_obj\u001b[1;34m(base_mapper, states, uowtransaction, single)\u001b[0m\n\u001b[0;32m     91\u001b[0m         )\n\u001b[0;32m     92\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 93\u001b[1;33m         _emit_insert_statements(\n\u001b[0m\u001b[0;32m     94\u001b[0m             \u001b[0mbase_mapper\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     95\u001b[0m             \u001b[0muowtransaction\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\orm\\persistence.py\u001b[0m in \u001b[0;36m_emit_insert_statements\u001b[1;34m(base_mapper, uowtransaction, mapper, table, insert, bookkeeping, use_orm_insert_stmt, execution_options)\u001b[0m\n\u001b[0;32m   1038\u001b[0m             \u001b[0mmultiparams\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mrec\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mrec\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrecords\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1039\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1040\u001b[1;33m             result = connection.execute(\n\u001b[0m\u001b[0;32m   1041\u001b[0m                 \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1042\u001b[0m             )\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, statement, parameters, execution_options)\u001b[0m\n\u001b[0;32m   1411\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1412\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1413\u001b[1;33m             return meth(\n\u001b[0m\u001b[0;32m   1414\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1415\u001b[0m                 \u001b[0mdistilled_parameters\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\sql\\elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[1;34m(self, connection, distilled_params, execution_options)\u001b[0m\n\u001b[0;32m    481\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mTYPE_CHECKING\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    482\u001b[0m                 \u001b[1;32massert\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExecutable\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 483\u001b[1;33m             return connection._execute_clauseelement(\n\u001b[0m\u001b[0;32m    484\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    485\u001b[0m             )\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[1;34m(self, elem, distilled_parameters, execution_options)\u001b[0m\n\u001b[0;32m   1635\u001b[0m             \u001b[0mlinting\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcompiler_linting\u001b[0m \u001b[1;33m|\u001b[0m \u001b[0mcompiler\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mWARN_LINTING\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1636\u001b[0m         )\n\u001b[1;32m-> 1637\u001b[1;33m         ret = self._execute_context(\n\u001b[0m\u001b[0;32m   1638\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1639\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001b[0m\n\u001b[0;32m   1844\u001b[0m             )\n\u001b[0;32m   1845\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1846\u001b[1;33m             return self._exec_single_context(\n\u001b[0m\u001b[0;32m   1847\u001b[0m                 \u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1848\u001b[0m             )\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1985\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1986\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1987\u001b[1;33m             self._handle_dbapi_exception(\n\u001b[0m\u001b[0;32m   1988\u001b[0m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr_statement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0meffective_parameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1989\u001b[0m             )\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context, is_sub_exec)\u001b[0m\n\u001b[0;32m   2342\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2343\u001b[0m                 \u001b[1;32massert\u001b[0m \u001b[0msqlalchemy_exception\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2344\u001b[1;33m                 \u001b[1;32mraise\u001b[0m \u001b[0msqlalchemy_exception\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2345\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2346\u001b[0m                 \u001b[1;32massert\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_exec_single_context\u001b[1;34m(self, dialect, context, statement, parameters)\u001b[0m\n\u001b[0;32m   1966\u001b[0m                             \u001b[1;32mbreak\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1967\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1968\u001b[1;33m                     self.dialect.do_execute(\n\u001b[0m\u001b[0;32m   1969\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr_statement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0meffective_parameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1970\u001b[0m                     )\n",
      "\u001b[1;32m~\\AppData\\Roaming\\Python\\Python38\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    918\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    919\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 920\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    921\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    922\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIntegrityError\u001b[0m: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint \"pk_company_hr_court\"\nDETAIL:  Key (hr, court_id)=(12334, 2) already exists.\n\n[SQL: INSERT INTO company (hr, court_id, name, street, zip, city, sector) VALUES (%(hr)s, %(court_id)s, %(name)s, %(street)s, %(zip)s, %(city)s, %(sector)s)]\n[parameters: {'hr': 12334, 'court_id': 2, 'name': 'Volkswagen', 'street': 'Berliner Ring 2', 'zip': 38440, 'city': 'Wolfsburg', 'sector': 'Automobil'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)"
     ]
    }
   ],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    comp=Company( \n",
    "    hr= int(df['HR'].iloc[i]),\n",
    "    court_id= int(df['Amtsgericht'].iloc[i]),\n",
    "    name = str(df['Name'].iloc[i]),\n",
    "    street = str(df['Strasse'].iloc[i]),\n",
    "    zip = int(df['PLZ'].iloc[i]),\n",
    "    city = str(df['Stadt'].iloc[i]),\n",
    "    sector=str(df['Branche'].iloc[i]))\n",
    "    session.add(comp)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc23ecab",
   "metadata": {},
   "source": [
    "# Befülle die Tabellen *company*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d4547b1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Company_HR', 'Company_Court', 'Jahr', 'Umsatz', 'Ebit', 'EBITDA'], dtype='object')"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('BASF_Data.csv', sep=';', decimal=\",\",encoding=\"ISO-8859-1\")  \n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d1d86e88",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    fin=Finance( \n",
    "    company_hr = int(df['Company_HR'].iloc[i]),\n",
    "    company_court = int(df['Company_Court'].iloc[i]),\n",
    "    date=datetime.strptime(str(df['Jahr'].iloc[i]), '%Y'),\n",
    "    total_volume=str(df['Umsatz'].iloc[i]),\n",
    "    ebit=str(df['Ebit'].iloc[i]) ,\n",
    "    ebitda=str(df['EBITDA'].iloc[i]),\n",
    "    ebit_margin=null(),\n",
    "    total_balance=null(),\n",
    "    equity=null(),\n",
    "    debt=null(),\n",
    "    return_on_equity=null(),\n",
    "    capital_turnover_rate=null())\n",
    "    \n",
    "    session.add(fin)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdae69a9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0e9ebfae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Company_HR', 'Company_Court', 'Jahr', 'Umsatz', 'Ebit', 'EBITDA'], dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('Telekom_Data.csv', sep=';',decimal=',',encoding=\"ISO-8859-1\")  \n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "01fae1da",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    fin=Finance( \n",
    "    company_hr = int(df['Company_HR'].iloc[i]),\n",
    "    company_court = int(df['Company_Court'].iloc[i]),    \n",
    "    date=datetime.strptime(str(df['Jahr'].iloc[i]), '%Y'),\n",
    "    total_volume=str(df['Umsatz'].iloc[i]),\n",
    "    ebit=str(df['Ebit'].iloc[i]) ,\n",
    "    ebitda=str(df['EBITDA'].iloc[i]),\n",
    "    ebit_margin=null(),\n",
    "    total_balance=null(),\n",
    "    equity=null(),\n",
    "    debt=null(),\n",
    "    return_on_equity=null(),\n",
    "    capital_turnover_rate=null())\n",
    "    \n",
    "    session.add(fin)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc29367e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "f48deb0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Company_HR', 'Company_Court', 'Jahr', 'Umsatz', 'Ebit', 'EBITDA'], dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('EON_Data.csv', sep=';',decimal=',',encoding=\"ISO-8859-1\")  \n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a01ba01b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    fin=Finance( \n",
    "    company_hr = int(df['Company_HR'].iloc[i]),\n",
    "    company_court = int(df['Company_Court'].iloc[i]),\n",
    "    date=datetime.strptime(str(df['Jahr'].iloc[i]), '%Y'),\n",
    "    total_volume=str(df['Umsatz'].iloc[i]),\n",
    "    ebit=str(df['Ebit'].iloc[i]) ,\n",
    "    ebitda=str(df['EBITDA'].iloc[i]),\n",
    "    ebit_margin=null(),\n",
    "    total_balance=null(),\n",
    "    equity=null(),\n",
    "    debt=null(),\n",
    "    return_on_equity=null(),\n",
    "    capital_turnover_rate=null())\n",
    "    \n",
    "    session.add(fin)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f47963f1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "41a3312d",
   "metadata": {},
   "source": [
    "# Befülle die Tabelle Person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "8dcd4c4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Name', 'Surname'], dtype='object')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('person1000.csv', sep=';',decimal=',',encoding=\"ISO-8859-1\")  \n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "a2307a07",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    per=Person( \n",
    "    name = str(df['Name'].iloc[i]),\n",
    "    surname = str(df['Surname'].iloc[i])\n",
    ")\n",
    "    \n",
    "    session.add(per)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "933b3ec6",
   "metadata": {},
   "source": [
    "# Erzeuge die Tabelle Person-Beziehungen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "3f38817a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "\n",
    "relation=['Executive',\n",
    "'Auditor',\n",
    "'Supervisory_Board',\n",
    "'Managing_Director',\n",
    "'Authorized_Representive',\n",
    "'Final_Auditor'\n",
    "]\n",
    "\n",
    "hr_court=[\n",
    "(12334,2),\n",
    "(64566,2),\n",
    "(5433,3),\n",
    "(12334,4),\n",
    "(12336,5),\n",
    "(555,6),\n",
    "(555,7),\n",
    "(12384,8),\n",
    "(64345,9),\n",
    "(4344,1),\n",
    "(866,1),\n",
    "(9875,1)\n",
    "]\n",
    "\n",
    "edges=[]\n",
    "\n",
    "#create amount of combinations\n",
    "for i in range(2000):\n",
    "    rand_comp=random.randint(0,11)\n",
    "    comp_hr=hr_court[rand_comp][0]\n",
    "    comp_court=hr_court[rand_comp][1]\n",
    "    \n",
    "    rand_person=random.randint(1,999)\n",
    "    rand_relation=random.randint(0,5)\n",
    "    rand_year_start=random.randint(2005,2023)\n",
    "    if rand_year_start<2023:\n",
    "        year_to=rand_year_start+1\n",
    "    else:\n",
    "        pass\n",
    "    #    year_to=None\n",
    "        \n",
    "    #edges.append((rand_company,df['Name'].iloc[rand_person],rand_year_start,year_to,relation[rand_relation]))    \n",
    "    edges.append((comp_hr,comp_court,rand_person,int(rand_year_start),year_to,relation[rand_relation]))    \n",
    "  \n",
    "#edges.to_csv('edges.csv')\n",
    "col=['Company_HR','Company_Court','Person_ID','Year_From','Year_To','Relation_Type']\n",
    "dfEdges=pd.DataFrame(edges,columns=col)\n",
    "dfEdges.to_csv('edges.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d0063c7",
   "metadata": {},
   "source": [
    "# Befülle die Tabelle Person-Beziehungen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "5ba301ea",
   "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>Unnamed: 0</th>\n",
       "      <th>Company_HR</th>\n",
       "      <th>Company_Court</th>\n",
       "      <th>Person_ID</th>\n",
       "      <th>Year_From</th>\n",
       "      <th>Year_To</th>\n",
       "      <th>Relation_Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>9875</td>\n",
       "      <td>1</td>\n",
       "      <td>877</td>\n",
       "      <td>2005</td>\n",
       "      <td>2006</td>\n",
       "      <td>Managing_Director</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5433</td>\n",
       "      <td>3</td>\n",
       "      <td>203</td>\n",
       "      <td>2008</td>\n",
       "      <td>2009</td>\n",
       "      <td>Auditor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>64345</td>\n",
       "      <td>9</td>\n",
       "      <td>336</td>\n",
       "      <td>2007</td>\n",
       "      <td>2008</td>\n",
       "      <td>Executive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>555</td>\n",
       "      <td>7</td>\n",
       "      <td>63</td>\n",
       "      <td>2012</td>\n",
       "      <td>2013</td>\n",
       "      <td>Executive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>64345</td>\n",
       "      <td>9</td>\n",
       "      <td>646</td>\n",
       "      <td>2016</td>\n",
       "      <td>2017</td>\n",
       "      <td>Auditor</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995</th>\n",
       "      <td>1995</td>\n",
       "      <td>555</td>\n",
       "      <td>6</td>\n",
       "      <td>296</td>\n",
       "      <td>2023</td>\n",
       "      <td>2012</td>\n",
       "      <td>Supervisory_Board</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1996</td>\n",
       "      <td>64566</td>\n",
       "      <td>2</td>\n",
       "      <td>643</td>\n",
       "      <td>2015</td>\n",
       "      <td>2016</td>\n",
       "      <td>Auditor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1997</td>\n",
       "      <td>64345</td>\n",
       "      <td>9</td>\n",
       "      <td>257</td>\n",
       "      <td>2011</td>\n",
       "      <td>2012</td>\n",
       "      <td>Auditor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1998</td>\n",
       "      <td>64345</td>\n",
       "      <td>9</td>\n",
       "      <td>277</td>\n",
       "      <td>2009</td>\n",
       "      <td>2010</td>\n",
       "      <td>Authorized_Representive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1999</td>\n",
       "      <td>866</td>\n",
       "      <td>1</td>\n",
       "      <td>369</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>Final_Auditor</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Unnamed: 0  Company_HR  Company_Court  Person_ID  Year_From  Year_To  \\\n",
       "0              0        9875              1        877       2005     2006   \n",
       "1              1        5433              3        203       2008     2009   \n",
       "2              2       64345              9        336       2007     2008   \n",
       "3              3         555              7         63       2012     2013   \n",
       "4              4       64345              9        646       2016     2017   \n",
       "...          ...         ...            ...        ...        ...      ...   \n",
       "1995        1995         555              6        296       2023     2012   \n",
       "1996        1996       64566              2        643       2015     2016   \n",
       "1997        1997       64345              9        257       2011     2012   \n",
       "1998        1998       64345              9        277       2009     2010   \n",
       "1999        1999         866              1        369       2010     2011   \n",
       "\n",
       "                Relation_Type  \n",
       "0           Managing_Director  \n",
       "1                     Auditor  \n",
       "2                   Executive  \n",
       "3                   Executive  \n",
       "4                     Auditor  \n",
       "...                       ...  \n",
       "1995        Supervisory_Board  \n",
       "1996                  Auditor  \n",
       "1997                  Auditor  \n",
       "1998  Authorized_Representive  \n",
       "1999            Final_Auditor  \n",
       "\n",
       "[2000 rows x 7 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('edges.csv', sep=',')  \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "64e79d92",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(df)):\n",
    "    #get data from dataframe\n",
    "    edges=Person_Relation( \n",
    "    company_hr = int(df['Company_HR'].iloc[i]),\n",
    "    company_court  = int(df['Company_Court'].iloc[i]),\n",
    "    person_id  = int(df['Person_ID'].iloc[i]),\n",
    "    date_from=datetime.strptime(str(df['Year_From'].iloc[i]), '%Y'),\n",
    "    date_to=datetime.strptime(str(df['Year_To'].iloc[i]), '%Y'),\n",
    "    relation = str(df['Relation_Type'].iloc[i])\n",
    ")\n",
    "    \n",
    "    session.add(edges)\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e83da6d1",
   "metadata": {},
   "source": [
    "# Abfragen in SQL Alchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c7ecb06",
   "metadata": {},
   "source": [
    "Der folgende Code-Snippet zeigt, wie man eine Abfrage gestaltet.\n",
    "\n",
    "```python\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column, Integer, String\n",
    "\n",
    "# Erstelle eine SQLite-Datenbankdatei oder gib den Pfad zur vorhandenen Datei an\n",
    "url = URL.create(\n",
    "    drivername=\"postgresql\",\n",
    "    username=\"postgres\",\n",
    "    password=\"postgres\",\n",
    "    host=\"localhost\",\n",
    "    database=\"postgres\"\n",
    ")\n",
    "\n",
    "#Erstelle eine Engine zur Verbindung mit der Datenbank\n",
    "engine = create_engine(url)\n",
    "\n",
    "#Erstelle eine Klasse, die eine Tabelle repräsentiert\n",
    "Base = declarative_base()\n",
    "class Company(Base):\n",
    "    __tablename__ = 'company'\n",
    "\n",
    "    hr = Column(Integer(), nullable=False, primary_key=True)\n",
    "    court_id = Column(Integer, ForeignKey(\"district_court.id\"), nullable=False, primary_key=True)\n",
    "    name = Column(String(100), nullable=False)\n",
    "    street = Column(String(100), nullable=False)\n",
    "    zip = Column(Integer(), nullable=False)\n",
    "    city = Column(String(100), nullable=False)\n",
    "    sector = Column(String(100), nullable=False)\n",
    "\n",
    "    __table_args__ = (\n",
    "        PrimaryKeyConstraint('hr', 'court_id', name='pk_company_hr_court'),\n",
    "    )\n",
    "\n",
    "#starte die Verbindung zur Datenbank\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "#Abfrage aller Spalten der Tabelle/Klasse Company\n",
    "Comps = session.query(Company).all()\n",
    "\n",
    "#Gebe die Spalten name, hr und court_id der Tabelle company aus\n",
    "for comp in Comps:\n",
    "    print(comp.name, comp.hr, comp.court_id)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "daacb128",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Volkswagen 12334 2\n",
      "Mercedes-Benz Group 64566 2\n",
      "Allianz 5433 3\n",
      "BMW Group 12334 4\n",
      "Deutsche Telekom 12336 5\n",
      "Deutsche Post DHL Group 555 6\n",
      "Bosch Group 555 7\n",
      "BASF 12384 8\n",
      "E.ON 64345 9\n",
      "Munich Re Group 4344 1\n",
      "Siemens 866 1\n",
      "Deutsche Bahn 9875 1\n"
     ]
    }
   ],
   "source": [
    "Comps = session.query(Company).all()\n",
    "\n",
    "for comp in Comps:\n",
    "    print(comp.name, comp.hr, comp.court_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36b76760",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "83a869b2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total_volume|ebit|ebitda\n",
      "29473.0 nan nan\n",
      "35946.0 nan nan\n",
      "32500.0 nan nan\n",
      "32216.0 nan nan\n",
      "33361.0 nan nan\n",
      "37537.0 nan nan\n",
      "42745.0 5830.0 nan\n",
      "52610.0 6750.0 nan\n",
      "57951.0 7316.0 nan\n",
      "62304.0 6463.0 9562.0\n",
      "50693.0 3677.0 7388.0\n",
      "63873.0 7761.0 11131.0\n",
      "73497.0 8586.0 11993.0\n",
      "72129.0 6742.0 10009.0\n",
      "73973.0 7160.0 10432.0\n",
      "74326.0 7626.0 11043.0\n",
      "70449.0 6248.0 10649.0\n",
      "57550.0 6275.0 10526.0\n",
      "61223.0 7587.0 10765.0\n",
      "60220.0 5974.0 8970.0\n",
      "59316.0 4201.0 8185.0\n",
      "59149.0 -191.0 6494.0\n",
      "78598.0 7677.0 11355.0\n",
      "87327.0 6548.0 10748.0\n"
     ]
    }
   ],
   "source": [
    "Fin = session.query(Finance).filter(Finance.company_hr == 12384).all()\n",
    "\n",
    "print(\"total_volume|ebit|ebitda\")\n",
    "for n in Fin:\n",
    "    print(n.total_volume, n.ebit, n.ebitda)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b807a00",
   "metadata": {},
   "outputs": [],
   "source": [
    "users = session.query(User).filter(User.name == 'John').all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d31ed85f",
   "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
}
