Create a MySQL database that contains information about students in a university. Possible fields might include date of birth, major, current grade point average, credits earned, etc. Write a Py- thon program to manage the database. Include the following functionality: sort all students according to GPA (descending), create a display of all students in one particular major and remove all

Question

records from the database where the student has the required amount of credits to graduate.

What will be an ideal response?

Answer 1

```
# The program connects to the students database and
# executes various queries based on user input.

import MySQLdb

userInput = None

# available database operations
queries = \
[ "SELECT name, gpa FROM students ORDER BY gpa DESC;",
"SELECT name, major FROM students WHERE major = \"%s\"",
"DELETE FROM students WHERE credits_earned >= 20" ]

connection = MySQLdb.connect( db = "students" )
cursor = connection.cursor()

# prompt user to enter menu option
while userInput != "7":
print "1. Execute query sorting the students by gpa."
print "2. Print query sorting the students by gpa."
print "3. Execute query selecting students by major."
print "4. Print query selecting students by major."
print "5. Execute query removing graduated students."
print "6. Execute query removing graduated students."
print "7. Quit"

userInput = raw_input( "? " )
print

if userInput in [ "1", "3", "5" ]:

# select students by major
if userInput == "3":
major = raw_input( "what major? " )
majorQuery = queries[ 1 ] % major
cursor.execute( majorQuery )
else:

# sort students by GPA or remove graduating students
cursor.execute( queries[ int( userInput ) / 2 ] )

print cursor.fetchall()

# print requested query information
if userInput in [ "2", "4", "6" ]:
print queries[ int( userInput ) / 3 ]

print

cursor.close()
conection.close()
```
1. Execute query sorting the students by gpa.
2. Print query sorting the students by gpa.
3. Execute query selecting students by major.
4. Print query selecting students by major.
5. Execute query removing graduated students.
6. Execute query removing graduated students.
7. Quit
? 1
(('Jack Smith', 3.831), ('Joe Doe', 3.7869999999999999), ('Jane Doe',
3.53100000
00000001), ('Jamie Smith', 3.331))
1. Execute query sorting the students by gpa.
2. Print query sorting the students by gpa.
3. Execute query selecting students by major.
4. Print query selecting students by major.
5. Execute query removing graduated students.
6. Execute query removing graduated students.
7. Quit
? 3
what major? computer science
(('Joe Doe', 'Computer Science'),)
1. Execute query sorting the students by gpa.
2. Print query sorting the students by gpa.
3. Execute query selecting students by major.
4. Print query selecting students by major.
5. Execute query removing graduated students.
6. Execute query removing graduated students.
7. Quit
? 7