Flask: missing prefix when select with sqlalchemy

Question

I was creating an API using Flask and MySQLdb to query data but was advice to use sqlalchemy due to connection pooling. This will be better for my site if high traffic occurred as I do not need to do hard open and close database connection

However, when I transform the working code to sqlalchemy, it does not getting the prefix

    from flask import Flask,jsonify,abort,make_response,request,render_template
    from flask.ext.sqlalchemy import SQLAlchemy
    from sqlalchemy import create_engine

    @app.route('/KLSE/search', methods=['GET'])
    def KLSEsearch():
        engine = create_engine('mysql+mysqldb://xxx:xxx@zzz.mysql.pythonanywhere-services.com/zzz$default')
        conn = engine.raw_connection()
        cur = conn.cursor()
        name = request.args.get('name',default='',type=str)
        volumeMin = request.args.get('volumeMin',default=0,type=float)
        volumeMax = request.args.get('volumeMax',default=0,type=float)
        query = """ SELECT * FROM KLSE WHERE (Stock LIKE %s or Name LIKE %s or Stockcode LIKE %s)
                            AND (Volume_changes_pc BETWEEN (IF (%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) """
        input = (name+"%",name+"%",name+"%",volumeMin,volumeMin,volumeMax,volumeMax)
    try:
        cur.execute(query,(input))
        h = cur.fetchall()
    except Exception:
        return 'Error: unable to fetch items'
      return jsonify({'Stock': h})

Result:

{
  "Stock": [
    [
      11,
      "PRESTAR",
      "PRESTAR RESOURCES BERHAD [S]",
      23.8,
    ],

What I get from previous code with MySQLdb :

    from flask import Flask,jsonify,abort,make_response,request,render_template  
    import MySQLdb
    import MySQLdb.cursors

    @app.route('/KLSE/search', methods=['GET'])
    def KLSEsearch():
        db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='zzz',passwd='xxx',db='zzz$default',cursorclass=MySQLdb.cursors.DictCursor)
        curs = db.cursor()
        name = request.args.get('name',default='',type=str)
        volumeMin = request.args.get('volumeMin',default=0,type=float)
        volumeMax = request.args.get('volumeMax',default=0,type=float)
        query = """ SELECT * FROM KLSE WHERE (Stock LIKE %s or Name LIKE %s or Stockcode LIKE %s)
                            AND (Volume_changes_pc BETWEEN (IF (%s='_',-5000,%s)) AND (IF(%s='_',5000,%s))) """
        input = (name+"%",name+"%",name+"%",volumeMin,volumeMin,volumeMax,volumeMax)
    try:
        curs.execute(query,(input))
        h = curs.fetchall()
    except Exception:
        return 'Error: unable to fetch items'
    finally:
        curs.close()
        db.close()
      return jsonify({'Stock': h})

Result that require and show prefix:

{
  "Stock": [
    {
      "Stock": "PRESTAR",
      "Stockcode": "11",
      "Change_pc": 2.604,
      "Name": "PRESTAR RESOURCES BERHAD [S]",
    },

In the code with sqlalchemy, I do not use cursor close and db close, as sqlalchemy engine will handle the connection pooling, is that correct?

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

I think the issue is that cursor object being serialized by jsonify({'Stock': h}) is different for the SQLAlchemy and MySQLdb cursors.

All the data is still there and can be accessed in multiple ways, so you just have to build a dictionary out of the SQLAlchemy cursor and then jsonify that.

So something like this should work (see link for other examples):

def row2dict(row):
    d = {}
    for column in row.__table__.columns:
        d[column.name] = str(getattr(row, column.name))
    return d

I don' think this has anything to do with connection pooling though.

edited May 23 '17 at 12:07

Community

1
1

answered Jan 10 '17 at 16:45

ACV

1,895
1
19
28

I use sqlalchemy core because it can handle connection pooling. however, is my code is good for high traffic or still sqlalchemy ORM is preferred? – vindex Jan 10 '17 at 23:26
So you are asking two different questions, the first is after how to return your query results as a dictionary instead of a list. The second is about the performance of collection pooling in SQLAlchemy Core vs ORM. Is that correct? – ACV Jan 11 '17 at 16:03

Flask: missing prefix when select with sqlalchemy

1 Answers1