Re: [sqlalchemy] Raising on usage of attribute in SQL queries
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 > 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 b
Re: [sqlalchemy] Raising on usage of attribute in SQL queries
On Wed, Aug 16, 2017 at 7:35 PM, Neena Parikh 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+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
Re: [sqlalchemy] Raising on usage of attribute in SQL queries
On Thu, Aug 17, 2017 at 12:35 AM, Neena Parikh 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.
>
>
> 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?
>
To catch the "undefer('*')" case, you could perhaps change the column
type to something based on a TypeDecorator that raises an exception in
process_bind_param and process_result_value:
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator
By "implementation overhead", do you mean the amount of typing you
have to do each time you disable a column (creating the hybrid
properties)? If so, you might be able to use the events system to
create those properties automatically. For example, you could try
listening for the "after_configured" mapper event, then inspect the
class's properties. If you find any disabled columns, you could then
attach corresponding hybrid properties that raise exceptions.
Hope that helps,
Simon
--
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.
