CHAPTER XIII

INTERFACE PYTHON WITH SQL

PREPARED BY

Mrs. B. DIVYA PRABHA

SREE GOKULAM PUBLIC SCHOOL

REFERENCES:

STD XII BOOK BY NCERT, SUMITA ARORA AND PREETHI ARORA

&

VARIOUS SOURCES FROM INTERNET

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 Oracle, SQL

Server, MySQL.

What is an Interface?
Interface is the way for an application to interact with certain
system/application.

For Example:

API -APPLICATION PROGRAMMING INTERFACE

In computer programming, an application programming interface is a

set of subroutine definitions, communication protocols, and tools for
building software. In general terms, it is a set of clearly defined
methods of communication among various components.
BENEFITS OF PYTHON FOR DATABASE PROGRAMMING

There are many good reasons to use Python for programming database
applications:
Programming in Python is arguably more efficient and faster
compared to other languages.
Python is famous for its portability.
It is platform independent.
Python supports SQL cursors.
In many programming languages, the application developer
needs to take care of the open and closed connections of the
database, to avoid further exceptions and errors. In Python, these
connections are taken care of.
Python supports relational database systems.
Python database APIs are compatible with various databases, so
it is very easy to migrate and port database application interfaces.

In our syllabus we have to understand how to connect Python

programs with MySQL

Before we connect python program with any database like MySQL

we need to build a bridge to connect Python and MySQL.

What is MYSQL Connector Python?

MYSQL Connector Python is module or library
available in python to communicate with a MySQL

ADVANTAGES OF MYSQL CONNECTOR PYTHON

MySQL Connector Python is written in pure Python, and it is

self-sufficient to execute database queries through python.
It is an official Oracle-supported driver to work with MySQL and
python.
It is Python 3 compatible, actively maintained.
 To build this bridge so that data can travel both ways we need a
connector called “mysql.connector”.

We can install “mysql.connector” by using following methods:

At command prompt (Administrator login)
Type “pip install mysql.connector” and press enter
(internet connection in required)
This connector will work only for MySQL 5.7.3 or
later
Or open
“https://dev.mysql.com/downloads/connector/pytho
n/” & and download connector as per OS and
Python version
ARGUMENTS REQUIRED TO CONNECT MYSQL FROM
PYTHON
You need to know the following detail of the MySQL server to
perform the connection from Python.
Username – i.e., the username that you use to work with MySQL
Server. The default username for the MySQL database is a root
Password – Password is given by the user at the time of installing
the mysql database. If you are using root then you won’t need the
password.
Host Name – is the server name or Ip address on which MySQL
is running. if you are running on localhost, then you can use
localhost, or it’s IP, i.e. 127.0.0.0
Database Name – Database name to which you want to connect.

Once the connector is installed you are ready to connect your

python program to MySQL.

STEPS TO CONNECT MYSQL DATABASE IN PYTHON

USING MySQL Connector Python

Install MySQL Connector Python using pip.

Use the mysql.connector.connect() method of MySQL
Connector Python with required parameters to connect MySQL.
Use the connection object returned by a connect() method to
create a cursor object to perform Database Operations.
The cursor.execute() to execute SQL queries from Python.
Close the Cursor object using a cursor.close() and MySQL
database connection using connection.close() after your work
completes.
Catch Exception if any that may occur during this process.
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python

Follow the steps:-

Step 1: Start the Python
Step 2: Import Package
Step 3: Open Connection or Connect to database
Step 4: Create a cursor
Step 5: Execute Query
Step 6 Extract data from the result set
Step 7. Close the connection or clean up the environment.

STEPS TO CONNECT MYSQL DATABASE IN PYTHON

USING MySQL Connector Python

Step 1: Start the Python

Start the Python IDLE editor to write the script
Step 2: Import MySQL Connector Python Package.
import mysql.connector
Or
import mysql.connetor as SQLCon

Step 3: Open Connection or Connect to database.

