path: root/notebooks/sqlalchemy/sqlalchemy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8d981972-51c1-4f44-98fb-47abefacffb7",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    },
    "tags": []
   },
   "source": [
    "# SQLAlchemy\n",
    "\n",
    "Simeon Simeonov @ Statnett\n",
    "\n",
    "\n",
    "## Agenda\n",
    "\n",
    "- Design & overview\n",
    "\n",
    "- A small practical example\n",
    "\n",
    "- General principles and best practices"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# What is SQLAlchemy?\n",
    "\n",
    "SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.\n",
    "\n",
    "SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Why use SQLAlchemy?\n",
    "\n",
    "- free software - free as in \"freedom\" (MIT licensed)\n",
    "\n",
    "- portability - the programming interface is independent of the type of RDBMS and connector used\n",
    "\n",
    "- security - no more SQL injections\n",
    "\n",
    "- abstraction - no need to bother with complex JOINs\n",
    "\n",
    "- object-orientation - you work with objects instead of tables and rows\n",
    "\n",
    "- performance - exploits the likehood of reusing a particular query\n",
    "\n",
    "- flexibility - you can override almost anything\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d68798f0-de17-44f9-b677-d7ee06409a9c",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Basic architecture\n",
    "\n",
    "- Engine- manages the connection pool and the RDBMS-independent SQL dialect layer\n",
    "\n",
    "- MetaData - used to collect and organize information about your table layout (schema)\n",
    "\n",
    "- SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)\n",
    "\n",
    "- ORM - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)\n",
    "\n",
    "- Session - establishes all conversations with the RDBMS and represents a \"holding zone\" for all the objects which you've loaded or associated with it during its lifespan\n",
    "\n",
    "![title](images/sqla_arch.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "# engine = create_engine(\"postgresql+psycopg2://user:zipassword@localhost/mydb\" , echo=True)\n",
    "# The string form of the URL is dialect+driver://user:password@host/d[?key=valuebname..],\n",
    "# where dialect is a database name such as mysql, oracle, postgresql, etc.,\n",
    "# and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle\n",
    "# The echo flag is a shortcut to setting up SQLAlchemy logging,\n",
    "# which is accomplished via Python’s standard logging module.\n",
    "\n",
    "# engine = create_engine(\"sqlite:///library.db\", echo=True)\n",
    "engine = create_engine(\"sqlite:///:memory:\", echo=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "3b4a777d-7d38-4149-8ab2-c876e2533766",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
      "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n",
      "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE production_types (\n",
      "\tproduction_type_id INTEGER NOT NULL, \n",
      "\tcode VARCHAR(3) NOT NULL, \n",
      "\tdescription VARCHAR, \n",
      "\tPRIMARY KEY (production_type_id), \n",
      "\tUNIQUE (code)\n",
      ")\n",
      "\n",
      "\n",
      "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n",
      "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE bidding_areas (\n",
      "\tbidding_area_id INTEGER NOT NULL, \n",
      "\tcode VARCHAR(3) NOT NULL, \n",
      "\tname VARCHAR(32), \n",
      "\tPRIMARY KEY (bidding_area_id), \n",
      "\tUNIQUE (code)\n",
      ")\n",
      "\n",
      "\n",
      "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n",
      "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine \n",
      "CREATE TABLE production_plans (\n",
      "\trecord_created_time DATETIME NOT NULL, \n",
      "\tstart_time DATETIME NOT NULL, \n",
      "\tbidding_area_id INTEGER NOT NULL, \n",
      "\tproduction_type_id INTEGER NOT NULL, \n",
      "\tvalue NUMERIC NOT NULL, \n",
      "\tPRIMARY KEY (record_created_time, start_time, bidding_area_id, production_type_id), \n",
      "\tFOREIGN KEY(bidding_area_id) REFERENCES bidding_areas (bidding_area_id), \n",
      "\tFOREIGN KEY(production_type_id) REFERENCES production_types (production_type_id)\n",
      ")\n",
      "\n",
      "\n",
      "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n",
      "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n",
    "from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship\n",
    "\n",
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "\n",
    "class ProductionType(Base):\n",
    "    __tablename__ = \"production_types\"\n",
    "\n",
    "    production_type_id = mapped_column(Integer, primary_key=True)\n",
    "    code = mapped_column(String(3), nullable=False, unique=True)\n",
    "    description = mapped_column(String)\n",
    "\n",
    "    production_plans = relationship(\n",
    "        \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n",
    "    )\n",
    "\n",
    "    def __init__(self, code, description):\n",
    "        self.code = code\n",
    "        self.description = description\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<ProductionType(production_type_id={self.production_type_id!r}, \"\n",
    "            f\"code={self.code!r}, description={self.description!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return self.code\n",
    "\n",
    "\n",
    "class BiddingArea(Base):\n",
    "    __tablename__ = \"bidding_areas\"\n",
    "\n",
    "    bidding_area_id = mapped_column(Integer, primary_key=True)\n",
    "    code = mapped_column(String(3), nullable=False, unique=True)\n",
    "    name = mapped_column(String(32))\n",
    "\n",
    "    production_plans = relationship(\n",
    "        \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n",
    "    )\n",
    "\n",
    "    def __init__(self, code, name):\n",
    "        self.code = code\n",
    "        self.name = name\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<BiddingArea(bidding_area_id={self.bidding_area_id!r}, \"\n",
    "            f\"code={self.code!r}, name={self.name!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return self.code\n",
    "\n",
    "\n",
    "class ProductionPlan(Base):\n",
    "    __tablename__ = \"production_plans\"\n",
    "\n",
    "    record_created_time = mapped_column(DateTime(timezone=False), primary_key=True)\n",
    "    start_time = mapped_column(DateTime(timezone=False), primary_key=True)\n",
    "    bidding_area_id = mapped_column(Integer, ForeignKey(\"bidding_areas.bidding_area_id\"), primary_key=True)\n",
    "    production_type_id = mapped_column(Integer, ForeignKey(\"production_types.production_type_id\"), primary_key=True)\n",
    "    value = mapped_column(Numeric, nullable=False)\n",
    "\n",
    "    # defining relationships.\n",
    "    # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects\n",
    "    production_type = relationship(ProductionType, back_populates=\"production_plans\")\n",
    "    bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n",
    "\n",
    "    def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n",
    "        self.record_created_time = record_created_time\n",
    "        self.start_time = start_time\n",
    "        self.production_type = production_type  # a 'ProductionType' object\n",
    "        self.bidding_area = bidding_area  # a 'BiddingArea' object\n",
    "        self.value = value\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<ProductionPlan(record_created_time={self.record_created_time!r}, \"\n",
    "            f\"start_time={self.start_time!r}, \"\n",
    "            f\"production_type={self.production_type!r}, \"\n",
    "            f\"bidding_area={self.bidding_area!r}, \"\n",
    "            f\"value={self.value!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return (\n",
    "            f\"{self.record_created_time} {self.start_time} \"\n",
    "            f\"{self.production_type} {self.bidding_area} {self.value}\"\n",
    "        )\n",
    "\n",
    "Base.metadata.create_all(engine)  # create tables\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab7e9561-2780-4d85-843c-b217d15ee4c8",
   "metadata": {},
   "source": [
    "# Reflection\n",
    "\n",
    "*Table* object can be instructed to load information about itself from the corresponding database schema object already existing within the database.\n",
    "This process is called *reflection*.\n",
    "\n",
    "If the DB schema is already defined and maintained \"somewhere else\", it may be useful to \"reflect\" the schema instead of explicitly defining it.\n",
    "\n",
    "**N.B. This is not the recommended way to use SQLAlchemy**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-09-08 09:45:43,387 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,391 INFO sqlalchemy.engine.Engine ROLLBACK\n",
      "2023-09-08 09:45:43,391 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine ROLLBACK\n",
      "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n",
      "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,399 INFO sqlalchemy.engine.Engine ROLLBACK\n"
     ]
    }
   ],
   "source": [
    "## N.B. This example does NOT represent SQLAlchemy - best practice\n",
    "from sqlalchemy import Table, Column\n",
    "from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped\n",
    "\n",
    "\n",
    "class Base(DeclarativeBase):\n",
    "    pass\n",
    "\n",
    "\n",
    "class ProductionType(Base):\n",
    "    __tablename__ = \"production_types\"\n",
    "    __table_args__ = {\"autoload_with\": engine}\n",
    "    # __table__ = Table(\"production_types\", Base.metadata, Column(\"description\", String(32)), autoload_with=engine)\n",
    "\n",
    "    # redefining the 'description' column\n",
    "    description = mapped_column(String(32))\n",
    "    production_plans = relationship(\n",
    "        \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n",
    "    )\n",
    "\n",
    "    def __init__(self, code, description):\n",
    "        self.code = code\n",
    "        self.description = description\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<ProductionType(production_type_id={self.production_type_id!r}, \"\n",
    "            f\"code={self.code!r}, description={self.description!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return self.code\n",
    "\n",
    "\n",
    "class BiddingArea(Base):\n",
    "    __tablename__ = \"bidding_areas\"\n",
    "    __table_args__ = {\"autoload_with\": engine}\n",
    "    # __table__ = Table(\"bidding_areas\", Base.metadata, autoload_with=engine)\n",
    "\n",
    "    production_plans = relationship(\n",
    "        \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n",
    "    )\n",
    "\n",
    "    def __init__(self, code, name):\n",
    "        self.code = code\n",
    "        self.name = name\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<BiddingArea(bidding_area_id={self.bidding_area_id!r}, \"\n",
    "            f\"code={self.code!r}, name={self.name!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return self.code\n",
    "\n",
    "\n",
    "class ProductionPlan(Base):\n",
    "    # __tablename__ = \"production_plans\"\n",
    "    # __table_args__ = {\"autoload_with\": engine}\n",
    "    # declarative mapping with imperative Table definition (A.K.A. Hybrid declarative)\n",
    "    __table__ = Table(\"production_plans\", Base.metadata, autoload_with=engine)\n",
    "\n",
    "    production_type = relationship(ProductionType, back_populates=\"production_plans\")\n",
    "    bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n",
    "\n",
    "    def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n",
    "        self.record_created_time = record_created_time\n",
    "        self.start_time = start_time\n",
    "        self.production_type = production_type  # a 'ProductionType' object\n",
    "        self.bidding_area = bidding_area  # a 'BiddingArea' object\n",
    "        self.value = value\n",
    "\n",
    "    def __repr__(self):\n",
    "        return (\n",
    "            f\"<ProductionPlan(record_created_time={self.record_created_time!r}, \"\n",
    "            f\"start_time={self.start_time!r}, \"\n",
    "            f\"production_type={self.production_type!r}, \"\n",
    "            f\"bidding_area={self.bidding_area!r}, \"\n",
    "            f\"value={self.value!r})>\"\n",
    "        )\n",
    "\n",
    "    def __str__(self):\n",
    "        return (\n",
    "            f\"{self.record_created_time} {self.start_time} \"\n",
    "            f\"{self.production_type} {self.bidding_area} {self.value}\"\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "1f18d9c1-bd39-4195-b868-114c3296eac2",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-09-08 09:45:43,405 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [generated in 0.00005s (insertmanyvalues) 1/5 (ordered; batch not supported)] ('NO1', 'Elspot NO1')\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/5 (ordered; batch not supported)] ('NO2', 'Elspot NO2')\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n",
      "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/5 (ordered; batch not supported)] ('NO3', 'Elspot NO3')\n",
      "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n",
      "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine [insertmanyvalues 4/5 (ordered; batch not supported)] ('NO4', 'Elspot NO4')\n",
      "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n",
      "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine [insertmanyvalues 5/5 (ordered; batch not supported)] ('NO5', 'Elspot NO5')\n",
      "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [generated in 0.00004s (insertmanyvalues) 1/7 (ordered; batch not supported)] ('B19', 'Wind Onshore')\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/7 (ordered; batch not supported)] ('B10', 'Hydro-electric pure pumped storage head installation')\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/7 (ordered; batch not supported)] ('B11', 'Hydro Run-of-river head installation')\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 4/7 (ordered; batch not supported)] ('B12', 'Hydro-electric storage head installation')\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 5/7 (ordered; batch not supported)] ('A04', 'Generation')\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 6/7 (ordered; batch not supported)] ('B37', 'Thermal unspecified')\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n",
      "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 7/7 (ordered; batch not supported)] ('B30', 'Wind unspecified')\n",
      "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n",
      "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine [generated in 0.00024s] [('2023-09-08 09:45:43.404962', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6, 90.5)]\n",
      "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2023-09-08 09:45:43,411 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,411 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id \n",
      "FROM production_plans \n",
      "WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n",
      "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ('2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6)\n",
      "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine UPDATE production_plans SET value=? WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n",
      "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (70.5, '2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6)\n",
      "2023-09-08 09:45:43,413 INFO sqlalchemy.engine.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# adding some data...\n",
    "import datetime\n",
    "import decimal\n",
    "\n",
    "from sqlalchemy.orm import Session\n",
    "\n",
    "session = Session(engine)\n",
    "# with Session(engine) as session:\n",
    "\n",
    "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n",
    "session.add(bidding_area1),\n",
    "\n",
    "session.add_all(\n",
    "    [\n",
    "        BiddingArea(\"NO2\", \"Elspot NO2\"),\n",
    "        BiddingArea(\"NO3\", \"Elspot NO3\"),\n",
    "        BiddingArea(\"NO4\", \"Elspot NO4\"),\n",
    "        BiddingArea(\"NO5\", \"Elspot NO5\"),\n",
    "    ]\n",
    "),\n",
    "\n",
    "production_type_B37 = ProductionType(\"B37\", \"Thermal unspecified\")\n",
    "production_type_B30 = ProductionType(\"B30\", \"Wind unspecified\")\n",
    "\n",
    "session.add_all(\n",
    "    [\n",
    "        ProductionType(\"B19\", \"Wind Onshore\"),\n",
    "        ProductionType(\"B10\", \"Hydro-electric pure pumped storage head installation\"),\n",
    "        ProductionType(\"B11\", \"Hydro Run-of-river head installation\"),\n",
    "        ProductionType(\"B12\", \"Hydro-electric storage head installation\"),\n",
    "        ProductionType(\"A04\", \"Generation\"),\n",
    "        production_type_B37,\n",
    "        production_type_B30,\n",
    "    ]\n",
    ")\n",
    "\n",
    "session.add(\n",
    "    ProductionPlan(\n",
    "        datetime.datetime.now(),\n",
    "        datetime.datetime(2022, 11, 2, 1, 0),\n",
    "        production_type_B37,\n",
    "        bidding_area1,\n",
    "        decimal.Decimal(\"80.5\"),\n",
    "    )\n",
    ")\n",
    "\n",
    "production_plan2 = ProductionPlan(\n",
    "    datetime.datetime.now(),\n",
    "    datetime.datetime(2022, 11, 2, 2, 0),\n",
    "    production_type_B37,\n",
    "    bidding_area1,\n",
    "    decimal.Decimal(\"90.5\"),\n",
    ")\n",
    "\n",
    "session.add(production_plan2)\n",
    "\n",
    "session.flush()  # execute pending operations\n",
    "session.commit()  # execute and commit pending operations (implicit flush)\n",
    "\n",
    "production_plan2.value = decimal.Decimal(\"70.5\")\n",
    "production_plan2 in session\n",
    "    # Out: True\n",
    "\n",
    "session.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "875195c5-ae99-4aa8-9aca-89d6e9c27ae2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-09-08 09:45:43,415 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,416 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,416 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()\n",
      "2023-09-08 09:45:43,417 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans \n",
      "WHERE production_plans.start_time > ?\n",
      "2023-09-08 09:45:43,417 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('2022-09-01 00:00:00.000000',)\n",
      "2023-09-08 09:45:43,418 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n",
      "FROM (SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time) AS anon_1\n",
      "2023-09-08 09:45:43,418 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (80,)\n",
      "2023-09-08 09:45:43,419 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      " LIMIT ? OFFSET ?\n",
      "2023-09-08 09:45:43,419 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (80, 1, 0)\n",
      "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine [generated in 0.00014s] (80,)\n",
      "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_temp_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n",
      "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n",
      "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n",
      "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
      "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,431 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans\n",
      "2023-09-08 09:45:43,431 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ()\n",
      "2023-09-08 09:45:43,432 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2023-09-08 09:45:43,432 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine [generated in 0.00026s] (80,)\n",
      "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine ROLLBACK\n",
      "2023-09-08 09:45:43,434 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans, production_types \n",
      "WHERE production_plans.production_type_id = production_types.production_type_id AND production_types.code = ?\n",
      "2023-09-08 09:45:43,434 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ('B37',)\n",
      "2023-09-08 09:45:43,435 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans JOIN production_types ON production_types.production_type_id = production_plans.production_type_id \n",
      "WHERE production_types.code = ?\n",
      "2023-09-08 09:45:43,435 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('B37',)\n",
      "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine SELECT production_types.production_type_id AS production_types_production_type_id, production_types.code AS production_types_code, production_types.description AS production_types_description \n",
      "FROM production_types \n",
      "WHERE production_types.production_type_id = ?\n",
      "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (6,)\n",
      "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n",
      "FROM production_plans \n",
      "WHERE ? = production_plans.production_type_id\n",
      "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine [generated in 0.00093s] (6,)\n",
      "2023-09-08 09:45:43,437 INFO sqlalchemy.engine.Engine SELECT pp.* FROM production_plans pp, production_types pt WHERE pp.production_type_id = pt.production_type_id AND pt.code=?\n",
      "2023-09-08 09:45:43,437 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('B37',)\n",
      "2023-09-08 09:45:43,439 INFO sqlalchemy.engine.Engine SELECT bidding_areas.bidding_area_id AS bidding_areas_bidding_area_id, bidding_areas.code AS bidding_areas_code, bidding_areas.name AS bidding_areas_name \n",
      "FROM bidding_areas \n",
      "WHERE bidding_areas.bidding_area_id = ?\n",
      "2023-09-08 09:45:43,439 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (1,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[<ProductionPlan(record_created_time=datetime.datetime(2023, 9, 8, 9, 45, 43, 404962), start_time=datetime.datetime(2022, 11, 2, 1, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('80.5000000000'))>,\n",
       " <ProductionPlan(record_created_time=datetime.datetime(2023, 9, 8, 9, 45, 43, 405033), start_time=datetime.datetime(2022, 11, 2, 2, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('70.5000000000'))>]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Old SQLAlchemy 1.x API - still working in SQLAlchemy 2\n",
    "import pandas as pd\n",
    "\n",
    "from sqlalchemy import text\n",
    "\n",
    "session.query(ProductionPlan).order_by(ProductionPlan.start_time)  # returns a Query instance\n",
    "session.query(ProductionPlan).order_by(ProductionPlan.start_time).all()  # returns an object-list\n",
    "\n",
    "# return all production plans where start_time after 2022-09-01 00:00\n",
    "session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()\n",
    "\n",
    "# return production plans with value > 80\n",
    "query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)\n",
    "query.count()  # returns 1\n",
    "production_plan = query.first()  # returns the first object (element)\n",
    "production_plan = query.one()  # raises NoResultFound exception or MultipleResultsFound in case elements != 1\n",
    "\n",
    "# generate Pandas DataFrame from a query or entire table\n",
    "df = pd.read_sql_table(\"production_plans\", con=session.get_bind())  # or con=engine\n",
    "df = pd.read_sql_query(query.statement, engine)\n",
    "\n",
    "# return production plans with production type 'B37'\n",
    "session.query(ProductionPlan).filter(\n",
    "    ProductionPlan.production_type_id == ProductionType.production_type_id,\n",
    "    ProductionType.code == \"B37\"\n",
    ").all()\n",
    "session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == \"B37\").all()\n",
    "session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()\n",
    "session.query(\n",
    "    ProductionPlan\n",
    ").from_statement(\n",
    "    text(\n",
    "        \"SELECT pp.* FROM production_plans pp, production_types pt \"\n",
    "        \"WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code\"\n",
    "    )\n",
    ").params(code=\"B37\").all()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c2f70746-485d-4d1d-ae6e-27e3cf70b99c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine [generated in 0.00041s] ()\n",
      "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine [cached since 0.0009604s ago] ()\n",
      "2023-09-08 09:45:43,444 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE production_plans.start_time > ?\n",
      "2023-09-08 09:45:43,444 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('2022-09-01 00:00:00.000000',)\n",
      "2023-09-08 09:45:43,445 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n",
      "FROM (SELECT production_plans.record_created_time AS record_created_time, production_plans.start_time AS start_time, production_plans.bidding_area_id AS bidding_area_id, production_plans.production_type_id AS production_type_id, production_plans.value AS value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time) AS anon_1\n",
      "2023-09-08 09:45:43,445 INFO sqlalchemy.engine.Engine [generated in 0.00020s] (80,)\n",
      "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine [generated in 0.00024s] (80,)\n",
      "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine [cached since 0.0007238s ago] (80,)\n",
      "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_temp_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n",
      "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n",
      "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n",
      "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n",
      "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n",
      "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n",
      "2023-09-08 09:45:43,452 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,452 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n",
      "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n",
      "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n",
      "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n",
      "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n",
      "2023-09-08 09:45:43,460 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n",
      "2023-09-08 09:45:43,460 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans\n",
      "2023-09-08 09:45:43,461 INFO sqlalchemy.engine.Engine [generated in 0.00023s] ()\n",
      "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine COMMIT\n",
      "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n",
      "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n",
      "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine [cached since 0.01647s ago] (80,)\n",
      "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine ROLLBACK\n",
      "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans, production_types \n",
      "WHERE production_plans.production_type_id = production_types.production_type_id AND production_types.code = ?\n",
      "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ('B37',)\n",
      "2023-09-08 09:45:43,464 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans JOIN production_types ON production_types.production_type_id = production_plans.production_type_id \n",
      "WHERE production_types.code = ?\n",
      "2023-09-08 09:45:43,464 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ('B37',)\n",
      "2023-09-08 09:45:43,465 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n",
      "FROM production_plans \n",
      "WHERE ? = production_plans.production_type_id\n",
      "2023-09-08 09:45:43,465 INFO sqlalchemy.engine.Engine [generated in 0.00026s] (6,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[<ProductionPlan(record_created_time=datetime.datetime(2023, 9, 8, 9, 45, 43, 404962), start_time=datetime.datetime(2022, 11, 2, 1, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('80.5000000000'))>,\n",
       " <ProductionPlan(record_created_time=datetime.datetime(2023, 9, 8, 9, 45, 43, 405033), start_time=datetime.datetime(2022, 11, 2, 2, 0), production_type=<ProductionType(production_type_id=6, code='B37', description='Thermal unspecified')>, bidding_area=<BiddingArea(bidding_area_id=1, code='NO1', name='Elspot NO1')>, value=Decimal('70.5000000000'))>]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# New SQLAlchemy 2 - API\n",
    "\n",
    "from sqlalchemy import func, select\n",
    "\n",
    "# Returns a sqlalchemy.sql.expression.Select instance (statement)\n",
    "# No actual SELECT statement(s) have been sent to the DB-server\n",
    "stmt = select(ProductionPlan).order_by(ProductionPlan.start_time)\n",
    "\n",
    "# sends the actual SELECT statement to the DB-server\n",
    "result = session.scalars(stmt)  # returns a sqlalchemy.engine.ScalarResult\n",
    "result.all()  # returns a list of objects (if any) and then \"closes\" the result\n",
    "result.first()  # N.B. will return None or raise an exeption since the Result has been \"closed\"\n",
    "\n",
    "session.scalars(select(ProductionPlan).order_by(ProductionPlan.start_time)).all()\n",
    "\n",
    "# return all production plans where start_time after 2022-09-01 00:00\n",
    "session.scalars(\n",
    "    select(\n",
    "        ProductionPlan\n",
    "    ).where(\n",
    "        ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)\n",
    "    )\n",
    ").all()\n",
    "\n",
    "# return production plans with value > 80\n",
    "stmt = select(ProductionPlan).where(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)\n",
    "session.execute(select(func.count()).select_from(stmt.subquery())).scalar_one()  # 1\n",
    "# N.B. Result (or ScalarResult) can be invoked only once\n",
    "production_plan = session.scalars(stmt).first()  # returns the first object (element)\n",
    "production_plan = session.scalars(stmt).one()  # raises NoResultFound exception or MultipleResultsFound in case elements != 1\n",
    "\n",
    "# generate Pandas DataFrame from a query or entire table\n",
    "df = pd.read_sql_table(\"production_plans\", con=session.get_bind())  # or con=engine\n",
    "df = pd.read_sql_query(stmt, engine)\n",
    "\n",
    "# return production plans with production type 'B37'\n",
    "session.scalars(\n",
    "    select(ProductionPlan).where(\n",
    "        ProductionPlan.production_type_id == ProductionType.production_type_id,\n",
    "        ProductionType.code == \"B37\"\n",
    "    )\n",
    ").all()\n",
    "session.scalars(\n",
    "    select(ProductionPlan).join(ProductionPlan.production_type).where(ProductionType.code == \"B37\")\n",
    ").all()\n",
    "session.scalars(\n",
    "    select(ProductionPlan).where(ProductionPlan.production_type == production_type_B37)\n",
    ").all()\n",
    "##session.query(\n",
    "##    ProductionPlan\n",
    "##).from_statement(\n",
    "##    text(\n",
    "##        \"SELECT pp.* FROM production_plans pp, production_types pt \"\n",
    "##        \"WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code\"\n",
    "##    )\n",
    "##).params(code=\"B37\").all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5b79cc1-bec7-4837-89a0-9fdbcbfd7fc0",
   "metadata": {},
   "source": [
    "# Some basic tips when using ORM models\n",
    "\n",
    " - **Business logic is implemented in the respective models.** Objects are responsible for their own actions.\n",
    " - All object-orientation rules (SOLID etc.) should apply\n",
    " - When is comes to overall design. in general always consider using composition (**has a** relationships) before considering inheritance (**is a** relationships)\n",
    " - Try to make the DBMS do the job. If not possible, try to make SQLAlchemy do it and only if that is not possible try to do it yourself\n",
    " - Analyze use cases and come up with a 'lazyness' strategy\n",
    " - Do not create relationships you don't need in your mapped classes\n",
    " - Session strategy should be based on a real world DB session use-case -> usually a new transaction is started, a series of jobs / queries are performed, the session is commited and finally closed. Context manager(s) may be a good idea.\n",
    " - Sharing a session object (if possible) instead of spawning several session objects and using them in parallel may guard you from unwanted locks, connection / cursor leaks and bugs in general.\n",
    " - An idea that can not be implemented using SQLAlchemy, may not always be a good idea :)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}