Export filtered query as CSV

dwatt · June 7, 2023, 8:05am

I’ve added an export CSV function to my page. I would like it to retain the filtered data that is rendered when the user submits the form, however, the export is downloading all rows rather than the filtered rows. The view is returning the correct filtered rows, but not when exporting.

Views


class PropertyListView(View):

    def get(self, request):
        form = PropertyFilterForm()
        context = {
            'form': form,
        }
        return render(request, 'listings/property-list.html', context)

    def post(self, request):
        form = PropertyFilterForm(request.POST)
        if form.is_valid():
            start_date = form.cleaned_data['start_date']
            end_date = form.cleaned_data['end_date']
            suburb = form.cleaned_data['suburb']
            state = form.cleaned_data['state']
            postcode = form.cleaned_data['postcode']

            listings = PropertyListing.objects.all().order_by('-date_sold')

            if start_date and end_date:
                listings = listings.filter(date_sold__range=[start_date, end_date])
            if suburb:
                listings = listings.filter(suburb__icontains=suburb)
            if state:
                listings = listings.filter(state__icontains=state)
            if postcode:
                listings = listings.filter(postcode__icontains=postcode)

            context = {
                'form': form,
                'listings': listings,
            }

            # Check if export to CSV is requested
            if 'export_csv' in request.POST:
                response = self.export_to_csv()
                return response

            return render(request, 'listings/property-list.html', context)

        # Form is invalid, render the form again
        context = {
            'form': form,
        }
        return render(request, 'listings/property-list.html', context)


def export_csv(request):
    form = PropertyFilterForm(request.POST)

    if form.is_valid():

        start_date = form.cleaned_data['start_date']
        end_date = form.cleaned_data['end_date']
        suburb = form.cleaned_data['suburb']
        state = form.cleaned_data['state']
        postcode = form.cleaned_data['postcode']

        listings = PropertyListing.objects.all().order_by('-date_sold')

        if start_date and end_date:
            listings = listings.filter(date_sold__range=[start_date, end_date])
        if suburb:
            listings = listings.filter(suburb__icontains=suburb)
        if state:
            listings = listings.filter(state__icontains=state)
        if postcode:
            listings = listings.filter(postcode__icontains=postcode)

        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = 'attachment; filename="property_listings.csv"'

        writer = csv.writer(response)
        writer.writerow(['ID', 'Date Sold', 'Sold By', 'Price Guide', 'Price Guide Info', 'Address',
                         '# of Beds', '# of Baths', '# of Parking', 'Size (m²)', 'House Type', 'Inspection',
                         'Full Address', 'Listing Type', 'Suburb', 'State', 'Postcode', 'Profile'])

        for listing in listings:
            writer.writerow([listing.id, listing.date_sold, listing.sold_by, listing.price_guide,
                             listing.price_guide_info, listing.address1, listing.num_of_beds, listing.num_of_baths,
                             listing.num_of_parking, listing.size_msq, listing.house_type, listing.inspection,
                             listing.full_address, listing.listing_type, listing.suburb, listing.state,
                             listing.postcode, listing.profile])

        return response
    else:
        return HttpResponseBadRequest()

FORMS


class PropertyFilterForm(forms.Form):
    start_date = forms.ChoiceField(label='Start Date', choices=[], required=False)
    end_date = forms.ChoiceField(label='End Date', choices=[], required=False)
    suburb = forms.ChoiceField(label='Suburb', choices=[], required=False)
    state = forms.ChoiceField(label='State', choices=[], required=False)
    postcode = forms.ChoiceField(label='Postcode', choices=[], required=False)

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.fields['start_date'].choices = self.get_date_choices()
        self.fields['end_date'].choices = self.get_date_choices()
        self.fields['suburb'].choices = self.get_suburb_choices()
        self.fields['state'].choices = self.get_state_choices()
        self.fields['postcode'].choices = self.get_postcode_choices()

    def get_date_choices(self):
        date_choices = PropertyListing.objects.values_list('date_sold', flat=True).distinct()
        formatted_choices = [
            (date.strftime('%Y-%m-%d'), date.strftime('%Y-%m-%d'))
            for date in date_choices
        ]
        return formatted_choices

    def get_suburb_choices(self):
        return [(suburb, suburb) for suburb in PropertyListing.objects.values_list('suburb', flat=True).distinct()]

    def get_state_choices(self):
        states = PropertyListing.objects.values_list('state', flat=True).distinct()
        return [(state, state) for state in states]

    def get_postcode_choices(self):
        return [(postcode, postcode) for postcode in PropertyListing.objects.values_list('postcode', flat=True).distinct()]

