def get_officials_by_name(query): ''' Query the database to get all officials that have a name match with the query string. ''' # Normalize query rm_punct_trans = str.maketrans('', '', '.,:;?[]{}()-_+=*&%$#@!') query = str(query).translate(rm_punct_trans) tokens = [tok.strip() for tok in query.split() if tok.strip()] if len(tokens) == 0: return None select_by_name = ''' SELECT * FROM officials_info WHERE {like_clause} ''' like_clause = ''' OR '''.join("LOWER(name) LIKE '%{tok}%'".format(tok=tok) for tok in tokens) cursor = get_db().cursor() # print(select_by_name.format(like_clause=like_clause)) cursor.execute(select_by_name.format(like_clause=like_clause)) results = [dict(res) for res in cursor.fetchall()] partial_matches = [] full_matches = [] for res in results: if str(res['name']).lower().translate(rm_punct_trans) == query: full_matches.append(res) else: partial_matches.append(res) return full_matches + partial_matches
def editCampaign(campaign_id): import db_helpers db = db_helpers.get_db() print(campaign_id) campaign = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id)) print("campaign is", campaign) events = db_helpers.query_db('select * from events where campaign = %s'%(campaign_id)) edit_campaign_form = request.form if request.method == 'POST': query = db_helpers.query_db('update campaigns set name = "%s", description = "%s", tags = "%s", start_date = "%s", end_date = "%s", comments_target = "%s", sentiment_score = "%s", likes_target = "%s" where id=%s'%( edit_campaign_form['campaign_name'], edit_campaign_form['campaign_description'], edit_campaign_form['tags'], edit_campaign_form['start_date'], edit_campaign_form['end_date'], edit_campaign_form['comment_count'], edit_campaign_form['sentiment_score'], edit_campaign_form['like_count'], campaign_id )) db.commit() q1 = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id)) return render_template("editCampaign.html", campaign=campaign)
def get_officials_by_address(query): ''' Query the database to get all officials that represent a particular address. ''' # Get state, congressional district from Google Civic Information API state, cd, predicted_address = get_state_district_for_address(query) if state is None or cd is None: return None select_by_address = ''' SELECT * FROM officials_info WHERE state = :state AND ( /* Senators */ LOWER(role) LIKE 'senator%' /* Representatives of particular congressional district */ OR ( LOWER(role) = 'representative' AND district = :cd ) ) ; ''' cursor = get_db().cursor() cursor.execute(select_by_address, {'state': state, 'cd': cd}) return [dict(res) for res in cursor.fetchall()]
def attr_to_str(collection_id, headers, attr): db = get_db(headers) docs = db.read(collection_id) for doc in docs: if attr in doc.keys(): doc[attr] = str(doc[attr]) db.update(collection_id, docs)
def register(): import db_helpers from db_helpers import query_db registration_form = RegistrationForm(request.form) db = db_helpers.get_db() cur = db.cursor() if request.method == 'POST' and registration_form.validate(): result = request.form # check that the company doesn't already exist # make db entry #print('insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url'])) cur.execute( 'insert into users (email, password, companyName, companyWebsite, companyFacebook) values ("%s", "%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_website'], result['company_facebook']) ) db.commit() #log in user straight away user = query_db('select * from users where email = "%s" and password = "******"' % (result['email'], result['password']), (), True) user = load_user(user[0]) login_user(user) return redirect('/trackCampaigns') return render_template("reg.html", form=registration_form)
def scheduleCampaign(): import db_helpers db = db_helpers.get_db() campaign_id = request.args.get('campaign_id') print("---", campaign_id) events = return_events(campaign_id) print("!!!",events) #overwrite events.json with open("events.json", "w") as jsonFile: json.dump(events, jsonFile) event_form = EventForm(request.form) if request.method == 'POST': q = 'insert into events values (null, "%s", "%s", "%s", "%s", "%s", " %s")' % ( getval(event_form['event_name']), getval(event_form['event_description']), getval(event_form['event_type']), getval(event_form['start_date']), getval(event_form['end_date']), campaign_id ) query = db_helpers.query_db(q) db.commit() return render_template('scheduleCampaign.html', form=event_form, events=events, campaign_id=campaign_id) return render_template('scheduleCampaign.html', form=event_form, events=events, campaign_id=campaign_id)
def createCampaign(): import db_helpers db = db_helpers.get_db() create_campaign_form = request.form if request.method == 'POST': query = db_helpers.query_db('insert into campaigns (name, description, tags, start_date, end_date, comments_target, sentiment_score, likes_target) values ("%s", "%s", "%s", "%s", "%s", %s, %s, %s)'%( create_campaign_form['campaign_name'], create_campaign_form['campaign_description'], create_campaign_form['tags'], create_campaign_form['start_date'], create_campaign_form['end_date'], create_campaign_form['comment_count'], create_campaign_form['sentiment_score'], create_campaign_form['like_count'] )) db.commit() campaign_id = db_helpers.query_db('select id from campaigns where name = "%s"'%(create_campaign_form['campaign_name']), (), True) query = db_helpers.query_db('insert into user_campaigns (user_id, campaign_id) values (%s, %s)'%(current_user.id, campaign_id[0])) db.commit() return render_template("createCampaign.html") return render_template("createCampaign.html")
def str_to_float(mode, collection_id, attr): db = get_db(mode) docs = db.read(collection_id) print docs for doc in docs: is_dollar = u'$' in doc[attr] # replace(u'£', u'') doc[attr] = doc[attr].replace(u'\xa33', u'').replace(u'\xa30', u'').replace('n/a', '-1').replace(',', '').\ replace('From ', '').replace('From: ', '').replace(' / 15 sachets', '').replace('from just ', '').\ replace('from ', '').replace(u'USD $', '').replace(u'$', '') doc[attr] = doc[attr].strip() if not doc[attr]: doc[attr] = '-1' full_hyphen_pattern = '[0-9]+-[0-9]+' half_hyphen_pattern = '[0-9]+-' print doc[attr] if re.match(full_hyphen_pattern, doc[attr]): try: half_match = re.match(half_hyphen_pattern, doc[attr]) doc[attr] = re.sub(full_hyphen_pattern, half_match.group(0), doc[attr]).replace('-', '') except Exception as e: print e print doc[attr] raise e try: doc[attr] = float(doc[attr]) if is_dollar: doc[attr] *= 0.76 except Exception as e: print e print doc print doc[attr] raise e print docs db.update(collection_id, docs)
def register(): import db_helpers registration_form = RegistrationForm(request.form) db = db_helpers.get_db() cur = db.cursor() if request.method == 'POST' and registration_form.validate(): result = request.form # check that the company doesn't already exist # make db entry #print('insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url'])) cur.execute( 'insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url']) ) db.commit() return redirect('/login') return render_template("reg.html", form=registration_form)
def fetch_member_ids(member_id): ''' Fetch member IDs (Votesmart, Govtrack, Opensecrets) from the database. ''' select_member_ids = ''' SELECT id, votesmart_id, opensecrets_id, govtrack_id FROM officials_info WHERE id = :id ; '''.format() cursor = get_db().cursor() cursor.execute(select_member_ids, {'id': member_id}) results = [dict(res) for res in cursor.fetchall()] if len(results) != 1: return None member_ids = { 'bioguide_id': results[0]['id'], 'votesmart_id': results[0]['votesmart_id'], 'opensecrets_id': results[0]['opensecrets_id'], 'govtrack_id': results[0]['govtrack_id'], } return member_ids
def viewCampaign(): import db_helpers db = db_helpers.get_db() campaign_id = request.args.get('campaign_id') # get events events = return_events(int(campaign_id)) print("!!!",events) #overwrite events.json with open("events.json", "w") as jsonFile: json.dump(events, jsonFile) user = load_user(current_user.id) match = re.search(r"facebook\.com/(\w+).*", user.companyFacebook) facebookName = match.group(1) campaign = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id)) #print(campaign) if(datetime.strptime(campaign[0][4], "%Y-%m-%d") > now): campaign.append("in_progress") else: campaign.append("ended") #sentiments = get_all_weeks(facebookName,campaign[0][3],campaign[0][4]) ### end_date = campaign[0][4] start_date = campaign[0][3] stats = "id,name,website,description,category,fan_count,post_like_count,post_comment_count,post_type,post_message" facebook = displayFacebookJSON(facebookName, start_date+'T00:00:00Z', end_date+'T00:00:00Z', stats)['FacebookStatisticData'] fb = filterPosts(campaign[0][5], facebook) total_likes = 0 for post in fb['posts']: total_likes += post['post_like_count'] campaign_length = datetime.strptime(campaign[0][3], "%Y-%m-%d") - datetime.strptime(campaign[0][4], "%Y-%m-%d") c_len = campaign_length.days facebook_data={} facebook_data['num_posts'] = len(fb['posts']) facebook_data['daily_posts'] = round(facebook_data['num_posts']/c_len, 2) facebook_data['avg_react_per_post'] = round(total_likes/facebook_data['num_posts'], 2) # should be done by sentiment but whatever post_popularity=islice(sort_posts(fb['posts']), 5) content = sort_posts(fb['posts'])[0]['post_type'] ### event_form = EventForm(request.form) if request.method == 'POST': q = 'insert into events values (null, "%s", "%s", "%s", "%s", "%s", " %s")' % ( getval(event_form['event_name']), getval(event_form['event_description']), getval(event_form['event_type']), getval(event_form['start_date']), getval(event_form['end_date']), campaign_id ) query = db_helpers.query_db(q) db.commit() return render_template('viewCampaign.html', form = event_form, events = events, campaign = campaign, facebook=facebook, facebook_data=facebook_data, post_popularity=post_popularity, content=content)
def food_status(): db = get_db(DATABASE).cursor() fridge_contents = query_db(db, 'select * from fridge') return render_template('food_status.html', fridge_contents=fridge_contents)