Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Mike Bayer]

On Mon, Nov 16, 2020, at 2:58 PM, Terrence-Monroe: Brannon wrote:
> 
> 
> 
> 
> 
> On Mon, Nov 16, 2020 at 2:45 PM Mike Bayer  wrote:
>> __
>> 
>> 
>> On Mon, Nov 16, 2020, at 1:32 PM, Terrence-Monroe: Brannon wrote:
>>> 
>>> 
>>> On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer  wrote:
 __
 schema name for tables is set using the .schema parameter of the Table 
 object.
>>> 
>>> yes, Table() is a core concept. I'm more interested in the ORM way of 
>>> achieving this.
>> 
>> use __table_args__ as mentioned in the above linked documentation
>> 
>> class MyClass(...):
>> __tablename__ = 'my_table'
>> 
>> # ...
>> 
>>  __table_args__ = {"schema": "my_schema"}
> 
> From my reading of this thread on Postgres 
> ,
>  `my_schema` is not the name of the schema but a particular table mapping and 
> the actual name of the schema is listed in the mapping. E.g.
> 
> `session = Session()
session.connection(execution_options={
"schema_translate_map": {"my_schema": "actual_schema_name"}})`

we are not referring to the "schema_translate_map" feature.  we're referring to 
"schema_name" which operates independently of the schema_translate_map feature.

I have some additional docs in review to reduce the ambiguity here.



> ``
> 
>  
>> 
>> 
>>> Do you think this question qualifies as a FAQ or perhaps update of the 
>>> docs? There are plenty of StackOverflow threads where people are asking a 
>>> similar question.
>> 
>> 
>> show me a sampling of the question being asked as I see people successfully 
>> using "schema" with __table_args__ all the time, so i need to know what it 
>> is people are not understanding.
> 
> * 
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
> * 
> https://stackoverflow.com/questions/46373236/specify-oracle-schema-for-sqlalchemy-engine
> * (this one is about Postgres and you answered here already) 
> https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas

