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?

What is your SQL engine? (mysql, sqlite, ...)

Nima Soroush
– Nima Soroush

2014-11-18 16:28:53 +00:00
Commented Nov 18, 2014 at 16:28 — Nima Soroush
– Nima Soroush, Commented Nov 18, 2014 at 16:28
Its SQLite. I don't think its a problem with other engines.

monostop
– monostop

2014-11-19 12:12:14 +00:00
Commented Nov 19, 2014 at 12:12 — monostop
– monostop, Commented Nov 19, 2014 at 12:12

Nima Soroush · Accepted Answer · 2014-11-19 15:22:01Z

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()))

Collectives™ on Stack Overflow

Sqlalchemy sqlite "too many SQL variables"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related