Re: [sqlalchemy] overriding/ignoring specific database records

[Jonathan Vanasco]

On Tuesday, March 20, 2018 at 11:29:18 AM UTC-4, Mike Bayer wrote:
>
>
> if you just need a different name, rename it: 
>
> my_attr = Column('id_foo__context_a', Integer, ...) 
>
>
>
SqlAlchemy does everything I need.  Thanks Mike!

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

Re: [sqlalchemy] overriding/ignoring specific database records

[Mike Bayer]

On Tue, Mar 20, 2018 at 3:02 AM, Jonathan Vanasco  wrote:
> this is silly - I realized I can just use a flag to run a different
> `orm.relationship` on the Admin and Public views:
>
> if ADMIN:
>  records  = orm.relationship(a.id=b.id)
> else:
>  records  = orm.relationship(and(a.id=b.id, b.id<1000))
>
> Is there any magical SqlAlchemy feature that would let me define
> setters/getters for a table's python column, while still keeping the
> underlying name of the column?
>
> to explain this poorly worded question...
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
> id_foo__context_a = Column(Integer, ForeignKey("foo.id"))
> id_foo__context_b = Column(Integer, ForeignKey("foo.id"))
>
> i'd like to replace `id_foo__context_a` with a property or setter/getter,
> which can still access/manipulate a stored value for a database field named
> `id_foo__context_a`.  usually i rename the column or field to handle this,
> but in this case I can't.

if you just need a different name, rename it:

my_attr = Column('id_foo__context_a', Integer, ...)




>
> Perhaps there is an undocumented way to alter the backing db column of a
> sqlalchemy column, something like this...
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
> _id_foo__context_a = Column(Integer, ForeignKey("foo.id"),
> DatabaseColumn='id_foo__context_a')
> id_foo__context_b = Column(Integer, ForeignKey("foo.id"))
>
>  @property
> def id_foo__context_a(self):
>  return self._id_foo__context_a
>
>
> --
> 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 sqlalchemy@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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] overriding/ignoring specific database records

[Jonathan Vanasco]

this is silly - I realized I can just use a flag to run a different 
`orm.relationship` on the Admin and Public views:

if ADMIN:
 records  = orm.relationship(a.id=b.id)
else:
 records  = orm.relationship(and(a.id=b.id, b.id<1000))

Is there any magical SqlAlchemy feature that would let me define 
setters/getters for a table's python column, while still keeping the 
underlying name of the column?

to explain this poorly worded question...

class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
id_foo__context_a = Column(Integer, ForeignKey("foo.id"))
id_foo__context_b = Column(Integer, ForeignKey("foo.id"))

i'd like to replace `id_foo__context_a` with a property or setter/getter, 
which can still access/manipulate a stored value for a database field named 
`id_foo__context_a`.  usually i rename the column or field to handle this, 
but in this case I can't.

Perhaps there is an undocumented way to alter the backing db column of a 
sqlalchemy column, something like this...

class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
_id_foo__context_a = Column(Integer, ForeignKey("foo.id"), 
DatabaseColumn='id_foo__context_a')
id_foo__context_b = Column(Integer, ForeignKey("foo.id"))

 @property
def id_foo__context_a(self):
 return self._id_foo__context_a


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

Re: [sqlalchemy] overriding/ignoring specific database records

[Mike Bayer]

since you need the relationship part also did you try a mapping to a
select() statement?The downside of that would be that your queries
would always be like "SELECT ... FROM (SELECT .. FROM..)". The
other way is to alter the query.   You can probably do pretty well on
this with a before_compile event:
http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile
 shows pretty much exactly how you'd do this (e.g. look in the
entities for the one that has special filter criteria, then add that
criteria).

On Sat, Mar 17, 2018 at 7:47 PM, Jonathan Vanasco  wrote:
> I was wondering if something were doable with SqlAlchemy in a decently
> performant manner that can be maintained cleanly.  I'm open to any
> strategies that people can suggest.
>
> We have a handful of reserved internal "system" or "control" records in our
> database. Depending on the table, anywhere from 10k to 1MM ids are reserved
> (in practice, only 10 are actually used).
>
> I'd like to exclude these items from showing up in relationships (and
> possibly even from appearing on a record as an id field).  They are only
> needed in our Admin interface (runs as a separate app, so it can be
> configured differently), but are a hindrance on the Public app.
>
> Here's a typical use case:
>
> One of our tables represents a content-graph of spidered web pages. During
> the course of development, we realized certain data wasn't being attributed
> to the right pages due to issues with redirect detection in a specific
> scenario.  Consequently, a chunk of records created before we addressed this
> case now list their 'redirected_from' id as the control record for
> "Untracked Redirect Chain".  (Due to the size of the DB and several other
> constraints, adding in a new field to track errors/data wasn't an option).
>
> That leaves me needing to hide this connection in two places on our
> clientside app:
> * don't load it into a sqlalchemy.orm.relationship
> * discard it when looking at the raw column ids (when populating the
> read-through cache)
>
> To handle this, I'm doing the latter manually and using a 'display_'
> prefixed property to wrap the real relationship - but this is ugly.
>
> I'm hoping for some suggestions from the larger community.
>
> --
> 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 sqlalchemy@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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] overriding/ignoring specific database records

[Jonathan Vanasco]

I was wondering if something were doable with SqlAlchemy in a decently 
performant manner that can be maintained cleanly.  I'm open to any 
strategies that people can suggest.

We have a handful of reserved internal "system" or "control" records in our 
database. Depending on the table, anywhere from 10k to 1MM ids are reserved 
(in practice, only 10 are actually used).

I'd like to exclude these items from showing up in relationships (and 
possibly even from appearing on a record as an id field).  They are only 
needed in our Admin interface (runs as a separate app, so it can be 
configured differently), but are a hindrance on the Public app.

Here's a typical use case:

One of our tables represents a content-graph of spidered web pages. During 
the course of development, we realized certain data wasn't being attributed 
to the right pages due to issues with redirect detection in a specific 
scenario.  Consequently, a chunk of records created before we addressed 
this case now list their 'redirected_from' id as the control record for 
"Untracked Redirect Chain".  (Due to the size of the DB and several other 
constraints, adding in a new field to track errors/data wasn't an option).

That leaves me needing to hide this connection in two places on our 
clientside app:
* don't load it into a sqlalchemy.orm.relationship
* discard it when looking at the raw column ids (when populating the 
read-through cache)

To handle this, I'm doing the latter manually and using a 'display_' 
prefixed property to wrap the real relationship - but this is ugly.

I'm hoping for some suggestions from the larger community.

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