def index():
latlon = pd.read_csv('files/us_lat_lon.csv')
if request.method == 'POST':
year = request.form['year']
else:
year = 2017
v_o_t_e = db_votes(year)
Sen_db = v_o_t_e[1]
sen_map = Sen_db.drop(['Year','Congress','Session'], axis=1)
sen_map = map_create(sen_map, latlon)
sen_map.save('templates/map.html')
party_total = v_o_t_e[0][['Party','Yea','Nay','Not Voting','Total']]
party_total['Party'] = party_total['Party'].replace("D", "Democrat")
party_total['Party'] = party_total['Party'].replace("R", "Republican")
party_total['Party'] = party_total['Party'].replace("I", "Independent")
sq = """SELECT * FROM congres_tb WHERE year = {0};"""
sql2 = sq.format(year)
senate_info = rsq(sql2,g.db)
vote_list=vote_list_fun(year)
pd.set_option('display.max_colwidth', -1)
year_list=rsq("SELECT year FROM congres_tb ORDER BY year DESC", g.db)['year'].tolist()
year_list.insert(0, year)
return render_template('index.html', year = year, party_total=party_total, senate_info=senate_info, vote_list=vote_list,
year_list=year_list)
def get_cong_ses_db(
year): # Necessary to complete the table name in the db_votes function
cong = int(
rsq(("SELECT * FROM congres_tb WHERE year =" + str(year) + ""),
g.db)['congress'])
sess = int(
rsq(("SELECT * FROM congres_tb WHERE year =" + str(year) + ""),
g.db)['session'])
return pd.Series({'Congress': cong, 'Session': sess})
def vote_list_fun(year):
table_name = str("vl_" + str(year) + "_" + str(get_cong_ses_db(year)[0]) +
"_" + str(get_cong_ses_db(year)[1]) + "_vl")
ls_query = ("SELECT * FROM " + table_name + " ;")
list_votes = rsq(ls_query, g.db)
list_votes = list_votes[['Vote_Number', 'Question', 'Yeas', 'Nays']]
return list_votes
def senator_voting(year, member):
tablename = str("vote_"+str(year)+"_"+str(get_cong_ses_db(year)[0])+"_"+str(get_cong_ses_db(year)[1]))
sen_q=("SELECT * FROM "+tablename+" WHERE Member ='"+member+"' ;")
df_sen = rsq(sen_q,conn)
df_sen=df_sen.transpose()[8:]
df_sen.columns=[str(member)]
return df_sen
def db_votes(
year
): # Runs the vote_count_all function on the appropriate df after a sql query
tablename = str("vote_" + str(year) + "_" + str(get_cong_ses_db(year)[0]) +
"_" + str(get_cong_ses_db(year)[1]))
que = ("SELECT * FROM " + tablename + " ;")
dbvot = vote_count_all(rsq(que, g.db))
return dbvot
vote_ol = int(max(re.findall('vote=([0-9]{5})+', str(response))))
#print("Current online congress %d, current session %d, and current vote %d"
# % (congress_ol, session_ol, vote_ol))
# Getting the last vote on data base
DATABASE = 'sen_vote.db'
def connect_db():
return sqlite3.connect(DATABASE)
con = connect_db()
q_a = rsq("SELECT * FROM vote_2017_114_1", con)
db = sqlite3.connect('..\data\sen_vote.db')
db.text_factory = str
cur = db.cursor()
result = cur.execute(
"SELECT name FROM sqlite_master WHERE type='table';").fetchall()
table_names = sorted(zip(*result)[0])
print("\ntables are:" + newline_indent + newline_indent.join(table_names))
rsq('show', con)
lastVote = pd.DataFrame(q_a.columns)[pd.DataFrame(
q_a.columns)[0].str.startswith("v") == True].tail(1).iloc[0]
vote_db = int(lastVote[0][-3:])
def senator_name(year):
tablename = str("vote_"+str(year)+"_"+str(get_cong_ses_db(year)[0])+"_"+str(get_cong_ses_db(year)[1]))
sen_name=("SELECT Last FROM "+tablename+" Limit 1;")
df_sen_name = rsq(sen_name,conn)
return df_sen_name
def vote_list(year):
table_name = str("vl_"+str(year)+"_"+str(get_cong_ses_db(year)[0])+"_"+str(get_cong_ses_db(year)[1])+"_vl")
ls_query=("SELECT * FROM "+table_name+" ;")
list_votes = rsq(ls_query,conn)
list_votes = list_votes[['Vote_Numer']]
return list_votes
def db_votes(year):
tablename = str("vote_"+str(year)+"_"+str(get_cong_ses_db(year)[0])+"_"+str(get_cong_ses_db(year)[1]))
que=("SELECT * FROM "+tablename+" ;")
dbvot = vote_count_all(rsq(que,conn))
return dbvot
def get_cong_ses_db(year):
cong = int(rsq(("SELECT * FROM congres_tb WHERE year ="+str(year)+""), conn)['congress'])
sess = int(rsq(("SELECT * FROM congres_tb WHERE year ="+str(year)+""), conn)['session'])
return pd.Series({'Congress': cong, 'Session': sess})
conn.commit()
# close (this saves the db to the harddrive)
conn.close()
#####################
#OPEN the DATA BASE:
conn = sqlite3.connect('/Users/jpinzon/Desktop/flask/map_app/sen_vote.db')
# START THE CURSOR
cur = conn.cursor()
os.listdir('/Users/jpinzon/Desktop/flask/map_app/')
###### Transfer a pandas DF to a sql database
congres_tb.to_sql(name='congres_tb', con=conn, if_exists='replace', index=False)
# List tables in the database
rsq("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", conn)
cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name ASC;").fetchall()
# Information from the tables.
rsq("PRAGMA table_info(congres_tb)", conn)
# Print contents in a tabl
rsq("SELECT * FROM congres_tb WHERE year =2018", conn)
rsq("SELECT * FROM vl_2017_115_1_vl", conn)
# Print header of each table
for i in range(1,len(v)):
vote = v['name'][i]
cur=conn.cursor()