[SQL] Python with MySQL

0. DB에 연결하기¶

• mysql.connector.connect()
• close()

In [41]:

import mysql.connector
import pandas as pd

In [3]:

local = mysql.connector.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '****',
    database = 'amrbase'
)
local.close()

1. SQL Query 실행¶

1.1 Execute¶

• cursor()
• execute()

In [4]:

remote = mysql.connector.connect(
    host = 'database-1.cccbm3nnjzrq.ap-northeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '****',
    database = 'amrbase'
)

cur = remote.cursor()
cur.execute('CREATE TABLE sql_file (id int, filename varchar(16))')

remote.close()

In [5]:

remote = mysql.connector.connect(
    host = 'database-1.cccbm3nnjzrq.ap-northeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '****',
    database = 'amrbase'
)

cur = remote.cursor()
cur.execute('DROP TABLE sql_file')

remote.close()

1.2 test03.sql 실행¶

• open().read()

In [6]:

remote = mysql.connector.connect(
    host = 'database-1.cccbm3nnjzrq.ap-northeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '****',
    database = 'amrbase'
)

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

remote.close()

1.3 SQL File 내에 Query 가 여러개 존재하는 경우¶

• Multi = True

In [10]:

remote = mysql.connector.connect(
    host = 'database-1.cccbm3nnjzrq.ap-northeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '****',
    database = 'amrbase'
)

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')

1.4 Fetchall¶

• SELECT 해온 것을 읽을 때

In [15]:

remote = mysql.connector.connect(
    host = 'database-1.cccbm3nnjzrq.ap-northeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '****',
    database = 'amrbase'
)

cur = remote.cursor(buffered=True)  # 읽어올 데이터 양이 많은 경우 buffered=True
cur.execute("SELECT * FROM sql_file")

result = cur.fetchall()
for result_iterator in result:
    print(result_iterator)

remote.close()

(1, 'test01.sql')
(2, 'test02.sql')
(3, 'test03.sql')
(4, 'test04.sql')

1.5 Pandas로 읽기¶

In [16]:

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

Out[16]:

	0	1
0	1	test01.sql
1	2	test02.sql
2	3	test03.sql
3	4	test04.sql

3. Python with CSV¶

3.1 csv 에 있는 데이터를 Python 으로 INSERT¶

In [18]:

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

Out[18]:

	서울특별시경찰청	서울시 종로구 사직로8길 31
0	서울중부경찰서	서울특별시 중구 수표로 27
1	서울종로경찰서	서울특별시 종로구 율곡로 46
2	서울남대문경찰서	서울특별시 중구 한강대로 410
3	서울서대문경찰서	서울특별시 서대문구 통일로 113
4	서울혜화경찰서	서울특별시 종로구 창경궁로 112-16

In [32]:

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

3.2 데이터 입력¶

• 쿼리를 다음과 같이 입력 받음
  • "INSERT INTO police_station VALUES (%s, %s)"
• df.iterrows(): 한 줄 씩 INSERT

In [ ]:

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

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

3.3 결과 확인¶

In [ ]:

cursor.execute('SELECT * FROM police_station')
result = cursor.fetchall()
for row in result:
    print(row)

In [37]:

conn.close()

In [36]:

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

Out[36]:

	0	1
0	서울중부경찰서	서울특별시 중구 수표로 27
1	서울종로경찰서	서울특별시 종로구 율곡로 46
2	서울남대문경찰서	서울특별시 중구 한강대로 410
3	서울서대문경찰서	서울특별시 서대문구 통일로 113
4	서울혜화경찰서	서울특별시 종로구 창경궁로 112-16

4. 범죄 현황 데이터 활용 예제¶

In [48]:

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

Out[48]:

	구분	죄종	발생검거	건수
0	중부	살인	발생	1
1	중부	살인	검거	1
2	중부	강도	발생	3
3	중부	강도	검거	4
4	중부	강간,추행	발생	113

4.1 DB 연결¶

In [56]:

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

In [57]:

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

4.2 crime_status 테이블에 데이터를 INSERT¶

• 자료형에 상관없이 모두 %s로 들어감

In [ ]:

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

4.3 crime_status 테이블의 데이터 조회¶

In [ ]:

cursor.execute('SELECT * FROM crime_status')

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

In [61]:

conn.close()

4.4 조회한 결과를 Pandas 로 변환해서 확인¶

In [60]:

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

Out[60]:

	0	1	2	3	4
0	2020	중부	살인	발생	1
1	2020	중부	살인	검거	1
2	2020	중부	강도	발생	3
3	2020	중부	강도	검거	4
4	2020	중부	강간,추행	발생	113

5. 서울 CCTV 데이터 활용 예제¶

5.1 AWS RDS (database-1) amrbase 접속¶

In [62]:

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

5.2 cctv Table 생성¶

In [71]:

cursor = conn.cursor()
cursor.execute('CREATE TABLE cctv (name varchar(16), sum int, before_2013 int, y_2014 int, y_2015 int, y_2016 int)')

5.3 cctv 데이터를 pandas 로 읽어오기 (Seoul_CCTV.csv)¶

In [64]:

df = pd.read_csv('./data/Seoul_CCTV.csv')
df.head()

Out[64]:

	기관명	소계	2013년도 이전	2014년	2015년	2016년
0	강남구	3238	1292	430	584	932
1	강동구	1010	379	99	155	377
2	강북구	831	369	120	138	204
3	강서구	911	388	258	184	81
4	관악구	2109	846	260	390	613

5.4 데이터를 cctv 테이블에 INSERT¶

In [ ]:

sql = 'INSERT INTO cctv VALUES (%s, %s, %s, %s, %s, %s)'
for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

In [ ]:

cursor.execute('SELECT * FROM cctv')
result = cursor.fetchall()
for row in result:
    print(row)

5.5 조회한 결과를 Pandas로 변환하여 출력¶

In [84]:

df = pd.DataFrame(result, columns=['Location', 'Sum', 'Befor 2013', '2014', '2015', '2016'])
df.head()

Out[84]:

	Location	Sum	Befor 2013	2014	2015	2016
0	강남구	3238	1292	430	584	932
1	강동구	1010	379	99	155	377
2	강북구	831	369	120	138	204
3	강서구	911	388	258	184	81
4	관악구	2109	846	260	390	613