python 3.x – How to extract the string or name of all foreign keys used in a model in django-pandas dataframe

I have data stored in a Django Model/Table which I am fetching in pandas dataframe via django-pandas (Version: 0.6.6)

The model has 3 columns as foreign key viz: state_name, district_name and block_name

The model.py is as:

####################################################################################

BOOL_CHOICES = ((True, 'Yes'), (False, 'No'))
UNIT_CATEGORIES = (('AMFU', 'AMFU'), ('DAMU', 'DAMU'))
  
class GroupDetail(models.Model):
    """Village level Whatsapp Information options"""

    unit_type = models.CharField(max_length=16, choices=UNIT_CATEGORIES, default="DAMU")
    state_name = models.ForeignKey(State, to_field='state_name', on_delete=models.SET_NULL, null=True)
    unit_name = models.CharField(max_length=100, default="KVK")
    district_name = models.ForeignKey(District, to_field='district_name', on_delete=models.SET_NULL, null=True)
    block_name = models.ForeignKey(Block, to_field='block_name', on_delete=models.SET_NULL, null=True)
    village_name = models.CharField(max_length=150)
    village_covered_through_whatsapp_group = models.BooleanField(verbose_name=_('Village Covered through Whatsapp Group "yes/no"'), choices=BOOL_CHOICES)
    number_of_whatsapp_group_created = models.PositiveIntegerField(validators=[MinValueValidator(0), MaxValueValidator(100)])
    number_of_farmers_covered_in_the_village = models.PositiveIntegerField(validators=[MinValueValidator(1), MaxValueValidator(1000)])

    objects = DataFrameManager()
    class Meta:
        db_table="Group_Info_Table"
        constraints = [models.UniqueConstraint(fields=["state_name","district_name", "block_name", "village_name"], name="all_keys_unique_together")]



    def __str__(self):
        return self.unit_type
        return self.state_name
        return self.unit_name
        return self.district_name
        return self.block_name
        return self.village_name
        return self.village_covered_through_whatsapp_group
        return self.number_of_whatsapp_group_created
        return self.number_of_farmers_covered_in_the_village

views.py is as:

Case 1:
PS: kindly look at string of column name passed : state_name__id

If I am fetching data like this, it returns id's of all columns having foreign keys

def amfu_state_wise(request):

    state_name_is = None 
    district_name_is = None 
    block_name_is = None 
    if request.method == "POST":
        state_name_is=request.POST.get('state')
        district_name_is=request.POST.get('district')
        block_name_is=request.POST.get('block')

    fetch_whole_database = GroupDetail.objects.all()

    all_fields = GroupDetail._meta.get_fields()
    print("all_fields",all_fields)

    fetch_data_df = fetch_whole_database.to_dataframe(['unit_type', 'state_name__id', 'unit_name', 'district_name__id', 'block_name__id', 'village_name', 'village_covered_through_whatsapp_group', 'number_of_whatsapp_group_created', 'number_of_farmers_covered_in_the_village'], index='id')

    print("fetch_data_df", fetch_data_df.state_name__id.head(5))

The output in console is as

all_fields id
31618    11
31619    11
31620    11
31621    11
31622    11
Name: state_name__id, dtype: int64
[15/Jun/2022 12:37:36] "GET /damuwhatsapp/amfu_state_wise HTTP/1.1" 200 16811

Case 2:

But when I filter all object of model, it retures NaN value

PS: kindly look at string of column name passed : state_name__name

def amfu_state_wise(request):

    state_name_is = None 
    district_name_is = None 
    block_name_is = None 
    if request.method == "POST":
        state_name_is=request.POST.get('state')
        district_name_is=request.POST.get('district')
        block_name_is=request.POST.get('block')

    fetch_whole_database = GroupDetail.objects.filter().values().all()

    all_fields = GroupDetail._meta.get_fields()
    print("all_fields",all_fields)

    fetch_data_df = fetch_whole_database.to_dataframe(['unit_type', 'state_name__name', 'unit_name', 'district_name__id', 'block_name__id', 'village_name', 'village_covered_through_whatsapp_group', 'number_of_whatsapp_group_created', 'number_of_farmers_covered_in_the_village'], index='id')

    print("fetch_data_df", fetch_data_df.state_name__name.head(5))

The output in console is as

all_fields id
31618   NaN
31619   NaN
31620   NaN
31621   NaN
31622   NaN
Name: state_name__name, dtype: float64
[15/Jun/2022 12:44:24] "GET /damuwhatsapp/amfu_state_wise HTTP/1.1" 200 16811

Case 3:

But when I use
fetch_whole_database = GroupDetail.objects.filter.all()
or
fetch_whole_database = GroupDetail.objects.all()

PS: kindly look at string of column name passed : state_name

def amfu_state_wise(request):

    state_name_is = None 
    district_name_is = None 
    block_name_is = None 
    if request.method == "POST":
        state_name_is=request.POST.get('state')
        district_name_is=request.POST.get('district')
        block_name_is=request.POST.get('block')

    fetch_whole_database = GroupDetail.objects.filter.all()


    all_fields = GroupDetail._meta.get_fields()
    print("all_fields",all_fields)

    fetch_data_df = fetch_whole_database.to_dataframe(['unit_type', 'state_name', 'unit_name', 'district_name__id', 'block_name__id', 'village_name', 'village_covered_through_whatsapp_group', 'number_of_whatsapp_group_created', 'number_of_farmers_covered_in_the_village'], index='id')

    print("fetch_data_df", fetch_data_df.state_name.head(5))

The foreign key string HARYANA is returned with error as :


    condition = self.build_lookup(lookups, col, value)
  File "/home/webtesting/django_venv/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1273, in build_lookup
    lookup = lookup_class(lhs, rhs)
  File "/home/webtesting/django_venv/lib/python3.9/site-packages/django/db/models/lookups.py", line 27, in __init__
    self.rhs = self.get_prep_lookup()
  File "/home/webtesting/django_venv/lib/python3.9/site-packages/django/db/models/lookups.py", line 275, in get_prep_lookup
    rhs_value = self.lhs.output_field.get_prep_value(rhs_value)
  File "/home/webtesting/django_venv/lib/python3.9/site-packages/django/db/models/fields/__init__.py", line 1990, in get_prep_value
    raise e.__class__(
ValueError: Field 'id' expected a number but got 'HARYANA'.
[15/Jun/2022 13:18:35] "GET /damuwhatsapp/amfu_state_wise HTTP/1.1" 500 159434

So how to get the string of all columns with foreign keys in django-pandas Dataframe?

Leave a Comment