python - mysql DB 생성, txt 파일을 DB Table에 넣기 (pymysql)

pymysql을 통해 mysql의 Database, Table을 생성하고 해당 Table에 Data를 수동으로도 넣어보고, txt 파일을 읽어서도 넣어보고자 한다.

우선 pymysql 모듈을 설치한다.
pip install pymysql

이제 Mysql에 접속해서 현재 Database를 조회해본다.

 

신규로 생성할 Database의 이름은 hanguptest_2로 한다.

##Database 생성 절차 'hanguptest_2'


import pymysql
conn = pymysql.connect(host='localhost', user='root', password='root', charset='utf8')
cursor = conn.cursor()
sql = "CREATE DATABASE hanguptest_2"
cursor.execute(sql)
conn.commit()
conn.close()

 

해당 스크립트를 수행하고 mysql의 Database를 검색하면 아래와 같이 hanguptest_2 라는 신규 Database가 생성된 것 을 확인할 수 있다.

 

이제 해당 Database에 해당하는 Table을 생성하고자 한다.
Table 명은 hangup_table_test_2로 하고 각각의 Field명과 Type은 아래와 같이 지정한다.

date_time char(20),

system_group varchar(10),

system_name varchar(20),

system_ip char(20),

location varchar(10),

result varchar(2),

description varchar(30)

​###테이블 생성 'hangup_table_test_2' 1
import pymysql


conn = pymysql.connect(host='localhost', user='root', password='root', db='hanguptest_2', charset='utf8')
cursor = conn.cursor()
sql = '''CREATE TABLE hangup_table_test_2 (
date_time char(20),
system_group varchar(10),
system_name varchar(20),
system_ip char(20),
location varchar(10),
result varchar(2),
description varchar(30)
)
'''
cursor.execute(sql)
conn.commit()
conn.close()

 

스크립트를 수행후 mysql 조회시 Table이 생성된 것을 확인할 수 있다.

다음으로는 Data를 수동으로 넣어보도록 한다.

​### mysql INSERT1
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='root', db='hanguptest_2', charset='utf8')
cursor = conn.cursor()
sql = "INSERT INTO hangup_table_test_2 (date_time, system_group, system_name, system_ip, location, result, description) VALUES (%s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql,("[03-19 01:30:06]","EPC", "SPGW", "172.1.1.1","daejun","0","desc"))
conn.commit()
conn.close()

 

스크립트를 수행후 수동으로 Data가 생성되었음을 알 수 있다.

mysql DB에 저장되어 있는 Data를 파이썬에서는 읽을 수 없을까?
아래 SELECT 명령어를 통해 방금 전에 저장한 data를 확인할 수 있다.

​###mysql SELECT
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='root', db='hanguptest_2', charset='utf8')
cursor = conn.cursor()
sql = "SELECT * FROM hangup_table_test_2"
cursor.execute(sql)
res = cursor.fetchall()
for data in res:
print(data)
conn.commit()
conn.close()

 

스크립트 수행하면 아래와 같이 mysql DB에 저장된 data가 출력된다.

​PS C:\pymin> & C:/Users/Min/AppData/Local/Programs/Python/Python38/python.exe c:/pymin/pymysql_test.py
('[03-19 01:30:06]', 'EPC', 'SPGW', '172.1.1.1', 'daejun', '0', 'desc')
PS C:\pymin>

Data를 수동으로 넣어주는 것은 한계가 있으니 txt 파일에 저장된 Data 를 읽어 mysql DB에 Insert를 해보자

우선, 아래처럼 txt 파일을 준비해야 한다. (','로 Field를 구분하였다)

C:/pymin/txt/test.txt

2021-03-11 17:45:06,EPC,server1,192.168.35.1,DUNSAN,0,description1 2021-03-11 17:45:06,EPC,server2,192.168.35.1,DUNSAN,0,description2 2021-03-11 17:45:06,EPC,server3,192.168.35.1,DUNSAN,0,description3 2021-03-11 17:45:06,EPC,server4,192.168.35.1,DUNSAN,0,description4 2021-03-11 17:45:06,EPC,AWS_server5,192.168.35.1,DUNSAN,-2,description5 2021-03-11 17:45:06,EPC,AWS_server6,192.168.35.1,DUNSAN,-2,description6

다음으로 아래와 같이 스크립트를 수행한다.

# ### mysql INSERT5
import re
import pymysql


f = open("C:/pymin/txt/test.txt", 'r')
while True:
line = f.readline()
if not line:
break
final_data = re.split("[,]", line)
print(final_data)


conn = pymysql.connect(host='localhost', user='root', password='root', db='hanguptest_2', charset='utf8')
cursor = conn.cursor()
sql = "INSERT INTO hangup_table_test_2 (date_time, system_group, system_name, system_ip, location, result, description) VALUES (%s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql,final_data)
conn.commit()
conn.close()
f.close()

 

스크립트를 수행하면 파이썬에서도 결과를 확인할 수 있고, mysql에서 조회해볼 수도 있다.

아래는 mysql에서의 조회 결과

다음은 파이썬 수행 결과이다.

(위 스크립트에서 print(final_data)를 추가하여 mysql에 data를 INSERT 하기 전에 data를 출력하도록 설정해 두었다.)

 

PS C:\pymin> & C:/Users/Min/AppData/Local/Programs/Python/Python38/python.exe c:/pymin/text_test.py ['2021-03-11 17:45:06', 'EPC', 'server1', '192.168.35.1', 'DUNSAN', '0', 'description1\n'] ['2021-03-11 17:45:06', 'EPC', 'server2', '192.168.35.1', 'DUNSAN', '0', 'description2\n'] ['2021-03-11 17:45:06', 'EPC', 'server3', '192.168.35.1', 'DUNSAN', '0', 'description3\n'] ['2021-03-11 17:45:06', 'EPC', 'server4', '192.168.35.1', 'DUNSAN', '0', 'description4\n'] ['2021-03-11 17:45:06', 'EPC', 'AWS_server5', '192.168.35.1', 'DUNSAN', '-2', 'description5\n'] ['2021-03-11 17:45:06', 'EPC', 'AWS_server6', '192.168.35.1', 'DUNSAN', '-2', 'description6'] PS C:\pymin>

 

pymysql을 통해 Database, Table을 생성/조회 했으며 Table에 Data를 수동으로도 넣어보고, 특정 txt파일에 있는 data를 가공 후 DB에 넣어보기도 했다.

 

이전 글에서는 Ping Test 결과를 txt 파일로 저장했었다. 이 txt 파일을 이용하면 특정 주기 또는 txt 파일이 업데이트가 되는 순간 순간 해당 Data를 mysql DB에 넣을 수 있지 않을까? 한번 고민해볼 필요가 있다.