From 63acc390e6417a716ebd789c07530414db7d0981 Mon Sep 17 00:00:00 2001 From: Simeon Simeonov Date: Mon, 26 Feb 2024 14:06:51 +0100 Subject: Add git course (reveal.js) and SQLAlchemy 2 (notebooks) --- reveal.js/sqlalchemy.html | 301 +++++++++++++++++++++++----------------------- 1 file changed, 151 insertions(+), 150 deletions(-) (limited to 'reveal.js/sqlalchemy.html') diff --git a/reveal.js/sqlalchemy.html b/reveal.js/sqlalchemy.html index bbcd1ca..a0d6617 100644 --- a/reveal.js/sqlalchemy.html +++ b/reveal.js/sqlalchemy.html @@ -98,29 +98,29 @@ metadata = MetaData() production_types_table = Table( - "production_types", - metadata, - Column("production_type_id", Integer, primary_key=True), - Column("code", String(3), nullable=False, unique=True), - Column("description", String), # Column("name", String(128)) is possible + "production_types", + metadata, + Column("production_type_id", Integer, primary_key=True), + Column("code", String(3), nullable=False, unique=True), + Column("description", String), # Column("name", String(128)) is possible ) bidding_areas_table = Table( - "bidding_areas", - metadata, - Column("bidding_area_id", Integer, primary_key=True), - Column("code", String(3), nullable=False, unique=True), - Column("name", String(32)), + "bidding_areas", + metadata, + Column("bidding_area_id", Integer, primary_key=True), + Column("code", String(3), nullable=False, unique=True), + Column("name", String(32)), ) production_plans_table = Table( - "production_plans", - metadata, - Column("record_created_time", DateTime(timezone=False), primary_key=True), - Column("start_time", DateTime(timezone=False), primary_key=True), - Column("bidding_area_id", Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True), - Column("production_type_id", Integer, ForeignKey("production_types.production_type_id"), primary_key=True), - Column("value", Numeric, nullable=False), + "production_plans", + metadata, + Column("record_created_time", DateTime(timezone=False), primary_key=True), + Column("start_time", DateTime(timezone=False), primary_key=True), + Column("bidding_area_id", Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True), + Column("production_type_id", Integer, ForeignKey("production_types.production_type_id"), primary_key=True), + Column("value", Numeric, nullable=False), ) metadata.create_all(engine) # creates the tables @@ -150,13 +150,13 @@ insert_stmt.execute(bidding_area_id=1, code="NO1", name="Elspot NO1") # insert a single entry # ... or a list of entries insert_stmt.execute( - [ - {"bidding_area_id": 2, "code": "NO2", "name": "Elspot NO2"}, - {"bidding_area_id": 3, "code": "NO3", "name": "Elspot NO3"}, - {"bidding_area_id": 4, "code": "NO4", "name": "Elspot NO4"}, - {"bidding_area_id": 5, "code": "NO5", "name": "Elspot NO5"}, - {"bidding_area_id": 6, "code": "NO6", "name": "Elspot NO6"}, - ] + [ + {"bidding_area_id": 2, "code": "NO2", "name": "Elspot NO2"}, + {"bidding_area_id": 3, "code": "NO3", "name": "Elspot NO3"}, + {"bidding_area_id": 4, "code": "NO4", "name": "Elspot NO4"}, + {"bidding_area_id": 5, "code": "NO5", "name": "Elspot NO5"}, + {"bidding_area_id": 6, "code": "NO6", "name": "Elspot NO6"}, + ] ) metadata.bind = engine # no need to explicitly bind the engine from now on @@ -182,21 +182,21 @@ from sqlalchemy.orm import mapper class ProductionType: - def __init__(self, code, description): - self.code = code - self.description = description + def __init__(self, code, description): + self.code = code + self.description = description - def __str__(self): - return self.code + def __str__(self): + return self.code class BiddingArea: - def __init__(self, code, name): - self.code = code - self.name = name + def __init__(self, code, name): + self.code = code + self.name = name - def __str__(self): - return self.code + def __str__(self): + return self.code mapper(ProductionType, production_types_table) mapper(BiddingArea, bidding_areas_table) @@ -212,27 +212,27 @@ from sqlalchemy.orm import relationship class ProductionPlan: - def __init__(self, record_created_time, start_time, production_type, bidding_area, value): - self.record_created_time = record_created_time - self.start_time = start_time - self.production_type = production_type - self.bidding_area = bidding_area - self.value = value - - def __str__(self): - return ( - f"{self.record_created_time} {self.start_time} " - f"{self.production_type} {self.bidding_area} {self.value}" - ) + def __init__(self, record_created_time, start_time, production_type, bidding_area, value): + self.record_created_time = record_created_time + self.start_time = start_time + self.production_type = production_type + self.bidding_area = bidding_area + self.value = value + + def __str__(self): + return ( + f"{self.record_created_time} {self.start_time} " + f"{self.production_type} {self.bidding_area} {self.value}" + ) mapper( - ProductionPlan, - production_plans_table, - properties = { - "production_type": relationship(ProductionType, backref="production_plans"), - "bidding_area": relationship(BiddingArea, backref="production_plans"), - }, + ProductionPlan, + production_plans_table, + properties = { + "production_type": relationship(ProductionType, backref="production_plans"), + "bidding_area": relationship(BiddingArea, backref="production_plans"), + }, ) @@ -249,32 +249,32 @@ Base = declarative_base() class ProductionType(Base): - __tablename__ = "production_types" + __tablename__ = "production_types" - production_type_id = Column(Integer, primary_key=True) - code = Column(String(3), nullable=False, unique=True) - description = Column(String) + production_type_id = Column(Integer, primary_key=True) + code = Column(String(3), nullable=False, unique=True) + description = Column(String) - def __init__(self, code, description): - self.code = code - self.description = description + def __init__(self, code, description): + self.code = code + self.description = description - def __str__(self): - return self.code + def __str__(self): + return self.code class BiddingArea(Base): - __tablename__ = "bidding_areas" + __tablename__ = "bidding_areas" - bidding_area_id = Column(Integer, primary_key=True) - code = Column(String(3), nullable=False, unique=True) - name = Column(String(32)) + bidding_area_id = Column(Integer, primary_key=True) + code = Column(String(3), nullable=False, unique=True) + name = Column(String(32)) - def __init__(self, code, name): - self.code = code - self.name = name + def __init__(self, code, name): + self.code = code + self.name = name - def __str__(self): - return self.code + def __str__(self): + return self.code @@ -288,31 +288,31 @@ from sqlalchemy.orm import relationship, backref class ProductionPlan(Base): - __tablename__ = "production_plans" - - record_created_time = Column(DateTime(timezone=False), primary_key=True) - start_time = Column(DateTime(timezone=False), primary_key=True) - bidding_area_id = Column(Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True) - production_type_id = Column(Integer, ForeignKey("production_types.production_type_id"), primary_key=True) - value = Column(Numeric, nullable=False) - - # defining relationships. - # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects - production_type = relationship(ProductionType, backref=backref("production_plans")) - bidding_area = relationship(BiddingArea, backref=backref("production_plans")) - - def __init__(self, record_created_time, start_time, production_type, bidding_area, value): - self.record_created_time = record_created_time - self.start_time = start_time - self.production_type = production_type # a 'ProductionType' object - self.bidding_area = bidding_area # a 'BiddingArea' object - self.value = value - - def __str__(self): - return ( - f"{self.record_created_time} {self.start_time} " - f"{self.production_type} {self.bidding_area} {self.value}" - ) + __tablename__ = "production_plans" + + record_created_time = Column(DateTime(timezone=False), primary_key=True) + start_time = Column(DateTime(timezone=False), primary_key=True) + bidding_area_id = Column(Integer, ForeignKey("bidding_areas.bidding_area_id"), primary_key=True) + production_type_id = Column(Integer, ForeignKey("production_types.production_type_id"), primary_key=True) + value = Column(Numeric, nullable=False) + + # defining relationships. + # the defined attributes will reference 'ProductionType' and 'BiddingArea' objects + production_type = relationship(ProductionType, backref=backref("production_plans")) + bidding_area = relationship(BiddingArea, backref=backref("production_plans")) + + def __init__(self, record_created_time, start_time, production_type, bidding_area, value): + self.record_created_time = record_created_time + self.start_time = start_time + self.production_type = production_type # a 'ProductionType' object + self.bidding_area = bidding_area # a 'BiddingArea' object + self.value = value + + def __str__(self): + return ( + f"{self.record_created_time} {self.start_time} " + f"{self.production_type} {self.bidding_area} {self.value}" + ) Base.metadata.create_all(engine) # create tables @@ -337,27 +337,27 @@ session.add(bidding_area1) session.add_all( - [ - BiddingArea("NO2", "Elspot NO2"), - BiddingArea("NO3", "Elspot NO3"), - BiddingArea("NO4", "Elspot NO4"), - BiddingArea("NO5", "Elspot NO5"), - ] + [ + BiddingArea("NO2", "Elspot NO2"), + BiddingArea("NO3", "Elspot NO3"), + BiddingArea("NO4", "Elspot NO4"), + BiddingArea("NO5", "Elspot NO5"), + ] ) production_type_B37 = ProductionType("B37", "Thermal unspecified") production_type_B30 = ProductionType("B30", "Wind unspecified") session.add_all( - [ - ProductionType("B19", "Wind Onshore"), - ProductionType("B10", "Hydro-electric pure pumped storage head installation"), - ProductionType("B11", "Hydro Run-of-river head installation"), - ProductionType("B12", "Hydro-electric storage head installation"), - ProductionType("A04", "Generation"), - production_type_B37, - production_type_B30, - ] + [ + ProductionType("B19", "Wind Onshore"), + ProductionType("B10", "Hydro-electric pure pumped storage head installation"), + ProductionType("B11", "Hydro Run-of-river head installation"), + ProductionType("B12", "Hydro-electric storage head installation"), + ProductionType("A04", "Generation"), + production_type_B37, + production_type_B30, + ] ) @@ -371,21 +371,21 @@ # adding some production plans... session.add( - ProductionPlan( - datetime.datetime.now(), - datetime.datetime(2022, 11, 2, 1, 0), - production_type_B37, - bidding_area1, - decimal.Decimal("80.5"), - ) + ProductionPlan( + datetime.datetime.now(), + datetime.datetime(2022, 11, 2, 1, 0), + production_type_B37, + bidding_area1, + decimal.Decimal("80.5"), + ) ) production_plan2 = ProductionPlan( - datetime.datetime.now(), - datetime.datetime(2022, 11, 2, 2, 0), - production_type_B37, - bidding_area1, - decimal.Decimal("90.5"), + datetime.datetime.now(), + datetime.datetime(2022, 11, 2, 2, 0), + production_type_B37, + bidding_area1, + decimal.Decimal("90.5"), ) session.add(production_plan2) @@ -427,17 +427,17 @@ # return production plans with production type 'B37' session.query(ProductionPlan).filter( - ProductionPlan.production_type_id == ProductionType.production_type_id + ProductionPlan.production_type_id == ProductionType.production_type_id ).filter(ProductionType.code == "B37").all() session.query(ProductionPlan).join(ProductionType).filter(ProductionType.code == "B37").all() session.query(ProductionPlan).filter(ProductionPlan.production_type == production_type_B37).all() session.query( - ProductionPlan + ProductionPlan ).from_statement( - text( - "SELECT pp.* FROM production_plans pp, production_types pt " - "WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code" - ) + text( + "SELECT pp.* FROM production_plans pp, production_types pt " + "WHERE pp.production_type_id = pt.production_type_id AND pt.code=:code" + ) ).params(code="B37").all() @@ -449,28 +449,29 @@ - - - - - - - - + + + + + + + + -- cgit v1.3