[zerobase_데이터취업스쿨] SQL_CH12-01~CH13-11 [python으로 mysql 조작하기, aws rds 실습]

CH12_02: SQL FILE 실행 예제

MYSQL 로그인 이후 SQLFILE 열기

• sql 파일 실행하기
• mysql> source </path/filename.sql>
• mysql> . </path/filename.sql>
• 만약 현재 폴더에 파일 있으면 path 생략가능

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| person             |
| police_station     |
| snl_show           |
| table1             |
| table2             |
| test1              |
| test2              |
+--------------------+
8 rows in set (0.00 sec)

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(16)  | YES  |     | NULL    |       |
| address | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

CH12_03: SQL FILE 실행 예제

MYSQL 로그인 하지 않고 sql file 바로 열기

C:\Users\kd010\Documents\sql_ws>mysql -u root -p zerobase < test02.sql
Enter password: **********


mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year           | year        | YES  |     | NULL    |       |
| police_station | varchar(8)  | YES  |     | NULL    |       |
| crime_type     | varchar(16) | YES  |     | NULL    |       |
| status_type    | char(2)     | YES  |     | NULL    |       |
| case_number    | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

CH12_05: 데이터베이스 백업하기

데이터베이스 백업해보기

# 특정 데이터베이스에 대해서만 백업 
mysqldump -u username -p databasename > backup.sql(백업파일이름)
# 모든 데이터베이스에 대해 백업 
mysqldump -u username -p --all-databases > backup.sql

• 밑의 백업sql파일에서 보듯이 백업한 데이터베이스의 테이블을 모두 DROP 한 후에 백업 시점의 테이블 정보로 다시 CREATE 하게 된다.

--
-- Table structure for table `celeb`
--

DROP TABLE IF EXISTS `celeb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `celeb` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(32) NOT NULL DEFAULT '',
  `BIRTHDAY` date DEFAULT NULL,
  `AGE` int DEFAULT NULL,
  `SEX` char(1) DEFAULT NULL,
  `JOB_TITLE` varchar(32) DEFAULT NULL,
  `AGENCY` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `celeb`
--

LOCK TABLES `celeb` WRITE;
/*!40000 ALTER TABLE `celeb` DISABLE KEYS */;
INSERT INTO `celeb` VALUES (1,'아이유','1993-05-16',29,'F','가수, 탤런트','EDAM엔터테이먼트'),(2,'이미주','1994-09-23',28,'F','가수','울림엔터테이먼트'),(3,'송강','1994-04-23',28,'M','탤런트','나무엑터스'),(4,'강동원','1981-01-18',41,'M','영화배우, 탤런트','YG엔터테이먼트'),(5,'유재석','1972-08-14',50,'M','MC, 개그맨','안테나'),(6,'차승원','1970-06-07',48,'M','영화배우, 모델','YG엔터테이먼트'),(7,'이수현','1999-05-04',23,'F','가수','YG엔터테이먼트');
/*!40000 ALTER TABLE `celeb` ENABLE KEYS */;
UNLOCK TABLES;

CH12_06: 데이터베이스 백업, restore

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

• 즉, 따로 복구하는 방법이 있는게 아니고 sql file을 실행하면 복구한것임

AWS RDS가 사용가능한 상태에서 mysql 접속하기(aws rds로)

AWS RDS의 엔드포인트를 사용하여 MYSQL 실행하기
mysql -h "엔드포인트주소" -P 포트번호 -u 어드민이름 -p

AWS RDS의 database-1으로 mysql 접속하여 지난번에 만들어놨던 ZEROBASE데이터베이스에 아까 로컬에서 백업해 놨던 SQL FILE을 실행시켜서 DATABASE-1의 ZEROBASE데이터베이스에 넣어주자

로컬에서 zerobase 데이터베이스 전체 백업하기

mysqldump -u 유저네임 -p 데이터베이스이름 > backup.sql

AWS RDS(DATABASE-1)의 ZEROBASE에서 celeb 테이블 삭제하고 다시 TABLE RESTORE 하기

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

로컬 데이터베이스에서 snl_show Table Schema(데이터가 포함되지 않은 그냥 테이블의 Schema만) 백업하기

• 아까 데이터 백업과 문법은 동일한데 mysqldump 뒤에 -d가 추가됨

mysqldump -d -u 유저네임 -p 데이터베이스이름 테이블이름 > backup.sql

CH12_09: 데이터베이스 백업 문제풀이

AWS RDS(database-1)의 ZEROBASE 데이터베이스 백업하기 그냥 로컬에서 이 명령어 입력하면 AWS RDS의 데이터베이스의 데이터를 로컬에 백업할 수 있다

주의> —set-gtid-purged=OFF -h <hostname> -P <port> 를 추가해야함

```sql
mysqldump --set-gtid-purged=OFF -h <host> -P <port> -u admin -p <tablename> > backup.sql

AWS RDS에 admin 계정으로 로그인 후 Database(zerodb)를 생성하기

mysql> create database zerodb default character set utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ZEROBASE           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerodb             |
+--------------------+
6 rows in set (0.01 sec)

앞서 생성한 사용자 (zero@%)에게 zerodb의 모든 권한을 부여하기

