Menyimpan Jutaan Record Data berbasis SQL dengan Python secara Mudah

By | January 14, 2021
679 Views

Jika kalian memilih sebuah format penyimpanan dengan bentuk seperti table, maka format tabel relational menjadi pertimbangan seperti sqlite yang single file, portable. API Sqlite sudah tersedia secara default kalau kalian menggunakan python lho jadi tidak perlu ribet untuk melakukan instalasi. Bahkan penulis sering menyimpan data sampai 135 juta record berukuran hampir 7 gb tidak ada masalah sedikit pun (bikin records sebanyak itu butuh waktu berjam-jam), akan sangat berbeda kalau kalian menyimpan dalam bentuk csv, xml, JSON, ataupun menggunakan excel. Keuntungan yang didapat jika menyimpan dalam format database SQL

  1. tentu kita dapat memilih record tertentu saja, misalkan loading record untuk id dari 0 sampai 50 juta record sehingga komputer kita tidak kehabisan RAM karena yang di loading yang dibutuhkan saja.
  2. insert record: kalau kalian membuat sebuah aplikasi logger seperti monitoring suhu, debit air, maka penggunaan format date menjadi sangat berguna. Misalkan untuk operasi select data menjadi lebih mudah berdasarkan date nya
  3. update record menjadi mudah karena ada primary key nya.
  4. Format terbuka untuk semua OS, yup kalian bisa membuka file tersebut menggunakan beragam OS karena API yang terbuka bahkan android menggunakan Sqlite menjadi standar database nya lho.

Sqlite yang merupakan format terbuka (ENCODE dan DECODE nya) maka secara umum kita membuat format penyimpanan Sqlite ada 3 macam yaitu

  1. Aplikasi Sqlite berbasis CLI, yang bisa kalian ketikan perintah menggunakan console, serta tidak perlu install karena portable. Software ini bisa kalian peroleh di https://sqlite.org/cli.html
  2. Aplikasi Sqlite berbasis GUI, penulis sering menggunakan DB Browser for Sqlite yang bisa kalian download di https://sqlitebrowser.org/. Ada banyak feature nya lho seperti
    1. Create and compact database files
    2. Create, define, modify and delete tables
    3. Create, define, and delete indexes
    4. Browse, edit, add, and delete records
    5. Search records
    6. Import and export records as text
    7. Import and export tables from/to CSV files
    8. Import and export databases from/to SQL dump files
    9. Issue SQL queries and inspect the results
    10. Examine a log of all SQL commands issued by the application
    11. Plot simple graphs based on table or query data
  3. API Sqlite untuk Python, yups ini yang akan kita bahas. Python yang merupakan sebuah bahasa general alias bisa dibuat untuk apa saja sesuka kita.

Nah fokus kita yaitu membuat operasi sesuai judul diatas Menyimpan Data berbasis SQL dengan Python secara Mudah berikut yang akan kita bahas

  1. Membuat database
  2. Membuat table
  3. Memasukan record
  4. Select record
  5. Update record
  6. Hapus record

Pembahasan berkisar dasar-dasar SQL untuk CRUD (create, read, update, dan delete), oiya bagi kalian yang belum tahu apa itu SQL yaitu structure query languange yang merupakan perintah dasar untuk database relational. Database relational akan melibatkan banyak table dengan setiap table mempunyai PK (primary key) dan FK (foreign key)

Membuat database dan melakukan koneksi

Hal yang pertama kali kita lakukan yaitu membuat database dan melakukan koneksi. Perintah connect() akan digunakan seperti berikut

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    #membuat database dan melakukan koneksi
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print('berhasil membuat database, dengan versi ',sqlite3.version)
        
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()


#panggil function
create_connection('kontak.db')

Maka didalam folder tersebut akan terdapat sebuah file kontak.db atau bisa juga membuat database hanya di RAM tanpa ditulis di hardisk, maka nama filenya diganti dengan :memory: seperti contoh berikut

create_connection(':memory:')

Untuk contoh selanjutnya kita membutuhkan return connection untuk membuat sebuah table, maka kode diatas harus kita ubah agar return nya connection

def create_connection(db_file):
    #membuat database dan melakukan koneksi    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print('berhasil membuat database, dengan versi ',sqlite3.version)
        return conn
    except Error as e:
        print(e)

    return conn

Jangan lupa untuk setiap connection() harus dilakukan close jika tidak dipakai lagi.

Membuat table pada database Sqlite

Untuk membuat table pada database sqlite seperti layaknya menggunakan perintah SQL pada umumnya, adapun desain dari table yang akan kita buat dengan id sebagai primary key dan bersifat auto increment.

