[sqlalchemy] connection in a different thread can't read tables created in another thread?

[niuji...@gmail.com]

In a testing suite I have a fixture that drop all the tables in an engine, 
then start fresh and create all the tables. After this fixture logic, my 
test case runs, using the newly created table. 
The fixture and the test case are run in the MainThread, while the database 
consumer is a web application server run in another thread.
However, I keep getting: *sqlite3.OperationalError: no such table: 
I've checked that they are using the same in-memory engine, but different 
connections(this is correct). And I've checked that the fixture does run 
before the consumer thread starts running.
What could be possible cause?

My code is as below:



import os
import pytest
import cherrypy


class DAL:
def __init__(self,
 path="database",
 filename=None,
 conn_string=None,
 echo=False):
if filename is None and conn_string is None:
conn_string = "sqlite:///:memory:"
elif conn_string is not None:
conn_string = conn_string
else:
conn_string = f'sqlite:///{os.path.abspath(path)}/{filename}'
self.conn_string = conn_string
engine = create_engine(conn_string, echo=echo)
Session_Factory = sessionmaker(bind=engine)
self.Session = sqlalchemy.orm.scoped_session(Session_Factory)

def __str__(self):
return f"object: {self.conn_string}, at {hex(id(self))}"

def get_a_dbsession(self):
opened_db = self.Session()
return opened_db

def __enter__(self):
return self.get_a_dbsession()

def __exit__(self, exception_type, exception_value, 
exception_traceback):
opened_db = self.Session()
try:
opened_db.commit()
except:
opened_db.rollback()
raise
finally:
self.Session.remove()

else:
opened_db.close()
raise exception_type

def create_schema(self):
SchemaBase.metadata.create_all(self.Session().connection().engine)


class SAEnginePlugin(cherrypy.process.plugins.SimplePlugin):
def __init__(self, bus, dal):
"""
The plugin is registered to the CherryPy engine.
"""
cherrypy.process.plugins.SimplePlugin.__init__(self, bus)
self.dal = dal

def start(self):
self.bus.subscribe("bind-session", self.bind)

def stop(self):
self.bus.unsubscribe("bind-session", self.bind)
if self.dal:
del self.dal

def bind(self):
"""
Whenever this plugin receives the 'bind-session' message, it applies
this method and bind the received session to the engine.
"""
# self.dal.Session.configure(bind=self.dal.engine)
session = self.dal.get_a_dbsession()
return session


class SATool(cherrypy.Tool):
def __init__(self):
"""
This tool binds a session to the engine each time
a requests starts and commits/rollbacks whenever
the request terminates.
"""
cherrypy.Tool.__init__(self,
   'on_start_resource',
   self.bind_session,
   priority=20)

def _setup(self):
cherrypy.Tool._setup(self)
cherrypy.request.hooks.attach('on_end_resource',
  self.close_session,
  priority=80)

def bind_session(self):
"""
Attaches a session to the request's scope by requesting
the SA plugin to bind a session to the SA engine.
"""
session = cherrypy.engine.publish('bind-session').pop()
cherrypy.request.db = session

def close_session(self):
"""
Commits the current transaction or rollbacks if an error occurs.
In all cases, the current session is unbound and therefore
not usable any longer.
"""
if not hasattr(cherrypy.request, 'db'):
return

try:
cherrypy.request.db.commit()
except:
cherrypy.request.db.rollback()
raise
finally:
cherrypy.request.db.close()
cherrypy.request.db = None


# Register the SQLAlchemy tool
cherrypy.tools.db = SATool()


class UnitServer:
   ...

@cherrypy.expose
@cherrypy.tools.json_in()
def list_filtered_entries(self):
...
queryOBJ = cherrypy.request.db.query(classmodel_obj)
...

# main module code below :

# mocking 'db':
dal = database.DAL()

# configure cherrypy:
SAEnginePlugin(cherrypy.engine, dal).subscribe()


@pytest.fixture(autouse=True) # automatically run before every test case
def mocked_dal(request):
# first, clean the database by dropping all tables:
database.SchemaBase.metadata.drop_all(dal.Session().connection().engine)
# second, create the schema from 

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

[niuji...@gmail.com]

Thanks for pointing this out. It did address this problem already.
I just solved this by manually adding all the primary/foreign keys to each 
classes. Not a big deal, actually it helps clear code!

On Sunday, November 28, 2021 at 6:37:30 PM UTC-8 Mike Bayer wrote:

