A bit of advocacy from my side on cx_Oracle:
https://github.com/oracle/python-cx_Oracle/issues/365
Mike, there's something you might want to look at. We have this monkey
patch on SQLAlchemy in our recent code, which was necessary to get the
correct behavior for NCHAR columns.
from sqlalchemy.dialects.oracle import cx_oracle
from sqlalchemy.sql sqltypes
class _OracleNChar(sqltypes.NCHAR):
def get_dbapi_type(self, dbapi):
return dbapi.FIXED_NCHAR
cx_oracle._OracleNChar = _OracleNChar
cx_oracle.dialect.colspecs[sqltypes.NCHAR] = _OracleNChar
On Thursday, October 3, 2019 at 12:26:40 PM UTC-4, Mike Bayer wrote:
>
> 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 sqlal...@googlegroups.com <javascript:>.
> 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 sqlal...@googlegroups.com <javascript:>.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/94e3b8da-415e-434d-bf6a-dbd1a671e576%40googlegroups.com.