Mycon is a connection object

Mycon=mysql.connector.connect (
host='localhost', database='mysql', user='root', password='')

What is Database Connection Object?

A Database connection object controls the connection to the
database. It represents a unique session with a database connected
from within a script or program

One can check the connection by writing the following code.

If mycon.is_connected():
print(“Successfully Connected”)

What is cursor?
A database cursor is a special control structure that facilitates
the row by processing of records in the result set.

What is result set?

Result set refers to the logical set of records that are fetched
from the database by executing an SQL query. It is the set of records
retrieved as per the query.

Step 4: Create a cursor.

Cursor object=
connectionobject.cursor()
For example: Cursor Object
EmpCursor = mycon.cursor()

Mycon is a connection object

Step 5: Execute Query

EmpCursor.execute(“select * from emp”)

Step 6: Extract data from the result set.

After retrieving the records from the DB using SQL Select

Query. You need to extract records from the result set.

You can extract the result set using any of the

following fetch functions/ cursor methods.

.fetchone() .fetchmany(n) .fetchall()

Cursor other methods are: -

.close() .callproc() .nextset()

Step 7. Close the connection or clean up the environment.

Syntax:
Connectionobject.close()
Example:
Mycon.close()

Open a connection to MySQL Database

To create connection, connect() function is used

Its syntax is: connect(host=,user=, passwd=[,database=])

Here server_name means database servername, generally it is given

as “localhost”
 User_name means user by which we connect with mysql generally

it is given as “root”

Password is the password of user “root”

Database is the name of database whose data (table) we want to use

Example: To establish connection with MySQL

“mys” is an alias of package “mysql.connector”

“mycon” is connection object which stores connection established
with MySQL
“connect()” function is used to connect with mysql by specifying
parameters like host user_passwd_database
Table to work (emp)

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 he connection in one go.
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.

Creating Cursor and Executing Query

TO CREATE CURSOR
Cursor_name = connectionObject.cursor()
For e.g.
mycursor = mycon.cursor()
 TO EXECUTE QUERY
We use execute() function to send query to connection
Cursor_name.execute(query)
For e.g. mycursor.execute(„select * from emp )
Example – Cursor

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 tuple.
fetchone() : it return one record from the result set. 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.

Example – fetchall()
Fetch all method will return all the rows from the result set in the form
of tuple containing the records.
Data=EmpCursor.fetchall()
V_count=EmpCursor.rowcount
print(“Total Rows retrieved : “,V_count)
for row in data:
print(row)
Example 2 – fetchall()

Example 3 – fetchall()
Example 4: fetchone()
Fetch the next row of a query result set, returning a single sequence,
or None when no more data is available
Data=EmpCursor.fetchone()
V_count=EmpCursor.rowcount
print(“Total Rows retrieved : “,V_count)
print(data)
Example 5: fetchmany(n)
Fetch the next row of a query result set, returning a single sequence,
or None when no more data is available
Data=EmpCursor.fetchone()
V_count=EmpCursor.rowcount
print(“Total Rows retrieved : “,V_count)
print(data)
Guess the output
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 values.
To Create Parameterized query we can use various methods like:
Concatenating dynamic variable to query in which values
are entered.
String template with % formatting
String template with {} and format function

Concatenating variable with query

String template with %s formatting

In this method we will use %s in place of values to substitute and
then pass the value for that place.
 INSERT QUERY

To Insert a record in a table use cursor object. When you perform

insert or update remember to commit the transaction.
MyQuery=“Insert into student ( rollno,name, marks) values
({},’{}’,{} ) “ .format(1203,’Raman’,67.6)
Cursor.execute(MyQuery)
Mycon.execute()
 UPDATE QUERY

To Update a record in a table use cursor object. When you

perform insert or update remember to commit the transaction.
MyQuery=“update student set marks={}“ where marks={}”
.format(84,66)
Cursor.execute(MyQuery)
Mycon.execute()