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 sqlalchemy+unsubscr...@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > 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/ee7a5b01-c467-4523-9725-f33922c50ba2%40www.fastmail.com.