cscw_2021_sponsor/import_questionnaire_results_from_csv.py

# aim: import all the responses from CSV to mysql database
# author: zhangxunhui
# date: 2021-07-10

import pandas as pd
from pandas.core.algorithms import isin

from utils import *
import yaml, math

f = open('config.yaml', 'r')
config = yaml.load(f.read(), Loader=yaml.BaseLoader)
conn = connectMysqlDB(config, autocommit = True)
cur = conn.cursor(pymysql.cursors.DictCursor)


def insert_dict(mydict, insert_table):
    placeholders = ', '.join(['%s']* len(mydict))
    columns = ', '.join(mydict.keys())
    insert_sql =  "INSERT INTO %s ( %s ) VALUES ( %s )" % (insert_table, columns, placeholders)
    cur.execute(insert_sql, list(mydict.values()))

# read login and email relationship
email_login_dict = {}
cur.execute("select login, email from middle_data_questionnaire_users")
items = cur.fetchall()
for item in items:
    email_login_dict[item['email'].lower()] = item['login']

'''
file_path - the relative path of csv file
which_round - which round is the questionnaire
which_type - maintainer, sponsor or others
'''
def import_one_file(file_path, which_round, which_type):

    # read csv file
    content = pd.read_csv(file_path, header=0, encoding="UTF-8")
    colns = content.columns.tolist()

    # 单选在一个表中，多选单独创建表
    if which_type == "maintainer" and which_round == 2:
        db_col_dict = {
            # second round maintainers
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'How satisfied are you with the income from sponsors?': 'satisfaction',
            'To what extent can sponsorship motivate you?': 'motivate_extent',
            'Why do you set up your Sponsors in your user account?': ["set_up_reasons"], # list表示应创建一个新表存储这些结果，list[0]表示该表的后缀
            'In which way do you think you can get more sponsorship?': ["how_get_more"],
            'What are the shortcomings of sponsor mechanism?': ["shortcomings"],
            'Do you have anything else to tell us about sponsor mechanism?': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系
    elif which_type == "sponsor" and which_round == 2:
        db_col_dict = {
            # second round sponsors
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'Why do you want to sponsor a developer?': ["sponsor_reasons"],
            'As a sponsor, to what extent does your sponsorship meet your expectations?': 'expectation_extent',
            'What kind of developer do you prefer to sponsor?': ["sponsor_kinds"],
            'What are the shortcomings of sponsor mechanism?': ["shortcomings"],
            'Do you have anything else to tell us about sponsor mechanism?': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系
    elif which_type == "others" and which_round == 2:
        db_col_dict = {
            # second round others
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'Why not set up GitHub Sponsors for your user account？': ["not_set_reasons"],
            'Do you have anything else to tell us about Sponsor mechanism': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系
    elif which_type == "maintainer" and which_round == 1:
        db_col_dict = {
            # second round maintainers
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'How satisfied are you with the income from sponsors?': 'satisfaction',
            'To what extent can sponsorship motivate you?': 'motivate_extent',
            'Why do you want to set up your Sponsors in your user account?': "set_up_reasons",
            'In which way do you think you can get more sponsorship?': "how_get_more",
            'What are the shortages of sponsor mechanism?': "shortcomings",
            'In which way do you think can improve sponsor mechanism?': "improve_ways",
            'Do you have anything else to tell us about sponsor mechanism?': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系

    elif which_type == "sponsor" and which_round == 1:
        db_col_dict = {
            # second round maintainers
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'Why do you want to sponsor others?': 'sponsor_reasons',
            'As a sponsor, to what extent does your sponsorship meet your expectations?': 'expectation_extent',
            'What kind of developer do you prefer to sponsor?': "sponsor_kinds",
            'What are the shortages of sponsor mechanism?': "shortcomings",
            'In which way do you think can improve sponsor mechanism?': "improve_ways",
            'Do you have anything else to tell us about sponsor mechanism?': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系

    elif which_type == "others" and which_round == 1:
        db_col_dict = {
            # second round maintainers
            'Respondent ID': 'respondent_id',
            'Collector ID': 'collector_id',
            'Start Date': 'start_date',
            'End Date': 'end_date',
            'IP Address': 'ip_address',
            'Email Address': 'email_address',
            'How would you best describe yourself?': 'self_description',
            'How many years of OSS development experience do you have?': 'experience',
            'Why not set up GitHub Sponsors for your user account？': 'not_set_reasons',
            'If you know sponsor mechanism, what shortages do you think it has?': 'shortcomings',
            'If you know sponsor mechanism, in which way do you think it can be improved?': "improve_ways",
            'If you know sponsor mechanism, do you have anything else to tell us about it?': 'else_to_tell'
        } # 构建csv文件中col名字与mysqldb中列名的对应关系

    col_name_dict = {} # 用于存储列名与具体问题的对应关系
    for i in range(len(colns)):
        coln = colns[i]
        if coln.startswith("Unnamed:"):
            for j in range(i-1, -1, -1): # 向前找到最近的一个非unnamed列
                if colns[j].startswith("Unnamed:") == False:
                    real_name = colns[j]
                    break
            col_name_dict[coln] = real_name
    
    # truncate related tables
    if which_type == "maintainer" and which_round == 2:
        cur.execute("truncate table questionnaire_results_maintainer_2_basic")
        cur.execute("truncate table questionnaire_results_maintainer_2_how_get_more")
        cur.execute("truncate table questionnaire_results_maintainer_2_set_up_reasons")
        cur.execute("truncate table questionnaire_results_maintainer_2_shortcomings")
    elif which_type == "sponsor" and which_round == 2:
        cur.execute("truncate table questionnaire_results_sponsor_2_basic")
        cur.execute("truncate table questionnaire_results_sponsor_2_sponsor_kinds")
        cur.execute("truncate table questionnaire_results_sponsor_2_sponsor_reasons")
        cur.execute("truncate table questionnaire_results_sponsor_2_shortcomings")
    elif which_type == "others" and which_round == 2:
        cur.execute("truncate table questionnaire_results_others_2_basic")
        cur.execute("truncate table questionnaire_results_others_2_not_set_reasons")
    elif which_type == "maintainer" and which_round == 1:
        cur.execute("truncate table questionnaire_results_maintainer_1_basic")
    elif which_type == "sponsor" and which_round == 1:
        cur.execute("truncate table questionnaire_results_sponsor_1_basic")
    elif which_type == "others" and which_round == 1:
        cur.execute("truncate table questionnaire_results_others_1_basic")


    # extract results from csv file
    row_2 = content.iloc[0]
    for index, row in content.iterrows():
        if index == 0:
            continue # 不需要处理第一行 （不是数据）
        result = {} # key: db colname, value: list or string
        for coln in colns:
            real_coln = coln
            value = row[coln]
            if coln.startswith("Unnamed:") and isinstance(value, str):
                real_coln = col_name_dict[coln]
                # 判断是否为单选题
                if real_coln not in db_col_dict:
                    continue
                if isinstance(db_col_dict[real_coln], list) == True:
                    # 表示是多选题，需要将结果存储到新表中，且以list形式暂存在内存中（需要判断第二行对应列是不是Other，如果是需要加上这个前缀）
                    if isinstance(row_2[coln], str) and row_2[coln].startswith("Other"):
                        value = "Other (please specify): " + value
                    result.setdefault(db_col_dict[real_coln][0], [])
                    result[db_col_dict[real_coln][0]].append(value)
                else:
                    # 表示是单选题，需要将Other specify的内容与选项连接在一起
                    result[db_col_dict[real_coln]] = "Other (please specify): " + value
            elif isinstance(value, str):
                
                if db_col_dict[real_coln] == 'start_date' or db_col_dict[real_coln] == 'end_date':
                    tmp = value
                    tmps = tmp.split(" ")
                    date = tmps[0].split("/")
                    month = date[0]
                    day = date[1]
                    year = date[2]
                    tm = tmps[1].split(":")
                    hour = tm[0]
                    minute = tm[1]
                    second = tm[2]
                    if tmps[2] == "PM":
                        hour = str(int(hour) + 12)
                    if int(hour) >= 24:
                        hour = str(int(hour) - 12)
                    value = year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second

                if isinstance(db_col_dict[real_coln], list) == True:
                    result.setdefault(db_col_dict[real_coln][0], [])
                    result[db_col_dict[real_coln][0]].append(value)
                else:
                    result[db_col_dict[real_coln]] = value
            elif isinstance(value, float) and math.isnan(value) == False:
                if isinstance(db_col_dict[real_coln], list) == True:
                    result.setdefault(db_col_dict[real_coln][0], [])
                    result[db_col_dict[real_coln][0]].append(int(value))
                else:
                    result[db_col_dict[real_coln]] = int(value)

        # insert into database
        # 1. basic information
        basic_dict = {}
        for n in db_col_dict.values():
            if isinstance(n, str):
                if n not in result:
                    result[n] = None
                basic_dict[n] = result[n]
        if basic_dict['email_address'] is None:
            if basic_dict['respondent_id'] == 12786967949:
                basic_dict['email_address'] = "VladimirRybas@gmail.com"
            elif basic_dict['respondent_id'] == 12781592617:
                basic_dict['email_address'] = "hdeep2@illinois.edu"
        basic_dict['login'] = email_login_dict[basic_dict['email_address'].lower()]
        insert_dict(basic_dict, "questionnaire_results_" + str(which_type) + "_" + str(which_round) + "_basic")

        # 2. multi result tables
        table_names = []
        key_names = []
        for n in db_col_dict.values():
            if isinstance(n, list):
                table_names.append("questionnaire_results_" + str(which_type) + "_" + str(which_round) + "_" + n[0])
                key_names.append(n[0])
        for i in range(len(table_names)):
            table_name = table_names[i]
            key_name = key_names[i]
            
            if key_name not in result or len(result[key_name]) == 0:
                result_dict = {"login": email_login_dict[basic_dict['email_address'].lower()], "value": None}
                insert_dict(result_dict, table_name)
            else:
                for v in result[key_name]:
                    result_dict = {"login": email_login_dict[basic_dict['email_address'].lower()], "value": v}
                    insert_dict(result_dict, table_name)
    

    print("finish")


files = [
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\second round\\results\\csv\\2021-07-13\\maintainer.csv", 2, "maintainer"),
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\second round\\results\\csv\\2021-07-13\\sponsor.csv", 2, "sponsor"),
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\second round\\results\\csv\\2021-07-13\\others.csv", 2, "others"),
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\first round\\results\\csv\\2021-07-10\\maintainer.csv", 1, "maintainer"),
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\first round\\results\\csv\\2021-07-10\\sponsor.csv", 1, "sponsor"),
    ("C:\\Users\\zhang\\Documents\\Trustie-on-blockchain\\papers\\CSCW-2021-sponsor-assist\\questionnaire\\first round\\results\\csv\\2021-07-10\\others.csv", 1, "others")
]

for f in files:
    import_one_file(f[0], f[1], f[2])


# code the textual comments
# author: zxh
# date: 2021-07-13

cur.execute("truncate table questionnaire_comment_code")

# maintainer comments

# 1. questionnaire_results_maintainer_1_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_maintainer_1_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "self_description", item["login"], item["self_description"], None))

# 2. questionnaire_results_maintainer_1_basic, set_up_reasons
cur.execute("select id, login, set_up_reasons from questionnaire_results_maintainer_1_basic where set_up_reasons is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "set_up_reasons", item["login"], item["set_up_reasons"], None))


# 3. questionnaire_results_maintainer_1_basic, how_get_more
cur.execute("select id, login, how_get_more from questionnaire_results_maintainer_1_basic where how_get_more is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "how_get_more", item["login"], item["how_get_more"], None))

# 4. questionnaire_results_maintainer_1_basic, shortcomings
cur.execute("select id, login, shortcomings from questionnaire_results_maintainer_1_basic where shortcomings is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "shortcomings", item["login"], item["shortcomings"], None))

# 5. questionnaire_results_maintainer_1_basic, improve_ways
cur.execute("select id, login, improve_ways from questionnaire_results_maintainer_1_basic where improve_ways is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "improve_ways", item["login"], item["improve_ways"], None))

# 5. questionnaire_results_maintainer_1_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_maintainer_1_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_1_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))


# 6. questionnaire_results_maintainer_2_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_maintainer_2_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_2_basic", item["id"], "self_description", item["login"], item["self_description"], None))


# 7. questionnaire_results_maintainer_2_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_maintainer_2_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_2_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))

# 8. questionnaire_results_maintainer_2_how_get_more, value
cur.execute("select id, login, value from questionnaire_results_maintainer_2_how_get_more where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_2_how_get_more", item["id"], "value", item["login"], item["value"], None))

# 9. questionnaire_results_maintainer_2_set_up_reasons, value
cur.execute("select id, login, value from questionnaire_results_maintainer_2_set_up_reasons where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_2_set_up_reasons", item["id"], "value", item["login"], item["value"], None))

# 10. questionnaire_results_maintainer_2_shortcomings, value
cur.execute("select id, login, value from questionnaire_results_maintainer_2_shortcomings where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_maintainer_2_shortcomings", item["id"], "value", item["login"], item["value"], None))


# sponsor comments

# 1. questionnaire_results_sponsor_1_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_sponsor_1_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "self_description", item["login"], item["self_description"], None))

# 2. questionnaire_results_sponsor_1_basic, sponsor_reasons
cur.execute("select id, login, sponsor_reasons from questionnaire_results_sponsor_1_basic where sponsor_reasons is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "sponsor_reasons", item["login"], item["sponsor_reasons"], None))


# 3. questionnaire_results_sponsor_1_basic, sponsor_kinds
cur.execute("select id, login, sponsor_kinds from questionnaire_results_sponsor_1_basic where sponsor_kinds is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "sponsor_kinds", item["login"], item["sponsor_kinds"], None))

# 4. questionnaire_results_sponsor_1_basic, shortcomings
cur.execute("select id, login, shortcomings from questionnaire_results_sponsor_1_basic where shortcomings is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "shortcomings", item["login"], item["shortcomings"], None))

# 5. questionnaire_results_sponsor_1_basic, improve_ways
cur.execute("select id, login, improve_ways from questionnaire_results_sponsor_1_basic where improve_ways is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "improve_ways", item["login"], item["improve_ways"], None))

# 5. questionnaire_results_sponsor_1_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_sponsor_1_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_1_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))


# 6. questionnaire_results_sponsor_2_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_sponsor_2_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_2_basic", item["id"], "self_description", item["login"], item["self_description"], None))


# 7. questionnaire_results_sponsor_2_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_sponsor_2_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_2_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))


# 8. questionnaire_results_sponsor_2_sponsor_kinds, value
cur.execute("select id, login, value from questionnaire_results_sponsor_2_sponsor_kinds where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_2_sponsor_kinds", item["id"], "value", item["login"], item["value"], None))

# 9. questionnaire_results_sponsor_2_sponsor_reasons, value
cur.execute("select id, login, value from questionnaire_results_sponsor_2_sponsor_reasons where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_2_sponsor_reasons", item["id"], "value", item["login"], item["value"], None))

# 10. questionnaire_results_sponsor_2_shortcomings, value
cur.execute("select id, login, value from questionnaire_results_sponsor_2_shortcomings where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_sponsor_2_shortcomings", item["id"], "value", item["login"], item["value"], None))



# other comments

# 1. questionnaire_results_others_1_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_others_1_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_1_basic", item["id"], "self_description", item["login"], item["self_description"], None))

# 2. questionnaire_results_others_1_basic, not_set_reasons
cur.execute("select id, login, not_set_reasons from questionnaire_results_others_1_basic where not_set_reasons is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_1_basic", item["id"], "not_set_reasons", item["login"], item["not_set_reasons"], None))

# 3. questionnaire_results_others_1_basic, shortcomings
cur.execute("select id, login, shortcomings from questionnaire_results_others_1_basic where shortcomings is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_1_basic", item["id"], "shortcomings", item["login"], item["shortcomings"], None))

# 4. questionnaire_results_others_1_basic, improve_ways
cur.execute("select id, login, improve_ways from questionnaire_results_others_1_basic where improve_ways is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_1_basic", item["id"], "improve_ways", item["login"], item["improve_ways"], None))

# 5. questionnaire_results_others_1_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_others_1_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_1_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))


# 6. questionnaire_results_others_2_basic, self_description
cur.execute("select id, login, self_description from questionnaire_results_others_2_basic where self_description like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_2_basic", item["id"], "self_description", item["login"], item["self_description"], None))


# 7. questionnaire_results_others_2_basic, else_to_tell
cur.execute("select id, login, else_to_tell from questionnaire_results_others_2_basic where else_to_tell is not null")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_2_basic", item["id"], "else_to_tell", item["login"], item["else_to_tell"], None))


# 8. questionnaire_results_others_2_not_set_reasons, value
cur.execute("select id, login, value from questionnaire_results_others_2_not_set_reasons where value like 'Other (please specify)%'")
items = cur.fetchall()
for item in items:
    cur.execute("insert into questionnaire_comment_code (table_name, table_id, column_name, login, comment, code) values (%s, %s, %s, %s, %s, %s)", ("questionnaire_results_others_2_not_set_reasons", item["id"], "value", item["login"], item["value"], None))


# code the comments
count_maintainer = 0
count_sponsor = 0
count_others = 0
cur.execute("select id, table_name from questionnaire_comment_code order by id asc")
items = cur.fetchall()
for item in items:
    id = item["id"]
    table_name = item["table_name"]
    if "maintainer" in table_name:
        count_maintainer += 1
        cur.execute("update questionnaire_comment_code set code=%s where id=%s", ("MC" + str(count_maintainer), id))
    elif "sponsor" in table_name:
        count_sponsor += 1
        cur.execute("update questionnaire_comment_code set code=%s where id=%s", ("SC" + str(count_sponsor), id))
    elif "other" in table_name:
        count_others += 1
        cur.execute("update questionnaire_comment_code set code=%s where id=%s", ("OC" + str(count_others), id))
    else:
        print("error: pause")

print("finish")