Python下ORM框架之SQLAlchemy

由于菜鸡水平无法写出高性能的SQL语句，从而尝试使用对象映射关系（ORM）框架来实现“编写”SQL语句，而SQLAlchemy就是在Python下使用较为广泛的ORM框架之一。

0x00 关于SQLAlchemy

在介绍SQLAlchemy之前先说一下ORM框架是什么。简单的说ORM框架就是提供了一套简单的规则，然后将写出来的规则代码转换为高效率的SQL语句。

ORM框架又大概分为DB first和Code first。

• DB first：手动创建数据库和表——ORM——自动创建出类
• Code first：手动创建代码和数据库——ORM——自动创建表

SQLAlchemy就是属于Code first的ORM框架，而著名的Django既可以DB first又可以Code first。

至于SQLAlchemy是什么就不一一介绍了，详细的介绍可以翻看Wikipedia。

SQLAlchemy框架是建立在数据库API至上的，通过对象对应关系进行数据库操作。总来来说就是将类转化为表格，将对象转换为数据行。

由于SQLAlchemy自身无法直接操作数据库，需要通过第三方插件来实现比如pymysql，Dialect用于与API进行通信，从而可以达到使用不同的数据就调用不同的数据库API。

MySQL-Python
     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql
     mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector
     mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>     

cx_Oracle
     oracle+cx_oracle://user:pass@host:port/dbname[?key=value&amp;key=value...]

#更多Dialects写法

https://docs.sqlalchemy.org/en/latest/dialects/index.html

由于SQLAlchemy并不是Python自带的模块，因而需要通过pip来进行安装。

通过pip3 install sqlalchemy安装完毕后，即可使用SQLAlchemy。

0x01 SQLAlchemy的基本操作

1.创建表或映射表对应关系

a.基本操作

由于SQLALchemy属于Code First类型的ORM，所以首先需要创建数据库，之后可以通过ORM来建立表或者用ORM映射到已经存在的表中。

a.使用SQLAlchemy创建表及删除表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String,create_engine

Base = declarative_base()
engine = create_engine('mysql+pymysql://root:@localhost:3306/salchemy?charset=utf8',max_overflow = 5)    #连接池=5

#继承Base类
class MyTable(Base):
    #表名
    __tablename__ = 'student'

    #表结构
    id = Column(INTEGER,primary_key=True,autoincrement=True)
    name = Column(String(16))
    email = Column(String(32))

#创建表
Base.metadata.create_all(engine)

#删除表
Base.metadata.drop_all(engine)

首先要通过declarative_base()创建一个Base类。将需要创建的表格（类）继承与Base类下。这样才能让SQLAlchemy知道要创建或者映射哪一个类。

其次创建一个类，继承于Base。定义表名与结构。

最后通过使用Base的方法来创建或者删除表格。

b.进阶操作

在创建表格的时候还可以设置诸如禁止为空、默认值、索引、联合索引、外链等。将其参数放置与Column对象中和__tableargs__中。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String,create_engine,UniqueConstraint,ForeignKey,Index

Base = declarative_base()
engine = create_engine('mysql+pymysql://root:@localhost:3306/salchemy?charset=utf8',max_overflow = 5)

#继承Base类
class MyTable(Base):
    #表名
    __tablename__ = 'student'

    #表结构
    id = Column(INTEGER,primary_key=True,autoincrement=True)
    #禁止为空,若为空默认为xzymoe（例子不是很恰当）
    name = Column(String(16),nullable=False,default='xzymoe',)
    #创建了唯一索引,索引名称为email（自动）
    email = Column(String(32),unique=True)
    #设置属性列并设置外键
    type = Column(INTEGER,ForeignKey('gender.id'))

    #表格其他属性（注意普通索引和联合唯一索引给索引命名的地方）
    #普通索引也可以在上面使用index=True设置
    __tableargs__ = (
        UniqueConstraint(name,email,'ix_name_email'),
        Index('ix_name',name),
    )

class StudentType(Base):
    __tablename__ = 'gender'
    id = Column(INTEGER,autoincrement=True,primary_key=True)
    type = Column(String(2))

# #创建表
Base.metadata.create_all(engine)

c.映射已存在的表格

如果表已经存在就不用谢创建表的函数即可，定义出类就完成了表格与类的映射操作。之后可以使用sessionmaker创造的类的对象来进行操作数据表。

2.SQLALchemy的增删改查操作

在对数据库进行增删改查操作之前必须通过Session创建于数据库连接。使用sessionmaker创建一个Session类后，并实例化Session类之后通过该对象进行数据表操作。

a.创建Session

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
dbsession = Session()

创建好Session的对象后就可以通过其与数据库进行通信了。

b.增加数据

