Scribd
Upload
42 views

C12 Cs SqlConnectPython Notes

Uploaded by

azmiana786
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
42 views

C12 Cs SqlConnectPython Notes

Uploaded by

azmiana786
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 7
1) Interface Python with MySQL ive 151 Introduction 152 Connecting to MySQL from Python 153 Parameterised Queries 154. Performing INSERT and UPDATE Queries 131 INTRODUCTION When you design real-life applications, you are bound to | encounter situations wherein you need to manipulate data ——— | Sored in a database through an application designed by You. fore you start working wth Since you are developing Python applications, in this chapter Python mysa onnectr vou net our discussion will be based on how you can connect to a to instal et computer. Both lation of Python MYSQL database from within a Python script. nipaneCommeccr is event order to connect to a database from within Python, you Ampendie Coffe ae ‘wed a library that provides connectivity jonality. There Te the sme pos different libraries available for Python to accomplish . We shall work with mysql connector library for the same.COMPUTER SCIENCE WITH PYTHON _ 622 15.2.1 Gteps for Creating Databos ‘There are mainly seven steps that application. 1 Start Python. a 2 ae packages required for database Pro} ‘Step 3 Open a connection to database. Step 4 (Step5 Execute a query. Step 6 Extract data from result set. (Step7 Clean up the environment’) Let us talk about these steps in details. Step 1. Start Python. ‘Start Python's editor where you can create your Python scripts. Tt can be IDLE or Spyder IDE- whatever you feel comfortable in, Step 2,Amport mysql.connector Package (First of all you need to import mysql.connector package in your Python scripts)For this, write import command as shown below = e Connectivity Applications must be followed in order to create a database connectyy gramming. Create a cursor instance. dmport mysql. connector ae 10 can use ary identifier of your choice or Smport mysql.connector as sqltor You can also use pymysql in place of mysql.connector for connecting with MySQL database. We shall talk about this in section 15.5.2. Step 3. Open a Connection to MySQL Database P Next you need to establish connection to a MySQL database using connect( ) function of mysql.connector package.) .e connect( ) function of mysql.connector establishes i d requires four parameters) which are : connection to a MySQL database an Connection-Object> = mysql. connector. connect (host = , user = , \ Passwd = [, database = name. Y. eee for executing queries and for other tasks on connected database. For eon be using this mae =) ‘ample ; _Anport mysqiconnector as sqltor ‘oginid and password of your MySQL daiabost The connecion ——t#CON = Sq1tOP.connect (host = "localhost" sy : ; database = testy POOt”» passwd = "MyPass”» 4 MySOL databaseU5 INTERFACE PYTHON WITH MySQL 1 oe 623 \ sar we imported mysqlconnector package as salto, we MM Set peomector. Ifyou have impored the package as + We are using the name sqltor in place of import mysql.connector you need to write above function as: ‘Then snycon = mysql. connector. connect(host = “localhost” senuigroot passwd ror ‘MyPass", database = "test") \ che above command will establish connect ; eeword as “MyPass” and to the MyS See eee Ue er P s a 'ySQL database namely test which exists on the MySQL Here, you must ensure that the user and password that i i for yout local MYSQL installation. Soe ee ere eae cee ee eet ay tonnected to MySQL database. You can also check for datbnse Cometon bet = connectionobject>.cursor() the row by row proc , i of That is, for our connection established in earlier steps, we can records in the resultset, , the. of records retrieved 35 per quan create cursor( ) by writing : Burson = mycon.cursor() Cursor object created Connection object Since we established database connection through created a cursor object using the same connection object mycon. connection object mycon earlier, we hay Step 5. Execute SQL Query ‘Once you have created a cursor(you can execute SQL query using execute( ) function with cursor object as per following syntax : sgzursorobject>.execute()) For example, if you want to view all the records of table data which is a table in the database test to which you established connection in step 2, you can execute SQL query “select * fom data” by writing : Aitsor execute( “select * fron data") Se ave S01 any ingot ‘cursor object name The above code will execute the given SQL query and store the retrieved records (i.e, the resultset) in the cursor Cite result set refers toa loge ‘Of records that are fetched from object (namely cursor) which you can then use in your rograms/scripts as required. ‘the database by executing an SQL ~P e a query and made ayailable to the application moray) 6. Extract Data from Resultset You will need this step (step 6) you have retrieved records from the database using SOL SELECT query and need to extract records from the retrieved resultset. Once the result of query is available in the form of a resultset stored in a cursor object, you can extract data from the resultset using any of the following fetch...() functions) ) = .fetchall( ).4t will retum all the records retrieved as per query in ® tuple forni)(ie, now will be a tuple.) (ii) = .fetchone( ).Gt will retum one record from the resultset as a tuple O° list. First time it will return the first record, next time it will fetch the next record Step on} This method returns one record as a tuple : if there are no more records then it ru" ch and then it None. (Gy = fetchmany(). (his method accepts number of records to fe returns a tuple where each record itself is a tuple. If there are not more records returns an empty tuple) evariable> = .rowcount. The rowcount is a property of cursor object a returns the number of rows retrieved from the cursor so ES) Following examples make it more clear. wr >F 15 INTERFACE PYTHON WITH Mysqu Bm Joo foo > f= [> tus now see with following code ¢ amples, we shall be connecting to datab, ” student of MySQL database test {_Rolino | Name Marks | Gr, 101__| Ruhani_ | 76,80 aie Section | pojet| 102 | George | 71.20 A__| Pending, 103 _| Simran | 8120 A Submited { ae ae a 3 Evaluated 105 | Kushal | 51.60 ae \ et err C | Evaluated 60 + B__| Submitted 107 | Raunak | 3250 F B__| Submitted using connect( ) method of mysql.connector as discussed in earlier steps. That is, all the following code examples of fetch functions have following code pre-executed for them : import mysql.connector as sqltor database ="test") if mycgn.is_connected() == False: ‘print (‘Error connecting to MySQl database) ‘The SQL query retrieves all the data of Following code examples assume that the connection to the database has been established aycon = sqltor .connect(host = "localhost", user = "root", passud="HyPass", cursor = mycon.cursor() table student of database test “cursor .execute("select * from student") ; Let us now see that in addition to above code how all fetch. ‘methods will behave. (ihe fetchall( ) method : : ‘The fetchall( ) method will return all the rows from the resultset in the form of a tuple” | containing the records. ‘tablished and cyrsor object #database connected estab) ee many records esr by SC query i the rei! _—— data = cursor.fetchall() Thedaia variable will > 7" count » cursor -PONCOUDE ” es r ciber of rais retrieved inresv}tset » count) tuple (a tuple of re- 2 the dato tuple | fuer: ora dns — Non can 2 print(n 1 the output produced by abova code iS: Total nynber of roms retrieved in ee ee G01, ‘muhani’, oecimal wp apr, ‘eyatuated) xn pil Pa aS ert i y hich © Gos, ‘simran’, oecimal ('81-20") tore ‘assigned’? fens ts ws 5 e r e 5 sa G printed | Y 04, ‘ali’, pecinal('61-29°> Opt ‘syatuated 2. then \ GOs, ‘Kushal’, Decimal number of rows from the resullset in the # database connected established and cursor object created = cursor. fetchmany (4) Fetch 4 records in the resultset lit a voriable will 7” ow many records returned by SOL query in Te ate (gous Cue enter Sige a a cords from the resultset oy eee print(*Total nunber of POWs netrieved from resultset :", count) (a tuple of records) ae For row indata : .close() tabase connectio g, in this final SteP: __Agonnection obj abject mycon above: S° €8,, we established the dat We shall write : vs ofthe database le —itycon.close( ) wn via connection Follow; plete code W lowing example lists the COMP © 10 Student of MySQL database test © se

You might also like