Re: Performing a query returns an error

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

Le mercredi 13 novembre 2019 10:28:30 UTC-5, Patrick Carra a écrit :

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.

--
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+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d3097247-d3bf-433a-bed4-43bf597d5261%40googlegroups.com.