SQL - mini test _ 세계 테러 분석

원본 Data Download

• https://www.kaggle.com/datasets/START-UMD/gtd

문제 1. csv 파일에 저장된 세계 테러 데이터를 하나의 테이블에 저장하세요.

• globalterrorismmdb_0718.csv - https://www.kaggle.com/datasets/START-UMD/gtd
• pandas의 to_sql 함수 사용 (힌트 : create_engine)
• Table name : origin_terror_data

# !pip install sqlalchemy
# !pip install pymysql
# !pip install SQLAlchemy Flask-SQLAlchemy

# 실수로 oneday DB를 삭제해서...다시 생성(1)
import mysql.connector

conn = mysql.connector.connect(
    host = "내꺼",
    port = 3306,
    user = "admin",
    password = "내꺼",
    database = "zerobase"
)

cursor = conn.cursor(buffered = True)
sql = 'create database oneday default character set utf8mb4'
cursor.execute(sql)

cursor.execute("create user 'oneday'@'%' identified by '1234'")
cursor.execute("grant all on oneday.* to 'oneday'@'%'")

conn = mysql.connector.connect(
    host = "내꺼",
    port = 3306,
    user = "oneday",
    password = "1234",
    database = "oneday"
)

import time
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import configparser

# 데이터 불러오기
df = pd.read_csv('./globalterrorismdb_0718dist.csv', encoding='ISO-8859-1')

# DB 접속 엔진 객체 생성
user = 'oneday'
password = '1234'
host = '내꺼'
port = 3306
database = 'oneday'

# Engine 객체 설정 (URL 문자열을 사용하여 데이터베이스 호스트 연결)
engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4')

# DB 테이블 명(생성될 테이블 이름)
table_name = "origin_terror_data"

# DB에 DataFrame 적재
df.to_sql(index = False, 
          name = table_name,
          con = engine,
          if_exists = 'append',
          method = 'multi', 
          chunksize = 10000)

# (에러) 'Engine' object has no attribute 'execute'
# records = engine.execute("SELECT COUNT(*) FROM origin_terror_data").fetchall()
# print(records)

# 방법을 바꿈 (참고 : https://blog.naver.com/lechga/223290561410)
from sqlalchemy import text

conn = engine.connect()

with engine.connect() as conn:
    records = conn.execute(text("SELECT COUNT(*) FROM origin_terror_data")).fetchall()
    print(records)

문제 2. origin_terror_data 에서 region, country 관련 데이터는 code 와 txt (name) 속성으로 정의되어 있습니다.
문제 2-1. Region 및 Country 테이블을 그림과 같은 구조로 생성하세요.

• origin_terror_data 를 분석하여 각 테이블의 데이터 타입을 정의하세요.
• 문자열 데이터의 사이즈는 origin_terror_data 테이블에서 해당 데이터의 max length 를 쿼리로 체크하여 정의하세요.
• Region 과 Country 데이터 사이의 관계를 파악하여 Foreign Key 를 설정하세요.
  참고>
• Region.region_code = origin_terror_data.region
• Region.region_name = origin_terror_data.region_txt
• Country.country_code = origin_terror_data.country
• Country.country_name = origin_terror_data.country_txt

##############################################################################################
#  문제 2. region / country / city 데이터 추출하여 데이터베이스로 변환하기 
#  중복을 제거한 code - name 값 
#  region - country - city 관계 정의 
##############################################################################################
import mysql.connector

conn = mysql.connector.connect(
    host = 'database-1.cj22sogoe8oa.ap-southeast-2.rds.amazonaws.com',
    port = 3306,
    user = "oneday",
    password = "1234",
    database = "oneday"
)
cursor = conn.cursor(buffered=True)

# region_txt 의 max length 체크 
region_maxL = 'select max(char_length(region_txt)) from origin_terror_data'

cursor.execute(region_maxL)
result = cursor.fetchall()
result

# Region 테이블 만들기
region_table = ("create table Region(region_code int not null auto_increment primary key, region_name varchar(32))")
cursor.execute(region_table)
conn.commit()

sql = ('desc Region')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

# country_txt 의 max length 체크 

country_maxL = "select max(char_length(country_txt)) from origin_terror_data"

cursor.execute(country_maxL)
result = cursor.fetchall()
result

# Country 테이블 만들기 
country_table = ("create table Country ("
        "country_code int not null auto_increment primary key, "
        "region_code int, "
        "country_name varchar(32), "
        "foreign key (region_code) references Region(region_code)"
        ")"
        )
cursor.execute(country_table)
conn.commit()

# desc country
sql = ('desc Country')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

문제 2-2. origin_terror_data 테이블에서 Region 및 Country 데이터를 추출하여 문제 2-1.에서 생성한 테이블에 입력하고 확인하세요.

