def usersBehaviour(connector): #TODO: check whether the command was executed already first = False if first: connector.execute('''create table answerers(userid INTEGER PRIMARY KEY);''') connector.execute('''create table askers(userid INTEGER PRIMARY KEY);''') connector.execute('''create table voters(userid INTEGER PRIMARY KEY);''') connector.execute('''insert into answerers (userid) select distinct users.id from users, posts where posts.posttypeid = 2 and posts.owneruserid = users.id;''') connector.execute('''insert into askers (userid) select distinct users.id from users, posts where posts.posttypeid = 1 and posts.owneruserid = users.id;''') connector.execute('''insert into voters (userid) select distinct users.id from users where upvotes > 0 OR downvotes > 0;''') data = {'q': 0, 'a': 0, 'v': 0, 'qa': 0, 'qv': 0, 'av': 0, 'qva': 0, 'u': 0} # count folks who questioned, answered, voted data['qva'] = once(lambda x: runquery(connector, x),'''select count(*) from voters, askers, answerers where voters.userid = askers.userid and askers.userid = answerers.userid;''')[0][0] # count folks who questioned, answered data['qa'] = once(lambda x: runquery(connector, x),'''select count(*) from askers, answerers where askers.userid = answerers.userid;''')[0][0] data['qv'] = once(lambda x: runquery(connector, x),'''select count(*) from askers, voters where askers.userid = voters.userid;''')[0][0] data['av'] = once(lambda x: runquery(connector, x),'''select count(*) from answerers, voters where answerers.userid = voters.userid;''')[0][0] # count folks who questioned, answered data['q'] = once(lambda x: runquery(connector, x),'''select count(*) from askers;''')[0][0] data['a'] = once(lambda x: runquery(connector, x),'''select count(*) from answerers;''')[0][0] data['v'] = once(lambda x: runquery(connector, x),'''select count(*) from voters;''')[0][0] data['u'] = once(lambda x: runquery(connector, x),'''select count(*) from users;''')[0][0] #print data return data
def test_once_first(self): # Failure message: # expected once(add) to return 4 when it is called with 2 and 2 the first time oneAddition = once(add) self.assertEqual(oneAddition(2, 2), 4)
def test_once_second(self): # Failure message: # expected once(add) to return None when it is called with 2 and 2 after the first function call oneAddition = once(add) oneAddition(2, 2) self.assertEqual(oneAddition(2, 2), None)
def Test(): onceinit(__file__) #run the query conn = sqlite3.connect('/mnt/nb254_data/db/stackoverflow.db') c = conn.cursor() c.execute('PRAGMA temp_store=MEMORY') c.execute('PRAGMA cache_size=500000') result = once(lambda x: runquery(c,x),'''SELECT q.Tags as Tags, MIN(strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToFirstAnswer FROM posts as q, posts as a WHERE q.PostTypeId = 1;''') # LIMIT 100000
def questWithAcceptedAnswers(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as PostId, q.OwnerUserId as UserId, q.Title as TitleQ, q.Body as BodyQ FROM posts as q, posts as a WHERE q.PostTypeId = 1 and q.AcceptedAnswerId = a.Id;''') # LIMIT 100000 return result
def usersStats(connector): result = once(lambda x: runquery(connector, x),'''SELECT Users.Id as UserId, Users.DisplayName as Name, Users.Location as LOCATION, Users.CreationDate as DateCreated, ((strftime('%s','2014-09-26T00:00:00.000') - strftime('%s',Users.CreationDate))/(60*60*24)) as DAYS_ON_SITE, Users.Reputation as U_REPUTATION, Users.UpVotes as U_UPVOTES, Users.DownVotes as U_DOWNVOTES, Users.Views as U_VIEWS FROM Users GROUP BY Users.Id;''') # LIMIT 100000 return result
def usersAvAnsTime(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.OwnerUserId as UserId, AVG((strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate))) as U_AV_ANS_TIME FROM posts as q, posts as a WHERE q.PostTypeId = 1 and q.AcceptedAnswerId = a.Id GROUP BY q.OwnerUserId;''') # LIMIT 100000 return result
def numComments(connector): result = once(lambda x: runquery(connector, x),'''select count(*) as COMMENTS_NUM from comments;''') # LIMIT 100000 return result
def postsText_Data(connector): result = once(lambda x: runquery(connector, x),'''SELECT Id as PostId, OwnerUserId as UserId, Title as Q_Title, Body as Q_Body, (length(Title) - length(replace(Title, ' ', '')) +1) as TITLE_LENGTH, (length(Body) - length(replace(Body, ' ', '')) +1) as BODY_LENGTH FROM posts WHERE PostTypeId = 1;''') # LIMIT 100000 return result
def upvotedAnsTime1(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as PostId, q.CreationDate as TimeAsked, MIN (strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToUpvotedAnswer, a.Id as AnswerId, q.OwnerUserId as AskerId, a.OwnerUserId as AnswererId FROM posts as q, posts as a WHERE q.PostTypeId = 1 AND a.ParentId = q.Id AND a.Score>0 GROUP BY q.Id;''') # LIMIT 100000 return result
def numQuestions(connector): result = once(lambda x: runquery(connector, x),'''select count(*) as Q_NUM from posts where PostTypeId=1;''') # LIMIT 100000 return result
def numofViews(connector, maxdate, s): result = once(lambda sql, arg: runquery(connector, sql, arg),'''SELECT ViewCount as Q_VIEWS, Count(*) as Q_VIEW_COUNT FROM posts WHERE (PostTypeId=1 AND CAST((strftime('%s',?) - strftime('%s',CreationDate)) AS INTEGER) < ?) GROUP BY ViewCount''', (maxdate, s)) return result
def questTitleAnswers(connector): result = once(lambda x: runquery(connector, x),'''SELECT (length(Title) - length(replace(Title, ' ', '')) +1) as TITLE_LENGTH, (length(Body) - length(replace(Body, ' ', '')) +1) as BODY_LENGTH, AnswerCount as ANSWERS_NUM, Title as Q_TITLE FROM posts;''') return result
def postsStats(connector): result = once(lambda x: runquery(connector, x),'''SELECT CreationDate as TimeCreated, PostTypeId as PostTypeId FROM posts GROUP BY CreationDate;''') # LIMIT 100000 return result
def tagStats(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as PostId, MIN (strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToUpvotedAnswer, q.body, q.tags FROM posts as q, posts as a WHERE q.PostTypeId = 1 AND a.ParentId = q.Id AND a.Score > 0 GROUP BY q.Id''') return result
def usersActivity(connector): result = once(lambda x: runquery(connector, x),'''SELECT Posts.Id as PostId, Posts.OwnerUserId as UserId, Posts.CreationDate as TimePosted, Posts.PostTypeId as PostType FROM Posts, Users on Users.Id = OwnerUserId GROUP BY Posts.Id;''') # LIMIT 100000 return result
def questStats(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as QuestionId, q.OwnerUserId as UserId, a.OwnerUserId as AnswererId, q.ViewCount as Q_VIEWS, q.Tags as Tags, q.CreationDate as TimeAsked, a.CreationDate as TimeAnswered, (strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToAcceptedAnswer, a.Id as AnswerId FROM posts as q, posts as a WHERE q.PostTypeId = 1 and q.AcceptedAnswerId = a.Id GROUP BY q.Id;''') # LIMIT 100000 return result
def postsText_Data1(connector): result = once(lambda x: runquery(connector, x),'''SELECT Id as PostId, OwnerUserId as UserId, Title as Q_Title, Body as Q_Body FROM posts WHERE PostTypeId = 1;''') # LIMIT 100000 return result
def firstAnsTime(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as PostId, q.CreationDate as TimeAsked, MIN(strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToFirstAnswer FROM posts as q, posts as a WHERE q.PostTypeId = 1 AND a.ParentId = q.Id GROUP BY q.Id;''') # LIMIT 100000 return result
def requestQuestion(connector, QuestionId): result = once(lambda x: runquery(connector, x),'''SELECT Id as PostId, OwnerUserId as UserId, Title as title, Body as body, CreationDate as timePosted, Tags as tags FROM Posts WHERE Id = ''' + str(QuestionId) + ''';''') # LIMIT 100000 return result
def firstAnsTime1(connector): result = once(lambda x: runquery(connector, x),'''SELECT q.Id as PostId, q.CreationDate as TimeAsked, (strftime('%s', a.CreationDate)-strftime('%s',q.CreationDate)) as SecondsToAcceptedAnswer, a.Id as AnswerId, q.OwnerUserId as AskerId, a.OwnerUserId as AnswererId FROM posts as q, posts as a WHERE q.PostTypeId = 1 and q.AcceptedAnswerId = a.Id;''') # LIMIT 100000 return result
def acceptedAnswers(connector): result = once(lambda x: runquery(connector, x),'''SELECT AcceptedAnswerId as PostId , Users.Id as UserId FROM Posts, Users WHERE AcceptedAnswerId<>'' and users.Id = posts.OwnerUserId GROUP BY UserId;''') # LIMIT 100000 return result
def numUsers(connector): result = once(lambda x: runquery(connector, x),'''select count(*) as U_NUM from users;''') # LIMIT 100000 return result
def numAcceptedAnswers(connector): result = once(lambda x: runquery(connector, x),'''select count(*) as Q_W_ACC_ANSWERS from posts where PostTypeId=1 and AcceptedAnswerId is not null;''') # LIMIT 100000 return result
def numAnswers(connector): result = once(lambda x: runquery(connector, x),'''select count(*) as ANSWERS_NUM from posts where PostTypeId=2;''') # LIMIT 100000 return result
def ansPerUser(connector): result = once(lambda x: runquery(connector, x),'''SELECT Users.Id as UserId, count(Posts.Id) as U_ANSWERS FROM Posts, Users on Users.Id = OwnerUserId WHERE PostTypeId = 2 GROUP BY Users.Id;''') # LIMIT 100000 return result
def postsPerUser(connector): result = once(lambda x: runquery(connector, x),'''SELECT users.Id as UserId, count(posts.OwnerUserId) as U_POSTS FROM Posts, users WHERE users.Id = posts.OwnerUserId GROUP BY users.Id;''') # LIMIT 100000 return result
def postsTitleLength_OLD(connector): result = once(lambda x: runquery(connector, x),'''SELECT Posts.Id as PostId, Posts.OwnerUserId as UserId, Posts.PostTypeId as PostTypeId, (length(Title) - length(replace(Title, ' ', '')) +1) as TITLE_LENGTH, (length(Body) - length(replace(Body, ' ', '')) +1) as BODY_LENGTH FROM Posts GROUP BY Posts.Id;''') # LIMIT 100000 return result
image_name_prefix = sys.argv[1] or "KITTI11" show_images = False output_dir = 'output' image_pair_paths = glob.glob(f"images/{image_name_prefix}*.png") threshold = 5 pathlib.Path(output_dir).mkdir(parents=False, exist_ok=True) detected_keypoints = [] descriptors = [] images = [] for filename in image_pair_paths: [cur_detected_keypoints, cur_descriptors] = once(siftdetector.detect_keypoints, filename, threshold) keypoints_cv2 = siftdetector.to_cv2_kplist(cur_detected_keypoints) descriptors_cv2 = siftdetector.to_cv2_di(cur_descriptors) detected_keypoints.append(keypoints_cv2) descriptors.append(descriptors_cv2) print(f"Found {len(keypoints_cv2)} keypoints in {filename}") img = cv2.imread(filename) images.append(img) image_with_keypoints = img.copy() cv2.drawKeypoints(img, keypoints_cv2, image_with_keypoints, flags=cv2.DRAW_MATCHES_FLAGS_DRAW_RICH_KEYPOINTS) image_with_keypoints_filename = f"{output_dir}/{filename.replace('/', '_')}" cv2.imwrite(image_with_keypoints_filename, image_with_keypoints)
def answerDistribution(connector): result = once(lambda x: runquery(connector, x),'''select AnswerCount, count(*) as Q_NUM from posts where PostTypeId=1 group by AnswerCount;''') # LIMIT 100000 return result