[SQL] Python with MySQL

허재훈·2023년 4월 27일

sql

SQL

목록 보기

12/17

1. 실습환경 만들기

Jupyter Notebook 실행
- VSCode 혹은 Web Browser 를 통해 실행하고, ds_study 환경에서 작업합니다.

실행위치
- Jupyter notebook 과 mysql 은 모두 sql_ws 폴더에서 시작합니다.

MySQL
- AWS RDS 로 생성한 database-1 을 모두 사용합니다.

데이터 삭제
- AWS RDS(database-1) zerobase 의 police_station 테이블의 데이터를 모두 지워줍니다.

(아래는 백업)

aws 접속
mysql -h "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p

show databases;

use zerobase;

show tables;

delete from police_station;

show tables;

python.ipynb 파일 생성
- VSCode 의 경우 새파일을 열고 해당 파일명을 입력합니다.
  오른쪽 상단에서 ds_study 를 선택한뒤 실습합니다.

2. Python with MySQL

Python 으로 MySQL 접속 후 사용하는 방법

Install MySQL Driver

Python 에서 MySQL 을 사용하기 위해서는 먼저 MySQL Driver 를 설치한다.

# cmd 에서 conda activate ds_study 를 먼저 한다
pip install mysql-connector-python

설치 확인

import mysql.connector

Create Connection

MySQL 에 접속하기 위한 코드

mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>"
)

Create Connection 예제

Create Connection 예제 1
- Local Database 연결

# Local Database 의 zerobase 연결
local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "******" # mysql root 패스워드
)

Create Connection 예제 2
- AWS RDS (database-1) 연결

# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "엔드포인트 코드",
    port = 3306,
    user = "admin",
    password = "*************" # AWS RDS 패스워드
)

Close Database

import mysql.connector

local = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

local.close()
# mydb.close() 는 오타이다.

local.close() # 로컬 연결 종료

remote.close() # 원격 연결 종료

Connect to Database

특정 Database 에 접속하기 위한 코드

import mysql.connector

mydb = mysql.connector.connect(
    host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

Create Connection 예제

Create Connection 예제 1 : Local MySQL 의 zerobase 연결

# Local Database 의 zerobase 연결
local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "*******", # mysql root 패스워드
    database = 'zerobase'
)

Create Connection 예제 2 : AWS RDS (database-1) 의 zerobase 에 연결

# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "****", # AWS RDS 패스워드
    database = "zerobase"
)

Execute SQL

Query 를 실행하기 위한 코드

import mysql.connector

mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.cursor() # cursor() 생성
mycursor.execute(<query>); # cursor() 를 실행함으로써 쿼리 실행

Execute SQL 예제

Execute SQL 예제 - 1. 테이블 생성

# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "******", # AWS RDS 패스워드
    database = "zerobase"
)

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

# 닫기
remote.close()

Execute SQL 예제 - 2. 결과 확인(터미널에서 mysql 접속 먼저)

Execute SQL 예제 - 3. 테이블 삭제

# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*******", # AWS RDS 패스워드
    database = "zerobase"
)
# 테이블 삭제
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")
# 닫기
remote.close()

Execute SQL 예제 - 4. 결과 확인(터미널에서)

Execute SQL File 1

SQL File 을 실행하기 위한 코드

mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql);

Execute SQL File 1 예제

test03.sql 생성

CREATE TABLE sql_file
(
    id int,
    filename varchar(16)
);

test03.sql 실행(vscode > python.ipynb 에서)

# test03.sql 실행
# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*******", # AWS RDS 패스워드
    database = "zerobase"
)
# test03.sql 실행
cur = remote.cursor()
sql = open("test03.sql").read() # 다른 경로에 있다면 경로를 지정해줘야 함
cur.execute(sql)
# 닫기
remote.close()

결과 확인

Execute SQL File 2

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

mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql, multi=True);

Execute SQL File 2 예제

test04.sql 생성

실행 (에러)

Multi = True

# SQL File 내에 Query 가 여러개 존재하는 경우
# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*********", # AWS RDS 패스워드
    database = "zerobase"
)
# test03.sql 실행
cur = remote.cursor()
sql = open("test04.sql").read() # 다른 경로에 있다면 경로를 지정해줘야 함
# cur.execute(sql, multi=True) # 이렇게만 해줘도 됨

# 아래는 값을 보기위해서 for문을 넣음, 위처럼 한줄로 해도 됨
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()

결과 확인

Fetch All

쿼리를 실행한 다음에 그 결과값이 row를 포함하고 있으면 Fetch All
실행하는 쿼리가 아니라 조회하는 select 문을 실행한 경우에는 데이터를 가지고 오는데, 데이터가 있는 경우에는 그 데이터를 Fetch All을 써서 변수에 담을 수 있다
변수를 담은 데이터를 바로 출력하면 한번에 출력되고, for 문을 쓰면 row마다 출력된다

mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
    print(datd)

Fetch All 예제

sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

# sql_file 테이블 조회
# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*********", # AWS RDS 패스워드
    database = "zerobase"
)

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 문 추가

# sql_file 테이블 조회
# AWS RDS (database-1) 연결
remote =mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********", # AWS RDS 패스워드
    database = "zerobase"
)

cur = remote.cursor(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')

참고, 검색결과를 Pandas 로 읽기

3. Python with CSV

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

Read CSV

제공받은 police_station.csv 를 Pandas 로 읽어와서 데이터를 확인합니다.

Zerobase 에 연결

Cursor 만들기

읽어올 양이 많은 경우 cursor 생성 시 buffer 설정을 해준다.

INSERT 문 만들기

위에서 판다스로 가져온 데이터프레임을 보면 컬럼이 2개임
그래서 values(%s, %s) 해준 것

데이터 입력

commit() 은 database 에 적용하기 위한 명령
commit() 하는 순간 database에 적용됨.
그래서 for 문 밖에 commit() 적었는데, for 문 중간에 에러가 나면 모두 database에 저장되지 않지만, for문 안에 commit()을 적으면 에러나기 전까지는 모두 database에 저장됨

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

튜플로 감싸줌

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

결과 확인

검색결과를 Pandas 로 읽기

Tip

csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정 (특히 우리나라 사이트에서 제공받은 csv 파일들.)

4. Python with CSV 예제

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

AWS RDS(database-1) zerobase 에 접속

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

INSERT 쿼리 작성

2020 이란 숫자는 파일 내용에 없기 때문에 고정으로 넣어줌
결과값을 읽어올 것이기 때문에 buffered = True
2020에 쌍따옴표를 썼기 때문에 양쪽에 쌍따옴표 3개씩 넣음

데이터를 crime_status 테이블에 INSERT

crime_status 테이블의 데이터 조회

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

혼자 해봅시다

문제 1. AWS RDS(database-1) zerobase 에 접속합니다.

# 문제 1. AWS RDS(database-1) zerobase 에 접속합니다.

import mysql.connector

conn = mysql.connector.connect(
    host = "database-1.c46uo9qzntem.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "zero",
    password = "zerobase", # AWS RDS 패스워드
    database = "zerobase"
)

문제 2. cctv Table 을 생성합니다. (문제 3.까지)

# 문제 2. cctv Table 을 생성합니다.

# 아래가 문제 3. 풀이
# 문제 3. cctv SQL 파일에서 데이터를 Pandas 로 읽어옵니다.
import pandas as pd

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

# 아래가 문제 2. 풀이
# 쿼리 작성

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 합니다.

# 문제 4. 데이터를 cctv 테이블에 INSERT 합니다.

#  쿼리 작성
sql = """insert into cctv values (%s, %s, %s, %s, %s, %s)"""
cursor = conn.cursor(buffered=True)

# 데이터를 cctv 테이블에 INSERT

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

>>
('강남구', 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)

문제 5. cctv 테이블의 데이터 조회하여 확인합니다.

# 문제 5. cctv 테이블의 데이터 조회하여 확인합니다.

cursor.execute("select * from cctv")

result = cursor.fetchall()
for row in result:
    print(row)
    
>>
('강남구', 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)

문제 6. 조회된 데이터를 Pandas 로 변환하여 출력합니다.

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

업로드중..

위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.

허재훈

허재

이전 포스트

[SQL] SQL File

다음 포스트

[SQL] Python with MySQL

SQL

1. 실습환경 만들기

2. Python with MySQL

Install MySQL Driver

Create Connection

Create Connection 예제

Close Database

Connect to Database

Create Connection 예제

Execute SQL

Execute SQL 예제

Execute SQL File 1

Execute SQL File 1 예제

Execute SQL File 2

Execute SQL File 2 예제

Fetch All

Fetch All 예제

3. Python with CSV

Read CSV

Zerobase 에 연결

Cursor 만들기

INSERT 문 만들기

데이터 입력

Tip

4. Python with CSV 예제

AWS RDS(database-1) zerobase 에 접속

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

INSERT 쿼리 작성

데이터를 crime_status 테이블에 INSERT

crime_status 테이블의 데이터 조회

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

혼자 해봅시다

문제 1. AWS RDS(database-1) zerobase 에 접속합니다.

[SQL] SQL File

[SQL] PRIMARY KEY, FOREIGN KEY

0개의 댓글