pymysql 라이브러리를 이용하여 데이터베이스에 연결하고 다양한 쿼리들을 수행하는 테스트를 해보겠습니다.
그리고 ‘[Python] 파이썬 프로그래밍 : mysql 데이터베이스 연결을 위한 pymysql 분석‘ 에서 pymysql의 기본 예제를 참고하면 되겠고 기본 예제를 바탕으로 다양한 테스트를 해보겠습니다.
[Test.1] select 문
[코드]
import pymysql
connection = pymysql.connect(host='...', user='...', password='...', db='...')
try:
with connection.cursor() as cursor:
sql = "select * from jejusodory_areaCode"
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
print(i)
finally:
connection.close()
[결과]
이미 구성된 데이터베이스의 areaCode라는 테이블을 select 하는 테스트 입니다.
처음 시도해 보는 테스트로 select를 해보았는데 생각했던 것과 다른 결과값이 나와서 당황을 하였습니다.
데이터베이스 연결할 때 charset을 utf8로 설정을 하였음에도 불구하고 한글이 정상적으로 출력되지 않는 상황이였습니다.
이 문제를 해결하기 위하여 검색하고 테스트한 결과 cursor.execute(“set names utf8”)를 코드에 추가하니 그나마 정상 동작을 하는 것을 확인할 수 있었습니다.
[코드]
...
with connection.cursor() as cursor:
sql = "select * from jejusodory_areaCode"
cursor.execute("set names utf8")
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
print(i)
...
[결과]
('1', '서울', '1') ('2', '인천', '2') ('3', '대전', '3') ('4', '대구', '4') ('5', '광주', '5') ('6', '부산', '6') ('7', '울산', '7') ('8', '세종특별자치시', '8') ('31', '경기도', '9') ('32', '강원도', '10')
추가적으로 pymysql 샘플 예제에는 없었던 함수를 사용한 부분이 있습니다.
샘플 예제에서는 데이터를 select할 때 아래와 같이 fetchon()을 이용하여 데이터를 불러왔습니다.
result = cursor.fetchone()
fetchon()은 1개의 row의 데이터를 불러오는 함수로 전체 row를 불러오기 위해서는 위 테스트 코드와 같이 fetchall() 함수를 사용하고 그 결과를 for문을 이용하여 한줄 씩 출력해야 모든 데이터를 나타낼 수 있습니다.
[Test.2] insert 문
[코드]
import pymysql
connection = pymysql.connect(host='localhost', user='jackerlab', password='tpgns628!!', db='jacker_test')
try:
with connection.cursor() as cursor:
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('jackerlab_1', 'jackerlab_!'))
cursor.execute(sql, ('jackerlab_2', 'jackerlab_@'))
cursor.execute(sql, ('jackerlab_3', 'jackerlab_#'))
connection.commit()
finally:
connection.close()
[결과]
insert 쿼리를 수행하는 테스트를 하였습니다.
‘[Test.1] select 문’ 에서 sql 부분과 cursor.execute() 부분만 수정을 하면 쉽게 insert 쿼리를 수행 할 수 있습니다.
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('jackerlab_1', 'jackerlab_!'))
sql 변수에 insert 쿼리를 작성하고 대신 VALUES 부분에는 문자열 변수를 받을 수 있도록 하였습니다.
그리고 execute를 실행할 때 sql과 변수에 들어갈 문자열을 함께 넘겨주어 수행이 되도록 하였습니다.
쿼리 내에 하드 코딩된 문자열로 쿼리를 작성하면 데이터가 많거나 동적인 경우 모두 수정을 해야 하는 번거로움이 발생합니다.
그렇기 때문에 위와 같이 변수를 넘겨주는 방식으로 처리하면 되고 위 execute()의 변수는 tuple 형태이기 때문에 좀 더 유연하게 데이터를 바꾸어 가면서 insert 쿼리를 수행할 수 있겠습니다.
[Test.3] update 문
[코드]
...
try:
with connection.cursor() as cursor:
sql = "UPDATE `users` SET `password` = %s WHERE `email` = %s"
cursor.execute(sql, ('change_pw', 'jackerlab_3'))
connection.commit()
finally:
connection.close()
update 쿼리를 수행하는 테스트를 하였습니다.
insert 쿼리와 동일한 형태이며 sql 쿼리만 update 문으로 변경하였습니다.
update문에 사용될 문자열들은 변수로 받아서 execute()를 실행할 때 같이 넘겨주어 실행되게 하였습니다.
[결과]
(1, 'webmaster@python.org', 'very-secret') (2, 'jackerlab_1', 'jackerlab_!') (3, 'jackerlab_2', 'jackerlab_@') (4, 'jackerlab_3', 'change_pw') (5, 'jackerlab_1', 'jackerlab_!') (6, 'jackerlab_2', 'jackerlab_@') (7, 'jackerlab_3', 'change_pw') (8, 'jackerlab_1', 'jackerlab_!') (9, 'jackerlab_2', 'jackerlab_@') (10, 'jackerlab_3', 'change_pw') (11, 'jackerlab_1', 'jackerlab_!') (12, 'jackerlab_2', 'jackerlab_@') (13, 'jackerlab_3', 'change_pw')
[Test.4] delete 문
[코드]
...
try:
with connection.cursor() as cursor:
sql = "DELETE FROM `users` WHERE `email` = %s"
cursor.execute(sql, ('jackerlab_3'))
connection.commit()
with connection.cursor() as cursor:
sql = "SELECT `id`, `email`, `password` FROM `users`"
cursor.execute(sql)
#result = cursor.fetchone()
result = cursor.fetchall()
for i in result:
print(i)
finally:
connection.close()
[결과]
(1, 'webmaster@python.org', 'very-secret') (2, 'jackerlab_1', 'jackerlab_!') (3, 'jackerlab_2', 'jackerlab_@') (5, 'jackerlab_1', 'jackerlab_!') (6, 'jackerlab_2', 'jackerlab_@') (8, 'jackerlab_1', 'jackerlab_!') (9, 'jackerlab_2', 'jackerlab_@') (11, 'jackerlab_1', 'jackerlab_!') (12, 'jackerlab_2', 'jackerlab_@') (14, 'jackerlab_1', 'jackerlab_!') (15, 'jackerlab_2', 'jackerlab_@')
[Test.5] 추가 쿼리 실행 테스트
예제 1) Tuple 변수를 이용한 select문 실행
... user_email = ('jackerlab_3') with connection.cursor() as cursor: sql = "SELECT `id`, `email`, `password` FROM `users` WHERE `email` = %s" cursor.execute(sql, user_email) result = cursor.fetchall() for i in result: print(i)
조건절의 email 을 user_email 변수로 지정하여 execute() 실행 시, 파라미터를 같이 넘겨주는 테스트입니다.
user_email을 문자열로 지정하였으나 다른 곳에서 받아온 문자열을 처리하도록 응용할 수 있을 것 같습니다.
예제2) Tuple 변수를 이용한 update문 실행
... try: user_email = ('jackerlab_3') with connection.cursor() as cursor: sql = "UPDATE `users` SET `password` = %s WHERE `email` = %s" cursor.execute(sql, ('change_pw', user_email)) connection.commit() with connection.cursor() as cursor: sql = "SELECT `id`, `email`, `password` FROM `users`" cursor.execute(sql) #result = cursor.fetchone() result = cursor.fetchall() for i in result: print(i) finally: connection.close()
예제3) Dintionary Curcor를 이용한 데이터 조회
import pymysql connection = pymysql.connect(host='...', user='...', password='...', db='...', cursorclass=pymysql.cursors.DictCursor) # 2) 방법 try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: # 1) 방법 # Read a single record sql = "SELECT `id`, `email`, `password` FROM `users`" cursor.execute(sql) #result = cursor.fetchone() result = cursor.fetchall() for i in result: print(i['email']) finally: connection.close()
Dictionary 커서를 사용하여 Dictionary의 key(필드명)을 이용하여 원하는 데이터만 조회할 수 있습니다.
위 예제는 데이터베이스로 부터 전체 필드의 데이터를 조회하였지만 실제 출력(print) 할 대는 email 필드만 출력하게 합니다.
Dictionary 커서를 사용하기 위해서는 1)cursor를 생성할 때 DictCursor를 추가하는 방법과 2) 기본 예제에서 처럼 connect()할 때 커서를 추가하는 방법이 있습니다.