util/dbquery.py

import json, os
import mysql.connector

def connection():
    connection = mysql.connector.connect(
        host=os.environ["SQL_ADDRESS"],  # MySQL 호스트 주소
        user='root',  # MySQL 사용자명
        password=os.environ["MYSQL_ROOT_PASSWORD"],  # MySQL 암호
        database='trdtionKMarket_database',
        charset='utf8mb4'
    )
    return connection

def getDataNum():
    conn = connection()  
    mycursor = conn.cursor() 
    query = "SELECT COUNT(*) FROM Markets"
    mycursor.execute(query)  
    data_num = mycursor.fetchone()  
    return data_num[0]
    
def insert_data(json_data):
    conn = connection()
    inserted_rows = 0  # 삽입된 행의 수

    try:
        with conn.cursor() as cursor:
            # 새로운 데이터 삽입
            for item in json_data:
                query = "INSERT IGNORE INTO Markets(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) VALUES (%s, %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], item[13]))
                inserted_rows += cursor.rowcount  # 삽입된 행의 수 업데이트

        conn.commit()

        with conn.cursor() as cursor:
            # 삽입한 데이터를 검색
            query = "SELECT COUNT(*) as id FROM Markets"
            cursor.execute(query)
            result = cursor.fetchall()
            print(f"현재 Markets 테이블에 존재하는 데이터: {result}")
    finally:
        conn.close()

    return inserted_rows  # 삽입된 행의 수 반환

#Load json file
def load_json(input_data):
    json_data = []
    for data in input_data:
        id = data["P_SEQ"]
        Market_id = data["M_SEQ"]
        Market_name_KR = data["M_NAME"]
        Market_type_name = data ["M_TYPE_NAME"]
        Market_type_id = data ["M_TYPE_CODE"]
        Local_id = data ["M_GU_CODE"]
        Local_name_KR = data ["M_GU_NAME"]
        Product_id = data ["A_SEQ"]
        Product_name = data ["A_NAME"]
        Product_unit = data ["A_UNIT"]
        Product_price = data ["A_PRICE"]
        Year_of_sale = data ["P_YEAR_MONTH"]
        etc = data ["ADD_COL"]
        UpdateDate = data ["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