Pymysql

1 개요

데이터를 메모장이나 엑셀 등으로 저장하는 것도 괜찮지만... 속도의 효율 등을 따져보면 그닥 이득은 아닐 것이다. 때문에 거대한 데이터를 다루기 위해선 DB가 필수적.

mysql 뿐 아니라 mariaDB에서도 사용 가능하다.

1.1 설치

pip install pymysql

1.2 기본컨셉

1. DB를 연결하여 DB객체 생성 후, 이 안에 속한 커서객체를 생성한다.
2. 커서객체.execute('SQL')로 SQL을 전달한다.
3. 커서객체.fetchall() 안에 DB의 응답이 담기는데, 이를 활용한다.

2 기본사용법

의도	설명	코드
DB파일만들기	저장파일을 만든다. 만약 이미 있으면 연결. 한글텍스트를 사용하기 위해 utf8 설정도 준다.	DB객체=pymysql.connect(host='localhost', port=번호, db='DB이름', user='root', passwd='비밀번호', charset='utf8')
커서객체 생성	커서객체를 만들면 SQL 질의를 자유롭게 쓸 수 있다. (아래의 데이터조작 참조)	커서객체=DB객체.cursor()
연결종료	종료한다. 커서객체를 다음과 같은 형식으로 불러오면 굳이 종료가 필요치 않다. with self.DB.cursor() as curs:	DB객체.close()
명령	DB질의를 한다.	커서객체.execute("질의문")
DB에 반영	내용을 DB에 반영한다.	DB객체.commit()

2.1 테이블 조작

의도	설명	코드
테이블 생성		커서객체.execute('create table if not exists 테이블명(열1, 열2)')
테이블 삭제		커서객체.execute('drop table if exists 테이블명')
테이블 목록 보기		커서객체.execute('SHOW TABLES') tables = 커서객체.fetchall()

2.2 데이터 조작

의도	설명	코드
테이블에 데이터 삽입		커서객체.execute("insert into 테이블명 values('내용1', '내용2')")
DB에 반영	내용을 DB에 반영한다.	DB객체.commit()
데이터 불러오기(하나)	테이블에 접근한 후 불러온다. 커서가 이동하며 한줄씩 읽는다.	커서객체.execute('select * from 테이블명') 객체=커서객체.fetchone()
데이터 전체 불러오기	전체를 읽어와 리스트로 반환.	커서객체.execute('select * from 테이블명') 객체=커서객체.fetchall()
데이터 일부 불러오기	n개 데이터를 읽어온다.	객체=커서객체.fetchmany(n)
데이터 하나 불러오기		객체=커서객체.fetchone()

3 다양한 사용법

3.1 백업

sql 자체에 백업기능이 있지만, 어째서인지, 행의 갯수도 달라지고, 용량도 달라진다. 반면 dataframe의 데이터를 직접 박아넣는 것은 행의 갯수도 크게 달라지지 않고(1,2개는 달라지는데, 이유를 모르겠다;), 용량도 오히려 많이 줄어든다.(아마 로그는 옮겨지지 않기 때문일듯..)

과정	설명
백업할 DB 연결	##### 백업할 DB 연결 및 테이블리스트 얻기. import pymysql from_db = pymysql.connect(host='연결할호스트', port=3306, db='coin_minute_info', user="유저", passwd='비밀번호', charset='utf8') from_cur = from_db.cursor() from_cur.execute('SHOW TABLES') tables = from_cur.fetchall() table_list = [] for i in tables: table_list.append(i[0]) print('테이블리스트 생성 완료.')
복원할 DB 연결 및 테이블 생성	to_db = from_db = pymysql.connect(host='localhost', port=3306, db='coin_minute_info', user="root", passwd='비밀번호', charset='utf8') to_cur = to_db.cursor() for i in table_list: sql='CREATE TABLE IF NOT EXISTS {} (time DATETIME, start FLOAT, close FLOAT, \ high FLOAT, low FLOAT, volume FLOAT)'.format(i) to_cur.execute(sql) # 테이블 따라서 생성. to_db.commit() # 반영. print('테이블 복사 완료')
dataframe을 sql로 넣어주는 모듈 준비	from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://{user}:{pw}@{domain}/{db}" .format(user="root", # sql 계정 입력. domain='localhost', # 도메인 주소 pw="vudghk99", # sql 비밀번호 입력. db="coin_minute_info")) # 연결할 db이름 입력.
옮기기 실행	import pandas as pd for i in table_list: from_cur.execute('select * from {}'.format(i)) data = from_cur.fetchall() data = pd.DataFrame(data) data.rename(columns={0:'time', 1:'start', 2:'close', 3:'high', 4:'low', 5:'volume'}, inplace=True) data.set_index('time', inplace=True) print(data) table_name = i data.to_sql(table_name, con = engine, if_exists = 'append', chunksize = 1000)

4 에러

4.1 pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([WinError 10061] No connection could be made because the target machine actively refused it)")

굉장히 많은 이유로 발생하는 에러이다.

의도	설명	방법
뭔가 잘못 쓴 경우	user든, 비밀번호든, 포트번호든 뭔가 잘못쓴 경우.	한 번 더 점검해보는 수밖에...
버전이 안맞을 경우	새로운 버전 혹은 파이썬 버전과 맞는 버전을 설치한다.	pip install --upgrade pip pip install pymysql

4.2 pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '장소'")

사용하는 구문이 맞지 않을 때 발생하는 에러. execute 안의 SQL_query가 틀렸을 때 나오는 에러이다.