CODEWITHSUNDEEP
pythonmysqljson
Vincent
Vincent

Reputation: 852

how to get all mysql tuple result and convert to json

I was able to get a single data from a table. but when i'm trying to get all the data on my table, i got only a single row. 

cnn.execute(sql)
        rows = cnn.fetchall()
        column = [t[0] for t in cnn.description]
        for row in rows:
            myjson = {column[0]: row[0], column[1]: row[1], column[2]: row[2], column[3]: row[3], column[4]: row[4], column[5]: row[5], column[6]: row[6], column[7]: row[7], column[8]: row[8], column[9]: row[9], column[10]: row[10], column[11]: row[11], column[12]: row[12], column[13]: row[13], column[14]: row[14], column[15]: row[15], column[16]: row[16], column[17]: row[17], column[18]: row[18], column[19]: row[19], column[20]: row[20]}
            myresult = json.dumps(myjson, indent=3)
            return myresult

Upvotes: 6

Views: 19420

Answers (5)

Khalid Habib
Khalid Habib

Reputation: 1156

#imports 
import collections
import MySQLdb
import json

#connect to database
conn = MySQLdb.connect(host= "localhost", user="root", passwd="abc",  db="mydatabase")

#Fetch rows
sql  = "SELECT * from userstable"
cursor = conn.cursor()
cursor.execute(sql)
data = cursor.fetchall()

#Converting data into json
user_list = []
for row in data :
    d = collections.OrderedDict()
    d['firstName']  = row[1] #name
    d['lastName']   = row[2] #lname
    d['email']      = row[3] #email
    user_list.append(d)

return json.dumps(user_list)


##Result
[{"firstName":"jame","lastName":"king","email":"[email protected]"}]

Upvotes: 2

sumitb.mdi
sumitb.mdi

Reputation: 990

Now, in PyMysql, there is a facility to configure your connection to use the cursorClass which by default generates Dictionary as the output. (And thus works directly when returning in the API result as it gets converted to JSON)

From the documentation of PyMysql: Configure your connection as 

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

result = cursor.fetchone()
        print(result)

Output for this result : 

{'password': 'very-secret', 'id': 1}

Upvotes: 19

hizbul25
hizbul25

Reputation: 3849

Yeah, @metatoaster is right,

Try with this: 

cnn.execute(sql)
    rows = cnn.fetchall()
    column = [t[0] for t in cnn.description]
    for row in rows:
        myjson = {column[0]: row[0], column[1]: row[1], column[2]: row[2], column[3]: row[3], column[4]: row[4], column[5]: row[5], column[6]: row[6], column[7]: row[7], column[8]: row[8], column[9]: row[9], column[10]: row[10], column[11]: row[11], column[12]: row[12], column[13]: row[13], column[14]: row[14], column[15]: row[15], column[16]: row[16], column[17]: row[17], column[18]: row[18], column[19]: row[19], column[20]: row[20]}
        myresult = json.dumps(myjson, indent=3)
    return myresult

Upvotes: 0

alecxe
alecxe

Reputation: 473763

You don't need to specify a "hardcoded" key-value mapping, use zip() instead (or itertools.izip()).

Also, collect the rows in a list and only then dump the results to json:

def dictfetchall(cursor):
    """Returns all rows from a cursor as a list of dicts"""
    desc = cursor.description
    return [dict(itertools.izip([col[0] for col in desc], row)) 
            for row in cursor.fetchall()]

Usage: 

results = dictfetchall(cursor)
json_results = json.dumps(results)

Hope that helps.

Upvotes: 5

metatoaster
metatoaster

Reputation: 18898

Your return statement is inside the for loop, so after a single iteration it will return immediately with the value of myresult.

Upvotes: 1

Related Questions