Issue with SQL ALCHEMY relationship (M:1)

Question

I am having issues with pulling up a user/video in view_state based on relationship.

Using Python 2.7 SQLALCHEMY and CRUD method

Test.py

def test_crud_operations():

    api = ConvenienceAPI()
    api.create_view_state('module1', 'ack')
    api.retrieve_view_state('ack')
    api.update_view_state('ack', 'module1') 

Code:

#base.py
    class View_State(Base):
        __tablename__ = 'view_states'

        id = Column(Integer, primary_key=True)
        timestamp = Column(DateTime, default=datetime.utcnow)
        time_update = Column(DateTime, onupdate=datetime.utcnow)
        completed = Column(Boolean, default=False) #have to set default

        video_id = Column(Integer, ForeignKey('videos.id'))
        video = relationship('Video', backref='view_states')

        user_id = Column(Integer, ForeignKey('users.id'))
        user = relationship('User', backref='view_states')

        def __init__(self, video, user):
            self.completed = False
            self.video = video
            self.user = user

        def __repr__(self):
            return "<View_State(timestamp='%s', time_update='%s', completed='%s', video='%s', user='%s')>" % (self.timestamp, self.time_update, self.completed, self.video, self.user)

    #object.py

        # View State CRUD

            def update_view_state(self, username, videoname):
                #update Boolean completed status to 'complete = True'
                update_completed = self.session.query(View_State).\
                filter(View_State.user.has(User.username == username)).\
                filter(View_State.video.has(Video.videoname == videoname)).one()
                print 'retrieved from update complete: ', update_completed
                if update_completed:
                    completed = True
                    temp = update_completed.completed
                    print 'changed status: ', temp
                    return update_completed
                    self.session.commit()

   #convenience.py 

        def update_view_state(self, username, videoname):
            user = self.retrieve_user(username=username)
            video = self.retrieve_video(videoname)
            return super(ConvenienceAPI, self).update_view_state(user, video)

Traceback:

InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type. [SQL: u'SELECT view_states.id AS view_states_id, view_states.timestamp AS view_states_timestamp, view_states.time_update AS view_states_time_update, view_states.completed AS view_states_completed, view_states.video_id AS view_states_video_id, view_states.user_id AS view_states_user_id \nFROM view_states \nWHERE (EXISTS (SELECT 1 \nFROM users \nWHERE users.id = view_states.user_id AND users.username = ?)) AND (EXISTS (SELECT 1 \nFROM videos \nWHERE videos.id = view_states.video_id AND videos.videoname = ?))'] [parameters: (<User(username ='ack', firstname ='A', lastname ='cr', email='a@gmail.org', institution='foo', residency_year='None')>, <Video(videoname='module1', length='8.0', url='https://vimeo.com/138326103')>)]

Answer 1

If you look closely to the traceback message, you are not sending an username but a class as a parameter but try matching it to a string. Which is actually what your program does. you first select user class and video class and pass these classes to update_view_state.

What would happen if you changed this line:

return super(ConvenienceAPI, self).update_view_state(user, video)

to

return super(ConvenienceAPI, self).update_view_state(user.username, 
                                                     video.videoname)

Or you can change your update_view_state to operate on the relationship instead:

filter(View_State.user.has(user == username)).\
filter(View_State.video.has(video == videoname)).one()

Hannu