With cx_Oracle, you really should likely be using typing for everything as
cx_Oracle is pretty sensitive as well to the cursor.setinputsizes() settings,
which SQLAlchemy will do for you if you send in typed bound parameters;
additionally, you can set typing information for result columns also (use
text().columns()) which SQLAlchemy uses in order to set up
cursor.outputtypehandler. Both of these are cx_Oracle things that are
unfortunately unique to this DBAPI and they are hugely important; even the
django ORM has to use outputtypehandler.
On Thu, Oct 3, 2019, at 12:20 PM, Mike Bayer wrote:
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/c313d214-d5a2-455c-b661-3a7cdfb379f2%40www.fastmail.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/ee7a5b01-c467-4523-9725-f33922c50ba2%40www.fastmail.com.
