잡학문구

Why RDS Proxy?

Lambda 함수는 마이크로 VM에서 실행됩니다. Lambda는 호출 간에 마이크로 VM을 재사용합니다. 이러한 이유로 핸들러 외부(init 코드에서) 데이터베이스에 대한 연결을 생성하고 열어 두는 것이 좋습니다. 마이크로 VM이 완전히 격리되어 있으므로 서버에서와 같이 연결 풀을 사용할 수 없습니다. 이러한 이유로 우리는 RDS 프록시를 만들었습니다 . RDS 프록시는 데이터베이스에 대한 연결 풀을 유지합니다. Lambda 함수는 데이터베이스에 직접 연결하는 대신 프록시에 연결합니다.

.XLSM 파일이란? 

확장자가 XLSM인 파일은 매크로를 지원하는 스프레드시트 파일 유형입니다. 매크로는 Visu

al Basic Editor를 사용하여 Excel 통합 문서 내에서 Microsoft의 VBA(Visual Basic for Applications)로 프로그래밍되며 직접 실행할 수 있습니다.

엑셀 xlsm 형식의 파일을 다룰 수 있는 파이썬 라이브러리는  openpyxl , xlwings, editpyxl, asposecells, pandas 등등 많은 라이브러리가 있습니다.

openpyxl  라이브러리는 xlsm 파일 저장 시 컨트롤 버튼이 사라지는 현상이 있습니다.

본 문서에서는 xlwings 라이브러리를 사용하도록 하겠습니다.

🟢 파이썬 환경

-OS : Window10

- Python : 3.10.1

- xlwings : 0.30.10

- pip : 23.2.1

🟢 xlwings  인스톨

xlwings 라이브러리를 사용하기 위해 인스톨합니다.

pip install xlwings

🟢 xlsm 파일 열고 저장하기

파일이 있는 절대 경로를 지정하거나 작업하는 디렉토리에 있는 파일이 있어야 합니다. 윈도우의 디렉토리 경로는 '/'를 쓰거나 '\\' 를 사용합니다. '\' 문자는 escape 문자이기때문에 하나를 더 써야 합니다.

아래는 xlsm 파일을 열고 A1 열에 데이터를 쓴 후 저장하는 파일썬 파일입니다.

import xlwings as xw

work_dir = 'D:/python_project/'
wb = xw.Book(work_dir + 'excel_macro.xlsm')

sheet = wb.sheets['Sheet1']
sheet.range('A1').value = 'xlwings 라이브러리'

wb.save()

다른 이름으로 저장하려면 다음과 같이 save 함수에 저장하고자 하는 파일명을 쓰면 됩니다.

wb.save(work_dir + 'result_excel_macro.xlsm')

🟢 범위 확장

아래와 같이 쓰면 A1 셀을 기준으로 2개의 행과 2개의 열에 값을 입력합니다.

sheet['A1'].value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
sheet['A1'].expand().value

📑 엑셀 Macro에서 파이썬 호출

xlwings 엑셀 Add-in 설치

명령창에 아래와 같이 명령어를 실행하여 xlwings add-in 을 엑셀에 설치합니다.

xlwings addin install

엑셀에서 도구모음. 

엑셀에 추가된 add-in 메뉴의 xlwings에서 [Run main] 버튼을 클릭하거나 RunPython 함수를 사용하여 VBA에서 Python 함수를 호출할 수 있습니다.

[Run main]  버튼에는 엑셀파일과 같은 이름의 Python 모듈에 main이라는 함수가 필요합니다. 이 방식은 엑셀 문서에 매크로를 사용할 필요 없이 xlsx로 저장할 수 있습니다.

엑셀 파일이 있는 위치에 hello_world.xlsx 파일을 만들고 같은 위치에 hello_world.py 파일을 아래와 같이 생성합니다.

# hello_world.py

# hello_world.py
import xlwings as xw

def main():    
    wb = xw.Book.caller()
    wb.sheets[0]['A1'].value = 'Hello World!'

엑셀파일에서 xlwings 메뉴의 [Run main] 버튼을 클릭합니다. 아래와 같이 A1 Cell에 Hello World! 문자가 입력됩니다.

모듈 이름과 관계없이 Python 함수를 호출하려면 RunPython을 사용합니다.

엑셀의 VBA에 [도구] - [참조] 버튼을 클릭하고 xlwings를 참조하도록 선택합니다.

