I recently ran into a case where I wanted a very specific collation used
for sorting something and I found I had to double-quote the collation
value. I managed to smush things down into a self-contained test-case,
which is reproduced below.
I expected the (commented out) line containing .collate('C') to work (like
the examples, which BTW also use "en_EN" not "en_US" or "en_GB", etc.).
The (incorrect) query renders as:
SELECT "table"."column"
FROM "table" ORDER BY "table"."column" COLLATE C
which should render as:
SELECT "table"."column"
FROM "table" ORDER BY "table"."column" COLLATE "C"
I'm using SQLAlchemy 1.0.14 on Python 2.7.12 on openSUSE 42.1 (x86_64) with
PostgreSQL 9.4.6, however this was also tested on other operating systems
and postgresql versions through 9.5.4.
#! /usr/bin/python
import sqlalchemy as sa
import locale
locale.setlocale(locale.LC_COLLATE, 'C')
e = sa.create_engine('postgresql:///test', echo=True)
m = sa.MetaData()
t = sa.Table('table', m, sa.Column('column', sa.TEXT()))
with e.connect() as conn:
trans = conn.begin()
t.create(bind=conn)
# this is also the expected sort order with the C locale
rows = [
{ 'column': u'foo.com' },
{ 'column': u'foo0.com' },
]
ins = t.insert()
conn.execute(ins, rows)
#s = sa.select([t]).order_by(t.c.column.collate('C'))
s = sa.select([t]).order_by(t.c.column.collate('"C"'))
res = conn.execute(s).fetchall()
assert res == rows
trans.rollback()
--
Jon Nelson
Dyn / Principal Software Engineer
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
