def bill_id_in_bills_table(bill):
bill_id = None
count_command_string = f"SELECT COUNT(*) FROM Bills WHERE titleStripped = '{bill.title_stripped}';"
count_from_interaction = db_agent.select(count_command_string)
count = count_from_interaction[0][0]
print(f"type count: {type(count)}")
print(f"count: {count}")
if count != 0:
if count > 1:
raise Exception("billID must be unique, duplicates found")
select_bill_id_string = f"SELECT billID FROM Bills WHERE titleStripped = '{bill.title_stripped}';"
bill_id_from_interaction = db_agent.select(select_bill_id_string)
print(f"type bill id interaction: {type(bill_id_from_interaction)}")
print(f"bill id interaction: {bill_id_from_interaction}")
bill_id = int(bill_id_from_interaction[0][0])
print(f"bill_id: {bill_id}")
print(f"bill_id type: {type(bill_id)}")
else:
return None
return bill_id
def put_bill_and_division_data_in_db(bills_overview):
for bill in bdi.get_bill_details(bills_overview):
# get the billID from DB
bill_id = bill_id_in_bills_table(bill)
print("bill ID")
print(bill_id)
# if there is no billID, then the bill is not in the table, hence we need to insert the bill
if bill_id is None:
print(f"bill {bill.title_stripped} not yet in Bills table")
execute_insert_new_bill_into_bills_table(bill)
bill_id = bill_id_in_bills_table(bill)
else:
print(f"bill {bill.title_stripped} already in Bills table")
row_before_op = db_agent.select(
f"SELECT * FROM Bills WHERE titleStripped = '{bill.title_stripped}';"
)
print(f"row before: {row_before_op}")
execute_update_bill(bill)
row_after_op = db_agent.select(
f"SELECT * FROM Bills WHERE titleStripped = '{bill.title_stripped}';"
)
print(f"row after: {row_after_op}")
for division in bill.divisions_list:
division_title = division.division_name
division_stage = division.division_stage
# only insert if we haven't already inserted the results
if not division_in_mpvotes_table(division.division_name):
print(f"division {division_title} not yet in MPVotes table")
for no in division.noes:
execute_insert_new_vote_into_mpvotes_table(division_title,
division_stage,
bill_id,
no,
aye=False)
for aye in division.ayes:
execute_insert_new_vote_into_mpvotes_table(division_title,
division_stage,
bill_id,
aye,
aye=True)
else:
print(f"division {division_title} already in MPVotes table")
def execute_update_bill(bill):
sessions_string = get_sessions_string(bill.sessions)
datetime_iso_string = bill.last_updated.isoformat()
print(f"datetime iso: {datetime_iso_string}")
print("row matching")
print(
db_agent.select(
f"SELECT * FROM Bills WHERE titleStripped = '{bill.title_stripped}';"
))
update_command_string = f"UPDATE Bills SET billOrAct='{bill.title_postfix}', dateAdded='{datetime_iso_string}', shortDesc='{bill.summary}', sessions='{sessions_string}', link='{bill.url}' WHERE titleStripped='{bill.title_stripped}';"
def is_in_field(table, field, val, type):
if type == "string":
count_from_interaction = db_agent.select(
f"SELECT COUNT(*) FROM {table} WHERE {field} = '{val}';")
elif type == "int":
count_from_interaction = db_agent.select(
f"SELECT COUNT(*) FROM {table} WHERE {field} = {val};")
else:
raise ValueError("unrecog type")
print("in is_in_field")
print(count_from_interaction)
print(type(count_from_interaction))
print(count_from_interaction[0])
print(type(count_from_interaction[0]))
print(count_from_interaction[0][0])
print(type(count_from_interaction[0][0]))
count = count_from_interaction[0][0]
if count > 0:
return True
else:
return False
def execute_insert_mp_data_in_db(first_name, second_name, email, constituency,
member_id, party_id, active):
if active == True:
current = 1
else:
current = 0
print("current state of MP")
select_string = f"SELECT * FROM MP;"
rows = db_agent.select(select_string)
print(rows)
insert_command_string = f"INSERT INTO MP (mpID, firstName, lastName, email, partyID, area, current) VALUES ('{member_id}','{first_name}','{second_name}','{email}',{party_id},'{constituency}','{current}');"
print(f"mp insert command string")
print(insert_command_string)
db_agent.interact(insert_command_string)
def division_in_mpvotes_table(division_name):
count_command_string = f"SELECT COUNT(*) FROM MPVotes WHERE title = '{division_name}';"
count_from_interaction = db_agent.select(count_command_string)
#print("in division_in_mpvotes_table")
#count = count_from_interaction[0]
#print(f"count after index: {count}")
#count = count[0]
#print(f"count after second index: {count}")
#print(f"count type after second index: {type(count)}")
#count = extract_first_string_from_db_interaction(count_from_interaction)
#count = int(count)
count = count_from_interaction[0][0]
if count > 0:
return True
else:
return False
def print_all_rows_of_table(table_name):
rows = db_agent.select(f"SELECT * FROM {table_name};")
print(f"all items in table {table_name}:")
print(rows)
def db_describe_table(table_name):
description = db_agent.select(f"DESCRIBE {table_name};")
print(f"{table_name} table structure")
print(description)