Hello all,
I'm trying to get to the bottom of an issue in which Python 3 (unicode by
definition) strings are always encoded as NVARCHAR for at least two
backends (pymssql and pyodbc). Using bytstrings as comparison arguments
(for example Table.column == value.encode('utf-8')) sends a regular string
literal as expected, but regular strings are encoded as NVARCHAR literals.
This behavior is fairly logical at the underlying driver (pymssql or
pyodbc) level, which is why I'm posting here. I believe the the use of a
String column (as opposed to a Unicode column) type should not pass an
NVARCHAR literal. Doing so has disastrous performance implications, as SQL
Server ends up casting the whole column up. This will wreak havoc when
regularly dealing with large-ish tables (1.7 million rows or so in our
case).
I have previously posted with a LOT more details on StackOverflow
(https://stackoverflow.com/questions/55098426/strings-used-in-query-always-sent-with-nvarchar-syntax-even-if-the-underlying-c).
I also have an MCVE over on GitHub
(https://github.com/ianthetechie/pymssql_sqlalchemy_55098426).
In my MCVE, I outline a possible approach for fixing this, but it appears
to have some problems. I'm posting here asking for feedback on what's wrong
with my approach, and what would be the best way to go about getting this
fixed.
Thanks!
Ian
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
