Interface python with sql database.pdf--

cs project

1. Chapter 11 :
Informatics
Practices
Class XII ( As per
CBSE Board)
Interface python
with SQL
Database And
SQL commands
Visit : python.mykvs.in for regular updates
New
Syllabus
2019-20

2. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Adatabaseisnothingbutanorganizedcollectionofdata.Dataisorganizedintorows,columnsand
tablesanditisindexedtomakeiteasiertofindrelevantinformation.Allcompanieswhetherlargeor
smallusedatabases.Soitbecomenecessarytodevelopproject/softwareusinganyprogramming
languagelikepythoninsuchamannerwhichcaninterfacewithsuchdatabaseswhichsupport
SQL.GeneralisedformofInterfaceofpythonwithSQLDatabasecanbeunderstoodwiththehelpof
thisdiagram.
Form/anyuserinterfacedesignedinanyprogramminglanguageisFrontEndwhereasdatagiven
bydatabaseasresponseisknownasBack-Enddatabase.
SQLisjustaquerylanguage,itisnotadatabase.ToperformSQLqueries,weneedtoinstallany
databaseforexampleOracle,MySQL,MongoDB,PostGresSQL,SQLServer,DB2etc.
UsingSQLinanyofthedbms,databasesandtablecanbecreatedanddatacanbeaccessed,
updatedandmaintained.ThePythonstandardfordatabaseinterfacesisthePythonDB-API.Python
DatabaseAPIsupportsawiderangeofdatabaseservers,likemsql,mysql,postgressql,Informix,
oracle,Sybaseetc.

3. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
WhychoosePythonfordatabaseprogramming
Followingarethereasontochoosepythonfordatabase
programming
•Programmingmoreefficientandfastercomparedtoother
languages.
•Portabilityofpythonprograms.
•Supportplatformindependentprogramdevelopment.
•PythonsupportsSQLcursors.
•Pythonitselftakecareofopenandcloseofconnections.
•Pythonsupportsrelationaldatabasesystems.
•Portingofdatafromonedbmstootheriseasilypossibleasit
supportlargerangeofAPIsforvariousdatabases.

4. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
SQLConnectors
WemustdownloadaseparateDBAPImoduleforeach
databaseweneedtoaccess.Supposeweneedtoaccessan
OracledatabaseaswellasaMySQLdatabase,wemust
downloadboththeOracleandtheMySQLdatabasemodules.
TheDBAPIprovidesaminimalstandardforworkingwith
databasesusingPythonstructuresandsyntaxwherever
possible.
ThisAPIincludesthefollowing−
●ImportingtheAPImodule.
●Acquiringaconnectionwiththedatabase.
●IssuingSQLstatementsandstoredprocedures.
●Closingtheconnection

5. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Hereweareusingmysqlasbackenddatabasebecauseofitisopen
source,freeandportableandwidelyused.Anyoneofmysql-
connectororMySQLdbcanbeusedfordatabaseprogramming.
1.mysql-connector
MySQL-ConnectorenablesPythonprogramstoaccessMySQL
databases,usinganAPIthatiscompliantwiththePythonDatabase
APISpecificationv2.0(PEP249).ItiswritteninpurePythonanddoes
nothaveanydependenciesexceptforthePythonStandardLibrary.
Stepstousemysql-connector
1.DownloadMysqlAPI,exefileandinstallit.(clickheretodownload)
2.InstallMysql-PythonConnector(Opencommandpromptand
executecommand)>pipinstallmysql-connector
3.NowconnectMysqlserverusingpython
4.Writepythonstatementinpythonshellimportmysql.connector
Ifnoerrormessageisshownmeansmysqlconnectorisproperly
installed

6. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
2.MySQLdb
MySQLdbisaninterfaceforconnectingtoaMySQLdatabaseserver
fromPython.ItimplementsthePythonDatabaseAPIv2.0andisbuilt
ontopoftheMySQLCAPI.
Stepstousemysqlclient
1.FirstUpgradepipcommandthrough>python–mpipinstall–
upgradepip
2.Installmysqlclientthroughpipinstallmysqlclient
3.Aftersuccessfulinstallationcheckthroughimportmysqldb
4.Ifitisinstallednoerrorwillbedisplayedotherwiseerrormessagewillbe
displayed
ToinstallMySQLdbmodule,usethefollowingcommand−
ForUbuntu,usethefollowingcommand-
$sudoapt-getinstallpython-pippython-devlibmysqlclient-dev
ForFedora,usethefollowingcommand-
$sudodnfinstallpythonpython-develmysql-develredhat-rpm-configgcc
ForPythoncommandprompt,usethefollowingcommand-
pipinstallMySQL-python
Note−Makesureyouhaverootprivilegetoinstallabovemodule

7. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Establishconnection
Fordatabaseinterface/databaseprogramming ,connectionmustbe
established.Beforeestablishingconnectiontheremustbemysqlinstalledon
thesystemandadatabaseandtableisalreadycreated.Infollowingwaywe
canestablishaconnectionwithmysqldatabasethroughmysql.connector.
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root“,database
=“school”)
print(mydb)
Alternativelywecanwritethefollowingstatementifweareusingmysqldb
importMySQLdb
mydb=MySQLdb.connect("localhost",“root",“root",“school")
print(mydb)
Inbothwaywearespecifyinghost,user,passwordanddatabasenameas
arguments.databaseisoptionalargumentifwewanttocreatedatabase
throughprogramminglateron.
Aftersuccessfulexecutionofabovestatementsinpythonfollowingoutwill
bedisplayed
<mysql.connector.connection.MySQLConnectionobjectat0x022624F0>
Otherwiseanerrormessagewillbeshown.

8. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Cursorobject:
TheMySQLCursorclassinstantiatesobjectsthatcanexecuteoperations
suchasSQLstatements.CursorobjectsinteractwiththeMySQLserver
usingaMySQLConnectionobject.
Howtocreatecursorobjectanduseit
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root")
mycursor=mydb.cursor()
mycursor.execute("createdatabaseifnotexistsschool")
mycursor.execute("showdatabases")
forxinmycursor:
print(x)
Throughline4wearecreatingadatabasenamedschoolifitisalreadynot
createdwiththehelpofcursorobject.
Line5executesthesqlqueryshowdatabasesandstoreresultinmycursor
ascollection,whosevaluesarebeingfetchedinxvariableonebyone.
Onexecutionofaboveprogramschooldatabaseiscreatedandalistof
availabledatabasesisshown.

9. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtocreatetableatruntime
Tablecreationisveryeasy,ifwearealreadywellversedinsqltablecreation
thenwehavetojustpassthecreatetablequeryinexecute()methodof
cursorobject.Butbeforetablecreationwemustopenthedatabase.Herewe
areopeningdatabaseschool(throughconnect()method)beforestudenttable
creation.
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("createtablestudent(rollnoint(3)primarykey,name
varchar(20),ageint(2))")
Onsuccessfulexecutionofaboveprogramatablenamedstudentwiththree
fieldsrollno,name,agewillbecreatedinschooldatabase.
Wecancheckstudenttableinmysqlshellalso,ifrequired.

10. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtochangetablestructure/(add,edit,removecolumofatable)atruntime
Tomodifythestructureofthetablewejusthavetousealtertable
query.Belowprogramwilladdacolumnmarkinthestudenttable.
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("altertablestudentadd(marksint(3))")
mycursor.execute("descstudent")
forxinmycursor:
print(x)
Aboveprogramwilladdacolumnmarksinthetablestudentandwilldisplay
thestructureofthetable

11. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtosearchrecordsofatableatruntime
Belowstatementdemonstratetheuseofselectqueryforsearchingspecific
recordfromatable.
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
nm=input("entername")
mycursor.execute("select*fromstudentwherename='"+nm+"'")
forxinmycursor:
print(x)
Abovestatementswillpromptanamefromuser,asusertypethename,that
nameissearchedintothetablestudentwiththehelpofselectquery.result
willbeshownwiththehelpofmycursorcollection.

12. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtofetchallrecordsofatableatruntime
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("select*fromstudent")
myrecords=mycursor.fetchall()
forxinmyrecords:
print(x)
MySQLCursor.fetchall()Method
Themethodfetchesall(orallremaining)rowsofaqueryresultsetandreturnsa
listoftuples.Ifnomorerowsareavailable,itreturnsanemptylist.

13. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtofetchonerecordofatableatruntime
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("select*fromstudent")
row=mycursor.fetchone()
whilerowisnotNone:
print(row)
row=mycursor.fetchone()
MySQLCursor.fetchone() Method
Thismethodretrievesthenextrowofaqueryresultsetandreturnsasingle
sequence,orNoneifnomorerowsareavailable.Bydefault,thereturnedtuple
consistsofdatareturnedbytheMySQLserver,convertedtoPythonobjects.
MySQLCursor.fetchmany()Method
rows=cursor.fetchmany(size=1)
Thismethodfetchesthenextsetofrowsofaqueryresultandreturnsalist
oftuples.Ifnomorerowsareavailable,itreturnsanemptylist.

14. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
Howtodeleterecordofatableatruntime
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("deletefromstudentwhererollno=1")
mydb.commit()
Inaboveprogramdeletequerywilldeletearecordwithrollno=1.commit()
methodisnecessarytocallfordatabasetransaction.
Howtoupdaterecordofatableatruntime
importmysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root"
,database="school")
mycursor=mydb.cursor()
mycursor.execute("updatestudentsetmarks=99whererollno=2")
mydb.commit()
Inaboveprogramupdatequeryupdatethemarkswith99ofrollno=2
Studentsareadvisedtodevelopmenudrivenprogramusingaboveconcepts
forbetterunderstatingofpythonmysqldatabaseinterface.

15. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
ManageDatabaseTransaction
Databasetransactionrepresentsasingleunitofwork.Anyoperation
whichmodifiesthestateoftheMySQLdatabaseisatransaction.
PythonMySQLConnectorprovidesthefollowingmethodtomanage
databasetransactions.
commit–MySQLConnection.commit()methodsendsaCOMMIT
statementtotheMySQLserver,committingthecurrenttransaction.
rollback–MySQLConnection.rollbackrevertthechangesmadeby
thecurrenttransaction.
AutoCommit–MySQLConnection.autocommitvaluecanbeassigned
asTrueorFalsetoenableordisabletheauto-commitfeatureof
MySQL.BydefaultitsvalueisFalse.

16. Interface python with SQL Database
Visit : python.mykvs.in for regular updates
ManageDatabaseTransaction
try:
conn=mysql.connector.connect(host='localhost',
database='school',
user='root',
password='root')
conn.autocommit=false
cursor=conn.cursor()
sql_update_query="""Updatestudentsetmarks=95whererollno=2"""
cursor.execute(sql_update_query)
print("RecordUpdatedsuccessfully")
#Commityourchanges
conn.commit()
exceptmysql.connector.Erroraserror:
print("Failedtoupdaterecordtodatabaserollback:{}".format(error))
#revertingchangesbecauseofexception
conn.rollback()
finally:
#closingdatabaseconnection.
if(conn.is_connected()):
cursor.close()
conn.close()
print("connectionisclosed")
Inaboveprogramifupdatequeryissuccessfullyexecutedthencommit()methodwillbeexecuted
otherwiseexceptionerrorpartwillbeexecutedwhererevertofupdatequerywillbedonedueto
error.Atfinallyweareclosingcursoraswellasconnection.Torollbackorcommitwehavetoset
autocommit=false,justlikeconn.autocommit=falseinaboveprogramotherwiserollbackwillnotwork

17. SQL Commands
Visit : python.mykvs.in for regular updates
GroupingRecordsinaQuery
•SometimeitisrequiredtoapplyaSelectqueryinagroupof
recordsinsteadofwholetable.
•WecangrouprecordsbyusingGROUPBY<column>clause
withSelectcommand.Agroupcolumnischosenwhichhave
non-distinct(repeating)valueslikeCity,Jobetc.
•Generally,thefollowingAggregateFunctions[MIN(),MAX(),
SUM(),AVG(),COUNT()]etc.areappliedongroups.
Name Purpose
SUM() Returns the sum of givencolumn.
MIN() Returns the minimum value in the givencolumn.
MAX() Returns the maximum value in the givencolumn.
AVG() Returns the Average value of the givencolumn.
COUNT()Returns the total number of values/ records as per given
column.

