def delete_owns(database):
delete_on = input("Deleting from owns. Specify deletion attribute: \n\t"
"Delete table: t\n\t"
"Delete on land_id: l\n\t"
"Delete on owner_id: o\n\t").lower()
if delete_on == 'q':
util.exit_TEAL(database)
elif delete_on == 't':
database.delete_table('owns')
elif delete_on == 'l':
delete_id = input('land id to delete: \t')
if delete_id.isdigit():
database.delete('owns', 'land_id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer\t')
delete_land(database)
elif delete_on == 'o':
delete_id = input('owner id to delete: \t')
if delete_id.isdigit():
database.delete('owns', 'owner_id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer\t')
delete_land(database)
else:
sys.stdout.write("Did not recognize selection.\t")
delete_owner(database)
repeat(database)
def delete_land(database):
delete_on = input("Deleting from land. Specify deletion attribute: \n\t"
"Delete table: t\n\t"
"Delete on id: id\n\t"
"Delete on quality rating: r\n\t"
"Delete on area: a\n\t").lower()
if delete_on == 'q':
util.exit_TEAL(database)
elif delete_on == 't':
database.delete_table('land')
elif delete_on == 'id':
delete_id = input('id to delete: \t')
if delete_id.isdigit():
database.delete_item('land', 'id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer')
delete_land(database)
elif delete_on == 'r':
delete_rating = input('delete on rating (accepts range using <, >, =): \t')
database.delete_item('land', 'rating', delete_rating)
elif delete_on == 'a':
delete_area = input('delete on area (accepts range using <, >, =): \t')
database.delete_item('land', 'area', delete_area)
else:
sys.stdout.write("Did not recognize selection.\t")
delete_land(database)
repeat(database)
def initiate_delete(database):
try:
table = input('Delete: \n\t'
'Table name: \t').lower()
if table == 'q':
util.exit_TEAL(database)
elif table == 'land':
delete_land(database)
elif table == 'county':
delete_county(database)
elif table == 'improvement':
delete_improvement(database)
elif table == 'owner':
delete_owner(database)
elif table == 'governs':
delete_governs(database)
elif table == 'owns':
delete_owns(database)
elif table == '':
sys.stdout.write('Table must be specified for delete\t')
initiate_delete(database)
else:
sys.stdout.write('Did not recognize table name. Please specify table in database: \n\t'
'land\n\t'
'county\n\t'
'improvement\n\t'
'owner\n\t')
initiate_delete(database)
except Exception as e:
print("Encountered error: ", e, "while attempting delete.")
initiate_delete(database)
repeat(database)
def delete_county(database):
delete_on = input("Deleting from county. Specify deletion attribute: \n\t"
"Delete table: t\n\t"
"Delete on id: id\n\t"
"Delete on name: n\n\t"
"Delete on population: p\n\t"
"Delete on growth rate: gr\n\t").lower()
if delete_on == 'q':
util.exit_TEAL(database)
elif delete_on == 't':
database.delete_table('county')
elif delete_on == 'id':
delete_id = input('id to delete: \t')
if delete_id.isdigit():
database.delete_item('county', 'id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer\t')
delete_land(database)
elif delete_on == 'n':
delete_name = input('name to delete: \t')
database.delete_item('county', 'name', '= "' + delete_name + '"')
elif delete_on == 'p':
delete_pop = input('population to delete (accepts range using >, <, =): \t')
database.delete_item('county', 'pop', delete_pop)
elif delete_on == 'gr':
delete_gr = input('growth rate to delete (accepts range using >, <, =): \t')
database.delete_item('county', 'growth_rate', delete_gr)
else:
sys.stdout.write("\tDid not recognize selection.\t")
delete_county(database)
repeat(database)
def initiate_modify(database):
table = input('Modify: \n\t' 'Table name: \t').lower()
if table == 'q':
util.exit_TEAL(database)
elif table == 'land':
modify_land(database)
elif table == 'county':
modify_county(database)
elif table == 'improvement':
modify_improvement(database)
elif table == 'owner':
modify_owner(database)
elif table == '':
sys.stdout.write('Table must be specified for modify\t')
initiate_modify(database)
else:
sys.stdout.write(
'Did not recognize table name. Please specify table in database: \n\t'
'land\n\t'
'county\n\t'
'improvement\n\t'
'owner\n\t')
initiate_modify(database)
repeat(database)
def delete_owner(database):
delete_on = input("Deleting from owner. Specify deletion attribute: \n\t"
"Delete table: t\n\t"
"Delete on id: id\n\t"
"Delete on status: s\n\t"
"Delete on name: n\n\t").lower()
if delete_on == 'q':
util.exit_TEAL(database)
elif delete_on == 't':
database.delete_table('owner')
elif delete_on == 'id':
delete_id = input('id to delete: \t')
if delete_id.isdigit():
database.delete('land', 'id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer\t')
delete_land(database)
elif delete_on == 's':
delete_status = input('status to delete (private/public): \t')
database.delete_item('owner', 'status', '= "' + delete_status + '"')
elif delete_on == 'n':
delete_name = input('name to delete: \t')
database.delete_item('owner', 'name', '= "' + delete_name + '"')
else:
sys.stdout.write("Did not recognize selection.\t")
delete_owner(database)
repeat(database)
def delete_improvement(database):
delete_on = input("Deleting from improvement. Specify deletion attribute: \n\t"
"Delete table: t\n\t"
"Delete on id: id\n\t"
"Delete on improvement_type: it\n\t"
"Delete on cost: c\n\t"
"Delete on improvement level: i\n\t").lower()
if delete_on == 'q':
util.exit_TEAL(database)
elif delete_on == 't':
database.delete_table('improvement')
elif delete_on == 'id':
delete_id = input('id to delete: \t')
if delete_id.isdigit():
database.delete_item('improvement', 'id', '=' + delete_id)
else:
sys.stdout.write('id must be an integer\t')
delete_land(database)
elif delete_on == 'it':
delete_improvement_type = input("improvement type to delete: \t").lower()
database.delete_item('improvement', 'improvement_type', '= "' + delete_improvement_type + '"')
elif delete_on == 'c':
delete_cost = input('cost to delete (accepts range using >, <, =): \t')
database.delete_item('improvement', 'cost', delete_cost)
elif delete_on == 'i':
delete_i = input('improvement level to delete (accepts range using >, <, =): \t')
database.delete_item('improvement', 'improvement', delete_i)
else:
sys.stdout.write("\tDid not recognize selection.\t")
delete_improvement(database)
repeat(database)
def repeat(database):
repeat_modify = input("\tmodify again (y/n)?: ").lower()
if repeat_modify == 'q':
util.exit_TEAL(database)
elif repeat_modify == 'y':
initiate_modify(database)
else:
user_prompt.interact(database)
def repeat(database):
repeat_input = input('Insert again (y/n)?: \n').lower()
if repeat_input == 'q':
util.exit_TEAL(database)
elif repeat_input == 'y':
initiate_insert(database)
else:
user_prompt.interact(database)
def repeat(database):
repeat_modify = input("\tquery again (y/n)?: ").lower()
if repeat_modify == 'q':
util.exit_TEAL(database)
elif repeat_modify == 'y':
query_options(database)
else:
user_prompt.interact(database)
def repeat(database):
repeat_delete = input("\tDelete again (y/n)?: ").lower()
if repeat_delete == 'q':
util.exit_TEAL(database)
elif repeat_delete == 'y':
initiate_delete(database)
else:
user_prompt.interact(database)
def get_csv(prompt, database):
csv = input(prompt)
if csv.lower() == 'q':
util.exit_TEAL(database)
if not csv.endswith('.csv'):
get_csv("\tPlease provide the data in csv format: ", database)
csv = util.get_base_dir() / 'final_project/input' / csv
if not csv.is_file():
get_csv(
"File not found. Please provide name of csv in input directory: ",
database)
return csv
def interact(database):
select = selection_prompt()
while select != 'q':
if select == 'i':
input_prompt.initiate_insert(database)
elif select == 'd':
delete_prompt.initiate_delete(database)
elif select == 'm':
modify_prompt.initiate_modify(database)
elif select == 'o':
query_prompt.query_options(database)
else:
select = selection_prompt()
util.exit_TEAL(database)
def get_database(prompt, database):
database_file = input(prompt)
if database_file.lower() == 'q':
util.exit_TEAL(database)
if not database_file.endswith('.sqlite'):
get_database('\tPlease provide a path to a sqlite file: ', database)
database_file = util.get_base_dir(
) / 'final_project/database' / database_file
if not database_file.is_file():
get_database(
"\tDatabase file not found. Please provide sqlite file in database directory: ",
database)
return database_file
def user_prompt():
sys.stdout.write("Welcome to TEAL (Texas Explorer for Arid Land)\n\t"
"To exit, press q\n"
"Database Setup: \t\n")
defaults = input("\tUse Defaults (y/n): ").lower()
if defaults == 'q':
util.exit_TEAL(None)
if defaults == 'y':
database_file = util.get_base_dir(
) / 'final_project/database/TEAL.sqlite'
land_csv = util.get_base_dir() / 'final_project/input/land.csv'
county_csv = util.get_base_dir() / 'final_project/input/county.csv'
owner_csv = util.get_base_dir() / 'final_project/input/owner.csv'
improvement_csv = util.get_base_dir(
) / 'final_project/input/improvement.csv'
governs_csv = util.get_base_dir() / 'final_project/input/governs.csv'
owns_csv = util.get_base_dir() / 'final_project/input/owns.csv'
else:
database_file = get_database(
"\tPlease provide a path to the database: ", None)
land_csv = get_csv("\tPlease provide land data: ", None)
county_csv = get_csv("\tPlease provide county data: ", None)
owner_csv = get_csv("\tPlease provide land owner data: ", None)
improvement_csv = get_csv("\tPlease provide land improvement data: ",
None)
governs_csv = get_csv("\tPlease provide land governance data: ", None)
owns_csv = get_csv("\tPlease provide land ownership data: ", None)
database = queries.Database(database_file)
database.load_land_data(land_csv)
database.load_county_data(county_csv)
database.load_owner_data(owner_csv)
database.load_improvement_data(improvement_csv)
database.load_governs_data(governs_csv)
database.load_owns_data(owns_csv)
sys.stdout.write('\tDatabase successfully initialized.\n')
interact(database)
def initiate_insert(database):
try:
table = input(
'Insert: \n\t'
'If you do not wish to specify an input, press enter \n\t'
'Table name: ').lower()
if table == 'q':
util.exit_TEAL(database)
elif table == 'land':
insert_land(database)
elif table == 'county':
insert_county(database)
elif table == 'improvement':
insert_improvement(database)
elif table == 'owner':
insert_owner(database)
elif table == 'governs':
insert_governs(database)
elif table == 'owns':
insert_owns(database)
elif table == '':
sys.stdout.write('Table must be specified for insert\n')
initiate_insert(database)
else:
sys.stdout.write(
'Did not recognize table name. Please specify table in database: \n\t'
'land\n\t'
'county\n\t'
'improvement\n\t'
'owner\n')
initiate_insert(database)
except Exception as e:
print('Encountered error: ', e, ' while attempting to insert.')
initiate_insert(database)
repeat(database)
def query_options(database):
select = input('Query Options: \n\t'
'Get Land by County: 1\n\t'
'Get Land by Area: 2\n\t'
'Get Land by Owner: 3\n\t'
'Get Land by Quality: 4\n\t'
'View Land Details: 5\n\t'
'Get Average Land Rating by County: 6\n\t'
'Get Land Area by Owner: 7\n\t'
'Get Owners in County: 8\n\t'
'Get Critical Land Count by County: 9\n\t'
'Get Land by Owner Status: 10\n\t'
'Optimize Land Improvement: 11\n\t'
'Formulate Custom Query: 12\n\t').lower()
if select == 'q':
util.exit_TEAL(database)
try:
select = int(select)
except Exception as e:
print('Encountered error: ', e,
'while processing user input. Integer expected')
query_options(database)
try:
if select == 1:
county_name = input('search county name: \t')
database.get_land_by_county(county_name)
elif select == 2:
min_area = int(input('minimum area for search: \t'))
max_area = int(input('maximum area for search: \t'))
database.get_land_by_area(min_area, max_area)
elif select == 3:
owner_name = input('search owner name: \t')
database.get_land_by_owner(owner_name)
elif select == 4:
min_ratiing = input('minimum rating for search: \t')
max_rating = input('maximum rating for search: \t')
database.get_land_by_quality_rating(min_ratiing, max_rating)
elif select == 5:
database.view_land_details()
elif select == 6:
database.get_average_rating_county()
elif select == 7:
database.get_area_by_owner()
elif select == 8:
county_name = input('county name for search: \t')
database.get_owners_in_county(county_name)
elif select == 9:
critical_threshold = int(
input('critical land quality threshold: \t'))
database.get_critical_land_count_by_county(critical_threshold)
elif select == 10:
status = input('ownership status for search (public/private): \t')
database.get_land_by_status(status)
elif select == 11:
owner_name = input('owner name for optimization: \t')
county_name = input('county name for optimization: \t')
database.optimize(owner_name, county_name)
elif select == 12:
table = input('table to perform query on: \t')
distinct = input('DISTINCT (y/n): \t').lower() == 'y'
compare_val = input('attribute to be queried: \t')
condition = input(
'condition on attribute (accepts range using >, <, =): ')
database.generic_query(table, distinct, compare_val, condition)
else:
sys.stdout.write("Query selection not recognized.")
query_options(database)
database.write_result()
except Exception as e:
print("Encountered error: ", e, "while processing query")
query_options(database)
repeat(database)
