Python with CSV

---

Ex 1

---

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

import pandas as pd

df = pd.read_csv('police_station.csv')
df.head()

• cursor 만들기
  • 읽어올 양이 많은 경우 cursor 생성 시 buffer 설정

import mysql.connector

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

cursor = conn.cursor(buffered = True)

• sql문 만들기

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

• commit() : Database에 적용하기 위한 명령

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

• 결과 확인

cursor.execute('SELECT * FROM police_station')

result = cursor.fetchall()

for row in result:
    print(row)

• 검색결과를 Pandas로 읽기

df = pd.DataFrame(result)
df

Tip

---

• csv 한글이 깨지는 경우, encoding 값을 ‘euc-kr’로 설정
  • 우리나라 사이트에서 제공받은 csv 파일들의 경우 대부분 사용

import pandas as pd

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

Ex 2

---

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

import mysql.connector
import pandas as pd

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

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

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

# 데이터를 crime_status 테이블에 INSERT
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

• crime_status 테이블 데이터 조회

# crime_status 테이블 데이터 조회

cursor.execute('SELECT * FROM crime_status')

result = cursor.fetchall()

for row in result:
    print(row)

• 조회 결과를 Pandas로 변환해서 확인

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

실습

---

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

• localhost로 대체

import mysql.connector

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

2. CCTV 테이블 생성

• 데이터 형태 확인

import pandas as pd

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

• SQL query 생성

sql = 'CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)'

cursor = mydb.cursor(buffered = True)
cursor.execute(sql)

• cctv table 확인

3. CCTV SQL 파일에서 데이터를 Pandas로 읽어오기

import pandas as pd

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

4. 데이터를 cctv 테이블에 INSERT

sql = 'INSERT INTO cctv VALUES (%s, %s, %s, %s, %s, %s)'

cursor = mydb.cursor(buffered = True)

for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    mydb.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()