[MySQL] Python으로 SQL 사용하기

- VSCode 혹은 Web Browser를 통해 SQL을 실행하고, workspace 환경에서 작업한다.

- Jupyter notebook(쓸 경우에)과 mysql은 모두 sql_ws 파일 경로에서 사용한다.

- AWS RDS로 생성한 database-1을 모두 사용한다.

 

앞서 database-1의 zerobase의 police_station 테이블의 데이터를 모두 지워준다.

주의사항으로 AWS RDS에서 쓸 때는 --set-gtid-purged=OFF 라는 옵션을 줘야한다. 이렇게 하지 않으면 다른 옵션들이 들어가서 쿼리 실행 시 오류가 날 수 있기 때문이다.

 

mysqldump --set-gtid-purged=OFF -h "endpoint주소" -P 3306 -u admin -p zerobase police_station > backup_police.sql
Enter password:
mysql> use zerobase;
Database changed
mysql> delete from police_station;
Query OK, 4 rows affected(0.02 sec)

 

VSCode의 경우 새 파일을 열고 해당 파일명을 입력한다.

오른쪽 상단에서 ds_study를 선택한 뒤 실습해보자.

 

 

여기까지 실습환경이다.

참고로, ds_study 환경을 만드는 방법은 terminal에서 아래 코드를 쓰면 된다.

conda create -n ds_study python=3.8

1. Python으로 MySQL 접속

우선 Python에서 MySQL을 쓰려면 MySQL Driver가 설치되어있어야 한다.

pip install mysql-connector-python

잘 설치되었는지 확인해보자.

import mysql.connector

 

터미널이든 어디에서든 접속을 먼저했었다.

그 코드는 mysql -u root -p 혹은 -h -P를 썼었는데 python에서도 접속을 먼저 해줘야한다.

 

MySQL에 접속하기 위한 코드는 아래와 같다. 참고로 쌍따옴표""를 써줘야한다.

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>"
)

local 데이터베이스에 연결할 때는 host가 "localhost"이다.

import mysql.connector

local = mysql.connector.connect(
	host = "localhost",
	user = "root",
	password = "*******"
)

AWS RDS (database-1)에 연결할 때는 host가 endpoint 주소, port가 3306이다.

remote = mysql.connector.connect(
	host = "endpoint주소",
	port = 3306,
	user = "admin",
    password = "*******"
)

연결했을 땐 반드시 연결을 끊어줘야 한다.

연결만 계속 하다보면 연결연결연결로 연결이 너무 많아지기 때문이다.

따라서 연결을 끊어주는 코드가 꼭 있어야한다.

 

내부 연결은 local이라고 이름을 지어줬기 때문에

local.close()

라고 연결을 끊어주면 되고,

AWS RDS 외부 연결은 remote라고 이름을 지어줬기 때문에

remote.close()

라고 연결을 끊어주면 된다.

2. Fetch All

Query를 실행한 다음에 결과값이 row들 포함하고 있으면 fetchall()해서 데이터를 불러온다.

쉽게 이해하기 위해서는 sql문에 select가 있는 경우(데이터를 가져오는 경우)에 fetchall()을 해서 불러온다.

 

mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
	print(data)

result를 바로출력하면 한꺼번에 출력되고 위처럼 for문을 쓰면 한 줄 한 줄 읽어온다.

그리고 execute의 대상인 mycursor의 경우 remote.cursor()의 객체화인데,

execute하는 sql문이 가져오는 데이터의 양이 많은 경우

cursor(buffered=True) 옵션을 준다

mycursor = remote.cursor(buffered=True)
mycursor.execute(<query>)

3. Query로 가져온 데이터를 Pandas로 읽기

위에서 mycursor.fetchall()해서만든 result를 dataframe에 담아준다.

import pandas as pd

df = pd.DataFrame(result)
df

문제는 column명은 가져오지 않는다

4. CSV로 가져온 데이터를 SQL로 입력

import pandas as pd

df = pd.read_csv("data.csv")
df

데이터프레임을 만든 뒤

위 1번에서 썼던 connection문을 통해 연결시켜줍니다.

읽어올 양이 많은 경우 cursor 생성 시 buffer 설정을 해줍니다.

그리고 for문을 통해 반복시켜 insert할 거기 때문에 insert문은 하나만 써준다.

이 때 values() 항목 안에 들어갈 데이터는 아래와 같이 써준다.

 

테이블 이름은 data이다.

sql = "INSERT INTO data VALUES (%s, %s)"

이제 commit()하기 위한 for문을 돌려 준다.

for i, row in df.iterrows():
	
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

commit()은 database에 적용하기 위한 명령어이다.

execute를 시키면 sql문이 실행되지만, commit을 하지않으면 데이터베이스 저장이 안된다.

execute를 여러번 한 뒤 for문이 끝나고 commit을 해도 되지만, 그러면 데이터 입력 중 어느 줄에서 오류가 나는지 알 수 없으므로, for문 안에서 commit을 하는편이 좋다.

5. 입력한 데이터 결과 확인

select문을 쓰므로 fetchall하여 결과를 확인할 것이다.

cursor.execute("select * from data")

result = cursor.fetchall()
for row in result:
	print(row)

검색 결과를 다시 pandas로 변환한다.

df = pd.DataFrame(result)
df

 

Tip.

1. csv 한글이 깨지는 경우, encoding 값을 'euc-kr'로 설정한다

(특히 우리나라 사이트에서 제공받은 csv 파일들)

 

2. 쿼리 입력 시 특정 컬럼에 동일한 데이터를 넣고 싶은 경우

sql = """INSERT INTO data VALUES ("2020", %s, %s, %s, %s)"""
cursor = conn.cursor(buffered=True)