I tried an alternate version:
@classmethod
def dict_delete2(cls, dict_in):
"""
Delete records from the database
based on a dictionary keyed by
PK tuple
"""
s = dbc.get_session()
keys_in = ["-".join(k) for k in dict_in.keys()]
batch_size = 1000
cols = [getattr(cls, colname) for colname in cls.SQL_PK]
cols_dash = []
for col in cols:
cols_dash.append(col)
cols_dash.append('-')
cols_dash = cols_dash[:-1]
print(cols_dash)
while len(keys_in):
id_batch = keys_in[:batch_size]
# print(id_batch)
del keys_in[:batch_size]
q = s.query(cls)
q =
q.filter(func.concat(*cols_dash).in_(id_batch)).delete(synchronize_session=False)
s.commit()
s.close()
Performance was even worse?
673960 function calls (659542 primitive calls) in 2315.534 seconds
Ordered by: internal time
ncalls tottime percall cumtime percall filename:lineno(function)
16 2312.810 144.551 2312.810 144.551 {method 'execute' of
'pyodbc.Cursor' objects}
9567 0.186 0.000 0.186 0.000 {method 'fetchone' of
'pyodbc.Cursor' objects}
9572 0.166 0.000 0.433 0.000 elements.py:861(__init__)
1 0.119 0.119 2315.536 2315.536 del_test.py:1(<module>)
62442/61837 0.101 0.000 0.118 0.000 {built-in method isinstance}
9572 0.068 0.000 0.407 0.000
compiler.py:1164(visit_bindparam)
9575 0.062 0.000 0.171 0.000 elements.py:3962(__new__)
9561 0.060 0.000 0.492 0.000 functions.py:436(_bind_param)
9574 0.059 0.000 0.090 0.000
compiler.py:1265(_process_anon)
9574 0.054 0.000 0.227 0.000
compiler.py:1246(_truncated_identifier)
10 0.052 0.005 0.738 0.074
default_comparator.py:110(_in_impl)
9562 0.051 0.000 0.319 0.000 result.py:1156(fetchone)
9655 0.049 0.000 0.084 0.000 elements.py:3918(__new__)
9572 0.047 0.000 0.291 0.000
compiler.py:1233(_truncate_bindparam)
2 0.047 0.023 0.048 0.024 {built-in method connect}
9574 0.047 0.000 0.163 0.000 elements.py:4073(apply_map)
10 0.042 0.004 0.042 0.004 {method 'commit' of
'pyodbc.Connection' objects}
40649 0.040 0.000 0.040 0.000 {method 'append' of 'list'
objects}
9572 0.039 0.000 0.049 0.000
compiler.py:1271(bindparam_string)
9696/14 0.039 0.000 0.521 0.037
visitors.py:75(_compiler_dispatch)
9611 0.039 0.000 0.485 0.000 compiler.py:806(<genexpr>)
14090/13802 0.037 0.000 0.042 0.000 {built-in method hasattr}
17697/17695 0.037 0.000 0.038 0.000 {built-in method getattr}
9562 0.037 0.000 0.058 0.000 result.py:1085(process_rows)
9561 0.036 0.000 0.080 0.000 elements.py:4254(_is_literal)
28683 0.036 0.000 0.036 0.000 del_test.py:14(<genexpr>)
9562 0.030 0.000 0.068 0.000
type_api.py:504(coerce_compared_value)
22 0.029 0.001 0.103 0.005 elements.py:1784(<listcomp>)
12713 0.027 0.000 0.536 0.000 {method 'join' of 'str'
objects}
9572 0.026 0.000 0.038 0.000
sqltypes.py:2608(_resolve_value_to_type)
16 0.026 0.002 0.026 0.002 {method 'close' of
'pyodbc.Cursor' objects}
9574 0.026 0.000 0.116 0.000
_collections.py:728(__missing__)
9584 0.026 0.000 0.067 0.000 compiler.py:494(<genexpr>)
21325 0.025 0.000 0.025 0.000 {method 'get' of 'dict'
objects}
125 0.025 0.000 0.025 0.000 {built-in method loads}
750/724 0.025 0.000 0.338 0.000 {built-in method
__build_class__}
9572 0.025 0.000 0.041 0.000
type_api.py:452(_cached_bind_processor)
9562 0.024 0.000 0.343 0.000 result.py:868(__iter__)
9564 0.022 0.000 0.209 0.000 result.py:1053(_fetchone_impl)
9603 0.022 0.000 0.022 0.000 elements.py:640(self_group)
10206 0.021 0.000 0.021 0.000 {method 'split' of 'str'
objects}
9562 0.021 0.000 0.021 0.000 result.py:1098(<listcomp>)
9611 0.021 0.000 0.506 0.000 compiler.py:804(<genexpr>)
10421 0.020 0.000 0.020 0.000 {built-in method __new__ of
type object at 0x7fc6ee607e40}
9603 0.020 0.000 0.053 0.000
elements.py:4216(_expression_literal_as_text)
9624 0.019 0.000 0.033 0.000
elements.py:4220(_literal_as_text)
18953/18838 0.019 0.000 0.019 0.000 {built-in method len}
10209 0.016 0.000 0.016 0.000 weakref.py:364(__getitem__)
290/1 0.016 0.000 2315.538 2315.538 {built-in method exec}
68 0.016 0.000 0.042 0.001 inspect.py:264(getmembers)
361 0.014 0.000 0.102 0.000 inspect.py:943(getfullargspec)
2884 0.014 0.000 0.019 0.000 sre_parse.py:197(__next)
9561 0.014 0.000 0.014 0.000 result.py:168(keys)
361 0.012 0.000 0.064 0.000 inspect.py:2383(from_function)
>
--
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.
