thesayhey thesayhey - 23 days ago 9
Python Question

Issue with SQL ALCHEMY relationship (M:1)

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

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