#创造Session类并创建Session对象
Session = sessionmaker(bind=engine)
dbsession = Session()

# 创建一行数据
stu1 = MyTable(name = 'xzymoe',email = 'xzymoe@qmail111.com')

#添加一行数据
dbsession.add(stu1)

#向数据库提交数据
dbsession.commit()

通过add()方法可以想数据库添加一行数据，不过暂时是保存在内存中的，需要commit()向数据库提交数据后才会保存到数据库。

dbsession.add_all({MyTable(name = 'xzymoe1',email = 'xzymoe@qmai1.com'),
                   MyTable(name = 'xzymoe2',email = 'xzymoe1@gmai2.com')})

#向数据库提交数据
dbsession.commit()

使用add_all()方法可以向数据库中一次性提交多个数据行，多行数据放置到一个集合中，当然列表也可以。尝试了一下使用列表List也可以提交成功数据，而API的注释是”””Add the given collection of instances to this Session.”””。不太清楚collection是啥子意思，反正Java里是集合的意思，不过Python中集合又是Set。对于List来说更适合操作数据，所以因需要而使用吧！源码上只要是个迭代对象即可哦！

c.删除数据

噗嗤~尴尬的东西是删除数据和修改数据都要筛选出来才可以操作啊，不过查询是Alchemy里最为复杂的东西。就先简单介绍一下查询及删除吧！通过query()指定查找表格的字段，在通过filter()来筛选出条件。

#删除id=3的数据
dbsession.query(MyTable).filter(MyTable.id==4).delete()

#向数据库提交数据
dbsession.commit()

需要注意的是，这个时候表已经是类名了，而不是__tablename__，__tablename__我理解的作用仅仅是创建表的时候表的名字，操作的表的时候因为都是通过ORM映射的，所以参数要写的是类名。

d.更新数据

数据的更新也是通过查询后更新为新的数据，使用update()方法来实现，通过字典对应关系来对数据进行更新。

#更新为指定数据
dbsession.query(MyTable).filter(MyTable.id == 3).update(
    {'name':'xzymoeupdate'})

#字符串类型连加更新
dbsession.query(MyTable).filter(MyTable.id>1).update(
    {'name':MyTable.name+'5'},synchronize_session=False)

#数字类型的更新
dbsession.query(MyTable).filter(MyTable.id>1).update(
    {'id':MyTable.id+222},synchronize_session="evaluate")

#向数据库提交数据
dbsession.commit()

e.查询数据

1.条件查询

条件的筛选可以使用关键字filter及filter_by。其区别是在传参的时候，filter_by传入了一个self，因而只用写表格字段即可；all()的作用是将所有满足条件的行返回，并将其装入一个list对象中。

result = dbsession.query(MyTable).filter(MyTable.id == 1).all()
result = dbsession.query(MyTable).filter_by(id = 1).all()
for row in result:
    print(row.id,row.name,row.email,row.type)

在范围搜索中也可以使用between和in关键字。不过需要注意in关键字的写法有个下划线~

result = dbsession.query(MyTable).filter(MyTable.id.between(1,3)).all()
result = dbsession.query(MyTable).filter(MyTable.id.in_([1,3,5])).all()
#否定查询between和in使用~
result = dbsession.query(MyTable).filter(~MyTable.id.between(1,3)).all()
result = dbsession.query(MyTable).filter(~MyTable.id.in_([1,3,5])).all()

与或查询，默认情况下，filter()中可以包含多个条件，其之间的关系为and关系，当然可以通过函数来完成and和or的操作。

from sqlalchemy import and_,or_

result = dbsession.query(MyTable).filter(MyTable.id == 1,MyTable.name == 'xzymoe').all()
result = dbsession.query(MyTable).filter(and_(MyTable.id == 1,MyTable.name == 'xzymoe')).all()
result = dbsession.query(MyTable).filter(or_(MyTable.id == 1,MyTable.name == 'xzymoe')).all()

2.通配符

result = dbsession.query(MyTable).filter(MyTable.name.like('xzy_')).all()
result = dbsession.query(MyTable).filter(MyTable.name.like('xzy%')).all()

3.排序

result = dbsession.query(MyTable).order_by(MyTable.id.asc()).all()
result = dbsession.query(MyTable).filter(MyTable.name.like('xzy%')).order_by(MyTable.id.desc()).all()

4.分组

from sqlalchemy.sql import func

result = dbsession.query(MyTable.type,func.count(MyTable.name)).group_by(MyTable.type).all()
result = dbsession.query(MyTable.type,func.count(MyTable.name)).group_by(MyTable.type).having(func.min(MyTable.id) > 1).all()
print(result)

分组中中进行条件筛选使用的关键字为having。不过纳闷的是，不能直接使用MyTable.id > 1，而必须使用func的功能才可以筛选。

