Cara menggunakan mock postgres database python

A demonstration of writing unit tests for Python functions that perform some action on a Postgres database using testing.postgresql package and nose.

Background

In an ideal world all functions would be pure and free of side-effects, however in the real world we often need to write functions who's job it is to create side-effects such as writing to a file or updating a database. There are case when mocks can be used to test such functions but often you need to test against an actual database in order to test properties such as:

• the effects of triggers
• conversion of values to conform with a columns type
• ensuring constraints are enforced etc.

Approach

This project demonstrates testing two very simple functions insert and increment which operate on a table public.numbers. The functions to test are defined in app.py, tests in test/test_app.py and resources used by the tests in

db.url()

The general approach is:

Organisation

• Try and isolate the functionality that interacts with the database to individual functions, this will hopefully make other functions easier to test as they don't mess with the database and allow those functions that do to be tested in a consistent manner
• Pass either an existing database connection or the configuration needed to create a connection to the function so that they can be called independently. Both insert and increment accept a map of configuration parameters which make it possible to call them independently

Writing tests

• Use module level set-up to create a fresh temporary database instance using testing.postgresql and use SQL script to set-up the structure of the database (roles, schemas, tables, view, functions, triggers etc.) and load any data that does not change. Ideally this would be done before each test but as creating the temporary database take a second or two I've found it's generally OK to do the basic set-up once and then reuse the database for all tests
• Have each individual test set the initial state of the database before calling the function to be tested, this is generally done by executing a SQL script such as

  db.url()
  

  3 which first truncates the public.numbers table then inserts a known set of data
• Once the test has established the database state it can call the function to be tested followed by querying the database state in-order to determine if the function call and any associated database triggers, constraints etc. had the desired affect.

Tips

Connecting to the temporary database

While writing tests and implementing functionality it's often useful to connect to the temporary database in-order to inspect it's current state. A simple way to pause the execution of the tests or the application being tested is to drop into the Python debugger

db.url()

import pdb; pdb.set_trace()

Once execution is paused you can get the connection parameters and connect to the database using

db.url()

db.url()

Then connect to the database using

db.url()

db.url()

psql postgresql://[email protected]:46183/test

Running

In order to run the project yourself, clone or download the repository then create a virtual environment and install dependencies with:

pip install -r requirements.txt

