Python Django Mysql like statement

Question

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.

Django has a beautiful ORM. Why use Raw Queries when you can do that ? Read up on Q objects — karthikr
– karthikr, Commented Jan 22, 2015 at 15:37
I think the issue is the '%' character. Try to escape it when you construct query by using '/'. And karthikr is right. Why on earch do you need raw sql? — Jerry Meng
– Jerry Meng, Commented Jan 22, 2015 at 15:48
to address your specific issue, i think it is the case when there are no parameters, you have a dangling where clause — karthikr
– karthikr, Commented Jan 22, 2015 at 15:55

Anentropic · Accepted Answer · 2015-01-22 16:13:45Z

1

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)

answered Jan 22, 2015 at 16:13

Anentropic

34.2k13 gold badges111 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

guy_next_door Over a year ago

Thanks Anentropic, however, if I am using SELECT * FROM getdata_record_search where domain like 'w3%' and ttl like '3%' , which has an and, will filter_quargs going to work to query on multiple columns?

Anentropic Over a year ago

yes, if you supply a bunch of kwargs to filter() they will be treated as AND clauses ....if you needed OR logic you'd have to use Q objects, but in this case not necessary

dan-klasson · Accepted Answer · 2015-01-22 15:49:35Z

1

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

answered Jan 22, 2015 at 15:49

dan-klasson

14.3k14 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

Python Django Mysql like statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related