which column type to use for case sensitive queries?

[John]

SQLAlchemy,

I'm looking here http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html
for info on which column type to use such that my queries will be case
sensitive, but not having luck changing it from the case insensitive
default. I'm sure this question has been asked a billion times (since
everyone who creates a password field must solve it), but somehow I
can't find the answer.

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.

Thanks,
John

[Michael Bayer]

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).

[John Fries]

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).