20201010 (python3: 한글데이타를 mysql에 저장 에러 - 디버깅)

[hjoon0510]

이곳은 프로젝트의 작업 내용들을 기록 및 보관하는 곳입니다. 수행한 작업들을 체계적으로 기록하면 문제들을 효과적으로 해결이 가능하고, 반복되는 실수들을 최소화하는데 큰 도움을 제공합니다.

작업내용

python3으로 한글데이타를 mysql DB에 저장이 안되거나, 해결을 하더라도 한글 정보가 정상적으로 mysql db에 저장되지 않았다.

• 분석1:

    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # print(timestamp)

    # https://stackoverflow.com/questions/6202726/writing-utf-8-string-to-mysql-with-python
    # Open database connection
    db = MySQLdb.connect(cfg.mysql['host'],cfg.mysql['user'],cfg.mysql['passwd'],cfg.mysql['db'],charset='utf8')

    # Prepare a cursor object using cursor() method
    cursor = db.cursor()

• 분석2:

  
  # Prevent broken korean statements
  db.query("set names utf8;")
  db.query("set character_set_connection=utf8;")
  db.query("set character_set_server=utf8;")
  db.query("set character_set_client=utf8;")
  db.query("set character_set_results=utf8;")
  db.query("set character_set_database=utf8;")
  
  # Run a mysql command
  violation=int(len(violate))
  #print (" Current Violations: ", timestamp, int(len(violate)))
  print ("Timestamp: ", timestamp , "Violation:", violation)
  if violation >= 0:
      try:
          sql = """INSERT INTO covid_sd (time, site, location, violation) VALUES (%s, %s, %s, %s) """
          # https://stackoverflow.com/questions/62656579/why-im-getting-unicodeencodeerror-charmap-codec-cant-encode-character-u2
          # for python2
          record_tuple = (timestamp, cfg.covid_sd['site'], cfg.covid_sd['location'], violation)
          # for python3
          #record_tuple = (timestamp, cfg.covid_sd['site'].encode('utf-8').decode('ascii', 'ignore'), cfg.covid_sd['location'].encode('utf-8').decode('ascii', 'ignore'), violation)
          cursor.execute(sql, record_tuple)
          db.commit()
          cursor.close()
          print("Record inserted successfully into a table.")
      except:
          db.rollback()
          print("Failed to insert a record into a table.")
  
      # disconnect from server
      db.close() 

문제원인
=======
* https://stackoverflow.com/questions/6202726/writing-utf-8-string-to-mysql-with-python

해결방법
=======

https://stackoverflow.com/questions/6202726/writing-utf-8-string-to-mysql-with-python

Open database connection

db = MySQLdb.connect(cfg.mysql['host'],cfg.mysql['user'],cfg.mysql['passwd'],cfg.mysql['db'],charset='utf8')

이상.