Cara menggunakan python mysql transaction example

PostgreSQL adalah sebuah sistem database relasional objek, dan merupakan salah satu basis data yang paling banyak digunakan saat ini, selain MySQL dan Oracle. Walaupun dikembangkan secara open-source, namun ia mendukung sebagian besar standar Structured Query Language (SQL)

SQL terdiri dari Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), dan Transaction Control Language. Commit, Rollback dan Save Point di PostgreSQL adalah 3 contoh Query dari Transaction Control Language (TCL) yang umum digunakan oleh DBA Developer dalam memanage sistem database.

Baca Juga :
PostgreSQL Dinobatkan Sebagai Solusi Manajemen Database Terbaik

Commit, Rollback dan Save Point merupakan fungsi yang digunakan untuk melakukan restore data ke kondisi awal sebelum dilakukan perubahan data. Berikut adalah basic demonstrasi dari Commit, Rollback dan Save Point di PostgreSQL:

Dengan menggunakan COMMIT, kita dapat mengakhiri semua transaksi dan menjadikannya sebagai perubahan permanen.

Commit

Dengan menggunakan ROLLBACK, kita bisa melompat ke keadaan terakhir dari sebuah transaksi yang telah di-commit sehingga update query berikut tidak akan tercatat di dalam transaksi.

Rollback Query

Selain melompat ke commit terakhir dari sebuah transaction, Rollback juga dapat digunakan melompat ke suatu titik tertentu yang didefinisikan didalam Savepoint. Savepoint menetapkan savepoint baru dalam transaksi saat ini. Sebuah savepoint adalah tanda khusus di dalam transaksi yang memungkinkan semua perintah yang dijalankan setelah dibuat untuk di Rollback, mengembalikan status transaksi ke keadaan pada saat savepoint. Savepoint hanya bisa terbentuk saat berada di dalam blok transaksi. Ada beberapa savepoint yang dapat didefinisikan di dalam transaksi.

SavePoint Query

Rollback to SavePoint Query. Dengan menggunakan Query berikut maka update query dibawah tidak akan tercatat di dalam transaksi.

Berikut adalah hasil akhir isi dari table Pegawai dimana kolom empid 2 dan 5 tidak terupdate karena Query Rollback.

Baca Juga :
Cara Mudah Ganti Password Root di Red Hat Enterprise Linux (RHEL) 7

Demikian contoh-contoh demonstrasi dasar dari Commit, Rollback dan Save Point pada PostgreSQL. Ingin belajar lebih lanjut tentang tips dan trik berkaitan dengan PostgreSQL? Anda bisa mengikuti PostgreSQL Complete Administration Training yang disediakan oleh i3.

Untuk info lebih lengkap mengenai training yang tersedia di i3, Anda dapat menghubungi langsung tim sales kami melalui halaman Contact Us.

Tentang i3

PT. Inovasi Informatika Indonesia (i3) dikenal sebagai perusahaan penyedia solusi dan layanan TI yang berfokus pada Open Source, Security, Big Data dan Cloud bagi bisnis. i3 menyediakan layanan TI yang komprehensif, meliputi konsultasi, migrasi dan implementasi, pelatihan, troubleshooting, dan managed services. Untuk informasi lebih lanjut perihal layanan dan solusi yang ditawarkan, Anda dapat menghubungi kami melalui [email protected].

A Transaction contains a set of SQL commands used to change the data in the dataset. If we say transaction, it means that the data in the database has changed. It is used to change the database from one consistent state to another. It is a logical set of instructions to modify or change the dataset and maintain its consistency.

A transaction can be understood with an example if we want to withdraw money from account X and deposit it in account Y, then this can be a transaction.

A transaction is made up of ACID property, and these four properties are:

1. Atomicity: In this property, all transactions will be successful or none. It’s either all or none. If any of the commands fail, then the entire transaction will fail. Either transaction will complete, or nothing will happen at all.
2. Consistency: In this property, a transaction should always acquire a consistent state. From the start to the end, a transaction should be consistent.
3. Isolation: In this property, any transaction that is happening should not be visible to any other transaction. It should be isolated. Its intermediate result should not be visible to any other transaction.
4. Durability: In this property, if a transaction has been committed, its effect must be persistent even if the system fails.

Python provides two methods to perform transactions which are commit and rollback.

Steps to perform transactions in Python MySQL

• Create MySQL database in Python.
• Collect or prepare the set of queries you want to perform. It can be multiple queries in one transaction.
• Set an auto-commit property of MySQL to false.
• Execute all the queries one by one using cursor.execute().
• If the queries get executed successfully, then commit the changes to the database.
• If any of the queries get blocked or failed, then we will rollback all the changes.
• We will also catch the SQL exception in this process.
• Close the cursor and MySQL database.

Python commit () method

The python commit() method is used to maintain the database’s consistency. In the database, if any changes occur in the transaction, then it must be consistent; for that, we use the commit method.

The method will send a COMMIT statement to the MySQL server, which then commits the current transaction. When execution of the query is successful, it makes permanent changes in a database using the commit() of the connection class.

The syntax for commit() method is given below:

connec.commit()   # connec is the connection object

Any operation that we perform in a transaction to modify the database will not occur until we don’t call the commit method.

Python rollback() method

The Python rollback() method is used if any of the queries in the transaction doesn’t get executed or fail. In that case, we use the rollback method.

The syntax for rollback() method is given below:

connec.rollback()  # connec is the connection object

Python autoCommit() method

It is used to enable or disable the value of auto-commit as true or false. By default, its value is taken as False.

The syntax for autoCommit() method is given below:

connec.autocommit() # connec is the connection object

In the python DB-API, we perform transaction processing through the connection object model. We will invoke begin() to begin the transaction in the database and after that, we can use commit or rollback to end it. The begin() call and the commit() call goes into the same block whereas the rollback() block goes into the corresponding except block which will cancel the transaction if any error occurs.

Let’s understand with an example. In this transaction we will withdraw and deposit money from one person to another.