import pymysql, openpyxl
from datetime import datetime
 
 
def board_write(board, subject, content, wr_link1, mb_id, nickname):
    try:
        # MySQL connection 및 cursor 생성
        conn = pymysql.connect(host = '####.####.com',
                            user = '#####',
                            password = '#####',
                            db = '#####',
                            charset = 'utf8')
        curs = conn.cursor()
   
        # 작성글 INSERT
        sql = f"select wr_num from g5_write_{board}"    
        curs.execute(sql)
        wr_num = str(int(curs.fetchone()[0]) - 1)
        now = datetime.today().strftime('%Y-%m-%d %H:%M:%S') # 그누보드의 날짜 형식 준수 (ex: 2021-04-05 23:45:15)
        sql = f"insert into g5_write_{board} set wr_num = {wr_num}, \
            wr_reply = '', wr_comment = 0, ca_name = '', wr_option = 'html1', wr_subject = '{subject}', \
            wr_content = '{content}', wr_link1 = '{wr_link1}', wr_link2 = '', \
            wr_link1_hit = 0, wr_link2_hit = 0, wr_hit = 1, wr_good = 0, wr_nogood = 0, \
            mb_id = '{mb_id}', wr_password = '', wr_name = '{nickname}', wr_email = '', wr_homepage = '', \
            wr_datetime = '{now}', wr_last = '{now}', wr_ip = '111.111.111.111', \
            wr_1 = '', wr_2 = '', wr_3 = '', wr_4 = '', wr_5 = '', \
            wr_6 = '', wr_7 = '', wr_8 = '', wr_9 = '', wr_10 = ''"
        curs.execute(sql)
   
        # 부모 아이디에 UPDATE
        sql = f"select wr_id from g5_write_{board}"
        curs.execute(sql)
        wr_id = str(curs.fetchall()[-1][0])
        sql = f"update g5_write_{board} set wr_parent = {wr_id} where wr_id = {wr_id}"
        curs.execute(sql)
   
        # 새글 INSERT
        sql = f"insert into g5_board_new ( bo_table, wr_id, wr_parent, bn_datetime, mb_id ) values \
            ( '{board}', '{wr_id}', '{wr_id}', '{now}', '{mb_id}' )"
        curs.execute(sql)
   
        # 게시글 1 증가
        sql = f"select bo_count_write from g5_board where bo_table = '{board}'"
        curs.execute(sql)
        bo_count_write = str(int(curs.fetchone()[0]))
        sql = f"update g5_board set bo_count_write = {bo_count_write} + 1 where bo_table = '{board}'"
        curs.execute(sql)
   
        # MySQL connection 닫기
        conn.close()
        print(f"게시글 등록 성공: {subject}")
    except Exception as e:
        print(f"게시글 등록 실패: {e}")
 
    return
 
 
def read_xlsx(filename):
    wb = openpyxl.load_workbook(filename)
    sh = wb['Sheet1']
    data = []
    for i in range(sh.max_row): # XLSX 파일 길이만큼 반복문을 수행합니다.
        temp = []
        for j in range(1, 4):
            cv = sh.cell(row=i+1, column=j).value
            temp.append(cv) if not cv is None else temp.append('') # None을 ''로 처리합니다.
        data.append(temp)
    return data
 
 
def main():
    result_list = read_xlsx('C:/py/py/test.xlsx')
    board = 'free'
    mb_id = 'test'
    nickname = '테스트계정'
    for r in result_list:
        board_write(board, r[0], r[1], r[2], mb_id, nickname)
 
 
if __name__ == "__main__":
    main()