[데이터 분석] 데이터 조회하기 / 입력, 수정, 삭제 / 커서, 커넥션

* 데이터 조회하기

 -  조회 시에는 pymysql라이브러리 사용

 

* DB 프로그램 순서

1. 데이터베이스 설정 정보 준비
2. DB접속하기 : 커넥션(connect)
3. 커서 받아오기 : currsor()
4. 구문 작성하기 :  sql(입력, 조회, 수정, 삭제)          
5. 구문 실행하기 : execute() -> 결괏값은 체크                   
6. 조회인 경우 커서에서 데이터 추출하기
        -> 한건 : fetchone()
        -> 여러건 : fetchall()                                                
7. 조회결과 데이터프레임 또는 웹페이지에 출력
        -> 데이터프레임 : pd.DataFrame(여러건), pd.DateFrame([한건])        
8. 조회가 아닌경우 : 처지 결과 조건으로 확인하기 if "처리성공" else "처리실패"    
9. DB정보반환하기                                                                       
        -> 반환순서 : currsor > connect

 

<데이터베이스 연결>

### 접속정보
# - 접속 ID또는 도메인
host = "localhost"

# - 사용자 계정
user = "gjuser"

#- 패스워드
password = "dbdb"

# - 데이터베이스명
db = "gjdb"

# - 한글처리
charset = "utf8"

# - 조회시 컬럼명을 동시에 보여줄지 여부결정
cursorclass = pymysql.cursors.DictCursor

# - 자동반영
autocommit = True

### DB접속하기
try:
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           #charset = charset,
                           autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속성공 >>>", conn)
except:
    print("DB sever checking...")

결과 : DB접속성공 >>> <pymysql.connections.Connection object at 0x000001E75BF14150>

 

### 커서 받아오기
cur = conn.cursor()
cur

결과 : <pymysql.cursors.DictCursor at 0x1e75e4b2710>

 

### select문 생성후 DB에게 요청 및 응답 받기
sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

결과 : 4380 건이 조회되었습니다.

 

### 데이터베이스 닫기
try:
    cur.close()
    conn.close()
except:
    print("이미 모든 커서와 접속정보가 반납되었습니다.")

---

1. 조회결과가 여러 건인경우

rows = cur.fetchall()
rows

 

 

fetchall() : 소괄호 안에 아무 숫자도 쓰지 않을 때는 모든 데이터 조회, 

숫자 입력 시 그만큼의 데이터 조회

 

 

 

 

ymd_power_df = pd.DataFrame(rows)
ymd_power_df

 

 

 

만약 컬럼명 없이 받아온 경우

ymd_power_df = pd.DataFrame(rows, colums=["ymd", "time", "power"])

컬럼명을 넣어주어야 한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

2. 조회결과가 한 건인경우

sql = """
        Select * From time_power_demand
        Where ymd = '2021-01-01'
        And time = '1시'
    """

 - 한건 조회를 위한 sql구문 생성하기
 -  sql 구문 쓸 때 양 옆 한 칸씩 띄우기 붙여 쓰면 sql이 인식할 때 하나의 단어로 본다.

rs_cnt = cur.execute(sql)
rs_cnt

결과 : 1

### 1건의 데이터 추출하기
row = cur.fetchone()
row

결과 : {'ymd': '2021-01-01', 'time': '1시', 'power': 64942}

### 데이터 프레임에 담아서 보여주기
ymd_power_one = pd.DataFrame([row])
ymd_power_one

 

 

 

한건을 조회할 때는 무조건 list 형식으로 [] 감싸기.

---

데이터 입력 / 수정 / 삭제

 

1. 데이터 입력 (insert)

sql = """
        Insert Into time_power_demand(
            ymd, time, power
        ) Values (
            '2020-12-31', '24시', 1234
        )
        """

입력을 위한 구문 생성하기.

rs_cnt = cur.execute(sql)
rs_cnt
print(f"{rs_cnt}건이 처리되었습니다")

결과 : 1건이 처리되었습니다

저장 요청 시에는 결괏값이 숫자값으로 반환된다.

if rs_cnt > 0:
    print(f"{rs_cnt}건이 입력되었습니다.")
else:
    print("입력이 되지 않았습니다.")

결과 : 1건이 입력되었습니다.

 

 

2. 데이터 수정 (update)

    sql = """
            Update time_power_demand 
            set power = 5678
            where ymd = '2020-12-31' and time = '24시'
        """

수정을 위한 구문 생성하기.

### ymd가 2020-12-31이고, 시간이 24시인 행을 찾아서 
# power의 값을 5678로 수정하기.

rs_cn = cur.execute(sql)
if rs_cn > 0:
    print(f"{rs_cnt}건이 수정되었습니다.")
else:
    print("수정안됨")

결과 : 1건이 수정되었습니다.

 

 

3. 데이터 삭제 (delete)

sql = """
        delete from time_power_demand
        where ymd = '2020-12-31' and time = '24시' 
    """

삭제를 위한 구문 생성하기.

rs_cn = cur.execute(sql)
if rs_cn > 0:
    print(f"{rs_cn}건이 삭제되었습니다.")
else:
    print("삭제가 안된듯?")

결과 : 1건이 삭제되었습니다.

---

커서 및 커넥션 정보 반납(종료) 하기

try:
    cur.close()
    conn.close()
    print("DB접속을 마침니다")
except:
    print("이미 모든 커서와 접속정보가 반납되었습니다.")

결과 : 이미 모든 커서와 접속정보가 반납되었습니다.

 * 데이터베이스에서 모든 작업이 끝나면 무조건 커서와 커넥션 정보를 반납시켜 주며 작업을 종료시켜주어야 한다.

    종료하지 않으면 데이터베이스 서버 내의 메모리를 계속 차지하고 있다.