Grade 12 Exam  >  Grade 12 Notes  >  Computer Science for Grade 12  >  Chapter Notes: Interface Python with SQL Database

Interface Python with SQL Database Chapter Notes | Computer Science for Grade 12 PDF Download

Download, print and study this document offline
Please wait while the PDF view is loading
 Page 1


               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python code with SQL queries 
As we know, the method cursor () is used to create a cursor instance/ object, 
after establishing the connection between Python and MySQL database. The 
database cursor is a control structure, which enables us to make traversal over 
the records / rows in a database. A cursor can be treated a pointer to the first row 
/ tuple of the set of the records stored in the database. Like a file pointer which is 
pointing to current record / object of the file, moves automatically to next record 
and finally reaches the end of the file, the cursor points to the first row, then 
automatically travels to other rows of the database and finally reaches end of the 
table. Thus, the cursor facilitates retrieve, addition, updating and deletion of 
database records. 
Once we have created a cursor instance /object, we can execute various types of 
SQL queries using the method execute() to manipulate the records of the 
database linked with the Python. 
Let us consider the following relation RESULT is already created in the database 
“AECS”, which is connected with the Python. 
RollNo Name Class Subject DOE Marks 
1011 Ramesh XII-A Computer 14-03-2020 98 
1013 Harsha XII-B Physics  12-03-2020 95 
1016 Yogesh XII-B Chemistry 09-03-2020 96 
1018 Girija XII-C Accountancy 14-03-2020 100 
1014 Jevan XII-A Mathematics 18-03-2020 99 
1015 Anju XII-C Economics 12-03-2020 94 
1016 Kishore XII-B Biology 16-03-2020 96 
1017 Kiran XII-C English 06-03-2020 91 
1020 Kirutika XII-A Computer 14-03-2020 100 
1012 Arvind XII-B Mathematics 18-03-2020 96 
 
Page 2


               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python code with SQL queries 
As we know, the method cursor () is used to create a cursor instance/ object, 
after establishing the connection between Python and MySQL database. The 
database cursor is a control structure, which enables us to make traversal over 
the records / rows in a database. A cursor can be treated a pointer to the first row 
/ tuple of the set of the records stored in the database. Like a file pointer which is 
pointing to current record / object of the file, moves automatically to next record 
and finally reaches the end of the file, the cursor points to the first row, then 
automatically travels to other rows of the database and finally reaches end of the 
table. Thus, the cursor facilitates retrieve, addition, updating and deletion of 
database records. 
Once we have created a cursor instance /object, we can execute various types of 
SQL queries using the method execute() to manipulate the records of the 
database linked with the Python. 
Let us consider the following relation RESULT is already created in the database 
“AECS”, which is connected with the Python. 
RollNo Name Class Subject DOE Marks 
1011 Ramesh XII-A Computer 14-03-2020 98 
1013 Harsha XII-B Physics  12-03-2020 95 
1016 Yogesh XII-B Chemistry 09-03-2020 96 
1018 Girija XII-C Accountancy 14-03-2020 100 
1014 Jevan XII-A Mathematics 18-03-2020 99 
1015 Anju XII-C Economics 12-03-2020 94 
1016 Kishore XII-B Biology 16-03-2020 96 
1017 Kiran XII-C English 06-03-2020 91 
1020 Kirutika XII-A Computer 14-03-2020 100 
1012 Arvind XII-B Mathematics 18-03-2020 96 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 1 
#Python code to retrieve all the rows from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
 
#Interface MySQL database with the Python 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRows = CurObj.fetchall()    #Fetch all the rows from the database 
NumRows = CurObj.rowcount    #Find number of rows   
print(“Number of records retrieved from the table : “, NumRows); 
#To display all the records  
for row in DataRows: 
 print (row)  #Display each record 
MyCon.close()   #Terminate the connection 
 
OUTPUT  
MySQL database is successfully connected 
Number of records retrieved from the table : 10 
Then all the rows will be displayed 
 
Page 3


               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python code with SQL queries 
