Using pipe in a system call

[Peter Otten]

Dennis Lee Bieber wrote:

> On Fri, 09 Oct 2015 08:56:18 +0200, Cecil Westerhof <Cecil at 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',)
('Ä',)
('ä',)