SQL_심화_2

1. Python with MySQL

실습 환경 만들기

# Terminal에서
# sql_ws 폴더로 이동
cd Documents/sql_ws

# 해당 위치에서 vscode 실행
code .

# vscode Terminal에서
# AWS RDS zerobase 데이터베이스에서 police_station 테이블 백업
mysqldump --set-gtid-purged=OFF -h "엔드포인트" -P 포트 -u admin -p zerobase police_station > backup_police.sql

# mysql 접속
mysql -h "엔드포인트" -P 포트 -u admin -p

# police_station 데이터 지우기
use zerobase;
delete from police_station; # 테이블명만 남아있고 내용 지워짐.

# vscode에서 sql_ws 폴더 안에 python.ipynb 파일 만들기

Python으로 MySQL 접속 후 사용하는 방법

# 터미널에서 설치
pip install mysql-connector-python

# mysql.connector 모듈 불러오기
import mysql.connector

# 

#### 1. Create Connection

mydb = mysql.connector.connect(
	host =,
    user =,
    password =,
)

#### Create Connection 예제 1
# - Local Database 연결

local = mysql.connector.connect(
	host = "localhost",
    user = "root",
    password = "비밀번호"
)

#### Create Connection 예제 2
# - AWS RDS(database-1) 연결

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호"
)

#### Close Database

local.close()
remote.close()

#### Create Connection 예제 1
# - Local MySQL의 zerobase에 연결

local = mysql.connector.connect(
	host = "localhost",
    user = "root",
    password = "비밀번호",
    database = "zerobase"
)

#### Create Connection 예제 2
# - AWS RDS(database-1) zerobase에 연결
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

execute() : SQL Query를 실행하기 위한 코드

#### Execute SQL 예제 1 - 테이블 생성
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

remote.close()

#### Execute SQL 예제 2 - 테이블 삭제
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("DROP TABLE sql_file")

remote.close()

#### Execute SQL File 예제 1 
# test03.sql 생성

# test03.sql 실행
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

#### Execute SQL File 예제 2 - SQL File 내에 Query가 여러개 존재하는 경우
# test04.sql 생성

# test04.sql 실행
remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test04.sql").read()
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()

Fetch All

• Fetch All 예제
  - sql_file 테이블을 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")
result = cur.fetchall() # 리스트 형태로 불러옴.

for result_iterator in result:
	print(result_iterator)
    
remote.close()

>>> 
(1, 'test01.sql')
(2, 'test02.sql')
(3, 'test03.sql')
(4, 'test04.sql')

result

>>>
[(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]

• 참고. 검색결과를 Pandas로 읽기

import pandas as pd

df = pd.DataFrame(result)
df.head()

2. Python with CSV

csv에 있는 데이터를 python으로 INSERT

# police_station.csv를 pandas로 읽어오기
import pandas as pd

df = pd.read_csv("police_station.csv")
df.tail()

# AWS RDS zerobase에 연결
import mysql.connector

conn = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

# cursor 만들기
cursor = conn.cursor(buffered=True)

# insert문 만들기
sql = "insert into police_station values (%s, %s)" # 컬럼 2개, 2개의 str 값 받을 수 있게 설정

# 데이터 입력 - commit()은 데이터베이스에 적용하기 위한 명령
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
    
# 결과 확인
cursor.execute("select * from police_station")

result = cursor.fetchall()
result

# 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)
df.tail()

Python with CSV 예제

• AWS RDS crime_status 테이블에 2020_crime.csv를 입력하는 코드를 작성

# 2020_crime.csv 데이터(encoding="euc-kr") 읽어오기
import pandas as pd

df = pd.read_csv("2020_crime.csv", encoding="euc-kr")
df.head()

# AWS RDS zerobase에 연결
import mysql.connector

conn = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

# cursor 만들기
cursor = conn.cursor(buffered=True)

# insert문 만들기
sql = "insert into crime_status values ('2020', %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

# 데이터를 crime_status 테이블에 insert
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

# 결과 확인
cursor.execute("select * from crime_status")

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

# 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)
df.tail()

Python with CSV 문제

#### 1. AWS RDS(database-1) zerobase에 접속 

import mysql.connector

conn = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "비밀번호",
    database = "zerobase"
)

#### 2. cctv SQL 파일에서 데이터를 Pandas로 읽어오기 (데이터 확인)

import pandas as pd

df = pd.read_csv('Seoul_CCTV.csv', encoding='utf-8')
df.head(2)

#### 3. 같은 모양으로 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)

#### 4. 데이터를 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()

>>>
('강남구', 3238, 1292, 430, 584, 932)
('강동구', 1010, 379, 99, 155, 377)
('강북구', 831, 369, 120, 138, 204)
('강서구', 911, 388, 258, 184, 81)
('관악구', 2109, 846, 260, 390, 613)
('광진구', 878, 573, 78, 53, 174)
('구로구', 1884, 1142, 173, 246, 323)
('금천구', 1348, 674, 51, 269, 354)
('노원구', 1566, 542, 57, 451, 516)
('도봉구', 825, 238, 159, 42, 386)
('동대문구', 1870, 1070, 23, 198, 579)
('동작구', 1302, 544, 341, 103, 314)
('마포구', 980, 314, 118, 169, 379)
('서대문구', 1254, 844, 50, 68, 292)
('서초구', 2297, 1406, 157, 336, 398)
('성동구', 1327, 730, 91, 241, 265)
('성북구', 1651, 1009, 78, 360, 204)
('송파구', 1081, 529, 21, 68, 463)
('양천구', 2482, 1843, 142, 30, 467)
('영등포구', 1277, 495, 214, 195, 373)
('용산구', 2096, 1368, 218, 112, 398)
('은평구', 2108, 1138, 224, 278, 468)
('종로구', 1619, 464, 314, 211, 630)
('중구', 1023, 413, 190, 72, 348)
('중랑구', 916, 509, 121, 177, 109)

#### 5. cctv 테이블의 데이터 조회하여 확인

cursor.execute('SELECT * FROM cctv')

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

>>>
('강남구', 3238, 1292, 430, 584, 932)
('강동구', 1010, 379, 99, 155, 377)
('강북구', 831, 369, 120, 138, 204)
('강서구', 911, 388, 258, 184, 81)
('관악구', 2109, 846, 260, 390, 613)
('광진구', 878, 573, 78, 53, 174)
('구로구', 1884, 1142, 173, 246, 323)
('금천구', 1348, 674, 51, 269, 354)
('노원구', 1566, 542, 57, 451, 516)
('도봉구', 825, 238, 159, 42, 386)
('동대문구', 1870, 1070, 23, 198, 579)
('동작구', 1302, 544, 341, 103, 314)
('마포구', 980, 314, 118, 169, 379)
('서대문구', 1254, 844, 50, 68, 292)
('서초구', 2297, 1406, 157, 336, 398)
('성동구', 1327, 730, 91, 241, 265)
('성북구', 1651, 1009, 78, 360, 204)
('송파구', 1081, 529, 21, 68, 463)
('양천구', 2482, 1843, 142, 30, 467)
('영등포구', 1277, 495, 214, 195, 373)
('용산구', 2096, 1368, 218, 112, 398)
('은평구', 2108, 1138, 224, 278, 468)
('종로구', 1619, 464, 314, 211, 630)
('중구', 1023, 413, 190, 72, 348)
('중랑구', 916, 509, 121, 177, 109)

#### 6. 조회된 데이터를 Pandas로 변환하여 출력

df = pd.DataFrame(result)
df.head()