AWS RDS

• AWS(Amazon Web Services)에서 제공하는 RDS(Relational Database Service)
• 클라우드 환경에서 데이터베이스를 손쉽게 설정, 운영, 확장할 수 있는 시스템
• 터미널 환경에서 -h 옵션으로 RDS의 엔드포인트와 -P(port) 옵션으로 포트를 설정하면 어디서나 접속할 수 있다.

SQL 파일

• SQL 쿼리를 모아놓은 파일
• 여러 개의 쿼리를 한꺼번에 실행할 수 있음
• database, table 등을 백업하고 복원(resotre)할 때 용이하다.

SQL 파일 실행

• 방법1: 파일이 저장된 위치에서 SQL에 접속해서 실행

source filename.sql

• 방법2: 터미널 환경에서 바로 실행

mysql -u username -p <database> < </path/filename.sql>

SQL 백업

• SQL 파일로 database를 백업할 수 있다.

mysqldump -u username -p dbname > backup.sql            # 특정 db 백업
mysqldump -u username -p --all-databases > backup.sql   # 모든 db 백업

• 백업시 기존에 데이터와 중복되는 경우, 기존 데이터를 삭제하고 다시 만들도록 SQL 백업파일이 생성된다.

위 방식으로 백업 후 RDS에서 실행했을 때 에러가 발생하는 경우가 있다.
인코딩 문제로 보이는데, 백업시 언어 설정을 기본값으로 설정했던 utf8mb4가 아닌 utf8로 설정해줘서 해결할 수 있었다.

mysqldump -u username -p dbname --default-character-set utf8 > backup.sql

mysql에 접속할 때 인코딩을 설정하는 방법도 있다

mysql -u username -p --default -character-set utf8mb4

• AWS RDS의 데이터베이스를 백업할 때는 옵션을 추가해야 한다.

mysqldump --set-gtid-purged=OFF -h hostname -P port -u username -p dbname > backup.sql

Table 백업

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

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

• 복원(resotre) 방법은 위 SQL 파일 실행과 같다.

Table Schema 백업

• Table에서 데이터를 제외하고 테이블 정보만 백업할 수도 있다.
• 방법은 table 백업과 동일하고, user 옵션 앞에 -d 옵션을 추가하면 된다.

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

Python - SQL

• mysql-connector-python 모듈 설치

pip install mysql-connector-python

• python에서 불러올 때

import mysql.connector

Create Connection

mydb = mysql.connector.connect(
    host = "<hostname>",
    port = <port>,   # local 연결시 생략
    user = "<username>",
    password = "<password>",
    database = "<databasename>"   # 생략시 DB선택 없이 mysql 접속
)

Execute SQL

• cursor를 만들어 sql 쿼리문을 전송한다.

cur = mydb.cursor()
cur.excute("<query>")

SQL 파일 실행

• open().read() 함수를 통해 실행

sql = open("<filename>.sql").read
cur.excute(sql)

TMI

open("file") 자체는 특수 클래스(_io.TextIOWrapper)로, 뒤에 .read()를 써줘야 안의 내용을 변수에 담을 수 있다.

• sql 파일에 여러 문장이 들어있는 경우, multi=True 옵션을 추가해야 한다.
  사용법

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

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

• commit()을 실행함으로써 sql 데이터에 반영된다.(insert문 등이)

TMI

• cur.excute(sql)에서 sql이 단문인 경우, return값이 없이 실행만 되는 메서드이다.(print문 안에 있더라도 쿼리는 실행된다)
  
• 하지만 sql이 복문(여러 쿼리 포함)이라면, generator 객체를 return한다.
  
  

TMI2

• 여러 sql문을 한꺼번에 execute할 때 executemany()를 쓸 수 있다.

Fetch All

• fetch: 가지고[데리고/불러] 오다
• sql에서 select문을 사용하는 경우 결과 데이터를 가져온다.
• 가져온 데이터를 변수에 담을 때 cur.fetchall()을 사용한다.
• 읽어올 데이터 양이 많을 때는 커서를 만들 때 buffered=True 옵션을 주는 것이 좋다.
• fetchall()을 통해 가져온 데이터는 list 안에 tuple 형태로 담긴다.

  

Pandas 활용

• fetchall()을 통해 가져온 데이터가 리스트/튜플이므로, pandas를 통해 데이터프레임으로 만들 수 있다.

  

• execute()에 data를 직접 넣어 변수를 한꺼번에 입력할 수 있다.
  cursor.execute(query, data)

cur = remote.cursor(buffered=True)
sql = "INSERT INTO police_station VALUES (%s, %s)"

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

TMI

1. 강의에서는 tuple 형태로 넣었는데, list도 가능했다.
2. executemany(query, data)를 쓰면 for문을 쓰지 않고 여러 데이터를 한번에 입력할 수 있다.
   
   executemany()에 들어가는 데이터는 컨테이너 안에 컨테이너 형태로 들어가야 한다.
   pandas df를 넣기 위해서는 자료형 변환이 필요하다.