Hebrew in Raw Query Like Clause

[Art Zemon]

Hello,

I have a column in a table that contains Hebrew text. I need to do a query with a LIKE clause on the Hebrew text. The query works if I execute the SQL directly, either in a SQL window of phpMyAdmin or in a command line mysql client. But I cannot get it to work within Django.

I tried Bible.objects.filter(hebrew_text__contains('דֶּשֶׁא') and I get nothing back.

I tried a raw query in a custom manager:

class BibleManager(models.Manager):

    def contains_hebrew_word(self, word='דֶּשֶׁא'):

        sql = """select sortkey, book, chapter, verse, hebrew_text

                 from bible_bible

                 where hebrew_text like '%%%s%%' """

        raw_query_set = self.raw(sql, [word])

        result_list = []

        for b in raw_query_set:

            result_list.append(b)

        return result_list

but I get this error:

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'דֶּשֶׁא'%'' at line 3")

I am using 

• Django 1.9
• Python 3
• mysqlclient 1.3.7
  
• MySQL 5.5.49

Any ideas?

    -- Art Z.

--

http://CheerfulCurmudgeon.com/

"If I am not for myself, who is for me? And if I am only for myself, what am I? And if not now, when?" Hillel

[Michal Petrucha]

On Thu, Jul 07, 2016 at 11:23:21PM -0500, Art Zemon wrote:
> Hello,
>
> I have a column in a table that contains Hebrew text. I need to do a query
> with a LIKE clause on the Hebrew text. The query works if I execute the SQL
> directly, either in a SQL window of phpMyAdmin or in a command line mysql
> client. But I cannot get it to work within Django.
>
> I tried Bible.objects.filter(hebrew_text__contains('דֶּשֶׁא') and I get
> nothing back.

Here, one thing you might want to try is to use django-debug-toolbar's
debugsqlshell, run the query inside that, and take a look at the
actual SQL query generated for that; maybe it will reveal some oddity.

> I tried a raw query in a custom manager:
>
> class BibleManager(models.Manager):
> def contains_hebrew_word(self, word='דֶּשֶׁא'):
> sql = """select sortkey, book, chapter, verse, hebrew_text
> from bible_bible
> where hebrew_text like '%%%s%%' """
> raw_query_set = self.raw(sql, [word])
> result_list = []
> for b in raw_query_set:
> result_list.append(b)
> return result_list
>
> but I get this error:
>
> django.db.utils.ProgrammingError: (1064, "You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near 'דֶּשֶׁא'%'' at line 3")

I may be wrong, but it seems to me that you're using wrong quoting in
that raw query; I think it should be::

select sortkey, book, chapter, verse, hebrew_text
from bible_bible

where hebrew_text like '%%' || %s || '%%'

The DBAPI will already take care of the proper quoting for the string
argument, you just need to properly concatenate it with percent signs
on both sides.

Good luck,

Michal

[Art Zemon]

On Friday, July 8, 2016 at 8:34:00 AM UTC-5, Michal Petrucha wrote:

I may be wrong, but it seems to me that you're using wrong quoting in
that raw query; I think it should be::

    select sortkey, book, chapter, verse, hebrew_text
    from bible_bible
    where hebrew_text like '%%' || %s || '%%'

The DBAPI will already take care of the proper quoting for the string
argument, you just need to properly concatenate it with percent signs
on both sides.

Michal,

You're a genius! That did it. Thank you.

    -- Art Z. 

[Art Zemon]

Michal,

Actually, your syntax didn't quite work. It threw a couple of warnings and seemed to match all records in the database. But you got me on the right track. This syntax does work properly:

select ... where word like concat('%%', %s, '%%')

Thanks for getting me pointed in the right direction.

-- Art Z.