SQLAlchemy: UnicodeEncodeError: 'ascii' codec can't encode characters (db engine encoding ignored?)

Question

I am currently engaged in evaluating SQLAlchemy for a new project. When trying to execute queries containing non-ascii characters an exception is raised.

The SQL statement used for carrying out the evaluation:

SELECT owner, table_name FROM all_tables  WHERE owner LIKE 'äöüßÄÖÜ';

Executing this statement in SQL*Plus, SQL Developer results--as expected--in an empty list.

In order to connect to an Oracle database the following code is being used:

from sqlalchemy import create_engine, MetaData, Table, inspect, select
import pandas as pd
import keyring

dbtype = 'Oracle'
dbenv = 'LOCAL'
dbname = 'MYDB'
dbsys = '%s%s' % (dbtype, dbenv)
dbusr = 'myusr'
dbpwd = keyring.get_password(dbsys, dbusr)
dbhost = 'mydbhost'
dbport = 1521
dbconstr = 'oracle+cx_oracle://%s:%s@%s:%s/%s' % (dbusr, dbpwd, dbhost, dbport, dbname)

To evaluate the database engine encoding:

dbencs = ['UTF8', 'UTF-8', 'utf8', 'utf-8', 'latin1', 'ascii', None]

for dbenc in dbencs:
    if dbenc is None:
        engine = create_engine(dbconstr)
    else:
        engine = create_engine(dbconstr, encoding=dbenc)
    con = engine.connect()

    try:
        df = pd.read_sql_query(u'SELECT owner, table_name FROM all_tables  WHERE owner LIKE \'äöüßÄÖÜ\'', con)
        print('SUCCESS: sql query with db encoding %s succeeded!' % dbenc)
    except Exception as e:
        print('ERROR: sql query with db encoding %s failed (%s)' % (dbenc, e))

    con.close()
    engine.dispose()

Regardless of the encoding specified when creating the db engine, every single attempt to executed the query raises an exception

ERROR: sql query with db encoding UTF8 failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding UTF-8 failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding utf8 failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding utf-8 failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding latin1 failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding ascii failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding None failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))

When connecting to the database directly with cx_Oracle (without SQLAlchemy)

import cx_Oracle
import pandas as pd
import keyring

dbtype = 'Oracle'
dbenv = 'LOCAL'
dbname = 'MYDB'
dbsys = '%s%s' % (dbtype, dbenv)
dbusr = 'myusr'
dbpwd = keyring.get_password(dbsys, dbusr)
dbhost = 'mydbhost'
dbport = 1521
dbconstr = '%s:%s/%s' % (dbhost, dbport, dbname)

dbencs = ['UTF8', 'UTF-8', 'utf8', 'utf-8', 'latin1', 'ascii', None]

for dbenc in dbencs:
    print('=' * 70)
    print('db encoding: %s' % dbenc)
    print('-' * 30)

    if dbenc is None:
        connection = cx_Oracle.connect(dbusr, dbpwd, dbconstr)
    else:
        connection = cx_Oracle.connect(dbusr, dbpwd, dbconstr, encoding=dbenc)
    cursor = connection.cursor()

    try:
        r = cursor.execute("SELECT owner, table_name FROM all_tables  WHERE owner LIKE 'äöüßÄÖÜ'")
        recs = list()
        for owner, table_name in cursor:
            recs.append({'owner': owner, 'table': table_name})
        df = pd.DataFrame(recs)
        print('SUCCESS: sql query with db encoding %s succeeded!' % dbenc)
    except Exception as e:
        print('ERROR: sql query with db encoding %s failed (%s)' % (dbenc, e))

    cursor.close()
    connection.close()

everything works as expected.

SUCCESS: sql query with db encoding UTF8 succeeded!
SUCCESS: sql query with db encoding UTF-8 succeeded!
SUCCESS: sql query with db encoding utf8 succeeded!
SUCCESS: sql query with db encoding utf-8 succeeded!
SUCCESS: sql query with db encoding latin1 succeeded!
ERROR: sql query with db encoding ascii failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))
ERROR: sql query with db encoding None failed ('ascii' codec can't encode characters in position 60-66: ordinal not in range(128))

What do I have to do differently in order to have SQLAlchemy approache rendering the identical results like the cx_Oracle one does?

My environment comprises of

Ubuntu linux 16.04LTS;
Python 3.8;
SQLAlchemy 1.3.16;
cx_Oracle 7.3.0;
psycopg2 2.8.5;
local Oracle 18c Instant client;
remote Oracle 19c database;
local PostgreSQL 9.5 database.

EDIT

######################################################################
Traceback (most recent call last):
  File "/data/projects/Python/database/sqlalchemy/sqlalchemy_oracle.py", line 45, in <module>
    df = pd.read_sql_query(u'SELECT owner, table_name FROM all_tables  WHERE owner LIKE \'äöüßÄÖÜ\'', con)
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/pandas/io/sql.py", line 326, in read_sql_query
    return pandas_sql.read_query(
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/pandas/io/sql.py", line 1218, in read_query
    result = self.execute(*args)
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/pandas/io/sql.py", line 1087, in execute
    return self.connectable.execute(*args, **kwargs)
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1145, in _execute_text
    ret = self._execute_context(
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1287, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1485, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "/opt/pyenv/versions/3.8.2/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 60-66: ordinal not in range(128)
######################################################################

EDIT2

Engine object configuration

convert_unicode = False
cx_oracle_ver = (7, 3, 0)
driver = cx_oracle
encoding = UTF8
nencoding ... attribute not available

user4338 · Accepted Answer · 2020-05-07 05:16:24Z

2

Adding the encoding parameter to the connection string did do the trick.

from sqlalchemy import create_engine, MetaData, Table, inspect, select
import pandas as pd
import keyring

dbtype = 'Oracle'
dbenv = 'LOCAL'
dbname = 'MYDB'
dbsys = '%s%s' % (dbtype, dbenv)
dbusr = 'myusr'
dbpwd = keyring.get_password(dbsys, dbusr)
dbhost = 'mydbhost'
dbport = 1521
dbconstr = 'oracle+cx_oracle://%s:%s@%s:%s/%s?encoding=utf-8&nencoding=utf-8' % (dbusr, dbpwd, dbhost, dbport, dbname)

With the code modified like this, it now renders the results as expected.

answered May 7, 2020 at 5:16

user4338

1733 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

SQLAlchemy: UnicodeEncodeError: 'ascii' codec can't encode characters (db engine encoding ignored?)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related