Sqlalchemy sqlite "too many SQL variables"

Question

I have two entities in a one-to-many relationship. There are instances where one of the entities has more than 999 child entities. I want to delete the entity and its child entities but get a problem with sqlite maximum sql variables:

connection.execute(child.delete().where(child.c.parent_id == parent_id))
connection.execute(parent.delete().where(parent.c.id == parent_id))

sqlalchemy.exc.OperationalError:
OperationalError: (OperationalError) too many SQL variables u'DELETE FROM child WHERE child.parent_id IN (?,?...

I have found that the maxium sql variables in sqlite is 999 so that's where my problem is. I read the answer in another question that this is a modeling problem (https://stackoverflow.com/a/8433514/931325), but I don't see how I can model my data in a different way.

What are my options to solve this problem?

Answer 1

Try to delete the rows in different chunks. You can use limit(999) as maximum SQLITE_LIMIT_VARIABLE_NUMBER is equal to 999. This is pesudo of what you need to do

from sqlalchemy import func, in_

# getting the amount of total rows
stmt = select([func.count(child.c.parent_id).label('num_rows_childs')]).where(child.c.parent_id == parent_id)
number_of_rows = conn.execute(stmt).fetchall()

# Getting quotient and reminder of all rows on 999
# This gives you the number of times that delete() should run
# div_mod[0] is the quotient
# div_mod[1] is the reminder
div_mod = divmod(number_of_rows[0], 999)

for x in xrange(div_mod[0]):
    child.delete().where(child.c.parent_id.in_(select([child.c.parent_id]).where(child.c.parent_id == parent_id).limit(999).all()))
else:
    child.delete().where(child.c.parent_id.in_(select([child.c.parent_id]).where(child.c.parent_id == parent_id).limit(div_mod[1]).all()))