Python with MySQL

1. 실습환경 만들기

1) Jupyter Notebook 실행

• VSCode 혹은 Web Browser 를 통해 실행하고, ds_study 환경에서 작업

2) 실행위치

• Jupyter notebook 과 mysql 은 모두 sql_ws 폴더에서 시작

3) MySQL

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

4) 데이터 삭제

• AWS RDS(database-1) zerobase의 police_station 테이블의 데이터를 모두 지워줌

mysqldump --set-gtid-purged=OFF -h "엔트포인트" -P 3306 -u admin -p zerobase police_station > backup_police.sql

use zerobase;
show tables;

delete from police_station;
show tables;
select * from police_station;

5) python.ipynb 파일 생성

• VSCode의 경우 새파일을 열고 해당 파일명 입력
• 오른쪽 상단에서 ds_study를 선택한 뒤 실습 (커널 선택)
  

2. Python with MySQL

2-1. Install MySQL Driver

• Python에서 MySQL을 사용하기 위해서는 먼저 MySQL Driver 설치

pip install mysql-connector-python
import mysql.connector

2-2. Create Connection

• MySQL 에 접속하기 위한 코드

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

<Create Connection 예제 1>

• Local Database 연결

import mysql.connector

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 = "*******"
)

2-3. Close Database

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

mydb.close()

<Close Database 예제 1>

• Close Database

local.close()
remote.close()

2-4. Connect to Database

• 특정 Database 에 접속하기 위한 코드

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

<Connect to Database 예제 1>

• Local MySQL의 zerobase 연결

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

<Connect to Database 예제 2>

• AWS RDS (database-1)의 zerobase에 연결

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

• Close Database

local.close()
remote.close()

2-5. Execute SQL

• Query를 실행하기 위한 코드

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

mycursor = mydb.cursor()
mycursor.execute(<query>);

<Execute SQL 예제 1>

• Table 생성

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()

• 결과 확인(terminal에서 실행)

desc sql_file;

<Execute SQL 예제 2>

• Table 삭제

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "*******",
    database = "zerobase"
    
cur = remote.cursor()
cur.execute("drop table sql_file")

remote.close()
)

• 결과 확인(cmd에서 실행)

desc sql_file;

2-6. Execute SQL File 1

• SQL File을 실행하기 위한 코드

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

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql)

<Execute SQL File 1 예제>

• test03.sql 생성

create table sql_file
(
	id int,
    filename varchar(16)
)

• 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()

• 결과 확인(Terminal)

desc sql_file;

2-7. Execute SQL File 2

• SQL File 내에 Query 가 여러개 존재하는 경우

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

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
result = mycursor.execute(sql, multi = True)

<Execute SQL File 2 예제>

• test04.sql 생성

insert into sql_file values(1, "test01.sql");
insert into sql_file values(2, "test02.sql");
insert into sql_file values(3, "test03.sql");
insert into sql_file values(4, "test04.sql");

• 실행 (Multi = True)

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()

• 결과 확인(Terminal)

desc sql_file;
select * from sql_file;

2-8. Fetch All

mycursor.execute(<query>)

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

<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()

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

import pandas as pd

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

3. Python with CSV

• CSV에 있는 데이터를 Python으로 INSERT

3-1. Read CSV

• 제공 받은 police_station.csv를 Pandas로 읽어와서 데이터를 확인

import pandas as pd

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

3-2. Zerobase에 연결

import mysql.connector

conn = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "*******",
    database = "zerobase"
)

3-3. Cursor 만들기

• 읽어올 양이 많은 경우 cursor 생성 시 buffer 설정

cursor = conn.cursor(buffered = True)

3-4. INSERT 문 만들기

sql = "insert into police_station values (%s, %s)"

3-5. 데이터 입력

• commit()은 database 에 적용하기 위한 명령

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()
for row in result:
    print(row)

• 검색 결과를 Pandas로 읽기
  

3-6. Tip

• csv 한글이 깨지는 경우, encoding 값을 'euc-kr'로 설정 (특히 우리나라 사이트에서 제공 받은 csv 파일들)

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

4. Python with CSV 예제

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

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

import mysql.connector

conn = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "*******",
    database = "zerobase"
)

2) 2020_crime.csv 데이터(encoding='euc-kr') 읽어오기

import pandas as pd

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

3) INSERT 쿼리 작성

sql = """insert into crime_status values ("2020", %s, %s, %s, %s)"""
cursor = conn.cursor(buffered = True)

4) 데이터를 crime_status 테이블에 INSERT

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

5) crime_status 테이블의 데이터 조회

cursor.execute("select * from crime_status")

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

6) 조회한 결과를 Pandas 로 변환해서 확인

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

5. 문제풀이(실습)

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

import mysql.connector

conn = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "*******",
    database = "zerobase"
)

2) cctv 파일을 Pandas로 읽어오기

import pandas as pd

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

desc cctv;

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()

5) cctv 테이블의 데이터를 조회하여 확인

cursor.execute("select * from cctv")

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

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

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