SQLAlchemy框架的使用

系统管理员 2021-12-21 05:53 323阅读 0赞

# SQLAlchemy框架的使用 #

## ORM ##

ORM,对象关系映射，对象和关系之间的映射，使用面向对象的凡是来操作数据库。

关系模型和Python对象之间的映射
    table   =>  class       #表映射为类
    row     =>  object      #行映射为示例
    column  =>  property    #字段映射为属性

举例

1.  有表student，字段为id int，name varchar，age int
2.  映射到Python为：

class Student:
        id = ?某类型字段
        name = ?某类型字段
        age = ?某类型字段
    
    # 最终得到的实例
    class Student:
        def __init__(self):
            self.id = ?
            self.name = ?
            self.age = ?

## SQLAlchemy ##

*  SQLAlchemy是一个ORM框架
 *  安装 `pip install sqlalchemy`
 *  官方文档 [https://docs.sqlalchemy.org/en/13/][https_docs.sqlalchemy.org_en_13]
 *  查看当前版本
    
        import sqlalchemy
        print(sqlalchemy.__version__)
 *  SQLAlchemy内部使用了连接池

### 1.创建连接 ###

数据库连接的事情，交给引擎

1.  sqlalchemy.create\_engine(sqlstr,echo=False) \#创建连接数据库引擎
    
     *  sqlstr \#连接数据库字符串
        
         *  格式为：`pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]`
     *  echo 是否打印执行的sql语句，默认False,不打印
2.  **注意**：engine是lazy connecting。惰性连接。创建引擎并不会马上连接数据库，直到让数据库执行任务时才连接。

import sqlalchemy
    
    user = "xdd"
    password = "xdd"
    host = "127.0.0.1"
    port = "3306"
    dbname = "xdd"
    
    # mysqldb连接 mysql+mysqldb://<user>:<password>@ <host>[:<port>]/<dbname>
    mysqldb = "mysql+mysqldb://{user}:{password}@{host}:{port}/{dbname}".format(
        user = user,
        password = password,
        host=host,
        port= port,
        dbname = dbname
    )
    print(mysqldb)
    
    # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
    pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
    print(pymysql)
    # sqlalchemy.create_engine(pymysql)
    # echo表示引擎是否打印执行的SQl语句。方便调试
    # engine = sqlalchemy.create_engine(mysqldb,echo=True)
    engine = sqlalchemy.create_engine(pymysql,echo=True)

### 2.Declare a Mapping创建映射 ###

#### 创建基类 ####

from sqlalchemy.ext.declarative import declarative_base
    # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
    Base = declarative_base()

#### 创建实体类 ####

1.  **student表对于的sql语句**
    
        CREATE TABLE student (
            `id` INTEGER NOT NULL AUTO_INCREMENT,
            `name` VARCHAR(64) NOT NULL,
            `age` INTEGER,
            PRIMARY KEY (`id`)
        )
2.  **python中类的构建**
    
     *  必须继承`Base = declarative_base()`类
     *  `__tablename__`指定表名
     *  Column类指定对于的字段，必须指定
    
        from sqlalchemy import Column,Integer,String
        from sqlalchemy.ext.declarative import declarative_base
        
        # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
        Base = declarative_base()
        
        # 创建实体类
        class Student(Base):
            # 指定表名
            __tablename__ = 'student'
            #定义类属性对应字段
            id = Column(Integer,primary_key=True,autoincrement=True)
            name = Column(String(64),nullable=False)
            age = Column(Integer)
            # 注意：Column中第一参数是字段名，如果和属性名不一致，一定要指定
            # age1 = Column("age",Integer)
        
            def __repr__(self):
                return "{} id={} name={} age={}".format(
                    self.__class__.__name__,self.id,self.name,self.age
                )
        
        print(Student)
        print(Student.__dict__)
        print(" -"*20)
        print(repr(Student.__table__))
    
    ![sqlalchemy\_001][sqlalchemy_001]
3.  **实例化**
    
     *  实例化时，如果需要指定属性值，必须使用关键字传参
    
        # 如果需要指定属性，必须使用关键字传参
        s = Student(name="tom")
        print(s.name)
        s.age = 20
        print(s.age)
        print(s)
    
    ![sqlalchemy\_002][sqlalchemy_002]
4.  **创建表**
    
     *  可以使用SQLAlchemy来创建、删除表
     *  生产环境中很少这样创建表，都是系统上线的时候由脚本生成。
     *  生产环境很少删除表，宁可废弃都不能删除。
    
        # 删除继承自Base的所有表
        Base.metadata.drop_all(engine)
        # 创建继承自Base的所有表
        Base.metadata.create_all(engine)
    
     *  创建删除表的完整代码
    
        import sqlalchemy
        from sqlalchemy import Column,Integer,String
        from sqlalchemy.ext.declarative import declarative_base
        
        user = "xdd"
        password = "xdd"
        host = "127.0.0.1"
        port = "3306"
        dbname = "xdd"
        
        # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
        Base = declarative_base()
        
        # 创建实体类
        class Student(Base):
            # 指定表名
            __tablename__ = 'student'
            #定义类属性对应字段
            id = Column(Integer,primary_key=True,autoincrement=True)
            name = Column(String(64),nullable=False)
            age = Column(Integer)
            # 注意：Column中第一参数是字段名，如果和属性名不一致，一定要指定
            # age1 = Column("age",Integer)
        
            def __repr__(self):
                return "{} id={} name={} age={}".format(
                    self.__class__.__name__,self.id,self.name,self.age
                )
        
        # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
        pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
        engine = sqlalchemy.create_engine(pymysql,echo=True)
        
        # 删除继承自Base的所有表
        Base.metadata.drop_all(engine)
        # 创建继承自Base的所有表
        Base.metadata.create_all(engine)
    
    ![sqlalchemy\_003][sqlalchemy_003]

#### 创建会话session ####

1.  在一个会话操作数据库，会话建立在链接上，链接被引擎管理。当第一次使用数据库时，从引擎维护的链接池中获取一个连接使用。
    
     *  **session对象线程不安全，所以不同线程应该使用不同的session对象**
     *  Session类和engine有一个就行了。

# 导入sessionmaker模块
    from sqlalchemy.orm import sessionmaker
    # 创建session
    Session = sessionmaker(bind = engine)
    session = Session()

#### CRUD操作 ####

1.  **增加**
    
     *  add():增加一个对象
     *  add\_all():可迭代对象，元素是对象
    
        import sqlalchemy
        from sqlalchemy import Column,Integer,String
        from sqlalchemy.ext.declarative import declarative_base
        # 导入sessionmaker模块
        from sqlalchemy.orm import sessionmaker
        
        user = "xdd"
        password = "xdd"
        host = "127.0.0.1"
        port = "3306"
        dbname = "xdd"
        
        # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
        Base = declarative_base()
        
        # 创建实体类
        class Student(Base):
            # 指定表名
            __tablename__ = 'student'
            #定义类属性对应字段
            id = Column(Integer,primary_key=True,autoincrement=True)
            name = Column(String(64),nullable=False)
            age = Column(Integer)
            # 注意：Column中第一参数是字段名，如果和属性名不一致，一定要指定
            # age1 = Column("age",Integer)
        
            def __repr__(self):
                return "{} id={} name={} age={}".format(
                    self.__class__.__name__,self.id,self.name,self.age
                )
        
        # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
        pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
        engine = sqlalchemy.create_engine(pymysql,echo=True)
        # 创建session
        session = sessionmaker(bind = engine)()
        
        s = Student(name="tom")
        s.age = 20
        print(s)
        
        session.add(s)
        print(s)
        session.commit()
        print(s)
        print(" -"*30)
        
        try:
            session.add_all([s])
            print(s)
            session.commit()
            print("~ " * 30)
            print(s)
            print("~ " * 30)
        except:
            session.rollback()
            print("roll back")
            raise
    
    ![sqlalchemy\_004][sqlalchemy_004]
    
     *  add\_all()方法不会提交成功的，不是因为它不对。而是s,s成功提交后，s的主键就有了值，所以，只要s没有修改过，就认为没有改动，如下,s变化了，就可以提交修改了。
    
        s.name = 'jerry' #修改
        session.add_all([s])
        session.commit()
    
     *  s主键没有值，就是新增；主键有值，就是找到主键对应的记录修改。
2.  **简单查询**
    
     *  使用query()方法，返回一个Query对象
     *  query方法将实体类传入，返回类的对象可迭代对象，这时候并不查询。迭代它就执行SQL来查询数据库，封装数据到指定类的实例。
     *  get方法使用主键查询，返回一条传入类的一个实例。
     *  查询整个表
    
        # 查询
        students = session.query(Student)
        print("- "*30)
        print(students) #无内容，惰性的
        print("- "*30)
        for student in students:
            print(student)
        print("- "*30)
    
    ![sqlalchemy\_005][sqlalchemy_005]
    
     *  使用get方法查询，主键查询
    
        # 创建session
        session = sessionmaker(bind = engine)()
        # 查询
        student = session.query(Student).get(8)
        print(student)
    
    ![sqlalchemy\_006][sqlalchemy_006]
3.  **改**
    
     *  先查回来，修改后，再提交更改
    
        # 创建session
        session = sessionmaker(bind = engine)()
        # 查询
        student = session.query(Student).get(8)
        print(student)
        student.name = "sum"
        student.age = 30
        session.add(student)
        session.commit()
        print(student)
    
    ![sqlalchemy\_007][sqlalchemy_007]
4.  **删除**
    
     *  先查询后删除
    
        # 创建session
        session = sessionmaker(bind = engine)()
        # 查询
        student = session.query(Student).get(3)
        print(student)
        session.delete(student)
        session.commit()
        student = session.query(Student).get(3)
        print(student)

#### 实体对象的状态 ####

*  每一个实体，都有一个状态属性`_sa_instance_state`,其类型是sqlalchemy.orm.state.InstanceState,可以使用sqlalchemy.inspect(entity)函数查看状态。
 *  常见的状态值有transient、pending、persistent、deleted、detached。

<table> 
 <thead> 
  <tr> 
   <th align="left">状态</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">transient</td> 
   <td align="left">实体类尚未加入到session中，同时并没有保存到数据库中</td> 
  </tr> 
  <tr> 
   <td align="left">pending</td> 
   <td align="left">transient的实体被add()到session中，状态切换到pending,但它还没有flush到数据库中</td> 
  </tr> 
  <tr> 
   <td align="left">persistent</td> 
   <td align="left">session中的实体对象对应着数据库中的真实记录。pending状态在提交成功后可以变成persistent状态，或者查询成功返回的实体也是persistent状态</td> 
  </tr> 
  <tr> 
   <td align="left">deleted</td> 
   <td align="left">实体被删除且已经flush但未commit完成。事务提交成功了，实体变成detached,事务失败，返回persistent状态</td> 
  </tr> 
  <tr> 
   <td align="left">detached</td> 
   <td align="left">删除成功的实体状态进入这个状态</td> 
  </tr> 
 </tbody> 
</table>

*  **新建实体**，状态是transient。临时的
 *  **add()**,添加后从transient状态变成pending状态
 *  **commit()**,提交后，从pending状态变成persistent(持久化)状态
 *  成功查询返回的实体对象，也是persistent状态
 *  persistent状态的实体，修改依然是persistent状态。
 *  persistent状态的实体，删除后，flush后但没有commit，就变成deteled状态。
    
    1.  成功提交后，变为detached状态
    2.  提交失败后，还会还原到persistent状态。
 *  flush方法，主动把改变应用到数据库中去。
 *  **删除、修改**操作，需要对应一个真实的记录，所以要求实体对象是persistent状态。
 *  综合示例

import sqlalchemy
    from sqlalchemy import Column,Integer,String
    from sqlalchemy.ext.declarative import declarative_base
    # 导入sessionmaker模块
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.orm.state import InstanceState
    
    user = "xdd"
    password = "xdd"
    host = "127.0.0.1"
    port = "3306"
    dbname = "xdd"
    
    # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
    Base = declarative_base()
    
    # 创建实体类
    class Student(Base):
        # 指定表名
        __tablename__ = 'student'
        #定义类属性对应字段
        id = Column(Integer,primary_key=True,autoincrement=True)
        name = Column(String(64),nullable=False)
        age = Column(Integer)
        # 注意：Column中第一参数是字段名，如果和属性名不一致，一定要指定
        # age1 = Column("age",Integer)
    
        def __repr__(self):
            return "{} id={} name={} age={}".format(
                self.__class__.__name__,self.id,self.name,self.age
            )
    
    # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
    pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
    engine = sqlalchemy.create_engine(pymysql,echo=False)
    # 创建session
    session = sessionmaker(bind = engine)()
    
    #显示每个实体的状态信息
    def showstat(student,i):
        print("- "*40)
        print(student)
        stat:InstanceState = sqlalchemy.inspect(student)
        print("{}:key={}\nsid={},attached={},transient={},pending={}\npersistent={},deleted={},detached={}".format(
            i,stat.key,
            stat.session_id,stat._attached,stat.transient,stat.pending,
            stat.persistent,stat.deleted,stat.detached
        ))
        print("~ "*40)
    
    
    student = session.query(Student).get(1)
    showstat(student,"查询后的实体状态") # persistent
    
    try:
        student = Student(id=1,name="jum",age=19)
        showstat(student,"实例化的实体状态") #transient,id会是指定的值
    
        student = Student(name="xdd",age=26)
        showstat(student,"实例化的实体状态") #transient
    
        session.add_all([student]) #add后变成pending
        showstat(student,"add后的状态") #pending
        # session.delete(student) #异常，删除的前提必须是persistent,也就是说先查询后删除
        # showstat(student,"删除后的状态")
    
        session.commit() #提交后变成persistent
        showstat(student,"commit提交后的状态") #persistent
    
    except Exception as e:
        session.rollback()
        print(e,"~~~~~~~~~~~~~~~~")

![sqlalchemy\_008][sqlalchemy_008]

*  只有提交过后，或者查询出来的实体对象才有key，实例化的未提交的对象key值为None
 *  如果将上述核心代码改为如下：

student = session.query(Student).get(1)
    showstat(student,"查询后的实体状态") # persistent
    
    try:
        session.delete(student) #删除的前提是persistent
        showstat(student,"删除后的状态") #persistent
    
        session.flush()
        showstat(student,"flush后的状态") #deleted
    
        session.commit()
        showstat(student,"commit后的状态") #detached 删除后没有了sid
    
    except Exception as e:
        session.rollback()
        print(e,"~~~~~~~~~~~~~~~~")

![sqlalchemy\_009][sqlalchemy_009]

### 复杂查询 ###

*  数据库中sql脚本为：

CREATE TABLE `employees` (
        `emp_no` int(11) NOT NULL,
        `birth_date` date NOT NULL,
        `first_name` varchar(14) NOT NULL,
        `last_name` varchar(16) NOT NULL,
        `gender` enum('M','F') NOT NULL,
        `hire_date` date NOT NULL,
        PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT charset = utf8;
    
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10003, '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10004, '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10006, '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1989-02-10');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1994-09-15');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10010, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1989-08-24');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10011, '1953-11-07', 'Mary', 'Sluis', 'F', '1990-01-22');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10012, '1960-10-04', 'Patricio', 'Bridgland', 'M', '1992-12-18');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10013, '1963-06-07', 'Eberhardt', 'Terkki', 'M', '1985-10-20');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10014, '1956-02-12', 'Berni', 'Genin', 'M', '1987-03-11');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10015, '1959-08-19', 'Guoxiang', 'Nooteboom', 'M', '1987-07-02');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10016, '1961-05-02', 'Kazuhito', 'Cappelletti', 'M', '1995-01-27');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10017, '1958-07-06', 'Cristinel', 'Bouloucos', 'F', '1993-08-03');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10018, '1954-06-19', 'Kazuhide', 'Peha', 'F', '1987-04-03');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10019, '1953-01-23', 'Lillian', 'Haddadi', 'M', '1999-04-30');
    INSERT INTO `xdd`.`employees`(`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10020, '1952-12-24', 'Mayuko', 'Warwick', 'M', '1991-01-26');

1.  **构建对应实体类**
    
        import sqlalchemy
        from sqlalchemy import Column,Integer,String,DATE,Enum
        import enum
        from sqlalchemy.ext.declarative import declarative_base
        # 导入sessionmaker模块
        from sqlalchemy.orm import sessionmaker
        
        
        user = "xdd"
        password = "xdd"
        host = "127.0.0.1"
        port = "3306"
        dbname = "xdd"
        
        # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
        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):
                ref = "{} no={} name={} {} gender={}".format(
                    self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value
                )
                return ref
        
        # 打印函数
        def show(emps):
            for x in emps:
                print(x)
            print(" -"*30,"\n")
        
        # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
        pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
        engine = sqlalchemy.create_engine(pymysql,echo=True)
        # 创建session
        session = sessionmaker(bind=engine)()
    
    **注意**：下面查询都会基于上面这段代码
2.  **简单where条件查询**
    
        # where 条件查询
        emps = session.query(Employees).filter(Employees.emp_no == 10001)
        # emps = session.query(Employees).filter(Employees.emp_no > 10015)
        show(emps)
    
    ![sqlalchemy\_010][sqlalchemy_010]
3.  **and条件**
    
        # 与或非
        from sqlalchemy import and_
        
        # and 条件
        emps1 = session.query(Employees).filter(Employees.emp_no > 10015).filter(Employees.emp_no <10018)
        show(emps1)
        emps2 = session.query(Employees).filter(Employees.emp_no > 10015,Employees.emp_no <10018)
        show(emps2)
        emps3 = session.query(Employees).filter(and_(Employees.emp_no > 10015,Employees.emp_no <10018))
        show(emps3)
        emps4 = session.query(Employees).filter((Employees.emp_no > 10015) & (Employees.emp_no <10018))
        show(emps4) # & 一定要注意&符号两边的表达式都要加括号
    
    ![sqlalchemy\_011][sqlalchemy_011]
4.  **or条件**
    
        from sqlalchemy import or_
        
        # or条件
        emps1 = session.query(Employees).filter(or_(Employees.emp_no > 10018,Employees.emp_no <10005))
        show(emps1)
        emps2 = session.query(Employees).filter((Employees.emp_no > 10018) | (Employees.emp_no <10005))
        show(emps2)
    
    ![sqlalchemy\_012][sqlalchemy_012]
5.  **not条件**
    
        from sqlalchemy import not_
        
        # not条件
        emps1 = session.query(Employees).filter(not_(Employees.emp_no < 10018))
        show(emps1)
        # 注意：一定要加括号
        emps2 = session.query(Employees).filter(~(Employees.emp_no < 10018))
        show(emps2)
        # 总之，与或非的运算符&，|，~，一定要在表达式 上加上括号
    
    ![sqlalchemy\_013][sqlalchemy_013]
6.  **in条件**
    
        # in条件
        emps1 = session.query(Employees).filter(Employees.emp_no.in_([10010,10011,10012]))
        show(emps1)
    
    ![sqlalchemy\_014][sqlalchemy_014]
7.  **not in 条件**
    
        # not in条件
        from sqlalchemy import not_
        
        emplist = [10010,10011,10012,10013,10014,10015,10016,10017,10018,10019,10001,10002,10003,10004,10005,10006]
        emps1 = session.query(Employees).filter(Employees.emp_no.notin_(emplist))
        show(emps1)
        emps2 = session.query(Employees).filter(~Employees.emp_no.in_(emplist))
        show(emps2)
        emps3 = session.query(Employees).filter(not_(Employees.emp_no.in_(emplist)))
        show(emps3)
    
    ![sqlalchemy\_015][sqlalchemy_015]
8.  **like与not like条件**
    
     *  **ilike**可以忽略大小写
    
        # like与not like条件
        emps = session.query(Employees).filter(Employees.last_name.like("m%"))
        show(emps)
        emps = session.query(Employees).filter(Employees.last_name.notlike("m%"))
        show(emps)
    
    ![sqlalchemy\_016][sqlalchemy_016]
9.  **排序**
    
        # 排序升序
        emps = session.query(Employees).filter(Employees.emp_no >10015).order_by(Employees.emp_no.asc())
        show(emps)
        # 排序降序
        emps = session.query(Employees).filter(Employees.emp_no >10015).order_by(Employees.emp_no.desc())
        show(emps)
        # 多列排序
        emps = session.query(Employees).filter(Employees.emp_no >10015).order_by(Employees.emp_no.desc()).order_by(Employees.last_name.desc())
        show(emps)
        emps = session.query(Employees).filter(Employees.emp_no >10015).order_by(Employees.emp_no.desc(),Employees.first_name.desc())
        show(emps)
    
    ![sqlalchemy\_017][sqlalchemy_017]
10. **分页**
    
        # 分页
        emps = session.query(Employees).filter(Employees.emp_no >10010).limit(4)
        show(emps)
        emps = session.query(Employees).filter(Employees.emp_no >10010).limit(4).offset(2)
        show(emps)
    
    ![sqlalchemy\_018][sqlalchemy_018]
11. **消费者方法**
    
     *  消费者方法调用后，Query对象(可迭代)就转换成了一个容器
    
        # 总行数
        emps = session.query(Employees)
        print(len(list(emps))) #查询得到结果集，转换成list,然后取长度
        print(emps.count()) #聚合函数count(*)的查询
        
        # 取所有数据
        print(emps.all()) #返回列表，查不到返回空列表
        
        # 取首行
        print(emps.first()) #返回首行，查不到返回None,等驾驭limit
        
        # 有且只能有一行
        # print(emps.one()) #如果查询结果是多行抛出异常
        print(emps.limit(1).one())
        
        # 删除 delete by query
        session.query(Employees).filter(Employees.emp_no > 10018).delete()
        # session.commit() # 提交删除
12. **聚合函数count,max,min,avg**
    
     *  需要使用模块func `from sqlalchemy import func`
    
        # 聚合函数
        from sqlalchemy import func
        
        query = session.query(func.count(Employees.emp_no))
        print(query.all()) #列表中一个元素
        print(query.first()) #一个只能有一个元素的元组
        print(query.one()) #只能有一行返回，一个元组
        print(query.scalar()) #取one()的第一个元素
        
        print(session.query(func.max(Employees.emp_no)).scalar())
        print(session.query(func.min(Employees.emp_no)).scalar())
        print(session.query(func.avg(Employees.emp_no)).scalar())
    
    ![sqlalchemy\_019][sqlalchemy_019]
13. **分组**
    
        # 聚合函数
        from sqlalchemy import func
        query = session.query(Employees.gender,func.count(Employees.emp_no),
                            func.max(Employees.emp_no)).group_by(Employees.gender)
        
        for g,c,m in query.all():
            print(g.value,c,m)
    
    ![sqlalchemy\_020][sqlalchemy_020]

### 关联查询 ###

*  **sql脚本如下**：

CREATE TABLE `departments` (
      `dept_no` char(4) NOT NULL,
      `dept_name` varchar(40) NOT NULL,
      PRIMARY KEY (`dept_no`),
      UNIQUE KEY `dept_name` (`dept_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `employees` (
      `emp_no` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `first_name` varchar(14) NOT NULL,
      `last_name` varchar(16) NOT NULL,
      `gender` enum('M','F') NOT NULL,
      `hire_date` date NOT NULL,
      PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `dept_emp` (
      `emp_no` int(11) NOT NULL,
      `dept_no` char(4) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date NOT NULL,
      PRIMARY KEY (`emp_no`,`dept_no`),
      KEY `dept_no` (`dept_no`),
      CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
      CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- departments表添加数据
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d009', 'Customer Service');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d005', 'Development');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d002', 'Finance');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d003', 'Human Resources');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d001', 'Marketing');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d004', 'Production');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d006', 'Quality Management');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d008', 'Research');
    INSERT INTO `xdd`.`departments`(`dept_no`, `dept_name`) VALUES ('d007', 'Sales');
    
    -- employees表添加数据，之前sql脚本中有，这里就不重复写入了
    
    -- dept_emp表中添加数据
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10001, 'd005', '1986-06-26', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10002, 'd007', '1996-08-03', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10003, 'd004', '1995-12-03', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10004, 'd004', '1986-12-01', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10005, 'd003', '1989-09-12', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10006, 'd005', '1990-08-05', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10007, 'd008', '1989-02-10', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10008, 'd005', '1998-03-11', '2000-07-31');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10009, 'd006', '1985-02-18', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10010, 'd004', '1996-11-24', '2000-06-26');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10010, 'd006', '2000-06-26', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10011, 'd009', '1990-01-22', '1996-11-09');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10012, 'd005', '1992-12-18', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10013, 'd003', '1985-10-20', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10014, 'd005', '1993-12-29', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10015, 'd008', '1992-09-19', '1993-08-22');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10016, 'd007', '1998-02-11', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10017, 'd001', '1993-08-03', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10018, 'd004', '1992-07-29', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10018, 'd005', '1987-04-03', '1992-07-29');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10019, 'd008', '1999-04-30', '9999-01-01');
    INSERT INTO `xdd`.`dept_emp`(`emp_no`, `dept_no`, `from_date`, `to_date`) VALUES (10020, 'd004', '1997-12-30', '9999-01-01');

