Issue 1:
Is your feature request related to a problem? Please describe.

My company is currently happily using SQLAlchemy (thank you!), and mostly happily using https://github.com/dropbox/sqlalchemy-stubs. 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.

I've tried doing the switch, but found, among other issues, that annotating every foreign key column and relationship property was frustrating.

For instance, previously:

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("Bar", uselist=True)

class Bar(base):
id = Column(Integer(), primary_key=True)
foo_id = Column(ForeignKey(Foo.id))
foo = relationship("Foo")

With sqlalchemy2-stubs:

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: "Bar" = relationship("Bar", back_populates="foo", uselist=True)

class Bar(base):
id = Column(Integer(), primary_key=True)
foo_id: Optional[int] = Column(ForeignKey(Foo.id))
foo: Optional[Foo] = relationship("Foo", back_populates="bars")

I find this problematic for a few reasons:

it's error-prone: a mistake in the annotation (like bars: "Bar" above: it should be bars: List["Bar"]), despite the information presumably being known (e.g. the sqlalchemy-mypy plugin can infer the types correctly)

it's redundant

it's more verbose

Describe the solution you'd like

It would be great if the mypy plugin could infer more types for these foreign key and relationship properties.

Describe alternatives you've considered

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).

Additional context

I tested the code above using the following versions:

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

Issue 2:
Migrated issue, originally created by Anonymous

is all against sqlalchemy r1343. sqlite 3.3.5, MySQLdb 1.2.1g3, python2.4, gcc 4.0.1

The repro case is for sqlite. It outputs::

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',)

After the patch I get::

(1, u'bart', u'simpson', datetime.datetime(2006, 4, 27, 2, 23, 5))

Also tried this on MySQL 4.1 with NOW() and behaviour is similar before & after patch.

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,

objectstore.PKTest.testprimarykey 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::

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,)

(can't find the attatchment button, I'll add the patch & repro as comment)

Attachments: repro_compiled_insertcase_sqlite.py | compiled_insert_params_against_r1343.patch