On Sep 20, 2009, at 11:42 PM, John wrote:
>
> I create mytable like this:
> mytable = Table('mytable', metadata, Column('mycolumn', String(512)))
> class MyTable:
> pass
> mapper(MyTable, mytable)
>
> But when I run "session.query(MyTable).filter(MyTable.mycolumn ==
> "Foo")", it not only return the rows that match "Foo", but also the
> rows that match "foo".
>
> Is there a standard solution? I see some advice to modify, not the
> column at creation time, but the query at query time so as to provide
> case-sensitive matching on a per query basis. Some online references
> suggest setting "collate = utf8_bin" (but that's MS specific), or
> character set to binary, etc. Other references suggest passing in a
> character_set=utf-8 at the time sqlalchemy connects to the database,
> so as to change the default character set (http://www.sqlalchemy.org/
> docs/05/reference/dialects/mysql.html#character-sets). I am not very
> familiar yet with mysql, character sets, unicode, utf-8, latin1, etc.
>
> I am using mysql. I can run
> "ALTER TABLE mytable MODIFY mycolumn VARCHAR(512) COLLATE utf8_bin;"
> or
> "ALTER TABLE mytable MODIFY mycolumn VARCHAR(512) CHARACTER SET
> BINARY;" either of which seems to fix the problem, but I'd rather do
> it from within sqlalchemy since that is how I normally create my
> tables.
for MySQL we have the "COLLATE" operator that is good for at query
time. Here are their docs:
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
SQLA Column objects offer a collate() method to provide this, so you
can say:
column.collate('latin1_german2_ci') == "foo"
If you want to issue COLLATE when you create your tables, you can use
the MSString() datatype that in the 0.5 series you can import from
sqlalchemy.databases.mysql. For example you can use a case sensitive
collation, and then for a case insensitive match use either collate()
again, or just func.lower(mycolumn) == func.lower(myvalue).
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
