On 08/31/2016 09:50 AM, Jonathon Nelson wrote:
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.
Thanks for the test case.
Unfortunately, this is what's documented right now:
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=collate#sqlalchemy.sql.expression.collate
collate(mycolumn, 'utf8_bin')
produces:
mycolumn COLLATE utf8_bin
It's safe to assume the whole world is working around this on the
Postgresql side already, so any correction to this would have to be
phased in with a regular expression to check for existing quoting. I'll
gladly accept a pull request with tests that starts work on this.
Implemenation-wise, we'd need to build a new "Collation()" construct
that each dialect can intercept specifically - right now the
implementation is hardwired on the expression production side.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3785/postgresql-collate-requires-quotes
is added.
#! /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 <http://foo.com>' },
{ 'column': u'foo0.com <http://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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
