session and transaction

Steven Chen · Steven Chen · commit 43da92416673 · 2023-11-21T01:00:52.000-08:00
diff --git a/15_session/__init__.py b/15_session/__init__.py
diff --git a/15_session/db_init.py b/15_session/db_init.py
@@ -0,0 +1,64 @@
+import datetime
+
+from sqlalchemy import create_engine, String, ForeignKey
+from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
+from typing_extensions import Annotated
+from typing import List
+
+
+class Base(DeclarativeBase):
+    pass
+
+
+class Base2(DeclarativeBase):
+    pass
+
+
+engine = create_engine('mysql://root:test@localhost/testdb', echo=True)
+engine2 = create_engine('mysql://root:test@localhost/myblog_db', echo=True)
+
+
+int_pk = Annotated[int, mapped_column(primary_key=True)]
+required_unique_string = Annotated[str, mapped_column(String(128), unique=True, nullable=False)]
+required_string = Annotated[str, mapped_column(String(128), nullable=False)]
+timestamp_not_null = Annotated[datetime.datetime, mapped_column(nullable=False)]
+
+
+class User(Base2):
+    __tablename__ = "users"
+
+    id: Mapped[int_pk]
+    name: Mapped[required_unique_string]
+
+    def __repr__(self):
+        return f'id: {self.id}, name: {self.name}'
+
+
+class Department(Base):
+    __tablename__ = "department"
+
+    id: Mapped[int_pk]
+    name: Mapped[required_unique_string]
+
+    employees: Mapped[List["Employee"]] = relationship(back_populates="department")
+
+    def __repr__(self):
+        return f'id: {self.id}, name: {self.name}'
+
+
+class Employee(Base):
+    __tablename__ = "employee"
+
+    id: Mapped[int_pk]
+    dep_id: Mapped[int] = mapped_column(ForeignKey("department.id"))
+    name: Mapped[required_unique_string]
+    birthday: Mapped[timestamp_not_null]
+
+    department: Mapped[Department] = relationship(back_populates="employees")
+
+    def __repr__(self):
+        return f'id: {self.id}, dep_id: {self.dep_id}, name: {self.name}, birthday: {self.birthday}'
+
+
+Base.metadata.create_all(engine)
+Base2.metadata.create_all(engine2)
diff --git a/15_session/query.py b/15_session/query.py
@@ -0,0 +1,13 @@
+from sqlalchemy import select, insert, update, delete
+from sqlalchemy.orm import Session
+
+from db_init import engine, engine2, Department, Employee, User
+
+
+with Session(engine) as session1, session1.begin(), Session(engine2) as session2, session2.begin():
+    dep = Department(name="pop2")
+    session1.add(dep)
+
+    user = User(name="hhh2")
+    session2.add(user)
+
