PyMySql

install

pip install pymysql

connect

import os
import pymysql

def connect(self):
  host = os.getenv('DB_HOST')
  user = os.getenv('DB_USER')
  password = os.getenv('DB_PASSWORD')
  db = os.getenv('DB_DATABASE')
  port = int(os.getenv('DB_PORT'))
  # print(host, port, user, password, db)
  return pymysql.connect(host=host, port=port, user=user, password=password, db=db, charset='utf8')

cursor

호출시 column 숫자로 호출

curs = conn.cursor()
curs.execute(sql)
rs = curs.fetchone()
# rs[0], rs[1]...

호출시 column 이름으로 호출

with conn.cursor(pymysql.cursors.DictCursor) as curs:
  curs.execute(sql)
  rs = curs.fetchone()
  # rs['column1'], rs['column2']...

query

select

fetchall()

여러개의 row 데이타를 가져올 때

conn = self.connect()
try:
  with conn.cursor(pymysql.cursors.DictCursor) as curs:
      sql = "select * from tableName where code = %s order by ymd desc limit 0, %s"
      curs.execute(sql, (code, limit))

      rs = curs.fetchall()
      for row in rs:
        print(row['code'])
        
      # return rs
except Exception as e:
    # print('I got a Exception  - reason "%s"' % str(e))
    # print(curs._last_executed)
    # raise

fetchone()

하나의 row 데이타를 가져올 때

sql = "select * from tableName where code = %s"
curs.execute(sql, (code))

rs = curs.fetchone()

fetcmany

n개만큼의 데이터를 가져올 때 사용

fetchmany(n)

insert

conn = self.connect()
try:
  with conn.cursor(pymysql.cursors.DictCursor) as curs:
    sql = "insert into tableName (...) values (%s...)"
    curs.execute(sql, (v1...))
    conn.commit()
except Exception as e:
  # print('I got a Exception  - reason "%s"' % str(e))
  # print(curs._last_executed)
  # raise

update

conn = self.connect()
try:
  with conn.cursor(pymysql.cursors.DictCursor) as curs:
    sql = "update tableName field1=%s,.....  where id=%s"
    curs.execute(sql, (v1...))
    conn.commit()
except Exception as e:
  # print('I got a Exception  - reason "%s"' % str(e))
  # print(curs._last_executed)
  # raise

pandas.DataFrame으로 변환

import pandas as pd
......
rs = curs.fetchall() # mysql에서 데이타를 가져온다.
df = pd.DataFrame(rs)

기타

_executed

실행된 query를 문자열로 디스플레이

print(curs._executed)
# update corporations set recom_cnt=1, concensus_updown='S' where code='396270'

rowcount

실행된 갯수(db에 insert, update, delete)를 표시
업데이트의 경우 기존내용과 같은 경우 0 이 됨

print(curs.rowcount, "record(s) affected")
# 1 record(s) affected

예제

curs = conn.cursor()
..........
sql = "select \
  id, field1, field2, count(id) as cnt \
  from table1  \
  group by id, field1, field2 "
curs.execute(sql)

for row in curs.fetchall():
  updown = self.updown(row[1], row[2])
  sql = 'update table2 set ' \
        'field1=%s, ' \
        'field2=%s ' \
        'where id=%s'
  curs.execute(sql, (row[1], row[2], str(row[0])))
  conn.commit()
  print(curs._executed)
  print(curs.rowcount, "record(s) affected")