如何在Flask的SQLAlchemy中为cx_Oracle传入UTF-16编码参数？

I've run into this exact issue before when working with UTF-16 Oracle databases and SQLAlchemy—here's how you can pass those critical encoding and nencoding parameters to cx_Oracle under the hood:

Option 1: Configure via Flask App Settings #

The simplest way is to add SQLALCHEMY_ENGINE_OPTIONS to your Flask app's configuration. This lets SQLAlchemy pass the required arguments directly to cx_Oracle's connect method:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Replace with your actual Oracle connection URI
app.config['SQLALCHEMY_DATABASE_URI'] = 'oracle+cx_oracle://username:password@your_conn_str'

# Add the encoding parameters to engine options
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'connect_args': {'encoding': 'UTF-16', 'nencoding': 'UTF-16'}
}

db = SQLAlchemy(app)

# Rest of your model setup
db.Model.metadata.reflect(db.engine)
class MyTable(db.Model):
    __table__ = db.Model.metadata.tables['mytable']

# Test the fix
for row in MyTable.query:
    print(row.column_with_diacritics)  # Should now display áóé correctly

Option 2: Manually Create the Engine #

If you prefer more control, you can explicitly create the SQLAlchemy engine with the connect_args parameter, then pass it to the SQLAlchemy constructor:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine

app = Flask(__name__)

# Create engine with cx_Oracle encoding params
engine = create_engine(
    'oracle+cx_oracle://username:password@your_conn_str',
    connect_args={'encoding': 'UTF-16', 'nencoding': 'UTF-16'}
)

# Initialize SQLAlchemy with the custom engine
db = SQLAlchemy(app, engine=engine)

# Model setup and testing as before
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)

Why This Works #

When you use connect_args, SQLAlchemy forwards those key-value pairs directly to cx_Oracle.connect()—exactly like you were doing with the standalone cx_Oracle client. This ensures the driver correctly handles the UTF-16 encoding of your database, preserving diacritics and other non-ASCII characters.

Just double-check that your Oracle database is indeed using UTF-16 (verify with SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';) and that you're using a compatible version of cx_Oracle and SQLAlchemy (most recent versions support this parameter passing).

内容的提问来源于stack exchange，提问作者Kapucko