New

syllabus
2022-23

Chapter 10
Interface python
with
SQL Database

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database
A database is nothing but an organized collection of data. Data is organized into rows,
columns and tables and it is indexed to make it easier to find relevant information. All
companies whether large or small use databases. So it become necessary to develop
project/software using any programming language like python in such a manner which
can interface with such databases which support [Link] form of Interface of
python with SQL Database can be understood with the help of this diagram.
Form/any user interface designed in any
programming language is Front End
where as data given by database as
response is known as Back-End
[Link] is just a query language, it is
not a database. To perform SQL queries,
we need to install any database for
example Oracle, MySQL, MongoDB,
PUossintGgrSesQSLQiLn, SaQnyL oSfertvheer,dDbBm2set,d. atabases and table can be created and data can be
accessed, updated and maintained. The Python standard for database interfaces is the
Python DB-API. Python Database API supports a wide range of database servers, like
msql , mysql, postgressql, Informix, oracle, Sybase etc.

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database

Why choose Python for database programming

Following are the reason to choose python for database
programming
• Programming more efficient and faster compared to other
languages.
• Portability of python programs.
• Support platform independent program development.
• Python supports SQLcursors.
• Python itself take care of open and close of connections.
• Python supports relational database systems.
• Porting of data from one dbms to other is easily possible as it
support large range of APIs for various databases.

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database

SQL Connectors
We must download a separate DB API module for each database
we need to access. Suppose we need to access an Oracle database
as well as a MySQL database, we must download both the Oracle
and the MySQL database modules .
The DB API provides a minimal standard for working with
databases using Python structures and syntax wherever possible.
This API includes the following −
● Importing the APImodule.
● Acquiring a connection with the database.
● Issuing SQL statements and stored procedures.
● Closing the connection

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database

Here we are using mysql as back end database because of it is open

