<p dir="auto">Hello - thanks for all the hard work on sqlalchemy!</p>
<p dir="auto">I have an application where a handful of processes all interact with a single sqlite DB: <code class="notranslate">foo.db</code>. <code class="notranslate">foo.db</code> starts with 0 tables, and each process "lazily" bootstraps <code class="notranslate">foo.db</code> with a table named <code class="notranslate">"some_table"</code>.</p>
<p dir="auto">I notice, however, that when multiple processes call <code class="notranslate">metadata.create_all(checkfirst=True)</code> to try and create <code class="notranslate">"some_table"</code> in <code class="notranslate">foo.db</code>...sqlalchemy winds up raising an <code class="notranslate">OperationalError</code> "table already exists" error.</p>
<p dir="auto">In other words, running this script...</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import concurrent.futures

from sqlalchemy import create_engine, Table, Column, Integer, MetaData


def make_db():
    engine = create_engine(&quot;sqlite:///foo.db&quot;)
    metadata = MetaData()
    Table(
        &quot;some_table&quot;,
        metadata,
        Column(&quot;id&quot;, Integer, primary_key=True),
    )
    metadata.create_all(bind=engine, checkfirst=True)


def main():
    n = 3
    with concurrent.futures.ProcessPoolExecutor() as executor:
        futures = [executor.submit(make_db) for _ in range(n)]
        for future in concurrent.futures.as_completed(futures):
            future.result()


if __name__ == &quot;__main__&quot;:
    main()"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">concurrent</span>.<span class="pl-s1">futures</span>

<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-s1">create_engine</span>, <span class="pl-v">Table</span>, <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">MetaData</span>


<span class="pl-k">def</span> <span class="pl-en">make_db</span>():
    <span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite:///foo.db"</span>)
    <span class="pl-s1">metadata</span> <span class="pl-c1">=</span> <span class="pl-v">MetaData</span>()
    <span class="pl-v">Table</span>(
        <span class="pl-s">"some_table"</span>,
        <span class="pl-s1">metadata</span>,
        <span class="pl-v">Column</span>(<span class="pl-s">"id"</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>),
    )
    <span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">bind</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>, <span class="pl-s1">checkfirst</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-k">def</span> <span class="pl-en">main</span>():
    <span class="pl-s1">n</span> <span class="pl-c1">=</span> <span class="pl-c1">3</span>
    <span class="pl-k">with</span> <span class="pl-s1">concurrent</span>.<span class="pl-s1">futures</span>.<span class="pl-v">ProcessPoolExecutor</span>() <span class="pl-k">as</span> <span class="pl-s1">executor</span>:
        <span class="pl-s1">futures</span> <span class="pl-c1">=</span> [<span class="pl-s1">executor</span>.<span class="pl-en">submit</span>(<span class="pl-s1">make_db</span>) <span class="pl-k">for</span> <span class="pl-s1">_</span> <span class="pl-c1">in</span> <span class="pl-en">range</span>(<span class="pl-s1">n</span>)]
        <span class="pl-k">for</span> <span class="pl-s1">future</span> <span class="pl-c1">in</span> <span class="pl-s1">concurrent</span>.<span class="pl-s1">futures</span>.<span class="pl-en">as_completed</span>(<span class="pl-s1">futures</span>):
            <span class="pl-s1">future</span>.<span class="pl-en">result</span>()


<span class="pl-k">if</span> <span class="pl-s1">__name__</span> <span class="pl-c1">==</span> <span class="pl-s">"__main__"</span>:
    <span class="pl-en">main</span>()</pre></div>
<p dir="auto">... yields the following error:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;sqlite_multiprocessing_create.py&quot;, line 26, in &lt;module&gt;
    main()
  File &quot;sqlite_multiprocessing_create.py&quot;, line 22, in main
    future.result()
  File &quot;/usr/local/lib/python2.7/dist-packages/concurrent/futures/_base.py&quot;, line 455, in result
    return self.__get_result()
  File &quot;/usr/local/lib/python2.7/dist-packages/concurrent/futures/_base.py&quot;, line 414, in __get_result
    raise exception_type, self._exception, self._traceback
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table some_table already exists
[SQL: 
CREATE TABLE some_table (
	id INTEGER NOT NULL, 
	PRIMARY KEY (id)
)

]
(Background on this error at: http://sqlalche.me/e/e3q8)"><pre class="notranslate"><span class="pl-v">Traceback</span> (<span class="pl-s1">most</span> <span class="pl-s1">recent</span> <span class="pl-s1">call</span> <span class="pl-s1">last</span>):
  <span class="pl-v">File</span> <span class="pl-s">"sqlite_multiprocessing_create.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">26</span>, <span class="pl-s1">in</span> <span class="pl-c1">&lt;</span><span class="pl-s1">module</span><span class="pl-c1">&gt;</span>
    <span class="pl-en">main</span>()
  <span class="pl-v">File</span> <span class="pl-s">"sqlite_multiprocessing_create.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">22</span>, <span class="pl-s1">in</span> <span class="pl-s1">main</span>
    <span class="pl-s1">future</span>.<span class="pl-en">result</span>()
  <span class="pl-v">File</span> <span class="pl-s">"/usr/local/lib/python2.7/dist-packages/concurrent/futures/_base.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">455</span>, <span class="pl-s1">in</span> <span class="pl-s1">result</span>
    <span class="pl-k">return</span> <span class="pl-s1">self</span>.<span class="pl-en">__get_result</span>()
  <span class="pl-v">File</span> <span class="pl-s">"/usr/local/lib/python2.7/dist-packages/concurrent/futures/_base.py"</span>, <span class="pl-s1">line</span> <span class="pl-c1">414</span>, <span class="pl-s1">in</span> <span class="pl-s1">__get_result</span>
    <span class="pl-k">raise</span> <span class="pl-s1">exception_type</span>, <span class="pl-s1">self</span>.<span class="pl-s1">_exception</span>, <span class="pl-s1">self</span>.<span class="pl-s1">_traceback</span>
<span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">exc</span>.<span class="pl-v">OperationalError</span>: (<span class="pl-s1">sqlite3</span>.<span class="pl-v">OperationalError</span>) <span class="pl-s1">table</span> <span class="pl-s1">some_table</span> <span class="pl-s1">already</span> <span class="pl-s1">exists</span>
[<span class="pl-v">SQL</span>: 
<span class="pl-v">CREATE</span> <span class="pl-v">TABLE</span> <span class="pl-en">some_table</span> (
	<span class="pl-s1">id</span> <span class="pl-v">INTEGER</span> <span class="pl-v">NOT</span> <span class="pl-v">NULL</span>, 
	<span class="pl-v">PRIMARY</span> <span class="pl-v">KEY</span> (<span class="pl-s1">id</span>)
)

]
(<span class="pl-v">Background</span> <span class="pl-s1">on</span> <span class="pl-s1">this</span> <span class="pl-s1">error</span> <span class="pl-s1">at</span>: <span class="pl-s1">http</span>:<span class="pl-c1">//</span><span class="pl-s1">sqlalche</span>.<span class="pl-s1">me</span><span class="pl-c1">/</span><span class="pl-s1">e</span><span class="pl-c1">/</span><span class="pl-s1">e3q8</span>)</pre></div>
<p dir="auto">Of course, an obvious workaround would be to have, "the parent process bootstrap tables in <code class="notranslate">foo.db</code>. Is it possible, however, to instruct sqlalchemy to use the <a href="https://www.sqlite.org/lang_createtable.html" rel="nofollow"><code class="notranslate">if not exists</code> clause that sqlite supports</a>?</p>
<p dir="auto">I've read <a href="https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#pysqlite-serializable" rel="nofollow">through the notes on sqlite / <code class="notranslate">pysqlite</code> 's DDL semantics</a>, but hadn't found a nice workaround.</p>
<p dir="auto"><strong>Migrated issue, originally created by Craig Weber (<a href="https://github.com/weberc2">@weberc2</a>)</strong></p>
<p dir="auto">For tables without a primary key, TableClause.count() surprisingly compiles down to <code class="notranslate">SELECT count(&lt;first-column-in-ColumnCollection&gt;)...</code> instead of <code class="notranslate">SELECT count(*)...</code>. As a consequence, the returned result is incorrect by the number of NULLs in the first column in the column collection. This behavior is unexpected, and I couldn't find any documentation on the subject.</p>
<p dir="auto">Here is the offending code (from <a href="https://bitbucket.org/zzzeek/sqlalchemy/src/7189d0bc82598c2d6dcbb55b054837416db2ee7d/lib/sqlalchemy/sql/selectable.py?at=master&amp;fileviewer=file-view-default" rel="nofollow">https://bitbucket.org/zzzeek/sqlalchemy/src/7189d0bc82598c2d6dcbb55b054837416db2ee7d/lib/sqlalchemy/sql/selectable.py?at=master&amp;fileviewer=file-view-default</a>):</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="    @util.dependencies(&quot;sqlalchemy.sql.functions&quot;)
    def count(self, functions, whereclause=None, **params):
        &quot;&quot;&quot;return a SELECT COUNT generated against this
        :class:`.TableClause`.&quot;&quot;&quot;

        if self.primary_key:
            col = list(self.primary_key)[0]
        else:
            col = list(self.columns)[0]
        return Select(
            [functions.func.count(col).label('tbl_row_count')],
            whereclause,
            from_obj=[self],
            **params)"><pre class="notranslate">    <span class="pl-en">@<span class="pl-s1">util</span>.<span class="pl-en">dependencies</span>(<span class="pl-s">"sqlalchemy.sql.functions"</span>)</span>
    <span class="pl-k">def</span> <span class="pl-en">count</span>(<span class="pl-s1">self</span>, <span class="pl-s1">functions</span>, <span class="pl-s1">whereclause</span><span class="pl-c1">=</span><span class="pl-c1">None</span>, <span class="pl-c1">**</span><span class="pl-s1">params</span>):
        <span class="pl-s">"""return a SELECT COUNT generated against this</span>
<span class="pl-s">        :class:`.TableClause`."""</span>

        <span class="pl-k">if</span> <span class="pl-s1">self</span>.<span class="pl-s1">primary_key</span>:
            <span class="pl-s1">col</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-s1">self</span>.<span class="pl-s1">primary_key</span>)[<span class="pl-c1">0</span>]
        <span class="pl-k">else</span>:
            <span class="pl-s1">col</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-s1">self</span>.<span class="pl-s1">columns</span>)[<span class="pl-c1">0</span>]
        <span class="pl-k">return</span> <span class="pl-v">Select</span>(
            [<span class="pl-s1">functions</span>.<span class="pl-s1">func</span>.<span class="pl-en">count</span>(<span class="pl-s1">col</span>).<span class="pl-en">label</span>(<span class="pl-s">'tbl_row_count'</span>)],
            <span class="pl-s1">whereclause</span>,
            <span class="pl-s1">from_obj</span><span class="pl-c1">=</span>[<span class="pl-s1">self</span>],
            <span class="pl-c1">**</span><span class="pl-s1">params</span>)</pre></div>