From 0e50a48d04184aac1d940369590599697d0a874b Mon Sep 17 00:00:00 2001 From: Simeon Simeonov Date: Tue, 20 Sep 2022 09:10:35 +0200 Subject: Add notebooks presentations folder and add sqlalchemy --- notebooks/sqlalchemy/sqlalchemy.ipynb | 379 ++++++++++++++++++++++++++++++++++ 1 file changed, 379 insertions(+) create mode 100644 notebooks/sqlalchemy/sqlalchemy.ipynb (limited to 'notebooks/sqlalchemy/sqlalchemy.ipynb') diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb new file mode 100644 index 0000000..f2f65cc --- /dev/null +++ b/notebooks/sqlalchemy/sqlalchemy.ipynb @@ -0,0 +1,379 @@ +{ + "cells": [ + { + "cell_type": "markdown", + "id": "f37249e7-a474-407f-ae2e-17def087bd72", + "metadata": { + "tags": [] + }, + "source": [ + "# SQLAlchemy\n", + "\n", + "Simeon Simeonov @ Statnett\n", + "\n", + "\n", + "## Agenda\n", + "\n", + "- Design & overview\n", + "\n", + "- A small practical example\n" + ] + }, + { + "cell_type": "markdown", + "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb", + "metadata": {}, + "source": [ + "# What is SQLAlchemy?\n", + "\n", + "SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.\n", + "\n", + "SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).\n" + ] + }, + { + "cell_type": "markdown", + "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b", + "metadata": {}, + "source": [ + "# Why use SQLAlchemy?\n", + "\n", + "- free software - free as in \"freedom\" (MIT licensed)\n", + "\n", + "- portability - the programming interface is independent of the type of RDBMS and connector used\n", + "\n", + "- security - no more SQL injections\n", + "\n", + "- abstraction - no need to bother with complex JOINs\n", + "\n", + "- object-orientation - you work with objects instead of tables and rows\n", + "\n", + "- performance - exploits the likehood of reusing a particular query\n", + "\n", + "- flexibility - you can override almost anything\n" + ] + }, + { + "cell_type": "markdown", + "id": "d68798f0-de17-44f9-b677-d7ee06409a9c", + "metadata": {}, + "source": [ + "# Basic architecture\n", + "\n", + "SQLAlchemy consists of several components, including the ORM.\n", + "\n", + "- Engine- manages the connection pool and the RDBMS-independent SQL dialect layer\n", + "\n", + "- MetaData - used to collect and organize information about your table layout (schema)\n", + "\n", + "- SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)\n", + "\n", + "- ORM - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)\n", + "\n", + "- Session - establishes all conversations with the RDBMS and represents a \"holding zone\" for all the objects which you've loaded or associated with it during its lifespan\n", + "\n", + "![title](images/sqla_arch.png)" + ] + }, + { + "cell_type": "code", + "execution_count": 1, + "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", + "metadata": {}, + "outputs": [], + "source": [ + "from sqlalchemy import create_engine\n", + "\n", + "# engine = create_engine(\"postgresql+psycopg2://user:zipassword@localhost/mydb\" , echo=True)\n", + "# The string form of the URL is dialect+driver://user:password@host/d[?key=valuebname..],\n", + "# where dialect is a database name such as mysql, oracle, postgresql, etc.,\n", + "# and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle\n", + "# The echo flag is a shortcut to setting up SQLAlchemy logging,\n", + "# which is accomplished via Python’s standard logging module.\n", + "\n", + "# engine = create_engine(\"sqlite:///library.db\", echo=True)\n", + "engine = create_engine(\"sqlite:///:memory:\", echo=True)" + ] + }, + { + "cell_type": "code", + "execution_count": 2, + "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "2022-09-19 15:33:48,105 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-19 15:33:48,106 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2022-09-19 15:33:48,107 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,108 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", + "2022-09-19 15:33:48,108 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,110 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2022-09-19 15:33:48,110 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,112 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", + "2022-09-19 15:33:48,112 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,113 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2022-09-19 15:33:48,113 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,115 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", + "2022-09-19 15:33:48,115 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine \n", + "CREATE TABLE production_types (\n", + "\tproduction_type_id INTEGER NOT NULL, \n", + "\tcode VARCHAR(3) NOT NULL, \n", + "\tdescription VARCHAR, \n", + "\tPRIMARY KEY (production_type_id), \n", + "\tUNIQUE (code)\n", + ")\n", + "\n", + "\n", + "2022-09-19 15:33:48,117 INFO sqlalchemy.engine.Engine [no key 0.00045s] ()\n", + "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine \n", + "CREATE TABLE bidding_areas (\n", + "\tbidding_area_id INTEGER NOT NULL, \n", + "\tcode VARCHAR(3) NOT NULL, \n", + "\tname VARCHAR(32), \n", + "\tPRIMARY KEY (bidding_area_id), \n", + "\tUNIQUE (code)\n", + ")\n", + "\n", + "\n", + "2022-09-19 15:33:48,119 INFO sqlalchemy.engine.Engine [no key 0.00051s] ()\n", + "2022-09-19 15:33:48,121 INFO sqlalchemy.engine.Engine \n", + "CREATE TABLE production_plans (\n", + "\trecord_created_time DATETIME NOT NULL, \n", + "\tstart_time DATETIME NOT NULL, \n", + "\tbidding_area_id INTEGER NOT NULL, \n", + "\tproduction_type_id INTEGER NOT NULL, \n", + "\tvalue NUMERIC NOT NULL, \n", + "\tPRIMARY KEY (record_created_time, start_time, bidding_area_id, production_type_id), \n", + "\tFOREIGN KEY(bidding_area_id) REFERENCES bidding_areas (bidding_area_id), \n", + "\tFOREIGN KEY(production_type_id) REFERENCES production_types (production_type_id)\n", + ")\n", + "\n", + "\n", + "2022-09-19 15:33:48,122 INFO sqlalchemy.engine.Engine [no key 0.00095s] ()\n", + "2022-09-19 15:33:48,123 INFO sqlalchemy.engine.Engine COMMIT\n" + ] + } + ], + "source": [ + "from sqlalchemy import Column, MetaData, Table\n", + "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n", + "from sqlalchemy.orm import relationship\n", + "\n", + "metadata = MetaData()\n", + "\n", + "from sqlalchemy.ext.declarative import declarative_base\n", + "\n", + "Base = declarative_base()\n", + "\n", + "\n", + "class ProductionType(Base):\n", + " __tablename__ = \"production_types\"\n", + "\n", + " production_type_id = Column(Integer, primary_key=True)\n", + " code = Column(String(3), nullable=False, unique=True)\n", + " description = Column(String)\n", + "\n", + " production_plans = relationship(\n", + " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n", + " )\n", + "\n", + " def __init__(self, code, description):\n", + " self.code = code\n", + " self.description = description\n", + "\n", + " def __str__(self):\n", + " return self.code\n", + "\n", + "\n", + "class BiddingArea(Base):\n", + " __tablename__ = \"bidding_areas\"\n", + "\n", + " bidding_area_id = Column(Integer, primary_key=True)\n", + " code = Column(String(3), nullable=False, unique=True)\n", + " name = Column(String(32))\n", + "\n", + " production_plans = relationship(\n", + " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n", + " )\n", + "\n", + " def __init__(self, code, name):\n", + " self.code = code\n", + " self.name = name\n", + "\n", + " def __str__(self):\n", + " return self.code\n", + "\n", + "\n", + "class ProductionPlan(Base):\n", + " __tablename__ = \"production_plans\"\n", + "\n", + " record_created_time = Column(DateTime(timezone=False), primary_key=True)\n", + " start_time = Column(DateTime(timezone=False), primary_key=True)\n", + " bidding_area_id = Column(Integer, ForeignKey(\"bidding_areas.bidding_area_id\"), primary_key=True)\n", + " production_type_id = Column(Integer, ForeignKey(\"production_types.production_type_id\"), primary_key=True)\n", + " value = Column(Numeric, nullable=False)\n", + "\n", + " # defining relationships.\n", + " # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects\n", + " production_type = relationship(ProductionType, back_populates=\"production_plans\")\n", + " bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n", + "\n", + " def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n", + " self.record_created_time = record_created_time\n", + " self.start_time = start_time\n", + " self.production_type = production_type # a 'ProductionType' object\n", + " self.bidding_area = bidding_area # a 'BiddingArea' object\n", + " self.value = value\n", + "\n", + " def __str__(self):\n", + " return (\n", + " f\"{self.record_created_time} {self.start_time} \"\n", + " f\"{self.production_type} {self.bidding_area} {self.value}\"\n", + " )\n", + "\n", + "Base.metadata.create_all(engine) # create tables\n" + ] + }, + { + "cell_type": "code", + "execution_count": 3, + "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", + "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "2022-09-19 15:33:48,160 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-19 15:33:48,163 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-19 15:33:48,164 INFO sqlalchemy.engine.Engine [generated in 0.00128s] ('NO1', 'Elspot NO1')\n", + "2022-09-19 15:33:48,166 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-19 15:33:48,167 INFO sqlalchemy.engine.Engine [cached since 0.003926s ago] ('NO2', 'Elspot NO2')\n", + "2022-09-19 15:33:48,168 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-19 15:33:48,169 INFO sqlalchemy.engine.Engine [cached since 0.005792s ago] ('NO3', 'Elspot NO3')\n", + "2022-09-19 15:33:48,170 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-19 15:33:48,171 INFO sqlalchemy.engine.Engine [cached since 0.007965s ago] ('NO4', 'Elspot NO4')\n", + "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?)\n", + "2022-09-19 15:33:48,172 INFO sqlalchemy.engine.Engine [cached since 0.009323s ago] ('NO5', 'Elspot NO5')\n", + "2022-09-19 15:33:48,174 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,175 INFO sqlalchemy.engine.Engine [generated in 0.00066s] ('B19', 'Wind Onshore')\n", + "2022-09-19 15:33:48,176 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,177 INFO sqlalchemy.engine.Engine [cached since 0.002644s ago] ('B10', 'Hydro-electric pure pumped storage head installation')\n", + "2022-09-19 15:33:48,178 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,178 INFO sqlalchemy.engine.Engine [cached since 0.004299s ago] ('B11', 'Hydro Run-of-river head installation')\n", + "2022-09-19 15:33:48,179 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,180 INFO sqlalchemy.engine.Engine [cached since 0.005933s ago] ('B12', 'Hydro-electric storage head installation')\n", + "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,181 INFO sqlalchemy.engine.Engine [cached since 0.007409s ago] ('A04', 'Generation')\n", + "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,183 INFO sqlalchemy.engine.Engine [cached since 0.009187s ago] ('B37', 'Thermal unspecified')\n", + "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?)\n", + "2022-09-19 15:33:48,184 INFO sqlalchemy.engine.Engine [cached since 0.01042s ago] ('B30', 'Wind unspecified')\n", + "2022-09-19 15:33:48,187 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", + "2022-09-19 15:33:48,187 INFO sqlalchemy.engine.Engine [generated in 0.00072s] (('2022-09-19 15:33:48.159940', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6, 90.5))\n", + "2022-09-19 15:33:48,189 INFO sqlalchemy.engine.Engine COMMIT\n", + "2022-09-19 15:33:48,191 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2022-09-19 15:33:48,194 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-19 15:33:48,195 INFO sqlalchemy.engine.Engine [generated in 0.00095s] ('2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2022-09-19 15:33:48,197 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-19 15:33:48,198 INFO sqlalchemy.engine.Engine [generated in 0.00097s] (70.5, '2022-09-19 15:33:48.160093', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2022-09-19 15:33:48,199 INFO sqlalchemy.engine.Engine COMMIT\n" + ] + } + ], + "source": [ + "# adding some data...\n", + "import datetime\n", + "import decimal\n", + "\n", + "from sqlalchemy.orm import sessionmaker\n", + "\n", + "Session = sessionmaker(bind=engine) # bound session\n", + "session = Session()\n", + "\n", + "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n", + "session.add(bidding_area1)\n", + "\n", + "session.add_all(\n", + " [\n", + " BiddingArea(\"NO2\", \"Elspot NO2\"),\n", + " BiddingArea(\"NO3\", \"Elspot NO3\"),\n", + " BiddingArea(\"NO4\", \"Elspot NO4\"),\n", + " BiddingArea(\"NO5\", \"Elspot NO5\"),\n", + " ]\n", + ")\n", + "\n", + "production_type_B37 = ProductionType(\"B37\", \"Thermal unspecified\")\n", + "production_type_B30 = ProductionType(\"B30\", \"Wind unspecified\")\n", + "\n", + "session.add_all(\n", + " [\n", + " ProductionType(\"B19\", \"Wind Onshore\"),\n", + " ProductionType(\"B10\", \"Hydro-electric pure pumped storage head installation\"),\n", + " ProductionType(\"B11\", \"Hydro Run-of-river head installation\"),\n", + " ProductionType(\"B12\", \"Hydro-electric storage head installation\"),\n", + " ProductionType(\"A04\", \"Generation\"),\n", + " production_type_B37,\n", + " production_type_B30,\n", + " ]\n", + ")\n", + "\n", + "session.add(\n", + " ProductionPlan(\n", + " datetime.datetime.now(),\n", + " datetime.datetime(2022, 11, 2, 1, 0),\n", + " production_type_B37,\n", + " bidding_area1,\n", + " decimal.Decimal(\"80.5\"),\n", + " )\n", + ")\n", + "\n", + "production_plan2 = ProductionPlan(\n", + " datetime.datetime.now(),\n", + " datetime.datetime(2022, 11, 2, 2, 0),\n", + " production_type_B37,\n", + " bidding_area1,\n", + " decimal.Decimal(\"90.5\"),\n", + ")\n", + "\n", + "session.add(production_plan2)\n", + "\n", + "session.flush() # execute pending operations\n", + "session.commit() # execute and commit pending operations (implicit flush)\n", + "\n", + "production_plan2.value = decimal.Decimal(\"70.5\")\n", + "production_plan2 in session\n", + "# Out: True\n", + "\n", + "session.commit()\n" + ] + } + ], + "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 +} -- cgit v1.3