[제로베이스 데이터 분석 취업 스쿨]Day13 SQL

SQL FILE

SQL 쿼리를 모아놓은 파일으로, 안에 있는 쿼리를 모두 한꺼번에 사용할 수 있다. 

로그인 이후 실행 방법 

mysql> source </path/filename.sql>
mysql> \. </path/filename.sql>
mysql> \. <filename.sql>

vscode에서 mysql 실행

mysql -u root -p비밀번호

 

외부 단계에서 SQL FILE 실행

터미널 단계에서 바로 실행시킬 수 있다. 

VS CODE 터미널 창에서 아래 코드 입력

mysql -u root -p database < filename

본인은 터미널 창에 "'<' 연산자는 나중에 사용하도록 예약되어 있습니다."라는 에러가 발생하여 아래와 같이 해결하였다.

cmd.exe /c "mysql -u root -p zerobase < test02.sql"

 

Database Backup

mysqldump -u root -p zerobase > zerobase.sql

zerobase 데이터베이스를 파일로 저장한다. 

위와 같이 vscode로 확인할 수 있다.

 

aws RDS 연결

아래와 같이 vscode 터미널에서 aws RDS를 연결한다.

mysql -h "엔드포인트" -P 3306 -u username -p

zerobase 파일을 실행시켜서, 쿼리문을 입력한다.

source zerobase.sql

쿼리문이 제대로 실행되었는지 확인한다. 

 

Table Restore

mysqldump -u root -p zerobase tablename > filename.sql

 

aws RDS에서 로컬로 테이블 저장

# 아마존 서버 접속
mysql -h "엔드포인트" -P 3306 -u username -p비밀번호 database

drop table celab;
source celab.sql

테이블 스키마 저장

mysqldump -d -u root -p zerobase snl_show > snl.sql

앞에 -d를 덧붙히면 스키마를 저장할 수 있다. 

 

Python with MySQL

mysql 실행 

mysql -u [username] -p

vscode에서 파이썬 파일 생성한다.

kernal 설정 

본인은 자주 쓰는 python 3.7 커널로 연결하였다.

본인은 실행되는 ipynb 파일에서 mysql 파이썬 커텍터를 설치하였다.

!pip install mysql-connector-python 명령어를 입력하여 모듈을 설치하였다.

 

Local Database 연결 

import mysql.connector

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

이제 파이썬에서 Local Database 연결 및 AWS RDS 연결을 할 수 있다. 

 

Excute SQL

Query를 실행하기 위한 코드

 

파일을 실행시키려면 코드를 아래와 같이 작성한다.(test03.sql이라는 파일을 aws RDS에 실행시키려고 한다.) 

remote = mysql.connector.connect(
    host = "database-1.clou0aa205oy.us-east-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "38103810",
    database = 'ZEROBASE'
)

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

remote.close()

 

SQL _FILE 내에 쿼리가 여러 개 있을 경우

remote = mysql.connector.connect(
    host = "database-1.clou0aa205oy.us-east-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "38103810",
    database = 'ZEROBASE'
)

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

remote.close()

multi 파라미터를 입력해 준다.

 

Fetch All

조회한 데이터를 변수에 담아서 출력할 수 있다. (읽어올 데이터 양이 많은 경우 buffered=True)

 

Python with CSV

읽어오기

import pandas as pd
df = pd.read("police_station.csv")

Python으로 csv 파일을 sql로 삽입

remote = mysql.connector.connect(
    host = "endpoint",
    port = 3306,
    user = "admin",
    password = "password",
    database = 'ZEROBASE'
)

cur = remote.cursor(buffered=True)
sql = "insert into police_station values (%s, %s)"

반복문을 사용하여 데이터프레임을 아래와 같이 나타낼 수 있다.

위의 반복문을 사용하여 csv파일을 MySQL에 삽입해보자.( MySQL 공식 문서에서 execute를 참고해 볼 수 있다. )

결과 확인