def get(self, cursor=None, user_id=None, venue_id=None, **kwargs): if (venue_id): subqry = {'select': 'COUNT(id)', 'table': 'post_reports', 'where': ('post_id = posts.id')} qry = {'select': ('posts.id', 'user_id', 'posts.venue_id', 'caption', 'time', 'hidden', 'users.forename', 'users.surname'), 'left_join': 'users', 'on': 'posts.user_id = users.id', 'table': 'posts', 'where': ('posts.venue_id = ?', 'hidden = 0', '(' + util.query(**subqry) + ') < 3' #, #'time > ' + str(util.now() - 691200)), ), 'order_by': 'time DESC', 'limit': 50} cursor.execute(util.query(**qry), (venue_id,)) else: qry = {'select': ('posts.id', 'venues.name', 'posts.time'), 'left_join': 'venues', 'on': 'posts.venue_id = venues.id', 'table': 'posts', 'where': ('posts.user_id = ?'), 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, venue_id=None, delete=None, promotion_id=None, title=None, description=None, start=None, end=None, maximum=None, passcode=None, level=None, **kwargs): if util.to_bool(delete) and promotion_id: qry = {'update': 'promotions', 'set_values': ('hidden'), 'where': 'id = ?'} cursor.execute(util.query(**qry), (1, promotion_id)) elif promotion_id: qry = {'update': 'promotions', 'set_values': ('title', 'description', 'start', '[end]', 'maximum', 'passcode', 'venue_id', 'level'), 'where': 'id = ?'} cursor.execute(util.query(**qry), (title, description, start, end, maximum, passcode, venue_id, level, promotion_id)) else: qry = {'insert_into': 'promotions', 'columns': ('title', 'description', 'start', '[end]', 'maximum', 'creator', 'passcode', 'venue_id', 'level')} cursor.execute(util.query(**qry), (title, description, start, end, maximum, user_id, passcode, venue_id, level)) return True
def _test_measures(numer_ids, numer_aggregate, section_tags, denom_reltype, geom): in_params = [] for numer_id in numer_ids: in_params.append({ 'numer_id': numer_id, 'normalization': 'predenominated' }) params = query(u''' SELECT {schema}OBS_GetMeta({geom}, '{in_params}') '''.format(schema='cdb_observatory.' if USE_SCHEMA else '', geom=geom, in_params=json.dumps(in_params))).fetchone()[0] # We can get duplicate IDs from multi-denominators, so for now we # compress those measures into a single params = OrderedDict([(p['id'], p) for p in params]).values() assert_equal(len(params), len(in_params), 'Inconsistent out and in params for {}'.format(in_params)) q = u''' SELECT * FROM {schema}OBS_GetData(ARRAY[({geom}, 1)::geomval], '{params}') '''.format(schema='cdb_observatory.' if USE_SCHEMA else '', geom=geom, params=json.dumps(params).replace(u"'", "''")) resp = query(q).fetchone() assert_is_not_none(resp, 'NULL returned for {}'.format(in_params)) rawvals = resp[1] vals = [v['value'] for v in rawvals] assert_equal(len(vals), len(in_params)) for i, val in enumerate(vals): assert_is_not_none(val, 'NULL for {}'.format(in_params[i]['numer_id']))
def test_getmeasure_performance(geom_complexity, api_method, normalization, geom, boundary): print(api_method, geom_complexity, normalization, geom, boundary) col = 'measure' if 'measure' in api_method.lower() else 'category' results = [] rownums = (1, 5, 10, ) if geom_complexity == 'complex' else (5, 25, 50, ) for rows in rownums: stmt = '''UPDATE obs_perftest_{complexity} SET {col} = {schema}{api_method}({args}) WHERE cartodb_id <= {n}'''.format( col=col, complexity=geom_complexity, schema='cdb_observatory.' if USE_SCHEMA else '', api_method=api_method, args=ARGS[api_method, normalization].format(geom, boundary), n=rows) start = time() query(stmt) end = time() qps = (rows / (end - start)) results.append({ 'rows': rows, 'qps': qps, 'stmt': stmt }) print(rows, ': ', qps, ' QPS') if 'OBS_RECORD_TEST' in os.environ: record({ 'geom_complexity': geom_complexity, 'api_method': api_method, 'normalization': normalization, 'boundary': boundary, 'geom': geom }, results)
def get(self, cursor=None, user_id=None, venue_id=None, getall=None, level=None, from_time=None, until_time=None, **kwargs): red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': 'promotion_id = promotions.id'} promo_qry = {'select': ['id', 'title', 'description', 'passcode', 'start', '[end]', 'maximum', 'creator', 'level', '(' + util.query(**red) + ') AS redemptions'], 'table': 'promotions', 'where': ['venue_id = ?', 'hidden != 1'], 'order_by': 'id DESC'} if from_time and until_time: own_red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': ('promotion_id = promotions.id', 'time >= ' + from_time, 'time < ' + until_time, 'user_id = ' + str(user_id))} promo_qry['select'].append('(' + util.query(**own_red) + ') AS own_redemptions') if not util.to_bool(getall): promo_qry['limit'] = 1 promo_qry['where'].append(str(util.now()) + ' >= start') promo_qry['where'].append('([end] = 0 OR [end] > ' + str(util.now()) + ')') promo_qry['where'].append('(maximum = 0 OR (' + util.query(**red) + ') < maximum)') promo_qry['where'].append(level + ' >= level') promo_qry['order_by'] = 'level DESC, id DESC' cursor.execute(util.query(**promo_qry), (venue_id,)) row = cursor.fetchone() if row: return {t[0]: val for t, val in zip(cursor.description, row)} else: return None cursor.execute(util.query(**promo_qry), (venue_id,)) return [util.row_to_dict(cursor, row) for row in cursor.fetchall()]
def test_getgeometryscores_performance(geom_complexity, api_method, filters, target_geoms): print(api_method, geom_complexity, filters, target_geoms) rownums = (1, 5, 10, ) if 'complex' in geom_complexity else (5, 25, 50,) results = [] for rows in rownums: stmt = '''SELECT {schema}{api_method}(geom, {filters}, {target_geoms}) FROM obs_perftest_{complexity} WHERE cartodb_id <= {n}'''.format( complexity=geom_complexity, schema='cdb_observatory.' if USE_SCHEMA else '', api_method=api_method, filters=filters, target_geoms=target_geoms, n=rows) start = time() query(stmt) end = time() qps = (rows / (end - start)) results.append({ 'rows': rows, 'qps': qps, 'stmt': stmt }) print(rows, ': ', qps, ' QPS') if 'OBS_RECORD_TEST' in os.environ: record({ 'geom_complexity': geom_complexity, 'api_method': api_method, 'filters': filters, 'target_geoms': target_geoms }, results)
def set(self, cursor=None, user_id=None, post_id=None, **kwargs): qry = {'select': 'id', 'table': 'post_reports', 'where': ('user_id = ?', 'post_id = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (user_id, post_id)) res = cursor.fetchone() if not res: qry = {'insert_into': 'post_reports', 'columns': ('user_id', 'post_id')} cursor.execute(util.query(**qry), (user_id, post_id)) return True
def set(self, cursor=None, facebook_id=None, user_id=None, venue_id=None, name=None, address=None, country=None, phone=None, email=None, email_verified=None, category_id=None, headline=None, tonight=None, website=None, facebook=None, twitter=None, v_facebook_id=None, twitter_id=None, twitter_token=None, twitter_secret=None, lat=None, lon=None, official=None, verified=None, customer_spend=None, authenticated=None, creator_version=None, **kwargs): data = {'name': name, 'address': address, 'country': country, 'phone': phone, 'email': email, 'email_verified': util.to_bool(email_verified), 'category_id': util.to_int(category_id), 'headline': headline, 'tonight': tonight, 'website': website, 'facebook': facebook, 'twitter': twitter, 'facebook_id': v_facebook_id, 'twitter_id': twitter_id, 'twitter_token': twitter_token, 'twitter_secret': twitter_secret, 'lat': util.to_float(lat), 'lon': util.to_float(lon), 'official': util.to_bool(official), 'verified': util.to_bool(verified), 'customer_spend': util.to_float(customer_spend), 'authenticated': util.to_bool(authenticated), 'creator': user_id, 'creator_version': creator_version} columns = [] values = [] for key, val in data.iteritems(): if val != None: columns.append(key) values.append(val) if venue_id: qry = {'update': 'venues', 'set_values': columns, 'where': 'id = ?'} values.append(venue_id) cursor.execute(util.query(**qry), values) else: qry = {'insert_into': 'venues', 'columns': columns} cursor.execute(util.query(**qry), values) cursor.execute(util.query(last_id=True)) return int(cursor.fetchone().identity)
def get(self, cursor=None, venue_id=None, **kwargs): nameqry = {'select': ('CONCAT(forename, \' \', SUBSTRING(surname, 1, 1))',), 'table': 'users', 'where': ('users.id = venue_comments.user_id',)} fbidqry = {'select': ('facebook_id',), 'table': 'users', 'where': ('users.id = venue_comments.user_id',)} qry = {'select': ('id', 'user_id', 'venue_id', 'time', 'comment', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_comments', 'where': ('venue_id = ?',), 'order_by': 'time DESC', 'limit': 10} cursor.execute(util.query(**qry), (venue_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, post_id=None, media_id=None, **kwargs): qry = {'insert_into': 'post_shares', 'columns': ('user_id', 'post_id', 'media_id', 'time')} cursor.execute(util.query(**qry), (user_id, post_id, media_id, util.now())) return True
def get(self, cursor=None, user_id=None, **kwargs): qry = {'select': ('id', 'term', 'time'), 'table': 'user_searches', 'where': 'user_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def set(self, cursor=None, user_id=None, venue_id=None, comment=None, **kwargs): qry = {'insert_into': 'venue_comments', 'columns': ('user_id', 'venue_id', 'time', 'comment')} cursor.execute(util.query(**qry), (user_id, venue_id, util.now(), comment)) return True
def get(self, cursor=None, **kwargs): qry = {'select': ('id', 'type'), 'table': 'venue_categories', 'where': '', 'order_by': 'type ASC'} cursor.execute(util.query(**qry)) return [util.row_to_dict(cursor, row) for row in cursor]
def query(company, page_results=10000): url = "https://api.rocketreach.co/v1/api/search" params = { "api_key": keys["rocketreach"]["key"], "company": "{}".format(company), "page_size": page_results } more_results = True while more_results: r = util.query(url, params) response = json.loads(r.text) if "error" in response or ("detail" in response and response["detail"] == "Invalid API key"): sys.stderr.write("Query error: {}\n".format(response)) sys.exit(1) if "profiles" in response: for profile in response["profiles"]: yield profile pagination = response["pagination"] if pagination["total"] > pagination["nextPage"]: params["start"] = pagination["nextPage"] else: more_results = False
def index(self, cursor=None, venue_id=None, user_id=None, hashd=None, **kwargs): if not venue_id or not user_id or not hashd: return 'Error!' qry = {'select': ('name', 'email', 'phone', 'website'), 'table': 'venues', 'where': ('id = ?')} cursor.execute(util.query(**qry), (venue_id,)) venue = cursor.fetchone() if not venue: return 'Error!' qry = {'select': ('forename', 'surname'), 'table': 'users', 'where': ('id = ?')} cursor.execute(util.query(**qry), (user_id,)) user = cursor.fetchone() if not user: return 'Error!' if hashd != hashlib.md5(venue.email + '|' + str(venue_id) + '|' + str(user_id) + '|confirm|' + os.environ['APP_SECRET']).hexdigest(): return 'Error!' qry = {'update': 'venues', 'set_values': ('email_verified'), 'where': 'id = ?'} cursor.execute(util.query(**qry), (1, venue_id)) with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'email_confirmed.txt'), 'rb') as f: msg = f.read() msg = msg.replace('[EmailAddress]', venue.email) msg = msg.replace('[PhoneNumber]', venue.phone) msg = msg.replace('[Website]', venue.website) msg = msg.replace('[Name]', user.forename + ' ' + user.surname) msg = msg.replace('[VenueName]', venue.name) subject = 'Thanks for verifying [EmailAddress], we will now complete the verification of [VenueName]' subject = subject.replace('[EmailAddress]', venue.email) subject = subject.replace('[VenueName]', venue.name) msg = email.mime.text.MIMEText(msg) msg['Subject'] = subject msg['From'] = os.environ['EMAIL'] msg['To'] = venue.email s = smtplib.SMTP(os.environ['SMTP_SERVER']) s.ehlo() s.starttls() s.ehlo() s.login(os.environ['SMTP_USER'], os.environ['SMTP_PASS']) s.sendmail(msg['From'], [msg['To']], msg.as_string()) s.quit() return 'Confirmed.'
def set(self, cursor=None, user_id=None, venue_id=None, caption=None, hide=None, post_id=None, image=None, **kwargs): if post_id and util.to_bool(hide): qry = {'update': 'posts', 'set_values': ('hidden'), 'where': 'id = ?'} cursor.execute(util.query(**qry), ('1', post_id)) return post_id else: qry = {'insert_into': 'posts', 'columns': ('user_id', 'venue_id', 'caption', 'time')} cursor.execute(util.query(**qry), (user_id, venue_id, caption, util.now())) cursor.execute(util.query(last_id=True)) post_added = int(cursor.fetchone().identity) azureutil.store(image.file, 'post', str(post_added)) return post_added
def set(self, cursor=None, user_id=None, venue_id=None, **kwargs): qry = {'select': 'id', 'table': 'venue_managers', 'where': ('user_id = ?', 'venue_id = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (user_id, venue_id)) res = cursor.fetchone() if not res: qry = {'insert_into': 'venue_managers', 'columns': ('user_id', 'venue_id', 'time')} cursor.execute(util.query(**qry), (user_id, venue_id, util.now())) qry = {'update': 'venues', 'set_values': ('official'), 'where': 'id = ?'} cursor.execute(util.query(**qry), (1, venue_id)) qry = {'select': ('name', 'email'), 'table': 'venues', 'where': ('id = ?')} cursor.execute(util.query(**qry), (venue_id,)) venue = cursor.fetchone() qry = {'select': ('forename', 'surname'), 'table': 'users', 'where': ('id = ?')} cursor.execute(util.query(**qry), (user_id,)) user = cursor.fetchone() with open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'email_confirm.txt'), 'rb') as f: msg = f.read() msg = msg.replace('[Name]', user.forename + ' ' + user.surname) msg = msg.replace('[VenueName]', venue.name) msg = msg.replace('[Link]', 'https://shnergle-api.azurewebsites.net/confirm/?venue_id=' + str(venue_id) + '&user_id=' + str(user_id) + '&hashd=' + hashlib.md5(venue.email + '|' + str(venue_id) + '|' + str(user_id) + '|confirm|' + os.environ['APP_SECRET']).hexdigest()) subject = 'Verify Email Address ownership for [VenueName] on Shnergle' subject.replace('[VenueName]', venue.name) msg = email.mime.text.MIMEText(msg) msg['Subject'] = subject msg['From'] = os.environ['EMAIL'] msg['To'] = venue.email s = smtplib.SMTP(os.environ['SMTP_SERVER']) s.ehlo() s.starttls() s.ehlo() s.login(os.environ['SMTP_USER'], os.environ['SMTP_PASS']) s.sendmail(msg['From'], [msg['To']], msg.as_string()) s.quit() return True
def set(self, cursor=None, user_id=None, staff_user_id=None, venue_id=None, manager=None, promo_perm=None, delete=None, **kwargs): if util.to_bool(delete): qry = {'delete': 'venue_staff', 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) qry = {'delete': 'venue_managers', 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) elif util.to_bool(manager): qry = {'select': 'id', 'table': 'venue_managers', 'where': ('user_id = ?', 'venue_id = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) res = cursor.fetchone() if not res: qry = {'delete': 'venue_staff', 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) qry = {'insert_into': 'venue_managers', 'columns': ('user_id', 'venue_id', 'time')} cursor.execute(util.query(**qry), (staff_user_id, venue_id, util.now())) else: qry = {'select': 'id', 'table': 'venue_staff', 'where': ('user_id = ?', 'venue_id = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) res = cursor.fetchone() if not res: qry = {'delete': 'venue_managers', 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (staff_user_id, venue_id)) qry = {'insert_into': 'venue_staff', 'columns': ('user_id', 'venue_id', 'time', 'promo_perm')} cursor.execute(util.query(**qry), (staff_user_id, venue_id, util.now(), 1 if util.to_bool(promo_perm) else 0)) else: qry = {'update': 'venue_staff', 'set_values': ('promo_perm'), 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (1 if util.to_bool(promo_perm) else 0, staff_user_id, venue_id)) return True
def set(self, cursor=None, user_id=None, term=None, **kwargs): qry = {'select': 'id', 'table': 'user_searches', 'where': ('user_id = ?', 'term = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (user_id, term)) res = cursor.fetchone() if res: qry = {'update': 'user_searches', 'set_values': ('time'), 'where': 'user_id = ?'} cursor.execute(util.query(**qry), (util.now(), user_id)) else: qry = {'insert_into': 'user_searches', 'columns': ('user_id', 'term', 'time')} cursor.execute(util.query(**qry), (user_id, term, util.now())) return True
def set(self, cursor=None, user_id=None, promotion_id=None, **kwargs): cnt = {'select': ('COUNT(id)'), 'table': 'promotion_redemptions', 'where': ('promotion_id = promotions.id')} promo = {'select': ('[end]', 'maximum', 'passcode', '(' + util.query(**cnt) + ') AS count'), 'table': 'promotions', 'where': ('id = ?')} cursor.execute(util.query(**promo), (promotion_id,)) row = cursor.fetchone() if int(row.end) != 0 and int(row.end) < util.now(): return 'time' if int(row.maximum) != 0 and int(row.count) >= int(row.maximum): return 'number' qry = {'insert_into': 'promotion_redemptions', 'columns': ('user_id', 'promotion_id', 'time')} cursor.execute(util.query(**qry), (user_id, promotion_id, util.now())) return row.passcode
def set(self, cursor=None, user_id=None, venue_id=None, following=None, **kwargs): qry = {'select': 'id', 'table': 'venue_followers', 'where': ('user_id = ?', 'venue_id = ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (user_id, venue_id)) res = cursor.fetchone() if util.to_bool(following) and not res: qry = {'insert_into': 'venue_followers', 'columns': ('user_id', 'venue_id')} cursor.execute(util.query(**qry), (user_id, venue_id)) elif not util.to_bool(following) and res: qry = {'delete': 'venue_followers', 'where': ('user_id = ?', 'venue_id = ?')} cursor.execute(util.query(**qry), (user_id, venue_id)) return True
def train_al_and_report(model_name, kernel, warp, ard): dataset_dir = os.path.join(MODEL_DIR, DATASET) try: os.makedirs(dataset_dir) except OSError: print "skipping output folder" for fold in xrange(1): fold_dir = os.path.join(SPLIT_DIR, DATASET, str(fold)) train_data = np.loadtxt(os.path.join(fold_dir, 'train')) test_data = np.loadtxt(os.path.join(fold_dir, 'test')) params_file = None output_dir = os.path.join(dataset_dir, str(fold)) try: os.makedirs(output_dir) except OSError: print "skipping output folder" if ard: iso_dir = output_dir.replace('True', 'False') params_file = os.path.join(iso_dir, 'params') # Split train into train and pool pool_data = train_data[50:] train_data = train_data[:50] metrics_list = [] while True: # Train gp gp = util.train_gp_model(train_data, kernel, warp, ard, params_file) # Get metrics on test metrics = util.get_metrics(gp, test_data) metrics_list.append([metrics[0], metrics[1], metrics[2][0], metrics[2][1], metrics[3]]) # Predict pool and select instance in pool with higher variance new_instance, new_i = util.query(gp, pool_data) # Update train and pool train_data = np.append(train_data, [new_instance], axis=0) pool_data = np.delete(pool_data, (new_i), axis=0) #if pool_data.shape[0] == 0: # break if train_data.shape[0] == 500: break print pool_data.shape gc.collect(2) # Final metrics on full train set (sanity check) gp = util.train_gp_model(train_data, kernel, warp, ard, params_file) metrics = util.get_metrics(gp, test_data) metrics_list.append([metrics[0], metrics[1], metrics[2][0], metrics[2][1], metrics[3]]) util.save_metrics_list(metrics_list, os.path.join(output_dir, 'metrics'))
def get(self, cursor=None, user_id=None, **kwargs): qry = {'select': ('venues.name', 'promotion_redemptions.time', 'promotions.passcode', 'promotions.description'), 'left_join': ('promotions', 'venues'), 'on': ('promotion_redemptions.promotion_id = promotions.id', 'venues.id = promotions.venue_id'), 'table': 'promotion_redemptions', 'where': ('promotion_redemptions.user_id = ?'), 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (user_id,)) return [util.row_to_dict(cursor, row) for row in cursor]
def retrieve(self, cursor=None, user_id=None, term=None): if util.to_bool(term): qry = {'select': ['id', 'facebook_id', 'forename', 'surname' ], 'table': 'users', 'where': ("CONCAT(forename, \' \', surname) LIKE ?",), 'order_by': 'surname ASC, forename ASC'} cursor.execute(util.query(**qry), ("%" + term.replace(' ', "%") + "%",)) return [util.row_to_dict(cursor, row) for row in cursor] else: qry = {'select': ['id', 'facebook', 'twitter', 'forename', 'surname', 'age', 'birth_day', 'birth_month', 'birth_year', 'gender', 'employee', 'joined', 'country', 'language', 'email', 'top5', 'save_locally', 'last_login', 'last_facebook', 'last_twitter' ], 'table': 'users', 'order_by': 'id'} qry['select'].append('twitter_id') qry['select'].append('twitter_token') qry['select'].append('twitter_secret') qry.update({'where': 'id = ?', 'limit': 1}) cursor.execute(util.query(**qry), (user_id,)) res = cursor.fetchone() return util.row_to_dict(cursor, res)
def get(self, cursor=None, venue_id=None, from_time=None, until_time=None, own=None, user_id=None, **kwargs): qry = {'select': 'COUNT(id) AS cnt', 'table': 'venue_rsvps', 'where': ('venue_id = ?', 'maybe = 1', 'going = 0', 'time >= ?', 'time < ?')} values = (venue_id, from_time, until_time) if util.to_bool(own): qry['where'] += ('user_id = ?',) values += (user_id,) cursor.execute(util.query(**qry), values) maybe = cursor.fetchone().cnt qry = {'select': 'COUNT(id) AS cnt', 'table': 'venue_rsvps', 'where': ('venue_id = ?', 'going = 1', 'time >= ?', 'time < ?')} if util.to_bool(own): qry['where'] += ('user_id = ?',) cursor.execute(util.query(**qry), values) going = cursor.fetchone().cnt return {'maybe': maybe, 'going': going}
def get(self, cursor=None, venue_id=None, **kwargs): nameqry = {'select': ('CONCAT(forename, \' \', surname)',), 'table': 'users'} fbidqry = {'select': ('facebook_id',), 'table': 'users'} nameqry['where'] = ('users.id = venue_staff.user_id',) fbidqry['where'] = nameqry['where'] qry = {'select': ('id', 'user_id', 'promo_perm', 'time', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_staff', 'where': 'venue_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (venue_id,)) staff = [util.row_to_dict(cursor, row) for row in cursor] nameqry['where'] = ('users.id = venue_managers.user_id',) fbidqry['where'] = nameqry['where'] qry = {'select': ('id', 'user_id', 'time', '(' + util.query(**nameqry) + ') AS name', '(' + util.query(**fbidqry) + ') AS facebook_id'), 'table': 'venue_managers', 'where': 'venue_id = ?', 'order_by': 'time DESC'} cursor.execute(util.query(**qry), (venue_id,)) managers = [util.row_to_dict(cursor, row) for row in cursor] return {'staff': staff, 'managers': managers}
def get(self, cursor=None, user_id=None, entity=None, entity_id=None, **kwargs): if not entity or not entity_id: raise cherrypy.HTTPError(404) if entity == 'venue': entity = 'post' venue_id = entity_id subqry = {'select': 'COUNT(id)', 'table': 'post_reports', 'where': ('post_id = posts.id')} qry = {'select': ('id', 'venue_id', 'time'), 'table': 'posts', 'where': ('venue_id = ?', 'hidden = 0', '(' + util.query(**subqry) + ') < 3' #, #'time > ' + str(util.now() - 691200)), ), 'order_by': 'time DESC', 'limit': 50} cursor.execute(util.query(**qry), (entity_id,)) try: entity_id = str(cursor.fetchone().id) qry = {'insert_into': 'venue_loads', 'columns': ('user_id', 'venue_id', 'time')} cursor.execute(util.query(**qry), (user_id, venue_id, util.now())) image = azureutil.retrieve(entity, entity_id) if not image: image = cherrypy.thread_data.placeholder_image except: image = cherrypy.thread_data.placeholder_image else: image = azureutil.retrieve(entity, entity_id) if not image: image = cherrypy.thread_data.placeholder_image #if image: # cherrypy.response.headers['Content-Type'] = 'image/jpeg' # return image cherrypy.response.headers['Content-Type'] = 'image/jpeg' return image
def set(self, cursor=None, user_id=None, venue_id=None, maybe=None, going=None, from_time=None, until_time=None, **kwargs): qry = {'select': 'id', 'table': 'venue_rsvps', 'where': ('user_id = ?', 'venue_id = ?', 'time >= ?', 'time < ?'), 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (user_id, venue_id, from_time, until_time)) res = cursor.fetchone() if res: values = [] columns = [] if maybe: values.append(util.to_bool(maybe)) columns.append('maybe') if going: values.append(util.to_bool(going)) columns.append('going') values.append(res.id) qry = {'update': 'venue_rsvps', 'set_values': columns, 'where': 'id = ?'} cursor.execute(util.query(**qry), values) else: values = [user_id, venue_id, util.now()] columns = ['user_id', 'venue_id', 'time'] if maybe: values.append(util.to_bool(maybe)) columns.append('maybe') if going: values.append(util.to_bool(going)) columns.append('going') qry = {'insert_into': 'venue_rsvps', 'columns': columns} cursor.execute(util.query(**qry), values) return True
def wrap(cls, object_id=None, form_data=None, request=None, session=None): form = wrapped(cls) if object_id is not None: obj = util.query(cls, object_id, request=request, session=session) if obj is not None: for field_name in form.__dict__.keys(): attribute_name = form.__dict__[field_name].attr value = obj.__dict__[attribute_name] form.__dict__[field_name].data = value elif form_data is not None: for field_name in form_data.keys(): if field_name in form.__dict__.keys(): form.__dict__[field_name].data = form_data[field_name] setattr(form, '__form_name__', cls.__form_name__) setattr(form, '__form_attached_to__', cls) return form
def thresholds(self, cursor): users = {'select': 'COUNT(id) AS count', 'table': 'users'} cursor.execute(util.query(**users)) users = cursor.fetchone().count thresholds = [] for percent in (0.8, 0.95, 0.99): number = math.floor(percent * users) venue_shares = {'select': 'COUNT(id)', 'table': 'venue_shares', 'where': ('user_id = users.id', 'time > ' + str(util.now() - 2592000))} post_shares = {'select': 'COUNT(id)', 'table': 'post_shares', 'where': ('user_id = users.id', 'time > ' + str(util.now() - 2592000))} comments = {'select': 'COUNT(id)', 'table': 'venue_comments', 'where': ('user_id = users.id', 'time > ' + str(util.now() - 2592000))} likes = {'select': 'COUNT(id)', 'table': 'post_likes', 'where': ('user_id = users.id', 'time > ' + str(util.now() - 2592000))} posts = {'select': 'COUNT(id)', 'table': 'posts', 'where': ('user_id = users.id', 'time > ' + str(util.now() - 2592000))} thresholdqry = {'select': ('((' + util.query(**venue_shares) + ') * 5 + ' + '(' + util.query(**post_shares) + ') * 5 + ' + '(' + util.query(**posts) + ') * 4 + ' + '(' + util.query(**comments) + ') * 2 + ' + '(' + util.query(**likes) + ') * 2) AS count',), 'table': 'users', 'group_by': 'id', 'order_by': 'count', 'limit': (number - 1, 1)} cursor.execute(util.query(**thresholdqry)) count = cursor.fetchone() thresholds.append(count.count if count else 0) return thresholds
'fr.insee.P12_RP_CASE', 'fr.insee.P12_RP_TTEGOU', 'fr.insee.P12_RP_ELEC', 'fr.insee.P12_ACTOCC15P_ILT45D', 'fr.insee.P12_RP_CHOS', 'fr.insee.P12_RP_HABFOR', 'fr.insee.P12_RP_EAUCH', 'fr.insee.P12_RP_BDWC', 'fr.insee.P12_RP_MIDUR', 'fr.insee.P12_RP_CLIM', 'fr.insee.P12_RP_MIBOIS', 'fr.insee.P12_RP_CASE', 'fr.insee.P12_RP_TTEGOU', 'fr.insee.P12_RP_ELEC', 'fr.insee.P12_ACTOCC15P_ILT45D', 'uk.ons.LC3202WA0007', 'uk.ons.LC3202WA0010', 'uk.ons.LC3202WA0004', 'uk.ons.LC3204WA0004', 'uk.ons.LC3204WA0007', 'uk.ons.LC3204WA0010', 'br.geo.subdistritos_name' ]) MEASURE_COLUMNS = query(''' SELECT ARRAY_AGG(DISTINCT numer_id) numer_ids, numer_aggregate, denom_reltype, section_tags FROM observatory.obs_meta WHERE numer_weight > 0 AND numer_id NOT IN ('{skip}') AND section_tags IS NOT NULL AND subsection_tags IS NOT NULL GROUP BY numer_aggregate, section_tags, denom_reltype '''.format(skip="', '".join(SKIP_COLUMNS))).fetchall() #CATEGORY_COLUMNS = query(''' #SELECT distinct numer_id #FROM observatory.obs_meta #WHERE numer_type ILIKE 'text' #AND numer_weight > 0 #''').fetchall() # #BOUNDARY_COLUMNS = query(''' #SELECT id FROM observatory.obs_column
'''INSERT INTO obs_perftest_complex (point, geom, offset_geom, name) SELECT ST_PointOnSurface(the_geom) point, the_geom geom, ST_Translate(the_geom, -0.1, 0.1) offset_geom, geom_refs AS name FROM (SELECT * FROM {schema}OBS_GetBoundariesByGeometry( st_makeenvelope(-75.05437469482422,40.66319159533881, -73.81885528564453,41.745696344339564, 4326), 'us.census.tiger.county_clipped')) foo ORDER BY ST_NPoints(the_geom) DESC LIMIT 50;'''): q_formatted = q.format( schema='cdb_observatory.' if USE_SCHEMA else '', ) start = time() resp = query(q_formatted) end = time() print('{} for {}'.format(int(end - start), q_formatted)) if q.lower().startswith('insert'): if resp.rowcount == 0: raise Exception('''Performance fixture creation "{}" inserted 0 rows, this will break tests. Check the query to determine what is going wrong.'''.format(q_formatted)) commit() ARGS = { ('OBS_GetMeasureByID', None): "name, 'us.census.acs.B01001002', '{}'", ('OBS_GetMeasure', 'predenominated'): "{}, 'us.census.acs.B01003001', null, {}", ('OBS_GetMeasure', 'area'): "{}, 'us.census.acs.B01001002', 'area', {}", ('OBS_GetMeasure', 'denominator'): "{}, 'us.census.acs.B01001002', 'denominator', {}",
def query(q): url = "http://avoindata.prh.fi/bis/v1/" return util.query(url + q)
import time import json import random import pandas as pd import urllib.parse import urllib.request import wikiparser as parser import wikipedia as wiki from pprint import pprint from nltk.tokenize import word_tokenize import util import integration QA = integration.QustionAnswering() myquery = util.query() app = Flask(__name__, static_folder='static') app.config['DEBUG'] = True def get_url(string): try: tmp = wiki.page(string) return tmp.url except Exception as e: info = e.__str__() entities = [] entities = info.split("\n")[1:-3] if(len(entities) == 0): return "" return wiki.page(entities[0]).url
def index(): # list of group numbers groups = [1, 2, 3, 4] # list to hold group data user_data = [] # list of all countries countries = ['USA', 'Canada', 'UK', 'Romania', 'Switzerland', 'Rwanda', 'Hong Kong', 'France', 'C yprus', \ 'Israel', 'Portugal', 'Ireland I', 'Germany', 'Australia', 'China', 'New Zealand', 'Palestine'] # loop to query data for each group for x in groups: # loop split data by country for country in countries: data = util.query('WebAppsDatabase.db', x, country) # loop to split data if there are more than 10 elements if len(data) >= 10: labels = util.cluster_user_data(data) data = util.split_user_data(data, labels) # adds data to user_data list user_data.append(data) # loop to see if an element is a list of split data # Only used to see which countries are at which index and have split data for data in user_data: if len(data) != 0: print(data[0][1]) print(user_data.index(data)) print("\n") if isinstance(data[0], list): country = util.get_country(data[0][0]) print("***SPLIT***") print(country) print(user_data.index(data)) print("\n") return render_template('index.html', column_html=column_names, data1usa1_html=user_data[0][0], data1usa2_html=user_data[0][1], data1usa3_html=user_data[0][2], data1canada_html=user_data[1], data1uk_html=user_data[2], data1romania1_html=user_data[3][0], data1romania2_html=user_data[3][1], data1romania3_html=user_data[3][2], data1switz_html=user_data[4], data1rwanda_html=user_data[5], data1isreal_html=user_data[9], data1germany_html=user_data[12], data2usa1_html=user_data[17][0], data2usa2_html=user_data[17][1], data2usa3_html=user_data[17][2], data2canada_html=user_data[18], data2uk_html=user_data[19], data2romania_html=user_data[20], data2switz_html=user_data[21], data2rwanda_html=user_data[22], data2hongkong_html=user_data[23], data2france_html=user_data[24], data2germany_html=user_data[29], data2nz_html=user_data[32], data2pal_html=user_data[33], data3usa1_html=user_data[34][0], data3usa2_html=user_data[34][1], data3usa3_html=user_data[34][2], data3canada_html=user_data[35], data3uk_html=user_data[36], data3romania1_html=user_data[37][0], data3romania2_html=user_data[37][1], data3romania3_html=user_data[37][2], data3switz1_html=user_data[38][0], data3switz2_html=user_data[38][1], data3switz3_html=user_data[38][2], data3rwanda_html=user_data[39], data3portugal_html=user_data[44], data3ireland_html=grp3ireland, data3germany_html=user_data[46], data3australia_html=user_data[47], data3china_html=user_data[48], data4usa1_html=user_data[51][0], data4usa2_html=user_data[51][1], data4usa3_html=user_data[51][2], data4canada1_html=user_data[52][0], data4canada2_html=user_data[52][1], data4canada3_html=user_data[52][2], data4uk_html=user_data[53], data4romania1_html=user_data[54][0], data4romania2_html=user_data[54][1], data4romania3_html=user_data[54][2], data4switz_html=user_data[55], data4portugal_html=user_data[61], data4germany_html=user_data[63], data4australia_html=user_data[64])
def get(self, cursor=None, user_id=None, term=None, following_only=None, my_lat=None, my_lon=None, distance=None, own=None, quiet=None, trending=None, from_time=None, until_time=None, promotions=None, level=None, around_me=None, **kwargs): subqry = {'select': 'COUNT(id)', 'table': 'venue_followers', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} red = {'select': 'COUNT(id)', 'table': 'promotion_redemptions', 'where': 'promotion_id = promotions.id'} promoqry = {'select': 'COUNT(id)', 'table': 'promotions', 'where': ('venue_id = venues.id', str(util.now()) + ' >= start', '([end] = 0 OR [end] > ' + str(util.now()) + ')', '(maximum = 0 OR (' + util.query(**red) + ') < maximum)', level + ' >= level', 'hidden != 1')} managerqry = {'select': 'COUNT(id)', 'table': 'venue_managers', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} staffqry = {'select': 'COUNT(id)', 'table': 'venue_staff', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} staffppqry = {'select': 'SUM(promo_perm)', 'table': 'venue_staff', 'where': ('user_id = ' + str(user_id), 'venue_id = venues.id')} fields = ['id', 'name', 'address', 'country', 'phone', 'email', 'email_verified', 'category_id', 'headline', 'tonight', 'website', 'facebook', 'twitter', 'facebook_id', 'twitter_id', 'twitter_token', 'twitter_secret', 'lat', 'lon', 'official', 'verified', 'customer_spend', 'authenticated', 'creator', '(' + util.query(**managerqry) + ') AS manager', '(' + util.query(**staffqry) + ') AS staff', '(' + util.query(**staffppqry) + ') AS promo_perm', "(" + util.query(**subqry) + ") AS following", '(' + util.query(**promoqry) + ') AS promotions'] order_by = ('name ASC',) if term: where = ("name LIKE ?",) elif util.to_bool(following_only): where = ("(" + util.query(**subqry) + ") > 0") elif own: where = ('(' + util.query(**managerqry) + ') = 1 OR (' + util.query(**staffqry) + ') = 1') elif my_lat and my_lon and distance: maybe = {'select': 'COUNT(id)', 'table': 'venue_rsvps', 'where': ('maybe = 1', 'venue_id = venues.id', 'going = 0', 'time >= ?', 'time < ?')} going = {'select': 'COUNT(id)', 'table': 'venue_rsvps', 'where': ('going = 1', 'venue_id = venues.id', 'time >= ?', 'time < ?')} if util.to_bool(quiet): order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 ASC',) elif util.to_bool(trending): order_by = ('(' + util.query(**maybe) +') + (' + util.query(**going) +') * 2 DESC',) else: order_by = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) ASC',) where = ('((lat - ?) * (lat - ?) + (lon - ?) * (lon - ?)) <= ? * ?',) if util.to_bool(promotions): where += ('(' + util.query(**promoqry) + ') > 0',) elif util.to_bool(quiet) or util.to_bool(trending): fields[0] = 'TOP(12) id' elif util.to_bool(around_me): psubqry = {'select': 'COUNT(id)', 'table': 'post_reports', 'where': ('post_id = posts.id')} post_count = {'select': 'CASE WHEN COUNT(id) > 0 THEN 1 ELSE 0 END', 'table': 'posts', 'where': ('posts.venue_id = venues.id', 'hidden = 0', '(' + util.query(**psubqry) + ') < 3', 'time > ' + str(util.now() - 691200))} order_by = ('(' + util.query(**post_count) + ') DESC',) + order_by else: where = '' qry = {'select': fields, 'table': 'venues', 'where': where, 'order_by': order_by} if term: cursor.execute(util.query(**qry), ("%" + term + "%",)) return [util.row_to_dict(cursor, row) for row in cursor] else: values = tuple() if my_lat and my_lon and distance: values += (float(my_lat), float(my_lat), float(my_lon), float(my_lon), float(distance), float(distance)) if util.to_bool(quiet) is None and util.to_bool(trending) is None: values += (float(my_lat), float(my_lat), float(my_lon), float(my_lon)) else: values += (from_time, until_time, from_time, until_time) cursor.execute(util.query(**qry), values) return [util.row_to_dict(cursor, row) for row in cursor]
def query(q): url = "https://www.asiakastieto.fi/yritykset/haku" parameter_dict = {"type": "BUSINESS"} parameter_dict["q"] = q r = util.query(url, parameter_dict) return r.text
def test_getdata_performance(geom_complexity, normalization, geom, boundary): print(geom_complexity, normalization, geom, boundary) cols = ['us.census.acs.B01001002', 'us.census.acs.B01001003', 'us.census.acs.B01001004', 'us.census.acs.B01001005', 'us.census.acs.B01001006', 'us.census.acs.B01001007', 'us.census.acs.B01001008', 'us.census.acs.B01001009', 'us.census.acs.B01001010', 'us.census.acs.B01001011', ] in_meta = [{"numer_id": col, "normalization": normalization, "geom_id": None if boundary.lower() == 'null' else boundary.replace("'", '')} for col in cols] rownums = (1, 5, 10, ) if geom_complexity == 'complex' else (10, 50, 100) for num_meta in (1, 10, ): results = [] for rows in rownums: stmt = ''' with data as ( SELECT id, data FROM {schema}OBS_GetData( (SELECT array_agg(({geom}, cartodb_id)::geomval) FROM obs_perftest_{complexity} WHERE cartodb_id <= {n}), (SELECT {schema}OBS_GetMeta( (SELECT st_setsrid(st_extent({geom}), 4326) FROM obs_perftest_{complexity} WHERE cartodb_id <= {n}), '{in_meta}'::JSON )) )) UPDATE obs_perftest_{complexity} SET measure = (data->0->>'value')::Numeric FROM data WHERE obs_perftest_{complexity}.cartodb_id = data.id ; '''.format( point_or_poly='point' if geom == 'point' else 'polygon', complexity=geom_complexity, schema='cdb_observatory.' if USE_SCHEMA else '', geom=geom, in_meta=json.dumps(in_meta[0:num_meta]), n=rows) start = time() query(stmt) end = time() qps = (rows / (end - start)) results.append({ 'rows': rows, 'qps': qps, 'stmt': stmt }) print(rows, ': ', qps, ' QPS') if 'OBS_RECORD_TEST' in os.environ: record({ 'geom_complexity': geom_complexity, 'api_method': 'OBS_GetData', 'normalization': normalization, 'boundary': boundary, 'geom': geom, 'num_meta': str(num_meta) }, results)
def get(self, cursor=None, user_id=None, **kwargs): t = self.thresholds(cursor) posts_total = {'select': 'COUNT(id) AS count', 'table': 'posts', 'where': 'user_id = ?'} cursor.execute(util.query(**posts_total), (user_id,)) posts_total = cursor.fetchone().count following_total = {'select': 'COUNT(id) AS count', 'table': 'venue_followers', 'where': 'user_id = ?'} cursor.execute(util.query(**following_total), (user_id,)) following_total = cursor.fetchone().count redemptions_total = {'select': 'COUNT(id) AS count', 'table': 'promotion_redemptions', 'where': 'user_id = ?'} cursor.execute(util.query(**redemptions_total), (user_id,)) redemptions_total = cursor.fetchone().count posts = {'select': 'COUNT(id) AS count', 'table': 'posts', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**posts), (user_id,)) posts = cursor.fetchone().count comments = {'select': 'COUNT(id) AS count', 'table': 'venue_comments', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**comments), (user_id,)) comments = cursor.fetchone().count likes = {'select': 'COUNT(id) AS count', 'table': 'post_likes', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**likes), (user_id,)) likes = cursor.fetchone().count redemptions = {'select': 'COUNT(id) AS count', 'table': 'promotion_redemptions', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**redemptions), (user_id,)) redemptions = cursor.fetchone().count share_venue = {'select': 'COUNT(id) AS count', 'table': 'venue_shares', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**share_venue), (user_id,)) share_venue = cursor.fetchone().count share_posts = {'select': 'COUNT(id) AS count', 'table': 'post_shares', 'where': ('user_id = ?', 'time >' + str(util.now() - 2592000))} cursor.execute(util.query(**share_posts), (user_id,)) share_posts = cursor.fetchone().count score = ((share_posts + share_venue) * 5 + posts * 4 + comments * 2 + likes * 2) for threshold in range(len(t)): if score < t[threshold]: res = threshold break else: res = 3 return {'thresholds': t, 'level': res, 'posts_total': posts_total, 'following_total': following_total, 'redemptions_total': redemptions_total, 'posts': posts, 'redemptions': redemptions, 'share': share_posts + share_venue, 'rsvps': 0, 'comments': comments, 'likes': likes, 'score': score}
def set(self, cursor=None, user_id=None, post_id=None, **kwargs): qry = {'insert_into': 'post_views', 'columns': ('user_id', 'post_id')} cursor.execute(util.query(**qry), (user_id, post_id)) return True
def home(id): c_list = util.query(id) return render_template('pages/home.html', results=c_list) ## TODO fix this (should include distance as second param)
def set(self, cursor=None, facebook_id=None, twitter_token=None, facebook=None, twitter=None, forename=None, surname=None, age=None, birth_day=None, birth_month=None, birth_year=None, gender=None, employee=None, country=None, language=None, email=None, top5=None, twitter_id=None, twitter_secret=None, save_locally=None, app_version=None, iphone_model=None, ios_version=None, last_facebook=None, last_twitter=None, **kwargs): if not facebook_id: raise cherrypy.HTTPError(403) qry = {'select': 'COUNT(id) AS count', 'table': 'users', 'where': 'facebook_id = ?'} cursor.execute(util.query(**qry), (facebook_id,)) res = cursor.fetchone().count data = {'twitter_id': twitter_id, 'twitter_token': twitter_token, 'twitter_secret': twitter_secret, 'facebook': facebook, 'twitter': twitter, 'forename': forename, 'surname': surname, 'age': util.to_int(age), 'birth_day': util.to_int(birth_day), 'birth_month': util.to_int(birth_month), 'birth_year': util.to_int(birth_year), 'gender': gender, 'employee': util.to_bool(employee), 'country': country, 'language': language, 'email': email, 'top5': util.to_bool(top5), 'save_locally': util.to_bool(save_locally), 'app_version': app_version, 'iphone_model': iphone_model, 'ios_version': ios_version, 'last_login': util.now(), 'last_facebook': util.to_bool(last_facebook), 'last_twitter': util.to_bool(last_twitter)} columns = [] values = [] for key, val in data.iteritems(): if val != None: columns.append(key) if val is not True and val is not False: values.append(val) else: if val: values.append('1') else: values.append('0') values.append(facebook_id) if res: qry = {'update': 'users', 'set_values': columns, 'where': 'facebook_id = ?'} cursor.execute(util.query(**qry), values) else: columns.append('facebook_id') columns.append('joined') values.append(util.now()) qry = {'insert_into': 'users', 'columns': columns} cursor.execute(util.query(**qry), values) qry = {'select': 'id', 'table': 'users', 'where': 'facebook_id = ?', 'order_by': 'id', 'limit': 1} cursor.execute(util.query(**qry), (facebook_id,)) user_id = cursor.fetchone().id return self.retrieve(cursor=cursor, user_id=user_id)