pymssql how to remove data type in queried result

Question

I use pymssql library to query the result, here is one example:

('999001', '4871C9DF-6E95-458B-B763-6BBBB6C75F5D', 1, True, 2, 'EOI', 'Dropped', None, None, 4, False, False, 'Enbloc', False, False, False, False, None, None, 'USD', None, None, None, None, None, None, 'Investment', None, None, None, 'JLL Deal', 'Exclusive Mandated', 'Sales Advisory', None, None, None, None, None, 'Korea Standard Time', None, None, None, None, None, None, None, None, None, 1, None, True, 'En-bloc', False, False, False, False, False, False, None, '1', None, None, None, None, None, None, Decimal('0.05'), '100%', None, None, None, None, None, 'Sqft', 'USD', Decimal('120000000.00'), 'USD$ 120.0M', Decimal('120000000.00'), 'USD$ 120.0M', None, None, None, None, None, None, None, None, None, None, Decimal('120000000.00'), Decimal('120000000.00'), Decimal('120000000.00'), Decimal('120000000.00'), 'Undisclosed', 'Hubville Co Ltd', 'An Exciting Investment Opportunity - The Gateway to Seoul', None, None, None, None, 'An Exciting Investment Opportunity - The Gateway to Seoul', None, None, datetime.datetime(2016, 1, 15, 0, 5, 51, 480000), datetime.datetime(2016, 12, 7, 5, 6, 52, 633000), 1, None)

But I found there are values like Decimal('120000000.00') and datetime.datetime(2016, 1, 15, 0, 5, 51, 480000). I only want to get the raw value without Decimal() and datetime.datetime(). How can I implement this?

Here is my code:

def extract_opportunities(sql):
    cursor.execute(sql)
    opportunities = cursor.fetchall()
    attributes = get_attributes_of_table(cursor)
    availability_id_idx = attributes.index("availabilityid")
    opportunity_dict = dict()
    for idx, val in enumerate(opportunities):
        opportunity_key = val[availability_id_idx]
        opportunity_value = dict(zip(attributes, val))
        opportunity_dict[opportunity_key] = opportunity_value

The purpose of this function is to construct a JSON format file with attribute and value pairs, like:

{'Price': '120000000.00'} 

instead of

{'Price': Decimal('120000000.00')}

Answer 1

You can pass a default function to the json.dumps method that converts Decimal to string, like this:

import datetime
from decimal import Decimal
import json
import pymssql

def json_dumps_default(obj):
    # ref: http://stackoverflow.com/a/16957370/2144390
    if isinstance(obj, Decimal):
        return str(obj)
    if isinstance(obj, datetime.datetime):
        return str(obj)
    raise TypeError

conn = pymssql.connect(
    host=r'localhost:49242',
    database='myDb'
    )
crsr = conn.cursor(as_dict=True)
sql = """\
SELECT
    CONVERT(DECIMAL(18,4), 120000000) AS Price,
    CONVERT(DATETIME, '2016-12-07 05:06:52.633') AS SaleDate
UNION ALL
SELECT
    CONVERT(DECIMAL(18,4), 340000000) AS Price,
    CONVERT(DATETIME, '2017-01-09 07:44:32.1') AS SaleDate
"""
crsr.execute(sql)
rows = crsr.fetchall()
print()
print("Rows as returned by pymssql:")
print(rows)

as_json = json.dumps(rows, default=json_dumps_default, sort_keys=False, indent=2)
print()
print("Rows as returned by json.dumps:")
print(as_json)

crsr.close()
conn.close()

The console output is:

Rows as returned by pymssql:
[{'Price': Decimal('120000000.0000'), 'SaleDate': datetime.datetime(2016, 12, 7, 5, 6, 52, 633000)}, {'Price': Decimal('340000000.0000'), 'SaleDate': datetime.datetime(2017, 1, 9, 7, 44, 32, 100000)}]

Rows as returned by json.dumps:
[
  {
    "Price": "120000000.0000",
    "SaleDate": "2016-12-07 05:06:52.633000"
  },
  {
    "Price": "340000000.0000",
    "SaleDate": "2017-01-09 07:44:32.100000"
  }
]

Note that the function could return float(obj) instead of str(obj) for Decimal values, but a loss of precision might occur.