Immediate vs load-on-first-access availability
<p>The <a href="http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#adding-and-updating-objects" rel="nofollow">docs say</a> (at the end of the linked section):</p>

<blockquote>
  <p>After the Session inserts new rows in the database, all newly generated identifiers and database-generated defaults become available on the instance, either immediately or via load-on-first-access.</p>
</blockquote>

<p>What's the difference between <code>immediately</code> and <code>load-on-first-access</code>? Doesn't SQLAlchemy know the new identifiers and defaults after it completed the <code>INSERT</code> operation, and so don't they become available even without reloading?</p>

<p>SQLAlchemy uses <code>INSERT .. RETURNING</code> for DBs that support it to fetch primary keys, or special functions like <a href="http://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html" rel="nofollow"><code>mysql_insert_id</code></a> for DBs that don't.</p>

<p>For default values, it tries to use <code>RETURNING</code> but if it's not supported, it has to use another <code>SELECT</code>.</p>

<p>Furthermore, it may not be possible to use <code>RETURNING</code> (even on supported DBs) in specific situations (e.g. when the <code>INSERT</code> statements are batched, see <a href="https://groups.google.com/d/msg/sqlalchemy/3l6RhfAFpvM/3OqL6aXJo70J" rel="nofollow">this mailing list post</a>).</p>

<p>Finally, SQLAlchemy only fetches the primary key by default after an <code>INSERT</code>. Thus, only the primary key is available "immediately" while unconfigured default columns are "load-on-first-access". If you need it to fetch generated values, specify <a href="http://docs.sqlalchemy.org/en/latest/core/defaults.html#triggered-columns" rel="nofollow"><code>server_default=FetchedValue()</code></a>. (In the case where the DB does not support <code>RETURNING</code> and you specify <code>FetchedValue</code> for a column, I am not certain whether it still fetches the value with an immediate <code>SELECT</code> or simply falls back to "load-on-first-access".)</p>

python sqlalchemy