CSV file

[Percy Masekwameng]

Hi

I have web app survey that collect data and generate a CSV file,

I'm using railway to deploy my web app, running on 8GB RAM and each time I generate a file, the server goes down and display "Application failed to respond" the database table has over 2k records

Is there any way to improve the performance so that I can be able to download large dataset from the web app?

[Muhammad Juwaini Abdul Rahman]

How big is the file?

I don't think a mere 2K records can kill an 8GB RAM machine.

Probably something to do with your workers' settings?

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CANfc-pBoutR0kD%3DcqwRHGSQ8gac%2B_YLMyCQoFK--%3DD54TZpotA%40mail.gmail.com.

[Percy Masekwameng]

It depends on the data in the database

Explain more about workers settings and provide me with an example

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFKhtoTkSsNMcvOZgP%2BWw2U2%3DMkfpD4%3DFLiNUL-Ftd1xzAi4uA%40mail.gmail.com.

[Prosper Lekia]

Ask this question on the Railway Discord community. The expert their will tell you if it's a Railway thing or not. But you should look more at your code, your settings.py.

--

[Muhammad Juwaini Abdul Rahman]

Probably you need to show the code snippet on where you generate the csv file.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CANfc-pBv8u_sGNP%3DZhJL6sG3RHvXRuHXn0M17_vvPa8f2UYkjQ%40mail.gmail.com.

[Peter Benjamin Ani]

Try using Python anywhere 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFKhtoQypbdPf9k1w9LM1wgZWSOqbs7d8XwRqdVm%3D3xm-kYtEQ%40mail.gmail.com.

[Percy Masekwameng]

