Python 이용하여 Mysql에서 Json 데이터 Insert하기 (json 데이터 삽입)

geoJSon 데이터를 Python을 이용하여 Mysql에 삽입해보자

 

일반적인 python mysql 핸들링 순서는 다음과 같다.

1. 패키지설치
2. PyMySql 모듈 import
3. pymysql.connect() 메소드를 사용하여 MySQL에 연결. 호스트명, 포트, 로그인, 암호, 접속할 DB 등을 파라미터로 지정
4. MySQL 접속이 성공하면, 위에서 만든 Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴
   - Cursor: SQL 구문을 실행하기 위해서 만드는 객체 (Executes a SQL statement.)
5. SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 서버로부터 가져온 데이터를 코드에서 활용
6. 삽입, 갱신, 삭제 등의 DML(Data Manipulation Language) 문장을 실행하는 경우, INSERT/UPDATE/DELETE 후 Connection 객체의 commit() 메서드를 사용하여 데이타를 확정
7. Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫음

---

1. 패키지 설치

$ pip3 install pymysql

 

먼저 패키지를 설치해 주어야한다.

 

https://www.w3schools.com/python/python_mysql_insert.asp

Python MySQL Insert Into

이 공식문서를 참고하였을 때는 딱히 패키지설치가 필요하지 않은듯 하다.

 

2. Connector 정보 입력

mysql에 연결하기위한 connector 정보를 입력한다.

import pymysql.cursors

# 접속
# 비밀번호가 포함되어 있기 때문에 보통 config파일에서 key값으로 부른다.
conn = pymysql.connect(
    host = "localhost", 	 #ex) '127.0.0.1'
    port=3306,
    user = "username", 		 #ex) root
    password = "password",
    database = "mydatabase"
	charset = 'utf8'

)

# Cursor Object 가져오기
curs = conn.cursors()

• conn 변수 안에 pymysql.connect를 선언하고 connection 정보를 적어준다.
• curs = conn.cursors() : DB와 상호 작용을 위해 cursor 객체를 생성한다.

---

3. Insert (data 삽입하기)

db : Safemap

 

테이블 구조 : loadpoint

table : loadpoint

 

 sql문 삽입하기,

sql 문은 문자열이여야하기 때문에 "%s" 로 진행해야한다.

 

• 처음, %d, %lf로 했다가 "pymysql.err.OperationalError: (1305, 'FUNCTION safemap.str does not exist')" 다음과 같은에러가 발생함

 

Test

# Cursor Object 가져오기
    curs = conn.cursor()

    sql = "INSERT INTO loadpoint(lat, lon) VALUES (%s, %s)"
    val = (float(127.5), float(37.7))

    curs.execute(sql, val)
    conn.commit()

    print(curs.rowcount, "record inserted")

insert Succes

---

4. GeoJson 파일 데이터 Mysql에 삽입하기

 

이제 하고자하는 Json파일을 Mysql에 insert해보자

 

GeoJson 전체 파일의 insert가 아닌, 필요한 데이터만 삽입하고자 한다.

 

<하고자 하는 것>

1. Json 파일을 불러옴

2. 반목문으로 Json파일 조회

3. 한줄 씩 파싱 후 insert

 

import pymysql
import json
def insertsql_from_json():
    # connection 정보

    # 접속
    # 비밀번호가 포함되어 있기 때문에 보통 config파일에서 key값으로 부른다.
    conn = pymysql.connect(
        host = "localhost", 	 #ex) '127.0.0.1'
        port=3306,
        user = "root", 		 #ex) root
        password = "1234",
        database = "safemap",
        charset = 'utf8'
    )
    # Cursor Object 가져오기
    curs = conn.cursor()
               
    #geoJson 가져오기
    with open('static/json/AvailableLoadData.json', encoding='utf-8') as json_file:
        json_data = json.load(json_file)
        #json의 key로 접근
        #json_line : json 객체를 가지는 Array
        json_line = json_data['geometries']

        for a in json_line:
            lon = a['coordinates'][0]
            lat = a['coordinates'][1]

            sql = "INSERT INTO loadpoint(lat, lon) VALUES (%s, %s)"
            val = (float(lat), float(lon))

            curs.execute(sql, val)
            conn.commit()

    print(curs.rowcount, "record inserted")

