SQLAlchemy

SQLAIchemy是Python中的ORM框架
安装：pip install sqlalchemy

1、查看版本 Version Check
官方文档：http://docs.sqlalchemy.org/en/latest/

2、创建连接 Connecting
1）SQLAlchemy内部使用了连接池，减少了连接创建时的消耗；

2）数据库连接的事情封装到了引擎；用引擎连接；引擎类提供一个数据库的包装类，负责数据库的连接和CRUD操作、取代实体类的CRUD方法，实体类不提供数据库连接、只完成表和类的映射；
引擎结构如下：

Engine是任何SQLAlchemy应用程序的起点；它是实际数据库及其DBAPI的“home base”，它描述了如何与特定类型的数据库/ DBAPI组合进行通信；
方言Dialect：支持各种数据库，如MySQL、Oracle、SQLite；SQLAlchemy包含许多Dialect后端的实现；包含最常见数据库的方言；
该create_engine()函数Engine基于数据库网址URL 生成对象；这些URL遵循RFC-1738，通常可以包括用户名，密码，主机名，数据库名称以及用于其他配置的可选关键字参数；在某些情况下，接受文件路径，而在其他情况下，“数据源名称”替换“主机”和“数据库”部分；数据库URL的典型形式是：
dialect+driver://username:[email protected]:port/database
方言名称包括SQLAlchemy的方言，名称的标识名称，例如sqlite，mysql，postgresql，oracle，或mssql；drivername是用于使用全小写字母连接到数据库的DBAPI的名称；如果未指定，将导入“默认”DBAPI（如果可用） - 此默认值通常是该后端可用的最广为人知的驱动程序；

返回值create_engine()是一个实例 Engine，它代表数据库的核心接口，通过一个方言进行调整，该方言处理数据库和正在使用的DBAPI的细节；第一次调用Engine.execute()或Engine.connect()调用方法时，Engine建立与数据库的真实DBAPI连接，然后用于发出SQL；使用ORM时，我们通常不会在Engine创建后直接使用; 相反，它将在ORM的幕后使用；
在Python3中driver一般只写两种：pymql、mqldb

3）echo=True，是否打开Debug，即引擎是否打印执行的语句，调试的时候打开很方便；生产环境一般不打开；默认echo是False；
该echo标志是设置SQLAlchemy日志记录的快捷方式，可通过Python的标准logging模块完成。启用它后，我们将看到生成的所有生成的SQL；如果您正在完成本教程并希望生成更少的输出，请将其设置为False；
注意：创建引擎的时候并不会马上连接数据库，而是从连接池挑一个连接用，直到让数据库执行任务时才连接；这一次连接会话完结时，就会归还给连接池；

3、创建映射 Declare a Mapping
1）创建基类

from sqlalchemy.ext.declarative import declarative_base
​
Base = declarative_base()

现在创建方式通常是从元类生成一个基类，其它实体类继承自基类；

2）创建实体类
__tablename__指定表名，Column类指定对应的字段，必须指定

3）实例化

4）创建表
可以使用SQLAlchemy来创建、删除表；生产环境很少这样创建表，都是系统上线时由专业的模型工具设计数据库的表格并生成sql脚本，然后导入到数据库生成实体表；生产环境很少删除表，宁可废弃也不删除；
SQLAlchemy创建表时一般用以下语句，需要绑定引擎，实现和数据库的真正连接；创建好后会自动生成DDL文本；可以用该方法检验自己创建的类和数据库的表格是否一一对应；
Base管理的表（继承自它的类）都会被创建；

5）创建会话session
在一个会话中操作数据库，会话建立在连接上，连接被引擎管理；当第一次使用数据库时，从引擎维护的连接池中获取一个连接使用；Session类和engine有一个就行了；每一次数据库操作都是在一个会话中完成，将cursor的操作封装到会话中；
session对象线程不安全，所以不同线程应该创建不同的session对象；
提供了多线程使用的session类，是Threadlocal类；