@login_required(login_url="system_urls:login")
def csv_file(request):
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename=Captured-Claims-' + str(date.today().strftime('%Y-%m-%d')) + '.csv'

    #create a csv writer
    writer = csv.writer(response)

    claims = claim.objects.prefetch_related('assessor', 'updated_by', 'principal_member')

    # Retrieving paramenters from request
    scheme_query = request.GET.get('scheme')
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    assessor = request.GET.get('assessor')
    paymentstatus = request.GET.get('payment_claim_status')

    if start_date and end_date:
        claims = claims.filter(created__range=(start_date, end_date))

    elif start_date:
        claims = claims.filter(created__gte=start_date)

    elif end_date:
        claims = claims.filter(created__lte=end_date)

    elif assessor and scheme_query:
        claims = claims.filter(assessor__first_name=assessor, scheme_name__name=scheme_query)

    elif scheme_query:
        claims = claims.filter(scheme_name__name=scheme_query)

    elif assessor:
        claims = claims.filter(assessor__first_name=assessor)

    elif paymentstatus:
        claims = claims.filter(payment_status__name=paymentstatus, is_paid=True)

    elif paymentstatus and start_date and end_date:
        claims = claims.filter(payment_status__name=paymentstatus, is_paid=True,created__range=(start_date, end_date))

    elif assessor and scheme_query and start_date and end_date:
        claims = claims.filter(assessor__first_name=assessor, scheme_name__name=scheme_query,created__range=(start_date, end_date))


    # Add headers
    writer.writerow(['Assessor Name','Assessor Email','Insurer Name','Scheme Name','Original Claim Form','Claim Status','Date Claim Submitted','Date Claim Captured','Date Claim Paid','Submission Month','Capture Month','Payment Month','External Policy number','Product Type', 'Risk Status', 'Cause Of Death', 'Inception Date', 'Termination Date', 'Gross Premium','Principal Member Name(s) & Surname','ID Type', 'Principal Member ID/Passport Number', 'Name of Claimant', 'ID Type','Claimant ID/Passport',
     'Date of Death','Deceased Name(s)','Deceased Surname','Marital Status','Residence Country','Birth Country','Nationality','Deceased Member Type',
     'ID Type','Deceased ID/Passport Number', 'Deceased Gender','Deceased Date of Birth', 'Age Band', 'Age At Death','Cover Amount','Amount Paid', 'Tax Reference Number(Optional)', 'Source Of Funds (Optional)', 'Nature of Income (Nature of Sanlam Pay-out)(optional)','Place of Death','Name of Hospital','Medical Practioner Name','Medical Practioner Practice Number','Informant Name','Informant Relationship To The Deceased','Chiefs Contact Number','Funeral Parlour Performing Service'
     ,'Death Certificate Issuer','HA That Issued DC', 'DHA1663 Reference Number', 'DHA1680 Reference Number', 'Name Of Beneficiary','Bank Name','Account Type','Bank Account Number','Bank Code Number','Premium Check Six Months','Data Confirmation Check','Review Status','Payment Status','Closure Status','Notes'])

    for obj in claims.iterator():
        assessor_details = obj.assessor.first_name + " " + obj.assessor.last_name

        if obj.date_claim_submission:
            date_submited = obj.date_claim_submission
        else:
            date_submited = obj.created

        if obj.payment_date:
            date_payment = obj.payment_date
        else:
            date_payment = " "

        if obj.paid_month:
            date_paid_month = obj.paid_month
        else:
            date_paid_month = " "

        if obj.principal_member.product_types.all():
            principal_products = ', '.join([product_type.name for product_type in obj.principal_member.product_types.all()])
        else:
            principal_products = ', '.join([product_type.name for product_type in obj.product_type.all()])

        if obj.principal_member.termination_date:
            member_termination = obj.principal_member.termination_date
        else:
            member_termination = " "

        if obj.gross_premium:
            member_premium = obj.gross_premium
        else:
            member_premium = " "

        pmember_details = obj.principal_member.full_names + " " + obj.principal_member.surname

        if obj.principal_member.id_number:
            pmember_idtype = "RSA ID"
        elif obj.principal_member.passport:
            pmember_idtype = "Passport"

        if obj.principal_member.id_number:
            pmember_id = obj.principal_member.id_number
        elif obj.principal_member.passport:
            pmember_id = obj.principal_member.passport

        # Claimant
        if obj.claimant:
            claimant_details = obj.claimant.name_of_claimant + " " + obj.claimant.surname_of_claimant
        else:
            claimant_details = obj.principal_member.full_names + " " + obj.principal_member.surname

        # Claimant ID Type
        if obj.claimant and obj.claimant.id_of_claimant:
            claimant_id_type = "RSA ID"
        elif obj.claimant and obj.claimant.passport:
            claimant_id_type = "Passport"

        elif obj.principal_member.id_number:
            claimant_id_type = "RSA ID"

        elif obj.principal_member.passport:
            claimant_id_type = "Passport"

        # Claimant ID
        if obj.claimant and obj.claimant.id_of_claimant:
            claimant_id = obj.claimant.id_of_claimant

        elif obj.claimant and obj.claimant.passport:
            claimant_id = obj.claimant.passport

        elif obj.principal_member.id_number:
            claimant_id = obj.principal_member.id_number

        elif obj.principal_member.passport:
            claimant_id = obj.principal_member.passport

        # Deceased
        if obj.deceased_name:
            deceasedname = obj.deceased_name
        else:
            deceasedname = " "

        if obj.deceased_surname:
            deceasedsurname = obj.deceased_surname
        else:
            deceasedsurname = " "

        if obj.marital_status:
            deceasedmarital = obj.marital_status
        else:
            deceasedmarital = " "

        if obj.residence_country:
            deceasedresidence = obj.residence_country
        else:
            deceasedresidence = " "

        if obj.birth_country:
            deceasedbirthcountry = obj.birth_country
        else:
            deceasedbirthcountry = " "

        if obj.nationality:
            deceasednationality = obj.nationality
        else:
            deceasednationality = " "

        if obj.principal_member.id_number or obj.deceased_id_number:
            deceased_idtype = "RSA ID"

        elif obj.principal_member.passport or obj.passport:
            deceased_idtype = "Passport"

        if obj.deceased_id_number:
            deceased_id = obj.deceased_id_number
            deceased_gender = obj.get_gender
            deceaseddob = obj.get_dateofb

        elif obj.passport:
            deceased_id = obj.passport
            deceased_gender = obj.gender
            deceaseddob = obj.date_of_birth

        if obj.amount_paid:
            paid_amount = obj.amount_paid
        else:
            paid_amount = ""

        if obj.tax_ref:
            tax_reference = obj.tax_ref
        else:
            tax_reference = " "

        if obj.source_of_funds:
            source_funds = obj.source_of_funds
        else:
            source_funds = " "

        if obj.nature_of_income:
            nature_income = obj.nature_of_income
        else:
            nature_income = " "

        if obj.chiefs_phone_number:
            chief_number = obj.chiefs_phone_number
        else:
            chief_number = ""

        if obj.DHA1663:
            dha1663 = obj.DHA1663
        else:
            dha1663 = " "

        if obj.DHA1680:
            dha1680 = obj.DHA1680
        else:
            dha1680 = ""

        if obj.premium_check:
            premium_ch = obj.premium_check
        else:
            premium_ch = " "

        if obj.data_confirmation:
            data_confirm = obj.data_confirmation
        else:
            data_confirm = " "

        if obj.review_status:
            review = obj.review_status
        else:
            review = "Not Reviewed"

        if obj.payment_status:
            payment_stat = obj.payment_status
        else:
            payment_stat = "Not Paid"

        if obj.claim_closure:
            claimclose = obj.claim_closure
        else:
            claimclose = ""

        if obj.notes:
            claim_notes = obj.notes
        else:
            claim_notes = " "


        writer.writerow([assessor_details, obj.assessor.email, obj.insurer_name.name, obj.scheme_name.name," ", obj.claim_status, date_submited,obj.created, date_payment,obj.submission_month, obj.capture_month, date_paid_month, obj.policy_number, principal_products, obj.principal_member.risk_status, obj.deceased_cause_of_death, obj.inception_date, member_termination,
            member_premium,pmember_details,pmember_idtype,pmember_id,claimant_details,claimant_id_type,claimant_id,obj.deceased_date_of_death,deceasedname,deceasedsurname,deceasedmarital,deceasedresidence,deceasedbirthcountry,deceasednationality,obj.deceased_member,deceased_idtype,deceased_id,deceased_gender,deceaseddob, "",obj.get_age, obj.claim_amount,paid_amount,
            tax_reference,source_funds,nature_income, obj.place_of_death, obj.name_of_hospital,obj.dr_Pathologist_nurse_name,obj.doctor_practice_umber, obj.informant_name,obj.relation_to_deceased,chief_number, obj.funeral_parlour,obj.Death_Certificate_issued_by_User,obj.HA_that_Issued_DC,dha1663,dha1680, obj.account_holder,obj.bank_name,obj.account_type,obj.account_number,
            obj.bank_name.branch_code,premium_ch,data_confirm,review,payment_stat,claimclose, claim_notes])

    return response

