US06CBCA52: Python Programming BCA Sem 6

Topic Covers:
File Handling:
 Introduction,
 Create,
 Read,
 Write
 Delete File
Database connection using MYSQL,
 Creating,
 Searching and Drop Tables,
 Record Manipulation:
o Select,
o Insert,
o Update,
o Delete,
o Searching,
o Sorting,
o Join

 File Handling
 The key function for working with files in Python is the open() function.
 The open() function takes two parameters; filename, and mode.
 There are four different methods (modes) for opening a file:

"r" Read Default value. Opens a file for reading, error if the file does not exist
"a" Append Opens a file for appending, creates the file if it does not exist
"w" Write Opens a file for writing, creates the file if it does not exist
"x" Create Creates the specified file, returns an error if the file exists
In addition you can specify if the file should be handled as binary or text mode
"t" Text Default value. Text mode
"b" Binary Binary mode (e.g. images)

Syntax

File IO Management and Databases 1

US06CBCA52: Python Programming BCA Sem 6

To open a file for reading it is enough to specify the name of the file: f
= open("[Link]")

The code above is the same as:

f = open("[Link]", "rt")

Because "r" for read, and "t" for text are the default values, you do not need to specify
them.

Note: Make sure the file exists, or else you will get an error.

 Python File Open

 Open a File on the Server

Assume we have the following file, located in the same folder as Python:
[Link]

Hello! Welcome to
[Link] This file is
for testing purposes.
Good Luck!

To open the file, use the built-in open() function.

The open() function returns a file object, which has a read() method for reading the content
of the file:

Example
f = open("[Link]", "r")
print([Link]())

 Read Only Parts of the File

By default the read() method returns the whole text, but you can also specify
how many character you want to return:

Example
Return the 5 first characters of the file:

f = open("[Link]", "r")
print([Link](5))

 Read Lines
You can return one line by using the readline() method:

Example

File IO Management and Databases 2

US06CBCA52: Python Programming BCA Sem 6

Read one line of the file:

f = open("[Link]", "r")
print([Link]())

By calling readline() two times, you can read the two first lines

Example
Read two lines of the file:

f = open("[Link]", "r")
print([Link]())
print([Link]())

By looping through the lines of the file, you can read the whole file, line by line:

 Python File Write

 Write to an Existing File:

To write to an existing file, you must add a parameter to the open() function:

"a" Append will append to the end of the file

"w" Write will overwrite any existing content

Example
Open the file "[Link]" and append content to the file:

f = open("[Link]", "a")
[Link]("Now the file has one more line!")

Example
Open the file "[Link]" and overwrite the content:

f = open("[Link]", "w") [Link]("Woops! I have deleted

the content!")

Note: the "w" method will overwrite the entire file.

 Create a New File

To create a new file in Python, use the open() method, with one of the following
parameters:

"x" Create will create a file, returns an error if

the file exist
"a" Append will create a file if the specified file
does not exist
"w" Write will create a file if the specified file
does not exist

File IO Management and Databases 3

US06CBCA52: Python Programming BCA Sem 6

Example
Create a file called "[Link]":

f = open("[Link]", "x")

Result: a new empty file is created!

Example
Create a new file if it does not exist:

f = open ("[Link]", "w")

Example
Loop through the file line by line:

f=open ("[Link]", "r")

for x in f:
print(x)

 Python Delete File

 Delete a File
To delete a file, you must import the OS module, and run its [Link]() function:

Example
Remove the file "[Link]":

import os
[Link]("[Link]")

 Check if File exist:

To avoid getting an error, you might want to check if the file exist before you try to
delete it:

Example
Check if file exist, then delete it:

import os
if [Link]("[Link]"):
[Link]("[Link]")
else:
print("The file does not exist")

 Delete Folder
To delete an entire folder, use the [Link]() method:

Example

File IO Management and Databases 4

US06CBCA52: Python Programming BCA Sem 6

Remove the folder "myfolder":

import os
[Link]("myfolder")

Note: You can only remove empty folders.

 Environment Setup
To build the real world applications, connecting with the databases is the necessity for the
programming languages. However, python allows us to connect our application to the
databases like MySQL, SQLite, MongoDB, and many others.

In this section of the tutorial, we will discuss Python - MySQL connectivity, and we will
perform the database operations in python. We will also cover the Python connectivity with
the databases like MongoDB and SQLite later in this tutorial.

 Install [Link]

To connect the python application with the MySQL database, we must import the
[Link] module in the program.

The [Link] is not a built-in module that comes with the python installation. We need
to install it to get it working.

Execute the following command to install it using pip installer.

> python -m pip install mysql-connector

Or follow the following steps.

1. Click the link:

[Link]
d5411298bcacbd309f96/[Link] to download the source code.

2. Extract the archived file.

3. Open the terminal (CMD for windows) and change the present working directory to the
source code directory.

$ cd mysql-connector-python-8.0.13/

4. Run the file named [Link] with python (python3 in case you have also installed python 2)
with the parameter build.

File IO Management and Databases 5

US06CBCA52: Python Programming BCA Sem 6

$ python [Link] build

5. Run the following command to install the mysql-connector.

$ python [Link] install

This will take a bit of time to install mysql-connector for python. We can verify the installation
once the process gets over by importing mysql-connector on the python shell.

Hence, we have successfully installed mysql-connector for python on our system.

 Database Connection
In this section of the tutorial, we will discuss the steps to connect the python application to the
database.

There are the following steps to connect a python application to our database.

1. Import [Link] module

2. Create the connection object.
3. Create the cursor object
4. Execute the query

 Creating the connection

To create a connection between the MySQL database and the python application, the connect()
method of [Link] module is used.

Pass the database details like HostName, username, and the database password in the method
call. The method returns the connection object.

The syntax to use the connect() is given below.

ConnectionObject= [Link](host = <hostname> , user = <username> ,

passwd = <password> )

Consider the following example.

File IO Management and Databases 6

US06CBCA52: Python Programming BCA Sem 6

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "")

