📍 실습환경 세팅
-
Jupyter Notebook 실행
- VSCode 또는 Web Browser를 통해 실행, ds_study 환경에서 작업
- Jupyter Notebook과 mysql 모두 sql_ws 위치에서 시작
- AWS RDS로 생성한 database-1 모두 사용 -
데이터 삭제
- AWS RDS(database-1) zerobase의 police_station 테이블의 데이터 (백업 후) 모두 삭제sql_ws > mysqldump --set-gtid-purged=OFF -h 엔드포인트 -P 3306 -u admin -p zerobase police_station > backup_police.sql mysql> use zerobase; mysql> delete from police_station; -
python.ipynb 파일 생성
- VSCode의 경우 새 파일을 열고 해당 파일명 입력
- 실습을 위해 생성한 da_study 커널 선택
📂 Python에서 MySQL 접속 후 사용하는 방법
1) MySQL Driver 설치
-
anaconda prompt 실행 후
conda activate da_study를 통해 가상환경 이동 후 진행
(터미널) (da_study) 경로\sql_ws>pip install mysql-connector-python -
설치 확인
(ipynb 파일) import mysql.connector
2) Create Connection - MySQL에 접속
- 로컬 DB에 연결
local = mysql.connector.connect(
host = "localhost",
user = "root",
password = "password"
)- AWS RDS(database-1)에 연결
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password"
)3) Close Database - 사용이 끝나면 연결 항상 끊어주기
local.close()
remote.close()4) 특정 DB로 접속 > databse = "dbname" 만 추가
mydb = mysql.connector.connect(
host = "localhost" / "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "dbname"
)
mydb.close()📂 Python에서 SQL 쿼리 실행
1) 테이블 생성
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
remote.close()
# zerobase DB 연결하여 결과 확인
mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| filename | varchar(16) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+2) 테이블 삭제
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")
remote.close()
# 결과 확인
mysql> desc sql_file;
ERROR 1146 (42S02): Table 'zerobase.sql_file' doesn't exist📂 Python에서 SQL File 실행
📍 1. 실행할 쿼리가 하나인 경우
1) test03.sql 생성
2) 실행 및 결과 확인
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)
remote.close()
# 결과 확인
mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| filename | varchar(16) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+📍 2. SQL File 내에 쿼리가 여러 개 존재하는 경우
(execute에 multi = True 옵션 추가)
1) test04.sql 생성
2) 실행 및 결과 확인
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
cur = remote.cursor()
sql = open("test04.sql").read()
# cur.execute(sql, multi=True)
for result_iterator in cur.execute(sql, multi=True) :
if result_iterator.with_rows:
print(result_iterator.fetchall())
else:
print(result_iterator.statement)
remote.commit()
remote.close()
# 결과 확인
mysql> select * from sql_file;
+------+------------+
| id | filename |
+------+------------+
| 1 | test01.sql |
| 2 | test02.sql |
| 3 | test03.sql |
| 4 | test04.sql |
+------+------------+📂 Fetch All
📍 fetchall() 함수는 python과 데이터베이스 간 데이터를 주고 받기 위해 cursor 객체에서 사용되는 메서드
- 모든 row를 읽고, 리스트 형식으로 쿼리 결과를 한 번에 가져오는 방식
mycursor.execute("query")
reuslt = mycursor.fetchall()
for data in result:
print(data)예제) (AWS RDS) sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered = True)
remote = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")
result = cur.fetchall()
print(result) # (1)
for result_iterator in result :
print(result_iterator) # (2)
remote.close()
참고) 검색 결과를 pandas로 읽기
import pandas as pd
df = pd.DataFrame(result)
df.head()
📂 with CSV
CSV에 있는 데이터를 Python으로 insert
1. CSV 읽기
- police_station.csv를 pandas로 읽어와서 데이터 확인
import pandas as pd
df = pd.read_csv("police_station.csv")
df.head()2. AWS RDS(database-1)의 zerobase DB 연결
import mysql.connector
conn = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)3. cursor 선언
- 읽어올 양이 많은 경우
buffered = True설정
cursor = conn.cursor(buffered=True)4. insert문 만들기
sql = "INSERT INTO police_station VALUES (%s, %s)"5. 데이터 입력
- commit()은 database에 적용하기 위한 명령
for i, row in df.iterrows():
cursor.execute(sql, tuple(row)) # 실행 결과가 db에 바로 적용되지 않음 > commit하는 순간 적용됨
print(tuple(row))
conn.commit()6. 결과 확인
cursor.execute("SELECT * FROM police_station")
result = cursor.fetchall()
for row in result:
print(row)
7. 검색 결과 pandas로 확인
df = pd.DataFrame(result)
df
conn.close()
❗ csv 한글이 깨지는 경우, encoding 값을 'euc-kr'로 설정 (특히, 우리나라 사이트에서 제공받은 csv파일)
import pandas as pd df = pd.read_csv('파일명', encoding = 'euc-kr')
실습
- AWS RDS(database-1) zerobase 접속
import mysql.connector
conn = mysql.connector.connect(
host = "엔드포인트",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)- cctv Table 생성
sql = "CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cursor = conn.cursor(buffered=True)
cursor.execute(sql)- cctv SQL 파일에서 데이터를 pandas로 읽기
import pandas as pd
df = pd.read_csv('csv/Seoul_CCTV.csv', encoding = 'utf-8')
df.head()- 데이터를 cctv 테이블에 insert
sql = "INSERT INTO cctv VALUES (%s, %s, %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)
for i, row in df.iterrows():
cursor.execute(sql, tuple(row))
print(tuple(row))
conn.commit()- cctv 테이블 데이터 조회하여 확인
cursor.execute("SELECT * FROM cctv")
result = cursor.fetchall()
for row in result:
print(row)- 조회된 데이터를 pandas로 변환하여 출력
df = pd.DataFrame(result)
df.head()