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.

cowbert · Accepted Answer · 2018-12-12 07:43:15Z

6

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

answered Dec 12, 2018 at 7:43

cowbert

3,4833 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kapucko · Accepted Answer · 2018-01-31 10:43:56Z

1

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

answered Jan 31, 2018 at 10:43

Kapucko

3433 silver badges12 bronze badges

1 Comment

devaerial Over a year ago

link is no longer working

Collectives™ on Stack Overflow

Pass encoding parameter to cx_oracle from sqlalchemy

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related