Prevent writes to specific tables (read-only tables)

Question

Is there a way to mark tables as read-only when using Pyramid with SQLAlchemy?

Some brief background: I am working on an app with an existing database which is very complicated. My app adds a few new tables to this database, but updating (update, insert, delete) other tables is unsafe because the schema is not well understood (and isn't documented).

My basic aim is to fail-fast before any unsafe db updates so the offending code can be found and removed, so I and future developers don't cause any data issues with this app.

My naive approach: add a pyramid_tm commit veto hook that checks objects to be updated (new, dirty, and deleted in the db session) and vetos if there are any read-only tables.

Is there an existing mechanism to handle this? Or a better approach?

score 4 · Answer 1 · answered Aug 05 '13 at 21:45

If you're using the "declarative" approach, you can try making the classes themselves "read-only":

class MyBase(object):
    """
    This class is a superclass of SA-generated Base class,
    which in turn is the superclass of all db-aware classes
    so we can define common functions here
    """

    def __setattr__(self, name, value):
        """
        Raise an exception if attempting to assign to an atribute of a "read-only" object
        Transient attributes need to be prefixed with "_t_"
        """
        if (getattr(self, '__read_only__', False)
          and name != "_sa_instance_state"
          and not name.startswith("_t_")):
            raise ValueError("Trying to assign to %s of a read-only object %s" % (name, self))
        super(MyBase, self).__setattr__(name, value)



Base = declarative_base(cls=MyBase)

Similarly you can try to override __init__ method to prevent the objects from being instantiated.

However, solving this problem at the application level feels a bit fragile - I would just look at creating a special user in the database for your app to connect with, which would save limited permissions on the tables you want to keep intact.

Even if this a realy old answer, the concept is still valid. However, instead of overwritting the __init__ method, i needed to create a custom declerative_constructor. See https://stackoverflow.com/a/20730249/1211607 — jrast, Oct 18 '19 at 09:51
The purpose of the custom --init-- in my answer was to raise an exception when you try to instantiate the object to make sure you can't create new instances of the class and are only limited to the ones which SqlAlchemy returns from the queries. — Sergey, Oct 18 '19 at 11:03
Yea, I understand. But somehow this didn't work in my case if I implemented the check in the `MyBase.__init__` method. — jrast, Oct 18 '19 at 11:16

Prevent writes to specific tables (read-only tables)

1 Answers1