[python] csv 파일 읽어서 sql insert 문 생성

LOAD DATA LOCAL INFILE 로 넣으려고 했으나

1. local_infile 옵션을 수정할 수 가 없다 .

2. vdesk 를 통해서 파일을 옮기니 한글이 깨진다. 

 

그래서 sql 문을 만들어서 컨트롤 씨부이를 해야겠다 .

 

재활용 파일 

# -*- coding: utf-8 -*-
import time
import json

from elasticsearch import Elasticsearch
from elasticsearch.helpers import bulk

import tensorflow_hub as hub
import tensorflow_text
import kss, numpy


##### INDEXING #####

def csv_data():
    f_v = open("stopword_prd.sql",'w')
    f_a = open("attribute_prd.sql",'w')
    count= 71166300;
    with open(CSV_FILE) as data_file:
        for line in data_file:
            line = line.strip()
            lineArray = line.split(',')

            sql_v = "INSERT INTO `search`.tb_dic_vocabulary (word_id, word_name, sense_tag, reg_date, note, use_flag, stop_flag, reg_userid, mod_userid, mod_date) VALUES("+str(count)+" , '"+lineArray[1]+"', '000000', now(), '', '1', '1', '2022100206', '2022100206', now());\n"
            sql_a = "INSERT INTO `search`.tb_dic_attribute (word_id, dict_type, pos, noun_attr, verb_attr, ne_type, `domain`, origin_language, dont_split_comp, is_general) VALUES ("+str(count)+", 'TOTAL', 'NOUN', '', 'X', '', '', '', 0, 'N');\n"

            f_v.write(sql_v)
            f_a.write(sql_a)
            print(sql_v)

            count += 100
    f_v.close()
    f_a.close()
    print("Done sql.")

#INSERT INTO `search`.tb_dic_vocabulrary_test (word_id, word_name, sense_tag, reg_date, note, use_flag, stop_flag, reg_userid, mod_userid, mod_date) VALUES(0, '', '0', CURRENT_TIMESTAMP, '', '1', '0', '', '', '');
#INSERT INTO `search`.tb_dic_attribute_test (word_id, dict_type, pos, noun_attr, verb_attr, ne_type, `domain`, origin_language, dont_split_comp, is_general) VALUES (0, '', 'NOUN', '', 'X', '', '', '', 0, 'N');


##### MAIN SCRIPT #####

if __name__ == '__main__':

    CSV_FILE = "./data/products/stopword0525.csv"
    csv_data()
    print("Done.")