Nama table tersebut yaitu biodata,  hal yang harus diperhatikan dalam membuat table

  1. Nama table dan nama kolom table menggunakan huruf kecil dan pemisah underscore
  2. Setiap nama kolom harus diberikan atribut tipe variabel seperti integer, string, primary key

untuk perintah create table SQL secara umum yaitu

-- table: biodata
CREATE TABLE IF NOT EXISTS biodata (
   id integer PRIMARY KEY,
   nama text NOT NULL,
   umur integer,
   pekerjaan text
);

lakukan eksekusi perintah SQL yaitu execute() tapi jangan lupa menggunakan connection terlebih dahulu.

def create_table(conn, create_table_sql):
    #create table
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

Berikut cara membuat perintah SQL dan memanggil perintah create_connection() serta create_table()

connection = create_connection('kontak.db')        
sql_create_table_biodata = """ CREATE TABLE IF NOT EXISTS biodata (
                            	id integer PRIMARY KEY,
                            	nama text NOT NULL,
                            	umur integer,
                            	pekerjaan text
                            ); """
    
create_table(connection, sql_create_table_biodata)
connection.close()

Kalian bisa cek, apakah sudah berhasil membuat sebuah table dengan menggunakan DB Browser for Sqlite

isi tabel

 

Setelah membuka dengan aplikasi diatas, jangan lupa juga untuk close database ya untuk menutup database

Insert record

Tentu setiap table kita harus isi jangan dibiarkan kosong, kita akan mencoba untuk mengisi beberapa record seperti berikut

Kita tidak perlu mengisi id karena akan terisi sendiri (primary key),oiya dengan menggunakan place holder berupa tanda ? maka perintah SQL menjadi lebih mudah

def create_biodata(conn, data):
    #memasukan biodata
    sql = ''' INSERT INTO biodata(nama,umur,pekerjaan)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, data)
    return cur.lastrowid

Maka perintah untuk insert record diatas yaitu

connection = create_connection('kontak.db')
bejo = ('Bejo Pamungkas','26','Marketing')
bintang = ('Bintang Pambudi','30','Desainer')
fira = ('Fira Lesmana','35','Fotografer')
fajar = ('Fajar Kurniawan','24','Help Desk')

with connection: #untuk memastikan ada koneksi dan langsung close otomatis
    create_biodata(connection,bejo)
    create_biodata(connection,bintang)
    create_biodata(connection,fira)
    create_biodata(connection,fajar)

Kalian bisa cek menggunakan aplikasi

 

Terlihat id sudah terisi secara otomatis

Select Record

Selain cara diatas untuk melihat sebuah record, kalian juga bisa menggunakan perintah SQL berikut

def select_all_biodata(conn):
    #select all
    cur = conn.cursor()
    cur.execute("SELECT * FROM biodata")

    rows = cur.fetchall()

    for row in rows:
        print(row)

Kita panggil function diatas

connection = create_connection('kontak.db')
with connection:
    select_all_biodata(connection)

hasil

berhasil membuat database, dengan versi  2.6.0
(1, 'Bejo Pamungkas', 26, 'Marketing')
(2, 'Bintang Pambudi', 20, 'Writer')
(3, 'Fira Lesmana', 35, 'Fotografer')
(4, 'Fajar Kurniawan', 24, 'Help Desk')

Select Record dengan syarat Kondisi

Misalkan kita akan memilih / query record dengan syarat umur >=26

def select_biodata_umur(conn,umur):
    #query
    cur = conn.cursor()
    cur.execute("SELECT * FROM biodata WHERE umur >= ?", (umur,))

    rows = cur.fetchall()

    for row in rows:
        print(row)

Kita panggil function diatas

connection = create_connection('kontak.db')
with connection:
    select_biodata_umur(connection,26)

hasil

berhasil membuat database, dengan versi  2.6.0
(1, 'Bejo Pamungkas', 26, 'Marketing')
(3, 'Fira Lesmana', 35, 'Fotografer')

Update record

Adakalanya kita akan melakukan sebuah update record, maka yang dibutuhkan hanyalah sebuah id (primary key) seperti contoh berikut

Akan diganti umur dan pekerjaan sedangkan namanya tetap

def update_umur_pekerjaan(conn, data):
    #update record
    sql = ''' UPDATE biodata
              SET umur = ? ,
                  pekerjaan = ? 
              WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, data)
    conn.commit()

Kita akan coba untuk melakukan update

connection = create_connection('kontak.db')
bintang = ('20','Writer',2)

with connection:
    update_umur_pekerjaan(connection,bintang)