mysql> grant all on zerodb.* to 'zero'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'zero'@'%';
+----------------------------------------------------+
| Grants for zero@%                                  |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `zero`@`%`                   |
| GRANT ALL PRIVILEGES ON `ZEROBASE`.* TO `zero`@`%` |
| GRANT ALL PRIVILEGES ON `zerodb`.* TO `zero`@`%`   |
+----------------------------------------------------+
3 rows in set (0.01 sec)

앞서 백업한 zerobase 백업 파일을 zerodb에서 실행하기

use zerodb;

source back_up_zerobase.sql

police_station 테이블에 데이터 넣는 sql 파일 생성하기

sql file 실행하여 AWS RDS(Database-1) ZEROBASE의 police_station 테이블에 데이터를 INSERT 하고 확인하시오

AWS RDS(Database-1) zerobase의 police_station 테이블을 SQL 파일로 백업하시오

# 일단 로컬환경으로 나간 후
mysqldump --set-gtid-purged=Off -h "엔드포인트" -P <포트> -u admin -p 데이터베이스이름 테이블이름 > backup.sql

SQL 파일을 실행하여 AWS RDS(Database-1)의 zerodb의 police_station 테이블을 zerobase 와 동일하게 만들고 확인하시오

mysql> use zerodb;
Database changed
mysql> source police_station_data.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from police_station;
+--------------------------+-----------------------------------------+
| name                     | address                                 |
+--------------------------+-----------------------------------------+
| 서울특별시경찰청         | 서울시 종로구 사직로8길 31              |
| 서울중부경찰서           | 서울특별시 중구 수표로 27               |
| 서울종로경찰서           | 서울특별시 종로구 율곡로 46             |
| 서울남대문경찰서         | 서울특별시 중구 한강대로 410            |
+--------------------------+-----------------------------------------+
4 rows in set (0.01 sec)

CH13_01~04: python with MySQL

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

AWS RDS(Database-1) zerobase의 police_station 데이터 모두 지워주기

Python에서 MySQL 사용하기 위하여 MySQL Driver 설치하기

!pip install mysql-connector-python

python으로 MySQL 접속위한 코드

## 로컬의 경우
mydb = mysql.connector.connect(
	host = "<hostname>",
	user = "<username>",
	password = "<password>")

python으로 AWS RDS(Database-1)에 접속하기

remote = mysql.connector.connect(
		host = "<host>",
		port = port숫자,
		user = "<username>",
		password = "<password>")

작업 끝난 뒤에는 반드시 close() 해주기

local.close()
remote.close()

CH13_05: python으로 sql 쿼리 실행해보기

테이블생성

터미널에서 AWS RDS(Database-1)의 zerobase 데이터베이스에 sql_file 테이블이 생성되었는지 결과 확인

테이블 삭제

AWS RDS(Database-1)의 zerobase 데이터베이스에서 sql_file 테이블 삭제되었는지 확인

sql_file을 python을 통해 실행하기

cur = remote.cursor()
sql = open("test04.sql").read()
cur.execute(sql) 
# 이것을 실행하면 sql객체에 담긴 test04.sql 파일이 생성된다
remote.close()

sql_file에 쿼리가 여러개 존재하는 경우(execute(sql, multi = True)로 해야함

1. for result_iterator in cur.execute(sql, multi=True):

   이 줄은 쿼리 또는 여러 쿼리의 결과 집합에 대해 반복합니다. multi=True 매개변수는 sql 이 세미콜론으로 구분된 여러 SQL 문을 포함할 수 있음을 나타냅니다. cur 변수는 데이터베이스 연결로부터 얻은 커서 객체입니다.

2. if result_iterator.with_rows:

   현재 result_iterator가 행을 가지고 있는지 확인합니다. 즉, 데이터를 반환하는 SELECT 쿼리 또는 다른 명령의 결과입니다.

3. print(result_iterator.fetchall())

   현재 이터레이터가 가진 모든 결과 행을 출력합니다. fetchall() 함수는 쿼리의 결과로 반환된 모든 데이터를 한 번에 가져오는 데 사용됩니다.

4. print(result_iterator.statement)

   실행된 SQL 문을 출력합니다. 이는 데이터를 반환하지 않는 명령의 경우 유용할 수 있습니다.

5. remote.commit()

   `remote는 데이터베이스 연결 객체로 추정됩니다. commit()함수를 호출하여 트랜잭션을 커밋합니다. 이는INSERT, UPDATE, DELETE 등의 문이 데이터베이스에 영구적으로 반영되도록 합니다.

6. remote.close()

   데이터베이스 연결을 닫습니다. 모든 작업이 끝난 후 연결을 닫는 것은 좋은 습관입니다.

CH13_07: fetchall() 이론

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

cur = remote.cursor(buffered = True)

cur.execute("select * from sql_file")

result = cur.fetchall()
for result_iter in result:
    print(result_iter)
remote.close()

fetchall()을 담아준 변수를 DataFrame으로 변환 가능

CH13_08~11 : Python with MySQL(csv읽어오기)

police_station.csv를 Pandas로 읽어오기

AWS RDS(Database-1)의 zerobase 데이터베이스의 police_station 테이블에 정보 넣기

터미널에서 확인하지 않고 주피터노트북에서 python을 통해 바로 결과확인하기(fetchall())을 통하여

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

cur = conn.cursor(buffered = True)
sql = "insert into crime_status values('2020', %s, %s, %s, %s)"
for idx, row in df_c.iterrows():
    cur.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

python으로 crime_status 테이블에서 발생인 행들과 police_station의 값을 출력해보고 데이터프레임으로 만들어보기