SQL-Connectivity

INTERFACEPYTHONWITH
MYSQL
Connecting Python application with MySQL
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,
SQLServer, MySQL .
 In our syllabus we have to understand how to connect Python
programs with MySQL
Pre-requisite to connect Python with
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 “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 is required)
◼ Thisconnector will work only for MySQL 5.7.3 or later
Or open
“https://dev.mysql.com/downloads/connector/python/”
and download connector as per OS and Python version.
Connecting to MySQL from Python
 Once the connector is installed you are ready to

connect your python program to MySQL.
 Thefollowing steps to follow while connecting your
python program with MySQL
Open python
Import the package required (import mysql.connector)
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 mysql.connector
import mysql.connector
Or
import mysql.connector as ms
Here “ms” is an alias, soevery time we can use“ms” in place

of “mysql.connector”
Open a connection to MySQL
Database
 Tocreate connection, connect( ) function is used
 Its syntax is:
connect(host=<server_name>,user=<user_name>,
passwd=<password>[,database=<database>])
 Here server_name meansdatabase servername, generally it is

given as “localhost”
 User_name meansuser by which we connect with mysql
generally it is given as “root”
 Passwordis the password of user “root”
 Database is the name of database whosedata(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
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 the 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
❑ We use cursor( ) function to create a cursor
❑ Cursor_name = connectionObject.cursor( )
❑ For e.g.
mycursor = mycon.cursor( )
➢ TO EXECUTE QUERY
 We useexecute( ) function to send query to connection
 Cursor_name.execute(query)
 For e.g.
mycursor.execute(“select * from emp‟)

Example - Cursor
Output shows cursor is created and query is fired and stored, but no data is coming. To
fetch data we have to usefunctions like fetchall( ), fetchone( ), fetchmany( ) are used.
Mysql connector
mysql.connector as mys
mys
Fetchall( ) – method extracts all rows
INSERTING RECORDS
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 DML Commands -
insert or update or delete 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

RETRIEVING RECORDS
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 soon. 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()
Example 2 – fetchall()
Example 3 – fetchall()
Example 4: fetchone()
Example 5: fetchmany(n)
Guessthe output
SEARCHING RECORDS
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.
 ToCreate 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%sin 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
parameter inside { } sothat whilepassing
values through formatfunction we need not

remember the order of value to pass. to
{roll},{name} etc. For e.g.
and format( )
and format( )
UPDATING RECORDS
Example: Updating record
DELETING RECORDS

SQL-Connectivity

Uploaded by

Copyright:

Available Formats

SQL-Connectivity

Uploaded by

Document Information

Original Title

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

SQL-Connectivity

Uploaded by

Copyright:

Available Formats

INTERFACEPYTHONWITH

 Once the connector is installed you are ready to

Here “ms” is an alias, soevery time we can use“ms” in place

 Here server_name meansdatabase servername, generally it is

is_connected() function returns

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

mycursor.execute(“select * from emp‟)

Example : inserting data

AFTER PROGRAM EXECUTION

Concatenating dynamic variable to query in which

values through formatfunction we need not

You might also like