As we know, the method cursor () is used to create a cursor instance/ object, 
after establishing the connection between Python and MySQL database. The 
database cursor is a control structure, which enables us to make traversal over 
the records / rows in a database. A cursor can be treated a pointer to the first row 
/ tuple of the set of the records stored in the database. Like a file pointer which is 
pointing to current record / object of the file, moves automatically to next record 
and finally reaches the end of the file, the cursor points to the first row, then 
automatically travels to other rows of the database and finally reaches end of the 
table. Thus, the cursor facilitates retrieve, addition, updating and deletion of 
database records. 
Once we have created a cursor instance /object, we can execute various types of 
SQL queries using the method execute() to manipulate the records of the 
database linked with the Python. 
Let us consider the following relation RESULT is already created in the database 
“AECS”, which is connected with the Python. 
RollNo Name Class Subject DOE Marks 
1011 Ramesh XII-A Computer 14-03-2020 98 
1013 Harsha XII-B Physics  12-03-2020 95 
1016 Yogesh XII-B Chemistry 09-03-2020 96 
1018 Girija XII-C Accountancy 14-03-2020 100 
1014 Jevan XII-A Mathematics 18-03-2020 99 
1015 Anju XII-C Economics 12-03-2020 94 
1016 Kishore XII-B Biology 16-03-2020 96 
1017 Kiran XII-C English 06-03-2020 91 
1020 Kirutika XII-A Computer 14-03-2020 100 
1012 Arvind XII-B Mathematics 18-03-2020 96 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 1 
#Python code to retrieve all the rows from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
 
#Interface MySQL database with the Python 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRows = CurObj.fetchall()    #Fetch all the rows from the database 
NumRows = CurObj.rowcount    #Find number of rows   
print(“Number of records retrieved from the table : “, NumRows); 
#To display all the records  
for row in DataRows: 
 print (row)  #Display each record 
MyCon.close()   #Terminate the connection 
 
OUTPUT  
MySQL database is successfully connected 
Number of records retrieved from the table : 10 
Then all the rows will be displayed 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 2 
#Alternate Python code to retrieve one record at a time 
#from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRow = CurObj.fetchone() # fetch each record 
while  DataRow is not None: 
 print (DataRow) 
          DataRow = CurObj.fetchone() 
NumRows = CurObj.rowcount 
print(“Number of records retrieved from the table : “, NumRows); 
MyCon.close() 
 
OUTPUT  
MySQL database is successfully connected 
First, all the rows will be displayed 
Then,  
Number of records retrieved from the table : 10 will be displayed 
 
Page 4


               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python code with SQL queries 
As we know, the method cursor () is used to create a cursor instance/ object, 
after establishing the connection between Python and MySQL database. The 
database cursor is a control structure, which enables us to make traversal over 
the records / rows in a database. A cursor can be treated a pointer to the first row 
/ tuple of the set of the records stored in the database. Like a file pointer which is 
pointing to current record / object of the file, moves automatically to next record 
and finally reaches the end of the file, the cursor points to the first row, then 
automatically travels to other rows of the database and finally reaches end of the 
table. Thus, the cursor facilitates retrieve, addition, updating and deletion of 
database records. 
Once we have created a cursor instance /object, we can execute various types of 
SQL queries using the method execute() to manipulate the records of the 
database linked with the Python. 
Let us consider the following relation RESULT is already created in the database 
“AECS”, which is connected with the Python. 
RollNo Name Class Subject DOE Marks 
1011 Ramesh XII-A Computer 14-03-2020 98 
1013 Harsha XII-B Physics  12-03-2020 95 
1016 Yogesh XII-B Chemistry 09-03-2020 96 
1018 Girija XII-C Accountancy 14-03-2020 100 
1014 Jevan XII-A Mathematics 18-03-2020 99 
1015 Anju XII-C Economics 12-03-2020 94 
1016 Kishore XII-B Biology 16-03-2020 96 
1017 Kiran XII-C English 06-03-2020 91 
1020 Kirutika XII-A Computer 14-03-2020 100 
1012 Arvind XII-B Mathematics 18-03-2020 96 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 1 
#Python code to retrieve all the rows from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
 
