在 web2py 中批量插入或更新 [英] Bulk insert or update in web2py

问题描述

我知道在 web2py 中有像 bulk_insert 和 insert_or_update 这样的方法.前者一次插入多条记录，而后者处理重复的主/唯一键插入条件(ON DUPLICATE KEY UPDATE).我想做一个批量插入，同时还要确保重复的关键条件.web2py 中是否有类似 bulk_insert_or_update 的东西?如果没有，我怎样才能实现它?

I know there are methods like bulk_insert and insert_or_update in web2py. The former inserts multiple records at one go while latter handles duplicate primary/unique key insert conditions(ON DUPLICATE KEY UPDATE). I want to do a bulk insert while also ensuring duplicate key condition. Is there anything like bulk_insert_or_update in web2py? If not, how could I achieve it?

我写了以下查询，我想使用类似 bulk_insert_or_update

I have written following query which I want to use something like bulk_insert_or_update

db.mcexlinker.insert_or_update(
db(db.mcex.example.like(‘%’+db.mc.element+’%’)).select(db.mc.id,db.mcex.id)
)

更新

我的表定义如下:

Update

My table definitions are as given below:

CREATE TABLE `mc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `element` varchar(30) NOT NULL,
  `locale` int(11) NOT NULL,
  `synind` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_mc_locale` (`locale`),
  KEY `fk_mc_synind` (`synind`),
  CONSTRAINT `fk_mc_synind` FOREIGN KEY (`synind`) REFERENCES `mc` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_mc_locale` FOREIGN KEY (`locale`) REFERENCES `locale` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=latin1;

CREATE TABLE `mcex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `example` varchar(200) NOT NULL,
  `ranking` int(11) DEFAULT NULL,
  `enteredby` int(11) DEFAULT NULL,
  `verificationstatus` int(11) DEFAULT '0' COMMENT '0-unverified,1-verified',
  PRIMARY KEY (`id`),
  UNIQUE KEY `example_UNIQUE` (`example`)
) ENGINE=InnoDB AUTO_INCREMENT=6233 DEFAULT CHARSET=latin1;

CREATE TABLE `mcexlinker` (
  `id` int(11) NOT NULL,
  `mcid` int(11) NOT NULL,
  `exampleid` int(11) NOT NULL,
  `linkstatus` int(11) NOT NULL COMMENT '0-new,1-verified',
  PRIMARY KEY (`id`),
  KEY `fk_mclinker_element` (`mcid`),
  KEY `fk_mcexlinker_example` (`exampleid`),
  CONSTRAINT `fk_mcexlinker_element` FOREIGN KEY (`mcid`) REFERENCES `mc` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_mcexlinker_example` FOREIGN KEY (`exampleid`) REFERENCES `mcex` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表 mc 和 mcex 通过 mcexlinker 表链接成多对多关系.表 mc 和 mcex 将独立填充.在某些时候，计划作业将运行并通过检查看起来像元素(mc 表元素列)的示例(mcex table-example 列)并获取它们的 id 并将它们插入到 mcexlinker 表中来链接这些表中的新条目.如果您需要更多信息，请告诉我.

Tables mc and mcex are linked through mcexlinker table to have many-to-many relationship. Table mc and mcex are going to be populated independently. At certain point a scheduled job will run and link new entries in these tables by checking examples(mcex table-example column) which look like elements(mc table-element column) and taking their ids and inserting them in mcexlinker table. Please let me know if you need more information.

推荐答案

除非您使用 Google App Engine，否则使用 bulk_insert 比简单地循环和单独插入没有任何优势.因此，您不妨在循环或列表推导中使用 update_or_insert.

Unless you are on Google App Engine, there is no advantage to using bulk_insert over simply looping and doing individual inserts. So, you might as well just use update_or_insert in a loop or list comprehension.

另外，'%'+db.mc.element+'%' 将不起作用，因为 dc.mc.element 是一个 Field> 对象，而不是字符串.

Also, ‘%’+db.mc.element+’%’ will not work, as dc.mc.element is a Field object, not a string.

更新:

根据您更新的问题，您可以使用此解决方案，但如果您不希望它成为数据库依赖，你可以在 Python 中做一些循环，如下所示:

Based on your updated question, you could use this solution, but if you don't want it to be database dependent, you could do some looping in Python as follows:

for example in db(db.mcex).select(db.mcex.id, db.mcex.example):
    matches = db(db.mc.element.contains(example.example)).select(db.mc.id)
    for match in matches:
        db.mcexlinker.update_or_insert(mcid=match.id, exampleid=example.id)

你也可以做一些测试，看看在 Python 中进行匹配是否更有效:

You can also do some testing to see if it is more efficient to do the matching in Python:

elements = db(db.mc).select(db.mc.id, db.mc.element)
for example in db(db.mcex).select(db.mcex.id, db.mcex.example):
    for element in elements:
        if example.example in element.element:
            db.mcexlinker.update_or_insert(mcid=element.id, exampleid=example.id)

这篇关于在 web2py 中批量插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！