18. SQL Commands
Visit : python.mykvs.in for regular updates
AggregateFunctions&NULL
ConsideratableEmphavingfollowingrecordsas-
Nullvaluesareexcludedwhile(avg)aggregatefunctionis
used
SQLQueries
mysql>SelectSum(Sal)fromEMP;
mysql>SelectMin(Sal)fromEMP;
mysql>SelectMax(Sal)fromEMP;
mysql>SelectCount(Sal)fromEMP;
mysql>SelectAvg(Sal)fromEMP;
mysql>SelectCount(*)fromEMP;
Emp
Code Name Sal
E1 Mohak NULL
E2 Anuj 4500
E3 Vijay NULL
E4 Vishal 3500
E5 Anil 4000
Result of query
12000
3500
4500
3
4000
5

19. SQL Commands
Visit : python.mykvs.in for regular updates
Aggregate Functions & Group
An Aggregate function may applied on a column with DISTINCT or ALL
keyword. If nothing is given ALL is assumed.
Using SUM (<Column>)
This function returns the sum of values in given column or expression.
mysql>SelectSum(Sal)fromEMP;
mysql>SelectSum(DISTINCTSal)fromEMP;
mysql>SelectSum(Sal)from EMPwhereCity=‘Jaipur’;
mysql>SelectSum(Sal)from EMPGroupByCity;
mysql>SelectJob,Sum(Sal)fromEMP GroupByJob;
Using MIN (<column>)
This functions returns the Minimum value in the given column.
mysql>SelectMin(Sal)fromEMP;
mysql>SelectMin(Sal)fromEMPGroupByCity;
mysql>SelectJob,Min(Sal)fromEMP GroupByJob;

20. SQL Commands
Visit : python.mykvs.in for regular updates
Aggregate Functions &Group
Using MAX (<Column>)
This function returns the Maximum value in given column.
Using AVG(<column>)
This functions returns the Average value in the givencolumn.
mysql> Select AVG(Sal)from EMP;
mysql> Select AVG(Sal)from EMP Group ByCity;
Using COUNT(<*|column>)
This functions returns the number of rows in the given
column.
mysql>SelectMax(Sal)fromEMP;
mysql>SelectMax(Sal)fromEMPwhereCity=‘Jaipur’;
mysql>SelectMax(Sal)fromEMPGroupByCity;
mysql>SelectCount(*)from EMP;
mysql>SelectCount(Sal)from EMPGroupByCity;
mysql>SelectCount(*),Sum(Sal)fromEMP GroupByJob;

21. SQL Commands
Visit : python.mykvs.in for regular updates
Aggregate Functions & Conditions
You may use any condition on group, if required. HAVING
<condition> clause is used to apply a condition on a group .
mysql> Select Job,Sum(Pay) fromEMP
GroupBy Job HAVING Sum(Pay)>=8000;
mysql> SelectJob,Sum(Pay) fromEMP
GroupBy Job HAVING Avg(Pay)>=7000;
mysql> SelectJob,Sum(Pay) from EMP
GroupBy Job HAVING Count(*)>=5;
mysql> SelectJob, Min(Pay),Max(Pay),Avg(Pay) from EMP Group
By Job HAVING Sum(Pay)>=8000;
mysql> SelectJob, Sum(Pay)from EMP Where City=‘Jaipur’
Note:-Where clauseworksinrespectofwholetablebutHavingworks
onGrouponly.IfWhereandHavingbothareusedthenWherewillbe
executedfirst.

22. SQL Commands
Visit : python.mykvs.in for regular updates
Ordering Query Result –ORDER BY Clause
A query result can be orders in ascending (A-Z) or
descending (Z-A)
order as per any column. Default is Ascending order.
mysql> SELECT * FROM Student ORDER BY City;
To get descending order use DESC key word.
mysql> SELECT * FROM Student ORDER BY City
DESC;
mysql> SELECT Name, Fname, City FROM Student
Where Name LIKE ‘R%’ ORDER BY Class;