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 ++++++++++++----- reveal.js/sqlalchemy.html | 820 +++++++++++++++++++--------------- 3 files changed, 1022 insertions(+), 470 deletions(-) create mode 100644 notebooks/python/python_oo.ipynb 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" ] } ], diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html index 96f83e7..a0d6617 100755 --- a/reveal.js/sqlalchemy.html +++ b/reveal.js/sqlalchemy.html @@ -1,375 +1,477 @@ - - - SQLAlchemy - - - - - - - - - - - - - - - -
- - -
- -
-

SQLAlchemy

-

Data Science @ Statnett

+ + + SQLAlchemy + + + + + + + + + + + + + + + +
+ + +
+ +
+

SQLAlchemy

+

Data Engineering @ Statnett


-

Simeon Simeonov

-
+

Simeon Simeonov

+
-
+
-
-

Agenda

+
+

Agenda


    -
  • SQLAlchemy - Design & overview
    •  -
  • SQLAlchemy - A small practical example
    •  -
  • Q & A
    •  +
  • SQLAlchemy - Design & overview
    •  +
  • SQLAlchemy - A small practical example
    •  +
  • Q & A
    •
-
+
-
+
-
-

What is SQLAlchemy?

+
+

What is SQLAlchemy?


-

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.

-

SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).

-
+

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.

+

SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).

+
-
-

Why use SQLAlchemy?

+
+

Why use SQLAlchemy?


    -

  • free software - free as in "freedom" (MIT licensed)

    • -

  • portability - the programming interface is independent of the type of RDBMS and connector used

    • -

  • security - no more SQL injections

    • -

  • abstraction - no need to bother with complex JOINs

    • -

  • object-orientation - you work with objects instead of tables and rows

    • -

  • performance - exploits the likehood of reusing a particular query

    • -

  • flexibility - you can override almost anything

    • +

  • free software - free as in "freedom" (MIT licensed)

    • +

  • portability - the programming interface is independent of the type of RDBMS and connector used

    • +

  • security - no more SQL injections

    • +

  • abstraction - no need to bother with complex JOINs

    • +

  • object-orientation - you work with objects instead of tables and rows

    • +

  • performance - exploits the likehood of reusing a particular query

    • +

  • flexibility - you can override almost anything

-
- -
-

Basic architecture

-

SQLAlchemy consists of several components, including the ORM.

-
    -
  • Engine- manages the connection pool and the RDBMS-independent SQL dialect layer
    • -
  • MetaData - used to collect and organize information about your table layout (schema)
    • -
  • 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)
    • -
  • 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)
    • -
  • 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
    • -
- -
- -
-

Example

-

SQLAlchemy gives us the choice between classical mapping and the newer declarative mapping

- 
-            
-              import sqlalchemy
-              sqlalchemy.__version__
-              # Out: '1.3.23'
-
-              from sqlalchemy import create_engine
-
-              # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
-              # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
-              # where dialect is a database name such as mysql, oracle, postgresql, etc.,
-              # and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle
-              # The echo flag is a shortcut to setting up SQLAlchemy logging,
-              # which is accomplished via Python’s standard logging module.
-              # engine = create_engine("sqlite:///library.db", echo=True)
-              engine = create_engine("sqlite:///:memory:", echo=True)
-
-              from sqlalchemy import Column, ForeignKey, Integer, String, Table
-
-              metadata = MetaData()
-
-              authors_table = Table(
-                  "authors",
-                  metadata,
-                  Column("author_id", Integer, primary_key=True),
-                  Column("name", String),
-              )  # Column("name", String(50)) is possible
-
-              books_table = Table(
-                  "books",
-                  metadata,
-                  Column("book_id", Integer, primary_key=True),
-                  Column("title", String),
-                  Column("description", String),
-                  Column("author_id", ForeignKey('authors.author_id')),
-              )
-
-              metadata.create_all(engine)  # creates the tables
-            
-
-
- -
-

Example (cont...)

-

Use of SQL expression language

