Sqlalchemy case insensitive query sql alchemy

Question

I am using sqlalchemy 0.7 and MySQL server version 5.1.63.

I have the following table on my database:

CREATE TABLE `g_domains` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

The corresponding model is :

class GDomain(Base):
    __tablename__ = 'g_domains'
    __table_args__ = {  
        'mysql_engine': 'InnoDB',  
        'mysql_charset': 'utf8',
        'mysql_collate': 'utf8_general_ci'  
    }

    id = Column(mysql.BIGINT(unsigned=True), primary_key=True)
    name = Column(mysql.VARCHAR(255, collation='utf8_general_ci'), 
                  nullable=False, unique=True)

The following query in sql alchemy returns no rows :

session.query(GDomain).filter(GDomain.name.in_(domain_set)).
limit(len(domain_set)).all()

where domain_set is a python list containing some domain names like

domain_set = ['www.google.com', 'www.yahoo.com', 'www.AMAZON.com']

Although the table has a row (1, www.amazon.com) the above query returns only (www.google.com, www.yahoo.com).

When I run the sql query :

SELECT * FROM g_domains 
WHERE name IN ('www.google.com', 'www.yahoo.com', 'www.AMAZON.com')

Do you have an idea why this is happening?

Thanks in advance

Answer 1

What is the model_domain variable? Usually it looks like this:

session.query(GDomain).filter(GDomain.name.in_(domain_set)).
limit(len(domain_set)).all()

Note that the GDomain is used in both places. Alternatively you can use aliases:

domains = orm.aliased(GDomain, name='domain')
session.query(domains).filter(domains.name.in_(domain_set))

You can always try debugging, print the query that produced by sqlalchemy (see: SQLAlchemy: print the actual query)