*  从语句看出员工、部门之间的关系是多对多关系。
 *  先把这些表的Model类和字段属性建立起来。
 *  `ForeignKey('employees.emp_no', ondelete='CASCADE')` 定义外键约束

import sqlalchemy
    from sqlalchemy import Column,Integer,String,Date,Enum,ForeignKey
    import enum
    from sqlalchemy.ext.declarative import declarative_base
    # 导入sessionmaker模块
    from sqlalchemy.orm import sessionmaker
    
    user = "xdd"
    password = "xdd"
    host = "127.0.0.1"
    port = "3306"
    dbname = "xdd"
    
    # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
    Base = declarative_base()
    
    # 部门表
    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 "{} no={} name={}".format(
                type(self).__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)
        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):
            ref = "{} no={} name={} {} gender={}".format(
                self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value
            )
            return ref
    
    # 员工所属部门表
    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 "{} empno={} deptno={}".format(
                type(self).__name__,self.emp_no,self.dept_no)
    
    # 打印函数
    def show(emps):
        for x in emps:
            print(x)
        print(" -"*30,"\n")
    
    # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
    pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
    engine = sqlalchemy.create_engine(pymysql,echo=True)
    # 创建session
    session = sessionmaker(bind=engine)()

1.  使用**隐试内连接查询**10010员工所在部门编号及员工信息
    
        results = session.query(Employees,Dept_emp).filter((Employees.emp_no == Dept_emp.emp_no) & (Employees.emp_no == 10010)).all()
        show(results)
    
     *  对应的sql语句为：
    
        SELECT * FROM employees, dept_emp WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = 10010
    
    ![sqlalchemy\_021][sqlalchemy_021]
