Python下ORM框架之SQLAlchemy

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

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。
Python下ORM框架之SQLAlchemy
SQLAlchemy框架是建立在数据库API至上的,通过对象对应关系进行数据库操作。总来来说就是将类转化为表格,将对象转换为数据行。
由于SQLAlchemy自身无法直接操作数据库,需要通过第三方插件来实现比如pymysql,Dialect用于与API进行通信,从而可以达到使用不同的数据就调用不同的数据库API。

Code example:
1
2
3
4
5
6
7
8
9
10
11
MySQL-Python
     mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
pymysql
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来进行安装。

Python下ORM框架之SQLAlchemy
通过pip3 install sqlalchemy安装完毕后,即可使用SQLAlchemy。

0x01 SQLAlchemy的基本操作

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

a.基本操作

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

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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__中。

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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

Code example:
1
2
3
4
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
dbsession = Session()

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

b.增加数据

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
#创造Session类并创建Session对象
Session = sessionmaker(bind=engine)
dbsession = Session()
# 创建一行数据
stu1 = MyTable(name = 'xzymoe',email = '[email protected]')
#添加一行数据
dbsession.add(stu1)
#向数据库提交数据
dbsession.commit()

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

Code example:
1
2
3
4
5
dbsession.add_all({MyTable(name = 'xzymoe1',email = '[email protected]'),
                   MyTable(name = 'xzymoe2',email = '[email protected]')})
#向数据库提交数据
dbsession.commit()

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

c.删除数据

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

Code example:
1
2
3
4
5
#删除id=3的数据
dbsession.query(MyTable).filter(MyTable.id==4).delete()
#向数据库提交数据
dbsession.commit()

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

d.更新数据

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

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#更新为指定数据
dbsession.query(MyTable).filter(MyTable.id == 3).update(
    {'name':'xzymoeupdate'})
#字符串类型连加更新
dbsession.query(MyTable).filter(MyTable.id&amp;amp;amp;amp;gt;1).update(
    {'name':MyTable.name+'5'},synchronize_session=False)
#数字类型的更新
dbsession.query(MyTable).filter(MyTable.id&amp;amp;amp;amp;gt;1).update(
    {'id':MyTable.id+222},synchronize_session=&amp;amp;quot;evaluate&amp;amp;quot;)
#向数据库提交数据
dbsession.commit()

e.查询数据

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

Code example:
1
2
3
4
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关键字的写法有个下划线~

Code example:
1
2
3
4
5
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的操作。

Code example:
1
2
3
4
5
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.通配符

Code example:
1
2
result = dbsession.query(MyTable).filter(MyTable.name.like('xzy_')).all()
result = dbsession.query(MyTable).filter(MyTable.name.like('xzy%')).all()

3.排序

Code example:
1
2
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.分组

Code example:
1
2
3
4
5
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) &amp;amp;gt; 1).all()
print(result)

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

Code example:
1
2
3
4
#类似于笛卡尔发卡机后筛选 约等于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函数来完成连表操作。

Code example:
1
2
3
4
5
#默认情况下为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的进阶操作

主要有子查询和临时表!

Code example:
1
2
3
4
#子查询操作
#select * from (select * from tb1) as B
query1 = dbsession.query(MyTable).filter(MyTable.id &amp;amp;gt; 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关系。

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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参数的设置。

Code example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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对象(即一行数据)。由于反向链接是可以出现多个结果的,因而其返回值是一个列表。

发表评论