URLS

from django.urls import path
from .views import PropertyListView, export_csv

app_name = 'listings'

urlpatterns = [
    path('property-list/', PropertyListView.as_view(), name='property-list'),
    path('export-csv/', export_csv, name='export-csv'),
]

HTML

    <div style="display: flex; justify-content: center;">
        <a href="{% url 'listings:export-csv' %}" class="btn btn-primary">Export to CSV</a>
    </div>

plopidou · June 7, 2023, 1:24pm

several comments?

usually better (I think) to do:

suburb = form.cleaned_data.get('suburb', None) # or any other fallback

than

suburb = form.cleaned_data['suburb']

Also:

listings = PropertyListing.objects.all().order_by('-date_sold')

Having the order_by there… wondering if that could be your issue, and causing the queryset to completely ignore the subsequent filter() calls.

Try:

listings = PropertyListing.objects
# filters here
listings = listings.order_by('-date_sold')

?

last… maybe you could even do a values() call to make the CSV export faster (no object instantiation, etc). In which case you would get a list of dicts and no longer a list of objects.
so listing.id would become listing['id']

dwatt · June 8, 2023, 12:25am

Thanks for your response, noted for the speed tip. I’ve tried your suggestions and unfortunately they didn’t work. I believe the issue would possibly be in the export_csv() function (i’ve added comments):

def export_csv(request):
    # This seems to be taking all possible values rather then the users input.
    form = PropertyFilterForm(request.POST)

    if form.is_valid():
        # The form appears to be valid
        start_date = form.cleaned_data['start_date']
        end_date = form.cleaned_data['end_date']
        suburb = form.cleaned_data['suburb']
        state = form.cleaned_data['state']
        postcode = form.cleaned_data['postcode']

        # If I put a print statement here, start_date, ...., postcode all return blanks. 
        # I replaced with .get('start_date', None) and it still returned blanks.

        listings = PropertyListing.objects.all().order_by('-date_sold')

        if start_date and end_date:
            listings = listings.filter(date_sold__range=[start_date, end_date])
        if suburb:
            listings = listings.filter(suburb__icontains=suburb)
        if state:
            listings = listings.filter(state__icontains=state)
        if postcode:
            listings = listings.filter(postcode__icontains=postcode)

        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = 'attachment; filename="property_listings.csv"'

        writer = csv.writer(response)
        writer.writerow(['ID', 'Date Sold', 'Sold By', 'Price Guide', 'Price Guide Info', 'Address',
                         '# of Beds', '# of Baths', '# of Parking', 'Size (m²)', 'House Type', 'Inspection',
                         'Full Address', 'Listing Type', 'Suburb', 'State', 'Postcode', 'Profile'])

        # I'll change this to your dict suggestion after.
        for listing in listings:
            writer.writerow([listing.id, listing.date_sold, listing.sold_by, listing.price_guide,
                             listing.price_guide_info, listing.address1, listing.num_of_beds, listing.num_of_baths,
                             listing.num_of_parking, listing.size_msq, listing.house_type, listing.inspection,
                             listing.full_address, listing.listing_type, listing.suburb, listing.state,
                             listing.postcode, listing.profile])

        return response
    else:
        return HttpResponseBadRequest()

plopidou · June 8, 2023, 8:22am

several things again, I think.

have you tried to print(form.cleaned_data) and see what’s inside?
your indentation in the original post
Your def export_csv(request): should be indented so that it sits as a function of class PropertyListView(View):
Not sure if your code is like this or if this is just a typing mistake/glitch. Worth checking

In your OP too:

# Check if export to CSV is requested
if 'export_csv' in request.POST:
    response = self.export_to_csv() # <- should it be self.export_to_csv(request)?
    return response

The request argument seems to be missing from the self.export_to_csv call

Instead of if 'export_csv in def post:, why not use it as a “normal” form field, like the others?

export_csv = form.cleaned_data.get('export_csv', False);
if export_csv:
    self.export_csv(...)

Inside def export_to_csv, no need to repeat if form.is_valid(): that’s been done already in def post.

Inside def export_to_csv, I do not think there is any need to rebuild the listings queryset again. That is done just above too.
So try something like this?

# ... build listings queryset
# then send that to 
self.export_to_csv(listings)
def export_to_csv(listings):
    # do the csv stuff

