SQlite none english char

[Gerhard Häring]

Gandalf wrote:
> I works with python 2.5 on windows,  And I use sqlite3
> 
> Now, I have problem searching string in Hebrew in my database
> 
> I have table called "test" with field num and test
> firs row i insert  "1" and "עברית"  (that is "Hebrew" in Hebrew)
> second row i insert "2" and "English" [...]

I recommend you use Unicode strings for all your Python strings in the
application. You can then be that things will just work with the sqlite3
module.

The problem is that the sqlite3 module doesn't currently check if what
you insert into the database is in UTF-8 encoding. But if it isn't,
you're likely to run into problems later on.

So, let's assume that you've written your Python using a UTF-8 editor,
you can then use something like:

# coding: utf-8

as the first line in the script. Among others, this will allow you to
write Unicode literals in UTF-8, i. e. do something like:

data = u"עברית".

then you can insert this into the database:

cur.execute("insert into test(test) values (?)", (data,))

Note that I use the parametrized form of execute() with ? as
placeholders. *Always* use this when the SQL statement is not constant,
but has parameters of some sort.

> [...]
> cur.execute("select *  from `test` where text like '%"+i+"%'  ")
> for row in cur:
>     print row[1]
> 
> but this one print me nothing
> instead of עברית

This could be two problems. Either (likely) it simply isn't found
because you inserted garbage (non-UTF-8 data) or you got a decoding
error to UTF-8 executing the select, which the sqlite3 module will
currently unfortunately ignore and return a None value instead.

Again, use the parametrized form of the execute() statement with Unicode
Python strings to avoid any problems.

cur.execute("select * from test where text like '%' || ? || '%'",
(searchstr,))

!!! Ok, I just found out that you used the + operator in SQL to
concatenate strings. Don't, as it will not work (except on maybe MySQL).
Use the || operator instead!

Note that when you use cur.execute(sql, params), then params is actually
a tuple of parameters. In the above examples there was only one
parameter, so it was a one-tuple, which is written as (element,).
Dont'write it as (element), because Python will not recognize it as a
tuple then.

Don't hesitate to ask if you need further help.

-- Gerhard