Creating a case insensitive SQLAlchemy query for MS-SQL

Question

I'm currently trying to transfer a program designed for a MySQL database onto a MS-SQL database and I've run into some trouble. I discovered that MySQL does not have case sensitivity by default as MS-SQL has. This has lead to some problems with code similar to that listed below.

class Employee(Base):
    __tablename__ = "Employees"
    Id = Column(Integer(unsigned=True),
                primary_key=True, nullable=False, unique=True)
    DisplayName = Column(String(64),
                         nullable=False)
    #more columns

def get_employees(sql_session, param, columns=None, partial_match=True):
    if not columns:
        columns = [Employee.Id, Employee.DisplayName]
    clauses = []

    if partial_match:
        clauses.append(Employee.DisplayName.startswith(param))

    whereclause = and_(*clauses)
    stmt = select(columns, whereclause)
    return sql_session.execute(stmt)

I know of the SQL keyword COLLATE but I'm not sure how to implement that, or if it's even the best option to use in this situation. What recommendations would you give to create a case insensitive LIKE query using SQLAlchemy?

Python 2.7.7
SQLAlchemy 0.7.7

Matti John · Accepted Answer · 2012-12-22 01:33:52Z

2

That's a bit odd, in my experience MS SQL Server is case insensitive by default although you can optionally set it to case sensitive using the database's collation setting.

You can use COLLATE with SqlAlchemy (see here), so you should be able to do (I have not tried this myself):

clauses.append(Employee.DisplayName.startswith(collate(param, 'SQL_Latin1_General_CP1_CI_AS')))

SQL Server also supports regex-like pattern matching with LIKE queries, so alternatively you could make use of this in your param value e.g. '[vV]alue%'

edited Dec 22, 2012 at 1:33

answered Dec 22, 2012 at 0:45

Matti John

20.7k7 gold badges46 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JBolton Over a year ago

Thanks for your help, it solved the problem perfectly with MS-SQL, however it does create a minor problem. Adding the collate breaks compatibility with MySQL due to "Unknown collation". Do you have any recommendations that solves both problems?

Matti John Over a year ago

I'm afraid the collation strings are RDBMS specific, so you would need to use something different in MySQL - see the documentation here

Collectives™ on Stack Overflow

Creating a case insensitive SQLAlchemy query for MS-SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related