pymysql
1. 데이터 조회시에 유용한 라이브러리
2.설치
- 방법1 : conda install -c conda-forge pymysql
- 방법2 : pip install pymysql
DB 프로그램 순서
데이터베이스 설정 정보 준비
import pymysql
##반드시 있어야 하는 정보
# 접속 ID or 도메인
host = "localhost"
# 사용자계정
user = "gjuser"
# 패스워드
password = "dbdb"
# 데이터베이스명
db = "gjdb"
##설정하면 좋은 정보
# 한글처리
charset = "utf8"
# 자동반영
autocommit = True
# 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor
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 Server Checking...")
DB접속 성공 >>> <pymysql.connections.Connection object at 0x0000020C6EBC1050>
커서 받아오기
### cursor() : db를 왔다갔다 할 수 있는 통로
cur = conn.cursor()
cur
<pymysql.cursors.DictCursor at 0x20c6f34c850>
SQL 작성 + 구문 실행
###SQL 구문 작성
sql = " Select * From time_power_demand "
###구문 실행하기
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")
8760건이 조회 되었습니다.
데이터 추출
###데이터 추출
# 조회결과가 한건인 경우
row = cur.fetchone()
row
# 조회결과가 여러건인 경우
rows = cur.fetchall()
rows
※ 이 단계 후 DB연결은 끊어도 됨
데이터프레임 생성 및 출력
### 데이터프레임 생성 및 출력
# 조회결과가 한 건인 경우
ymd_power_df = pd.DataFrame(rows)
ymd_power_df
# 조회결과가 여러건인 경우(반드시 리스트 형식으로 데이터프레임에 넣어야함)
ymd_power_df = pd.DataFrame([rows])
ymd_power_df
※ 데이터베이스 설정정보에서 "cursorclass = pymysql.cursors.DictCursor" 넣었으면 자동으로 컬럼생성됨
※ 컬럼명 없이 받아온 경우는 컬럼 생성해줘야한다.
- [(), ()...] 이런 형태
- 컬럼 생성 : ymd_power_df = pd.DataFrame(rows, columns=["ymd", "time", "power"])
구문 작성하기
SQL(조회, 입력, 수정, 삭제)
1. 조회 ( Select )
###데이터 추출
# 조회결과가 한 건인 경우
# 한건 조회를 위한 sql 구문 생성하기 ( 구문 줄당 양옆 띄어쓰기 꼭하기! )
sql = """
select*from time_power_demand
where ymd = '2021-01-01'
AND time = '1시'
"""
### sql 구문을 DB서버에 요청하고, 결과 받아오기
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.\n")
### 1건 데이터 추출하기
row = cur.fetchone()
### 데이터프레임으로 만들기
result = pd.DataFrame([row])
print(result)
2. 입력 (INSERT)
###데이터 입력하기
# 입력을 위한 구문 생성하기
sql = """
INSERT INTO time_power_demand(
ymd, time, power
) VALUES (
'2020-12-31', '24시', 1234
)
"""
# 저장 요청 시에는 결과값이 숫자값으로 반환됩니다. (insert, update, delete는 실행시 실행건수만 나옴)
rs_cnt = cur.execute(sql)
### 처리가 잘 되었는지 확인하기
# 0보다 크면 "1건이 입력되었습니다." 출력
# 0이하인 경우에는 "입력되지 않았습니다." 출력
if rs_cnt > 0 :
print(f"{rs_cnt}건이 입력되었습니다.")
else :
print("입력되지 않았습니다.")
1건이 입력되었습니다.
3. 수정 (UPDATE)
### ymd가 2020-12-31이고, 시간이 24시인 행을 찾아서 power의 값을 5678로 수정하기
sql = """
UPDATE time_power_demand
SET POWER = 5678
WHERE ymd = '2020-12-31'
AND TIME = '24시';
"""
### DB에 SQL구문 요청하고 결과 받아오기
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
print(f"{rs_cnt}건이 수정되었습니다.")
else :
print("수정되지 않았습니다.")
1건이 수정되었습니다.
4. 삭제 (DELETE)
### ymd가 2020-12-31이고, 시간이 24시인 행을 찾아서 삭제시키기
sql = """
DELETE FROM time_power_demand
WHERE ymd = '2020-12-31'
AND TIME = '24시'
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
print(f"{rs_cnt}건이 삭제되었습니다.")
else :
print("삭제되지 않았습니다.")
1건이 삭제되었습니다.
DB 종료하기
### DB 종료하기
# 계속 접속되어있는 상태이면 서버에서 메모리 공간이 생성된 것을 유지하기 때문에 서버가 느려질 수 있다.
# 반드시 종료해주기!!
try :
cur.close()
conn.close()
print("DB 접속이 종료되었습니다.")
except :
print("이미 모든 커서와 접속정보가 반납되었습니다.")
DB 접속이 종료되었습니다.
문제
- 한번도 구매한적이 없는 회원정보를 조회해주세요
- 조회컬럼 : 회원아이디, 회원이름
sql = """
SELECT mem_id, mem_name
FROM member
WHERE NOT EXISTS(SELECT cart_member FROM cart WHERE cart_member = mem_id)
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
print(f"{rs_cnt}건이 조회되었습니다.")
else :
print("조회되지 않았습니다.")
### 커서가 받아온 데이터 추출하기(여러건)
rows = cur.fetchall()
### 데이터프레임으로 만들기
result = pd.DataFrame(rows)
result
9건이 조회되었습니다.
- DB 접속 종료하기
# 커서 및 커넥션 정보 반납(종료)하기
try :
cur.close()
conn.close()
print("DB 접속이 종료되었습니다.")
except :
print("이미 모든 커서와 접속정보가 반납되었습니다.")
DB 접속이 종료되었습니다.
< DB 프로그램 순서 >
1. 데이터베이스 설정 정보 준비
2. DB접속하기 : 커넥션(connect)
3. 커서 받아오기 : cursor()
4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)
5. 구문 실행하기 : execute() → 결과값 체크
6. 조회인 경우 커서에서 데이터 추출하기
→ 한건 : fetchone()
→ 여러건 : fetchall()
7. 조회결과 데이터프레임 또는 웹페이지에 출력
→ 한건 : pd.DataFrame([한건])
→ 여러건 : pd.DataFrame(여러건)
8. 조회가 아닌경우 : 처리 결과 조건으로 확인하기
→ if "처리 성공"
else "처리 실패"
9. DB정보 반환하기 : close()
- 반환 순서 : cursor > connect
10. DB 접속 종료하기
< DB 프로그램 class화 >
○ : 고정으로 둘 값 [CLASS 안]
× : 바뀔 수 있는 값 [CLASS 밖]
△ : 중간 [CLASS 안 or 밖]
1. 데이터베이스 설정 정보 준비 ○
2. DB접속하기 ○
3. 커서 받아오기 ○
4. 구문 작성하기 ×
5. 구문 실행하기 △
6. 조회인 경우 커서에서 데이터 추출하기 △
7. 조회결과 데이터프레임 또는 웹페이지에 출력 △
→ 편하게 쓰려면 6번이랑 묶기
8. 조회가 아닌경우 : 처리 결과 조건으로 확인하기 △
9. DB정보 반환하기 : close() ○
class화 실습
1.클래스 생성
- 클래스 이름 : PowerClass
2. 클래스 내에 함수들 정의
3. 외부에서 조회/한건입력/수정/삭제 처리하기
<내 코드>
#필요한 라이브러리 import
import pymysql
import pandas as pd
#클래스 생성
class PowerClass:
#초기값 : sql구문을 문장으로 담을수 있는 문자열 생성
def __init__(self):
self.sql = """ """
#데이터베이스 설정정보준비 함수
def db_info(self):
host = "localhost"
user = "gjuser"
password = "dbdb"
db = "gjdb"
charset = "utf8"
autocommit = True
cursorclass = pymysql.cursors.DictCursor
#DB접속하기 함수
def db_conn(self):
try :
self.conn = pymysql.connect(host=host,
user=user,
password=password,
db=db,
charset=charset,
autocommit=autocommit,
cursorclass=cursorclass)
print("DB접속 성공 >>>", self.conn)
except :
print("DB Server Checking...")
#커서 받아오는 함수
def get_cur(self):
self.cur = self.conn.cursor()
print("커서 생성 성공 >>>")
#구문 실행하는 함수
def sql_exc(self):
self.rs_cnt = self.cur.execute(self.sql)
if self.rs_cnt > 0 :
print(f"{self.rs_cnt}건이 실행되었습니다.")
else :
print("실행되지 않았습니다.")
#데이터 추출하기(한건 or 여러건) 함수
def get_db(self):
if self.rs_cnt == 1 :
rows = self.cur.fetchone()
result = pd.DataFrame([rows])
else :
rows = self.cur.fetchall()
result = pd.DataFrame(rows)
return result
#DB정보 반환하기 함수
def db_close(self):
try :
self.cur.close()
self.conn.close()
print("DB 접속이 종료되었습니다.")
except :
print("이미 모든 커서와 접속정보가 반납되었습니다.")
# 실행하기
def main():
db = PowerClass()
while True:
print_menu = input('''
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
''')
if print_menu == '1':
sql = input('조회, 입력, 수정, 삭제할 sql구문을 입력하세요')
db.sql = sql
db.db_conn()
db.get_cur()
db.sql_exc()
#데이터프레임 출력
results = db.get_db()
print(results)
db.db_close()
elif print_menu == '2':
print("서비스 종료")
break
else :
print('잘못입력하셨습니다. 다시 입력해주세요.')
if __name__ == "__main__" :
main()
<실행할 구문들>
# 조회
Select * From time_power_demand
WHERE ymd = '2020-12-31'
AND TIME = '24시';
# 입력
INSERT INTO time_power_demand(
ymd, time, power
) VALUES (
'2020-12-31', '24시', 1234
);
# 수정
UPDATE time_power_demand
SET POWER = 5678
WHERE ymd = '2020-12-31'
AND TIME = '24시';
# 삭제
DELETE FROM time_power_demand
WHERE ymd = '2020-12-31'
AND TIME = '24시';
결과
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
INSERT INTO time_power_demand( ymd, time, power ) VALUES ( '2020-12-31', '24시', 1234 )
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F92C9DD0>
커서 생성 성공 >>>
1건이 실행되었습니다.
0
0 None
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
Select * From time_power_demand WHERE ymd = '2020-12-31' AND TIME = '24시'
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F9219450>
커서 생성 성공 >>>
2건이 실행되었습니다.
ymd time power
0 2020-12-31 24시 1234.0
1 2020-12-31 24시 1234.0
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
UPDATE time_power_demand SET POWER = 5678 WHERE ymd = '2020-12-31' AND TIME = '24시'
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F8727010>
커서 생성 성공 >>>
2건이 실행되었습니다.
Empty DataFrame
Columns: []
Index: []
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
Select * From time_power_demand WHERE ymd = '2020-12-31' AND TIME = '24시'
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F891A2D0>
커서 생성 성공 >>>
2건이 실행되었습니다.
ymd time power
0 2020-12-31 24시 5678.0
1 2020-12-31 24시 5678.0
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
DELETE FROM time_power_demand WHERE ymd = '2020-12-31' AND TIME = '24시'
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F7E345D0>
커서 생성 성공 >>>
2건이 실행되었습니다.
Empty DataFrame
Columns: []
Index: []
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
1
조회, 입력, 수정, 삭제할 sql구문을 입력하세요
# 조회 Select * From time_power_demand WHERE ymd = '2020-12-31' AND TIME = '24시'
DB접속 성공 >>> <pymysql.connections.Connection object at 0x000001D4F7CB0AD0>
커서 생성 성공 >>>
실행되지 않았습니다.
Empty DataFrame
Columns: []
Index: []
DB 접속이 종료되었습니다.
[SQL 구문작성하기]
1. 조회, 입력, 수정, 삭제
2. 종료
원하시는 서비스 번호를 입력하세요.
2
서비스 종료
<교수님 코드>
#필요한 라이브러리 import
import pymysql
import pandas as pd
#클래스 생성
class PowerClass :
### 생성자 : DB접속정보 정의
def __init__(self) :
self.host = "localhost"
self.user = "gjuser"
self.password = "dbdb"
self.db = "gjdb"
self.charset = "utf8"
self.cursorclass = pymysql.cursors.DictCursor
self.autocommit = True
##### 초기값으로 connect와 cursor 함수 실행하기
### DB 접속하기
self.initConnection()
### Cursor 생성하기
self.initCursor()
### DB 접속하기 : connect
def initConnection(self) :
try :
self.conn = pymysql.connect(host=self.host,
user=self.user,
password=self.password,
db=self.db,
charset=self.charset,
autocommit=self.autocommit,
cursorclass=self.cursorclass)
print("DB접속 성공 >>> ", self.conn)
except :
print("DB Server Checking...")
### 커서 생성하기
def initCursor(self) :
self.cur = self.conn.cursor()
print("커서 생성 성공 >>> ", self.cur)
### 구문 실행하기
def getExecute(self, sql) :
rs_cnt = self.cur.execute(sql)
return rs_cnt
### 조회 - 여러건 데이터프레임으로 변환하여 반환하기
def getSelectAll(self, sql) :
rs_cnt = self.getExecute(sql)
if rs_cnt > 0 :
rows = self.cur.fetchall()
rs_df = pd.DataFrame(rows)
else :
rs_df = False
return rs_df
### 조회-한건 데이터프레임으로 변환하여 반환하기
def getSelectOne(self, sql) :
rs_cnt = self.getExecute(sql)
if rs_cnt > 0 :
row = self.cur.fetchone()
rs_df = pd.DataFrame([row])
else :
rs_df = False
return rs_df
### DB 접속 정보 반환하기
def dbClose(self) :
try :
self.cur.close()
self.conn.close()
print("DB 접속을 종료합니다.")
except :
print("이미 DB 접속이 종료되어 있습니다.")
### 데이터 여러건 조회하기
try :
# 클래스 생성하기
pClass = PowerClass()
# 조회-여러건
sql = """
SELECT mem_id, mem_name
FROM member
WHERE NOT EXISTS(SELECT cart_member From cart
WHERE cart_member = mem_id)
"""
# 여러건 데이터프레임으로 조회하기
rs_df = pClass.getSelectAll(sql)
except Exception as e:
print(f"오류발생 : {e}")
##### finally로 반드시 정보 반환하게 하고 클래스도 소멸시키기(메모리 공간 삭제)
finally :
# DB정보 반환하기
pClass.dbClose()
# 사용된 클래스 소멸(삭제)시키기
del pClass
### 출력
rs_df
결과
### 데이터 한건 조회하기
try :
# 클래스 생성하기
pClass = PowerClass()
# 조회-한건
sql = """
SELECT mem_id, mem_name
FROM member
WHERE mem_id = 'a001'
"""
# 한건 조회하기
rs_df = pClass.getSelectOne(sql)
except Exception as e:
print(f"오류발생 : {e}")
finally :
# DB정보 반환하기
pClass.dbClose()
# 사용된 클래스 소멸(삭제)시키기
del pClass
### 출력
rs_df
결과
### 데이터 입력 조회하기
try :
# 클래스 생성하기
pClass = PowerClass()
# 입력을 위한 구문 생성하기
sql = """
Insert Into time_power_demand(
ymd, time, power
) Values (
'2020-12-31', '24시', 1234
)
"""
rs_cnt = pClass.getExecute(sql)
if rs_cnt > 0 :
print(f"{rs_cnt}건이 입력되었습니다.")
else :
print("입력되지 않았습니다.")
except Exception as e:
print(f"오류발생 : {e}")
finally :
# DB정보 반환하기
pClass.dbClose()
# 사용된 클래스 소멸(삭제)시키기
del pClass
결과
'Back-End > 데이터베이스' 카테고리의 다른 글
| [DB] 데이터베이스 실습 - 버스교통카드 데이터 전처리 시각화(히트맵, 막대그래프, 선그래프) (2) | 2023.11.30 |
|---|---|
| [DB] 데이터베이스 실습 - 버스교통카드 데이터 수집 가공 (0) | 2023.11.29 |
| [DB] 데이터베이스 테이블에 저장하기, 설계하기 (DB 연결, 저장, 자원반환) (2) | 2023.11.28 |
| [DB] 데이터 수집하기 (0) | 2023.11.28 |
| [DB] 데이터 전처리 기초 - 판다스(pandas) (6) | 2023.11.28 |
