django.db.utils.ProgrammingError: operator does not exist: character varying = integer

Question

Guys i kindly need your help to figure out why am getting this error when i ran my script on a server. the server runs a postgres db i think it is something to do with the object type of the data it's been iterated but not sure how to fix that.

Traceback (most recent call last):
  File "scripts/change_topup.py", line 58, in <module>
    main()
  File "scripts/change_topup.py", line 55, in main
    process_file(path)
  File "scripts/change_topup.py", line 45, in process_file
    for enrolment in enrolments:
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
    cursor.execute(sql, params)
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/opt/cv_instances/cv1/autodeploy/branches/nboreports/.venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: operator does not exist: character varying = integer
LINE 1: ...n"."type" = 1 AND "registration_household"."name" IN (SELECT...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

my script is here

# script to update hh top-up locations
import django
django.setup()


import sys
import sys
import xlrd
from django.db import transaction
from maidea.apps.enrolment.models import Enrolment
from maidea.apps.redemption.models import Retailer
from maidea.apps.registration.models import Household
from maidea.apps.interventions.models import Intervention
from maidea.apps.wfp.models import Office


office = Office.objects.get(slug='so-co')
intervention = Intervention.objects.get(project__office=office, slug='cash-for-dadaab-returnees')

print("Using Office: {0}".format(office))
print("Using Intervention: {0}".format(intervention))

def process_file(path):
    # get the first worksheet
    book = xlrd.open_workbook(path)
    first_sheet = book.sheet_by_index(0)
    print('Sheet name: {0}'.format(first_sheet.name))

    print("Looping through excel rows. Please wait ...")
    #counter = 0

    with transaction.atomic():
        # iterate through rows
        for row_idx in range(1, first_sheet.nrows):
            # household names
            hh_name = str(first_sheet.row_values(row_idx)[0])
            # the new top-up locations
            new_top_up = str(first_sheet.row_values(row_idx)[2])


            households = Household.objects.filter(name__in=hh_name)
            top_up = Retailer.objects.filter(name__in=new_top_up) 
            enrolments = Enrolment.objects.filter(intervention=intervention, household__name__in=households) 
            #print("Fetched {0} enrolments".format(enrolments.count())
            for enrolment in enrolments:
                print enrolment
                # enrolment.retailer = top_up
                # enrolment.save()
                #counter += 1
            print("{0} enrolments updated".format(counter))


def main():
    path = "/opt/cv_instances/cv1/autodeploy/branches/nboreports/maidea/somalia/data_import_files/Enrolment.xlsx"
    process_file(path)
    print("DONE PROCESSING FILE.")

main()

The file been processed has rows like

HH | top up |

SD803-11H47782 | MERCY US NEW POS TOPUP

SD803-09H03991 | DRC-MOG

Help will highly appreciated.Thanks

As ettanany said we need your models for the "Enrolment" class so we can see what type it is in your Database. — Jbertrand
– Jbertrand, Commented Dec 8, 2016 at 15:18

Alasdair · Accepted Answer · 2016-12-08 16:18:47Z

3

The problem is this line

enrolments = Enrolment.objects.filter(intervention=intervention, household__name__in=households):

households is a queryset of Household instances, not names. I think that either of the following would work.

enrolments = Enrolment.objects.filter(intervention=intervention, household__in=hh_name) 


enrolments = Enrolment.objects.filter(intervention=intervention, household__name__in=hh_name)

If you use the second one, then it looks like you can remove the households queryset from your code.

answered Dec 8, 2016 at 16:18

Alasdair

310k59 gold badges605 silver badges534 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edward Okech Over a year ago

it worked the issue was from my enrolment queryset that could not filter the household name object thanks

Collectives™ on Stack Overflow

django.db.utils.ProgrammingError: operator does not exist: character varying = integer

HH | top up |

SD803-11H47782 | MERCY US NEW POS TOPUP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

HH | top up |

SD803-11H47782 | MERCY US NEW POS TOPUP

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related