SQLAlchemy的基本用法

ORM

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

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

举例
有表student，字段为id int，name varchar，age int 映射到Python如下:

class Student:
    id = ?   某类型字段    
    name = ? 某类型字段    
    age = ?  某类型字段

最终得到实例
class Student:    
    def __init__(self):
        self.id = ?
        self.name = ?
        self.age = ?

---

SQLAlchemy

SQLAlchemy 是一个ORM框架

• 安装:$ pip install sqlalchemy

开发:QLAlchemy内部使用了连接池.

创建连接

数据库连接

import sqlalchemy

IP ='172.198.11.11'
USERNAME = 'yu'
PASSWORD = '123'
DATABASE = 'test'
PORT = 3306

engine = sqlalchemy.create_engine("mysql+pymysql://{}:{}@{}:{}/{}".\
        format(USERNAME,PASSWORD,IP,PORT,DATABASE), echo=True)
        
lazy connecting：懒连接。创建引擎并不会马上连接数据库，直到让数据库执行任务时才连接。

创建基类

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base() # ORM Mapping 创建基类,便于实体类继承

创建实例类

class Empl(Base):

    __tablename__ = "empl"  #映射,对应数据库中的表empl

    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(
            __class__.__name__,self.id,self.name,self.age
        )

• __tablename__指定表名(要注意大小写)
• Column类指定对应的字段，必须指定

创建表

# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)

加载表

from sqlalchemy import MetaData,Table

md = MetaData(bind=engine)
class Employees(Base):
    __table__ = Table("employees",md,autoload=True)  # 自动加载表

    def __repr__(self):   #__repr__ 函数可以通过打印emp来查看SQL语句来设置,与数据库中的表字段的顺序对应,方便打印查看
        return("{},emp_no={},birth_date={},first_name={},last_name={},gender={},hire_date={}".format(self.__class__.__name__,\
                self.emp_no,self.birth_date,self.first_name,self.last_name,self.gender,self.hire_date))
                
emp = session.query(Employees)
print(emp)
---------------------------------
SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.gender AS employees_gender, employees.hire_date AS employees_hire_date 
FROM employees

实例化

empl = Empl(id=1,name = 'jake')  #实例化
empl.name = 'tom'   #实例改属性,而name是类属性,(用到描述器)
empl.age = 20

创建会话session

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

Session = sessionmaker(bind=engine)   #创建会话
session = Session() #线程不安全

GURD 操作,增删改查

• add()：增加一个对象
• add_all()：可迭代对象，元素是对象

Session = sessionmaker(bind=engine)   #创建会话
session = Session() #线程不安全

empl = Empl()  #创建实例
empl.name = 'jurry'
empl.age = 23

session.add(empl)   # 增加一个对象
session.commit()

try:
    empl.name = 'jake'
    session.add_all([empl])  # add_all()：可迭代对象，元素是对象
    session.commit()
except Exception as er:
    print(er)
    session.rollback()

主键没有值，就是新增；主键有值，就是找到主键对应的记录修改

简单查询

使用:query()方法，返回一个Query对象
em = session.query(Empl).filter(Employee.id > 10)

print(len(list(empl)))      #查到的为表Empl的总行数
em = session.query(Empl)  #query查询,empl 为返回的结果集
print(em)
em = session.query(Empl).get(1)    # 通过主键查询
print(em)
-------------------------------------
16
SELECT empl.id AS empl_id, empl.name AS empl_name, empl.age AS empl_age
FROM empl
Empl,id=1,name=jurry,age=10

query方法将实体类传入，返回类的对象可迭代对象，这时候并不查询。迭代它就执行SQL来查询数据库，封装数据到指定类的实例。get方法使用主键查询，返回一条传入类的一个实例。

改

先查询,后更改,再提交 (实质是更改实例属性)

em = session.query(Empl).get(2)  #查主键为2的实例
em.name = 'xw'
em.age = 18
session.add(em)
session.commit()

状态

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

状态	说明
transient(暂时的)	实体类尚未加入到session中,同时并没有保存到数据库中
pending(挂起的)	transient的实体被add()到session中,状态切换到pending,但是它还没有flush到数据库中
persistent(持久的)	session中的实体对象对应着数据库中的真实记录,pending状态在提交成功后可以变成persistent状态,或者查询成功返回实体也是persistent状态
deleted(删除的)	实体被删除且已经flush但未commit完成,事务提交成功了实体变成detached,事务失败则返回persistent状态
detached(分离的)	删除成功的实体进入这个状态

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

删除

try:
    em = session.query(Empl).get(2)   #先查询
    session.delete(em)   # 后删除
    session.flush()      # 再flush
    session.commit()     # 再commit
except Exception as e:
    session.rollback()
    
sqlalchemy.orm.make_transient(emp)  #此方法删除后又可以找回来
session.add(emp)
session.commit()
    

复杂查询(AND条件,OR条件,NOT条件,IN条件,Like条件),用到了过滤器

与或非的运算符&、|、~，一定要在表达式上加上括号(加上括号安全)

# AND条件
from sqlalchemy import or_,and_,not_
# ems = session.query(Empl).filter(Empl.id>10 and Empl.age==25)  此方法不行
ems = session.query(Empl).filter(Empl.id>10).filter(Empl.age==25)  #下面四种方法都可以
ems = session.query(Empl).filter((Empl.id<10)&(Empl.age==25))
ems = session.query(Empl).filter(Empl.id<10,Empl.age==25)
ems = session.query(Empl).filter(and_(Empl.id<10,Empl.age==25))

