ㅅㅇ
Python _DB : pymysql 을 이용해 mysql 연동 본문
플레이데이터 빅데이터캠프 공부 내용 _ 6/3
Python _DB : pymysql을 이용해 mysql 연동
1. pymysql
: 파이썬에서 MySQL, MariaDB DBMS와 연동하는 다양한 함수를 제공하는 모듈
- Python DB API 2.0 표준을 따른다.
db 마다 연결하는(전송하는) 방식이 다르다. 그러나, 연결한다라는 것은 즉, 하는 일은 동일하다.
이는 Python [DB API 2.0] 표준에 따르기 때문이다.
그렇기에, 모듈과 CONNECT 내 구문 등 달라질 수 도 있지만(DB연결방법에는 차이가 있으니),
CONNECT, CURSOR, CLOSE ... 등 이는 표준에 따르기에 모든 DBMS 공통이다.
- 오라클용 py 라이브러리는 xOracle. 이걸 다운받아 사용해야 하지만
기본적으로 pymysql이든 오라클용이든 사용법(함수...)은 동일한 것.
2. pymysql 설치(jupyter notebook 내)
쥬피터 노트북 환경에서의 사용을 위해 pymysql을 pip 설치 해준다.
- 조건
- python version 3.6 이상
- mysql version 5.6 이상
- 설치
- `pip install PyMySQL`
- `conda install -y -c conda-forge pymysql` #conda로 설치가 되지만, -y 붙어줘야함. conda가 더 느림.
- 쥬피터에서 pymysql 설치
# pymysql 설치는 느낌표없이도 깔리긴 하지만, 원래 넣는 게 맞음.
! pip install pymysql
** 참고) 쥬피터 내 터미널 명령어 실행법
# ! 느낌표를 앞에두면 터미널 명령어를 실행 가능함.
! dir3. 기본 작성 절차
< 연결하기 >
1) Database 연결
- connect() 연결해주기
- port 번호 기본값: 3306
- import 해줘야 한다.
import pymysql
# DB 연결 - connect() => 연결 실패 시 Exception 발생.
connection = pymysql.connect(host="DBMS 서버 ip",
port="port번호",
user="계정명",
password="비밀번호",
db="연결할데이터베이스이름",
charset='utf8')
< 데이터주고받고 >
2) Connection을 이용해 Cursor 생성
- Cursor(): 연결된 Database에 sql문을 전송하고 select결과 조회 기능을 제공하는 객체
# Cursor를 connection으로부터 얻어온다.
cursor = connection.cursor()
3) Cusror를 이용해 SQL문 전송
execute : dms로 쿼리문을 전송하는 메소드
cursor.execute("select문")
- sql 문 실행함.
- 만약, 쿼리문 실행 실패를 한다면 (sql문을 잘못 짠 경우. 제약조건 문제.. ) - > Exception 발생할 수 있다.
cursor.execute("sql문")- mysql 쿼리 구문 그대로 " " 따옴표 안에 써주면 된다.
<연결 닫기>
4) 연결 닫기
- cursor. connection 연결을 닫는다.
- 보통, except-finally 이나 with문을 이용하여 구현.
cursor.close()
connection.close()
___ 구체적 예제로 설명 (사실 위 그대로 작성하는 것보다는 아래 with문 예제처럼 작성한다.)____
3.1 테이블 생성
- 참고) pymysql을 이용해서는 보통 DML(insert, update, delete, update)문을 실행한다.
DDL도 할 수 있지만, 거의 실행하지 않는다. 직접 DBMS로 해주기
# 1 import
import pymysql
# 2 DB 연결 - connect() => 연결 실패 시 Exception 발생.
# 지금은 로컬 ip로 하자.
connection = pymysql.connect(host='127.0.0.1',
port = 3306,
user = '-',
password = '-',
db = 'testdb'
)
# 3 Cursor를 connection으로부터 얻어온다.
cursor = connection.cursor()
# 4 sql 문은 string을 정의. execute() 메소드를 이용해서 db에 전송.
sql = """
create table test_user(
id int auto_increment primary key,
name varchar(30) not null,
email varchar(100) not null unique,
tall decimal(5,2),
birthday date
)
"""
# 4 실행. sql문 보냄.
cursor.execute(sql)
# 5 연결닫기 - cursor, connection close
cursor.close()
connection.close()
3.2 DML _ insert 예시
3.2.1 구문 _ 예외처리 & with문 & 함수 처리(매개변수 받아서)
1) 예외처리 try - except - finally 작성
import pymysql
sql = "insert into test_user (name, email, tall, birthday) values ('홍길동','hong@a.com', 180.23, '2000-12-23')"
try:
# 연결
connection = pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8')
# cursor 생성
cursor = connection.cursor()
# insert문 실행. 확인출력 메시지를 위해 결과를 cnt 변수에 넣어줌.
cnt = cursor.execute(sql)
# 항상 commit을 안 하면 처리가 안 됨.
connection.commit()
# 결과를 반환
print(f"{cnt} 행이 insert 되었습니다.")
except Exception as e:
if connection: # exception 예외 발생 - rollback 원상복구
connection.rollback()
print(e)
finally:
# 연결 닫기 반드시 해야 함.
if cursor: # != None : cursor가 None이 아니라면 연결을 닫아라.
cursor.close()
if connection: # != None : connection이 연결되어 있다면 close해라.
connection.close()
- insert/delete/update 문 실행 후 commit 처리를 꼭 해야 한다.
auto commit = False => commit이 자동으로 안 되기에 우리가 처리해준다. 또, 자동이 아니기에 rollback 이 가능하다.
- exception 예외 발생 처리 : rollback 원상복구
- 현구문에서 오류로 인해 commit 되기 전에 finally로 간다. 즉, commit을 안 하기 때문에 dbms로 안 가긴 함.
그리고 쿼리문이 하나라 지금은 rollback을 할 필요가 없지만, 습관적으로 이것도 작성해주자.
- finally : 에러가 발생했어도 연결을 열었으면 연결 닫기가 필수적으로 이뤄져야 한다.
보통, 이 처리를 위해 본 방법보다는 아래의 with문 방법을 이용해서 작성한다.
2) with문 작성
- with 연결함수 as 변수
sql = "insert into test_user (name, email, tall, birthday) values ('홍길동2','hong222@a.com', 150.23, '2003-12-23')"
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(sql)
print(f'{cnt} 행이 insert됨')
connection.commit() # 항상 commit
# 여기서도 여러 쿼리문이라면 rollback 해줘야 함.
3) with문 _ 함수 처리 작성
def insert_user(name, email, tall, birthday):
sql = f"insert into test_user (name, email, tall, birthday) values ('{name}','{email}', '{tall}', '{birthday}')"
# values 데이터 값을 매개변수로 넣을 때' ' 로 묶어줘야 한다.
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(sql)
print(f'{cnt} 행이 insert됨')
connection.commit() # 항상 commit
insert_user('이순신','lee@a.com',190.20,'1955-11-11')
- mysql연동 프로그램을 전역함수로 만들 수 없는 이유는 연결을 항상 끊는 작업이 필요해서이다.
그렇기에 함수로 만들어 재사용을 한다. 함수가 끝나면 무조건 연결끊기가 처리되도록 작성해야 한다. => with 문
- values ('{name}','{email}', '{tall}', '{birthday}')"
=> values 데이터 값을 매개변수로 넣을 때' ' 로 묶어줘야 한다.
이게 매우 귀찮기 때문에 우린 보통 아래의 parameterized query 방법을 사용한다.
3.2.2 구문 _ Parameterized Query
: SQL 문에서 컬럼 값이 들어가는 자리에 값대신 `%s` placeholder를 사용한뒤
execute()에서 placeholder에 넣을 값을 tuple로 제공한다.
- query문을 쉽게 작성할 수 있는 장점이 있다.
나중에 값 들어갈거야를 말해줌. 나중에 들어갈 때 ' ' 따옴표 알아서 들어가서 편하다. 위처럼 일일이 붙일 필요 없음. - 모든 문에서 다 사용 가능.
1) 기본예제
( ex1 ) Parameterized Query을 이용해 데이터 삽입 _ with문
from datetime import date
sql = 'insert into test_user(name, email, tall, birthday) values (%s, %s,%s,%s)'
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
# 날짜 시간 데이터 - 문자열로 ' '
cnt = cursor.execute(sql, ('유관순', 'yo331@a.com', 173.23, '2000-02-12')) # 튜플로 넣어줌. 타입에 맞춰 값만 넣어주면 됨.
# 날짜 시간 데이터 - date() 이용
cnt2 = cursor.execute(sql, ('강감찬', 'dfdf@a.com', 180.3, date(1955,2,3)))
print(f'{cnt+cnt2} 행이 insert됨')
connection.commit() # 항상 commit을 안 하면 처리가 안 됨.- 튜플로 넣어야 한다. 원소 하나 일 때 , 넣어주기
cursor.execute(delete_sql, (4, ))
- sql 쿼리문 작성 방식
- sql 쿼리문에서 문자열은 ' ' 작은 따옴표로 감싸줘야 한다.
- date 데이터 타입
- 문자열 방식 ' 2022-12-12'
- date(2022,12,12) 이용
- 알아서 타입에 맞춰서 들어감.
- 그리고 select 조회 시 date 타입으로 받는다.
- date.today() # 현 날짜 반환
(참고) datetime 모듈
: 날짜 -date, 시간-time, 날짜시간 - datetime 등 날짜와 시간을 다루는 모듈
-> DB의 날짜/시간 타입과 연동.
2) Parameterized Query를 이용해 여러개 행 insert
( ex1 ) for문 사용
- 0, 1, 2 인덱스 별로 데이터 정보 적어주고 for문 zip() 함수 사용
names = ['이름1', '이름2','이름3']
emails = ['e1@a.com', 'e2@a.com', 'e3@a.com']
talls = [192.3,149.2, 150.6]
birthdays = ['2000-10-10', date.today(), '1999-02-02']
sql = 'insert into test_user(name, email, tall, birthday) values (%s, %s,%s,%s)'
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
for data in zip(names, emails, talls, birthdays):
cursor.execute(sql,data)
connection.commit()
- 이 방법보다는 executemamy() 훨씬 편하다.
( ex2 ) executemany() 사용
- 아래 3.2 select에서 더 자세히 배울 것이다.
- insert할 값들을 가진 리스트를 넣어 한번에 여러 행을 insert한다.
- excute를 여러번 할 거야.
- 0, 1, 2 인덱스 별로 데이터 정보 적어주고 executemany(sql, datas)
datas = [
['name1', 'a31@a.com', 172.2, '2000-12-11'],
['name2', 'b13@a.com', 193.2, '1999-12-09'],
['name3', 'c31@a.com', 163.2, '1998-01-09'],
]
sql = 'insert into test_user (name, email, tall, birthday) values (%s,%s,%s,%s)'
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cursor.executemany(sql, datas) # 내부적으로 반복돌면서 알아서 넣어짐.
connection.commit()- datas에 리스트, 튜플 둘 다 넣을 수 있다.
3.2.3 기타 예제
ex) 'Null을 허용하는 컬럼, default값이 있는 컬럼'들을 '기본값이 있는 매개변수'로 선언
insert 에서 아직 정보가 없는 또는 모르는 데이터에 해당하는 컬럼에 default None 을 설정했다.
이렇게 기본값을 주면 일일이 함수 호출할 때 None 값을 안 써도 된다.
- 주의. 디폴트값 있는 애들 뒤에 디폴트값 없는 애를 적을 수 없다.
없는 애들 먼저 써주고, 있는 애들 써주기.
# 1개의 행을 emp 테이블에 insert하는 함수 - 호출하는 값 insert
def insert_emp(emp_id, emp_name, job, hire_date, mgr_id=None, salary=None, comm_pct=None, dept_name=None):
sql = "insert into emp values (%s, %s, %s, %s, %s, %s, %s,%s)"
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-', db ='hr', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(sql,(emp_id, emp_name, job, mgr_id, hire_date, salary, comm_pct, dept_name)) # 주의. hr emp 테이블 틀 순서대로
print(cnt)
connection.commit()
# 넣어준 대로 들어감.
insert_emp(402, '홍길동', 'IT_PROG', '2010-12-12', 103, 3500, 0.2, '기획부')
# 안 넣어준 컬럼이 있다. 이때, 기본값 None 들어감.
insert_emp(401, '박영수', '구매', '2000-11-21') # 안 넣어준 컬럼에는 기본값 None 들어감.3.3 DML _ update & delete 예시
- 코딩 절차는 insert 와 동일
ex1 ) update 데이터 수정 _ 특정 컬럼 데이터 수정
# tall 컬럼 데이터 모두 +10 , birthday 모두 변경
update_sql = "update test_user set tall = tall +10, birthday = '2000-01-01'"
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(update_sql)
connection.commit()
print(f'{cnt}행이 update 됨')
ex2 ) update 데이터 수정 _ 함수_ 특정 id 의 user 정보를 변경( 해당 id와 바꿀 값들 모두 매개변수로 입력 받음.)
호출 : 안바뀌는 값, 바뀐 값 모두 argument로 전달.
- 실제로도 where절 조건 데이터 값, 바꿀 값들 모두 인수로 받아 매개변수에 넣어진다.
- 쿼리문 %s 으로 할 때 매개변수 잘 넣어주기.
# 특정 id 의 user 정보를 변경
def update_user_by_id(id, name, email, tall, birthday):
update_sql = 'update test_user set name=%s, email=%s, tall=%s, birthday = %s where id = %s'
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(update_sql, (name, email, tall, birthday, id)) # 순서 헷갈리지 말기
connection.commit()
return cnt
update_user_by_id(1, '홍길동', 'hhh@abd.com', 192.23, '2000-10-10')
ex3 ) delete 데이터 삭제
- 삭제할 데이터가 이미 없다면 0 출력. 에러 발생하는 건 아니다.
def delete_user_by_id(id):
delete_sql = 'delete from test_user where id = %s'
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cnt = cursor.execute(delete_sql, (id, )) # 튜플로 만들어야 하기에.
print(cnt)
connection.commit()
return cnt
delete_user_by_id(2)3.4 DQL _ Select 예시
: 조회결과 조회
- DQL(Data Query Language) : Select가 DML에 포함되긴 하지만 정확히 말하면 DQL이다.
- 데이터 없으면 None 반환
- execute : dms로 쿼리문을 전송하는 메소드
- cursor.execute("select문") 실행 후
cursor의 결과 조회 메소드(fetch메소드)를 이용해 결과를 받는다.
- 결과를 받는 것도 cursor로부터 들고 온다.
- fetch메소드 (cursor에 있는 메소드)
(1) cursor.fetchall()
- 조회한 모든 행을을 반환
(2) cursor.fetchmany(size=개수)
- 지정한 size개수 만큼 반환
(3) cursor.fetchone()
- 조회결과 중 첫번째 행만 반환(한 행만.)
- 주로 pk 동등 조건으로 조회한 경우 사용
- 제공 방식
- 튜플로 받는 방법 : 하나의 데이터 결과(한 행)를 tuple로
- 딕셔너리로 받는 방법 : 하나의 데이터 결과(한 행)를 dict로
조회 결과를 didtionary 로 반환하는 커서 (connect 문에 아래 구문 추가.)
cursorclass=pymysql.cursors.DictCursor # 안 써주면 tuple로
- 둘 다 많이 쓰임
3.4.1 fetchall()
(ex. 1) fetchall()_ 튜플로 받는 방법
import pymysql
sql = 'select * from test_user'
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
print(type(cursor))
# select 문 실행
cursor.execute(sql)
# select 결과 조회 (cursor.fetchXXX() 메소드 이용)
result = cursor.fetchall()
- execute 후 cursor.fetachall() 조회로 받은 데이트를 result 변수에 담음.
print(type(result)) # <class 'tuple'>
print(type(result[0])) # <class 'tuple'>
- 전체 테이터도 튜플로 묶고, 데이터별도 튜플로 묶어서 보낸다.
=> fetchall() 의 결과는 튜플 안에 튜플로 묶인 한 개의 데이터로 나온다.
그리고 한 데이터 (한 행) 안에는 속성(컬럼)이 여러 개이다.
그렇기에 한 데이터 별(한 행별로)로 묶을 필요가 있다.
n개에 대한 각 데이터별로 튜플로 묶고 쿼리문으로 조회받은 데이터 하나 자체도 튜플로 전체를 묶어준다.
((1, '홍길동', 'hhh@abd.com', Decimal('192.23'), datetime.date(2000, 10, 10)),
(8, '유관순', 'yo11@a.com', Decimal('193.23'), datetime.date(2000, 1, 1)))
(ex. 2) fetchall() _ 딕셔너리로 받는 방법
cursorclass = pymysql.cursors.DictCursor 추가.
=> 조회 결과를 didtionary 로 반환하는 커서.
sql =select * from test_user
with pymysql.connect(host='127.0.0.1',
port=3306, user='-',
password='-',
db='testdb',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
as connection:
with connection.cursor() as cursor:
# select 문 실행
cursor.execute(sql)
result = cursor.fetchall()
- dict : key 컬럼명, value 컬럼값. # 별칭 안 넣어서 db table 컬럼명 그대로
- result 변수에 담아 원하는 정보 인덱스명으로 이용해 받기
print(type(result,), len(result)) # <class 'list'> 2
print(type(result[0])) # <class 'dict'>
# result 변수에 담아 원하는 정보 인덱스를 이용해 받기
result[0]['id'], result[0]['email']
- 전체 데이터는 리스트, 리스트 내 데이터 별 원소는 dict 으로 묶는다.
[{'id': 1, 'name': '홍길동', 'email': 'hhh@abd.com', 'tall': Decimal('192.23'), 'birthday': datetime.date(2000, 10, 10)},
{'id': 8, 'name': '유관순', 'email': 'yo11@a.com', 'tall': Decimal('193.23'), 'birthday': datetime.date(2000, 1, 1)}]
3.4.2 fetchone()
: 특정 데이터 특정 행 하나의 행을 보고 싶을 때
데이터가 n개일 때 튜플로 전체를 묶는 건데, 여기서는 한 데이터를 반환한다.
그렇다면 굳이 전체 튜플로 묶을 필요가 있는가.
받은 데이터 처리 시 중복리스트 인덱스 형식으로 값 처리해줘야 하고 불편할 것이다.
이렇게 한 데이터 한 행만을 볼 때 쓰는 게 fetchone()
ex) fetchone()
# pk로 조회. - 조회결과 0행(없으면) 또는 1행(데이터 있으면)
# 조회 결과가 없으면 None을, 있으면 1개의 데이터를 반환
sql = 'select * from test_user where id = %s'
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cursor.execute(sql, 1)
result = cursor.fetchone()
print(len(result)) # 5 데이터 내 5개 속성
print(result)
(1, '홍길동', 'hhh@abd.com', Decimal('192.23'), datetime.date(2000, 10, 10))- 만약 여러 행을 불러모아도 제일 첫 번째 행만을 반환한다.
3.4.3 fetchmany()
- fetchall로 하니 너무 많다. 갯수를 정해 그 행만 일부분만 가져오고싶을 때 사용.
- 많이 쓰지는 않음
cursor.fetchmany(size = 원하는 크기정수로 )
ex) fetchmany()
sql = "select * from test_user"
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cursor.execute(sql)
result1 = cursor.fetchmany(size = 3)
print(result1)- 정렬대로 3개 행 반환
3.4.4 기타 예제
ex) 'cursor 는 iterable 타입'
: for문에 select 실행한 cursor를 사용하면 조회결과를 한 행씩 조회할 수 있다.
sql = "select * from test_user"
with pymysql.connect(host='127.0.0.1', port=3306, user='-', password='-', db='testdb', charset='utf8') as connection:
with connection.cursor() as cursor:
cursor.execute(sql)
for data in cursor: # 한번 반복시 마다 조회결과를 한행씩 반환
print(data)
ex) db 쿼리문 작성 문법과 동일하게 쥬피터에서도 작성해주면 된다.
# salary 범위를 받아서 그 범위의 salary를 받는 직원들을 조회한 결과를 반환하는 함수
def select_emp4(maxsalary,minsalary):
sql = "select * from emp where salary between %s and %s"
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-',db='hr',charset='utf8') as connection:
with connection.cursor() as cursor:
cursor.execute(sql,(maxsalary,minsalary))
result4 = cursor.fetchall()
return result4
R = select_emp4(6000,8000)
for data in R:
print(f"이름: {data[1]}, 월급 : {data[5]}")
ex) group by, 집계함수
# 부서별 급여 통계정보(합계, 평균, 최대, 최소)를 반환하는 함수
def select_emp5():
sql = "select dept_name, sum(salary) 합계, avg(salary) 평균, max(salary) 최대급여, min(salary) 최소급여 from emp group by dept_name"
with pymysql.connect(host='127.0.0.1',port=3306, user='-', password='-',db='hr',charset='utf8', cursorclass=pymysql.cursors.DictCursor) as connection:
with connection.cursor() as cursor:
cursor.execute(sql)
result5 = cursor.fetchall()
return result5
x = select_emp5()
print(x)'AI_STUDY > Python' 카테고리의 다른 글
| 아나콘다 가상환경 생성 및 활성화 (Python, Windows) & 패키지 pip 설치 (pandas jupyter matplotlib) (0) | 2022.06.03 |
|---|---|
| python 프로그래밍 : 정규표현식 (0) | 2022.05.23 |
| python 프로그래밍 : 텍스트 파일 입출력 (0) | 2022.05.19 |
| python 프로그래밍 : 예외와 예외처리 (0) | 2022.05.18 |
| python 프로그래밍 : 자료 구조_리스트 (0) | 2022.05.16 |