> this is addressed in the docs which discuss "cascading" here:
>
>
> https://docs.sqlalchemy.org/en/14/orm/declarative_mixins.html#mixing-in-columns-in-inheritance-scenarios
>
> "The declared_attr.cascading feature currently does *not* allow for a 
> subclass to override the attribute with a different function or value. This 
> is a current limitation in the mechanics of how @declared_attr is 
> resolved, and a warning is emitted if this condition is detected. This 
> limitation does *not* exist for the special attribute names such as 
> __tablename__, which resolve in a different way internally than that of 
> declared_attr.cascading."
>
>
> you will see a warning in your program's output:
>
> SAWarning: Attribute 'record_id' on class  
> cannot be processed due to @declared_attr.cascading; skipping
>
>
> as a workaround, you can look for the attribute in __dict__ and return it, 
> though you still get the warning:
>
> class has_polymorphic_id(object):
> @declared_attr.cascading
> def record_id(cls):
> if "record_id" in cls.__dict__:
> return cls.__dict__['record_id']
> elif has_inherited_table(cls):
> return Column(ForeignKey("employee.record_id"), 
> primary_key=True)
> else:
> return Column(Integer, primary_key=True)
>
>
> otherwise you'd want to look at "cls" with inspect(cls).local_table etc. 
> and figure out the correct column to include in the FK if you are doing 
> things this way.
>
>
>
>
>
>
>
> On Sun, Nov 28, 2021, at 4:22 PM, niuji...@gmail.com wrote:
>
> I've just manually put this line to the `Programmer` class definition, but 
> it still gives me the same error, strangely:
>
>
> class Programmer(Engineer):
> __tablename__ = 'programmer'
> record_id = Column(ForeignKey('engineer.record_id'),
>primary_key=True)
> 
> On Sunday, November 28, 2021 at 8:25:30 AM UTC-8 Mike Bayer wrote:
>
>
>
> On Sun, Nov 28, 2021, at 4:24 AM, niuji...@gmail.com wrote:
>
> I'm using the "joined table inheritance" model. I have three levels of 
> inheritance.
>
> class has_polymorphic_id(object):
> @declared_attr.cascading
> def record_id(cls):
> if has_inherited_table(cls):
> return Column(ForeignKey('employee.record_id'),
>   primary_key=True)
> else:
> return Column(Integer, primary_key=True)
>
>
> class Employee(has_polymorphic_id, Base):
> __tablename__ = 'employee'
> name = Column(String(50))
> type = Column(String(50))
>
> __mapper_args__ = {
> 'polymorphic_identity':'employee',
> 'polymorphic_on':type
> }
>
> class Engineer(Employee):
> __tablename__ = 'engineer'
> 
>
> class Programmer(Engineer):
> __tablename__ = 'programmer'
> 
>
> This only works for the second level, namely `Enginner` can inherits the 
> foreignkey/primarykey from `Employee`'s mixin, but the next level, the 
> `Programmer`, python gives me an error:
> `sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key 
> relationships between 'engineer' and 'programmer'.`
>
>
> The "cascading" attribute seems to be working correctly.  The error here 
> is because you aren't providing any column that will allow for a JOIN 
> between the "programmer" and "engineer" table.
>
> you would want Programmer.record_id to be a foreign key to 
> Engineer.record_id, not Employee.record_id.When you load Programmer 
> rows, the join would be "FROM employee JOIN engineer ON  JOIN 
> programmer ON ".
>
>
>
> Is this designed this way? And if I manually set the foreignkey, should 
> the third level reference to the base level or to its immediate parent 
> level's primarykey?
>
>
> it has to be to the immediate parent.  that's what the error message here 
> is talking about.
>
>
>
>
> -- 
> 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 Goog

Re: [sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

[niuji...@gmail.com]

I've just manually put this line to the `Programmer` class definition, but 
it still gives me the same error, strangely:


class Programmer(Engineer):
__tablename__ = 'programmer'
record_id = Column(ForeignKey('engineer.record_id'),
   primary_key=True)

On Sunday, November 28, 2021 at 8:25:30 AM UTC-8 Mike Bayer wrote:

>
>
> On Sun, Nov 28, 2021, at 4:24 AM, niuji...@gmail.com wrote:
>
> I'm using the "joined table inheritance" model. I have three levels of 
> inheritance.
>
> class has_polymorphic_id(object):
> @declared_attr.cascading
> def record_id(cls):
> if has_inherited_table(cls):
> return Column(ForeignKey('employee.record_id'),
>   primary_key=True)
> else:
> return Column(Integer, primary_key=True)
>
>
> class Employee(has_polymorphic_id, Base):
> __tablename__ = 'employee'
> name = Column(String(50))
> type = Column(String(50))
>
> __mapper_args__ = {
> 'polymorphic_identity':'employee',
> 'polymorphic_on':type
> }
>
> class Engineer(Employee):
> __tablename__ = 'engineer'
> 
>
> class Programmer(Engineer):
> __tablename__ = 'programmer'
> 
>
> This only works for the second level, namely `Enginner` can inherits the 
> foreignkey/primarykey from `Employee`'s mixin, but the next level, the 
> `Programmer`, python gives me an error:
> `sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key 
> relationships between 'engineer' and 'programmer'.`
>
>
> The "cascading" attribute seems to be working correctly.  The error here 
> is because you aren't providing any column that will allow for a JOIN 
> between the "programmer" and "engineer" table.
>
> you would want Programmer.record_id to be a foreign key to 
> Engineer.record_id, not Employee.record_id.When you load Programmer 
> rows, the join would be "FROM employee JOIN engineer ON  JOIN 
> programmer ON ".
>
>
>
> Is this designed this way? And if I manually set the foreignkey, should 
> the third level reference to the base level or to its immediate parent 
> level's primarykey?
>
>
> it has to be to the immediate parent.  that's what the error message here 
> is talking about.
>
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/02ce8134-2946-4d1b-b6d3-e47ad7944e0en%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/02ce8134-2946-4d1b-b6d3-e47ad7944e0en%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
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/b08ba9e6-e1e3-451e-b39b-bc5ee4b63847n%40googlegroups.com.

[sqlalchemy] @declared_attr.cascading doesn't work when inheritance of multiple levels

[niuji...@gmail.com]

I'm using the "joined table inheritance" model. I have three levels of 
inheritance.

class has_polymorphic_id(object):
@declared_attr.cascading
def record_id(cls):
if has_inherited_table(cls):
return Column(ForeignKey('employee.record_id'),
  primary_key=True)
else:
return Column(Integer, primary_key=True)


class Employee(has_polymorphic_id, Base):
__tablename__ = 'employee'
name = Column(String(50))
type = Column(String(50))

__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}

class Engineer(Employee):
__tablename__ = 'engineer'


class Programmer(Engineer):
__tablename__ = 'programmer'


This only works for the second level, namely `Enginner` can inherits the 
foreignkey/primarykey from `Employee`'s mixin, but the next level, the 
`Programmer`, python gives me an error:
`sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key 
relationships between 'engineer' and 'programmer'.`

Is this designed this way? And if I manually set the foreignkey, should the 
third level reference to the base level or to its immediate parent level's 
primarykey?

-- 
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/02ce8134-2946-4d1b-b6d3-e47ad7944e0en%40googlegroups.com.

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

[niuji...@gmail.com]