insertsql_from_json()

 

데이터가 워낙 많아서 그런지 시간이 너무 오래걸리다... 1시간째 컴파일 중...

혹시 몰라서 select * from table; 해보니 50000개까지는 들어갔다... 하하

 

아무튼 이렇ㄱ ㅔ 끝!

 

+

---

5. Mysql에 벌크 데이터(대량 데이터) 넣기

 

이렇게 끝낼려하였으나.. 2시간이 지나도 완료되지않아, 작업을 멈추고 데이터양을 세어보니

약 400만개..

 

이대로는 안되겠다 싶어 조금이나마 효율을 높일려고 방법을 찾아보았다.

 

1. try.. finally

• SQL Connection을 열고 프로그램 중간에서 에러가 발생하면, Connection은 그대로 열려 있는 상태로 있을 수 있다고 한다.
• 이렇게 오픈되어 있는 Connection이 증가하면, 나중에 새로운 Connection을 오픈할 수 없게 되는데, 이를 Connection Leak라고 하고 
• 이러한 문제점을 막기위해 try...finally 블력을 사용하여 finally에서 항상 Conneciton을 Close해 주는 것이 좋다고 한다.

 

참고하여 적용해 주었다.

 

2. Bulk Inserting

특별할 거 없는 쿼리문 기법이다.

이 특별할 거 없는 간단한 방법이 효율을 극단적으로 높여주었다.

 

Bulk Inserting이란,

한번에 다량의 데이터를 삽입하고자 할때, 하나의 쿼리문에 여러개의 데이터를 한꺼번에 삽입하는 것이다.

 

(참고)

https://dev.dwer.kr/2020/04/mysql-bulk-inserting.html

Bulk Inserting - MySQL 다량의 데이터 넣기

 

위 글에 의하여, 10만개 이상부터 효율차이가 극명하게 나는거 같다.

실제로 Bulk Inserting으로 4개의 데이터씩 묶어서 쿼리 문을 작성하였는데 굉장히 효율적이었다!!

(대략 3시간 걸려도 안되던 것이.. 20~30분에 끝났음..ㅠ.ㅠ)

 

import pymysql
import json
def insertsql_from_json():
    # connection 정보

    # 접속
    # 비밀번호가 포함되어 있기 때문에 보통 config파일에서 key값으로 부른다.
    conn = pymysql.connect(
        host = "localhost", 	 #ex) '127.0.0.1'
        port=3306,
        user = "root", 		 #ex) root
        password = "1234",
        database = "safemap",
        charset = 'utf8'
    )
    # Cursor Object 가져오기
    curs = conn.cursor()

    check_number =0;
    try :    
        #geoJson 가져오기
        with open('static/json/AvailableLoadData.json', encoding='utf-8') as json_file:
            json_data = json.load(json_file)
            #json의 key로 접근
            #json_line : json 객체를 가지는 Array
            json_line = json_data['geometries']

            # print(len(json_line))
            # a =[]
            count=0;
            while(check_number!=len(json_line)) :
                print(check_number)

                lon1 = json_line[check_number]['coordinates'][0]
                lat1 = json_line[check_number]['coordinates'][1]
                check_number+=1

                lon2 = json_line[check_number]['coordinates'][0]
                lat2 = json_line[check_number]['coordinates'][1]
                check_number+=1

                lon3 = json_line[check_number]['coordinates'][0]
                lat3 = json_line[check_number]['coordinates'][1]
                check_number+=1

                lon4 = json_line[check_number]['coordinates'][0]
                lat4 = json_line[check_number]['coordinates'][1]
                check_number+=1

                sql = "INSERT INTO loadpoint(lat, lon) VALUES (%s, %s), (%s, %s), (%s, %s), (%s, %s)"
                val = (float(lat1), float(lon1), float(lat2), float(lon2), float(lat3), float(lon3), float(lat4), float(lon4))

                curs.execute(sql, val)
            conn.commit()
         
    finally :
        conn.close()


    print("record inserted")

insertsql_from_json()

아 추가로 Commit은 마지막에 한 번만해도 정상 작동이 된다.

 

 

진짜 끝! 무야호!