aborting insert/update from within before_insert/update events?

[Gerald Thibault]

I am working with 2 models, a "Location" model, and a "Phone" model. There is a one-to-many relationship between them.

When a phone number is submitted, I need to format it using the phonenumbers modules, which requires a country code, which exists on the Location object. So the formatting can only happen after the flush(), as I need to have the location_id populated, so I can grab the country code from the parent Location. If the formatting of the phone number fails, I want the entire object eliminated and not written to the db.

This is the current (nonworking) code I am using.

@event.listens_for(Phone, 'before_insert', raw=True)

@event.listens_for(Phone, 'before_update', raw=True)

def save_phone(mapper, connection, target):

    phone = target.obj()

    country = object_session(phone) \

        .query(Location) \

        .get(phone.location_id) \

        .country

    try:

        number = phonenumbers.parse(phone.number, country)

        phone.number = phonenumbers.format_number(number,

            phonenumbers.PhoneNumberFormat.E164)

    except:

        print 'failed on phone number %s' % phone.number

I'm not sure how to abort the insertion/update of the object. Is this even possible? Is there something I can put in the except section to cancel the pending commit of the object in question?

[Michael Bayer]

On Sep 18, 2012, at 6:28 PM, Gerald Thibault wrote:

> I am working with 2 models, a "Location" model, and a "Phone" model. There is a one-to-many relationship between them.
>
> When a phone number is submitted, I need to format it using the phonenumbers modules, which requires a country code, which exists on the Location object. So the formatting can only happen after the flush(), as I need to have the location_id populated, so I can grab the country code from the parent Location. If the formatting of the phone number fails, I want the entire object eliminated and not written to the db.

At some point, the Phone is being associated with a Location object in memory, and this would be independent of whether or not location_id is present. The location_id can only be set, assuming this is relationship() mechanics, if this is the case. So you shouldn't need a flush() for this to happen, and you can perform this validation before a flush plan is established.

Otherwise if location_id is populated by some other means, that would point to an area where you'd want to get Location objects present in memory ahead of time, rather than relying upon primary keys alone.

This might not be enough to solve your issue so feel free to add some detail how location_id is coming into being here, such that the Location isn't nearby.

[Gerald Thibault]

We're using a modified version of tastypie, with all the django-specific stuff modified to work with sqlalchemy. One of the things this offers is the ability to submit nested resources to API endpoints, and have it recursively build them by creating the parents, then appending the children to the relationship as they are created. In this case, Location is built before the phones, and location_id is set (i think) when the Phone is added to the Location.phones relation.

It is also possible to submit directly to the Phones endpoint and create a phone number for an existing location which one already has the id for, in which case the Location does not exist until the event listener (from my first post) uses the location_id to pull up the Location, from which it extracts the country code.

Currently, the country code extraction is working perfectly, and I am able to see which records fail. I am unable to stop those records from being written to the db, that is where my troubles are. How can I stop a record from being written to the db from within the before_insert event listener?

[Michael Bayer]

On Sep 18, 2012, at 8:04 PM, Gerald Thibault wrote:

We're using a modified version of tastypie, with all the django-specific stuff modified to work with sqlalchemy. One of the things this offers is the ability to submit nested resources to API endpoints, and have it recursively build them by creating the parents, then appending the children to the relationship as they are created. In this case, Location is built before the phones, and location_id is set (i think) when the Phone is added to the Location.phones relation.

It is also possible to submit directly to the Phones endpoint and create a phone number for an existing location which one already has the id for, in which case the Location does not exist until the event listener (from my first post) uses the location_id to pull up the Location, from which it extracts the country code.

Each of those descriptions seems to indicate that a Location object is available in memory and there's nothing special about a flush(), there's no database-side triggers or defaults you're waiting on, so this issue needs to be addressed before the flush proceeds, the latest would be in the before_flush() event.

Currently, the country code extraction is working perfectly, and I am able to see which records fail. I am unable to stop those records from being written to the db, that is where my troubles are. How can I stop a record from being written to the db from within the before_insert event listener?

You cannot, except for raising an exception which would abort the whole transaction, as before_insert() occurs within the core of the flush process well after the flush plan has been finalized.    before_flush() is provided for pre-flush modifications to the flush plan.

before_insert() is not generally necessary, people frequently want to take advantage of the fact that it provides a built-in iteration of objects to be inserted, but this iteration can be done for the purposes of affecting the flush plan ahead of time within the before_flush() event, by iterating session.new.  An object detected at this stage can be omitted from the insert by calling session.expunge(obj).    

On Tuesday, September 18, 2012 4:29:43 PM UTC-7, Michael Bayer wrote:

On Sep 18, 2012, at 6:28 PM, Gerald Thibault wrote:

> I am working with 2 models, a "Location" model, and a "Phone" model. There is a one-to-many relationship between them.
>
> When a phone number is submitted, I need to format it using the phonenumbers modules, which requires a country code, which exists on the Location object. So the formatting can only happen after the flush(), as I need to have the location_id populated, so I can grab the country code from the parent Location. If the formatting of the phone number fails, I want the entire object eliminated and not written to the db.

At some point, the Phone is being associated with a Location object in memory, and this would be independent of whether or not location_id is present.    The location_id can only be set, assuming this is relationship() mechanics, if this is the case.   So you shouldn't need a flush() for this to happen, and you can perform this validation before a flush plan is established.

Otherwise if location_id is populated by some other means, that would point to an area where you'd want to get Location objects present in memory ahead of time, rather than relying upon primary keys alone.

This might not be enough to solve your issue so feel free to add some detail how location_id is coming into being here, such that the Location isn't nearby.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mElFMIjDpsEJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Gerald Thibault]

On Tuesday, September 18, 2012 8:41:15 PM UTC-7, Michael Bayer wrote:

On Sep 18, 2012, at 8:04 PM, Gerald Thibault wrote:

before_insert() is not generally necessary, people frequently want to take advantage of the fact that it provides a built-in iteration of objects to be inserted, but this iteration can be done for the purposes of affecting the flush plan ahead of time within the before_flush() event, by iterating session.new.  An object detected at this stage can be omitted from the insert by calling session.expunge(obj).    

This did the trick. This is the current working event listener:

@event.listens_for(orm.sessionmaker(), 'before_flush')

def normalize_phonenumbers(session, flush_context, instances):

    for obj in session.new:

        if isinstance(obj, Phone):

            if obj.location:

                # we already have the location in the session

                country = obj.location.country

            else:

                country = session.query(Location) \

                    .get(obj.location_id) \

                    .country

            try:

                number = phonenumbers.parse(obj.number, country)

                obj.number = phonenumbers.format_number(number,

                    phonenumbers.PhoneNumberFormat.E164)

            except:

                session.expunge(obj)

I have one more question. When I do the following:

test = BusinessPhone(type='fax', number='2068857529', location_id=1)

session.add(test)

session.commit()

At what point is the 'location' relationship populated? I tried this and it failed on this line, due to the relation not being populated:

country = obj.location.country

which necessitated adding the conditional manual load of Location. Am I handling this in an appropriate way?