jJMBH0GS

1import mysql.connector
2db_connection = mysql.connector.connect(
3	host= "localhost",
4	user= "root",
5	passwd= ""
6	)
7
8db_cursor = db_connection.cursor()
9
10decision = input('What would you like to do? (create_db, create_tbl, insert_data, get_data, delete_tbl, alter_tbl, drop_tbl, drop_db): ')
11
12if decision == 'create_db':
13	db_name = input('Please provide new Database name: ')
14	db_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name};")
15	db_cursor.execute("SHOW DATABASES;")
16
17	for db in db_cursor:
18		print(db)
19
20elif decision == 'create_tbl':
21	#db_name = input('Please provide name of the Database where you would like to create a table: ')
22	table_name = input('Please provide name of the Table that you would like to create: ')
23	col_num = int(input('Please provide number of columns: '))
24	db_cursor.execute(f"CREATE TABLE IF NOT EXISTS db_project.{table_name} (ID INT AUTO_INCREMENT PRIMARY KEY);")
25	for i in range(col_num):
26		column_name = input('Please provide name of the column: ')
27		column_type = input('Please provide column  data type: ')
28		db_cursor.execute(f"ALTER TABLE db_project.{table_name} ADD {column_name} {column_type};")
29
30elif decision == 'insert_data':
31	db_cursor.execute("INSERT INTO db_project.test(`ID`, `aa`, `bb`, `cc`) VALUES (NULL,'aaaa','bbbb','cccc');")
32	db_connection.commit()
33
34elif decision == 'get_data':
35	table_name = input('Please provide name of the table: ')
36	is_sorted = input('Would you like your data to be sorted? (Y/N): ')
37	if is_sorted == 'Y':
38		db_cursor.execute(f'SHOW COLUMNS FROM db_project.{table_name};')
39		column_name = input('Please select name of the column you want to order by: ')
40		db_cursor.execute(f'SELECT * FROM db_project.{table_name} ORDER BY {column_name} ASC;')
41	elif is_sorted == 'N':
42		db_cursor.execute(f'SELECT * FROM db_project.{table_name};')
43
44elif decision == 'delete_tbl':
45	table_name = input('Please provide name of the table you would like to delete: ')
46	db_cursor.execute(f'DELETE * FROM TABLE db_project.{table_name};')
47	db_cursor.execute(f'SHOW TABLES;')
48
49elif decision == 'alter_tbl':
50	table_name = input('Please provide name of the table you would like to alter: ')
51	alter_operation = input('Please provide alter operation you would like to perform (ADD, DROP, MODIFY): ')
52	if alter_operation == 'ADD':
53		column_name = input('Please provide name of the column to add: ')
54		column_type = input('Please provide column data type: ')
55		db_cursor.execute(f"ALTER TABLE db_project.{table_name} ADD {column_name} {column_type};")
56	elif alter_operation == 'DROP':
57		column_name = input('Please provide name of the column to drop: ')
58		db_cursor.execute(f"ALTER TABLE db_project.{table_name} DROP COLUMN {column_name};")
59	elif alter_operation == 'MODIFY':
60		column_name = input('Please provide name of the column to modify: ')
61		column_type = input('Please provide column data type: ')
62		db_cursor.execute(f"ALTER TABLE db_project.{table_name} MODIFY COLUMN {column_name} {column_type};")
63
64elif decision == 'drop_tbl':
65	table_name = input('Please provide name of the table you would like to drop: ')
66	db_cursor.execute(f"DROP TABLE db_project.{table_name};")
67
68elif decision == 'drop_db':
69	db_name = input('Please provide name of the Database you would like to drop: ')
70	db_cursor.execute(f"DROP DATABASE IF EXISTS {db_name};")
71	db_cursor.execute("SHOW DATABASES;")
72
73else:
74	print('Ooops, something went wrong :(')