hey there -
you should apply typing behavior which should be safe to apply to any CHAR like
this:
class PaddedChar(TypeDecorator):
impl = NCHAR
def process_bind_param(self, value, dialect):
if value is not None:
value = value + (" " * (self.impl.length - len(value)))
return value
result = conn.execute(
text("select * from nchartable where id = :id").bindparams(
bindparam("id", type_=PaddedChar(4))
),
{"id": "1"},
).fetchall()
On Thu, Oct 3, 2019, at 4:47 AM, 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/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/475bbd60-f3d8-486b-a640-5fd58d679af6%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/c313d214-d5a2-455c-b661-3a7cdfb379f2%40www.fastmail.com.
