• ORM框架之SQLAlchemy


    SQLAlchemyPython编程语言下的一款开源软件。提供了SQL工具包及对象关系映射(ORM:Object Relational Mappers)工具。SQLAlchemy“采用简单的Python语言,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型”。SQLAlchemy的理念是,SQL数据库的量级和性能重要于对象集合;而对象集合的抽象又重要于表和行。因此,SQLAlchemy采用了类似于JavaHibernate的数据映射模型,而不是其他ORM框架采用的Active Record模型。

    1.单数据表

      1.1单数据表创建

     1 #pip install SQLAlchemy模块
     2 
     3 #(1)创建对象基类------类似django中的Model
     4 from sqlalchemy.ext.declarative import declarative_base
     5 Base=declarative_base()
     6 
     7 #(2)定义类对象---表
     8 from sqlalchemy import Column,Integer,String
     9 class Student(Base):#继承基类
    10     __tablename__='student'#表名
    11     id=Column(Integer,primary_key=True,autoincrement=True)#类属性---表字段
    12     name=Column(String(32),nullable=False,index=True,unique=True)
    13 
    14 
    15 #(3)创建数据库引擎(依赖pymysql模块驱动)
    16 from sqlalchemy.engine import create_engine
    17 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")#源码有配置说明:'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
    18 
    19 
    20 # (4)创建表
    21 Base.metadata.create_all(engine)#创建基于Base基类的engine数据库引擎中的所有表
    22 # Base.metadata.drop_all(engine)#删除基于Base基类的engine数据库引擎中的所有表
    create_table.py

      1.2单数据表的增删改查

      1.2.1增 

     1 from sqlalchemy.orm import sessionmaker
     2 from create_table import Student,engine
     3 
     4 #创建与数据库的连接
     5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型
     6 db_session=session()#创建数据库连接
     7 
     8 
     9 
    10 #
    11 #单个增加add
    12 s=Student(name='yang')#实例化对象
    13 db_session.add(s)#创建插入语句(只在当前的缓存中执行,但是id会被占用)
    14 db_session.commit()#提交数据库执行同步更新指令
    15 db_session.close()#使用完之后最好关闭连接
    16 
    17 
    18 #批量增加add_all
    19 s_list=[Student(name='zhang'),Student(name='li')]
    20 db_session.add_all(s_list)
    21 db_session.commit()
    22 db_session.close()#使用完之后最好关闭连接
    单数据表---增 

      1.2.2查

     1 from sqlalchemy.orm import sessionmaker
     2 from create_table import Student,engine
     3 
     4 #创建与数据库的连接
     5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型
     6 db_session=session()#创建数据库连接
     7 
     8 #查query
     9 # (在执行其他操作之后务必关闭数据库连接,否则查询的是上一次连接建立时导入缓存的数据,导致数据不准确)
    10 print(db_session.query(Student))#直接打印出原生SQL:SELECT student.id AS student_id, student.name AS student_name FROM student
    11 
    12 #普通查询
    13 res_all=db_session.query(Student).all()#查询所有
    14 print(res_all)
    15 for i in res_all:
    16     print(i.id,i.name)
    17 
    18 
    19 res_first=db_session.query(Student).first()#查询所有结果的第一个
    20 print(res_first)
    21 print(res_first.id,res_first.name)
    22 
    23 res_one=db_session.query(Student)[1]#查询结果中选取某一个
    24 # res_one=db_session.query(Student).all()[1]
    25 print(res_one)
    26 print(res_one.id,res_one.name)
    27 
    28 
    29 res_many=db_session.query(Student)[:2]#查询结果中的连续的某几个
    30 # res_many=db_session.query(Student).all()[:2]
    31 print(res_many)
    32 for i in res_many:
    33     print(i.id,i.name)
    34 
    35 # 带条件的查询
    36 res=db_session.query(Student).filter(Student.id>1)
    37 print(res)#直接输出了原生SQL语句
    38 #SELECT student.id AS student_id, student.name AS student_name
    39 # FROM student
    40 # WHERE student.id > %(id_1)s
    41 
    42 #filter中的算数运算符过滤
    43 res=db_session.query(Student).filter(Student.id>1).all()
    44 print(res,[(i.id,i.name) for i in res],sep='
    ')
    45 res=db_session.query(Student).filter(Student.id==5).all()
    46 print(res,[(i.id,i.name) for i in res],sep='
    ')
    47 res=db_session.query(Student).filter(Student.id!=1).all()
    48 print(res,[(i.id,i.name) for i in res],sep='
    ')
    49 
    50 
    51 #filter中的并列条件过滤(条件之间用逗号隔开)
    52 res=db_session.query(Student).filter(Student.id>1,Student.name!='yang').all()
    53 print(res,[(i.id,i.name) for i in res],sep='
    ')
    单数据表---查

      1.2.3改

     1 from sqlalchemy.orm import sessionmaker
     2 from create_table import Student,engine
     3 
     4 #创建与数据库的连接
     5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型
     6 db_session=session()#创建数据库连接
     7 
     8 # 改(基于查询)
     9 res=db_session.query(Student).filter(Student.name=='YANG').update({"name":'yang'})#单条修改,返回修改条数
    10 db_session.commit()#务必要提交到数据库
    11 print(res)
    12 db_session.close()
    13 
    14 
    15 res=db_session.query(Student).filter(Student.id>5).update({"name":'yang'})#更新多条,注意更新的该字段不能设置唯一,否则报错
    16 db_session.commit()
    17 print(res)#更新记录条数
    18 db_session.close()
    单数据表---改

      1.2.4删  

     1 from sqlalchemy.orm import sessionmaker
     2 from create_table import Student,engine
     3 
     4 #创建与数据库的连接
     5 session=sessionmaker(engine)#初始化与数据库引擎的连接类型
     6 db_session=session()#创建数据库连接
     7 
     8 # 删(基于查询)
     9 res=db_session.query(Student).filter(Student.id>8).delete()#返回删除的记录条数
    10 db_session.commit()
    11 print(res)
    12 db_session.close()
    单数据表---删

     1.2.5 高级版查询操作 

      1 # 高级版查询操作,厉害了哦
      2 #老规矩
      3 from create_table import Student,engine
      4 from sqlalchemy.orm import sessionmaker
      5 
      6 Session = sessionmaker(engine)
      7 db_session = Session()
      8 
      9 
     10 # db_session.add_all([User(name='yang'),User(name='zhang'),User(name='wang')])
     11 # db_session.commit()
     12 
     13 # 查询数据表操作
     14 # and or
     15 from sqlalchemy.sql import and_ , or_,text
     16 ret = db_session.query(Student).filter(and_(Student.id > 3, Student.name == 'yang')).all()
     17 print(ret)
     18 ret = db_session.query(Student).filter(or_(Student.id < 2, Student.name == 'yang')).all()
     19 print(ret)
     20 
     21 # # 查询所有数据
     22 r1 = db_session.query(Student).all()
     23 
     24 # # 查询数据 指定查询数据列 加入别名
     25 r2 = db_session.query(Student.name.label('username'), Student.id).first()
     26 print(r2.id,r2.username) # 3 wang
     27 
     28 # 表达式筛选条件
     29 r3 = db_session.query(Student).filter(Student.name == "yang").all()
     30 
     31 # # 原生SQL筛选条件
     32 r4 = db_session.query(Student).filter_by(name='yang').all()
     33 r5 = db_session.query(Student).filter_by(name='yang').first()
     34 
     35 # 字符串匹配方式筛选条件 并使用 order_by进行排序
     36 r6 = db_session.query(Student).filter(text("id<:value and name=:name")).params(value=224, name='yang').order_by(Student.id).all()
     37 
     38 # #原生SQL查询
     39 r7 = db_session.query(Student).from_statement(text("SELECT * FROM User where name=:name")).params(name='yang').all()
     40 
     41 # # 筛选查询列
     42 # # query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取
     43 user_list = db_session.query(Student.name).all()
     44 print(user_list)#[('wang',), ('yang',), ('zhang',)]
     45 for row in user_list:
     46     print(row.name)
     47 #
     48 # # 别名映射  name as nick
     49 user_list = db_session.query(Student.name.label("nick")).all()
     50 print(user_list)
     51 for row in user_list:
     52     print(row.nick) # 这里要写别名了
     53 #
     54 # # 筛选条件格式
     55 user_list = db_session.query(Student).filter(Student.name == "yang").all()
     56 user_list = db_session.query(Student).filter(Student.name == "yang").first()
     57 user_list = db_session.query(Student).filter_by(name="yang").first()
     58 for row in user_list:
     59     print(row.nick)
     60 
     61 # 复杂查询
     62 from sqlalchemy.sql import text
     63 user_list = db_session.query(Student).filter(text("id<:value and name=:name")).params(value=3,name="DragonFire")
     64 
     65 # 查询语句
     66 from sqlalchemy.sql import text
     67 user_list = db_session.query(Student).filter(text("select * from User id<:value and name=:name")).params(value=3,name="DragonFire")
     68 
     69 # 排序 :
     70 user_list = db_session.query(Student).order_by(Student.id).all()
     71 user_list = db_session.query(Student).order_by(Student.id.desc()).all()
     72 for row in user_list:
     73     print(row.name,row.id)
     74 
     75 #其他查询条件
     76 """
     77 ret = session.query(Student).filter_by(name='yang').all()
     78 ret = session.query(Student).filter(Student.id > 1, Student.name == 'yang').all()
     79 ret = session.query(Student).filter(Student.id.between(1, 3), Student.name == 'yang').all() # between 大于1小于3的
     80 ret = session.query(Student).filter(Student.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的
     81 ret = session.query(Student).filter(~Student.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
     82 ret = session.query(Student).filter(Student.id.in_(session.query(Student.id).filter_by(name='yang'))).all() 子查询
     83 from sqlalchemy import and_, or_
     84 ret = session.query(Student).filter(and_(Student.id > 3, Student.name == 'yang')).all()
     85 ret = session.query(Student).filter(or_(Student.id < 2, Student.name == 'yang')).all()
     86 ret = session.query(Student).filter(
     87     or_(
     88         Student.id < 2,
     89         and_(User.name == 'eric', Student.id > 3),
     90         Student.extra != ""
     91     )).all()
     92 # select * from Student where id<2 or (name="eric" and id>3) or extra != "" 
     93 
     94 # 通配符
     95 ret = db_session.query(Student).filter(Student.name.like('e%')).all()
     96 ret = db_session.query(Student).filter(~Student.name.like('e%')).all()
     97 
     98 # 限制
     99 ret = db_session.query(Student)[1:2]
    100 
    101 # 排序
    102 ret = db_session.query(Student).order_by(Student.name.desc()).all()
    103 ret = db_session.query(Student).order_by(Student.name.desc(), Student.id.asc()).all()
    104 
    105 # 分组
    106 from sqlalchemy.sql import func
    107 
    108 ret = db_session.query(Student).group_by(Student.extra).all()
    109 ret = db_session.query(
    110     func.max(Student.id),
    111     func.sum(Student.id),
    112     func.min(Student.id)).group_by(Student.name).all()
    113 
    114 ret = db_session.query(
    115     func.max(Student.id),
    116     func.sum(Student.id),
    117     func.min(Student.id)).group_by(Student.name).having(func.min(Student.id) >2).all()
    118 """
    119 
    120 # 关闭连接
    121 db_session.close()
    高级版查询

       1.2.6高级版修改操作

     1 #高级版更新操作
     2 from create_table import Student,engine
     3 from sqlalchemy.orm import sessionmaker
     4 
     5 Session = sessionmaker(engine)
     6 db_session = Session()
     7 
     8 #直接修改
     9 db_session.query(Student).filter(Student.id > 0).update({"name" : "099"})
    10 
    11 #在原有值基础上添加 - 1
    12 db_session.query(Student).filter(Student.id > 0).update({Student.name: Student.name + "099"}, synchronize_session=False)
    13 
    14 #在原有值基础上添加 - 2
    15 db_session.query(Student).filter(Student.id > 0).update({"age": Student.age + 1}, synchronize_session="evaluate")
    16 db_session.commit()
    17 db_session.close()
    高级版修改

    2.一对多ForeignKey数据表(基于relationship)

       2.1ForeignKey一对多数据表及relationshipi关系创建

     1 from sqlalchemy.ext.declarative import declarative_base
     2 #(1)创建基类
     3 Base=declarative_base()
     4 
     5 from sqlalchemy import Column,Integer,String,ForeignKey
     6 from sqlalchemy.orm import relationship
     7 #(2)创建对象,ForeignKey及relationship关系
     8 class ClassTable(Base):
     9     __tablename__='classtable'
    10     id=Column(Integer,primary_key=True)
    11     name=Column(String(32),unique=False)
    12 
    13 class Student(Base):
    14     __tablename__='student'
    15     id=Column(Integer,primary_key=True)
    16     name=Column(String(32),nullable=False)
    17     #创建foreignKey和relationship关系
    18     class_id=Column(Integer,ForeignKey('classtable.id')) # 关联字段,让class_id 与 classtable 的 id 进行关联,主外键关系(这里的ForeignKey一定要是表名.id不是对象名)
    19     stc=relationship("ClassTable",backref='cts')# 将student 与 ClassTable 创建关系 这个不是字段,只是关系,backref是反向关联的关键字
    20 
    21 
    22 #(3)创建数据库引擎
    23 from sqlalchemy import create_engine
    24 engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")
    25 
    26 #(4)基于数据库引擎创建集成基类的表
    27 Base.metadata.create_all(engine)
    28 # Base.metadata.drop_all(engine)
    ForeignKey一对多数据表及relationshipi关系创建(create_ForeignKey_table.py)

      2.2一对多基于relationship的增查和改删操作

        2.2.1一对多基于relationship的增

     1 from sqlalchemy.orm import sessionmaker
     2 from create_ForeignKey_table import Student,ClassTable,engine
     3 #(1)建立数据库链接会话
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 #一对多的增
     8 
     9 #普通的增
    10 
    11 #先增加班级
    12 db_session.add(ClassTable(name='三年一班'))
    13 db_session.add_all([ClassTable(name='三年二班'),ClassTable(name='三年三班')])
    14 db_session.commit()
    15 #查询要添加到的班级
    16 cla=db_session.query(ClassTable).filter(ClassTable.name=='三年一班').first()
    17 #增加对应班级的学生
    18 db_session.add(Student(name='yang',class_id=cla.id))
    19 db_session.add_all([Student(name='zhang',class_id=cla.id),Student(name='li',class_id=cla.id)])
    20 db_session.commit()
    21 db_session.close()
    22 
    23 #基于relationshi增加
    24 
    25 #正向增加(foreiKey决定)
    26 db_session.add(Student(name='he',stc=ClassTable(name='二年一班')))
    27 db_session.commit()
    28 
    29 
    30 #反向增加
    31 class_obj=ClassTable(name='二年二班')#实例化班级或者查询出一个班级
    32 # class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年二班').first()
    33 
    34 # 向 Student 数据表中添加 1条或多条数据 并将 1条或多条数据的class_id 写成 class_obj的id
    35 class_obj.cts=[Student(name='wu'),Student(name='feng')]#添加一个也必须是列表
    36 
    37 db_session.add(class_obj)
    38 db_session.commit()
    39 db_session.close()
    40 
    41 一对多基于relationship的增
    一对多基于relationship的增

        2.2.2一对多基于relationship的查

     1 from sqlalchemy.orm import sessionmaker
     2 from create_ForeignKey_table import Student,ClassTable,engine
     3 #(1)建立数据库链接会话
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 #查(基于relationship)
     8 #正向查询
     9 stu_list=db_session.query(Student).all()
    10 print([(stu.id,stu.name,stu.stc.name,stu.stc.id) for stu in stu_list])
    11 
    12 #反向查询
    13 cla_list=db_session.query(ClassTable).all()
    14 # print([(cla.id,cla.name,[stu.name for stu in cla.cts])for cla in cla_list])
    15 for cla in cla_list:
    16     for stu in cla.cts:#通过relationship设置的backref反向关联到学生对象
    17         print(cla.id,cla.name,stu.name)
    18 
    19 一对多基于relationship的查
    一对多基于relationship的查

        2.2.3一对多的改 

     1 from sqlalchemy.orm import sessionmaker
     2 from create_ForeignKey_table import Student,ClassTable,engine
     3 #(1)建立数据库链接会话
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 #通过班级修改学生信息
     8 class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年二班').first()
     9 db_session.query(Student).filter(Student.class_id==class_obj.id).update({'name':'22'})
    10 db_session.commit()
    11 db_session.close()
    12 
    13 
    14 #通过学生修改班级信息
    15 stu_obj=db_session.query(Student).filter(Student.name=='22').first()
    16 db_session.query(ClassTable).filter(ClassTable.id==stu_obj.class_id).update({'name':'二年四班'})
    17 db_session.commit()
    18 db_session.close()
    19 
    20 一对多的改
    一对多的改

        2.2.4一对多的删

     1 from sqlalchemy.orm import sessionmaker
     2 from create_ForeignKey_table import Student,ClassTable,engine
     3 #(1)建立数据库链接会话
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 #
     8 class_obj=db_session.query(ClassTable).filter(ClassTable.name=='二年四班').first()
     9 res=db_session.query(Student).filter(Student.class_id==class_obj.id).delete()
    10 db_session.commit()
    11 print(res)
    12 db_session.close()
    13 
    14 一对多的删
    一对多的删

      3.多对多基于第三张的数据表

      3.1多对多数据表relationship及第三张表ForeignKey创建

     1 from sqlalchemy.ext.declarative import declarative_base
     2 #(1)创建基类
     3 Base=declarative_base()
     4 
     5 
     6 from sqlalchemy import Column,Integer,String,ForeignKey
     7 from sqlalchemy.orm import relationship
     8 #(2)创建对象和relationship关系
     9 class Girl(Base):
    10     __tablename__='girl'
    11     id=Column(Integer,primary_key=True)
    12     name=Column(String(32),nullable=False)
    13     gtb=relationship('Boy',secondary='friendship', backref='btg')#创建多对多的relationship关系,注意第三张表的桥梁关系
    14 
    15 
    16 class Boy(Base):
    17     __tablename__='boy'
    18     id = Column(Integer, primary_key=True)
    19     name = Column(String(32), nullable=False)
    20 
    21 #多对多关系必须创建第三张关联表
    22 class Friendship(Base):
    23     __tablename__='friendship'
    24     id = Column(Integer, primary_key=True)
    25     girl_id=Column(Integer,ForeignKey('girl.id'))#创建于girl表的外键关联
    26     boy_id = Column(Integer, ForeignKey('boy.id'))#创建于boy表的外键关联
    27 
    28 #(3)创建数据库引擎
    29 from sqlalchemy import create_engine
    30 engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8')
    31 
    32 #(4)基于数据库引擎创建继承Base基类的表
    33 Base.metadata.create_all(engine)
    多对多数据表relationship及第三张表ForeignKey创建(create_MTM_table.py)

      3.2多对多基于relationship的增查

        3.2.1多对多基于relationship的增

     1 from sqlalchemy.orm import sessionmaker
     2 from create_MTM_table import engine,Girl,Boy
     3 #(1)创建数据库连接
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 
     8 #多对多表的增
     9 #MTM基于relationship的正向增
    10 db_session.add(Girl(name='g1',gtb=[Boy(name='b1'),Boy(name='b11')]))
    11 db_session.commit()
    12 
    13 #MTM基于relationship的反向增
    14 boy_obj=Boy(name='b2')
    15 boy_obj.btg=[Girl(name='g2'),Girl(name='g22')]
    16 db_session.add(boy_obj)
    17 db_session.commit()
    18 
    19 db_session.close()
    多对多基于relationship的增

        3.2.1多对多基于relationship的查

     1 from sqlalchemy.orm import sessionmaker
     2 from create_MTM_table import engine,Girl,Boy
     3 #(1)创建数据库连接
     4 session=sessionmaker(engine)
     5 db_session=session()
     6 
     7 #多对多表的查
     8 #通过girl查所有的boy
     9 girl_list=db_session.query(Girl).all()
    10 for girl in girl_list:
    11     for boy in girl.gtb:
    12         print(girl.name,"----------",boy.name)
    13 #通过boy查询所有的girl
    14 boy_list=db_session.query(Boy).all()
    15 for boy in boy_list:
    16     for girl in boy.btg:
    17         print(boy.name, "----------", girl.name)
    多对多基于relationship的查
  • 相关阅读:
    SQL里的EXISTS与in、not exists与not in
    N秒后自动跳转
    Array类型的扩展
    css中block与inline的区别
    数据绑定表达式语法(Eval,Bind区别)
    case
    SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY的比较 (转载)
    内容随鼠标漂移
    IIS下下伪静态html(URL Rewrite)设置方法
    sql查询含有某列名的所有表
  • 原文地址:https://www.cnblogs.com/open-yang/p/11278093.html
Copyright © 2020-2023  润新知