The generic version would look something like this:
table = cls.__table__
pkconditions = []
for pk in cls.SQL_PK:
pkconditions.append(table.c[pk] == sa.bindparam(pk)
condition = sa.and_(*pkconditions)
statement = sa.delete(table, whereclause=condition)
batchparams = [dict(zip(cls.SQL_PK, v)) for v in id_batch]
session.execute(statement, batchparams)
As for the portability of the textual version: your original code
produced fragments like this:
field == 'value'
a) Is the double-equals a typo, or is it syntax that SQL Server
expects? I've never used double-equals in MySQL.
b) Are the primary key fields always strings? You later said that they
were, but if you ever had an integer primary key column, comparing it
against a quoted value would be an error on postgres (I believe).
c) Can the values that you are comparing against ever contain single
quotes? That would break your query and potentially leave you open to
SQL injection.
d) Are your values unicode strings or byte strings? If unicode, does
the encoding match what the database expects?
If none of those are issues for you, the textual version is probably
pretty safe.
Simon
On Wed, Aug 30, 2017 at 5:30 PM, Ken MacKenzie <[email protected]> wrote:
> Not sure how I would iterate through a non predetermined number of primary
> keys.
>
> I guess part of me is wondering that although textual sql is not inherently
> db neutral how different between the db targets is the where field = 'value'
> syntax?
>
> On Wednesday, August 30, 2017 at 12:07:52 PM UTC-4, Simon King wrote:
>>
>> You could also try using executemany:
>>
>>
>> http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements
>>
>> I think it would look something like this:
>>
>> table = cls.__table__
>> condition = sa.and_(
>> table.c.pk1 == sa.bindparam('pk1'),
>> table.c.pk2 == sa.bindparam('pk2'),
>> )
>> statement = sa.delete(table, whereclause=condition)
>> batchparams = [{'pk1': v[0], 'pk2': v[1]} for v in id_batch]
>> session.execute(statement, batchparams)
>>
>> Simon
>>
>> On Wed, Aug 30, 2017 at 4:28 PM, Ken MacKenzie <[email protected]> wrote:
>> > After the current sorted profile finishes I will revert to the textual
>> > version and run a profile on that. I expect another 10-15 minutes for
>> > this
>> > to finish right now.
>> >
>> > At present the batch size is set to 1000, total record count is just
>> > over
>> > 9000 in these tests.
>> >
>> > The reason for 1000 was at first I was looking at doing this as a
>> > tuple_(fld, fld).in_((val, val),(val,val)) format. The 1000 should keep
>> > me
>> > under most DB restrictions on the in statement.
>> >
>> > However since SQL Server does not seem to support the tuple_ usage I
>> > reverted to this method.
>> >
>> > I technically have one more method and that is a concat_ in_ where I
>> > concat
>> > the fields.
>> >
>> > Other specifics, the table in question has 2 fields for the PK, both are
>> > varchar, one length 3, the other length 10. There are 5 non key fields,
>> > 3
>> > short varchars, one decimal at 14,2 precision and one varchar(800) which
>> > contains description text.
>> >
>> > Total record count of the table before any deletion is about 1.05
>> > million.
>> >
>> > Python version is 3.4.5, running on a modest CentOS desktop and to be
>> > fair
>> > the SQL Server instance is sub optimal for development.
>> >
>> > On Wednesday, August 30, 2017 at 11:18:13 AM UTC-4, Simon King wrote:
>> >>
>> >> It would be interesting to see the profile of the textual SQL version.
>> >> It looks like most of the time is being spent inside pyodbc, rather
>> >> than SQLAlchemy, so I guess it must be something to do with the
>> >> processing of bind parameters. How many parameters are being sent in
>> >> per query? ie. what is len(id_batch) * len(cls.SQL_PK)?
>> >>
>> >> You could try playing with your batch sizes to see what sort of effect
>> >> that has.
>> >>
>> >> Simon
>> >
>> > --
>> > 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.
--
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.
