python - Django View 中的嵌套 SQL 查询

Question

所以我进行了搜索，到目前为止找不到任何答案。我有一个数据库，当我输入时:

SELECT event_name, team_number
FROM teams_by_event
WHERE team_number IN (SELECT team_number 
                    FROM teams_by_event 
                    WHERE event_name = '[user inputs event name]'
                    ) ORDER BY team_number;

它给了我我想要的返回。我的问题是我想不出在 Django View 中执行此操作的方法，并且我尝试过，仅在 Django 中使用原始 SQL 也不起作用，我收到一个语法错误，指出 SQL 上的最后一个括号查询无效。

views.py:

def teams_by_event(request, shorthand):
    code = TeamsByEvent.objects.filter(shorthand=shorthand)
    for event in TeamsByEvent.objects.raw('SELECT event_name, team_number \
                               FROM teams_by_event \
                               WHERE team_number IN \
                               (SELECT team_number \
                                FROM teams_by_event) \
                                WHERE shorthand= % s', [code])
    print (event.team_number, event.event_name)
    return render(request, 'event-info.html', {'info': info})

models.py

class TeamsByEvent(models.Model):
    team_number = models.IntegerField()
    event_name = models.CharField(max_length=50)
    shorthand = models.CharField(max_length=12)

    class Meta:
        db_table = 'teams_by_event'
        app_label = 'frcstats'

编辑::回溯:

Traceback (most recent call last):
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/core/handlers/base.py", line 149, in get_response
    response = self.process_exception_by_middleware(e, request)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/core/handlers/base.py", line 147, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/Users/alicen/git/first_robotics/frcstats/views.py", line 1034, in teams_by_event
    for event in TeamsByEvent.objects.raw(raw_query):
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/query.py", line 1219, in __iter__
    query = iter(self.query)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/sql/query.py", line 79, in __iter__
    self._execute_query()
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/models/sql/query.py", line 113, in _execute_query
    self.cursor.execute(self.sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 322, in execute
    query = self.convert_query(query)
  File "/Users/alicen/git/first_robotics/venv/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 330, in convert_query
    return FORMAT_QMARK_REGEX.sub('?', query).replace('%%', '%')
TypeError: expected string or buffer

Answer 1

看到您的表结构(即模型)和查询，我很确定您根本不需要嵌套查询。您的查询:

SELECT event_name, team_number
FROM teams_by_event
WHERE team_number IN (SELECT team_number 
                    FROM teams_by_event 
                    WHERE event_name = '[user inputs event name]'
                    ) ORDER BY team_number;

应返回与以下查询结果完全相同的内容:

SELECT event_name, team_number
FROM teams_by_event
WHERE event_name = '[user inputs event name]' ORDER BY team_number;

等效的 Django 查询类似于:

TeamsByEvent.objects.filter(event_name='your_event_name')