This is very helpful.

How to return a list of scalar values in this case? Now the query returns a 
list of tuples, and each tuple only has one value, which is what actually 
needed. Is there a parameter to return a series of scalar value like this?
On Wednesday, November 10, 2021 at 12:05:27 PM UTC-8 Mike Bayer wrote:

> it has to do with how SQLAlchemy determines the FROM list in a select() 
> statement.
>
> if you say select(table.c.id), it knows that "table" is the thing to 
> select "from".
>
> however, if you say select(select(...).correlate(...).scalar_subquery()), 
> that's assuming it's a SELECT from a scalar subquery,  which normally does 
> not imply a FROM clause.
>
> In this case, that you are saying correlate(C), we could argue that *does* 
> imply a FROM clause of some kind, but SQLAlchemy doesn't make that leap 
> right now.  You might have said correlate(C, D) or correlate_except(Q) and 
> then it's not as clear what the scalar subquery would be selected FROM.
>
> going further, there *could* be some new kind of API where a scalar 
> subquery could suggest a FROM clause that should be used when this subquery 
> is selected FROM.  that would allow your column_property() to be built up 
> such that it would be "self sufficient" when queried by itself.  but again 
> we don't have that right now.
>
> with all those options exhausted, for SQLAlchemy to be given 
> select(select().scalar_subquery()), and then to produce "SELECT * FROM 
> (SELECT ) FROM ", that FROM part has to be added on 
> explicitly, i.e. select(select().scalar_subuqery()).select_from( table>).
>
>
>
> On Wed, Nov 10, 2021, at 2:35 PM, niuji...@gmail.com wrote:
>
> This works! Could you explain a little about this differences by using 
> select_from here? I think this is very important and useful, really want to 
> learn it right.
>
> On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
>
>
> try calling:
>
> query(C.symbol_from_a).select_from(C)
>
>
>
> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>   
>  sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id == 
> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole object, 
> it works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property 
> when query that column_property independently.
>
> How to solve this?
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
>
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com
>  
> <https://groups.g

Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?

[niuji...@gmail.com]

This works! Could you explain a little about this differences by using 
select_from here? I think this is very important and useful, really want to 
learn it right.

On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:

> try calling:
>
> query(C.symbol_from_a).select_from(C)
>
>
>
> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>   
>  sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id == 
> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole object, 
> it works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property 
> when query that column_property independently.
>
> How to solve this?
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>

-- 
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/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com.

[sqlalchemy] correlated subquery as column_property can't be queried independently?

[niuji...@gmail.com]

class A(Base):
primary_id = Column(Integer, prirmary_key=True)
some_A_marker = Column(String)

class B(Base):
primary_id = Column(Integer, primary_key=True)
referencing_A_id = Column(Integer, ForeignKey(A.primary_id))

class C(Base):
primary_id = Column(Integer, primary_key=True)
basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
symbol_from_A = column_property(
  
 sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id == 
C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
   )


When I query this highlighted column_property along with the whole object, 
it works fine:

>>> sess.query(C, C.symbol_from_A).all()

However, when I query this column_property alone, it doesn't work:

>>>sess.query(C.symbol_from_A).distinct().all()

I noticed that somehow the correlated subquery wasn't executed property 
when query that column_property independently.

How to solve this?

-- 
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/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com.

[sqlalchemy] Is "uselist=False" imcompatible with lazy='joined' in relationship?

[niuji...@gmail.com]

Although the official documentation is very robust and detailed,  one thing 
I noticed is not very clear.

When specifying arguments for a relationship like this:

class Bonus(Base):

basis = relationship("Accomplishment", uselist=False, lazy='joined')

whenever there is "uselist=False", the eager-loading doesn't happen, 
regardless of setting the lazy='joined' or not.

If I load the Bonus object and expunge it from the session, and then try to 
access the 'basis' attribute, I will get a 
'sqlalchemy.orm.exc.DetachedInstanceError'.


Is uselist=False naturally incompatible with eager-loading? If so, what is 
the underlying reasong? 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/68998421-8f9e-4484-bf02-a93a49f09cd8n%40googlegroups.com.

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

[niuji...@gmail.com]

from_column(x) not in negatives,
  File "/codebase_mountpoint/codebase/database/__init__.py", line 106, in 
get_property_name_from_column
return sqlalchemy.inspect(cls).get_property_by_column(column_obj).key
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/mapper.py", 
line 1998, in get_property_by_column
return self._columntoproperty[column]
AttributeError: 'Mapper' object has no attribute '_columntoproperty'

On Friday, October 8, 2021 at 2:12:14 PM UTC-7 Mike Bayer wrote:

>
>
> On Fri, Oct 8, 2021, at 4:13 PM, niuji...@gmail.com wrote:
>
> Thanks for this very important information!
>
> If I do want to utilize the mapper functions at this stage, what can I do? 
> currently I have this error showing up:
>
> AttributeError: 'Mapper' object has no attribute '_columntoproperty'
>
>
> you would need to show me a stack trace on that.  however, depending on 
> which event you are using to get at the mapper, it may not be initialized 
> yet (can confirm that class_instrument() event is too early if access to 
> the internals of the mapper is required).  as stated previously, the 
> best event to use when a class is first mapped but before the full span of 
> mappings have been considered is the before_mapper_configured() event (can 
> confirm _columntoproperty is available at that stage).
>
>
>
> On Friday, October 8, 2021 at 6:58:02 AM UTC-7 Mike Bayer wrote:
>
>
>
> On Fri, Oct 8, 2021, at 7:09 AM, niuji...@gmail.com wrote:
>
> In order to make sure that the mapped class is fully ready, I chose the 
> latest point, namely `class_instrument`.
> However, it seems that at that moment the `__mapper__` attribute is not 
> available.
> When I tried 
>   return cls.__mapper__.get_property_by_column(column_obj).key
>
> I got:
>  AttributeError: type object 'GeoAreaVariantName' has no attribute 
> '__mapper__'
>
> Is this a bug or a feature?
>
>
> it's a normal behavior, that event is called before mapping has been 
> established.
>
> but also, the "__mapper__" attribute is a convenience feature that should 
> not be relied upon at this stage.  the correct way to get the mapper for a 
> class is to use the sqlalchemy.inspect() function, i.e.  mapper = 
> inspect(class).
>
>
>
>
>
> On Wednesday, October 6, 2021 at 5:36:22 AM UTC-7 Mike Bayer wrote:
>
>
> events that occur around this time include
>
> this one claims it's before:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.instrument_class
>
> this one says after:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.InstrumentationEvents.class_instrument
>
> this one is definitely after and is usually fine for anything that needs 
> to happen for mapped classes before they are reconciled against other 
> classes:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.before_mapper_configured
>
>
> On Wed, Oct 6, 2021, at 4:48 AM, niuji...@gmail.com wrote:
>
> I want to do some after processing on each class that is just put into 
> the class_registry. Is there a event hook for this? 
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
>
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/066675cb-164a-4f61-9764-15ac402b6a3bn%40googlegroups.com
>  
> <https://groups.google.c

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

