이번 부록 시간에는 7교시때 만들었던 쿼리 결과를 엑셀로 저장했던 코드를 함수를 이용해서 정리해 보는 작업을 해보려고 한다. 과정을 설명하는게 좀 난해하고 속을 보이는 것 같아 창피하게 느껴지니 전개가 어설프더라도 이해하기 바란다.
[목차]
0. 왜 파이썬 공부에 구글을 이용하는게 좋은가?
1. 언어를 바라보는 방법. 파이썬을 어떻게 바라봐야 할까?
2. 파이썬 설치와 환경, 버전 선택 하기의 이유.
3. 만들고자 하는 기능을 모르는 조각으로 나눠 조사해 보기
4. 데이터 베이스에서 내용 가져와 출력하기
5. 암호화 모듈을 이용해 암복호화 해보기
6. 퍼즐 조각들을 합쳐보기
7. 엑셀 파일 사용해 보기 -> 부록 : fuction 을 이용해서, 코드 정리해 보기
8. 정규표현식을 왜 사용해야 할까? 언어속의 미니 언어 정규표현식 살펴보기
9. 입력과 결과를 GUI 화면과 연결해 보기
10. Whois API 이용해 보기
11. 웹페이지 호출해 내용 파싱 하기(BeautifulSoup 그리고 한계)
12. 자동화 - 웹 자동화(with Selenium)
13. 자동화 - 윈도우즈 GUI 자동화(with pywinauto)
14. 자동화 - 작업 자동화
15. 수학 라이브러리 살펴보기
16. 그래픽 라이브러리 살펴보기
17. 머신러닝에서의 파이썬의 역활
18. 웹 프로그래밍 - Legacy Web
19. 웹 프로그래밍 - Flask 살펴보기(feat. d3.js)
20. 웹 프로그래밍 - Django 살펴보기
21. 정리 - 이런저런 이야기
[들어가면서]
함수라는 것은 일반적으로 반복적인 코드를 독립된 기능으로 분리해 냄으로서 유지보수 성을 높여주게 된다. 또 부가적인 효과로 함수로 분리된 기능을 원래 그런 기능이구나 하고 개념적으로 분류해 버림으로서, 함수를 호출하는 메인 코드들에만 집중하게 되어 가독성을 높일 수 있으며, 현재 우리가 사용하는 여러 모듈 처럼, 모듈 내의 구현된 코드를 알지 못하면서도 가져다가 다른 프로그램에서 사용할 수도 있게 된다. (메쏘드 등의 설명과 겹치는 것도 같지만, 어차피 역활상 비슷비슷한 개념이라고 봐서...)
그리고 또 하나 부수 적인 기능 하나가, 우리가 함수안 코드를 몰라도 인자만 집어 넣으면 사용할 수 있게 설계를 하는 과정에서 자연스럽게 함수 내에 영향을 미치는 여러 요소 값들이 가능한 입력을 기준으로 판단하게 되는 '일반화' 가 된다는 것이다. 하드 코딩이 사라진다고 얘기해도 될 듯 하다.(뭐 바람직한건 아니라고들 많이 얘기하지만 글로벌 변수를 참조할 수는 있을 건 같다) 함수에 대한 얘기는 아래 링크들을 참조 해 보길 바란다.
http://www.hackerschool.org/Sub_Html/HS_University/BOF/essential/PDF_Files/07.pdf
http://python-guide-kr.readthedocs.io/ko/latest/writing/structure.html#object-oriented-programming
그럼 원래 코드를 함 봐보자.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 |
#-*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
#from openpyxl.compat import range
import pymssql
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = "output"
conn = pymssql.connect(server='localhost', user='pyuser', password='test1234', database='mytest')
cursor = conn.cursor()
column_char = 'a'
# supermarket 의 컬럼들 가져옴
cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'supermarket\';')
# 한행 씩 가져오면서
row = cursor.fetchone()
while row:
# 컬럼 문자를 하나씩 증가하면서 해당 행을 넣음.
ws[column_char + '1'] = row[0]
column_char = chr(ord(column_char) + 1)
row = cursor.fetchone()
# 2번째 행 표시
column_num = 2
# supermarket 테이블의 내용을 가져온다.
cursor.execute('SELECT Itemno, Category, FoodName, Company, Price FROM supermarket(nolock);')
# 한 행씩 가져오면서
row = cursor.fetchone()
while row:
# 예전 수동 타자기 처럼, 새로운 줄이 오게 되면, 첫째 셀 a 로 돌아가는 초기 값
column_char = 'a'
# 1~5 까지 x 가 변하면서 컬럼 문자, row를 하나씩 늘여 결과를 하나씩 담음.
# ws['a1'] = row[0], ws['b1'] = row[1], ws['c1'] = row[2]...
for x in range(1, 6):
ws[column_char + str(column_num)] = row[x-1]
column_char = chr(ord(column_char) + 1)
# 다음 행을 표시하기 위해 뒤의 숫자 증가
column_num = column_num + 1
row = cursor.fetchone()
# 파일을 실제 저장
wb.save("test.xlsx") |
cs |
[함수로 기능 분리 해보기]
함수로 분리할 부분을 생각해 보면 크게 2가지가 있다. 컬럼명 가져와서 엑셀에 저장하기, 테이블 내용 가져와서 엑셀에 저장하기 2가지 이다.
그런데 개인적으로 생각하기에 두 번째 테이블 내용 가져오는 쪽의 select 쿼리에 컬럼 명들이 필요하기(위에서는 'select Itemno, Category, .... , Price' 이 부분) 때문에 첫번째 컬럼명 가져와서 엑셀에 저장하기 기능은 컬러명 가져오기 기능과, 엑셀에 저장하기 기능으로 나누려고 한다. 그리고 컬럼명 가져오기 기능의 결과는 두 번째 컨텐츠 가져올때 인자로 재 사용 하고 싶다(물론 무시하고 select * 카드로 가져와도 되지만, 혹시 나중에 특정 컬럼만 가져오는 기능을 원할 수도 있기 때문에 컬럼명을 명시적으로 정하는 부분을 그대로 쓰려 한다)
그럼 대충 분리 시키려는 내용을 그려보면 아래와 같다. 뭐 기능이나 취향에 따라서 두 번째 테이블 내용 엑셀에 저장하는 기능을 첫 번째 엑셀을 저장하는 함수에 머지해도 무방할 듯도 싶지만, 일단은 따로 분리할 필요는 없을 듯해서, 같이 넣었다.
그럼 A) 컬럼 이름 얻어오기 부터 보자. 처음에는 얻어온 값을 Array 에 담아서 저장하려 했다. 컬럼 숫자나 순서대로 쓰기 유용할 것 같아서 말이다. 근데, 구글에 'python array' 라고 검색했을 때, 아래와 같이 array 보다는 list 쪽이 저장하는 데이터 형이나, 사용 면에서 편리 할 거 같아서 list 로 방향을 틀었다.
http://stackoverflow.com/questions/9405322/python-array-v-list
그럼 리스트에 컬럼 조회한 값을 하나씩 넣어야 되는데, 문법을 알기 위해서 'python list add' 라고 검색한다. 아래의 페이지를 보면 append 라는 명령어를 써야 하는 것 같다.
https://www.tutorialspoint.com/python/list_append.htm
너무 자세하면 지루할 수 있으므로, 해당 부분을 이용해 만든 코드는 아래와 같다. select 쿼리 내의 \' 표시는 문자열 내에 ' 문자가 필요할때 쓰는 escape 문자 이다. escape 문자 설명은 아래를 참고. 위의 그림과 동일하게 입력은 테이블 이름을, 출력은 배열로 출력한다.
http://egloos.zum.com/pythondev/v/125926
|
1
2
3
4
5
6
7
8
9
10 |
# 컬럼이름 얻어오기
def getColName(tName):
cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'' + tName + '\';')
row = cursor.fetchone()
cName = []
while row:
cName.append(row[0])
row = cursor.fetchone()
return cName |
cs |
두번째, B) 컬럼이름 엑셀에 저장 부분은 기존 코드를 거의 그대로 가져온다. 입력으로 컬럼 이름들이 담긴 배열을 담고, 루프를 돌리기 위해서 구글에서 'python list loop' 로 검색 한다. 첫번째 페이지의 샘플에 잘 나와 있는데로("for 변수 in 리스트"), 코드를 구현한다.
https://learnpythonthehardway.org/book/ex32.html
|
1
2
3
4
5
6 |
# 엑셀에 컬럼 저장
def saveColName(cName):
column_char = 'a'
for name in cName:
ws[column_char + '1'] = name
column_char = chr(ord(column_char) + 1) |
cs |
세번째 C) select 쿼리 부분 제작은 컬럼 들이 A, B, C, D.. 이런식으로 되어야 나중에 쿼리에 끼워 넣는데, 문법에 맞추려면 마지막 E 뒤의 ',' 는 제거 되어야 한다. for 문을 돌리면서 리스트 마지막 요소일때 ',' 를 안 붙이는 방법도 있겠지만, 그냥 무조건 ',' 를 붙이고 마지막 글자 하나를 빼서 마지막에 붙은 ',' 를 없애도록 만드려고 한다. 해당 기능을 위해 구글에서 'python substring' 을 검색 한다. 아래 페이지를 참조해서 맨 마지막 문자를 잘라낸다.("문자열[:-1]")
http://stackoverflow.com/questions/663171/is-there-a-way-to-substring-a-string-in-python
|
1
2
3
4
5
6
7 |
# 컬럼 쿼리 만들기 Category, Food, Price
def makeColumnQuery(cName):
sCol = ''
for name in cName:
sCol = sCol + name + ','
sCol = sCol[:-1]
return sCol |
cs |
네번째 D) 테이블 컨텐츠 엑셀에 저장 하는 부분이다. 아까 얘기했듯이 엑셀을 저장하는 부분을 따로 떼어내거나 B) 기능과 합칠 수도 있겠지만, 그냥 D 기능에서 조회와 엑셀 저장을 하는게 현재 용도로는 깔끔하게 느껴져서, 그렇게 구현했다. 쿼리 부분을 보면 컬럼 리스트 넣는 부분을 위해 C) 기능(makeColumnQuery)을 이용해 컬럼을 얻어 쿼리에 조합 시켰다. 하나 또 달라진 부분은 아까 얘기한 일반화를 위해서, for 루프 안의 range 값을 고정된 숫자 6에서 인자로 넘어온 컬럼 리스트의 숫자+1 을 하도록(len(cName)+1) 바꾸었다. 이렇게 함으로써 테이블이 다른 테이블로 바뀌게 되더라도, 자동으로 컬럼 수를 추출해서 엑셀로 저장할 수 있게 기능이 일반화가 된다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
def getTableContent(tName, cName):
sCol = makeColumnQuery(cName)
cursor.execute('SELECT ' + sCol + ' FROM ' + tName + '(nolock);')
row = cursor.fetchone()
column_num = 2
while row:
column_char = 'a'
for x in range(1, len(cName)+1): #컬럼수 참조하게 변경
ws[column_char + str(column_num)] = row[x-1]
column_char = chr(ord(column_char) + 1)
column_num = column_num + 1
row = cursor.fetchone() |
cs |
마지막으로 해당 함수들을 호출하는 실제 메인 코드를 보면 아래와 같이 나름 심플해 진다. "컬럼 이름을 얻어와 -> 컬럼이름을 저장하고 -> 테이블 조회의 인자로 넘겨주어, 내용을 조회해 저장한다". 각각 함수의 용도를 안 다면 함수의 세부 기능 코드를 무시하게 되고, 그럼 메인 코드에만 집중해서 함수를 사용하면(사실 함수 뿐만 아니라 클래스등 모든 고급 문법 요소가 그런지도 모른다) 가독성이 높아진다는 것을 증명하게 되지 않는가 싶다.
|
1
2
3
4 |
# 실행
colName= getColName(tableName)
saveColName(colName)
getTableContent(tableName, colName) |
cs |
[합쳐 보기]
그럼 완성된 전체 코드를 봐보자.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67 |
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
#from openpyxl.compat import range
import pymssql
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = "output"
conn = pymssql.connect(server='localhost', user='pyuser', password='test1234', database='mytest')
cursor = conn.cursor()
tableName = 'supermarket'
# 컬럼이름 얻어오기
def getColName(tName):
cursor.execute('SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'' + tName + '\';')
row = cursor.fetchone()
cName = []
while row:
cName.append(row[0])
row = cursor.fetchone()
return cName
# 엑셀에 컬럼 저장
def saveColName(cName):
column_char = 'a'
for name in cName:
ws[column_char + '1'] = name
column_char = chr(ord(column_char) + 1)
# 컬럼 쿼리 만들기 Category, Food, Price
def makeColumnQuery(cName):
sCol = ''
for name in cName:
sCol = sCol + name + ','
sCol = sCol[:-1]
return sCol
# 컨텐츠 가져오기
def getTableContent(tName, cName):
sCol = makeColumnQuery(cName)
cursor.execute('SELECT ' + sCol + ' FROM ' + tName + '(nolock);')
row = cursor.fetchone()
column_num = 2
while row:
column_char = 'a'
for x in range(1, len(cName)+1): #컬럼수 참조하게 변경
ws[column_char + str(column_num)] = row[x-1]
column_char = chr(ord(column_char) + 1)
column_num = column_num + 1
row = cursor.fetchone()
# 실행
colName= getColName(tableName)
saveColName(colName)
getTableContent(tableName, colName)
# test2.xlsx 파일에 저장하기
wb.save("test2.xlsx") |
cs |
해당 내용을 c:\python\code 폴더에 excel_save_function.py로 저장하고(역시 모르면 2교시 참고), 코드를 실행해 본다. 실행 후 폴더 안을 보면 test2.xlsx 파일이 저장됨을 볼 수 있다. 여기서 만든 함수는 나중에, API 호출 하여 엑셀에 저장되는 기능을 만들 때 재 사용 될 예정이다.
c:\Python\code>python excel_save_function.py
[마무리 하면서]
데이터베이스에서 새로운 테이블을 만들고 코드 중 "tableName = 'supermarket'" 부분의 테이블 이름만 수정하면 해당 테이블의 내용을 가져다 저장할 수 있다(지금 다시 생각해 보니 getTableContent 안에서 saveColName 호출해서 아예 컬럼과 내용을 모두 저장하는 것도 괜찮아 보이긴 한다). 그리고 데이터베이스 내의 테이블 리스트를 모두 얻어오는 쿼리를 이용하면(구글에서 'mssql table name' 으로 검색) 이 함수들을 거의 그대로 이용해서 루프를 만들어 데이터베이스 내의 모든 테이블 내용을 10행식 조회해(select top 10 ...) 엑셀에 저장하게 할 수도 있을 것이다. 물론 그 경우 엑셀에 저장하는 코드 부분은 테이블 끼리 서로 쓰는 영역이 겹치지 않도록 적당히 수정되야 할 것이다.
여기서 예를 들은 부분이 정말로 함수를 잘 설명했는지는 알수 없지만(사실 상황에 따라 달라져 정답이 정확히 있는거 같지도 않고, 들이는 시간도 고려해 실용성과 재사용성을 고려하는 것이 맞을 듯하다), 어느 정도는 함수를 만드는 도중에 만나는 문제와 만든 후의 모습은 보여 줄 수 있었다고 생각하며 글을 마친다.
[보충]
문의가 와서 보니 openpyxl 최신버전에서는 아래 import 문이 에러가 나네요. 지금 보니 사실상 코드안에서 사용하지 않는 부분이라서 아예 빼버림 에러가 발생안합니다
from openpyxl.compat import range
|
2017.3.1 by 자유로운설탕 |
cs |
'프로그래밍' 카테고리의 다른 글
| 구글로 공부하는 파이썬 - 9교시 (GUI 프로그램 만들어보기) (0) | 2017.03.05 |
|---|---|
| 구글로 공부하는 파이썬 - 8교시 (정규표현식 소개) (0) | 2017.03.04 |
| 구글로 공부하는 파이썬 - 7교시 (엑셀 다뤄보기) (3) | 2017.02.26 |
| 구글로 공부하는 파이썬 - 6교시 (해결된 조각 합치기) (0) | 2017.02.25 |
| 구글로 공부하는 파이썬 - 5교시 (암호화 모듈 사용) (8) | 2017.02.23 |