def delete_columns(table, drop_columns_str):
drop_columns = [
item for item in drop_columns_str.replace(',', ' ').split()
]
database_connection = DatabaseConnection(table)
database_connection.drop_columns(drop_columns)
database_connection.close_connection()
def insert_data(table, filepath):
data = pd.read_csv(filepath)
data = data.copy()
columns = [column for column in data.columns]
database_connection = DatabaseConnection(table)
database_connection.insert_rows(columns, data)
database_connection.close_connection()
def add_columns(table, column_names_str, column_dtypes_str):
# Converts the string variable to a list with each column name an element
new_columns = [item for item in column_names_str.replace(',', ' ').split()]
new_dtypes = [item for item in column_dtypes_str.replace(',', ' ').split()]
database_connection = DatabaseConnection(table)
database_connection.add_columns(new_columns, new_dtypes)
database_connection.close_connection()
def update_rows(table, conditions, filepath):
print(filepath)
new_data = pd.read_csv(filepath)
new_data = new_data.copy()
columns = [column for column in new_data.columns]
database_connection = DatabaseConnection(table)
database_connection.update_rows(columns, new_data, conditions)
database_connection.close_connection()
def rename_columns(table, old_column_names_str, new_column_names_str):
old_column_names = [
item for item in old_column_names_str.replace(',', ' ').split()
]
new_column_names = [
item for item in new_column_names_str.replace(',', ' ').split()
]
database_connection = DatabaseConnection(table)
database_connection.rename_columns(old_column_names, new_column_names)
database_connection.close_connection()
def between(table,
frame,
column_name,
start_value,
end_value,
type,
filepath=None):
global conditions
database_connection = DatabaseConnection(table)
conditions.append(
database_connection.between(column_name, start_value, end_value))
database_connection.close_connection()
for widget in frame.winfo_children():
widget.destroy()
main_label = tk.Label(
frame,
text='Would you like to include more conditions?',
font=myFont).grid(row=5, columnspan=2)
yes_btn = tk.Button(frame,
text='Yes',
font=myFont,
command=lambda: condition_creator(
table, frame, type, filepath)).grid(row=6,
column=0,
pady=10)
if type == 'drop_rows':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: drop_rows(table, conditions)).grid(row=6,
column=1,
pady=10)
elif type == 'query':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: limit_creator(table, frame, conditions)).grid(
row=6, column=1, pady=10)
elif type == 'update_rows':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: update_rows(table, conditions, filepath)).grid(
row=6, column=1, pady=10)
def create_table(table, filepath, id_included, create_and_insert):
# Reads in DataFrame using pandas
data = pd.read_csv(filepath)
data = data.copy()
# Stores each column name in a list
columns = [column for column in data.columns]
database_connection = DatabaseConnection(table)
database_connection.create_table(columns, data, id_included)
# If the user wishes to insert data as well, the program is able to carry both these out
if (create_and_insert):
database_connection.insert_rows(columns, data)
database_connection.close_connection()
def query_data(table, frame, conditions=None, limit=None, order=None):
# Converts the limit to an integer if it has been specified
if limit is not None:
limit_int = int(limit)
else:
limit_int = None
database_connection = DatabaseConnection(table)
# Obtains query result as a DataFrame
df = database_connection.query(conditions, order, limit_int)
database_connection.close_connection()
for widget in root.winfo_children():
widget.destroy()
root.pack_propagate(False)
root.resizable(0, 0)
data_frame = tk.LabelFrame(root, text="Results", font=myFont)
data_frame.place(height=450, width=450)
data_treeview = ttk.Treeview(data_frame)
data_treeview.place(relheight=1, relwidth=1)
treescrolly = tk.Scrollbar(data_frame,
orient="vertical",
command=data_treeview.yview)
treescrollx = tk.Scrollbar(data_frame,
orient="horizontal",
command=data_treeview.xview)
data_treeview.configure(xscrollcommand=treescrollx.set,
yscrollcommand=treescrolly.set)
treescrollx.pack(side="bottom", fill="x")
treescrolly.pack(side="right", fill="y")
data_treeview["column"] = list(df.columns)
data_treeview["show"] = "headings"
for column in data_treeview["columns"]:
# let the column heading = column name
data_treeview.heading(column, text=column)
# turns the dataframe into a list of lists
df_rows = df.to_numpy().tolist()
# Displays the data in a TreeView: similar to a table
for row in df_rows:
data_treeview.insert("", "end", values=row)
def create_order_by_statement(table,
frame,
column_name,
direction,
conditions=None,
limit=None):
database_connection = DatabaseConnection(table)
if direction == 'Ascending':
order = database_connection.asc(column_name)
elif direction == 'Descending':
order = database_connection.desc(column_name)
database_connection.close_connection()
query_data(table, frame, conditions, limit, order)
def equals(table, frame, column_name, values_str, type, filepath=None):
# Allows for any changes to conditions in this function to be registered globally
global conditions
# Allows for multiple values to be inputted for equivalence
values = [item for item in values_str.replace(',', ' ').split()]
database_connection = DatabaseConnection(table)
conditions.append(database_connection.equal(column_name, values))
database_connection.close_connection()
for widget in frame.winfo_children():
widget.destroy()
main_label = tk.Label(
frame,
text='Would you like to include more conditions?',
font=myFont).grid(row=5, columnspan=2)
yes_btn = tk.Button(frame,
text='Yes',
font=myFont,
command=lambda: condition_creator(
table, frame, type, filepath)).grid(row=6,
column=0,
pady=10)
if type == 'drop_rows':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: drop_rows(table, conditions)).grid(row=6,
column=1,
pady=10)
elif type == 'query':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: limit_creator(table, frame, conditions)).grid(
row=6, column=1, pady=10)
elif type == 'update_rows':
no_btn = tk.Button(
frame,
text='No',
font=myFont,
command=lambda: update_rows(table, conditions, filepath)).grid(
row=6, column=1, pady=10)
def run():
introduction()
# Initiates continuous loop
while True:
table = input(
'Please input the name of the table you would like to use: ')
# Instantiates DatabaseConnection object
database_connection = DatabaseConnection(table)
time.sleep(1)
options = ' a --> Alter existing table \n ' \
'c --> Create new table \n d --> Delete stuff \n i --> Insert rows \n ' \
'q --> Query data \n s --> Save the table as a CSV \n u --> Update rows \n'
print(options)
user_choice = input(
'Please select one of the above options for what you would like to do: '
)
user_choice = user_choice.lower()
if user_choice == 'a':
alter_options = ' a --> Add new columns \n rc --> Rename columns \n rt --> Rename whole table \n'
print(alter_options)
alter_choice = input(
'Choose which of these processes you would like to use: ')
if alter_choice == 'a':
new_columns_str = input(
'Pass a list of new column names to add: ')
# Converts the string variable to a list with each column name an element
new_columns = [
item for item in new_columns_str.replace(',', ' ').split()
]
new_dtypes_str = input(
'Pass a list of the pythonic data type for each new column: '
)
new_dtypes = [
item for item in new_dtypes_str.replace(',', ' ').split()
]
database_connection.add_columns(new_columns, new_dtypes)
elif alter_choice == 'rc':
old_column_names_str = input(
'Pass a list of old column names to change: ')
old_column_names = [
item
for item in old_column_names_str.replace(',', ' ').split()
]
new_column_names_str = input(
'Pass a list of new column names to be changed to: ')
new_column_names = [
item
for item in new_column_names_str.replace(',', ' ').split()
]
database_connection.rename_columns(old_column_names,
new_column_names)
elif alter_choice == 'rt':
new_table_name = input(
'What would you like to rename the table to: ')
database_connection.rename_table(new_table_name)
else:
print(
f'Sorry, {alter_choice} was not one of the options, halting operation now...'
)
elif user_choice == 'c':
print(
'To create a table, a CSV file is needed including columns and rows so the program can '
'gather the data type of each column')
filepath = input('Please type the whole path of this data: ')
id_bool = input(
'Does the CSV file already contain an index? y for yes and anything else for no: '
)
if id_bool.lower() == 'y':
id_included = True
else:
id_included = False
try:
# Reads in DataFrame using pandas
data = pd.read_csv(filepath)
data = data.copy()
# Stores each column name in a list
columns = [column for column in data.columns]
database_connection.create_table(columns, data, id_included)
# If file doesn't exist, issue is raised to user and process stopped
except FileNotFoundError:
print(
'Sorry, that file does not exist, halting operation now...'
)
elif user_choice == 'd':
delete_options = ' c --> Drop columns \n r --> Delete rows \n t --> Drop whole table (Use with caution!) \n'
print(delete_options)
delete_choice = input(
'Choose which of these processes you would like to use: ')
if delete_choice == 'c':
drop_columns_str = input('Pass a list of columns to drop: ')
drop_columns = [
item
for item in drop_columns_str.replace(',', ' ').split()
]
database_connection.drop_columns(drop_columns)
elif delete_choice == 'r':
print(
'Warning - if no conditions are specified all rows will be deleted by default'
)
conditions_choice = input(
'Would you like to include conditions? y for yes and n for no: '
)
if conditions_choice.lower() == 'y':
conditions = [condition_creator(database_connection)]
while True:
multiple_conditions_choice = input(
'Would you like to include more conditions? '
'y for yes, anything else for no: ')
# If user wishes to add more conditions, program will continue looping
if multiple_conditions_choice.lower() == 'y':
conditions.append(
condition_creator(database_connection))
continue
# If user does not want to add more conditions, loop will break
else:
break
# Ensures rows are only deleted when conditions are specified if user has asked for them
if conditions is not None:
database_connection.delete_rows(conditions)
elif conditions_choice.lower() == 'n':
database_connection.delete_rows()
else:
print(f'{conditions_choice} was not an option!')
elif delete_choice == 't':
database_connection.drop_table()
else:
print(
f'Sorry, {delete_choice} was not one of the options, halting operation now...'
)
elif user_choice == 'i':
print(
'To insert rows, a CSV file is needed containing the rows to be added'
)
filepath = input('Please type the whole path of this data: ')
try:
data = pd.read_csv(filepath)
data = data.copy()
columns = [column for column in data.columns]
database_connection.insert_rows(columns, data)
except FileNotFoundError:
print(
'Sorry, that file does not exist, halting operation now...'
)
elif user_choice == 'q':
number_choice = input(
'Would you like to limit results? y for yes and n for no: ')
if number_choice.lower() == 'y':
while True:
row_number = input(
'How many results would you like to have returned: ')
# Ensures the user inputs an integer value for number of rows returned
try:
row_number = int(row_number)
break
except ValueError:
print('Please only input an integer value')
continue
conditions_choice = input(
'Would you like to include conditions? y for yes and n for no: '
)
if conditions_choice.lower() == 'y':
conditions = [condition_creator(database_connection)]
while True:
multiple_conditions_choice = input(
'Would you like to include more conditions? '
'y for yes, anything else for no: ')
# If user wishes to add more conditions, these will be appended to the list
if multiple_conditions_choice.lower() == 'y':
conditions.append(
condition_creator(database_connection))
continue
else:
break
order_choice = input(
'Would you like to order the result? y for yes and n for no: '
)
if order_choice.lower() == 'y':
order = order_by_creator(database_connection)
df = database_connection.query(conditions, order,
row_number)
print(df)
elif order_choice.lower() == 'n':
df = database_connection.query(conditions,
row_number=row_number)
# This prints the resulting DataFrame, but can be modified to e.g. save to a CSV file
print(df)
else:
print(
f'Sorry, {order_choice} was not one of the options, halting operation now...'
)
elif conditions_choice.lower() == 'n':
order_choice = input(
'Would you like to order the result? y for yes and n for no: '
)
if order_choice.lower() == 'y':
order = order_by_creator(database_connection)
df = database_connection.query(order=order,
row_number=row_number)
print(df)
elif order_choice.lower() == 'n':
df = database_connection.query(row_number=row_number)
print(df)
else:
print(
f'Sorry, {order_choice} was not one of the options, halting operation now...'
)
else:
print(
f'Sorry, {conditions_choice} was not one of the options, halting operation now...'
)
elif number_choice.lower() == 'n':
conditions_choice = input(
'Would you like to include conditions? y for yes and n for no: '
)
if conditions_choice.lower() == 'y':
conditions = [condition_creator(database_connection)]
while True:
multiple_conditions_choice = input(
'Would you like to include more conditions? '
'y for yes, anything else for no: ')
if multiple_conditions_choice.lower() == 'y':
conditions.append(
condition_creator(database_connection))
continue
else:
break
order_choice = input(
'Would you like to order the result? y for yes and n for no: '
)
if order_choice.lower() == 'y':
order = order_by_creator(database_connection)
df = database_connection.query(conditions, order)
print(df)
elif order_choice.lower() == 'n':
df = database_connection.query(conditions)
print(df)
else:
print(
f'Sorry, {order_choice} was not one of the options, halting operation now...'
)
elif conditions_choice.lower() == 'n':
order_choice = input(
'Would you like to order the result? y for yes and n for no: '
)
if order_choice.lower() == 'y':
order = order_by_creator(database_connection)
df = database_connection.query(order=order)
print(df)
elif order_choice.lower() == 'n':
df = database_connection.query()
print(df)
else:
print(
f'Sorry, {order_choice} was not one of the options, halting operation now...'
)
else:
print(
f'Sorry, {conditions_choice} was not one of the options, halting operation now...'
)
else:
print(
f'Sorry, {number_choice} was not one of the options, halting operation now...'
)
elif user_choice == 's':
filepath = input(
'Please input the filepath you would like to save the file to: '
)
try:
database_connection.save_table(filepath)
except FileNotFoundError:
print(
'Sorry, that directory does not exist, halting operation now...'
)
elif user_choice == 'u':
print(
'To update rows, a CSV file is needed containing the rows to be added. This should also include '
'all columns present in the table')
filepath = input('Please type the whole path of this data: ')
try:
new_data = pd.read_csv(filepath)
new_data = new_data.copy()
columns = [column for column in new_data.columns]
print(
'To know which rows to update, conditions must be given: \n'
)
conditions = [condition_creator(database_connection)]
while True:
multiple_conditions_choice = input(
'Would you like to include more conditions? '
'y for yes, anything else for no: ')
if multiple_conditions_choice.lower() == 'y':
conditions.append(
condition_creator(database_connection))
continue
else:
break
# Forces user to input conditions otherwise program won't know which rows to update
database_connection.update_rows(columns, new_data, conditions)
except FileNotFoundError:
print(
'Sorry, that file does not exist, halting operation now...'
)
else:
print(
f'Sorry, {user_choice} was not one of the options, halting operation now...'
)
time.sleep(1)
# Closes connection and cursor after processes have been completed
database_connection.close_connection()
# Offers the opportunity to run another process
loop = input(
'Would you like to do another operation? y for yes and anything else for no: '
)
if loop.lower() == 'y':
continue
else:
print('Closing program now...')
time.sleep(1)
break
def drop_table(table):
database_connection = DatabaseConnection(table)
database_connection.drop_table()
database_connection.close_connection()
def rename_table(table, new_table_name):
database_connection = DatabaseConnection(table)
database_connection.rename_table(new_table_name)
database_connection.close_connection()
def drop_rows(table, conditions):
database_connection = DatabaseConnection(table)
database_connection.delete_rows(conditions)
database_connection.close_connection()
def save_data(table, filepath):
database_connection = DatabaseConnection(table)
database_connection.save_table(filepath)
database_connection.close_connection()