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

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()

CodePudding user response：

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)

CodePudding user response：

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.