Using rank_order=True argument can be slow and can cause SQLAlchemy Operational Error

[kellyjonbrazil]

I tried using the rank_order=True argument on my SQLite3 database index (~400k rows) and for queries that return a small number of results it works great. But if there are tens of thousands of rows returned it runs very slow and can even completely fail at the SQLAlchemy/database level with the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) too many SQL variables

It seems to create a huge query that looks like the following:

[SQL: SELECT food.fdc_id AS food_fdc_id, food.data_type AS food_data_type, food.description AS food_description, food.food_category_id AS food_food_category_id, food.publication_date AS food_publication_date 
FROM food 
WHERE food.data_type = ? AND food.fdc_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,...'1053481', 126716, '350165', 126717, '350173', 126718, '397064', 126719, '408685', 126720, '456184', 126721, '476338', 126722, '476496', 126723, '529344', 126724, '591595', 126725, '600635', 126726, '604648', 126727, '611206', 126728, '611218', 126729, '626396', 126730, '715414', 126731, '760876', 126732, '760892', 126733, '888874', 126734, '900938', 126735, '958545', 126736, '998071', 126737, '349959', 126738, '404281', 126739, '610910', 126740, '760506', 126741, '826782', 126742, '406192', 126743, '439251', 126744, '439253', 126745, '942754', 126746, '471345', 126747, '541084', 126748, '600501', 126749, '717922', 126750, '888832', 126751, '505267', 126752, 10, 0)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

(I snipped out 10's of thousands of lines from the middle of the query)