It might help to display the stack trace when the encoding fails, so
we can see exactly where the error is coming from.
Simon
On Wed, May 6, 2020 at 9:01 AM Anno Nühm <anno.me.nu...@gmail.com> 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 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.
>
>
> --
> 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/db3bc380-3591-444c-a391-9494a5f0fa94%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/CAFHwexdgJcxE2ht7tYQf5%2BSZrANWmUEirgX-C_zxVD%2BJ7-LcEg%40mail.gmail.com.
