In my flask application, a view function has this piece of code:
####################################################
sql_text = text(" \
SELECT CONNECT_BY_ROOT \
part_no as ROOT_PART_NO, \
bc.part_no, \
bc.cmpnt_part_no, \
bc.cmpnt_qty, \
LEVEL AS level_, \
SYS_CONNECT_BY_PATH (cmpnt_part_no, ' /') AS path_ \
FROM bom_nana_combined bc \
START WITH bc.part_no IN ('3358007') CONNECT BY PRIOR bc.part_no =
bc.cmpnt_part_no \
ORDER SIBLINGS BY part_no \
")
sql_result = db.engine.execute(sql_text).fetchall() #This returns
a ResultProxy object.
results = {"data":sql_result,}
resp = make_response(json.dumps(results))
return resp
##########################################################
An ajax call will be made to this method so I'm creating a custom response
object that contains the data that this query returns.
I am getting TypeError: ('3358007', '3358007', '0839527', 2, 1, '
/0839527') is not JSON serializable on the line with json.dumps
This basically means that a row in the ResultProxy object is not json
realizable. I checked the type of the row and found that it is a RowProxy.
My question is, how can I make the Resultproxy json serializable? So it can
look like [{root_part_no: '3358007', cmpnt_part_no:'3358007' part_no:
'0839527', cmpnt_qty:2, level_:1, path_: ' /0839527'}] ?
I did the same/similar thing in another app where I made a custom response
from data that was queried from a reflected table object as below:
##########################################
m = db.MetaData()
t = db.Table(tablename, m, autoload = True, autoload_with = db.engine)
results = db.session.query(t).order_by("id").limit(10).all()
results = {"data":results,}
resp = make_response(json.dumps(results)
resp.headers['Content-type'] = "application/json; charset=utf-8"
return resp
#########################################
I realized that in the above code, results =
db.session.query(t).order_by("id").limit(10).all() returns a Query object;
each row is sqlalchemy.util._collections.result unlike the former where
each row was a sqlalchemy.engine.result.RowProxy object.
I suspect that the rowproxy cannot be json serialized whereas the row in
the Query object (sqlalchemy.util._collections.result) can. What can I do
to make the ResultProxy object json serializable? In my current app, I'm
not querying from a reflected table. As you've seen above, I'm doing
hierarchical query in oracle. that's something that sqlalchemy doesn't
currently implement (if I'm wrong, please let me know). So I'm having to
use the Core and execute raw sql.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
