I'd add that other dialects such as pyodbc+mssql work as expected, which is
to ignore trailing white space in fixed width character fields for
comparison purposes.
On Thursday, October 3, 2019 at 4:47:41 AM UTC-4, mdob wrote:
>
> Hi everyone,
>
> There's IMO unusual behavior in Oracle when using bound parameters on
> NCHAR column.
>
> from sqlalchemy import create_engine
> from sqlalchemy.sql import text
>
>
> e = create_engine('oracle://chinook:p4ssw0rd@localhost/xe')
>
>
> result = e.execute(
> text("select * from nchartable where id = '1'"),
> ).fetchall()
>
>
> print 'hardcoded:', list(result)
>
>
> result = e.execute(
> text('select * from nchartable where id = :id'),
> {'id': '1'}
> ).fetchall()
>
>
> print 'trimmed:', list(result)
>
>
>
>
> result = e.execute(
> text('select * from nchartable where id = :id'),
> {'id': '1 '} # padded with spaces
> ).fetchall()
>
>
> print 'padded:', list(result)
>
>
> output
> hardcoded: [(u'1 ',)]
> trimmed: []
> padded: [(u'1 ',)]
>
> When value is hardcoded or bound parameter is padded the statement works
> as expected, but it doesn't when value is trimmed. Although the value in
> where statement is just like in hardcoded case.
>
> As explained on cxOracle mailing list
> https://sourceforge.net/p/cx-oracle/mailman/message/36775002/ it's a
> deliberate decision to bind values str to VARCHAR2 and unicode to
> NVARCHAR2. Unfortunately that doesn't work correctly for filtering on CHAR
> and NCHAR.
>
> I know it's more cxOracle matter but how do you deal with this in
> SQLAlchemy? Expecially when working with text SQL statement which may be
> complex e.g. use stored procedures.
>
> Thanks for any clues,
> Michal
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/68f07e23-c432-4bf0-9d55-30c76c237fb8%40googlegroups.com.