- 
-            
-              insert_stmt = authors_table.insert(bind=engine)
-              type(insert_stmt)
-              # Out: <class 'sqlalchemy.sql.expression.Insert'>
-              print(insert_stmt)
-              # Out: INSERT INTO authors (id, name) VALUES (:id,:name)
-
-              compiled_stmt = insert_stmt.compile()
-              print(compiled_stmt.params)
-              # Out: {'id': None, 'name': None}
-
-              insert_stmt.execute(name="Alexandre Dumas")  # insert a single entry
-              insert_stmt.execute([{"name": "Mr X"}, {"name": "Mr Y"}])  # a list of entries
-
-              metadata.bind = engine  # no need to explicitly bind the engine from now on
-              select_stmt = authors_table.select(authors_table.c.id==2)
-              result = select_stmt.execute()
-              result.fetchall()
-              # Out: [(1, 'Mr X')]
-
-              del_stmt = authors_table.delete()
-              del_stmt.execute(whereclause=text("name='Mr Y'"))
-              del_stmt.execute()  # delete all
-            
-
-
- -
-

Example (cont...)

-

Use of classical mapping

- 
-            
-              from sqlalchemy.orm import backref, mapper, relation
-
-              class Author:
-                  def __init__(self, name):
-                      self.name = name
-
-                  def __str__(self):
-                      return self.name
-
-
-              class Book:
-                  def __init__(self, title, description, author):
-                      self.title = title
-                      self.description = description
-                      self.author = author
-
-                  def __str__(self):
-                      return self.title
-
-              mapper(Book, books_table)
-              mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")})
-            
-
-
- -
-

Example (cont...)

-

Doing the same thing the easy way with declarative mapping

- 
-            
-              from sqlalchemy.ext.declarative import declarative_base
-              from sqlalchemy.orm import relationship, backref
-
-              Base = declarative_base()
-
-              class Author(Base):
-                  __tablename__ = "authors"
-
-                  author_id = Column(Integer, primary_key=True)
-                  name = Column(String)
-
-                  def __init__(self, name):
-                      self.name = name
-
-                  def __str__(self):
-                      return self.name
-
-
-              class Book(Base):
-                  __tablename__ = "books"  # self.__table__ will be available for our objects
-
-                  book_id = Column(Integer, primary_key=True)
-                  title = Column(String)
-                  description = Column(String)
-                  author_id = Column(Integer, ForeignKey("authors.author_id"))
-                  author = relationship(Author, backref=backref("books", order_by=title))
-
-                  def __init__(self, title, description, author):
-                      self.title = title
-                      self.description = description
-                      self.author = author
-
-                  def __str__(self):
-                      return self.title
-
-              Base.metadata.create_all(engine)  # create tables
-            
-
-
- -
-

Example (cont...)

-

Creating instances

- 
-            
-              from sqlalchemy.orm import sessionmaker
-
-              Session = sessionmaker(bind=engine)  # bound session
-              session = Session()
-
-              author_1 = Author("Richard Dawkins")
-              author_2 = Author("Matt Ridley")
-
-              book_1 = Book("The Red Queen", "A popular science book", author_2)
-              book_2 = Book("The Selfish Gene", "A popular science book", author_1)
-              book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1)  # typo
-
-              session.add(author_1)
-              session.add(author_2)
-              session.add(book_1)
-              session.add(book_2)
-              session.add(book_3)
-              # or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
-
-              # session.flush()
-              session.commit()  # flushes (issues the statements and sends them to the RDBMS) and commits
-
-              book_3.description = "The theory of evolution"  # update the object
-              book_3 in session   # check whether the object is in the session
-              # Out: True
-
-              session.commit()
-            
-
-
- -
-

Example (cont...)

-

Queries

- 
-            
-              session.query(Book).order_by(Book.book_id)  # returns a Query instance with a .statement attribute
-              session.query(Book).order_by(Book.book_id).all()  # returns an object-list
-
-              # return all book objects where title == "The Selfish Gene"
-              session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.book_id).all()
-
-              # using LIKE
-              session.query(Book).filter(Book.title.like("The%")).order_by(Book.book_id).all()
-
-              query = session.query(Book).filter(Book.book_id == 9).order_by(Book.book_id)
-              query.count()  # returns 0
-              query.all()  # returns an empty list
-              query.first()  # returns None
-              query.one()  # raises NoResultFound exception
-
-              query = session.query(Book).filter(Book.book_id == 1).order_by(Book.book_id)
-              book_1 = query.one()
-              book_1.description  # returns "A popular science book"
-              book_1.author.books  # returns a list of Book-objects representing all the books from the same author.
-
-              # get a list of all Book-instances where the author"s name is "Richard Dawkins"
-              session.query(Book).filter(Book.author_id == Author.author_id).filter(Author.name == "Richard Dawkins").all()
-              session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
-              session.query(Book).\
-                  from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.author_id AND a.name=:name").\
-                      params(name="Richard Dawkins").all()
-              session.query(Book).filter(Book.author == author_1).all()
-            
-
-
- -
-

