thesayhey thesayhey - 1 year ago 75
Python Question

Issue with SQL ALCHEMY relationship (M:1)

I am having issues with pulling up a user/video in

based on relationship.

Python 2.7
CRUD method

def test_crud_operations():

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

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(''))
video = relationship('Video', backref='view_states')

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

def __init__(self, video, user):
self.completed = False = 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.user)

# 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( == 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

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)


InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type. [SQL: u'SELECT 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 = view_states.user_id AND users.username = ?)) AND (EXISTS (SELECT 1 \nFROM videos \nWHERE = view_states.video_id AND videos.videoname = ?))'] [parameters: (<User(username ='ack', firstname ='A', lastname ='cr', email='', institution='foo', residency_year='None')>, <Video(videoname='module1', length='8.0', url='')>)]

Answer Source

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)


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

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

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


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download