Python Django Mysql like语句

Question

我从像这样的字典构建了一个查询

def search(params):
  query_data = []
  db_query = "SELECT * FROM getdata_record_search where "

  for key, value in params.iteritems():
    query_data.append(key + " like " + value + "% and ")
  db_query = re.sub("and\s$", "", db_query + " ".join(query_data))

  query_data = record_search.objects.raw(db_query)
  data = serializers.serialize("json", query_data)

我没有看到预期的输出。当我通过manage.py shell 执行相同操作时，我看到以下错误。

>>> string = 'w3' + '%'
>>> db_query = "SELECT * FROM getdata_record_search where domain like '%s'" % string
>>> db_query
"SELECT * FROM getdata_record_search where domain like 'w3%'"
>>> testdata = record_search.objects.raw(db_query)
>>> testdata
<RawQuerySet: "SELECT * FROM getdata_record_search where domain like 'w3%'">
>>> data = serializers.serialize("json", testdata)

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python2.6/dist-packages/django/core/serializers/__init__.py", line 122, in serialize
s.serialize(queryset, **options)
File "/usr/local/lib/python2.6/dist-packages/django/core/serializers/base.py", line 41, in serialize
for obj in queryset:
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 1398, in __iter__
query = iter(self.query)
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 73, in __iter__
self._execute_query()
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 87, in _execute_query
self.cursor.execute(self.sql, self.params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 69, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 53, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/mysql/base.py", line 124, in execute
return self.cursor.execute(query, args)
File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 151, in execute
query = query % db.literal(args)
TypeError: not enough arguments for format string

我在这里做错了什么？

检查 mysql 日志后，我看不到那里的查询。但是，如果我省略 like 语句，并仅执行 select * from getdata_record ，我可以看到查询实际上记录在mysql服务器中。

Answer 1

您不需要对您在这里所做的任何事情使用原始查询，这可以通过 Django ORM 实现:

https://docs.djangoproject.com/en/1.7/topics/db/queries/#escaping-percent-signs-and-underscores-in-like-statements

def search(params):
    filter_kwargs = {
        '{}__startswith'.format(key): value
        for key, value in params.iteritems()
    }
    query_data = record_search.objects.filter(**filter_kwargs)
    data = serializers.serialize("json", query_data)