[sqlalchemy] replicating/triggering a session connection issue for testing?

I discovered an edge-case in one of our apps today. A form got pummeled by a botnet which was going through a list of compromised account data for hits (e.g. credentials that match haveibeenpwned.com). That triggered a lot of internal metrics logging, which eventually a dedicated logging

Re: [sqlalchemy] Is Textual SQL DB neutral

On a side note the literal_column change worked fine performance wise allowing me to remove the literal_bind/compile step. I guess I am 50/50 on which is cleaner. Is either one more advantageous from a SQL injection perspective? SQL Injection is not a primary concern at the moment for this

Re: [sqlalchemy] Is Textual SQL DB neutral

Tried doing various conversions on the pk values as they enter the statement: 1. to bytes 2. to ascii 3. to latin1 (technically the same encoding as the extract source before entering the db) None of which yielded a performance improvement for the non-compiled version. I have read that this

Re: [sqlalchemy] Is Textual SQL DB neutral

So inspecting the elements of the tuple, they are both str, so hence unicode. Are you saying that if I convert those values to bytes it could improve performance? > I'd not bother with the literal_binds and just use a literal value: > > pkf = [(col == literal_column("'%s'" % v)) for (col,

Re: [sqlalchemy] Is Textual SQL DB neutral

On Thu, Aug 31, 2017 at 2:19 PM, Ken MacKenzie 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) > >

Re: [sqlalchemy] Is Textual SQL DB neutral

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

Re: [sqlalchemy] Is Textual SQL DB neutral

Ending output from batch size of 10: 501 - 0:24:35.558103 491 - 0:24:37.197976 481 - 0:24:38.909318 471 - 0:24:40.645801 461 - 0:24:41.954807 451 - 0:24:43.628955 441 - 0:24:45.246469 431 - 0:24:46.662228 421 - 0:24:48.192343 411 - 0:24:49.839395 401 - 0:24:51.513340 391 - 0:24:53.153668 381 -

Re: [sqlalchemy] Updating pg8000 dialect following new release of pg8000

On Thu, Aug 31, 2017 at 7:02 AM, Tony Locke wrote: > There's a new release (1.11.0) of the pg8000 driver for PostgreSQL. It's a > pure-python driver, and it already has a dialect for SQLAlchemy. This latest > release is not backwardly compatible with the previous release,

[sqlalchemy] Updating pg8000 dialect following new release of pg8000

There's a new release (1.11.0) of the pg8000 driver for PostgreSQL. It's a pure-python driver, and it already has a dialect for SQLAlchemy. This latest release is not backwardly compatible with the previous release, and I'm trying to modify the dialect accordingly. The main change is that

Re: [sqlalchemy] Is Textual SQL DB neutral

I tried a batch size as small as ten and each step took about 2 seconds. I set it up to show me the time between each batch for that run. Lowering the batch size actually seems to worsen performance. Which is not what I expected. Sent from my iPad > On Aug 31, 2017, at 5:42 AM, Simon King

Re: [sqlalchemy] Is Textual SQL DB neutral

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 wrote: > > The

Re: [sqlalchemy] Is Textual SQL DB neutral

I'm pretty sure the bad performance is due to pyodbc (or maybe SQL Server) not handling *huge* numbers of bind parameters gracefully. You are generating a query with (batch_size * n_primary_key_columns) bind parameters, which even in your original version meant 2000 bind parameters. Try with a

Re: [sqlalchemy] Is Textual SQL DB neutral

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))

Re: [sqlalchemy] One-to-one relationship with delete-orphan and update breaks UniqueConstraint

Thank you for your answer. I think detaching and doing a session.flush() is a good enough solution for now. Den onsdag 30 augusti 2017 kl. 16:31:03 UTC+2 skrev Mike Bayer: > > On Wed, Aug 30, 2017 at 7:52 AM, Levon Saldamli > wrote: > > I'm trying to have to objects with a