def insert(data, deviceId):
num = len(data)
conn_str = "dbname='zpr' user='******' host='localhost' password='******'"
try:
conn = psycopg2.connect(conn_str)
except:
print("I am unable to connect to the database")
exit(-1)
bar = Bar(
"Populating db with measurements from device {}".format(deviceId),
max=num)
cur = conn.cursor()
for record in data:
dt = record[1]
pg_datetime = psycopg2.Timestamp(dt.year, dt.month, dt.day, dt.hour,
dt.minute, dt.second)
sql = "INSERT INTO Measurements VALUES (default, {0}, {1}, {2});".format(
round(record[0], 2), pg_datetime, deviceId)
cur.execute(sql)
bar.next()
conn.commit()
cur.close()
bar.finish()
def enterLogMessage(self, message):
dt = datetime.datetime.now()
queryString = """insert into logs (message, time) values ('{message}', {time});""".format(
message=message,
time=psycopg2.Timestamp(dt.year, dt.month, dt.day, dt.hour,
dt.minute, dt.second))
return self.writeQuery(queryString)
def test_date_time_allocation_bug(self):
d1 = psycopg2.Date(2002,12,25)
d2 = psycopg2.DateFromTicks(time.mktime((2002,12,25,0,0,0,0,0,0)))
t1 = psycopg2.Time(13,45,30)
t2 = psycopg2.TimeFromTicks(time.mktime((2001,1,1,13,45,30,0,0,0)))
t1 = psycopg2.Timestamp(2002,12,25,13,45,30)
t2 = psycopg2.TimestampFromTicks(
time.mktime((2002,12,25,13,45,30,0,0,0)))
def insertDatasetHistory(self, dataset_name, num_elements):
try:
db = dbConnection()
cursor = db.cursor()
now = dt.now()
print(now.year, now.month, now.day,
now.hour, now.minute, now.second)
cursor.execute("INSERT INTO public.dataset_history (dataset_name, num_elements, date_upload) VALUES(%s, %s, %s)",
(dataset_name, num_elements, psycopg2.Timestamp(now.year, now.month, now.day, now.hour, now.minute, now.second)))
db.commit()
db.close()
cursor.close()
except Exception as e:
print(e)
raise Exception('Error! into create dataset_history')
def sqlNow():
t = time.gmtime()
ts = dbapi2.Timestamp(t.tm_year, t.tm_mon, t.tm_mday, t.tm_hour, t.tm_min,
t.tm_sec)
return str(ts)
def worker(thread_number, socket_number):
print("WOWOW")
#build the socket
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind((common_host, socket_number))
s.listen(1)
conn, addr = s.accept()
#handle socket problems
try:
if verbose:
print('Connected by', addr)
#build a bytestring of input
data = bytes([])
while True:
new_data = conn.recv(1024)
if not new_data: break
data += new_data
data = str(data, 'UTF-8')
if verbose:
print("Data Recieved: ", data)
#turn those bytes into a json request
try:
json_data = json.loads(data)
except Exception as e:
print("Failed to parse JSON")
#check to make sure the request has a type
if "request_type" in json_data:
if json_data["request_type"] == "save_blogs":
# get the blogs and the links from the request
try:
insert_values = []
blog_list = json_data["blogs"]
link_list = json_data["links"]
if len(blog_list) != len(link_list):
raise Exception
#append these together into a list
for a in range(len(blog_list)):
insert_values.append({
"name": blog_list[a],
"link": link_list[a]
})
#now build the db stuff and insert into the db
conn_string = "host='localhost' dbname='cs585' user='******' "
db_conn = psycopg2.connect(conn_string)
cursor = db_conn.cursor()
for a in insert_values:
try:
cursor.execute("insert into blog values(%s,%s);",
(a["name"], a["link"]))
db_conn.commit()
except Exception as e:
db_conn.rollback()
pass
db_conn.commit()
cursor.close()
db_conn.close()
send_data = {
"worked": True,
"request_type": "save_blogs",
}
except Exception as e:
print("WOW: " + str(e))
send_data = {
"worked": False,
"request_type": "save_blogs",
}
pass
elif json_data["request_type"] == "save_posts":
# get the blogs and the links from the request
try:
insert_values = []
post_list = json_data["posts"]
#now build the db stuff and insert into the db
conn_string = "host='localhost' dbname='cs585' user='******' "
db_conn = psycopg2.connect(conn_string)
cursor = db_conn.cursor()
for a in post_list:
try:
t = time.gmtime(int(a["timestamp"]))
if a["title"] != None:
a["title"] = a["title"][:100]
cursor.execute(
"insert into post values(%s,%s,%s,%s,%s,%s,%s,%s);",
(
a["post_id"],
a["post_link"][:300],
a["blog_name"],
a["type"],
a["content"][:500],
psycopg2.Timestamp(t.tm_year, t.tm_mon,
t.tm_mday, t.tm_hour,
t.tm_min, t.tm_sec),
a["note_count"],
a["title"],
))
db_conn.commit()
except Exception as e:
print("DB Fail - ", str(e))
db_conn.rollback()
pass
if "tags" in a:
for b in a["tags"]:
try:
cursor.execute(
"insert into tag values(%s,%s);",
(b, a["post_id"]))
db_conn.commit()
except Exception as e:
db_conn.rollback()
pass
db_conn.commit()
cursor.close()
db_conn.close()
send_data = {
"worked": True,
"request_type": "save_blogs",
}
except Exception as e:
print("WOW: " + str(e))
send_data = {
"worked": False,
"request_type": "save_blogs",
}
pass
elif json_data["request_type"] == "save_notes":
# get the blogs and the links from the request
try:
insert_values = []
note_list = json_data["notes"]
#now build the db stuff and insert into the db
conn_string = "host='localhost' dbname='cs585' user='******' "
db_conn = psycopg2.connect(conn_string)
cursor = db_conn.cursor()
for a in note_list:
try:
t = time.gmtime(int(a["timestamp"]))
cursor.execute(
"insert into note values(%s,%s,%s,%s);",
(a["post_id"], a["type"],
psycopg2.Timestamp(
t.tm_year, t.tm_mon, t.tm_mday, t.tm_hour,
t.tm_min, t.tm_sec), a["blog_name"]))
db_conn.commit()
except Exception as e:
print("sdfsdfdsf", str(e))
db_conn.rollback()
pass
db_conn.commit()
cursor.close()
db_conn.close()
send_data = {
"worked": True,
"request_type": "save_blogs",
}
except Exception as e:
print("WOW: " + str(e))
send_data = {
"worked": False,
"request_type": "save_blogs",
}
pass
#make sure we catch all shitty requests
else:
#build the json for the return string
send_data = {
"worked": True,
"request_type": "NOT RECOGNIZED",
}
#send the message
conn.send(str.encode(json.dumps(send_data)))
conn.shutdown(socket.SHUT_WR)
#catch all thread exceptions so that we know what happened
except Exception as e:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
print("Error in Thread " + str(thread_number) + ": " + str(e) +
" on line " + str(exc_tb.tb_lineno))
#make sure the connection closed
finally:
if conn != None:
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 toTsmp(d):
return psycopg2.Timestamp(d.year, d.month, d.day, d.hour, d.minute,
d.second)
def mxdatetimetype(value):
""" psycopg2 adapter, mx.DateTimeType -> Timestamp. """
return psycopg2.Timestamp(value.year, value.month, value.day, value.hour,
value.minute, int(value.second))