• 중복을 제거한 Unique Data 를 추출하세요.
• 데이터를 INSERT 할때 순서를 고민하세요.

# Region 데이터 추출하기 

region_data = ('SELECT DISTINCT region, region_txt FROM origin_terror_data ORDER BY region ASC')
cursor.execute(region_data)

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

# Region 데이터 추출하기 

region_data = ('SELECT DISTINCT region, region_txt FROM origin_terror_data ORDER BY region ASC')
cursor.execute(region_data)

result = cursor.fetchall()
print(result)

# Region 테이블에 INSERT 
region_insert = ('INSERT INTO Region(region_code, region_name) SELECT DISTINCT region, region_txt FROM origin_terror_data')

cursor.execute(region_insert)
conn.commit()

# Country 데이터 추출하기 
country_data = ('SELECT DISTINCT country, country_txt FROM origin_terror_data')
cursor.execute(country_data)

result = cursor.fetchall()
print(result)

# Country 테이블에 INSERT 
country_insert = ("INSERT INTO Country (country_code, region_code, country_name)"
        "SELECT DISTINCT country, region, country_txt FROM origin_terror_data;")

cursor.execute(country_insert)
conn.commit()

문제 3. origin_terror_data 에서 attack type, target type, weapon type 관련 데이터는 code 와 txt 속성으로 정의되어 있습니다.
문제 3-1. AttackType, TargetType, WeaponType 테이블을 그림과 같은 구조로 생성하세요.

• origin_terror_data 를 분석하여 각 테이블의 데이터 타입을 정의하세요.
• 문자열 데이터의 사이즈는 origin_terror_data 테이블에서 해당 데이터의 max length 를 쿼리로 체크하여 정의하세요.
  참고>
• AttackType.attacktype_code = origin_terror_data.attacktype1
• AttackType.attacktype_desc = origin_terror_data.attacktype1_txt
• TargetType.targtype_code = origin_terror_data.targtype1
• TargetType.targtype_desc = origin_terror_data.targtype1_txt
• WeaponType.weaptype_code = origin_terror_data.weaptype1
• WeaponType.weaptype_desc = origin_terror_data.weaptype1_txt

# attacktype1 의 max length 체크 

attacktype1 = "SELECT max(char_length(attacktype1_txt)) FROM origin_terror_data"

cursor.execute(attacktype1)
result = cursor.fetchall()
result

# AttackType 테이블 만들기 
sql = ("CREATE table AttackType ("
        "attacktype_code int not null auto_increment primary key, "
        "attacktype_desc varchar(35) "
        ")"
        )

cursor.execute(sql)
conn.commit()

# targettype1 의 max length 체크 
targettype1_l = 'SELECT max(char_length(targtype1_txt)) FROM origin_terror_data;'

cursor.execute(targettype1_l)
result = cursor.fetchall()
result

# TargetType 테이블 만들기 
TargetType = ("CREATE table TargetType ("
        "targtype_code int not null auto_increment primary key, "
        "targtype_desc varchar(32) "
        ")"
        )

cursor.execute(TargetType)
conn.commit()

# weaptype1 의 max length 체크 
weaptype1_l = 'SELECT max(char_length(weaptype1_txt)) FROM origin_terror_data'

cursor.execute(weaptype1_l)
result = cursor.fetchall()
result

# WeaponType 테이블 만들기 
WeaponType = ("create table WeaponType ("
        "weaptype_code int not null auto_increment primary key, "
        "weaptype_desc varchar(80) "
        ")"
        )

cursor.execute(WeaponType)
conn.commit()

문제 3-2. origin_terror_data 테이블에서 Attack Type, Target Type, Weapon Type 데이터를 추출하여 문제 3-1.에서 생성한 테이블에 입력하고 확인하세요.

• 중복을 제거한 Unique Data 를 추출하세요.
• 데이터를 INSERT 할때 순서를 고민하세요.

# attacktype1 데이터 추출하기 
attacktype1_data = ('SELECT DISTINCT attacktype1, attacktype1_txt FROM origin_terror_data ORDER BY attacktype1 ASC')
cursor.execute(attacktype1_data)

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

# AttackType 테이블에 INSERT 
AttackType_data = ("INSERT INTO AttackType (attacktype_code, attacktype_desc)"
        "SELECT DISTINCT attacktype1, attacktype1_txt FROM origin_terror_data")

cursor.execute(AttackType_data)
conn.commit()

# desc AttackType
sql = ('desc AttackType')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

# targtype1 데이터 추출하기 
targtype1_data = ('SELECT DISTINCT targtype1, targtype1_txt FROM origin_terror_data ORDER BY targtype1 ASC ')
cursor.execute(targtype1_data)

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

# TargetType 테이블에 INSERT 

# ALTER TABLE tablename
# CHANGE COLUMN old_columnname new_columnname new_datatype;

