need to refine psycopg2 executemanymode to not fire off for on conflict inserts

[Lotram]

Describe the bug

HI, I'm trying to migrate from 1.3.23 to 1.4.17, with a Postgres DB. I have an error while trying to use on_conflict_do_update with a constant in the set_ arg, I hope I did not miss anything in the doc about migrating to 1.4 which could explain this.

To Reproduce

metadata = MetaData(naming_convention=convention)
db = SQLAlchemy(metadata=metadata)
db.session = db.create_scoped_session()
class Base(db.Model):
    __abstract__ = True

    created_at = db.Column(db.DateTime(), server_default=db.func.now())
    updated_at = db.Column(
        db.DateTime(), server_default=db.func.now(), onupdate=db.func.now()
    )

class GithubUser(Base):
    __tablename__ = "github_users"
    id = db.Column(db.Integer, primary_key=True)
    login = db.Column(db.String(255), index=True)

 insert_query = insert(GithubUser.__table__)
  upsert_query = insert_query.on_conflict_do_update(
      index_elements=[GithubUser.__table__.c.id],
      set_={
           "login": insert_query.excluded.login,
          "is_deleted": False,
          "updated_at": db.func.now(),
      },
  )

  db.session.execute(upsert_query, [{'id': 1337, 'login': 'cnorris'}, {'id': 1338, 'login': 'jtrivette'}]) # raises an error 

Error

test.py:249: in _update_or_create_github_users
    db.session.execute(upsert_query, [{'id': 1337, 'login': 'cnorris'}, {'id': 1338, 'login': 'jtrivette'}])
<string>:2: in execute
    ???
/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py:1677: in execute
    result = conn._execute_20(statement, params or {}, execution_options)
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1582: in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:324: in _execute_on_connection
    return connection._execute_clauseelement(
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1451: in _execute_clauseelement
    ret = self._execute_context(
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1813: in _execute_context
    self._handle_dbapi_exception(
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1998: in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:207: in raise_
    raise exception
/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1750: in _execute_context
    self.dialect.do_executemany(
/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py:912: in do_executemany
    context._psycopg2_fetched_rows = xtras.execute_values(
/usr/local/lib/python3.8/site-packages/psycopg2/extras.py:1281: in execute_values
    pre, post = _split_sql(sql)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

sql = b'INSERT INTO github_users (id, login, is_deleted) VALUES %s ON CONFLICT (id) DO UPDATE SET updated_at = now(), login = excluded.login, is_deleted = %(param_1)s'

    def _split_sql(sql):
        """Split *sql* on a single ``%s`` placeholder.

        Split on the %s, perform %% replacement and return pre, post lists of
        snippets.
        """
        curr = pre = []
        post = []
        tokens = _re.split(br'(%.)', sql)
        for token in tokens:
            if len(token) != 2 or token[:1] != b'%':
                curr.append(token)
                continue

            if token[1:] == b's':
                if curr is pre:
                    curr = post
                else:
                    raise ValueError(
                        "the query contains more than one '%s' placeholder")
            elif token[1:] == b'%':
                curr.append(b'%')
            else:
>               raise ValueError("unsupported format character: '%s'"
                    % token[1:].decode('ascii', 'replace'))
E               ValueError: unsupported format character: '('

/usr/local/lib/python3.8/site-packages/psycopg2/extras.py:1322: ValueError

Versions.

• OS: Ubuntu 20.04
• Python: 3.8
• SQLAlchemy: 1.4.17
• Database: Postgres 11
• DBAPI: psycopg2 2.8.6

Additional context I did some investigation on this error:

• In the do_executemany method, the statement sent to execute_values is 'INSERT INTO github_users (id, login, is_deleted) VALUES %s ON CONFLICT (id) DO UPDATE SET updated_at = now(), login = excluded.login, is_deleted = %(param_1)s' The %(param_1)s should not be here, that's why psycopg2 raises an error.
• If I remove the "is_deleted": False from the set_, everything works fine.
• In sqlalchemy 1.3, in the do_executemany method, self.executemany_mode is EXECUTEMANY_DEFAULT is True, so self._psycopg2_extras().execute_values is not called.

Let me know if I need to provide more info about this. Have a nice day!

[zzzeek]

hi -

set up your engine using executemany_mode=None right now to work around this:

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True, executemany_mode=None)

[Lotram]

Thanks, it does work with this option

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

don't enable "fast insert executemany" for ON CONFLICT etc https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2857

[zzzeek]

this should be all good in the next release