MySQL and SQLAlchemy integer diversion in WHERE clause
Question: I have a table that contains a column called `region_code` with `Integer` as
its datatype. In my Python program I have this line:

    
    
    region = session.query(Region).filter(Region.region_code/100 == region_prefix).one()
    

The important part here is the chosen **filter** method

    
    
    Region.region_code/100 == region_prefix
    

where region_code is the integer column in the table and the region_prefix
variable holds an integer value

The `region_code` is the integer column in the table containing values like
`1199, 1200, 1300, 1499` while the region_prefix variable (from the program)
holds integer values like `11, 12, 13, 14`.

What I now want to achive with this filter option is to perform an integer
division on `region_code` (hence cut everything behind the decimal place) to
be able to compare it with `region_prefix`.

  
**Example:**

    
    
    region_prefix = 11
    

Assume that in the Region table exists a entry with

    
    
    region_code = 1199
    

The calculation within the filter should look like this

    
    
    1199 / 100 = 11
    

So with this result I am able to filter for `region_prefix (11=11)`. **This
works just fine with MSSQL (Microsoft).**

  
Unfortunately (in my case) **MySQL** division operator `/` does not do integer
divisions. So the result of `1199/100` will be `11.99` and so my query will
not find a fitting entry in the DB (because 11.99 != 11). Nevertheless MySQL
actually has the inter division implemented: the **DIV** operator.

So the following SQL query works just fine for me:

    
    
    SELECT * FROM Region where region_code DIV 100 = 11;
    

  
**So my question is:** _How do I get the SQLAlchemy`filter` method to use
`DIV` instead of `/` operator? Is this possible? Or is the only workaround to
use native queries?_


Answer: Luckily SQLAlchemy allows for custom operators. There are simple ways of doing
this (simply specifying in the query) or more complex versions of doing it
(fully defining the new operator and making it multi database aware). So it
depends on what you're looking for.

<http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html#sqlalchemy.sql.expression.BinaryExpression.op>

    
    
    region = (session
        .query(Region)
        .filter(Region.region_code.op('div')(region_prefix))
        .one()
    )
    

And the fancy way:

<http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators>

    
    
    from sqlalchemy import Integer
    
    class DivisibleInteger(Integer):
        class comparator_factory(Integer.Comparator):
            def div(self, other):
                return self.op('div')(other)
    

