Python MySQL 데이터 단일 행, 다중 행 추가

이번 글에서는 Python을 이용하여, MySQL의 TABLE에 데이터를 추가하고 삭제해보겠습니다.

 

데이터를 추가하기 전에는 DB와 TABLE이 존재해야 합니다. DB와 TABLE이 추가되지 않은 상태라면, 아래 글들을 참조하세요.

 

2022.02.19 - [소프트웨어/Python] - Python MySQL DB 생성, 삭제

2022.02.19 - [소프트웨어/Python] - Python MySQL 테이블 생성, 제거

1. 정리

위에서 설명한 바와 같이, 데이터를 생성하기 전에는 DB와 TABLE이 있어야 합니다.

 

TABLE은 생성되어있다고 가정하겠습니다. TABLE의 구조는 다음과 같습니다.

이름	종류
dateTime	datetime
name	varchar(255)
val1	smallint(6)
val2	smallint(6)

 

1.1. 데이터의 단일 행 추가

먼저, pymysql을 import하고 datetime 타입의 데이터를 만들기 위해, datetime을 import합니다.

 

그리고 now라는 변수에 datetime.datetime.now() 함수를 통해 년도, 월, 일, 시, 분, 초를 불러옵니다.

 

그리고 strftime 함수를 통해 원하는 format으로 타입을 변환합니다. 아래 코드에서는 '년-월-일 시:분:초' 타입으로 변환하겠네요.

 

TABLE의 구조를 보면 각 열의 타입이, datetime, varchar, smallint, smallint임을 알 수 있습니다. 한 행을 추가하기 위해, 각 열의 데이터 타입에 맞춰 sqlData라는 tuple을 생성해줍니다.

import pymysql
import datetime

now = datetime.datetime.now()
format = '%Y-%m-%d %H:%M:%S'
now = now.strftime(format)
sqlData = (now, 'test', 0, 1)

 

저장할 데이터의 구성은 만들어졌습니다.

 

이제 DB와 연결하고 쿼리문을 작성해보겠습니다. 데이터 삽입의 쿼리문은 'INSERT INTO 테이블명 (데이터 이름) VALUES (%S)' 입니다. 현재는 열이 4개이므로 쿼리문은 아래와 같겠네요.

 

 

 

'INSERT INTO test_table (dateTime, name, val1, val2) VALUES (%s, %s, %s, %s)' 

 

 

 

이제 execute함수를 통해, 쿼리문을 실행해야 합니다.

 

그런데, DB와 TABLE 생성때랑은 사용방법이 조금 다릅니다. 차이점이 보이시나요? 차이점은 인자를 하나 더 넣는 것입니다.

 

데이터를 삽입하기 위해서는 쿼리문도 필요하지만, 넣어야 하 데이터도 필요하기 때문이죠.

 

이후에는 commit을 하고 DB와의 연결을 해제합니다. 

 

#db 연결
conn = pymysql.connect(host='localhost', user='root', password='', db='TEST', charset='utf8')

#데이터 단일 행 추가
try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO test_table (dateTime, name, val1, val2) VALUES (%s, %s, %s, %s)'
        cursor.execute(sql, sqlData)       #단일행 excute
    conn.commit()
finally:
    conn.close()

 

이제, TABLE을 확인해보겠습니다. 저장하고자 했던 데이터들이 원하는 값대로 잘 저장되었네요.

 

1.2. 데이터의 다중 행 추가

데이터의 단일 행 추가는 이전 예시를 통해, 알게 되었습니다.

 

그런데, 다중 행을 추가하려면 어떻게 해야할까요? 물론, python 코드 내에서 반복문을 통해서도 가능합니다.

 

하지만, 데이터가 많아지면 DB 접근이 많아지게 되고 서버의 부하가 커질 것입니다.

 

그리고 반복문 도중에 에러 발생 시, 디버깅하기도 조금 복잡할 듯 합니다.

 

위와 같은 이유로 다중 행 추가가 필요하고 MySQL에서도 지원하고 있습니다.

 

이제 다중 행 추가를 해보겠습니다. 먼저, 다중행을 위한 데이터 형태를 만들어야 겠습니다.

 

tuple 형태의 데이터 모음을 총 3개 만들어보죠. 여러 개의 데이터를 구분하기 위해, sqlData1, sqlData2, sqlData3의 val2의 값은 1, 2, 3으로 저장하겠습니다.

 

이제 이 데이터들을 list_sqlData라는 변수에 list로 한 번에 저장하겠습니다. list_sqlData를 출력하면 다음과 같이 나옵니다.

 

 

 

[('2021-03-25 13:53:05', 'test', 0, 1),
 ('2021-03-25 13:53:05', 'test', 0, 1),
 ('2021-03-25 13:53:05', 'test', 0, 1)]

 

 

 

단일 행 데이터와 구조는 같지만, 리스트로 복수 개의 tuple이 존재할 뿐입니다.

 

sqlData1 = (now, 'test', 0, 1)
sqlData2 = (now, 'test', 0, 2)
sqlData3 = (now, 'test', 0, 3)

list_sqlData = [sqlData1, sqlData2, sqlData3]

 

이제 DB에 연결하고 쿼리를 작성한 후, 실행하여 commit을 해주면 됩니다.

 

단일 행 추가할 때와, 차이가 없어 보이나요? 한 가지, 차이점이 있습니다.

 

excute 함수가 아닌 excutemany함수를 사용하게 됩니다. 또한, 파라미터도 단일 tuple이 아닌 리스트로 구성된 tuple 입니다. 이제 결과를 보겠습니다.

#데이터 다중 행 추가
conn = pymysql.connect(host='localhost', user='root', password='', db='TEST', charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO test_table (dateTime, name, val1, val2) VALUES (%s, %s, %s, %s)'
        cursor.executemany(sql, list_sqlData)       
    conn.commit()
finally:
    conn.close()

 

아래 그림과 같이, 이전에 추가했던 데이터에서 3개의 데이터가 추가됨을 볼 수 있네요.

 

val2의 값도 1, 2, 3으로 들어온 것을 보니 올바른 값이 저장되었음이 확인되었습니다.

 

2. 전체코드

import pymysql
import datetime

now = datetime.datetime.now()
format = '%Y-%m-%d %H:%M:%S'
now = now.strftime(format)
sqlData = (now, 'test', 0, 1)

#db 연결
conn = pymysql.connect(host='localhost', user='root', password='', db='TEST', charset='utf8')

#데이터 단일 행 추가
try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO test_table (dateTime, name, val1, val2) VALUES (%s, %s, %s, %s)'
        cursor.execute(sql, sqlData)       #단일행 excute
    conn.commit()
finally:
    conn.close()
    
sqlData1 = (now, 'test', 0, 1)
sqlData2 = (now, 'test', 0, 2)
sqlData3 = (now, 'test', 0, 3)

list_sqlData = [sqlData1, sqlData2, sqlData3]

#데이터 다중 행 추가
conn = pymysql.connect(host='localhost', user='root', password='', db='TEST', charset='utf8')

try:
    with conn.cursor() as cursor:
        sql = 'INSERT INTO test_table (dateTime, name, val1, val2) VALUES (%s, %s, %s, %s)'
        cursor.executemany(sql, list_sqlData)       #다중행 excute
    conn.commit()
finally:
    conn.close()