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.
Add a comment
|
3 Answers
In the SQLAlchemy 0.8 they added collation parameter to all String types. The COLLATE keyword is now supported by several db back-ends including MySQL, SQLite, and Postgresql. You should be able to write something like this:
my_table = Table('table_name', meta,
Column('my_column', String(255, collation='NOCASE'),
nullable=False))
Comments
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!
Comments
SQLite does allow NOCASE collation on text fields:
SQLite version 3.6.22
sqlite> create table me (name text collate nocase);
sqlite> .schema
CREATE TABLE me (name text collate nocase);
sqlite> insert into me values("Bob");
sqlite> insert into me values("alice");
sqlite> select * from me order by name;
alice
Bob
and SQLalchemy has a collation() operator on a schema, but I'm not sure when you apply it.