targetType_insert = ('INSERT INTO TargetType (targtype_code, targtype_desc) SELECT DISTINCT targtype1, targtype1_txt FROM origin_terror_data')

cursor.execute(targetType_insert)
conn.commit()

# desc TargetType
sql = ('desc TargetType')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

# weaptype1 데이터 추출하기 
weaptype1_data = ('SELECT DISTINCT weaptype1, weaptype1_txt FROM origin_terror_data ORDER BY weaptype1 ASC')
cursor.execute(weaptype1_data)

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

# WeaponType 테이블에 INSERT 
sql = ('INSERT INTO WeaponType (weaptype_code, weaptype_desc) SELECT DISTINCT weaptype1, weaptype1_txt FROM origin_terror_data')

cursor.execute(sql)
conn.commit()

# desc WeaponType
sql = ('desc WeaponType')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

문제 4. TerrorData 테이블을 만들고 앞서 만들어둔 테이블과 관계를 설정하도록 하겠습니다.
문제 4-1. TerrorData 테이블을 앞의 그림과 같이 생성하세요.

• origin_terror_data 를 분석하여 데이터 타입을 정의하세요.
• 문자열 데이터의 사이즈는 origin_terror_data 테이블에서 해당 데이터의 max length 를 쿼리로 체크하여 정의하세요.
  참고>
• TerrorData.terror id : Auto Increment
• TerrorData.city_name = origin_terror_data.city
• TerrorData.target = orgin_terror_data.target1
• TerrorData.group_name = origin_terror_data.gname
• TerrorData.kill_count = origin_terror_data.nkill
• TerrorData.wound_count = origin_terror_data.nwound
• TerrorData.motive = origin_terror_data.motive
• TerrorData.summary = origin_terror_data.summary
• TerrorData.latitude = origin_terror_data_latitude
• TerrorData.longitude = origin_terror_data_longitude
• TerrorData.terror_date = origin_terror_data.iyear + origin_terror_Data.imonth + origin_terror_data.iday (Date Type)

# max length 체크 

# city 의 max length 체크 
city_ML = 'SELECT max(char_length(city)) FROM origin_terror_data'
cursor.execute(city_ML)
city = cursor.fetchone()

# target1 의 max length 체크 
target1_ML = 'SELECT max(char_length(target1)) FROM origin_terror_data'
cursor.execute(target1_ML)
target = cursor.fetchone()

# gname 의 max length 체크 
gname_ML = 'SELECT max(char_length(gname)) FROM origin_terror_data'
cursor.execute(gname_ML)
gname = cursor.fetchone()

# summary 의 max length 체크 
summary_ML = 'SELECT max(char_length(summary)) FROM origin_terror_data'
cursor.execute(summary_ML)
summary = cursor.fetchone()

# motive 의 max length 체크 
motive_ML = 'SELECT max(char_length(motive)) FROM origin_terror_data'
cursor.execute(motive_ML)
motive = cursor.fetchone()

print(f'city: {city}')
print(f'target1: {target}')
print(f'gname: {gname}')
print(f'summary: {summary}')
print(f'motive: {motive}')

# TerrorData 테이블 만들기 
terrorData_table = ("create table TerrorData ("
        "terror_id int not null auto_increment primary key, "
        "terror_date date, "
        "region_code int, "
        "country_code int, "
        "city varchar(65), "
        "target varchar(350), "
        "group_name varchar(120), "
        "targtype_code int, "
        "attacktype_code int, "  
        "weaptype_code int, "
        "kill_count int, "
        "wound_count int, "     
        "motive varchar(900), "
        "summary varchar(2450), "
        "latitude decimal(16, 14), "
        "longitude decimal(17, 14), "  
        "foreign key (region_code) references Region(region_code), "
        "foreign key (country_code) references Country(country_code), "
        "foreign key (attacktype_code) references AttackType(attacktype_code), "
        "foreign key (targtype_code) references TargetType(targtype_code), "
        "foreign key (weaptype_code) references WeaponType(weaptype_code) "
        ");"
        )

cursor.execute(terrorData_table)
conn.commit()

문제 4-2. origin_terror_data 테이블에서 Terror Data를 추출하여 문제 4-1.에서 생성한 테이블에 입력하고 확인하세요.

• 앞서 생성한 Region, Country, AttackType, TargetType, WeaponType 데이터와의 관계에 주의하세요.
• Count 값을 가지는 칼럼의 값이 null 인 경우, 0으로 예외처리 해주세요.
• 위도 경도 데이터 중 범위를 넘어서는 데이터가 존재합니다. 이 경우, null 값으로 예외처리 해주세요. (위도 경도 범위 : 구글링해보세요.)
• terror_date 칼럼의 경우, origin_terror_data 의 연, 월, 일 정보를 조합하여 date type 으로 정의해주세요. (Format : ‘YYYY-mm-dd’)
• origin_terror_data 의 월, 일 정보중 값이 0 인 경우 date type 으로 변환되지 않습니다. 이 경우, 1 로 예외처리 해주세요.
• 데이터 입력까지 완료한 이후, origin_terror_data 테이블을 삭제하고 확인하세요.

