SQL 심화(4)_Python with CSV : insert, commit( )

.csv 파일을 SQL table에 INSERT

(by. Python)

1) Read CSV

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

2) cunnection (with AWS RDS)

3) Cursor 생성

• 만약 읽어올 데이터 양이 많을 경우 cursor 생성 시 (buffered=True)
  

4) INSERT 문 만들기

• '%s' : 값은 string으로 처리한다는 의미
  

5) commit( )으로 데이터 입력하기

• commit( )은 database에 적용하기 위한 명령어임.
  = 따라서 vscode, jupyter notebook 등에서 코드를 작성해도 마지막에 commit( )으로 마무리 하지 않으면 작성한 코드의 내용, 결과가 데이터베이스에 반영되지 않음
  

  ---> 이 예제의 경우, for문이 한바퀴 돌 때마다 commit( )으로 결과를 데이터베이스에 반영하도록 작성되어 있는데///

  • 만약 for문 사이사이에 commit( )을 넣지 않고 모든 코드가 실행된 가장 마지막에 작성할 경우, for문 실행 도중 fail이 생기면 마지막 줄의 commit( )까지 코드가 도달할 수 없으므로 결국 database에 추가된 내용이 반영되지 않을 수도 있음
  • 본 예제와 같이 for문이 한바퀴 돌 때마다 commit( )을 실행시킨다면,
    예를 들어 for문을 10번 반복하는 도중 6번째에서 fail이 발생해도 5번째 반복문 내용까지는 database에 반영된 것을 확인할 수 있음

6) 결과 확인 1
(터미널이 아닌) vs code에서 바로 출력하여 확인_print

7) 결과 확인 2

• 검색결과를 pandas로 읽어올 수도 있음
  

---

CSV 한글이 깨질 때

• CSV의 한글이 깨지는 경우, read_csv 할 때 encoding 값을 euc-kr로 설정할 것 (특히 우리나라 사이트에서 제공받은 CSV 파일에서 빈번하게 발생)
• write 할 때도 한글이 깨진다면 간단하게 utf8로 변환시키면 ok

---

실습 예제(1)

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

1) AWS RDS(database-1) zerobase에 접속

2) 2020_crime.csv 데이터(encoding='euc-kr') 읽어오기

3) INSERT 쿼리 작성

• 기존 CSV 파일에는 2020년이 없기 때문에 고정으로 2020 값을 지정함
  = ("2020", %s, %s, %s, %s)

sql = "INSERT INTO crime_status VALUES ('2020', %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

또는

4) 데이터를 crime_status 테이블에 INSERT

5) crime_status 테이블의 데이터 조회(결과 확인)

6) 조회한 결과를 Pandas로 변환해서 확인하기

실습 예제(2)

• SQL 테이블 생성 --> csv 파일 데이터 insert --> pandas(df)로 읽어오기

1. 계정 로그인

import mysql.connector

conn = mysql.connector.connect(
    host = "database-1.cexprgis0las.ap-northeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "***********",
    database = "zerobase"
)

2. pandas로 데이터프레임 살펴보기

import pandas as pd

df = pd.read_csv('Seoul_CCTV.csv', encoding='utf-8')
df.head(2)

3. csv파일을 pandas로 읽어오기

sql = "CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cursor = conn.cursor(buffered=True)
cursor.execute(sql)

4. 데이터를 CCTV테이블에 insert

sql = "INSERT INTO cctv VALUES (%s, %s, %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

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

5. CCTV 테이블 조회하여 데이터 확인

cursor.execute("SELECT * FROM cctv")

result = cursor.fetchall()
for row in result:
    print(row)

6. 조회된 데이터를 pandas로 변환하여 출력

df = pd.DataFrame(result)
df.head()