Case insensitive string columns in SQLAlchemy?
Question: can i create a case insensitive string column in sqlalchemy? im using sqlite,
and theres probaby a way to do it through DB by changing collation, but i want
to keep it in sqlalchemy/python.


Answer: SQLAlchemy doesn't seem to allow COLLATE clauses at the table creation (DDL)
stage by default, but I finally figured out a way to get this working on
SQLAlchemy 0.6+. Unfortunately, it involves a bit of subclassing and
decorating, but it's reasonably compact.

    
    
    from sqlalchemy import *
    from sqlalchemy.ext.compiler import compiles
    from sqlalchemy.types import TypeDecorator
    
    class CI_String(TypeDecorator): 
      """ Case-insensitive String subclass definition"""
      impl = String 
      def __init__(self, length, **kwargs):
          if kwargs.get('collate'):
              if kwargs['collate'].upper() not in ['BINARY','NOCASE','RTRIM']:
                  raise TypeError("%s is not a valid SQLite collation" % kwargs['collate'])
              self.collation = kwargs.pop('collate').upper()
          super(CI_String, self).__init__(length=length, **kwargs)
    
    @compiles(CI_String, 'sqlite')
    def compile_ci_string(element, compiler, **kwargs):
      base_visit = compiler.visit_string(element, **kwargs) 
      if element.collation:
          return "%s COLLATE %s" % (base_visit, element.collation) 
      else:
          return base_visit
    

The new string type can then be used normally for creating tables:

    
    
    just_a_table = Table('table_name', metadata,
                   Column('case_insensitive', CI_String(8, collate='NOCASE'), nullable=False))
    

Hope someone finds this useful!

