python/SQLAchemy
python/SQLAchemy
SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
安装:
1
|
pip3 install SQLAlchemy |
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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...] 更多详见:http://docs.sqlalchemy.org/en/latest/dialects/ index .html |
ORM功能使用:
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象,对象转换成SQL,执行SQL。
创建表单:
1 from sqlalchemy.ext.declarative import declarative_base 2 from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index 3 from sqlalchemy.orm import sessionmaker,relationship 4 from sqlalchemy import create_engine 5 Base=declarative_base() 6 7 #定义一个类(类==表,对象==行) 8 class UserType(Base): 9 __tablename__='usertype' ##设置表名 10 id=Column(Integer,primary_key=True,autoincrement=True) ##设置表行 11 title=Column(String(32),nullable=True,index=True) ##设置表行 12 13 14 class Useru(Base): 15 __tablename__='useru' 16 id=Column(Integer,primary_key=True,autoincrement=True) 17 name=Column(String(32),nullable=True,index=True) 18 email=Column(String(16),unique=True) 19 user_type_id=Column(Integer,ForeignKey('usertype.id')) 20 user_type=relationship('UserType',backref='xxoo') 21 ##设置关联语句 relationship找指定的表名(UserType)创建列名(xxoo)实现双向关联,而在自身的表中创建出一列。 22 23 engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/lian?charset=utf8",max_overflow=5) 24 ##设置连接引擎 25 26 # 找到所有继承了Base得类,类-SQL语句在数据库创建表 27 Base.metadata.create_all(engine) 28 29 # 找到所有继承了Base得类,类-SQL语句在数据库删除表 30 # Base.metadata.drop_all(engine)
数据行操作:
1 # 操作数据行: 2 # # 去引擎中获取一个连接 3 Session=sessionmaker(bind=engine) 4 session=Session() 5 # session.commit() 6 # session.close() 7 8 9 ########表增加####### 10 # obj=Useru(name='话梅',email='92242@qq.com',user_type_id=2) 要插入每行的内容 11 # session.add(obj) 通过对象.add(要插入的变量名)进行添加 12 # session.commit() 通过对象.commit()进行提交 13 #------单个增加------- 14 15 #======多个增加======= 16 # obj=[ 17 # Useru(name='alex1',email='12321@qq.com'), 18 # Useru(name='alex2',email='12322@qq.com'), 19 # Useru(name='alex3',email='12323@qq.com'), 20 # Useru(name='alex4',email='12324@qq.com'), 21 # Useru(name='alex5',email='12325@qq.com') 22 # 23 # ] 24 # 25 # session.add_all(obj) 26 # session.commit() 27 28 ########查看######### 29 # user_list=session.query(Useru) 30 # for row in user_list: 31 # print(row.name,row.id,row.user_type_id) 32 33 34 #要进行表的修改和删除首先要查看表的内容 35 ########表修改######## 36 # session.query(UserType).filter(UserType.id==1).update({'title':'黄金用户'}) 37 # session.commit() 38 # session.query(UserType).filter(UserType.id==1).update({UserType.title:UserType.title+"x"},synchronize_session=False) 39 # session.commit() 40 # session.query(Useru).filter(Useru.id > 0).update({Useru.name: Useru.name + "x"},synchronize_session=False) 41 # session.query(Useru).filter(Useru.id>2).update({'num':Users.num+1},synchronize_session='evaluate')
# session.commit() 42 ########表删除####### 43 # session.query(UserType).filter(UserType.id==1).delete() 44 # session.commit() 45 46 47 # type_list=session.query(UserType) 48 # for row in type_list: 49 # print(row.id,row.title) 50 # for j in row.xxoo: 51 # print('-----',j.name)
其他操作:
1 ##其他操作## 2 # 条件: 3 # ret=session.query(Useru).filter_by(name='alex').all() ##filter_by 可以接收**args类型 4 # ret=session.query(Useru).filter(Useru.id>1,Useru.name=='aelx').all() 5 # ret=session.query(Useru).filter(Useru.id.between(1,3),Useru.name=='alex').all() 6 # ret=session.query(Useru).filter(Useru.id.in_([1,3,5])).all() ##in_就是相当于in 7 # ret=session.query(Useru).filter(~Useru.id.in_([1,3,5])).all() ##~ 相当于非的意思 8 # ret=session.query(Useru).filter(Useru.id.in_(session.query(Useru.id).filter_by(name='alex'))).all() ##多层套嵌条件查询 9 # from sqlalchemy import and_,or_ 10 # ret =session.query(Useru).filter(and_(Useru.id>3,Useru.name=='alex')).all() ##and_ 相当于and 11 # ret=session.query(Useru).filter(or_(Useru,id<2,Useru.name=='alex')).all() ##or_ 相当于or 12 # ret=session.query(Useru).filter( 13 # or_(Useru.id <2,and_(Useru.name=='alex',Useru.id >3),Useru.extra !='') 14 # )##or_括号里都是or的关系,但是在括号里有一个是and 的关系 15 # 16 # 通配符: 17 # ret=session.query(Useru).filter(Useru.name.like('e%')).all() #查找name以e开头的 18 # ret=session.query(Useru).filter(~Useru.name.like('e%')).all() #查找name不是以e开头的 19 # 20 # 限制: 21 # ret=session.query(Useru)[1:2] #通过切片的实现分页的功能 22 23 24 # 排序: 25 # ret=session.query(Useru).order_by(Useru.name.desc()).all() #以降序进行排序 26 # ret=session.query(Useru).order_by(Useru.name.desc(),Useru.id.asc()).all() #开始以降序进行排序,如果过有重复的就按照id以升序排序 27 28 29 # 分组: 30 from sqlalchemy.sql import func 31 # ret =session.query(Useru).group_by(Useru.name).all() #以名字进行分组 32 # ret=session.query( 33 # func.max(Useru.id), 34 # func.sum(Useru.id), 35 # func.min(Useru.id) 36 # ).group_by(Useru.name).all() #以名字进行分组,然后操作合并后的列 37 38 # ret = session.query( 39 # func.max(Useru.id), 40 # func.sum(Useru.id), 41 # func.min(Useru.id)).group_by(Useru.name).having(func.min(Useru.id) >2).all() #有having二次筛选的 42 43 # 44 # print(ret) 45 46 # 连表: 47 # ret=session.query(Useru,UserType).filter(UserType.id==Useru.id).all() #进行连表操作 48 # ret =session.query(Useru).join(UserType).all() # 这样是inner join (不显示空) 49 # ret = session.query(Useru).join(UserType,isouter=True).all() #这样是left join (显示空) 50 51 52 #组合: 53 # q1=session.query(Useru.name).filter(Useru.id >2) 54 # q2=session.query(UserType.id).filter(UserType.id<1) 55 # ret=q1.union(q2).all() #这是去重组合 56 # 57 # q1 = session.query(Useru.name).filter(Useru.id > 2) 58 # q2 = session.query(UserType.id).filter(UserType.id < 1) 59 # ret = q1.union_all(q2).all() #这是不去 60 # 61 # 62 # 子查询: 63 # q3=session.query(Useru.name).filter(Useru.id >2).subquery() 64 # restt=session.query(q3).all() 65 # print(restt)
创建一对多等:
1 # 一对多 2 class Favor(Base): 3 __tablename__ = 'favor' 4 nid = Column(Integer, primary_key=True) 5 caption = Column(String(50), default='red', unique=True) 6 7 8 class Person(Base): 9 __tablename__ = 'person' 10 nid = Column(Integer, primary_key=True) 11 name = Column(String(32), index=True, nullable=True) 12 favor_id = Column(Integer, ForeignKey("favor.nid")) 13 14 15 # 多对多 16 class Group(Base): 17 __tablename__ = 'group' 18 id = Column(Integer, primary_key=True) 19 name = Column(String(64), unique=True, nullable=False) 20 port = Column(Integer, default=22) 21 22 23 class Server(Base): 24 __tablename__ = 'server' 25 26 id = Column(Integer, primary_key=True, autoincrement=True) 27 hostname = Column(String(64), unique=True, nullable=False) 28 29 30 class ServerToGroup(Base): 31 __tablename__ = 'servertogroup' 32 nid = Column(Integer, primary_key=True, autoincrement=True) 33 server_id = Column(Integer, ForeignKey('server.id')) 34 group_id = Column(Integer, ForeignKey('group.id'))
简述ORM原理:
1 class User: 2 def __init__(self): 3 self.id=id 4 self.name=name 5 self.email=email 6 def order_by(): 7 pass 8 9 ogj=User('1.1.1.1','alex','34434@qq.com') 10 -------------------------------- 11 obj.__dict__={'id':'1.1.1.1','name':'alex','email':'34434@qq.com'} 12 -------------------------------------------------------------------------- 13 ORM的作用就是把类和对象方式解析成SQL语句(不进行连接,连接借助第三方模块) 14 code first 通过手动写SQL语句-------->类 15 db first 通过手动写类--------------->SQL语句