AND

Welldone!

You’vemadeitthroughtheforestofTkinter,pastthehauntedlakeofPmwandemerged(fairly)unscathedfromaduelwithTabnanny.Goodonya!

Now,itgetsREALLYinteresting

Obviously,you’veallreadthestudydesignforUnit4andyouwouldknowthattheSACforOutcome1isasfollows:

“Oncompletionofthisunitthestudentshouldbeabletoapplythestagesofsoftwaredevelopmenttoproducepurpose-designedsoftwarethattakesintoaccountanetworkedinformationsystemobjectiveandtheneedsofend-users.”

AfewkeywordsherethatIhavehighlightedforyou:

Firstly–Youhavetodesignyourownsoftware(justlikeinUnit3)usingPythontomeetanobjective

Secondly–Weneedtouseanetworkedinformationsystem–sothatanumberofpeoplecanusethesystemsimultaneously.RememberyourSACinUnit3?Well,reallyonlyoneuseratatimecoulduseit–NOGOODforanetworkedsystem

Thirdly–weneedtomeettheneedsoftheusersofaparticularscenario

So,whatwe’regoingtodoiscreateaGUI(GraphicalUserInterface)usingTkinter,toallowustoenterandretrievedatathatissavedinaMySQLdatabase.

I’mnotgoingtogointoagreatdealaboutwhatMySQLis,howitworksetc.,shortofsayingthatitsFREE,youcandownloaditathomeifyouwant,it’sadatabaseanditsprettycommonthroughouttheworld.Therearelotsofvariationsonit(PostGreSQL,MSSQLamongstothers)

Ifyouwantmoreinformation–goto

andthere’sloadsofinformation

Onwiththeshow…

Chapter1–GettingMySQLinstalledandsorted

Thankfully,MySQLisalreadyinstalledandreadytogoonour(your)serverthatyouaregoingtobeplayingwith,sononeedtoworryaboutthat.Ifyouwanttosetitupathome,goto

,downloadtheversionthatyouneedandfollowtheinstructions–deadeasy.IfyouDONOThaveaccesstotheinternet,oryouareondialup,seemeforacopyofMySQLthatyoucanhaveonyourflashdrive.

YouwillalsoneedtodownloadtheMySQLbindingsthatallowPythontotalktoMySQL–thesearealreadydownloadedforus,sononeedtoworry,butifyouwanttoinstalltheseathome,goto:

/project/showfiles.php?group_id=22307

anddownloadthelatestreleaseversion(attimeofwritingitwas1.2.2)orseemewithyourflashdrive.Youjustrunthe.exefile–deadeasy!

Ok,sotogetaccesstoMySQLontheserver,youneedtotypein

Athome,youneedtogotoSTART>PROGRAMS>MySQL>MySQLServer5.0>MySQLCommandLineClientandinputdetailsstraightfromthere.

Chapter2–SomeSQLqueries

Youshouldnowbefacedwithablackwindow(calledtheCOMMANDLINECLIENT)whereyouinputdata.Itwilllooksomethinglikethis.

YourPASSWORDisxxxxxx

Nowthescreenwilllooklikethis

Allyourcommandstocreateyourdatabasetableandsetfieldsizes,namesandthesuchlikewillbedonehere

Typehelpafterthemysql>cursor

Youwillhavealistofcommandsthatyoucanuse.AtthelevelweareusingMySQL,wewon’tbeusingmanyofthese,withtheexceptionofUSEandEXIT.

NOTICEhowthemysql>cursorisback–thatmeansyoucantypesomethinginstraightaway.

NowtypeinSELECTVERSION(),CURRENT_DATE;

YoushouldhaveatabletellingyoutheversionofMySQLthatyouareusingandthecurrentdate

MySQLisnottoobotheredaboutcase,soyoucouldtypein:

selectversion(),current_date;

Nowtry:

SELECTUSER();

Theresultofthisisgoingtobeimportant,somakeanoteofit.WhenItypethisinIgetthisresult:

NOTICEthatItypedinselectuser()andpressedEnter,thentypedin;togettheresult.AllqueriesinMySQLneedtoendwithasemicolon(;).Ifyoudon’tputitin,thennothingwillhappenandyouwillneedtoenteritatthenextcursor.

