15-MySQL Connectivity cs 12

Revised as
Computer Science per

CBSE
Curriculum
Class XII (CBSE Board) 2020-21
UNIT-3: Database Management

Chapter:15
MySQL- Python Connectivity
Authored By:- Rajesh Kumar Mishra, PGT (Comp.Sc.)

Kendriya Vidyalaya Khanapara, Guwahati (Assam)
e-mail : [email protected]
Expected Learning Outcome
In this presentation you will learn about
connectivity between MySQL Database and
Python Program i.e. you can handle database
operations through Python code.
 Understanding Database Connections
 Steps to connect Database with Python
 Executing simple queries through Python
 Passing parameters to query
Introduction
A real life application requires data to be stored
for future use or reference. All application
programs stores data in database.
A school application stores student’s attendance, fee,
exams details in database for future use.
A rail reservation system stores passenger and
seat details in a database and later on for sending
messages or printing tickets etc.
A Python program allows us to connect different
types of database applications like Oracle, SQL
Server, MySQL etc.
Requirement to connect Python
with MySQL
Before connecting python program with database, we need a
Connector/Driver program to build a bridge between Python
Program and MySQL database. This bridge facilitate two way
travel of data. Python offers a connector library called
“mysql.connector” to make connection with MySQL database.
We can install “mysql.connector” by using following way:
• Make sure that your PC/Laptop is connected with Internet.
• 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
If you are getting
error after giving
import command, it
shows that connector
is not installed.
Connecting to MySQL from Python
 Once the connector is installed you can connect
your python program to MySQL.
 The following steps to follow while connecting
your python program with MySQL
 Import the connector package
(import mysql.connector)
 Open the connection to database
 Create a cursor instance
 Execute the query and store result in resultset
 Extract data from resultset
 Clean up the environment
Step 1: Importing mysql.connector
import mysql.connector [as <connection object>]
Example:
import mysql.connector
Or
import mysql.connector as ms
Here ms is user given identifier to connection object

which works as alias of connector i.e. we can use ms in
place of “mysql.connector”
Step 2: Open a connection to MySQL Database
 To create connection, connect() function of connection object is
used.
 Its syntax is:
ConnectionObject.connect(host=<server_name>,user=<user_name>,
passwd=<password> [,database=<database>] )
 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”
 passwd 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
is_connected() function returns

true if connection is established
otherwise false
“mys” is an alias of package “mysql.connector”

“mycon” is connection object which stores connection established with MySQL
Step 3: Creating Cursor
 Cursor is holding area of records received from the database
as response of a query.
 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 at database end returns all record at once. Cursor stores
all the data as a temporary container of returned data and we
can fetch data one row at a time from Cursor.
 TO CREATE CURSOR
Cursor_name = connectionObject.cursor()
For e.g.
mycursor = mycon.cursor()
Step 4: Executing Query and fetching records
 TO EXECUTE QUERY
 We use execute() function to send query to connection
 Cursor_name.execute(query)
 For e.g.
mycursor.execute("select * from emp‟)
Any type of DDL and DML query like Create, select,

insert, update, delete etc can be executed.
Query to be given as string (enclosed in quotes)
Step 4: Executing Query and fetching records
 We use execute() function to send query to connection
 Cursor_name.execute(query)
 For e.g.
mycursor.execute("select * from emp‟)
Step 5: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()
nrec is
working as
record set
Example 2 – fetchall()
Example 3 – fetchall()
Example 4: fetchone()
Example 5: fetchmany(n)
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.
String template with %s formatting
String template with {} and format()
 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
pass named
values parameter
through formatinside {} so we
function that while
need passing
not to
remember the order of value to pass. For e.g.
{roll},{name} etc.
Inserting data in MySQL table from Python
 INSERT and UPDATE operation are executed in 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 connection object with commit().
 For e.g. (if our connection object name is mycon)
 mycon.commit()
BEFORE PROGRAM EXECUTION
Example : inserting data
AFTER PROGRAM EXECUTION

Example: Updating record
To display list of available databases in MySQL
Showing list of
databases
We can also create our database through
python
Creating
Database
See a
newdatabase
telephonedir
is created
output
Creating a table ‘customer’
Creati
ng
table
Displaying
list of
tables
OUTPUT
Inserting data into table customer
Inserti
ng a
record
Inserting records by taking its input by user
OUTPU
T
With INSERT INTO statement,

we can mention the format
specifier (%s) in place of values.
Inserting multiple records
Inserting many
records by
creating list of
tuples
Note that here we have to use

executemany() instead of execute()
Updating Data … have a look on the table
below- We are going to change the name of
customer 1003 to ‘Sudha’
34
Code to update record
Name of 1003
customer is
changed to
Sudha
Deleting record: lets delete the record
Record with
cust_id
1006 is
deleted now

15-MySQL Connectivity cs 12

Uploaded by

Copyright:

Available Formats

15-MySQL Connectivity cs 12

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

15-MySQL Connectivity cs 12

Uploaded by

Copyright:

Available Formats

Revised as

Computer Science per

UNIT-3: Database Management

Authored By:- Rajesh Kumar Mishra, PGT (Comp.Sc.)

Here ms is user given identifier to connection object

 server_name means database servername, generally it is

is_connected() function returns

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

Any type of DDL and DML query like Create, select,

 To extract data from cursor following functions are used:

 In this method in place of %s we will use {}

 INSERT and UPDATE operation are executed in the

Example : inserting data

AFTER PROGRAM EXECUTION

With INSERT INTO statement,

Note that here we have to use

You might also like