#printing the connection object

print(myconn)

Output:

<[Link] object at 0x7fb142edd780>

Here, we must notice that we can specify the database name in the connect() method if we want
to connect to a specific database.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "mydb")

#printing the connection object

print(myconn)

Output:

<[Link] object at 0x7ff64aa3d7b8>

 Creating a cursor object

The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It
facilitates us to have multiple separate working environments through the same connection to
the database. We can create the cursor object by calling the 'cursor' function of the connection
object. The cursor object is an important aspect of executing queries to the databases.

The syntax to create the cursor object is given below.

<my_cur> = [Link]()
Example
import [Link]
#Create the connection object
myconn = [Link](host = "localhost", user = "root",passwd = “”,
database = "mydb")

#printing the connection object

File IO Management and Databases 7

US06CBCA52: Python Programming BCA Sem 6

print(myconn)

#creating the cursor object

cur = [Link]()

print(cur)

Output:

<[Link] object at 0x7faa17a15748>

MySQLCursor: (Nothing executed yet)
 Creating new databases
In this section of the tutorial, we will create the new database PythonDB.

 Getting the list of existing databases

We can get the list of all the databases by using the following MySQL query.

> show databases;

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "")

#creating the cursor object

cur = [Link]()

try:
dbs = [Link]("show databases")
except:
[Link]()
for x in cur:
print(x)
[Link]()

Output:

('EmployeeDB',)
('Test',)
('TestDB',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)

File IO Management and Databases 8

US06CBCA52: Python Programming BCA Sem 6

('mysql',)
('performance_schema',)
('testDB',)
 Creating the new database
The new database can be created by using the following SQL query.

> create database <database-name>

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "")

#creating the cursor object

cur = [Link]()

try:
#creating a new database
[Link]("create database PythonDB2")

#getting the list of all the databases which will now include the new database PythonDB
dbs = [Link]("show databases")

except:
[Link]()

for x in cur:
print(x)

[Link]()

Output:

('EmployeeDB',)
('PythonDB',)
('Test',)
('TestDB',)
('anshika',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)

 Creating the table

File IO Management and Databases 9

US06CBCA52: Python Programming BCA Sem 6

In this section of the tutorial, we will create the new table Employee. We have to mention the
database name while establishing the connection object.

We can create the new table by using the CREATE TABLE statement of SQL. In our database
PythonDB, the table Employee will have the four columns, i.e., name, id, salary, and
department_id initially.

The following query is used to create the new table Employee.

> create table Employee (name varchar(20) not null, id int primary key, salary float not n
ull, Dept_Id int not null)

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",
passwd = "",database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Creating a table with name Employee having four columns i.e., name, id, salary, and
department id
dbs = [Link]("create table Employee(name varchar(20) not null, id int(20) not null
primary key, salary float not null, Dept_id int not null)")
except:
[Link]()
[Link]()

