Uploading csv with ForeignKey field

Using Django Using the ORM
1

Created a function in admin.py to iterate over rows in a csv file. The csv file has “Budget Item” that contains the text value of the ForeignKey (FK) in the model I’m mapping to. I have to change the text values to the numeric id of the FK.

for ind, row in df.iterrows():
                created = Ledger.objects.update_or_create(
                    date = row['Date'],
                    description = row['Description'],
                    # Field 'id' expected a number but got 'Administration'
                    budget_item = row['Budget Item'],

How would one replace the id number with the text value in the csv file for importing to the FK field?

@admin.register(Ledger)
class LedgerAdmin(admin.ModelAdmin):
    list_display = ('date',
                    'budget_item',
                    'description',
                    'credit',
                    'debit',
                    'reconciled')
    
    # for the CSV upload
    def get_urls(self):
        urls = super().get_urls()
        new_urls = [path('upload_csv/', self.upload_csv),]
        return new_urls + urls
    
    # CSV Upload
    def upload_csv(self, request):
        
        if request.method == "POST":
            csv_file = request.FILES["csv_upload"]

            if not csv_file.name.endswith('.csv'):
                messages.warning(request, 'Error, CSV File required.')
                return HttpResponseRedirect(request.path_info)
        
            # Using Pandas to clean the file.
            df = pd.read_csv(csv_file)
            print(df)
            items = Ledger.objects.all()
            print(items)
            # csv to DB model mapping without checking the data. You'll need to make sure 
            # the file has been cleaned.
            # @ Add a data checking routine.
            for ind, row in df.iterrows():
                created = Ledger.objects.update_or_create(
                    date = row['Date'],
                    description = row['Description'],
                    # Field 'id' expected a number but got 'Administration'
                    budget_item = row['Budget Item'],
                    #budget_item_id = row['Budget Item'],
                    #budget_item = Ledger.objects.get(budget_item=row['Budget Item']),
                    credit = row['Credit'],
                    debit = row['Debit'],)
            url = reverse('admin:index')
            return HttpResponseRedirect(url)
        # CsvImportForm() added above
        form = CsvImportForm()
        data = {"form": form}
        return render(request, 'admin/csv_upload.html', data)
2

You can use a query to search for any field within a model.

For example, if you have a model:

class Budget(Model):
  item = CharField(...)

(with the implicit id field as well)

You can then query on item as Budget.objects.get(item=row['Budget Item']) and retrieve the id field from the returned object.
e.g.
budget_item_id = Budget.objects.get(item=row['Budget Item']).id

I see where you appear to have tried something like this - it would be helpful to know what errors were thrown with that approach. (What appears wrong to me is that you’re trying to run this query on the table in which you’re trying to insert data. That seems to me to be the wrong source for this information.)

3

When text a text value of “Administration” is in the “Budget Item” column in the csv file using

for ind, row in df.iterrows():
                created = Ledger.objects.update_or_create(
                    date = row['Date'],
                    description = row['Description'],
                    # Field 'id' expected a number but got 'Administration'
                    budget_item = row['Budget Item'],
                    #budget_item_id = row['Budget Item'],
                    #budget_item = Ledger.objects.get(budget_item=row['Budget Item']),
                    credit = row['Credit'],
                    debit = row['Debit'],)
            url = reverse('admin:index')
            return HttpResponseRedirect(url)

The error thrown is

Field 'id' expected a number but got 'Administration'

How does one match the text value of the csv file for import, with the model’s FK field?

class Ledger(models.Model):
    date = models.DateField('Date', auto_now = False, unique=False)
    budget_item = models.ForeignKey('BudgetItem', on_delete=models.CASCADE)
    description = models.CharField(max_length=100, blank=True)
    credit = models.DecimalField(max_digits= 8, decimal_places=2)
    debit = models.DecimalField(max_digits= 8, decimal_places=2)
4

What does the BudgetItem model look like?

Or, more accurately, what source of data do you have that associates the text ‘Administration’ with the corresponding id in BudgetItem? Is that text value stored in BudgetItem?

5

Yes, that’s the problem. ID numbers corresponding to text items is difficult to manage. I’m in the development stage and making changes to the model. So when I have to delete the table data to make a changes I have to either manually enter a all the records or better, upload a csv file.
Each “Budget Item” has a corresponding id in the model. The spreadsheet has text values in the “Budget Item” column but have to match the id numbers in the model.

6

So that’s the purpose and function of the query above.

If item_text is the name of the field in BudgetItem that contains the text,

returns the row in BudgetItem matching that text value.

7

Ok, thanks Ken. I’ll give that a try.