[python] pymysql 에서 쿼리문 여러개 한꺼번에 사용하기

파이썬에서 쿼리문 처리를 하다보면, 여러 다른 종류의 쿼리문을 한꺼번에 사용하고자 할때가 있다.

예를 들면 작업 흐름에 따라 아래와 같이 순차적인 쿼리가 필요한경우가 있다.

 

DROP DATABASE IF EXISTS sql_test;

CREATE DATABASE sql_test;

USE sql_test;

CREATE TABLE test_table(
        id INT PRIMARY KEY AUTO_INCREMENT,
        test_field VARCHAR(250) NOT NULL DEFAULT ''
);


INSERT INTO test_table(test_field) VALUES('test1');
INSERT INTO test_table(test_field) VALUES('test2');
INSERT INTO test_table(test_field) VALUES('test3');

아마 일반적인 경우 pymysql 모듈 선언은 아래와 같다.

import pymysql

conn = {
    "host": "mysql_server_hostname",
    "password": "my_password",
    "port": <<port_no>>,
    "user": "my_username" }

혹은 간편하게 이렇게도 사용한다.

import pymysql

mydb = pymysql.connect(user='root', passwd='haha', host='192.x.x.x', db='testdb', charset='utf8')
cursor = mydb.cursor()

이런 선언인경우, pymysql은 단일 쿼리문만 지원한다.

아마 여러 쿼리문을 넣게 되면 아래와 같은 에러를 만나게 될것이다.

1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update table_name set title = ..... at line 2"

핵심은 멀티쿼리를 지원하는 옵션을 활성화 시키는것이다.

 CLIENT.MULTI_STATEMENTS

선언문을 아래와 같이 추가해보자. 

import pymysql
from pymysql.constants import CLIENT

conn = {
    "host": "mysql_server_hostname",
    "password": "my_password",
    "port": <<port_no>>,
    "user": "my_username",
    "client_flag": CLIENT.MULTI_STATEMENTS
}

혹은

mydb = pymysql.connect(user='root', passwd='haha', host='192.x.x.x', db='testdb', charset='utf8', client_flag=CLIENT.MULTI_STATEMENTS)
cursor = mydb.cursor()

이제 쿼리문 string에 여러개의 쿼리문을 넣어도 한꺼번에 모두 실행되는것을 볼수있다.

각 쿼리문의 끝은 반드시 ' ; ' 으로 구분하도록 하자. 

참고로 아주 긴 쿼리문을 작성한다면, 쿼리문 스트링이 너무 길어져 문자열 관리가 너무 어렵다.

'+'로 이어 붙이거나 ' \ '나 ' \n '을 붙이거나 ( )를 넣어 묶거나 난리가 아닌데다 중간 중간 변수까지 반영해야

하다보면 머리가 아파온다.

 

아래와 같이 따옴표 3개를 맨 위와 아래에 붙이고 그 안에 쿼리문을 넣으면 간편하다.

쿼리분석기 등에서 작성하고 있던 긴 쿼리문을 그냥 붙여넣기만 하면된다.

query = """

<이 공간에 쿼리문을 그대로 붙여넣자>


"""

변수 반영이 필요한경우는 마지막 따옴표 3개 뒤에 .format(변수1, 변수2) 등으로 반영하면 된다.

아래와 같은 모습이 될것이다.

query ="""
CREATE TABLE test_table_{}
select @num := @num+1 as root, X.*
from
(
	select A.*, B.title, B.info, replace(replace(B.linework',''),'/','') from
		(select domain, count(*) as cnt, sum(score) as score_sum
		 from your_project where cross_date = '{}'
		 and is_confirm = 'yes'
	     group by domain ) as A
         inner JOIN table_b B ON A.domain = B.domain
	order by score_sum desc
) X
where ......

이하 쿼리문 100줄 생략

""".format('smith','2022-04-05')

변수는 마지막 format에서 두개를 지정했고, 쿼리문 내에 { } 공간에 변수가 차례대로 들어간다.

위 쿼리는 뭘 하려는 쿼리인지는 모르지만..

어쨌든 첫번째 변수와 두번째 변수가 순차적으로 들어가면

생성되는 테이블 이름은 test_table_smith 가 되고 조건중에 cross_date 는 2022-04-05 조건이 반영된다.