Then run the tests with (

db.url()

psql postgresql://[email protected]:46183/test

Pada artikel sebelumnya yang membahas tentang teknik random data MySQL, kita telah belajar bagaimana teknik dasar mengambil data secara random pada beberapa jenis bentuk data di MySQL, baik itu data berupa bilangan float, integer, data tanggal, dan juga data dari tabel.

Sedangkan pada artikel kali ini, kita akan menerapkan teknik random tersebut untuk mengenerate data dummy di MySQL. Keberadaan data dummy di sebuah database dapat dimanfaatkan untuk menguji performa sebuah aplikasi. Bagaimana performa aplikasi dalam memproses sejumlah data bisa dapat diketahui melalui data dummy tersebut.

Untuk memudahkan pembahasan pada artikel ini, akan diberikan sebuah studi kasus.

Studi kasus yang diberikan adalah sebagai berikut. Misalkan ada sebuah database untuk menyimpan data persewaan mobil, dengan struktur tabel seperti di bawah ini.

CREATE TABLE `mobil` (
  `idMobil` char(3) NOT NULL,
  `namaMobil` varchar(20),
  PRIMARY KEY (`idMobil`)
);

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `rental` (
  `idRental` int(11) NOT NULL AUTO_INCREMENT,
  `idMobil` char(3),
  `idCustomer` char(3),
  `tglSewa` date,
  `tglHarusKembali` date,
  `tglKembali` date,
  `terlambat` int(11),
  PRIMARY KEY (`idRental`)
)

Database di atas terdiri dari tiga buah tabel, yaitu: mobil, customer, dan rental. Tabel mobil isinya adalah data-data mobil yang disewakan oleh rental mobil tersebut. Tabel customer berisi data customernya, dan tabel rental berisi data transaksi persewaannya.

Pada tabel rental, di situ terdapat field

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

Misalkan dalam kasus ini, terdapat ketentuan bahwa batas maksimum lama sewa adalah 4 hari. Sehingga untuk data pada field

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

Selanjutnya, misalkan kita telah memiliki data dummy untuk tabel mobil sebagai berikut.

dan untuk tabel customer seperti pada tampilan di bawah ini

Dari data dummy yang ada di dua tabel tersebut, kita akan mencoba mengenerate data dummy yang ada di tabel rental secara referensial dengan mengambil tabel mobil dan customer sebagai tabel utamanya. Dalam hal ini, untuk field

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `rental` (
  `idRental` int(11) NOT NULL AUTO_INCREMENT,
  `idMobil` char(3),
  `idCustomer` char(3),
  `tglSewa` date,
  `tglHarusKembali` date,
  `tglKembali` date,
  `terlambat` int(11),
  PRIMARY KEY (`idRental`)
)

CREATE TABLE `rental` (
  `idRental` int(11) NOT NULL AUTO_INCREMENT,
  `idMobil` char(3),
  `idCustomer` char(3),
  `tglSewa` date,
  `tglHarusKembali` date,
  `tglKembali` date,
  `terlambat` int(11),
  PRIMARY KEY (`idRental`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

CREATE TABLE `customer` (
  `idCustomer` char(3) NOT NULL,
  `namaCustomer` varchar(20),
  PRIMARY KEY (`idCustomer`)
)

Sebagai bahan eksperimen, kita akan mencoba mengenerate data dummy sebanyak 1.000 record di tabel rental.

Untuk mengenerate data dummy dengan cepat, kita bisa menggunakan stored procedure, seperti perintah di bawah ini.

BEGIN
    # deklarasi variabel dan tipe data yang diperlukan
	DECLARE i, n, hariterlambat INT;
	DECLARE mobil, customer CHAR(3);
    DECLARE sewa, maxkembali, kembali DATE;
    
    # inisialisasi nilai awal untuk increment i
	SET i = 0;
    # inisialisasi menyatakan banyaknya looping
	SET n = 1000;

    # looping 1000 kali
	WHILE i < n DO
        # merandom sebuah data idMobil di tabel mobil
		SET mobil = (SELECT idMobil FROM mobil ORDER BY RAND() LIMIT 1);
 
        # merandom sebuah data idCustomer dari tabel customer
        SET customer = (SELECT idCustomer FROM customer ORDER BY RAND() LIMIT 1);

        # merandom tanggal sewa mulai 31-12-2021 sampai 30 hari ke belakang
        SET sewa = (SELECT '2021-12-31' - INTERVAL FLOOR(RAND() * 30) DAY);

        # merandom tanggal kembali dalam rentang 10 hari dari tgl sewa
        SET kembali = (SELECT sewa + INTERVAL FLOOR(RAND() * 10) DAY);

        # mendapatkan tgl batas maksimum sewa, yaitu 4 hari dari tgl sewa
        SET maxkembali = (SELECT sewa + INTERVAL 4 DAY);
        
        # hitung selisih hari dari tgl kembali dan tgl batas kembali
        SET hariterlambat = (SELECT DATEDIFF(kembali, maxkembali));

        # jika selisih harinya tidak positif (tidak terlambat)
        # maka set terlambatnya = 0
        IF hariterlambat <= 0 THEN
        	SET hariterlambat = 0;
        END IF;
        
        # insert data dummy ke tabel rental    
		INSERT INTO rental (idMobil, idCustomer, tglSewa, tglHarusKembali, tglKembali, terlambat) VALUES (mobil, customer, sewa, maxkembali, kembali, hariterlambat);

        # increment
		SET i = i + 1;
	END WHILE;
END

Apabila stored procedure (routine) di atas dijalankan, maka akan dihasilkan 1.000 buah record di tabel rental secara cepat dan otomatis seperti pada tampilan di bawah ini.

Mudah bukan membuatnya? 😀

Demikian artikel tentang cara membuat data dummy dengan menerapkan teknik random data di MySQL ini saya buat, semoga mudah dipahami dan bermanfaat. Jika ada pertanyaan tentang artikel ini, silakan ditanyakan di komentar.