One to many relationships?

[nochristrequired]

Hi, I love Schematics and I love SQLAlchemy, so I decided to try schemalchemy vs having two entity classes. Works great for single table.

I'm running into an issue with a one-to-many relationship though. If the record doesn't exist in both tables, it works fine. If there's an existing record, it seems to fail. I was wondering if you had experienced this in the past or may have any insight into how to fix the issue.

Here's my entity classes-

class ChildRecord(Base):
    #SQLAlchemy Table Definition
    __tablename__ = ‘child’

    _child_id = Column(‘child_id', Integer, primary_key=True)
    _parent_id = Column(‘parent_id', String, ForeignKey(‘parent.id'))
    _type = Column('type', String)
    _value = Column('value', String)

    #Schematics Definition
    type = StringType()
    value = StringType()

   #The Values don’t seem to be set properly for children
    def fixValues(self):
       self._type = self.type
       self._value = self.value
    End Fix

class ParentRecord(Base):
    #SQLAlchemy Table Definition
    __tablename__ = ‘parent’

    _id = Column('id', String, primary_key=True, nullable=False)
    _child = relationship(“ChildRecord”, collection_class=list,  
                             cascade="all, delete-orphan", passive_deletes=True)

    #Schematics Definition
    child = ListType(ModelType(ChildRecord))

Here's the stack trace (database_shared.py is my code, doing a merge operation on the parent entity class, id like to do setter injection somehow and have the operations on the entity class itself... not sure if that's possible)

File "./database_shared.py", line 41, in MergeDBRow self.session.merge(databaseObject) File "./lib/sqlalchemy/orm/session.py", line 1709, in merge load=load, _recursive=_recursive) File "./lib/sqlalchemy/orm/session.py", line 1752, in merge merged = self.query(mapper.class).get(key[1]) File "./lib/sqlalchemy/orm/query.py", line 831, in get return self._get_impl(ident, loading.load_on_ident) File "./lib/sqlalchemy/orm/query.py", line 864, in _get_impl return fallback_fn(self, key) File "./lib/sqlalchemy/orm/loading.py", line 219, in load_on_ident return q.one() File "./lib/sqlalchemy/orm/query.py", line 2718, in one ret = list(self) File "./lib/sqlalchemy/orm/loading.py", line 86, in instances util.raise_from_cause(err) File "./lib/sqlalchemy/util/compat.py", line 202, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "./lib/sqlalchemy/orm/loading.py", line 71, in instances rows = [proc(row) for row in fetch] File "./lib/sqlalchemy/orm/loading.py", line 432, in _instance state.manager.dispatch.load(state, context) File "./lib/sqlalchemy/event/attr.py", line 256, in call fn(_args, *_kw) File "./lib/sqlalchemy/orm/mapper.py", line 2860, in _event_on_load instrumenting_mapper._reconstructor(state.obj()) File "./schemalchemy.py", line 94, in _reconstructor self._set_mapped_field_values() File "./schemalchemy.py", line 108, in _set_mapped_field_values setattr(self, field_name, value) File "", line 1, in set File "./schemalchemy.py", line 46, in set setattr(instance, self.column_name, getattr(instance, self.name)) File "./lib/sqlalchemy/orm/attributes.py", line 224, in set instance_dict(instance), value, None) File "./lib/sqlalchemy/orm/attributes.py", line 1027, in set lambda adapter, i: adapter.adapt_like_to_iterable(i)) File "./lib/sqlalchemy/orm/attributes.py", line 1043, in _set_iterable new_values = list(adapter(new_collection, iterable)) File "./lib/sqlalchemy/orm/attributes.py", line 1027, in lambda adapter, i: adapter.adapt_like_to_iterable(i)) File "./lib/sqlalchemy/orm/collections.py", line 637, in adapt_like_to_iterable given, wanted)) TypeError: Incompatible collection type: None is not list-like

Any help would be greatly appreciated! Thanks for contributing this library. ;)

[lkraider]

You should not map the relationship to a schematics type.

Here is a working test case with your model: https://gist.github.com/lkraider/42efce9f193afdba1ea09b0a32951eb0

Let me know if this works for you!

[nochristrequired]