these seem to be mostly confusion over Oracle's specific difficulties with a 
"default" schema so this is a different question to be answered.It is 
cleaner to have the database connection set up so that the "default" schema is 
the one that's desired, so to the extent that these users are not able to 
configure their login as they'd want (which is definitely the easiest way to do 
this),  the approach to emit "`ALTER SESSION SET CURRENT_SCHEMA = myschema" is 
likely a good one and you might want to use that instead.   The way this should 
be performed is using the connect() event handler at 
`https://docs.sqlalchemy.org/en/14/core/events.html?highlight=poolevents#sqlalchemy.events.PoolEvents.connect:

from sqlalchemy import event
from sqlalchemy import create_engine

engine = create_engine("oracle://...")

@event.listens_for(engine, 'connect')
def receive_connect(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = myschema")
cursor.close()

This would allow you to set the schema up front without having to change the 
rest of your code.




``




> 
>  
>> 
>> 
>> a brief titled section at 
>> https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#declarative-table-configuration
>>  as well as the corresponding 1.3 section that is explicitly for the 
>> "schema" argument as it is the most common is likely all that's needed.
>> 
>> 
>> 
>> 
>>> 
>>> 
 
 
 
 
 On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
> I've been using the SA ORM for 3 months simply declaring tables as you 
> see here:
> 
> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
> 
> And then connecting to Oracle via oracle://user:pass@server
> with no problem.
> 
> However, today, I was told that my auto-generated queries were failing 
> because they were not qualified with the schema name. I.e, even though
> 
> SELECT some_column FROM some_table 
> 
> was working,  I am now being asked to make the query
> 
>SELECT some_volumn FROM SCHEMA."some_table"
> 
> and even though there are docs about specifying the schema for the 
> SQLAlchemy core, I dont see similar docs for the ORM.
> 
> I found an answer:
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
> 
> but I cannot believe that I need to manually issue this sort of query for 
> this:
> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") 
> 
> 
> 

> -- 
> 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 h

Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Terrence-Monroe: Brannon]

On Mon, Nov 16, 2020 at 2:45 PM Mike Bayer  wrote:

>
>
> On Mon, Nov 16, 2020, at 1:32 PM, Terrence-Monroe: Brannon wrote:
>
>
>
> On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer 
> wrote:
>
>
> schema name for tables is set using the .schema parameter of the Table
> object.
>
>
> yes, Table() is a core concept. I'm more interested in the ORM way of
> achieving this.
>
>
> use __table_args__ as mentioned in the above linked documentation
>
> class MyClass(...):
> __tablename__ = 'my_table'
>
> # ...
>
>  __table_args__ = {"schema": "my_schema"}
>

>From my reading of this thread on Postgres
,
`my_schema` is not the name of the schema but a particular table mapping
and the actual name of the schema is listed in the mapping. E.g.

session = Session()
session.connection(execution_options={
"schema_translate_map": {"my_schema": "actual_schema_name"}})




>
> Do you think this question qualifies as a FAQ or perhaps update of the
> docs? There are plenty of StackOverflow threads where people are asking a
> similar question.
>
>
>
> show me a sampling of the question being asked as I see people
> successfully using "schema" with __table_args__ all the time, so i need to
> know what it is people are not understanding.
>

*
https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
*
https://stackoverflow.com/questions/46373236/specify-oracle-schema-for-sqlalchemy-engine
* (this one is about Postgres and you answered here already)
https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas



>
> a brief titled section at
> https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#declarative-table-configuration
> as well as the corresponding 1.3 section that is explicitly for the
> "schema" argument as it is the most common is likely all that's needed.
>
>
>
>
>
>
>
>
>
>
> On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
>
> I've been using the SA ORM for 3 months simply declaring tables as you see
> here:
>
> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
>
> And then connecting to Oracle via oracle://user:pass@server
> with no problem.
>
> However, today, I was told that my auto-generated queries were failing
> because they were not qualified with the schema name. I.e, even though
>
> SELECT some_column FROM some_table
>
> was working,  I am now being asked to make the query
>
>SELECT some_volumn FROM SCHEMA."some_table"
>
> and even though there are docs about specifying the schema for the
> SQLAlchemy core, I dont see similar docs for the ORM.
>
> I found an answer:
>
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
>
> but I cannot believe that I need to manually issue this sort of query for
> this:
> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}")
>
>
>
> --
> 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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/uYlXQZBBT0g/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.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 becaus

Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Mike Bayer]

On Mon, Nov 16, 2020, at 1:32 PM, Terrence-Monroe: Brannon wrote:
> 
> 
> On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer  wrote:
>> __
>> schema name for tables is set using the .schema parameter of the Table 
>> object.
> 
> yes, Table() is a core concept. I'm more interested in the ORM way of 
> achieving this.

use __table_args__ as mentioned in the above linked documentation

class MyClass(...):
__tablename__ = 'my_table'

# ...

 __table_args__ = {"schema": "my_schema"}

> Do you think this question qualifies as a FAQ or perhaps update of the docs? 
> There are plenty of StackOverflow threads where people are asking a similar 
> question.


show me a sampling of the question being asked as I see people successfully 
using "schema" with __table_args__ all the time, so i need to know what it is 
people are not understanding.

a brief titled section at 
https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#declarative-table-configuration
 as well as the corresponding 1.3 section that is explicitly for the "schema" 
argument as it is the most common is likely all that's needed.




> 
> 
>> 
>> 
>> 
>> 
>> On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
>>> I've been using the SA ORM for 3 months simply declaring tables as you see 
>>> here:
>>> 
>>> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
>>> 
>>> And then connecting to Oracle via oracle://user:pass@server
>>> with no problem.
>>> 
>>> However, today, I was told that my auto-generated queries were failing 
>>> because they were not qualified with the schema name. I.e, even though
>>> 
>>> SELECT some_column FROM some_table 
>>> 
>>> was working,  I am now being asked to make the query
>>> 
>>>SELECT some_volumn FROM SCHEMA."some_table"
>>> 
>>> and even though there are docs about specifying the schema for the 
>>> SQLAlchemy core, I dont see similar docs for the ORM.
>>> 
>>> I found an answer:
>>> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
>>> 
>>> but I cannot believe that I need to manually issue this sort of query for 
>>> this:
>>> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") 
>>> 
>>> 
>>> 

>>> -- 
>>> 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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%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 a topic in the 
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/sqlalchemy/uYlXQZBBT0g/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.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/CACv_mNSz3rH_rUW5NP5e-LO-SjFKLKPZAgDnxQWsOO%2BDU4OKRA%40mail.gmail.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide a

Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Terrence-Monroe: Brannon]

On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer  wrote:

> schema name for tables is set using the .schema parameter of the Table
> object.
>

yes, Table() is a core concept. I'm more interested in the ORM way of
achieving this.



>
> The docs don't state this at once but you are essentially combining the
> information contained at
> https://docs.sqlalchemy.org/en/13/core/metadata.html#specifying-the-schema-name
>

financial_info = Table('financial_info', meta,

Column('id', Integer, primary_key=True),
Column('value', String(100), nullable=False),
schema='remote_banks'

)

this an example of doing this in core



> with
> https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html
> .
>

Regarding some code I read there I have 2 issues:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()Base.metadata.reflect(some_engine)
class User(Base):
__table__ = metadata.tables['user']
class Address(Base):

__table__ = metadata.tables['address']

1. why is the code referring to `metadata` when the only thing accessible
is `Base.metadata`
2. where, on this page, would one modify an example so that in one place
you specify the schema for all of your subsequent sql queries? And can you
provide a concrete example of the exact modification?


> you can also set the default .schema for all Table objects at once using
> the
> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.params.schema
> parameter.
>

But with the ORM, all we do is use __tablename__ and __table__args__  ...
is there something about __table_args__ that we change?

Do you think this question qualifies as a FAQ or perhaps update of the
docs? There are plenty of StackOverflow threads where people are asking a
similar question.


>
>
> On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
>
> I've been using the SA ORM for 3 months simply declaring tables as you see
> here:
>
> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
>
> And then connecting to Oracle via oracle://user:pass@server
> with no problem.
>
> However, today, I was told that my auto-generated queries were failing
> because they were not qualified with the schema name. I.e, even though
>
> SELECT some_column FROM some_table
>
> was working,  I am now being asked to make the query
>
>SELECT some_volumn FROM SCHEMA."some_table"
>
> and even though there are docs about specifying the schema for the
> SQLAlchemy core, I dont see similar docs for the ORM.
>
> I found an answer:
>
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
>
> but I cannot believe that I need to manually issue this sort of query for
> this:
> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}")
>
>
>
> --
> 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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/uYlXQZBBT0g/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.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...@googlegro

Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Mike Bayer]

On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
> I've been using the SA ORM for 3 months simply declaring tables as you see 
> here:
> 
> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
> 
> And then connecting to Oracle via oracle://user:pass@server
> with no problem.
> 
> However, today, I was told that my auto-generated queries were failing 
> because they were not qualified with the schema name. I.e, even though
> 
> SELECT some_column FROM some_table 
> 
> was working,  I am now being asked to make the query
> 
>SELECT some_volumn FROM SCHEMA."some_table"
> 
> and even though there are docs about specifying the schema for the SQLAlchemy 
> core, I dont see similar docs for the ORM.

Also, that sounds suspicious, if your queries are "working", have you been 
querying from the wrong set of tables ?   There is a default "owner" that's set 
up when you connect to Oracle and that's what that name signifies.  if they are 
moving the "owner" or something then you'd log in as that other owner most 
ideally.I'm otherwise not sure why "someone" is telling you your working 
queries need to change.   otherwise I have seen the approach where DBAs make 
synonyms to all the tables/views, but specifying the owner explicitly works as 
well.




> 
> I found an answer:
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
> 
> but I cannot believe that I need to manually issue this sort of query for 
> this:
> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") 
> 
> 
> 

> -- 
> 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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%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/d135a17c-0c59-47bf-8471-a05dd2d0835f%40www.fastmail.com.

Re: [sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Mike Bayer]

schema name for tables is set using the .schema parameter of the Table object.

The docs don't state this at once but you are essentially combining the 
information contained at  
https://docs.sqlalchemy.org/en/13/core/metadata.html#specifying-the-schema-name 
with 
https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html 
. 
  
you can also set the default .schema for all Table objects at once using the 
https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.params.schema
 parameter.



On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote:
> I've been using the SA ORM for 3 months simply declaring tables as you see 
> here:
> 
> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping
> 
> And then connecting to Oracle via oracle://user:pass@server
> with no problem.
> 
> However, today, I was told that my auto-generated queries were failing 
> because they were not qualified with the schema name. I.e, even though
> 
> SELECT some_column FROM some_table 
> 
> was working,  I am now being asked to make the query
> 
>SELECT some_volumn FROM SCHEMA."some_table"
> 
> and even though there are docs about specifying the schema for the SQLAlchemy 
> core, I dont see similar docs for the ORM.
> 
> I found an answer:
> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy
> 
> but I cannot believe that I need to manually issue this sort of query for 
> this:
> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") 
> 
> 
> 

> -- 
> 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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%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/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.com.

[sqlalchemy] schema qualifying Oracle auto-generated queries for the ORM

[Terrence-Monroe: Brannon]

I've been using the SA ORM for 3 months simply declaring tables as you see 
here:

https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping

And then connecting to Oracle via oracle://user:pass@server
with no problem.

However, today, I was told that my auto-generated queries were failing 
because they were not qualified with the schema name. I.e, even though

SELECT some_column FROM some_table 

was working,  I am now being asked to make the query

   SELECT some_volumn FROM SCHEMA."some_table"

and even though there are docs about specifying the schema for the 
SQLAlchemy core, I dont see similar docs for the ORM.

I found an answer:
https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy

but I cannot believe that I need to manually issue this sort of query for 
this:
session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") 


-- 
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/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%40googlegroups.com.

Re: [sqlalchemy] avoid setting attribute on init and or.reconstructor

[Mike Bayer]

I would use a memoized descriptor for that and have it evaluate when called.

Amazingly, i can't find public examples of Python memoize decorators that 
aren't overwrought.we use one that is completely efficient and simple:

class memoized_property(object):
"""A read-only @property that is only evaluated once."""

def __init__(self, fget, doc=None):
self.fget = fget
self.__doc__ = doc or fget.__doc__
self.__name__ = fget.__name__

def __get__(self, obj, cls):
if obj is None:
return self
obj.__dict__[self.__name__] = result = self.fget(obj)
return result


then you can just set it up as:

class Planning(...):
@memoized_property
def matrix(self):
return self.matrix_factor(self.stops)




On Mon, Nov 16, 2020, at 11:43 AM, lars van gemerden wrote:
> Hi Mike,
> 
> What if during reconstruction you need a one-to-many attribute, like:
> 
> class Planning(SqlaBase):
> stops = relationship(*"Stop"*, back_populates=*"planning"*, 
> lazy=*"joined"*)
> 
> matrix_factory = Matrix
> 
> def __init__(self, **kwargs):
> super().__init__(**kwargs)
> self.matrix = self.matrix_factory(self.stops)
> 
> @reconstructor
> def init_on_load(self):
>  self.matrix = self.matrix_factory(self.stops)
> The docs say self.stops will not be completely loaded in init_on_load, how 
> could i make this work?
> 
> Cheers, Lars
> On Monday, August 7, 2017 at 6:09:08 AM UTC+2 Mike Bayer wrote:
>> 
>> 
>> On Aug 6, 2017 1:33 PM, "Shane Carey"  wrote:
>>> Hey Mike,
>>> 
>>> I can expand my example. I have an orm mapped attribute like this
>>> 
>>> class Obj(Base):
>>> _evaluator = Column(String)
>>> 
>>> def __init__(self, **kwargs):
>>> super().__init__(**kwargs)
>>> self._eval_func = eval(self._evaluator)
>>> 
>>> @orm.reconstructor
>>> def init_on_load(self):
>>> self._eval_func = eval(self._evaluator)
>>> 
>>> @property
>>> def evaluator(self):
>>>  return self._eval_func
>>> 
>>> @evaluator.setter
>>> def set_evaluator(ev):
>>> self._evaluator = ev
>>> self._eval_func = eval(self._evaluator)
>>> 
>>> You can see that I have to explicitly set self._eval_func in three 
>>> different places, when really I just want to set it every time 
>>> self._evaluator is set.
>>> 
>>> It looks to me like the orm events are just a different way of placing the 
>>> different settings of this class attribute
>>> 
>>> Also, I would like to not call eval in the getter of the property for the 
>>> sake of performance (I know that would simplify the issue).
>>> 
>>> Is there a way to intercept the setting of self._evaluator for all cases?
>> 
>> 
>> Use the init and load event listeners from my previous email on top of one 
>> function.  It will be called for init and load. 
>> 
>> 
>>> 

>>> -- 
>>> 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 post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>> 
> 

> -- 
> 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/e9b2b9ff-0979-43be-94f4-fdb3ceb48ae9n%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/s

Re: [sqlalchemy] avoid setting attribute on init and or.reconstructor

[lars van gemerden]

Hi Mike,

What if during reconstruction you need a one-to-many attribute, like:

class Planning(SqlaBase):
stops = relationship("Stop", back_populates="planning", lazy="joined")

matrix_factory = Matrix

def __init__(self, **kwargs):
super().__init__(**kwargs)
self.matrix = self.matrix_factory(self.stops)

@reconstructor
def init_on_load(self):
 self.matrix = self.matrix_factory(self.stops)

The docs say self.stops will not be completely loaded in init_on_load, how 
could i make this work?

Cheers, Lars
On Monday, August 7, 2017 at 6:09:08 AM UTC+2 Mike Bayer wrote:

>
>
> On Aug 6, 2017 1:33 PM, "Shane Carey"  wrote:
>
> Hey Mike,
>
> I can expand my example. I have an orm mapped attribute like this
>
> class Obj(Base):
> _evaluator = Column(String)
>
> def __init__(self, **kwargs):
> super().__init__(**kwargs)
> self._eval_func = eval(self._evaluator)
>
> @orm.reconstructor
> def init_on_load(self):
> self._eval_func = eval(self._evaluator)
>
> @property
> def evaluator(self):
>  return self._eval_func
>
> @evaluator.setter
> def set_evaluator(ev):
> self._evaluator = ev
> self._eval_func = eval(self._evaluator)
>
> You can see that I have to explicitly set self._eval_func in three 
> different places, when really I just want to set it every time 
> self._evaluator is set.
>
> It looks to me like the orm events are just a different way of placing the 
> different settings of this class attribute
>
> Also, I would like to not call eval in the getter of the property for the 
> sake of performance (I know that would simplify the issue).
>
> Is there a way to intercept the setting of self._evaluator for all cases?
>
>
>
> Use the init and load event listeners from my previous email on top of one 
> function.  It will be called for init and load. 
>
>
> -- 
> 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 post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>

-- 
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/e9b2b9ff-0979-43be-94f4-fdb3ceb48ae9n%40googlegroups.com.