-pandas로 변환
import pandas as pd
df = pd.DataFrame(result)
df.head
-csv파일읽기(pandas사용)
import pandas as pd // pandas를 pd로 import하기
df = pd.read_csv("police_station.csv") // pd.read_csv("파일이름.csv")
df.head()
import pandas as pd
df = pd.read_csv("2020_crime.csv",encoding = 'euc-kr') // 한글이 깨지는 경우 'euc-kr'사용
df.head()
-csv 데이터 적용
데이터베이스 서버에 커넥트
cursor = conn.cursor(buffered=True)
sql = "INSERT INTO police_station VALUES(%s,%s)"
for i, row in df.iterrows():
cursor.execute(sql,tuple(row))
print(tuple(row))
conn.commit() // commit 하는 순간에 데이터가 적용된다.
그래서 만약 4번째 for문에서 에러가 나도 3번째까지는 commit이 되있다.
※commit이 for문밖에있으면 for문 안에서 만약 6번쨰에 에러가 생기면 5번째 데이터까지 모두 적용이 안된다.
-조회한 결과를 pandas로 변환해서 확인
df = pd.DataFrame(result)
df.head()
실습에서
sql = """INSERT INTO crime_status VALUES ("2020",%s,%s,%s,%s)""" // 이건 앞에 2020을 임의로 다 추가한것
----------------SQL 13장 이론 08 까지 들었고 그 이후는 사전준비를 다 하고 들을 예정---------------------
-PRIMARY KEY
: 테이블에서 각 레코드를 식별하기 위한 key
- 중복되지 않아야 하고 고유값을 가져야하고 테이블당 하나의 값을 가지고 NULL값을 가질 수 없음.
ex) PRIMARY KEY 가 하나일때
create table 테이블이름 ( pid int NOT NULL , name varchar(16) , age int , PRIMARY KEY (pid) );
//pid값을 PRIMARY KEY로 지정하겠다.
ex) PRIMARY KEY 2개가 하나의 PRIMARY KEY 일때
create table 테이블이름 ( pid int NOT NULL , name varchar(16) , age int , PRIMARY KEY (pid,name) );
//pid값과 name이 각각 PRIMARY KEY인 것이 아니라 2개가 하나의 PRIMARY KEY 이다.
-프라이머리 키 삭제
ALTER TABLE table이름 DROP PRIMARY KEY;
-프라이머리 키 생성 ( 이미 테이블이 만들어져있을때 )
ALTER TABLE table이름 ADD PRIMARY KEY (~);
-FOREIGN KEY(외래키)
:테이블과 테이블을 연결해주는 역할 , FOREIGN KEY는 다른 연결된 테이블의 기본키가 된다.
create table table 이름 ( oid int not null ~ , CONSTRAINT 컨스트레인트 name FOREIGNKEY(oid) REFERENCES person(pid) );
※PRIMARY KEY는 테이블당 하나지만 , FOREIGN KEY키는 여러개 있을 수 있음
※PRIMARY KEY = PRI , FOREIGN KEY = MUL
※create table 에서 FOREIGN KEY를 지정할 경우에는 CONSTRAINT 생략 가능.
-> 자동생성된 이름을 모르기때문에 조회해서 이름을 알아내야함.
-자동 생성된 CONSTRAINT 확인
show create table table이름;
-삭제
ALTER TABLE 테이블이름 DROP FOREIGN KEY 키이름
-> desc해서 보면 기존과 같이 MUL이 있지만 constraint를 조회해서 보면 참조(레퍼런스)가 사라져있어서 foreign key가 사라졌다고 알수있음.
-생성 ( 이미 생성된 테이블에서 FOREIGN KEY 생성)
ALTER TABLE 테이블이름 ADD FOREIGN KEY (칼럼1) REFERENCES 참조할칼럼1;
ex) ALTER TABLE orders ADD FOREIGN (pid) REFERENCES person(pid);
★police_station 과 crime_status.police_station 을 매칭하여 관계를 맺기하기
1. select count(distinct name) from police_station;
: select 한다 distinct name을 이것은 중복을 name중에서 중복이 있으면 중복을 제거한 이름을 의미한다.
중복된 이름을 select 할것이다 police_station 에서
2. select count(distinct police_station) from crime_status;
: select 한다 distinct police_station을 crime_status 로부터
이것은 crims_status 테이블에서 police_station 칼럼의 중복을 제거한 종류를 출력해준다.
1,2단계에서 둘다 31개로 나오지만 name에서는 '서울중부경찰서' 라고 나오지만 police_station에서는 '중부' 라고 나온다
-> 그래서 '중부'를 '서울중부경찰서'로 만들것이기 때문에 concat을 사용해서 '서울'+'중부'+'경찰서'로 만들것이다.
3. select c.police_station , p.name from crime_status c , police_station p
where p.name like concat('서울', c.police_station,'경찰서')
: p.name(서울중부경찰서)가 '서울'+'c.police_station'+경찰서 같은 형식으로 부합하면
group by c.police_station , p.name
: 그룹 칼럼을 만들것이다 c.police_station(중부) 과 p.name(서울중부경찰서)순으로
4.alter table police_station ADD PRIMARY KEY (name);
: police_station.name을 primary key로 지정한다.
5. alter table crime_status ADD COLUMN reference varchar(16);
: crime_status 테이블에 reference 라는 칼럼을 varchar 형으로 추가
6.alter table crime_status ADD FOREIGN KEY (reference) REFERENCES police_station(name)
: crime_status.reference에 police_station.name을 foreign키로 묶는다.
7. update crime_status c , police_station p
set c.reference = p.name
: 데이터를 업데이트 할건데 c.reference와 p.name이 같게 업데이트를 할것이다.
where p.name like concat('서울',c.police_station,'경찰서');
: p.name(서울중부경찰서)이 '서울',c.police_station,'경찰서' 와 같은 구조로 있으면
8. select distinct police_station , reference from crims_status;
: 업데이트가 됬는지 확인하기 위해서 crime_status의 police_station 칼럼과 reference 칼럼을 확인한다.
이때 예상된 결과는 c.reference에 p.name을 넣었기 때문에 c.reference에는 p.name의 형식에 맞게 들어가있을것이다.