RunPython 함수를 사용한 VBA 스크립트 입니다. hello.xlsm 파일을 만들고 아래처럼 VBA 스크립트를 작성합니다.

Sub HelloWorld()
    RunPython "import hello; hello.world()"
End Sub

기본적으로 "RunPython"은 Excel 과 파이썬 파일이 동일한 디렉터리에 같은 이름을 가졌다고 가정하지만, 파일 이름과 디렉토리 위치를 다르게 지정할 수 있습니다. Python 파일이 다른 폴더에 있는 경우 PYTHONPATH에 해당 폴더를 추가합니다. 파일 이름이 다른 경우 RunPython 명령어에 파일 이름을 지정합니다.

xlwings.Book.caller()를 사용하여 Excel  Workbook을 호출하는 예제입니다.

아래는 hello.py 파일 내용입니다.

# hello.py
import xlwings as xw

def world():
    wb = xw.Book.caller()
    wb.sheets[0]['B1'].value = 'RunPython : Hello World! '

이제 위에서 작성한 엑셀의 "HelloWorld" VBA 모듈을 실행합니다.

데이터가 없는 마지막 row 찾기

num_row = sheet.range('A1').end('down').row

데이터가 없는 마지막 column 찾기

num_col = sheet.range('A1').end('right').column

⚙ 예제 소스 : 

# 1. ERWind에서 csv 파일 생성
# [복제], [외부], [연계] row 삭제
# 3. xlsx 복사해서 표준 xlsm 파일로 붙여넣기
# 4. 매크로 실생
# 5. 저장

import win32com.client
import xlwings as xw

work_dir = 'D:/ERD/report/entity.csv/'
csv_wb = xw.Book(work_dir + 'SSGD_validation.csv')
xls_wb = xw.Book(work_dir + 'TOBE_SSGD_표준관리_로컬_v1.0.xlsm')
csv_sheet = csv_wb.sheets['SSGD_validation']
xls_sheet1 = xls_wb.sheets['변환기']
xls_sheet2 = xls_wb.sheets['테이블컬럼점검']

all_data_range = csv_sheet.range ("A1").expand ('table')
all_data_range.api.WrapText = False

# [복제], [외부], [연계] 삭제
check_char = '['
before_del_last_row = csv_sheet.range(1,1).end('down').row
for i in range(before_del_last_row, 1, -1):
    if csv_sheet.range(i,1).value[0] == check_char:
        csv_sheet.api.Rows(i).Delete()

# [복제], [외부], [연계] 삭제 후 row, column 건수
last_row = csv_sheet.range(1,1).end('down').row
csv_sheet.range(1,11).value = 'DataType Check'
csv_sheet.range((2,11), (last_row, 11)).options(ndim=2).value = '=exact(H2,J2)'
# last_col = csv_sheet.range(1,1).end('right').column

# 데이터를 복사하기 전에 표준 xlxm 파일을 정리한다.
standard_last_row = xls_sheet1.range(1,1).end('down').row
xls_sheet1.range((2,1), (standard_last_row, 3)).options(ndim=2).value = None
standard_last_row = xls_sheet2.range(1,1).end('down').row
xls_sheet2.range((2,1), (standard_last_row, 10)).options(ndim=2).value = None
xls_sheet2.range((2,13), (standard_last_row, 13)).options(ndim=2).value = None
xls_sheet2.range((2,14), (standard_last_row, 14)).options(ndim=2).value = None

# cvs 내용을 xlsm 으로 전체 복사
xls_sheet2.range((2,1), (last_row, 10)).options(ndim=2).value = csv_sheet.range((2,1), (last_row, 10)).options(ndim=2).value

# cvs 에서 엔터티명을 xlsm 으로 복사
xls_sheet1.range((2,1), (last_row, 1)).options(ndim=2).value = csv_sheet.range((2,1), (last_row, 1)).options(ndim=2).value

# [표준체크] Macro 수행
ko_to_en_macro = xls_wb.macro('한영변환')
ko_to_en_macro()
# [표준체크] Macro 수행결과를 [테이블컬럼점검] tab의 테이블명으로 복사
xls_sheet2.range((2,13), (last_row, 13)).options(ndim=2).value = xls_sheet1.range((2,3), (last_row, 3)).options(ndim=2).value

