<p dir="auto"><strong>Migrated issue, originally created by Varialus (<a href="https://github.com/varialus">@varialus</a>)</strong></p>
<p dir="auto">Flushing data to disk with SQLite locks the whole database until a commit has been performed. Because the autoflush setting is enabled by default, if a flush goes unnoticed, the database will stay locked, blocking all other threads. SQLite is commonly used while evaluating SQLAlchemy, so the your default settings should work seamlessly with it.</p>
<p dir="auto">Risk Mitigation Ideas</p>
<ol dir="auto">
<li>
<p dir="auto">If the autoflush value is manually specified while making a session, then if an SQLite engine is being used, autoflush should be set to False by default.</p>
</li>
<li>
<p dir="auto">Add some bright warnings to your documentation.</p>
</li>
<li>
<p dir="auto">Don't allow autoflush to be set while using SQLite.</p>
</li>
<li>
<p dir="auto">Disable autoflush by default regardless of the database.</p>
</li>
<li>
<p dir="auto">Change the behavior of SQLite to not lock the whole database while performing writes to disk. The optional support for write-ahead logging which was added in SQLite version 3.7.0 may be of interest.</p>
</li>
<li>
<p dir="auto">Flush to a cache.</p>
</li>
<li>
<p dir="auto">Demand that SQLite change their default behavior to suit SQLAlchemy.</p>
</li>
</ol>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2447/TestCaseAutoflushLocksSQLite.py">TestCaseAutoflushLocksSQLite.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Andrew Paulo Robillo (<a href="https://github.com/shimofuri">@shimofuri</a>)</strong></p>
<p dir="auto">I'd been copying this <a href="https://bitbucket.org/snippets/shimofuri/bALAz" rel="nofollow">snippet</a> from my old cookbook to add a surrogate key to a declarative table class:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class MixinIntKey(object):
    @declared_attr
    def key(cls):
        return Column(
            pg.INTEGER,
            Sequence(f&quot;sqk_{cls.__tablename__}&quot;, 1, 1),
            nullable=False,
            primary_key=True
            )"><pre class="notranslate"><code class="notranslate">class MixinIntKey(object):
    @declared_attr
    def key(cls):
        return Column(
            pg.INTEGER,
            Sequence(f"sqk_{cls.__tablename__}", 1, 1),
            nullable=False,
            primary_key=True
            )
</code></pre></div>
<p dir="auto">This code was working before (some 3-4 years ago, I think around version 0.8 ): it generates the Sequence when I build the database objects through metadata.create_all(). The only change I made to the snippet was to update it to use format strings (Python 3.6).</p>
<p dir="auto">After some googling around and testing (see attached file/<a href="https://bitbucket.org/snippets/shimofuri/bALAz" rel="nofollow">link</a>), the Sequence can be generated only if the metadata is passed during Sequence init:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Sequence(f&quot;sqk_{cls.__tablename__}&quot;, 1, 1, metadata=metadata)"><pre class="notranslate"><code class="notranslate">Sequence(f"sqk_{cls.__tablename__}", 1, 1, metadata=metadata)
</code></pre></div>
<p dir="auto">My environment: psycopg2==2.7.1, SQLAlchemy==1.1.8, Python 3.6 64bit on Windows 8.1 64bit, PostgreSQL 9.6 server</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3951/sequence_bug.py">sequence_bug.py</a></p>