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] Advice for handling QueuePool limit exceptions (3o7r)

[Mike Bayer]

On Wed, May 6, 2020, at 11:24 AM, Colton Allen wrote:
> Specifically: https://docs.sqlalchemy.org/en/13/errors.html#error-3o7r
> 
> I think I've got a good handle on what the problem is. I just don't have the 
> experience to know how to solve it effectively and with confidence.
> 
> Just some of my application's stats:
> 
> - Postgres database hosted with Amazon RDS (currently a t3.small).
> - Default QueuePool limits: 5 + 10 overflow.
> - Using flask + celery + gevent + concurrency of 1,000.
> 
> I only get this error when I'm saturating my celery queue. So I'm thinking I 
> can either reduce the concurrency or just add more connections. I'm hesitant 
> to reduce concurrency because the goal is to have even more throughput. 

1000 is very common but in my experience it is very high. having a pool of 
worker processes with concurrency of 100-200 per process is more realistic.

also you have to consider that if you are trying to get throughput by having 
1000 jobs all present at once and all waiting for results, then your database 
needs to do that too. so yes, you have to allow that many connections at the 
same time and also tune your database to be ready for that too.


> 
> 
> 
>  I could add more database connections (I think I have ~170 available in 
> total) but I'm unsure of the implications that action has (my app is the only 
> app that uses the database).

if 170 is your total available then I don't see how you can have 1000 greenlets 
at once unless they only occasionally use the database. also, to get the error 
you are seeing, you not only have to request more connections than are actually 
available, but your application also has to **time out** for 30 seconds by 
default, which indicates you have greenlets that are waiting for 30 seconds. 

the "1000 greenlet" number is pretty familiar to me in the Openstack world but 
my experience has been that a value this high tends to be extremely optimistic. 
It's likely better to start with small settings for everything, then do load 
testing on the outside , and increase settings to get the desired throughput 
via load testing. if numbers like number of greenlets are set too high, then 
you have jobs or requests starting up, being placed into the system, and then 
just waiting for unavailable resources. 

I don't know if Celery also supports a multi-process model, if you have 
multiple processes, you are better off with new jobs / requesets being rejected 
by a process that is already handling a few hundred greenlets and instead being 
picked up by another process. I am more familiar with this calculus from the 
web request side of things and not as much with how Celery handles this.


> 
> If in the future I decide to add more concurrency is there some sort of 
> equation I should follow. Like "database connections * n = concurrency limit".

this depends highly on what your appliciation does as well as what tolerance 
you have for jobs / requests to be waiting. if every job uses a single database 
connection from start to finish, then you need to be able to have a database 
connection open for every job, or at least enough so that jobs that are waiting 
for a connection don't wait longer than the configured timeout. or you could 
have a small number of connections and a huge timeout so that jobs just wait a 
long time. I tend to think about this visually in terms of things waiting to 
access some resource that is in scarce supply.


> 
> How does sqlalchemy decide to create a new connection(I'm using 
> *scoped_session* scoped to flask's *_app_ctx_stack.__ident_func__*)? Does it 
> happen every time *scoped_session()* is called?

scoped_session() is a constructor that creates a new Session() object, or if 
one is already associated with the current "scope" (like a greenlet or thread), 
it returns that. Within the Session() no database connection is procured when 
it is first constructed. Once the Session is asked to do a database query, it 
requests a connection from the engine. Assuming the session is configured in 
its default mode of non-autocommit, it holds onto this connection until the 
rollback(), commit(), or close() method is called.


> 
> Let's say I want to scale up to a million concurrent users (as unlikely as 
> that is) surely I can't have 100k active database connections on a single 
> database. 

probably not

> 
> 
> I'd have to divide them among a couple slave databases. So then how does the 
> master database handle the write load? Would I start putting my write 
> operations into a queue and batching them?

you are here on the path to seeking higher tps. This is the whole game really, 
and like anything else there is a whole world of practices and techniques to 
work this out. in MySQL for example we have a cluster called Galera which does 
multimaster clustering. But it still serializes transaction commits across the 
cluster, so in that regard it's maybe more of an HA solution rather than a 
performance boost. I think the 

Re: [sqlalchemy] Adding extra constraints to monkey-patched model classes

[Alejandro Avilés]

Great, thank you. Managed to make it work!

