CODEWITHSUNDEEP
web2py
user3352330
user3352330

Reputation:

Bulk insert or update in web2py

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?

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;

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.

Upvotes: 0

Views: 1552

Answers (2)

olorunshola matins
olorunshola matins

Reputation: 89

You can try this:

http://127.0.0.1:8000/Melinastoreservice/default/test?data={'name':'emma','age':'23'},{'name':'emma','age':'23'}

@request.restful()
def test():
    response.view = 'generic.json'

    response.headers["Access-Control-Allow-Origin"] = '*'
    response.headers['Access-Control-Max-Age'] = 86400
    response.headers['Access-Control-Allow-Headers'] = '*'
    response.headers['Access-Control-Allow-Methods'] = '*'
    response.headers['Access-Control-Allow-Credentials'] = 'true'

    def GET(data):

        import ast

        sett = ast.literal_eval(data)

        save = db.test.bulk_insert(sett)

        return response.json(save)

    return locals()

Upvotes: 0

Anthony
Anthony

Reputation: 25536

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.

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

UPDATE:

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)

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)

Upvotes: 1

Related Questions