· 4 years ago · Jul 20, 2021, 04:50 AM
1import csv
2import mysql.connector
3from mysql.connector import errorcode
4from os import system, name
5
6# Connection details
7cnx = mysql.connector.connect(
8 host = 'localhost',
9 user = 'root',
10 password = 'root',
11 )
12DB_NAME = 'Chowdhurry_Bataa'
13cursor = cnx.cursor()
14
15# Using provided database name new database is Born into this world.
16def create_database(cursor, DB_NAME):
17 try:
18 cursor.execute("CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
19 cursor.execute('SET GLOBAL sql_mode =""')
20 cursor.execute('SET SESSION sql_mode =""')
21 except mysql.connector.Error as err:
22 print("Failed creating database: {}".format(err))
23 exit(1)
24
25# Newly born database acquires its first table...A 'planets'.
26def create_table_planets(cursor):
27 create_planets = "CREATE TABLE `planets` ("\
28 " `name` varchar(15),"\
29 " `rotation_period` int,"\
30 " `orbital_period` int,"\
31 " `diameter` int,"\
32 " `climate` varchar(30),"\
33 " `gravity` varchar(20),"\
34 " `terrain` varchar(45),"\
35 " `surface_water` int,"\
36 " `population` int" \
37 ") ENGINE=InnoDB"
38 try:
39 print("Creating table planets: ")
40 cursor.execute(create_planets)
41 except mysql.connector.Error as err:
42 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
43 print("already exists.")
44 else:
45 print(err.msg)
46 else:
47 print("OK")
48
49# Secondly A 'species' table.
50def create_table_species(cursor):
51 create_species = "CREATE TABLE `species` (" \
52 " `name` varchar(15)," \
53 " `classification` varchar(14)," \
54 " `designation` varchar(14)," \
55 " `average_height` int," \
56 " `skin_colors` varchar(25)," \
57 " `hair_colors` varchar(14)," \
58 " `eye_colors` varchar(24)," \
59 " `average_lifespan` int," \
60 " `language` varchar(20)," \
61 " `homeworld` varchar(20)" \
62 ") ENGINE=InnoDB"
63 try:
64 print("Creating table species: ")
65 cursor.execute(create_species)
66 except mysql.connector.Error as err:
67 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
68 print("already exists.")
69 else:
70 print(err.msg)
71 else:
72 print("OK")
73
74# In case of abnormal null values.
75def na_to_null(row):
76 value = 'None'
77 for i in range(len(row)):
78 if(row[i] == "NA" or row[i] == "N/A"):
79 row[i] = value
80
81# Populating 'planets' table.
82def insert_into_planets(cursor):
83 print("------------------PLANETS------------------")
84 try:
85 with open('planets.csv') as csv_file:
86 reader = csv.reader(csv_file)
87 cnt = 0
88 for row in reader:
89 if cnt == 0:
90 cnt += 1
91 else:
92 na_to_null(row)
93 cursor.execute(f"INSERT INTO planets(\
94 name,\
95 rotation_period,\
96 orbital_period,\
97 diameter,\
98 climate,\
99 gravity,\
100 terrain,\
101 surface_water,\
102 population)\
103 VALUES\
104 {row[0] , row[1] , row[2] , row[3] , row[4] , row[5] , row[6] , row[7] , row[8]};")
105 except mysql.connector.Error as err:
106 print(err.msg)
107 else:
108 # Make sure data is committed to the database
109 cnx.commit()
110 print("OK")
111
112# Populating 'species' table.
113def insert_into_species(cursor):
114 print("------------------SPECIES------------------")
115 try:
116 with open('species.csv') as csv_file:
117 reader = csv.reader(csv_file)
118 cnt = 0
119 for row in reader:
120 na_to_null(row)
121 if cnt == 0:
122 cnt += 1
123 else:
124 cursor.execute(f"INSERT INTO SPECIES (\
125 name,\
126 classification,\
127 designation,\
128 average_height,\
129 skin_colors,\
130 hair_colors,\
131 eye_colors,\
132 average_lifespan,\
133 language,\
134 homeworld)\
135 VALUES\
136 {row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9]};")
137 except mysql.connector.Error as err:
138 print(err.msg)
139 else:
140 # Make sure data is committed to the database
141 cnx.commit()
142 print("OK")
143
144# Clear terminal page
145def clear():
146 if name == 'nt':
147 _ = system('cls')
148 else:
149 _ = system('clear')
150
151# Main menu
152def menu(cursor):
153 menu = 0
154 while(menu != 6):
155 print("\t 1. List all planets.\n\
156 2. Search for planet details.\n\
157 3. Search for species with height higher than given number.\n\
158 4. What is the most likely desired climate of the given species?\n\
159 5. What is the average lifespan per species classification?\n\
160 6. Quit.\n")
161 menu = int(input("Menu(1-6): "))
162
163 if(menu == 1):
164 query = "SELECT name FROM planets"
165 elif(menu == 2):
166 planeName = str(input("\t Planet name: "))
167 query = f"SELECT * from planets WHERE name = '{planeName}'"
168 elif(menu == 3):
169 testHeight = int(input("\t Standard height: "))
170 query = f"SELECT name from species WHERE average_height > '{testHeight}'"
171 elif(menu == 4):
172 specieName = input("\t Specie name: ")
173 query = f"SELECT climate from planets WHERE name = (SELECT homeworld from species WHERE name = '{specieName}')"
174 elif(menu == 5):
175 query = "SELECT classification, avg(average_lifespan) as Average from species GROUP BY classification"
176 elif(menu == 6):
177 print("Closing connection...")
178 cursor.close()
179 print("Have a nice day!")
180 continue
181 else:
182 print("Please choose from the provided options.")
183 continue
184
185 # Clear window before showing result.
186 clear()
187 print_results(query, menu, cursor)
188
189 more = input("\t<<< Press 'Enter' key to go back <<<")
190 clear()
191
192# Once correct query for chosen task is selected this fucntion prints the results.
193def print_results(query, number, cursor):
194 cursor.execute(query)
195 if(number == 2):
196 for tupl in cursor:
197 print(f"\t Rotation period: \t{tupl[1]}\n\
198 Orbital period: \t{tupl[2]}\n\
199 Diameter: \t{tupl[3]}\n\
200 Climate: \t{tupl[4]}\n\
201 Gravity: \t{tupl[5]}\n\
202 Terrain: \t{tupl[6]}\n\
203 Surface water: \t{tupl[7]}\n\
204 Population: \t{tupl[8]}")
205 elif(number == 5):
206 for (classification, Average) in cursor:
207 print(f"\t Classification: {classification}\t\tAverage lifespan: {Average}")
208 else:
209 cnt = 0
210 for tupl in cursor:
211 cnt += 1
212 for info in tupl:
213 print(f"\t {cnt}) {info}")
214
215# Main
216try:
217 cursor.execute("USE {}".format(DB_NAME))
218 clear()
219 menu(cursor)
220
221except mysql.connector.Error as err:
222 print("Database {} does not exists.".format(DB_NAME))
223 if err.errno == errorcode.ER_BAD_DB_ERROR:
224 create_database(cursor, DB_NAME)
225 print("Database {} created successfully.".format(DB_NAME))
226 cnx.database = DB_NAME
227 create_table_planets(cursor)
228 create_table_species(cursor)
229 print("Tables created successfully.\n")
230 insert_into_planets(cursor)
231 insert_into_species(cursor)
232 else:
233 print(err)