Some nice features

- 
-            
-              import pandas as pd
-
-              from sqlalchemy import func
-
-              class Book(Base):
-                  # ...
-                  author = relationship(
-                      Author, backref=backref("books", lazy="dynamic", order_by=title)
-                  )
-                  # ...
-
-                  @hybrid_property
-                  def newly_arrived(self):
-                      return self.book_id > 2
-
-                  @newly_arrived.expression
-                  def newly_arrived(cls):
-                      return cls.book_id > 2
-                      # return func.abs(cls.book_id) > 2
-
-              # .books is now a Query object
-              query = author_obj.books.filter(Book.title.ilike("%red%"))
-
-              session.query(Book).filter(Book.newly_arrived.is_(True)).all()
-              # Out: [<__main__.Book at 0x7f132bdf0130>]
-              # WHERE (abs(books.book_id) > ?) IS 1  ... in the case of func.abs
-
-              # with Pandas
-              df = pd.read_sql_table("my_table", con=session.get_bind())  # or con=engine
-
-              df = pd.read_sql_query(query.statement, engine)
-
-            
-
-
- -
-

Q & A

-
- -
-
- - - - - - - - - - + + +
+

Basic architecture

+

SQLAlchemy consists of several components, including the ORM.

+
    +
  • Engine- manages the connection pool and the RDBMS-independent SQL dialect layer
    • +
  • MetaData - used to collect and organize information about your table layout (schema)
    • +
  • 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)
    • +
  • 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)
    • +
  • 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
    • +
+ +
+ +
+

Example

+

SQLAlchemy gives us the choice between classical mapping and the newer declarative mapping

+ 
+                        
+                            # option 1: classical mapping
+                            # explicitly defining Table objects and mapping them to pure Python base classes
+                            from sqlalchemy import create_engine
+
+                            # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
+                            # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
+                            # engine = create_engine("sqlite:///library.db", echo=True)
+                            engine = create_engine("sqlite:///:memory:", echo=True)
+
+                            from sqlalchemy import Column, MetaData, Table
+                            from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String
+
+                            metadata = MetaData()
+
+                            production_types_table = Table(
+                                "production_types",
+                                metadata,
+                                Column("production_type_id", Integer, primary_key=True),
+                                Column("code", String(3), nullable=False, unique=True),
+                                Column("description", String),  # Column("name", String(128)) is possible
+                            )
+
+                            bidding_areas_table = Table(
+                                "bidding_areas",
+                                metadata,
+                                Column("bidding_area_id", Integer, primary_key=True),
+                                Column("code", String(3), nullable=False, unique=True),
+                                Column("name", String(32)),
+                            )
+
+                            production_plans_table = Table(
+                                "production_plans",
+                                metadata,
+                                Column("record_created_time", DateTime(timezone=False), primary_key=True),
+                                Column("start_time", DateTime(timezone=False), primary_key=True),
+                                Column("bidding_area_id", Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True),
+                                Column("production_type_id", Integer, ForeignKey("production_types.production_type_id"), primary_key=True),
+                                Column("value", Numeric, nullable=False),
+                            )
+
+                            metadata.create_all(engine)  # creates the tables
+                        
+
+
+ +
+

Example (cont...)

+

Use of SQL expression language

+ 
+                        
+                            # option 1: classical mapping (continues)
+                            # Using the SQL expression language (low level interface)
+                            from sqlalchemy import text
+
+                            insert_stmt = bidding_areas_table.insert(bind=engine)
+                            type(insert_stmt)
+                            # Out: <class 'sqlalchemy.sql.dml.Insert'>
+                            print(insert_stmt)
+                            # Out: INSERT INTO bidding_areas (bidding_area_id, code, name) VALUES (?, ?, ?)
+
+                            compiled_stmt = insert_stmt.compile()
+                            print(compiled_stmt.params)
+                            # Out: {'bidding_area_id': None, 'code': None, 'name': None}
+
+                            insert_stmt.execute(bidding_area_id=1, code="NO1", name="Elspot NO1")  # insert a single entry
+                            # ... or a list of entries
+                            insert_stmt.execute(
+                                [
+                                    {"bidding_area_id": 2, "code": "NO2", "name": "Elspot NO2"},
+                                    {"bidding_area_id": 3, "code": "NO3", "name": "Elspot NO3"},
+                                    {"bidding_area_id": 4, "code": "NO4", "name": "Elspot NO4"},
+                                    {"bidding_area_id": 5, "code": "NO5", "name": "Elspot NO5"},
+                                    {"bidding_area_id": 6, "code": "NO6", "name": "Elspot NO6"},
+                                ]
+                            )
+
+                            metadata.bind = engine  # no need to explicitly bind the engine from now on
+                            select_stmt = bidding_areas_table.select(bidding_areas_table.c.bidding_area_id==2)
+                            result = select_stmt.execute()
+                            result.fetchall()
+                            # Out: [(2, 'NO2', 'Elspot NO2')]
+
+                            del_stmt = bidding_areas_table.delete()
+                            del_stmt.execute(whereclause=text("name='Elspot NO6'"))
+                            del_stmt.execute()  # delete NO6
+                        
+
+
+ +
+

