使用外键的Django连接

Question

我是django 2.7的新手。我尝试使用外键约束运行联接查询。我有两个表table1和table2，具有以下属性-：

Model.py-：

class table1(models.Model):
    abcid = models.IntegerField(db_column='abcid', primary_key=True)  # Field name made lowercase.
    abcName = models.CharField(db_column='abcName', max_length=50, blank=True, null=True)  # Field name made lowercase.
    abcyear = models.IntegerField(db_column='abcYear', blank=True, null=True)



class table2(models.Model):
    abcid = models.ForeignKey('table1', models.DO_NOTHING, db_column='abcid')
    xyzname = models.CharField(db_column='xyzName', max_length=150, blank=True, null=True)  # Field name made lowercase.
    xyztype = models.CharField(db_column='xyzType', max_length=150, blank=True, null=True)  # Field name made lowercase.

我想要所有xyzname包含“菠萝”的记录。 我需要的列是-abcid,abcname,abcyear,xyzname

到目前为止，我所做的努力如下：

^{pr2}$

请帮忙。 错误-：

Request Method: GET
Request URL: http://127.0.0.1:8000/abc/trade/

Django Version: 1.9
Python Version: 2.7.3
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'abc_act']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  149.                     response = self.process_exception_by_middleware(e, request)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  147.                     response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/deep/workspace/src/abcNew/abc_journal/abcjournal/abcsoft/abc_act/views.py" in abcactname
  18.         data = serializers.serialize('json',abcactDS)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/core/serializers/__init__.py" in serialize
  129.     s.serialize(queryset, **options)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/core/serializers/base.py" in serialize
  79.         for count, obj in enumerate(queryset, start=1):

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/models/query.py" in __iter__
  258.         self._fetch_all()

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/models/query.py" in _fetch_all
  1074.             self._result_cache = list(self.iterator())

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/models/query.py" in __iter__
  52.         results = compiler.execute_sql()

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  852.             cursor.execute(sql, params)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  79.             return super(CursorDebugWrapper, self).execute(sql, params)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  64.                 return self.cursor.execute(sql, params)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/utils.py" in __exit__
  95.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  64.                 return self.cursor.execute(sql, params)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  112.             return self.cursor.execute(query, args)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  205.             self.errorhandler(self, exc, value)

File "/home/deep/workspace/src/abcJournal/abcjournal/local/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  36.     raise errorclass, errorvalue

Exception Type: OperationalError at /abc/trade/
Exception Value: (1054, "Unknown column 'table2.id' in 'field list'")

Answer 1

table2.objects.filter(xyzname__icontains='pineapple') \
              .select_related('abcid__abcid',
                              'abcid__abcName',
                              'abcid__abcyear')

select_relateddjango doc。在

Answer 2

在Django中，通常的方法是从一个模型返回对象的查询集，而不是返回包含多个模型字段的对象。在

objects = table2.objects.filter(xyzname__icontains= 'pineapple')

然后，您可以循环遍历这些对象，并使用obj.abcid从table2到table1的外键

^{pr2}$

为了加快查询速度，可以使用^{}，这样Django就可以进行内部联接。在

objects = table2.objects.filter(xyzname__icontains= 'pineapple').select_related('abcid')

这仍然返回table2对象的queryset。您可以循环使用它们并以与上面相同的方式访问外键。在