SQL 심화_2

YJ·2023년 4월 20일
sql
0

▷ 오늘 학습 계획: SQL 강의(심화3~5)

📖 03_Python with MySQL

1) Python with MySQL connect

  • MySQL Driver 설치

pip install mysql-connector-python
import mysql.connector

2) Create Connection

  • MySQL 접속

Local Database

local = mysql.connector.connect(
	host = "localhost",
    user = "root",
    password = "********"
)
local.close()  # Close Database

AWS RDS (database-1)

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
remote.close()  # Close Database

3) Execute SQL

  • Query 실행

  • dictionary 형태로 저장하고 싶을 때
    dict_cursor = mydb.cursor(dictionary=True)
    dict_cursor.execute(sql)
    dict_cursor.fetchall()

테이블 생성

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
remote.close()

테이블 삭제

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")
remote.close()

4) Execute SQL File

  • SQL File 실행

예제(test03.sql 실행)

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)
remote.close()

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

예제(test04.sql 실행)

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    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()

5) Fetch All

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

remote = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "********",
    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()

6) Python with MySQL CSV

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

csv 한글이 깨지는 경우, encoding 값을 'euc-kr'로 설정
commit(): database에 적용하기 위한 명령

csv를 Pandas로 읽어와서 데이터 확인하기

import pandas as pd
df = pd.read_csv("police_station.csv")
df.head()

zerobase database에 연결

import mysql.connector
conn = mysql.connector.connect(
	host = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "********",
    database = "zerobase"
)

cursor 만들기

cursor = conn.cursor(buffered=True)

INSERT 문 만들기

sql = "INSERT INTO police_staion VALUES (%s, %s)"

데이터 입력

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()
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 = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "********",
    database = "zerobase"
)

Seoul_CCTV.csv 데이터를 Pandas 로 읽어오기

import pandas as pd
df = pd.read_csv("Seoul_CCTV.csv", encoding="utf-8")
df.head()

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)

데이터를 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))
    conn.commit()

cctv 테이블의 데이터 조회하여 확인

cursor.execute("select * from cctv")
result = cursor.fetchall()
for row in result:
    print(row)    
conn.close()

조회된 데이터를 Pandas 로 변환하여 출력

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

📖 04_Primary key, Foreign key

1) PRIMARY KEY(기본키)

테이블의 각 레코드를 식별
중복되지 않은 고유값을 포함
NULL 값을 포함할 수 없음
테이블 당 하나의 기본키를 가짐

PRIMARY KEY 생성

TABLE 생성된 이후 PRIMARY KEY 생성

ALTER TABLE tablename
ADD PRIMARY KEY(column1, column2);
ALTER TABLE tablename
ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column2);

PRIMARY KEY 삭제

ALTER TABLE tablename
DROP PRIMARY KEY;

2) FOREIGN KEY(외래키)

한 테이블을 다른 테이블과 연결해주는 역할
참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

FOREIGN KEY 생성

TABLE 생성된 이후 FOREIGN KEY 생성

ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

CONSTRAINT 생략 가능(생략하는 경우 CONSTRAINT가 자동으로 생성됨)
FOREIGN KEY는 여러개 존재할 수 있음

자동 생성된 CONSTRAINT 확인 방법

SHOW CREATE TABLE tablename;

FOREIGN KEY 삭제

ALTER TABLE tablename
DROP FOREIGN KEY constraint_name;

예제

police_station 과 crime_status 테이블 사이에 관계 (Foreign Key) 설정

crime_status.police_station 을 police_station.name 과 같게 만들기

select c.police_station, p.name
from crime_status c, police_station p
where p.name like concat ('서울', c.police_station, '경찰서')
group by c.police_station, p.name;

police_station.name 을 Primary Key 로 설정

ALTER TABLE police_station ADD PRIMARY KEY (name);

crime_status 테이블에 Foreign Key 로 사용할 Column 추가

ALTER TABLE crime_status
ADD COLUMN reference varchar(16);

Foreign Key 생성

ALTER TABLE crime_status
ADD FOREIGN KEY (reference) REFERENCES police_station(name);

Foreign Key 값 Update

UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name like CONCAT('서울', c.police_station, '경찰서');

Foreign Key 를 기준으로 두 테이블을 연관시켜 검색

SELECT c.police_station, p.address
FROM crime_status c, police_station p
WHERE c.reference = p.name
group by c.police_station;

▷ 내일 학습 계획: SQL 강의(심화6~8)

[이 글은 제로베이스 데이터 취업 스쿨의 강의 자료 일부를 발췌하여 작성되었습니다.]

profile
YJ
이전 포스트

SQL 심화_1

다음 포스트

SQL 심화_3

0개의 댓글