MENU Options in Python Console Interface

Database MySQL (Use python)

Create a table and insert the data from the CSV file

planets.csv (CSV file contains):

name,rotation_period,orbital_period,diameter,climate,gravity,terrain,surface_water,population Alderaan,24,364,12500,temperate,1 standard,"grasslands, mountains",40,2000000000 Yavin IV,24,4818,10200,"temperate, tropical",1 standard,"jungle, rainforests",8,1000 Hoth,23,549,7200,frozen,1.1 standard,"tundra, ice caves, mountain ranges",100,NA Dagobah,23,341,8900,murky,N/A,"swamp, jungles",8,NA Bespin,12,5110,118000,temperate,"1.5 (surface), 1 standard (Cloud City)",gas giant,0,6000000 Endor,18,402,4900,temperate,0.85 standard,"forests, mountains, lakes",8,30000000 Naboo,26,312,12120,temperate,1 standard,"grassy hills, swamps, forests, mountains",12,4500000000 Coruscant,24,368,12240,temperate,1 standard,"cityscape, mountains",NA,1000000000000 Kamino,27,463,19720,temperate,1 standard,ocean,100,1000000000 Geonosis,30,256,11370,"temperate, arid",0.9 standard,"rock, desert, mountain, barren",5,100000000000 Utapau,27,351,12900,"temperate, arid, windy",1 standard,"scrublands, savanna, canyons, sinkholes",0.9,95000000 Mustafar,36,412,4200,hot,1 standard,"volcanoes, lava rivers, mountains, caves",0,20000 Kashyyyk,26,381,12765,tropical,1 standard,"jungle, forests, lakes, rivers",60,45000000 Polis Massa,24,590,0,artificial temperate ,0.56 standard,airless asteroid,0,1000000 Mygeeto,12,167,10088,frigid,1 standard,"glaciers, mountains, ice canyons",NA,19000000 Felucia,34,231,9100,"hot, humid",0.75 standard,fungus forests,NA,8500000 Cato Neimoidia,25,278,0,"temperate, moist",1 standard,"mountains, fields, forests, rock arches",NA,10000000 Saleucami,26,392,14920,hot,NA,"caves, desert, mountains, volcanoes",NA,1400000000 Stewjon,NA,NA,0,temperate,1 standard,grass,NA,NA Eriadu,24,360,13490,polluted,1 standard,cityscape,NA,22000000000 Corellia,25,329,11000,temperate,1 standard,"plains, urban, hills, forests",70,3000000000 Rodia,29,305,7549,hot,1 standard,"jungles, oceans, urban, swamps",60,1300000000 Nal Hutta,87,413,12150,temperate,1 standard,"urban, oceans, swamps, bogs",NA,7000000000 Dantooine,25,378,9830,temperate,1 standard,"oceans, savannas, mountains, grasslands",NA,1000 Bestine IV,26,680,6400,temperate,NA,"rocky islands, oceans",98,62000000 Ord Mantell,26,334,14050,temperate,1 standard,"plains, seas, mesas",10,4000000000 NA,0,0,0,NA,NA,NA,NA,NA Trandosha,25,371,0,arid,0.62 standard,"mountains, seas, grasslands, deserts",NA,42000000 Socorro,20,326,0,arid,1 standard,"deserts, mountains",NA,300000000 Mon Cala,21,398,11030,temperate,1,"oceans, reefs, islands",100,27000000000 Chandrila,20,368,13500,temperate,1,"plains, forests",40,1200000000 Sullust,20,263,12780,superheated,1,"mountains, volcanoes, rocky deserts",5,18500000000 Toydaria,21,184,7900,temperate,1,"swamps, lakes",NA,11000000 Malastare,26,201,18880,"arid, temperate, tropical",1.56,"swamps, deserts, jungles, mountains",NA,2000000000 Dathomir,24,491,10480,temperate,0.9,"forests, deserts, savannas",NA,5200 Ryloth,30,305,10600,"temperate, arid, subartic",1,"mountains, valleys, deserts, tundra",5,1500000000 Aleen Minor,NA,NA,NA,NA,NA,NA,NA,NA Vulpter,22,391,14900,"temperate, artic",1,"urban, barren",NA,421000000 Troiken,NA,NA,NA,NA,NA,"desert, tundra, rainforests, mountains",NA,NA Tund,48,1770,12190,NA,NA,"barren, ash",NA,0 Haruun Kal,25,383,10120,temperate,0.98,"toxic cloudsea, plateaus, volcanoes",NA,705300 Cerea,27,386,NA,temperate,1,verdant,20,450000000 Glee Anselm,33,206,15600,"tropical, temperate",1,"lakes, islands, swamps, seas",80,500000000 Iridonia,29,413,NA,NA,NA,"rocky canyons, acid pools",NA,NA Tholoth,NA,NA,NA,NA,NA,NA,NA,NA Iktotch,22,481,NA,"arid, rocky, windy",1,rocky,NA,NA Quermia,NA,NA,NA,NA,NA,NA,NA,NA Dorin,22,409,13400,temperate,1,NA,NA,NA Champala,27,318,NA,temperate,1,"oceans, rainforests, plateaus",NA,3500000000 Mirial,NA,NA,NA,NA,NA,deserts,NA,NA Serenno,NA,NA,NA,NA,NA,"rainforests, rivers, mountains",NA,NA Concord Dawn,NA,NA,NA,NA,NA,"jungles, forests, deserts",NA,NA Zolan,NA,NA,NA,NA,NA,NA,NA,NA Ojom,NA,NA,NA,frigid,NA,"oceans, glaciers",100,500000000 Skako,27,384,NA,temperate,1,"urban, vines",NA,500000000000 Muunilinst,28,412,13800,temperate,1,"plains, forests, hills, mountains",25,5000000000 Shili,NA,NA,NA,temperate,1,"cities, savannahs, seas, plains",NA,NA Kalee,23,378,13850,"arid, temperate, tropical",1,"rainforests, cliffs, canyons, seas",NA,4000000000 Umbara,NA,NA,NA,NA,NA,NA,NA,NA Tatooine,23,304,10465,arid,1 standard,desert,1,200000 Jakku,NA,NA,NA,NA,NA,deserts,NA,NA

