So here is my solution as of now. As far as I can tell hits the marks of:
1. DB Neutral
2. Performance (9k records deleted in 5 seconds)
3. Generic
(excuse the print statements still there for testing)
Would welcome comments on this proposed solution. I have learned a lot
from the advice in this thread and can always stand to learn more for the
sake of my code.
@classmethod
def dict_delete(cls, dict_in):
"""
Delete records from the database
based on a dictionary keyed by
PK tuple
"""
s = dbc.get_session()
d_start = datetime.datetime.now()
keys_in = [k for k in dict_in.keys()]
batch_size = 1000
cols = [getattr(cls, colname) for colname in cls.SQL_PK]
while len(keys_in):
id_batch = keys_in[:batch_size]
del keys_in[:batch_size]
or_cond = []
for x in id_batch:
pkf = [(col == v) for (col, v) in zip(cols, x)]
and_cond = and_(*pkf)
or_cond.append(and_cond)
stmt = cls.__table__.delete(or_(*or_cond))
stmt_comp = str(stmt.compile(dialect=s.bind.dialect,
compile_kwargs={"literal_binds": True}))
print(stmt_comp)
print(len(keys_in), "-", datetime.datetime.now() - d_start)
s.execute(stmt_comp)
s.commit()
s.close()
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
