由于菜鸡水平无法写出高性能的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&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&amp;amp;amp;gt;1).update( {'name':MyTable.name+'5'},synchronize_session=False) #数字类型的更新 dbsession.query(MyTable).filter(MyTable.id&amp;amp;amp;gt;1).update( {'id':MyTable.id+222},synchronize_session=&amp;quot;evaluate&amp;quot;) #向数据库提交数据 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) &amp;gt; 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 &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关系。
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对象(即一行数据)。由于反向链接是可以出现多个结果的,因而其返回值是一个列表。
越来越看不懂大佬的文章了