NOTICEthattheresultofthisisroot@localhost

rootismyUSER

localhostismyhost.

Iwillneedtousetheseinmyscriptlateron,sokeepanoteoftheresultyouget.

Chapter3–Creatingadatabase

Let’ssaywewanttocreateadatabaseofourpetsandtheirdetails.

Firstthingwedoisseewhatdatabasesalreadyexistonourserver,sothatwewon’ttakeanamethatalreadyexists.

TypeSHOWDATABASES;

Youshouldgetthis(above),WITHOUTtheonecalledhours(that’smytestdatabase)

Clearly,youwillnotcallyourdatabaseinformation_schema,mysqlortest.

AlsoNOTICEthatadatabaseshouldbeONEWORDorjoinedbyanunderscore(orsimilar)

LetscallourdatabasePETSRT(wherethenameisPETSwithmyinitialsafterwards)butUSEYOUROWNINITIALS!

TypeinCREATEDATABASEPETSRT;

ThentypeUSEPETSRT;

NowweneedtocreateaTABLEinthatdatabase.Reallyadvanceddatabasescanhavemorethanonetable(askanyonedoingITApps!),butwe’llstickwithonetablefortoday.

TypeinCREATETABLEpets(nameCHAR(200),ageINT(5),sexCHAR(1),speciesCHAR(100),breedCHAR(30);

ThiswillCREATEatablecalledpets.Therewillbe5fields,eachofvaryinglengths(chosendependingonwhatisgoinginthefields)

TheotherchoicesofDATASTRUCTUREarebelow

Don’taskmewhattheyallmean,butthecommononesareCHAR,INT,DATETIME,FLOAT,DATE,TIMESTAMPandYEAR,andthat’smorethanenoughtobegoingonwith!

NowtypeinEXPLAINpets;

ThiswillbringupasummaryofyourtableandthetypeofDATASTRUCTURESthatyouhave:

ThescreenshotaboveiswhenIgottheresultformytestdatabasecalledhours.

NOTICEthatyoutypeinEXPLAIN<tablename>NOTEXPLAIN<databasename>.Acommonerror…

Nowyouneedtoensurethatyouhaveaccesstoread/writetoyourSQLdatabase,BEFOREwestartcoding.

GRANTALLON*.*TOROOT@LOCALHOSTIDENTIFIEDBY“PASSWORD”;

Someexplanation:

WeneedtoGRANTaccesstotheusernameandhostnamethatwefoundoutearlier,soreplaceROOT@LOCALHOSTwithwhateveryougotfromCHAPTER2.

ReplacePASSWORDwithapasswordthatyouwanttouse.This‘stops’othersfromaccessingyourtable(unlesstheyseeyourcode)

NowtypeSELECT*FROMPETS

Itwill(hopefully)reportbackthatyouhavean‘EmptySet’whichmeansthatthereareNORECORDSinthattable.Whichtherearen’t.‘Coswehaven’tputanyinyet.

REMEMBERthatadatabaseissplitintoTABLES.WeneedtoUSEdatabase_nameandthenoperateontable_name

InsertingData

WewillbeinsertingdataviaourPythonscript,butifyouwanttoseethatyourdatabaseworks…

TypeinINSERTINTOpetsVALUES(‘Puffball’,‘12’,‘F’,‘Dog’,‘Rottweiller’);

Thiswillinsertthesevaluesintheorderofthefields(NamethenAgethenSexthenSpeciesthenBreed).Ifyouwantafieldblank,thentypeNULL.

NowtypeinSELECT*FROMpets

Andyourfirstpetshouldbeinthere!

IfyouhadLOTSofdatatoinsertfromExcelforexample,youcansaveitasa.csvfile(commaseparatedvalues)andloaditasabatch,ratherthantypingtheminsingly)

ThisbringsupapointofPLANNINGYOURDATABASE.

YouMUST,MUST,MUSTmakesurethatyourdatabasehasthecorrectfields,fieldlength,datastructureandorderoffieldsBEFOREyoustarttypingitintoMySQL.

Planitonpaperfirst,askotherpeoplewhattheythinkandTHINKABOUTYOURFINALSOLUTION–whoaretheusersandwhatdotheywant?

