mysqlMeltSuggestionForStackoverflowQuestion.py

"""
# http://stackoverflow.com/questions/26248943/mysql-sseparate-value-from-one-column-to-several-rows-and-join-with-other-column

setup the tables:

  create table papers
  (
  	paper_title varchar(10),
  	authors	varchar(10),
  	year integer
  );
  
  insert into papers (paper_title, authors, year) values ('paper1', 'A,B,C', 2010);
  insert into papers (paper_title, authors, year) values ('paper2', 'A,D,E', 2011);
  insert into papers (paper_title, authors, year) values ('paper3', 'F,E', 2012);
  
  create table papers_melted
  (
  	paper_title varchar(10),
  	author varchar(10),
  	year integer
  );
"""

import MySQLdb
import pandas.io.sql as psql
import json

conn = MySQLdb.connect(host = "localhost", user = "root", passwd = "", db = "test")
cursor = conn.cursor()
sql = "select paper_title, authors, year from papers"

df = psql.read_sql(sql, conn)

for row in df.iterrows():
    rownum, rowdata = row
    paper_title, authors, year = rowdata
    for author in authors.split(','):
        sql = """insert into papers_melted (paper_title, author, year) values ({0}, {1}, {2})""".format(json.dumps(paper_title), json.dumps(author), year)
        cursor.execute(sql)

cursor.execute('commit')