Case-insensitive string columns in SQLAlchemy?

Question

Can I create a column without regard to a row in sqlalchemy? im using sqlite and theres probaby is a way to do this via DB by changing the sort, but I want to save it in sqlalchemy / python.

Answer 1

SQLite allows NOCASE sorting in 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 the collation () operator in the schema, but I'm not sure when you apply it.

Answer 2

SQLAlchemy apparently does not allow COLLATE clauses at the table creation stage (DDL) by default, but I finally figured out a way to get this to work on SQLAlchemy 0.6+. Unfortunately, it includes a few subclasses and decorations, but it is quite 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 row type can then be used usually to create tables:

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

Hope someone finds this helpful!

Answer 3

In SQLAlchemy 0.8, they added a sort option to all row types. The COLLATE keyword is now supported by several back-end db, including MySQL, SQLite and Postgresql. You should write something like this:

 my_table = Table('table_name', meta, Column('my_column', String(255, collate = 'NOCASE'), nullable=False)) 

https://bitbucket.org/zzzeek/sqlalchemy/issues/2276