对象关系映射(英语:(Object Relational Mapping,简称ORM))
from sqlalchemy import Column, Integer, String, Date, Enum, and_, or_, not_, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import enum
engine = create_engine("mysql+pymysql://xinzhu:123456@192.168.106.128/test", echo=True)
Base = declarative_base()
class MyEnum(enum.Enum):
M = 'M'
F = 'F'
class Employees(Base):
__tablename__ = 'employees'
emp_no = Column(Integer, primary_key=True)
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)
def __repr__(self):
return '{} no={} name={:10} {:11} gender={}'.format(
self.__class__.__name__, self.emp_no, self.first_name, self.last_name,
self.gender.value
)
Session = sessionmaker(bind=engine)
session = Session()
emps = session.query(Employees).filter(Employees.emp_no > 10015)
print(emps)
print('~~~~~~~~~~~~~~~~~~~~~~~~')
def show(emps):
for x in emps:
print(x)
print('~~~~~~~~~~~~~~~~~~\n')
show(emps)
与 and
emps = session.query(Employees).filter(Employees.emp_no > 10015, Employees.emp_no < 10018)
show(emps)
或 or
emps = session.query(Employees).filter(or_(Employees.emp_no == 10015, Employees.emp_no == 10018))
show(emps)
emps = session.query(Employees).filter((Employees.emp_no == 10015) | (Employees.emp_no == 10018))
show(emps)
非 not
emps = session.query(Employees).filter(~(Employees.emp_no == 10015))
show(emps)
emps = session.query(Employees).filter(not_(Employees.emp_no == 10015))
in
emps = session.query(Employees).filter(Employees.emp_no.in_([10015, 10018, 10020]))
show(emps)
like
emps = session.query(Employees).filter(Employees.last_name.like('P%'))
show(emps)
order_by 排序
emps = session.query(Employees).filter(Employees.last_name.like('P%')).order_by(Employees.emp_no.desc())
show(emps) #降序desc在条件上操作
emps = session.query(Employees).filter(Employees.last_name.like('P%')).order_by(Employees.emp_no)
show(emps)
#两个order_by 先看第一个,第一个相同再比较第二个
emps = session.query(Employees).filter(Employees.last_name.like('%M%')).order_by(Employees.gender.asc(), Employees.emp_no.desc())
show(emps)
limit 取几个 .offset()跳过前几个
emps = session.query(Employees).filter(Employees.last_name.like('%M%')).order_by(Employees.gender.asc(), Employees.emp_no.desc())
emps= emps.limit(4).offset(2)
show(emps)
消费者方法
all 所有
print(emps.all())
first 一行,没有返回None 查询方法是limit(1)
print(emps.first())
count 统计行数
print(emps.count())
one 只取1个多于1个出异常
print(emps.one())
或者
print(emps.limit(1).all())
聚合,分组
count, max,min,avg,sum
from sqlalchemy import func
emps = session.query(func.count(Employees.emp_no), func.max(Employees.emp_no),
func.sum(Employees.emp_no), func.avg(Employees.emp_no))
print(emps.all())
print('+++++++++++++++')
print(emps.first())
print(emps.one())
print(emps.scalar())
分组
emps = session.query(func.count(Employees.emp_no), func.sum(Employees.emp_no))
emps.group_by(Employees.emp_no)
print(emps.all())
需求
查询10010部门编号及员工信息
from sqlalchemy import Column, Integer, String, Date, Enum, and_, or_, not_, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import enum
engine = create_engine("mysql+pymysql://xinzhu:123456@192.168.106.128/test", echo=True)
Base = declarative_base()
class MyEnum(enum.Enum):
M = 'M'
F = 'F'
class Employees(Base):
__tablename__ = 'employees'
emp_no = Column(Integer, primary_key=True)
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)
departments = relationship('Dept_emp')
def __repr__(self):
return '{} no={} name={:10} {:11} gender={} depts={}'.format(
self.__class__.__name__, self.emp_no, self.first_name, self.last_name,
self.gender.value, self.departments
)
Session = sessionmaker(bind=engine)
session = Session()
def show(emps):
for x in emps:
print(x)
print('~~~~~~~~~~~~~~~~~~\n')
class Departments(Base):
__tablename__ = 'departments'
dept_no = Column(String(4), primary_key=True)
dept_name = Column(String(40), nullable=False, unique=True)
def __repr__(self):
return '<{} {} {}>'.format(__class__.__name__, self.dept_no,
self.dept_name)
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)
def __repr__(self):
return '<{} {} {}>'.format(__class__.__name__, self.emp_no,
self.dept_no)
emps = session.query(Employees, Dept_emp).\
filter(Employees.emp_no == Dept_emp.emp_no).\
filter(Employees.emp_no == 10010)
print(emps.all())
print('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
emps = session.query(Employees).\
join(Dept_emp, Employees.emp_no == Dept_emp.emp_no).\
filter(Employees.emp_no == 10010)
emps = session.query(Employees).filter(Employees.emp_no == 10010)
x = emps.first()
print(x)
print(x.departments)