Re: [sqlalchemy] Temporarily disable DB persistence for optimization routine
On Thu, Jan 4, 2018 at 12:05 PM, Ruben Di Battista wrote: > From the tests everything is working as expected. The problem is that I'm > getting some performance issues while performing the scheduling procedure: > this procedure needs to read the `constraints` and `weights` associated to > each satellite to select the best passages among all the ones possible on > each ground station. While reading the values of the `constraints` and > `weights`, SQLAlchemy needs to store the Passage object in the Database. So > all the passages are stored in the database during the propagation of all > the possible passages, and then I need to manually expunge or delete the > passages that haven't been scheduled from the DB. I don't understand this requirement. Why are you putting objects into the session that don't need to be persisted (or are you persisting them in the transaction then wishing to roll them back? ) I would not fill the session with objects I don't want, then expunge them, I'd just not associate them in the first place. Also, "expunge" and "delete" are totally different operations, they don't do the same thing; one implies that you've INSERTED a row and the other does not. It's not clear what you are actually trying to do. If you are trying to create new objects that you want to do things like lazy load from, but they aren't "real" rows, there's a method enable_relationship_loading() http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading that can do this, but I don't really know if that's what you need. If OTOH you are persisting these objects within a transaction and want to delete them before the transaction commits, just store their primary keys and emit one big DELETE statement with all their primary key values within an IN clause. > > What I would like to achieve is to "shut down", after selecting the ground > stations and the satellites for which to perform the optimization, the > SQLAlchemy persistence in order to perform the optimization procedure only > on Python objects, without having SQLAlchemy to store them in the DB, and > then just storing efficiently the optimized, smaller, list of them that are > computed by the scheduling algorithm. OK...just don't add() them to the session. I don't see why you are adding them if you dont want to persist them. > > Currently the scheduling script is like this (schematically): > > > # Create a DB session > session_factory = orm.sessionmaker(db) > session = orm.scoped_session(session_factory) > > > # Retrieve satellites from DB > sats = session.query(Satellite).all() > > > # Retrieve gss > ground_stations = session.query(GroundStation).all() > > > # Init Scheduler instance > scheduler = Scheduler(sats, ground_stations, start_day) > > > # This methods generates all the possible passages of all satellites on > all ground stations > # it needs to read `constraints` and `weights` for each satellite to > perform what it needs. > # currently all the passages get stored in the DB > all_passages = scheduler.propagate() > > > # This method selects from the totality of all_passages, a subset that > fulfills all > # the constraints. > scheduled_passages = prop.schedule(all_passages, iterations, pool) > > > # Remove from session passages not scheduled > # === This is the thing I would like to avoid since it's slow === > for passage in all_passages: > if passage not in scheduler_passages: > session.expunge(passage) > session.delete(passage) > > > session.commit() > session.close() > > As you can see, I need to manually expunge or delete the passages that are > not scheduled by the algorithm. If the number of passages is huge, that > means performing a DELETE query for each passage, that is slow. What I would > like to achieve is to have `scheduler.propagate`, and `scheduler.schedule` > method to perform operations only on Python objects (fully loaded with all > the correct parameters from the DB), avoiding the need to store and then > delete all the passages in the DB. Can I "shut down" temporarily the DB > persistence? > > Thanks for you help, > RdB > > PS: I'm on MySQL (MariaDB) > > > > > > > > -- > 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] Join on filter for chained loads
Hi I have following relation A -> B (with filter) A -> C -> A -> B (with filter) and I want to load this in one query session.Query(A).join(A.b, and_(A.id == B.parent_id, B.field == 123)). options(contains_eager(A.b), joinedload(A.c).joinedload(C.a).joinedload(A.b )) how could I provide custom join for second load of A.b with filter (same as first one) -- 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.
Re: [sqlalchemy] group by and order by aliasing on custom dialects
On Fri, Jan 5, 2018 at 10:03 AM, Florian Apolloner wrote: > Hi Mike, > > you are to fast for me! Those work, thanks. > > Aside from the oracle requirements I also get: > ``` > 'Requirements' object has no attribute 'order_by_col_from_union' > ``` > -- should that be added to the base requirements with exclusions.open? if it's being referenced in lib/sqlalchemy/testing/suite then yes, everything there has to be in the publicly exported requirements.py > > Cheers, > Florian > > On Thursday, January 4, 2018 at 10:30:50 PM UTC+1, Mike Bayer wrote: >> >> Please see the merges: >> >> https://gerrit.sqlalchemy.org/622 >> https://gerrit.sqlalchemy.org/621 >> >> which should resolve both of these. >> >> >> >> On Thu, Jan 4, 2018 at 10:37 AM, Mike Bayer >> wrote: >> > On Thu, Jan 4, 2018 at 5:46 AM, Florian Apolloner >> > wrote: >> >> Hi there, >> >> >> >> I am writing a custom dialect and sqlalchemy currently generates a >> >> statement >> >> like this: >> >> >> >> ``` >> >> SELECT count(some_table.id) AS count_1, some_table.x + some_table.y AS >> >> lx >> >> FROM some_table GROUP BY some_table.x + some_table.y ORDER BY lx >> >> ``` >> >> >> >> As you can see it uses the alias lx in ORDER BY but not in GROUP BY. Is >> >> there any way to tell it to use the alias in GROUP BY instead of the >> >> raw >> >> expression (my database doesn't seem to support those expressions in >> >> group >> >> by)? >> >> The test I am currently running against is test_group_by_composed from >> >> the >> >> sqlalchemy test suite. >> > >> > OK the answer for now is to disable this test, and I will add a >> > requirements rule so that people stop hitting this, as the identical >> > situation occurred in october: >> > >> > >> > https://groups.google.com/forum/#!searchin/sqlalchemy/group$20by$20dialect|sort:date/sqlalchemy/r4X7ddN4rgA/c4HMI2qhBAAJ >> > >> > you can also see background in that thread for how to actually get the >> > "GROUP BY ix" syntax if you wanted to do so (though not in that test). >> > >> >> >> >> Also I needed to add: >> >> ``` >> >> @property # WTF >> >> def broken_cx_oracle6_numerics(self): >> >> return exclusions.closed() >> >> ``` >> >> and others to the requirements of my dialect to get the testsuite >> >> running at >> >> all. Shouldn't sqlalchemy ship with sane default requirements (I mainly >> >> followed >> >> https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst )? >> > >> > that's also a bug, that rule should have been removed before the 1.2 >> > release as it should no longer be necessary. >> > >> >> >> >> Thanks and best regards, >> >> Florian >> >> >> >> -- >> >> 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 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.
Re: [sqlalchemy] trouble with a self-referential query and remote side population
On Thursday, January 4, 2018 at 10:58:49 AM UTC-5, Mike Bayer wrote:
>
> that and, when you use contains_eager you need to tell it what entity
> it's looking for when it considers columns as part of a relationship:
>
> .options(sqlalchemy.orm.contains_eager('foo_alt', alias=Foo_2))\
>
As always, THANK YOU SO MUCH, MIKE. That alias kwarg was the missing bit.
The `foreign` was left over from the original query and wasn't removed by
accident (the original doesn't fkey on a primary for the join).
--
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.
Re: [sqlalchemy] group by and order by aliasing on custom dialects
Hi Mike, you are to fast for me! Those work, thanks. Aside from the oracle requirements I also get: ``` 'Requirements' object has no attribute 'order_by_col_from_union' ``` -- should that be added to the base requirements with exclusions.open? Cheers, Florian On Thursday, January 4, 2018 at 10:30:50 PM UTC+1, Mike Bayer wrote: > > Please see the merges: > > https://gerrit.sqlalchemy.org/622 > https://gerrit.sqlalchemy.org/621 > > which should resolve both of these. > > > > On Thu, Jan 4, 2018 at 10:37 AM, Mike Bayer > wrote: > > On Thu, Jan 4, 2018 at 5:46 AM, Florian Apolloner > wrote: > >> Hi there, > >> > >> I am writing a custom dialect and sqlalchemy currently generates a > statement > >> like this: > >> > >> ``` > >> SELECT count(some_table.id) AS count_1, some_table.x + some_table.y AS > lx > >> FROM some_table GROUP BY some_table.x + some_table.y ORDER BY lx > >> ``` > >> > >> As you can see it uses the alias lx in ORDER BY but not in GROUP BY. Is > >> there any way to tell it to use the alias in GROUP BY instead of the > raw > >> expression (my database doesn't seem to support those expressions in > group > >> by)? > >> The test I am currently running against is test_group_by_composed from > the > >> sqlalchemy test suite. > > > > OK the answer for now is to disable this test, and I will add a > > requirements rule so that people stop hitting this, as the identical > > situation occurred in october: > > > > > https://groups.google.com/forum/#!searchin/sqlalchemy/group$20by$20dialect|sort:date/sqlalchemy/r4X7ddN4rgA/c4HMI2qhBAAJ > > > > > you can also see background in that thread for how to actually get the > > "GROUP BY ix" syntax if you wanted to do so (though not in that test). > > > >> > >> Also I needed to add: > >> ``` > >> @property # WTF > >> def broken_cx_oracle6_numerics(self): > >> return exclusions.closed() > >> ``` > >> and others to the requirements of my dialect to get the testsuite > running at > >> all. Shouldn't sqlalchemy ship with sane default requirements (I mainly > >> followed > >> https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst > )? > > > > that's also a bug, that rule should have been removed before the 1.2 > > release as it should no longer be necessary. > > > >> > >> Thanks and best regards, > >> Florian > >> > >> -- > >> 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.
Re: [sqlalchemy] Extra table name in FROM clause
Here is SQL that works as expected:
SELECT
m.*,
d.*
FROM (
SELECT
person_id,
service_id,
person_id_internal,
meters_readings, -- two-dimensional array
meters_readings [generate_subscripts(meters_readings, 1)] [1] AS meter_id,
meters_readings [generate_subscripts(meters_readings, 1)] [2] AS
organization_reading,
date
FROM
databases
) d LEFT OUTER JOIN LATERAL (
SELECT DISTINCT ON (service_id, person_id_internal, meter_id)
user_id,
reading
FROM meter_readings
WHERE
person_id = d.person_id AND
service_id = d.service_id AND
meter_id = d.meter_id AND
commit_date > d.date AND
rollback_date IS NULL AND
reading IS NOT NULL
ORDER BY
service_id,
person_id_internal,
meter_id,
commit_date DESC,
commit_time DESC
) m ON TRUE
WHERE
d.person_id = 2099000301470;
And that is SA query that also works :) I managed to write it ))
def get_person_meters_q(dbsession, person_id):
database = dbsession.query(
Database.person_id,
Database.service_id,
Database.person_id_internal,
Database.date,
(
type_coerce(
Database.meters_readings[
func.generate_subscripts(Database.meters_readings, 1)
],
type_=ARRAY(Text)
)[1]
).label('meter_id'),
(
type_coerce(
Database.meters_readings[
func.generate_subscripts(Database.meters_readings, 1)
],
type_=ARRAY(Text)
)[2]
).label('organization_reading')
).subquery()
meter_readings = dbsession.query(
MeterReading.user_id,
MeterReading.reading
). \
distinct(
MeterReading.service_id,
MeterReading.person_id_internal,
MeterReading.meter_id). \
filter(
and_(
MeterReading.person_id == database.c.person_id,
MeterReading.service_id == database.c.service_id,
MeterReading.meter_id == database.c.meter_id,
MeterReading.commit_date > database.c.date,
MeterReading.rollback_date == None,
MeterReading.reading != None
)
). \
order_by(
MeterReading.service_id,
MeterReading.person_id_internal,
MeterReading.meter_id,
MeterReading.commit_date.desc(),
MeterReading.commit_time.desc()
).subquery().lateral()
q = dbsession.query(
meter_readings,
database
). \
select_from(
database.outerjoin(meter_readings, true())
).filter(database.c.person_id == person_id)
return q
The problem with extra "database" table name in select was because I use
filter(Database.person_id == person_id)
but not filter(database.c.person_id == person_id)
About LATERAL
https://www.postgresql.org/docs/current/static/sql-select.html
The LATERAL key word can precede a sub-SELECT FROM item.
This allows the sub-SELECT to *refer to columns of FROM items **that appear
before it* in the FROM list.
(Without LATERAL, each sub-SELECT is evaluated independently and so cannot
cross-reference any other FROM item.)
So I refer columns that are in database query (first) from meter_readings query
(second select)
Without LATERAL I get:
ERROR: invalid reference to FROM-clause entry for table "d"
Hint: There is an entry for table "d", but it cannot be referenced from this
part of the query.
And that is SQL query result:
\N \N 2099000153759 75 952160 {{952160,1140,0}} 952160
11402017-11-30
\N \N 2099000153759 2 27852 {{219935,14768,0}} 219935
14768 2017-11-30
\N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}}
468805 57 2017-11-30
\N \N 2099000153759 4 206688 {{468805,57,0},{589164,73,0}}
589164 73 2017-11-30
--
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.
