Python을 이용한 SQL Database 다루기

졸업 하기 위한 생존형 코드를 짜던 도중... 데이터를 넣고나니 mysql에 크롤링해서 넣은 데이터 값이 잘못된걸 알았다...

설마 이걸 다시 크롤링 해야하나 막막해 하던 도중에  

팀원의 " 어? 그거 파이썬으로 DB값 가져와서 UPDATE만 시키면 되지않을까요?" 라는 천사의 속삭임을 들었다....  

이렇게 시작된 python으로 데이터베이스 수정

 

1.  mysql을 사용하기위해 pymysql import

# python 에서 mysql사용하기 위해 pymysql import
import pymysql.cursors 

2. database 연동 (db 정보를 conn 객체에 넣음)

# 데이터베이스 연동
conn = pymysql.connect(
    host = 'db_address', # 데이터베이스 주소 ex) 로컬db = localhost 
    user = 'user_name',  # 유저이름
    password = 'password',  # 비밀번호
    db = 'database',  # 사용할 데이터베이스
    charset = 'utf8'  # 인코딩 캐릭터셋
  )

이 작업까지 하게되면 'conn'을 이용해 db에 접근할 수 있다.

3. cursor() 메소드를 사용하여 cursor 객체를 가져오기 

cursor = conn.cursor()

cursor 객체는 excute() 메소드를 이용해 sql문을 DB에 실행시킬수 있다.

sql = "sql문"
# sql문 자체가 이미 완성되어 있을 때
cursor.execute(sql)

# sql = "select * from %s ;" 일 때는 2번째 인자로 %s의 value를 적어줘야한다.
cursor.execute(sql,value)

여기서부터는 sql문법에 맞춰서 python에서 sql문을 작성해주는 코드이다.

# select 문
sql = "select [컬럼명] from [테이블명] where = [조건]; "

# insert 문
sql = "insert into [테이블명(컬럼명)] values [(컬럼값1,컬럼값2,~)];"

# 특정 변수값을 집어넣고 싶다면 
sql = "insert into [테이블명(컬럼명)] values(%d,'%s',~);" %(value_d,value_s)

# update 문
sql = "update [테이블명] set [컬럼명 = 변경할값] where = [조건];"

사실 select,insert, update 말고도 굉장히 많은 sql문법들이 있지만 위의 코드를 이해했다면 sql문법만 안다면 동일하게 적용하면된다.

 

---

나의 경우에는 item 테이블의 stock_quantity 값이 item_option 테이블에서 같은 item_id를 가진 상품들의 모든 재고(quantity)를 합한 값으로 바꿔야했다.

# item_option 테이블에서 item_id가 같은 요소들의 합을 구해서 item.stock_quantity 수량 바꾸기
try:
  for i in range(1,307):
    result = 0
    cursors.execute("select sum(stock_quantity) from item_option where item_id = " + str(i) +";")
    conn.commit()
    item_stock_quantity_tuple = cursors.fetchone()
    
    # 가끔 fetchone()을 했지만 값이 없는 경우에는 None이 반환되어 except문으로 빠져버리기 때문에 이 부분을 해결하고자 함
    if item_stock_quantity_tuple[0] != None:
      # fetchone()의 반환값은 tuple 형태 (값1,0) 형식으로 반환되기 때문에
      # tuple[0]번째 요소가 우리가 찾는 값인 sum(stock_quantity)값 이다.
      sql = 'update item set stock_quantity =  %s where item_id = %s;' %(str(item_stock_quantity),str(i)) # update문
      item_stock_quantity = item_stock_quantity_tuple[0]	
      cursors.execute(sql)
       # ! 중요 ! 이부분을 빼먹게 되면 파이썬은 auto_commit 기능을 지원하지 않기 때문에
       # 꼭!!! commit을 해줘야한다 (본인은 여기서 10분넘게 헤맴...ㅠ)
      conn.commit()
    else:
      continue
   
except:
  print(str(i)+ "번째에서 실패")	# 만약에 실패할 경우 i값을 알아야 그 지점을 수정할 수 있기 때문