def insert_address(column_values, row_values):
'''
Grabs a row and inserts the data for a row in the Address table.
Takes in a dataframe read_excel object with iloc[row].
'''
# check if client exists in the database
client_id = row_values[2]
index_list = [(7, 15)]
if (not (database_methods.check_id(client_id, 'client_data.db', "Client",
"Unique_ID_Value"))):
add_id = insert_general.insert_address(row_values, index_list)
return add_id
else:
# if customer already exists, update their address with current data
lst = [
"Street_Number", "Street_Name", "Street_Type", "Street_Direction",
"Unit", "City", "Province", "Postal_Code"
]
(col, data) = database_methods.update_lists(lst, column_values,
row_values, index_list)
add_id = database_methods.get_existing_id("client_data.db", "Client",
"Address_ID",
"Unique_ID_Value", client_id)
query = database_methods.create_update_query(col, data, "Address",
"ID", add_id)
database_methods.update_query(query, "client_data.db")
def insert_client_needs(column_values, row_values, id_value):
'''
Inserts data into the Client_Needs table. Values are all Yes/No
'''
index_list = [(11, 29), (30, 32), (33, 34), (41, 47), (48, 68)]
table = "Client_Needs"
prim = "(Client_Unique_ID_Value, Type)"
lst = ["Value"]
i = 0
while (i < len(index_list)):
start = index_list[i][0]
end = index_list[i][1]
while (start < end):
if (type(row_values[start] == str)):
prim_val = "(" + id_value + ", '" + column_values[start] + "')"
value = database_methods.get_existing_id(
db, table, "Type", prim, prim_val)
if (value is not None):
(col, data) = database_methods.update_lists(
lst, None, row_values, [(start, start + 1)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
insert_general.insert_3_value(column_values, row_values,
table, id_value, start,
start + 1)
start += 1
i += 1
#if __name__ == "__main__":
#df = pd.read_excel('file.xlsx')
#column_values = df.iloc[1]
#row_values = df.iloc[2]
def insert_trans_int(row_values, id_value):
'''
Inserts information in the Translation_Interpretation table.
'''
table = "Translation_Interpretation"
prim = "(Client_Unique_ID_Value, Type)"
trans_id = ("(" + id_value + ", 'Translation')")
int_id = ("(" + id_value + ", 'Interpretation')")
val = [id_value]
lst = ["'Between'", "'And'"]
# check if "Translation" is Yes
if (row_values[82] == "Yes"):
value = database_methods.get_existing_id(db, table, "Type", prim,
trans_id)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(83, 85)])
query = database_methods.create_update_query(
col, data, table, prim, trans_id)
database_methods.update_query(query, db)
else:
val.append("Translation")
# get columns 83-84
val = database_methods.fetch_values(83, 85, row_values, val)
insert_helper.insert_row(val, table)
val = [id_value]
# check if "Interpretation" is Yes
if (row_values[85] == "Yes"):
value = database_methods.get_existing_id(db, table, "Type", prim,
int_id)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(86, 88)])
query = database_methods.create_update_query(
col, data, table, prim, int_id)
database_methods.update_query(query, db)
else:
val.append("Interpretation")
val = database_methods.fetch_values(86, 88, row_values, val)
insert_helper.insert_row(val, table)
def insert_improve_skills(column_names, row_values, id_value):
'''
Inserts data into the Improve_Skills table.
'''
val = [id_value]
table = "Improve_Skills"
prim = "(Client_Unique_ID_Value, Improve)"
lst = ["'Description'"]
if (type(row_values[29]) == str):
prim_val = "(" + id_value + ", '" + column_names[29] + "')"
value = database_methods.get_existing_id(db, table, "Improve", prim,
prim_val)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(29, 30)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
val.append(column_names[29])
val = database_methods.fetch_values(29, 30, row_values, val)
insert_helper.insert_row(val, table)
val = [id_value]
if (type(row_values[32]) == str):
prim_val = "(" + id_value + ", '" + column_names[32] + "')"
value = database_methods.get_existing_id(db, table, "Improve", prim,
prim_val)
if (value is not None):
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(32, 33)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
val.append(column_names[32])
val = database_methods.fetch_values(32, 33, row_values, val)
insert_helper.insert_row(val, table)
def update_child(row_values, id_value, lst, table, prim, i, j, last):
while (j < last):
if (type(row_values[j]) == str or type(row_values[j + 1]) == str):
child_id = database_methods.get_existing_id(
db, table, table, prim, ("(" + id_value + "," + str(i) + ")"))
if (child_id is not None):
#update current child
(col,
data) = database_methods.update_lists(lst, None, row_values,
[(j, j + 2)])
query = database_methods.create_update_query(
col, data, table, prim,
("(" + id_value + "," + str(i) + ")"))
database_methods.update_query(query, db)
else:
#insert new child into the database
val = [id_value, i]
val = database_methods.fetch_values(j, j + 2, row_values, val)
insert_helper.insert_row(val, table)
i += 1
j += 2
def update_skills(column_values, row_values, client_id, index_list):
table = "Skills"
prim = "(Client_Unique_ID_Value, Skill)"
lst = ["Value"]
i = 0
while (i < len(index_list)):
start = index_list[i][0]
end = index_list[i][1]
while (start < end):
if (type(row_values[start] == str)):
prim_val = "(" + client_id + ", '" + column_values[start] + "')"
value = database_methods.get_existing_id(
db, table, "Skill", prim, prim_val)
if (value is not None):
(col, data) = database_methods.update_lists(
lst, column_values, row_values, [(start, start + 1)])
query = database_methods.create_update_query(
col, data, table, prim, prim_val)
database_methods.update_query(query, db)
else:
insert_3_value(column_values, row_values, table, client_id,
start, start + 1)
start += 1
i += 1