{ "cells": [ { "cell_type": "markdown", "id": "8d981972-51c1-4f44-98fb-47abefacffb7", "metadata": { "slideshow": { "slide_type": "slide" }, "tags": [] }, "source": [ "# SQLAlchemy\n", "\n", "Simeon Simeonov @ Statnett\n", "\n", "\n", "## Agenda\n", "\n", "- Design & overview\n", "\n", "- A small practical example\n" ] }, { "cell_type": "markdown", "id": "19d6fced-434e-4604-bcb7-b71fadfe6dcb", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# What is SQLAlchemy?\n", "\n", "SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.\n", "\n", "SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).\n" ] }, { "cell_type": "markdown", "id": "fa5ad88f-d2ec-4d86-9982-73f7b9fe0e9b", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Why use SQLAlchemy?\n", "\n", "- free software - free as in \"freedom\" (MIT licensed)\n", "\n", "- portability - the programming interface is independent of the type of RDBMS and connector used\n", "\n", "- security - no more SQL injections\n", "\n", "- abstraction - no need to bother with complex JOINs\n", "\n", "- object-orientation - you work with objects instead of tables and rows\n", "\n", "- performance - exploits the likehood of reusing a particular query\n", "\n", "- flexibility - you can override almost anything\n" ] }, { "cell_type": "markdown", "id": "d68798f0-de17-44f9-b677-d7ee06409a9c", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Basic architecture\n", "\n", "- Engine- manages the connection pool and the RDBMS-independent SQL dialect layer\n", "\n", "- MetaData - used to collect and organize information about your table layout (schema)\n", "\n", "- SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)\n", "\n", "- ORM - provides a convenient way to add database persistence to your Python objects without requiring you to design your objects around the database, or the database around the objects (high-level interface)\n", "\n", "- Session - establishes all conversations with the RDBMS and represents a \"holding zone\" for all the objects which you've loaded or associated with it during its lifespan\n", "\n", "![title](images/sqla_arch.png)" ] }, { "cell_type": "code", "execution_count": 11, "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", "metadata": { "slideshow": { "slide_type": "slide" } }, "outputs": [], "source": [ "from sqlalchemy import create_engine\n", "\n", "# engine = create_engine(\"postgresql+psycopg2://user:zipassword@localhost/mydb\" , echo=True)\n", "# The string form of the URL is dialect+driver://user:password@host/d[?key=valuebname..],\n", "# where dialect is a database name such as mysql, oracle, postgresql, etc.,\n", "# and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle\n", "# The echo flag is a shortcut to setting up SQLAlchemy logging,\n", "# which is accomplished via Python’s standard logging module.\n", "\n", "# engine = create_engine(\"sqlite:///library.db\", echo=True)\n", "engine = create_engine(\"sqlite:///:memory:\", echo=True)" ] }, { "cell_type": "code", "execution_count": 12, "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "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", "\tdescription VARCHAR, \n", "\tPRIMARY KEY (production_type_id), \n", "\tUNIQUE (code)\n", ")\n", "\n", "\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", "\tname VARCHAR(32), \n", "\tPRIMARY KEY (bidding_area_id), \n", "\tUNIQUE (code)\n", ")\n", "\n", "\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", "\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-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" ] } ], "source": [ "from sqlalchemy import Column, Table\n", "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n", "from sqlalchemy.orm import declarative_base, relationship\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 __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return self.code\n", "\n", "\n", "class BiddingArea(Base):\n", " __tablename__ = \"bidding_areas\"\n", "\n", " bidding_area_id = 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 __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return self.code\n", "\n", "\n", "class ProductionPlan(Base):\n", " __tablename__ = \"production_plans\"\n", "\n", " record_created_time = 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 __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return (\n", " f\"{self.record_created_time} {self.start_time} \"\n", " f\"{self.production_type} {self.bidding_area} {self.value}\"\n", " )\n", "\n", "Base.metadata.create_all(engine) # create tables\n" ] }, { "cell_type": "markdown", "id": "ab7e9561-2780-4d85-843c-b217d15ee4c8", "metadata": {}, "source": [ "# Reflection\n", "\n", "*Table* object can be instructed to load information about itself from the corresponding database schema object already existing within the database.\n", "This process is called *reflection*.\n", "\n", "If the DB schema is already defined and maintained \"somewhere else\", it may be useful to \"reflect\" the schema instead of explicitly defining it.\n", "\n", "**N.B. This is not the recommended way to use SQLAlchemy**" ] }, { "cell_type": "code", "execution_count": 13, "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "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" ] } ], "source": [ "## N.B. This example does NOT represent SQLAlchemy - best practice\n", "from sqlalchemy.ext.declarative import declarative_base\n", "\n", "Base = declarative_base()\n", "Base.metadata.bind = engine\n", "\n", "\n", "class ProductionType(Base):\n", " __tablename__ = \"production_types\"\n", " __table_args__ = {\"autoload\": True}\n", "\n", " # override the existing 'description'...\n", " description = Column(String(64))\n", "\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n", " )\n", "\n", " def __init__(self, code, description):\n", " self.code = code\n", " self.description = description\n", "\n", " def __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return self.code\n", "\n", "\n", "class BiddingArea(Base):\n", " __tablename__ = \"bidding_areas\"\n", " __table_args__ = {\"autoload\": True}\n", "\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n", " )\n", "\n", " def __init__(self, code, name):\n", " self.code = code\n", " self.name = name\n", "\n", " def __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return self.code\n", "\n", "\n", "class ProductionPlan(Base):\n", " __tablename__ = \"production_plans\"\n", " __table_args__ = {\"autoload\": True}\n", "\n", " production_type = relationship(ProductionType, back_populates=\"production_plans\")\n", " bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n", "\n", " def __init__(self, record_created_time, start_time, production_type, bidding_area, value):\n", " self.record_created_time = record_created_time\n", " self.start_time = start_time\n", " self.production_type = production_type # a 'ProductionType' object\n", " self.bidding_area = bidding_area # a 'BiddingArea' object\n", " self.value = value\n", "\n", " def __repr__(self):\n", " return (\n", " f\"\"\n", " )\n", "\n", " def __str__(self):\n", " return (\n", " f\"{self.record_created_time} {self.start_time} \"\n", " f\"{self.production_type} {self.bidding_area} {self.value}\"\n", " )\n" ] }, { "cell_type": "code", "execution_count": 14, "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "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-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" ] } ], "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" ] }, { "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", "metadata": {}, "source": [ "# TODO\n", "\n", "- delete\n" ] } ], "metadata": { "celltoolbar": "Slideshow", "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.8" } }, "nbformat": 4, "nbformat_minor": 5 }