OK what you can try here that might resolve all the issues is to only
pass encoded utf8 to the app (and also receive it on the way back); you
can do that by setting convert_unicode=True at the create_engine() level
where will take effect for all String columns. Technically if you're
storing non-ascii data you'd be using an NVARCHAR column so you wouldn't
get that comparison error. For the result rows coming back, again the
VARCHAR is not giving pyodbc the clue it needs to know that it should
decode, so SQLA's convert_unicode will get you that also.
On 03/09/2016 11:13 AM, Tim Pierson wrote:
Python 2.7, on windows using pyodbc and whatever the default driver is.
Not sure if the problem exists on FreeTDS.
Here's an example of the ProgrammingError that is raised when I don't
cast the filter() query parameter text:
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py",
line 2320, in all
return list(self)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py",
line 2438, in __iter__
return self._execute_and_instances(context)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py",
line 2453, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
729, in execute
return meth(self, multiparams, params)
File
"C:\Python27\lib\site-packages\sqlalchemy\sql\elements.py", line
322, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams,
params)
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
826, in _execute_clauseelement
compiled_sql, distilled_params
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
958, in _execute_context
context)
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1159, in _handle_dbapi_exception
exc_info
File
"C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line
199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
951, in _execute_context
context)
File
"C:\Python27\lib\site-packages\sqlalchemy\engine\default.py",
line 436, in do_execute
cursor.execute(statement, parameters)
ProgrammingError: (ProgrammingError) ('42000', '[42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]The data types
varchar(max) and ntext are incompatible in the equal to
operator. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL
Server Driver][SQL Server]Statement(s) could not be prepared.
(8180)') [....]
And here's the relevant stack trace for the UnicodeDecodeError that
thows on a similar (but different) query with a text parameter passed to
filter. I believe the problem is when the database string comes back
and fails to be decoded:
[Script ... ] line 450, in get
AnnotationTypes.tag_or_inline == self.tag_or_inline).all()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py",
line 2320, in all
return list(self)
File
"C:\Python27\lib\site-packages\sqlalchemy\orm\loading.py", line
76, in instances
labels) for row in fetch]
File "C:\Python27\lib\site-packages\sqlalchemy\orm\query.py",
line 3523, in proc
return row[column]
UnicodeDecodeError: 'utf8' codec can't decode byte 0x93 in
position 506: invalid start byte
On Sunday, March 6, 2016 at 6:59:44 PM UTC-5, Mike Bayer wrote:
Can you share a stack trace please ? Encoding operations can occur
in many places and I don't see that identified here. Also this is
Python 3? What OS platform and ODBC driver / configuration as well?
If freetds please share your freetds config too.
On Mar 4, 2016, at 2:51 PM, Tim Pierson <tim.p...@gmail.com
<javascript:>> wrote:
I've seen a couple of issues regarding mssql and drivers handling
of strings but I haven't been able to resolve my problem.
I have a flask app with flask-sqlalchemy models defined and a
simple query is throwing the above complaint about decoding the
string.
My connection string:
mssql+pyodbc://[...]/[...]?trusted_connection=yes&charset=utf8&deprecate_large_types=True
A
Which is given to the SQLAlchemy class instance db after the
initialization of the app with:
db.init_app(app)
The offending model:
class Annotations(db.Model):
id = db.Column(db.Integer, primary_key=True)
creation_date = db.Column(db.DateTime, default=datetime.datetime.now)
created_by = db.Column(db.String, nullable=False)
annotation = db.Column(sqlalchemy.Unicode, nullable=False)
annotation_type_id = db.Column(db.Integer,
db.ForeignKey('AnnotationTypes.id'))
The query:
db.session.query(Annotations.annotation).all()
I've replaced the original column type db.String with
sqlalchemy.Unicode on the field that throws the error as per an
earlier question on this list but it hasn't resolved the problem.
Could someone offer any pointers?
Thanks,
--
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 sqlalchemy+...@googlegroups.com <javascript:>.
To post to this group, send email to sqlal...@googlegroups.com
<javascript:>.
Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