source,free and portable and widely used. Any one of mysql-connector or
MySQLdb can be used for database programming.
1. mysql-connector
MySQL-Connector enables Python programs to access MySQL databases,
using an API that is compliant with the Python Database API Specification
v2.0 (PEP 249). It is written in pure Python and does not have any
dependencies except for the Python Standard Library.
Steps to use mysql-connector
1. Download Mysql API ,exe file and install it.(click here to download)
2. Install Mysql-Python Connector (Open command prompt and execute
command) >pip install mysql-connector
3. Now connect Mysql server using python
4. Write python statement in python shell import [Link]
If no error message is shown means mysql connector is properly installed

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database
2. MySQLdb
MySQLdb is an interface for connecting to a MySQL database server from
Python. It implements the Python Database API v2.0 and is built on top of
the MySQL CAPI.
Steps to use mysqlclient
1. First Upgrade pip command through > python –m pip install –upgrade
pip
2. Install mysqlclient through pip install mysqlclient
3. After successful installation check through import mysqldb
[Link] it is installed no error will be displayed otherwise error message will be displayed
To install MySQLdb module, use the following command −
For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gc c
For Python command prompt, use the following command -
pip install MySQL-python
Note − Make sure you have root privilege to install above module

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database
Establish connection
For database interface/database programming ,connection must be [Link]
establishing connection there must be mysql installed on the system and a database
and table is already [Link] following way we can establish a connection with mysql
database through [Link].
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root“,database=“school”)
print(mydb)
Alternatively we can write the following statement if we are using mysqldb
import MySQLdb
mydb = [Link]("localhost",“root",“root",“school" )
print(mydb)
In both way we are specifying host,user,password and database name as
[Link] is optional argument if we want to create database through
programming later on.
After successful execution of above statements in python following out will be
displayed
<[Link] object at 0x022624F0>
Otherwise an error message will be shown.
Mrs Namrata Shah, PGT(Computer
 Interface python
with SQL Database
Cursor object :
The MySQLCursor class instantiates objects that can execute operations such as SQL
statements. Cursor objects interact with the MySQL server using a MySQLConnection
object.
How to create cursor object and use it
import [Link]
mydb=[Link](host="localhost",user="root",passw="root"
)
mycursor=[Link]()
[Link]("create database if not exists school")
[Link]("show databases")
for x in mycursor:
print(x)
Through line 4 we are creating a database named school if it is already not created with
the help of cursor object.
Line 5 executes the sql query show databases and store result in mycursor as collection
,whose values are being fetched in x variable one by one.
On execution of above program school database is created and a list of available
databases is shown.

Mrs Namrata Shah, PGT(Computer

 Interface python
with SQL Database
How to create table at run time
Table creation is very easy ,if we are already well versed in sql table creation
then we have to just pass the create table query in execute() method of cursor
object. But before table creation we must open the [Link] we are
opening database school(through connect() method) before student table
creation.
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",
database="school")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary key,name
varchar(20),age int(2))")

On successful execution of above program a table named student with three

fields rollno,name,age will be created in school database.
We can check student table in mysql shell also,if required.

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database
How to change table structure/(add,edit,remove colum of a table)
at run time
To modify the structure of the table we just have to use alter table
[Link] program will add a column mark in the student table.
import [Link]
mydb=[Link](host="localhost",user="root",pass
wd="root",database="school")
mycursor=[Link]()
[Link]("alter table student add (marks int(3))")
[Link]("desc student")
for x in mycursor:
print(x)
Above program will add a column marks in the table student and
will display the structure of the table
Mrs Namrata Shah, PGT(Computer Science)
 Interface python
with SQL Database
How to insert record in a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",dat
abase="school")
mycursor=[Link]()
while 1==1:
ch=int(input("enter -1 to exit any other no to insert record into student
table"))
if ch==-1:
break
rollno=int(input("Enter rollno"))
class1=int(input("Enter Class"))
name=input("Enter name")
marks=int(input("Enter marks"))
[Link]("insert into student
values('"+str(rollno)+"','"+name+"','"+str(class1)+"','"+str(marks)+"')")
[Link]()

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database

How to search records of a table at run time

Below statement demonstrate the use of select query for searching
specific record from a table.
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
nm=input("enter name")
[Link]("select * from student where name='"+nm+"'")
for x in mycursor:
print (x)
Above statements will prompt a name from user,as user type the name
,that name is searched into the table student with the help of select
query .result will be shown with the help of mycursor collection.

Mrs Namrata Shah, PGT(Computer Science)

 Interface python
with SQL Database

How to fetch all records of a table at run time

import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("select * from student")
myrecords=[Link]()
for x in myrecords:
print (x)

[Link]() Method
The method fetches all (or all remaining) rows of a query result set and
returns a list of tuples. If no more rows are available, it returns an
empty list.
Mrs Namrata Shah, PGT(Computer Science)
 Interface python
with SQL Database
How to fetch one record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",database
="school")
mycursor=[Link]()
[Link]("select * from student")
row=[Link]()
while row is not None:
print(row)
row = [Link]()

[Link]() Method
This method retrieves the next row of a query result set and returns a single sequence,
or None if no more rows are available. By default, the returned tuple consists of data
returned by the MySQL server, converted to Python objects.
[Link]() Method
rows = [Link](size=1)
This method fetches the next set of rows of a query result and returns a list of tuples.
If no more rows are available, it returns an empty list.
Mrs Namrata Shah, PGT(Computer Science)
 Interface python
with SQL Database
rowcount : Rows affected by Query. We can get number of rows affected by the query
by using rowcount. We will use one SELECT query here.
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",database
="school")
mycursor=[Link]()
mycursor = [Link](buffered=True)
[Link]("select * from student")
noofrows=[Link]
print("No of rows in student table are",noofrows)

buffered=True
We have used my_cursor as buffered cursor.
my_cursor = my_connect.cursor(buffered=True)
This type cursor fetches rows and buffers them after getting output from MySQL
database. We can use such cursor as iterator. There is no point in using buffered cursor
for single fetching of [Link] we don’t use buffered cursor then we will get -1 as output
from rowcount
Mrs Namrata Shah, PGT(Computer
 Interface python
with SQL Database
How to delete record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",database
="school")
mycursor=[Link]()
[Link]("delete from student where rollno=1")
[Link]()
In above program delete query will delete a record with rollno=[Link]() method is
necessary to call for database transaction.
How to update record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd="root",database
="school")
mycursor=[Link]()
[Link]("update student set marks=99 where rollno=2")
[Link]()
In above program update query update the marks with 99 of rollno=2
Students are advised to develop menu driven program using above concepts for better
understating of python mysql database interface.
Mrs Namrata Shah, PGT(Computer
 Interface python
with SQL Database
Manage Database Transaction
try:
conn = [Link](host='localhost',database='school', user='root', password='root')
[Link] = false
cursor = [Link]()
sql_update_query = """Update student set marks = 95 where rollno = 2"""
[Link](sql_update_query)
print ("Record Updated successfully ")
#Commit your changes
[Link]()
except [Link] as error :
print("Failed to update record to database rollback: {}".format(error))
#reverting changes because of exception
[Link]()
finally:
#closing database connection.
if(conn.is_connected()):
[Link]()
[Link]()
print("connection is closed")
In above program if update query is successfully executed then commit() method will be executed otherwise
exception error part will be executed where revert of update query will be done due to [Link] finally we are
closing cursor as well as [Link] rollback or commit we have to set autocommit=false,just like
[Link] = false in above program otherwise rollback will not work
Mrs Namrata Shah, PGT(Computer Science)