Issue: Fix decimal.InvalidOperation error for empty string
Author: soyoung97
State: closed
Created: 2021-07-04 15:04:28

Hello, thank you for making such a good opensource code!
I was working with [sqlite db viewer](https://github.com/nitinprakash96/sqlite-db-viewer) that uses peewee.
However, when I was trying to iterate through None values at integer field (by {% for row in query %} in html code), there was a peewee error saying decimal.InvalidOperation.
I think this issue could also be related with [this pull request](https://github.com/coleifer/peewee/commit/cd07f4e01a1262166d8944736e11d0681fb76214), since the error appeared at returning value of the function python_value `decimal.Decimal(text_type(value))`.

After inspection, I found out that sometimes an empty string('') comes into this function(which it shouldn't since empty string is not a number) and by adding a filter (adding `and row[i] != ''` at ModelDictCursorWrapper), I managed to solve this problem.

*Please note that I am new to this code and I may be not understanding this perfectly, so please correct me if I'm wrong in solving this problem. 
Attatched below is my original code, error traceback, and pdb operations.
Thank you!

### Error Traceback
![image](https://user-images.githubusercontent.com/29880214/124389717-5d123400-dd23-11eb-8370-e1d7925abe36.png)
![image](https://user-images.githubusercontent.com/29880214/124389742-8206a700-dd23-11eb-9c39-d4fdae1dd057.png)

### Setting try-catch pdb and results at python_value at peewee.py
![image](https://user-images.githubusercontent.com/29880214/124389767-a3679300-dd23-11eb-88b8-59d465eeaac7.png)
![image](https://user-images.githubusercontent.com/29880214/124389796-c09c6180-dd23-11eb-9b3c-1e28543263c0.png)

### Filtering String values
![image](https://user-images.githubusercontent.com/29880214/124389841-f2152d00-dd23-11eb-9130-c81ee1a011d2.png)

### Correctly displayed application (mine)
![image](https://user-images.githubusercontent.com/29880214/124389888-2557bc00-dd24-11eb-9451-eaa909f3e86f.png)
![image](https://user-images.githubusercontent.com/29880214/124389909-45877b00-dd24-11eb-9075-8963167d886a.png)

Here, I think what caused the problem is the ibb, hbp, sh, sf, g_idp column, which has Nonetype values as well as numbers(0). Adding this code to handle exceptions for this case would be great!

FYI, this was the full stack trace for my application.
```Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2309, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2295, in wsgi_app
    response = self.handle_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1741, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/views.py", line 121, in inner
    return fn(cur_db, table, *args, **kwargs)
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/views.py", line 179, in table_content
    total_pages=total_pages)
  File "/usr/local/lib/python3.6/site-packages/flask/templating.py", line 135, in render_template
    context, ctx.app)
  File "/usr/local/lib/python3.6/site-packages/flask/templating.py", line 117, in _render
    rv = template.render(context)
  File "/usr/local/lib/python3.6/site-packages/jinja2/environment.py", line 1090, in render
    self.environment.handle_exception()
  File "/usr/local/lib/python3.6/site-packages/jinja2/environment.py", line 832, in handle_exception
    reraise(*rewrite_traceback_stack(source=source))
  File "/usr/local/lib/python3.6/site-packages/jinja2/_compat.py", line 28, in reraise
    raise value.with_traceback(tb)
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/table_content.html", line 1, in top-level template code
    {% extends "base_table.html" %}
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/base_table.html", line 1, in top-level template code
    {% extends "base_tables.html" %}
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/base_tables.html", line 1, in top-level template code
    {% extends "base.html" %}
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/base.html", line 42, in top-level template code
    {% block content %}{% endblock %}
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/base_table.html", line 29, in block "content"
    {% block inner_content %}
  File "/Users/soyoung/Desktop/coding/research/sql/sql-instruction-web/templates/table_content.html", line 36, in block "inner_content"
    {% for row in query %}
  File "/usr/local/lib/python3.6/site-packages/peewee.py", line 4378, in next
    self.cursor_wrapper.iterate()
  File "/usr/local/lib/python3.6/site-packages/peewee.py", line 4297, in iterate
    result = self.process_row(row)
  File "/usr/local/lib/python3.6/site-packages/peewee.py", line 7450, in process_row
    result[attr] = converters[i](row[i])
  File "/usr/local/lib/python3.6/site-packages/peewee.py", line 4681, in python_value
    return decimal.Decimal(text_type(value))
decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]```

So sqlite does not have an actual dedicated Decimal field. It interprets the value as numeric, I believe, but it looks like in your table you have empty string (`''`) instead of NULL or 0. Python cannot convert empty string into a decimal, so you get the error.

To fix this either:

1. update all decimal values stored as '' to be 0
2. update all decimal values stored as '' to be NULL
3. use a different field type or different column storage.

For what it's worth, it looks like @nitinprakash96 copy/pasted a bit of code directly from my project `sqlite-web`. I don't know what the deal is, but it looks like he's created a shittier version of my project. You might have better luck using it:

https://github.com/coleifer/sqlite-web

Oh I didn't know that there was original code! thanks for the pointer! I'll change my codebase to https://github.com/coleifer/sqlite-web.
By the way, (I have to test whether this problem also happens or not after changing the codebase but if so,) I just thought that it'll be great if we can treat this exception at the peewee library level, since currently I cannot update the original database (It is given to me as official training data). However thanks for the solution and fast response!

I would like to be able to fix at the library level, but it seems like a fairly particular edge-case arising from a couple different problems:

1. the use of DECIMAL type in sqlite (sqlite is weakly-typed and inconsistent data values can be a problem. See: https://www.sqlite.org/datatype3.html#type_affinity )
2. storing empty string in a numeric column instead of NULL or 0
3. peewee conversion logic for non-null decimal values

Like I said, I think there's a couple issues in play, but my suggestion would be to fix-up the data if you can.

Thanks so much for additional pointer. I'm not an expert at sqlite, so I didn't know about this issue. Now I fully understand that this is an edge case, and it should be better treated at the database level. Thanks again for your consideration! I really appreciate it :)