# cvs 에서 속성명을 xlsm 으로 복사
xls_sheet1.range((2,1), (last_row, 1)).options(ndim=2).value = csv_sheet.range((2,4), (last_row, 4)).options(ndim=2).value
# [표준체크] Macro 수행
ko_to_en_macro = xls_wb.macro('한영변환')
ko_to_en_macro()

# [표준체크] Macro 수행결과를 [테이블컬럼점검] tab의 컬럼명으로 복사
xls_sheet2.range((2,14), (last_row, 14)).options(ndim=2).value = xls_sheet1.range((2,3), (last_row, 3)).options(ndim=2).value


# [테이블컬럼점검] tab을 활성화(Visible)
xls_sheet2.activate()

all_data_range = xls_sheet2.range ("A1").expand ('table')
all_data_range.api.WrapText = False

xls_wb.save()
# csv_wb.save()

Amazon RDS 서비스 중 하나인 Restore to point in time 를 수행해 본다.

데이터가 어느정도 복구가 되는지 확인하기 위해 아래의 순서로 테스트를 진행했다.

1. Aurora (MySQL Compatible) RDS 생성

2. 생성된 DB에 3천만건을 입력하는 프로시저를 수행(매 건마다 commit 수행)

3. 데이터 입력 중 DB 중지

4. 중지 중 "Restore to point in time" 수행하여 새로운 DB로 복구

5. 소스 DB의 데이터 입력 건수와 복구한 DB에 입력된 데이터 건수 비교

* 두 DB간의 데이터가 상당한 차이가 있었다.

"Restore to point in time" 수행 시 Restore time 을 선택 또는 입력하도록 되어 있는데 

Lastest restorable time 시간을 보면 DB가 내려가는 시간과 약 2분여 정도의 차이가 있었다. 그래서

Custom date and time으로 DB가 내려간 시간을 선택하니 아래와 같은 메시지가 출력되었다.즉 Latest Restorable Time 보다 더 최신 시간을 지정하여 복구할 수 없다는 얘기다.

"Please specify RestoreToTime between Earliest Restorable Time and Latest Restorable Time of source cluster."

결론은 완전 복구를 할 수 없다?

• PL / SQL Developer Version : 14.0.6.1988

PL/SQL Developer 세미콜론(;) 단위로 실행 설정

Configure > Preferences > Wndow Types 카테고리 아래의 SQL Window > Autoselect statement 에 체크를 해주면 세미콜론(;)을 구분하여 하나씩 실행이 된다. 

또는 Configure > Preferences 클릭 후 화면이 나타나면 우측 상단의 검색창에 "Autoselect statement" 입력 후 검색한 후 목록에 나타나면 체크해 준다.

설정 이후부터는 F8 키를 누르면 현재 커서가 있는 SQL 문장을 수행한다.

Font 설정

Configure > Preferences > User Interfaces 카테고리 아래의 Fonts를 클릭하여 Brower, Grid, Editor 에 대한 font 를 각각 설정한다.

실행계획

Plan을 확인할 SQL 문장 전체를 선택한 후 F5를 클릭한다.

Command Window(명령창)

File > New> Command Window를 클릭하여 명령창이 나타난다. 명령창에서 용이하게 사용할 수 있는 desc, set timing on, set time on 등의 명령어를 사용할 수 있다.

종료하려면 exit 명령어를 수행하면 된다.

DB 접속 설정

Configure 메뉴의 Connections를 클릭한다.

접속환경 설정 창에 아래와 같이 각 해당 항목을 입력한다.

• Display : 화면에 표시할 이름
• Image : 화면에 표시할 이미지
• Username : DB 계정
• Database : Database 이름
• Password : 패스워드

DB 접속

방법 1) Session 메뉴의 "Log on" 버튼을 클릭한 후 아래와 같은 화면이 나타나면 계정, database 를 입력하고 [OK] 버튼을 클릭하면 DB에 접속된다.

방법 2) DB 접속 설정에서 설정한 정보로 DB에 접속하는 방법으로 Session 메뉴의 "Set Main Connection"을 클릭하면 이전에 설정한 정보들이 보이고 이를 클릭하여 DB에 접속할 수 있다.

Data Generator

Tools 메뉴의 Data Generator 메뉴를 클릭한다. 데이터 생성기를 사용하면 데모 및 테스트 데이터를 만들 수 있다. 

