def unparse_row(self,record):
# convert an object to a row
# default is simply columns named after fields, with SQL NULL (Python None) for any missing values
FIELDS = self.FIELDS
row = dict(zip(FIELDS,[None for _ in range(len(FIELDS))]))
row.update(dict_slice(record,FIELDS))
return row
def create_annotations(self,annotations):
tuples = []
for d in annotations:
fields = 'image,scope,category,geometry,annotator,timestamp,assignment,pid,percent_cover'
d = dict_slice(d,fields,None)
tuples.append((d['image'], d['scope'], d['category'], json.dumps(d['geometry']).strip('{}'), d['annotator'],d['timestamp'], d['assignment'], d['pid'], d['percent_cover']))
with xa(self.config.psql_connect) as (connection,cursor):
cursor.executemany("insert into annotations (image_id, scope_id, category_id, geometry_text, annotator_id, timestamp, assignment_id, annotation_id, percent_cover) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)", tuples)
connection.commit()
def bulk_create_annotations(self,annotations):
FIELDS = self.FIELDS
with NamedTemporaryFile(dir='/tmp',delete=False) as tmp:
tmp.write(','.join(FIELDS)+'\n')
for ann in annotations:
record = dict(zip(FIELDS,['NULL' for _ in range(len(FIELDS))]))
record.update(dict_slice(ann,FIELDS))
tmp.write(','.join([record[f] for f in FIELDS]) + '\n')
tmp.flush()
name = tmp.name
os.chmod(name, 0777)
c = psql.connect(self.psql_connect)
db = c.cursor()
db.execute('copy annotations from \'%s\' with null as \'NULL\' csv header' % name)
c.commit()
os.remove(name)
def list_assignments(self):
for ass in self.assignments:
yield dict_slice(ass,'pid,label,annotator,status,mode,images')
