Python Flask REST API MySQL get cursor keys and values

Question

I have written a little testing code using Python3/Flask/MySQL to get response from MySQL database with SELECT query. But I was unable to parse the row names (keys) according with the values.

import pymongo, json
import pymysql
from flask import Flask, request, jsonify
from flask_limiter import Limiter
from werkzeug.contrib.fixers import ProxyFix
from flask_restful import Resource, Api
from gevent.pywsgi import WSGIServer
#from mongoAuth import auth
from time import gmtime, strftime
import urllib.request
import re

notFound = json.loads('{"ERROR" : "No data found"}')

# Init MongoDB;
#MC = pymongo.MongoClient(auth['host'] + auth['port'])

# Init MySQL; (Database firewalled)
con = pymysql.connect("127.0.0.1","root","","test" )
cursor = con.cursor()

def get_real_ip():
    print (str(request.remote_addr) + ' Client initiated request ->')
    return (request.remote_addr)

# Flask rules
app = Flask(__name__)
app.wsgi_app = ProxyFix(app.wsgi_app, num_proxies=1)
limiter = Limiter(app, key_func=get_real_ip, default_limits=["6/minute"])
app.url_map.strict_slashes = False
api = Api(app, prefix="/apiv1/free")

def checkInvalidChars(value):
    regex = re.compile('[@_!#$%^&*()<>?/\|}{~:,.}{+]')
    if (regex.search(value) == None):
        return 'OK'
    else:
        return 'FAIL'

# http://127.0.0.1:5000/apiv1/free/getDapp
class getDapp(Resource):
    def get(self):
        cursor.execute("select * from dapp LIMIT 10;")
        return jsonify(data=cursor.fetchall())

# Routes
api.add_resource(getDapp, '/getDapp')

# Serve the high performance http server
if __name__ == '__main__':
    http_server = WSGIServer(('', 5000), app)
    http_server.serve_forever()

As you can see the output is only values without keys (aka rows):

{ 
 "data":[ 
  [ 
    1,
    "b127a532-c5d0-4450-bcd2-5e9c9d5e79c6",
    "Ether Tulips",
    "http://ethertulips.com",
    "",
    "",
    "games",
    "eth",
    "approved",
    null,
    0,
    0,
    "Sat, 03 Feb 2018 01:11:15 GMT",
    "Wed, 11 Sep 2019 10:43:01 GMT",
    0,
    "[]",
    0
  ]
 ]
}

Any help how to achieve key/value would be appreciated.

mrEvgenX · Accepted Answer · 2020-01-15 09:34:31Z

2

Try to use DictCursor.

According to documentation of pymysql a method cursor recieves type of cursor should be created.

from pymysql.cursors import DictCursor
cursor = con.cursor(DictCursor)

answered Jan 15, 2020 at 9:34

mrEvgenX

7768 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Python Flask REST API MySQL get cursor keys and values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related