SQL AWS, python

1. AWS RDS

AWS RDS 터미널 실행

% mysql -h "엔드포인트" -P 포트 -u 마스터 사용자 이름 -p

AWS RDS 중지 / 시작

• 중지: AWS -> RDS -> 데이터베이스 작업 선택에서 일시 중지 클릭
• 시작: AWS -> RDS -> 데이터베이스 -> 해당 데이터베이스 클릭 작업 시작 클릭

2. SQL FILE

SQL File

• SQL 쿼리를 모아놓은 파일

SQL File 실행

1) 로그인 이후

source /path/filename.sql
\. /path/filename.sql # source 대신 \. 사용가능
\. filename.sql # 현재 폴더에 있으면 path 생략 가능

source test01.sql

2) 외부에서 바로 실행

% mysql -u username -p databasename < /path/filename.sql
% mysql -u root -p zerobase < test02.sql

Database Backup

• SQL File 로 Database 를 백업할 수 있다.

% mysqldump -u username -p dbname > backup.sql # 특정 database backup
% mysqldump -u username -p --all-databases > backup.sql # 모든 Database backup
% mysqldump --set-gtid-purged=OFF -h 엔드포인트 -P 포트 -u username -p databasename filename.sql # AWS Database backup

Database Restore

• 데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전 할 수 있다. (SQL File 을 실행하는 방법과 동일함)

# AWS RDS 서비스가 사용 가능한 상태에서 접속
% mysql -h 엔드포인트 -P 포트 -u 마스터 사용자 이름 -p

mysql> use dbname;
mysql> source filename.sql

Table Backup

• Table 단위로도 백업할 수 있다.

% mysqldump -u username -p dbname tablename > backup.sql

Table Restore

• Table 을 백업한 SQL File 을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다. (SQL File 을 실행하는 방법과 동일함)

Table Schema Backup

• 데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.

% mysqldump -d -u username -p dbname tablename > backup.sql # 특정 table schema backup
% mysqldump -d -u username -p dbname > backup.sql # 모든 table schema backup

3. Python with MySQL

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

• Create Connection
• Close Database -> 낭비가 없도록 꼭 닫기!
• Connect to Database

!pip install mysql-connector-python

import mysql.connector
#local
mydb = mysql.connector.connect(
	host ="hostname",
    user="username",
    password="password"
)

# AWS RDS
mydb = mysql.connector.connect(
	host ="hostname",
    port = port,
    user="username",
    password="password"
)

# close database
mydb.close()

#특정 database 접속 코드
mydb = mysql.connector.connect(
	host ="hostname",
    user="username",
    password="password",
    database="databasename"
)

Execute SQL

• Query 를 실행하기 위한 코드

import mysql.connector
mydb = mysql.connector.conncet(
	host ="hostname",
    user="username",
    password="password",
    database="databasename"
)
mycursor = mydb.cursor()
mycursor.execute(<query>)

• 테이블 생성

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

• 테이블 삭제

cursor = mydb.cursor()
cursor.execute("DROP TABLE sql_file")
mydb.close()

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

# test.sql
CREATE TABLE sql_file(
	id int,
    filename varchar(16)
);

# python 
mycursor = mydb.cursor()
sql = open("<filename>.sql").read()
mycursor.execute(sql)

cur = mydb.cursor()
sql = open("test.sql").read()
cur.execute(sql)
mydb.close()

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

# test02.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");

# python
mycursor = mydb.cursor()
sql = open("test02.sql").read()
mycursor.execute(sql, multi=True)
mydb.commit()
mydb.close()

Fetch All

• sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
	print(data)
-----------------------------------------------------
cur = mydb.cursor(buffered=True)
sql = open("test02.sql").read()
result = cur.execute(sql, multi=True)

for result_iterator in result:
	if result_iterator.with_rows:
    	print(result_iterator.fetchall())
    else:
    	print(result_iterator.statement)
mydb.commit()
mydb.close()

# dataframe으로 읽기
import pandas as pd
df = pd.DataFrame(result)
df.head()

4. Python with CSV

1) 제공받은 police_station.csv 를 Pandas 로 읽어와서 데이터를 확인
2) csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정 (특히 우리나라 사이트에서 제공받은 csv 파일들.)
3) mysql 연결
4) Query 를 실행하기 위한 코드
5) CSV 에 있는 데이터를 Python 으로 INSERT
6) commit() 은 database 에 적용하기 위한 명령
7) 결과 확인

# 1, 2번
import pandas as pd
df = pd.read_csv("police_station.csv", encoding="euc-kr")
df.head()

# 3번
conn = mysql.connector.connect(
    host="hostname",
    port = port,
    user ="username",
    password = "password",
    database = "databasename"
)

# 4번
cursor = conn.cursor(buffered=True)
sql = "INSERT INTO police_station VALUES (%s, %s)"

# 5번(for문에 commit()을 넣어주면 에러가 생겨도 그 전까지는 데이터가 넣어짐)
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

# 6번
cursor.execute("SELECT * FROM police_station")
result = cursor.fetchall()
for row in result:
    print(row)
    
#추가 데이터프레임으로 확인
df = pd.DataFrame(result)
df.head()

conn.close()

---

Reference
1) 제로베이스 강의자료