‘commit()’ method is used to commit all the
transactions to the database. If database supports an auto-commit feature, this
must be initially off. Committed transactions are not rollbacked.
#!/usr/bin/python # Import MySQLdb module import MySQLdb # Print data from given table def printData(cursor,tableName): print('***********************************') print("getting all the records from ", tableName) sql = "SELECT * FROM "+tableName # Execute the SQL command cursor.execute(sql) # Fetch all the rows results = cursor.fetchall() for row in results: id = row[0] firstName = row[1] lastName = row[2] salary = row[3] mailId = row[4] print(id, firstName, lastName, salary, mailId) # Drop and create table def dropAndCreateTable(cursor,tableName): sql="DROP TABLE IF EXISTS "+tableName cursor.execute(sql) sql = """CREATE TABLE employee ( id int, firstName varchar(30), lastName varchar(30), salary decimal, mailId varchar(30), PRIMARY KEY(id) )""" cursor.execute(sql) # Connection related variables host="localhost" userName="root" password="tiger" database="sample" # Open connection to a database db = MySQLdb.connect(host, userName, password, database) # Get cursor object cursor = db.cursor() # Prepare SQL query to create table record1="""INSERT INTO employee values(1, "Krishna", "Ananda", 100000.00, "krishna@krishna.com")""" record2="""INSERT INTO employee values(2, "Arjun", "Dhanunjay", 50000.00, "arjun@arjun.com")""" record3="""INSERT INTO employee values(3, "Ptr", "Ptr", 25000.00, "ptr@ptr.com")""" record4="""INSERT INTO employee values(4, "Nayan", "Nayanamma", 225000.00, "nayan@nayan.com")""" record5="""INSERT INTO employee values(5, "Kiran", "Darsi", 95000.00, "kiran@kiran.com")""" try: print("Dropping table employee") dropAndCreateTable(cursor,"employee") print("Inserting records") cursor.execute(record1) cursor.execute(record2) cursor.execute(record3) print("Commiting data to database") db.commit() printData(cursor,"employee") cursor.execute(record4) cursor.execute(record5) print("Between commit and rollback") print("****************************") printData(cursor,"employee") db.rollback() print("After rollback") print("****************************") printData(cursor,"employee") cursor.close() except Exception as e: print("Error while processing query", e) db.rollback() finally: # Close the connection to database db.close()
Output
Dropping table employee Inserting records Commiting data to database *********************************** ('getting all the records from ', 'employee') (1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com') (2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com') (3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com') Between commit and rollback **************************** *********************************** ('getting all the records from ', 'employee') (1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com') (2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com') (3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com') (4L, 'Nayan', 'Nayanamma', Decimal('225000'), 'nayan@nayan.com') (5L, 'Kiran', 'Darsi', Decimal('95000'), 'kiran@kiran.com') After rollback **************************** *********************************** ('getting all the records from ', 'employee') (1L, 'Krishna', 'Ananda', Decimal('100000'), 'krishna@krishna.com') (2L, 'Arjun', 'Dhanunjay', Decimal('50000'), 'arjun@arjun.com') (3L, 'Ptr', 'Ptr', Decimal('25000'), 'ptr@ptr.com')
Observe the output, I called db.commit() after
inserting 3 records into table, so these 3 records are not affected by
db.rollback() method.
No comments:
Post a Comment