search

coleifer / sqlite-web

Web-based SQLite database browser written in Python
MIT License
3.42k stars 339 forks source link

Internal Server Error when browse database content #123

Closed rikhtehgaran closed 1 year ago

rikhtehgaran commented 1 year ago

Hi, I have a numeric record for data time when I put a date into it sqlite-web returns 500 internal error but in another SQLite client it's OK As I see in SQLite documentation for date format we use numeric type so my data type is correct image

But when I put a date inside it like: image

it's my example database: my.db.zip

Error: image

[2023-08-01 14:00:50,366] ERROR in app: Exception on /posts/content/ [GET] Traceback (most recent call last): File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app response = self.full_dispatch_request() File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request rv = self.handle_user_exception(e) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request rv = self.dispatch_request() File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/sqlite_web.py", line 221, in inner return fn(table, *args, **kwargs) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/sqlite_web.py", line 476, in table_content return render_template( File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/templating.py", line 151, in render_template return _render(app, template, context) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/flask/templating.py", line 132, in _render rv = template.render(context) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/jinja2/environment.py", line 1301, in render self.environment.handle_exception() File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/jinja2/environment.py", line 936, in handle_exception raise rewrite_traceback_stack(source=source) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/table_content.html", line 1, in top-level template code {% extends "base_table.html" %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/base_table.html", line 1, in top-level template code {% extends "base_tables.html" %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/base_tables.html", line 1, in top-level template code {% extends "base.html" %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/base.html", line 62, in top-level template code {% block content %}{% endblock %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/base_table.html", line 37, in block 'content' {% block inner_content %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/sqlite_web/templates/table_content.html", line 40, in block 'inner_content' {% for row in query %} File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/peewee.py", line 4583, in next self.cursor_wrapper.iterate() File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/peewee.py", line 4503, in iterate result = self.process_row(row) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/peewee.py", line 7712, in process_row result[attr] = converters[i](row[i]) File "/mnt/My/Home/www/Projects/Python/bt/venv/lib/python3.8/site-packages/peewee.py", line 4891, in python_value return decimal.Decimal(text_type(value)) decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>] 127.0.0.1 - - [01/Aug/2023 14:00:50] "GET /posts/content/ HTTP/1.1" 500 -

coleifer commented 1 year ago

You can't really be putting a date inside a numeric. That's the issue as far as I can tell. A numeric must hold a decimal number. Use the date/datetime/text column type for dates.

If you want to store a unix timestamp or Julian date in a numeric that's fine but you're using a string in your example.

Also note that sqlite doesn't have a true decimal type, it's either floating point or int64, so you may take that into consideration as well.

rikhtehgaran commented 1 year ago

You can't really be putting a date inside a numeric. That's the issue as far as I can tell. A numeric must hold a decimal number. Use the date/datetime/text column type for dates.

If you want to store a unix timestamp or Julian date in a numeric that's fine but you're using a string in your example.

Also note that sqlite doesn't have a true decimal type, it's either floating point or int64, so you may take that into consideration as well.

Yes I know but in SQLite, the date type is categorised as a numeric type and it works on all SQLite clients also I try datetime functions on that and they work except your client

coleifer commented 1 year ago

Sqlite is not strongly typed and you can technically store anything in any data type column, but that doesn't make it correct in any way.