Dennis Lee Bieber wrote:
> On Fri, 09 Oct 2015 08:56:18 +0200, Cecil Westerhof <ce...@decebal.nl>
> declaimed the following:
>
>>My bad, I intended to mention that ORDER BY gives the wrong order (é
>>comes after z and with sort it comes after e), so that is why I use
>>the external sort command.
>>
> My books show how to define collation functions for SQLite3 -- from C;
> don't show if Python can interface to that side (custom aggregation
> functions are shown, however).
>
> Even if you have to use the external sort, you can still avoid one
> process level by not spawning the external SQLite3 process. Collect the
> data from the Python level and sort the output (possibly, as mentioned
> elsewhere, if the data fits in memory, you can sort using Python itself
> and avoid all external processes).
>
> AH! the help file (still Python 2.7 -- though a quick glance at the P3 has
> the same text) shows:
>
> -=-=-=-=-
> create_collation(name, callable)
>
> Creates a collation with the specified name and callable. The callable
> will be passed two string arguments. It should return -1 if the first is
> ordered lower than the second, 0 if they are ordered equal and 1 if the
> first is ordered higher than the second. Note that this controls sorting
> (ORDER BY in SQL) so your comparisons don’t affect other SQL operations.
>
> Note that the callable will get its parameters as Python bytestrings,
> which will normally be encoded in UTF-8.
>
> The following example shows a custom collation that sorts “the wrong
> way”:
> ...
> -=-=-=-=-
>
> So if one can write a short comparison function that gives the desired
> order, one can extend the SQL query with
>
> ... order by <field> collate <newFunction>
Turns out such a function already exists; it's called
locale.strcoll()
Modified example from the docs:
$ cat sqlite3_collation.py
import sqlite3
import locale
print("Using locale", locale.setlocale(locale.LC_ALL, ""))
con = sqlite3.connect(":memory:")
con.create_collation("localized", locale.strcoll)
cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany(
"insert into test(x) values (?)",
"aäbAÄB")
cur.execute("select x from test order by x collate localized")
for row in cur:
print(row)
con.close()
$ python3 sqlite3_collation.py
Using locale de_DE.UTF-8
('a',)
('A',)
('ä',)
('Ä',)
('b',)
('B',)
$ PYTHONIOENCODING=UTF-8 LANG=C python3 sqlite3_collation.py
Using locale C
('A',)
('B',)
('a',)
('b',)
('Ä',)
('ä',)
--
https://mail.python.org/mailman/listinfo/python-list