Example (cont...)

+

Use of classical mapping

+ 
+                        
+                            # option 1: classical mapping (continues)
+                            # Defining regular base classes and mapping them to the Table objects
+                            from sqlalchemy.orm import mapper
+
+                            class ProductionType:
+                                def __init__(self, code, description):
+                                    self.code = code
+                                    self.description = description
+
+                                def __str__(self):
+                                    return self.code
+
+
+                            class BiddingArea:
+                                def __init__(self, code, name):
+                                    self.code = code
+                                    self.name = name
+
+                                def __str__(self):
+                                    return self.code
+
+                            mapper(ProductionType, production_types_table)
+                            mapper(BiddingArea, bidding_areas_table)
+                        
+
+
+ +
+

Example (cont...)

+

Use of classical mapping

+ 
+                        
+                            from sqlalchemy.orm import relationship
+
+                            class ProductionPlan:
+                                def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
+                                    self.record_created_time = record_created_time
+                                    self.start_time = start_time
+                                    self.production_type = production_type
+                                    self.bidding_area = bidding_area
+                                    self.value = value
+
+                                def __str__(self):
+                                    return (
+                                        f"{self.record_created_time} {self.start_time} "
+                                        f"{self.production_type} {self.bidding_area} {self.value}"
+                                    )
+
+
+                            mapper(
+                                ProductionPlan,
+                                production_plans_table,
+                                properties = {
+                                    "production_type": relationship(ProductionType, backref="production_plans"),
+                                    "bidding_area": relationship(BiddingArea, backref="production_plans"),
+                                },
+                            )
+                        
+
+
+ +
+

Example (cont...)

+

Doing the same thing the easy way with declarative mapping

+ 
+                        
+                            # option 2: declarative mapping
+                            from sqlalchemy.ext.declarative import declarative_base
+
+                            Base = declarative_base()
+
+                            class ProductionType(Base):
+                                __tablename__ = "production_types"
+
+                                production_type_id = Column(Integer, primary_key=True)
+                                code = Column(String(3), nullable=False, unique=True)
+                                description = Column(String)
+
+                                def __init__(self, code, description):
+                                    self.code = code
+                                    self.description = description
+
+                                def __str__(self):
+                                    return self.code
+
+                            class BiddingArea(Base):
+                                __tablename__ = "bidding_areas"
+
+                                bidding_area_id = Column(Integer, primary_key=True)
+                                code = Column(String(3), nullable=False, unique=True)
+                                name = Column(String(32))
+
+                                def __init__(self, code, name):
+                                    self.code = code
+                                    self.name = name
+
+                                def __str__(self):
+                                    return self.code
+                        
+
+
+ +
+

Example (cont...)

+

Doing the same thing the easy way with declarative mapping

+ 
+                        
+                            # option 2: declarative mapping (continues)
+                            from sqlalchemy.orm import relationship, backref
+
+                            class ProductionPlan(Base):
+                                __tablename__ = "production_plans"
+
+                                record_created_time = Column(DateTime(timezone=False), primary_key=True)
+                                start_time = Column(DateTime(timezone=False), primary_key=True)
+                                bidding_area_id = Column(Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True)
+                                production_type_id = Column(Integer, ForeignKey("production_types.production_type_id"), primary_key=True)
+                                value = Column(Numeric, nullable=False)
+
+                                # defining relationships.
+                                # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects
+                                production_type = relationship(ProductionType, backref=backref("production_plans"))
+                                bidding_area = relationship(BiddingArea, backref=backref("production_plans"))
+
+                                def __init__(self, record_created_time, start_time, production_type, bidding_area, value):
+                                    self.record_created_time = record_created_time
+                                    self.start_time = start_time
+                                    self.production_type = production_type  # a 'ProductionType' object
+                                    self.bidding_area = bidding_area  # a 'BiddingArea' object
+                                    self.value = value
+
+                                def __str__(self):
+                                    return (
+                                        f"{self.record_created_time} {self.start_time} "
+                                        f"{self.production_type} {self.bidding_area} {self.value}"
+                                    )
+
+                            Base.metadata.create_all(engine)  # create tables
+                        
+
+
+ +
+