File IO Management and Databases 10

US06CBCA52: Python Programming BCA Sem 6

Now, we may check that the table Employee is present in the database.

Alter Table
Sometimes, we may forget to create some columns, or we may need to update the table schema.
The alter statement used to alter the table schema if required. Here, we will add the column
branch_name to the table Employee. The following SQL query is used for this purpose.

alter table Employee add branch_name varchar(20) not null

Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#adding a column branch name to the table Employee
[Link]("alter table Employee add branch_name varchar(20) not null")
except:
[Link]()

File IO Management and Databases 11

US06CBCA52: Python Programming BCA Sem 6

[Link]()

 Insert Operation
 Adding a record to the table

The INSERT INTO statement is used to add a record to the table. In python, we can mention
the format specifier (%s) in place of values.

We provide the actual values in the form of tuple in the execute() method of the cursor.

Consider the following example.

Example
import [Link]
#Create the connection object
myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")
#creating the cursor object
cur = [Link]()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s,
%s, %s)"

#The row values are provided in the form of tuple

val = ("John", 110, 25000.00, 201, "Newyork")

try:
#inserting the values into the table

File IO Management and Databases 12

US06CBCA52: Python Programming BCA Sem 6

[Link](sql,val)

#commit the transaction

[Link]()

except:
[Link]()

print([Link],"record inserted!")
[Link]()

Output:

1 record inserted!

 Insert multiple rows

We can also insert multiple rows at once using the python script. The multiple rows are
mentioned as the list of various tuples.

Each element of the list is treated as one particular row, whereas each element of the tuple is
treated as one particular column value (attribute).

Consider the following example.

Example
import [Link]

File IO Management and Databases 13

US06CBCA52: Python Programming BCA Sem 6

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s,
%s)"
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),
("Nick",104,90000.00,201,"Newyork")]

try:
#inserting the values into the table
[Link](sql,val)

#commit the transaction

[Link]()
print([Link],"records inserted!")

except:
[Link]()

[Link]()

Output:

3 records inserted!

File IO Management and Databases 14

US06CBCA52: Python Programming BCA Sem 6

 Row ID
In SQL, a particular row is represented by an insertion id which is known as row id. We can
get the last inserted row id by using the attribute lastrowid of the cursor object.

Consider the following example.

Example
import [Link]
#Create the connection object
myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")
#creating the cursor object
cur = [Link]()

sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s,
%s, %s)"

val = ("Mike",105,28000,202,"Guyana")

try:
#inserting the values into the table
[Link](sql,val)

#commit the transaction

[Link]()

#getting rowid
print([Link],"record inserted! id:",[Link])

except:
[Link]()

[Link]()

Output:

1 record inserted! Id: 0

 Read Operation
The SELECT statement is used to read the values from the databases. We can restrict the output
of a select query by using various clause in SQL like where, limit, etc.

Python provides the fetchall() method returns the data stored inside the table in the form of
rows. We can iterate the result to get the individual rows.

File IO Management and Databases 15

US06CBCA52: Python Programming BCA Sem 6

In this section of the tutorial, we will extract the data from the database by using the python
script. We will also format the output to print it on the console.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select * from Employee")

#fetching the rows from the cursor object

result = [Link]()
#printing the result

for x in result:
print(x);
except:
[Link]()

[Link]()

Output:

('John', 101, 25000.0, 201, 'Newyork')

('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')

 Reading specific columns

We can read the specific columns by mentioning their names instead of using star (*).

In the following example, we will read the name, id, and salary from the Employee table and
print it on the console.

Example
import [Link]
#Create the connection object
myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")
#creating the cursor object
cur = [Link]()

File IO Management and Databases 16

US06CBCA52: Python Programming BCA Sem 6

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee")

#fetching the rows from the cursor object

result = [Link]()
#printing the result
for x in result:
print(x);
except:
[Link]()
[Link]()

Output:

('John', 101, 25000.0)