On Wednesday, May 6, 2020 at 2:35:12 PM UTC+2, Mike Bayer wrote:
>
>
>
> On Wed, May 6, 2020, at 7:19 AM, Alejandro Avilés wrote:
>
> I'm working on a project where I need to add additional columns to tables 
> defined in a different package. This is done via monkeypatching the model 
> classes. I need now to also add additional constraints, but I'm getting no 
> luck overriding `__table_args__`.
>
> I'm a bit lost as to how to continue investigating, so my two questions 
> are:
> - Is SA taking into account `__table_args__` only once per model class?
>
>
> yes, this is based on a metaclass at class creation time
>
>
> - Is there any way to define new constraints outside the model class?
>
>
> yes you should work against the table directly:
>
> mymodel.__table__.append_constraint(Index(...))
>
>
>
>
>
> Any suggestion/lead on how to do this differently will also be appreciated.
> Thanks in advance!
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/2203e8d5-62e3-4a80-b453-489f4492d022%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/d460609d-b4db-41f8-93bc-9c1705230a43%40googlegroups.com.

[sqlalchemy] Advice for handling QueuePool limit exceptions (3o7r)

[Colton Allen]

Specifically: https://docs.sqlalchemy.org/en/13/errors.html#error-3o7r

I think I've got a good handle on what the problem is.  I just don't have 
the experience to know how to solve it effectively and with confidence.

Just some of my application's stats:

- Postgres database hosted with Amazon RDS (currently a t3.small).
- Default QueuePool limits: 5 + 10 overflow.
- Using flask + celery + gevent + concurrency of 1,000.

I only get this error when I'm saturating my celery queue.  So I'm thinking 
I can either reduce the concurrency or just add more connections.  I'm 
hesitant to reduce concurrency because the goal is to have even more 
throughput.  I could add more database connections (I think I have ~170 
available in total) but I'm unsure of the implications that action has (my 
app is the only app that uses the database).

If in the future I decide to add more concurrency is there some sort of 
equation I should follow.  Like "database connections * n = concurrency 
limit".

How does sqlalchemy decide to create a new connection(I'm using 
*scoped_session* scoped to flask's *_app_ctx_stack.__ident_func__*)?  Does 
it happen every time *scoped_session()* is called?

Let's say I want to scale up to a million concurrent users (as unlikely as 
that is) surely I can't have 100k active database connections on a single 
database.  I'd have to divide them among a couple slave databases.  So then 
how does the master database handle the write load?  Would I start putting 
my write operations into a queue and batching them?

Sorry for the brain dump but I'm very lost!

-- 
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/2ec7fcb5-1ad2-432b-a11f-ae6b5e89ee74%40googlegroups.com.

Re: [sqlalchemy] sqlalchemy list connections inside connection pool

[Mike Bayer]

the connection pool reuses connections, so when you "close" a connection, often 
it is recycled so that it is returned to its pooled store rather than being 
fully closed.

Some background on this is at 
https://docs.sqlalchemy.org/en/13/glossary.html#term-released



On Wed, May 6, 2020, at 8:45 AM, Oğuzhan Kaya wrote:
> what is mean that checkedout checkedin connection and why When connection is 
> closed connection in pool number increases?
> 
> 5 Mayıs 2020 Salı 19:50:22 UTC+3 tarihinde Mike Bayer yazdı:
>> engine.pool has some metrics on it:
>> 
>> >>> from sqlalchemy import create_engine
>> >>> e = create_engine("mysql://scott:tiger@localhost/test")
>> >>> e.pool.status()
>> 'Pool size: 5 Connections in pool: 0 Current Overflow: -5 Current Checked 
>> out connections: 0'
>> >>> e.pool.checkedout()
>> 0
>> >>> c1 = e.connect()
>> >>> e.pool.checkedout()
>> 1
>> >>> e.pool.status()
>> 'Pool size: 5 Connections in pool: 0 Current Overflow: -4 Current Checked 
>> out connections: 1'
>> >>> c2 = e.connect()
>> >>> e.pool.status()
>> 'Pool size: 5 Connections in pool: 0 Current Overflow: -3 Current Checked 
>> out connections: 2'
>> >>> c1.close()
>> >>> e.pool.status()
>> 'Pool size: 5 Connections in pool: 1 Current Overflow: -3 Current Checked 
>> out connections: 1'
>> >>> c2.close()
>> >>> e.pool.status()
>> 'Pool size: 5 Connections in pool: 2 Current Overflow: -3 Current Checked 
>> out connections: 0'
>> 
>> 
>> 
>> 
>> On Tue, May 5, 2020, at 11:14 AM, Oğuzhan Kaya wrote:
>>> I made a connection with sqlalchemy to mssql and I used queue pool. I am 
>>> trying to understand this connetion pool is worked or not. and also I want 
>>> to see the connections and the queue inside connection pool how can I see?
>>> 
>>> engine = sal.create_engine('IP',pool_size=1,max_overflow=0)
>>> result = engine.execute('select * from mytable')
>>> result.close()
>>> 