Tasks 1. Parse the files and import the data in the database In the moodle you will find a zip file with some csv files in it. Your task is to parse the files and insert them into the created database. Your first steps should be to analyze the files and see how they are related to each other in order to design a database schema for the given data. Give it time, as a wrong design might create issues with solving the later tasks of this assignment. Several important implementation details must be followed at this point: 1. You will need to use the Connector/Python for this assignment. This is mandatory! You can use the following command to do so: (mandatory) a. shell pip install mysql-connector-python 2. Your code should check if the database with your last name exists. If you are working alone it should be named Lastname. If you are doing the assignment in a group it should have both Lastname 1_Lastname2. (10 points) 3. In case the database exists, the program should ask the user for input i.e. show the main menu (explained in the next Task). If the database does not exist, it should be created using the instructions in Step 1, create the appropriate tables and insert the data from the csv files. (10 points) 4. At the top of your python file (after the import statements), you should include the connection details. For example: (mandatory) a. cnx = mysql.connector connect(user-'root' password=root host-127.0.0.1') DB_NAME 'jusufi 5. Do not forget to comment on your code. (10 points) 2. Queries After the database has been created and the data inserted, the program should show an interactive prompt. Users should see a main menu as the one displayed in the picture. Each number should perform a certain action and run appropriate queries as described below 1. List all planets. After pressing number 1 followed by enter the program should list the names of all the planets. Users will need to click any key to return to the main menu. (10 points) 2. Search for planet details. After pressing number 2 followed by enter the program should ask the user to enter the name of the planet. After the user enters the data and presses enter all the details of the planet should be displayed. Users will need to click any key to return to the main menu. Users will need to click any key to return to the main menu. (10 points) 3. Search for species with height higher than given number. After pressing number 3 followed by enter the program should ask the user to enter the average height of the species. The program should list all the species fulfilling the given condition. Users will need to click any key to return to the main menu. (10 points) 4. What is the most likely desired climate of the given species? After pressing number 4 followed by enter the program should ask the user to enter the name of the species. After the user enters the data and presses enter the question should be answered. (20 points) 5. What the average lifespan per species classification ? After pressing number 5 followed by enter the program should list the names of species classification and their average lifespan. Users will need to click any key to return to the main menu. 20 points) 1. List all planets. 2. Search for planet details. 3. Search for species with height higher than given number. 4. What is the most likely desired climate of the given species? 5. What is the average lifespan per species classification? Q. Quit Please choose one option