完整创建代码如下

import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, Date, Enum, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
import enum
from sqlalchemy import and_, or_, not_
from sqlalchemy import func
​
​
# 创建连接
USER = 'xxx'
PWD = '123456'
HOST = '127.0.0.1'
PORT = 3306   
DB = 'test'
engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(
    USER, PWD, HOST, PORT, DB), echo=True)
​
# 基类
Base = declarative_base()  
​
# Mapper
class Student(Base):
    __tablename__ = 'student'  
​
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Integer)
​
    def __repr__(self):
        return '<{} id={} name={} age={}>'.format(self.__class__.__name__, self.id, self.name, self.age)
​
# Python中的枚举，一般可拓展
class GenderEnum(enum.Enum):  
    M = 'M'
    F = 'F'
​
class Employee(Base):
    __tablename__ = 'employees'
​
    # 一般属性名和字段名都保持一致，那么下面'emp_no'就可省略
    emp_no = Column('emp_no', 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(GenderEnum), nullable=False)
    hire_date = Column(Date, nullable=False)
​
    # 要写类名的字符串；这属于关系、不是字段
    departments=relationship('Dept_emp')
​
    def __repr__(self):
        return '<{} emp_no={} name="{} {}" gender={}> depts={}'.format(self.__class__.__name__, self.emp_no, self.first_name,self.last_name, self.gender.value, self.departments)
​
class Department(Base):
    __tablename__='departments'
​
    dept_no=Column(String(4),primary_key=True)
    dept_name=Column(String(40),nullable=False,unique=True)
​
    def __repr__(self):
        return '<Department number={} name={}>'.format(self.dept_no,self.dept_name)
​
class Dept_emp(Base):
    __tablename__ = 'dept_emp'
​
    emp_no = Column(Integer, ForeignKey('employees.emp_no'), primary_key=True)
    dept_no = Column(String(4), ForeignKey('departments.dept_no'), primary_key=True)
    from_date=Column(Date,nullable=False)
    to_date=Column(Date,nullable=False)
​
    def __repr__(self):
        return '<Dept_emp dno={} eno={}>'.format(self.dept_no,self.emp_no)
​
# 构建session，跟数据库操作只能用session
Session = sessionmaker(bind=engine)  # 构建Session会话类
session = Session()  # 实例化，得到session实例对象
​
def showresults(emps):
    for x in emps:
        print(x)

4、CRUD操作

1）增 add()
增加一个对象 add_all() 可迭代对象，元素是对象，常用add_all()

# 实例化
s=Student(name='Tom')
s.age=30
print(1,s)  # 此时打印的s里没有id
​
# 把数据instance送到数据库
session.add(s)  # 1个instance,这种写法不好
session.commit() # 提交以后，数据库发现没有id会自动分配一个id，此时打印s将带有id
​
try:
    # 如果没有这句，try内将不执行，因为发现s已存在、且s无改变
    s.age=50 
    session.add_all([s]) # 里面一个可迭代对象
    session.commit()
except Exception as e:
    print(2,e)
    session.rollback()

add_all()方法不会提交成功的，因为s提交成功后，s的主键就有了值，只要s没有修改过就会认为没有改动，当s变化了就可以提交修改了；
s主键没有值，就是新增；主键有值，就是找到主键对应的记录修改；
打开s.age=50，执行3次，数据库显示：

因为每次都会分配新id，新建一个age=30的对象，然后再覆盖；
可以看到先insert，再update；

简单查询
使用query()方法，返回一个Query对象；
query方法将实体类传入，返回类的可迭代对象，这时候并不查询，迭代它才会执行SQL来查询数据库，封装数据到指定类的实例；

get()方法使用主键查询，返回传入类的一个实例；

最好的做法是用一个容器接收查询的结果，比如:
l = list(students)
这样避免以后再次执行查询语句，反复和数据库要数据、浪费时间；

2）改
先查再改，再提交更改；
先select，再update

3）删除

