Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As far as I know, there is no way to get the ORM to issue bulk inserts. I believe the underlying reason is that SQLAlchemy needs to keep track of each object's identity (i.e., new primary keys), and bulk inserts interfere with that. For example, assuming your <code>foo</code> table contains an <code>id</code> column and is mapped to a <code>Foo</code> class:</p> <pre><code>x = Foo(bar=1) print x.id # None session.add(x) session.flush() # BEGIN # INSERT INTO foo (bar) VALUES(1) # COMMIT print x.id # 1 </code></pre> <p>Since SQLAlchemy picked up the value for <code>x.id</code> without issuing another query, we can infer that it got the value directly from the <code>INSERT</code> statement. If you don't need subsequent access to the created objects via the <em>same</em> instances, you can skip the ORM layer for your insert:</p> <pre><code>Foo.__table__.insert().execute([{'bar': 1}, {'bar': 2}, {'bar': 3}]) # INSERT INTO foo (bar) VALUES ((1,), (2,), (3,)) </code></pre> <p>SQLAlchemy can't match these new rows with any existing objects, so you'll have to query them anew to for any subsequent operations.</p> <p>As far as stale data is concerned, it's helpful to remember that the session has no built-in way to know when the database is changed outside of the session. In order to access externally modified data through existing instances, the instances must be marked as <em>expired</em>. This happens by default on <code>session.commit()</code>, but can be done manually by calling <code>session.expire_all()</code> or <code>session.expire(instance)</code>. An example (SQL omitted):</p> <pre><code>x = Foo(bar=1) session.add(x) session.commit() print x.bar # 1 foo.update().execute(bar=42) print x.bar # 1 session.expire(x) print x.bar # 42 </code></pre> <p><code>session.commit()</code> expires <code>x</code>, so the first print statement implicitly opens a new transaction and re-queries <code>x</code>'s attributes. If you comment out the first print statement, you'll notice that the second one now picks up the correct value, because the new query isn't emitted until after the update.</p> <p>This makes sense from the point of view of transactional isolation - you should only pick up external modifications between transactions. If this is causing you trouble, I'd suggest clarifying or re-thinking your application's transaction boundaries instead of immediately reaching for <code>session.expire_all()</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. POBulk insert with SQLAlchemy ORM
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USdhaffey
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for your reply, I'm going to give that a go. WRT the expiring issue, what I saw wasn't quite the same. I am using a scoped session in turbogears. Performing a getSession().query(Foo).filter....all() returned different things depending on the request, also did not return the updated records that were in the db until I restarted it. I fixed this issue by doing a autocommit=True and adding in something that .remove()d the session after the request was complete (I gather you are meant to do that anyway).
      singulars
      1. PO
      1. USNick Holden
    2. COI guess it returned different things depending on the request because it had a scoped session per thread in the pool and the sessions were in different states? It seemed a bit odd that sa wouldn't get new data after a fresh request though. I expect I am missunderstanding what autocommit=False is doing
      singulars
      1. PO
      1. USNick Holden
    3. COWith `autocommit=False`, I believe you should be calling `session.commit()` upon request completion (I'm not familiar with TurboGears, so ignore this if that's handled for you at the framework level). Besides making sure your changes have made it to the database, this would expire everything in the session. The next transaction wouldn't begin until the next use of that session, so future requests on the same thread wouldn't see stale data.
      singulars
      1. PO
      1. USdhaffey
 

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