😢 스터디노트(SQL 13)

실습환경 만들기

• VSCode 혹은 Web Browser 를 통해 실행하고, ds_study 환경에서 작업
• Jupyter notebook 과 mysql 은 모두 sql_ws 폴더에서 시작
• aws 생성해둔 database 엔드 포인트 이용
  
  
• sql_ws 폴더 하위에서 실행(현재 폴더를 VSCode로 열라는 뜻)
  cmd

cd Documents/ds_sql_ws

code .

• plice_station 백업 파일 생성
  vscode

mysqldump --set-gtid-purged=OFF -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 admin -p zerobase police_station

• aws database 접속

mysql -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 -u admin 
-p

• zerobase의 police_station 테이블의 데이터 삭제

show databases;

use zerobase;

show tables;

delete from police_station;

select * from police_station;

• python.ipython 생성 후 ds_study 커널 선택

---

Python with MySQL

• Python 으로 MySQL 접속 후 사용하는 방법
  - Python 에서 MySQL 을 사용하기 위해서는 먼저 MySQL Driver 를 설치
  - 설치 확인

python.ipython

!pip install mysql-connector-python

import mysql.connector

• MySQL 에 접속하기 위한 코드

import mysql.connector

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

• 예제1 (create connection) Local Database 연결

# local database 연결
local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "설정한 패쓰워드"
)

• 예제2 (create connection) AWS RDS (database-1) 연결

# AWS RDS(database-1) database 연결
remote = mysql.connector.connect(
    host = "database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "설정한 패쓰워드"
)

---

• close database

local.close()

• 예제1 (create connection) Local Database 닫기

# local database close
local.close()

• 예제2 (create connection) AWS RDS (database-1) Database 닫기

# AWS RDS(database-1) database close
remote.close()

---

• 특정 database에 접속하기 위한 코드

import mysql.connector

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

• 예제1 (create connection) Local MySQL 의 zerobase 연결

local = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = "<password>",
    database = 'zerobase'
)
local.close()

• 예제2 (create connection) AWS RDS (database-1) 의 zerobase 연결

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)
remote.close()

---

• Query를 실행하기 위한 코드

import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute(<query>);

- 예제1 (excute sql) 테이블 생성

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)

cur = remote.cursor()
cur.execute('create table sql_file (id int, filename varchar(16));')

remote.close()

mysql -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 -u admin 
-p

use zerobase; 

show tables;

desc sql_file;

• 예제2 (excute sql) 테이블 삭제

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)

cur = remote.cursor()
cur.execute('drop table sql_file')

remote.close()

---

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

• 예제1 (excute sql file) test03.sql 생성 및 실행

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)

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

remote.close()

show tables;

desc sql_file;

---

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

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

mycursor = mydb.cursor()

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

• 예제1 (excute sql file) test04.sql 생성
• 예제1 (excute sql file) test04.sql 실행

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)

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

select * from sql_file;

---

• Fetch All : 조회할 수 있는 데이터가 있는 경우 데이터를 변수에 담기 가능, for문을 사용하면 하나씩 나오고, 그렇지 않은 경우 한번에 출력
  - 읽어올 데이터 양이 많은 경우 buffered=True

mycursor.execute(<query>)
result = mycursor.fetchall()
for data in result:
	print(data

• 예제1 (fetch all) sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)
• 검색결과를 Pandas 로 읽기

import mysql.connector

remote = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = "<password>",
    database = 'zerobase'
)

cur = remote.cursor(buffered=True)
cur.execute('select * from sql_file')

result = cur.fetchall()
result

for result_iterator in result:
    print(result_iterator)

remote.close()

import pandas as pd

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

---

Python with CSV

• csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정 (특히 우리나라 사이트에서 제공받은 csv 파일들.
• commit() : database 에 적용하기 위한 명령
  
  
  
• CSV 에 있는 데이터를 Python 으로 INSERT

import pandas as pd

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

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

import mysql.connector

conn = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'zero',
    password = "<password>", # db명과 동일
    database = 'zerobase'
)

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

cursor = conn.cursor(buffered=True)

• insert문 만들기

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()
result

for row in result:
    print(row)

• 검색결과를 Pandas 로 읽기

df = pd.DataFrame(result)
df

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

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

conn = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'zero',
    password = "<password>", # db명과 동일
    database = 'zerobase'
)

# 2020_crime.csv(encoding = 'euc-kr) 데이터 읽어오기
import pandas as pd

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

# insert 쿼리 작성
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()

df.tail()

# crime_status 테이블의 데이터 조회
cursor.execute('select * from crime_status')
result = cursor.fetchall()

for row in result:
    print(row)

# 조회한 결과를 pandas로 변환해서 확인
df = pd.DataFrame(result)
df

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

import mysql.connector

conn = mysql.connector.connect(
    host = 'database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com',
    port = 3306,
    user = 'zero',
    password = 'zerobase',
    database = 'zerobase'
)

💡 문제 2. cctv Table 을 생성

sql = 'create table cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)'
cursor = conn.cursor(buffered=True)
cursor.execute(sql)

💡 문제 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 = conn.cursor(buffered=True)

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

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

cursor.execute('select * from cctv')
result = cursor.fetchall()

for row in result:
    print(row)

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

df = pd.DataFrame(result)
df

어렵다...생소하다..

💻 출처 : 제로베이스 데이터 취업 스쿨