From 24d5938dfb98cc5c27eaec51b1da06b2f4a66932 Mon Sep 17 00:00:00 2001 From: Simeon Simeonov Date: Sun, 17 Sep 2023 01:22:00 +0200 Subject: Update notebooks/python/python_oo.ipynb and notebooks/sqlalchemy/sqlalchemy.ipynb --- notebooks/sqlalchemy/sqlalchemy.ipynb | 702 ++++++++++++++++++++++------------ 1 file changed, 464 insertions(+), 238 deletions(-) (limited to 'notebooks/sqlalchemy') diff --git a/notebooks/sqlalchemy/sqlalchemy.ipynb b/notebooks/sqlalchemy/sqlalchemy.ipynb index 92b2d56..58a7eb9 100644 --- a/notebooks/sqlalchemy/sqlalchemy.ipynb +++ b/notebooks/sqlalchemy/sqlalchemy.ipynb @@ -19,7 +19,9 @@ "\n", "- Design & overview\n", "\n", - "- A small practical example\n" + "- A small practical example\n", + "\n", + "- General principles and best practices" ] }, { @@ -90,7 +92,7 @@ }, { "cell_type": "code", - "execution_count": 11, + "execution_count": 50, "id": "d1ea9f51-e3de-45a8-8302-b4a89b5d1689", "metadata": { "slideshow": { @@ -114,7 +116,7 @@ }, { "cell_type": "code", - "execution_count": 12, + "execution_count": 51, "id": "3b4a777d-7d38-4149-8ab2-c876e2533766", "metadata": { "slideshow": { @@ -126,20 +128,20 @@ "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", + "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_types\")\n", + "2023-09-08 09:45:43,380 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"bidding_areas\")\n", + "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,381 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE production_types (\n", "\tproduction_type_id INTEGER NOT NULL, \n", "\tcode VARCHAR(3) NOT NULL, \n", @@ -149,8 +151,8 @@ ")\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", + "2023-09-08 09:45:43,382 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n", + "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE bidding_areas (\n", "\tbidding_area_id INTEGER NOT NULL, \n", "\tcode VARCHAR(3) NOT NULL, \n", @@ -160,8 +162,8 @@ ")\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", + "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n", + "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine \n", "CREATE TABLE production_plans (\n", "\trecord_created_time DATETIME NOT NULL, \n", "\tstart_time DATETIME NOT NULL, \n", @@ -174,25 +176,25 @@ ")\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" + "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()\n", + "2023-09-08 09:45:43,383 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], "source": [ - "from sqlalchemy import Column, Table\n", "from sqlalchemy import DateTime, ForeignKey, Integer, Numeric, String\n", - "from sqlalchemy.orm import declarative_base, relationship\n", + "from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship\n", "\n", - "Base = declarative_base()\n", + "class Base(DeclarativeBase):\n", + " pass\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", + " production_type_id = mapped_column(Integer, primary_key=True)\n", + " code = mapped_column(String(3), nullable=False, unique=True)\n", + " description = mapped_column(String)\n", "\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n", @@ -215,9 +217,9 @@ "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", + " bidding_area_id = mapped_column(Integer, primary_key=True)\n", + " code = mapped_column(String(3), nullable=False, unique=True)\n", + " name = mapped_column(String(32))\n", "\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n", @@ -240,11 +242,11 @@ "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", + " record_created_time = mapped_column(DateTime(timezone=False), primary_key=True)\n", + " start_time = mapped_column(DateTime(timezone=False), primary_key=True)\n", + " bidding_area_id = mapped_column(Integer, ForeignKey(\"bidding_areas.bidding_area_id\"), primary_key=True)\n", + " production_type_id = mapped_column(Integer, ForeignKey(\"production_types.production_type_id\"), primary_key=True)\n", + " value = mapped_column(Numeric, nullable=False)\n", "\n", " # defining relationships.\n", " # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects\n", @@ -293,7 +295,7 @@ }, { "cell_type": "code", - "execution_count": 13, + "execution_count": 52, "id": "70b0c5fa-35ae-44b5-b57b-2fa21b4f7da5", "metadata": {}, "outputs": [ @@ -301,76 +303,90 @@ "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" + "2023-09-08 09:45:43,387 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,388 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,389 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,390 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,391 INFO sqlalchemy.engine.Engine ROLLBACK\n", + "2023-09-08 09:45:43,391 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,392 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,393 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,394 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine ROLLBACK\n", + "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,395 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,396 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,397 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", + "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,398 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,399 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "## N.B. This example does NOT represent SQLAlchemy - best practice\n", - "from sqlalchemy.ext.declarative import declarative_base\n", + "from sqlalchemy import Table, Column\n", + "from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped\n", + "\n", "\n", - "Base = declarative_base()\n", - "Base.metadata.bind = engine\n", + "class Base(DeclarativeBase):\n", + " pass\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", + " __table_args__ = {\"autoload_with\": engine}\n", + " # __table__ = Table(\"production_types\", Base.metadata, Column(\"description\", String(32)), autoload_with=engine)\n", "\n", + " # redefining the 'description' column\n", + " description = mapped_column(String(32))\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"production_type\", lazy=\"dynamic\"\n", " )\n", @@ -391,7 +407,8 @@ "\n", "class BiddingArea(Base):\n", " __tablename__ = \"bidding_areas\"\n", - " __table_args__ = {\"autoload\": True}\n", + " __table_args__ = {\"autoload_with\": engine}\n", + " # __table__ = Table(\"bidding_areas\", Base.metadata, autoload_with=engine)\n", "\n", " production_plans = relationship(\n", " \"ProductionPlan\", back_populates=\"bidding_area\", lazy=\"dynamic\"\n", @@ -412,8 +429,10 @@ "\n", "\n", "class ProductionPlan(Base):\n", - " __tablename__ = \"production_plans\"\n", - " __table_args__ = {\"autoload\": True}\n", + " # __tablename__ = \"production_plans\"\n", + " # __table_args__ = {\"autoload_with\": engine}\n", + " # declarative mapping with imperative Table definition (A.K.A. Hybrid declarative)\n", + " __table__ = Table(\"production_plans\", Base.metadata, autoload_with=engine)\n", "\n", " production_type = relationship(ProductionType, back_populates=\"production_plans\")\n", " bidding_area = relationship(BiddingArea, back_populates=\"production_plans\")\n", @@ -443,7 +462,7 @@ }, { "cell_type": "code", - "execution_count": 14, + "execution_count": 53, "id": "1f18d9c1-bd39-4195-b868-114c3296eac2", "metadata": { "slideshow": { @@ -455,42 +474,42 @@ "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", + "2023-09-08 09:45:43,405 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [generated in 0.00005s (insertmanyvalues) 1/5 (ordered; batch not supported)] ('NO1', 'Elspot NO1')\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/5 (ordered; batch not supported)] ('NO2', 'Elspot NO2')\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n", + "2023-09-08 09:45:43,406 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/5 (ordered; batch not supported)] ('NO3', 'Elspot NO3')\n", + "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n", + "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine [insertmanyvalues 4/5 (ordered; batch not supported)] ('NO4', 'Elspot NO4')\n", + "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO bidding_areas (code, name) VALUES (?, ?) RETURNING bidding_area_id\n", + "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine [insertmanyvalues 5/5 (ordered; batch not supported)] ('NO5', 'Elspot NO5')\n", + "2023-09-08 09:45:43,407 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [generated in 0.00004s (insertmanyvalues) 1/7 (ordered; batch not supported)] ('B19', 'Wind Onshore')\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/7 (ordered; batch not supported)] ('B10', 'Hydro-electric pure pumped storage head installation')\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 3/7 (ordered; batch not supported)] ('B11', 'Hydro Run-of-river head installation')\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,408 INFO sqlalchemy.engine.Engine [insertmanyvalues 4/7 (ordered; batch not supported)] ('B12', 'Hydro-electric storage head installation')\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 5/7 (ordered; batch not supported)] ('A04', 'Generation')\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 6/7 (ordered; batch not supported)] ('B37', 'Thermal unspecified')\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine INSERT INTO production_types (code, description) VALUES (?, ?) RETURNING production_type_id\n", + "2023-09-08 09:45:43,409 INFO sqlalchemy.engine.Engine [insertmanyvalues 7/7 (ordered; batch not supported)] ('B30', 'Wind unspecified')\n", + "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine INSERT INTO production_plans (record_created_time, start_time, bidding_area_id, production_type_id, value) VALUES (?, ?, ?, ?, ?)\n", + "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine [generated in 0.00024s] [('2023-09-08 09:45:43.404962', '2022-11-02 01:00:00.000000', 1, 6, 80.5), ('2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6, 90.5)]\n", + "2023-09-08 09:45:43,410 INFO sqlalchemy.engine.Engine COMMIT\n", + "2023-09-08 09:45:43,411 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,411 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id \n", "FROM production_plans \n", "WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n", - "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" + "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ('2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine UPDATE production_plans SET value=? WHERE production_plans.record_created_time = ? AND production_plans.start_time = ? AND production_plans.bidding_area_id = ? AND production_plans.production_type_id = ?\n", + "2023-09-08 09:45:43,412 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (70.5, '2023-09-08 09:45:43.405033', '2022-11-02 02:00:00.000000', 1, 6)\n", + "2023-09-08 09:45:43,413 INFO sqlalchemy.engine.Engine COMMIT\n" ] } ], @@ -499,10 +518,10 @@ "import datetime\n", "import decimal\n", "\n", - "from sqlalchemy.orm import sessionmaker\n", + "from sqlalchemy.orm import Session\n", "\n", - "Session = sessionmaker(bind=engine) # bound session\n", - "session = Session()\n", + "session = Session(engine)\n", + "# with Session(engine) as session:\n", "\n", "bidding_area1 = BiddingArea(\"NO1\", \"Elspot NO1\")\n", "session.add(bidding_area1),\n", @@ -556,14 +575,14 @@ "\n", "production_plan2.value = decimal.Decimal(\"70.5\")\n", "production_plan2 in session\n", - "# Out: True\n", + " # Out: True\n", "\n", "session.commit()\n" ] }, { "cell_type": "code", - "execution_count": 15, + "execution_count": 54, "id": "875195c5-ae99-4aa8-9aca-89d6e9c27ae2", "metadata": {}, "outputs": [ @@ -571,139 +590,145 @@ "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", + "2023-09-08 09:45:43,415 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,416 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans ORDER BY production_plans.start_time\n", - "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", + "2023-09-08 09:45:43,416 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ()\n", + "2023-09-08 09:45:43,417 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans \n", "WHERE production_plans.start_time > ?\n", - "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", + "2023-09-08 09:45:43,417 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('2022-09-01 00:00:00.000000',)\n", + "2023-09-08 09:45:43,418 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n", "FROM (SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans \n", "WHERE production_plans.value > ? ORDER BY production_plans.start_time) AS anon_1\n", - "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", + "2023-09-08 09:45:43,418 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (80,)\n", + "2023-09-08 09:45:43,419 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans \n", "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", " LIMIT ? OFFSET ?\n", - "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", + "2023-09-08 09:45:43,419 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (80, 1, 0)\n", + "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans \n", "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", - "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", + "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine [generated in 0.00014s] (80,)\n", + "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,420 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_temp_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", + "2023-09-08 09:45:43,421 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,422 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", + "2023-09-08 09:45:43,423 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", + "2023-09-08 09:45:43,424 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,425 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,426 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", + "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,427 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,428 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2023-09-08 09:45:43,429 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,430 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,431 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", "FROM production_plans\n", - "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", + "2023-09-08 09:45:43,431 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ()\n", + "2023-09-08 09:45:43,432 INFO sqlalchemy.engine.Engine COMMIT\n", + "2023-09-08 09:45:43,432 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", "FROM production_plans \n", "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", - "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", + "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine [generated in 0.00026s] (80,)\n", + "2023-09-08 09:45:43,433 INFO sqlalchemy.engine.Engine ROLLBACK\n", + "2023-09-08 09:45:43,434 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans, production_types \n", "WHERE production_plans.production_type_id = production_types.production_type_id AND production_types.code = ?\n", - "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", + "2023-09-08 09:45:43,434 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ('B37',)\n", + "2023-09-08 09:45:43,435 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans JOIN production_types ON production_types.production_type_id = production_plans.production_type_id \n", "WHERE production_types.code = ?\n", - "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", + "2023-09-08 09:45:43,435 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('B37',)\n", + "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine SELECT production_types.production_type_id AS production_types_production_type_id, production_types.code AS production_types_code, production_types.description AS production_types_description \n", "FROM production_types \n", "WHERE production_types.production_type_id = ?\n", - "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", + "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (6,)\n", + "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time AS production_plans_record_created_time, production_plans.start_time AS production_plans_start_time, production_plans.bidding_area_id AS production_plans_bidding_area_id, production_plans.production_type_id AS production_plans_production_type_id, production_plans.value AS production_plans_value \n", "FROM production_plans \n", "WHERE ? = production_plans.production_type_id\n", - "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", + "2023-09-08 09:45:43,436 INFO sqlalchemy.engine.Engine [generated in 0.00093s] (6,)\n", + "2023-09-08 09:45:43,437 INFO sqlalchemy.engine.Engine SELECT pp.* FROM production_plans pp, production_types pt WHERE pp.production_type_id = pt.production_type_id AND pt.code=?\n", + "2023-09-08 09:45:43,437 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('B37',)\n", + "2023-09-08 09:45:43,439 INFO sqlalchemy.engine.Engine SELECT bidding_areas.bidding_area_id AS bidding_areas_bidding_area_id, bidding_areas.code AS bidding_areas_code, bidding_areas.name AS bidding_areas_name \n", "FROM bidding_areas \n", "WHERE bidding_areas.bidding_area_id = ?\n", - "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" + "2023-09-08 09:45:43,439 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (1,)\n" ] }, { "data": { "text/plain": [ - "[, bidding_area=, value=Decimal('80.5000000000'))>,\n", - " , bidding_area=, value=Decimal('70.5000000000'))>]" + "[, bidding_area=, value=Decimal('80.5000000000'))>,\n", + " , bidding_area=, value=Decimal('70.5000000000'))>]" ] }, - "execution_count": 15, + "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ + "# Old SQLAlchemy 1.x API - still working in SQLAlchemy 2\n", "import pandas as pd\n", "\n", "from sqlalchemy import text\n", "\n", - "\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", @@ -738,13 +763,214 @@ ] }, { - "cell_type": "markdown", - "id": "86336aaa-540c-466d-b5c1-f3fc179bd1e3", + "cell_type": "code", + "execution_count": 55, + "id": "c2f70746-485d-4d1d-ae6e-27e3cf70b99c", "metadata": {}, + "outputs": [ + { + "name": "stdout", + "output_type": "stream", + "text": [ + "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans ORDER BY production_plans.start_time\n", + "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine [generated in 0.00041s] ()\n", + "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans ORDER BY production_plans.start_time\n", + "2023-09-08 09:45:43,443 INFO sqlalchemy.engine.Engine [cached since 0.0009604s ago] ()\n", + "2023-09-08 09:45:43,444 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans \n", + "WHERE production_plans.start_time > ?\n", + "2023-09-08 09:45:43,444 INFO sqlalchemy.engine.Engine [generated in 0.00015s] ('2022-09-01 00:00:00.000000',)\n", + "2023-09-08 09:45:43,445 INFO sqlalchemy.engine.Engine SELECT count(*) AS count_1 \n", + "FROM (SELECT production_plans.record_created_time AS record_created_time, production_plans.start_time AS start_time, production_plans.bidding_area_id AS bidding_area_id, production_plans.production_type_id AS production_type_id, production_plans.value AS value \n", + "FROM production_plans \n", + "WHERE production_plans.value > ? ORDER BY production_plans.start_time) AS anon_1\n", + "2023-09-08 09:45:43,445 INFO sqlalchemy.engine.Engine [generated in 0.00020s] (80,)\n", + "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans \n", + "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", + "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine [generated in 0.00024s] (80,)\n", + "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans \n", + "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", + "2023-09-08 09:45:43,446 INFO sqlalchemy.engine.Engine [cached since 0.0007238s ago] (80,)\n", + "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,447 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n", + "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine SELECT name FROM sqlite_temp_master WHERE type='table' AND name NOT LIKE 'sqlite~_%' ESCAPE '~' ORDER BY name\n", + "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_plans\")\n", + "2023-09-08 09:45:43,448 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_plans\")\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,449 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_plans\")\n", + "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,450 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_plans\")\n", + "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_plans_1\")\n", + "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,451 INFO sqlalchemy.engine.Engine [raw sql] ('production_plans',)\n", + "2023-09-08 09:45:43,452 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"bidding_areas\")\n", + "2023-09-08 09:45:43,452 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,453 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,454 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"bidding_areas\")\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"bidding_areas\")\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_bidding_areas_1\")\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,455 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine [raw sql] ('bidding_areas',)\n", + "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine PRAGMA main.table_xinfo(\"production_types\")\n", + "2023-09-08 09:45:43,456 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list(\"production_types\")\n", + "2023-09-08 09:45:43,457 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,458 INFO sqlalchemy.engine.Engine PRAGMA main.table_info(\"production_types\")\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_list(\"production_types\")\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine PRAGMA main.index_info(\"sqlite_autoindex_production_types_1\")\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine [raw sql] ()\n", + "2023-09-08 09:45:43,459 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type in ('table', 'view')\n", + "2023-09-08 09:45:43,460 INFO sqlalchemy.engine.Engine [raw sql] ('production_types',)\n", + "2023-09-08 09:45:43,460 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans\n", + "2023-09-08 09:45:43,461 INFO sqlalchemy.engine.Engine [generated in 0.00023s] ()\n", + "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine COMMIT\n", + "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n", + "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans \n", + "WHERE production_plans.value > ? ORDER BY production_plans.start_time\n", + "2023-09-08 09:45:43,462 INFO sqlalchemy.engine.Engine [cached since 0.01647s ago] (80,)\n", + "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine ROLLBACK\n", + "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans, production_types \n", + "WHERE production_plans.production_type_id = production_types.production_type_id AND production_types.code = ?\n", + "2023-09-08 09:45:43,463 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ('B37',)\n", + "2023-09-08 09:45:43,464 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans JOIN production_types ON production_types.production_type_id = production_plans.production_type_id \n", + "WHERE production_types.code = ?\n", + "2023-09-08 09:45:43,464 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ('B37',)\n", + "2023-09-08 09:45:43,465 INFO sqlalchemy.engine.Engine SELECT production_plans.record_created_time, production_plans.start_time, production_plans.bidding_area_id, production_plans.production_type_id, production_plans.value \n", + "FROM production_plans \n", + "WHERE ? = production_plans.production_type_id\n", + "2023-09-08 09:45:43,465 INFO sqlalchemy.engine.Engine [generated in 0.00026s] (6,)\n" + ] + }, + { + "data": { + "text/plain": [ + "[, bidding_area=, value=Decimal('80.5000000000'))>,\n", + " , bidding_area=, value=Decimal('70.5000000000'))>]" + ] + }, + "execution_count": 55, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "# TODO\n", + "# New SQLAlchemy 2 - API\n", + "\n", + "from sqlalchemy import func, select\n", + "\n", + "# Returns a sqlalchemy.sql.expression.Select instance (statement)\n", + "# No actual SELECT statement(s) have been sent to the DB-server\n", + "stmt = select(ProductionPlan).order_by(ProductionPlan.start_time)\n", + "\n", + "# sends the actual SELECT statement to the DB-server\n", + "result = session.scalars(stmt) # returns a sqlalchemy.engine.ScalarResult\n", + "result.all() # returns a list of objects (if any) and then \"closes\" the result\n", + "result.first() # N.B. will return None or raise an exeption since the Result has been \"closed\"\n", "\n", - "- delete\n" + "session.scalars(select(ProductionPlan).order_by(ProductionPlan.start_time)).all()\n", + "\n", + "# return all production plans where start_time after 2022-09-01 00:00\n", + "session.scalars(\n", + " select(\n", + " ProductionPlan\n", + " ).where(\n", + " ProductionPlan.start_time > datetime.datetime(2022, 9, 1, 0, 0)\n", + " )\n", + ").all()\n", + "\n", + "# return production plans with value > 80\n", + "stmt = select(ProductionPlan).where(ProductionPlan.value > 80).order_by(ProductionPlan.start_time)\n", + "session.execute(select(func.count()).select_from(stmt.subquery())).scalar_one() # 1\n", + "# N.B. Result (or ScalarResult) can be invoked only once\n", + "production_plan = session.scalars(stmt).first() # returns the first object (element)\n", + "production_plan = session.scalars(stmt).one() # raises NoResultFound exception or MultipleResultsFound in case elements != 1\n", + "\n", + "# generate Pandas DataFrame from a query or entire table\n", + "df = pd.read_sql_table(\"production_plans\", con=session.get_bind()) # or con=engine\n", + "df = pd.read_sql_query(stmt, engine)\n", + "\n", + "# return production plans with production type 'B37'\n", + "session.scalars(\n", + " select(ProductionPlan).where(\n", + " ProductionPlan.production_type_id == ProductionType.production_type_id,\n", + " ProductionType.code == \"B37\"\n", + " )\n", + ").all()\n", + "session.scalars(\n", + " select(ProductionPlan).join(ProductionPlan.production_type).where(ProductionType.code == \"B37\")\n", + ").all()\n", + "session.scalars(\n", + " select(ProductionPlan).where(ProductionPlan.production_type == production_type_B37)\n", + ").all()\n", + "##session.query(\n", + "## ProductionPlan\n", + "##).from_statement(\n", + "## text(\n", + "## \"SELECT pp.* FROM production_plans pp, production_types pt \"\n", + "## \"WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code\"\n", + "## )\n", + "##).params(code=\"B37\").all()" + ] + }, + { + "cell_type": "markdown", + "id": "d5b79cc1-bec7-4837-89a0-9fdbcbfd7fc0", + "metadata": {}, + "source": [ + "# Some basic tips when using ORM models\n", + "\n", + " - **Business logic is implemented in the respective models.** Objects are responsible for their own actions.\n", + " - All object-orientation rules (SOLID etc.) should apply\n", + " - When is comes to overall design. in general always consider using composition (**has a** relationships) before considering inheritance (**is a** relationships)\n", + " - Try to make the DBMS do the job. If not possible, try to make SQLAlchemy do it and only if that is not possible try to do it yourself\n", + " - Analyze use cases and come up with a 'lazyness' strategy\n", + " - Do not create relationships you don't need in your mapped classes\n", + " - Session strategy should be based on a real world DB session use-case -> usually a new transaction is started, a series of jobs / queries are performed, the session is commited and finally closed. Context manager(s) may be a good idea.\n", + " - Sharing a session object (if possible) instead of spawning several session objects and using them in parallel may guard you from unwanted locks, connection / cursor leaks and bugs in general.\n", + " - An idea that can not be implemented using SQLAlchemy, may not always be a good idea :)" ] } ], @@ -765,7 +991,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.10.8" + "version": "3.11.5" } }, "nbformat": 4, -- cgit v1.3