Python with MySQL

실습 환경 세팅

1. Web Browser나 VSCode를 통해 Jupyter Notebook을 실행하고, ds_study 환경에서 작업한다.
2. Jypyter Notebook과 MySQL은 모두 sql_ws 폴더에서 시작한다.
3. AWS RDS로 생성한 database-1 DB가 사용 가능한 상태인지 확인한다.
4. AWS RDS (database-1) zerobase DB의 police_station Table의 데이터를 모두 지워준다.
5. ds_study 환경으로 새로운 Jupyter Notebook 파일(.ipynb)을 생성한다.

MySQL 연결

❕ Install MySQL Dirver

• Python에서 MySQL을 사용하기 위해서는 먼저 MySQL Driver를 설치해야 한다.

    pip install mysql-connector-python

• Jupyter Notebook에서 module이 잘 import되는지 확인한다.
  

❕ MySQL 접속

• MySQL에 접속하기 위해 connect를 생성한다.

• 연결한 후에는 반드시 연결을 해제해야 한다.
  

    import mysql.connector
    
    mydb = mysql.connector.connect(
    		host = "host_name",
    		port = port_number, //--> 외부 접속으로 포트 번호가 필요할 때 추가해 준다.
    		user = "user_name",
    		password = "password",
    		database = "database_name" //--> 특정 DB로 바로 접속하고자 할 때 추가한다.
    )
    mydb.close()

❕ Query 실행

• SQL 쿼리를 실행하기 위한 cursor 객체를 생성한다.

    import mysql.connector

    mydb = mysql.connector.connect(
            host = "host_name",
            port = port_number,
            user = "user_name",
            password = "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 확인
  
  
• 예제2. Table 삭제
  

    cur = remote.cursor()
    cur.execute("DROP TABLE sql_file")
    
    remote.close()

SQL파일 실행

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

    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
    mycursor.execute(sql, multi=True)
    
    mydb.close()

• SQL File 내에 Query가 여러개 존재하는 경우 multi=True 옵션을 준다.

🔰 One Query

• 예제1. test03.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("test03.sql").read()
    cur.execute(sql)
    
    remote.close()

🔰 Multi Query

• 예제2. 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()
    /*실행결과
    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")*/

🔰 Fetch All

• cursor.fetchall()

  • SELECT문을 실행해 Table의 Tuple 데이터를 가져올 때 사용하는 함수
  • 읽어올 데이터 양이 많은 경우 buffered=True 옵션을 사용한다.

• 예제: sql_file Table 조회
  

    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)
    // [(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]
    
    for result_iter in result:
        print(result_iter)
    /*  (1, 'test01.sql')
    	(2, 'test02.sql')
    	(3, 'test03.sql')
    	(4, 'test04.sql')  */
        
    remote.close()

CSV → DB

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

1. CSV 읽기

• police_station.csv 파일을 Pandas로 읽어와 데이터를 확인한다.
  

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

• csv 파일의 한글이 깨지는 경우, encoding 값을 ‘euc-kr’로 설정해 준다.

2. MySQL 연결

• AWS RDS (database-1)의 zerobase DB에 연결한다.
  

    import mysql.connector
    
    connector = mysql.connector.connect(
    	host = "database-1.us-east-2.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 생성

• 결과를 Pandas DataFrame으로 읽는다.
  

    result_df = pd.DataFrame(result)