def post(self):
"""Create a new campaign."""
campaign_dict = self.get_request_json()
validate_campaign_dict(campaign_dict)
# get a list of platforms
platforms_list = campaign_dict["platforms"]
del campaign_dict["platforms"]
# construct and store a new campaign
campaign = Campaign(**campaign_dict)
campaign.put()
campaign_id = campaign.key.id()
# construct and store platforms for campaign
platforms = []
for platform_name in platforms_list:
platform = Platform(name=platform_name,
counter=0,
campaign=campaign.key,
id="%d-%s" % (campaign_id, platform_name))
platforms.append(platform)
ndb.put_multi_async(platforms)
# prepare response representation of the created campaign
output = campaign_to_dict(campaign, platforms=platforms)
# set the appropriate response headers
self.response.location = self.uri_for("campaign-detail",
campaign_id=campaign_id)
self.response.status_int = 201
return output
def CreateCampaign():
if 'file' not in request.files:
return Response('no file found', status=400)
file = request.files['file']
if not allowed_file(file.filename):
return Response('invalid file type', status=400)
try:
newCampaign = Campaign(
request.form['name'] or None,
f'/mediafiles/campaigns/{secure_filename(file.filename)}')
if request.form['is_default'] == 'true':
old_default = Campaign.query.filter_by(is_default=True).all()
for camp in old_default:
camp.is_default = False
newCampaign.is_default = True
db.session.add(newCampaign)
db.session.commit()
os.makedirs(
f'/usr/src/app/mediafiles/campaigns/{newCampaign.name.replace(" ", "_")}',
exist_ok=True)
file.save(f'/usr/src/app{newCampaign.image}')
data = jsonify(newCampaign.to_dict())
data.status_code = 201
return data
except IntegrityError as error:
return Response(error.args[0], status=400)
def setUp(self):
app = create_app(test_config)
with app.app_context():
db.drop_all()
db.create_all()
self.app = app.test_client()
self.app_context = app.app_context()
self.app_context.push()
guild = Guild('Flames of Exile')
db.session.add(guild)
db.session.commit()
foe_guild = db.session.query(Guild).filter_by(
name='Flames of Exile').first()
admin = User('DiscordBot', sha256_crypt.encrypt('admin'), foe_guild.id,
User.Role.ADMIN)
admin.discord_confirmed = True
db.session.add(admin)
db.session.commit()
campaign = Campaign('campaign_name',
'/mediafiles/campaigns/campaign.png', True)
db.session.add(campaign)
db.session.commit()
world = World('world_name',
'/mediafiles/campaigns/campaign_name/world.png', 1, 1, 1,
1)
db.session.add(world)
db.session.commit()
pin = Pin(1, 1, Pin.Symbol.ANIMAL, Pin.Resource.WOLF, 1, 1, '', 1, 1,
'notes', 1, 1)
db.session.add(pin)
db.session.commit()
edit = Edit("", 1, 1)
db.session.add(edit)
db.session.commit()
event = Event('event', 'game',
datetime.datetime.now().isoformat(), 'note')
db.session.add(event)
db.session.commit()
self.DEFAULT_GUILD = guild
self.DEFAULT_USER = admin
self.DEFAULT_CAMPAIGN = campaign
self.DEFAULT_WORLD = world
self.DEFAULT_PIN = pin
self.DEFAULT_TOKEN = f'Bearer {self.login("DiscordBot", "admin").get_json()["token"]}'
self.DEFAULT_EVENT = event
self.maxDiff = None
self.assertEqual(app.debug, False)
def post(self, campaign_id):
campaign = Campaign.get_by_key_name(campaign_id)
if not campaign:
campaign = Campaign(key_name=campaign_id,
name=campaign_id,
short_code=campaign_id)
campaign.put()
status_view = CampaignStatusView(campaign=campaign,
response=self.response)
def post(self):
user = users.get_current_user()
if user:
name = self.request.get('name')
c = Campaign(name=name, owner=user)
c.put()
return self.redirect("/campaign/%s" % c.key().id())
else:
return self.redirect(users.create_login_url(self.request.uri))
def campaignDelete(id):
campaign = Campaign()
campaign = Campaign.query.filter_by(id=id).first()
borrado = campaign
db.session.delete(campaign) #PASOXXX
db.session.commit()
return jsonify('Campaña borrada'), 200
async def post_newsletter_to_mailing_list(post: Newsletter):
mail_list = list(
map(lambda row: row[EMAIL_KEY], data.get_all_emails_unpaginated()))
await newsletter(mail_list, post.subject, post.body)
campaign = Campaign(subject=post.subject,
body=post.body,
template=post.template,
sent_to=mail_list)
data.add_campaign(campaign.dict())
return {"msg": "success"}
def mutate(self, info, user_email, campaign_name):
session = Session()
campaign = Campaign(user_email=user_email, campaign_name=campaign_name)
session.add(campaign)
session.commit()
campaign_id = campaign.id
session.close()
return CreateCampaign(
id=campaign_id,
campaign_name=campaign_name,
user_email=user_email,
)
def test_api_request():
if 'credentials' not in flask.session:
return flask.redirect('authorize')
# Load credentials from the session.
credentials = google.oauth2.credentials.Credentials(
**flask.session['credentials'])
gmail = googleapiclient.discovery.build(API_SERVICE_NAME,
API_VERSION,
credentials=credentials)
flask.session['credentials'] = credentials_to_dict(credentials)
# get the user profile using Gmail API
userProfile = gmail.users().getProfile(userId="me").execute()
# create a User object with the data provided by Gmail API
user = User.query.filter_by(email=userProfile['emailAddress']).first()
flask.session['fromEmail'] = userProfile['emailAddress']
# add user to DB if not present already
if not user:
user = User(email=userProfile['emailAddress'],
unique_id=uuid.uuid4().hex)
db.session.add(user)
db.session.commit()
form = DripEmailBasicDetailsForm(request.form)
if request.method == 'POST' and form.validate():
# create a Campaign object based on the form data.
campaign = Campaign(title=form.title.data,
stages=form.noOfStages.data,
frequency=2,
recipients=form.receiverList.data,
user_id=user.unique_id,
unique_id=uuid.uuid4().hex)
# store some session variables to be used in emailTemplate form
flask.session['campaign_id'] = campaign.unique_id
flask.session['campaign'] = campaign.title
flask.session['frequency'] = campaign.stages
# add campaign to DB
db.session.add(campaign)
db.session.commit()
flash('Thanks for registering')
return flask.redirect(flask.url_for('addEmail'))
return render_template('register.html', form=form)
def campainsAdd():
data = request.json
campaign = Campaign() #PASOXXX
campaign.endDate = data["endDate"]
campaign.budget = data["budget"]
campaign.villages_id = data["villages_id"]
campaign.days_before = data["days_before"]
campaign.sms = data["sms"]
campaign.mail = data["mail"]
campaign.admin_id = data["admin_id"]
campaign.client_id = data["client_id"]
#user.password = sha256.hash(data["password"])
db.session.add(campaign) #PASOXXX
db.session.commit()
return jsonify(campaign.serialize()), 200
def comments():
if request.method == "POST":
usernames = [
u.strip() for u in request.form.get("usernames").split(",")
]
comment = request.form.get("comment")
api = instagram.client.InstagramAPI(
access_token=current_user.access_token) #,
#client_ips="127.0.0.1",
#client_secret=INSTAGRAM_SECRET)
campaign = Campaign(current=False,
like_campaign=False,
comment=comment,
user=current_user)
session.add(campaign)
session.commit()
for username in usernames:
user = api.user_search(q=username)[0]
prospect = session.query(Prospect).filter_by(\
username=user.username).first()
if not prospect:
prospect = Prospect(
username=user.username,
instagram_id=user.id,
)
session.add(prospect)
prospect_profile = ProspectProfile(campaign=campaign,
prospect=prospect,
done=False,
followed_back=False)
session.add(prospect_profile)
session.commit()
soon = datetime.datetime.now() + datetime.timedelta(minutes=1)
logging.basicConfig()
scheduler = get_scheduler()
job = update_comments.delay(campaign.id, api)
campaign.job_id = job.id
session.commit()
campaigns = session.query(Campaign).filter_by(user=current_user, \
like_campaign=False)
else:
campaigns = session.query(Campaign).filter_by(user=current_user, \
like_campaign=False)
return render_template("comments.html", campaigns=campaigns)
def create_campaign(request):
if request.method == 'POST':
campaign = Campaign(client=request.user)
form = CampaignForm(request.POST, request.FILES, instance=campaign)
if form.is_valid():
campaign = form.save()
if campaign.id:
return redirect('client_index')
if request.method == 'GET':
form = CampaignForm()
form.fields[
'contacts'].widget = django.forms.widgets.CheckboxSelectMultiple(
choices=((c.username, c.username)
for c in get_profile(request.user).knows.all()))
transaction.commit()
return render(request, 'new_campaign.html', {
'form': form,
})
def newcamp():
if ((g.user.role != ROLE_SALESEXEC) and (g.user.role != ROLE_ADMIN)):
flash(
'Only an Administrator or Users with Web Developer Roles can access this page'
)
return redirect(url_for('index'))
form = newcampaign()
if form.validate_on_submit():
camp = Campaign(creator_id=g.user.id, name=form.campaignname.data)
checkcamp = findcamp_byname(camp.name)
if not checkcamp:
db.session.add(camp)
db.session.commit()
url = api_url + '/fmapi/addcamp'
data = {'id': camp.id, 'campname': camp.name, 'funids': 'none'}
headers = {
'Content-type': 'application/json',
'Accept': 'text/plain'
}
r = requests.post(url,
data=json.dumps(data),
headers=headers,
auth=('unibluefm', '123456789'))
return redirect(url_for('managecamp', cid=camp.id))
else:
flash(
'A campaign with the same name already exists, please chose another name'
)
return render_template('newcampaign1.html',
title='Add New Campaign',
form=form)
return render_template('newcampaign1.html',
title='Add New Campaign',
form=form)
def create_campaign():
return_url = request.referrer or '/'
form = CreateCampaignForm()
if form.validate_on_submit():
if validate_name(form.name.data):
campaign = Campaign(name=form.name.data,
city=form.city.data,
start_date=form.start_date.data,
is_active=True,
goal=form.goal.data)
flash(f'!קמפיין "{campaign.name}" נוצר בהצלחה', 'success')
db.session.add(campaign)
db.session.commit()
return redirect(url_for('home'))
form.name.errors.append('קמפיין בשם הזה כבר קיים, אנא בחר שם אחר.')
return render_template('/create_campaign.html',
form=form,
legend="יצירת קמפיין",
return_url=return_url)
def create_campaign_and_redirect(request):
campaign_name = request.GET.get("campaign_name_field", None)
rule_set_pk = request.GET.get("rule_set_field", None)
#generate access code
size = 8
chars = string.ascii_uppercase + string.digits
while True:
code = ''.join(random.choice(chars) for _ in range(size))
if Campaign.objects.filter(access_code=code).count() is 0:
break
new_campaign = Campaign(rule_set=RuleSet.objects.get(pk=rule_set_pk),
name=campaign_name,
owning_user=request.user,
access_code=code)
new_campaign.save()
#redirect to the gm_session page
return HttpResponseRedirect(reverse('gm_session', args=[new_campaign.pk]))
def home():
if request.method == "POST":
username = request.form.get("username")
api = instagram.client.InstagramAPI(
access_token=current_user.access_token) #,
#client_ips="127.0.0.1",
#client_secret=INSTAGRAM_SECRET)
user = api.user_search(q=username)[0]
campaign = Campaign(current=False,
username=user.username,
instagram_id=user.id,
like_campaign=True,
user=current_user)
session.add(campaign)
session.commit()
campaigns = session.query(Campaign).filter_by(user=current_user, \
like_campaign=True)
else:
campaigns = session.query(Campaign).filter_by(user=current_user, \
like_campaign=True)
return render_template("home.html", campaigns=campaigns)
def setUp(self):
app = create_app(test_config)
with app.app_context():
db.drop_all()
db.create_all()
self.app = app
self.test_client = app.test_client()
self.app_context = app.app_context()
self.app_context.push()
self.socketio = SocketIO(app)
self.socketio.on_event('connect', on_connect)
self.socketio.on_event('campaign-update', handle_campaign_update)
guild = Guild('Flames of Exile')
db.session.add(guild)
db.session.commit()
foe_guild = db.session.query(Guild).filter_by(
name='Flames of Exile').first()
admin = User('DiscordBot', sha256_crypt.encrypt('admin'), foe_guild.id,
User.Role.ADMIN)
admin.discord_confirmed = True
db.session.add(admin)
db.session.commit()
campaign = Campaign('campaign_name', '/mediafiles/campaign.png', True)
db.session.add(campaign)
db.session.commit()
self.campaign = campaign.to_dict()
self.maxDiff = None
self.assertEqual(app.debug, False)
def send_async_email(camp_title,recipients,template,temp_var,email):
with app.app_context():
user = User.query.filter_by(user_id=email).all()
credentials = dict(json.loads(user[0].credentials))
credentials = google.oauth2.credentials.Credentials(**credentials)
user[0].credentials = json.dumps(credentials_to_dict(credentials))
db.session.commit()
for key in temp_var:
if key in template:
template = template.replace(key,temp_var[key])
gmail_service = googleapiclient.discovery.build("gmail", "v1", credentials=credentials)
message = MIMEText(template)
message['to'] = recipients
message['from'] = email
message['subject'] = camp_title
body = {'raw': base64.b64encode(message.as_string())}
try:
camp_obj = Campaign.query.filter_by(camp_title=camp_title).all()
#if reply of previous followup detected .. send no further mails
if camp_obj and camp_obj[0].thread_id and reply_detect(camp_obj[0].thread_id,email,credentials):
print "Reply detected."
return
message = gmail_service.users().messages().send(userId=email, body=body).execute()
thread_id = message["threadId"]
# if campaign does not exist.. create one
if not camp_obj:
new_camp_obj = Campaign(camp_title,thread_id)
db.session.add(new_camp_obj)
db.session.commit()
# else update to thread id of last mail to current one.
else:
camp_obj[0].thread_id = thread_id
db.session.commit()
except Exception as error:
print "Error occured" + str(error)
def create_campaign():
if "user_id" not in session:
flash("Please login first!")
return redirect('/login')
form = CampaignForm()
if form.validate_on_submit():
title = form.title.data
description = form.description.data
max_players = form.max_players.data
campaign = Campaign(
title=title,
description=description,
max_players=max_players,
user_id=session['user_id']
)
db.session.add(campaign)
db.session.commit()
return redirect('/campaigns')
return render_template('new_campaign.html', form=form)
def create_campaign(self, user_id, draft_id, name):
c = Campaign(title=name, draft_id=draft_id, user_id=user_id)
db.session.add(c)
db.session.commit()
return c
def addcampaign():
newcamp = Campaign(id=request.json['id'], name=request.json['campname'], funnel_ids=request.json['funids'])
db.session.add(newcamp)
db.session.commit()
return jsonify(msg= 'Campaign Added')
def create_campaign():
if request.method == 'POST':
responseObject = {
'status': 'failed',
'message': 'Campaign is not created'
}
responseObjectNotValidJson = {
'status': 'failed',
'message': 'Request is not a valid json object'
}
try:
json_data = request.get_json(force=True)
# json_object = json.loads(json_data)
if not json_data.get('marketingChannel', 'campaignDropDate'):
return make_response(jsonify(responseObjectNotValidJson)), 406
else:
json_data = request.get_json(force=True)
#identify the eligible population
if json_data.get('eligiblePopulationType') == 'MTG':
inpuFileUrl = 's3://cof-sbx-fsda-cohl-npi-analytic/flagship/part-00000-a77e5e45-aef3-489e-be96-01a4ed3a1f71.snappy.parquet'
elif json_data.get('eligiblePopulationType') == 'HE':
inpuFileUrl = 's3://url_to_HE_s3_file/file_name'
elif json_data.get('eligiblePopulationType') == 'HE':
inpuFileUrl = 's3://url_to_CRA_s3_file/file_name'
all_filters = json_data.get('filters')
all_sorts = json_data.get('sorts')
all_dedups = json_data.get('dedups')
all_classifies = json_data.get('classifies')
all_distributes = json_data.get('distributes')
all_minus = json_data.get('minus')
newCampaign = Campaign(
campaign_name=json_data.get('campaignName'),
strategy_number=json_data.get('strategyNumber'),
marketing_channel=json_data.get('marketingChannel'),
campaign_effective_dt=json_data.get(
'campaignEffectiveDate'),
campaign_drop_dt=json_data.get('campaignDropDate'),
input_file_url=inpuFileUrl,
input_file_type='parquet',
output_table_name=json_data.get('campaignName') +
'_readFileOutput' + str(json_data.get('ruleSequence')),
eligible_population_type=json_data.get(
'eligiblePopulationType'),
line_of_business=json_data.get('lineOfBusiness'),
# privacy_suppression_ind = json_data.get('privacySuppressionIndicator'),
rule_seq=json_data.get('ruleSequence'),
inserted_dt=datetime.datetime.now().replace(second=0,
microsecond=0),
inserted_by=json_data.get('user'),
# updated_dt = datetime.datetime.now().replace(second= 0, microsecond=0),
)
db.session.add(newCampaign)
if (all_filters):
input_table_name = json_data.get(
'campaignName') + '_readFileOutput' + str(
json_data.get('ruleSequence'))
for filter in all_filters:
output_table_name = json_data.get(
'campaignName') + '_FilterOutput' + str(
filter['ruleSequence'])
newFilter = Filters(
filter_column=filter['filterName'],
filter_operator=filter['filterOperator'],
filter_value=filter['filterValue'],
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
input_table_name=input_table_name,
output_table_name=output_table_name,
rule_seq=filter['ruleSequence'],
inserted_by=json_data.get('user'),
parent=newCampaign,
)
input_table_name = output_table_name
input_to_classify = output_table_name
db.session.add(newFilter)
if (all_sorts):
for sort in all_sorts:
newSort = Sorts(sort_keys=sort['sortKeys'],
rule_seq=sort['ruleSequence'],
order=sort['order'],
inserted_dt='2017-01-01',
inserted_by='yji914',
updated_dt='2017-09-09',
updated_by='yji914',
parent=newCampaign)
db.session.add(newSort)
if (all_classifies):
input_table = input_to_classify
for classify in all_classifies:
output_table_classify = json_data.get(
'campaignName') + '_ClassifyOutput' + str(
classify['ruleSequence'])
newClassify = Classifies(
input_table_name=input_table,
output_table_name=output_table_classify,
rule_seq=classify['ruleSequence'],
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newCampaign)
classify_rules = classify['classifyRules']
db.session.add(newClassify)
for each_rule in classify_rules:
newClassifyRules = Classify_rules(
expressions=each_rule['expressions'],
vendor_cell_value=each_rule['VendorCell'],
vendor_cell_description=each_rule[
'VendorDesc'],
classify_rule_order=each_rule['ruleNumber'],
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newClassify)
db.session.add(newClassifyRules)
if (all_distributes):
input_table_distribute = output_table_classify
for distribute in all_distributes:
output_table_distribute = json_data.get(
'campaignName') + '_DistributeOutput' + str(
distribute['ruleSequence'])
newDistribute = Distributes(
input_table_name=input_table_distribute,
output_table_name=output_table_distribute,
rule_seq=distribute['ruleSequence'],
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newCampaign)
distribute_rules = distribute['distributeRules']
db.session.add(newDistribute)
for each_rule in distribute_rules:
newDistributeRules = Distribute_rules(
expressions=each_rule['expressions'],
test_cell_weight=each_rule['testCellWeight'],
controlled_cell_weight=each_rule[
'controlCellWeight'],
distribute_rule_order=each_rule['ruleNumber'],
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newDistribute)
db.session.add(newDistributeRules)
if (all_dedups):
for dedup in all_dedups:
output_table_dedup = json_data.get(
'campaignName') + '_DedupOutput' + str(
dedup['ruleSequence'])
newDedup = Dedups(
unique_fields=dedup['uniqueFields'],
rule_seq=dedup['ruleSequence'],
dedup_fields=dedup['dedupFields'],
dedup_function=dedup['dedupFunction'],
description=dedup['dedupDescription'],
input_table_name=output_table_distribute,
output_table_name=output_table_dedup,
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newCampaign)
db.session.add(newDedup)
if (all_minus):
calculate_minus_sequence = len(all_minus)
prev_table = output_table_dedup
for minus in all_minus:
output_table_read = json_data.get(
'campaignName') + '_ReadOutput' + str(
minus['ruleSequence']),
output_table_minus = json_data.get(
'campaignName') + '_MinusOutput' + str(
minus['ruleSequence'] +
calculate_minus_sequence),
a = minus['ruleSequence']
newMinus = Minus(
s3input_table=minus['s3FileUrl'],
readfile_seq=minus['ruleSequence'],
minus_seq=minus['ruleSequence'] +
calculate_minus_sequence,
input_table_name=prev_table,
read_table_output=output_table_read,
output_table_name=output_table_minus,
inserted_dt=datetime.datetime.now().replace(
second=0, microsecond=0),
inserted_by=json_data.get('user'),
parent=newCampaign)
prev_table = output_table_minus
db.session.add(newMinus)
db.session.commit()
responseObject = {
'status': 'success',
'data': "Campaign successfully created"
}
return make_response(jsonify(responseObject)), 200
except ValueError:
return make_response(jsonify(responseObject)), 406
#providers = micawber.bootstrap_basic()
#providers.register('http://www.ustream.tv/channel/\S+', Provider('http://www.ustream.tv/oembed'))
#print providers.request('http://www.ustream.tv/channel/nasa-msfc')
print 'done'
ted = User(first_name='Ted',
last_name='Campbell',
username='******',
password='******',
email='*****@*****.**',
user_type=UserType.Producer,
is_verified=True,
is_user_active=True,
title="Executive Producer")
c = Campaign(title='test title')
#emails.send_reset_password_complete(ted)
#db.create_all()
#u = User(
# password='******',
# email='*****@*****.**',
# user_type=UserType.Backer,
# is_anonymous=True
#)
#db.session.add(u)
#db.session.commit()
dummy_text = "<p><img width=\"400\" alt=\"\" align=\"left\" src=\"http://res.cloudinary.com/hzdmrhkl4/image/upload/v1398912655/shntgrq6uzhzfni8btzq.png?n=njpgxkmye\">Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>"
funding_goal = randint(100000, 150000)
c = Campaign(
draft_id='81e7197e-f33e-4ebd-8e34-91cda0e57e47',
user_id=ted.id,
id=1,
thumbnail_url=
'http://res.cloudinary.com/hzdmrhkl4/image/upload/v1398907258/nm11q7fw7kayohkwcljo.png',
confirmation_message=
'<p><img alt="">Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>',
is_active=True,
short_description="Short Campaign Descriptionhort Campaign Description",
description=dummy_text,
title='New Campaign thats really cool!',
campaign_receiver_id=3,
campaign_type_id=3,
category_id=4,
expiration_date="2014-06-07 00:00:00",
funding_goal=funding_goal,
funded=randint(1, funding_goal),
fulfillment_service=True,
campaign_management=True,
evergreen_campaign_page=True,
vanity_url='vanity_url',
)
for i in range(6):
ci = CampaignUpdate(
id=uuid.uuid4(),
def main(args):
engine = create_engine(args.database)
session = sessionmaker(bind=engine)()
session.merge(ModelCode(id="HI",
description="Health insurance and voting"))
session.merge(ModelCode(id="VO", description="Voting Only"))
session.merge(ModelCode(id="PV", description="Poverty and voting"))
session.merge(ModelCode(id="IN", description="Income and voting"))
session.merge(ModelCode(id="PP", description="Population and voting"))
session.merge(ModelCode(id="GO", description="Geography and voting"))
session.merge(
ModelCode(id="GP", description="Geography, population, and voting"))
session.merge(
ModelCode(id="IH", description="Health insurance, income, and voting"))
session.merge(ModelCode(id="ALL", description="All variables"))
session.merge(
ModelCode(id="HI2020",
description="Health insurance and voting, including 2020"))
session.merge(
ModelCode(id="VO2020", description="Voting Only, including 2020"))
session.merge(
ModelCode(id="PV2020",
description="Poverty and voting, including 2020"))
session.merge(
ModelCode(id="IN2020",
description="Income and voting, including 2020"))
session.merge(
ModelCode(id="PP2020",
description="Population and voting, including 2020"))
session.merge(
ModelCode(id="GO2020",
description="Geography and voting, including 2020"))
session.merge(
ModelCode(
id="GP2020",
description="Geography, population, and voting, including 2020"))
session.merge(
ModelCode(
id="IH2020",
description="Health insurance, income, and voting, including 2020")
)
session.merge(
ModelCode(id="ALL2020", description="All variables, including 2020"))
mc_mapping = {
"HI": [
"state_population", "county_population", "people_in_poverty",
"poverty_rate", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"VO": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"PV": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"PP": [
"uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"GO": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate"
],
"GP":
["uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate"],
"HI2020": [
"state_population", "county_population", "people_in_poverty",
"poverty_rate", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"VO2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"PV2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "county_area_land", "county_area_water",
"county_pct_land", "county_pct_water", "state_area_land",
"state_area_water", "state_pct_land", "state_pct_water"
],
"PP2020": [
"uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate",
"county_area_land", "county_area_water", "county_pct_land",
"county_pct_water", "state_area_land", "state_area_water",
"state_pct_land", "state_pct_water"
],
"GO2020": [
"uninsured", "uninsured_pct", "state_population",
"county_population", "people_in_poverty", "poverty_rate"
],
"GP2020":
["uninsured", "uninsured_pct", "people_in_poverty", "poverty_rate"],
}
wc = {
"ALL": [
"year in ('2008', '2012')", "county_population is not null",
"state_population is not null", "county_area_land is not null",
"county_area_water is not null", "uninsured is not null",
"uninsured_pct is not null"
],
"HI": [
"year >= '2006'", "year < '2016'", "uninsured is not null",
"uninsured_pct is not null"
],
"PV": [
"year < 2020", "poverty_rate is not null",
"people_in_poverty is not null"
],
"GO": [
"year < 2020", "county_area_land is not null",
"county_area_water is not null"
],
"GP": [
"year < 2020", "county_area_land is not null",
"county_area_water is not null", "county_population is not null",
"state_population is not null"
],
"PP": [
"year < 2020", "county_population is not null",
"state_population is not null"
],
"ALL": [
"year in ('2008', '2012')", "county_population is not null",
"state_population is not null", "county_area_land is not null",
"county_area_water is not null", "uninsured is not null",
"uninsured_pct is not null"
],
"HI2020": [
"year >= '2006'", "year <= '2016'", "uninsured is not null",
"uninsured_pct is not null"
],
"PV2020":
["poverty_rate is not null", "people_in_poverty is not null"],
"GO2020":
["county_area_land is not null", "county_area_water is not null"],
"GP2020": [
"county_area_land is not null", "county_area_water is not null",
"county_population is not null", "state_population is not null"
],
"PP2020":
["county_population is not null", "state_population is not null"],
"ALL2020": [
"year in ('2008', '2012', '2016')",
"county_population is not null", "state_population is not null",
"county_area_land is not null", "county_area_water is not null",
"uninsured is not null", "uninsured_pct is not null"
],
}
for model_code, cols in mc_mapping.items():
for col in cols:
session.merge(ModelDropCol(model_code_id=model_code, column=col))
for model_code, wcs in wc.items():
for wc in wcs:
session.merge(ModelWhereClause(model_code_id=model_code, sql=wc))
# states and counties
states = []
counties = []
st_abbr_to_fips = dict()
with open(str(base_data / "fips.csv"), "r") as csv_file:
cols = ["fips", "name", "state"]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader) # skip header
county_names = [
"COUNTY", "PARISH", "BOROUGH", "CENSUS AREA", "MUNICIPALITY",
"CITY"
]
for row in reader:
fips = str(row.get("fips")).zfill(6)
name = str(row.get("name")).upper()
state = str(row.get("state")).upper()
# state
if not any([n in name for n in county_names]):
# DC Special case
if fips == "011000":
states.append(State(id=fips, name=name, code=state))
st_abbr_to_fips[state] = fips
elif fips == "011001":
counties.append(
County(id=fips,
name=name,
state_id=st_abbr_to_fips[state]))
else:
states.append(State(id=fips, name=name, code=state))
st_abbr_to_fips[state] = fips
# county
else:
counties.append(
County(id=fips, name=name,
state_id=st_abbr_to_fips[state]))
for state in states:
session.merge(state)
for county in counties:
session.merge(county)
session.merge(
PoliticalParty(code="D", name="Democratic Party", founding_year=1828))
session.merge(
PoliticalParty(code="R", name="Republican Party", founding_year=1854))
session.merge(PoliticalParty(code="OTH", name="Generic Other Candidate"))
session.merge(PoliticalParty(code="WI", name="Write In Candidate"))
session.merge(
PoliticalParty(code="W",
name="Whig Party",
founding_year=1833,
defunct_ind=True))
session.merge(
PoliticalParty(code="F",
name="Federalist Party",
founding_year=1791,
defunct_ind=True))
session.merge(
PoliticalParty(code="DR",
name="Democratic-Republican Party",
founding_year=1792,
defunct_ind=True))
session.merge(
PoliticalParty(code="L", name="Libertarian Party", founding_year=1971))
session.merge(
PoliticalParty(code="C", name="Constitution Party",
founding_year=1990))
session.merge(
PoliticalParty(code="RF", name="Reform Party", founding_year=1995))
session.merge(PoliticalParty(code="I", name="Independent"))
session.merge(
PoliticalParty(code="G", name="Green Party", founding_year=2001))
session.merge(
PoliticalParty(code="PSL",
name="Party for Socialism and Liberation",
founding_year=2004))
session.merge(
PoliticalParty(code="SPU",
name="Socialist Party USA",
founding_year=1973))
session.merge(
PoliticalParty(code="AM",
name="Anti-Masonic Party",
founding_year=1828,
defunct_ind=True))
session.merge(
PoliticalParty(code="DC",
name="States' Rights Democratic Party",
founding_year=1948,
defunct_ind=True))
session.merge(
PoliticalParty(code="CU",
name="Constitutional Union Party",
founding_year=1860,
defunct_ind=True))
session.merge(
PoliticalParty(code="KN",
name="Know Nothing Party",
founding_year=1844,
defunct_ind=True))
session.merge(
PoliticalParty(code="FS",
name="Free Soil Party",
founding_year=1848,
defunct_ind=True))
session.merge(
PoliticalParty(code="NU",
name="Nullifier Party",
founding_year=1828,
defunct_ind=True))
session.merge(
PoliticalParty(code="NR",
name="National Republican Party",
founding_year=1824,
defunct_ind=True))
session.merge(
PoliticalParty(code="AL", name="Alliance Party", founding_year=2018))
session.merge(
PoliticalParty(code="ADP",
name="American Delta Party",
founding_year=2016))
session.merge(
PoliticalParty(code="JU", name="Justice Party", founding_year=2011))
session.merge(
PoliticalParty(code="NL",
name="Natural Law Party",
founding_year=1992,
defunct_ind=True))
session.merge(
PoliticalParty(code="NA",
name="New Alliance Party",
founding_year=1979,
defunct_ind=True))
session.merge(
PoliticalParty(code="PO",
name="Populist Party",
founding_year=1984,
defunct_ind=True))
for t in [
"PRESIDENTIAL", "US SENATE", "US HOUSE", "GOVERNOR",
"STATE ASSEMBLY", "STATE SENATE"
]:
session.merge(ElectionType(code=t))
presidential = session.query(ElectionType).filter_by(
code="PRESIDENTIAL").first()
for year in range(1788, 2021, 4):
session.merge(Election(election_type=presidential, year=year))
with open(str(base_data / "candidates.csv"), "r") as csv_file:
cols = [
"first_name", "middle_initial", "last_name", "home_state",
"birth_date", "death_date", "nickname", "display_name"
]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader)
for r in reader:
home_state = r.get("home_state")
bd = r.get("birth_date")
dd = r.get("death_date")
if home_state:
home_state_id = session.query(
State.id).filter_by(code=home_state).scalar()
else:
home_state_id = None
if bd:
bd = datetime.datetime.strptime(bd, "%d-%b-%Y").date()
else:
bd = None
if dd:
dd = datetime.datetime.strptime(dd, "%d-%b-%Y").date()
else:
dd = None
session.merge(
Candidate(home_state_id=home_state_id,
first_name=r.get("first_name"),
last_name=r.get("last_name"),
birth_date=bd,
death_date=dd,
display_name=r.get("display_name"),
nickname=r.get("nickname"),
middle_initial=r.get("middle_initial")))
with open(str(base_data / "campaigns.csv"), "r") as csv_file:
cols = [
"p_first", "p_last", "vp_first", "vp_last", "party", "year",
"election_type"
]
reader = csv.DictReader(csv_file, fieldnames=cols)
next(reader)
for row in reader:
year = int(row.get("year"))
et = session.query(ElectionType).filter_by(
code=row.get("election_type")).first()
e = session.query(Election).filter_by(
year=year, election_type_id=et.id).first()
fn = row.get("p_first")
ln = row.get("p_last")
vpfn = row.get("vp_first")
vpln = row.get("vp_last")
p = session.query(PoliticalParty).filter_by(
code=row.get("party")).first()
# bush special case
if fn == "George" and ln == "Bush":
if vpfn == "Richard":
mi = "W"
else:
mi = "HW"
cand = session.query(Candidate).filter_by(
first_name=fn, last_name=ln, middle_initial=mi).first()
else:
cand = session.query(Candidate).filter_by(
first_name=fn, last_name=ln).first()
if vpfn and vpln:
vp = session.query(Candidate).filter_by(
first_name=vpfn, last_name=vpln).first()
else:
vp = None
session.merge(
Campaign(candidate_id=cand.id,
running_mate_id=vp.id if vp else None,
election_id=e.id,
political_party_id=p.id))
session.commit()
session.close()
