From 615cd66a873853f335177ec8b307c35be17d4b36 Mon Sep 17 00:00:00 2001 From: Simeon Simeonov Date: Wed, 21 Sep 2022 15:38:37 +0200 Subject: Add notebooks/python/python_oo.ipynb and update notebooks/sqlalchemy/sqlalchemy.ipynb --- notebooks/python/python_oo.ipynb | 274 +++++++++++++++++++++++ notebooks/sqlalchemy/sqlalchemy.ipynb | 398 ++++++++++++++++++++++++---------- 2 files changed, 561 insertions(+), 111 deletions(-) create mode 100644 notebooks/python/python_oo.ipynb (limited to 'notebooks') diff --git a/notebooks/python/python_oo.ipynb b/notebooks/python/python_oo.ipynb new file mode 100644 index 0000000..0b33a09 --- /dev/null +++ b/notebooks/python/python_oo.ipynb @@ -0,0 +1,274 @@ +{ + "cells": [ + { + "cell_type": "markdown", + "id": "2612fc97-1e84-4cc1-bd55-4cc58dbd5074", + "metadata": {}, + "source": [ + "# Introduction to object-oriented programming in Python\n", + "\n", + "Simeon Simeonov @ Statnett" + ] + }, + { + "cell_type": "markdown", + "id": "84139fa9-733e-4871-a2f9-920f6e7c4911", + "metadata": {}, + "source": [ + "# Goals\n", + "\n", + "- present the Python programming language in a different way than [https://docs.python.org](https://docs.python.org)\n", + "\n", + "- avoid information overload\n", + "\n", + "- use examples and interaction rather than documents and slides\n", + "\n", + "\n", + "## Target audience\n", + "\n", + "- beginner Python programmers\n", + "\n", + "- analysts using Python as a tool" + ] + }, + { + "cell_type": "markdown", + "id": "d6d3d0e7-c1d5-4119-940e-0f94ee7542ec", + "metadata": {}, + "source": [ + "# What is object-oriented programming?\n", + "\n", + "Object-oriented programming does **not** mean using an object-oriented programming language.\n", + "\n", + "It is rather a programming paradigm based on the concept of object, as well as on some general principles and best practices aiming at:\n", + "\n", + "- improving readability\n", + "\n", + "- improving re-usability\n", + "\n", + "- improving modularity\n", + "\n", + "- providing foundation for a more intuitive design" + ] + }, + { + "cell_type": "markdown", + "id": "ad19da5c-3c21-4130-9225-d8f7cb2a7f40", + "metadata": {}, + "source": [ + "# General principles\n", + "\n", + "The following four concepts / principles are presented in most object-oriented programming books:\n", + "\n", + "- separate and hide \"private\" details from the outside world and / or child (inheriting) functionality (*encapsulation*) - supports *separation of concerns*\n", + "\n", + "- separate the interface from its implementation (*abstraction*)\n", + "\n", + "- inherit and extend / adapt existing functionality, through \"is-a\" relationship hierarchy (*inheritance*)\n", + "\n", + "- execute different code / functionality based on the object's place in the hierarchy (polymorphism)" + ] + }, + { + "cell_type": "markdown", + "id": "22b2bf7e-764b-44c8-a9fd-83733319fc6e", + "metadata": {}, + "source": [ + "# Building blocks and definitions\n", + "\n", + "**Note:** \"Lacking universally accepted terminology to talk about classes, I will make occasional use of Smalltalk and C++ terms.\" - The Python tutorial [https://docs.python.org](https://docs.python.org)\n", + "\n", + "When talking about object-oriented programming, the following building blocks are involved:\n", + "\n", + "- class - a blueprint / template for creating objects\n", + "\n", + "- object - an instance of a class that may contain its own attributes as well as references to its class' attributes\n", + "\n", + "- attribute - variable, property, function defined in the class and present in its instances\n", + "\n", + "- class variable - attribute of which a single copy exists, regardless of how many instances of the class exist\n", + "\n", + "- object / instance variable - attribute for which each instantiated object of the class has a separate copy, or instance\n", + "\n", + "- method - member function - function that is an attribute" + ] + }, + { + "cell_type": "markdown", + "id": "9e15c843-f65b-4bd3-a536-e0217b3947f8", + "metadata": {}, + "source": [ + "# The object-oriented world of Python\n", + "\n", + "Some additional concepts and definitions are introduced in Python, as well as in some other programming languages:\n", + "\n", + "- metaclass - a class whose instances are classes\n", + "\n", + "- property - attribute that provides a flexible mechanism to read, write, or compute the value of a \"private\" attribute\n", + "\n", + "- type - old C object-oriented term, now (in Python 3) considered to be the same as class\n", + "\n", + "As is true for modules, classes partake of the dynamic nature of Python: they are created at runtime, and can be modified further after creation.\n", + "\n", + "Each object in Python has an ID - an integer which is guaranteed to be unique and constant for this object during its lifetime.\n", + "Two objects with non-overlapping lifetimes may have the same *id()* value. In *CPython* this is the address of the object in memory.\n", + "\n", + "Python has automatic memory management using reference counting. When an object no longer has\n", + "any references, the garbage collector kicks inn and removes the object from memory." + ] + }, + { + "cell_type": "markdown", + "id": "d647b1ad-948d-495d-bb30-15761b806354", + "metadata": {}, + "source": [ + "# Example1\n", + "\n", + "We will create and extend classes for representing points and vectors in 2D space" + ] + }, + { + "cell_type": "code", + "execution_count": 7, + "id": "391e816e-c53b-454b-9e9f-3587234f1fea", + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "2:8\n", + "2:8\n", + "2\n", + "2\n" + ] + }, + { + "ename": "AttributeError", + "evalue": "'Point' object has no attribute '__y'", + "output_type": "error", + "traceback": [ + "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", + "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)", + "Cell \u001b[0;32mIn [7], line 69\u001b[0m\n\u001b[1;32m 66\u001b[0m \u001b[38;5;66;03m# we \"should not\" be accessing private and protected attributes directly\u001b[39;00m\n\u001b[1;32m 67\u001b[0m \u001b[38;5;28mprint\u001b[39m(my_first_point\u001b[38;5;241m.\u001b[39m_x)\n\u001b[0;32m---> 69\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[43mmy_first_point\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m__y\u001b[49m)\n", + "\u001b[0;31mAttributeError\u001b[0m: 'Point' object has no attribute '__y'" + ] + } + ], + "source": [ + "class Point:\n", + " \"\"\"Basic class for representing points in 2D space\"\"\"\n", + "\n", + " def __init__(self, x: int, y: int):\n", + " \"\"\"\n", + " Called after the instance has been created (by __new__()), but before\n", + " it is returned to the caller. The arguments are those passed to the\n", + " class constructor expression.\n", + " \n", + " If a base class has an __init__() method, the derived class’s\n", + " __init__() method, if any, must explicitly call it to ensure proper\n", + " initialization of the base class part of the instance;\n", + " for example: super().__init__([args...]).\n", + " \"\"\"\n", + " self._x = x # _ indicates \"protected\" attribute\n", + " self.__y = y # not a typo: __ indicates \"private\" attribute\n", + "\n", + " def __repr__(self):\n", + " \"\"\"\n", + " Called by the repr() built-in function to compute the “official”\n", + " string representation of an object. If at all possible, this should\n", + " look like a valid Python expression that could be used to recreate an\n", + " object with the same value (given an appropriate environment). If this\n", + " is not possible, a string of the form <...some useful description...>\n", + " should be returned. The return value must be a string object.\n", + " \n", + " If a class defines __repr__() but not __str__(), then __repr__() is\n", + " also used when an “informal” string representation of instances of\n", + " that class is required. This is typically used for debugging, so it is\n", + " important that the representation is information-rich and unambiguous.\n", + " \"\"\"\n", + " return f\"\"\n", + "\n", + " def __str__(self):\n", + " \"\"\"\n", + " Called by str(object) and the built-in functions format() and print()\n", + " to compute the “informal” or nicely printable string representation of\n", + " an object. The return value must be a string object.\n", + "\n", + " This method differs from object.__repr__() in that there is no\n", + " expectation that __str__() return a valid Python expression: a more\n", + " convenient or concise representation can be used.\n", + "\n", + " The default implementation defined by the built-in type object calls\n", + " object.__repr__().\n", + " \"\"\"\n", + " return f\"{self._x}:{self.__y}\"\n", + "\n", + " @property\n", + " def x(self) -> int:\n", + " \"\"\"getter property x\"\"\"\n", + " return self._x\n", + "\n", + " @property\n", + " def y(self) -> int:\n", + " \"\"\"getter property y\"\"\"\n", + " return self.__y\n", + "\n", + "\n", + "my_first_point = Point(2, 8)\n", + "\n", + "print(f\"{my_first_point}\")\n", + "print(f\"{my_first_point}\")\n", + "print(my_first_point.x)\n", + "\n", + "# we \"should not\" be accessing private and protected attributes directly\n", + "print(my_first_point._x)\n", + "\n", + "print(my_first_point.__y) # will not work\n", + "# Out: AttributeError: 'Point' object has no attribute '__y'\n", + "\n", + "# will work, but should not be used by a sane programmer:\n", + "# print(my_first_point._Point__y)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "id": "e82feefb-025d-4cbb-a8ac-ddc3cc01269c", + "metadata": {}, + "outputs": [], + "source": [ + "class Vector:\n", + " \"\"\"Basic class for representing vectors in 2D space\"\"\"\n", + "\n", + " def __init__(self, start: Point, end: Point):\n", + " self._start = start\n", + " self._end = end\n", + "\n", + " def __str__(self):\n", + " return f\"{self._start} -> {self._end}\"\n" + ] + } + ], + "metadata": { + "kernelspec": { + "display_name": "Python 3 (ipykernel)", + "language": "python", + "name": "python3" + }, + "language_info": { + "codemirror_mode": { + "name": "ipython", + "version": 3 + }, + "file_extension": ".py", + "mimetype": "text/x-python", + "name": "python", + "nbconvert_exporter": "python", + "pygments_lexer": "ipython3", + "version": "3.8.10" + } + }, + "nbformat": 4, + "nbformat_minor": 5 +} diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb index ce0070c..51b00ab 100644 --- a/notebooks/sqlalchemy/sqlalchemy.ipynb +++ b/notebooks/sqlalchemy/sqlalchemy.ipynb @@ -90,7 +90,7 @@ }, { "cell_type": "code", - "execution_count": 12, + "execution_count": 11, "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", "metadata": { "slideshow": { @@ -114,7 +114,7 @@ }, { "cell_type": "code", - "execution_count": 13, + "execution_count": 12, "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", "metadata": { "slideshow": { @@ -126,20 +126,20 @@ "name": "stdout", "output_type": "stream", "text": [ - "2022-09-20 15:40:36,125 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", - "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", - "2022-09-20 15:40:36,126 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,127 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", - "2022-09-20 15:40:36,128 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", - "2022-09-20 15:40:36,129 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,130 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", - "2022-09-20 15:40:36,131 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", - "2022-09-20 15:40:36,132 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,133 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", - "2022-09-20 15:40:36,134 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,136 INFO sqlalchemy.engine.Engine \n", + "2022-09-21 09:05:41,263 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-21 09:05:41,264 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2022-09-21 09:05:41,265 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,267 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", + "2022-09-21 09:05:41,267 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,269 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2022-09-21 09:05:41,269 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,270 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", + "2022-09-21 09:05:41,271 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,272 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2022-09-21 09:05:41,272 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,274 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", + "2022-09-21 09:05:41,274 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,275 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE production_types (\n", "\tproduction_type_id INTEGER NOT NULL, \n", "\tcode VARCHAR(3) NOT NULL, \n", @@ -149,8 +149,8 @@ ")\n", "\n", "\n", - "2022-09-20 15:40:36,137 INFO sqlalchemy.engine.Engine [no key 0.00055s] ()\n", - "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine \n", + "2022-09-21 09:05:41,276 INFO sqlalchemy.engine.Engine [no key 0.00063s] ()\n", + "2022-09-21 09:05:41,278 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE bidding_areas (\n", "\tbidding_area_id INTEGER NOT NULL, \n", "\tcode VARCHAR(3) NOT NULL, \n", @@ -160,8 +160,8 @@ ")\n", "\n", "\n", - "2022-09-20 15:40:36,138 INFO sqlalchemy.engine.Engine [no key 0.00049s] ()\n", - "2022-09-20 15:40:36,140 INFO sqlalchemy.engine.Engine \n", + "2022-09-21 09:05:41,278 INFO sqlalchemy.engine.Engine [no key 0.00059s] ()\n", + "2022-09-21 09:05:41,280 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE production_plans (\n", "\trecord_created_time DATETIME NOT NULL, \n", "\tstart_time DATETIME NOT NULL, \n", @@ -174,8 +174,8 @@ ")\n", "\n", "\n", - "2022-09-20 15:40:36,141 INFO sqlalchemy.engine.Engine [no key 0.00068s] ()\n", - "2022-09-20 15:40:36,142 INFO sqlalchemy.engine.Engine COMMIT\n" + "2022-09-21 09:05:41,280 INFO sqlalchemy.engine.Engine [no key 0.00050s] ()\n", + "2022-09-21 09:05:41,281 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], @@ -293,7 +293,7 @@ }, { "cell_type": "code", - "execution_count": 14, + "execution_count": 13, "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5", "metadata": {}, "outputs": [ @@ -301,58 +301,58 @@ "name": "stdout", "output_type": "stream", "text": [ - "2022-09-20 15:40:36,151 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", - "2022-09-20 15:40:36,152 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,153 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,153 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", - "2022-09-20 15:40:36,155 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", - "2022-09-20 15:40:36,156 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,157 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", - "2022-09-20 15:40:36,158 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,160 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,160 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", - "2022-09-20 15:40:36,161 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", - "2022-09-20 15:40:36,162 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,163 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", - "2022-09-20 15:40:36,164 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,165 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", - "2022-09-20 15:40:36,166 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,168 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,169 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", - "2022-09-20 15:40:36,171 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", - "2022-09-20 15:40:36,172 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,173 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,174 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", - "2022-09-20 15:40:36,175 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", - "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,176 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", - "2022-09-20 15:40:36,177 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,179 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,179 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", - "2022-09-20 15:40:36,181 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", - "2022-09-20 15:40:36,181 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,182 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", - "2022-09-20 15:40:36,183 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,184 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", - "2022-09-20 15:40:36,185 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,186 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,186 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", - "2022-09-20 15:40:36,189 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", - "2022-09-20 15:40:36,189 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,191 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,191 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", - "2022-09-20 15:40:36,193 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", - "2022-09-20 15:40:36,194 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,195 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,196 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", - "2022-09-20 15:40:36,197 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", - "2022-09-20 15:40:36,197 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,199 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", - "2022-09-20 15:40:36,199 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,200 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", - "2022-09-20 15:40:36,201 INFO sqlalchemy.engine.Engine [raw sql] ()\n", - "2022-09-20 15:40:36,202 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", - "2022-09-20 15:40:36,202 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n" + "2022-09-21 09:05:41,292 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", + "2022-09-21 09:05:41,293 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,294 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,295 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,296 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", + "2022-09-21 09:05:41,297 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,298 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", + "2022-09-21 09:05:41,298 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,299 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,300 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,301 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2022-09-21 09:05:41,302 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,303 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2022-09-21 09:05:41,303 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,304 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", + "2022-09-21 09:05:41,305 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,306 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,307 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,309 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", + "2022-09-21 09:05:41,310 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,311 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,311 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,313 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,313 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,314 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,315 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,316 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,316 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,318 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,318 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,319 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,320 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,321 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", + "2022-09-21 09:05:41,322 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,323 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,323 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,326 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", + "2022-09-21 09:05:41,327 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,328 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,329 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2022-09-21 09:05:41,330 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", + "2022-09-21 09:05:41,331 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,331 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,332 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2022-09-21 09:05:41,334 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2022-09-21 09:05:41,334 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,336 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2022-09-21 09:05:41,336 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,337 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", + "2022-09-21 09:05:41,338 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,339 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,340 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n" ] } ], @@ -442,7 +442,7 @@ }, { "cell_type": "code", - "execution_count": 15, + "execution_count": 14, "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", "metadata": { "slideshow": { @@ -454,42 +454,42 @@ "name": "stdout", "output_type": "stream", "text": [ - "2022-09-20 15:40:36,217 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", - "2022-09-20 15:40:36,219 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", - "2022-09-20 15:40:36,219 INFO sqlalchemy.engine.Engine [generated in 0.00067s] ('NO1', 'Elspot NO1')\n", - "2022-09-20 15:40:36,221 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", - "2022-09-20 15:40:36,221 INFO sqlalchemy.engine.Engine [cached since 0.002563s ago] ('NO2', 'Elspot NO2')\n", - "2022-09-20 15:40:36,222 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", - "2022-09-20 15:40:36,223 INFO sqlalchemy.engine.Engine [cached since 0.004114s ago] ('NO3', 'Elspot NO3')\n", - "2022-09-20 15:40:36,224 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", - "2022-09-20 15:40:36,224 INFO sqlalchemy.engine.Engine [cached since 0.0056s ago] ('NO4', 'Elspot NO4')\n", - "2022-09-20 15:40:36,226 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", - "2022-09-20 15:40:36,226 INFO sqlalchemy.engine.Engine [cached since 0.007587s ago] ('NO5', 'Elspot NO5')\n", - "2022-09-20 15:40:36,228 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,228 INFO sqlalchemy.engine.Engine [generated in 0.00059s] ('B19', 'Wind Onshore')\n", - "2022-09-20 15:40:36,229 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,230 INFO sqlalchemy.engine.Engine [cached since 0.002172s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n", - "2022-09-20 15:40:36,231 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,232 INFO sqlalchemy.engine.Engine [cached since 0.003747s ago] ('B11', 'Hydro Run-of-river head installation')\n", - "2022-09-20 15:40:36,233 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,233 INFO sqlalchemy.engine.Engine [cached since 0.00528s ago] ('B12', 'Hydro-electric storage head installation')\n", - "2022-09-20 15:40:36,234 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,234 INFO sqlalchemy.engine.Engine [cached since 0.006383s ago] ('A04', 'Generation')\n", - "2022-09-20 15:40:36,236 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,236 INFO sqlalchemy.engine.Engine [cached since 0.00839s ago] ('B37', 'Thermal unspecified')\n", - "2022-09-20 15:40:36,237 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", - "2022-09-20 15:40:36,238 INFO sqlalchemy.engine.Engine [cached since 0.009993s ago] ('B30', 'Wind unspecified')\n", - "2022-09-20 15:40:36,240 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", - "2022-09-20 15:40:36,241 INFO sqlalchemy.engine.Engine [generated in 0.00109s] (('2022-09-20 15:40:36.216893', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\n", - "2022-09-20 15:40:36,242 INFO sqlalchemy.engine.Engine COMMIT\n", - "2022-09-20 15:40:36,243 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", - "2022-09-20 15:40:36,245 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", + "2022-09-21 09:05:41,358 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-21 09:05:41,360 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-21 09:05:41,360 INFO sqlalchemy.engine.Engine [generated in 0.00079s] ('NO1', 'Elspot NO1')\n", + "2022-09-21 09:05:41,361 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-21 09:05:41,362 INFO sqlalchemy.engine.Engine [cached since 0.002301s ago] ('NO2', 'Elspot NO2')\n", + "2022-09-21 09:05:41,364 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-21 09:05:41,364 INFO sqlalchemy.engine.Engine [cached since 0.004883s ago] ('NO3', 'Elspot NO3')\n", + "2022-09-21 09:05:41,366 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-21 09:05:41,366 INFO sqlalchemy.engine.Engine [cached since 0.006756s ago] ('NO4', 'Elspot NO4')\n", + "2022-09-21 09:05:41,367 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-21 09:05:41,368 INFO sqlalchemy.engine.Engine [cached since 0.008241s ago] ('NO5', 'Elspot NO5')\n", + "2022-09-21 09:05:41,370 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,370 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ('B19', 'Wind Onshore')\n", + "2022-09-21 09:05:41,371 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,372 INFO sqlalchemy.engine.Engine [cached since 0.00176s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n", + "2022-09-21 09:05:41,373 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,374 INFO sqlalchemy.engine.Engine [cached since 0.003672s ago] ('B11', 'Hydro Run-of-river head installation')\n", + "2022-09-21 09:05:41,375 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,375 INFO sqlalchemy.engine.Engine [cached since 0.00531s ago] ('B12', 'Hydro-electric storage head installation')\n", + "2022-09-21 09:05:41,376 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,377 INFO sqlalchemy.engine.Engine [cached since 0.00679s ago] ('A04', 'Generation')\n", + "2022-09-21 09:05:41,377 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,378 INFO sqlalchemy.engine.Engine [cached since 0.008175s ago] ('B37', 'Thermal unspecified')\n", + "2022-09-21 09:05:41,379 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-21 09:05:41,380 INFO sqlalchemy.engine.Engine [cached since 0.01012s ago] ('B30', 'Wind unspecified')\n", + "2022-09-21 09:05:41,382 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", + "2022-09-21 09:05:41,383 INFO sqlalchemy.engine.Engine [generated in 0.00075s] (('2022-09-21 09:05:41.357984', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\n", + "2022-09-21 09:05:41,384 INFO sqlalchemy.engine.Engine COMMIT\n", + "2022-09-21 09:05:41,385 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-21 09:05:41,387 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", - "2022-09-20 15:40:36,246 INFO sqlalchemy.engine.Engine [generated in 0.00069s] ('2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6)\n", - "2022-09-20 15:40:36,248 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", - "2022-09-20 15:40:36,248 INFO sqlalchemy.engine.Engine [generated in 0.00064s] (70.5, '2022-09-20 15:40:36.217038', '2022-11-02 02:00:00.000000', 1, 6)\n", - "2022-09-20 15:40:36,249 INFO sqlalchemy.engine.Engine COMMIT\n" + "2022-09-21 09:05:41,388 INFO sqlalchemy.engine.Engine [generated in 0.00093s] ('2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2022-09-21 09:05:41,390 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", + "2022-09-21 09:05:41,391 INFO sqlalchemy.engine.Engine [generated in 0.00054s] (70.5, '2022-09-21 09:05:41.358126', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2022-09-21 09:05:41,392 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], @@ -560,6 +560,182 @@ "session.commit()\n" ] }, + { + "cell_type": "code", + "execution_count": 15, + "id": "875195c5-ae99-4aa8-9aca-89d6e9c27ae2", + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "2022-09-21 09:05:41,400 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-21 09:05:41,402 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", + "2022-09-21 09:05:41,403 INFO sqlalchemy.engine.Engine [generated in 0.00078s] ()\n", + "2022-09-21 09:05:41,406 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", + "2022-09-21 09:05:41,406 INFO sqlalchemy.engine.Engine [generated in 0.00070s] ('2022-09-01 00:00:00.000000',)\n", + "2022-09-21 09:05:41,409 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", + "2022-09-21 09:05:41,410 INFO sqlalchemy.engine.Engine [generated in 0.00059s] (80,)\n", + "2022-09-21 09:05:41,412 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", + "2022-09-21 09:05:41,412 INFO sqlalchemy.engine.Engine [generated in 0.00056s] (80, 1, 0)\n", + "2022-09-21 09:05:41,414 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", + "2022-09-21 09:05:41,415 INFO sqlalchemy.engine.Engine [generated in 0.00066s] (80,)\n", + "2022-09-21 09:05:41,417 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2022-09-21 09:05:41,418 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,419 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", + "2022-09-21 09:05:41,420 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,421 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2022-09-21 09:05:41,423 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", + "2022-09-21 09:05:41,424 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,425 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,425 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2022-09-21 09:05:41,427 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", + "2022-09-21 09:05:41,427 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,428 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,429 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,430 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,432 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,432 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,434 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,434 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,436 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,436 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,437 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2022-09-21 09:05:41,438 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,439 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", + "2022-09-21 09:05:41,440 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,441 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,441 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2022-09-21 09:05:41,443 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", + "2022-09-21 09:05:41,443 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,445 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,445 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,446 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", + "2022-09-21 09:05:41,447 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,448 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", + "2022-09-21 09:05:41,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,450 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,450 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,452 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2022-09-21 09:05:41,452 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,453 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2022-09-21 09:05:41,454 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", + "2022-09-21 09:05:41,456 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,457 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,458 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2022-09-21 09:05:41,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2022-09-21 09:05:41,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,461 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2022-09-21 09:05:41,461 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,462 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", + "2022-09-21 09:05:41,462 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-21 09:05:41,464 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'\n", + "2022-09-21 09:05:41,464 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2022-09-21 09:05:41,466 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", + "2022-09-21 09:05:41,467 INFO sqlalchemy.engine.Engine [generated in 0.00114s] ()\n", + "2022-09-21 09:05:41,471 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", + "2022-09-21 09:05:41,472 INFO sqlalchemy.engine.Engine [generated in 0.00061s] (80,)\n", + "2022-09-21 09:05:41,475 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", + "2022-09-21 09:05:41,476 INFO sqlalchemy.engine.Engine [generated in 0.00097s] ('B37',)\n", + "2022-09-21 09:05:41,478 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", + "2022-09-21 09:05:41,478 INFO sqlalchemy.engine.Engine [generated in 0.00060s] ('B37',)\n", + "2022-09-21 09:05:41,481 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", + "2022-09-21 09:05:41,482 INFO sqlalchemy.engine.Engine [generated in 0.00098s] (6,)\n", + "2022-09-21 09:05:41,483 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", + "2022-09-21 09:05:41,484 INFO sqlalchemy.engine.Engine [generated in 0.00342s] (6,)\n", + "2022-09-21 09:05:41,486 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", + "2022-09-21 09:05:41,486 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ('B37',)\n", + "2022-09-21 09:05:41,492 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", + "2022-09-21 09:05:41,492 INFO sqlalchemy.engine.Engine [generated in 0.00065s] (1,)\n" + ] + }, + { + "name": "stderr", + "output_type": "stream", + "text": [ + "/tmp/ipykernel_611/3660144138.py:7: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n", + " session.query(ProductionPlan).order_by(ProductionPlan.start_time).all() # returns an object-list\n" + ] + }, + { + "data": { + "text/plain": [ + "[, bidding_area=, value=Decimal('80.5000000000'))>,\n", + " , bidding_area=, value=Decimal('70.5000000000'))>]" + ] + }, + "execution_count": 15, + "metadata": {}, + "output_type": "execute_result" + } + ], + "source": [ + "import pandas as pd\n", + "\n", + "from sqlalchemy import text\n", + "\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": "markdown", "id": "86336aaa-540c-466d-b5c1-f3fc179bd1e3", @@ -567,7 +743,7 @@ "source": [ "# TODO\n", "\n", - "- reflection\n" + "- delete\n" ] } ], -- cgit v1.3