Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL INSERT … ON DUPLICATE KEY UPDATE with django 1.4 for bulk insert
import datetime
from pprint import pprint
from django.db import models, signals
from django.utils import timezone
import django.dispatch
class BulkInsertManager(models.Manager):
_done = django.dispatch.Signal(providing_args=[])
def _bulk_insert_or_update(self, create_fields, update_fields, values, print_sql=False):
from django.db import connection, transaction
cursor = connection.cursor()
db_table = self.model._meta.db_table
values_sql = []
values_data =[]
for value_lists in values:
values_sql.append( "(%s)" % (','.join([ "%s" for i in range(len(value_lists))]),) )
values_data.extend(value_lists)
base_sql = "INSERT INTO %s (%s) VALUES " % (db_table, ",".join(create_fields))
on_duplicates = []
for field in update_fields:
on_duplicates.append(field + "=VALUES(" + field +")")
sql = "%s %s ON DUPLICATE KEY UPDATE %s" % (base_sql, ", ".join(values_sql), ",".join(on_duplicates))
try:
cursor.executemany(sql, [values_data])
if print_sql is True:
print cursor._last_executed
transaction.commit_unless_managed()
return True
except Exception as e:
print e
return False
# issue a signal when bulk insert is complete
def _post_save(self, **kwargs):
self._done.send(sender=self.model, **kwargs)
# return the fields that can be updated for
def _get_update_fields(self):
# fields that can not be updated
unique_fields = list(list(unique_field) for unique_field in self.model._meta.unique_together)[0]
unique_fields.append(self.model._meta.pk.column) # include the primary key field to the unique fields list
return list(set(list( field.column for field in self.model._meta.fields)) - set(unique_fields))
def save_user_friends(user, friends):
user_friends = []
for friend in friends:
create_fields = ['id', 'user_id', 'first_name', 'last_name', 'city', 'province', 'country']
update_fields = ['first_name', 'last_name', 'city', 'province', 'country']
user_friends.append(
[
str(user.id),
str(friend['id']),
friend['first_name'],
friend['last_name'],
friend['city'],
friend['province'],
friend['country'],
]
)
User_Friend.objects._bulk_insert_or_update(create_fields, update_fields, user_friends)
User_Friend.objects.Photo.objects._post_save([]) # pass any kwargs of your choice
from django.db import models
class User_Friend(models.Model):
objects = BulkInsertManager() # assign a custom manager to handle bulk insert
id = models.CharField(max_length=255)
user = models.ForeignKey(User, null=False, blank=False)
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=30)
city = models.CharField(max_length=50, null=True, blank=True)
province = models.CharField(max_length=50, null=True, blank=True)
country = models.CharField(max_length=30, null=True, blank=True)
from django.utils import simplejson as json
from django.db.models.signals import pre_save
from django.dispatch import receiver
import models as m
@receiver(m.bulk_insert_done, sender=m.User_Friend)
def bulk_photo_insert_handler(sender, **kwargs):
print "do your signal things with kwargs"
print kwargs
@mmohiudd

This comment has been minimized.

Copy link
Owner Author

@mmohiudd mmohiudd commented Oct 26, 2012

Now the custom manager has a built in signal dispatcher for bulk insert. Import signals to app's init.py to initiate the signal receiver or wherever it seems appropriate.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.