[niuji...@gmail.com]

Thanks for this very important information!

If I do want to utilize the mapper functions at this stage, what can I do? 
currently I have this error showing up:

AttributeError: 'Mapper' object has no attribute '_columntoproperty'

On Friday, October 8, 2021 at 6:58:02 AM UTC-7 Mike Bayer wrote:

>
>
> On Fri, Oct 8, 2021, at 7:09 AM, niuji...@gmail.com wrote:
>
> In order to make sure that the mapped class is fully ready, I chose the 
> latest point, namely `class_instrument`.
> However, it seems that at that moment the `__mapper__` attribute is not 
> available.
> When I tried 
>   return cls.__mapper__.get_property_by_column(column_obj).key
>
> I got:
>  AttributeError: type object 'GeoAreaVariantName' has no attribute 
> '__mapper__'
>
> Is this a bug or a feature?
>
>
> it's a normal behavior, that event is called before mapping has been 
> established.
>
> but also, the "__mapper__" attribute is a convenience feature that should 
> not be relied upon at this stage.  the correct way to get the mapper for a 
> class is to use the sqlalchemy.inspect() function, i.e.  mapper = 
> inspect(class).
>
>
>
>
> On Wednesday, October 6, 2021 at 5:36:22 AM UTC-7 Mike Bayer wrote:
>
>
> events that occur around this time include
>
> this one claims it's before:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.instrument_class
>
> this one says after:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.InstrumentationEvents.class_instrument
>
> this one is definitely after and is usually fine for anything that needs 
> to happen for mapped classes before they are reconciled against other 
> classes:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.before_mapper_configured
>
>
> On Wed, Oct 6, 2021, at 4:48 AM, niuji...@gmail.com wrote:
>
> I want to do some after processing on each class that is just put into 
> the class_registry. Is there a event hook for this? 
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
>
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/066675cb-164a-4f61-9764-15ac402b6a3bn%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/066675cb-164a-4f61-9764-15ac402b6a3bn%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
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/fb457c38-74a2-4ce6-bb1c-35157347fd9fn%40googlegroups.com.

Re: [sqlalchemy] Is there an event hook for putting a class into class_registry?

[niuji...@gmail.com]

In order to make sure that the mapped class is fully ready, I chose the 
latest point, namely `class_instrument`.
However, it seems that at that moment the `__mapper__` attribute is not 
available.
When I tried 
  return cls.__mapper__.get_property_by_column(column_obj).key

I got:
 AttributeError: type object 'GeoAreaVariantName' has no attribute 
'__mapper__'

Is this a bug or a feature?
On Wednesday, October 6, 2021 at 5:36:22 AM UTC-7 Mike Bayer wrote:

> events that occur around this time include
>
> this one claims it's before:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.instrument_class
>
> this one says after:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.InstrumentationEvents.class_instrument
>
> this one is definitely after and is usually fine for anything that needs 
> to happen for mapped classes before they are reconciled against other 
> classes:
>
>
> https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.MapperEvents.before_mapper_configured
>
>
> On Wed, Oct 6, 2021, at 4:48 AM, niuji...@gmail.com wrote:
>
> I want to do some after processing on each class that is just put into 
> the class_registry. Is there a event hook for this? 
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
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/066675cb-164a-4f61-9764-15ac402b6a3bn%40googlegroups.com.

[sqlalchemy] Is there an event hook for putting a class into class_registry?

[niuji...@gmail.com]

I want to do some after processing on each class that is just put into 
the class_registry. Is there a event hook for this?

-- 
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/6ec43831-d59e-4343-99cf-dce3773a1216n%40googlegroups.com.

[sqlalchemy] 'validates' include_removes=True doesn't seem to prevent deletion?

[niuji...@gmail.com]

I have the following validator:

@sa_orm.validates('variant_names', include_removes=True)
def validate_unique_identifying(self, key, name_dict,
is_remove):
if is_remove:
raise ValueError("DOn't")
else:
if name_dict.is_primary_identifying_flag is None:
name_dict.is_primary_identifying_flag = False
return name_dict


When deleting, I get the raised ValueError, but the deletion occured anyway.
Is this the expected behaviour? Or there is some other settings that can 
prevent the deletion?

-- 
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/a51745a0-3a49-4613-bd17-35920bfd5fe9n%40googlegroups.com.

Re: [sqlalchemy] Can `sqlalchemy.orm.validates` be set on multiple attributes?

[niuji...@gmail.com]

Hi Mike,

Instead of this:

class Entity:
   def set_timestamp(self, timestamp, timezone):
   # ...

which you have to manually call it to set, I'd like to have an automated 
setting directly on initiation, so that I can create new records in a more 
uniformed ways:
Entity(**params_from_web_form)