2.  **使用join查询**
    
        results = session.query(Employees.emp_no,Dept_emp).join(Dept_emp,Employees.emp_no == Dept_emp.emp_no).filter(Employees.emp_no == 10010).all()
        show(results)
    
    ![sqlalchemy\_023][sqlalchemy_023]
3.  **使用left join查询**  
    在join(),里面将isouter属性设置为Tru，就会使用left join查询
    
        # 使用left join 左链接查询
        results = session.query(Employees).join(Dept_emp,Dept_emp.emp_no==Employees.emp_no,isouter=True).filter(Employees.emp_no == 10010).all()
        show(results)
    
    ![sqlalchemy\_024][sqlalchemy_024]

### 实体对象中的属性关系 ###

*  在上述例子中，查询10010员工所在不部门编号及员工信息，使用join链接

results = session.query(Employees).join(Dept_emp,Employees.emp_no == Dept_emp.emp_no).filter(Employees.emp_no == 10010).all()
    print(results)

![sqlalchemy\_022][sqlalchemy_022]

*  这种写法只返回一条数据，原因在于`query(Employees)`这个只能返回一个实体对象中去。为了解决这个问题，需要修改实体类Employees,来增加属性用来存放部门信息
 *  sqlalchemy.orm.relationship(实体类名字字符串)

