쿼리 줄이기

spotify api 를 통해 mysql db에 삽입하려고 한다.

  if artist_raw['name'] == params['q']:

      artist.update(
          {
              'id': artist_raw['id'],
              'name': artist_raw['name'],
              'followers': artist_raw['followers']['total'],
              'popularity': artist_raw['popularity'],
              'url': artist_raw['external_urls']['spotify'],
              'image_url': artist_raw['images'][0]['url']
          }
      )

  query = """
      INSERT INTO artists (id, name, followers, popularity, url, image_url)
      VALUES ('{}', '{}', {}, {}, '{}', '{}')
      ON DUPLICATE KEY UPDATE id='{}', name='{}', followers={}, popularity={}, url='{}', image_url='{}'
  """.format(
          artist['id'],
          artist['name'],
          artist['followers'],
          artist['popularity'],
          artist['url'],
          artist['image_url'],
          artist['id'],
          artist['name'],
          artist['followers'],
          artist['popularity'],
          artist['url'],
          artist['image_url']
  )
  cursor.execute(query)

근데 테이블마다 칼럼별로 데이터를 넣으려고 query를 코딩하는데 너무 길다. 

이것을 다음과 같이 줄이자

 

def insert_row(cursor, data, table):

    placeholders = ', '.join(['%s'] * len(data))
    columns = ', '.join(data.keys())
    key_placeholders = ', '.join(['{0}=%s'.format(k) for k in data.keys()])
    sql = "INSERT INTO %s ( %s ) VALUES ( %s ) ON DUPLICATE KEY UPDATE %s" % (table, columns, placeholders, key_placeholders)
    cursor.execute(sql, list(data.values())*2)
    
    
if artist_raw['name'] == params['q']:

    artist.update(
        {
            'id': artist_raw['id'],
            'name': artist_raw['name'],
            'followers': artist_raw['followers']['total'],
            'popularity': artist_raw['popularity'],
            'url': artist_raw['external_urls']['spotify'],
            'image_url': artist_raw['images'][0]['url']
        }
    )

insert_row(cursor, artist, 'artists')