Now I just tried using @hybrid_property.setter:
class Tutorsession(Base):
  ...
  @start_time_local.setter
  def start_time_local(self, value):
  timezone_stamp = 
json.loads(cherrypy.request.body.read())['timezone_stamp']
  self.start_time = create_timestamp(value, timezone_stamp)



unfortunately it gives me an error:

TypeError: 'start_time_local' is an invalid keyword argument for 
Turtorsession




Is hybrid_property.setter allowed to create new records like this way at 
all?
On Saturday, September 4, 2021 at 7:43:24 AM UTC-7 Mike Bayer wrote:

>
>
> On Fri, Sep 3, 2021, at 4:07 PM, niuji...@gmail.com wrote:
>
> In the official documentation it says:
>
>
> *validates* 
> <https://docs.sqlalchemy.org/en/13/orm/mapped_attributes.html#sqlalchemy.orm.validates>(*names,
>  
> **kw)
>
> Decorate a method as a ‘validator’ for one or more named properties.
>
>
>
> I need to validate two incoming *-**attributes at the same time,  for 
> example:
>
>
> class Entity(Base):
>
> 
>
> attr_timezone = Column(String)
>
> attr_timestamp = Column(MyTimeStamp)
>
>
> When taking incoming arguments, `attr_timezone` as a string need to be 
> first validated and converted to timezone instance before being attached to 
> my custom class `MyTimeStamp`. Can `validates` do this?
>
>
> its not a simple yes or no because the use case does not totally make 
> sense.
>
> what happens if someone does this:
>
> some_entity().attr_timestamp = 
>
> and then nothing else?  what do you want to happen when that attribute set 
> occurs?
>
>
> what you probably want to do is require that one or the other attribute is 
> set first.   you can certainly use @validates to set this up, it's just an 
> event hook.
>
> more realistically though in the most practical sense, you'd want to have 
> a method that clearly documents what needs to be done:
>
> class Entity:
>def set_timestamp(self, timestamp, timezone):
># ...
>
>
> then make "attr_timestamp" and "attr_timezone" private. This is boring 
> and not magical but if I were in a large organization where we just need it 
> to be clear and unambigious, this is how I'd do it.
>
>
>
>
>
>
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/ee2abfce-6a5a-4d43-b508-f86be3ea8cd5n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/ee2abfce-6a5a-4d43-b508-f86be3ea8cd5n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
>

-- 
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/65671e2f-094a-427a-89a4-2678c9e6125en%40googlegroups.com.

