Interface Python with MySQLwedgvwewefwefwe.pptx

dgsdgsd

1. INTER F A CE PYTHON WITH MYSQL Connecting P y thon app l icati o n wi t h MySQL

2. Introduction Every application required data to be stored for future reference to manipulate data. Today every application stores data in database for this purpose For example, reservation system stores passengers details for reserving the seats and later on for sending some messages or for printing tickets etc. In school student details are saved for many reasons like attendance, fee collections, exams, report card etc. Python allows us to connect all types of database like O r acl e , SQL S e r v e r , My S QL . In our syllabus we have to understand how to connect P y th o n p r og r ams with My S QL

3. Pr e -re q uisite to c o nnect P y thon with MySQL Before we connect python program with any database like MySQL we need to build a bridge to connect P y th o n and My S QL . To build this bridge so that data can travel both ways we need a connector called “mysql.connector”. W e can instal l “ m ysq l . c o nne c tor ” b y usi n g f oll o wi n g methods: 🞑 At com m and p r om p t (Ad m i n i s t r at o r login) Type “pip install mysql.connector” and press enter ( i nte r net connecti on in requi r ed) This connector will work only for MySQL 5.7.3 or later 🞑 Or open “https://dev.mysql.com/downloads/connector/python /” VI a NO n D d KU d M o A w R V n E l R o M a A, d PG c T ( o CS n ), n K e V c O t E o F r KA a N s PU p R & er OS and Python version

4. Co n necting to My S QL f r om Pyth o n Once the c o n n e c tor i s installe d y ou are rea d y to c o n n e c t y o u r p yt h on p r ogr am t o MySQL. The following steps to follow while connecting your python program with MySQL 🞑 Open python 🞑 Import the package required (import mysql.connector) 🞑 Open the con n ection to d at a b a se 🞑 Cr ea t e a cu r sor i n stance 🞑 Execute the query and store it in resultset 🞑 Extract data from resultset 🞑 Clean up the environment

5. Importing mysql.connector import mysql.connector Or impo r t m ys q l.connecto r a s ms Here “ms” is an alias, so every time we can use “ms” in place of “mysql.connector”

6. Ope n a con n ection to M ySQL D a taba s e To create connection, connect() function is used It s syn t ax is: 🞑 connect( host = <server_name> , user = <user_name> , passwd = <password> [, database = <database> ]) Here server_name means database servername, generally it i s g i v en a s “ loca l hos t ” User_name means user b y whi c h w e con n ect w i th m ysql g ene r a l l y it i s g i v en a s “ r oot” P as s w ord i s the p as s w ord of user “ r oot” Database is the name of database whose data(table) we want to use

7. Example: T o estab l ish connect i on w i th MySQL is_co n ne c ted () fu n ction ret u r ns t r ue i f conn ecti on i s establ ished otherwise false “ m y s ” i s an alias of pa c ka g e “ m ysql.con nect o r ” “mycon” is connection object which stores connection established with MySQL “connec S t A ( C )” HIN fu B n H c A ti R o D n W is AJ u , s P e G d T(C to S), c K o V nn N e O c . t 1 w TE i Z th PU m R ysql by specifying parameters like host, user, passwd, database

8. Table to work (emp)

9. Creating Cursor It is a useful control structure of database connectivity. When we fire a query to database, it is executed and resultset (set of records) is sent over t he connection in on e g o . We may want to access data one row at a time, but query processing cannot happens as one row at a time, so cursor help us in performing this task. Cursor stores all the data as a temporary container of returned data and we can fetch data one row at a time from Cursor.

10. Creating Curso r and E x ecuti n g Que ry T O CRE A TE CURSOR 🞑 Cu r sor_ n a m e = connectionObjec t . cur s o r () 🞑 F or e . g . 🞑 m y cu r sor = m y co n . cu r so r () T O EXECU T E Q UE R Y We use execute() function to send query to connection Cursor_name.execute(query) F or e . g . m y c u rso r . e x ecut e („select * f r om em p ‟)

11. Example - Cursor Output shows cursor is created and query is fired and stored, but no data is coming. To fetch data we have to use functions like fetchall(), fetchone(), fetchmany() are used

12. Fetching( e xt r acting) d a ta from R esultSet T o e xt r act data f r om c u rsor f oll o wing fu n ctio n s are u sed: 🞑 fetchall() : it will return all the record in the form of tuple. 🞑 fetchone() : it return one record from the result set. i.e. first time it will return first record, next time it will return seco n d r ec o rd and s o o n . If n o m o r e r ecord it wi l l r etu r n None 🞑 fetchmany(n) : it will return n number of records. It no more record it will return an empty tuple. 🞑 rowcount : it will return number of rows retrieved from the cu r sor s o fa r .

13. Example – fet c hal l ()

14. Example 2 – fetchall()

15. Example 3 – fetchall()

16. Example 4: fetchone()

17. Example 5: fet c hma n y (n)

18. Guess the output

19. Parameterized Query We can pass values to query to perform dynamic search like we want to search for any employee number entered during runtime or to search any other column v alue s . T o C reate P a r ameterized que r y w e c an use v ario u s methods li k e : 🞑 Conca t ena t in g d yna m i c v ariab l e t o q u e r y v a l ues are ent e re d . 🞑 String template with % formatting 🞑 String template with {} and format function i n wh i c h

20. Concatenating variable with query

21. String templa t e wi t h % s f o r mat t ing In this method we will use %s in place of values to substitute and then pass the value for that place.

22. String templa t e wi t h % s f o r mat t ing

23. Str i ng te m pl a te wi t h { } and f o r m a t() In this method in place of %s we will use {} and to pass values for these placeholder format() is used. Inside we can optionally give 0,1,2… values for e.g. {0},{1} but its not mandatory. we can also optionally pas s nam e d p a r ame t er insid e {} so t h at w hile p a s s i n g w e n e e d to pass. not to F or e . g . v alu e s th r ou g h f o r mat f unct i on remember the order of value {roll},{name} etc.

24. Str i ng te m pl a te wi t h { } and f o r m a t()

25. Str i ng te m pl a te wi t h { } and f o r m a t()

26. Inse r t i ng d a ta i n MySQL tab l e fr o m Python INSE R T and UP D A T E op e r ati o n are e x e cuted i n the same way we execute SELECT query using execute () but one thing to remember, after executing insert or update query we must commit our query using co n necti o n o b ject with commit () . F or e . g . (if our connection object name i s m y co n ) mycon.commit()

27. Example : inserting data BEFORE P R O G R A M EX E C U T I ON A FTER P R O G R A M EX E C U T I ON

28. Example: Upd a t i ng r ecord