#Interface MySQL database with the Python 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRows = CurObj.fetchall()    #Fetch all the rows from the database 
NumRows = CurObj.rowcount    #Find number of rows   
print(“Number of records retrieved from the table : “, NumRows); 
#To display all the records  
for row in DataRows: 
 print (row)  #Display each record 
MyCon.close()   #Terminate the connection 
 
OUTPUT  
MySQL database is successfully connected 
Number of records retrieved from the table : 10 
Then all the rows will be displayed 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 2 
#Alternate Python code to retrieve one record at a time 
#from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRow = CurObj.fetchone() # fetch each record 
while  DataRow is not None: 
 print (DataRow) 
          DataRow = CurObj.fetchone() 
NumRows = CurObj.rowcount 
print(“Number of records retrieved from the table : “, NumRows); 
MyCon.close() 
 
OUTPUT  
MySQL database is successfully connected 
First, all the rows will be displayed 
Then,  
Number of records retrieved from the table : 10 will be displayed 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 3 
#Python code to retrieve all the record and display few columns 
#from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector  
conn = mysql.connector.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if conn.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
cob = conn.cursor() 
cob.execute(“select * from result”) 
 
dr = cob.fetchall()    #Fetch all the rows from the database 
print(“Number of records retrieved from the table : “, cob.rowcount); 
 
#To display all the records  
for row in dr: 
 rno =   row[0] #Assign RollNo  
 nam = row[1]   #Assign Name 
 sub =  row[3]    #Assign Subject 
 mark= row[5]    #Assign Marks   
 print (‘%-6d %-15s% %-15s %-6d’%(rno, nam, sub, mark))  #Display  
conn.close()   #Terminate the connection 
 
 
Page 5


               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python code with SQL queries 
As we know, the method cursor () is used to create a cursor instance/ object, 
after establishing the connection between Python and MySQL database. The 
database cursor is a control structure, which enables us to make traversal over 
the records / rows in a database. A cursor can be treated a pointer to the first row 
/ tuple of the set of the records stored in the database. Like a file pointer which is 
pointing to current record / object of the file, moves automatically to next record 
and finally reaches the end of the file, the cursor points to the first row, then 
automatically travels to other rows of the database and finally reaches end of the 
table. Thus, the cursor facilitates retrieve, addition, updating and deletion of 
database records. 
Once we have created a cursor instance /object, we can execute various types of 
SQL queries using the method execute() to manipulate the records of the 
database linked with the Python. 
Let us consider the following relation RESULT is already created in the database 
“AECS”, which is connected with the Python. 
RollNo Name Class Subject DOE Marks 
1011 Ramesh XII-A Computer 14-03-2020 98 
1013 Harsha XII-B Physics  12-03-2020 95 
1016 Yogesh XII-B Chemistry 09-03-2020 96 
1018 Girija XII-C Accountancy 14-03-2020 100 
1014 Jevan XII-A Mathematics 18-03-2020 99 
1015 Anju XII-C Economics 12-03-2020 94 
1016 Kishore XII-B Biology 16-03-2020 96 
1017 Kiran XII-C English 06-03-2020 91 
1020 Kirutika XII-A Computer 14-03-2020 100 
1012 Arvind XII-B Mathematics 18-03-2020 96 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 1 
#Python code to retrieve all the rows from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
 
#Interface MySQL database with the Python 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRows = CurObj.fetchall()    #Fetch all the rows from the database 
NumRows = CurObj.rowcount    #Find number of rows   
print(“Number of records retrieved from the table : “, NumRows); 
#To display all the records  
for row in DataRows: 
 print (row)  #Display each record 
MyCon.close()   #Terminate the connection 
 
