关于sqlalchemy一对多的orm实现及逻辑操作过程
表结构：
主表：
CREATE TABLE `nat_gateway_sc_snat_rules` ( `project_id` varchar(255) DEFAULT NULL, `id` varchar(36) NOT NULL, `nat_gateway_id` varchar(36) NOT NULL, `name` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `subnet_cidr` varchar(64) NOT NULL, `fip_ip` varchar(64) NOT NULL, `fip_bandwidth` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `nat_gateway_id` (`nat_gateway_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
副表：
CREATE TABLE `nat_gateway_sc_snat_rule_dummyfloatingips` ( `nat_gateway_sc_snat_rule_id` varchar(36) NOT NULL, `dummyfloatingip` varchar(255) NOT NULL, PRIMARY KEY (`nat_gateway_sc_snat_rule_id`,`dummyfloatingip`), CONSTRAINT `sc_snat_rules_floatingip` FOREIGN KEY (`nat_gateway_sc_snat_rule_id`) REFERENCES `nat_gateway_sc_snat_rules` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
备注：副表内的dummyfloatingip作为主表的一个list形式的字段
ORM model：
主表：
class NatGatewayScSnatRule(model_base.BASEV2, model_base.HasId, model_base.HasProject): '''Represents a v2 nat gateway sc snat rules''' __tablename__ = "nat_gateway_sc_snat_rules" nat_gateway_id = sa.Column(sa.String(36)) name = sa.Column(sa.String(255), nullable=True) description = sa.Column(sa.String(255), nullable=True) subnet_cidr = sa.Column(sa.String(64), nullable=False) fip_ip = sa.Column(sa.String(64), nullable=False) fip_bandwidth = sa.Column(sa.Integer(), nullable=False) dummyfloatingips = orm.relationship(NatGatewayScSnatRuleDummyFloatingIps, backref='nat_gateway_sc_snat_rule', cascade='all, delete, delete-orphan')
副表：
class NatGatewayScSnatRuleDummyFloatingIps(model_base.BASEV2): """Internal representation of a Az-connection Peer Cidrs.""" __tablename__ = "nat_gateway_sc_snat_rule_dummyfloatingips" nat_gateway_sc_snat_rule_id = sa.Column(sa.String(36), sa.ForeignKey('nat_gateway_sc_snat_rules.id', ondelete="CASCADE"), nullable=False, primary_key=True) dummyfloatingip = sa.Column(sa.String(64), nullable=False, primary_key=True)
增：
def _create_sc_snat_rule_dummyfloatingips_db(self, context, ng_sc_snat_rule_id, dummyfloatingips_list): """Write dummyfloatingips list to table scsnatruledummyfloatingips""" for dummyfloatingip in dummyfloatingips_list: with context.session.begin(subtransactions=True): sc_snat_rule_dummyfloatingips_db = NatGatewayScSnatRuleDummyFloatingIps( nat_gateway_sc_snat_rule_id=ng_sc_snat_rule_id, dummyfloatingip=dummyfloatingip) context.session.add(sc_snat_rule_dummyfloatingips_db) def _create_nat_gateway_sc_snat_rule_db(self, context, sc_snat_rule, project_id): """Create nat gateway sc snat rule db object.""" dummyfloatingips = sc_snat_rule.get('dummyfloatingips') with context.session.begin(subtransactions=True): ng_sc_snat_rule_db = NatGatewayScSnatRule( id=uuidutils.generate_uuid(), project_id=project_id, name=sc_snat_rule['name'], description=sc_snat_rule['description'], nat_gateway_id=sc_snat_rule['nat_gateway_id'], subnet_cidr=sc_snat_rule['subnet_cidr'], fip_ip=sc_snat_rule['fip_ip'], fip_bandwidth=sc_snat_rule['fip_bandwidth'] ) context.session.add(ng_sc_snat_rule_db) if dummyfloatingips: self._create_sc_snat_rule_dummyfloatingips_db( context, ng_sc_snat_rule_db['id'], dummyfloatingips) return ng_sc_snat_rule_db def _create_nat_gateway_sc_snat_rule(self, context, sc_snat_rule, validate=True): ng_sc_s_rule = sc_snat_rule['nat_gateway_sc_snat_rule'] project_id = context.project_id with context.session.begin(subtransactions=True): ng_sc_snat_rule = self._create_nat_gateway_sc_snat_rule_db( context, ng_sc_s_rule, project_id) return self._make_sc_snat_rule_dict(ng_sc_snat_rule) def create_nat_gateway_sc_snat_rule(self, context, nat_gateway_sc_snat_rule): """Create a sc snat rule on the specific nat gateway :param context: Neutron request context :param nat_gateway_sc_snat_rule: data for the sc snat rule being created :return: A sc snat rule object on success """ return self._create_nat_gateway_sc_snat_rule( context, nat_gateway_sc_snat_rule)
查：
def _make_sc_snat_rule_dummyfloatingips_dict( self, nat_gateway_sc_snat_rule_dummyfloatingips, fields=None): res = { 'nat_gateway_sc_snat_rule_id': nat_gateway_sc_snat_rule_dummyfloatingips['nat_gateway_sc_snat_rule_id'], 'dummyfloatingips': nat_gateway_sc_snat_rule_dummyfloatingips['dummyfloatingip'] } return self._fields(res, fields) def _make_sc_snat_rule_dict(self, sc_snat_rule, fields=None): dummyfloatingips = list() for item in sc_snat_rule.dummyfloatingips: item = self._make_sc_snat_rule_dummyfloatingips_dict(item) dummyfloatingips.append(str(item['dummyfloatingips']).strip()) res = { 'id': sc_snat_rule['id'], 'project_id': sc_snat_rule['project_id'], 'name': sc_snat_rule['name'], 'description': sc_snat_rule['description'], 'nat_gateway_id': sc_snat_rule['nat_gateway_id'], 'subnet_cidr': sc_snat_rule['subnet_cidr'], 'fip_ip': sc_snat_rule['fip_ip'], 'fip_bandwidth': sc_snat_rule['fip_bandwidth'], 'dummyfloatingips': dummyfloatingips } return self._fields(res, fields) def _get_nat_gateway_sc_snat_rule(self, context, sc_snat_rule_id): try: print(sc_snat_rule_id) ng_sc_snat_rule = self._get_by_id(context, NatGatewayScSnatRule, sc_snat_rule_id) except Exception as ex: print(ex) # raise ext_ng.NatGatewayScSnatRuleNotFound( # sc_snat_rule_id=sc_snat_rule_id) return ng_sc_snat_rule def get_nat_gateway_sc_snat_rules(self, context, filters=None, fields=None, sorts=None, limit=None, marker=None, page_reverse=False): marker_obj = self._get_marker_obj(context, 'nat_gateway_sc_snat_rule', limit, marker) return self._get_collection(context, NatGatewayScSnatRule, self._make_sc_snat_rule_dict, filters=filters, fields=fields, sorts=sorts, limit=limit, marker_obj=marker_obj, page_reverse=page_reverse) def get_nat_gateway_sc_snat_rule(self, context, id, fields=None): ng_sc_snat_rule = self._get_nat_gateway_sc_snat_rule(context, id) print(ng_sc_snat_rule) return self._make_sc_snat_rule_dict(ng_sc_snat_rule, fields)
改：
def _update_nat_gateway_sc_snat_rule_dummyfloatingips_db(self, context, nat_gateway_sc_snat_rule_id, dummyfloatingips_new, dummyfloatingips_list_from_db, mode): """Update table nat_gateway_sc_snat_rule_dummyfloatingips First step to remove all rows for given nat_gateway_sc_snat_rule id Then create new rows for given dummyfloatingips_list """ if mode == '': self._remove_nat_gateway_sc_snat_rule_dummyfloatingips(context, nat_gateway_sc_snat_rule_id) LOG.info("Remove all old nat_gateway_sc_snat_rule_dummyfloatingips from %s", nat_gateway_sc_snat_rule_id) self._create_sc_snat_rule_dummyfloatingips_db( context, nat_gateway_sc_snat_rule_id, dummyfloatingips_new) LOG.info("Create new nat_gateway_sc_snat_rule_dummyfloatingips for %s", nat_gateway_sc_snat_rule_id) elif mode == 'add': add_dummyfloatingips = set(dummyfloatingips_new) - set(dummyfloatingips_list_from_db) if add_dummyfloatingips: self._create_nat_gateway_sc_snat_rule_dummyfloatingips( context, nat_gateway_sc_snat_rule_id, list(add_dummyfloatingips)) elif mode == 'del': del_dummyfloatingips = set(dummyfloatingips_new) & set(dummyfloatingips_list_from_db) if del_dummyfloatingips: self._delete_nat_gateway_sc_snat_rule_dummyfloatingips(context, nat_gateway_sc_snat_rule_id, list(del_dummyfloatingips)) def _update_nat_gateway_sc_snat_rule_db(self, context, nat_gateway_sc_snat_rule_id, data): """Update nat_gateway_sc_snat_rule DB object.""" nat_gateway_sc_snat_rule_data_update = dict() with context.session.begin(subtransactions=True): nat_gateway_sc_snat_rule_db = self._get_nat_gateway_sc_snat_rule( context, nat_gateway_sc_snat_rule_id) if 'name' in data: nat_gateway_sc_snat_rule_data_update['name'] = data.get('name') if 'description' in data: nat_gateway_sc_snat_rule_data_update['description'] = data.get('description') if 'nat_gateway_id' in data: nat_gateway_sc_snat_rule_data_update['nat_gateway_id'] = data.get('nat_gateway_id') if 'subnet_cidr' in data: nat_gateway_sc_snat_rule_data_update['subnet_cidr'] = data.get('subnet_cidr') if 'fip_ip' in data: nat_gateway_sc_snat_rule_data_update['fip_ip'] = data.get('fip_ip') if 'fip_bandwidth' in data: nat_gateway_sc_snat_rule_data_update['fip_bandwidth'] = data.get('fip_bandwidth') nat_gateway_sc_snat_rule_db.update(nat_gateway_sc_snat_rule_data_update) return nat_gateway_sc_snat_rule_db def _update_nat_gateway_sc_snat_rule(self, context, nat_gateway_sc_snat_rule_id, nat_gateway_sc_snat_rule_new, validation=True): nat_gateway_sc_snat_rule_data = nat_gateway_sc_snat_rule_new['nat_gateway_sc_snat_rule'] dummyfloatingip_new = nat_gateway_sc_snat_rule_data.get('dummyfloatingips') mode = nat_gateway_sc_snat_rule_data.get('mode', '') dummyfloatingips_list_from_db = self._get_nat_gateway_sc_snat_rule_and_dummyfloatingips( context, nat_gateway_sc_snat_rule_id) if validation: if dummyfloatingip_new: self._update_nat_gateway_sc_snat_rule_dummyfloatingips_db( context, nat_gateway_sc_snat_rule_id, dummyfloatingip_new, dummyfloatingips_list_from_db, mode) nat_gateway_sc_snat_rule_updated = self._update_nat_gateway_sc_snat_rule_db( context, nat_gateway_sc_snat_rule_id, nat_gateway_sc_snat_rule_data) return self._make_sc_snat_rule_dict(nat_gateway_sc_snat_rule_updated) def update_nat_gateway_sc_snat_rule(self, context, id, nat_gateway_sc_snat_rule): """Update a nat_gateway_sc_snat_rule :param context: Neutron request context :param id: id of nat_gateway_sc_snat_rule being updated :param nat_gateway_sc_snat_rule: data for nat_gateway_sc_snat_rule being updated :return: nat_gateway_sc_snat_rule info dict after been updated """ return self._update_nat_gateway_sc_snat_rule(context, id, nat_gateway_sc_snat_rule)
删：
def _db_delete(self, context, item): with context.session.begin(subtransactions=True): context.session.delete(item) def delete_nat_gateway_sc_snat_rule(self, context, id): """Delete a nat_gateway_sc_snat_rule cascade delete associated dummyfloatingip resource in table nat_gateway_sc_snat_rule_dummyfloatingips . :param context: Neutron request context :param id: id of nat_gateway_sc_snat_rule being deleted """ nat_gateway_sc_snat_rule_db = self._get_nat_gateway_sc_snat_rule(context, id) self._db_delete(context, nat_gateway_sc_snat_rule_db)
总结：
1.增改时：需要对副表的字段进行操作，目前改操作时，执行先删除后插入，其逻辑可以根据传入字段进行分辨
2.删除时，会连同副表相关的数据一起删除。
浙公网安备 33010602011771号