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