[Python] 업무자동화 실습 : 강남구 애견미용샵 txt Data 가공하여 DB에 넣기

 

 [ 작업 환경 ]

Centos 7.3 & Python 3.7.5

 

 

insert_store_information_ver1.0.0.py

0.00MB

 

 

1. 데이터 샘플

 

 

2. 코드

#################### 패치노트 ####################
#   2019-11-26  ver1.0.0    파일 생성
#   2019-11-27  ver1.0.0    완성
##################################################

#################### 변수1 ####################
store_list_location = '애견샵압축_20191125'     #   가게 폴더들 리스트의 위치

##################################################

#################### 변수2 ####################
target_host = 'localhost'
target_DB = 'test_ver100'
target_table = 'store_information'
target_port = 3306
target_user = 'testuser'
target_passwd = 'testpasswd'
##################################################


import os

os.chdir(store_list_location)
store_list = os.listdir('.')

total_data_list = list()
DB_insert_Data = list()

# RAW DATA 읽기
for each_store in store_list:
    with open(each_store + '/' + '정보.txt', 'r') as raw_information:
        lines = list()
        while True:
            temp = raw_information.readline()
            if not temp:
                break
            elif temp == '\n' or temp == ' ' or temp == '  ':
                pass
            else:
                temp = temp.replace('\n', '')
                lines.append(temp)
    total_data_list.append(lines)

# DATA 가공
for i, each_store in enumerate(total_data_list):
    if ' / ' in each_store[0]:
        name = each_store[0].split(' / ')[0]
        coordinate = each_store[0].split(' / ')[1]
    else:
        name = each_store[0]
        coordinate = ''
    road_name_address_kor = each_store[1]
    if ' (우) ' in each_store[2] and '지번)' in each_store[2]:
        location_old_kor = each_store[2].split('지번)')[1].split(' (우) ')[0]
        postcode = each_store[2].split(' (우) ')[1]
    elif ' (우) ' in each_store[2] and '지번)' not in each_store[2]:
        location_old_kor = each_store[2].split(' (우) ')[0]
        postcode = each_store[2].split(' (우) ')[1]
    else:
        location_old_kor = each_store[2].split('지번)')[1]
        postcode = ''
    call_num = each_store[3]
    try:
        opening_hours = each_store[4]
    except IndexError:
        opening_hours = ''
    DB_insert_Data.append([call_num.strip(), name.strip(), road_name_address_kor.strip(), location_old_kor.strip(), postcode.strip(), coordinate.strip(), opening_hours.strip()])

print('[INFO] Insert_Data Setting Complete.')


# 데이터 삽입
import pymysql

conn = pymysql.connect(host=target_host, port=target_port, db=target_DB, user=target_user, passwd=target_passwd, charset='utf8')
curdic = conn.cursor(pymysql.cursors.DictCursor)
insert_store_information_sql = "INSERT INTO store_information (call_num, name, road_name_address_kor, location_old_kor, postcode, coordinate, opening_hours) VALUES (%s, %s, %s, %s, %s, %s, %s)"
cur = conn.cursor()
print('[INFO] Connection Success.')
for each_store in range(len(total_data_list)):
    cur.execute(insert_store_information_sql, DB_insert_Data[each_store])
print('[INFO] Insert Done.')
conn.commit()
print('[INFO] DB Disconnect Success.')