def insert_data(files):
from app.models import Region, District
dist, reg = get_file(files)
for item in range(1, len(dist)):
regions = Region.objects.get(id=int(reg[item]))
dis = District(region=regions, district_name=dist[item].lower())
dis.save()
def test_relationships(self):
house_title1 = "title_house1"
house_title2 = "title_house1"
district1 = "test_relation_district1"
district2 = "test_relation_district2"
area1 = "test_relation_area1"
area2 = "test_relation_area2"
community1 = "test_relation_c1"
community2 = "test_relation_c2"
d1 = District(name=district1)
d2 = District(name=district2)
db.session.add(d1)
db.session.add(d2)
db.session.commit()
a1 = Area(name=area1, district=d1)
a2 = Area(name=area2, district=d2)
db.session.add(a1)
db.session.add(a2)
db.session.commit()
c1 = Community(name=community1, area=a1)
c2 = Community(name=community2, area=a2)
db.session.add(c1)
db.session.add(c2)
db.session.commit()
h1 = House(title=house_title1, community=c1)
h2 = House(title=house_title2, community=c2)
db.session.add(h1)
db.session.add(h2)
db.session.commit()
self.assertTrue(District.query.filter_by(name=district1).first().areas.first()\
.communities.first().houses.first().title==house_title1)
self.assertTrue(District.query.filter_by(name=district2).first().areas.first() \
.communities.first().houses.first().title == house_title2)
def run(*args):
df = pd.read_csv(os.path.join(CURRENT_DIR, FILE_NAME),
parse_dates=['Date'])
print(df.info())
print(df.head())
for i in range(len(districts)):
dis = District(name=districts[i + 1]['name'], )
print(dis)
dis.save()
for index, row in df.iterrows():
print(index)
se = SeaTurtleCount(
district=dis,
date=row['Date'].to_pydatetime(),
value=row['sea_turtle_count'],
)
print(se)
se.save()
def initialize():
from flask_migrate import upgrade
from app.models import Role, Ad
upgrade()
db.create_all() # Create the materialized view
Role.insert_roles()
Canton.insert()
District.insert()
Location.insert()
Ad.insert_initial_xml()
User.insert_default_user()
def initialize():
from flask_migrate import upgrade
from app.models import Role, Ad
upgrade()
db.create_all() # Create the materialized view
Role.insert_roles()
Canton.insert()
District.insert()
Location.insert()
Ad.insert_initial_xml()
User.insert_default_user()
def add_districts(json_path):
districts = read_json(json_path)
for id, data in districts.items():
district = District(id=id, title=data['title'])
streets = data['streets']
for street_id in streets:
street = db.session.query(Street).get(street_id)
district.streets.append(street)
db.session.add(district)
db.session.commit()
def test_add_aera_setter(self):
district_name = "test_district"
area_name = "testarea"
district = District(name=district_name)
db.session.add(district)
db.session.commit()
d = District.query.filter_by(name=district_name).first()
area = Area(name=area_name, district=d)
db.session.add(area)
db.session.commit()
dd = District.query.filter_by(name=district_name).first()
self.assertTrue(dd.areas.first().name == area_name)
def addDistricts(app):
with app.app_context():
app.logger.info("Adding District/Division data...")
districts = [
("Axe", County.avon),
("Bath", County.avon),
("Bristol South", County.avon),
("Brunel", County.avon),
("Cabot", County.avon),
("Cotswold Edge", County.avon),
("Gordano", County.avon),
("Kingswood", County.avon),
("Wansdyke", County.avon),
("Bristol North East", County.bsg),
("Bristol North West", County.bsg),
("Bristol South", County.bsg),
("Bristol South West", County.bsg),
("Concorde", County.bsg),
("Frome Valley", County.bsg),
("Kingswood North", County.bsg),
("Kingswood South", County.bsg),
("Severnvale", County.bsg),
("South Cotswold", County.bsg),
("Avon Valley South", County.sn),
("Bath", County.sn),
("Cam Valley", County.sn),
("Portishead", County.sn),
("Weston-super-Mare", County.sn),
("Wraxhall", County.sn),
("Yeo Vale", County.sn),
("Bristol West", County.bsg),
]
for name, county in districts:
d = District(name=name, county=county)
db.session.add(d)
app.logger.info(f" - added { d.name }")
app.logger.info("...Districts/Divisions added!")
db.session.commit()
app.logger.info("Database seeding complete")
def create_jobs(session):
js_created = 0
county_polling_data = pd.read_csv(JOBS_DATASET, encoding="ISO-8859-1")
session.query(CountyJobStats).delete()
session.query(DistrictJobStats).delete()
session.query(StateJobStats).delete()
for _, row in county_polling_data.iterrows():
# stateAbbr,geoType,name,geoid,sourceURL
js = None
state_abbr = row['stateAbbr']
geo_type = row['geoType']
region_name = _clean_region_name(row['name'])
if geo_type == 'county':
county_shortcode = CountyFragment.to_shortcode(
state_abbr, region_name)
js = CountyJobStats(county_shortcode=county_shortcode)
elif geo_type == 'sldl':
district_num = District.to_district_num(state_abbr, region_name)
district_shortcode = District.to_shortcode(
state_abbr, DistrictType.STATE_HOUSE, district_num)
js = DistrictJobStats(district_shortcode=district_shortcode)
elif geo_type == 'sldu':
district_num = District.to_district_num(state_abbr, region_name)
district_shortcode = District.to_shortcode(
state_abbr, DistrictType.STATE_SENATE, district_num)
js = DistrictJobStats(district_shortcode=district_shortcode)
elif geo_type == 'cd':
district_num = District.to_district_num(state_abbr, region_name)
district_shortcode = District.to_shortcode(
state_abbr, DistrictType.CONGRESSIONAL, district_num)
js = DistrictJobStats(district_shortcode=district_shortcode)
elif geo_type == 'state':
js = StateJobStats(state_abbr=row['stateAbbr'])
for csv_key, sql_fieldname in JOBS_STATS.items():
setattr(js, sql_fieldname, clean_float(row[csv_key]))
session.add(js)
try:
session.commit()
except Exception as e:
print("OOPS: ", state_abbr, district_num, region_name)
js_created += 1
print("Job stats records created: %d" % js_created, end="\r")
print()
def api_post_district(api_version):
data = request.get_json()
if "data" in data:
data = data["data"]
created_data = []
try:
for district in data:
a = District(**district)
db.session.add(a)
created_data.append(a)
db.session.commit()
except Exception as e:
db.session.rollback()
return make_409(str(e))
return make_200([d.serialize() for d in created_data])
else:
return make_400("no data")
def read_voting_sentences(session, state_abbr, voting_csv_path, names_csv_path):
county_voting_info = pd.read_csv(voting_csv_path, encoding="ISO-88591")
names_districts_info = pd.read_csv(names_csv_path, encoding="ISO-8859-1")
candidate_fullnames = [
first + " " + last
for first, last in zip(list(names_districts_info['First Name']),
list(names_districts_info['Last Name']))]
district_nums = [
int(re.search(r'\d+', raw_district).group())
for raw_district in names_districts_info['District']]
district_types = [
(DistrictType.STATE_SENATE if chamber == "Senate" else
DistrictType.STATE_HOUSE if chamber == "House" else "??")
for chamber in names_districts_info['Branch']]
district_shortcodes = [
District.to_shortcode(state_abbr, dtype, num)
for dtype, num in zip(district_types, district_nums)
]
name_district_lookup = dict(zip(candidate_fullnames, district_shortcodes))
for row in county_voting_info.values:
legislator_name = row[1] + " " + row[2]
for i in range(4, len(row)):
district_shortcode = name_district_lookup[legislator_name]
sentence = row[i].replace("Candidate", legislator_name)
if sentence != '-':
vr = VotingRecord(district_shortcode=district_shortcode,
legislator_name=legislator_name,
vote_fulltext=sentence)
session.add(vr)
votes_created += 1
print("Voting records created: %d" % votes_created, end="\r")
print()
session.commit()
def create_counties_and_districts(session, folder, keys, district_type):
"""Yields counties and districts"""
districts_created = 0
counties_created = 0
for filename in os.listdir(folder):
state_abbr = os.path.splitext(filename)[0]
file_path = os.path.join(folder, filename)
csv_data = pd.read_csv(file_path, encoding="ISO-8859-1")
(county_name_key, district_num_key, county_pop_key, percent_key) = keys
for _, row in csv_data.iterrows():
raw_district_num = row.get(district_num_key)
# Exclude floterial districts in New Hampshire that have no
# associated geographical area
# (https://en.wikipedia.org/wiki/Floterial_district)
if type(raw_district_num) == str and "(F)" in raw_district_num:
continue
district_num = District.to_district_num(state_abbr,
raw_district_num)
county_fullname = row.get(county_name_key)
county_population = row.get(county_pop_key)
county_percent_of_whole = row.get(percent_key)
if district_num is None or not county_fullname:
continue
district = session.query(District).filter_by(
state=state_abbr, district_number=district_num).first()
district_shortcode = District.to_shortcode(state_abbr,
district_type,
district_num)
if not district:
district = District(shortcode=district_shortcode,
state=state_abbr,
district_type=district_type,
district_number=district_num)
districts_created += 1
print(
"%s District records created: %d County fragment records created: %d"
%
(district_type.value, districts_created, counties_created),
end="\r")
session.add(district)
county_shortcode = CountyFragment.to_shortcode(
state_abbr, county_fullname)
cf = CountyFragment(shortcode=county_shortcode,
district_shortcode=district_shortcode,
fullname=county_fullname,
population=county_population,
percent_of_whole=county_percent_of_whole)
counties_created += 1
print(
"%s District records created: %d County fragment records created: %d"
% (district_type.value, districts_created, counties_created),
end="\r")
session.add(cf)
print()
session.commit()
def deploy():
"""Run deployment tasks."""
from flask_migrate import upgrade
upgrade()
District.insert_districts()
for obj in provinces:
print(obj)
province = Province(id=obj['id'],
name=obj['name'],
created_at=datetime.datetime.today(),
updated_at=datetime.datetime.today())
db.session.add(province)
db.session.commit()
for obj in districts:
print(obj)
district = District(id=obj['id'],
province_id=obj['province_id'],
name=obj['name'],
created_at=datetime.datetime.today(),
updated_at=datetime.datetime.today())
db.session.add(district)
db.session.commit()
"""
for num in range(100):
fullname = fake.name().split()
first_name = fullname[0]
family_name = ' '.join(fullname[1:])
email = fake.email()
phone = fake.phone_number()
# Save in database
user = User(first_name=first_name, family_name=family_name, email=email, phone=phone)
db.session.add(user)
def write_database(post_id, user_id, text, created_at, created_at_dt,
reply_to_user_id, reply_to_scrname, reply_to_status_id,
retweet_count, favorite_count, is_retweet,
original_tweet_id, original_tweet_retweets, original_text,
original_tweet_created_at, original_tweet_likes,
original_author_id, original_author_scrname, polarity,
polarity_val, tag_list, url_list, user_scrname, user_name,
user_location, user_created, user_followers, user_friends,
user_statuses, query):
#print("starting db entry for postid = {}".format(post_id))
if query[4:7] == 'Sen':
district = 'sen'
district_name = query[2:7]
dist_type = 2
else:
district = query[4:6]
district_name = query[2:6]
dist_type = 1
#POST TABLE:
#If tweet ID not already in database, add to Post table
if db.session.query(Post).filter(Post.post_id == post_id).count() == 0:
#print('adding post')
#USER table
#If User already in User table, update dynamic elements, associate with this post
this_user = db.session.query(User).filter(
User.user_id == user_id).first()
if this_user != None:
this_user.user_location = user_location
this_user.user_followers = user_followers
this_user.user_friends = user_friends
this_user.user_statuses = user_statuses
db.session.add(this_user)
#Otherise, add User to user table, associate with this post
else:
this_user = User(user_id, user_scrname, user_name, user_location,\
user_created, user_followers, user_friends, user_statuses)
db.session.add(this_user)
#POST table
new_post = Post(post_id, user_id, text, created_at, created_at_dt,
reply_to_user_id, reply_to_scrname, reply_to_status_id,
retweet_count, favorite_count, is_retweet,
original_tweet_id, original_tweet_retweets,
original_text, original_tweet_created_at,
original_tweet_likes, original_author_id,
original_author_scrname, polarity, polarity_val)
db.session.add(new_post)
#If original tweet is in database, update its retweeted count. If not, do nothing
if original_tweet_id != None:
orig_tweet = db.session.query(Post).\
filter(Post.post_id == original_tweet_id).first()
if orig_tweet != None:
orig_tweet.retweet_count = original_tweet_retweets
db.session.add(orig_tweet)
#HASHTAG TABLE
# If tweet is being added, iterate through tag/url list, and create a
# Hashtag/Url table row for each tag
for item in tag_list:
#If hashtag is not already in Hashtag table, create new row
hash_search = db.session.query(Hashtag).\
filter(Hashtag.hashtag == item).first()
if hash_search == None:
new_hashtag = Hashtag(item)
db.session.add(new_hashtag)
else:
new_hashtag = hash_search
#Add association to posthash_assoc_table
new_post.hashtags.append(new_hashtag)
#db.session.add(posthash_assoc.hashtag)
#add one row to Post_extended per hashtag
# NOTE: this means number of rows per post_id = cartesian product
# of hashtags times districts (if picked up in search for every dist)
new_row = Post_extended(
post_id, user_id, created_at, created_at_dt, retweet_count,
is_retweet, original_tweet_id, original_text,
original_tweet_created_at, original_author_id,
original_author_scrname, polarity, polarity_val, item,
district_name, dist_type, user_scrname)
db.session.add(new_row)
#print("added newrow for hash {}".format(item))
#DISTRICT TABLE
#capture District_id from 1st query term:
state = query[2:4].lower()
#Handle Senate districts differently than congressional
# if query[4:7] == 'Sen':
# district = 'sen'
# district_name = query[2:7]
# dist_type = 2
# else:
# district = query[4:6]
# district_name = query[2:6]
# dist_type = 1
#Check if district is in DB, add if not
district_search = db.session.query(District).\
filter(District.district_name == district_name).first()
if district_search == None:
new_district = District(state, district, district_name, dist_type)
db.session.add(new_district)
else:
new_district = district_search
#Add association to postdist_assoc_table
new_post.districts.append(new_district)
#URL TABLE
#if URLS exist, add to db
if len(url_list) > 0:
for item in url_list:
url_search = db.session.query(Url).filter(
Url.url == item).first()
if url_search == None:
new_url = Url(item)
db.session.add(new_url)
else:
new_url = url_search
#Add association to postDistAssoc_table
new_post.urls.append(new_url)
#associate user with post
this_user.user_posts.append(new_post)
#If tweet ID in db (from another dist query), add new association to Post table
else:
#print('ID there, trying plan B')
district_check = db.session.query(District.district_name).\
join(Post.districts).\
filter(Post.post_id==post_id).all()
check = 0
for result in district_check: #iterate through associated dists
if result[0] == district_name:
check = 1
#print("already there") #if find match, check = 1, do nothing
if check == 0:
#print("adding newdist") # if no match, add to postdist_assoc
sql_command = '''INSERT INTO postdist_assoc (post_id, district_name)
VALUES (post_id, district_name);'''
conn = db.engine.connect()
conn.execute(sql_command)
conn.close()
def deploy():
"""Run deployment tasks."""
from flask_migrate import upgrade
upgrade()
District.insert_districts()
def post(self) -> District:
district = District(**api.payload)
current_session.add(district)
current_session.commit()
return district
def read_voting_data(session, state_abbr, datasets):
bills_created = 0
votes_created = 0
for xlsx_path in datasets:
df = pd.read_excel(xlsx_path, sheet_name=None)
bills_sheet = df["Votes"]
bills = {}
for _, row in bills_sheet.iterrows():
bill_id = row[VK.BILL]
bill_no = row[VK.BILL_NO]
bill_title = row[VK.BILL_TITLE]
bill_pro_env = VotingAction.fuzzy_cast(row[VK.BILL_PRO_ENV])
bill_details = row[VK.BILL_DETAILS]
bill_outcome = row.get(VK.BILL_OUTCOME, None)
b = Bill(state=state_abbr,
pro_environment_decision=bill_pro_env,
title=bill_title,
code=bill_no,
description=bill_details,
outcome=bill_outcome)
bills[bill_id] = b # For use in this function only
session.add(b) # Save to db
bills_created += 1
print("Voting records created: %d Bill records created: %d" % (
votes_created, bills_created), end="\r")
session.commit()
for sheet_name, district_type in VOTING_CHAMBERS:
sheet_content = df[sheet_name]
for _, row in sheet_content.iterrows():
state = row[VK.STATE]
year = int(row[VK.YEAR])
district_number = int(row[VK.DISTRICT])
legislator_name = row[VK.LEGISLATOR_NAME]
party = safe_cast(Party, row[VK.PARTY])
year_score = safe_cast(float, row[VK.YEAR_SCORE % year])
lifetime_score = safe_cast(float, row.get(VK.LIFETIME_SCORE))
district_shortcode = District.to_shortcode(state_abbr,
district_type,
district_number)
for key in row.keys():
if key and re.match(VK.BILL_ID, str(key)): # This column is a vote
raw_classification = row[key]
try:
c = VotingClassification(raw_classification)
except ValueError:
# print("Invalid vote classification: '%s'%s" % (
# raw_classification, ' ' * 20))
c = VotingClassification.UNKNOWN
v = Vote(district_shortcode=district_shortcode,
legislator_name=legislator_name,
classification=c,
party=party,
year=year,
year_score=year_score,
lifetime_score=lifetime_score,
bill_id=bills[key].id)
votes_created += 1
session.add(v)
print("Voting records created: %d Bill records created: %d" % (
votes_created, bills_created), end="\r")
session.commit()
print()
def test_add_district(self):
district = District(name="hello")
db.session.add(district)
db.session.commit()
year = ElectionYear.select(graph, data.CAND_ELECTION_YR ).first()
if not year:
year = ElectionYear()
year.YEAR = data.CAND_ELECTION_YR
graph.push(year)
cand.CAND_ELECTION_YR.add(year)
state = State.select(graph, data.CAND_OFFICE_ST ).first()
if not state:
state = State()
state.STATE = data.CAND_OFFICE_ST
graph.push(state)
cand.CAND_OFFICE_ST.add(state)
office = Office.select(graph, data.CAND_OFFICE).first()
if not office:
office = Office()
office.OFFICE = data.CAND_OFFICE
graph.push(office)
cand.CAND_OFFICE.add(office)
district = District.select(graph, data.CAND_OFFICE_DISTRICT).first()
if not district:
district = District()
district.DISTRICT = data.CAND_OFFICE_DISTRICT
graph.push(district)
cand.CAND_OFFICE_DISTRICT.add(district)
graph.push(cand)
