[2021.07.26] 인턴 +147 How to upload CSV file LODA DATA on Linux Using Python?(AWS EC2 Mysql-Server)

[2021.07.26] 인턴 +147  How to upload CSV file LODA DATA on Linux Using Python?(AWS EC2 Mysql-Server)

 

 

해당 게시글은, 파이썬을 이용하여, 리눅스에서 CSV 파일을 LOAD DATA 하는 방법이다.

 

 

 

----------------------------------------------------------------------------------------------------------------------

<Full Source Code>

 

#!/usr/bin/env python
# coding: utf-8

# -*- coding: utf-8 -*-
import csv
import json
import time
import requests
import pymysql
from selenium import webdriver
from selenium.webdriver.chrome.options import Options



# AWS DB 연동 (AWS - ELK)
def get_db():
    db = pymysql.connect(
        host='ec2-13-125-199-218.ap-northeast-2.compute.amazonaws.com',
        port=3306,
        user='admin',
        passwd='1/zw;GytAwx*',
        db='TEST',
        charset='utf8',
        #local_infile=True
        local_infile=1
    )
    return db



# AWS Inquiry TABLE DB에 데이터 넣기
def insert_LOAD_DATA():
    db = get_db()
    mycursor = db.cursor()

    try:
        query = 'LOAD DATA LOCAL INFILE "/home/ubuntu/data/air_quality.csv" INTO TABLE TEST.air_quality FIELDS TERMINATED BY "," IGNORE 1 LINES'
        mycursor.execute(query)
        print("DB에 LOAD DATA 성공")
        db.commit()
        db.close()

    except Exception as e:
        print('db insert inquiry error',e)
        db.close()



# ----------------------- main ------------------------------
insert_LOAD_DATA()

* mycursor.execute("select * from table") 

-> 즉 mycursor.execute(명령 수행할 query문) 이다.

 

 

LOAD_DATA.py

0.00MB

 

------------------------------------------------------------------------------------------------------------------------------

<실행 결과>

 

 

 

 

-----------------------------------------------------------------------------------------------------------------------

Linux에서 파이썬 모듈(라이브러리) 설치하는 방법

e.g : pymysql , selenium

 

 

 

ubuntu@ip-172-31-9-174:~$ python3 -m pip install pymysql

Collecting pymysql
  Downloading PyMySQL-1.0.2-py3-none-any.whl (43 kB)
     |████████████████████████████████| 43 kB 1.8 MB/s
Installing collected packages: pymysql

 

 

 

ubuntu@ip-172-31-9-174:~$ python3 -m pip install selenium

Collecting selenium
  Downloading selenium-3.141.0-py2.py3-none-any.whl (904 kB)
     |████████████████████████████████| 904 kB 1.6 MB/s
Requirement already satisfied: urllib3 in /usr/lib/python3/dist-packages (from selenium) (1.25.8)
Installing collected packages: selenium
Successfully installed selenium-3.141.0

 

 

 

----------------------------------------------------------------------------------------------------------------------

해당 코드를 작성하면서 해결한 오류들

 

 

can't concat tuple to bytes

can't concat tuple to bytes

 

기존 코드

query = "LOAD DATA LOCAL INFILE 'C:\\Users\\injekim97\\Desktop\\air_quality.csv' INTO TABLE DATA.blood_test FIELDS TERMINATED BY "," IGNORE 1 LINES"

바꾼 코드

query = 'LOAD DATA LOCAL INFILE "C:\\Users\\injekim97\\Desktop\\air_quality.csv" INTO TABLE DATA.blood_test FIELDS TERMINATED BY "," IGNORE 1 LINES'

 

 

즉, * 해결방법: PATH 경로에  '  --> "로 바꿔주면  can't concat tuple to bytes 에러가 해결 됨

 

 

 

-------------------------------------------------------------------------------------------------------------------------

다른 에러 발생

3948, 'Loading local data is disabled; this must be enabled on both the client and server sides

*  해결 방법 :  local_infile=1 추가


# AWS DB 연동 (AWS - ELK)

def get_db():
    db = pymysql.connect(
        host='ec2-13-125-199-218.ap-northeast-2.compute.amazonaws.com',
        port=3306,
        user='admin',
        passwd='1/zw;GytAwx*',
        db='TEST',
        charset='utf8',
        #local_infile=True
        local_infile=1
    )
    return db

 

로컬에서 발생하는 에러 (해결 X)
다음 에러 발생 : db insert inquiry error (1017, "Can't find file 'b'C:Usersinjekim97Desktopair_quality.csv''")

-> 리눅스에서는 존재 X