Import Excel ke PostgreSQL Database Menggunakan Python

Terkadang terdapat kebutuhan untuk menambahkan data dari file excel ke dalam database salah satunya PostgreSQL. Untuk melakukan hal tersebut kita bisa menggunakan bahasa Python dengan dibantu library yaitu openpyxl untuk memproses file excel dan juga psycopg2 untuk driver database PostgreSQL.

Aktivasi Virtual Environment dan Install Library

Disini kita akan menggunakan virtual environment, untuk membuat dan mengaktifkannya kita bisa menggunakan perintah

#python3
python3 -m venv venv

#atau
python -m venv venv

Untuk mengaktifkannya:

# Linux
source venv/bin/activate

# Windows
venv/Scripts/activate.bat

Setelah itu kita dapat menginstall library yang dibutuhkan yaitu openpyxl dan psycopg2

pip install openpyxl
pip install psycopg2

Membuat Koneksi ke PostgreSQL

Untuk membuat koneksi ke PostgreSQL kita menggunakan library psycopg2 sebagai driver PostgreSQL

Cara untuk membuat koneksinya adalah

import psycopg2

# Membuat koneksi ke postgresql
con = psycopg2.connect(
  database = "nama_database",
  user = "username_postgresql",
  password = "password_postgresql",
  host = "host_postgresql",
  port = 5432
)

Kita menggunakan fungsi connect() yang ada pada library psycopg2. Fungsi tersebut menerima argument/parameter seperti

1. databasa: nama database di postgresql
2. user: username postgresql
3. password: password postgresql
4. host: host dimana postgresql kita disimpan
5. port: port untuk postgresql (biasanya adalah 5432)

Memproses atau Manipulasi File Excel

Untuk proses atau manipulasi file excel kita menggunakan library openpyxl.

Dengan menggunakan openpyxl kita bisa membuka workbook, memilihin sheet, mengambil nilai kolom dan juga nilai baris.

import openpyxl

# Function for replace the whitespace, (),- to _
def transform_column_name(column_name):
  return column_name.strip().translate(str.maketrans('', '', '()-')).replace(' ', '_').replace('-', '_').lower()

# Excel File Path
excel_file_path = "path_to_excel_file"

# Open The Excel
workbook = openpyxl.load_workbook(excel_file_path)
sheet = workbook.active

# Get All List Column & Data
list_column = [transform_column_name(column.value) for column in sheet[1]]
data = [row for row in sheet.iter_rows(min_row=2, values_only=True)]

Pertama kita membuat fungsi transform_column_name yang menerima parameter yaitu column_name.

Fungsi ini bertujuan untuk transformasi atau mengubah nama kolom. Jika misalnya terdapat karakter seperti spasi kosong (” ”), dash (-) atau kurung ”()” maka semua karakter tersebut akan diubah menjadi underscore (_)

Hal tersebut bertujuan agar tidak terjadi error ketika kita membuat sebuah tabel atau kolom di database karena di PostgreSQL, kita tidak bisa membuat nama kolom yang mengandung karakter spasi kosong, dash atau kurung.

Selanjutnya kita membuka file excel dengan menggunakan fungsi openpyxl.load_workbook(). Fungsi tersebut menerima argument/parameter yaitu path file excel.

Lalu, kita memilih active sheet.

Nah dari sheet tersebut, lalu kita bisa mendapatkan list kolom apa saja yang ada di file excel tersebut. Disini kita anggap bahwa kolom pertama adalah sebagai header yang nantinya akan kita jadikan kolom di tabel database kita.

Selanjutnya untuk mendapatkan data, kita bisa mendapatkannya dimulai dari baris yang ke-2, karena baris ke-1 adalah header. Kita bisa menggunakan fungsi iter_rows(), parameter min_row adalah posisi start rownya yaitu 2 dan values_only untuk mendapatkan isi dari rownya saja.

Create Table

Selanjutnya kita membuat tabel, dimana kolom-kolomnya nanti akan diisi dari kolom header file excel. Tipe datanya kita akan buat menjadi VARCHAR(255) atau TEXT semua.

# Cursor for Execute Query
cursor = con.cursor()

# Table Name
table_name = "organize_crime"

# Execute query create table
cursor.execute("CREATE TABLE IF NOT EXISTS {} ({})".format(table_name, ", ".join('"{}" VARCHAR(255)'.format(column) for column in list_column)))

# Insert Query
sql = "INSERT INTO {} ({}) VALUES ({})".format(
  table_name,
  ', '.join(list_column),
  ', '.join(['%s' for _ in range(len(list_column))])
)

# Execute the SQL query with executemany
cursor.executemany(sql, data)

# Commit the transaction
con.commit()

# Close cursor and connection
cursor.close()
con.close()

Pertama kita membuka objek cursor untuk melakukan operasi ke database, seperti create, insert dan lainnya

Lalu jangan lupa definisikan nama tabel dimana kita akan menambahkan datanya

Setelah itu, kita melakukan operasi database yaitu create table, dimana kolomnya diisi dengan kolom header pada file excel dengan tipe data semuanya adalah VARCHAR(255).

Setelah itu, kita lakukan insert data ke tabel, dimana datanya diperoleh dari file excel yang sudah kita tangkap di variabel data

Kita menggunakan executemany() agar bisa insert data banyak sekaligus tanpa menggunakan perulangan satu per satu.

Setelah itu, commit transaction, tutup cursor dan juga koneksi.

Selesai.

Jalankan Script

Jalankan script dengan menggunakan perintah

#python
python file.py

#python3
python3 file.py

Terima kasih sudah membaca tulisan ini. Sampai jumpa!