def delete(self): conn = connect_db() cur = conn.cursor() cur.execute("DELETE FROM dba WHERE data->>'id' = %s",(self.data.get('id'),)) conn.commit() cur.close() return True
def search(self, searchstring, images_only): conn = connect_db() cur = conn.cursor() if images_only == "true": cur.execute( """SELECT data from dba WHERE to_tsvector('english',data::text) @@ to_tsquery('english',%s) AND data->>'id' IN (SELECT data->>'tfcase' from dba WHERE data->>'type' = 'tfimage') ORDER BY data->>'date-updated' DESC """, (searchstring, )) else: cur.execute( """SELECT data from dba WHERE to_tsvector('english',data::text) @@ to_tsquery('english',%s) ORDER BY data->>'date-updated' DESC """, (searchstring, )) # cur.execute("SELECT data from dba WHERE data->>'type' = 'tfcase' and data::text LIKE %s",('%'+searchstring+'%',)) data = cur.fetchall() conn.commit() cur.close() conn.close() mylist = [] for case in data: case = case[0] if not case.get('deleted'): mylist.append(case)
def update(self): datastring = json.dumps(self.data) conn = connect_db() cur = conn.cursor() cur.execute("UPDATE dba SET data = %s WHERE data->>'id' = %s",(datastring,self.data.get('id'))) conn.commit() cur.close() return True
def read(self,imageid): conn = connect_db() cur = conn.cursor() cur.execute("SELECT data from dba WHERE data->>'id' = %s",(imageid,)) data = cur.fetchone()[0] conn.commit() cur.close() self.data = data return data
def create(self): datastring = json.dumps(self.data) conn = connect_db() cur = conn.cursor() cur.execute("INSERT INTO dba (data) VALUES (%s)", (datastring,)) conn.commit() cur.close() conn.close() return self.data
def update(self, data): conn = connect_db() cur = conn.cursor() case_id = data['id'] data['date-updated'] = datetime.datetime.now().isoformat() datastring = json.dumps(data) cur.execute("UPDATE dba SET data = %s WHERE data->>'id' = %s", (datastring, case_id)) conn.commit() cur.close() conn.close() return data
def delete(self, case_id): conn = connect_db() cur = conn.cursor() cur.execute("SELECT data from dba WHERE data->>'id' = %s", (case_id, )) data = cur.fetchone()[0] print(data) data['deleted'] = True datastring = json.dumps(data) cur.execute("UPDATE dba SET data = %s WHERE data->>'id' = %s", (datastring, case_id)) conn.commit() cur.close() conn.close()
def create(self, data): case_id = str(uuid.uuid4()) data['id'] = case_id data['type'] = 'tfcase' data['date-created'] = datetime.datetime.now().isoformat() datastring = json.dumps(data) conn = connect_db() cur = conn.cursor() cur.execute("INSERT INTO dba VALUES (%s)", (datastring, )) conn.commit() cur.close() conn.close() self.data = data return data
def searchimagesbytfcase(self,caseid): conn=connect_db() cur = conn.cursor() cur.execute("SELECT data from dba WHERE data->>'tfcase' = %s",(caseid,)) data = cur.fetchall() if not data: return [] conn.commit() cur.close() conn.close() mylist = [] for image in data: mylist.append(image[0]) return sorted(mylist, key=lambda case: case['date-created'], reverse=True)
def list_all_cases(self): conn = connect_db() cur = conn.cursor() cur.execute( "SELECT data FROM dba WHERE data->>'type' = 'tfcase' ORDER BY data->>'date-updated' DESC" ) data = cur.fetchall() conn.commit() cur.close() conn.close() mylist = [] for case in data: case = case[0] if not case.get('deleted'): mylist.append(case)