Failure on json export

[retrography]

I have the following table in a postgresql database:

elzilncu=> \d donor
            Table "public.donor"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 donorno | integer               | not null
 dlname  | character varying(15) |
 dfname  | character varying(15) |
 dphone  | numeric(4,0)          |
 dstate  | character(2)          |
 dcity   | character varying(15) |
Indexes:
    "pk_donor" PRIMARY KEY, btree (donorno)
Referenced by:
    TABLE "gift" CONSTRAINT "fk_donatedby" FOREIGN KEY (donorno) REFERENCES donor(donorno)

That I can dump pretty easily in CSV:

❯❯❯ records "select * from donor" csv
donorno,dlname,dfname,dphone,dstate,dcity
101,Abrams,Louis,9018,GA,London
102,Aldinger,Dmitry,1521,GA,Paris
103,Beckman,Gulsen,8247,WA,Sao Paulo
104,Berdahl,Samuel,8149,WI,Sydney
105,Borneman,Joanna,1888,MD,Bombay
106,Brock,Scott,2142,AL,London
107,Buyert,Aylin,9355,AK,New York
108,Cetinsoy,Girwan,6346,AZ,Rome
109,Chisholm,John,4482,MA,Oslo
110,Crowder,Anthony,6513,NC,Stockholm
111,Dishman,Michelle,3903,NC,Helsinki
112,Duke,Peter,4939,FL,Tokyo
113,Evans,Ann,4336,GA,Singapore
114,Frawley,Todd,4785,MN,Perth
115,Guo,John,6247,MN,Moscow
116,Hammann,John,5369,ND,Kabaul
117,Hays,Cami,1352,SD,Lima
118,Herskowitz,Thomas,6872,MT,London
119,Jefts,Robert,8103,ME,Oslo

But the same operation fails if I try to export to JSON or YAML:

❯❯❯ records "select * from donor" json
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/tablib/packages/omnijson/core.py", line 63, in dumps
    return _engine[1](o)
  File "/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/__init__.py", line 230, in dumps
    return _default_encoder.encode(obj)
  File "/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py", line 199, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py", line 257, in iterencode
    return _iterencode(o, 0)
  File "/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py", line 180, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: Decimal('9018') is not JSON serializable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/records", line 11, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.5/site-packages/records.py", line 345, in cli
    print(rows.export(arguments['<format>']))
  File "/usr/local/lib/python3.5/site-packages/records.py", line 160, in export
    return self.dataset.export(format, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/tablib/core.py", line 464, in export
    return export_set(self, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/tablib/formats/_json.py", line 22, in export_set
    return json.dumps(dataset.dict, default=date_handler)
  File "/usr/local/lib/python3.5/site-packages/tablib/packages/omnijson/core.py", line 69, in dumps
    raise JSONError(why)
tablib.packages.omnijson.core.JSONError: Decimal('9018') is not JSON serializable

Here is the full stack trace from Python:

In [3]: rows.export('json')
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-3-5693f926b1b0> in <module>()
----> 1 rows.export('json')

NameError: name 'rows' is not defined

In [4]: rows = db.query('select * from donor')

In [5]: rows.export('json')
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/usr/local/lib/python3.5/site-packages/tablib/packages/omnijson/core.py in dumps(o, **kwargs)
     62     try:
---> 63         return _engine[1](o)
     64

/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/__init__.py in dumps(obj, skipkeys, ensure_ascii, check_circular, allow_nan, cls, indent, separators, default, sort_keys, **kw)
    229         default is None and not sort_keys and not kw):
--> 230         return _default_encoder.encode(obj)
    231     if cls is None:

/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py in encode(self, o)
    198         # equivalent to the PySequence_Fast that ''.join() would do.
--> 199         chunks = self.iterencode(o, _one_shot=True)
    200         if not isinstance(chunks, (list, tuple)):

/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py in iterencode(self, o, _one_shot)
    256                 self.skipkeys, _one_shot)
--> 257         return _iterencode(o, 0)
    258

/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/json/encoder.py in default(self, o)
    179         """
--> 180         raise TypeError(repr(o) + " is not JSON serializable")
    181

TypeError: Decimal('9018') is not JSON serializable

During handling of the above exception, another exception occurred:

JSONError                                 Traceback (most recent call last)
<ipython-input-5-5693f926b1b0> in <module>()
----> 1 rows.export('json')

/usr/local/lib/python3.5/site-packages/records.py in export(self, format, **kwargs)
    158     def export(self, format, **kwargs):
    159         """Export the RecordCollection to a given format (courtesy of Tablib)."""
--> 160         return self.dataset.export(format, **kwargs)
    161
    162     @property

/usr/local/lib/python3.5/site-packages/tablib/core.py in export(self, format, **kwargs)
    462             raise UnsupportedFormat('Format {0} cannot be exported.'.format(format))
    463
--> 464         return export_set(self, **kwargs)
    465
    466     # -------

/usr/local/lib/python3.5/site-packages/tablib/formats/_json.py in export_set(dataset)
     20 def export_set(dataset):
     21     """Returns JSON representation of Dataset."""
---> 22     return json.dumps(dataset.dict, default=date_handler)
     23
     24

/usr/local/lib/python3.5/site-packages/tablib/packages/omnijson/core.py in dumps(o, **kwargs)
     67
     68         if any([(issubclass(ExceptionClass, e)) for e in _engine[2]]):
---> 69             raise JSONError(why)
     70         else:
     71             raise why

JSONError: Decimal('9018') is not JSON serializable

Additional info:

In [18]: r = rows.next()

In [19]: r.keys()
Out[19]: ['donorno', 'dlname', 'dfname', 'dphone', 'dstate', 'dcity']

In [20]: r.get('dfname')
Out[20]: 'Dmitry'

In [21]: r.get('dphone')
Out[21]: Decimal('1521')

In [22]: r.get('donorno')
Out[22]: 102

Even more info:

In [25]: from sqlalchemy import *

In [27]: db = create_engine(dbUrl)

In [29]: cn = db.connect()

In [30]: db.name()

In [33]: res = db.execute("select * from donor")

In [38]: res.first()
Out[38]: (101, 'Abrams', 'Louis', Decimal('9018'), 'GA', 'London')

The error may actually originate in Tablib or SQLAlchemy. If so, please let me know and I will move the issue.

[kennethreitz]

Interesting! This does indeed look like a bug within Tablib. We could compensate for it here, but it would be better for everyone if we fixed it on Tablib's side.

[kennethreitz]

We do currently have a datetime fixer in this codebase, specifically for json, however. Perhaps we'll just add a Decimal fixer as well.

[iurisilvio]

Tablib uses omnijson and AFAIK it does not handle any JSON kwargs (not-kennethreitz/omnijson#5).

[BrianPainter]

Is it best to submit a pull request for omnijson and tablib separately, or to fix it within this repo?

[kennethreitz]

the separate projects would be best — tablib could likely drop support for omnijson altogether, and rely on the built-in json (this would require dropping support for Python 2.5, however)

[BrianPainter]

Ok, gave it a shot. PR on omnijson as well as tablib.

[Dieken]

After 'pip install ujson', I bypassed this bug.

[vlcinsky]

This issues shall be already resolved by upgraded tablib since version 0.11.4.

PR #150 added requirement for tablib to be at least given version.