ah no that one looks like this
e = create_engine("mssql+pyodbc://user:pass@dsn", convert_unicode=True,
encoding="utf8")
On 03/09/2016 03:03 PM, Tim Pierson wrote:
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>
>> <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>
>> <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
<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.