[Ryan Nowakowski]

If the issue is that you need to stream the CSV instead of batch it in
memory, the Django documentation addresses this directly:


https://docs.djangoproject.com/en/4.2/howto/outputting-csv/#streaming-csv-files

[Mz Gz]

I think obj.iterator() is the problem.

Can you try to loop using :

for obj in objs and see the results ?

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/fb67d24f-b3d5-f825-7938-3a62402f11de%40fattuba.com.

[Mz Gz]

Also try to fetch all data once, manipulate using pandas dataframe , then use pandas to_csv() to generate csv file once

[Percy Masekwameng]

i tried that, and some table im making queries from has foreign keys to other tables, the dataframe in pandas for those fields returns IDs and since the IDs do not match, I'm unable to query the fields i need from foreignkey fields

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAHV4E-e91%3DnwknoA30iyJEz26wQbnE8z1rW-bLXg7sRa9T1MXg%40mail.gmail.com.

[David Emanuel Sandoval]

Try to avoid creating objects if possible. I mean, instead of fetching "objects", try to fetch values() or values_list(), use annontate if possible to bring related data.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CANfc-pCGpCNxdJ3Kgn5OzyzzoB7A%3DFgs1Ak1h6tzguUzCwiNjA%40mail.gmail.com.

[David Emanuel Sandoval]

Mmm, I think i missunderstood the problem.

[Alec Delaney]

For server

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CANfc-pBoutR0kD%3DcqwRHGSQ8gac%2B_YLMyCQoFK--%3DD54TZpotA%40mail.gmail.com.

[Mz Gz]

Percy, when you use Pandas dataframe you can use merge() function, it works just like sql join 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFGN%3DGiUQMhWC42LnWbCTuXv5TROrDNjU%2BQLGKU2C_4aQVoLjw%40mail.gmail.com.