Delete record

Operasi selanjutnya yaitu untuk delete sebuah record, maka yang dibutuhkan berupa id (primary key) nya, misalkan kita ingin menghapus id = 1 atas nama bejo pamungkas atauapun menghapus semua record.

def delete_biodata_id(conn, id):
    #delete id
    sql = 'DELETE FROM biodata WHERE id=?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()

Kita panggil saja perintahnya

with connection:
    delete_biodata_id(connection,1)
    select_all_biodata(connection)

Maka hasilnya akan tersisa 4 record saja

berhasil membuat database, dengan versi  2.6.0
(2, 'Bintang Pambudi', 20, 'Writer')
(3, 'Fira Lesmana', 35, 'Fotografer')
(4, 'Fajar Kurniawan', 24, 'Help Desk')

Kalian bisa saja koq untuk menghapus tidak hanya berdasarkan ID, misalkan saja nama seperti menggunakan regex, misalkan hapus atas nama depan bejo, maka perintahnya %bejo. Nah sekarang kita akan hapus semua records.

def delete_all_biodata(conn):
    # delete all 
    sql = 'DELETE FROM biodata'
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()

Kita panggil saja perintahnya

connection = create_connection('kontak.db')
with connection:
    delete_all_biodata(connection)
    select_all_biodata(connection)

Ada banyak sekali operasi SQL yang ada di Sqlite yang belum kita bahas seperti

  1. Group by
  2. Joint
  3. Transaction
  4. Having

Lain kalian saja kita bahas, yang penting kita sudah bisa membuat CRUD (create, read, update, dan delete). Nah berikut kode python dan SQL selengkapnya yang bisa kalian pelajari lagi.

#softscients
import sqlite3
from sqlite3 import Error



def create_connection(db_file):
    #membuat database dan melakukan koneksi
    try:
        conn = sqlite3.connect(db_file)
        print('berhasil membuat database, dengan versi ',sqlite3.version)
        return conn
    except Error as e:
        print(e)

    return conn

def create_table(conn, create_table_sql):
    #create table
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
        
def create_biodata(conn, data):
    #memasukan biodata
    sql = ''' INSERT INTO biodata(nama,umur,pekerjaan)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, data)
    return cur.lastrowid

def select_all_biodata(conn):
    #select all
    cur = conn.cursor()
    cur.execute("SELECT * FROM biodata")
    rows = cur.fetchall()
    for row in rows:
        print(row)

def select_biodata_umur(conn,umur):
    #query
    cur = conn.cursor()
    cur.execute("SELECT * FROM biodata WHERE umur >= ?", (umur,))
    rows = cur.fetchall()
    for row in rows:
        print(row)

def update_umur_pekerjaan(conn, data):
    #update record
    sql = ''' UPDATE biodata
              SET umur = ? ,
                  pekerjaan = ? 
              WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, data)
    conn.commit()


def delete_biodata_id(conn, id):
    #delete id
    sql = 'DELETE FROM biodata WHERE id=?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()

def delete_all_biodata(conn):
    # delete all 
    sql = 'DELETE FROM biodata'
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()



     
connection = create_connection('kontak.db')        
with connection: 
    sql_create_table_biodata = """ CREATE TABLE IF NOT EXISTS biodata (
                            	id integer PRIMARY KEY,
                            	nama text NOT NULL,
                            	umur integer,
                            	pekerjaan text
                            ); """

    create_table(connection, sql_create_table_biodata)



connection = create_connection('kontak.db')
with connection: #untuk memastikan ada koneksi dan close 
    bejo = ('Bejo Pamungkas','26','Marketing')
    bintang = ('Bintang Pambudi','30','Desainer')
    fira = ('Fira Lesmana','35','Fotografer')
    fajar = ('Fajar Kurniawan','24','Help Desk')

    create_biodata(connection,bejo)
    create_biodata(connection,bintang)
    create_biodata(connection,fira)
    create_biodata(connection,fajar)


connection = create_connection('kontak.db')
with connection:
    select_all_biodata(connection)

connection = create_connection('kontak.db')
with connection:
    select_biodata_umur(connection,26)


connection = create_connection('kontak.db')
with connection:
    bintang = ('20','Writer',2)
    update_umur_pekerjaan(connection,bintang)

    
connection = create_connection('kontak.db')
with connection:
    delete_biodata_id(connection,1)
    select_all_biodata(connection)

connection = create_connection('kontak.db')
with connection:
    delete_all_biodata(connection)
    select_all_biodata(connection)

Nah bagaimana menurut kalian?

Leave a Reply

Your email address will not be published.




Enter Captcha Here :