# data 전처리 
# date type : year + month + day (month = 0 인경우 1, day = 0 인경우 1)
# nkill, nwound : null 인 경우 0
# longitude range : 180 ~ -180
# check : select longitude from origin_terror_data where longitude < -180 or longitude > 180; '-86185896'

sql = ("INSERT INTO TerrorData (region_code, country_code, attacktype_code, targtype_code, weaptype_code, terror_date, city, target, group_name, kill_count, wound_count, motive, summary, latitude, longitude) "
        "SELECT region, country, attacktype1, targtype1, weaptype1, city, target1, gname, "
        "IF(nkill IS NULL, 0, nkill), "   
        "IF(nwound IS NULL, 0, nwound), "
        "motive, summary, "
        "CASE WHEN latitude BETWEEN -90 and 90 THEN NULL ELSE latitude END, "
        "CASE WHEN longitude BETWEEN -180 and 180 THEN NULL ELSE longitude END, "
        "STR_TO_DATE(CONCAT(IF(iyear = 0, 1, iyear)'-'IF(imonth = 0, 1, imonth)'-'IF(iday = 0, 1, iday)), '%Y-%m-%d') FROM origin_terror_data")

cursor.execute(sql)
conn.commit()

# terror 데이터 추출하기 
# desc TerrorData
sql = ('desc TerrorData')
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

sql = 'SELECT COUNT(*) FROM TerrorData'
cursor.execute(sql)
result = cursor.fetchall()
print(result)

문제 5. TerrorData 의 전체 기간에서 테러의 숫자를 연도별로 집계하여 연도별 테러 발생 건수를 조회하세요.

답 틀림...🙄

sql = "SELECT DATE_FORMAT(terror_date, '%Y') as Y, count(*) as C FROM TerrorData GROUP BY Y"
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

문제 6. TerrorData 에서 테러가 가장 많이 일어난 순서로 국가를 정렬하여 상위 10위 국가를 조회하세요.

# 테러가 많이 일어난 상위 10위 Region
terrorRegion_10 = ("SELECT R.region_name, COUNT(*) FROM TerrorData as T "
         "JOIN Region as R ON T.region_code = R.region_code "
         "GROUP BY T.region_code ORDER BY COUNT(*) DESC limit 10"
         )

cursor.execute(terrorRegion_10)
result = cursor.fetchall()
for i in result:
    print(i)

# 테러가 많이 일어난 상위 10위 Country 
terrorCountry_10 = ("SELECT C.country_name, COUNT(*) FROM TerrorData as T "
         "JOIN Country as C ON T.country_code = C.country_code "
         "GROUP BY T.country_code ORDER BY COUNT(*) DESC limit 10"
         )

cursor.execute(terrorCountry_10)
result = cursor.fetchall()
for i in result:
    print(i)

문제 7. TerrorData 에서 테러가 가장 많이 일어난 상위 10위 국가에 대해 국가별로 사망자수와 부상자수, 사상자수(사망자수 + 부상자수)를 조회하세요.

country_10 = ("SELECT C.country_name, COUNT(*), " # 모든행의 갯수
        "sum(T.kill_count), "
        "sum(T.wound_count), "
        "sum(T.kill_count + T.wound_count) "
        "FROM TerrorData as T "
        "JOIN Country as C ON T.country_code=C.country_code "
        "GROUP BY T.country_code, C.country_code "
        "ORDER BY COUNT(*)" #  COUNT(*) 값을 기준으로 descending
        "DESC limit 10")

cursor.execute(country_10)
result = cursor.fetchall()
for i in result:
    print(i)

문제 8. 지역별 테러 공격 형태에 따른 사망자수, 부상자수, 사상자수를 조회하세요.

답 틀림..🙄

# 지역별 테러 공격 형태에 따른 사망자와 사상자 수 

region_c = ("SELECT R.region_name, AttackType.attacktype_desc, "
            "sum(T.kill_count), sum(T.wound_count),"
            "sum(T.kill_count + T.wound_count) "
            "FROM TerrorData as T "
            "JOIN Region as R "
            "ON T.region_code = R.region_code "
            "JOIN AttackType ON T.attacktype_code = AttackType.attacktype_code "
            "GROUP BY R.region_name, AttackType.attacktype_desc")

cursor.execute(region_c)
result = cursor.fetchall()
for i in result:
    print(i)

제로베이스 데이티 스쿨