Yes the double quote was a typo, sorry about that. Your point about non varchar pk, that is why I will double check other dbs. SQL server lets it fly but I think you are right about Postgres.
Sent from my iPad > On Aug 31, 2017, at 5:36 AM, Simon King <si...@simonking.org.uk> wrote: > > 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 <deviloc...@gmail.com> 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 <devil...@gmail.com> 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 sqlalchemy+...@googlegroups.com. >>>> To post to this group, send email to sqlal...@googlegroups.com. >>>> 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 sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> 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 a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/lAT3Epi7Fkw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
