Committing and Rolling Back Transactions with Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
3 minutes
Share the link to this page
You need to have access to the item to view this lesson.
One-time Fee
List Price:  $139.99
You save:  $40
List Price:  €128.51
You save:  €36.71
List Price:  £110.53
You save:  £31.58
List Price:  CA$190.63
You save:  CA$54.47
List Price:  A$208.90
You save:  A$59.69
List Price:  S$188.42
You save:  S$53.84
List Price:  HK$1,092.29
You save:  HK$312.10
CHF 91.18
List Price:  CHF 127.65
You save:  CHF 36.47
NOK kr1,073.95
List Price:  NOK kr1,503.58
You save:  NOK kr429.62
DKK kr686.39
List Price:  DKK kr960.97
You save:  DKK kr274.58
List Price:  NZ$228.20
You save:  NZ$65.20
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  ৳16,307.21
You save:  ৳4,659.53
List Price:  ₹11,661.24
You save:  ₹3,332.02
List Price:  RM656.20
You save:  RM187.50
List Price:  ₦205,785.30
You save:  ₦58,800
List Price:  ₨38,789.74
You save:  ₨11,083.57
List Price:  ฿5,038.70
You save:  ฿1,439.73
List Price:  ₺4,513.75
You save:  ₺1,289.73
List Price:  B$714.59
You save:  B$204.18
List Price:  R2,565.91
You save:  R733.17
List Price:  Лв251.83
You save:  Лв71.95
List Price:  ₩189,601.05
You save:  ₩54,175.60
List Price:  ₪516.98
You save:  ₪147.71
List Price:  ₱8,098.42
You save:  ₱2,313.99
List Price:  ¥21,792.94
You save:  ¥6,227
List Price:  MX$2,325.03
You save:  MX$664.34
List Price:  QR507.87
You save:  QR145.11
List Price:  P1,886.91
You save:  P539.15
List Price:  KSh18,172.80
You save:  KSh5,192.60
List Price:  E£6,553.98
You save:  E£1,872.69
List Price:  ብር7,998.81
You save:  ብር2,285.53
List Price:  Kz118,851.51
You save:  Kz33,960
List Price:  CLP$125,220.73
You save:  CLP$35,779.90
List Price:  CN¥1,011.17
You save:  CN¥288.92
List Price:  RD$8,157.46
You save:  RD$2,330.87
List Price:  DA18,848.39
You save:  DA5,385.64
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,081.96
You save:  Q309.15
List Price:  GY$29,135.07
You save:  GY$8,324.90
ISK kr13,781.37
List Price:  ISK kr19,294.47
You save:  ISK kr5,513.10
List Price:  DH1,386.51
You save:  DH396.17
List Price:  L2,464.07
You save:  L704.07
List Price:  ден7,916.55
You save:  ден2,262.03
List Price:  MOP$1,119.16
You save:  MOP$319.78
List Price:  N$2,547.77
You save:  N$727.98
List Price:  C$5,126.52
You save:  C$1,464.82
List Price:  रु18,565.58
You save:  रु5,304.83
List Price:  S/520.05
You save:  S/148.59
List Price:  K540.79
You save:  K154.52
List Price:  SAR524.96
You save:  SAR150
List Price:  ZK3,554.51
You save:  ZK1,015.64
List Price:  L640.62
You save:  L183.04
List Price:  Kč3,180.29
You save:  Kč908.72
List Price:  Ft49,813.32
You save:  Ft14,233.39
SEK kr1,071.59
List Price:  SEK kr1,500.27
You save:  SEK kr428.67
List Price:  ARS$123,415.66
You save:  ARS$35,264.13
List Price:  Bs962.21
You save:  Bs274.93
List Price:  COP$532,965.46
You save:  COP$152,286.72
List Price:  ₡71,270.84
You save:  ₡20,364.55
List Price:  L3,448
You save:  L985.21
List Price:  ₲1,043,416.42
You save:  ₲298,140.27
List Price:  $U5,431.06
You save:  $U1,551.84
List Price:  zł548.49
You save:  zł156.72
Already have an account? Log In


Hi, welcome to this lecture. In this lecture, we are going to talk a bit about the commit and rollback methods. As we've seen so far, the commit method commits or saves the changes that have been applied since the last commit during the current database transaction. If you don't use commit, after applying some changes, these changes won't be visible from other connections to the database and won't be applied to your tables. On the other hand, the rollback method rolls back any changes that you've made to the database since the last time you used the commit method. So let me prove this to you.

I'm going to first import psycho PG two, then I'm going to establish a new connection to the database. And of course, initialize the cursor and looking at our database at this point. We currently have these five records that we previously used In this section thus far, Okay, now let's assume that we are inserting a new record into the database into the table. So using the cursor, I'm going to execute this SQL statement right here, insert into my staff that employees and I'm going to insert the sixth record right here, Gene centers, department, HR, phone number, address, and salary. And let me hit enter. Okay, at this point, we haven't committed this change yet.

So let's go over to this connection to the database right here, and query the table once again. And you can see that the new record hasn't yet been added to the table. On the other hand, if we return to this session, this connection to the database, let's use cursor dot execute in order to query our table from within this session, so select all from my staff dot employees. Next we have cursor dot fetch all and let's use the same variable records and for i in Records brain tie. So you can see that in this session in this connection to the database, our change, our new record has been added to the table. However, the change is not yet visible from this connection right here.

Okay, now what if we change our mind and we want to roll back this change and do not add Jane Sanders to our table. In order to do that, we are going to use connection dot rollback to roll back the changes since the last commit. And now if we perform the query once again, let's fetch all the records as well. So for i in records, print time, and you can see that after using the rollback method, the newly created record, Jane Sanders is not present in the database anymore. Okay, so this is the way in which rollback can be used. However, if we do want to insert this record into the table and make it visible to other connections, as well as saved to the database.

Let's use cursor dot execute once again. And now let's use connection dot commit. And at this point, this new record should be visible inside the database from other connections as well. And let's try this. And you can see this record being added right here, Jen Sanders and all the data associated with this record. Okay, so this is the way in which the roll back and commit methods work inside databases.

And I think the rollback method is pretty useful whenever you make some changes to the database and then change your mind. So having that said, I'll see you in the next lecture.

Sign Up


Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.