import mysql.connector
import json
import csv

def flatten_json(y):
    out = {}

    def flatten(x, name=''):
        if type(x) is dict:
            for a in x:
                flatten(x[a], name + a + '_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '_')
                i += 1
        else:
            out[name[:-1]] = x

    flatten(y)
    return out

def connection():
    db = mysql.connector.connect(user='user', password='password',
                                 host='host',
                                 database='db')

    cursor = db.cursor()

    query = ('SELECT id, data FROM table')

    cursor.execute(query)

    columns = cursor.description
    result = [{columns[index][0]:column for index, column in enumerate(value)} for value in cursor.fetchall()]
    return result

temp = connection()
new_temp = []
for ele in temp:
    id = ele["id"]
    to_conv = json.loads(ele["data"])
    to_conv = flatten_json(to_conv)
    to_conv["id"] = id
    new_temp.append(to_conv)


keys = new_temp[0].keys()
with open('output.csv', 'w') as output_file:
    dict_writer = csv.DictWriter(output_file, keys)
    dict_writer.writeheader()
    dict_writer.writerows(new_temp)