normally I'd ask what are the conditions under which this row might be
deleted.
For a truly open-ended rule that will block any delete() from hitting
it, you'd need to implement a Core event like before_execute().
However, as the rule requires looking in the row to see if this field is
populated, that means you either need to SELECT ahead of time, or you
can try altering the statement to append a "WHERE reg_number IS NULL" to
the delete() construct. Depending on how this table is mapped and what
DB driver you're using, the ORM may be able to also detect that "zero
rows" were deleted when hitting this rule where it will then raise an error.
Below will work but I don't think using a trigger is a bad idea here.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
from sqlalchemy.sql.dml import Delete
import warnings
warnings.filterwarnings("error", "DELETE statement on table.*")
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
dont_delete_if_nonnull = Column(String(50))
e = create_engine("sqlite://", echo=True)
@event.listens_for(e, "before_execute", retval=True)
def before_execute(conn, clauseelement, multiparams, params):
if isinstance(clauseelement, Delete) and clauseelement.table.name
== 'a':
clauseelement = clauseelement.where(
clauseelement.table.c.dont_delete_if_nonnull == None)
return clauseelement, multiparams, params
Base.metadata.create_all(e)
s = Session(e)
a1, a2, a3 = A(id=1), A(id=2), A(id=3, dont_delete_if_nonnull='hi')
s.add_all([a1, a2, a3])
s.commit()
s.delete(a1)
s.commit()
s.delete(a2)
s.delete(a3)
s.commit()
On 02/09/2017 02:00 PM, Роберт Шотланд wrote:
I have a table 'item' that contains a nullable 'reg_number' field that,
if it contains a non-null value, the row must not be deleted. I would
normally implement a SQL delete-trigger to do this, except we would
prefer to maintain a pure SQLAlchemy environment.What would be the best
way of implement this using the ORM?
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
