분산된 웹 환경하에서 여러 데이터를 취합하거나 혹은 단일 데이터 저장소로부터 내려받은 데이터를 다른 저장소로 이전해야 하는 경우가 종종 발생한다. 예를 들어 공개된 생명정보 데이터베이스로부터 쿼리 결과를 FASTA 포맷으로 다운로드 받아 이를 전문화된 라이브러리 구축을 위해 별도의 데이터 저장소로 옮기는 등의 작업이 빈번히 발생할 수 있다.
참고로, 사적으로 여러 차례 언급해왔지만 단 한번 수행하고 오랜 시간 동안 반복하지 않을 일이라면, 예를 들어, 일년에 두세 차례 수행하고 말 일이라면, 그 데이터 양이 얼마든지 간에 손으로 해치우고 마는 것이 훨씬 일을 간결하게 할 수 있다. 혹은 데이터 양이 많고 일부분에서 손이 많이 가며 반복적 패턴이 있다면 딱 그 부분만 처리하도록 코드를 작성하는 것이 더 효율적이다.
이 포스팅은 파이썬으로 데이터 이전을 수행하는 과정에 있어 많은 부분을 자동적으로 처리하기 위해 고려해야 할 부분과 내가 선호하는 방법에 대해 풀어 설명하는 목적으로 작성했다. 여기서 다루는 내용은 Microsoft SQL Server 예제 데이터베이스인 AdventureWorks로부터 ASCII 포맷으로 내려 받은 데이터를 파이썬과 이에 내장된 SQLite 모듈을 이용해 SQLite3 데이터베이스로 이전하는 코드이다.
AdventureWorks 데이터베이스의 Address, Customer, CustomerAddress, Product, ProductCategory, ProductModel, SalesOrderHeader, SalesOrderDetail 테이블로부터 일부 데이터를 가공해 ASCII 포맷 Text 파일로 내려 받았다. 이들 테이블에 관한 상세 내역은 AdventureWorks Data Dictionary 페이지를 참조토록 한다. 첨언하면, 데이터베이스 설계, 프로그래밍, 관리, 튜닝 등을 공부하는데 이런 예제 데이터베이스를 활용하는 것이 가장 효율적인 방법이었다.
각 필드 구분자는 탭(\t)을 사용했고 이 값들과 사상(mapping)하는 필드 명은 아래 SQLite3 데이터베이스에 테이블 생성을 위한 SQL DDL(Data Definition Language)문으로 확인 할 수 있다.
SQLite는 프로그래밍 라이브러리로 이는 단일 데이터베이스 파일을 In-Memory에서 다루는 데이터베이스 엔진이다. SQLite는 여러 이종 플랫폼에서 광범위하게 이용되고 있다. 이에 관한 상세한 내용은 SQLite 공식 웹 사이트에서 확인 할 수 있다.
SQLite에 새로운 데이터베이스 파일을 생성하기 위해 다음 명령을 실행 후, SQL DDL로 테이블을 생성했다.
1: DROP TABLE IF EXISTS SalesOrderDetail;2: DROP TABLE IF EXISTS SalesOrderHeader;3: DROP TABLE IF EXISTS Product;4: DROP TABLE IF EXISTS ProductModel;5: DROP TABLE IF EXISTS ProductCategory;6: DROP TABLE IF EXISTS CustomerAddress;7: DROP TABLE IF EXISTS Customers;8: DROP TABLE IF EXISTS Address;9:10: CREATE TABLE Address11: (12: AddressID INTEGER,13: AddressLine1 TEXT,14: AddressLine2 TEXT,15: City TEXT,16: StateProvince TEXT,17: CountryRegion TEXT,18: PostalCode TEXT,19: PRIMARY KEY(AddressID ASC)20: );21:22: CREATE TABLE CustomerAddress23: (24: CustomerID INTEGER,25: AddressID INTEGER,26: AddressType TEXT,27: PRIMARY KEY(CustomerID ASC, AddressID ASC)28: );29:30: CREATE TABLE Customers31: (32: CustomerID INTEGER,33: Title TEXT,34: FirstName TEXT,35: MiddleName TEXT,36: LastName TEXT,37: Suffix TEXT,38: CompanyName TEXT,39: EmailAddress TEXT,40: Phone TEXT,41: SalesPerson TEXT,42: PRIMARY KEY(CustomerID ASC)43: );44:45: CREATE TABLE Product46: (47: ProductID INTEGER,48: Name TEXT,49: ProductNumber TEXT,50: Color TEXT,51: StandardCost REAL,52: ListPrice REAL,53: Size TEXT,54: Weight TEXT,55: ProductCategoryID INTEGER,56: ProductModelID INTEGER,57: PRIMARY KEY(ProductID ASC)58: );59:60: CREATE TABLE ProductCategory61: (62: ProductCategoryID INTEGER,63: ParentProductCategoryID INTEGER,64: Name TEXT,65: PRIMARY KEY(ProductCategoryID ASC)66: );67:68: CREATE TABLE ProductModel69: (70: ProductModelID INTEGER,71: Name TEXT,72: PRIMARY KEY(ProductModelID ASC)73: );74:75: CREATE TABLE SalesOrderHeader76: (77: SalesOrderID INTEGER,78: RevisionNumber INTEGER,79: OrderDate TEXT,80: DueDate TEXT,81: ShipDate TEXT,82: Status INTEGER,83: OnlineOrderFlag INTEGER,84: SalesOrderNumber TEXT,85: PurchaseOrderNumber TEXT,86: AccountNumber TEXT,87: CustomerID INTEGER,88: ShipToAddressID INTEGER,89: BillToAddressID INTEGER,90: ShipMethod TEXT,91: CreditCardApprovalCode INTEGER,92: SubTotal REAL,93: TaxAmt REAL,94: Freight REAL,95: TotalDue REAL,96: PRIMARY KEY(SalesOrderID ASC)97: );98:99: CREATE TABLE SalesOrderDetail100: (101: SalesOrderID INTEGER,102: SalesOrderDetailID INTEGER,103: OrderQty INTEGER,104: ProductID INTEGER,105: UnitPrice REAL,106: UnitPriceDiscount REAL,107: LineTotal REAL,108: PRIMARY KEY(SalesOrderDetailID ASC)109: );
여기서는 테이블 간 관계성(relationship)을 설정하지 않았는데, 그 이유는 SQLite 엔진은 외래 키 사용이 기본적으로 막혀 있기 때문이다. 이를 PRAGMA foreign_keys = ON; 명령으로 옵션 변경을 할 수 있지만 기본 키만 설정했다. SQLite 엔진에서 외래 키 옵션을 확인하기 위해서는 PRAGMA foreign_keys; 라 입력하면 0(불가) / 1(가능)으로 설정 값이 나온다.
SQLite의 데이터 타입은 5가지(TEXT, INTEGER, REAL, BLOB, NONE)이며, 이에 관한 상세한 사항은 다음 웹 페이지 Datatypes In SQLite Version3 를 참고토록 한다.
생성한 테이블 다이어그램은 아래와 같다.
예제 데이터베이스 AdventureWorks 데이터는 아래 그림과 같이 ASCII 포맷으로 데이터 타입에 관한 원래 정보는 해당 파일에서 이미 소실된 상태이다.
각 값들과 SQLite 테이블의 필드 순서는 일치 시켰으므로 그 순서는 중요치 않지만 각 데이터 타입에 맞게 처리해야 할 필요가 있다.
그래서 각 테이블 마다 필드에 사상하는 데이터 타입을 기술한 scheme.meta 라는 이름의 ASCII 파일을 생성해 아래 내용을 저장했고 이를 데이터 이전 작업에서 쉐도우 데이터로 활용했다.
1: Address;int,str,str,str,str,str,str2: CustomerAddress;int,int,str3: Customers;int,str,str,str,str,str,str,str,str,str4: Product;int,str,str,str,float,float,str,str,int,int5: ProductCategory;int,int,str6: ProductModel;int,str7: SalesOrderHeader;int,int,str,str,str,int,int,str,str,str,int,int,int,str,int,float,float,float,float8: SalesOrderDetail;int,int,int,int,float,float,float
세미콜론으로 구분되는 두 필드는 각각 테이블 명과 각 필드 데이터 타입에 사상하는 파이썬 데이터 타입 리스트이다. 파이썬에서는 이를 읽어, 키-값 쌍을 가지는 딕셔너리를 생성하여 메타 정보로 활용한다. 아래는 이 과정을 수행하는 코드 일부이며 전체 코드는 포스트 마지막에 표시한다.
fname은 해당 함수의 인자로 파일 경로이며, with 구문으로 파일을 열어 파이썬의 Context Manager에 그 처리를 이양했기 때문에 파일 닫기 작업은 불필요하다.
1: with open(fname, "r") as fh:2: for line in fh:3: t, s = line.split(";")4: q[t] = s.replace("\n", "")5: return q
데이터 베이스에 여러 값을 입력하는데 여러 테크닉이 존재하는데 여기서는 여러 쿼리를 실행하는 방법을 선택했다. 이는 일반적으로 손쉽게 행하는 반복 구문을 이용해 한번에 하나의 레코드 세트를 프로그램에서 집어 넣는 것이 아니라 레코드 세트에 해당하는 복수의 투플(tuple)을 가진 리스트를 생성 후 이를 하나의 쿼리와 묶어 처리토록 하는 것이다.
예를 들어 ABC란 테이블에 5개 필드에 두 개의 데이터 열을 넣고자 하면 값 리스트는 rows = [(1, 2, 3, 4, 5), (6, 7, 8, 9, 10)]과 같이 생성한 후 아래 코드와 같이 실행하면 된다.
1: cursor.executemany("INSERT INTO ABC VALUES(?, ?, ?, ?, ?)", rows)2: db.commit()
각 데이터 파일을 읽어 위 코드와 같이 테이블 별 데이터 세트로 만든 후, cursor.executemany(querySlot, dataSetList)로 호출하면 되는데 이를 먼저 보인 후 고려 사항에 대해 다룬다.
1: for key in tablesScheme.keys():2: if VERBOSE:3: print("TABLE: %s" % key, end=", ")4: rows = []5: scheme = tablesScheme[key].split(",")6: with open("AW" + key + ".txt", "r") as fh:7: for data in fh:8: value = data.replace("\n", "").split("\t")9: rows.append(tuple(i for i in evalRow(scheme, value)))10: cursor.executemany("INSERT INTO %s VALUES(%s)"11: % (key, createSlot(scheme)), rows)12: db.commit()13:14: if VERBOSE:15: print("%d rows are effected." % cursor.rowcount)
1 행에서 tablesScheme는 scheme.meta 파일을 읽어 생성한 딕셔너리로 tablesScheme.keys()는 모든 등록된 키를 되돌려준다. 이 후 각 키(즉 테이블 이름)으로 그 값을 받아와 scheme 리스트로 할당한다.
ASCII 파일(AW[테이블명].txt)를 열어 데이터를 받아와 한 줄씩 처리한다. rows 리스트에 담는 것은 레코드 세트(데이터 파일의 개별 행)의 값을 가진 투플로 만들어야 하므로 9 행에서는 tuple() 내장 함수로 들어오는 값을 모아 투플로 생성 후 rows 리스트에 담았다(append).
여기서 문제는 텍스트 파일에서 읽어 들인 값은 모두 문자열로 취급되고 그 데이터 타입에 대한 정보가 없으므로 이를 데이터 쉐도우 파일 scheme.meta에 기입한 데로 데이터 타입을 인식토록 하는 것이다.이를 수행하는 evalRow() 함수를 작성했고 그 내용은 아래와 같다.
1: for i in range(len(meta)):2: if data[i] == "NULL": yield None3: elif meta[i] == "str": yield data[i]4: else:5: yield eval(meta[i] + "(" + data[i] + ")")6:
1행은 첫 번째 파라메터 meta 인수로 받은 데이터 쉐도우의 필드 개수만큼 반복을 수행한다.
2행에서 value 인수로 받은 입력 할 데이터 값이 NULL로 표기 되어 있으면 파이썬의 None 타입 되돌린다.
3행에서 데이터 쉐도우의 필드 타입이 str이면 그 값 그대로 되돌린다.
4~5행에서 그 외 int나 float이면 이를 eval() 함수로 문자열 값을 evaluation 토록 했다. 아래 코드와 같이 4~5행을 작성해도 되나 직접적인 변환(casting) 보다는 파이썬이 직접 처리토록 하는 것을 선호하기 때문에 eval() 함수를 활용했다.
1: elif meta[i] == "int": yield int(data[i])2: else: yield float(data[i])
반환 값은 return 구문 대신 yield로 생성자(generator) 함수로 만들었다. 이 때문에 각 값에 대한 판별이 완료될 때마다 값을 함수 호출 지점으로 반환하며 이로 인해 이전 코드 9 행과 같이 for 루프로 해당 함수에서 나오는 값을 하나씩 처리할 수 있다.
이전 코드 11행의 createSlot() 함수는 scheme 리스트에 담긴 원소 수 만큼 물음표(?)를 쉼표로 구분해 생성하는 함수로 그 코드는 아래와 같다. 생성한 결과를 리턴 시 문자열 맨 마지막의 두 개는 잉여의 쉼표와 공백이 하나 더 붙으므로 문자열[0:-2]로 문자열의 마지막 두 개는 제외토록 제한 했다.
1: return ("?, " * len(scheme))[0:-2]
이에 관한 전체 코드는 다음과 같다. 96행 VERBOSE 변수는 1로 설정했는데, 0으로 설정할 경우 프로그램 수행 과정 상 아무런 출력을 하지 않는다. INSERT, UPDATE, DELETE 구문 수행 후 적용 받은 레코드 세트 수를 확인하려면 90 행과 같이 cursor.rowcount 속성 값을 확인하면 된다.
1: #!/usr/bin/env python2: # -*- coding: utf-8 -*-3: #-------------------------------------------------------------------------------4: # Name: adworksmigration5: # Purpose: adventureworks sample data베이스에서6: # text파일로 가져온 파일을 읽어7: # sqlite database로 migration8: #9: # Author: oscarpark10: #11: # Created: 16-11-201112: # Copyright: (c) oscarpark 201113: # Licence: licence free14: #-------------------------------------------------------------------------------15:16: def createSlot(scheme):17: """18: 주어진 리스트 원소 개수 만큼 '?, ' 를 생성하고 마지막 ', '는 제외 후 리턴19: """20: return ("?, " * len(scheme))[0:-2]21:22: def evalRow(meta, data):23: """24: meta 리스트 내 나열된 타입에 맞춰25: 같은 index의 data 리스트 내 값을 캐스팅하여 리턴함.26: """27: if not meta or not data:28: raise ValueError("meta리스트나 data리스트가 빈 리스트 입니다.")29:30: if len(meta) != len(data):31: raise ValueError("meta리스트와 data리스트 크기가 같아야 합니다.")32: try:33: for i in range(len(meta)):34: if data[i] == "NULL": yield None35: elif meta[i] == "str": yield data[i]36: else:37: yield eval(meta[i] + "(" + data[i] + ")")38: except Exception as err:39: raise (err)40:41: def getScheme(fname):42: """43: 테이블 명과 필드 타입이 들어 있는 파일을 읽어44: 키-값 쌍의 딕셔너리를 리턴함.45: """46: q = {} # create an empty dictionary for returning47: try:48: # open file49: with open(fname, "r") as fh:50: for line in fh:51: t, s = line.split(";")52: q[t] = s.replace("\n", "")53: return q54: except IOError as ioerr:55: raise (ioerr)56: except Exception as err:57: raise (err)58:59: def patchData(VERBOSE):60: import sqlite361:62: tablesScheme = getScheme("scheme.meta")63:64: db = sqlite3.connect("AdventureWorks.sdb")65:66: cursor = db.cursor()67:68: for key in tablesScheme.keys():69: if VERBOSE:70: print("TABLE: %s" % key, end=", ")71: rows = []72: scheme = tablesScheme[key].split(",")73: with open("AW" + key + ".txt", "r") as fh:74: for data in fh:75: value = data.replace("\n", "").split("\t")76: rows.append(tuple(i for i in evalRow(scheme, value)))77: """78: NOTE: 위 for loop와 아래 cursor.excutemany / db.commit의79: indentation 주의80: 여러 쿼리를 실행해야 할 경우, executemany() 메소드로 가능하다.81: 쿼리 VALUES 절에서 이하에서 각 필드값에 물음표(?)로 대체하고,82: 두번째 파라메터에 해당 값에 대응하는 투플의 리스트를 준다.83: 단 물음표 개수는 반드시 투플의 원소 개수와 일치해야 한다.84: """85: cursor.executemany("INSERT INTO %s VALUES(%s)"86: % (key, createSlot(scheme)), rows)87: db.commit()88:89: if VERBOSE:90: print("%d rows are effected." % cursor.rowcount)91:92: cursor.close()93: db.close()94:95: def main():96: VERBOSE = 197: patchData(VERBOSE)98:99: if __name__ == '__main__':100: main()101:
위 모듈 adworksmigration.py를 직접 실행 시 데이터 이전 작업이 수행되며 그 결과는 아래와 같이 출력됐다. 위 코드의 patchData() 함수 내 데이터베이스 / 파일 이용 부분에는 별도의 예외 처리는 작성하지 않았다. 99행 조건절로 인해 이 모듈을 import 시에는 patchData() 함수가 바로 실행되지 않는다.
댓글 없음:
댓글 쓰기