# 员工信息表
    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") # 新添加属性，帮助链接查询dept_emp员工部门表
    
        def __repr__(self):
            ref = "{} no={} name={} {} gender={} departments={}".format(
                self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value,self.departments
            )
            return ref

*  查询10010员工的所在部门编号及员工信息
    
        result = session.query(Employees).filter(Employees.emp_no == 10010).one()
        print(result)
        for i in result.departments:
            print(i)
    
    ![sqlalchemy\_025][sqlalchemy_025]
    
    1.  只要不访问departments属性，就不会查询dept\_emp这张表

### 总结 ###

*  在开发中，一般都会采用ORM框架，这样就可以使用对象操作表了。
 *  定义表映射的类，使用Column的描述器定义类属性，使用ForeignKey来定义外键约束。
 *  如果在一个对象中，想查看其他表对应的对象的内容，就要使用relationship来定义关系。

1.  是否使用外键约束？
    
     *  力挺派：能使数据保证完整性一致性
     *  弃用派：开发难度增加。

*  如果使用，则在大量数据的时候影响插入、修改、删除的效率。弃用则需要在业务层保证数据的一致性。

### 作业 ###

*  使用SqlAlchemy
    
    1.  10009号员工的工号、姓名、所有的头衔title
    2.  10010号员工的工号、姓名，所在部门名称

