[sqlite] Order with another sequence
> When I use ORDER BY an ? comes after a z. Is it possible to make an ? come
> after a z?
> If it is important I am using SQLite 3.8.6 and Python 3.4.1.
If you're using the Python built-in sqlite3 module, you're looking for
create_collation
https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_collation
Something like this (adapted from that example in the docs):
import sqlite3
def collate(string1, string2):
return cmp(string1, string2)
con = sqlite3.connect(":memory:")
con.create_collation("mycollation", collate)
cur = con.cursor()
cur.execute("create table test(x)")
values = [u'z', u'?']
values = map(lambda x: (x,), values)
cur.executemany("insert into test(x) values (?)", values)
cur.execute("select x from test order by x collate mycollation")
for row in cur:
print row
con.close()
That still won't sort right, but now you have the collate() function as a place
to plug in your actual collation logic. For that, you want something like
Locale (https://docs.python.org/2/library/locale.html) or ICU
(https://pypi.python.org/pypi/PyICU/)
-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 801 bytes
Desc: Message signed with OpenPGP using GPGMail
URL:
[sqlite] Order with another sequence
It should be possible with a custom collation sequence. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Cecil Westerhof Gesendet: Freitag, 09. Oktober 2015 12:08 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Order with another sequence When I use ORDER BY an ? comes after a z. Is it possible to make an ? come after a z? If it is important I am using SQLite 3.8.6 and Python 3.4.1. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Order with another sequence
When I use ORDER BY an ? comes after a z. Is it possible to make an ? come after a z? If it is important I am using SQLite 3.8.6 and Python 3.4.1. -- Cecil Westerhof
[sqlite] Order with another sequence
> On Fri, Oct 9, 2015 at 5:08 AM, Cecil Westerhof > wrote: > > > When I use ORDER BY an ? comes after a z. Is it possible to make an ? > come > > after a z? > > > > If it is important I am using SQLite 3.8.6 and Python 3.4.1. > Perhaps you might garner some insight from looking at other's code? I did a search on GitHub of APSW (Another Python SQLite wrapper) by Roger Binns: https://github.com/rogerbinns/apsw/search?utf8=%E2%9C%93=collation -- -- -- --???-- K e V i N
[sqlite] Order with another sequence
On Fri, Oct 9, 2015 at 5:08 AM, Cecil Westerhof wrote: > When I use ORDER BY an ? comes after a z. Is it possible to make an ? come > after a z? > > If it is important I am using SQLite 3.8.6 and Python 3.4.1. > > -- > Cecil Westerhof > > ?I am unsure of how to do this _exactly_ as you want, but I am fairly sure what you need is the COLLATE phrase in the ORDER BY: SELECT column_name FROM table ORDER BY colum_name COLLATE collation_name ASC? The difficulty is in determining what "collation_name" needs to be. You may need to create your own. Some pages on the SQLite site which might help: ?http://sqlite.org/lang_select.html ?http://sqlite.org/syntax/ordering-term.html? https://www.sqlite.org/datatype3.html#collation https://www.sqlite.org/c3ref/create_collation.html I don't have the slightest idea if this sort of thing can be used with the supplied "sqlite3" program. It seems to be designed to be used by C programs. I've never used SQLite from Python. -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
