Describe the bug

When using uuid columns with the psycopg2 backend, this error appears for many queries that involve uuids. I could reproduce it with a simple insert statement (see code example), however it also happens in select statements. For these, I couldn't find a minimal example yet.

The cause of the error seems to be a call to UUID(x) where x is already an uuid.

The following patch seems to fix the problem:

diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 4143dd041..e417856a5 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -577,7 +577,7 @@ class _PGUUID(UUID):
         if not self.as_uuid and dialect.use_native_uuid:
 
             def process(value):
-                if value is not None:
+                if value is not None and not isinstance(value, _python_UUID):
                     value = _python_UUID(value)
                 return value

To Reproduce

import uuid
import sqlalchemy
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import UUID

metadata = sqlalchemy.MetaData()

ids = sqlalchemy.Table("ids", metadata, Column("id", UUID, primary_key=True))
connection_string = f"postgresql:///?dbname=test"
engine = sqlalchemy.create_engine(connection_string)

metadata.create_all(engine)

with engine.connect() as con:
    con.execute(ids.insert(), [{"id": uuid.uuid4()}])

Error

Traceback (most recent call last):
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1700, in _execute_context
dialect, self, conn, execution_options, *args, **kw
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1080, in _init_compiled
for key in compiled_params
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1080, in
for key in compiled_params
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 581, in process
value = _python_UUID(value)
File "/usr/lib/python3.7/uuid.py", line 157, in init
hex = hex.replace('urn:', '').replace('uuid:', '')
AttributeError: 'UUID' object has no attribute 'replace'

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

Traceback (most recent call last):
File "tests/test_uuid.py", line 15, in
con.execute(ids.insert(), [{"id": uuid.uuid4()}])
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1286, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 326, in _execute_on_connection
self, multiparams, params, execution_options
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1488, in _execute_clauseelement
cache_hit=cache_hit,
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in execute_context
e, util.text_type(statement), parameters, None, None
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2024, in handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise
raise exception
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1700, in _execute_context
dialect, self, conn, execution_options, *args, **kw
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1080, in _init_compiled
for key in compiled_params
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1080, in
for key in compiled_params
File "/home/nikolai/.local/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 581, in process
value = _python_UUID(value)
File "/usr/lib/python3.7/uuid.py", line 157, in init
hex = hex.replace('urn:', '').replace('uuid:', '')
sqlalchemy.exc.StatementError: (builtins.AttributeError) 'UUID' object has no attribute 'replace'
[SQL: INSERT INTO ids (id) VALUES (%(id)s)]
[parameters: [{'id': UUID('c5454eca-5b50-41aa-986b-332afaecf9b1')}]]

Versions

• OS: Debian GNU/Linux 10 (buster)
• Python: 3.7.3
• SQLAlchemy: 1.4.26
• Database: psql (PostgreSQL) 14.0 (Debian 14.0-1.pgdg100+1)
• DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg2 2.9.1

Additional context

No response