Issue 1:
Describe the bug

when using the postgres version of Insert, the sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing method, if you provide a constraint argument, it doesn't properly escape it, causing a SQL syntax error on a otherwise valid constraint name.

if you add escaped quotes to the constraint name, it works, but I feel that sqlalchemy should escape these for you properly

# doesn't work
do_nothing_upsert_statement = upsert_statement.on_conflict_do_nothing(constraint="IXUQ-users_found-user_name")

# works
do_nothing_upsert_statement = upsert_statement.on_conflict_do_nothing(constraint="\"IXUQ-users_found-user_name\"")

To Reproduce

Provide your Minimal, Complete, and Verifiable example

here.

needs asyncpg, sqlalchemy and sqlalchemy-repr to run

import asyncio
import logging
import random

from sqlalchemy import Column, Index, Integer, Unicode, PrimaryKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy_repr import RepresentableBase
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.dialects.postgresql import insert

logging.basicConfig(level="DEBUG")
logger = logging.getLogger("main")

CustomDeclarativeBase = declarative_base(name="CustomDeclarativeBase")

class UsersFound(CustomDeclarativeBase):
__tablename__ = "users_found"

# primary key column
user_id = Column(Integer, nullable=False, autoincrement=True)

user_name = Column(Unicode, nullable=False)

__table_args__ = (
PrimaryKeyConstraint("user_id", name="PK-users_found-user_id"),

Index("IXUQ-users_found-user_name", "user_name", unique=True),

)

def __str__(self):
return f"UsersFound(user_id={self.user_id}, user_name={self.user_name})"

class ScrapeUsers:

def __init__(self):

self.config = None
self.sqla_engine = None
self.async_sessionmaker = None

async def run(self):

url = URL.create(drivername="postgresql+asyncpg",
username="postgres",
password="PASSWORD_HERE",
host="localhost",
port="5432",
database="testing",
query=None)
self.sqla_engine = create_async_engine(url, echo=False, future=True)

# expire_on_commit=False will prevent attributes from being expired
# after commit.
self.async_sessionmaker = sessionmaker(
bind=self.sqla_engine, expire_on_commit=False, class_=AsyncSession
)

async with self.sqla_engine.begin() as conn:
await conn.run_sync(CustomDeclarativeBase.metadata.create_all)

async with self.async_sessionmaker() as session:

name_list_letters = random.sample("abcdefghijklmnopqrstuvwxyz", 10)
name = "".join(name_list_letters)

# do it twice to get the duplicate key error
for i in range(2):
# get things from the queue
async with session.begin():

obj = UsersFound(user_name=name)

logger.info("adding: `%s`", obj)

list_of_dicts_to_insert = [{"user_name": obj.user_name}]

conn = await session.connection()

upsert_statement = insert(UsersFound.__table__).values(list_of_dicts_to_insert)
do_nothing_upsert_statement = upsert_statement.on_conflict_do_nothing(constraint="IXUQ-users_found-user_name")
await conn.execute(do_nothing_upsert_statement)

session.add(obj)

await self.sqla_engine.dispose()
await asyncio.sleep(0.1)

x = ScrapeUsers()
asyncio.run(x.run(), debug=False)

Error

Provide the complete text of any errors received including the complete

stack trace. If the message is a warning, run your program with the

-Werror flag: python -Werror myprogram.py

