Interface Python with
MySql
• When you design real life application, you are bound to encounter
situation wherein you need to manipulate data stored in a database
through an application designed by you.
• Python provides [Link] library for database connectivity.
• In order to establish connection to a database , we need to import the
mysql connector library in our python program.
• Steps for creating database connectivity applications:
1. Start python
2. Import the packages required for database programming.
3. Open a connection to database.
4. Create a cursor instance.
5. Execute a query
6. Extract data from result set.
7. Clean up the environment.
• Step 1:
• Start python editor where you can create python script.
• Eg: python IDLE, Spyder IDE etc.
• Step 2:
• Import [Link] package in the python script.
• import [Link]
OR
import [Link] as sqLtor
You can also use pymysql for connecting with MySQL database.
• Step 3:
• Open a connection to MySQL database.
• connect() function of [Link] establishes connection to a
MySQL database with 4 paramaters.
Syntax:
<connection_object> = [Link] (host = <hostname>,
user = <username>,
passwd =<password>,
[, database = <database>)
• Eg: mycon = [Link](host = “localhost”, user = “root”, passwd =
“Mypass”, database = “test”)
Mycon is the connection object name
host is the database server hostname or IP address. – localhost
user is the user name on MySQL.
passwd is the password of mysql
database is the name of the database to be accessed. (optional)
• If python does not report an errors, it means you have successfully
established the connection.
• is_connected() is a function used for checking the connectivity,
returns true if connection is successful.
• The entire code for establishing connection:
import [Link] as sqLtor
mycon = [Link](host = “localhost”, user = “root”, passwd =
“admin”, database = “School”)
if mycon.is_connected():
print(‘Successfully connected to mysql database’)
• Step 4:
• Create a cursor instance:
• Cursor is a temporary memory or temporary workstation.
• It helps in execution of SQL commands in a python program.
• It act as middleware between python file and database
• After python-database connectivity, the queries are send to the db
server and it gets executed.
• The result set of the query is send back to the program in one go.
• But if we want to access the retrieved data one row at a time, database
cursor can be used.
• Database cursor is a special control structure that facilitates the row by
row processing of records in the result set.
• Use cursor() function for creating a cursor instance.
• <cursor_object> = <connectionobject>.cursor()
Eg: cursor = [Link]()
• Step 5:
• Execute SQL Query: using execute() function
• <cursorobject>.execute(<sql query string>)
• Eg: [Link](“select * from student”)
• This will execute the query and store the retrieved records (resultset) in
cursor object, which can be used in the program as required.
Step 6:
• Extract data from resultset:
• Result refers to a logical set of records that are fetched from the
database by executing an SQL query and made available to the
application program.
• For individual row processing , the data has to be extracted from the
result set.
• For this we can use fetch() function:
1. fetchall()
2. fetchone()
3. fetchmany()
• fetchall():
• It will return all the records retrieved as per query in the form of list of tuples. (list of
records)
• Returns empty list if no records are fetched
<data> = <cursor>.fetchall()
• fetchone():
• It will return one record from the result set in the form of tuple/ list.
• Returns None if no records are fetched
<data> = <cursor>.fetchone()
• fetchmany():
• It will return the mentioned number of records in the form of list of tuple.
• Returns empty list if no records are fetched
• rowcount is a property of cursor which returns the number of rows
retrieved from the cursor so far.
<variable> = <cursor>.rowcount
Eg: consider student table from school database:
Rollno Name Marks Section Project
101 Ruhani 76.80 A Pending
102 George 71.20 A Submitted
103 Simran 81.20 B Evaluated
104 Ali 61.20 C Assigned
105 Kushal 51.60 C Evaluated
106 arshiya 91.60 B Submitted
import [Link] as sql
mycon = [Link](host = “localhost’, user = “root”, passwd =
“admin”, database = “school”)
If mycon.is_connected():
print(“Connection successful”)
cursor = [Link]()
[Link](“select * from student”)
data = [Link]() #data is a tuple of records
count = [Link]
print(“total no. of rows retrieved : ”, count)
for row in data:
print(row) #print each row content in form of tuple
• Output:
Total no. of rows retrieved : 6
(101, ‘Ruhani’, Decimal(76.80), ‘A’, ‘Pending’)
(102, ‘George’, Decimal(71.20), ‘A’, ‘Submitted’)
(103, ‘Simran’, Decimal(81.20), ‘B’, ‘Evaluated’)
(104, ‘Ali’, Decimal(61.20), ‘C’, ‘Assigned’)
(105, ‘Kushal’, Decimal(51.20), ‘C, ‘Evaluated’)
(106, ‘Arshiya’, Decimal(91.20), ‘B’, ‘Submitted’)
import [Link] as sql
mycon = [Link](host = “localhost’, user = “root”, passwd =
“admin”, database = “school”)
If mycon.is_connected():
print(“Connection successful”)
cursor = [Link]()
[Link](“select * from student”)
data = [Link](3) #data is a tuple of records
count = [Link]
print(“total no. of rows retrieved : ”, count)
for row in data:
print(row) #print each row content in form of tuple
• Output:
total no. of rows retrieved : 3
(101, ‘Ruhani’, Decimal(76.80), ‘A’, ‘Pending’)
(102, ‘George’, Decimal(71.20), ‘A’, ‘Submitted’)
(103, ‘Simran’, Decimal(81.20), ‘B’, ‘Evaluated’)
import [Link] as sql
mycon = [Link](host = “localhost’, user = “root”, passwd =
“admin”, database = “school”)
If mycon.is_connected():
print(“Connection successful”)
cursor = [Link]()
[Link](“select * from student”)
data = [Link]() #fetch 1 record in the resultset
count = [Link] #returns total number of rows retrieved so far.
print(“total no. of rows retrieved: “)
data = [Link]() #fetch 1 more record in the resultset
count = [Link]
print(“total no. of rows retrieved: “)
• Output:
total no. of rows retrieved : 1
(101, ‘Ruhani’, Decimal(76.80), ‘A’, ‘Pending’)
Total no. of rows retrieved : 2
(102, ‘George’, Decimal(71.20), ‘A’, ‘Submitted’)
import [Link] as sql
mycon = [Link](host = “localhost’, user = “root”, passwd = “admin”,
database = “school”)
If mycon.is_connected():
print(“Connection successful”)
cursor = [Link]()
[Link](“select * from student”)
data = [Link]()
count = [Link]
print(“total no. of rows retrieved : ”, count)
data = [Link]()
count = [Link]
print(“total no. of rows retrieved : ”, count)
data = [Link](3) #data is a tuple of records
count = [Link]
print(“total no. of rows retrieved : ”, count)
Step 7:
• Clean up the environment.
• Close the connection after all the processing is done.
• <[Link]>.close()
• Eg: [Link]()
Q. Write a python program that displays first three rows fetched from
student table of mysql database “test”.
• To form parameterized query strings:
• Instead of giving query directly inside execute(), Store sql query as a
string with the following format.
• Eg1: st = “select * from student where mark > { }”.format(70)
[Link](st)
Eg2: st2 = “select * from student where class = { } and section = ‘{ }’ ”.
format(10, ‘A’)
[Link](st2)
• Insert, update and delete queries:
• Similar to SELECT query, store the query in a string as a simple query
or as parameterized query and execute it using [Link]().
• Unlike SELECT, you must commit your query after executing INSERT,
UPDATE and DELETE.
• <connectionobject>.commit()
• INSERT:
• st = “INSERT INTO student VALUES ({}, ‘{}’, {}, ‘{}’ )”.format(101,
‘Maya’,10, ‘A’)
[Link](st)
[Link]()
• UPDATE:
st = “update student set mark = {} where sid = {}”.format(90, 104)
[Link](st)
[Link]()
DELETE:
[Link](“delete from student”)
[Link]()
• Write a python program to accept student details(Admno, Name,
Class, Section) from user and insert a record with these details into
table name ‘Student’, in School database.
• Host – localhost , User – root, password- admin