apply literal value resolution to String

Python string values for which a SQL type is determined from the type of
the value, mainly when using :func:`_sql.literal`, will now apply the
:class:`_types.String` type, rather than the :class:`_types.Unicode`
datatype, for Python string values that test as "ascii only" using Python
``str.isascii()``. If the string is not ``isascii()``, the
:class:`_types.Unicode` datatype will be bound instead, which was used in
all string detection previously. This behavior **only applies to in-place
detection of datatypes when using ``literal()`` or other contexts that have
no existing datatype**, which is not usually the case under normal
:class:`_schema.Column` comparison operations, where the type of the
:class:`_schema.Column` being compared always takes precedence.

Use of the :class:`_types.Unicode` datatype can determine literal string
formatting on backends such as SQL Server, where a literal value (i.e.
using ``literal_binds``) will be rendered as ``N'<value>'`` instead of
``'value'``. For normal bound value handling, the :class:`_types.Unicode`
datatype also may have implications for passing values to the DBAPI, again
in the case of SQL Server, the pyodbc driver supports the use of
:ref:`setinputsizes mode <mssql_pyodbc_setinputsizes>` which will handle
:class:`_types.String` versus :class:`_types.Unicode` differently.

Fixes: #7551
Change-Id: I4f8de63e36532ae8ce4c630ee59211349ce95361

1 files changed, 16 insertions, 3 deletions

diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 42fad5e04..575f402c3 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -184,6 +184,15 @@ class String(Concatenable, TypeEngine[typing_Text]):
         self.length = length
         self.collation = collation
 
+    def _resolve_for_literal(self, value):
+        # I was SO PROUD of my regex trick, but we dont need it.
+        # re.search(r"[^\u0000-\u007F]", value)
+
+        if value.isascii():
+            return _STRING
+        else:
+            return _UNICODE
+
     def literal_processor(self, dialect):
         def process(value):
             value = value.replace("'", "''")
@@ -3019,6 +3028,10 @@ MATCHTYPE = MatchType()
 TABLEVALUE = TableValueType()
 DATETIME_TIMEZONE = DateTime(timezone=True)
 TIME_TIMEZONE = Time(timezone=True)
+_DATETIME = DateTime()
+_TIME = Time()
+_STRING = String()
+_UNICODE = Unicode()
 
 _type_map = {
     int: Integer(),
@@ -3026,12 +3039,12 @@ _type_map = {
     bool: BOOLEANTYPE,
     decimal.Decimal: Numeric(),
     dt.date: Date(),
-    dt.datetime: DateTime(),
-    dt.time: Time(),
+    dt.datetime: _DATETIME,
+    dt.time: _TIME,
     dt.timedelta: Interval(),
     util.NoneType: NULLTYPE,
     bytes: LargeBinary(),
-    str: Unicode(),
+    str: _STRING,
 }