ORM具体操作

import sqlalchemy, enum
from sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey, Enum, and_
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base


engin = create_engine("mysql+pymysql://xinzhu:123456@192.168.106.128/test")
Base = declarative_base()
Session = sessionmaker(bind=engin)
session = Session()

class Department(Base):
    __tablename__ = 'departments'
    dept_no = Column(String(4), primary_key=True, nullable=False)
    dept_name = Column(String(40), nullable=False, unique=True)

    def __repr__(self):
        return 'table: {} dept_no: {} dept_name: {}'.format(
            __class__.__name__, self.dept_no, self.dept_name)


class MyEnum(enum.Enum):
    M = 'M'
    F = 'F'


class Employees(Base):
    __tablename__ = 'employees'
    emp_no = Column(Integer, primary_key=True, nullable=False)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender = Column(Enum(MyEnum), nullable=False)
    hire_date = Column(Date, nullable=False)
    titles = relationship('Titles')
    departments = relationship('Dept_emp')

    def __repr__(self):
        return 'table: {} emp_no: {} first_name: {}' \
               ' last_name : {} gender: {}'.format(
           self.__class__.__name__, self.emp_no, self.first_name,
            self.last_name, self.gender
        )


class Dept_emp(Base):
    __tablename__ = 'dept_emp'
    emp_no = Column(Integer, ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True)
    dept_no = Column(String(4), ForeignKey('departments.dept_no', ondelete='CASCADE'), primary_key=True)
    from_date = Column(Date, nullable=False)
    to_date = Column(Date, nullable=False)
    department = relationship('Department')

    def __repr__(self):
        return 'table:{} emp_no:{} dept_no:{} from_date:{} to_date:{}'.format(
            __class__.__name__, self.emp_no, self.dept_no, self.from_date,
            self.to_date
        )

class Titles(Base):
    __tablename__ = 'titles'
    emp_no = Column(Integer, ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True)
    title = Column(String(50), primary_key=True)
    from_date = Column(Date, primary_key=True)
    to_date = Column(Date, default=None)

    def __repr__(self):
        return 'table:{} emp_no:{} title:{} from_date:{} to_date:{}'.format(
            __class__.__name__, self.emp_no, self.title,
            self.from_date, self.to_date
        )

# 1题
# 一:
depms = session.query(Employees.emp_no, Employees.first_name, Employees.last_name, Titles.title, Titles).join(Employees, Employees.emp_no==Titles.emp_no).filter(Employees.emp_no==10009)
print(depms.all())
print(depms.count())

# 二: get方法
emp = session.query(Employees).get(10009)
print(emp.emp_no, emp.first_name, [t.title for t in emp.titles])



# 2题
# SELECT * FROM departments, employees WHERE dept_no IN (SELECT dept_no FROM dept_emp WHERE emp_no =10010) AND employees.`emp_no`=10010;
result = session.query(Employees).join(Dept_emp, Employees.emp_no == Dept_emp.emp_no).join(Departments, Departments.dept_no==Dept_emp.dept_no ).filter(Employees.emp_no==10010)

emp = session.query(Employees).get(10010)
print(emp)
for d in emp.departments:
    q = session.query(Department).get(d.dept_no)
    print(q.dept_name)

print([d.department.dept_name for d in emp.departments])


result = session.query(Employees.emp_no, Employees.first_name, Department.dept_no, Department.dept_name, Employees).join(Dept_emp, Employees.emp_no == Dept_emp.emp_no).join(Department, Department.dept_no==Dept_emp.dept_no ).filter(Employees.emp_no==10010).all()

for x in result:
    print(type(x))
    print(x.emp_no)
    print(x)