Hello, I'm writing a satellite passage scheduler that has a database persistence layer to store the scheduled passages.
The DB schema is organized as follows: - A table storing the satellites (using NORAD No as Primary Key) - A table storing the ground stations where to compute the passages of the satellites - A table storing the passages of these satellites, with two foreign keys linking each passage to a Ground Station and a Satellite - A table storing all the types of constraints a satellite can have - A table storing all the types of weights (used to perform the scheduling) that can be assigned to each satellite Than I configured some association proxies (with the related `Unique Object` pattern) in order to assign the weights and the satellites as a dictionary sat.constraints['min_elevation']= 10 The details of the relationships here below: # Relationship definitions orm.mapper(Satellite, satellite, properties={ 'passages': orm.relationship(Passage, backref='satellite', order_by=passage.c.aos, cascade='all, delete-orphan'), '_constraints': orm.relationship( ConstrainedSatellite, backref='satellite', collection_class=orm.collections.attribute_mapped_collection('name' ), cascade='all, delete-orphan'), '_weights': orm.relationship( WeightedSatellite, backref='satellite', collection_class=orm.collections.attribute_mapped_collection('name' ), lazy='joined', cascade='all, delete-orphan'), '_tle': satellite.c.tle }) orm.mapper(Constraint, constraint, properties={ 'satellites': orm.relationship(ConstrainedSatellite, backref= 'constraint') }) orm.mapper(Weight, weight, properties={ 'satellites': orm.relationship(WeightedSatellite, backref='weight') }) orm.mapper(ConstrainedSatellite, constraint_satellite) orm.mapper(WeightedSatellite, weight_satellite) orm.mapper(PassageData, passage_data) orm.mapper(Passage, passage, properties={ 'angles': orm.relationship(PassageData, backref='passage', order_by=passage_data.c.time, cascade='all, delete-orphan') }, confirm_deleted_rows=False ) orm.mapper(GroundStation, ground_station, properties={ 'passages': orm.relationship(Passage, backref='ground_station', order_by=passage.c.aos, cascade='all, delete-orphan') }) # Association Proxies Satellite.constraints = association_proxy( '_constraints', 'value', creator=constrained_sat_creator ) Satellite.weights = association_proxy( '_weights', 'value', creator=weighted_sat_creator ) ConstrainedSatellite.constraint_name = association_proxy('constraint', 'name') WeightedSatellite.weight_name = association_proxy('weight', 'name') >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. 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. 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.