Thanks Michael. I'm going to use MSString (confusingly, in my non-Microsoft database instance), unless someone suggests otherwise (say, for performance reasons). Having case-sensitive comparisons be the default seems more natural to me (coming from a Java/Python/C++ background).
On Sun, Sep 20, 2009 at 9:31 PM, Michael Bayer <[email protected]>wrote: > > > 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 -~----------~----~----~----~------~----~------~--~---
