Thanks.
So that would be a matter of adding convert_unicode=True to the connection
string?
. . .
?trusted_connection=yes&charset=utf8&deprecate_large_types=True&convert_unicode=True
On Wednesday, March 9, 2016 at 2:49:35 PM UTC-5, Mike Bayer wrote:
>
> 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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.
