SQLAchemy 链接查询

class DevType(db.Model):"""设备种类"""__tablename__ = 'dev_type'type_id = db.Column('type_id', db.Integer, primary_key=True, doc='id')type_name = db.Column('type_name', db.String, doc='名字')type_num = db.Column('type_num', db.Integer, doc='设备类型代号')parent_id = db.Column('parent_id', db.Integer, doc='设备父类型')# params = db.relationship('DevParamRelation', uselist=True)class DevParamRelation(db.Model):"""设备与实时数据字段关系"""__tablename__ = 'dev_params_relation'# db.ForeignKey('dev_type.type_id'),id = db.Column('id', db.Integer, primary_key=True, doc='id')dev_type = db.Column('dev_type', db.Integer, doc='设备类型')param_name = db.Column('param_name', db.String, doc='参数名称')param_desc = db.Column('param_desc', db.String, nullable=True, doc='参数描述')

• INNER JOIN:
  db.session.query(DevParamRelation, DevType.type_name).join(DevType, and_(DevParamRelation.dev_type == DevType.type_id)).all()

SELECT dev_params_relation.id AS dev_params_relation_id, dev_params_relation.dev_type AS dev_params_relation_dev_type, dev_params_relation.param_name AS dev_params_relation_param_name, dev_params_relation.param_desc AS dev_params_relation_param_desc, dev_type.type_name AS dev_type_type_name 
FROM dev_params_relation INNER JOIN dev_type ON dev_params_relation.dev_type = dev_type.type_id