SQL alchemy介绍

SQL alchemy是orm思想的一个具体实现的产品

orm：对象关系映射思想 Object Relational Mapping

就是将数据库里的资源与面向对象中的类和对象对应起来

　　一张表    ===》    一个类

　　一条数据 ===》   一个对象

在python中用这个思想实现具体的比较有名的产品就是sqlalchemy，

SQL alchemy是一个第三方的包，我们需要通过cmd 输入 pip3 install sqlalchemy来安装

我们可以操作类和对象来实现数据库的一些基本操作

创建表的操作

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,DateTime,Enum,ForeignKey,UniqueConstraint,ForeignKeyConstraint,Indexfrom sqlalchemy.orm import sessionmaker,relationship# 先要创建一个数据库连接对象engine = create_engine('mysql+pymysql://root:hsjqqq@127.0.0.1:3306/db4?charset=utf8',max_overflow=5)# 里面需要指定连接条件包括一些用户名密码连接ip及端口，还需要指定连接池max_overflow，指定最大连接数# 需要注意的是：在这里面指定字符编码并不会产生影响，需要在创建数据库时指定编码# 创建基类，其他类继承这个类就可以与表映射了Base = declarative_base()class Classes(Base):    # 创建表需要指定一个表名    __tablename__ = 'classes'    # 指定字段并且规定类型和约束    id = Column(Integer,primary_key=True,autoincrement=True)    cname = Column(String(32),nullable=False,server_default='')    # 需要注意的是：设置默认值必须用server_default，且如果设置int类型的默认值必须用str()转一下        addr = Column(String(32),nullable=False,server_default='',index=True)       # 给某一个列添加唯一索引或者索引可以直接在Column里直接设置即可        classes_id = Column(Integer, ForeignKey(Teacher.id))    # 添加外加也是在里面设置，通过 ForeignKey()方法来设置    # 这个是建立一对多的外键关系，如果是一对一的外键关系还需要加上唯一索引的条件    # eg：classes_id = Column(Integer, ForeignKey(Teacher.id),unique=True)    __table_args__ = (          UniqueConstraint(sname, phone, name='uix_snname_phone'),        Index('ix_addr',addr)    )    # 想要设置联合唯一索引或者是联合索引的话就必须在__table_args__里设置    # 通过UniqueConstraint、Index完成索引的建立        Base.metadata.drop_all(engine)  # 删除Base.metadata.create_all(engine)    # 创建# 最后创建映射的表和删除表需要通过上面两个方法# 需要注意的是：#       1、删除只会删除上面有映射关系的表，如果你数据库中有没有被映射的表，那么是删不掉的#          如果你数据库本来就是空的，那也不会报错#       2、创建的话如果你这表名已经存在，那么就不会覆盖创建，就相当于没执行

数据基本的增删改查

查询先要创建一个查询的对象session，用于帮我们转换命令和获得结果Session = sessionmaker(bind=egine)  # 需要绑定一下连接对象session = Session()提前声明：除了查询，其他的操作都必须要session.commit才能把操作真正的执行到数据库中# ------------------------------------------------------------------------添加    add()     add_all()# 需要先创建数据，在程序中对应的就是对象，有自增属性的列不用设置，其他的通过关键字赋值obj = Classes(cname='三年二班')session.add(obj)    # add添加的一行数据（一个对象）session.add_all([obj,obj2]) # add_all()添加多条数据，用列表装起来# ------------------------------------------------------------------------查询  query()# 查所有的，取所有字段res = session.query(Classes).all()  # 返回的是一个列表，里面装着对象session.query(Classes)  # 打印出来的是SQL语句res = session.query(Classes).first() # 取的是第一个，返回一个对象# 查所有，取特定字段res = session.query(Classes.cname).all()# 返回的是一个列表，里面装着元组，元组里放着你查询的值res = session.query(Classes.cname).first() # 取得是第一个，返回元组# 取所有字段相当于SQL语句中得 *  取特定字段相当于 某一列或者某几列# 两者得区别是：所有字段回来得是对象，特定字段回来得是元组# 具体得条件查询先看下文# ------------------------------------------------------------------------修改  update()# 把需要改得值先找出来，再通过update进行修改session.query(Classes).filter(Classes.id == 1).update({
   'cname':'xxx'})# update里放入一个字典k,v 对应着表里面的列和值# ------------------------------------------------------------------------删除 delete()# 删除也需要先把值找出来，再进行删除session.query(Classes).filter(Classes.id == 1).delete()

