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)
ORM具体操作
最新推荐文章于 2022-06-16 11:50:29 发布