import sqlalchemy
    from sqlalchemy import Column,Integer,String,Date,Enum,ForeignKey,join
    import enum
    from sqlalchemy.ext.declarative import declarative_base
    # 导入sessionmaker模块
    from sqlalchemy.orm import sessionmaker,relationship
    
    user = "xdd"
    password = "xdd"
    host = "127.0.0.1"
    port = "3306"
    dbname = "xdd"
    
    # 创建基类，便于实体类继承。SQLAlchem大量使用了元编程
    Base = declarative_base()
    
    # 部门表
    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 "{} no={} name={}".format(
                type(self).__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)
    
        departments = relationship("Departments") #对应部门
    
        def __repr__(self):
            return "{} empno={} deptno={}".format(
                type(self).__name__,self.emp_no,self.dept_no)
    
    # 性别枚举类
    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") # 新添加属性，帮助链接查询dept_emp员工部门表
        salaries = relationship("Salaries") # 增加员工工资
        titles = relationship("Titles") #增加员工头衔
    
        def __repr__(self):
            ref = "{} no={} name={} {} gender={} departments={} salaries={} titles={}".format(
                self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value,
                self.departments,self.salaries,self.titles
            )
            return ref
    
    # 员工薪资表
    class Salaries(Base):
        __tablename__ = "salaries"
    
        emp_no = Column(Integer,ForeignKey("employees.emp_no",ondelete='CASCADE'),primary_key=True)
        salary = Column(Integer,nullable=False)
        from_date = Column(Date,primary_key=True)
        to_date = Column(Date,nullable=False)
    
        def __repr__(self):
            return "{} emp_no={} salary={} from_date={} to_date={}".format(
                self.__class__.__name__,self.emp_no,self.salary,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),nullable=False)
        from_date = Column(Date,nullable=False)
        to_date = Column(Date,default=None)
    
        def __repr__(self):
            return "{} emp_no={} title={} from_date={} to_date={}".format(
                self.__class__.__name__,self.emp_no,self.title,self.from_date,self.to_date
            )
    
    # pymysql的链接方式 mysql+pymysql://<username>:<password>@<host>[:<port>]/<dbname>[?<options>]
    pymysql = "mysql+pymysql://{}:{}@{}:{}/{}".format(user,password,host,port,dbname)
    # engine = sqlalchemy.create_engine(pymysql,echo=True)
    engine = sqlalchemy.create_engine(pymysql,echo=False)
    # 创建session
    session = sessionmaker(bind=engine)()
    
    # 第一问
    result:Employees = session.query(Employees).filter(Employees.emp_no==10009).one()
    dept = [i.dept_no for i in result.departments] # 员工工号
    title = [i.title for i in result.titles]
    print("10009号员工的工号{}，姓名{} {}，头衔{}".format(
        dept,result.last_name,result.first_name,title
    ))
    
    # 第二问思路一
    result2:Employees = session.query(Employees.emp_no,Employees.first_name+" "+Employees.last_name,Dept_emp.dept_no,Departments.dept_name)\
        .join(Dept_emp,Dept_emp.emp_no==Employees.emp_no,isouter=True).join(Departments,Departments.dept_no==Dept_emp.dept_no,isouter=True).filter(Employees.emp_no==10010)\
        .order_by(Dept_emp.from_date.desc()).first()
    print("{}号员工，姓名为：{}，部门编号为：{}，所在部门名称：{}".format(*result2))
    
    # 第二问思路二
    result3:Employees = session.query(Employees).filter(Employees.emp_no==10010).one()
    msg = [result3.emp_no,result3.first_name+" "+result3.last_name]
    one:Dept_emp = result3.departments[0]
    for i in result3.departments[1:]:
        if one.from_date<i.from_date:
            one = i
    msg.append(one.dept_no)
    msg.append(one.departments.dept_name)
    print("{}号员工，姓名：{}，部门编号：{}，部门名称：{}".format(*msg))

