INTERFACE PYTHON WITH MYSQL

 Every application required data to be stored for future reference to manipulate data. Today every
application stores data in database for this purpose.
 Python allows us to connect all types of database like Oracle, SQL Server, MySQL.
 Before we connect python program with any database like MySQL we need to build a bridge to connect
Python and MySQL.
 To build this bridge so that data can travel both ways we need a connector called “[Link]”.
 The following steps to follow while connecting your python program with MySQL
 Open python
 Import the package required (import [Link])
 Open the connection to database
 Create a cursor instance
 Execute the query and store it in resultset
 Extract data from resultset
 Clean up the environment

Importing [Link] module

import [Link]
Or
import [Link] as ms
Here “ms” is an alias, so every time we can use “ms” in place of “[Link]”

Creating a connection to mysql/Establish a connection to mysql

For database interface/database programming, a connection must be established from python to MySql. To
create connection, connect() function is used.

Its syntax is:

Connection_object= [Link] .connect (host=<server_name>, user=<user_name>,

 Here server_name means database servername, generally it is given as “localhost”

For e.g.

import [Link]

mycon=[Link](host="localhost",user="root",passwd="root")
Cursor object

When we fire a query to database, it is executed and resultset (set of records) is sent over the connection in
one go. Cursor objects interact with the MySQL server using a MySQL Connection object. Cursor stores all
the data as a temporary container of returned data and we can fetch data one row at a time from Cursor.

The syntax to create a cursor object is:

Cursor_object = [Link]()

For e.g.

mycursor = [Link]()

To execute query after creating cursor object:

We use execute() function to send query. The syntax is:

Cursor_object.execute(query)

Example1: To create a database ‘school’ in MySql

import [Link] as ms
mydb=[Link](host="localhost",user="root",passwd="root")
mycursor=[Link]()
[Link]("create database if not exists school")

Example2: To create a table in MySql

import [Link] as ms
mydb=[Link](host="localhost",user="root",passwd="root",database="school")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary key, name varchar(20),age int(2))")

Fetching(extracting) data from ResultSet

To extract data from cursor following functions are used:

 fetchall() : it will return all the record in the form of list of tuple. If no more rows are available, it
returns an empty list.
 fetchone() : it return one record from the result set in the form of a tuple. i.e. first time it will return
first record, next time it will return second record and so on. If no more record it will return 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 cursor so far. (Note that there is no
parenthesis for rowcount)
Example1 – fetchall()

import [Link] as ms

mycon = [Link](host='localhost',user='root',passwd='admin', database='school')

mycursor = [Link]()

[Link]("select * from student")

mydata= [Link]()

nrec = [Link]

print("Total records found are", nrec)

for row in mydata:

print (row)

Example 2 – fetchall()

import [Link] as ms

mycon = [Link](host='localhost',user='root',passwd='admin', database='school')

mycursor = [Link]()

[Link]("select * from student")

mydata =[Link]()

nrec = [Link]

print("Total records found are", nrec)

for row in mydata:

print (row[0],':',row[1],':',row[2])

Note: row[0] will print rollno, row[1] will print name and row[2] will print age from the student table.)

Example 3: fetchone()

import [Link] as ms

mycon = [Link](host='localhost',user='root',passwd='admin', database='school')

mycursor = [Link]()

[Link]("select * from student")

mydata = [Link]()
nrec = [Link]

print("Total records found are", nrec)

print("Total data retrieved=", mydata)

Note: repeat the same mydata = [Link]() statement to retrieve the next record from the table.

Example 4: fetchmany(n)

import [Link] as ms

mycon = [Link](host='localhost',user='root',passwd='admin', database='school')

mycursor = [Link]()

[Link]("select * from student")

mydata= [Link](3) # cursor will retrieve only 3 records from the resultset

nrec = [Link]

print("Total records found are", nrec)

for row in mydata:

print (row)

Inserting and Updating data in MySQL table from Python

INSERT and UPDATE operation are executed in the same way we execute SELECT query using execute()
function but one thing to remember, after executing insert or update query, we must commit our query
using commit() function to reflect the changes in the database.

commit()– [Link]() method sends a COMMIT statement to the MySQL server,

rollback()– [Link] () revert the changes made by the current transaction.

Example 5: Update student data

import [Link] as ms

mycon = [Link](host='localhost',user='root',passwd='admin', database='school')

mycursor = [Link]()

query1 = "update student set marks = 95 where rollno = 2"

[Link](query1)

[Link]() #making the changes to reflect in table

print ("Record Updated successfully ")