5.连表

#类似于笛卡尔发卡机后筛选 约等于inner join
result = dbsession.query(MyTable,StudentType).filter(MyTable.id == StudentType.id).all()
for row in result:
    print(row[0].id,row[0].name,row[1].type)

当然还是推荐使用join函数来完成连表操作。

#默认情况下为inner join
result = dbsession.query(MyTable).join(StudentType).all()

#使用left join
result = dbsession.query(MyTable).join(StudentType,isouter=True).all()

注意关键字传参的isouter，中文是是不是外部链接？？不是inner？？哈哈，方便记忆。至于使用right join的话，只需将query和join函数中的类交换位置即可。

6.组合union及union_all

union和union_all()可以将两个表结构相似的表的查询结果相互合并，union则会去重，而union_all()则保留重复项，暂时没有好的例子，所以就跳过了！！

0x02 SQLAlchemy的进阶操作

主要有子查询和临时表！

#子查询操作
#select * from (select * from tb1) as B
query1 = dbsession.query(MyTable).filter(MyTable.id > 1).subquery() #设置为临时表
result = dbsession.query(query1).filter(query1.c.id == 225).all()   #需要使用c来调用字段

注意的是临时表的字段选取要加上关键字c才可以获得！！！

还有一个嵌套查询，关键字as_scalar()，还没研究透彻。。。先跳过~~~~( >﹏<。)～呜呜呜……

0x03 SQLAlchemy之relationship

relationship函数是sqlalchemy对关系之间提供的一种便利的调用方式, backref参数则对关系提供反向引用的声明。如上面的2张表，比如给你姓名要查询类型的话，在不连表的情况下需要先查询出其type的结果，在将结果去StudentType类型表里进行比对才出结果。而如果使用relationship的话，那么一步即可完成。前提2张表必须有外链，并且有relationship关系。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String,create_engine,ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship,backref

Base = declarative_base()
engine = create_engine('mysql+pymysql://root:@localhost:3306/salchemy?charset=utf8',max_overflow = 5)

#继承Base类
class MyTable(Base):
    #表名
    __tablename__ = 'student'

    #表结构
    id = Column(INTEGER,primary_key=True,autoincrement=True)
    name = Column(String(16),nullable=False,default='xzymoe',)
    email = Column(String(32),unique=True)

    #设置属性列并设置外键
    type = Column(INTEGER,ForeignKey('gender.id'))

    #设置relationship
    stu_type = relationship('StudentType')

class StudentType(Base):
    __tablename__ = 'gender'
    id = Column(INTEGER,autoincrement=True,primary_key=True)
    type_name = Column(String(2))

# Base.metadata.create_all(engine)

#创造Session类并创建Session对象
Session = sessionmaker(bind=engine)
dbsession = Session()

result = dbsession.query(MyTable).filter(MyTable.id == 1).all()
for row in result:
    print(row.stu_type.type_name)

#向数据库提交数据
dbsession.commit()

通过relationship后，在MyTable表中相当于自动多了一列为stu_type的列，该列的值为一个StudentType的一行（一个对象），因此这样就可以直接通过对MyTable表进行操作出需要的结果了。
当然也可以通过反向的操作，即通过StudentType来查询MyTable的内容，但是需要通过在MyTable中进行backref参数的设置。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,INTEGER,String,create_engine,ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship,backref

Base = declarative_base()
engine = create_engine('mysql+pymysql://root:@localhost:3306/salchemy?charset=utf8',max_overflow = 5)

#继承Base类
class MyTable(Base):
    #表名
    __tablename__ = 'student'

    #表结构
    id = Column(INTEGER,primary_key=True,autoincrement=True)
    name = Column(String(16),nullable=False,default='xzymoe',)
    email = Column(String(32),unique=True)

    #设置属性列并设置外键
    type = Column(INTEGER,ForeignKey('gender.id'))

    #设置relationship
    stu_type = relationship('StudentType',backref = backref('mytable')) #可以直接结尾backref = 'mytable'

class StudentType(Base):
    __tablename__ = 'gender'
    id = Column(INTEGER,autoincrement=True,primary_key=True)
    type_name = Column(String(2))

# Base.metadata.create_all(engine)

#创造Session类并创建Session对象
Session = sessionmaker(bind=engine)
dbsession = Session()

result = dbsession.query(StudentType).filter(StudentType.id == 1).all()
for row in result:
    print(row.mytable[0].name)  #注意其返回值是个列表

#向数据库提交数据
dbsession.commit()

通过在MyTable的relationship中设置了backref后，那么StudentType表中就类似于多出一类mytable列，其值为一个MyTable对象（即一行数据）。由于反向链接是可以出现多个结果的，因而其返回值是一个列表。