Python学习

flask-sqlalchemy数据库多表操作(四)

1、参数配置

对数据库连接字符串等信息进行配置，这里以类的形式配置

# 配置参数
class Config(object):
    # 连接字符串
    SQLALCHEMY_DATABASE_URL = 'mysql://root:root@127.0.0.1:3306/python_db'
    # 设置SQL自动跟踪数据库
    SQLALCHEMY_TRACE_MODIFICATIONS = True
    # 查询显示SQL语句
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)

2、创建模型类

创建数据库模型类学生类和班级类

# 学生模型
class Student(db.Model):
    __tablename__ = 'db_student1'  # 指名数据库表名
    # 指名主键 整型主键 自动递增
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(20))
    sex = db.Column(db.String(2))
    birthday = db.Column(db.Date)
    address = db.Column(db.String(100))
    cid = db.Column(db.Integer,db.ForeignKey('db_class1.id'))
    # 给这个Student模型添加一个Class属性（关系表），
    # Class为要连接的表，backref为定义反向引用
    # 与生成表结构无关，仅用于查询方便
    isClass = db.relationship('Class',backref='students')
   #方便输出 类似java中tostring方法
    def __repr__(self):
        return ('姓名:%s 性别:%s 生日:%s 地址:%s' %(self.name,self.sex,self.birthday,self.address))

# 班级模型
class Class(db.Model):
    __tablename__ = 'db_class1'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))

3、多表操作

3.1、联合查询

# 查询学生名以及对应的班级名
   ret = db.session.query(
       Student.name,Class.name
   ).filter(Student.cid == Class.id).all()
   for item in ret:
       print(item)
   # SELECT db_student1.name AS db_student1_name, db_class1.name AS db_class1_name
   # FROM db_student1, db_class1
   # WHERE db_student1.cid = db_class1.id

3.2、带条件的联合查询

# 查询学生编号为4的学生名以及对应的班级名
   ret = db.session.query(
       Student.name, Class.name
   ).filter(Student.id == 4).filter(Student.cid == Class.id).all()
   for item in ret:
       print(item)
   # SELECT db_student1.name AS db_student1_name, db_class1.name AS db_class1_name
   # FROM db_student1, db_class1
   # WHERE db_student1.id = %s AND db_student1.cid = db_class1.id

3.3、relationship关联查询

我们创建表结构的时候

 # 给这个Student模型添加一个Class属性（关系表），
    # Class为要连接的表，backref为定义反向引用
    # 与生成表结构无关，仅用于查询方便
    isClass = db.relationship('Class',backref='students')

可以利用relationship获得所属班级信息

ret = Student.query.filter(Student.id == 4).all()
   for item in ret:
       print(item.name,item.isClass.name)

执行的SQL语句如下：

SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid 
FROM db_student1 
WHERE db_student1.id = %s

获得对应班级的外键cid，类似java中mybatis查询的一种方式，分步查询，然后拿着cid再去c班级表查询

SELECT db_class1.id AS db_class1_id, db_class1.name AS db_class1_name 
FROM db_class1 
WHERE db_class1.id = %s

# 查询安卓二班的所有学生
   ret = Class.query.filter(Class.name == '安卓2班').all()
   for item in ret:
       print(item.students)
# SQL语句分步执行
# 1、查询安卓2班的编号
SELECT db_class1.id AS db_class1_id, db_class1.name AS db_class1_name 
FROM db_class1 
WHERE db_class1.name = %s
# 2、根据安卓2班的编号查询学生信息
SELECT db_student1.id AS db_student1_id, db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_student1.birthday AS db_student1_birthday, db_student1.address AS db_student1_address, db_student1.cid AS db_student1_cid 
FROM db_student1 
WHERE %s = db_student1.cid

3.4、join查询

join分为left join（左外连接）和right join（右外连接）以及内连接（等值连接）。
左外连接查询

# outerjoin实现联合查询
  ret = db.session.query(
      Student.name,Student.sex, Class.name
  ).outerjoin(
      Class,Student.cid == Class.id
  ).filter(Student.id == 4).all()
  for item in ret:
      print(item)
  # SELECT db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_class1.name AS db_class1_name
  # FROM db_student1 LEFT OUTER JOIN db_class1 ON db_student1.cid = db_class1.id
  # WHERE db_student1.id = %s

内连接查询

ret = db.session.query(
       Student.name, Student.sex, Class.name
   ).join(
       Class, Student.cid == Class.id
   ).filter(Student.id == 4).all()
   for item in ret:
       print(item)
   # SELECT db_student1.name AS db_student1_name, db_student1.sex AS db_student1_sex, db_class1.name AS db_class1_name
   # FROM db_student1 INNER JOIN db_class1 ON db_student1.cid = db_class1.id
   # WHERE db_student1.id = %s

3.5、子查询

使用子查询，分3个步骤
1、将子查询按照传统方式写好查询代码，然后在query对象后面执行subquery()，将这个查询变成子查询；
2、在子查询中，将以后需要用到的字段通过label(别名)方法，取别名；
3、在父查询中，如果想用子查询中的字段，可以通过子查询返回值.c.字段名拿到；

# 子查询实现 查询和张三同班的学生信息
   zhangsan = db.session.query(Student.cid.label('classid'),Student.name).filter(Student.name == '张三').subquery()
   print(zhangsan)
   stus = db.session.query(Student).filter(Student.cid == zhangsan.c.classid).all()
   print(stus)