OUTPUT  
MySQL database is successfully connected 
Number of records retrieved from the table : 10 
Then all the rows will be displayed 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 2 
#Alternate Python code to retrieve one record at a time 
#from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector as SqlCon 
MYCon = SqlCon.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if MyCon.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
CurObj = MyCon.cursor() 
CurObj.execute(“select * from result”) 
 
DataRow = CurObj.fetchone() # fetch each record 
while  DataRow is not None: 
 print (DataRow) 
          DataRow = CurObj.fetchone() 
NumRows = CurObj.rowcount 
print(“Number of records retrieved from the table : “, NumRows); 
MyCon.close() 
 
OUTPUT  
MySQL database is successfully connected 
First, all the rows will be displayed 
Then,  
Number of records retrieved from the table : 10 will be displayed 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
Python Code – 3 
#Python code to retrieve all the record and display few columns 
#from the relation RESULT 
#Import the package mysql.connector  
import mysql.connector  
conn = mysql.connector.connect(host=”localhost”, user=”root”, passwd=”gms”, 
database = “AECS”) 
if conn.is_connected() : 
print(“MySQL database is successfully connected”) 
else: 
print(“Error in connecting to MySQL database”) 
 
#Create cursor instance and execute SQL query 
cob = conn.cursor() 
cob.execute(“select * from result”) 
 
dr = cob.fetchall()    #Fetch all the rows from the database 
print(“Number of records retrieved from the table : “, cob.rowcount); 
 
#To display all the records  
for row in dr: 
 rno =   row[0] #Assign RollNo  
 nam = row[1]   #Assign Name 
 sub =  row[3]    #Assign Subject 
 mark= row[5]    #Assign Marks   
 print (‘%-6d %-15s% %-15s %-6d’%(rno, nam, sub, mark))  #Display  
conn.close()   #Terminate the connection 
 
 
               Computer Science  
3.6 Interface of Python with an SQL database   (2/2)  
 
        
 
 
OUTPUT  
MySQL database is successfully connected 
First,   
Number of records retrieved from the table : 10 will be displayed 
Then, 
RollNo, Name, Subject and Marks of each student will be displayed 
 
Deleting rows from a relation 
The Roll number of a student can be supplied through the keyboard while 
running the Python code. Further, a function definition Delete_Rows(rno) can 
also be used to delete a record of the student whose Roll number is passed as 
an argument to the function. The SQL command delete can be used with format 
as given below. 
 delete from result where rollno = ‘%d’ 
Here, ‘%d’ represents an integer(rollno of the student), which is supplied as an 
argument to the command. After deleting rows from the table, we can save the 
changes in the database by using commit method and if there is an error, we can 
un-save the changes by using rollback method as depicted below. 
 conn.commit() 
 conn.rollback()   
Python Code – 4 
#Python code to delete a record / row from the relation RESULT 
import  mysql.connector as sqcon 
 
#Function to delete a row from the relation 
def Delete_Rows(rno): 
conn = sqcon.connector.connect(host=”localhost”, user=”root”, 
passwd=”gms”, database = “AECS”) 
 
Read More
1 videos|25 docs|18 tests

Top Courses for Grade 12

1 videos|25 docs|18 tests
Download as PDF
Explore Courses for Grade 12 exam

Top Courses for Grade 12

Signup for Free!
Signup to see your scores go up within 7 days! Learn & Practice with 1000+ FREE Notes, Videos & Tests.
10M+ students study on EduRev
Related Searches

Important questions

,

study material

,

Interface Python with SQL Database Chapter Notes | Computer Science for Grade 12

,

Objective type Questions

,

ppt

,

Semester Notes

,

Extra Questions

,

shortcuts and tricks

,

pdf

,

Interface Python with SQL Database Chapter Notes | Computer Science for Grade 12

,

Exam

,

mock tests for examination

,

Free

,

Sample Paper

,

Viva Questions

,

practice quizzes

,

video lectures

,

Summary

,

past year papers

,

MCQs

,

Interface Python with SQL Database Chapter Notes | Computer Science for Grade 12

,

Previous Year Questions with Solutions

;