SQL_4 (python 활용)

🙅‍♂️휴대폰으로 볼 때 혹시 글자나 숫자가 화면에 다 안나오면, 휴대폰 가로로 돌리시면 됩니다

<목차>

1. 초기세팅
2. excute
3. fetchall
4. 예제
 4-1) police_station
 4-2) 2020_crime
 4-3) seoul_CCTV

*이번 글에서는 python과 sql을 함께 활용합니다

1. 초기세팅

AWS RDS에서 작업할 때는 --set-gtid-purged=OFF 필수!
(왜냐하면 저거 없을 때, 실행 시SQL파일에 다른 옵션이 들어가 오류난다함)

------------------ 세팅 --------------------------------------
# 경로 이동
cd C:/Users/withj/Documents/sql_ws
➡️먼저 vscode의 backup_police.sql 제거

# 백업
mysqldump --set-gtid-purged=OFF -h "아마존 주소" -P 3306 -u admin -p zerobase police_station > backup_police.sql

# 로그인
mysql -h "아마존 주소" -P 3306 -u admin -p --default-character-set=utf8mb4

# 확인
show databases;
use zerobase;
show tables;

# police_station 삭제후 확인
delete from police_station;
select * from police_station;

# 같은 경로의 vscode에서 python.ipynb 생성
ㄱㄱ

2. excute

우선 이것 설치 !pip install mysql-connector-python

# 즉석으로 파일 생성
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

# 즉석으로 파일 삭제
cur.execute("DROP TABLE sql_file")

# 읽기 모드
sql = open("test3.sql").read()
cur.execute(sql)

코드 작성

local, aws 2개로 작성하겠습니다.

local 연결

# local 연결
import mysql.connector as mc

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


# 사용다하면 종료
local.close()

AWS 연결

(1) query 생성

# AWS 연결
remote = mc.connect(
    host = "아마존주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)



# query 직접 생성
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")



# 사용다하면 종료
remote.close()

그리고 aws로 로그인한 mysql에서 desc sql_file 입력하여 확인

(2) query 삭제

# AWS 연결
remote = mc.connect(
    host = "아마존주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)


# query 삭제
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")


# 사용다하면 종료
remote.close()

그리고 aws로 로그인한 mysql에서 desc sql_file 입력하여 확인

(3) 쿼리 실행

sql 파일을 하나 만들고 py로 실행합니다

# test3.sql

CREATE TABLE sql_file
(
    id int,
    filename varchar(16)
);

# python.ipynb

remote = mc.connect(
    host = "아마존주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)

# test3.sql 실행
cur = remote.cursor()
sql = open("test3.sql").read()
cur.execute(sql)

# 사용다하면 종료
remote.close()

끝났으면 AWS mysql 로그인,
그리고 확인show databases;—>use zerobase;—>show tables;—>desc sql_file;

3. fetchall

SQL 쿼리의 결과 집합에서 모든 행을 가져옴

🔗sql file 내에 query가 여러개 존재하는 경우

test4.sql, python.ipynb 활용

# test4.sql

INSERT INTO sql_file VALUES (1, "test1.sql");
INSERT INTO sql_file VALUES (2, "test2.sql");
INSERT INTO sql_file VALUES (3, "test3.sql");
INSERT INTO sql_file VALUES (4, "test4.sql");

# python.ipynb

remote = mc.connect(
    host = "아마존 주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)



# 각각의 쿼리가 결과문으로 실행되는 것 확인
# buffered=True --> 읽어올 데이터 양이 많을 때 
cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")
result = cur.fetchall()
for result_iterator in result:
    print(result_iterator)



# 사용다하면 종료
remote.close()

완료 후 show databases;->use zerobase;->show tables;->desc sql_file;-> select * from sql_file; 입력 시 소속된 sql 파일들 확인 가능

mysql을 pandas로 불러오기

# python.ipynb
 
import pandas as pd
df =pd.DataFrame(result)
df.head()

4. 예제

# EDA 수업때 사용한 police_station.csv, 2020_crime.csv, seoul_CCTV.csv 가져오기
아래에 대응되는 2개의 컬럼값들이 각각 필요합니다 

4-1) police_station

# python.ipynb

#1 csv 불러오기
import pandas as pd
df =pd.read_csv("./police_station.csv")
df.head()

#2 mysql 연결
import mysql.connector
mconn = mysql.connector.connect(
    host = "아마존 주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)

# ----------------- 읽어올 데이터 설정 ----------------------------
#(buffered=True --> 읽어올 데이터 양이 많을 때)
cursor = mconn.cursor(buffered=True)

#3 insert쿼리 작성 (값 2개)
sql = "insert into police_station values (%s, %s)"

#4 데이터를 police_station 테이블에 insert
# commit (db에 적용하기 위한 명령)
# (처음이 list형태였으니까 for문을 통해 튜플형태로 1개씩 추출)
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    mconn.commit()

#5 결과 확인 및 저장
cursor.execute("select * from police_station")
result = cursor.fetchall()

#6 pandas 읽기
df = pd.DataFrame(result)
df.tail()

4-2) 2020_crime

#1 csv 불러오기
import pandas as pd
df =pd.read_csv("./2020_crime.csv", encoding="euc-kr")
df.head(2)

#2 mysql 연결
import mysql.connector
mconn = mysql.connector.connect(
    host = "아마존주소",
    port  = 3306,
    user = 'admin',
    password = "비밀번호",
    database = "zerobase"
)


# ----------------- 읽어올 데이터 설정 ----------------------------
#(buffered=True --> 읽어올 데이터 양이 많을 때)
#3 insert 쿼리 작성
sql = "insert into crime_status values ('2020', %s, %s, %s, %s)"
cursor = mconn.cursor(buffered=True)


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


#5 crime_status 테이블의 데이터 조회
cursor.execute("select * from crime_status")
result = cursor.fetchall()
for row in result:
    print(row)


#6 조회한 결과를 DataFrame으로 확인
df = pd.DataFrame(result)
df.head(2)    

4-3) seoul_CCTV

# ------------------------- python.ipynb ------------------------------

#1 seoul_CCTV.csv
import pandas as pd
df = pd.read_csv('./seoul_CCTV.csv')
df.head(2)

#2 mysql 연결
import mysql.connector
mconn = mysql.connector.connect(
    host = "아마존주소",
    port  = 3306,
    user = 'admin',
    password = "비번",
    database = "zerobase"
)

#3 insert 쿼리 작성
sql = "CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cursor = mconn.cursor(buffered=True)
cursor.execute(sql)
# 잘 들어왔는지 확인하는 방법⬇️
# 아마존 로그인 -> use zerobase; -> show tables; -> desc cctv;

#4 데이터를 cctv 테이블에 insert
sql = "INSERT INTO cctv values (%s, %s, %s, %s, %s, %s)"
cursor = mconn.cursor(buffered=True)
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    mconn.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(2)