sqlalchemy的使用二

对mysql函数的返回类型是mysql的数据类型
函数接收参数并返回,存储过程+
@t1=0 相当于全局变量
命中索引
在索引的数据结构中

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users,Depart

engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
SessionFactory = sessionmaker(bind=engine)

# 根据Users类对users表进行增删改查
session = SessionFactory()

# 1. 查询所有用户
# ret = session.query(Users).all()
# for row in ret:
#     print(row.id,row.name,row.depart_id)
# 2. 查询所有用户+所属部门名称 内链接查询
# ret = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id).all()
# for row in ret:
#     print(row.id,row.name,row.title)
左链接查询
# query = session.query(Users.id,Users.name,Depart.title).join(Depart,Users.depart_id == Depart.id,isouter=True)
# print(query)

# 3. relation字段:查询所有用户+所属部门名称
# ret = session.query(Users).all()
# for row in ret:
#     print(row.id,row.name,row.depart_id,row.dp.title)

# 4. relation字段:查询销售部所有的人员
# obj = session.query(Depart).filter(Depart.title == '销售').first()
# for row in obj.pers:
#     print(row.id,row.name,obj.title)

# 5. 创建一个名称叫：IT部门，再在该部门中添加一个员工：田硕
# 方式一：
# d1 = Depart(title='IT')
# session.add(d1)
# session.commit()
#
# u1 = Users(name='田硕',depart_id=d1.id)
# session.add(u1)
# session.commit()

# 方式二：
# u1 = Users(name='田硕',dp=Depart(title='IT'))
# session.add(u1)
# session.commit()

# 6. 创建一个部门名称叫：王者荣耀，再在该部门中添加一个员工：龚林峰/长好梦/王爷们
# d1 = Depart(title='王者荣耀')
# d1.pers = [Users(name='龚林峰'),Users(name='长好梦'),Users(name='王爷们'),]
# session.add(d1)
# session.commit()

session.close()

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import Integer,String,Text,Date,DateTime,ForeignKey,UniqueConstraint, Index
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship


Base = declarative_base()

class Depart(Base):
    =表名
    __tablename__ = 'depart'
    id = Column(Integer, primary_key=True)
    title = Column(String(32), index=True, nullable=False)

class Users(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    depart_id = Column(Integer,ForeignKey("depart.id"))
relationship的用法,关联表Depart,仅用于关联表,不会在表中创建字段
backref='pers'用于反向查找
    dp = relationship("Depart", backref='pers')

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)

    course_list = relationship('Course', secondary='student2course', backref='student_list')

class Course(Base):
    __tablename__ = 'course'
    id = Column(Integer, primary_key=True)
    title = Column(String(32), index=True, nullable=False)

class Student2Course(Base):
    __tablename__ = 'student2course'
    id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.id'))
    course_id = Column(Integer, ForeignKey('course.id'))

    __table_args__ = (
        UniqueConstraint('student_id', 'course_id', name='uix_stu_cou'), # 联合唯一索引
        # Index('ix_id_name', 'name', 'extra'),                          # 联合索引
    )

def create_all():
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)

def drop_all():
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    # drop_all()
    create_all()

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Student,Course,Student2Course

engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/s9day120?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
SessionFactory = sessionmaker(bind=engine)

# 根据Users类对users表进行增删改查
session = SessionFactory()

# 1. 录入数据
# session.add_all([
#     Student(name='先用'),
#     Student(name='佳俊'),
#     Course(title='生物'),
#     Course(title='体育'),
# ])
# session.commit()

# session.add_all([
#     Student2Course(student_id=2,course_id=1)
# ])
# session.commit()

# 2. 三张表关联
# ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).order_by(Student2Course.id.asc())
# for row in ret:
#     print(row)
# 3. “先用”选的所有课
# ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).filter(Student.name=='先用').order_by(Student2Course.id.asc()).all()
# print(ret)

# obj = session.query(Student).filter(Student.name=='先用').first()
# for item in obj.course_list:
#     print(item.title)

# 4. 选了“生物”的所有人
# obj = session.query(Course).filter(Course.title=='生物').first()
# for item in obj.student_list:
#     print(item.name)

# 5. 创建一个课程，创建2学生，两个学生选新创建的课程。
# obj = Course(title='英语')
# obj.student_list = [Student(name='为名'),Student(name='广宗')]
#
# session.add(obj)
# session.commit()


session.close()