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

[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.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f34b8ab4-37a0-4e4b-8926-63d6c8a039e0%40googlegroups.com.

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

[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'})


On Wed, May 6, 2020, at 8:43 AM, Mike Bayer wrote:
> 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
> 
> 
> On Wed, May 6, 2020, at 8:15 AM, Anno Nühm wrote:
>> Engine object configuration
>>  * convert_unicode = False
>>  * cx_oracle_ver = (7, 3, 0)
>>  * driver = cx_oracle
>>  * encoding = UTF8
>>  * nencoding = 
>> 
>> 

>> --
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/5934e2d1-c55a-49e8-8ea0-96d284c645b2%40googlegroups.com
>>  
>> .
> 

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/05bed84a-afc7-40a4-9c90-62fc1213667b%40www.fastmail.com.

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

[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


On Wed, May 6, 2020, at 8:15 AM, Anno Nühm wrote:
> Engine object configuration
>  * convert_unicode = False
>  * cx_oracle_ver = (7, 3, 0)
>  * driver = cx_oracle
>  * encoding = UTF8
>  * nencoding = 
> 
> 

> --
>  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+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/5934e2d1-c55a-49e8-8ea0-96d284c645b2%40googlegroups.com
>  
> .

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/dc6d95ca-1714-48c9-8b13-1c3097ae2840%40www.fastmail.com.

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

[Anno Nühm]

Engine object configuration

   - convert_unicode = False
   - cx_oracle_ver = (7, 3, 0)
   - driver = cx_oracle
   - encoding = UTF8
   - nencoding = 
   

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5934e2d1-c55a-49e8-8ea0-96d284c645b2%40googlegroups.com.

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

[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

On Wed, May 6, 2020 at 11:46 AM Anno Nühm  wrote:
>
> ##
> Traceback (most recent call last):
>   File "/data/projects/Python/database/sqlalchemy/sqlalchemy_oracle.py", line 
> 45, in 
> 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)
> ##
>
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/38aa0bb8-93f3-4bc7-a771-19a84a17670d%40googlegroups.com.

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeTz2vmLzusfPWXeL9PhSrMnQPhbKY0xY9d1oUfPWkB0w%40mail.gmail.com.

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

[Anno Nühm]

##
Traceback (most recent call last):
  File "/data/projects/Python/database/sqlalchemy/sqlalchemy_oracle.py", 
line 45, in 
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)
##



-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/38aa0bb8-93f3-4bc7-a771-19a84a17670d%40googlegroups.com.

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

[Anno Nühm]

##
Traceback (most recent call last):
  File "/data/projects/Python/database/sqlalchemy/sqlalchemy_oracle.py", 
line 45, in 
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)
##




On Wednesday, 6 May 2020 10:01:22 UTC+2, Anno Nühm wrote:
>
> 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 
>