CODEWITHSUNDEEP
pythonmysqldjango
pkill
pkill

Reputation: 421

Python Django Mysql like statement

I constructed a query from a dict like

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)

I am not seeing an expected output. When I am doing the same through the manage.py shell, I am seeing the following error.

>>> 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

What am I doing wrong here?

Upon checking the mysql logs, I cannot see the queries coming there. However, if I am omitting the like statement, and performing just a select * from getdata_record, I can see that the query is actually logged in the mysql server.

Upvotes: 0

Views: 825

Answers (2)

Anentropic
Anentropic

Reputation: 33833

You don't need to use a raw query for anything you're doing here, it's possible via 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)

Upvotes: 1

dan-klasson
dan-klasson

Reputation: 14190

With parameterized queries you should be doing:

db_query = "SELECT * FROM getdata_record_search where domain like %s"
testdata = record_search.objects.raw(db_query, [some_string])

Upvotes: 1

Related Questions