<p dir="auto"><strong>Is your feature request related to a problem? Please describe.</strong></p>
<p dir="auto">My company is currently happily using SQLAlchemy (thank you!), and mostly happily using <a href="https://github.com/dropbox/sqlalchemy-stubs">https://github.com/dropbox/sqlalchemy-stubs</a>. However, the latter is unmaintained and thus we're regularly hitting issues (and submitting fixes) that don't get fixed or land. Thus, I'd love to switch to the first-party mypy support.</p>
<p dir="auto">I've tried doing the switch, but found, among other issues, that annotating every foreign key column and relationship property was frustrating.</p>
<p dir="auto">For instance, previously:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import as_declarative

@as_declarative()
class Base:
    pass

class Foo(Base):
    id = Column(Integer(), primary_key=True)
    bars = relationship(&quot;Bar&quot;, uselist=True)

class Bar(base):
    id = Column(Integer(), primary_key=True)
    foo_id = Column(ForeignKey(Foo.id))
    foo = relationship(&quot;Foo&quot;)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">ForeignKey</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">relationship</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">as_declarative</span>

<span class="pl-en">@<span class="pl-en">as_declarative</span>()</span>
<span class="pl-k">class</span> <span class="pl-v">Base</span>:
    <span class="pl-k">pass</span>

<span class="pl-k">class</span> <span class="pl-v">Foo</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</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">bars</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"Bar"</span>, <span class="pl-s1">uselist</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">class</span> <span class="pl-v">Bar</span>(<span class="pl-s1">base</span>):
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</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">foo_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">ForeignKey</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span>))
    <span class="pl-s1">foo</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"Foo"</span>)</pre></div>
<p dir="auto">With sqlalchemy2-stubs:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import as_declarative
from typing import List, Optional

@as_declarative()
class Base:
    pass

class Foo(Base):
    id = Column(Integer(), primary_key=True)
    bars: &quot;Bar&quot; = relationship(&quot;Bar&quot;, back_populates=&quot;foo&quot;, uselist=True)

class Bar(base):
    id = Column(Integer(), primary_key=True)
    foo_id: Optional[int] = Column(ForeignKey(Foo.id))
    foo: Optional[Foo] = relationship(&quot;Foo&quot;, back_populates=&quot;bars&quot;)"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">Integer</span>, <span class="pl-v">ForeignKey</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">relationship</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">as_declarative</span>
<span class="pl-k">from</span> <span class="pl-s1">typing</span> <span class="pl-k">import</span> <span class="pl-v">List</span>, <span class="pl-v">Optional</span>

<span class="pl-en">@<span class="pl-en">as_declarative</span>()</span>
<span class="pl-k">class</span> <span class="pl-v">Base</span>:
    <span class="pl-k">pass</span>

<span class="pl-k">class</span> <span class="pl-v">Foo</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</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">bars</span>: <span class="pl-s">"Bar"</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"Bar"</span>, <span class="pl-s1">back_populates</span><span class="pl-c1">=</span><span class="pl-s">"foo"</span>, <span class="pl-s1">uselist</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">class</span> <span class="pl-v">Bar</span>(<span class="pl-s1">base</span>):
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</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">foo_id</span>: <span class="pl-v">Optional</span>[<span class="pl-s1">int</span>] <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">ForeignKey</span>(<span class="pl-v">Foo</span>.<span class="pl-s1">id</span>))
    <span class="pl-s1">foo</span>: <span class="pl-v">Optional</span>[<span class="pl-v">Foo</span>] <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(<span class="pl-s">"Foo"</span>, <span class="pl-s1">back_populates</span><span class="pl-c1">=</span><span class="pl-s">"bars"</span>)</pre></div>
