which column type to use for case sensitive queries?

Showing 1-3 of 3 messages
which column type to use for case sensitive queries? John 9/20/09 8:42 PM
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
Re: [sqlalchemy] which column type to use for case sensitive queries? Michael Bayer 9/20/09 9:31 PM

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


Re: [sqlalchemy] Re: which column type to use for case sensitive queries? John Fries 9/21/09 7:48 AM
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).