def insert_and_update_data(completely_fresh=False,
day_frequency_for_party_and_mp_data=7,
allow_party_and_mp_upsert=True,
run_on_app_engine=True):
global db_name
#global db_agent
db_name = "bill_data"
conn = None
cursor = None
#db_agent = DBAgent(db_name)
#execute_insert_mp_data_in_db(conn, cursor, "test_first", "test_second_new", "test", 6001, 0, False)
#db_describe_table("MP")
#print_all_rows_of_table("MP")
for i in range(10):
result = db_agent.select(
f"SELECT titleStripped FROM {db_name}.Bills WHERE billID={i};")
print(f"got result {i}")
print(result)
print(db_agent.select(f"SELECT * FROM {db_name}.MP;"))
print(db_agent.select(f"SELECT * FROM {db_name}.Party;"))
#cursor.close()
#conn.close()
return
"""
def get_bill(bill_id):
# not case-sensitive
response = database.select("SELECT * FROM Bills WHERE billID = " + bill_id + ";")
if response is None:
return jsonify({"error": "Query failed"})
else:
return jsonify(entry_to_json_dict(response[0]))
def is_new_address(email_address: str) -> bool:
"""
Checks the database to see if the given email address is already in use.
:param email_address: The email address to look up.
:return: True if the email address is not being used, false otherwise.
"""
query = database.select(f"SELECT * FROM Users WHERE email='{email_address}';") # Get the user(s) with the given email
if query:
return False # If the query returns a populated list, return False
return True # If the query returns an empty list return True
def create_session_token() -> str:
"""
Generate a unique token using a combination of random digits, lowercase and uppercase letters.
:return: The unique, generated token.
"""
token = ''.join(random.SystemRandom().choice(string.digits + string.ascii_lowercase + string.ascii_uppercase)
for _ in range(8)) # Use digits, lowercase and uppercase letters, length 8
# Look if it's unique i.e. does not appear already in the db (if not repeat the process)
if database.select(f"SELECT * FROM Users WHERE sessionToken='{token}';"): # Check if the token is in use
return create_session_token() # Repeat the process until a unique token is generated
return token # Return the unique token
def get_bills():
# not case-sensitive
response = database.select("SELECT * FROM Bills;")
if response is None:
return jsonify({"error": "Query failed"})
else:
list = []
for i in range(10):
list.append(entry_to_json_dict(response[i]))
return jsonify(str(list))
def is_in_field(conn, cursor, table, field, val, type):
if type == "string":
#cursor.execute(f"SELECT * FROM {db_name}.{table} WHERE {field} = \"{val}\"")
cursor = db_agent.select(
f"SELECT * FROM {db_name}.{table} WHERE {field} = \"{val}\"")
elif type == "int":
#cursor.execute(f"SELECT * FROM {db_name}.{table} WHERE {field} = {val}")
cursor = db_agent.select(
f"SELECT * FROM {db_name}.{table} WHERE {field} = {val}")
else:
raise ValueError("unrecog type")
count = 0
for row in cursor:
count += 1
if count > 0:
return True
else:
return False
def fetch_user(email_address: str) -> core.User or None:
"""
Finds the user with the given email address, constructs and returns the User object.
:param email_address: The email address of the user.
:return: The constructed User object.
"""
query = database.select(f"SELECT * FROM Users WHERE email='{email_address}';") # Query database for the user
user = None
if query:
user_info = query[0] # Get the user information
user = core.User(user_info[1], user_info[2], user_info[4], user_info[3], user_info[5]) # Construct user
return user
def division_in_mpvotes_table(conn, cursor, division_name):
count_command_string = f"SELECT COUNT(*) FROM {db_name}.MPVotes WHERE title = \"{division_name}\""
#cursor.execute(count_command_string)
cursor = db_agent.select(count_command_string)
for c in cursor:
print(f"type count[0] {type(c[0])}")
count = c[0]
if count > 0:
return True
else:
return False
def fetch_mp(mp_id: int) -> core.ParliamentMember or None:
"""
Finds the member of parliament with the given ID, constructs and returns the ParliamentMember object.
:param mp_id: The id of the MP.
:return: The constructed ParliamentMember object.
"""
parliament_member = None
query = database.select(f"SELECT * FROM MP WHERE mpID='{mp_id}';") # Query database for the member of parliament
if query:
mp_info = query[0] # Extract the MP information
parliament_member = core.ParliamentMember(mp_info[0], mp_info[3], mp_info[4], mp_info[5], mp_info[6],
mp_info[7], mp_info[8], mp_info[9]) # Construct MP object
return parliament_member
def bill_id_in_bills_table(conn, cursor, bill):
bill_id = None
#cursor.execute(f"SELECT billID FROM {db_name}.Bills WHERE titleStripped = \"{bill.title_stripped}\"")
cursor = db_agent.select(
f"SELECT billID FROM {db_name}.Bills WHERE titleStripped = \"{bill.title_stripped}\""
)
count = 0
for row in cursor:
bill_id = row[0]
count += 1
if count > 2:
raise Exception("billID must be unique, duplicates found")
return bill_id
def db_testing():
database.interact("INSERT INTO Users (email,password,postcode,norificationToken,sessionToken) VALUES ('*****@*****.**', 'johncenalover2', 'BA23PZ', 'ExponentPushToken[dTC1ViHeJ36_SqB7MPj6B7]', 'Ga70JuPC');")
return database.select("SELECT * FROM Users;")
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}:")
for x in rows:
print(x)
def db_describe_table(table_name):
description = db_agent.select(f"DESCRIBE {table_name}")
print(f"{table_name} table structure")
for x in description:
print(x)
