[python] pymysql로 INSERT 할 때, 마지막 PK 값에서 1씩 증가시키는 방법

처음만나는 에러

python의 pymysql 라이브러리를 이용해서 MySQL 데이터베이스 내 특정 테이블에 INSERT를 할 때,

아래 예시와 같이, SET~; 문과 INSERT ~ SELECT; [테이블 속성1, 테이블 속성2, ...] 문 2개를 한 번에 실행해서 INSERT를 하는 경우가 있다.

# 데이터베이스 INSERT 쿼리 : GolfMember 테이블에 있는 멤버의 id와 name을 Member 테이블에 추가 (중복시 name 업데이트)
last_id_query = '''
	SET @last_id := (SELECT MAX(id) FROM Member); -- Member 테이블의 마지막 pk 값을 변수로 저장
	INSERT INTO Member (`id`, `name`)
        SELECT @last_id:=@last_id+1 AS `id`, `name` -- 마지막 id 값 이후는 +1씩, name 값을 select
        FROM GolfMember G
	ON DUPLICATE KEY UPDATE -- 중복 발생 시 Member 테이블의 name을 GolfMember 테이블의 name으로 update
        name = G.name;
	'''

# 데이터베이스 커넥션 생성
conn = pymysql.connect(host=DB_HOST, # 데이터베이스의 호스트 주소
                       user=DB_SUPER_USER, # 데이터베이스 계정
                       password=DB_SUPER_PW, # 데이터베이스 비밀번호
                       port=PORT_NUM, # 포트번호
                       db=DB_NAME # 데이터베이스 이름
                       )

# 데이터베이스에 INSERT 쿼리 실행
with conn.cursor(pymysql.cursors.DictCursor) as curs:
    fetch_count = curs.execute(last_id_query)
    conn.commit()

위와 같은 상황에서, pymysql은 에러를 내보내는데, 그 에러는 INSERT ~ SELECT; 문에서 발생하는 에러다.

분명, mysql workbench에서는 저 쿼리가 실행이 됐는데, pymysql에서만 실행이 안된다.

이유와 해결 방법

그 이유는 단순하게도 pymysql에서 .execute() 메서드를 호출하면,

쿼리문 안의 세미콜론(;)을 하나만 인식해서 SET ~; 쿼리만 실행하고 끝내기 때문에 발생하는 에러다.

@last_id 변수는 SET ~; 쿼리를 한 번 실행하면 데이터베이스 내 지역변수로 저장하기 때문에 커넥션 세션이 닫혀도 계속 유지된다.

따라서 해결법은 .execute() 메서드를 각각의 쿼리마다 호출해주면 된다.

해결 코드 전문

first_query = 'SET @last_id := (SELECT MAX(id) FROM Member);'  -- Member 테이블의 마지막 pk 값을 변수로 저장
insert_query = '''
	INSERT INTO Member (`id`, `name`)
        SELECT @last_id:=@last_id+1 AS `id`, `name` -- 마지막 id 값 이후는 +1씩, name 값을 select
        FROM GolfMember G
	ON DUPLICATE KEY UPDATE -- 중복 발생 시 Member 테이블의 name을 GolfMember 테이블의 name으로 update
        name = G.name;
	'''

with conn.cursor(pymysql.cursors.DictCursor) as curs:
    fetch_count += curs.execute(first_query)
    fetch_count += curs.execute(insert_query)
    conn.commit()