5、状态
每一个实体，都有一个状态属性 _sa_instance_state，其类型是 sqlalchemy.orm.state.InstanceState;

可以使用 sqlalchemy.inspect(entity) 函数状态查看状态；

from sqlalchemy.orm.state import InstanceState
​
s=session.query(Student).get(2)
print(s)   # <Student id=2 name=jerry age=18>
print(sqlalchemy.inspect(s))  # <sqlalchemy.orm.state.InstanceState object at 0x1111ba4a8>
print(type(sqlalchemy.inspect(s)))  # <class 'sqlalchemy.orm.state.InstanceState'>

常见的状态值有transient（临时）、pending（预备）、persistent（持久）、deleted（删除）、detached（分离）；attached（附属、附加）

1）创建一个实体，状态是transient临时的；
2）一旦add()后从transient变成pending状态；
3）成功commit()后从pending状态变成persistent状态；
4）persistent状态的实体，修改后依然是persistent状态；删除修改等操作会先查状态是否是persistent；
5）persistent状态的实体删除后，flush后，没有commit的话就变成deleted状态，成功commit的话就变成detached分离状态，提交失败就还原成persistent状态；
6）flush方法，主动把改变应用到数据库中去；
7）删除、修改操作，需要对应一个真实的记录，所以要求实体对象是persistent状态；
8）一经初次add，就会attached=True，直至detached才变False；
9）只要写入到数据库，就会有key，直至detached其key依然存在；

6、复杂查询

1）实体类
创建实体类时，Column可以不用那么详细，毕竟不是用这个类create_all，只要能对应上数据库即可，最好保持一致；
关于枚举
一般from sqlalchemy import Enum 中的枚举类型Enum属于包装类，需要再用import enum创造一个Python中的枚举；然后把创造的类包装进去，才能使用；

2）查询 query
基本查询，注意要满足Python语法，filter函数后是条件，用的是==，!=，>，<

3）排序 order_by
多列排序的两种写法：连写，逗号隔开；

4）分页 limit

5）消费者方法
对查询到的数据进行消费，count()、all()、one()、first()、scalar() 方法
消费者方法调用后，Query对象（可迭代）就转换成了一个容器
常用count、all（省得自己建个容器）、first这几个消费者方法，并且通常是直接写在查询语句后面，那么就会立即触发执行，并返回消费值；

6）分组、聚合
one、scalar
scalar常常和one一起使用，拿到one结果序列的第一项

7）关联查询
在UML这种模型设计工具中，通常把一对多写成1：N，两端分别称为一端、多端；多对多写成N：N；
对于一对多，做法通常是在多端加外键；
对于多对多，常常会建立第三张中间表，用中间表描述多对多；

例如：员工、部门之间的关系有时是多对多；

设计第三张表时，常常把前两张表的主键引入、做联合主键；

在外键栏，可以看到从表对主表的外键约束：删除时级联删除、禁止更新；实际业务中一般不会删除；

写第三张表的实体类时，注意点如下：
ForeignKey里必须写字符串形式的表名和字段，不是类；可以同时定义外键约束，但不定义也没关系，数据库会做；
实际业务中很少用联合主键，往往会设立新字段做主键；

8）relationship
关系：从一张表找另一张表的数据
业务：查员工所在的部门，先拿到部门id，再从第二张表拿到部门信息；
重点在：从哪里向哪里看，A查B一般把relationship建在A处；由于是查员工的部门信息，故在员工表加入关系；
注意从业务分析在哪张表建立关系relationship；

只要不访问self.departments属性，就只是建立关系，不会查dept_emp这张表；
因为ORM本身效率没有直接SQL高，为节省资源都是懒惰方式；

总结：
1、开发中一般都使用ORM框架，这样就能使用对象操作表了，定义表映射的类，使用Column的描述器定义类属性，使用ForeignKey定义外键约束；
2、如果在一个对象中，想查看其他表对应的对象的内容，就要使用relationship来定义关系；