Youcanalteryourfieldsifyoumakeamistake(clickhere),butit’saLOTlesshassletogetitrightstraightaway

Chapter4–SortingourGUIout

Right,nowthatthebonesofourdatabaseissorted,weneedtocreateaprogramtouseourSQLdatabase

Createatkinterprogramthatisstructuredlikethis.Includeabuttonthatsavesthedata(don’tcreatethedeffunction,justwritepassinthecode).Yourdeletebuttonshoulddeletethecontentsofalltheentryboxes.Yourquitbuttonshoulddestroytherootwindow.

Deadeasy!–whaddyareckon–10minutes?

YoucanputthebuttoninthatsaysIGNOREME,justdon’tcreateadefforit(usepass)

Don’tpanicifyoucantrememberhowtodothisstuff–youhaven’treallydonethebasicssinceU3O2.

Usethetutorialsandyourpreviousprogramstohelpyou!

LayoutyourGUIhowyouwant–remembertomakeitattractive,colourfulandwellplanned.

Use.grid,buttonpicturesanddifferentfonts.

Beginyourscriptwiththis:

importMySQLdb

forfairlyobviousreasonsandthencontinueasnormal.

Chapter4–ConnectingourGUItoMySQL

Thisisthetrick,anditsNOTevendifficult!

ThisisthedeffunctionformySAVEbutton.

defbut1():

name=E1.get()

hours=E2.get()

rate=E3.get()

wage=E4.get()#'Gets'thedatafromEntryboxesE1,E2,E3,E4

myquery="INSERTINTOhours_worked(name,hours,rate,wage)VALUES(\'"+name+"\',\'"+hours+"\',\'"+rate+"\',\'"+wage+"\')"

db=MySQLdb.connect(host='localhost',user='root',passwd='hamish',db='hours')#Creates'myquery'andinsertstheresultsofthisintoapre-createdMySQLtable

curs=db.cursor()#Createsacursortomakechangesinthetable

curs.execute(myquery)#Thisexecutesthe'myquery'

curs.close()

mit()#thisistheCRUCIALlinethatneedstobeheretoCOMMITthechangestothedatabase

db.close()#thisclosesthedatabasetoanymorechanges

Thebitshighlightedinbluearetheonesyouneedtobeawareof.Thebitshighlightedinyellowaremyinternalcomments

Theotherbitsarefairlyobvious.

myqueryisthenamewe’regoingtogiveforthequery(eachqueryneedsadifferentname)

