Creating many related objects like INSERT ... SELECT in SQL

Question

I'm sending out messages and I need to keep track of each message being sent out, when, to whom, etc.

So I have the recipients in a an extra table model besides the message, and I need to populate the recipients every time I create a new message. The Recipient of a message will be populated from a third model, which contains all the current e-mail addresses that I want to sent out to.

So my question is how I would go about this the most efficient way?
I know I can do something similar to:

m = Message.objects.create(*args)
for email in ModelWithEmails.active.values_list('email', flat=True):
    Recipient.objects.create(message=m, email=email)

But that will still involve getting all the e-mail addresses out of the database and I would like to keep it all inside of the database if possible, as there's several thousand addresses that will be fetched every time.

Answer 1

You can't do INSERT .. SELECT with django ORM, but you can do a bulk insert (since django 1.4):

m = Message.objects.create(*args)
recipients = []
for email in ModelWithEmails.active.values_list('email', flat=True):
    recipients.append(Recipient(message=m, email=email))

Recipient.objects.bulk_create(recipients)

  Or a tiny bit more efficient:

m = Message.objects.create(*args)
emails = ModelWithEmails.active.values_list('email', flat=True)
Recipient.objects.bulk_create([Recipient(message=m, email=email) for email in emails])

 

For INSERT .. SELECT you'll have to fall back to raw SQL.

Answer 2

Django ORM doesn't need user to use raw sql any more. It is very convenient, but it may be not very flexible. If you want to use ORM, bulk_create will be your friend, just as Pavel Anossov said.