On Thu, Aug 31, 2017 at 2:19 PM, Ken MacKenzie <[email protected]> wrote:
> 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}))
I'd not bother with the literal_binds and just use a literal value:
pkf = [(col == literal_column("'%s'" % v)) for (col, v) in zip(cols, x)]
but also I'd look to see what the nature of "v" is, if it's like a
Unicode object or something, you might be getting bogged down on the
decode/encode or something like that. Sending as bytes() perhaps
might change that.
>
> 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.
--
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.
