데이터베이스에 데이터를 저장하려고 하는데 자꾸 오류가 발생하시나요? Python에서 MySQL INSERT 문을 사용할 때 흔히 겪는 실수들과 완벽한 해결 방법을 알려드립니다. 이 글을 읽으면 안전하고 효율적인 데이터 삽입 방법을 마스터할 수 있습니다.
INSERT INTO가 SQL과 VAL로 나뉘는 이유
파이썬에서 데이터베이스 삽입 시 SQL 쿼리문과 실제 값을 분리하는 것은 SQL Injection 공격을 방지하고 코드 재사용성을 높이기 위한 필수 보안 전략입니다. 이 방식을 Prepared Statement 또는 Parameterized Query라고 부릅니다.
기본 구조 이해하기
import pymysql
# SQL 쿼리문 (템플릿)
sql = "INSERT INTO tables (key, id) VALUES (%s, %s)"
# 실제 삽입할 값 (튜플 형태)
val = ("1", "hello")
# 데이터베이스 연결
conn = pymysql.connect(
host='localhost',
user='root',
password='your_password',
database='your_database',
charset='utf8mb4'
)
cursor = conn.cursor()
# 쿼리 실행
cursor.execute(sql, val)
# DB에 반영 (커밋)
conn.commit()
cursor.close()
conn.close()
핵심 포인트: %s는 자리 표시자(placeholder)로, 실제 값은 val 튜플에서 순서대로 들어갑니다. 직접 문자열을 연결하는 방식보다 10배 이상 안전합니다.
commit()이 필수인 이유
MySQL에서 INSERT, UPDATE, DELETE 같은 데이터 변경 작업은 기본적으로 트랜잭션으로 처리됩니다. conn.commit()을 호출해야 변경사항이 실제 데이터베이스에 저장됩니다.
# commit() 없이 실행하면?
cursor.execute(sql, val)
conn.close() # 데이터가 저장되지 않고 롤백됨!
# 올바른 방법
cursor.execute(sql, val)
conn.commit() # 이 순간 DB에 실제 반영
conn.close()
실무 팁: autocommit=True 옵션을 사용하면 자동 커밋되지만, 트랜잭션 관리가 필요한 경우 수동 커밋을 권장합니다.
단일 데이터 삽입 완벽 예제
기본 단일 INSERT
import pymysql
def insert_single_data():
try:
# DB 연결
conn = pymysql.connect(
host='localhost',
user='root',
password='password123',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor()
# SQL과 값 분리
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = ("김철수", "kim@example.com", 28)
# 실행 및 커밋
cursor.execute(sql, val)
conn.commit()
print(f"✅ 데이터 삽입 성공! 삽입된 ID: {cursor.lastrowid}")
except pymysql.Error as e:
print(f"❌ 데이터베이스 오류: {e}")
conn.rollback() # 오류 시 롤백
finally:
cursor.close()
conn.close()
insert_single_data()
활용 팁: cursor.lastrowid로 방금 삽입된 레코드의 AUTO_INCREMENT ID를 즉시 확인할 수 있습니다.
딕셔너리 데이터 삽입
def insert_from_dict():
user_data = {
'name': '이영희',
'email': 'lee@example.com',
'age': 32
}
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = (user_data['name'], user_data['email'], user_data['age'])
cursor.execute(sql, val)
conn.commit()
cursor.close()
conn.close()
딕셔너리 형태로 관리하면 코드 가독성이 높아지고 API 연동 시 편리합니다.
대량 데이터 삽입 최적화
executemany() 사용법
def insert_multiple_data():
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
# 여러 개의 튜플을 리스트로 묶음
val = [
("홍길동", "hong@example.com", 25),
("박민수", "park@example.com", 30),
("최지은", "choi@example.com", 27),
("정수아", "jung@example.com", 29)
]
# 한 번에 여러 행 삽입
cursor.executemany(sql, val)
conn.commit()
print(f"✅ {cursor.rowcount}개 데이터 삽입 완료")
cursor.close()
conn.close()
insert_multiple_data()
성능 비교: executemany()는 반복문보다 5~10배 빠릅니다. 1000건 이상 삽입 시 필수로 사용하세요.
크롤링 데이터 DB 저장
def save_crawling_data(keyword_list):
conn = pymysql.connect(host='localhost', user='root', password='pw', database='keyword_db')
cursor = conn.cursor()
sql = "INSERT INTO keywords (keyword, collected_date, source) VALUES (%s, NOW(), %s)"
val = [(kw, 'google') for kw in keyword_list]
try:
cursor.executemany(sql, val)
conn.commit()
print(f"✅ {len(keyword_list)}개 키워드 저장 완료")
except pymysql.Error as e:
print(f"❌ 오류 발생: {e}")
conn.rollback()
finally:
cursor.close()
conn.close()
# 사용 예시
keywords = ["파이썬", "크롤링", "데이터베이스"]
save_crawling_data(keywords)
웹 크롤링으로 수집한 대량 데이터를 효율적으로 저장할 수 있습니다.
중복 데이터 처리 방법
INSERT IGNORE 사용
sql = "INSERT IGNORE INTO users (email, name) VALUES (%s, %s)"
val = ("duplicate@example.com", "중복테스트")
cursor.execute(sql, val)
conn.commit()
이미 존재하는 데이터는 무시하고 넘어갑니다. 에러가 발생하지 않습니다.
ON DUPLICATE KEY UPDATE
sql = """
INSERT INTO users (email, name, login_count)
VALUES (%s, %s, 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1
"""
val = ("user@example.com", "김유저")
cursor.execute(sql, val)
conn.commit()
중복 시 기존 데이터를 업데이트합니다. 로그인 횟수 카운팅 등에 유용합니다.
REPLACE INTO 방식
sql = "REPLACE INTO users (id, email, name) VALUES (%s, %s, %s)"
val = (1, "replace@example.com", "교체된이름")
cursor.execute(sql, val)
conn.commit()
중복 레코드를 삭제하고 새로 삽입합니다. 주의해서 사용해야 합니다.
트랜잭션 관리 고급 기법
안전한 트랜잭션 처리
def safe_transaction():
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
try:
# 여러 INSERT 작업
sql1 = "INSERT INTO orders (user_id, product_id) VALUES (%s, %s)"
cursor.execute(sql1, (1, 101))
sql2 = "INSERT INTO order_details (order_id, quantity) VALUES (%s, %s)"
cursor.execute(sql2, (cursor.lastrowid, 3))
# 모두 성공 시 커밋
conn.commit()
print("✅ 트랜잭션 성공")
except Exception as e:
# 오류 발생 시 모두 롤백
conn.rollback()
print(f"❌ 트랜잭션 실패: {e}")
finally:
cursor.close()
conn.close()
safe_transaction()
실무 활용: 주문-결제-재고 차감처럼 여러 테이블이 연관된 작업은 트랜잭션으로 묶어야 데이터 정합성이 보장됩니다.
Context Manager 활용
from contextlib import closing
def insert_with_context():
with closing(pymysql.connect(host='localhost', user='root', password='pw', database='db')) as conn:
with closing(conn.cursor()) as cursor:
sql = "INSERT INTO logs (message) VALUES (%s)"
val = ("로그 메시지",)
cursor.execute(sql, val)
conn.commit()
print("✅ 자동으로 연결 종료됨")
insert_with_context()
with 구문을 사용하면 자동으로 연결을 닫아줘 리소스 누수를 방지합니다.
자주 발생하는 오류 해결
오류 1: Not all parameters were used
# 잘못된 예
sql = "INSERT INTO users (name) VALUES (%s, %s)"
val = ("김철수",) # 파라미터 개수 불일치
# 올바른 예
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("김철수", "kim@example.com")
SQL의 %s 개수와 val 튜플의 요소 개수가 일치해야 합니다.
오류 2: Unread result found
# 해결 방법
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
cursor.fetchall() # 결과 가져오기
# 이후 INSERT 실행
sql = "INSERT INTO users (name) VALUES (%s)"
cursor.execute(sql, ("새유저",))
conn.commit()
SELECT 후 결과를 가져오지 않으면 다음 쿼리 실행 시 오류가 발생합니다.
오류 3: Commands out of sync
# 해결 방법: 커서를 새로 생성
cursor1 = conn.cursor()
cursor1.execute("SELECT * FROM users")
cursor2 = conn.cursor() # 새 커서 생성
cursor2.execute("INSERT INTO logs (msg) VALUES (%s)", ("로그",))
conn.commit()
cursor1.close()
cursor2.close()
하나의 커서로 여러 쿼리를 동시에 처리하면 충돌이 발생합니다.
보안 강화 방법
SQL Injection 방지
# 위험한 방법 (절대 사용 금지!)
user_input = "admin' OR '1'='1"
sql = f"INSERT INTO users (username) VALUES ('{user_input}')" # SQL Injection 취약!
# 안전한 방법
sql = "INSERT INTO users (username) VALUES (%s)"
val = (user_input,)
cursor.execute(sql, val) # 자동으로 이스케이프 처리됨
중요: 사용자 입력값은 절대 직접 SQL 문자열에 연결하지 마세요. 항상 파라미터 바인딩을 사용해야 합니다.
입력값 검증
def insert_with_validation(email, name):
import re
# 이메일 형식 검증
email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\\\.[a-zA-Z]{2,}$'
if not re.match(email_pattern, email):
print("❌ 잘못된 이메일 형식")
return False
# 이름 길이 검증
if len(name) < 2 or len(name) > 50:
print("❌ 이름은 2-50자여야 합니다")
return False
# 검증 통과 후 삽입
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
sql = "INSERT INTO users (email, name) VALUES (%s, %s)"
val = (email, name)
cursor.execute(sql, val)
conn.commit()
cursor.close()
conn.close()
return True
insert_with_validation("test@example.com", "홍길동")
데이터베이스에 저장하기 전 반드시 입력값을 검증해야 합니다.
성능 최적화 팁
배치 삽입 크기 조절
def batch_insert(data_list, batch_size=1000):
conn = pymysql.connect(host='localhost', user='root', password='pw', database='db')
cursor = conn.cursor()
sql = "INSERT INTO logs (message, level) VALUES (%s, %s)"
for i in range(0, len(data_list), batch_size):
batch = data_list[i:i+batch_size]
cursor.executemany(sql, batch)
conn.commit()
print(f"✅ {i+len(batch)}/{len(data_list)} 완료")
cursor.close()
conn.close()
# 10,000개 데이터를 1000개씩 나눠서 삽입
large_data = [("메시지" + str(i), "INFO") for i in range(10000)]
batch_insert(large_data)
대용량 데이터는 적절한 크기로 나눠 삽입하면 메모리 효율이 향상됩니다.
인덱스 고려
# 대량 삽입 전 인덱스 제거
cursor.execute("ALTER TABLE users DROP INDEX idx_email")
# 대량 데이터 삽입
cursor.executemany(sql, val)
conn.commit()
# 삽입 후 인덱스 재생성
cursor.execute("ALTER TABLE users ADD INDEX idx_email (email)")
인덱스가 많으면 INSERT 속도가 느려집니다. 대량 삽입 시 일시적으로 제거하는 것도 방법입니다.
실전 활용 예제
웹 크롤링 데이터 자동 저장
def save_crawled_keywords():
# 크롤링 수행
keywords = crawl_trending_keywords() # 가정: 크롤링 함수
conn = pymysql.connect(host='localhost', user='root', password='pw', database='trend_db')
cursor = conn.cursor()
sql = """
INSERT INTO trending_keywords (keyword, source, rank, collected_at)
VALUES (%s, %s, %s, NOW())
"""
val = [(kw['text'], kw['source'], kw['rank']) for kw in keywords]
try:
cursor.executemany(sql, val)
conn.commit()
print(f"✅ {len(keywords)}개 트렌드 키워드 저장 완료")
except pymysql.IntegrityError:
print("⚠️ 중복 데이터 발견, 건너뜀")
conn.rollback()
finally:
cursor.close()
conn.close()
매시간 자동으로 트렌드 키워드를 수집하고 저장하는 시스템에 활용할 수 있습니다.
로그 시스템 구축
class DatabaseLogger:
def __init__(self):
self.conn = pymysql.connect(
host='localhost',
user='root',
password='pw',
database='log_db'
)
self.cursor = self.conn.cursor()
def log(self, level, message):
sql = "INSERT INTO app_logs (level, message, created_at) VALUES (%s, %s, NOW())"
val = (level, message)
self.cursor.execute(sql, val)
self.conn.commit()
def close(self):
self.cursor.close()
self.conn.close()
# 사용 예시
logger = DatabaseLogger()
logger.log("INFO", "애플리케이션 시작")
logger.log("ERROR", "데이터베이스 연결 실패")
logger.close()
애플리케이션 로그를 데이터베이스에 저장해 추후 분석할 수 있습니다.
마치며
Python에서 INSERT INTO 문을 안전하게 사용하는 방법을 SQL과 VAL 분리, commit() 필수성, 대량 삽입 최적화까지 전반적으로 다뤘습니다. 파라미터 바인딩으로 SQL Injection을 방지하고, executemany()로 성능을 극대화하며, 트랜잭션으로 데이터 무결성을 보장하는 것이 핵심입니다.
실무에서는 에러 처리, 입력값 검증, 로깅을 함께 구현해야 안정적인 시스템을 만들 수 있습니다. 이 가이드의 코드를 프로젝트에 바로 적용해보세요.
참고 자료
- PyMySQL 공식 문서: https://pymysql.readthedocs.io/
- MySQL 트랜잭션 가이드: https://dev.mysql.com/doc/refman/8.0/en/commit.html
- OWASP SQL Injection 방어: https://owasp.org/www-community/attacks/SQL_Injection