SQL - mysql.connector (python) : 데이터 취업 스쿨 스터디 노트 12/25

1. Python에서 MySQL 사용하기

1-1. mysql-connector-python 설치

# mysql driver 설치
pip install mysql-connector-python

# 설치 확인
import mysql.connector

1-2. mysql 접속

# local에 연결
local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "password"
)

# AWS RDS에 연결
remote = mysql.connector.connect(
    host = "host",
    port = 3306,
    user = "admin",
    password = "password"
)

# 특정 database에 접속
remote = mysql.connector.connect(
    host = "host",
    port = 3306,
    user = "admin",
    password = "password",
    database = "zerobase"
)

# 사용이 끝나면 반드시 연결 종료
local.close()

2. execute SQL

MySQLCursor.execute() Method
connect를 통해서 생성한 cursor를 이용해서 query를 실행할 수 있다.

cur = remote.cursor()

# 테이블 생성
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

# 테이블 삭제
cur.execute("DROP TABLE sql_file")

# test03.sql
CREATE TABLE sql_file
(
    id int,
    filename varchar(16)
);

# sql 파일 실행
sql = open("test03.sql").read()
cur.execute(sql)

# test04.sql
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");

# sql 파일 내에 query가 여러 개 존재하는 경우
# multi=True: 여러 번 실행
sql = open("test04.sql").read()
cur.execute(sql, multi=True)

# 데이터베이스에 적용
remote.commit()

# 값 확인하기
for result_iterator in cur.execute(sql, multi=True):
    if result_iterator.with_rows:
        print(result_iterator.fetchall())
    else:
        print(result_iterator.statement)

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

fetch all : sql_file 테이블 조회

# buffered=True: 읽어올 데이터의 양이 많을 때
cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")

# 실행 결과값을 보고 싶을 때
result = cur.fetchall()

result

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

for result_iterator in result:
    print(result_iterator)

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

# 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)

3. CSV 파일의 데이터 INSERT

3-1. police_station.csv

cur = remote.cursor(buffered=True)

df = pd.read_csv("police_station.csv")

# 데이터 입력
sql = "INSERT INTO police_station VALUES (%s, %s)"

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

# 결과 확인
cur.execute("SELECT * FROM police_station")

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

# 검색 결과를 pandas로 읽기
df_result = pd.DataFrame(result)

3-2. 2020_crime.csv

# CSV 파일 한글이 깨질 때 : encoding="euc-kr"
df = pd.read_csv("2020_crime.csv", encoding="euc-kr")

# 데이터 입력
sql = """INSERT INTO crime_status VALUES ("2020", %s, %s, %s, %s)"""

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

# 결과 확인
cur.execute("SELECT * FROM crime_status")

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

# 검색 결과를 pandas로 읽기
df_result = pd.DataFrame(result)

3-3. Seoul_CCTV.csv

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

# 테이블 생성
sql = "CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cur.execute(sql)

# 데이터 입력
sql = "INSERT INTO cctv VALUES (%s, %s, %s, %s, %s, %s)"

for i, row in df.iterrows():
    cur.execute(sql, tuple(row))
    print(tuple(row))
    remote.commit()
    
# 결과 확인
cur.execute("SELECT * FROM cctv")

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

# 검색 결과를 pandas로 읽기
df_result = pd.DataFrame(result)