from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String,DateTime,Enum,ForeignKey,UniqueConstraint,ForeignKeyConstraint,Index

from 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()

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()

session.query(Classes).filter(Classes.cname.like("%王%"))

# 在filter中，可以用某一列点出like方法进行模糊匹配

between and       between()

session.query(Classes).filter(Classes.id.between(1,2))

# 和like一样，也是通过某一列点出来，between是闭区间

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     切片

session.query(Classes)[2:6:2]

# 用切片来实现，和列表切片一样，切的长度和指定步长

order by      order_by()

session.query(Classes).order_by(Classes.id.desc())

# 通过order_by方式查询，里面可以指定哪列排序，可以点出是升序asc还是降序方法desc

# 同样的，括号里也可以指定多个列排序，用法和SQL语句用法一致

group by having      group_by()    having()

# 想要使用聚合函数，就必须先导入func方法

from sqlalchemy.sql import func

session.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_type找到Classes中对应的某一行，也可以点出Classes的属性来

通过绑定外键表(Student)中隐藏列查被绑定外键表(Classes)中的一行就叫正向查询

# 并且Classes也可以通过点xxx点出满足这个Classes.id的学生有多少

# 点出来的是一个列表，列表中放着学生的对象

通过被绑定外键表(Classes)中的隐藏列查有多少符合的表(Student)的行叫做反向查询