Thanks all for the prompt and thorough responses!
use Column subclass (class DontUseCol(Column)) then use @compiles to raise
> any time this column is accessed in a SQL context
We chose to follow this approach. It is a lightweight solution that works
well for all of the outlined use cases, without an additional performance
burden.
A few reasons why we don't want to remove the Column code from Python
altogether:
- We'd like to run our whole test suite with the same database schema we'll
have in production (including the "unused" column), in order to help catch
errors such as removing a non-nullable column from Python
- This helps us better assert that our local database schemas and
production schemas are in sync
- This makes it a little bit easier to test that migrations match the
python schema
Thanks again!
Best,
Neena
On Thursday, August 17, 2017 at 5:59:53 AM UTC-7, Mike Bayer wrote:
>
> On Wed, Aug 16, 2017 at 7:35 PM, Neena Parikh <ne...@benchling.com
> <javascript:>> wrote:
> > Hi there!
> >
> > I'm looking to create a helper or decorator function that will enable us
> to
> > "mark" a column or table as “unused”, and raise an error if that column
> or
> > table is queried for in SQL.
>
> There's a lot of ways to do this but they point towards what I would
> think is the most obvious, remove the "Column('my_column')" code from
> your Python application altogether. This column can be present in the
> database for real but not referred to in the Python application
> itself.
>
> The reason that comes up is because the next most direct way is just
> to use an object that's not a Column here, just a descriptor that
> raises an error:
>
>
> class RaiseMe(object):
> def __get__(self, obj, type):
> raise TypeError("nope")
>
>
> class MyClass(Base):
> # ...
>
> unused_col = RaiseMe()
>
> This is not too dissimilar from what you're doing but you just create
> a descriptor class to save on the typing.
>
>
> Other ways include:
>
> - use Column subclass (class DontUseCol(Column)) then use @compiles to
> raise any time this column is accessed in a SQL context
>
> - use @compiles on Column overall, check for an .info entry that the
> col should not be used, raise is column is accessed in a SQL context
> (this means the event is consulted for every column everywhere, tiny
> ding in performance)
>
> - use before_execute event, look in statement.columns for your column
> (misses things like the WHERE clause), or use traversal to find the
> column anywhere (a little more of a performance ding)
>
> - many others, the mapping can exclude the column from being mapped
> even though its present in the __table__, etc.
>
>
>
>
>
> >
> >
> > Context
> > The motivation behind this is to help us catch accidental references to
> > deleted columns in our code. We currently follow this process for
> database
> > migrations that involve dropping columns or tables:
> >
> > Remove all usages of the column in the code, except the column
> definition.
> > (We keep the column definition around since we have tests to ensure our
> > column definitions match up with our migrated database schemas.)
> > Make the column deferred
> > Wait a few days, then remove the column definition and run the database
> > migration that actually drops the column
> >
> > The main issue with this process is that if there’s any code left in
> that
> > loads in the deleted column, we run into a number of issues upon running
> the
> > migration. We’d like to introduce a helper or decorator for dropped
> > columns/tables that will trigger an error when the column/table is
> > referenced / loaded in any of the following ways:
> >
> > session.query(MyTable.my_column).all()
> > session.query(MyTable).options(undefer('*')).all()
> > my_table_instance.my_column
> > session.query(MyDroppedTable).all()
> >
> > Ideally, this would be a lightweight wrapper or decorator that could be
> > applied to any column or table — something like my_column =
> > unused(Column(…)) or an @unused decorator on a class.
> >
> >
> > Prior Work
> > We’ve looked at using hybrid_property to achieve this:
> >
> >
> > class MyTable():
> > ...
> > _my_column = deferred(Column('my_column', String(255),
> nullable=True))
> >
> > @hybrid_property
> > def my_column(self):
> > raise Exception('Unused column')
> >
> > @my_column.setter
> > def my_column(self, value):
> > raise Exception('Unused column')
> >
> >
> > What this gets us:
> >
> > Exception on my_table_instance.my_column
> > Exception on session.query(MyTable.my_column).all()
> > Exception on session.query(MyTable).options(undefer('my_column')).all()
> >
> > Issues with this approach:
> >
> > No exception on session.query(MyTable).options(undefer('*')).all()
> > Requires a fair amount of implementation overhead for each column
> > Not applicable to unused tables
> >
> >
> >
> > Any tips on how we can best use SQLAlchemy to accomplish all of the
> above?
> >
> >
> > Thank you!
> > Neena
> >
> > --
> > 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 <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
