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)
