Il | Database in python : Using SQL with python, retrieving rows from a table, 15
inserting rows into a table, deleting rows from a table, updating rows in a table,
creating database tables through python, Exception handling in databases. SSS
Chapter 5: Database in Python
5-1 to S11
ee nee ae nen NS eve ne ee PENOEOPENTPTEPEETEEnESS SS OOO
Sa Using SQL with Python mean
52 Retrieving Rows from a Table ween
53 Inserting Rows into a Table emnnnnsenmnn
54 Deleting Rows from a TaI€ vennmnmnnnnni
55 Updating Rows in a Table emnnennn
56 Creating Database Tables through Python...
87 Exception Handling in Databases . DATABASE IN PYTHON
Darbase in python : Using SQL with python, eteeving rows rm 2 table, inserting FOws int 9 table, deleting
row: from a table, updating rows in a tabla, creating databace tables through python, Exception handing in
enabases
QL with
+ Programming languages must be able to connect databases a order to bud real-word applications. Python,
‘on the other han, slows arto connect our spiication ta databases such as MySQL SQLite, MongsDB, and
others Here we are using MySQL
+ To Wore wih MySQL In Python follow these steps
2 Exableh a connection tothe database
(© Foryour daabase, crete an object
© Camry out the SOL query
(© Obtain record from the outcome,
you make any changes tothe able, you most not he database
esting MySQL
+ One the most widely used database MySQL.
“+ MySQU can be dowloaded and installed rom the MySQL ofl website. To llow this tutorial you must fst
Install the MySQL server
“+ Thea fr Python you must lstall mysghconector To connect Python Script athe MySQL: ita, we will
eed mysqconnector Install the myrlconnecor on your computer by downloading rom here,
+ Usngthe ellowng code, verify that you have correct inetd the myrconnecor,
port myalconnector
+ tne above code uns witout errors mysglconnectorhas ben sucesfaly installed and i ead to use Advanced Python Program
‘Creating the connection
‘The connect) method
of the mysalconnector module Is used to establish a connection between the MSO
database and the Python application.
im the method cal, pass the database detalls such as HostName, username, and database password. The
‘connection objects returned by this method
1+ Tee connect functions syntax listed as follows
‘Comnection-bject= myaghconnectorconnect{host= chost-name>, user = cuserame>. passwd =
)
Consider the following example
resrige hp mesin nce
output:
“rnysqLconnector connection MySQLConnection object at 03701 42e44780>
‘We should ote tha if we want to connect toa specific databace, we can ue the connect) method 1o specify the
database name.
SS mpeg] comectar
crease the conection object
[nyonn © aprghconactor, comet (host
tecircing the connection bject
pein nyona)
Output:
‘Creating a cursor object
‘The cursor objects an abstraction defined inthe Python DB-API 20 specification. I allows us to have multile
separate working environments using the same databste connection. The cursor abject created by calling the
connection object's ‘cursor’ function. The cursor object Isa crucial component of running database queries. Te
‘cursor object is created using the syntax listed 35 lows
say cur> =connasor()
ir ayel-cnmecte-cact ont © ata") wwe © hepato = yl tab =
tcitsng te eanstsn eect
(m0)
faz» ayecn. curser
peste
outpat:
‘
MysQUoursor thing exe ye)
creating new databaces
Geting he itoring databases
We an get the it ofa the databases by using the flowing MYSQL query.
> sow databases:
ample:
pel Saree
ouput:
(Cempiayerd8:)
Crest)
oa)
(sede)
Cay’)
mya)
(performance. sche)
ets)
TTT _Atvanced ton Programming (UES ony
“creating the new database
attache
‘The new database can be created by using the following SQL query.
> erste database
yegissoenestor
tcxaating the cursor object
SES ayeonn: cares
(Ceopoyeed8:)
(est)
(coo)
cron
(sade)
Cyc’)
Croat)
(peeformanee schema’)
(Casto)
Creating the table
‘once we have created the database, Now we are ready to create the table. The CREATE TABLE statements sed
to create tables Ih MYSQL, database Her, you need to spec the name of the able and, defntion (name and
Aaetype) ofeach column
Stax
Following the syntax to crate table in MySQL
(CREATE TABLE ble. name(
elumat datatype,
tun date,
numa datatype,
_— ]_ TT cola datatype,
x
ramp: Crestetable Employee
arose tne conection cject,
Jceon = yal cameron acne = “ac nt «soe tease = “Pihr")
Incntting te corse eject
oT er
‘rating 9 abe on vam oye nvig ose clans Ay tay teary, and earner 6
ST catsocete ere tae npopenune vrcar 8) 8 ay ta) A nN army Hy,
arya woe tll Seti nt mo mi)
Geom. rons)
com cto)
{ew rate the table Employ. You an type the commaon show table to sete table the database and you
‘anse the tbl description by typing command dese Employee
52 Rows from a Table
+ Reading values fom databases dove with the SELECT statment. Using SOL causes ke where, and
ters we can lithe output os elect qvr}.
4+ The etchall) method in Python returns the data stored in the table nthe form of rows To get the inde!
rows wecan iterate the elt
“+ Wen ae he python script to extract ta from the database thi section of the torial. We wl aso format
‘the outputs that itcan be printed fom the console.
Sue a
Zio
SSE _Aancd fyi Programming (40.85 ony ss
‘output
Mame | ia | Salary | depcid | _Branch.name
Rya_| 100 | s000000 | 100 Newark
tury | 101 | ascoo00 | 201 Ingle
sonny | 201 | 2500000 | 101 nd
53 Rows into a Table
‘To adda record oa table use the INSERT INTO statement We can use the format specilr (percents) instead
values in Python
Inthe carsor's xacute() method, we provide the actual values inthe form ofa tuple.
‘Consider the ftlowing Mustration.
SH mia camer
foes ~mylscomertor eet Dest = octbor:", ape Son
"pnsereing the ralane fate the table
ferenecoe(ogrral
fteomic te cransaction
roo. comit
‘coe. zotbackt)
Jnveous-clewe()
Name a Salary Dept | Branch name
aya 100 4000000 100 Newyork
Athare 101 3500000 20 India
Incertng multiple rows
‘Using the python script, we can also insert multiple rows at once. The Ist of varios tuples is used to describe
the multiple rowsach tem inthe list is rete a singe row, while cach tem nthe tuple is treated asa singe
‘oturn value (strtbate). Consider the following istration.
irr Advert Noten DH 8s ny 2 bran
meme | | sary | oept | Branch mame
ta | 100 | 4000000 100 Nemes
‘vary | 101 | 3500000 201 India
sunny | 201 | 2500000 101 na
5A__ Deleting Rows from a Table
To let specific record fom a table, se the DELETE FROM statement We must ne the WHERE cause to
impose a condition, otherwise al lhe ecrds rom the tale wil be removed.
“Tee employee deta wit thd 101i deleted rom the table sing the QL quer below.
> delet from Employee where id= 101
= a We ssvacet ton Programming (MU BSC Co pohese ashy
‘output:
Name “ Salary pepuid | Branch.name
Riya 100 40,000.00 100 Newyork
sunny 201 2500000 101 naa
SS_Updating RowsineTable
Any column in the table canbe updated wth he UPDATE-SET statement To update column, we the SQL gvey
low
> wpdate Employee set name ='yogeth where d= 201
Consider the allowing example,
mysql connector
cease
Pacer ceeey
foreating the cursor object
far = aycons cursor)
toeleting the employee details whose id
cate (ickTnce trom Earanyen sere
smyconn. rollback
output:
Name | td Salary Depts ‘ranch name
rye | 100 | 090000 100 Newyork
vores» | 201 | 2500000 1 Indi
56 Database Tables through Python
‘We wil make anew table called Employes. When creating the connection object, we mut specify the datas,
ir sired tan Progen (MU BS wt
‘The CREATE TABLE statement in SQL cam be used to rete a new tbl. The table Fmplaye nour data
‘stonD will have our columns a frst: name. alr, and department
Tocreat the new able Employes, run the flowing query.
rete table Eeployee (name varcar(20) not ll din primary hey sary Not at il Dept. 14 tml)
ample
spot! mpaal connector
fereate the connection ebject
sayconn = aysqi connector. connect (host =
fereating the cursos object
(cur = aycomn. curser)
Gb = curvenscute(‘crsate sable Eaployee (sane varchar 20) not malt
faint (20) not all peamary Key,
salary flea? sor ally
Depe ia ine ect mill)” |
‘ayconn.zolIback()
|myeom.ctose')
| varchar(2@) | NO
| int(28)
| float
| int)
57 in Databases
Becouseineracting with a database i an error-prone process we must avaysInchde a mechan to
rca handle errors. MySQL has 2 toplevel exception, MySQL Error, which can be wed to catch al database
‘aceptons ase by the MySQudomodule,
AAS vanced gn 8s. 20
Spanner
a
Sa reel cence eg secre
See eae ieee
a ee ee
SS a erence
Some aaron eer
—
1. Dataéeror 2 Inernarror
3 InteryEror A OperationalBror
5. NotSupportedError 6 ProgrammingSrror
4. DataBrror: This error is thrown by MySQlab when theresa problem with data processing such 35 division by
‘er or the mameric value of rage.
2 taternarror: This exception s thrown when the MySQL database encounters an Internal eror. For exam
an ald cursor, a transaction thats ut of syne and soon.
IntegrtyBrror: When foreign key check als ths exception thrown.
is exception Is thrown when something happens thatthe programmer has no contol
cover. Fr example, an unexpected disconnect, 2 memory allocation erer,ora database tha does not exist
‘5, NotSupportedtrror: When method or API's not supported, his exception thrown.
{6 ProgrammingZrror ; This exception is thrown when a programme falls For example, table not found. mys
syntax ero, incorrect number af parameters specified and 5009,
OT pee oe
— r
sarmne minaracscvannes |
eeeececere
sabe on cc tte
Sacleset)
ae? |
osc my ttacnarsee
eine).
escort
pest
ar psatonttcr
Prince) a
pets se deenereatce
peintinress
How connect SO server expan?
Wats conor 7 Explain twin example
How ratieve rows om a table?
ipa wih exams how to ns rome et aaa,
Explain wth exams ow to dle roe rom a table,
Explain win exampe ow to update rows na table,
Explain with example how chow to coat detabase ables trough python.
Expl Exception handing in databases.
‘ite & pybon program te inset & value In database fable emp wi folowing atrbuteemp_id number
erp_name sting omo.sa number.