Django orm make query wrong for Mysql db.

Using Django
1

i have face the problem in django orm
the field value is — ‘Korea, Republic of’
but the django orm make query and treat like two value is ‘Korea ‘,’ Republic of’'
please help me :slight_smile:

q_objects= (OR: (‘region__in’, [‘Americas Region’, ‘Europe Region’, ‘Russia Region’, ‘Middle East and Africa Region’]), (‘reg_temp__in’, [‘Comoros’, “Cote D’Ivoire”, ‘Ghana’, ‘Australia’, ‘China’, ‘Hong Kong’, ‘Indonesia’, ‘Japan’, ‘Korea, Republic of’, ‘Malaysia’, ‘New Zealand’, ‘Philippines’, ‘Singapore’, ‘Sri Lanka’, ‘Taiwan’, ‘Thailand’, ‘Viet Nam’]))

users = obj_users.filter(q_objects)

print(“user query++++++++”, users.query)

query is ---------
IN (Viet Nam, Malaysia, Hong Kong, Thailand, Japan, Australia, Philippines, New Zealand,
Korea, Republic of, China, Indonesia, Comoros, Taiwan, Cote D’Ivoire, Singapore, Sri Lanka, Ghana)

2

str(QuerySet.query) doesn’t show the exact SQL that will be executed (except on PostgreSQL). It is broken for quoting strings. In this case, it looks wrong, but if you actually execute the query, you’ll see it’s correct.

Here’s how to see the actual SQL being executed: https://docs.djangoproject.com/en/3.0/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

3

@adamchainz query is not working correct …
it takes " Koria, Republic" of two values and give wrong results.
Please help me or suggest me better way

4

@rahulkoundal,

@adamchainz has a point, and it’s a surprise that your query is not working. Which database are you using?

Have you tried to use quotes wrapping the string? Like "'Korea, Republic of'"?

There is this answer from Stackoverflow that suggests the same: https://stackoverflow.com/questions/18078542/django-queryset-filter-missing-quotes

5

@kplaube @adamchainz
My database is MySql
"'Korea, Republic of'" is not working for me .
i use also the trick of the stackflow but still is not working. its for Sqlite database only.

6

It works at query time. But QuerySet.query does not show the actual SQL that’s run, but an approximation, hence it’s wrong. I made an example model on MariaDB locally and the query works fine:

In [1]: from example.core.models import Book

In [2]: Book??
Init signature: Book(*args, **kwargs)
Docstring:      Book(id, title)
Source:
class Book(models.Model):
    title = models.CharField(max_length=200)
File:           ~/tmp/in-mysql/example/core/models.py
Type:           ModelBase
Subclasses:

In [3]: Book.objects.create(title="Korea, Republic of")
Out[3]: <Book: Book object ((1,))>

In [4]: Book.objects.filter(title__in=["Korea, Republic of"])
Out[4]: <QuerySet [<Book: Book object (1)>]>

In [5]: Book.objects.filter(title__in=["Korea, Republic of"]).query
Out[5]: <django.db.models.sql.query.Query at 0x106f11910>

In [6]: str(Book.objects.filter(title__in=["Korea, Republic of"]).query)
Out[6]: 'SELECT `core_book`.`id`, `core_book`.`title` FROM `core_book` WHERE `core_book`.`title` IN (Korea, Republic of)'

The linked stack overflow is very old (2013) and I’m not sure it’s applicable any more.

1 Like