# or条件
ems = session.query(Empl).filter(or_(Empl.id>12,Empl.id<3))
show(ems)
ems = session.query(Empl).filter((Empl.id>12)|(Empl.id<3))
show(ems)

# NOT条件
ems = session.query(Empl).filter(not_(Empl.id<10))   # not_ 是not加下划线
ems = session.query(Empl).filter(~(Empl.id<10))
show(ems)

# in 条件
id = [2,3,5]
ems = session.query(Empl).filter(Empl.id.in_(id))    # in_是in加下划线
ems = session.query(Empl).filter(Empl.id.notin_(id)) # notin_ 是notin加下划线

# like 条件
ems = session.query(Empl).filter(Empl.name.like('d%'))
ems = session.query(Empl).filter(Empl.name.notlike('d%')) 

排序

ems = session.query(Empl).filter(Empl.id>5).order_by(Empl.id) # 默认是升序 asc()
ems = session.query(Empl).filter(Empl.id>5).order_by(Empl.id.desc()) # 降序 desc()


# 多列排序
emp = session.query(Employees).filter(Employees.emp_no > 10010).order_by(Employees.last_name).order_by(Employees.emp_no.desc())
show(emp)

分页 (limit)

emp = session.query(Employees).limit(3)
show(emp)
emp = session.query(Employees).limit(3).offset(6)
show(emp)
-------------------------------------------
Employees,hire_date=1986-06-26,gender=M,first_name=Georgi,emp_no=10001,last_name=Facello,birth_date=1953-09-02
Employees,hire_date=1985-11-21,gender=F,first_name=Bezalel,emp_no=10002,last_name=Simmel,birth_date=1964-06-02
Employees,hire_date=1986-08-28,gender=M,first_name=Parto,emp_no=10003,last_name=Bamford,birth_date=1959-12-03
~~~~~~~~~~~~~~~~~~~~
Employees,hire_date=1989-02-10,gender=F,first_name=Tzvetan,emp_no=10007,last_name=Zielinski,birth_date=1957-05-23
Employees,hire_date=1994-09-15,gender=M,first_name=Saniya,emp_no=10008,last_name=Kalloufi,birth_date=1958-02-19
Employees,hire_date=1985-02-18,gender=F,first_name=Sumant,emp_no=10009,last_name=Peac,birth_date=1952-04-19

消费者方法

消费者方法调用后，Query对象（可迭代）就转换成了一个容器

emp = session.query(Employees)
print(len(list(emp)))
print(emp.count())

print(emp.all())
print(list(emp))
print(emp.first())

emp = session.query(Employees).get(10001)
session.delete(emp)
session.commit()

first方法本质上就是limit语句

聚合,分组(分组其实就是按字段查询)

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())

query = session.query(Employees.gender,func.count(Employees.emp_no)).group_by(Employees.gender.desc()).all()   分组(分组其实就是按字段查询)

for i,j in query:
    print(i,j)
----------------------------------------------
[(19,)]
(19,)
<bound method Query.one of <sqlalchemy.orm.query.Query object at 0x000001B8CB6FCCC0>>
19
10020
10001
10010.9474
F 7
M 12

需求
查询10010员工的所在部门的编号和员工信息
1,使用隐式内连接(没有join)

result = session.query(Employees,Dept_emp).filter(Employees.emp_no==Dept_emp.emp_no).filter(Employees.emp_no==10010).all()
for i in result:
    print(i)
-----------------------------------------------------------
(emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24, dept_no=d004)
(emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24, dept_no=d006)

2,使用join连接

results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == 10010).all()
-----------------------------------------------------------
[emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24]
#返回都只有一行数据

• 返回都只有一行数据，为什么？
• 它们生成的SQL语句是一样的，执行该SQL语句返回确实是2行记录，可以Python中的返回值列表中只有一个元素？原因在于query(Employee)这个只能返回一个实体对象中去，为了解决这个问题，需要修改实体类Employee，增加属性用来存放部门信息
  sqlalchemy.orm.relationship(实体类名字符串)

from sqlalchemy.orm import relationship     # 导入模块
md = MetaData(bind=engine)
class Employees(Base):
    __table__ = Table("employees",md,autoload=True)
    department =relationship('Dept_emp')       # 增加关系属性         

result = session.query(Employees).join(Dept_emp).filter(Employees.emp_no == 10010).all()   # 运行过程中会生成等值条件,效率减低
print(result)
result= session.query(Employees).join(Dept_emp).filter(Employees.emp_no == Dept_emp.emp_no).filter(Employees.emp_no == 10010).all()
print(result)
results = session.query(Employees).join(Dept_emp, Employees.emp_no == Dept_emp.emp_no).filter(Employees.emp_no == 10010).all()
print(result)
----------------------------------------------------------------------------------------------------------------------
[emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24,department=[dept_no=d004, dept_no=d006]]
[emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24,department=[dept_no=d004, dept_no=d006]]
[emp_no=10010,birth_date=1963-06-01,first_name=Duangkaew,last_name=Piveteau,gender=F,hire_date=1989-08-24,department=[dept_no=d004, dept_no=d006]]

• 第一种方法join(Dept_emp)中没有等值条件，会自动生成一个等值条件，如果后面有filter，哪怕是filter(Employee.emp_no == Dept_emp.emp_no)，这个条件会在where中出现。第一种这种自动增加join的等值条件的方式不推荐.
• 第二种方法在join中增加等值条件，阻止了自动的等值条件的生成。这种方式推荐
• 第三种方法就是第二种

总结

---

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