ORM(object relational mapping)

对象关系映射（英语：(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)