('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)

 The fetchone() method

The fetchone() method is used to fetch only one row from the table. The fetchone() method
returns the next row of the result-set.

Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee")

#fetching the first row from the cursor object

result = [Link]()

#printing the result

print(result)

except:

File IO Management and Databases 17

US06CBCA52: Python Programming BCA Sem 6

[Link]()

[Link]()

Output:

('John', 101, 25000.0)

 Formatting the result

We can format the result by iterating over the result produced by the fetchall() or fetchone()
method of cursor object since the result exists as the tuple object which is not readable.

Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:

#Reading the Employee data

[Link]("select name, id, salary from Employee")

#fetching the rows from the cursor object

result = [Link]()

print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
[Link]()

[Link]()

Output:

Name id Salary
John 101 25000
John 102 25000
David 103 25000
Nick 104 90000
Mike 105 28000

File IO Management and Databases 18

US06CBCA52: Python Programming BCA Sem 6

 Using where clause

We can restrict the result produced by the select statement by using the where clause. This will
extract only those columns which satisfy the where condition.

Consider the following example.

Example: printing the names that start with j

import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee where name like 'J%'")

#fetching the rows from the cursor object

result = [Link]()

print("Name id Salary");

for row in result:

print("%s %d %d"%(row[0],row[1],row[2]))
except:
[Link]()

[Link]()

Output:

Name id Salary
John 101 25000
John 102 25000

Example: printing the names with id = 101, 102, and 103

import [Link]

#Create the connection object

File IO Management and Databases 19

US06CBCA52: Python Programming BCA Sem 6

myconn = [Link](host = "localhost", user = "root",passwd = "",

database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee where id in (101,102,103)")

#fetching the rows from the cursor object

result = [Link]()

print("Name id Salary");

for row in result:

print("%s %d %d"%(row[0],row[1],row[2]))
except:
[Link]()

[Link]()

Output:

Name id Salary
John 101 25000
John 102 25000
David 103 2500

 Ordering the result

The ORDER BY clause is used to order the result. Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee order by name")

#fetching the rows from the cursor object

result = [Link]()

File IO Management and Databases 20

US06CBCA52: Python Programming BCA Sem 6

print("Name id Salary");

for row in result:

print("%s %d %d"%(row[0],row[1],row[2]))
except:
[Link]()
[Link]()

Output:

Name id Salary
David 103 25000
John 101 25000
John 102 25000
Mike 105 28000
Nick 104 90000

 Order by DESC

This orders the result in the decreasing order of a particular column.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Reading the Employee data
[Link]("select name, id, salary from Employee order by name desc")

#fetching the rows from the cursor object

result = [Link]()

#printing the result

print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))

except:
[Link]()
[Link]()

Output:

File IO Management and Databases 21

US06CBCA52: Python Programming BCA Sem 6

Name id Salary
Nick 104 90000
Mike 105 28000
John 101 25000
John 102 25000
David 103 25000
 Update Operation
The UPDATE-SET statement is used to update any column inside the table. The following
SQL query is used to update a column.

> update Employee set name = 'alex' where id = 110

Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#updating the name of the employee whose id is 110
[Link]("update Employee set name = 'alex' where id = 110")
[Link]()
except:

[Link]()

[Link]()

File IO Management and Databases 22

US06CBCA52: Python Programming BCA Sem 6

 Delete Operation

The DELETE FROM statement is used to delete a specific record from the table. Here, we
must impose a condition using WHERE clause otherwise all the records from the table will be
removed.

The following SQL query is used to delete the employee detail whose id is 110 from the table.

> delete from Employee where id = 110

Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#Deleting the employee details whose id is 110
[Link]("delete from Employee where id = 110")
[Link]()
except:

File IO Management and Databases 23

US06CBCA52: Python Programming BCA Sem 6

[Link]()

[Link]()
 Join Operation
We can combine the columns from two or more tables by using some common column among
them by using the join statement.

We have only one table in our database, let's create one more table Departments with two
columns department_id and department_name.

create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20)
not null);

As we have created a new table Departments as shown in the above image. However, we
haven't yet inserted any value inside it.

Let's insert some Departments ids and departments names so that we can map this to our
Employee table.

1. insert into Departments values (201, "CS");

2. insert into Departments values (202, "IT");

Let's look at the values inserted in each of the tables. Consider the following image.

File IO Management and Databases 24

US06CBCA52: Python Programming BCA Sem 6

