On Mon, Jan 15, 2018 at 3:18 PM, Stefan Schwarzer
<[email protected]> wrote:
> On 2018-01-12 16:33, Mike Bayer wrote:> On Fri, Jan 12, 2018 at 7:14 AM,
> Stefan Schwarzer
>> <[email protected]> wrote:
>> In SQLAlchemy 1.1 series and earlier, you can specify
>> exclude_setinputsizes=() to have STRING be part of the automatic
>> setinputsizes call. In SQLAlchemy 1.2 these features were all removed
>> as there was never any reason to pass most datatypes to setinputsizes.
>> in 1.2 you can still say
>> engine.dialect._include_setinputsizes.add(cx_Oracle.STRING) to re-add
>> it, but this should not be necessary (my test works with or without
>> it).
>>
>> Also which version of Python you're using matters, however I get a
>> round trip under both pythons.
>>
>> To round trip it, do this - note I had to expand the size of the
>> VARCHAR to fit your string, it was giving me a "data too large" error
>> before, so that might be a clue:
>> [...]
>
> I tried your example and got the same mismatch between
> original and actual value as before. Also your workaround
> that I tried for SQLAlchemy 1.2 didn't work for me. I
> haven't tried the workaround for SQLAlchemy 1.1.15. As
> described below, I (partially) found out why the workaround
> for version 1.2 didn't work.
>
> Since your code worked for you, but not for me, I concluded
> that probably the setup of your database is different from
> "mine" (I don't control the server). I searched for
> information on encodings in Oracle and found this document:
>
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-AA09A60E-123E-457C-ACE1-89E4634E492C
>
> So far I had assumed that if I use `NVARCHAR2`, the column
> data would be "unicode" and the Oracle driver would do all
> the necessary conversions.
>
> However, with `SELECT * FROM V$NLS_PARAMETERS` I got
>
> NLS_CHARACTERSET WE8MSWIN1252
> NLS_NCHAR_CHARACTERSET AL16UTF16
>
> What are the values for your database?
>
> As far as I understand the above Oracle document, the first
> encoding, `NLS_CHARACTERSET`, is used for the names of SQL
> entity names like table and column names, and also for
> columns defined with `VARCHAR2`, `CLOB` etc. (character data
> types without the `N` prefix). The second encoding,
> `NLS_NCHAR_CHARACTERSET` is used for `NVARCHAR2`, `NCLOB`
> etc. (character data types with the `N` prefix).
>
> According to the document, Oracle nowadays recommends
> `AL32UTF8` for the database encoding and not using
> `NVARCHAR2` etc. for columns because `AL32UTF8` for
> `VARCHAR2` can generally encode unicode.
>
> I assume that I won't be able to have the above database
> encodings changed. In this case, what do you recommend when
> using the database with SQLAlchemy? As far as I understand,
> if I use `VARCHAR` or `VARCHAR2` (as other tables in the
> database so far), I won't be able to store anything that
> doesn't belong in CP1252. If I use `NVARCHAR2`, I _should_
> be able to use Chinese characters.
>
> Why not? I changed your suggested code for SQLAlchemy 1.2
> to include
>
> e.dialect._include_setinputsizes.add(cx_Oracle.STRING)
> e.dialect._include_setinputsizes.add(cx_Oracle.NCHAR)
> e.dialect._include_setinputsizes.add(cx_Oracle.NCLOB)
>
> but still inserting the Chinese characters failed.
>
> With some debugging, I noticed that in
> `engine.default.DefaultExecutionContext.set_input_sizes`
> before the call to `cursor.setinputsizes`, the `text` column
> has a corresponding `cx_Oracle.STRING` in the `inputsizes`
> dictionary. However, the type of `text` (an `NVARCHAR2`
> column) should be `cx_Oracle.NCHAR`. This is also what I get
> in the cursor description after the `SELECT` invocation
> before the `cursor.setinputsizes` in my working pure-cx_Oracle
> example in my original posting. If I change `cx_Oracle.STRING`
> to `cx_Oracle.NCHAR` in
> `DefaultExecutionContext.set_input_sizes` in the debugger,
> the Chinese characters appear in the database as expected.
>
> See also
> https://github.com/oracle/python-cx_Oracle/issues/119
>
> If you don't have a suspicion why the wrong type for
> the `NVARCHAR2` column is used, I could try to do more
> debugging and hopefully find out why.
the issue you refer to with Anthony refers to a new use case for the
cx_Oracle DBAPI, where we use setinputsizes() again and use a
different datatype for strings. So that would need to be added to
SQLAlchemy's cx_Oracle dialect as an option, which can either be on a
per-datatype basis or engine-wide (engine-wide is easier to implement
and use however I'm not sure which is more appropriate).
You can probably get this to work right now doing this:
from sqlalchemy.dialects.oracle import _OracleString
class NCHAR(_OracleNVarChar):
def get_dbapi_type(self, dbapi):
return cx_Oracle.NCHAR
and then add cx_Oracle.NCHAR to the dialect._include_setinputsizes list.
I've proposed a few ways we might be able to add this API here:
https://bitbucket.org/zzzeek/sqlalchemy/issues/4163/cx_oracle-requires-method-to-force-use-of
>
> What do you think?
>
> Best regards,
> Stefan
>
> --
> 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 post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
