def do_work(conn, item):
cur=conn.cursor()
mturk_conn=mturk.conn()
#print item
try:
assignments=mturk_conn.get_assignments(hit_id=item["mturk_hit_id"])
except:
print "error in fetching assignments for: ", item["mturk_hit_id"]
#print assignments
for assgnmnt in assignments:
print "assignment: "+ str(assgnmnt)
#VALIDATION CODE TO CHECK FOR COMPLETION OF CONTROLS
mturk_worker_id=assgnmnt.WorkerId
mturk_assignment_id=assgnmnt.AssignmentId
submit_time=assgnmnt.SubmitTime
accept_time=assgnmnt.AcceptTime
autoapproval_time=assgnmnt.AutoApprovalTime
mturk_status=assgnmnt.AssignmentStatus
approval_time=None
rejection_time=None
utc = datetime.datetime.strptime(submit_time, '%Y-%m-%dT%H:%M:%SZ')
submit_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(accept_time, '%Y-%m-%dT%H:%M:%SZ')
accept_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(autoapproval_time, '%Y-%m-%dT%H:%M:%SZ')
autoapproval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
try:
dt=assgnmnt.ApprovalTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
approval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
try:
dt=assgnmnt.RejectionTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
rejection_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
#print assgnmnt.answers[0]
results={}
for i in assgnmnt.answers[0]:
#print i
results[i.qid]=i.fields[0]
result=json.dumps(results)
outfile.write(str(mturk_assignment_id)+','+str(item["hit_id"])+','+str(mturk_worker_id)+','+str(accept_time)+','+str(submit_time)+','+str(autoapproval_time)+','+str(approval_time)+','+str(rejection_time)+','+str(mturk_status)+'\n')
conn.commit()
def apprej(assignment, worker):
mturk_conn=mturk.conn()
conn = psycopg2.connect("dbname='"+settings["esl_dbname"]+"' user='******' host='"+settings["host"]+"'")
cur = conn.cursor()
sql="select * from assignments where id=%s;"
cur.execute(sql, (assignment, ))
row = cur.fetchone()
mturk_id = row[1]
paystatus = row[8]
sql = "select * from esl_workers where worker_id=%s;"
cur.execute(sql, (worker, ))
status = cur.fetchone()[4]
if(paystatus == "Submitted"):
print status
if(status == "APPROVE"):
print "approving assignment", assignment
try:
mturk_conn.approve_assignment(mturk_id, feedback=settings["esl_approve_feedback"])
sql = "update assignments set mturk_status='Approved' where id=%s;"
cur.execute(sql, (assignment, ))
except boto.mturk.connection.MTurkRequestError, err:
print "mturk api error while approving assignment"
if(status == "REJECT"):
print "rejecting assignment", assignment
try:
mturk_conn.reject_assignment(mturk_id, feedback=settings["esl_reject_feedback"])
sql = "update assignments set mturk_status='Rejected' where id=%s;"
cur.execute(sql, (assignment, ))
except boto.mturk.connection.MTurkRequestError, err:
print "mturk api error while rejecting assignment"
def do_work(conn, item):
#get language name/HITType from the queue and modify all HITs with this type
hittype_id = item["hittype_id"]
new_mturk_hittype_id = item["new_mturk_hittype_id"]
#typename=item["typename"]
print item
cur = conn.cursor()
mturk_conn = mturk.conn()
sql = "SELECT * from hits where hittype_id=%s;"
cur.execute(sql, (hittype_id, ))
rows = cur.fetchall()
for row in rows:
mturk_hit_id = str(row[1])
print "updating ", mturk_hit_id
try:
mturk_conn.change_hit_type_of_hit(mturk_hit_id,
new_mturk_hittype_id)
except:
print "error in updateing hittype of hit: ", mturk_hit_id
sql = "UPDATE hittypes SET mturk_hittype_id=%s where id=%s"
cur.execute(sql, (new_mturk_hittype_id, hittype_id))
conn.commit()
def apprej(assignment, worker):
mturk_conn = mturk.conn()
conn = psycopg2.connect("dbname='" + settings["esl_dbname"] + "' user='******' host='" + settings["host"] +
"'")
cur = conn.cursor()
sql = "select * from assignments where id=%s;"
cur.execute(sql, (assignment, ))
row = cur.fetchone()
mturk_id = row[1]
paystatus = row[8]
sql = "select * from esl_workers where worker_id=%s;"
cur.execute(sql, (worker, ))
status = cur.fetchone()[4]
if (paystatus == "Submitted"):
print status
if (status == "APPROVE"):
print "approving assignment", assignment
try:
mturk_conn.approve_assignment(
mturk_id, feedback=settings["esl_approve_feedback"])
sql = "update assignments set mturk_status='Approved' where id=%s;"
cur.execute(sql, (assignment, ))
except boto.mturk.connection.MTurkRequestError, err:
print "mturk api error while approving assignment"
if (status == "REJECT"):
print "rejecting assignment", assignment
try:
mturk_conn.reject_assignment(
mturk_id, feedback=settings["esl_reject_feedback"])
sql = "update assignments set mturk_status='Rejected' where id=%s;"
cur.execute(sql, (assignment, ))
except boto.mturk.connection.MTurkRequestError, err:
print "mturk api error while rejecting assignment"
def do_work(conn, item): #get language name/HITType from the queue and modify all HITs with this type hittype_id=item["hittype_id"] new_mturk_hittype_id=item["new_mturk_hittype_id"] #typename=item["typename"] print item cur=conn.cursor() mturk_conn=mturk.conn() sql="SELECT * from hits where hittype_id=%s;" cur.execute(sql,(hittype_id,)) rows=cur.fetchall() for row in rows: mturk_hit_id=str(row[1]) print "updating ",mturk_hit_id try: mturk_conn.change_hit_type_of_hit(mturk_hit_id, new_mturk_hittype_id) except: print "error in updateing hittype of hit: ", mturk_hit_id sql="UPDATE hittypes SET mturk_hittype_id=%s where id=%s" cur.execute(sql,(new_mturk_hittype_id, hittype_id)) conn.commit()
def apprej(assignment, worker):
updatedb(worker)
mturk_conn=mturk.conn()
conn = psycopg2.connect("dbname='"+settings["esl_dbname"]+"' user='******' host='"+settings["host"]+"'")
cur = conn.cursor()
sql="select * from esl_assignments where id=%s;"
cur.execute(sql, (assignment, ))
row = cur.fetchone()
mturk_id = row[1]
paystatus = row[8]
sql = "select * from esl_grades where assignment_id=%s;"
cur.execute(sql, (str(assignment), ))
status = cur.fetchone()[3]
if(status == "APPROVE"):
print "approving assignment", assignment
try:
approve_feedback=get_feedback(worker, assignment)
print approve_feedback
mturk_conn.approve_assignment(mturk_id, feedback=approve_feedback)
sql = "update esl_assignments set mturk_status='Approved' where id=%s;"
cur.execute(sql, (assignment, ))
except boto.mturk.connection.MTurkRequestError, err:
print "mturk api error while approving assignment"
logging.info("adjust Similar HITs assignments - START")
target_language = settings["target_language"]
logging.info("target language: %s" % (target_language))
try:
conn = psycopg2.connect("dbname='"+settings["dbname"]+"' user='******' host='"+settings["host"]+"'")
logging.info("successfully connected to database")
except:
logging.error("unable to connect to the database")
cur=conn.cursor()
cur2=conn.cursor()
mturk_conn=mturk.conn()
#select all Graded assignment (with any status including Approved/Rejected mturk_status) and pay workers and Approve/Reject them in MTurk
sql="""select h.id, h.mturk_hit_id, count(*) from similar_hits h, assignments a where a.hit_id=h.id and a.data_status=1 group by h.id, h.mturk_hit_id;"""
cur.execute(sql)
rows=cur.fetchall()
for row in rows:
hit_id=str(row[0])
mturk_hit_id=str(row[1])
count=int(row[2])
if count<3:
try:
#pass
print mturk_hit_id, count
conn.commit()
langs_properties[lang]["hittype_id"] = hittype_id2
sql = "SELECT id from hittypes where language_id=%s and typename='vocabulary';"
cur.execute(sql, (lang_id, ))
rows = cur.fetchall()
hittype_id = 0
for row in rows:
hittype_id = str(row[0])
sql = "SELECT mturk_hit_id, h.id, typename from hits h, hittypes ht where h.hittype_id=ht.id and h.language_id=%s and assignments>(rejected+approved);"
cur.execute(sql, (lang_id, ))
rows = cur.fetchall()
mturk_conn = mturk.conn()
print "changing HIT types"
for row in rows:
mturk_hit_id = str(row[0])
hit_id = str(row[1])
typename = str(row[2])
timeout = 5
passed = False
mturk_conn.change_hit_type_of_hit(mturk_hit_id, mturk_hittype_id)
conn.close()
logging.info("HIT Types migration pipeline - FINISH")
def do_work(conn, item):
cur=conn.cursor()
mturk_conn=mturk.conn()
#print item
try:
assignments=mturk_conn.get_assignments(hit_id=item["mturk_hit_id"])
except:
print "error in fetching assignments for: ", item["mturk_hit_id"]
#print assignments
for assgnmnt in assignments:
print "assignment: "+ str(assgnmnt)
#VALIDATION CODE TO CHECK FOR COMPLETION OF CONTROLS
mturk_worker_id=assgnmnt.WorkerId
mturk_assignment_id=assgnmnt.AssignmentId
submit_time=assgnmnt.SubmitTime
accept_time=assgnmnt.AcceptTime
autoapproval_time=assgnmnt.AutoApprovalTime
mturk_status=assgnmnt.AssignmentStatus
approval_time=None
rejection_time=None
utc = datetime.datetime.strptime(submit_time, '%Y-%m-%dT%H:%M:%SZ')
submit_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(accept_time, '%Y-%m-%dT%H:%M:%SZ')
accept_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(autoapproval_time, '%Y-%m-%dT%H:%M:%SZ')
autoapproval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
try:
dt=assgnmnt.ApprovalTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
approval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
try:
dt=assgnmnt.RejectionTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
rejection_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
#print assgnmnt.answers[0]
results={}
for i in assgnmnt.answers[0]:
#print i
results[i.qid]=i.fields[0]
result=json.dumps(results)
# print "assignment ", mturk_assignment_id, " mturk_status ", mturk_status
#import psycopg2
#import datetime
#dt=a[0].AutoApprovalTime
#utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
#ts=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
sql="INSERT INTO buffer_assignments (assignment_id, hit_id, worker_id, accept_time, submit_time, autoapproval_time, approval_time, rejection_time, result, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(mturk_assignment_id, item["hit_id"], mturk_worker_id, accept_time, submit_time, autoapproval_time, approval_time, rejection_time, result, mturk_status))
conn.commit()
def run(self):
#TODO: code to get assignments for HIT
mturk_conn=mturk.conn()
conn = psycopg2.connect("dbname='"+settings["dbname"]+"' user='******' host='"+settings["host"]+"'")
#conn=conn_pool.getconn()
logging.info("mturk hit id: "+self.mturk_hit_id+", fulltypename: "+self.fulltypename)
assignments=mturk_conn.get_assignments(hit_id=self.mturk_hit_id)
for assgnmnt in assignments:
mturk_worker_id=assgnmnt.WorkerId
mturk_assignment_id=assgnmnt.AssignmentId
submit_time=assgnmnt.SubmitTime
status='Open'
mturk_status=assgnmnt.AssignmentStatus
results={}
for i in assgnmnt.answers[0]:
results[i.fields[0][0]]=i.fields[0][1]
result=json.dumps(results)
print "assignment ", mturk_assignment_id, " mturk_status ", mturk_status
lock.acquire()
cur=conn.cursor()
sql="SELECT add_worker(%s, %s);"
cur.execute(sql,(mturk_worker_id, "unknown"))
db_worker_id = cur.fetchone()[0]
#conn.commit()
#lock.release()
sql="SELECT add_assignment(%s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(mturk_assignment_id, self.hit_id, mturk_worker_id, status, submit_time, result, mturk_status))
conn.commit()
lock.release()
assignment_id = cur.fetchone()[0]
if self.typename=="vocabulary":
#for i in range(settings["num_knowns"]+settings["num_unknowns"]):
for key in results.keys():
if key.find("word")==0:
wordnum=key
num=wordnum[4:5]
word_id=wordnum[6:15]
is_control=wordnum[15:16]=="0"
translation=results[key]
reason=results["reason"+num]
lock.acquire()
cur=conn.cursor()
sql="SELECT add_voc_hits_result(%s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, int(word_id), translation, reason, int(is_control)))
result_id = cur.fetchone()[0]
conn.commit()
lock.release()
if self.typename=="synonyms":
#for i in range(settings["num_knowns"]+settings["num_unknowns"]):
for key in results.keys():
if key.find("pair")==0:
pairnum=key
pair_id=pairnum[5:14]
pair_id_with_control=pairnum[5:15]
is_control=pairnum[14:15]
are_synonyms=results[key]
misspelled=""
try:
misspelled=results["misspelled_"+pair_id_with_control]
except KeyError:
pass
#debug for misspelled issue, fixed now
#print "pair ",pair_id ," misspelled ", misspelled
#print "pair ", pair_id, " are_synonyms ", are_synonyms
lock.acquire()
cur=conn.cursor()
sql="SELECT add_syn_hits_result(%s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, int(pair_id), are_synonyms, misspelled, is_control))
result_id = cur.fetchone()[0]
conn.commit()
lock.release()
ip=results.get("ip","")
city=results.get("city","")
region=results.get("region","")
country=results.get("country","")
zipcode=results.get("zipcode","")
lat=results.get("lat","")
lng=results.get("lng","")
timestamp=submit_time
lock.acquire()
cur=conn.cursor()
sql="SELECT add_location(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, db_worker_id, ip, city, region, country, zipcode, lat, lng, timestamp))
#sql="SELECT add_foreignenglishspeakingsurvey(%s, %s, %s, %s, %s);"
#cur.execute(sql,(db_worker_id, timestamp, native_speaker, years_speaking_foreign, native_english_speaker, years_speaking_english, country, born_country, language, language_id))
conn.commit()
lock.release()
#try:
# conn_pool.putconn(conn)
#except:
# print "conn pool error"
#print mturk_worker_id, assignment_id, submit_time
#print "answers ", len(assgnmnt.answers)
#print result
conn.close()
def do_work(conn, item):
cur = conn.cursor()
mturk_conn = mturk.conn()
#print item
try:
assignments = mturk_conn.get_assignments(hit_id=item["mturk_hit_id"])
except:
print "error in fetching assignments for: ", item["mturk_hit_id"]
#print assignments
for assgnmnt in assignments:
#print assgnmnt
mturk_worker_id = assgnmnt.WorkerId
mturk_assignment_id = assgnmnt.AssignmentId
submit_time = assgnmnt.SubmitTime
accept_time = assgnmnt.AcceptTime
autoapproval_time = assgnmnt.AutoApprovalTime
mturk_status = assgnmnt.AssignmentStatus
approval_time = None
rejection_time = None
utc = datetime.datetime.strptime(submit_time, '%Y-%m-%dT%H:%M:%SZ')
submit_time = psycopg2.Timestamp(utc.year, utc.month, utc.day,
utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(accept_time, '%Y-%m-%dT%H:%M:%SZ')
accept_time = psycopg2.Timestamp(utc.year, utc.month, utc.day,
utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(autoapproval_time,
'%Y-%m-%dT%H:%M:%SZ')
autoapproval_time = psycopg2.Timestamp(utc.year, utc.month, utc.day,
utc.hour, utc.minute,
utc.second)
try:
dt = assgnmnt.ApprovalTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
approval_time = psycopg2.Timestamp(utc.year, utc.month, utc.day,
utc.hour, utc.minute,
utc.second)
except:
pass
try:
dt = assgnmnt.RejectionTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
rejection_time = psycopg2.Timestamp(utc.year, utc.month, utc.day,
utc.hour, utc.minute,
utc.second)
except:
pass
#print assgnmnt.answers[0]
results = {}
for i in assgnmnt.answers[0]:
#print i
results[i.qid] = i.fields[0]
result = json.dumps(results)
print "assignment ", mturk_assignment_id, " mturk_status ", mturk_status
#import psycopg2
#import datetime
#dt=a[0].AutoApprovalTime
#utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
#ts=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
sql = "INSERT INTO buffer_assignments (assignment_id, hit_id, worker_id, accept_time, submit_time, autoapproval_time, approval_time, rejection_time, result, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql, (mturk_assignment_id, item["hit_id"], mturk_worker_id,
accept_time, submit_time, autoapproval_time,
approval_time, rejection_time, result, mturk_status))
conn.commit()
def run(self):
#TODO: code to get assignments for HIT
mturk_conn=mturk.conn()
conn=conn_pool.getconn()
cur=conn.cursor()
assignments=mturk_conn.get_assignments(hit_id=mturk_hit_id)
for assgnmnt in assignments:
mturk_worker_id=assgnmnt.WorkerId
mturk_assignment_id=assgnmnt.AssignmentId
submit_time=assgnmnt.SubmitTime
status=assgnmnt.AssignmentStatus
results={}
for i in assgnmnt.answers[0]:
results[i.fields[0][0]]=i.fields[0][1]
result=json.dumps(results)
mturk_status=""
lock.acquire()
sql="SELECT add_worker(%s, %s);"
cur.execute(sql,(mturk_worker_id, "unknown"))
db_worker_id = cur.fetchone()[0]
conn.commit()
lock.release()
sql="SELECT add_assignment(%s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(mturk_assignment_id, hit_id, mturk_worker_id, status, submit_time, result, mturk_status))
assignment_id = cur.fetchone()[0]
if self.typename=="vocabulary":
#for i in range(settings["num_knowns"]+settings["num_unknowns"]):
for key in results.keys():
if key.find("word")==0:
wordnum=key
num=wordnum[4:5]
word_id=wordnum[6:15]
is_control=wordnum[15:16]=="0"
translation=results[key]
reason=results["reason"+num]
sql="SELECT add_voc_hits_result(%s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, int(word_id), translation, reason, int(is_control)))
result_id = cur.fetchone()[0]
conn.commit()
if self.typename=="synonyms":
#for i in range(settings["num_knowns"]+settings["num_unknowns"]):
for key in results.keys():
if key.find("pair")==0:
pairnum=key
pair_id=pairnum[5:14]
pair_id_with_control=pairnum[5:15]
is_control=pairnum[14:15]
are_synonyms=results[key]
misspelled=""
try:
misspelled=results["misspelled_"+pair_id_with_control]
except KeyError:
pass
#debug for misspelled issue, fixed now
#print "pair ",pair_id ," misspelled ", misspelled
print "pair ", pair_id, " are_synonyms ", are_synonyms
sql="SELECT add_syn_hits_result(%s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, int(pair_id), are_synonyms, misspelled, is_control))
result_id = cur.fetchone()[0]
conn.commit()
ip=results.get("ip","")
city=results.get("city","")
region=results.get("region","")
country=results.get("country","")
zipcode=results.get("zipcode","")
lat=results.get("lat","")
lng=results.get("lng","")
timestamp=submit_time
sql="SELECT add_location(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(assignment_id, db_worker_id, ip, city, region, country, zipcode, lat, lng, timestamp))
#sql="SELECT add_foreignenglishspeakingsurvey(%s, %s, %s, %s, %s);"
#cur.execute(sql,(db_worker_id, timestamp, native_speaker, years_speaking_foreign, native_english_speaker, years_speaking_english, country, born_country, language, language_id))
conn.commit()
conn_pool.putconn(conn)
print mturk_worker_id, assignment_id, submit_time
print "answers ", len(assgnmnt.answers)
print result
def do_work(conn, item):
cur=conn.cursor()
mturk_conn=mturk.conn()
timeout=5
passed=False
while not passed:
#print item
try:
assignments=mturk_conn.get_assignments(hit_id=item["mturk_hit_id"])
passed=True
except:
passed=False
timeout=timeout*2
print "Sleep for %s seconds " % (timeout)
sleep(timeout)
#print "error in fetching assignments for: ", item["mturk_hit_id"]
#print assignments
for assgnmnt in assignments:
print "assignment: "+ str(assgnmnt)
mturk_worker_id=assgnmnt.WorkerId
mturk_assignment_id=assgnmnt.AssignmentId
submit_time=assgnmnt.SubmitTime
accept_time=assgnmnt.AcceptTime
autoapproval_time=assgnmnt.AutoApprovalTime
mturk_status=assgnmnt.AssignmentStatus
approval_time=None
rejection_time=None
utc = datetime.datetime.strptime(submit_time, '%Y-%m-%dT%H:%M:%SZ')
submit_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(accept_time, '%Y-%m-%dT%H:%M:%SZ')
accept_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
utc = datetime.datetime.strptime(autoapproval_time, '%Y-%m-%dT%H:%M:%SZ')
autoapproval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
try:
dt=assgnmnt.ApprovalTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
approval_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
try:
dt=assgnmnt.RejectionTime
utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
rejection_time=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
except:
pass
sql = "SELECT * FROM esl_workers WHERE worker_id=%s;"
cur.execute(sql, (mturk_worker_id, ))
if(cur.rowcount == 0):
sql="INSERT INTO esl_workers(worker_id,num_hits,num_correct_controls,num_controls,average,status,statusdesc,num_approved) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);"
cur.execute(sql,(mturk_worker_id, 0, 0, 0, 0, "PENDING","PENDING",0))
# else:
# sql = "SELECT * FROM esl_assignments WHERE worker_id=%s;"
# cur.execute(sql, (mturk_worker_id, ))
# if(cur.rowcount == 0):
# numhits = int(cur.fetchone()[2])+1
# sql = "UPDATE esl_workers SET num_hits=%s WHERE worker_id=%s;"
# cur.execute(sql, (numhits, mturk_worker_id))
#print assgnmnt.answers[0]
results={}
for i in assgnmnt.answers[0]:
#print i
results[i.qid]=i.fields[0]
result=json.dumps(results)
# print "assignment ", mturk_assignment_id, " mturk_status ", mturk_status
#import psycopg2
#import datetime
#dt=a[0].AutoApprovalTime
#utc = datetime.datetime.strptime(dt, '%Y-%m-%dT%H:%M:%SZ')
#ts=psycopg2.Timestamp(utc.year, utc.month, utc.day, utc.hour, utc.minute, utc.second)
sql="INSERT INTO buffer_assignments (assignment_id, hit_id, worker_id, accept_time, submit_time, autoapproval_time, approval_time, rejection_time, result, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
cur.execute(sql,(mturk_assignment_id, item["hit_id"], mturk_worker_id, accept_time, submit_time, autoapproval_time, approval_time, rejection_time, result, mturk_status))
conn.commit()
# sql="SELECT mturk_hit_id from esl_hits_data where language_id=%s;"
sql = "SELECT mturk_hit_id from esl_hits_data where language_id=en;"
# cur.execute(sql,(lang_id,))
cur.execute(sql)
rows = cur.fetchall()
cur2 = conn.cursor()
for row in rows:
mturk_hit_id = str(row[0])
timeout = 5
passed = False
conn2 = mturk.conn()
assignments = conn2.get_assignments(hit_id=mturk_hit_id)
for assgnmnt in assignments:
worker_id = assgnmnt.WorkerId
assignment_id = assgnmnt.AssignmentId
submit_time = assgnmnt.SubmitTime
results = {}
for i in assgnmnt.answers[0]:
results[i.fields[0][0]] = i.fields[0][1]
result = json.dumps(results)
print worker_id, assignment_id, submit_time