데이터 생성을 원하는 테이블을 선택하고 입력할 데이터 수를 입력한다.

• Owner : 테이블 오너
• Table : 테이블명
• Number of records : 입력할 데이터 수
• Name : 컬럼명, Type : 데이터타입, Size : 컬럼사이즈
• Data : 입력할 데이터(Dataset, Function, Character 등을 이용해서 적용)

Data 패치건수 조정

PLSQL Developer 의 [Configure] - [Preferences] 메뉴를 클릭한 후 나타난 화면에서 아래와 같이 

[Window Types] 항목의 [SQL Window]를 클릭한 후 우측의 "Records per Page"의 "Fixed" 항목에 패치하고자 하는 row 건수를 입력하면 된다. 아래 예시는 100건을 패치하도록 설정된 화면이다.

ERWin 에서 논리 모델링의 속성 순서를 변경할 경우, 물리 모델의 컬럼 순서는 변경되지 않는다.

이를 맞춰주기 위해서는 물리 모델의 컬럼 순서도 직접 변경해야 하나, 상당히 번거로울 수 있다.

한번에 논리모델 기준으로 컬럼 순서를 맞추는 방법을 기술한다.

아래와 같은 논리 엔터티와 이에 대응하는 물리 테이블이 있다고 가정한다.

사원부서배치 엔터티의 PK 속성 위치를 부서번호,사원번호 순서에서 사원번호, 부서번호 순서를 바꾸는 경우 물리 ERD의 컬럼 순서를 확인해 보면 순서가 변경되어 있지 않을 것을 확인할 수 있다.

이럴 경우에 논리/물리 속성의 순서를 동일하게 하는 작업을 해주면 된다.

1. Physical Model 에서 대상 테이블을 선택한 후 마우스 오른쪽을 클릭한 다음 "COlumn Porperties"를 클릭한다. 

2. 아이콘 중에서 "Reset Order" 아이콘을 클릭한다.

3. 아래와 같이 메뉴가 뜬다.

• Reset order to match attribute order : 선택한 테이블의 컬럼 순서를 논리 모델의 속성 순서와 정렬을 일치시킨다.
• Reset all... : 물리 모델 전체 컬럼 순서를 논리 모델 속성 순서와 일치시킬 수 있는 화면이 나타난다.

위와 같은 화면이 나타나면 

Reset all Tables in model 과 Reset column order to match attribute order 를 선택한 후 [OK] 버튼을 클릭한다.

위의 모델에 적용하면 아래와 같이 논리모델의 속성 순서와 동일하게 정렬된 것을 볼 수 있다.

VScode extension 으로 MySQL DB 연결하기

VSCode에서 MySQL용 Extensions을 설치하고 연결하고자 하는 MySQL DB의 정보로 접속합니다.

1단계: Visual Studio Code를 엽니다.

2단계: Extensions로 이동하여 MySQL 확장을 검색하고 "MySQL"을 입력합니다.

             - 아래 화면의 Extensions(①)을 클릭한 후 검색란(②)에 "MySQL"을 입력한다.

3단계: "MySQL" 확장 프로그램을 설치합니다.

            - 검색된 Extension 중 "MySQL(Database manager for MySQL/MariaDB, PostgreSQL, SQLite, Redis and ElasticSearch)" 을 [Install] 버튼을 클릭하여 설치합니다.

4단계: 좌측 메뉴발를 보면 "Database"와 "NoSQL" 메뉴가 추가된 것을 볼 수 있습니다.

5단계: "Database" 아이콘을 클릭하면 "Create Connection" 버튼이 보여집니다.버튼을 클릭합니다.

6단계: 접속할 DB의 정보를 입력합니다. 본 문서에서는 Local 컴퓨터에 MySQL 서버가 설치되어 있어 localhost(127.0.0.1)를 입력했습니다.

• 호스트: localhost(127.0.0.1) 또는 접속하고자 하는 서버의 IP Address 또는 endpoint
• 사용자: db user
•  비밀번호: 비밀번호
• 포트번호 : 3306

7단계: " + Connect" 버튼을 클릭하여 DB에 접속합니다.

8단계: 촤측 데이터베이스 메뉴에 localhost(127.0.0.1) 연결이 추가된 것을 볼 수 있습니다. 

