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

[Anno Nühm]

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.
  

[Simon King]

It might help to display the stack trace when the encoding fails, so
we can see exactly where the error is coming from.

Simon

> --
> 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 sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/db3bc380-3591-444c-a391-9494a5f0fa94%40googlegroups.com.

[Anno Nühm]

######################################################################
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)
######################################################################

[Simon King]

What are the values of "encoding" and "nencoding" on the connection object?

https://github.com/oracle/python-cx_Oracle/issues/36
https://stackoverflow.com/a/37600367/395053

You probably need to grab the raw dbapi connection:

https://docs.sqlalchemy.org/en/13/core/connections.html#working-with-raw-dbapi-connections

> --
> 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 sqlalchemy+...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/38aa0bb8-93f3-4bc7-a771-19a84a17670d%40googlegroups.com.

[Anno Nühm]

Engine object configuration

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

[Mike Bayer]

I see you are using an "encoding" on cx_Oracle connect(), which SQLAlchemy does not use; this parameter appears to be added to cx_Oracle only recently.

The standard way to set Oracle encodings is via the NLS_LANG environment variable, please use this parameter when dealing with Oracle client libraries and unicode.

https://www.oracle.com/database/technologies/faq-nls-lang.html

--

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5934e2d1-c55a-49e8-8ea0-96d284c645b2%40googlegroups.com.

[Mike Bayer]

Alternatively, this will probably work as well, SQLAlchemy will pass it through to the client:

create_engine("oracle+cx_oracle://user:pass@dsn/?encoding=utf-8")

will update the docs now.

>>> e = create_engine("oracle+cx_oracle://scott:tiger@oracle1120/?encoding=utf-8")

>>> e.dialect.create_connect_args(e.url)

([], {'encoding': 'utf-8', 'dsn': 'oracle1120', 'password': 'tiger', 'user': 'scott'})

[Anno Nühm]

Adding the encoding parameter to the connection string did do the trick. With this now my test code is running perfectly fine, rendering the results as expected.