Use python to turn mysql into json

I want to use python to turn mysql into json, but when I use the following code, the result is json line by line, not a whole set of json

import pymysql
import json
sql="**"

conn=pymysql.connect(host='localhost',user='root',passwd="abc",db="mydatabase",port=123) 
cur=conn.cursor() 
cur.execute(sql)
data=cur.fetchall()

fields=cur.description
cur.close () 
conn.close()

column_list = []
for i in fields:
    column_list.append(i[0])

for row in data:
    result = {}
    result[column_list[0]] = row[0]
    result[column_list[1]] = row[1]
    result[column_list[2]] = str(row[2])
    result[column_list[3]] = row[3]

    j=json.dumps(result)
    print(j)

Answer

You need to pull out the json.dumps() line from the for loop. This is resulting in conversion of into json with each iteration and printing in each iteration.

Your code should look like this :

import pymysql
import json
sql="**"

conn=pymysql.connect(host='localhost',user='root',passwd="abc",db="mydatabase",port=123) 
cur=conn.cursor() 
cur.execute(sql)
data=cur.fetchall()

fields=cur.description
cur.close () 
conn.close()

column_list = []
for i in fields:
    column_list.append(i[0])
final_resultset = []
for row in data:
    result = {}
    result[column_list[0]] = row[0]
    result[column_list[1]] = row[1]
    result[column_list[2]] = str(row[2])
    result[column_list[3]] = row[3]

    final_resultset.append(result)

j=json.dumps(final_resultset)
print(j)