파이썬을 이용한 엑셀 내용 업로드 질문입니다.

</p>

<p>import pymysql, openpyxl</p>

<p>from datetime import datetime</p>

<p> </p>

<p> </p>

<p>def board_write(board, subject, content, wr_link1, mb_id, nickname):</p>

<p>    try:</p>

<p>        # MySQL connection 및 cursor 생성</p>

<p>        conn = pymysql.connect(host = '####.####.com',</p>

<p>                            user = '#####',</p>

<p>                            password = '#####',</p>

<p>                            db = '#####',</p>

<p>                            charset = 'utf8')</p>

<p>        curs = conn.cursor()</p>

<p>   </p>

<p>        # 작성글 INSERT</p>

<p>        sql = f"select wr_num from g5_write_{board}"    </p>

<p>        curs.execute(sql)</p>

<p>        wr_num = str(int(curs.fetchone()[0]) - 1)</p>

<p>        now = datetime.today().strftime('%Y-%m-%d %H:%M:%S') # 그누보드의 날짜 형식 준수 (ex: 2021-04-05 23:45:15)</p>

<p>        sql = f"insert into g5_write_{board} set wr_num = {wr_num}, \</p>

<p>            wr_reply = '', wr_comment = 0, ca_name = '', wr_option = 'html1', wr_subject = '{subject}', \</p>

<p>            wr_content = '{content}', wr_link1 = '{wr_link1}', wr_link2 = '', \</p>

<p>            wr_link1_hit = 0, wr_link2_hit = 0, wr_hit = 1, wr_good = 0, wr_nogood = 0, \</p>

<p>            mb_id = '{mb_id}', wr_password = '', wr_name = '{nickname}', wr_email = '', wr_homepage = '', \</p>

<p>            wr_datetime = '{now}', wr_last = '{now}', wr_ip = '111.111.111.111', \</p>

<p>            wr_1 = '', wr_2 = '', wr_3 = '', wr_4 = '', wr_5 = '', \</p>

<p>            wr_6 = '', wr_7 = '', wr_8 = '', wr_9 = '', wr_10 = ''"</p>

<p>        curs.execute(sql)</p>

<p>   </p>

<p>        # 부모 아이디에 UPDATE</p>

<p>        sql = f"select wr_id from g5_write_{board}"</p>

<p>        curs.execute(sql)</p>

<p>        wr_id = str(curs.fetchall()[-1][0])</p>

<p>        sql = f"update g5_write_{board} set wr_parent = {wr_id} where wr_id = {wr_id}"</p>

<p>        curs.execute(sql)</p>

<p>   </p>

<p>        # 새글 INSERT</p>

<p>        sql = f"insert into g5_board_new ( bo_table, wr_id, wr_parent, bn_datetime, mb_id ) values \</p>

<p>            ( '{board}', '{wr_id}', '{wr_id}', '{now}', '{mb_id}' )"</p>

<p>        curs.execute(sql)</p>

<p>   </p>

<p>        # 게시글 1 증가</p>

<p>        sql = f"select bo_count_write from g5_board where bo_table = '{board}'"</p>

<p>        curs.execute(sql)</p>

<p>        bo_count_write = str(int(curs.fetchone()[0]))</p>

<p>        sql = f"update g5_board set bo_count_write = {bo_count_write} + 1 where bo_table = '{board}'"</p>

<p>        curs.execute(sql)</p>

<p>   </p>

<p>        # MySQL connection 닫기</p>

<p>        conn.close()</p>

<p>        print(f"게시글 등록 성공: {subject}")</p>

<p>    except Exception as e:</p>

<p>        print(f"게시글 등록 실패: {e}")</p>

<p> </p>

<p>    return</p>

<p> </p>

<p> </p>

<p>def read_xlsx(filename):</p>

<p>    wb = openpyxl.load_workbook(filename)</p>

<p>    sh = wb['Sheet1']</p>

<p>    data = []</p>

<p>    for i in range(sh.max_row): # XLSX 파일 길이만큼 반복문을 수행합니다.</p>

<p>        temp = []</p>

<p>        for j in range(1, 4):</p>

<p>            cv = sh.cell(row=i+1, column=j).value</p>

<p>            temp.append(cv) if not cv is None else temp.append('') # None을 ''로 처리합니다.</p>

<p>        data.append(temp)</p>

<p>    return data</p>

<p> </p>

<p> </p>

<p>def main():</p>

<p>    result_list = read_xlsx('C:/py/py/test.xlsx')</p>

<p>    board = 'free'</p>

<p>    mb_id = 'test'</p>

<p>    nickname = '테스트계정'</p>

<p>    for r in result_list:</p>

<p>        board_write(board, r[0], r[1], r[2], mb_id, nickname)</p>

<p> </p>

<p> </p>

<p>if __name__ == "__main__":</p>

<p>    main()</p>

<p>