Reducing number of repeated queries in Django when creating new objects in a loop

Question

I'm populating a database using Django from an Excel sheet. In the example below, the purchases list simulates the format of that sheet.

My problem is that I'm repeatedly using Customer.objects.get to retrieve the same Customer object when creating new Purchase entries. This results in repeated database queries for the same information.

I've tried using customers = Customer.objects.all() to read in the whole Customer table into memory, but that doesn't seem to have worked since customers.get() still creates a new database query each time.

from django.db import models

# My classes
class Customer(models.Model):
    name = models.CharField("Name", max_length=50, unique=True)

class Item(models.Model):
    name = models.CharField("Name", max_length=50, unique=True)

class Purchase(models.Model):
    customer = models.ForeignKey("Customer", on_delete=models.CASCADE)
    item = models.ForeignKey("Customer", on_delete=models.CASCADE)

# My data
purchase_table =[
    {'customer': 'Joe', 'item': 'Toaster'},
    {'customer': 'Joe', 'item': 'Slippers'},
    {'customer': 'Jane', 'item': 'Microwave'}
]

# Populating DB
for purchase in purchase_table:
    
    # Get item and customer (this is where the inefficient use of 'get' takes place)
    item = Item.objects.get(name=purchase['item'])
    customer = Customer.objects.get(name=purchase['customer'])
    
    # Create new purchase
    Purchase(customer, item).save()

Tomer Ariel · Accepted Answer · 2023-01-03 20:13:30Z

1

If you're dealing with small enough data to read into memory, then why not use .all() like you mentioned? That should do the trick.

Something like:

items = {item.name: item for item in Item.objects.all()}
customers = {customer.name: customer for customer in Customer.objects.all()}

purchases = [Purchase(customers[c], items[i]) for c, i in zip(items, customers)]
Purchase.objects.bulk_create(purchases)

edited Jan 3, 2023 at 20:13

answered Jan 3, 2023 at 17:03

Tomer Ariel

1,5557 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Plowright Over a year ago

Thanks, that worked. One small thing, in the first two lines, I think it needs to be {item.name : item...} and {customer.name: customer ...}

Sandeep Pulipati · Accepted Answer · 2023-01-03 17:39:15Z

There could be a better way, but i would have done this way to minimize database transactions.

from django.db import transaction

# My data
purchase_table =[
    {'customer': 'Joe', 'item': 'Toaster'},
    {'customer': 'Joe', 'item': 'Slippers'},
    {'customer': 'Jane', 'item': 'Microwave'}
]

# I am assuming name field is unique in Item as well as Customer
customers = {customer['name']: customer['id'] for customer in Customer.objects.values()}

items = {item['name']: item['id'] for item in Items.objects.values()}

with transaction.atomic():    
    for purchase in purchase_table:
        customer = customers.get(purchase['customer'])
        item = items.get(purchase['item'])
        if item and customer:
            Purchase(customer_id=customer, item_id=item).save()

Refer transaction.atomic for more details.

Collectives™ on Stack Overflow

Reducing number of repeated queries in Django when creating new objects in a loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related