diff options
Diffstat (limited to 'lib')
23 files changed, 644 insertions, 235 deletions
diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 96189c7fd..1fcb1e05a 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -252,6 +252,8 @@ from .types import TupleType as TupleType from .types import TypeDecorator as TypeDecorator from .types import Unicode as Unicode from .types import UnicodeText as UnicodeText +from .types import UUID as UUID +from .types import Uuid as Uuid from .types import VARBINARY as VARBINARY from .types import VARCHAR as VARCHAR diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index b4c620f91..4295e0ed0 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -813,7 +813,9 @@ import codecs import datetime import operator import re +from typing import overload from typing import TYPE_CHECKING +from uuid import UUID as _python_UUID from . import information_schema as ischema from .json import JSON @@ -854,6 +856,7 @@ from ...types import SMALLINT from ...types import TEXT from ...types import VARCHAR from ...util import update_wrapper +from ...util.typing import Literal if TYPE_CHECKING: from ...sql.dml import DMLState @@ -1369,9 +1372,89 @@ class SMALLMONEY(sqltypes.TypeEngine): __visit_name__ = "SMALLMONEY" -class UNIQUEIDENTIFIER(sqltypes.TypeEngine): +class MSUUid(sqltypes.Uuid): + def bind_processor(self, dialect): + if self.native_uuid: + # this is currently assuming pyodbc; might not work for + # some other mssql driver + return None + else: + if self.as_uuid: + + def process(value): + if value is not None: + value = value.hex + return value + + return process + else: + + def process(value): + if value is not None: + value = value.replace("-", "").replace("''", "'") + return value + + return process + + def literal_processor(self, dialect): + if self.native_uuid: + + def process(value): + if value is not None: + value = f"""'{str(value).replace("''", "'")}'""" + return value + + return process + else: + if self.as_uuid: + + def process(value): + if value is not None: + value = f"""'{value.hex}'""" + return value + + return process + else: + + def process(value): + if value is not None: + value = f"""'{ + value.replace("-", "").replace("'", "''") + }'""" + return value + + return process + + +class UNIQUEIDENTIFIER(sqltypes.Uuid[sqltypes._UUID_RETURN]): __visit_name__ = "UNIQUEIDENTIFIER" + @overload + def __init__( + self: "UNIQUEIDENTIFIER[_python_UUID]", as_uuid: Literal[True] = ... + ): + ... + + @overload + def __init__(self: "UNIQUEIDENTIFIER[str]", as_uuid: Literal[False] = ...): + ... + + def __init__(self, as_uuid: bool = True): + """Construct a :class:`_mssql.UNIQUEIDENTIFIER` type. + + + :param as_uuid=True: if True, values will be interpreted + as Python uuid objects, converting to/from string via the + DBAPI. + + .. versionchanged: 2.0 Added direct "uuid" support to the + :class:`_mssql.UNIQUEIDENTIFIER` datatype; uuid interpretation + defaults to ``True``. + + """ + self.as_uuid = as_uuid + self.native_uuid = True + class SQL_VARIANT(sqltypes.TypeEngine): __visit_name__ = "SQL_VARIANT" @@ -1619,6 +1702,12 @@ class MSTypeCompiler(compiler.GenericTypeCompiler): def visit_SMALLMONEY(self, type_, **kw): return "SMALLMONEY" + def visit_uuid(self, type_, **kw): + if type_.native_uuid: + return self.visit_UNIQUEIDENTIFIER(type_, **kw) + else: + return super().visit_uuid(type_, **kw) + def visit_UNIQUEIDENTIFIER(self, type_, **kw): return "UNIQUEIDENTIFIER" @@ -2709,6 +2798,10 @@ class MSDialect(default.DefaultDialect): supports_statement_cache = True supports_default_values = True supports_empty_insert = False + + # supports_native_uuid is partial here, so we implement our + # own impl type + execution_ctx_cls = MSExecutionContext use_scope_identity = True max_identifier_length = 128 @@ -2730,6 +2823,7 @@ class MSDialect(default.DefaultDialect): DATETIME2: DATETIME2, SMALLDATETIME: SMALLDATETIME, DATETIME: DATETIME, + sqltypes.Uuid: MSUUid, } engine_config_types = default.DefaultDialect.engine_config_types.union( diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 65de88cfe..b585ea992 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -2248,6 +2248,9 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler): else: return self._extend_string(type_, {"national": True}, "CHAR") + def visit_UUID(self, type_, **kw): + return "UUID" + def visit_VARBINARY(self, type_, **kw): return "VARBINARY(%d)" % type_.length diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 55d5a4fb5..37b81e1dd 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -690,7 +690,7 @@ class LONG(sqltypes.Text): class _OracleDateLiteralRender: - def literal_processor(self, dialect): + def _literal_processor_datetime(self, dialect): def process(value): if value is not None: if getattr(value, "microsecond", None): @@ -709,6 +709,25 @@ class _OracleDateLiteralRender: return process + def _literal_processor_date(self, dialect): + def process(value): + if value is not None: + if getattr(value, "microsecond", None): + value = ( + f"""TO_TIMESTAMP""" + f"""('{value.isoformat().split("T")[0]}', """ + """'YYYY-MM-DD')""" + ) + else: + value = ( + f"""TO_DATE""" + f"""('{value.isoformat().split("T")[0]}', """ + """'YYYY-MM-DD')""" + ) + return value + + return process + class DATE(_OracleDateLiteralRender, sqltypes.DateTime): """Provide the oracle DATE type. @@ -723,12 +742,16 @@ class DATE(_OracleDateLiteralRender, sqltypes.DateTime): __visit_name__ = "DATE" + def literal_processor(self, dialect): + return self._literal_processor_datetime(dialect) + def _compare_type_affinity(self, other): return other._type_affinity in (sqltypes.DateTime, sqltypes.Date) class _OracleDate(_OracleDateLiteralRender, sqltypes.Date): - pass + def literal_processor(self, dialect): + return self._literal_processor_date(dialect) class INTERVAL(sqltypes.NativeForEmulated, sqltypes._AbstractInterval): diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index 290789f32..fbac8b93e 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -580,7 +580,8 @@ class _CXOracleDate(oracle._OracleDate): class _CXOracleTIMESTAMP(oracle._OracleDateLiteralRender, sqltypes.TIMESTAMP): - pass + def literal_processor(self, dialect): + return self._literal_processor_datetime(dialect) # TODO: the names used across CHAR / VARCHAR / NCHAR / NVARCHAR diff --git a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py index 6b8b3f6d0..e831f2ed9 100644 --- a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py +++ b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py @@ -8,7 +8,6 @@ from .base import _FLOAT_TYPES from .base import _INT_TYPES from .base import PGDialect from .base import PGExecutionContext -from .base import UUID from .hstore import HSTORE from ... import exc from ... import types as sqltypes @@ -63,21 +62,6 @@ class _PsycopgHStore(HSTORE): ) -class _PsycopgUUID(UUID): - def bind_processor(self, dialect): - return None - - def result_processor(self, dialect, coltype): - if not self.as_uuid and dialect.use_native_uuid: - - def process(value): - if value is not None: - value = str(value) - return value - - return process - - class _PsycopgARRAY(PGARRAY): render_bind_cast = True @@ -106,7 +90,6 @@ class _PGDialect_common_psycopg(PGDialect): { sqltypes.Numeric: _PsycopgNumeric, HSTORE: _PsycopgHStore, - UUID: _PsycopgUUID, sqltypes.ARRAY: _PsycopgARRAY, }, ) @@ -115,14 +98,12 @@ class _PGDialect_common_psycopg(PGDialect): self, client_encoding=None, use_native_hstore=True, - use_native_uuid=True, **kwargs, ): PGDialect.__init__(self, **kwargs) if not use_native_hstore: self._has_native_hstore = False self.use_native_hstore = use_native_hstore - self.use_native_uuid = use_native_uuid self.client_encoding = client_encoding def create_connect_args(self, url): diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py index d320c323c..1ec787e1f 100644 --- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py +++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py @@ -138,7 +138,6 @@ from .base import PGDialect from .base import PGExecutionContext from .base import PGIdentifierPreparer from .base import REGCLASS -from .base import UUID from ... import exc from ... import pool from ... import util @@ -150,12 +149,6 @@ from ...util.concurrency import await_fallback from ...util.concurrency import await_only -try: - from uuid import UUID as _python_UUID # noqa -except ImportError: - _python_UUID = None - - class AsyncpgString(sqltypes.String): render_bind_cast = True @@ -237,30 +230,6 @@ class AsyncpgJSONPathType(json.JSONPathType): return process -class AsyncpgUUID(UUID): - render_bind_cast = True - - def bind_processor(self, dialect): - if not self.as_uuid and dialect.use_native_uuid: - - def process(value): - if value is not None: - value = _python_UUID(value) - return value - - return process - - def result_processor(self, dialect, coltype): - if not self.as_uuid and dialect.use_native_uuid: - - def process(value): - if value is not None: - value = str(value) - return value - - return process - - class AsyncpgNumeric(sqltypes.Numeric): render_bind_cast = True @@ -831,8 +800,6 @@ class PGDialect_asyncpg(PGDialect): statement_compiler = PGCompiler_asyncpg preparer = PGIdentifierPreparer_asyncpg - use_native_uuid = True - colspecs = util.update_copy( PGDialect.colspecs, { @@ -842,7 +809,6 @@ class PGDialect_asyncpg(PGDialect): sqltypes.DateTime: AsyncpgDateTime, sqltypes.Interval: AsyncPgInterval, INTERVAL: AsyncPgInterval, - UUID: AsyncpgUUID, sqltypes.Boolean: AsyncpgBoolean, sqltypes.Integer: AsyncpgInteger, sqltypes.BigInteger: AsyncpgBigInteger, diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 0aeeb806b..146e59c4d 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1453,9 +1453,6 @@ from collections import defaultdict import datetime as dt import re from typing import Any -from typing import overload -from typing import TypeVar -from uuid import UUID as _python_UUID from . import array as _array from . import dml @@ -1489,8 +1486,8 @@ from ...types import NUMERIC from ...types import REAL from ...types import SMALLINT from ...types import TEXT +from ...types import UUID as UUID from ...types import VARCHAR -from ...util.typing import Literal IDX_USING = re.compile(r"^(?:btree|hash|gist|gin|[\w_]+)$", re.I) @@ -1606,6 +1603,11 @@ _FLOAT_TYPES = (700, 701, 1021, 1022) _INT_TYPES = (20, 21, 23, 26, 1005, 1007, 1016) +class PGUuid(UUID): + render_bind_cast = True + render_literal_cast = True + + class BYTEA(sqltypes.LargeBinary[bytes]): __visit_name__ = "BYTEA" @@ -1765,103 +1767,6 @@ class BIT(sqltypes.TypeEngine[int]): PGBit = BIT -_UUID_RETURN = TypeVar("_UUID_RETURN", str, _python_UUID) - - -class UUID(sqltypes.TypeEngine[_UUID_RETURN]): - - """PostgreSQL UUID type. - - Represents the UUID column type, interpreting - data either as natively returned by the DBAPI - or as Python uuid objects. - - The UUID type is currently known to work within the prominent DBAPI - drivers supported by SQLAlchemy including psycopg, psycopg2, pg8000 and - asyncpg. Support for other DBAPI drivers may be incomplete or non-present. - - """ - - __visit_name__ = "UUID" - - @overload - def __init__(self: "UUID[_python_UUID]", as_uuid: Literal[True] = ...): - ... - - @overload - def __init__(self: "UUID[str]", as_uuid: Literal[False] = ...): - ... - - def __init__(self, as_uuid: bool = True): - """Construct a UUID type. - - - :param as_uuid=True: if True, values will be interpreted - as Python uuid objects, converting to/from string via the - DBAPI. - - .. versionchanged: 2 ``as_uuid`` now defaults to ``True``. - - """ - self.as_uuid = as_uuid - - def coerce_compared_value(self, op, value): - """See :meth:`.TypeEngine.coerce_compared_value` for a description.""" - - if isinstance(value, str): - return self - else: - return super(UUID, self).coerce_compared_value(op, value) - - def bind_processor(self, dialect): - if self.as_uuid: - - def process(value): - if value is not None: - value = str(value) - return value - - return process - else: - return None - - def result_processor(self, dialect, coltype): - if self.as_uuid: - - def process(value): - if value is not None: - value = _python_UUID(value) - return value - - return process - else: - return None - - def literal_processor(self, dialect): - if self.as_uuid: - - def process(value): - if value is not None: - value = "'%s'::UUID" % value - return value - - return process - else: - - def process(value): - if value is not None: - value = "'%s'" % value - return value - - return process - - @property - def python_type(self): - return _python_UUID if self.as_uuid else str - - -PGUuid = UUID - class TSVECTOR(sqltypes.TypeEngine[Any]): @@ -2162,6 +2067,7 @@ colspecs = { sqltypes.Enum: ENUM, sqltypes.JSON.JSONPathType: _json.JSONPathType, sqltypes.JSON: _json.JSON, + UUID: PGUuid, } ischema_names = { @@ -3043,6 +2949,12 @@ class PGTypeCompiler(compiler.GenericTypeCompiler): compiled = "BIT(%d)" % type_.length return compiled + def visit_uuid(self, type_, **kw): + if type_.native_uuid: + return self.visit_UUID(type_, **kw) + else: + return super().visit_uuid(type_, **kw) + def visit_UUID(self, type_, **kw): return "UUID" @@ -3267,6 +3179,7 @@ class PGDialect(default.DefaultDialect): supports_native_enum = True supports_native_boolean = True + supports_native_uuid = True supports_smallserial = True supports_sequences = True diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index fbed3a464..6cb97ece4 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -93,7 +93,6 @@ of the :ref:`psycopg2 <psycopg2_isolation_level>` dialect: """ # noqa import decimal import re -from uuid import UUID as _python_UUID from .array import ARRAY as PGARRAY from .base import _DECIMAL_TYPES @@ -105,7 +104,6 @@ from .base import PGCompiler from .base import PGDialect from .base import PGExecutionContext from .base import PGIdentifierPreparer -from .base import UUID from .json import JSON from .json import JSONB from .json import JSONPathType @@ -195,30 +193,6 @@ class _PGJSONPathType(JSONPathType): # DBAPI type 1009 -class _PGUUID(UUID): - render_bind_cast = True - - def bind_processor(self, dialect): - if not self.as_uuid: - - def process(value): - if value is not None: - value = _python_UUID(value) - return value - - return process - - def result_processor(self, dialect, coltype): - if not self.as_uuid: - - def process(value): - if value is not None: - value = str(value) - return value - - return process - - class _PGEnum(ENUM): def get_dbapi_type(self, dbapi): return dbapi.UNKNOWN @@ -391,7 +365,6 @@ class PGDialect_pg8000(PGDialect): sqltypes.JSON.JSONIndexType: _PGJSONIndexType, sqltypes.JSON.JSONIntIndexType: _PGJSONIntIndexType, sqltypes.JSON.JSONStrIndexType: _PGJSONStrIndexType, - UUID: _PGUUID, sqltypes.Interval: _PGInterval, INTERVAL: _PGInterval, sqltypes.DateTime: _PGTimeStamp, diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py index 634cea38a..7ec26cb4e 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py @@ -62,11 +62,9 @@ import re from ._psycopg_common import _PGDialect_common_psycopg from ._psycopg_common import _PGExecutionContext_common_psycopg -from ._psycopg_common import _PsycopgUUID from .base import INTERVAL from .base import PGCompiler from .base import PGIdentifierPreparer -from .base import UUID from .json import JSON from .json import JSONB from .json import JSONPathType @@ -120,10 +118,6 @@ class _PGJSONPathType(JSONPathType): pass -class _PGUUID(_PsycopgUUID): - render_bind_cast = True - - class _PGInterval(INTERVAL): render_bind_cast = True @@ -201,7 +195,6 @@ class PGDialect_psycopg(_PGDialect_common_psycopg): sqltypes.JSON.JSONPathType: _PGJSONPathType, sqltypes.JSON.JSONIntIndexType: _PGJSONIntIndexType, sqltypes.JSON.JSONStrIndexType: _PGJSONStrIndexType, - UUID: _PGUUID, sqltypes.Interval: _PGInterval, INTERVAL: _PGInterval, sqltypes.Date: _PGDate, diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index f7d1942a0..f5d84a5a3 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -676,7 +676,7 @@ class PGDialect_psycopg2(_PGDialect_common_psycopg): fns.append(on_connect) - if self.dbapi and self.use_native_uuid: + if self.dbapi: def on_connect(dbapi_conn): extras.register_uuid(None, dbapi_conn) diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 04b017f08..bcbe83f3f 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -144,6 +144,7 @@ class DefaultDialect(Dialect): supports_native_enum = False supports_native_boolean = False + supports_native_uuid = False non_native_boolean_check_constraint = True supports_simple_order_by_label = True diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py index c1008dc56..4020af354 100644 --- a/lib/sqlalchemy/engine/interfaces.py +++ b/lib/sqlalchemy/engine/interfaces.py @@ -789,6 +789,14 @@ class Dialect(EventTarget): """indicates if Decimal objects are handled and returned for precision numeric types, or if floats are returned""" + supports_native_uuid: bool + """indicates if Python UUID() objects are handled natively by the + driver for SQL UUID datatypes. + + .. versionadded:: 2.0 + + """ + construct_arguments: Optional[ List[Tuple[Type[ClauseElement], Mapping[str, Any]]] ] = None diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py index 8aa8f12cc..f6dd92865 100644 --- a/lib/sqlalchemy/sql/_elements_constructors.py +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -390,7 +390,7 @@ def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: def bindparam( key: Optional[str], value: Any = _NoArg.NO_ARG, - type_: Optional[TypeEngine[_T]] = None, + type_: Optional[_TypeEngineArgument[_T]] = None, unique: bool = False, required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG, quote: Optional[bool] = None, diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index 501188b12..d56035db7 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -629,14 +629,26 @@ class LiteralValueImpl(RoleImpl): _resolve_literal_only = True def _implicit_coercions( - self, element, resolved, argname, type_=None, **kw + self, + element, + resolved, + argname, + type_=None, + literal_execute=False, + **kw, ): if not _is_literal(resolved): self._raise_for_expected( element, resolved=resolved, argname=argname, **kw ) - return elements.BindParameter(None, element, type_=type_, unique=True) + return elements.BindParameter( + None, + element, + type_=type_, + unique=True, + literal_execute=literal_execute, + ) def _literal_coercion(self, element, argname=None, type_=None, **kw): return element diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 12a598717..3685751b0 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -3013,14 +3013,14 @@ class SQLCompiler(Compiled): def bindparam_string( self, - name, - positional_names=None, - post_compile=False, - expanding=False, - escaped_from=None, - bindparam_type=None, - **kw, - ): + name: str, + positional_names: Optional[List[str]] = None, + post_compile: bool = False, + expanding: bool = False, + escaped_from: Optional[str] = None, + bindparam_type: Optional[TypeEngine[Any]] = None, + **kw: Any, + ) -> str: if self.positional: if positional_names is not None: @@ -3045,9 +3045,23 @@ class SQLCompiler(Compiled): {escaped_from: name} ) if post_compile: - return "__[POSTCOMPILE_%s]" % name - - ret = self.bindtemplate % {"name": name} + ret = "__[POSTCOMPILE_%s]" % name + if expanding: + # for expanding, bound parameters or literal values will be + # rendered per item + return ret + + # otherwise, for non-expanding "literal execute", apply + # bind casts as determined by the datatype + if bindparam_type is not None: + type_impl = bindparam_type._unwrapped_dialect_impl( + self.dialect + ) + if type_impl.render_literal_cast: + ret = self.render_bind_cast(bindparam_type, type_impl, ret) + return ret + else: + ret = self.bindtemplate % {"name": name} if ( bindparam_type is not None @@ -5432,10 +5446,12 @@ class GenericTypeCompiler(TypeCompiler): def visit_NCLOB(self, type_, **kw): return "NCLOB" - def _render_string_type(self, type_, name): + def _render_string_type(self, type_, name, length_override=None): text = name - if type_.length: + if length_override: + text += "(%d)" % length_override + elif type_.length: text += "(%d)" % type_.length if type_.collation: text += ' COLLATE "%s"' % type_.collation @@ -5468,6 +5484,9 @@ class GenericTypeCompiler(TypeCompiler): def visit_BOOLEAN(self, type_, **kw): return "BOOLEAN" + def visit_uuid(self, type_, **kw): + return self._render_string_type(type_, "CHAR", length_override=32) + def visit_large_binary(self, type_, **kw): return self.visit_BLOB(type_, **kw) diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 61c5379d8..ce08a0a10 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -122,7 +122,9 @@ _NMT = TypeVar("_NMT", bound="_NUMBER") def literal( - value: Any, type_: Optional[_TypeEngineArgument[_T]] = None + value: Any, + type_: Optional[_TypeEngineArgument[_T]] = None, + literal_execute: bool = False, ) -> BindParameter[_T]: r"""Return a literal clause, bound to a bind parameter. @@ -136,13 +138,24 @@ def literal( :class:`BindParameter` with a bound value. :param value: the value to be bound. Can be any Python object supported by - the underlying DB-API, or is translatable via the given type argument. + the underlying DB-API, or is translatable via the given type argument. - :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which - will provide bind-parameter translation for this literal. + :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which will + provide bind-parameter translation for this literal. + + :param literal_execute: optional bool, when True, the SQL engine will + attempt to render the bound value directly in the SQL statement at + execution time rather than providing as a parameter value. + + .. versionadded:: 2.0 """ - return coercions.expect(roles.LiteralValueRole, value, type_=type_) + return coercions.expect( + roles.LiteralValueRole, + value, + type_=type_, + literal_execute=literal_execute, + ) def literal_column( diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 90b4b9c9e..50cb32503 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -30,6 +30,7 @@ from typing import Type from typing import TYPE_CHECKING from typing import TypeVar from typing import Union +from uuid import UUID as _python_UUID from . import coercions from . import elements @@ -689,11 +690,30 @@ class Double(Float[_N]): class _RenderISO8601NoT: - def literal_processor(self, dialect): - def process(value): - if value is not None: - value = f"""'{value.isoformat().replace("T", " ")}'""" - return value + def _literal_processor_datetime(self, dialect): + return self._literal_processor_portion(dialect, None) + + def _literal_processor_date(self, dialect): + return self._literal_processor_portion(dialect, 0) + + def _literal_processor_time(self, dialect): + return self._literal_processor_portion(dialect, -1) + + def _literal_processor_portion(self, dialect, _portion=None): + assert _portion in (None, 0, -1) + if _portion is not None: + + def process(value): + if value is not None: + value = f"""'{value.isoformat().split("T")[_portion]}'""" + return value + + else: + + def process(value): + if value is not None: + value = f"""'{value.isoformat().replace("T", " ")}'""" + return value return process @@ -746,6 +766,9 @@ class DateTime( else: return self + def literal_processor(self, dialect): + return self._literal_processor_datetime(dialect) + @property def python_type(self): return dt.datetime @@ -775,6 +798,9 @@ class Date(_RenderISO8601NoT, HasExpressionLookup, TypeEngine[dt.date]): def python_type(self): return dt.date + def literal_processor(self, dialect): + return self._literal_processor_date(dialect) + @util.memoized_property def _expression_adaptations(self): # Based on https://www.postgresql.org/docs/current/\ @@ -833,6 +859,9 @@ class Time(_RenderISO8601NoT, HasExpressionLookup, TypeEngine[dt.time]): operators.sub: {Time: Interval, Interval: self.__class__}, } + def literal_processor(self, dialect): + return self._literal_processor_time(dialect) + class _Binary(TypeEngine[bytes]): @@ -3302,6 +3331,223 @@ class MatchType(Boolean): """ +_UUID_RETURN = TypeVar("_UUID_RETURN", str, _python_UUID) + + +class Uuid(TypeEngine[_UUID_RETURN]): + + """Represent a database agnostic UUID datatype. + + For backends that have no "native" UUID datatype, the value will + make use of ``CHAR(32)`` and store the UUID as a 32-character alphanumeric + hex string. + + For backends which are known to support ``UUID`` directly or a similar + uuid-storing datatype such as SQL Server's ``UNIQUEIDENTIFIER``, a + "native" mode enabled by default allows these types will be used on those + backends. + + .. versionadded:: 2.0 + + .. seealso:: + + :class:`_sqltypes.UUID` - represents exactly the ``UUID`` datatype + without any backend-agnostic behaviors. + + """ + + __visit_name__ = "uuid" + + collation = None + + @overload + def __init__( + self: "Uuid[_python_UUID]", + as_uuid: Literal[True] = ..., + native_uuid: bool = ..., + ): + ... + + @overload + def __init__( + self: "Uuid[str]", + as_uuid: Literal[False] = ..., + native_uuid: bool = ..., + ): + ... + + def __init__(self, as_uuid: bool = True, native_uuid: bool = True): + """Construct a :class:`_sqltypes.Uuid` type. + + :param as_uuid=True: if True, values will be interpreted + as Python uuid objects, converting to/from string via the + DBAPI. + + .. versionchanged: 2.0 ``as_uuid`` now defaults to ``True``. + + :param native_uuid=True: if True, backends that support either the + ``UUID`` datatype directly, or a UUID-storing value + (such as SQL Server's ``UNIQUEIDENTIFIER`` will be used by those + backends. If False, a ``CHAR(32)`` datatype will be used for + all backends regardless of native support. + + """ + self.as_uuid = as_uuid + self.native_uuid = native_uuid + + @property + def python_type(self): + return _python_UUID if self.as_uuid else str + + def coerce_compared_value(self, op, value): + """See :meth:`.TypeEngine.coerce_compared_value` for a description.""" + + if isinstance(value, str): + return self + else: + return super().coerce_compared_value(op, value) + + def bind_processor(self, dialect): + character_based_uuid = ( + not dialect.supports_native_uuid or not self.native_uuid + ) + + if character_based_uuid: + if self.as_uuid: + + def process(value): + if value is not None: + value = value.hex + return value + + return process + else: + + def process(value): + if value is not None: + value = value.replace("-", "") + return value + + return process + else: + return None + + def result_processor(self, dialect, coltype): + character_based_uuid = ( + not dialect.supports_native_uuid or not self.native_uuid + ) + + if character_based_uuid: + if self.as_uuid: + + def process(value): + if value is not None: + value = _python_UUID(value) + return value + + return process + else: + + def process(value): + if value is not None: + value = str(_python_UUID(value)) + return value + + return process + else: + + if not self.as_uuid: + + def process(value): + if value is not None: + value = str(value) + return value + + return process + else: + return None + + def literal_processor(self, dialect): + character_based_uuid = ( + not dialect.supports_native_uuid or not self.native_uuid + ) + + if not self.as_uuid: + + def process(value): + if value is not None: + value = ( + f"""'{value.replace("-", "").replace("'", "''")}'""" + ) + return value + + return process + else: + if character_based_uuid: + + def process(value): + if value is not None: + value = f"""'{value.hex}'""" + return value + + return process + else: + + def process(value): + if value is not None: + value = f"""'{str(value).replace("'", "''")}'""" + return value + + return process + + +class UUID(Uuid[_UUID_RETURN]): + + """Represent the SQL UUID type. + + This is the SQL-native form of the :class:`_types.Uuid` database agnostic + datatype, and is backwards compatible with the previous PostgreSQL-only + version of ``UUID``. + + The :class:`_sqltypes.UUID` datatype only works on databases that have a + SQL datatype named ``UUID``. It will not function for backends which don't + have this exact-named type, including SQL Server. For backend-agnostic UUID + values with native support, including for SQL Server's ``UNIQUEIDENTIFIER`` + datatype, use the :class:`_sqltypes.Uuid` datatype. + + .. versionadded:: 2.0 + + .. seealso:: + + :class:`_sqltypes.Uuid` + + """ + + __visit_name__ = "UUID" + + @overload + def __init__(self: "UUID[_python_UUID]", as_uuid: Literal[True] = ...): + ... + + @overload + def __init__(self: "UUID[str]", as_uuid: Literal[False] = ...): + ... + + def __init__(self, as_uuid: bool = True): + """Construct a :class:`_sqltypes.UUID` type. + + + :param as_uuid=True: if True, values will be interpreted + as Python uuid objects, converting to/from string via the + DBAPI. + + .. versionchanged: 2.0 ``as_uuid`` now defaults to ``True``. + + """ + self.as_uuid = as_uuid + self.native_uuid = True + + NULLTYPE = NullType() BOOLEANTYPE = Boolean() STRINGTYPE = String() @@ -3319,6 +3565,7 @@ _type_map: Dict[Type[Any], TypeEngine[Any]] = { int: Integer(), float: Float(), bool: BOOLEANTYPE, + _python_UUID: Uuid(), decimal.Decimal: Numeric(), dt.date: Date(), dt.datetime: _DATETIME, diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index b9847d406..00bae17bc 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -134,6 +134,14 @@ class TypeEngine(Visitable, Generic[_T]): """ + render_literal_cast = False + """render casts when rendering a value as an inline literal, + e.g. with :meth:`.TypeEngine.literal_processor`. + + .. versionadded:: 2.0 + + """ + class Comparator( ColumnOperators, Generic[_CT], diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index e63a3e191..4fff6546e 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -722,6 +722,15 @@ class SuiteRequirements(Requirements): return exclusions.open() @property + def unicode_data_no_special_types(self): + """Target database/dialect can receive / deliver / compare data with + non-ASCII characters in plain VARCHAR, TEXT columns, without the need + for special "national" datatypes like NVARCHAR or similar. + + """ + return exclusions.open() + + @property def unicode_data(self): """Target database/dialect must support Python unicode objects with non-ASCII characters represented, delivered as bound parameters diff --git a/lib/sqlalchemy/testing/schema.py b/lib/sqlalchemy/testing/schema.py index 949f30e53..e4a92a732 100644 --- a/lib/sqlalchemy/testing/schema.py +++ b/lib/sqlalchemy/testing/schema.py @@ -39,6 +39,12 @@ def Table(*args, **kw): if "test_needs_fk" in test_opts or "test_needs_acid" in test_opts: kw["mysql_engine"] = "InnoDB" else: + # there are in fact test fixtures that rely upon MyISAM, + # due to MySQL / MariaDB having poor FK behavior under innodb, + # such as a self-referential table can't be deleted from at + # once without attending to per-row dependencies. We'd need to + # add special steps to some fixtures if we want to not + # explicitly state MyISAM here kw["mysql_engine"] = "MyISAM" elif exclusions.against(config._current, "mariadb"): if ( diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py index 817d3a53d..f1e376837 100644 --- a/lib/sqlalchemy/testing/suite/test_types.py +++ b/lib/sqlalchemy/testing/suite/test_types.py @@ -5,6 +5,7 @@ import datetime import decimal import json import re +import uuid from .. import config from .. import engines @@ -41,6 +42,8 @@ from ... import type_coerce from ... import TypeDecorator from ... import Unicode from ... import UnicodeText +from ... import UUID +from ... import Uuid from ...orm import declarative_base from ...orm import Session from ...sql.sqltypes import LargeBinary @@ -59,31 +62,54 @@ class _LiteralRoundTripFixture: # official type; ideally we'd be able to use CAST here # but MySQL in particular can't CAST fully - def run(type_, input_, output, filter_=None): + def run( + type_, + input_, + output, + filter_=None, + compare=None, + support_whereclause=True, + ): t = Table("t", metadata, Column("x", type_)) t.create(connection) for value in input_: - ins = ( - t.insert() - .values(x=literal(value, type_)) - .compile( - dialect=testing.db.dialect, - compile_kwargs=dict(literal_binds=True), - ) + ins = t.insert().values( + x=literal(value, type_, literal_execute=True) ) connection.execute(ins) - if self.supports_whereclause: - stmt = t.select().where(t.c.x == literal(value)) + if support_whereclause and self.supports_whereclause: + if compare: + stmt = t.select().where( + t.c.x + == literal( + compare, + type_, + literal_execute=True, + ), + t.c.x + == literal( + input_[0], + type_, + literal_execute=True, + ), + ) + else: + stmt = t.select().where( + t.c.x + == literal( + compare if compare is not None else input_[0], + type_, + literal_execute=True, + ) + ) else: stmt = t.select() - stmt = stmt.compile( - dialect=testing.db.dialect, - compile_kwargs=dict(literal_binds=True), - ) - for row in connection.execute(stmt): + rows = connection.execute(stmt).all() + assert rows, "No rows returned" + for row in rows: value = row[0] if filter_ is not None: value = filter_(value) @@ -278,6 +304,7 @@ class TextTest(_LiteralRoundTripFixture, fixtures.TablesTest): def test_literal(self, literal_round_trip): literal_round_trip(Text, ["some text"], ["some text"]) + @requirements.unicode_data_no_special_types def test_literal_non_ascii(self, literal_round_trip): literal_round_trip(Text, ["réve🐍 illé"], ["réve🐍 illé"]) @@ -310,6 +337,7 @@ class StringTest(_LiteralRoundTripFixture, fixtures.TestBase): # datatype for the literal part because all strings are unicode literal_round_trip(String(40), ["some text"], ["some text"]) + @requirements.unicode_data_no_special_types def test_literal_non_ascii(self, literal_round_trip): literal_round_trip(String(40), ["réve🐍 illé"], ["réve🐍 illé"]) @@ -410,7 +438,10 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase): @testing.requires.datetime_literals def test_literal(self, literal_round_trip): compare = self.compare or self.data - literal_round_trip(self.datatype, [self.data], [compare]) + + literal_round_trip( + self.datatype, [self.data], [compare], compare=compare + ) @testing.requires.standalone_null_binds_whereclause def test_null_bound_comparison(self): @@ -502,6 +533,11 @@ class DateTest(_DateFixture, fixtures.TablesTest): class DateTimeCoercedToDateTimeTest(_DateFixture, fixtures.TablesTest): + """this particular suite is testing that datetime parameters get + coerced to dates, which tends to be something DBAPIs do. + + """ + __requires__ = "date", "date_coerces_from_datetime" __backend__ = True datatype = Date @@ -761,6 +797,7 @@ class NumericTest(_LiteralRoundTripFixture, fixtures.TestBase): [15.7563, decimal.Decimal("15.7563")], [15.7563], filter_=lambda n: n is not None and round(n, 5) or None, + support_whereclause=False, ) @testing.requires.precision_generic_float_type @@ -1616,6 +1653,102 @@ class JSONLegacyStringCastIndexTest( ) +class UuidTest(_LiteralRoundTripFixture, fixtures.TablesTest): + __backend__ = True + + datatype = Uuid + + @classmethod + def define_tables(cls, metadata): + Table( + "uuid_table", + metadata, + Column( + "id", Integer, primary_key=True, test_needs_autoincrement=True + ), + Column("uuid_data", cls.datatype), + Column("uuid_text_data", cls.datatype(as_uuid=False)), + Column("uuid_data_nonnative", Uuid(native_uuid=False)), + Column( + "uuid_text_data_nonnative", + Uuid(as_uuid=False, native_uuid=False), + ), + ) + + def test_uuid_round_trip(self, connection): + data = uuid.uuid4() + uuid_table = self.tables.uuid_table + + connection.execute( + uuid_table.insert(), + {"id": 1, "uuid_data": data, "uuid_data_nonnative": data}, + ) + row = connection.execute( + select( + uuid_table.c.uuid_data, uuid_table.c.uuid_data_nonnative + ).where( + uuid_table.c.uuid_data == data, + uuid_table.c.uuid_data_nonnative == data, + ) + ).first() + eq_(row, (data, data)) + + def test_uuid_text_round_trip(self, connection): + data = str(uuid.uuid4()) + uuid_table = self.tables.uuid_table + + connection.execute( + uuid_table.insert(), + { + "id": 1, + "uuid_text_data": data, + "uuid_text_data_nonnative": data, + }, + ) + row = connection.execute( + select( + uuid_table.c.uuid_text_data, + uuid_table.c.uuid_text_data_nonnative, + ).where( + uuid_table.c.uuid_text_data == data, + uuid_table.c.uuid_text_data_nonnative == data, + ) + ).first() + eq_((row[0].lower(), row[1].lower()), (data, data)) + + def test_literal_uuid(self, literal_round_trip): + data = uuid.uuid4() + literal_round_trip(self.datatype, [data], [data]) + + def test_literal_text(self, literal_round_trip): + data = str(uuid.uuid4()) + literal_round_trip( + self.datatype(as_uuid=False), + [data], + [data], + filter_=lambda x: x.lower(), + ) + + def test_literal_nonnative_uuid(self, literal_round_trip): + data = uuid.uuid4() + literal_round_trip(Uuid(native_uuid=False), [data], [data]) + + def test_literal_nonnative_text(self, literal_round_trip): + data = str(uuid.uuid4()) + literal_round_trip( + Uuid(as_uuid=False, native_uuid=False), + [data], + [data], + filter_=lambda x: x.lower(), + ) + + +class NativeUUIDTest(UuidTest): + __requires__ = ("uuid_data_type",) + + datatype = UUID + + __all__ = ( "BinaryTest", "UnicodeVarcharTest", @@ -1640,4 +1773,6 @@ __all__ = ( "DateHistoricTest", "StringTest", "BooleanTest", + "UuidTest", + "NativeUUIDTest", ) diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py index b0df99c41..9914d5ad1 100644 --- a/lib/sqlalchemy/types.py +++ b/lib/sqlalchemy/types.py @@ -63,6 +63,8 @@ from .sql.sqltypes import TIMESTAMP as TIMESTAMP from .sql.sqltypes import TupleType as TupleType from .sql.sqltypes import Unicode as Unicode from .sql.sqltypes import UnicodeText as UnicodeText +from .sql.sqltypes import UUID as UUID +from .sql.sqltypes import Uuid as Uuid from .sql.sqltypes import VARBINARY as VARBINARY from .sql.sqltypes import VARCHAR as VARCHAR from .sql.type_api import adapt_type as adapt_type |