Hi Simon, thank you very much for your help (again!), and yes, that's how I'm using the mapping :).
I'm now getting an SQL error, I think because the user is not being pulled automatically through when trying to add the ArkUserContactGUID: OperationalError: (OperationalError) (1364, "Field 'user_id' doesn't have a default value") 'INSERT INTO `user_contact_UID_table` (contact_id, uid) VALUES (%s, %s)' (2L, 'http://www.google.com/m8/feeds/contacts/jules%40kettlestudio.co.uk/base/7c5456c108b2111b') File 'C:\\ark\\ark\\controllers\\contacts.py', line 368 in initial_sync contact_sync.initial_sync() File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 121 in initial_sync self.add_contact_to_google(contact) File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 263 in add_contact_to_google meta.Session.commit() File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\scoping.py', line 113 in do return getattr(self.registry(), name)(*args, **kwargs) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\session.py', line 617 in commit self.transaction.commit() File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\session.py', line 293 in commit self._prepare_impl() File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\session.py', line 277 in _prepare_impl self.session.flush() File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\session.py', line 1473 in flush self._flush(objects) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\session.py', line 1542 in _flush flush_context.execute() File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\unitofwork.py', line 327 in execute rec.execute(self) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\unitofwork.py', line 471 in execute uow File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\orm\\mapper.py', line 2163 in _save_obj execute(statement, params) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\engine\\base.py', line 1358 in execute params) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\engine\\base.py', line 1491 in _execute_clauseelement compiled_sql, distilled_params File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\engine\\base.py', line 1599 in _execute_context context) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\engine\\base.py', line 1592 in _execute_context context) File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1-py2.6.egg\\sqlalchemy\\engine\\default.py', line 325 in do_execute cursor.execute(statement, parameters) File 'C:\\ark\\env_x64\\lib\\site-packages\\MySQLdb\\cursors.py', line 173 in execute self.errorhandler(self, exc, value) File 'C:\\ark\\env_x64\\lib\\site-packages\\MySQLdb\\connections.py', line 36 in defaulterrorhandler raise errorclass, errorvalue OperationalError: (OperationalError) (1364, "Field 'user_id' doesn't have a default value") 'INSERT INTO `user_contact_UID_table` (contact_id, uid) VALUES (%s, %s)' (2L, 'http://www.google.com/m8/feeds/contacts/jules%40kettlestudio.co.uk/base/7c5456c108b2111b') I think this is because the contacts object uses a many to many relationship (a contact can belong to any or all users), code as follows: # ArkContact - clientprojectshot module orm.mapper(ArkContact, contacts_table, properties={ 'notes': orm.relation(ArkNote, secondary=contact_notes_table, backref='contacts', single_parent=True, cascade="all, delete, delete-orphan"), 'users': orm.relation(ArkUser, secondary=user_contact_table, backref='contacts'), 'google_UID': orm.relation(ArkUserContactGUID, cascade="all, delete", uselist=False, backref='user') }) I'm thinking I should join between the user and ArkUserContactGUID somehow, but don't know how... Many thanks, Jules On Thu, Jun 16, 2011 at 2:04 PM, King Simon-NFHD78 <simon.k...@motorolasolutions.com> wrote: >> -----Original Message----- >> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] >> On Behalf Of Jules Stevenson >> Sent: 16 June 2011 08:44 >> To: sqlalchemy@googlegroups.com >> Subject: [sqlalchemy] mapping a class linked with two other classes >> (AttributeError: 'str' object has no attribute '_sa_instance_state') >> >> Hi List, >> >> I have a user class, a contact class, and a googleID class. >> >> the contact class has can have a googleID per user in the system. I'm >> trying to map it out as follows: >> >> # ArkContact - clientprojectshot module >> orm.mapper(ArkContact, contacts_table, properties={ >> 'notes': orm.relation(ArkNote, >> secondary=contact_notes_table, >> backref='contacts', >> single_parent=True, >> cascade="all, delete, delete-orphan"), >> 'users': orm.relation(ArkUser, >> secondary=user_contact_table, >> backref='contacts'), >> 'google_UID': orm.relation(ArkUserContactGUID, >> cascade="all, delete", >> backref='user') >> }) >> >> #user contact google_GUID >> user_contact_UID = sa.Table('user_contact_UID_table', meta.metadata, >> sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'), >> primary_key=True), >> sa.Column('contact_id', sa.types.Integer, >> sa.ForeignKey('contacts.id'), primary_key=True), >> sa.Column('google_UID', sa.types.String(length = 1024)) >> ) >> >> class ArkUserContactGUID(object): >> def __init__(self): >> pass >> >> orm.mapper(ArkUserContactGUID, user_contact_UID) >> >> This raises two issues, the first is that an instrumented list is >> returned for the google_UID paramter on the contact object, whereas >> there should only ever be one (since as an operator there is only >> ever >> one user signed in - you). >> > > For one-to-one relationships, you should supply "uselist=False" to your > relationship: > > http://www.sqlalchemy.org/docs/orm/relationships.html#one-to-one > > >> The second is it outright errors :), presumably because my mapping is >> off: >> >> File 'C:\\ark\\ark\\controllers\\contacts.py', line 368 in >> initial_sync >> contact_sync.initial_sync() >> File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 121 in initial_sync >> self.add_contact_to_google(contact) >> File 'C:\\ark\\ark\\arkTools\\arkGoogle.py', line 259 in >> add_contact_to_google >> data.google_UID.append(entry.get_id()) >> File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1- >> py2.6.egg\\sqlalchemy\\orm\\collections.py', >> line 952 in append >> item = __set(self, item, _sa_initiator) >> File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1- >> py2.6.egg\\sqlalchemy\\orm\\collections.py', >> line 927 in __set >> item = getattr(executor, 'fire_append_event')(item, _sa_initiator) >> File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1- >> py2.6.egg\\sqlalchemy\\orm\\collections.py', >> line 618 in fire_append_event >> item, initiator) >> File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1- >> py2.6.egg\\sqlalchemy\\orm\\attributes.py', >> line 741 in fire_append_event >> value = fn(state, value, initiator or self) >> File 'C:\\ark\\env_x64\\lib\\site-packages\\sqlalchemy-0.7.1- >> py2.6.egg\\sqlalchemy\\orm\\unitofwork.py', >> line 35 in append >> item_state = attributes.instance_state(item) >> AttributeError: 'str' object has no attribute '_sa_instance_state' >> >> Many thanks for any help! >> >> Jules > > You're passing a string (presumably the result of entry.get_id()) where > SA is expecting an instance of a mapped class. I haven't looked at your > mapping in detail, but rather than this: > > data.google_UID.append(entry.get_id()) > > you probably want something like this: > > obj = ArkUserContactGUID(google_UID=entry.get_id()) > data.google_UID.append(obj) > > (If I've misunderstood your mapping, these class names are probably > wrong) > > Hope that helps, > > Simon > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
