CHAPTER -12

INTERFACE PYTHON WITH MYSQL

SYLLABUS
NEED FOR PYTHON MySQL CONNECTIVITY
 While designing real life applications, we encounter situation when data is
to be sent from front end to back end.
 For easier interaction with database by users, we need to connect
Python(front end) to MySql(Back end).
 Python is connected to SQL to store data permanently to an external
database.
INSTALLATION OF MYSQL
CONNECTOR(cmd)

Mysql-connector

 The Very first step is we must connect Python with MySql. To connect
Python with Mysql we need to install MySql connector using pip command.

pip install mysql-connector-python

Frond End

Back End
(SQL)
Frond End

DATA
DATA
Back End
(SQL)
STEPS FOR CREATING DATABASE
CONNECTIVITY
Step 1: Start Python using IDE.

Step 2: import the packages for database programming.

import mysql.connector

or

import mysql.connector as con

Step 3: Open a connection to database.

STEPS FOR CREATING DATABASE
CONNECTIVITY
Step 4: Test the connection by using is_connected()

SYNTAX:

Example:

if con.is_connected():
print("Connection
Successful")
PRACTICE QUESTION - 1
Write a Program in Python to test the connection status between
Python and SQL.
STEP 5: CREATING A CURSOR INSTANCE
 A database cursor is a special controlled structure that facilitates the row by
row processing of records in the result set.

Syntax:

Cursor_object_name=Connection_object_name.cursor()

Example:

cur=con.cursor()
STEP 6: EXECUTING SQL QUERY
 After the cursor is created, the SQL query can be used with the execute().
 SQL queries are run within Python using execute().

Syntax:'

Cursor_object_name.execute(‘Sql Query’)

Example:

cur.execute(‘Select * from Emp‘)

(i) Creating a new Database
 We must create database to store our tables. The following code is
used to create database from Python to SQL.
(ii) Creating a table in an existing
database
 After successful creation of Database, then we need to create table(s) to
store our data. The following code is used to create table from Python to
SQL.
commit()
 This function is used to changes to a database table.
 Hence, with queries like INSERT, UPDATE and DELETE the commit() must be
used such that the changes are reflected permanently in the database.
 It must be used after the query that makes changes in database.

Syntax:

connection_objectname.commit()
(iii) INSERTING VALUES INTO EXISTING
TABLE
 After successful creation of table we need to store our data in table. The
following code is used to store our data into table.
PARAMETERIZED QUERY
 Some times we need input from the user to insert or update or delete
records to/from database.
 Based on their input we need to create or pass queries from python to SQL.
 Parameterized queries are queries which helps to pass user’s input
to SQL. It contains place holders %s or { }.
 To execute these queries, we need to form SQL query strings that includes
values of parameter.
METHODS FOR FORMING PARAMETERIZED
QUERIES
 We can create Parameterized queries in two forms. They are:
(i) Old format – String templates with % formatting.
(ii) New format – String templates with format().
(i) Old format:
In old format:
(i) If user input is in Numeric form then we should use %s or %d.
(ii) If user input is in string form then we should use '%s' with quotes.
Syntax: Query="Sql query with % format" % (variable1,variable2, .… variable
n)
Connection_object.execute(Query)
Example: No=int(input(‘Enter Employee number”)
Name=input(“Enter Employee Name:”)
cur=con.cursor()
Query=“INSERT INTO EMP VALUES(%s, '%s')" %(No, Name)
cur.execute(query)
con.commit()
(ii) New Format
In new format no need to use %s . Instead we use only { }.
 In new format:
(i) If user input is Numeric form then we should use { }.
(ii) If user input is string form then we should use '{ }' with quotes.
Syntax:
Query="Sql query with {}" .format (variable1,variable2, .… variable n)
Connection_object.execute(Query)
Example: No=int(input(‘Enter Employee number”)
Name=input(“Enter Employee Name:”)
cur=con.cursor()
Query=“INSERT INTO EMP VALUES({ }, '{ }')".format(No,Name)
cur.execute(query)
con.commit()
(iv) UPDATE QUERY
 To update values of a table , we need to use update command.
 The following code illustrates the updating Emp_salary based on employee
number getting from user.
(iv) DELETE QUERY
 To delete particular detail of record from table, we need to use delete
command with where condition.
 The following code illustrates the deleting particular Employee details
based on employee number getting from user.
(vi) FETCHING/DISPLAYING RECORDS
FROM SQL TO PYTHON
 Using three ways we can fetch/display records from SQL to Python. They
are:

(i) fetchone()

(ii) fetchall()

(iii) fetchmany()
(i) fetchone()
 This function at a time return one record from the resultset as a
tuple.
 When one record is fetched, the pointer moves to the next record of the
result.
 When we use fetchone() again, it will return the next record of the resultset.

Syntax: Data=cursor_object_name.fetchone()

How fetchone() works?

Case: 1
Case: 2
PRACTICE QUESTION - 1

Write a Program in Python to extract the first two rows from the table emp
and display the same using fetchone().
(ii) fetchall()

 This function will return all the rows from resultset in the form of tuple
containing all the records.

Syntax: Data=cursor_object_name.fetchall()

EXAMPLE:
PRACTICE QUESTION - 1

Write a Program in Python to extract all rows from the table emp and
display the same using fetchall().
(iii) fetchmany()

 This function will return ‘n’ number of rows from the resultset in the
form of tuple containing the records.

Syntax: Data=cursor_object_name.fetchmany(n)

EXAMPLE:
EXAMPLE -1

Write a Program in Python to extract first 3 rows from the table emp and
display the same using fetchmany()
EXAMPLE - 2

Write a Program in Python to extract ‘n’ number of rows from the table
emp and display the same using fetchmany().
Rowcount

 It is the property of the cursor object.

 It is used to retrieve the number of rows from the cursor so far.

Syntax: Variable = cursor_objectname.rowcount

Row count Example

Write a Program in Python to extract ‘n’ number of rows from the table
emp and display the same using fetchmany(). Also, display the number
of rows fetched.
THE END
PAY ROLL MANAGEMENT PROJECT