How to make filter () match exact cases (case sensitivity) in sqlalchemy?

Question

I am using sqlalchemy with mysql database. When I use the following query for a User object:

session.query(User).filter(User.name == 'admin').all()

I get all the results that have a username like "Administrator", "Administrator", "ADMIN" (basically, not case-sensitive "admin"). I would like to know how to make filter () filter exact matches (without ignoring cases)?

Update: in fact, I only found out that mysql does not allow case-sensitive columns for the varchar () data type. Thus, the simplest solution would be to make the column be case sensitive when declared in mysql, for example:

  `name` VARCHAR(255) BINARY NULL UNIQUE,

But I would still like to know how to get the filter to exactly match the results (without ignoring cases). Is there anyway using the built-in (or custom, if possible) sqlalchemy function?

Answer 1

select * from users where binary name = 'AdMin';
select * from users where binary name = 'admin';


from sqlalchemy import func
User.query.filter(User.name == func.binary('AdMin')).count()
User.query.filter(User.name == func.binary('admin')).count()

Answer 2

Typically, what SQLAlchemy does is build a SQL string query from expressions. By freely typing your expression above, you will get something like this:

SELECT user.* FROM user WHERE user.name = 'admin'

Of course, user.*I would replace by explication of all columns user.

, , : , SQLAlchemy. , , , WHERE BINARY user.name = 'admin'. , SQLAlchemy, .

, SQLAlchemy (, , BINARY WHERE).