Thanks lkraider, and thanks again for contributing schemalchemy. I will try the tests. One reason I had defined the relationship using a schematics type was because I am loading the object in from json.loads through schematics like this entityType(json.loads(self._messageData) where entityType is the Parent model. I will use the test case to see if this is possible. I really appreciate your help.

[lkraider]

Please take note I just updated the gist. I am using a serializable type to output the children when serializing the parent. My guess is that using a serializable setter would work for importing, I have not tested though.

Edit: serializable setters are something not in this version of schematics, I have a branch with that feature here: https://github.com/nkey/schematics/tree/serializable-setter diff here: https://github.com/schematics/schematics/compare/76138625719bf202c461a687a8750a709168f3b7...nKey:serializable-setter

[nochristrequired]

Thanks! To be honest, I am using schemalchemy with the latest versions of Schematics and SQLAlchemy and not the versions tagged in the repository. Now that I think of it, I should have mentioned that in my original post. I guess that feature was introduced after your initial work on schemalchemy.

[lkraider]

Right, it should work with the new versions, although I have not tested them. Let me know if you see something that look like incompatibilities.

[nochristrequired]

I merged serializable setters into my codebase, still working on getting it working. Manually adding children works and children are fetched from the database properly and placed into the list - this is a great improvement. However, json.loads still yields a "None" list of children - trying to figure that one out.

[lkraider]

The problem is that the import loop does not touch the serializable setters on init (it iterates Model._fields, while they are in Model._serializables).

I got it to work by modifying the loop in schematics like so:

-        for field_name, field in self._fields.iteritems():
+        for field_name, field in itertools.chain(
+            self._fields.iteritems(),
+            ((s[0], s[1].type) for s in self._serializables.iteritems())
+        ):

To be fair I tested in my own schematics branch, so I am not sure how it applies to the version you are running. Hopefully it helps.

[nochristrequired]

For the current schematics master, it looks like they've moved a lot of the logic to transforms.py and it's more complicated. Working off your code for your dev branch, I've done something like this:

for field_name, field in iteritems(cls._fields): for field_name, field in itertools.chain(iteritems(cls._fields), ((s[0], s[1].type) for s in cls._serializables.iteritems())):

If I look a couple layers up in the processing, I see my data for the child object being returned, but still not making it into the child object. I'm still trying to figure it out - it's probably pretty close.

[nochristrequired]

Actually, by the looks of it, and as far as schematics is concerned, my data made it into the model... So the disconnect is higher even for why it's not making it into the db.

[nochristrequired]

Finally, what I'm seeing is that my child object is in the _data dictonary, but with no reference to it in the parent._child (the SQLAlchemy attribute in the parent object - which is None / empty.)

Also, it appears that parent._dict['child'][x]._value and parent._dict['child'][x]._type (the underscore attributes) are not set properly, but .value and .type are, so if I correct that, and then manually set parent._child = parent._data['child'], all is good.

Edit I'll write a test case this weekend showing this

[lkraider]

I hacked something together based on the https://github.com/schematics/schematics/tree/aa0405ebcd44ea71b63b384c48d2a9e8149a7202 development branch that forces the import loop to touch the serializable setter: 0001-TEMP-SERIALIZABLE.txt

Note: don't use this in any real code, it is completely unverified for correctness, it WILL BREAK your project and destroy all you hold dear without discernment, kittens and puppies alike.

[webmaven]

Is this still an open issue?

[lkraider]

I'll have to work on getting serializable setters into schematics for this issue to begin to be fixed.

[nochristrequired]

Hi, I still have this in my backlog to merge your patch. Modifications to transform.py mostly got it working and I suspect your patchset is a more complete fix. Thank you so much for helping.

[webmaven]

Progress?

[lkraider]

Still WIP.

Can track this issue in schematics: https://github.com/schematics/schematics/issues/446

[nochristrequired]

Thanks Paul - I will check it out as soon as I have time and provide feedback. For now, I had to switch to marshmallow / marshmallow-sqlalchemy which was far less painful than trying to making the current version of schematics work. I would help more, but I am in the middle of a major project.

On Mon, Oct 24, 2016 at 9:14 AM, Paul Eipper notifications@github.com wrote:

Still WIP.

Can track this issue in schematics: schematics/schematics#446 https://github.com/schematics/schematics/issues/446

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/schematics/schemalchemy/issues/1#issuecomment-255787728, or mute the thread https://github.com/notifications/unsubscribe-auth/AGsZWgL0cErPxYTaHUvR7ZqyDvrQOTt4ks5q3NlUgaJpZM4I7GGb .

[webmaven]

@nochristrequired, I would be very interested in your take on the tradeoffs between the two (excluding the pain of getting schematics to work, that is)?

[lkraider]

Schematics changes are ready in https://github.com/schematics/schematics/pull/466