Pass encoding parameter to cx_oracle from sqlalchemy

Question

I'm using Oracle Database with UTF-16 encoding. The diacritics is correctly displayed when using directly cx_oracle client. Connection statement is like this:

cx_Oracle.connect(username, password, conn_str, encoding='UTF-16', nencoding='UTF-16')

However, now I'm building bigger application and I would like to use SQLalchemy in Flask.

Code looks like this:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(app)
db.Model.metadata.reflect(db.engine)

class MyTable(db.Model):
    __table__ = db.Model.metadata.tables['mytable']

for row in MyTable.query:
    print(row.column_with_diacritics)

Output of the code above: aoe But the column value in the database is: áóé

So my question is, how can pass arguments encoding='UTF-16', nencoding='UTF-16' to cx_oracle which sqlalchemy uses underhood?

Thank you for any advice or other workaround.

Answer 1

SQLAlchemy create_engine allows you to pass additional arguments to the underlying cx_Oracle's connect() via the connect_args argument to create_engine:

import cx_Oracle
e = create_engine(
    "oracle+cx_oracle://user:pass@connstr...",
    connect_args={
        "encoding": "UTF-16",
        "nencoding": "UTF-16"
    }
)

Setting NLS_LANG actually did not work for me, but I think that's because I'm in Cygwin and have some other quirks (such as I actually want to set encoding dynamically, and I'd have to reload cx_Oracle even if I did manage to get NLS_LANG to work). Using this method worked for me.

Source: (SQLAlchemy Oracle dialect source code): https://github.com/zzzeek/sqlalchemy/blob/560452acd292c8a9a57db032378a6342f16448c6/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L37

Answer 2

I have found out that it's possible to pass this setting via environment variable NLS_LANG.

So, in my use case, I had to set: NLS_LANG=.AL32UTF8 (worked instead of UTF16).

Reference: cx_oracle Documentation