Re: [sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

Hi Mike,
This code doesn't seem to yield desired result, now all the 
`effective_enabling_factor` is `None`, except for those rows that carries a 
`_enabling_factor` on itself. In other words, it doesn't seem to recusively 
search the next immediately parent's marker at all.

And the SQL it generates is a little confusing, it seems to me that we 
introduce a `recursive_cte_a` aliased table and gain nothing from it:

WITH RECURSIVE lineage_nodes(
  anchor_id, _enabling_factor, selfreferencing_staff_id
) AS (
  SELECT 
anchor_f.id AS anchor_id, 
anchor_f._enabling_factor AS _enabling_factor, 
anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
  FROM 
staff AS anchor_f 
  UNION ALL 
  SELECT 
anchor_f.id AS id, 
anchor_f._enabling_factor AS _enabling_factor, 
anchor_f.selfreferencing_staff_id AS selfreferencing_staff_id 
  FROM 
staff AS anchor_f 
JOIN lineage_nodes AS anon_2 ON anon_2.selfreferencing_staff_id = 
anchor_f.id 
  WHERE 
anon_2._enabling_factor IS NULL
) 
SELECT 
  staff.id AS staff_id, 
  staff._enabling_factor AS staff__enabling_factor, 
  (
SELECT 
  final_f._enabling_factor 
FROM 
  staff AS final_f 
WHERE 
  final_f.id = (
SELECT 
  min(lineage_nodes.anchor_id) AS min_1 
FROM 
  lineage_nodes 
WHERE 
  lineage_nodes.anchor_id = staff.id
  )
  ) AS anon_1 
FROM 
  staff


On Sunday, September 26, 2021 at 7:35:20 AM UTC-7 Mike Bayer wrote:

> OK there's various small issues here but overall SQLAlchemy's new 
> "nesting" feature does not seem to work correctly yet for a RECURSIVE CTE 
> with UNION, meaning it's not very useful for RECURSIVE right now.
>
> But here's the thing, I'm not as SQL expert as some people think but I 
> don't think it should be necessary for a CTE to actually be embedded in a 
> subquery.  You can keep the CTE on top, where it can more efficiently 
> create a set of all rows, and then refer to it within a normal correlated 
> subquery that's in the column_property().
>
> ive written the below MCVE we can work with going forward.  see how close 
> this is, but the goal is, keep the CTE on top as it normally can be and do 
> correlations outside of it.
>
> from sqlalchemy import Column
> from sqlalchemy import ForeignKey
> from sqlalchemy import func
> from sqlalchemy import Integer
> from sqlalchemy import select
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import backref
> from sqlalchemy.orm import column_property
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Staff(Base):
> __tablename__ = "staff"
>
> id = Column(Integer, primary_key=True)
>
> selfreferencing_staff_id = Column(
> Integer,
> ForeignKey("staff.id", onupdate="CASCADE", ondelete="SET NULL"),
> )
> _enabling_factor = Column(Integer)
> record_id = Column(Integer)
>
> team_members = relationship(
> "Staff", backref=backref("supervisor", remote_side="Staff.id")
> )
>
>
> anchor_s = Staff.__table__.alias(name="anchor_s")
> s = Staff.__table__.alias(name="s")
> final_s = Staff.__table__.alias(name="final_s")
>
> recursive_cte = (
> select(
> [
> anchor_s.c.id.label("anchor_id"),
> anchor_s.c._enabling_factor,
> anchor_s.c.selfreferencing_staff_id,
> ]
> ).select_from(anchor_s)
> # this part we are going to move to the outside
> # .where(anchor_s.c.id == Staff.id)
> .cte(name="lineage_nodes", recursive=True)
> )
>
> recursive_cte_a = recursive_cte.alias()
>
> lineage_nodes = recursive_cte.union_all(
> select([s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id])
> .join(
> recursive_cte_a, recursive_cte_a.c.selfreferencing_staff_id == 
> s.c.id
> )
> .filter(recursive_cte_a.c._enabling_factor == None)
> )
>
> top_id = (
> select(func.min(lineage_nodes.c.anchor_id))
> # here's where we moved it
> .where(lineage_nodes.c.anchor_id == Staff.id)
> .correlate(Staff)
> .scalar_subquery()
> )
>
>
> Staff.effective_enabling_factor = column_property(
> select(final_s.c._enabling_factor)
> .where(final_s.c.record_id == top_id)
> .scalar_subquery()
> )  # This is where I define the desired column_property.
>
>
> s = Session()
>
> q = s.query(Staff)
> print(q)
>
> On Sun, Sep 26, 2021, at 2:56 AM, niuji...@gmail.com wrote:
>
>
> Hi Mike,
>
> Yes the SQL code runs as desire

Re: [sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

Hi Mike,

Yes the SQL code runs as desired, but the Python code doesn't, 
unfortunately.
After reading the references you pointed out, my Python code looks like 
this:

class Staff(Base):
  id  = Column(Integer, primary_key=True)
  selfreferencing_staff_id = Column(
Integer,
ForeignKey('staff_table.id',
   onupdate="CASCADE",
   ondelete='SET NULL'))
  _enabling_factor = Column(Integer)
 
   team_members = relationship('Staff',
   backref=backref(
   'supervisor',
   remote_side='Staff.id'))
   
anchor_s = Staff.__table__.alias(name="anchor_s")
s = Staff.__table__.alias(name="s")
final_s = Staff.__table__.alias(name="final_s")

recursive_cte = select([
anchor_s.c.id, anchor_s.c._enabling_factor, 
anchor_s.c.selfreferencing_staff_id
   ]).select_from(anchor_s).where(anchor_s.c.id == Staff.id).cte(
name="lineage_nodes", recursive=True)

lineage_nodes = recursive_cte.union_all(
   select([
s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id
]).join(recursive_cte, recursive_cte.c.selfreferencing_staff_id 
==
s.c.id).filter(recursive_cte.c._enabling_factor == None))

top_id = select(sasql.func.min(lineage_nodes.c.id))
Staff.effective_enabling_factor = 
column_property(select(final_s.c._enabling_factor).where(
final_s.c.record_id == top_id).scalar_subquery())  # This is where 
I define the desired column_property.




The problems seems to be that this code doesn't generate `correlated 
subquery` for each `Staff` row ( as in the SQL code `WHERE anchor_s.id = 
outer_s.id`). Could you take a look?
On Saturday, September 25, 2021 at 8:26:46 PM UTC-7 Mike Bayer wrote:

> well __class__.__table__ isn't going to be there inside the class body, 
> just to make things simple you will want to add this column_property() 
> after the Staff class is fully defined; then you make use of Staff. to 
> get at columns.  
> https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#using-column-property
>  
> will show how to assign a new column_property to the class after the fact.
>
> next, the query you have is correlating, but i really dont have any idea 
> if SQLAlchemy is going to handle a correlated column deep inside of a CTE 
> like that.   The form you have, with the CTE embedded in the parenthesis, 
> is only possible with the most recent SQLAlchemy 1.4.24 release where 
> someone contributed a new parameter called "nesting", which means the CTE 
> should not be moved to the top level of the SELECT.   still, not really 
> sure what a CTE will do inside a correlated subquery like that.   For an 
> example of how to use "nesting" see example four at 
> https://docs.sqlalchemy.org/en/14/core/selectable.html?highlight=hascte#sqlalchemy.sql.expression.HasCTE.cte
>  
> .  for the correlate, when you write out the select() that refers to 
> "outer_s", add .correlate(Staff) to it, which means Staff isn't added to 
> the FROM list, it's assumed to be on the outside.
>
> give those a try but im not totally sure CTEs work as correlated 
> subqueries right now, it's not been tried.  I assume you've confirmed this 
> query actually runs, im surprised you can even correlate inside of a CTE 
> like that.
>
>
>
>
>
> On Sat, Sep 25, 2021, at 12:04 AM, niuji...@gmail.com wrote:
>
> Hi Mike, thanks for pointing out the direction.
> I've worked out the SQL, but failed when converting to SQLAlchemy 
> construct.
>
> My SQL query looks like this:
>
> SELECT id, (
> WITH lineage_nodes (id, _enabling_factor, 
> selfreferencing_staff_id) AS
> (
> SELECT anchor_s.id, anchor_s._enabling_factor, 
> anchor_s.selfreferencing_staff_id
> FROM staff_table AS anchor_s
> WHERE anchor_s.id = outer_s.id
> 
> UNION ALL
> 
> SELECT s.id, s._enabling_factor, s.selfreferencing_staff_id
> FROM lineage_nodes AS l
> INNER JOIN staff_table AS s
> ON l.selfreferencing_staff_id = s.id
> WHERE l._enabling_factor IS NULL
> ),
> 
> top_node_id (top_id) AS
> (
> SELECT MIN(id) AS top_id FROM lineage_nodes
> )
> 
> SELECT staff_table._enabling_factor
> FROM staff_table
> INNER JOIN top_node_id
> ON staff_table.id = top_node_id.top_id
> ) AS effective_enabling_factor
> FROM staff_table AS outer_s;
>
>
>

Re: [sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

Hi Mike, thanks for pointing out the direction.
I've worked out the SQL, but failed when converting to SQLAlchemy construct.

My SQL query looks like this:

SELECT id, (
WITH lineage_nodes (id, _enabling_factor, selfreferencing_staff_id) 
AS
(
SELECT anchor_s.id, anchor_s._enabling_factor, 
anchor_s.selfreferencing_staff_id
FROM staff_table AS anchor_s
WHERE anchor_s.id = outer_s.id

UNION ALL

SELECT s.id, s._enabling_factor, s.selfreferencing_staff_id
FROM lineage_nodes AS l
INNER JOIN staff_table AS s
ON l.selfreferencing_staff_id = s.id
WHERE l._enabling_factor IS NULL
),

top_node_id (top_id) AS
(
SELECT MIN(id) AS top_id FROM lineage_nodes
)

SELECT staff_table._enabling_factor
FROM staff_table
INNER JOIN top_node_id
ON staff_table.id = top_node_id.top_id
) AS effective_enabling_factor
FROM staff_table AS outer_s;




My Python codes looks like this:


class Staff(Base):
  id  = Column(Integer, primary_key=True)
  selfreferencing_staff_id = Column(
Integer,
ForeignKey('staff_table.id',
   onupdate="CASCADE",
   ondelete='SET NULL'))
  _enabling_factor = Column(Integer)

  ## codes below doesn't work:
  anchor_s = __class__.__table__.alias(name="anchor_s")
  s = __class__.__table__.alias(name="s")

   recursive_cte = select([
id, _enabling_factor, selfreferencing_staff_id
   ]).select_from(anchor_s).where(anchor_s.c.id == id).cte(
name="lineage_nodes", recursive=True)

   lineage_nodes = recursive_cte.union_all(
   select([
s.c.id, s.c._enabling_factor, s.c.selfreferencing_staff_id
]).join(recursive_cte, recursive_cte.c.selfreferencing_staff_id 
==
s.c.id).filter(recursive_cte.c._enabling_factor == None))

   top_id = select(sasql.func.min(lineage_nodes.c.id))
   effective_enabling_factor = column_property(...) # I have trouble in 
this line here.
   ## codes above has a NameError: name '__class__' is not defined
 
   team_members = relationship('Staff',
   backref=backref(
   'supervisor',
   remote_side='Staff.id'))
On Friday, September 24, 2021 at 1:00:01 PM UTC-7 Mike Bayer wrote:

> this is a hefty query to dig in to but column_property() subqueries have 
> to be formed in terms of a correlated subquery.  So instead of injecting a 
> particular primary key into it, you set it to point to the Staff.id column.
>
> correlated subqueries are not terrific performers and the construct can be 
> a little bit clumsy in the ORM as well, however, the second example at 
> https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html#using-column-property
>  
> shows the general idea.
>
> a first step to understanding might be to write out the SQL you think you 
> want when you SELECT some Staff rows, where one of the columns in the row 
> is the "effective_enabling_factor".   that column needs to be written as a 
> correlated subquery for it to be compatible with column_property().
>
>
>
> On Fri, Sep 24, 2021, at 1:47 AM, niuji...@gmail.com wrote:
>
> class Staff(Base):
>   id  = Column(Integer, primary_key=True)
>   selfreferencing_staff_id = Column(
> Integer,
> ForeignKey('staff_table.id',
>onupdate="CASCADE",
>ondelete='SET NULL'))
>   _enabling_factor = Column(Integer)
>   effective_enabling_factor = column_property(...) # I have trouble in 
> this line here.
>   team_members = relationship('Staff',
>backref=backref(
>'supervisor',
>remote_side='Staff.id'))
>
>
> This is a self-referencing lineage. Each staff has one supervisor above 
> them. Each staff has a `_enabling_factor`, which can be either a Integer, 
> or Null. A staff's `effective_enabling_factor` is either their own 
> `_enabling_factor` value, or their supervisor's `effective_enabling_factor` 
> if their own is Null.
>
> This seems to be a case to use recursive CTE.
> I can construct the query for a certain staff member, e.g. staff #5:
>
> recursive_cte = select([Staff.id,  Staff._enabling_factor, 
> Staff.selfreferencing_staff_id]).where(Staff.id==5).cte(recursive=True)
>
> lineage_nodes = recursive_cte.union_all(select([Staff.id,  
> Staff._enabling_factor, 
> Staff.selfreferencing_staff_id]).join

[sqlalchemy] recursive cte use current row as anchor

[niuji...@gmail.com]

class Staff(Base):
  id  = Column(Integer, primary_key=True)
  selfreferencing_staff_id = Column(
Integer,
ForeignKey('staff_table.id',
   onupdate="CASCADE",
   ondelete='SET NULL'))
  _enabling_factor = Column(Integer)
  effective_enabling_factor = column_property(...) # I have trouble in 
this line here.
  team_members = relationship('Staff',
   backref=backref(
   'supervisor',
   remote_side='Staff.id'))


This is a self-referencing lineage. Each staff has one supervisor above 
them. Each staff has a `_enabling_factor`, which can be either a Integer, 
or Null. A staff's `effective_enabling_factor` is either their own 
`_enabling_factor` value, or their supervisor's `effective_enabling_factor` 
if their own is Null.

This seems to be a case to use recursive CTE.
I can construct the query for a certain staff member, e.g. staff #5:

recursive_cte = select([Staff.id,  Staff._enabling_factor, 
Staff.selfreferencing_staff_id]).where(Staff.id==5).cte(recursive=True)

lineage_nodes = recursive_cte.union_all(select([Staff.id,  
Staff._enabling_factor, 
Staff.selfreferencing_staff_id]).join(recursive_cte, 
recursive_cte.c.selfreferencing_staff_id==Staff.record_id).filter(recursive_cte.c._enabling_factor
 
== None))

marker_carrying_supervisor_id 
= select(sasql.func.min(lineage_nodes.c.id)).scalar_subquery()

select(Staff._enabling_factor).where(Staff.id==marker_carrying_supervisor_id)


However, I don't see how I can write this recursive CTE as a 
column_property on the `Staff` class. Instead of giving specific primary 
key  (e.g. #5), I need to somehow reference current row as the anchor.

How to solve this?

-- 
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/a5e26719-3e5e-4f6e-92fc-c3319ad3ec6fn%40googlegroups.com.

[sqlalchemy] Can `sqlalchemy.orm.validates` be set on multiple attributes?

[niuji...@gmail.com]

In the official documentation it says:


validates 
(*names,
 
**kw)

Decorate a method as a ‘validator’ for one or more named properties.



I need to validate two incoming *-**attributes at the same time,  for 
example:


class Entity(Base):



attr_timezone = Column(String)

attr_timestamp = Column(MyTimeStamp)


When taking incoming arguments, `attr_timezone` as a string need to be 
first validated and converted to timezone instance before being attached to 
my custom class `MyTimeStamp`. Can `validates` do this?

-- 
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/ee2abfce-6a5a-4d43-b508-f86be3ea8cd5n%40googlegroups.com.

[sqlalchemy] When to set "cache_ok" to True?

[niuji...@gmail.com]

I have consistently receiving the warning: 
will not produce a cache key because the ``cache_ok`` flag is not set to 
True.  Set this flag to True if this type object's state is safe to use in 
a cache key, or False to disable this warning.


After reading the documentation, I learned that the "cache_ok" class-level 
attribute can be set to either True or False. But the documentation is very 
abstract on when to use it? What is cache key? Is it for loading and 
caching a set of objects from the database? Or caching the query itself?

If my TypeDecorator class doesn't even have a __init__method, just 
"process_bind_param" and "process_result_value" two methods, do I even need 
to bother with this "cache_ok" setting at all?

-- 
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/abe578f0-fd11-468f-857d-dee6fd77ebc5n%40googlegroups.com.

Re: [sqlalchemy] conditionals inside column_property

[niuji...@gmail.com]

extremely helpful, like a beacon for a lost ship:) thanks

On Friday, December 11, 2020 at 5:59:50 AM UTC-8 Simon King wrote:

> I think this page might explain it:
>
>
> https://docs.sqlalchemy.org/en/13/core/sqlelement.html#column-elements-and-expressions
>
> The "case" function, like many other SQLAlchemy functions, returns an
> instance of a ClauseElement subclass. This instance is used later when
> building SQL queries. Columns themselves are also ClauseElement
> subclasses. You can construct them at any time and pass them around
> like any other python object. When it comes time to query the
> database, the SQLAlchemy compiler will build the query by traversing
> all the ClauseElements and converting them to the appropriate SQL.
>
> The column_property mechanism accepts a ClauseElement and causes it to
> be added to the SQL when you later query the table.
>
> Simon
>
> On Fri, Dec 11, 2020 at 11:11 AM Jinghui Niu  wrote:
> >
> > Thanks. One thing to clarify, I noticed that here you used `case` 
> without using in a context of `select`. Is this considered a shorthand 
> within sqlalchemy?
> >
> > On Fri, Dec 11, 2020 at 2:16 AM Simon King  
> wrote:
> >>
> >> You can do it, but you need to use an SQL conditional rather than a
> >> python one. In this case that would probably be a CASE expression:
> >>
> >> 
> https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.case
> >>
> >> I think it would look something like this:
> >>
> >> from sqlalchemy.sql import case
> >>
> >> aggr = column_property(
> >> case([(attr_a == 'go', attr_b + attr_c)],
> >> else_=attr_b - attr_c)
> >> )
> >>
> >> Hope that helps,
> >>
> >> Simon
> >>
> >> On Fri, Dec 11, 2020 at 9:13 AM niuji...@gmail.com  
> wrote:
> >> >
> >> > I have a mapped class:
> >> >
> >> > class Model(sqlalchemy.declarative_base()):
> >> > attr_a = Column(String)
> >> > attr_b = Column(Integer)
> >> > attr_c = Column(Integer)
> >> >
> >> > aggr = column_property(attr_b + attr_c IF attr_a=='go' ELSE attr_b - 
> attr_c)
> >> >
> >> > Last line is pseoudo code that requires some conditional logic. Is 
> such logic allowed inside column_property? Thanks a lot!
> >> >
> >> > --
> >> > SQLAlchemy -
> >> > The Python SQL Toolkit and Object Relational Mapper
> >> >
> >> > http://www.sqlalchemy.org/
> >> >
> >> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> >> > ---
> >> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group.
> >> > To unsubscribe from this group and stop receiving emails from it, 
> send an email to sqlalchemy+...@googlegroups.com.
> >> > To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/736f0ded-e39a-47fd-a0db-8ed33057d2a3n%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/a_-Bqhh5wY0/unsubscribe.
> >> To unsubscribe from this group and all its topics, send an email to 
> sqlalchemy+...@googlegroups.com.
> >> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeO2qgWeKeWPN-WH9LD8_Zch4jQRYeiB-WNbZrcMBrZFQ%40mail.gmail.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+...@googlegroups.com.
> > To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/CAOQAhNeTVkH5m-XXotNyHV_wBrb3Ev9SJnJpTYF2sRuJvxmOFA%40mail.gmail.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/93992e51-4b60-4093-b0fe-defd43c951e9n%40googlegroups.com.

[sqlalchemy] conditionals inside column_property

[niuji...@gmail.com]

I have a mapped class:

class Model(sqlalchemy.declarative_base()):
attr_a = Column(String)
attr_b = Column(Integer)
attr_c = Column(Integer)

aggr = column_property(attr_b + attr_c IF attr_a=='go' ELSE attr_b - 
attr_c)

Last line is pseoudo code that requires some conditional logic. Is such 
logic allowed inside column_property? Thanks a lot!

-- 
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/736f0ded-e39a-47fd-a0db-8ed33057d2a3n%40googlegroups.com.