Example (cont...)

+

Creating instances

+ 
+                        
+                            # adding some data...
+                            import datetime
+                            import decimal
+
+                            from sqlalchemy.orm import sessionmaker
+
+                            Session = sessionmaker(bind=engine)  # bound session
+                            session = Session()
+
+                            bidding_area1 = BiddingArea("NO1", "Elspot NO1")
+                            session.add(bidding_area1)
+
+                            session.add_all(
+                                [
+                                    BiddingArea("NO2", "Elspot NO2"),
+                                    BiddingArea("NO3", "Elspot NO3"),
+                                    BiddingArea("NO4", "Elspot NO4"),
+                                    BiddingArea("NO5", "Elspot NO5"),
+                                ]
+                            )
+
+                            production_type_B37 = ProductionType("B37", "Thermal unspecified")
+                            production_type_B30 = ProductionType("B30", "Wind unspecified")
+
+                            session.add_all(
+                                [
+                                    ProductionType("B19", "Wind Onshore"),
+                                    ProductionType("B10", "Hydro-electric pure pumped storage head installation"),
+                                    ProductionType("B11", "Hydro Run-of-river head installation"),
+                                    ProductionType("B12", "Hydro-electric storage head installation"),
+                                    ProductionType("A04", "Generation"),
+                                    production_type_B37,
+                                    production_type_B30,
+                                ]
+                            )
+                        
+
+
+ +
+

Example (cont...)

+

Creating instances

+ 
+                        
+                            # adding some production plans...
+
+                            session.add(
+                                ProductionPlan(
+                                    datetime.datetime.now(),
+                                    datetime.datetime(2022, 11, 2, 1, 0),
+                                    production_type_B37,
+                                    bidding_area1,
+                                    decimal.Decimal("80.5"),
+                                )
+                            )
+
+                            production_plan2 = ProductionPlan(
+                                datetime.datetime.now(),
+                                datetime.datetime(2022, 11, 2, 2, 0),
+                                production_type_B37,
+                                bidding_area1,
+                                decimal.Decimal("90.5"),
+                            )
+
+                            session.add(production_plan2)
+
+                            session.flush()  # execute pending operations
+                            session.commit()  # execute and commit pending operations
+
+                            production_plan2.value = decimal.Decimal("70.5")
+                            production_plan2 in session
+                            # Out: True
+
+                            session.commit()
+                        
+
+
+ +
+

Example (cont...)

+

Queries

+ 
+                        
+                            import pandas as pd
+
+                            session.query(ProductionPlan).order_by(ProductionPlan.start_time)  # returns a Query instance
+                            session.query(ProductionPlan).order_by(ProductionPlan.start_time).all()  # returns an object-list
+
+                            # return all production plans where start_time after 2022-09-01 00:00
+                            session.query(ProductionPlan).filter(ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)).all()
+
+                            # return production plans with value > 80
+                            query = session.query(ProductionPlan).filter(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)
+                            query.count()  # returns 1
+                            production_plan = query.first()  # returns the first object (element)
+                            production_plan = query.one()  # raises NoResultFound exception or MultipleResultsFound in case elements != 1
+
+                            # generate Pandas DataFrame from a query or entire table
+                            df = pd.read_sql_table("my_table", con=session.get_bind())  # or con=engine
+                            df = pd.read_sql_query(query.statement, engine)
+
+                            # return production plans with production type 'B37'
+                            session.query(ProductionPlan).filter(
+                                ProductionPlan.production_type_id == ProductionType.production_type_id
+                            ).filter(ProductionType.code == "B37").all()
+                            session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == "B37").all()
+                            session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all()
+                            session.query(
+                                ProductionPlan
+                            ).from_statement(
+                                text(
+                                    "SELECT pp.* FROM production_plans pp, production_types pt "
+                                    "WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code"
+                                )
+                            ).params(code="B37").all()
+                        
+
+
+ +
+

Q & A

+
+ + + + + + + + + + + + + -- cgit v1.3