![sqlalchemy\_026][sqlalchemy_026]

### 附加sql脚本 ###

-- ----------------------------
    -- Table structure for departments
    -- ----------------------------
    DROP TABLE IF EXISTS `departments`;
    CREATE TABLE `departments` (
      `dept_no` char(4) NOT NULL,
      `dept_name` varchar(40) NOT NULL,
      PRIMARY KEY (`dept_no`),
      UNIQUE KEY `dept_name` (`dept_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of departments
    -- ----------------------------
    INSERT INTO `departments` VALUES ('d009', 'Customer Service');
    INSERT INTO `departments` VALUES ('d005', 'Development');
    INSERT INTO `departments` VALUES ('d002', 'Finance');
    INSERT INTO `departments` VALUES ('d003', 'Human Resources');
    INSERT INTO `departments` VALUES ('d001', 'Marketing');
    INSERT INTO `departments` VALUES ('d004', 'Production');
    INSERT INTO `departments` VALUES ('d006', 'Quality Management');
    INSERT INTO `departments` VALUES ('d008', 'Research');
    INSERT INTO `departments` VALUES ('d007', 'Sales');
    
    -- ----------------------------
    -- Table structure for dept_emp
    -- ----------------------------
    DROP TABLE IF EXISTS `dept_emp`;
    CREATE TABLE `dept_emp` (
      `emp_no` int(11) NOT NULL,
      `dept_no` char(4) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date NOT NULL,
      PRIMARY KEY (`emp_no`,`dept_no`),
      KEY `dept_no` (`dept_no`),
      CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
      CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of dept_emp
    -- ----------------------------
    INSERT INTO `dept_emp` VALUES ('10001', 'd005', '1986-06-26', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10002', 'd007', '1996-08-03', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10003', 'd004', '1995-12-03', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10004', 'd004', '1986-12-01', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10005', 'd003', '1989-09-12', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10006', 'd005', '1990-08-05', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10007', 'd008', '1989-02-10', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10008', 'd005', '1998-03-11', '2000-07-31');
    INSERT INTO `dept_emp` VALUES ('10009', 'd006', '1985-02-18', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10010', 'd004', '1996-11-24', '2000-06-26');
    INSERT INTO `dept_emp` VALUES ('10010', 'd006', '2000-06-26', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10011', 'd009', '1990-01-22', '1996-11-09');
    INSERT INTO `dept_emp` VALUES ('10012', 'd005', '1992-12-18', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10013', 'd003', '1985-10-20', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10014', 'd005', '1993-12-29', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10015', 'd008', '1992-09-19', '1993-08-22');
    INSERT INTO `dept_emp` VALUES ('10016', 'd007', '1998-02-11', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10017', 'd001', '1993-08-03', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10018', 'd004', '1992-07-29', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10018', 'd005', '1987-04-03', '1992-07-29');
    INSERT INTO `dept_emp` VALUES ('10019', 'd008', '1999-04-30', '9999-01-01');
    INSERT INTO `dept_emp` VALUES ('10020', 'd004', '1997-12-30', '9999-01-01');
    
    -- ----------------------------
    -- Table structure for employees
    -- ----------------------------
    DROP TABLE IF EXISTS `employees`;
    CREATE TABLE `employees` (
      `emp_no` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `first_name` varchar(14) NOT NULL,
      `last_name` varchar(16) NOT NULL,
      `gender` enum('M','F') NOT NULL,
      `hire_date` date NOT NULL,
      PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of employees
    -- ----------------------------
    INSERT INTO `employees` VALUES ('10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26');
    INSERT INTO `employees` VALUES ('10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21');
    INSERT INTO `employees` VALUES ('10003', '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28');
    INSERT INTO `employees` VALUES ('10004', '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01');
    INSERT INTO `employees` VALUES ('10005', '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12');
    INSERT INTO `employees` VALUES ('10006', '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02');
    INSERT INTO `employees` VALUES ('10007', '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1989-02-10');
    INSERT INTO `employees` VALUES ('10008', '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1994-09-15');
    INSERT INTO `employees` VALUES ('10009', '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18');
    INSERT INTO `employees` VALUES ('10010', '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1989-08-24');
    INSERT INTO `employees` VALUES ('10011', '1953-11-07', 'Mary', 'Sluis', 'F', '1990-01-22');
    INSERT INTO `employees` VALUES ('10012', '1960-10-04', 'Patricio', 'Bridgland', 'M', '1992-12-18');
    INSERT INTO `employees` VALUES ('10013', '1963-06-07', 'Eberhardt', 'Terkki', 'M', '1985-10-20');
    INSERT INTO `employees` VALUES ('10014', '1956-02-12', 'Berni', 'Genin', 'M', '1987-03-11');
    INSERT INTO `employees` VALUES ('10015', '1959-08-19', 'Guoxiang', 'Nooteboom', 'M', '1987-07-02');
    INSERT INTO `employees` VALUES ('10016', '1961-05-02', 'Kazuhito', 'Cappelletti', 'M', '1995-01-27');
    INSERT INTO `employees` VALUES ('10017', '1958-07-06', 'Cristinel', 'Bouloucos', 'F', '1993-08-03');
    INSERT INTO `employees` VALUES ('10018', '1954-06-19', 'Kazuhide', 'Peha', 'F', '1987-04-03');
    INSERT INTO `employees` VALUES ('10019', '1953-01-23', 'Lillian', 'Haddadi', 'M', '1999-04-30');
    INSERT INTO `employees` VALUES ('10020', '1952-12-24', 'Mayuko', 'Warwick', 'M', '1991-01-26');
    
    -- ----------------------------
    -- Table structure for salaries
    -- ----------------------------
    DROP TABLE IF EXISTS `salaries`;
    CREATE TABLE `salaries` (
      `emp_no` int(11) NOT NULL,
      `salary` int(11) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date NOT NULL,
      PRIMARY KEY (`emp_no`,`from_date`),
      CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of salaries
    -- ----------------------------
    INSERT INTO `salaries` VALUES ('10001', '60117', '1986-06-26', '1987-06-26');
    INSERT INTO `salaries` VALUES ('10001', '62102', '1987-06-26', '1988-06-25');
    INSERT INTO `salaries` VALUES ('10001', '66074', '1988-06-25', '1989-06-25');
    INSERT INTO `salaries` VALUES ('10001', '66596', '1989-06-25', '1990-06-25');
    INSERT INTO `salaries` VALUES ('10001', '66961', '1990-06-25', '1991-06-25');
    INSERT INTO `salaries` VALUES ('10001', '71046', '1991-06-25', '1992-06-24');
    INSERT INTO `salaries` VALUES ('10001', '74333', '1992-06-24', '1993-06-24');
    INSERT INTO `salaries` VALUES ('10001', '75286', '1993-06-24', '1994-06-24');
    INSERT INTO `salaries` VALUES ('10001', '75994', '1994-06-24', '1995-06-24');
    INSERT INTO `salaries` VALUES ('10001', '76884', '1995-06-24', '1996-06-23');
    INSERT INTO `salaries` VALUES ('10001', '80013', '1996-06-23', '1997-06-23');
    INSERT INTO `salaries` VALUES ('10001', '81025', '1997-06-23', '1998-06-23');
    INSERT INTO `salaries` VALUES ('10001', '81097', '1998-06-23', '1999-06-23');
    INSERT INTO `salaries` VALUES ('10001', '84917', '1999-06-23', '2000-06-22');
    INSERT INTO `salaries` VALUES ('10001', '85112', '2000-06-22', '2001-06-22');
    INSERT INTO `salaries` VALUES ('10001', '85097', '2001-06-22', '2002-06-22');
    INSERT INTO `salaries` VALUES ('10001', '88958', '2002-06-22', '9999-01-01');
    INSERT INTO `salaries` VALUES ('10002', '65828', '1996-08-03', '1997-08-03');
    INSERT INTO `salaries` VALUES ('10002', '65909', '1997-08-03', '1998-08-03');
    INSERT INTO `salaries` VALUES ('10002', '67534', '1998-08-03', '1999-08-03');
    INSERT INTO `salaries` VALUES ('10002', '69366', '1999-08-03', '2000-08-02');
    INSERT INTO `salaries` VALUES ('10002', '71963', '2000-08-02', '2001-08-02');
    INSERT INTO `salaries` VALUES ('10002', '72527', '2001-08-02', '9999-01-01');
    INSERT INTO `salaries` VALUES ('10003', '40006', '1995-12-03', '1996-12-02');
    INSERT INTO `salaries` VALUES ('10003', '43616', '1996-12-02', '1997-12-02');
    INSERT INTO `salaries` VALUES ('10003', '43466', '1997-12-02', '1998-12-02');
    INSERT INTO `salaries` VALUES ('10003', '43636', '1998-12-02', '1999-12-02');
    INSERT INTO `salaries` VALUES ('10003', '43478', '1999-12-02', '2000-12-01');
    INSERT INTO `salaries` VALUES ('10003', '43699', '2000-12-01', '2001-12-01');
    INSERT INTO `salaries` VALUES ('10003', '43311', '2001-12-01', '9999-01-01');
    INSERT INTO `salaries` VALUES ('10004', '40054', '1986-12-01', '1987-12-01');
    INSERT INTO `salaries` VALUES ('10004', '42283', '1987-12-01', '1988-11-30');
    INSERT INTO `salaries` VALUES ('10004', '42542', '1988-11-30', '1989-11-30');
    INSERT INTO `salaries` VALUES ('10004', '46065', '1989-11-30', '1990-11-30');
    INSERT INTO `salaries` VALUES ('10004', '48271', '1990-11-30', '1991-11-30');
    INSERT INTO `salaries` VALUES ('10004', '50594', '1991-11-30', '1992-11-29');
    INSERT INTO `salaries` VALUES ('10004', '52119', '1992-11-29', '1993-11-29');
    INSERT INTO `salaries` VALUES ('10004', '54693', '1993-11-29', '1994-11-29');
    INSERT INTO `salaries` VALUES ('10004', '58326', '1994-11-29', '1995-11-29');
    INSERT INTO `salaries` VALUES ('10004', '60770', '1995-11-29', '1996-11-28');
    
    -- ----------------------------
    -- Table structure for titles
    -- ----------------------------
    DROP TABLE IF EXISTS `titles`;
    CREATE TABLE `titles` (
      `emp_no` int(11) NOT NULL,
      `title` varchar(50) NOT NULL,
      `from_date` date NOT NULL,
      `to_date` date DEFAULT NULL,
      PRIMARY KEY (`emp_no`,`title`,`from_date`),
      CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of titles
    -- ----------------------------
    INSERT INTO `titles` VALUES ('10001', 'Senior Engineer', '1986-06-26', '9999-01-01');
    INSERT INTO `titles` VALUES ('10002', 'Staff', '1996-08-03', '9999-01-01');
    INSERT INTO `titles` VALUES ('10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
    INSERT INTO `titles` VALUES ('10004', 'Engineer', '1986-12-01', '1995-12-01');
    INSERT INTO `titles` VALUES ('10004', 'Senior Engineer', '1995-12-01', '9999-01-01');
    INSERT INTO `titles` VALUES ('10005', 'Senior Staff', '1996-09-12', '9999-01-01');
    INSERT INTO `titles` VALUES ('10005', 'Staff', '1989-09-12', '1996-09-12');
    INSERT INTO `titles` VALUES ('10006', 'Senior Engineer', '1990-08-05', '9999-01-01');
    INSERT INTO `titles` VALUES ('10007', 'Senior Staff', '1996-02-11', '9999-01-01');
    INSERT INTO `titles` VALUES ('10007', 'Staff', '1989-02-10', '1996-02-11');
    INSERT INTO `titles` VALUES ('10008', 'Assistant Engineer', '1998-03-11', '2000-07-31');
    INSERT INTO `titles` VALUES ('10009', 'Assistant Engineer', '1985-02-18', '1990-02-18');
    INSERT INTO `titles` VALUES ('10009', 'Engineer', '1990-02-18', '1995-02-18');
    INSERT INTO `titles` VALUES ('10009', 'Senior Engineer', '1995-02-18', '9999-01-01');
    INSERT INTO `titles` VALUES ('10010', 'Engineer', '1996-11-24', '9999-01-01');
    INSERT INTO `titles` VALUES ('10011', 'Staff', '1990-01-22', '1996-11-09');
    INSERT INTO `titles` VALUES ('10012', 'Engineer', '1992-12-18', '2000-12-18');
    INSERT INTO `titles` VALUES ('10012', 'Senior Engineer', '2000-12-18', '9999-01-01');
    INSERT INTO `titles` VALUES ('10013', 'Senior Staff', '1985-10-20', '9999-01-01');
    INSERT INTO `titles` VALUES ('10014', 'Engineer', '1993-12-29', '9999-01-01');
    INSERT INTO `titles` VALUES ('10015', 'Senior Staff', '1992-09-19', '1993-08-22');

[https_docs.sqlalchemy.org_en_13]: https://docs.sqlalchemy.org/en/13/
[sqlalchemy_001]: /images/20211221/2910e8296eea44b99e4ecb5d6d9cba7f.png
[sqlalchemy_002]: /images/20211221/e50b8a88c6a242e3a15a5af0971bbefa.png
[sqlalchemy_003]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_003.jpg
[sqlalchemy_004]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_004.jpg
[sqlalchemy_005]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_005.jpg
[sqlalchemy_006]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_006.jpg
[sqlalchemy_007]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_007.jpg
[sqlalchemy_008]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_008.jpg
[sqlalchemy_009]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_009.jpg
[sqlalchemy_010]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_010.jpg
[sqlalchemy_011]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_011.jpg
[sqlalchemy_012]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_012.jpg
[sqlalchemy_013]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_013.jpg
[sqlalchemy_014]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_014.jpg
[sqlalchemy_015]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_015.jpg
[sqlalchemy_016]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_016.jpg
[sqlalchemy_017]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_017.jpg
[sqlalchemy_018]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_018.jpg
[sqlalchemy_019]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_019.jpg
[sqlalchemy_020]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_020.jpg
[sqlalchemy_021]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_021.jpg
[sqlalchemy_023]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_023.jpg
[sqlalchemy_024]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_024.jpg
[sqlalchemy_022]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_022.jpg
[sqlalchemy_025]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_025.jpg
[sqlalchemy_026]: https://raw.githubusercontent.com/1263351411/xdd.github.io/master/img/python/sqlalchemy_026.jpg