SQL - Python with MySQL

1. MySQL 연결

• VSCode에서 jupyter Notebook 실행 ds_study 환경 작업

• Jupyter Notebook MySQL은 모두 sql_ws 같은 폴더에서 작업

• AWS RDS가 생성한 databae-1이 접속 상태인지 확인

• ds_study 환경으로 새로운 Jupyter Notebook 파일 생성

• Python에서 MySQL 사용을 위한 MySQL Driver 설치

(1) MySQL 접속

• MySQL 접속위한 connect 생성
  - pip install mysql-connector-python #cmd 창
  주의!!! 연결 후는 반드시 연결 해제할 것
  (예제)

  • LOCAL DB 연결 시 및 연결해제

    mydb = mysql.connector.connect(
    	   host = "localhost",
    	   user = "root",
    	   password = "******",
           database = "databae_name"
    )
    mydb.close()

  • AWS RDS (database-1) 연결 시 및 연결해제

    	mydb = mysql.connector.connect(
     	   host = "database-1.*********.rds.amazonaws.com", 			#RDS 엔드포인트
     	   port = 3306,
     	   user = "admin",
     	   password = "********"
            database = "database_name"
    	)
    	mydb.close()
    	```
    (2) Query 실행

• SQL 쿼리 실행하기 위한 cursor 객체 생성
  AWS RDS로 연결시

  			mydb = mysql.connector.connect(
  	   host = "database-1.*********.rds.amazonaws.com", 			#RDS 엔드포인트
  	   port = 3306,
  	   user = "admin",
  	   password = "********"
         database = "database_name"
  			)
      mycursor = mydb.cursor()
      mycursor.execute("query_statement")
      mydb.close()
  		```
  (예제)
  (1) TABLE 생성
  	```
  			 remote = mysql.connector.connect(
  		host = "database-1.us-east-2.rds.amazonaws.com",
  		port = 3306,
  		user = "admin",
  		password = "***********",
  		database = "zerobase"
  	)
  
  	cur = remote.cursor()
  	cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
  			```
  - TABLE 확인
  		```
  		desc sql_file;	
  		```
  
  (2) TABLE 삭제
  	
      ```
  			cur = remote.cursor()
  	cur.execute("DROP TABLE sql_file")
  
  	remote.close()
  			```

---

(2) SQL파일 실행

• SQL파일 실행을 위한 코드

  	```
  	import mysql.connector

  mydb = mysql.connector.connect(
  host = "host_name",
  port = port_number,
  user = "user_name",
  password = "password",
  database = "database_name"
  )
  mycursor = mydb.cursor() sql = open("file_name.sql").read()

  * One Query

  mycursor.execute(sql)

  * Multi Query : Query가 여러개 존재하는 경우

  mycursor.execute(sql, multi=True) mydb.close()

  	```

  ---

  - ONE QUERY

  (예제1) test03.sql 파일 생성 (CREATE TABLE sql_file( id int, filename varchar(16)); 후 실행

  ```

  		    remote = mysql.connector.connect(
  			 host = "database-1.***.amazonaws.com",
  			 port = 3306,
  			 user = "admin",
  			 password = "***********",
  			 database = "zerobase"
  	)
  	cur = remote.cursor()
  	sql = open("test03.sql").read()
  	cur.execute(sql)
  
  	remote.close()
  		```

  - MULTI QUERY

  (예제 2) test04.sql 파일 생성
  INSERT INT sql_file VALUES (1, "test01.sql")
  INSERT INT sql_file VALUES (2, "test02.sql")
  INSERT INT sql_file VALUES (3, "test03.sql")
  INSERT INT sql_file VALUES (4, "test04.sql")

      후에 실행
      ```
      ```
  			remote = mysql.connector.connect(

  host = "database-1.us-east-2.rds.amazonaws.com",
  port = 3306,
  user = "admin",
  password = "****",
  database = "zerobase"
  ) 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()

  			```

• FETCH ALL
  - cursor.fetchall()
  - SELECT 문 실행해 테이블의 tuple데이터 가지고 올 때 사용
  - 읽어올 데이터 양이 많은 경우 buffered = True옵션

     (예제) sql_file 테이블 조회
     ```
  	remote = mysql.connector.connect(
  			 host = "database-1.us-east-2.rds.amazonaws.com",
  			 port = 3306,
  			 user = "admin",
  			 password = "*************",
  			 database = "zerobase"
  	)
  
  	cur = remote.cursor(buffered=True)
  	cur.execute("SELECT * FROM sql_file")
  
  	result = cur.fetchall()
  	print(result)
  
  	for result_iter in result:
      	print(result_iter)
      
  	remote.close()
  	```

  =====================================================

2. CSV 데이터로 DB 테이블 만들기

• CSV에 있는 데이터를 Python으로 읽어와 MySQL(INSERT)를 이용해 데이터베이스 테이블로 만듬

(1) CSV 파일 읽기
- police_station.csv 파일을 Pandas로 읽어와 데이터를 확인 (한글 깨질때 encoding = 'euc-kr')

```
import pandas as pd

df = pd.read_csv("police_station.csv")
```

(2) MySQL연결
- AWS RDS의 (database-1)으로 연결

	```
    ```
   import mysql.connector

	connector = mysql.connector.connect(
				host = "database-1.*****.rds.amazonaws.com",
				port = 3306,
				user = "zero",
				password = "zerobase",
				database = "zerobase"
	)

`` ```
(3) CURSOR 생성
- 읽어올 데이터 양이 많으면 cursor 생성 시 buffer = True

```
cursor = connector.cursor(buffered=True)
```

(4) INSERT 문
- pandas.DataFrame 형태의 데이터를 police_station 테이블의 데이터로 넣기 위한 INSERT Query문 생성

	```
	 sql = "INSERT INTO police_station VALUES (%s, %s)"
	```

(5) 데이터 입력

```
mysql.connector execute()

commit() : 데이터베이스에 적용하기

 for idx, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    connector.commit()

(6) 결과 확인
	데이터베이스에 데이터가 잘 반영되어 있는지 확인
  
  ```
  cursor.execute("SELECT * FROM police_station")
  result = cursor.fetchall()
  for row in result:
      print(row)
  
  connector.close()
	```
(7) DataFrame으로 결과읽기
	
  ```
	 result_df = pd.DataFrame(result)
	```