INSERTINTOhours_workedVALUESisanSQLcommand–INSERTINTOtellsPythontoINSERTthedataINTOatable(inmycasecalledhours_worked,inyourcaseitwouldbe…

(\'"+name+"\',\'"+hours+"\',\'"+rate+"\',\'"+wage+"\')"–ThesearethenamesofthefieldsinyourSQLdatabase.TheyneedtobeTHESAME.Exactly.Soyourswouldbename,age,sexetc.Pythongetsthevaluesforname,ageetc.fromthebitatthetopofthedeffunction(name=E1.get()etc.).ReplaceE1.get()withthenameofyourEntryboxesandreplacename,hoursetc.withthenamesofyourfields.

db=MySQLdb.connect(host='localhost',user='root',passwd='hamish',db='hours')–thisisprettyobviousstuff–replacewithyourdetails

TherestofthecodeinblueisexplainedintheINTERNALCOMMENTS.

Eachlineiscrucial–makesureyouhavethemallandinthatorder.

Theonlybityoucanalteristhe

curs.execute(myquery),whereyouwouldchangemyquerytosomethingelse,ifyouchangeditfurtherupinthecode

Whew!

Chapter4–Checkingitsallthere

Right,ourGUIissendingdatatotheSQLtableinourdatabase.Butweneedtocheckitsthere,bothinSQLformandalsothroughourGUI–rememberthatwedon’twantourclienthavingtogothroughtheSQLcommandlineinterfaceallthetime.Firstthingsfirst…

GototheMySQLcommandlineinterface,aswedidinChapter2

Typeinyourpassword

TypeSHOWdatabases;

TypeUSE<your_database_name>;

TypeSELECT*FROM<your_table_name>;

Hopefully(really,reallyhopefully),youshouldhavearecordinyourtablethattellsusallaboutPuffballtherottweiller!

IwouldadviseyoutocheckyourSQLtableatregulartimes,justtomakesurethatallisok–keepthecommandlineinterfaceopenwhenyou’retkinter-ing,andcheckwhenrequired.

Ok,sothat’showwecheckthroughSQL,butwhataboutcheckingthecontentsthroughourGUI…

We’regoingtousealistboxtoupdatetherecordsfromtheSQLtabletoourGUI,herewego…

STEP1–InsertaLISTBOX

Youshouldrememberhowtodothis,ifnot…

b5=Button(root,text="AddtoListBox",command=but5)

b5.pack()

list=Listbox(root)

list.pack(expand=1,fill=BOTH)

Insertthisunderallyourbuttonsandentryboxes

STEP2–Createacursorforthistask

defget_cursor_for_pets_table():

db=MySQLdb.connect(host='localhost',user='root',passwd='hamish',db='pets')

curs=db.cursor()

returncurs

Putthisinyourdefssomewhere.Remembertochangehost,user,passwordanddbnametoYOURdetails!

STEP3–CollectallthedatafromthetableinthedatabasebyusingtheFETCHALLcommand,closethecursordownand‘return’ourResults

defget_all_entries():

curs=get_cursor_for_pets_table()#Thepreviousdefinition

myquery="SELECT*FROMpets"#Thenameofourquery

curs.execute(myquery)#Executesthe‘myquery’

Results=curs.fetchall()#Fetchesallthedata

curs.close()

returnResults#Returnstheresults

Putthisunderyourpreviousdefinition

STEP4–PutallthisdataintoourLISTBOX

defbut5():

results=get_all_entries()#‘results’isthepreviousdef

list.delete(0,END)

forresultinresults:

printresult

name=result[0]

age=result[1]

sex=result[2]

species=result[3]

breed=result[4]

list.insert(END,name+""+age+""+sex+""+species+““+breed)#printallresultsinthelistbox

The““justputspacesinbetweenthevariables–ifyouwantedto,youcouldincludewritinghere,suchas“Name=”or“Sex=”etc.tomakeyourlistboxabitclearer.

STEP5–InitiatetheListbox…

list=Listbox()

list.pack(expand=1,fill=BOTH)

root.mainloop()

Done!

Chapter5–SomefunkyGUI/MySQLstuff

Timestamps

WhatifyouwantedtoknowWHENarecordwaslastinserted?Youcouldeitherasktheusertoinsertthedate(andgivethefieldaDATETIMEdatastructure)whichwouldinvolvealotoftypingfromtheuserORgetSQLtoautomaticallyrecordthedate&time.IknowwhichI’dratherdo…

Thisiseasy…

WhenyoucreateyourtableinMySQL,justcreateafieldcalleddate/date_enteredorsomethinglikethatandgiveitthedatastructuretitleTIMESTAMP,likebelow…

createtablehours_worked(nameCHAR(255),hoursCHAR(255),rateCHAR(255),date_enteredTIMESTAMP)

Eachtimearecordiscreated,itautomaticallyinsertsatime/datestamp–handy…

Deletingarecord

Tobeadded

Addingascrollbar

WhatifyouhavesomanyentriesinyourListboxthatyoucantseethemall?Youneedtogetyourselfascrollbar!

ThiscodegoesintothebitthatinitiatedourListbox(seeChapter4)

scrollbar=Scrollbar()

scrollbar.pack(side=RIGHT,fill=Y)

list=Listbox(root,yscrollcommand=scrollbar.set)

list.pack(expand=1,fill=BOTH)

scrollbar.config(command=list.yview)

The‘yview’isascrollbarthatgoesvertically.Youcanalsohave‘xview’whichisascrollbarthatgoeshorizontally–youprobablywontneedthis…

Chapter6–Alteringyourtables

Addingafield

Let’ssayyouhavecreatedyourtablebutforgottentocreatetheNAMEfield.Crisis!

Neverfear,asolutionisathand,withtheALTERTABLEcommand…

Typein: ALTERTABLE<tablename>(noteNOsemicolon)

ADD<fieldname><fieldtype>AFTER<existingfieldname>;

Sothiswouldlooklike:

ALTERTABLEpets

ADDfoodCHAR(30)AFTERsex

Or,whatifyouw