47 lines
1.4 KiB
Python
47 lines
1.4 KiB
Python
import sqlite3
|
|
import openpyxl
|
|
import logging
|
|
|
|
# 로거 인스턴스 가져오기
|
|
logger = logging.getLogger('default_logger')
|
|
def read_excel_data(filename, sheet_name, start_row, end_row):
|
|
workbook = openpyxl.load_workbook(filename, data_only=True)
|
|
sheet = workbook[sheet_name]
|
|
|
|
data = []
|
|
for row in sheet.iter_rows(min_row=start_row, max_row=end_row, values_only=True):
|
|
data.append(row[0]) # assuming the data is in the first column (B)
|
|
|
|
return data
|
|
|
|
def parse_data(data):
|
|
parsed_data = []
|
|
for item in data:
|
|
if item: # Check if the item is not None or empty
|
|
cat_code = item[:10]
|
|
categories = item[11:].split("-", maxsplit=3)
|
|
while len(categories) < 4:
|
|
categories.append(None) # Fill with None if less than 4 categories
|
|
parsed_data.append((cat_code, *categories))
|
|
return parsed_data
|
|
|
|
def insert_data_to_db(db_name, data):
|
|
conn = sqlite3.connect(db_name)
|
|
c = conn.cursor()
|
|
|
|
for item in data:
|
|
c.execute('''
|
|
INSERT INTO SSCategoryCode (cat_code, cat1, cat2, cat3, cat4)
|
|
VALUES (?, ?, ?, ?, ?)''', item)
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
db_name = 'keywords.db'
|
|
filename = 'baseXLS_Percenty.xlsx'
|
|
sheet_name = '스스 카테고리'
|
|
data = read_excel_data(filename, sheet_name, 2, 4500)
|
|
parsed_data = parse_data(data)
|
|
insert_data_to_db(db_name, parsed_data)
|