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?

Wolph · Accepted Answer · 2018-07-26 21:08:23Z

3

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/latest/core/custom_types.html?highlight=operators#redefining-and-creating-new-operators

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)

edited Jul 26, 2018 at 21:08

answered Jan 28, 2013 at 13:20

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

4 Comments

cyphorious Over a year ago

Fantastic. Exactly what I was looking for. Now I'm going to find out if I get it to run on MySQL and MSSQL.

Wolph Over a year ago

@cyphorious: I'm not exactly sure how to do that straight away with an operator, but here's a nice example with custom types: docs.sqlalchemy.org/en/rel_0_8/core/…

Versus Over a year ago

I still do not know how to do that too, what is 'div' ? how to implement it as my custom operator? I can't use it directly in my python project. and I can not find any useful info in docs.

Wolph Over a year ago

@Versus: the documentation links have been changed unofrtunately, but this should help you: docs.sqlalchemy.org/en/latest/core/…

cms_mgr · Accepted Answer · 2013-01-28 13:20:06Z

0

Why not perform the division operation separately in your python script, saving the result to a variable, then pass the variable in your query instead of the operation?

answered Jan 28, 2013 at 13:20

cms_mgr

2,0172 gold badges18 silver badges33 bronze badges

1 Comment

cyphorious Over a year ago

Of course I could do that (altought I had to preload data from the DB). But this is not an answer to my question if there is some SQLAlchemy magic I'm not aware of. I already have a different algorithm to have a working application, but the aim of this question is to learn more about SQLAlchemy.

Collectives™ on Stack Overflow

MySQL and SQLAlchemy integer diversion in WHERE clause

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related