SQLAlchemy

from sqlalchemy import  Column, Integer, String, ForeignKey, Date
from sqlalchemy.orm import relationship, DeclarativeBase


class Base(DeclarativeBase):
    pass


"""
部门模型(一对多：一个部门包含多个员工)
"""
class Department(Base):

  __tablename__ = "departments"

  id = Column(Integer, primary_key=True, autoincrement=True)
  name = Column(String(50), nullable=False, unique=True) # 部门名称
  location = Column(String(100)) # 部门位置(如"北京总部")

  employees = relationship(
    "Employee",
    back_populates="department",
    # 可选：级联删除(删除部门时自动删除下属员工,根据业务选择)
    # cascade="all, delete-orphan",
    # 可选：懒加载策略,优化查询性能
    lazy="selectin"
  )

""" 
员工模型(多对一：多个员工属于一个部门)
"""
class Employee(Base):
  # 定义表,声明表名
  __tablename__ = "employees"

  # 定义表中的列名以及数据类型以及约束
  id = Column(Integer, primary_key=True, autoincrement=True)
  name = Column(String(50), nullable=False) # 姓名
  age = Column(Integer) # 年龄
  hire_date = Column(Date) # 入职日期

  #添加外键约束,绑定到departments.id
  department_id = Column(
    Integer,
    ForeignKey("departments.id", ondelete="RESTRICT"), # 禁止删除有员工的部门
    nullable=False # 员工必须归属一个部门(根据业务可改为True,允许无部门)
  )

  # 核心修正：多对一关联,指定uselist=False(返回单个部门对象)
  department = relationship(
    "Department",
    back_populates="employees",
    lazy="joined" # 可选：立即加载部门数据,减少查询次数
  )

from sqlalchemy.orm import sessionmaker
from sqlalchemy import  create_engine

from demo.model import Base

db_engine = create_engine(
    url='postgresql+psycopg://',
    echo=True,
    pool_pre_ping=True
)

session_factory = sessionmaker(
    bind=db_engine
)

from demo.db import db_engine
from demo.model import Base


def create_table():
    print( Base.metadata.tables.keys() )
    Base.metadata.create_all(bind=db_engine)

if __name__ == '__main__':
    create_table()

from datetime import date

from demo.db import session_factory
from demo.model import Employee, Department


def insert_db():
    session = session_factory()
    try:
        dept = Department(
            name='营销部',
            location='北京'
        )
        session.add(dept)
        session.commit() #提交数据库
        session.refresh(dept) #刷新对象，获取自增ID

        emp1 = Employee(
            name='晶晶',
            age=20,
            hire_date=date(year=2014, month=1, day=1),
            department_id=dept.id
        )
        emp2 = Employee(
            name='妍',
            age=20,
            hire_date=date(year=2014, month=1, day=1),
            department_id=dept.id
        )
        session.add_all([emp1, emp2])
        session.commit()
        session.refresh(emp1)
        session.refresh(emp2)

    except Exception as e:
        print(e)
        session.rollback()
    finally:
        session.close()

if __name__ == '__main__':
    insert_db()

from demo.db import session_factory
from demo.model import Employee, Department

if __name__ == '__main__':
    session = session_factory()
    try:
        emp = session.query(Employee).filter(Employee.name == '晶晶').first()
        if emp:
            print(emp.__dict__)
            session.delete(emp)
            session.commit()
    except Exception as e:
        session.rollback()
    finally:
        session.close()

from demo.db import session_factory
from demo.model import Employee, Department

if __name__ == '__main__':
    session = session_factory()
    try:
        emp = session.query(Employee).filter(Employee.name == '妍').first()
        if emp:
            emp.name = '董总'
            session.commit()
    except Exception as e:
        session.rollback()
    finally:
        session.close()