Performing a query returns an error

[Patrick Carra]

Hello all I am new to django and I am trying to do a query to that will provide me with information from multiple tables I have included my models.py file below:

Enter code hclass Circuitinfotable(models.Model):

    pid = models.CharField(max_length=255, blank=True, null=True)

    circuitid = models.CharField(primary_key=True, max_length=255, blank=True, null=False)

    bandwidth = models.CharField(max_length=255, blank=True, null=True)

    region = models.CharField(max_length=255, blank=True, null=True)

    

    class Meta:

        managed = False

        db_table = 'circuitinfotable'

class Budgettable(models.Model):

    id = models.IntegerField(primary_key=True)

    circuitid = models.CharField(max_length=255, blank=True, null=True)

    pid = models.ForeignKey('Circuitinfotable', on_delete=models.CASCADE)

    monthnum = models.IntegerField(blank=True, null=True)

    yearnum = models.IntegerField(blank=True, null=True)

    budgetmrc = models.TextField(blank=True, null=True)  # This field type is a guess.

    actualmrc = models.TextField(blank=True, null=True)  # This field type is a guess.

    region = models.CharField(max_length=255, blank=True, null=True)

    class Meta:

        managed = False

        db_table = 'budgettable'

the code I was using to query this is below:

>>> from viewLit.models import Circuitinfotable, Budgettable

>>> btc = Circuitinfotable.objects.get(circuitid='ETH100GB-23349286')

>>> btc.budgettable_set.all()

Traceback (most recent call last):

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

psycopg2.errors.UndefinedColumn: column budgettable.pid_id does not exist

LINE 1: ...CT "budgettable"."id", "budgettable"."circuitid", "budgettab...

                                                             ^

HINT:  Perhaps you meant to reference the column "budgettable.pid".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 250, in __repr__

    data = list(self[:REPR_OUTPUT_SIZE + 1])

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 274, in __iter__

    self._fetch_all()

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 1242, in _fetch_all

    self._result_cache = list(self._iterable_class(self))

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 55, in __iter__

    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/sql/compiler.py", line 1100, in execute_sql

    cursor.execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 99, in execute

    return super().execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute

    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers

    return executor(sql, params, many, context)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/utils.py", line 89, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

django.db.utils.ProgrammingError: column budgettable.pid_id does not exist

LINE 1: ...CT "budgettable"."id", "budgettable"."circuitid", "budgettab...

                                                             ^

HINT:  Perhaps you meant to reference the column "budgettable.pid".

The next thing that I tried was to just get an object from the Budgettable directly code below:

>>> btcBud=Budgettable.objects.get(pid='ETH100GB-23349286')

Traceback (most recent call last):

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

psycopg2.errors.UndefinedColumn: column budgettable.pid_id does not exist

LINE 1: ...CT "budgettable"."id", "budgettable"."circuitid", "budgettab...

                                                             ^

HINT:  Perhaps you meant to reference the column "budgettable.pid".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method

    return getattr(self.get_queryset(), name)(*args, **kwargs)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 402, in get

    num = len(clone)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 256, in __len__

    self._fetch_all()

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 1242, in _fetch_all

    self._result_cache = list(self._iterable_class(self))

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/query.py", line 55, in __iter__

    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/models/sql/compiler.py", line 1100, in execute_sql

    cursor.execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 99, in execute

    return super().execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute

    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers

    return executor(sql, params, many, context)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/utils.py", line 89, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File "/home/db_user/ciopsdb/venv/lib64/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute

    return self.cursor.execute(sql, params)

django.db.utils.ProgrammingError: column budgettable.pid_id does not exist

LINE 1: ...CT "budgettable"."id", "budgettable"."circuitid", "budgettab...

                                                             ^

HINT:  Perhaps you meant to reference the column "budgettable.pid".

>>>

I don't understand what I am doing wrong but I think it may be the way I'm defining the pid in models.py.  Any help is appreciated I have been working with the Django documentation but don't quite understand what I am missing.  This is the first time I have attempted to define a relationship between tables and perform a query after defining that relationship.

[Simon Charette]

Hello Patrick,

From what I understand you are relying on unmanaged (Meta.managed=False) models

to use the ORM to query an externally managed database.

The part you missed here is that the ORM maps ForeignKey fields to database columns with

a "_id" suffix by default. In your case that means it expects a "pid_id" column to exist for the

Budgettable.pid field.

If you column is actually named "pid" you'll want to use the "db_column" option to let the ORM

know about it[0]. That is `db_column=pid` in your `pid = ForeignKey` definition.

Cheers,

Simon

[0] https://docs.djangoproject.com/en/2.2/ref/models/fields/#database-representation

[Patrick Carra]

Simon thank you for that I had been playing around with db_column but did not realize the exact reference I should have been making.  Thank you for the quick reply!