session.commit() session.close()

其他的查询

SQL语句中的查询在SQL alchemy中基本上都有对应的方法来实现，具体如下

where

where 条件查询    filter()    filter_by()# 括号里面写入判断条件(可以是多个)，两者区别如下session.query(Classes).filter(Classes.id == 1)# 括号里放的是具体哪个列表的哪个列等于某个值session.query(Classes).filter_by(id = 1)# 括号里放的是列名 = 值（相当于赋值，在查多个表并且出现列名相同时会有麻烦）session.query(Classes).filter(~Classes.id == 1)# ~ 是取反的意思

like

通配符like模糊匹配      like()session.query(Classes).filter(Classes.cname.like("%王%"))# 在filter中，可以用某一列点出like方法进行模糊匹配

between and

between and       between()session.query(Classes).filter(Classes.id.between(1,2))# 和like一样，也是通过某一列点出来，between是闭区间

in / not in

in  not in      in_  notin_session.query(Classes).filter(Classes.id.in_([1]))session.query(Classes).filter(Classes.id.notin_([1,2,4]))# 也是通过具体的列可以点出方法# 需要注意的是，in_或者notin_里面放的必须是可迭代对象

limit

limit     切片session.query(Classes)[2:6:2]# 用切片来实现，和列表切片一样，切的长度和指定步长

order by

order by      order_by()session.query(Classes).order_by(Classes.id.desc())# 通过order_by方式查询，里面可以指定哪列排序，可以点出是升序asc还是降序方法desc# 同样的，括号里也可以指定多个列排序，用法和SQL语句用法一致

group by (having)

group by having      group_by()    having()# 想要使用聚合函数，就必须先导入func方法from sqlalchemy.sql import funcsession.query(func.max(Classes.id)).group_by(Classes.cname).having(func.max(Classes.id)>3)# 在query中使用聚合函数必须通过func点出来# group里放入按照分组的列，最后可以点出having()来进行二次查询

连表查询

连表基本的连表session.query(Classes,Student)# 直接把两张表连起来，会有笛卡尔积的问题# 通过外键判断连接session.query(Classes,Student),filter(Classes.id == Student.class_id)左连接  join()session.query(Student).join(Classes)# join中只放入一张表相当于 inner join   内连接session.query(Student).join(Classes,isouter=True)# 把join中的isouter改成True就变成左连接了但是你如果想要获得班级名称的值，那也就必须把班级表放到query中

正查反查

正查反查   relationship# 在有外键的表中添加隐藏属性（就是在列中定义一个属性）attr = relationship('Classes',backref='hideattr')# relationship里面第一个是你被你关联外键的表，backref是对方表中的隐藏列案例class Classes(Base):    __tablename__ = 'classes'    id = Column(Integer,primary_key=True,autoincrement=True)    cname = Column(String(32),nullable=False,server_default='')class Student(Base):    id = Column(Integer,primary_key=True,autoincrement=True)    sname = Column(String(32),nullable=False,server_default='')    class_id = Column(Integer,ForeignKey(Classes.id))    class_type = relationship('Classes',backref='xxx')# 这样Student这张表可以通过Student.class_id找到Classes中对应的某一行，也可以点出Classes的属性来通过绑定外键表(Student)中隐藏列查被绑定外键表(Classes)中的一行就叫正向查询# 并且Classes也可以通过点xxx点出满足这个Classes.id的学生有多少# 点出来的是一个列表，列表中放着学生的对象通过被绑定外键表(Classes)中的隐藏列查有多少符合的表(Student)的行叫做反向查询