9단계: 터미널을 이용하려고 아래 그림과 같이 "Open Terminal"을 클릭하면 "Free account not support open terminal!" 이라는 메시지가 출력된다. Free 버전에서는 지원이 안되고 년간 20$를 지불하는 Premium은 지원된다. 20$를 지불하면서까지 사용하고 싶은 생각은 없다.

-> 2023.09.26 현재 기준 Terminal 기능이 무료로 지원된다. "Open Termial" 을 클릭하면 아래와 같은 MySQL DB command line 화면이 보여진다.

10단계: MySQL의 각 스키마를 클릭한 후 아래 화면의 "Open "을 클릭하여 SQL 편집기를 연다.

11단계: SQL 편집기에 원하는 SQL을 입력하고 "Execute" 버튼을 클릭하거나 "Ctlr + Enter"를 친다. 입력한 SQL 문장에 마우스 오른쪽을 클릭하면 SQL 실행을 할 수 있는 여러 메뉴들이 뜬다.

 

  12단계: 이 MySQL 쿼리를 실행하면 아래창에 결과가 출력된다.

VSCode를 사용하여 MySQL 서버에 연결하기

VSCode에서 MySQL DB에 연결하기 위해 MySQL용 Extensions을 설치하고 연결하고자 하는 DB의 정보를 입력하여 접속합니다.

1단계: Visual Studio Code를 엽니다.

2단계: Extensions로 이동하여 MySQL 확장을 검색하고 "MySQL"을 입력합니다.

             - 아래 화면의 Extensions(①)을 클릭한 후 검색란(②)에 "MySQL"을 입력한다.

3단계: "MySQL" 확장 프로그램을 설치합니다.

            - 검색된 Extension 중 "MySQL(MySQL management tool)" 을 [Install] 버튼을 클릭하여 설치한다.

4단계: 탐색기 옵션을 클릭하거나 ( Ctrl + Shift + E )를 누르면 MySQL이 이 섹션에 추가된 것을 볼 수 있습니다.

5단계: "MYSQL" 우측의 "+"를 클릭하면 database 의 host 정보를 입력하라는 창이 보여집니다. host, user, password, port, SSL certificate path 를 입력하는 창이 순서대로 나타납니다. 접속하고자 하는 DB의 정보를 각각 이력합니다. 

6단계: 서버 주소를 입력합니다. 본 문서에서는 컴퓨터에 MySQL 서버가 설치되어 있으므로 localhost를 입력했습니다.

7단계: 그런 다음 DB계정, 암호, user, password, port, SSL certificate path 를 차례를 입력합니다. SSL certificate path 가 없으면 입력하지 않습니다.

• 호스트: localhost
• 사용자: db user
•  비밀번호: 비밀번호
• 포트번호 : 3306
• SSL certificate path : 해당사항 없으면 입력하지 않음

8단계: 데이터베이스에 액세스할 수 있는 MySQL 섹션에 localhost 연결이 추가된 것을 볼 수 있습니다. 

 

9단계: 데이터베이스에 연결하려면 localhost를 마우스 오른쪽 버튼으로 클릭하고 새 쿼리를 클릭합니다.     

CREATE DATABASE myrestaurant ;
CREATE TABLE IF NOT EXISTS myrestaurant.users(
    id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    phone VARCHAR(200),
    address VARCHAR(200),
    password VARCHAR(200) NOT NULL
);
INSERT INTO myrestaurant.users(name,phone,address,password)
VALUES
('Gaurav','123456789','Mumbai,India','pass134'),
('Sakshi','987654321','Chennai,India','pass456');

 11단계: 이 데이터베이스에 추가된 'users'라는 새 테이블을 보려면 연결을 새로 고칩니다. 'users'를 마우스 오른쪽 버튼으로 클릭하고 '상위 1000개 선택'을 클릭합니다.

 12단계: 이 MySQL 쿼리를 실행하고 결과를 확인합니다.

flyway migrate 수행 시 아래와 같은 오류가 발생하는 경우

jdbc Url 뒤에 ‘allowPublicKeyRetrieval=true&useSSL=false 를 붙여주면 됩니다.

SQL State  : S1009
Error Code : 0
Message    : Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)

Caused by: java.sql.SQLTransientConnectionException: Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)
Caused by: java.sql.SQLException: RSA public key is not available client side (option serverRsaPublicKeyFile not set)

flyway.url=jdbc:mysql://127.0.0.1:3306/database_name?allowPublicKeyRetrieval=true&useSSL=false