Databases
StackOverflow2013
Postsdbo
POHow do I maintain multi-table integrity across multiple selects in SQLAlchemy in Pyramid?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I maintain multi-table integrity across multiple selects in SQLAlchemy in Pyramid?
    primarykey
    data
    text
    <p>I'm trying to build a Pyramid application. I started with the SQLAlchemy scaffolding. I'm running into a issue and I'm wondering what the best way is to address it. In one of my views I need to select a lot of rows from two unrelated tables. I need to make sure no rows were inserted into the second table between the time I selected the rows from the first table and the time I selected the rows from the second table.</p> <p>I have three models, <code>Node</code>, <code>Test</code>, and <code>Tasking</code>. Both <code>Nodes</code> and <code>Tests</code> have quite a bit of metadata. Given a list of <code>Nodes</code> and a list of <code>Tests</code>, a global list of <code>Taskings</code> can be created. For example, we could have three <code>Nodes</code>, <code>a</code>, <code>b</code>, and <code>c</code> and two <code>Tests</code> "we need one node to do task <code>P</code>" and "we need two nodes to do task <code>Q</code>".</p> <p>From that information, three <code>Tasks</code> should be created. For example:</p> <ol> <li>"Node <code>a</code> should do task <code>P</code>"</li> <li>"Node <code>b</code> should do task <code>Q</code>"</li> <li>"Node <code>c</code> should do task <code>Q</code>"</li> </ol> <p>Now, I'm attempting to provide a REST API for this. The vast majority of time clients will be requesting the list of <code>Tasks</code>, so that needs to be fast. However, sometimes a client might add a <code>Node</code> or a <code>Test</code>. When that happens, I need the entire list of <code>Tasks</code> to be regenerated.</p> <p>Here's a rough example:</p> <pre><code>@view_config(route_name='list_taskings') def list_taskings(request): return DBSession.Query(Tasking).all() @view_config(route_name='add_node') def add_node(request): DBSession.add(Node()) _update_taskings() @view_config(route_name='add_test') def add_test(request): DBSession.add(Test()) _update_taskings() def _update_taskings(): nodes = DBSession.query(Node).all() tests = DBSession.query(Test).all() # Process... Tasking.query.delete() for t in taskings: DBSession.add(t) </code></pre> <p>I'm using the default Pyramid SQLAlchemy scaffolding. So, each request auto starts a transaction. So, if <code>_update_tasking</code> is called from one request (say <code>add_node</code>), then the new node will be added to the local <code>DBSession</code>, and querying for all <code>Nodes</code> and <code>Tests</code> in <code>_update_tasking</code> will return that new element. In addition, deleting all existing <code>Taskings</code> and adding the newly computed ones is also safe.</p> <p>I have two problems:</p> <ol> <li><p>What happens if a new row is added into the <code>Tests</code> table between the time I get my list of <code>nodes</code> and my list of <code>tests</code> in <code>_update_taskings</code>? In my real world production system these selects are close together but not right next to each other. There is the possibility of a race condition.</p></li> <li><p>How do I ensure two requests that will update the <code>Taskings</code> don't overwrite each other? For example, imagine if our existing system had one <code>Node</code> and one <code>Test</code>. Two requests come in at the same, one to add a <code>Node</code> and one to add a <code>Test</code>. Even if problem #1 wasn't an issue and I knew that each request's pair of selects represented "a single instance of time in the database", there's still the problem of one request overriding the other. If the first request finishes first with now two <code>Nodes</code> and one <code>Test</code>, the second request will still be selecting the old data (potentially) and will generate a list of <code>Taskings</code> with one <code>Node</code> and two <code>Tests</code>.</p></li> </ol> <p>So, what's the best way to deal with this? I'm using SQLite for development and PostgreSQL in production, but I'd like a database agnostic solution. I'm not worried about other applications accessing this database. My REST API will be the only access mechanism. Should I put a lock around any of requests that mutate the database (adding a <code>Node</code> or a <code>Test</code>)? Should I lock the database somehow?</p> <p>Thanks for any help!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USjmacdonagh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I maintain multi-table integrity across multiple selects in SQLAlchemy in Pyramid?
      1. USRaj
      1. VTFavorite
    2. VO
      singulars
      1. POHow do I maintain multi-table integrity across multiple selects in SQLAlchemy in Pyramid?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow do I maintain multi-table integrity across multiple selects in SQLAlchemy in Pyramid?
      1. USascobol
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload