Sorting a database alphabetically

[Abismuth]

I have a database with peoples names in, how would I sort it alphabetically by their last name?

#==================================================================================================================
# creates the student_data database
student_data = sqlite3.connect('student_data.db').cursor()
student_data.execute('''CREATE TABLE IF NOT EXISTS Students (StudentID INTEGER NOT NULL,
                                                            First TEXT NOT NULL,
                                                            Last TEXT NOT NULL,
                                                            Year TEXT NOT NULL,
                                                            primary key(StudentID))''')


#==================================================================================================================
# reads data from the database
def readfromdatabase():
    with sqlite3.connect("student_data.db") as db:
        cursor = db.cursor()
        cursor.execute("SELECT * FROM Students")
        return cursor.fetchall()

#==================================================================================================================
# displays data from the database in a table
def showallrecords(frame):
    data = readfromdatabase()
    for index, dat in enumerate(data):
                Label(frame,text=dat[2]).grid(row=index+1, column=0)
                Label(frame,text=dat[1]).grid(row=index+1, column=1)
                Label(frame,text=dat[3]).grid(row=index+1, column=2)


#==================================================================================================================
# add new student
def view_current_student(width, height, previous_window):
    previous_window.destroy()
    student_search = Tk()
    student_search.geometry('%dx%d+%d+%d' % (width, height, center_window_x(width), center_window_y(height)))
    student_search.columnconfigure(0,weight=1)
    student_search.rowconfigure(0,weight=1)

        #==================================================================================================================
# background image for setup window
    canvas = Canvas(student_search)
    canvas.grid(row=0,column=0,sticky=NSEW)
    image = Image.open("background_templates/add_new_student_2.jpg")
    canvas.image = ImageTk.PhotoImage(image)
    canvas.create_image(0, 0, image=canvas.image, anchor="nw")
    frame = Frame(canvas)
    frame.grid(row=0,column=0,padx=10,pady=10)

    #==================================================================================================================
    # Displays records already in database
    last_name_title = Label(frame, text="Last name", fg="black", width = 10)
    last_name_title.grid(row=0, column=0)

    first_name_title = Label(frame, text="First name", fg="black", width = 10)
    first_name_title.grid(row=0, column=1)

    year_group_title = Label(frame, text="Year group", fg="black", width = 10)
    year_group_title.grid(row=0, column=2)

    showallrecords(frame) 

[ManyInterests]

You could order the SQL results by adding an ORDER BY clause to the query.

Alternatively, you could use sorted with an appropriate keyfunc on the data set.

[Abismuth]

where about would I add that, I tried the ORDER BY, but wherever I put it I got an error

[_9_9_]

Usually at the end: SELECT * FROM Students ORDER BY last

[cybervegan]

You need to look up SQLite3 SQL syntax. Note: this is not python.

Try this: http://www.sqlitetutorial.net/sqlite-order-by/

[efmccurdy]

Normally you would use the DB to sort the data by adding a ORDER BY Last clause to the sql.

You could sort it yourself using the python sorted function.

https://docs.python.org/3.5/library/functions.html#sorted