INTERFACEPYTHON

WITH 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, SQL Server, 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.
 Tobuild this bridge so that data can travel both ways we
need a connector called “[Link]”.
 We can install “[Link]” by using following
methods:
🞑 At command prompt (Administrator login)
◼ Type “pip install [Link]” and press enter
◼ (internet connection in required)
◼ This connector will work only for MySQL 5.7.3 or later
🞑Or open “
[Link]
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.
 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]
import [Link]

Or

import [Link] as ms

Here “ms” is an alias, so every time we can use “ms” in

place of “[Link]”
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 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

is_connected() function returns

true if connection is established
otherwise false

“mys” is an alias of package “[Link]”

“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 t 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 = [Link]()
🞑 For e.g.

🞑 mycursor = [Link]()

 TO EXECUTEQUERY
 We use execute() function to send query to connection
 Cursor_name.execute(query)
 For e.g.
 [Link]("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 use functions like fetchall(), fetchone(),
fetchmany() are used
Fetching(extracting) data from ResultSet

 To extract data from cursor following functionsare 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()
Example 2 – fetchall()
Example 3 – fetchall()
Example 4: fetchone()
Example 5: fetchmany(n)
Guess the output
Connecting with MySQL Database using
pymysql
PyMySQL is an interface for connecting to a MySQL database
server from Python. It implements the Python Database API
v2.0 and contains a pure-Python MySQL client
library.(available by Python)

MySQL is a leading open source database management

system. It is a multiuser, multithreaded database
management system. (available by Oracle)
 Steps to connect a python application
to our PyMySQL database
1. Start Python
2. Import pymysql module
import pymysql as pym
3. Create the connection object.
Pass the database details like HostName, username, and the database
password in the method call. The method returns the connection object.
• Syntax :
• ConnObject = [Link] (
“localhostname”, <username> , <password> ,<databasename>)
• Eg.-
• Mycon=[Link] ( “localhostname”, ”root” , ”root” “school”)
• Rest of the steps are same as steps of MySQL connector.
• Pymysql does not provide is_connected() method to test the connection.
Connecting to MySQL
The proper way to get an instance of this class is to call connect() method. This method
establishes a connection to the MySQL database and accepts several arguments:
Parameters :
•host – Host where the database server is located
•user – Username to log in as
•password – Password to use.
•database – Database to use, None to not use a particular one.
•port – MySQL port to use, default is usually OK. (default: 3306)

•con = [Link](host="localhost",user="root",password="root",db="xiia")

21
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 parameter inside {} so that while passing
values through format function we need not to
remember the order of value to pass. For e.g.
{roll},{name} etc.
String template with {} and format()
String template with {} and format()
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)
 [Link]()
 BEFORE PROGRAM EXECUTION

Example : inserting data

AFTER PROGRAM EXECUTION

Example: Updating record
USEFUL METHODS USED IN THE MYSQL-
PYTHON CONNECTIVITY PROGRAMS
What connect() method does in MySQL Python connectivity?
To create a connection between the MySQL database and the python application, the connect() method
of mysql. connector module is used. Pass the database details like HostName, username, and the
database password in the method call. The method returns the connection object.
What is cursor in MySQL Python connector?
The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to
communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch
data from the result sets, call procedures.
What does cursor execute do in Python?
A cursor is an object which helps to execute the query and fetch the records from the database.
What is the role of Execute () in MySQL Python connectivity?
This method executes the given database operation (query or command). The parameters found in the
tuple or dictionary are bound to the variables in the operation. NOTE: In Python, a tuple containing a single
value must include a comma.
What is commit () in Python?
The commit() method is used to make the database transactions. The commit() method is used to confirm
the changes made by the user to the database. Whenever any change is made to the database using
update or any other statements, it is necessary to commit the changes.
How do you close a connection and cursor in Python?
The close() method closes the cursor, resets all results, and ensures that the cursor object has no
reference to its original connection object.