>>> --
>>> 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 sqlal...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/d565c8a9-0d34-46cb-971a-b08ecc1fc881%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/78d2ce76-b6f1-4cc5-93d6-96939c4c7728%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/da67aab7-b83c-4b6e-aa0c-9378bec1f5c0%40www.fastmail.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] sqlalchemy list connections inside connection pool

[Oğuzhan Kaya]

what is mean that checkedout checkedin connection and why When connection 
is closed connection in pool number increases?

5 Mayıs 2020 Salı 19:50:22 UTC+3 tarihinde Mike Bayer yazdı:
>
> engine.pool has some metrics on it:
>
> >>> from sqlalchemy import create_engine
> >>> e = create_engine("mysql://scott:tiger@localhost/test")
> >>> e.pool.status()
> 'Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked 
> out connections: 0'
> >>> e.pool.checkedout()
> 0
> >>> c1 = e.connect()
> >>> e.pool.checkedout()
> 1
> >>> e.pool.status()
> 'Pool size: 5  Connections in pool: 0 Current Overflow: -4 Current Checked 
> out connections: 1'
> >>> c2 = e.connect()
> >>> e.pool.status()
> 'Pool size: 5  Connections in pool: 0 Current Overflow: -3 Current Checked 
> out connections: 2'
> >>> c1.close()
> >>> e.pool.status()
> 'Pool size: 5  Connections in pool: 1 Current Overflow: -3 Current Checked 
> out connections: 1'
> >>> c2.close()
> >>> e.pool.status()
> 'Pool size: 5  Connections in pool: 2 Current Overflow: -3 Current Checked 
> out connections: 0'
>
>
>
>
> On Tue, May 5, 2020, at 11:14 AM, Oğuzhan Kaya wrote:
>
> I made a connection with sqlalchemy to mssql and I used queue pool. I am 
> trying to understand this connetion pool is worked or not. and also I want 
> to see the connections and the queue inside connection pool how can I see?
>
> engine = sal.create_engine('IP',pool_size=1,max_overflow=0)
> result = engine.execute('select * from mytable')
> result.close()
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d565c8a9-0d34-46cb-971a-b08ecc1fc881%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/78d2ce76-b6f1-4cc5-93d6-96939c4c7728%40googlegroups.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] Adding extra constraints to monkey-patched model classes

[Mike Bayer]

On Wed, May 6, 2020, at 7:19 AM, Alejandro Avilés wrote:
> I'm working on a project where I need to add additional columns to tables 
> defined in a different package. This is done via monkeypatching the model 
> classes. I need now to also add additional constraints, but I'm getting no 
> luck overriding `__table_args__`.
> 
> I'm a bit lost as to how to continue investigating, so my two questions are:
> - Is SA taking into account `__table_args__` only once per model class?

yes, this is based on a metaclass at class creation time


> - Is there any way to define new constraints outside the model class?

yes you should work against the table directly:

mymodel.__table__.append_constraint(Index(...))




> 
> Any suggestion/lead on how to do this differently will also be appreciated.
> Thanks in advance!
> 

> --
>  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/2203e8d5-62e3-4a80-b453-489f4492d022%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/6ce4b689-6851-4fb3-875c-677502301927%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.

[sqlalchemy] Adding extra constraints to monkey-patched model classes

[Alejandro Avilés]

I'm working on a project where I need to add additional columns to tables 
defined in a different package. This is done via monkeypatching the model 
classes. I need now to also add additional constraints, but I'm getting no 
luck overriding `__table_args__`.

I'm a bit lost as to how to continue investigating, so my two questions are:
- Is SA taking into account `__table_args__` only once per model class?
- Is there any way to define new constraints outside the model class?

Any suggestion/lead on how to do this differently will also be appreciated.
Thanks in advance!

-- 
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/2203e8d5-62e3-4a80-b453-489f4492d022%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 
> 

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

[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

On Wed, May 6, 2020 at 9:01 AM 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 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 

Re: [sqlalchemy] generate top-level python code for an alembic revision from render_item

[Adrian]

 Works great, thanks!

-- 
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/8211829c-bf2c-4675-b9e7-7d728fad7c47%40googlegroups.com.

[sqlalchemy] 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