Now, let's create a python script that joins the two tables on the common column, i.e., dept_id.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#joining the two tables on departments_id
[Link]("select [Link], [Link], [Link], [Link]
_id, Departments.Dept_Name from Departments join Employee on Departments.Dept_id
= Employee.Dept_id")
print("ID Name Salary Dept_Id Dept_Name")
for row in cur:
print("%d %s %d %d %s"%(row[0], row[1],row[2],row[3],row[4]))

except:
[Link]()

[Link]()

File IO Management and Databases 25

US06CBCA52: Python Programming BCA Sem 6

Output:

ID Name Salary Dept_Id Dept_Name

101 John 25000 201 CS
102 John 25000 201 CS
103 David 25000 202 IT
104 Nick 90000 201 CS
105 Mike 28000 202 IT

 Right Join

Right join shows all the columns of the right-hand side table as we have two tables in the
database PythonDB, i.e., Departments and Employee. We do not have any Employee in the
table who is not working for any department (Employee for which department id is null).
However, to understand the concept of right join let's create the one.

Execute the following query on the MySQL server.

insert into Employee(name, id, salary, branch_name) values ("Alex",108,29900,"Mumbai");

This will insert an employee Alex who doesn't work for any department (department id is null).

Now, we have an employee in the Employee table whose department id is not present in the
Departments table. Let's perform the right join on the two tables now.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = ",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#joining the two tables on departments_id
result = [Link]("select [Link], [Link], [Link], Departme
nts.Dept_id, Departments.Dept_Name from Departments right join Employee on Depart
ments.Dept_id = Employee.Dept_id")

print("ID Name Salary Dept_Id Dept_Name")

for row in cur:

print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4])

except:
[Link]()

File IO Management and Databases 26

US06CBCA52: Python Programming BCA Sem 6

[Link]()

Output:

ID Name Salary Dept_Id Dept_Name

101 John 25000.0 201 CS
102 John 25000.0 201 CS
103 David 25000.0 202 IT
104 Nick 90000.0 201 CS
105 Mike 28000.0 202 IT
108 Alex 29900.0 None None

 Left Join

The left join covers all the data from the left-hand side table. It has just opposite effect to the
right join. Consider the following example.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
#joining the two tables on departments_id
result = [Link]("select [Link], [Link], [Link], Departme
nts.Dept_id, Departments.Dept_Name from Departments left join Employee on Departme
nts.Dept_id = Employee.Dept_id")
print("ID Name Salary Dept_Id Dept_Name")
for row in cur:
print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4])
except:
[Link]()
[Link]()

Output:

ID Name Salary Dept_Id Dept_Name

101 John 25000.0 201 CS
102 John 25000.0 201 CS
103 David 25000.0 202 IT
104 Nick 90000.0 201 CS
105 Mike 28000.0 202 IT

File IO Management and Databases 27

US06CBCA52: Python Programming BCA Sem 6

 Performing Transactions
Transactions ensure the data consistency of the database. We have to make sure that more than
one applications must not modify the records while performing the database operations. The
transactions have the following properties.

1. Atomicity
Either the transaction completes, or nothing happens. If a transaction contains 4 queries then
all these queries must be executed, or none of them must be executed.
2. Consistency
The database must be consistent before the transaction starts and the database must also be
consistent after the transaction is completed.
3. Isolation
Intermediate results of a transaction are not visible outside the current transaction.
4. Durability
Once a transaction was committed, the effects are persistent, even after a system failure.

 Python commit() method

Python provides the commit() method which ensures the changes made to

the database consistently take place.

The syntax to use the commit() method is given below.

[Link]() #conn is the connection object

All the operations that modify the records of the database do not take place until the commit()
is called.

 Python rollback() method

The rollback() method is used to revert the changes that are done to the database. This method
is useful in the sense that, if some error occurs during the database operations, we can rollback
that transaction to maintain the database consistency.

The syntax to use the rollback() is given below.

[Link]()

File IO Management and Databases 28

US06CBCA52: Python Programming BCA Sem 6

 Closing the connection

We need to close the database connection once we have done all the operations regarding the
database. Python provides the close() method. The syntax to use the close() method is given
below.

[Link]()

In the following example, we are deleting all the employees who are working for the CS
department.

Example
import [Link]

#Create the connection object

myconn = [Link](host = "localhost", user = "root",passwd = "",
database = "PythonDB")

#creating the cursor object

cur = [Link]()

try:
[Link]("delete from Employee where Dept_id = 201")
[Link]()
print("Deleted !")
except:
print("Can't delete !")
[Link]()

[Link]()

Output:

Deleted !

File IO Management and Databases 29