Python Konversi Database MSSQL ke CSV

Kode Lima Detik

import pyodbc
import csv
import pandas as pd

params = 'DRIVER={SQL Server Native Client 11.0};' \
         'SERVER=.\MSSQLSERVER2014;' \
         'PORT=1433;' \
         'DATABASE=AdventureWorks2014;' \
         'UID=namapengguna;' \
         'PWD=katasandi;'
cnxn = pyodbc.connect(params)
cursor = cnxn.cursor()
cursor2 = cnxn.cursor()

tables = []
sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\'BASE TABLE\''

cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:    
    table_name = row[1] + '.' + row[2]
    sql2 = 'SELECT COUNT(*) FROM ' + table_name
    cursor2.execute(sql2)
    counter = cursor2.fetchone()
    
    print(table_name, counter[0]) # Untuk kenyamanan
    if counter[0] > 0:
        sql3 = 'SELECT * FROM ' + table_name
        df = pd.read_sql(sql3, cnxn)
        filename = 'data/' + table_name + '.csv'
        df.to_csv(filename, sep='\t')

Contents hide

1 Latar Belakang

2 Kode

2.1 Koneksi

2.2 Ambil Semua Tabel

2.3 Ambil Hanya Tabel yang Memiliki Data

2.4 Ekspor ke CSV

3 Catatan

4 Referensi

Latar Belakang

Pada beberapa kesempatan, akses langsung ke RDBMS tidaklah memungkinkan. Mungkin kita sedang tidak dalam jaringan yang sama dengan server data. Bisa juga karena kita tidak berhasil melakukan instalasi yang diperlukan untuk mengakses RDBMS. Atau bisa jadi kita ingin melakukan olah data di mesin cloud seperti tulisan sebelumnya.

Dalam kondisi seperti itu, tentu menyenangkan jika data telah ada dalam bentuk csv yang dapat diakses langsung tanpa perlu memikirkan ubo rampe koneksi ke RDBMS.

Sama seperti sebelumnya, kita akan melakukan koneksi ke MSSQL 2014 untuk mengambil semua data dari database AdventureWorks2014 kemudian menyimpannya sebagai berkas csv.

Kode

Kita akan melakukan langkah demi langkah untuk mendapatkan kode seperti di atas.

Koneksi

Pertama impor library yang digunakan dan definisikan variabel yang akan digunakan.

params = 'DRIVER={SQL Server Native Client 11.0};' \
         'SERVER=.\MSSQLSERVER2014;' \
         'PORT=1433;' \
         'DATABASE=AdventureWorks2014;' \
         'UID=namapengguna;' \
         'PWD=katasandi;'
cnxn = pyodbc.connect(params)
cursor = cnxn.cursor()
cursor2 = cnxn.cursor()

Ambil Semua Tabel

Langkah ini adalah jembatan keledai yang memudahkan kita mendapatkan kode yang diperlukan, juga memberikan kemudahan mengaudit kode kita jika terjadi kesalahan.

Ambil dulu semua tabel yang ada dalam database AdventureWorks2014.

tables = []
sql = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=\'BASE TABLE\''

cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
    print(row)
    tables.append(row[1] + '.' + row[2])

Variabel tables akan berisi daftar tabel.

Ambil Hanya Tabel yang Memiliki Data

Langkah ini opsional, jika dirasa perlu mendapatkan semua tabel, silahkan dilewati.

non_empty_tables = []
for t in tables:
    sql = 'SELECT COUNT(*) FROM ' + t
    cursor.execute(sql)
    row = cursor.fetchone()
    print(t, row[0])
    
    if row[0] > 0:
        non_empty_tables.append(t)

Variabel yang digunakan untuk menampung daftar tabel yang berisi data adalah non_empty_tables.

Ekspor ke CSV

Ini adalah langkah terakhir dari jembatan keledai kita.

for table in non_empty_tables: 
        print(table)

        sql = 'SELECT * FROM ' + table
        df = pd.read_sql(sql, cnxn)
        filename = 'AdventureWorks/' + table + '.csv'
        df.to_csv(filename, sep='\t')

Tiap tabel akan disimpan dalam folder AdventureWorks (silahkan sesuaikan dengan kebutuhan pembaca) dan diberi nama sesuai nama tabelnya. Penyimpanan dalam format csv dengan separator (pemisah) berupa tab (\t).

Catatan

Kode di atas adalah skenario terbaik, dimana tidak ditemukan masalah apapun. Dalam mengolah data seringya kita mendapat kejutan, kali ini bukan pengecualian.

Gambar lengkap dari kode terakhir adalah seperti ini.

Masalah timbul karena terdapat kolom berisi heksadesimal pada beberapa tabel berikut:

HumanResources.Employee
Person.Address
Production.Document
Production.ProductDocument

Untuk mengatasinya, kita eksklusi tabel tersebut pada kode terakhir.

blacklist = [
    'Production.ProductDocument',
    'Person.Address',
    'Production.Document',
    'HumanResources.Employee'
]

for table in non_empty_tables:    
    if table not in blacklist:
        print(table)

        sql = 'SELECT * FROM ' + table
        df = pd.read_sql(sql, cnxn)
        filename = 'AdventureWorks/' + table + '.csv'
        df.to_csv(filename, sep='\t')

Untuk tabel yang dieksklusi tersebut, kita buatkan kode tersendiri.

# Employee
sql = "SELECT CAST(OrganizationNode AS nvarchar(100)) AS OrganizationNode, BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, CurrentFlag, rowguid, ModifiedDate FROM HumanResources.Employee"
df = pd.read_sql(sql, cnxn)
filename = 'AdventureWorks/HumanResources.Employee.csv'
df.to_csv(filename, sep='\t')

# Address
sql = "SELECT CAST(SpatialLocation AS nvarchar(100)) AS SpatialLocation, AddressID, AddressLine1, AddressLine2, City, StateProvinceID, rowguid, ModifiedDate FROM Person.Address"
df = pd.read_sql(sql, cnxn)
filename = 'AdventureWorks/Person.Address.csv'
df.to_csv(filename, sep='\t')

# Document
sql = "SELECT CAST(DocumentNode AS nvarchar(100)) AS DocumentNode, DocumentLevel, Title, Owner, FolderFlag, FileName, FileExtension, Revision, ChangeNumber, Status, DocumentSummary, Document, rowguid, ModifiedDate FROM Production.Document"
df = pd.read_sql(sql, cnxn)
filename = 'AdventureWorks/Production.Document.csv'
df.to_csv(filename, sep='\t')

# ProductDocument
sql = "SELECT CAST(DocumentNode AS nvarchar(100)) AS DocumentNode, ProductID, ModifiedDate FROM Production.ProductDocument"
df = pd.read_sql(sql, cnxn)
filename = 'AdventureWorks/Production.ProductDocument.csv'
df.to_csv(filename, sep='\t')

Kita menggunakan fungsi CAST (dapat pula CONVERT) untuk mengubah tipe data heksadesimal menjadi karakter (nvarchar).

Referensi

Cover Image by StartupStockPhotos from Pixabay