util/initialize_database.py

import json
from util.dbquery import connection, load_json, insert_data
import csv


# def load_json(name):
#     json_data = []
#     with open(name, "r", encoding = 'utf-8') as file:
#         data = json.load(file)
#         for item in data["DATA"]:
#             id = item["p_seq"]
#             Market_id = item ["m_seq"]
#             Market_name_KR = item ["m_name"]
#             Market_type_name = item ["m_type_name"]
#             Market_type_id = item ["m_type_code"]
#             Local_id = item ["m_gu_code"]
#             Local_name_KR = item ["m_gu_name"]
#             Product_id = item ["a_seq"]
#             Product_name = item ["a_name"]
#             Product_unit = item ["a_unit"]
#             Product_price = item ["a_price"]
#             Year_of_sale = item ["p_year_month"]
#             etc = item ["add_col"]
#             UpdateDate = item ["p_date"]
#             temp = [id, Market_id, Market_name_KR, Market_type_name, Market_type_id, Local_id, Local_name_KR, Product_id, Product_name, Product_unit, Product_price, Year_of_sale, etc, UpdateDate]
#             json_data.append(temp)
#     return json_data

def load_market_csv(name):
    csv_data = []
    with open(name, newline='', encoding='utf-8') as file:
        data = csv.reader(file)
        # 각 행을 출력하는 예시
        for item in data:
            Key_value = item[10]
            Market_name_KR = item[1] 
            Market_Name_EN = item[9] 
            Local_name_KR = item[0]
            Local_name_EN = item[11] 
            Local_name_dong = item[12]
            Address = item[3]
            Latitude = item[4]
            Longitude = item[5]
            Tel = item[6]
            Market_shape = item[2]
            Building_scale = item[7]
            Num_of_stores = item[8]
            temp = [Key_value, Market_name_KR, Market_Name_EN, Local_name_KR, Local_name_EN, Local_name_dong, Address, Latitude, Longitude, Tel, Market_shape, Building_scale, Num_of_stores]
            csv_data.append(temp)
    return csv_data

def load_population_csv(name):
    csv_data = []
    with open(name, newline='', encoding='utf-8') as file:
        data = csv.reader(file)
        for item in data:
            id = item[1]
            Category = item[0]
            Area_code = item[2]
            Area_name_KR = item[3]
            Area_name_EN = item[6]
            Latitude = item[4]
            Longitude = item[5]
            temp = [id, Category, Area_code, Area_name_KR, Area_name_EN, Latitude, Longitude]
            csv_data.append(temp)
    return csv_data

def init_data():
    print("데이터 베이스 초기설정 중....")
    conn = connection()
    try:
        with conn.cursor() as cursor:
            #테이블 생성
            query1 = "CREATE TABLE IF NOT EXISTS Markets(id INT, Market_id INT, Market_name_KR VARCHAR(255), Market_type_name VARCHAR(50), Market_type_id VARCHAR(10), Local_id VARCHAR(10), Local_name_KR VARCHAR(50), Product_id INT, Product_name VARCHAR(255), Product_unit VARCHAR(50), Product_price DECIMAL(10,2), Year_of_sale VARCHAR(7), etc VARCHAR(255), UpdateDate DATE, PRIMARY KEY (id))"
            query2 = "CREATE TABLE IF NOT EXISTS Market_Info(Key_value VARCHAR(20), Market_name_KR VARCHAR(255), Market_name_EN VARCHAR(255), Local_name_KR VARCHAR(50), Local_name_EN VARCHAR(50), Local_name_dong VARCHAR(50), Address VARCHAR(255), Latitude DOUBLE, Longitude DOUBLE, Tel VARCHAR(20), Market_shape VARCHAR(50), Building_scale FLOAT, Num_of_stores FLOAT, PRIMARY KEY(Key_value))"
            query3 = "CREATE TABLE IF NOT EXISTS Population(id INT, Category VARCHAR(50), Area_code VARCHAR(100), Area_name_KR VARCHAR(100), Area_name_EN VARCHAR(100), Latitude DOUBLE, Longitude DOUBLE, PRIMARY KEY(id))"
            cursor.execute(query1)
            cursor.execute(query2)
            cursor.execute(query3)
            # 새로운 데이터 삽입
            json_data = load_json("data/data.json")
            csv_market_data = load_market_csv("data/market_name_utf8.csv")
            csv_population_data = load_population_csv("data/population.csv")
            
            insert_data(json_data)
            for item in csv_market_data:
                query = "INSERT IGNORE INTO Market_Info(Key_value, Market_name_KR, Market_name_EN, Local_name_KR, Local_name_EN, Local_name_dong, Address, Latitude, Longitude, Tel, Market_shape, Building_scale, Num_of_stores) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                cursor.execute(query, (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9], item[10], item[11], item[12]))
            for item in csv_population_data:
                query = "INSERT IGNORE INTO Population(id, Category, Area_code, Area_name_KR, Area_name_EN, Latitude, Longitude) VALUES (%s, %s, %s, %s, %s, %s, %s)"
                cursor.execute(query, (item[0], item[1], item[2], item[3], item[4], item[5], item[6])) 
                
        # 삽입한 데이터를 커밋
        conn.commit()
        with conn.cursor() as cursor:
            # 삽입한 데이터를 검색
            query = "SELECT COUNT(*) as Key_value FROM Market_Info"
            cursor.execute(query)
            result = cursor.fetchall()
            print(f"현재 Market_Info 테이블에 존재하는 데이터: {result}")
            query = "SELECT COUNT(*) as id FROM Population"
            cursor.execute(query)
            result = cursor.fetchall()
            print(f"현재 Population 테이블에 존재하는 데이터: {result}")            
    finally:
        # 연결 닫기
        conn.close()

#code test
# init_data()