<p dir="auto">I find this problematic for a few reasons:</p>
<ul dir="auto">
<li>it's error-prone: a mistake in the annotation (like <code class="notranslate">bars: "Bar"</code> above: it should be <code class="notranslate">bars: List["Bar"]</code>), despite the information presumably being known (e.g. the sqlalchemy-mypy plugin can infer the types correctly)</li>
<li>it's redundant</li>
<li>it's more verbose</li>
</ul>
<p dir="auto"><strong>Describe the solution you'd like</strong><br>
It would be great if the mypy plugin could infer more types for these foreign key and relationship properties.</p>
<p dir="auto"><strong>Describe alternatives you've considered</strong></p>
<p dir="auto">Another option would be to require the annotations, but validate that they match the relationship/foreign key targets. This resolves our main concern that the annotations are error prone, but I'm not sure this redundancy is useful (unlike some cases with type annotations).</p>
<p dir="auto"><strong>Additional context</strong></p>
<p dir="auto">I tested the code above using the following versions:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="greenlet==1.0.0
mypy==0.812
mypy-extensions==0.4.3
SQLAlchemy==1.4.11
sqlalchemy2-stubs==0.0.1a4
typed-ast==1.4.3
typing-extensions==3.7.4.3"><pre class="notranslate"><code class="notranslate">greenlet==1.0.0
mypy==0.812
mypy-extensions==0.4.3
SQLAlchemy==1.4.11
sqlalchemy2-stubs==0.0.1a4
typed-ast==1.4.3
typing-extensions==3.7.4.3
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto"><a href="this">is all against sqlalchemy r1343. sqlite 3.3.5, MySQLdb 1.2.1g3, python2.4, gcc 4.0.1</a></p>
<p dir="auto">The repro case is for sqlite. It outputs::</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    self.execute(statement, parameters, connection=connection, cursor=cursor, return_raw=True)
  File &quot;/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py&quot;, line 692, in execute
    self._execute(cursor, statement, parameters)
  File &quot;/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py&quot;, line 712, in _execute
    raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied. 'INSERT INTO t1 (name, familyname, registered) VALUES (?, ?, current_timestamp)' ['simpson', '0000-00-00 00:00:00']('bart',)"><pre class="notranslate"><code class="notranslate">    self.execute(statement, parameters, connection=connection, cursor=cursor, return_raw=True)
  File "/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py", line 692, in execute
    self._execute(cursor, statement, parameters)
  File "/home/robin/devel/sqlalchemy/lib/sqlalchemy/engine.py", line 712, in _execute
    raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied. 'INSERT INTO t1 (name, familyname, registered) VALUES (?, ?, current_timestamp)' ['simpson', '0000-00-00 00:00:00']('bart',)
</code></pre></div>
<p dir="auto">After the patch I get::</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="(1, u'bart', u'simpson', datetime.datetime(2006, 4, 27, 2, 23, 5))"><pre class="notranslate"><code class="notranslate">(1, u'bart', u'simpson', datetime.datetime(2006, 4, 27, 2, 23, 5))
</code></pre></div>
<p dir="auto">Also tried this on MySQL 4.1 with NOW() and behaviour is similar before &amp; after patch.</p>
<p dir="auto">The patch prevents SQLEngine._process_defaults from filling in column defaults for parameters that have been supplied at compile time. It's not quite right,<br>
<strong>objectstore.PKTest.testprimarykey</strong> fails, and it looks like something subtle is going on with multivalue, or single value, PK's that use Sequence. There is coupling between ansisql and engine that I just don't understand, the single additional test failure I got with this patch said::</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="objectstore.PKTest.testprimarykey

    SQLError: (IntegrityError) multi.multi_id may not be NULL 'INSERT INTO multi 
    (multi_rev, name, value) VALUES (?, ?, ?)' ['entry1', 'this is entry 1'](2,)"><pre class="notranslate"><code class="notranslate">objectstore.PKTest.testprimarykey

    SQLError: (IntegrityError) multi.multi_id may not be NULL 'INSERT INTO multi 
    (multi_rev, name, value) VALUES (?, ?, ?)' ['entry1', 'this is entry 1'](2,)
</code></pre></div>
<p dir="auto">(can't find the attatchment button, I'll add the patch &amp; repro as comment)</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/165/repro_compiled_insertcase_sqlite.py">repro_compiled_insertcase_sqlite.py</a> | <a href="../wiki/imported_issue_attachments/165/compiled_insert_params_against_r1343.patch">compiled_insert_params_against_r1343.patch</a></p>