PS C:\Users\auror\Code\sqlalchemy_bugs> python .\sqlalchemy_bug_on_confilict_do_nothing_escape.py
DEBUG:asyncio:Using proactor: IocpProactor
INFO:main:adding: `UsersFound(user_id=None, user_name=vzxwnfpjoh)`
Traceback (most recent call last):
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 388, in _prepare_and_execute
prepared_stmt, attributes = await adapt_connection._prepare(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 630, in _prepare
prepared_stmt = await self._connection.prepare(operation)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\asyncpg\connection.py", line 530, in prepare
return await self._prepare(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\asyncpg\connection.py", line 546, in _prepare
stmt = await self._get_statement(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\asyncpg\connection.py", line 375, in _get_statement
statement = await self._protocol.prepare(
File "asyncpg\protocol\protocol.pyx", line 168, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "-"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1770, in _execute_context
self.dialect.do_execute(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
cursor.execute(statement, parameters)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 449, in execute
self._adapt_connection.await_(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 69, in await_only
return current.driver.switch(awaitable)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 122, in greenlet_spawn
value = await result
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 424, in _prepare_and_execute
self._handle_exception(error)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 358, in _handle_exception
self._adapt_connection._handle_exception(error)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 652, in _handle_exception
raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError: <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "-"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users\auror\Code\sqlalchemy_bugs\sqlalchemy_bug_on_confilict_do_nothing_escape.py", line 101, in <module>
asyncio.run(x.run(), debug=False)
File "c:\python39\lib\asyncio\runners.py", line 44, in run
return loop.run_until_complete(main)
File "c:\python39\lib\asyncio\base_events.py", line 642, in run_until_complete
return future.result()
File "C:\Users\auror\Code\sqlalchemy_bugs\sqlalchemy_bug_on_confilict_do_nothing_escape.py", line 91, in run
await conn.execute(do_nothing_upsert_statement)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\ext\asyncio\engine.py", line 405, in execute
result = await greenlet_spawn(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 127, in greenlet_spawn
result = context.throw(*sys.exc_info())
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1582, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1451, in _execute_clauseelement
ret = self._execute_context(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1813, in _execute_context
self._handle_dbapi_exception(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1994, in _handle_dbapi_exception
util.raise_(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\base.py", line 1770, in _execute_context
self.dialect.do_execute(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
cursor.execute(statement, parameters)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 449, in execute
self._adapt_connection.await_(
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 69, in await_only
return current.driver.switch(awaitable)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 122, in greenlet_spawn
value = await result
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 424, in _prepare_and_execute
self._handle_exception(error)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 358, in _handle_exception
self._adapt_connection._handle_exception(error)
File "C:\Users\auror\AppData\Local\pypoetry\Cache\virtualenvs\sqlalchemy-bugs-w_rCfYdx-py3.9\lib\site-packages\sqlalchemy\dialects\postgresql\asyncpg.py", line 652, in _handle_exception
raise translated_error from error
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "-"
[SQL: INSERT INTO users_found (user_name) VALUES (%s) ON CONFLICT ON CONSTRAINT IXUQ-users_found-user_name DO NOTHING]
[parameters: ('vzxwnfpjoh',)]
(Background on this error at: http://sqlalche.me/e/14/f405)
Exception ignored in: <function _ProactorBasePipeTransport.__del__ at 0x0000020F2DF3BB80>
Traceback (most recent call last):
File "c:\python39\lib\asyncio\proactor_events.py", line 116, in __del__
File "c:\python39\lib\asyncio\proactor_events.py", line 108, in close
File "c:\python39\lib\asyncio\base_events.py", line 746, in call_soon
File "c:\python39\lib\asyncio\base_events.py", line 510, in _check_closed
RuntimeError: Event loop is closed
PS C:\Users\auror\Code\sqlalchemy_bugs>

Versions.

OS: windows 10

Python: Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)]

SQLAlchemy: 1.4.20

Database: postgres 13.2

DBAPI: asyncpg 0.23.0

Additional context

Have a nice day!

Issue 2:
Migrated issue, originally created by Adrian (@thiefmaster)

In some cases it would be useful to have a set on the python side, e.g. because you don't need/want duplicates and the set api is nicer to use in that case (as you won't have to worry about removing dupes).

Since there's already as_tuple, I'd deprecate this option and have a python_type kwarg instead that defaults to list but can be set to tuple or set, too. as_tuple and python_type would be mutually exclusive.