[SQL] Python with MYSQL / CSV

• 사전 작업
  • AWS RDS(database-1) zerobase 의 police_station 테이블의 데이터를 모두 지워준다.

    use zerobase;
    delete from police_station;

1️⃣ 파이썬에서 MySQL 사용하기

1. Python 에서 MySQL 을 사용하기 위해서는 먼저 MySQL Driver 설치하기

   pip install mysql-connector-python

• 설치 확인

  import mysql.connector

2. MySQL 접속하기

• 로컬 데이터베이스에 연결하기

  local = mysql.connector.connect(
      host = 'localhost',
      user = 'root',
      password = '********'
  )

• AWS RDS (database-1) 연결하기

  remote = mysql.connector.connect(
      host = '_____',
      port = ____,
      user = 'admin',
      password = '********'
  )

• 특정 데이터베이스에 접속하기: Local MySQL 의 zerobase 연결

  local = mysql.connector.connect(
      host = 'localhost',
      user = 'root',
      password = '********',
      database = 'zerobase'
  )

3. 데이터베이스 닫기

   local.close()
   remote.close()

2️⃣ 파이썬에서 MySQL 사용하기 - 쿼리 실행

• 테이블 생성

  remote = mysql.connector.connect(
      host = '_____',
      port = ____,
      user = 'admin',
      password = '********'
      database =  'zerobase'
  )
  
  cur = remote.cursor()
  cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
  
  remote.close()

  

• 테이블 삭제

  remote = mysql.connector.connect(
      host = '_____',
      port = ____,
      user = 'admin',
      password = '********'
      database =  'zerobase'
  )
  
  cur = remote.cursor()
  cur.execute("DROP TABLE sql_file")
  
  remote.close()

3️⃣ 파이썬에서 MySQL 사용하기 - sql 파일 실행

remote = mysql.connector.connect(
    host = '_____',
    port = ____,
    user = 'admin',
    password = '********'
    database =  'zerobase'
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

4️⃣ 파이썬에서 MySQL 사용하기 - sql 파일 실행(쿼리 여러 개)

• Multi = True 옵션을 준다.

  remote = mysql.connector.connect(
      host = '_____',
      port = ____,
      user = 'admin',
      password = '********'
      database =  'zerobase'
  )
  
  cur = remote.cursor()
  sql = open("test03.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()

  

5️⃣ Fetch All

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

  remote = mysql.connector.connect(
      host = '_____',
      port = ____,
      user = 'admin',
      password = '********'
      database =  'zerobase'
  )
  
  cur = remote.cursor(buffered=True)
  cur.execute('SELECT * FROM sql_file')
  
  result = cur.fetchall()
  for result_iterator in result:
      print(result_iterator)
  
  remote.close()

• 검색 결과를 판다스로 읽기!

  import pandas as pd
  
  df = pd.DataFrame(result)
  df.head()

  

6️⃣ Python with CSV

• CSV 에 있는 데이터를 Python 으로 INSERT

1. 제공받은 police_station.csv 를 Pandas 로 읽어와서 데이터 확인

   import pandas as pd
   
   df = pd.DataFrame('police_station.csv')
   df.head()

2. zerobase에 연결

   import mysql.connector
   
   conn = mysql.connector.connect(
       host = '_____',
       port = ____,
       user = 'admin',
       password = '********'
       database =  'zerobase'
   )

3. 읽어올 양이 많은 경우 cursor 생성 시 buffer 설정하기

   cursor = conn.cursor(buffered=True)

4. INSERT문 만들기

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

5. 데이터 입력하기

   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)

   

7. 검색결과를 Pandas 로 읽기

   df = pd.DataFrame(result)
   df

8. 기타: csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정 (특히 우리나라 사이트에서 제공받은 csv 파일)

   import pandas as pd
   
   df = pd.read_csv('2020_crime.csv', encoding='euc.kr')
   df.head()