pyodbc result set has data type embedded

Question

I am querying Teradata using pyodbc and flask with the intention of charting the data using d3.

The result set has two columns, one is a decimal and the other an integer. When I pass the results to my html page and log the output I am getting something along these lines:

[(Decimal(& #39;-16.200000000& #39;), 5), (Decimal(& #39;-23.100000000& #39;), 12), (Decimal(& #39;500.300000000& #39;), 5)]. 

The embedded data type information is making it difficult to do anything with the result set. How can I get output to look like this instead?

[[-16.200000000, 5], [-23.100000000, 12], [500.300000000, 5]]

In other words I just want an array of arrays.

Steps I am following:

1. create a connection
2. create a cursor
3. execute the SQL
4. use fetchall() to store the rows in a variable
5. pass the variable to my html page using render_template

6. in javascript set variable equal to data passed in

   var data={{dataset}};  
   console.log(data);
   

I have seen many flask examples where they take the result set and iterate through it to print the lines in the html but I want to use the resulting dataset as an input to my d3 code. Why is it displaying the data type? And why would it not also display the data type of the integer column?

I am guessing the issue is related to the row construct but I have tried creating a list instead and can't get rid of the data type information. I then get all sorts of errors due to the ampersands.

Answer 1

import re
processed = []
for piece in results:
    processed.append([re.sub(".*?(-?\d+\.\d+).*", "\1", repr(piece[0])), piece[1]]

Something along those lines. It's displaying that data because it's of type Decimal (the teradata kind). I'm guessing repr() will give you the same results you're seeing in the log... then just regex it to what you want.

Answer 2

I think the better approach is as provided in this answer, as it is best to dumps the data of the query into a json object so you can easily retrieve it from the client side using JS, without the need to parse it using regex or other mechanism.

STEPS:

1 - Subclass the json.JSONEncoder:

class DecimalEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, decimal.Decimal):
            return float(o) #Type conversion happens here
        return super(DecimalEncoder, self).default(o)

2 - Pass the params to the template as a json object:

data = json.dumps({'pi': decimal.Decimal('3.14')}, cls=DecimalEncoder)
return render_template('mytemplate.html', dataset=data)

3 - Read back the result of the query in your JS:

var data = {{dataset}};