Re: [sqlalchemy] Is Textual SQL DB neutral

When I get in tomorrow I will take a look at some of these options. I also intend to spin up a quick MySQL or postgresql db to test if my textual solution can work against an alternate target. Sent from my iPad > On Aug 30, 2017, at 6:20 PM, Jonathan Vanasco wrote: > >

Re: [sqlalchemy] Is Textual SQL DB neutral

Looking at your code and considering the size of your database, I've run into performance issues under PostgreSQL a few times that may be related... they're all usually because very slight text changes to a query can drastically alter the server's query planner. The cause/fixes were: 1.

Re: [sqlalchemy] Is Textual SQL DB neutral

When you call create_engine: e = create_engine(url, echo=True) include the "echo=True" part. Then look at the SQL being emitted. That's the important part here. On Wed, Aug 30, 2017 at 5:09 PM, Ken MacKenzie wrote: > So the big difference between the methods when

Re: [sqlalchemy] Is Textual SQL DB neutral

So the big difference between the methods when seen with print(q) Textual SQL is building a statement with the values inline such as where college=888 and check=1234567 The methods using the column object are building with parameters where college = (?) and check = (?) That is all I can see

Re: [sqlalchemy] Is Textual SQL DB neutral

With a batch size of 1 based on the original selection, no improvement. I am going to see what sql this is creating. 2328928 function calls (2085124 primitive calls) in 1084.925 seconds Ordered by: internal time ncalls tottime percall cumtime percall

Re: [sqlalchemy] Is Textual SQL DB neutral

I am noticing the 16 execute calls. There should only be 10 with a batch size of 1000 and a record size of 9,000 and some change. Are you just wanting to see the sql via a print(q)? On Wednesday, August 30, 2017 at 2:01:31 PM UTC-4, Mike Bayer wrote: > > it looks like you have 16 SQL

Re: [sqlalchemy] Is Textual SQL DB neutral

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

Re: [sqlalchemy] Is Textual SQL DB neutral

it looks like you have 16 SQL statements that take a very different amount of time depending on format. Can you please paste the output with echo=True on create_engine() so that the difference in SQL statements can be seen? On Wed, Aug 30, 2017 at 12:30 PM, Ken MacKenzie

Re: [sqlalchemy] Is Textual SQL DB neutral

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,

Re: [sqlalchemy] Is Textual SQL DB neutral

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

Re: [sqlalchemy] Is Textual SQL DB neutral

Sorted Textual SQL profile ncalls tottime percall cumtime percall filename:lineno(function) 161.8400.1151.8400.115 {method 'execute' of 'pyodbc.Cursor' objects} 95720.1960.0000.7880.000 elements.py:1860(_construct) 577000.1890.000

Re: [sqlalchemy] Is Textual SQL DB neutral

Sorted: ncalls tottime percall cumtime percall filename:lineno(function) 16 1053.794 65.862 1053.794 65.862 {method 'execute' of 'pyodbc.Cursor' objects} 348916/3483110.5390.0000.5560.000 {built-in method isinstance} 160920/1609180.3130.0000.313

Re: [sqlalchemy] Is Textual SQL DB neutral

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

Re: [sqlalchemy] Is Textual SQL DB neutral

After I finish the current profile run I will revert and profile that one for comparison. I am expecting another 10-15 minutes for this to finish. 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. >

Re: [sqlalchemy] Is Textual SQL DB neutral

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

Re: [sqlalchemy] Is Textual SQL DB neutral

So I implemented this version with one change. I moved this line: cols = [getattr(cls, colname) for colname in cls.SQL_PK] To the beginning before the loop, actually before the outer loop that parses the batch. However, the new version is MUCH slower than the original, so I profiled it and

Re: Rename an existing constraint

On Wed, Aug 30, 2017 at 7:18 AM, wrote: > Given an Alembic migration, would you recommend the following code to rename > constraints? > > from alembic import op > import sqlalchemy as sa > from srv.orm.meta import NAMING_CONVENTION # as per Pylons cookiecutter > template

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

I'm trying to have to objects with a one-to-one relationship, e.g. User and Address, with a UniqueConstraint for user.id in the address table (names changed from the real code). But when user.address is updated, the commit fails because the UniqueConstraint is broken, probably because

Re: Rename an existing constraint

Given an Alembic migration, would you recommend the following code to rename constraints? from alembic import op import sqlalchemy as sa

Re: [sqlalchemy] Is Textual SQL DB neutral

On Tue, Aug 29, 2017 at 9:49 PM, Ken MacKenzie wrote: > I have a query I have constructed and I had to deal with a composite primary > key to select items; > > q = s.query(cls) > or_cond = [] > for x in id_batch: > > pkf =