Python with CSV
- 큰 데이터를 한꺼번에 넣고 싶을 때
- CSV에 있는 데이터를 Python으로 INSERT
💡 .commit() : 데이터를 DB에 저장 시키는 명령어
(for문 중 fail이 나도 fial 전까지의 데이터가 저장 됨)
🙄 csv 한글이 깨지는 경우, encoding 값을 euc-kr로 설정
(특히 한국 사이트에서 제공받은 csv 파일들)
- CSV에 있ㄴ느 데이터를 Python으로 INSERT
- police_station.csv 파일을 Pandas로 읽어오기
import pandas as pd
df = pd.read_csv('police_station.csv')
df.tail()
- Table 'zerobase'에 연결
import mysql.connector
conn = mysql.connector.connect(
host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "94woals!!",
database = "zerobase"
)
cursor = conn.cursor(buffered=True)- cursor + buffer 설정
cursor = conn.cursor(buffered=True)- insert 문 만들기
- sql 변수
- %s : stirng 형식의 column이 들어갈 자리 확보
- %s, %s : police_staition 컬럼은 총 2개
sql = "INSERT INTO police_station VALUES (%s, %s)"- 데이터 입력
- commit() : database에 적용하기 위한 명령
- tuple(row) : 2데이터가 짝을 이뤄 담겨짐
- cursor.execute : cursor의 execute메서드
- sql, tuple(row) : 위 [sql=쿼리]에 tuple(row) 데이터 각각 보내줌
MySQL 메뉴얼_참고
for i, row in df.iterrows():
cursor.execute(sql,tuple(row))
print(tuple(row))
conn.commit()
- 검색 결과 pandas로 읽기
- "SELECT * FROM police_station" : 에 있는 데이터를 확인하고 싶다
- for : 리스트 형태니까 for문으로 하나씩 꺼내오기
- pd.DataFrame(result) : pandas로 데이터 가져오기
cursor.execute("SELECT * FROM police_station")
result = cursor.fetchall()
for row in result:
print(row)
df = pd.DataFrame(result)
df
예제
- Table 'crime_status'에 2020_crime.csv 를 입력하는 코드 작성
- 기본세팅
import mysql.connector
conn = mysql.connector.connect(
host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "94woals!!",
database = "zerobase"
)- 2020_crime.csv 데이터(encoding='euc-kr') 읽어오기
import pandas as pd
df = pd.read_csv('2020_crime.csv', encoding='euc-kr')
df.head()
- INSERT 쿼리 작성
# %s, %s, %s, %s : 컬럼 4개니까.
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() # 실행 될 때 마다 commit을 날려 저장
- 테이블 데이터 조회
cursor.execute("SELECT * FROM crime_status")
result = cursor.fetchall()
for row in result:
print(row)
- 조회한 결과를 pandas로 확인
df = pd.DataFrame(result)
df
실습
- AWS RDS(database-1) zerobase에 접속
import mysql.connector
conn = mysql.connector.connect(
host = "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "94woals!!",
database = "zerobase"
)- CCTV Table 생성
- Pandas로 읽어오기
import pandas as pd
df = pd.read_csv('Seoul_CCTV.csv', encoding='utf-8')
df.tail()
- Table 만들기 - VS Code
sql = 'CREATE TABLE cctv (기관명 varchar(0), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)'
cursor = conn.cursor(buffered=True)
cursor.execute(sql)- Table 확인 - VS Code_cmd
C:\Users\PC\Documents\sql_ws> mysql -h "database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p94woals!! zerobase
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| cctv |
| celeb |
| crime_status |
| oil_price |
| person |
| police_station |
| refueling |
| snl_show |
| sql_file |
| test1 |
| test2 |
+--------------------+
11 rows in set (0.15 sec)
mysql> desc cctv;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| 기관명 | varchar(0) | YES | | NULL | |
| 소계 | int | YES | | NULL | |
| 2013년도이전 | int | YES | | NULL | |
| 2014년 | int | YES | | NULL | |
| 2015년 | int | YES | | NULL | |
| 2016년 | int | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
6 rows in set (0.17 sec)
- 데이터를 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()- 데이터 조회 & 확인
cursor.execute("select * from cctv")
result = cursor.fetchall()
for row in result:
print(row)df = pd.DataFrame(result)
df.head()mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| cctv |
| celeb |
| crime_status |
| oil_price |
| person |
| police_station |
| refueling |
| snl_show |
| sql_file |
| test1 |
| test2 |
+--------------------+
11 rows in set (0.15 sec)
mysql> select * from cctv
-> ;
+----------+------+--------------+--------+--------+--------+
| 기관명 | 소계 | 2013년도이전 | 2014년 | 2015년 | 2016년 |
+----------+------+--------------+--------+--------+--------+
| 강남구 | 3238 | 1292 | 430 | 584 | 932 |
| 강동구 | 1010 | 379 | 99 | 155 | 377 |
| 강북구 | 831 | 369 | 120 | 138 | 204 |
| 강서구 | 911 | 388 | 258 | 184 | 81 |
| 관악구 | 2109 | 846 | 260 | 390 | 613 |
| 광진구 | 878 | 573 | 78 | 53 | 174 |
| 구로구 | 1884 | 1142 | 173 | 246 | 323 |
| 금천구 | 1348 | 674 | 51 | 269 | 354 |
| 노원구 | 1566 | 542 | 57 | 451 | 516 |
| 도봉구 | 825 | 238 | 159 | 42 | 386 |
| 동대문구 | 1870 | 1070 | 23 | 198 | 579 |
| 동작구 | 1302 | 544 | 341 | 103 | 314 |
| 마포구 | 980 | 314 | 118 | 169 | 379 |
| 서대문구 | 1254 | 844 | 50 | 68 | 292 |
| 서초구 | 2297 | 1406 | 157 | 336 | 398 |
| 성동구 | 1327 | 730 | 91 | 241 | 265 |
| 성북구 | 1651 | 1009 | 78 | 360 | 204 |
| 송파구 | 1081 | 529 | 21 | 68 | 463 |
| 양천구 | 2482 | 1843 | 142 | 30 | 467 |
| 영등포구 | 1277 | 495 | 214 | 195 | 373 |
| 용산구 | 2096 | 1368 | 218 | 112 | 398 |
| 은평구 | 2108 | 1138 | 224 | 278 | 468 |
| 종로구 | 1619 | 464 | 314 | 211 | 630 |
| 중구 | 1023 | 413 | 190 | 72 | 348 |
| 중랑구 | 916 | 509 | 121 | 177 | 109 |
+----------+------+--------------+--------+--------+--------+
25 rows in set (0.15 sec)
비전공자의 데이터 공부법