thesayhey thesayhey - 2 months ago 18
Python Question

updating boolean (using CRUD) in SQL Alchemy

I am trying to update the boolean statement to

from the default
. The idea here is that a user watches a video and the update method then updates False to True.

Right now the program crashes at the test line:
api.update_view_state('ack', 'module1')
. I think it is due to the attempt at changing/updating the boolean

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 create_view_state(self, video, user):
new_view_state = View_State(video, user)
print 'new view state created: ', new_view_state
return new_view_state

def retrieve_view_state(self, something_unique):
if isinstance(something_unique, int):
print 'retrieve view state id', something_unique
return self.session.query(View_State).\
filter( == something_unique).one()
elif isinstance(something_unique, basestring):
print 'retrieve user view state:', something_unique
q = self.session.query(View_State).\
filter(View_State.user.has(User.username == something_unique)).first()
print 'result from retrieve user view state:', q
return q
isinstance(something_unique, View_State), 'this is an object'
print 'retrieve something_unique is an object', something_unique
return something_unique

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 create_view_state(self, videoname, username):
video = self.retrieve_video(videoname)
user = self.retrieve_user(username=username)
return super(ConvenienceAPI, self).create_view_state(video, user)

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='')>)]


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()