Convert mySql query result to json using python

Question

I want to monitor mySql Database for getting update of it's performance so I executed a query show global status; This gives me a list of varibale like Uptime, aborted_connections etc.

But I want to export the result in a json format, can I achieve that using python script?

What I have done till now is,

exported result of query to .csv file .
Trying to convert that csv file to json using the following python code

 import csv
import json
csvfile = open ('./tempp/op.csv','r')
jsonfile = open('./tempp/op.json','w')
fieldnames = ("Variable_name","Value")
reader = csv.DictReader(csvfile,fieldnames)
for row in reader:
  json.dump(row,jsonfile)
  jsonfile.write('\n')

Issues:

The above script is not giving the desired result.It produces json like

{"Value": null, "Variable_name": "Variable_name\tValue"}
{"Value": null, "Variable_name": "Aborted_clients\t0"}
{"Value": null, "Variable_name": "Aborted_connects\t7"}
{"Value": null, "Variable_name": "Binlog_cache_disk_use\t0"}
{"Value": null, "Variable_name": "Binlog_cache_use\t0"}
{"Value": null, "Variable_name": "Binlog_stmt_cache_disk_use\t0"}
{"Value": null, "Variable_name": "Binlog_stmt_cache_use\t0"}

This seems to be a bad approach to first write the result to a file and then read data from it. Is there any better approach to directly convert result from mySql query to json object directly?

Edit based on answers: My op.csv file looks like:

Variable_name   Value
Aborted_clients 0
Aborted_connects    7
Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  31075
Bytes_sent  1891186
Com_admin_commands  445
Com_assign_to_keycache  0
Com_alter_db    0
Com_alter_db_upgrade    0

Hi mate, I think the main issue with your parse failures is that file you're calling "CSV" (comma separated values) is in fact "TSV" (tab separated values). — initialed85
– initialed85, Commented May 18, 2017 at 3:22

initialed85 · Accepted Answer · 2017-05-16 13:42:12Z

2

Give this a try buddy-

import csv
import json

rows = []
with open('test.csv', 'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        rows += [row]

print json.dumps(rows, sort_keys=True, indent=4, separators=(',', ': '))

The arguments in the json.dumps are just so the output is formatted nicely.

So, for an input of the following-

Heading1,Heading2,Heading3
Row1Value1,Row1Value2,Row1Value3
Row2Value1,Row2Value2,Row2Value3
Row3Value1,Row3Value2,Row3Value3

You should get this-

[
    {
        "Heading1": "Row1Value1",
        "Heading2": "Row1Value2",
        "Heading3": "Row1Value3"
    },
    {
        "Heading1": "Row2Value1",
        "Heading2": "Row2Value2",
        "Heading3": "Row2Value3"
    },
    {
        "Heading1": "Row3Value1",
        "Heading2": "Row3Value2",
        "Heading3": "Row3Value3"
    }
]

answered May 16, 2017 at 13:42

initialed85

1846 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

initialed85 Over a year ago

If you want to go to JSON straight from MySQL, you'll need to pull down a MySQL module using PIP and get to the point where you're executing that query with Python- I think it'll return a list of tuples.

Prashant Prabhakar Singh Over a year ago

Thanks, Edward. I have updated my question to show you how my csv file looks like. I am getting { "Variable_name\tValue": "Com_alter_db_upgrade\t0" } as json while I was expecting {Com_alter_db_upgrade: 0}.

Prashant Prabhakar Singh Over a year ago

BTW. I am able to successfully pull down json from mysql query directly. I am posting the script in answers to help future users.

Testing man · Accepted Answer · 2017-05-16 13:07:47Z

1

Pandas is very flexible library for data. You can download it here: LINK

once you have it:

import pandas as pd
from pandas.io import sql
import sqlite3

conn = sqlite3.connect('example.db')
e = pd.read_sql('select * from my_table;',conn)
b = e.to_json()

print(b)

I am working with sqlite3, but it is universal for all sqls i guess.

there is as well function: pd.read_sql_table to read direct from table, however you would need alchemy.

answered May 16, 2017 at 13:07

Testing man

7512 gold badges12 silver badges33 bronze badges

Comments

Prashant Prabhakar Singh · Accepted Answer · 2017-05-17 04:03:10Z

1

Posting solution to my own question to help future visitors of this page:

Use following script to convert the result of mySql query output to JSON and save it to a file.

But before that install mySql connector:

sudo pip install MySQL-python

and the use following script.

import MySQLdb
import json

conn = MySQLdb.connect(host = "localhost", user="root", passwd="your_password_here")

cursor = conn.cursor()
cursor.execute("SHOW GLOBAL STATUS")

rs = cursor.fetchall()

result = dict(rs)

with open('result.json', 'w') as f:
    json.dump(result, f)

answered May 17, 2017 at 4:03

Prashant Prabhakar Singh

1,2004 gold badges16 silver badges35 bronze badges

Collectives™ on Stack Overflow

Convert mySql query result to json using python

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related