Issue 1:
Environment

SQLAlchemy: 1.3.5 or 1.3.11

PostgreSQL: 9.6

Python: 3.7.4

Problem

When delete parent model which has a relationship to children via secondary table, and a relationship to secondary table directly, duplicated delete queries on same instance have been emitted and report SAWarning.

IMO, Unexpectedly db.session.deleted keep deleted instance after delete record in secondary table automatically.

Following logs are outputted when delete parent record. (Some comments are my opinion)

# Delete automatically by secondary relationship
# https://docs.sqlalchemy.org/en/13/orm/cascades.html#delete
2019-11-19 14:30:03,653 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,654 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51})

# Delete automatically by explicitly cascade
# Unexpectedly db.session.deleted keep deleted instance?
2019-11-19 14:30:03,655 INFO sqlalchemy.engine.base.Engine DELETE FROM user_assignments WHERE user_assignments.user_id = %(user_id)s AND user_assignments.account_id = %(account_id)s
2019-11-19 14:30:03,656 INFO sqlalchemy.engine.base.Engine ({'user_id': 8, 'account_id': 6}, {'user_id': 8, 'account_id': 51}, {'user_id': 8, 'account_id': 987})

# Report SAWarning due to part of user_assignments are already deleted.
/home/centos/.pyenv/versions/3.7.4/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py:1367: SAWarning: DELETE statement on table 'user_assignments' expected to delete 3 row(s); 1 were matched. Please set confirm_deleted_rows=False within the mapper configuration to prevent this warning.

Reproduce

We have two models Account and User.

These models have many-to-many relation via UserAssignment model which has data for email confirmation status.

class Account(db.Model):
users = db.relationship('User', secondary='user_assignments', back_populates='accounts', lazy='dynamic',
secondaryjoin='''and_(User.id == UserAssignment.account_id,
UserAssignment.confirmed_at.isnot(None))''')

assignments = db.relationship('UserAssignment', cascade='all, delete-orphan', lazy='dynamic')

select * from user_assignments where user_id = 8;
user_id | account_id | confirmed_at
---------+------------+-------------------------------
8 | 6 | 2017-05-19 16:21:38.938036+00
8 | 987 |
8 | 51 | 2019-09-27 05:38:53.467833+00

user = User.query.get(8)
db.session.delete(user)
db.session.deleted
# IdentitySet([<User 8>, <UserAssignment 8, 6>, <UserAssignment 8, 987>, <UserAssignment 8, 51>])

db.session.flush()

Issue 2:
Migrated issue, originally created by Anonymous

SQLite with default=func.now() Column definition fails on insert

Testcase:

import sqlalchemy as sqa
import datetime

db = sqa.create_engine("sqlite://")
metadata = sqa.BoundMetaData(db)

#metadata.engine.echo = True # debug output

events_table = sqa.Table('events', metadata,
sqa.Column('id', sqa.Integer, primary_key=True),
sqa.Column('name', sqa.String(255)),
sqa.Column('datetime', sqa.DateTime, default=sqa.func.now()),
)
events_table.create()

i = events_table.insert()
name = "just-a-name"
i.execute(name = name)

The error message:

Traceback (most recent call last):
File "D:\testcase02.py", line 19, in ?
i.execute(name = name)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1207, in execute
return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1097, in execute
return e.execute_compiled(self, *multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 780, in execute_compiled
return connection.execute_compiled(compiled, *multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 567, in execute_compiled
context.pre_exec()
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 200, in pre_exec
self._process_defaults()
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 302, in _process_defaults
newid = drunner.get_column_default(c)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1235, in get_column_default
return column.default.accept_visitor(self)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\schema.py", line 840, in accept_visitor
return visitor.visit_column_default(self)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1276, in visit_column_default
return self.exec_default_sql(default)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 1264, in exec_default_sql
return self.connection.execute_compiled(c).scalar()
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 568, in execute_compiled
self._execute_raw(context)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 581, in _execute_raw
self._execute(context)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such function: now u'SELECT now()' [workaround
`
#!diff
--- sqlalchemy/databases/sqlite_old.py	Sat Jul 21 03:18:32 2007
+++ sqlalchemy/databases/sqlite.py	Thu Aug 02 15:10:01 2007
@@ -325,6 +325,12 @@
self.typemap.setdefault("CAST", cast.type)
self.strings[cast](]

Possible) = self.stringscast.clause

def visit_function( self , func ):

if func.name.lower() == 'now':

self.strings[func](func) = 'DATETIME("NOW")'

else:

ansisql.ANSICompiler.visit_function( self , func )

def limit_clause(self, select):

text = ""

if select.limit is not None:

But it also fails

Traceback (most recent call last):
File "D:\testcase02.py", line 19, in ?
i.execute(name = name)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1207, in execute
return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1097, in execute
return e.execute_compiled(self, *multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 780, in execute_compiled
return connection.execute_compiled(compiled, *multiparams, **params)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\base.py", line 567, in execute_compiled
context.pre_exec()
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 201, in pre_exec
self.parameters = self._encode_param_keys(self.dialect.convert_compiled_params(self.compiled_parameters))
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\engine\default.py", line 120, in convert_compiled_params
parameters = parameters.get_raw_list()
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 864, in get_raw_list
return [for key in self.positional](self.get_processed(key))
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 845, in get_processed
return bind.typeprocess(value, self.dialect)
File "C:\Tools\Python24\Lib\site-packages\sqlalchemy\sql.py", line 1883, in typeprocess
return self.type.dialect_impl(dialect).convert_bind_param(value, dialect)
File "C:\Tools\Python24\lib\site-packages\sqlalchemy\databases\sqlite.py", line 38, in convert_bind_param
return value.strftime(self.__format__)
AttributeError: 'unicode' object has no attribute 'strftime'

Attachments: testcase02.py