or even, create a new def get_queryset(form) function where you can have a centralised function returning a queryset, reusable across many different scenarios (template rendeting, csv, excel, whatever). That can then be used in what I think is a nice an easy way, for example:

class PropertyListView(View):
    def get(self, request):
        return self.render_html(form, listings=[])
    def post(self, request):
        form = PropertyFilterForm(request.POST)
        if form.is_valid():
            listings = self.get_queryset(form)
            render_csv = form.cleaned_data.get('render_csv', False)
            if render_csv:
                return self.render_csv(listings=listings)
            return self.render_html(form, listings=listings)
        else:
            return self.render_html(form, listings=[])
    def get_queryset(form):
        start_date = form.cleaned_data.get('start_date', None)
        # other fields...
        listings = PropertyListing.objects
        filters = Q()
        if start_date:
            filters &= Q(start_date__gte=start_date)
        # other filters
        listings = listings.filter(filters)
        
        return listings.order_by('-date_sold')
    # render template here..
    def render_html(form, listings=[]):
        pass
    # csv rendering
    def render_csv(listings=[]):
        pass

Or a similar variant… play around

my 2p (haven’t tried this, just typed it quickly!)

dwatt · June 9, 2023, 4:03am

Thanks for your response!

You’re right about the self.export_to_csv(). It was still working so I didn’t realise.
You’re also correct about the points, - repeating if form.is_valid() and the listings queryset.

A problem in my structure was that the Export to CSV button was actually outside the form in the html, therefore not retaining the users submitted responses and making a seperate API call.

See below for updated POST code - probably not the most efficient way, but for the meantime it’s working

    def post(self, request):
        form = PropertyFilterForm(request.POST)
        if form.is_valid():
            start_date = form.cleaned_data['start_date']
            end_date = form.cleaned_data['end_date']
            suburb = form.cleaned_data['suburb']
            state = form.cleaned_data['state']
            price_guide_info = form.cleaned_data['price_guide_info']

            listings = PropertyListing.objects.all().order_by('-date_sold')

            if start_date and end_date:
                listings = listings.filter(date_sold__range=[start_date, end_date])
            if suburb:
                data = suburb.split(", ")
                listings = listings.filter(suburb__icontains=data[0])
                listings = listings.filter(postcode__icontains=data[1])
            if state:
                listings = listings.filter(state__icontains=state)
            if price_guide_info:
                listings = listings.exclude(price_guide_info__icontains='Price Withheld')

            context = {
                'form': form,
                'listings': listings,
            }

            # I've added a try/except because the request.POST['export_to_csv'] was throwing an error, because initially it doesn't exist.
            try:
                if 'Export to CSV' == request.POST['export_to_csv'] and listings.exists():
                    response = export_csv(listings) # I've fixed this here, now it's calling the correct function below
                    return response
            except:
                pass

            return render(request, 'listings/property-list.html', context)

        # Form is invalid, render the form again
        context = {
            'form': form,
        }
        return render(request, 'listings/property-list.html', context)

CSV Function

def export_csv(listings):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="property_listings.csv"'

    writer = csv.writer(response)
    writer.writerow(['ID', 'Date Sold', 'Sold By', 'Price Guide', 'Price Guide Info', 'Address',
                     '# of Beds', '# of Baths', '# of Parking', 'Size (m²)', 'House Type', 'Inspection',
                     'Full Address', 'Listing Type', 'Suburb', 'State', 'Postcode', 'Profile'])

    for listing in listings:
        writer.writerow([listing.id, listing.date_sold, listing.sold_by, listing.price_guide,
                         listing.price_guide_info, listing.address1, listing.num_of_beds, listing.num_of_baths,
                         listing.num_of_parking, listing.size_msq, listing.house_type, listing.inspection,
                         listing.full_address, listing.listing_type, listing.suburb, listing.state,
                         listing.postcode, listing.profile])

    return response

Topic		Replies	Views
Issue with filtering the queryset between fromdate and todate to export in csv Using Django	1	416	April 20, 2021
Exporting Django Model Data to CSV File Using the csv Module: Code Assistance Needed Forms & APIs	2	746	August 23, 2023
Filtering rows in the changelist_view method of the admin.ModelAdmin class The Admin	1	449	November 15, 2023
Django-filter: filtering last entry of a kind.... Forms & APIs	8	1019	November 1, 2023
Format data queryset result to use in form.ModelChoiceField queryset option Forms & APIs	2	293	August 3, 2023

Export filtered query as CSV

Related Topics