First, we will display the data to be modified by running the following Python to MySQL command:

import MySQLdb as mysql 
import Ch07_Code.GuiDBConfig as guiConf 

class MySQL(): 
    # class variable 
    GUIDB  = 'GuiDB' 
    #------------------------------------------------------ 
    def showData(self): 
        # connect to MySQL 
        conn, cursor = self.connect()    

        self.useGuiDB(cursor)       

        # execute command 
        cursor.execute("SELECT * FROM books") 
        print(cursor.fetchall()) 

        cursor.execute("SELECT * FROM quotations") 
        print(cursor.fetchall()) 

        # close cursor and connection 
        self.close(cursor, conn) 
#========================================================== 
if __name__ == '__main__':  
    # Create class instance 
    mySQL = MySQL() 
    mySQL.showData()

Running the code yields the following result:

GUI_MySQL_class.py

We might not agree with the Gang of Four, so let's change their famous programming quote.

We will do this by updating our database of favorite quotes. First, we retrieve the primary key value by searching for the book title and then we pass that value into our search for the quote:

    #------------------------------------------------------ 
    def updateGOF(self): 
        # connect to MySQL 
        conn, cursor = self.connect()    

        self.useGuiDB(cursor)       

        # execute command 
        cursor.execute("SELECT Book_ID FROM books WHERE Book_Title = 
                       'Design Patterns'") 
        primKey = cursor.fetchall()[0][0] 
        print("Primary key=" + str(primKey)) 

        cursor.execute("SELECT * FROM quotations WHERE Books_Book_ID = 
                       (%s)", (primKey,)) 
        print(cursor.fetchall()) 

        # close cursor and connection 
        self.close(cursor, conn)  
#========================================================== 
if __name__ == '__main__':  
    mySQL = MySQL()          # Create class instance 
    mySQL.updateGOF()

This gives us the following result:

GUI_MySQL_class.py

Now that we know the primary key of the quote, we can update the quote by executing the following commands:

    #------------------------------------------------------ 
    def showDataWithReturn(self): 
        # connect to MySQL 
        conn, cursor = self.connect()    

        self.useGuiDB(cursor)       

        # execute command 
        cursor.execute("SELECT Book_ID FROM books WHERE Book_Title = 
                       'Design Patterns'") 
        primKey = cursor.fetchall()[0][0] 
        print(primKey) 

        cursor.execute("SELECT * FROM quotations WHERE Books_Book_ID = 
                       (%s)", (primKey,)) 
        print(cursor.fetchall()) 

        cursor.execute("UPDATE quotations SET Quotation = 
                       (%s) WHERE Books_Book_ID = (%s)",  
                       ("Pythonic Duck Typing: If it walks like a duck and 
                       talks like a duck it probably is a duck...", 
                       primKey)) 

        # commit transaction 
        conn.commit () 

        cursor.execute("SELECT * FROM quotations WHERE Books_Book_ID = 
                       (%s)", (primKey,)) 
        print(cursor.fetchall()) 

        # close cursor and connection 
        self.close(cursor, conn) 

#========================================================== 
if __name__ == '__main__':  
    # Create class instance 
    mySQL = MySQL() 
    #------------------------ 
    mySQL.updateGOF() 
    book, quote = mySQL.showDataWithReturn()     
    print(book, quote)

By running the preceding code, we make this programming classic more Pythonic.

As can be seen in the following screenshot, before we ran the preceding code, our title with Book_ID 1 was related via a primary to foreign key relationship to the quotation in the Books_Book_ID column of the quotation table. This is the original quotation from the Design Patterns book.

We then updated the quotation related to this ID via the SQL UPDATE command.

None of the IDs have changed but the quotation that is now associated with Book_ID 1 has changed as can be seen in the second MySQL client window: