Ch13-14 Python with MySQL, Primary Key, Foreign Key (심화 3-5)

김민지·2023년 4월 21일
0

Part 05. SQL

목록 보기
6/7
  1. Python with MySQL
  • jupyter notebook이나 vscode로 실행, ds_study 환경에서 작업
  • jupyter notebook, mysql 모두 sql_ws 폴더에서 시작
  • AWS RDS로 생성한 database-1을 모두 사용
mysqldump --set-gtid-purged=OFF -h "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p zerobase police_station > backup_police.sql

mysql > use zerobase;
mysql > delete from police_station;
  • Python에서 MySQL을 사용하기 위해서는 먼저 MySQL Driver를 설치해야 함
pip install mysql-connector-python
import mysql.connector
  • MySQL에 접속하기 위한 코드
mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>"
)
# Local Database 연결

local = mysql.connector.connect(
	host = "localhost",
    user = "root",
    password = "*********"
)
# AWS RDS (database-1) 연결

remote = mysql.connector.connect(
	host = "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*********"
)
  • 사용후 연결은 꼭 종료해줘야 됨
local.close()
remote.close()
  • 특정 Database에 접속하기 위한 코드
import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>",
    database = "<databasename>"
)
  • Query를 실행하기 위한 코드
import mysql.connector

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

mycursor = mydb.cursor()
mycursor.execute(<query>);
  • 예제
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

remote.close()

-> 쿼리를 실행해서 테이블 생성

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)
  • 예제
cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()
  • SQL File 내에 쿼리가 여러 개 존재하는 경우
mycursor = mydb.cursor()

sql = oepn("<filename>.sql").read()
result = mycursor.execute(sql, multi=True)
  • 예제
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()

-> 쿼리를 직접 입력해서 실행시켜도 되고, 쿼리를 파일로 만들어서 파일을 실행시켜도 됨

  • Fetch All
mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
	print(data)
  • 읽어올 데이터 양이 많은 경우 -> buffered=True
  • 예제
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')
  • Fetch All 검색결과를 Pandas로 읽기
import pandas as pd

df = pd.DataFrame(result)
df.head()
  • Python with CSV
  • CSV에 있는 데이터를 Python으로 INSERT
for i, row in df.iterrows():
	cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

-> commit() : database에 적용하기 위한 명령

  • 데이터 insert 결과 확인
cursor.execute("SELECT * FROM police_station")

result = cursor.fetchall()
for row in result:
	print(row)
  • 검색결과를 Pandas로 읽기
df = pd.DataFrame(result)
df
  • 주의) csv 한글이 깨지는 경우, encoding 값을 'euc-kr'로 설정 (특히 한국 사이트에서 제공받은 csv 파일들)
import pandas as pd

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

<혼자서 해보기>

(1)

import mysql.connector
conn = mysql.connector.connect(
    host = "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "minji980603",
    database = "zerobase"
)

(2) 데이터 파악하고, 그에 맞게 cctv 테이블 만들기

import pandas as pd

df = pd.read_csv("Seoul_CCTV.csv", encoding='utf-8') 
df.head() 
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 = "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()

(4) cctv 테이블의 데이터 조회하기

cursor.execute("select * from cctv")

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

(5) 조회한 데이터를 pandas로 변환하여 출력하기

df = pd.DataFrame(result)
df.head()
  1. Primary Key (기본키)
  • 테이블의 각 레코드를 식별하기 위함

  • 중복되지 않은 고유값을 포함

  • NULL 값을 포함할 수 없음

  • 테이블 당 하나의 기본키를 가짐

  • Primary Key 생성 문법

CREATE TABLE tablename
(
  column1 datatype NOT NULL,
  column2 datatype NOT NULL,
  ...
  CONSTRAINT constraint_name   # constraint는 생략 가능
  PRIMARY KEY (column1, column2, ...)
);
  • Primary Key 삭제 문법
ALTER TABLE tablename
DROP PRIMARY KEY;
  • 이미 만들어진 테이블에서 Primary Key 생성
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...)
  1. Foreign Key (외래키)
  • 한 테이블을 다른 테이블과 연결해주는 역할
  • 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
CREATE TABLE tablename
(
  column1 datatype NOT NULL,
  column2 datatype NOT NULL,
  column3 datatype,
  column4 datatype,
  ...
  CONSTRAINT constraint_name
   PRIMARY KEY (column1, column2, ...),
  CONSTRAINT constraint_name
   FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
  • CREATE TABLE에서 FOREIGN KEY를 지정하는 경우 예제
CREATE TABLE orders
(
  oid int NOT NULL,
  order_no varchar(16),
  pid int,
  PRIMARY KEY (oid),
   CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);

-> order 테이블의 pid(외래키)는 person 테이블의 pid를 참조함

  • CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT 생략 가능
CREATE TABLE job
(
  jid int NOT NULL,
  name varchar(16),
  pid int,
  PRIMARY KEY (jid),
  FOREIGN KEY (pid) REFERENCES person(pid)
);
  • 자동 생성된 CONSTRAINT를 확인하는 방법
SHOW CREATE TABLE tablename;
  • Foreign Key 삭제 문법
ALTER TABLE tablename
DROP FOREIGN KEY constraint_name;
  • 이미 만들어진 테이블에서 Foreign Key 생성하기
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

<제로베이스 데이터 취업 스쿨>

0개의 댓글