coleifer / sqlite-web Goto Github PK

View Code? Open in Web Editor NEW

2.9K 73.0 299.0 1.33 MB

Web-based SQLite database browser written in Python

License: MIT License

Python 45.11% CSS 3.80% HTML 40.44% Shell 0.32% Dockerfile 2.01% JavaScript 8.31%

sqlite flask python database

sqlite-web's Introduction

sqlite-web is a web-based SQLite database browser written in Python.

Project dependencies:

Installation

$ pip install sqlite-web

Usage

$ sqlite_web /path/to/database.db

Features

Works with your existing SQLite databases, or can be used to create new databases.
Add or drop:
- Tables
- Columns (with support for older versions of Sqlite)
- Indexes
Export data as JSON or CSV.
Import JSON or CSV files.
Browse table data.
Insert, Update or Delete rows.

Screenshots

The index page shows some basic information about the database, including the number of tables and indexes, as well as its size on disk:

The structure tab displays information about the structure of the table, including columns, indexes, triggers, and foreign keys (if any exist). From this page you can also create, rename or drop columns and indexes.

Columns are easy to add, drop or rename:

The content tab displays all the table data. Links in the table header can be used to sort the data:

The query tab allows you to execute arbitrary SQL queries on a table. The query results are displayed in a table and can be exported to either JSON or CSV:

The import tab supports importing CSV and JSON files into a table. There is an option to automatically create columns for any unrecognized keys in the import file:

The export tab supports exporting all, or a subset, of columns:

Basic INSERT, UPDATE and DELETE queries are supported:

Command-line options

The syntax for invoking sqlite-web is:

$ sqlite_web [options] /path/to/database-file.db

The following options are available:

-p, --port: default is 8080
-H, --host: default is 127.0.0.1
-d, --debug: default is false
-l, --log-file: filename for application logs.
-x, --no-browser: do not open a web-browser when sqlite-web starts.
-P, --password: prompt for password to access sqlite-web. Alternatively, the password can be stored in the "SQLITE_WEB_PASSWORD" environment variable, in which case the application will not prompt for a password, but will use the value from the environment.
-r, --read-only: open database in read-only mode.
-R, --rows-per-page: set pagination on content page, default 50 rows.
-e, --extension: path or name of loadable extension(s). To load multiple extensions, specify -e [path] for each extension.
-f, --foreign-keys: enable foreign-key constraint pragma.
-u, --url-prefix: URL prefix for application, e.g. "/sqlite-web".
-c, --cert and -k, --key - specify SSL cert and private key.
-a, --ad-hoc - run using an ad-hoc SSL context.

Using docker

A Dockerfile is provided with sqlite-web. To use:

$ cd docker/  # Change dirs to the dir containing Dockerfile
$ docker build -t coleifer/sqlite-web .
$ docker run -it --rm \
    -p 8080:8080 \
    -v /path/to/your-data:/data \
    -e SQLITE_DATABASE=db_filename.db \
    coleifer/sqlite-web

sqlite-web's People

Contributors

Stargazers

Watchers

Forkers

mhiller pombredanne nfredrik yhlam aaaliua singularityxspace abhijitmamarde uestchp fahadysf 3mao giserh wangjun cityzz shaoran yennanliu xlong88 abhi-shetty digideskio kamrex daoer878 rockzhangcn pablosajnovsky wdscxsj dirkakrid lijiajun kevin-catlikepuma dgreyling gladiopeace yjfintalkdev lzl0601 rgwt123 miroslavvidovic h4ckl4bm3 mamewotoko praetoreean gazzola hellodata www3838438 i-a-m ipaste r14152 grynko k4vej rajeshprasanth simonzhoup zknyy bluemagnificent bmwant bigrlab vo0doo yuremboo myhololens vbianchi kpivy8 jhy1000 estefania17e yufc2002 qd452 dragonview rako9000 patarapolw efeng1st chenxuelee pradorocchi andytsai mkkie zz38 annamalairaja fhector zeichenkette ichobits jamiejackherer eshnil2000 tuxsud0 ihaohong mjunaidi baiyongzhen kuanyuchen gdyshi miklobit ilya-maltsev peifenghu ccf19881030 dunez joe-cosgrove nlori bernhard10 zfenj tylerlhess google38438 somas1 kinkir pivotsecurity summitn adammendoza cellcpu hakanaku1234 whsheng jimbertools dragonbeen

sqlite-web's Issues

Is there an option to see views?

I would like to see views and their data in the same way.

Crash when viewing UUIDs

Hi! I'm using SQLite and my table's primary key is an UUID. When I click on the Content tab, sqlite_web crashes with this exception:

OperationalError: Could not decode to UTF-8 column 'id' with text '��6.��N���؎b�w�'

Full exception

[2017-11-16 10:59:42,191] ERROR in app: Exception on /chans/content/ [GET] Traceback (most recent call last): File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app response = self.full_dispatch_request() File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 185, in inner return fn(table, *args, **kwargs) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 436, in table_content total_rows=total_rows) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/templating.py", line 134, in render_template context, ctx.app) File "/home/tchoutri/.local/lib/python2.7/site-packages/flask/templating.py", line 116, in _render rv = template.render(context) File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 1008, in render return self.environment.handle_exception(exc_info, True) File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 780, in handle_exception reraise(exc_type, exc_value, tb) File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 1, in top-level template code {% extends "base_table.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 1, in top-level template code {% extends "base_tables.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_tables.html", line 1, in top-level template code {% extends "base.html" %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base.html", line 58, in top-level template code {% block content %}{% endblock %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 26, in block "content" {% block inner_content %} File "/home/tchoutri/.local/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 38, in block "inner_content" {% for row in query %} File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3240, in __iter__ return iter(self.execute()) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3233, in execute self._qr = ResultWrapper(model_class, self._execute(), query_meta) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 2912, in _execute return self.database.execute_sql(sql, params, self.require_commit) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3775, in execute_sql self.commit() File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3598, in __exit__ reraise(new_type, new_type(*exc_args), traceback) File "/home/tchoutri/.local/lib/python2.7/site-packages/peewee.py", line 3768, in execute_sql cursor.execute(sql, params or ()) OperationalError: Could not decode to UTF-8 column 'id' with text '��6.��N��؎b�w�'

Do you plan to add support for UUID inspection?

UnicodeDecodeError: 'ascii' codec can't decode byte 0xff in position 0: ordinal not in range(128)

classic unicode issue, please fix, thanks

Docker container does not start

After building the docker container it fails to run:

Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
cursor.execute(sql, params or ())
sqlite3.OperationalError: no such module: spellfix1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/local/bin/sqlite_web", line 11, in
load_entry_point('sqlite-web==0.3.5', 'console_scripts', 'sqlite_web')()
File "/usr/local/lib/python3.7/site-packages/sqlite_web/sqlite_web.py", line 818, in main
initialize_app(args[0], options.read_only, password, options.url_prefix)
File "/usr/local/lib/python3.7/site-packages/sqlite_web/sqlite_web.py", line 789, in initialize_app
dataset = SqliteDataSet('sqlite:///%s' % filename, bare_fields=True)
File "/usr/local/lib/python3.7/site-packages/playhouse/dataset.py", line 44, in init
bare_fields=bare_fields)
File "/usr/local/lib/python3.7/site-packages/playhouse/reflection.py", line 619, in generate_models
include_views)
File "/usr/local/lib/python3.7/site-packages/playhouse/reflection.py", line 524, in introspect
table_columns = self.metadata.get_columns(table, self.schema)
File "/usr/local/lib/python3.7/site-packages/playhouse/reflection.py", line 161, in get_columns
for metadata in self.database.get_columns(table, schema))
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 3176, in get_columns
(schema or 'main', table))
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 2690, in execute_sql
self.commit()
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 2481, in exit
reraise(new_type, new_type(*exc_args), traceback)
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 178, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
cursor.execute(sql, params or ())
peewee.OperationalError: no such module: spellfix1

css issue in firefox

the table_name and table-search input fields do not show
their values in firefox (in chrome it works) because .input-sm
has a padding that pushes them out of view: padding: 15px 10px;

as far as i can say, in vanilla bootstrap that padding is smaller: padding: 5px 10px;

changing it to the bootstrap value makes them visible again.

Permalinks for queries

I was wondering whether it'd be possible to add permanent links for query pages. It's useful for bookmarking or fast sharing of links without having to re-enter again the specific query. I'd expect something along the lines:

http://localhost/my_table/query/.WHERE?cond1=AAA&cond2=TRUE.ORDERBY?column1

I guess that at least simple keywords such WHERE, LIMIT, ORDER BY and GROUP BY would be sufficient. Covering all cases isn't necessary for a simple lightweight tool.

Thanks for this sweet tool by the way.

project name

i know it is kind of late, but would you consider picking
some kind of name/nickname for your project?
there is already https://github.com/sqlitebrowser/sqlitebrowser/
and i would like to create a package for openbsd
so its possible to easily install and use it locally...
sqlitebrowser (not sqlite-browser) is way to heavy
on dependencies.

peewee is also not called "orm" :)

Some safety features for remote DB access

I just needed a sqlite db browser for testing of my program on remote Linux server.
Sqlite-web is the only I found. It is simple and useful. Thank you!

What I'm missing it is simple password protection to access the database remotely.
For example specifying password as parameter in the start command line would be fine. Now each time I have to stop the server and it is unsafe if I forget it. There might be ways to handle it but I'm not good at web server administration.

Another useful option would be server auto stop after some time of inactivity

Request: new option to support base url path for reverse proxy support

I have nginx running as a reverse proxy for all my local apps, exposing them to the internet with some security layered in. Most of my (cherrypy) apps have an option to set a base URL path, allowing me to bake in a subdirectory into the non-proxied server's url...

http://privvateserver.local:8080/appname/

Then in the nginx config I can do something like this...

  location /appname/ {
    proxy_pass http://privateserver.local:8080;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_redirect http:// https://;
  }

to allow access like this:

https://publicserver.ontheinternet.com/appname/

I've tried various forms of the nginx rewrite directive, but the css keeps getting hosed up. Setting a base URL path would (hopefully) avoid that... thoughts?

OperationalError

I used with the sqlite3's db file. Some table work fine, but when I access to the table which name is 'git_log_file', I got the Execption as follow.
OperationalError: table "git_log_file" already exists

I don't create the table, but only access to the table to view the contents.
any idea to help me?

the error detail is as follow.

File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1836, in call
Open an interactive python shell in this framereturn self.wsgi_app(environ, start_response)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
response = self.make_response(self.handle_exception(e))
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functionsrule.endpoint
File "/usr/local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 177, in inner
return fn(table, _args, *_kwargs)
File "/usr/local/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 193, in table_structure
ds_table = dataset[table]
File "/usr/local/lib/python2.7/site-packages/playhouse/dataset.py", line 59, in getitem
return Table(self, table, self._models.get(table))
File "/usr/local/lib/python2.7/site-packages/playhouse/dataset.py", line 147, in init
model_class.create_table()
File "/usr/local/lib/python2.7/site-packages/peewee.py", line 4561, in create_table
db.create_table(cls)
File "/usr/local/lib/python2.7/site-packages/peewee.py", line 3540, in create_table
return self.execute_sql(_qc.create_table(model_class, safe))
File "/usr/local/lib/python2.7/site-packages/peewee.py", line 3461, in execute_sql
self.commit()
File "/usr/local/lib/python2.7/site-packages/peewee.py", line 3285, in exit
reraise(new_type, new_type(_exc_args), traceback)
File "/usr/local/lib/python2.7/site-packages/peewee.py", line 3454, in execute_sql
cursor.execute(sql, params or ())

convert gif files to png

any particular reason to have the query images as gif?
after converting to png and running pngcrush,
images went from 245,423 bytes to 196,856
easy pickings...

Can I open multiple databases?

Is it possible to open multiple databases on the same port?

Accessing a table throws a AttributeError

https://gist.github.com/anonymous/f531d279757afb75bdff

Add support for postgresql

please add support to other db

The content tab is not refreshed when the structure of the database is changed.

When a column is added or dropped from a table, the content tab is not refreshed.

If you drop a column, visiting the tab will return a server error:

Internal Server Error

The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

If you add a column, it will not be displayed.

Closing and restarting the application will resolve the issue.

Content Tab - Attribute Error

Hi,

I just installed sqlite-web using pip and started to use it. Most of the functionality seems to work. However, whenever I try to access the Content tab, it throws an error:

AttributeError: 'AutoField' object has no attribute 'db_column'

I tried it on using both Python 2.7 and Python 3.6 and both threw the same error.

This behavior was consistent across all SQLite databases I tried (including the http://www.sqlitetutorial.net/download/sqlite-sample-database/?wpdmdl=94 and https://github.com/jpwhite3/northwind-SQLite3). Any ideas? Thanks.

Secure Authentication

What would be the better way of doing authentication rather than modifying the Python code where the line saying password = None to password = 'mypassword'? Thanks.

New feature - Content modification

I think it would be great if somebody could also update the contents of the table, for example to edit a row using a form (add/edit/remove rows).

error message in use

Running - but

192.168.1.31 - - [18/Sep/2018 14:14:31] "GET /timeseries/content/ HTTP/1.1" 200 -

Exception happened during processing of request from ('192.168.1.31', 54295)
Traceback (most recent call last):
File "/usr/lib/python2.7/SocketServer.py", line 290, in _handle_request_noblock
self.process_request(request, client_address)
File "/usr/lib/python2.7/SocketServer.py", line 318, in process_request
self.finish_request(request, client_address)
File "/usr/lib/python2.7/SocketServer.py", line 331, in finish_request
self.RequestHandlerClass(request, client_address, self)
File "/usr/lib/python2.7/SocketServer.py", line 654, in init
self.finish()
File "/usr/lib/python2.7/SocketServer.py", line 713, in finish
self.wfile.close()
File "/usr/lib/python2.7/socket.py", line 283, in close
self.flush()
File "/usr/lib/python2.7/socket.py", line 307, in flush
self._sock.sendall(view[write_offset:write_offset+buffer_size])
error: [Errno 32] Broken pipe

Version Checking is Incorrect

RuntimeError: Peewee >= 2.4.3 is required. Found version 2.10.1. Please update by running pip install --update peewee

2.10.1 is greater than 2.4.3 but is seems that the 10 is being processed as a 1

Edit with Spreadsheet UI

I've been looking for a hosted alternative to Google Sheets, for relatively small datasets. If a table could be edited like a spreadsheet, this would certainly be a candidate.

I know this opens up a whole can of worms on the concurrent editing side of things, but at least for our use case it would be fine to simply detect the concurrent edit and reject it with an error message.

Right-click enabling

I really want to make a column a little wider, to make it easier to read.

I actually plan to something like this in Handsontable (which is right-click enabled), but it seems that I have to create a lot of API's...

OK, I made it resizable #47 but right-click enabled (jQuery-contextMenu, perhaps) is another subject...

RowID

It would be really nice if the content tab showed rowid as well. I think just changing the sql from select * to select rowid,* will do it.

OperationalError: no such column: t1.id

When selecting CONTENT tab for one specific table I get the following error.

[2017-05-30 15:59:12,406] ERROR in app: Exception on /GONEUSERS/content/ [GET]
Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/lib64/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/lib64/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib64/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/lib64/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 185, in inner
    return fn(table, *args, **kwargs)
  File "/usr/lib/python2.7/site-packages/sqlite_web/sqlite_web.py", line 436, in table_content
    total_rows=total_rows)
  File "/usr/lib64/python2.7/site-packages/flask/templating.py", line 134, in render_template
    context, ctx.app)
  File "/usr/lib64/python2.7/site-packages/flask/templating.py", line 116, in _render
    rv = template.render(context)
  File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 989, in render
    return self.environment.handle_exception(exc_info, True)
  File "/usr/lib/python2.7/site-packages/jinja2/environment.py", line 754, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 1, in top-level template code
    {% extends "base_table.html" %}
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 1, in top-level template code
    {% extends "base_tables.html" %}
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/base_tables.html", line 1, in top-level template code
    {% extends "base.html" %}
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/base.html", line 58, in top-level template code
    {% block content %}{% endblock %}
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/base_table.html", line 26, in block "content"
    {% block inner_content %}
  File "/usr/lib/python2.7/site-packages/sqlite_web/templates/table_content.html", line 38, in block "inner_content"
    {% for row in query %}
  File "/usr/lib/python2.7/site-packages/peewee.py", line 3240, in __iter__
    return iter(self.execute())
  File "/usr/lib/python2.7/site-packages/peewee.py", line 3233, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2912, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 3775, in execute_sql
    self.commit()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 3598, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
OperationalError: no such column: t1.id
127.0.0.1 - - [30/May/2017 15:59:12] "GET /GONEUSERS/content/ HTTP/1.1" 500 -

sqlite> .schema goneusers
CREATE TABLE GONEUSERS
       (USERNAME  TEXT     NOT NULL
   );

table already exists

Hi!
When I click on table name I get this error

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python2.7/dist-packages/sqlite_browser/sqlite_browser.py", line 136, in inner
    return fn(table, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlite_browser/sqlite_browser.py", line 152, in table_structure
    ds_table = dataset[table]
  File "/usr/local/lib/python2.7/dist-packages/playhouse/dataset.py", line 59, in __getitem__
    return Table(self, table, self._models.get(table))
  File "/usr/local/lib/python2.7/dist-packages/playhouse/dataset.py", line 147, in __init__
    model_class.create_table()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 3798, in create_table
    db.create_table(cls)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2956, in create_table
    return self.execute_sql(*qc.create_table(model_class, safe))
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2877, in execute_sql
    self.commit()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2732, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2869, in execute_sql
    cursor.execute(sql, params or ())
OperationalError: table "bondclear" already exists

Deploy the sqlite-web to personal homepage

Is it possible to deploy the sqlite-web generated webpage to a public website, maybe with Heroku? So like everyone can view the sqlitedatabase with a url.

basic auth

Is there a simple way to require authentication when accessing the web page?

CSV import appears broken

After trying to import a simple CSV file i receive the following message:

Error importing file: iterator should return strings, not bytes (did you open the file in text mode?)

Steps to reproduce

echo -e "col1,col2\n1,2" > test.csv
sqlite_web empty.sqlite
Create table test
Click the Import tab, select file test.csv (created in step 1), set Create columns for unknown keys to Yes

After clicking Import the error will be reported.

always tries to go to 127.0.0.1

i am currently running sqlite3 behind a caddy proxy to add basic authentication but sqlite3 always tried to go to 127.0.0.1/something after some requests which doesnt work. would you be able to make it go to a specific url using cli arguments?

string to float exception

Hi,
when trying to view the content of a table, I get an exception :

ValueError: could not convert string to float:

Full traceback here: https://gist.github.com/saimn/7471a06f96be0d6a285b
The value is an empty string, and peewee tries to convert it to a float value.

Maybe the issue comes from the database I'm visualizing, but would it be possible to catch this exception in sqlite-browser and ignore this specific value ?
From the traceback I guess it is not so easy to do in sqlite-browser, maybe in peewee ?
Otherwise sqlite-browser is a nice and useful tool, thanks for that !

fails to display table with foreign keys

Using the latest version from pip, I have the following issue:

When I click on a table that contains a foreign key, an Internal Server Error is caused with the following Traceback:

[2018-04-26 10:56:55,863] ERROR in app: Exception on /expression/ [GET]
Traceback (most recent call last):
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/_compat.py", line 33,in reraise
    raise value
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/sqlite_web/sqlite_web.py", line 201, in inner
    return fn(table, *args, **kwargs)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/sqlite_web/sqlite_web.py", line 217, in table_structure
    ds_table = dataset[table]
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/dataset.py", line63, in __getitem__
    self.update_cache(table)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/dataset.py", line92, in update_cache
    literal_column_names=True)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 598, in generate_models
    literal_column_names=literal_column_names)
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 586, in introspect
    related_name=related_names.get(src))
  File "/storage/apps/easybuild/software/Anaconda3/5.0.1/lib/python3.6/site-packages/playhouse/reflection.py", line 120, in set_foreign_key
    self.rel_model = model_names[foreign_key.dest_table]
KeyError: 'sample'

The SQL of the table looks like

create table sample (sample varchar(80)
                     , patient varchar(80)
                     , cohort varchar(10)
                     , sample_type varchar(4)
                     , PRIMARY KEY (sample)
                     , FOREIGN KEY (cohort) references cohort(cohort)
                     , FOREIGN KEY (sample_type) references sample_type(sample_type))

The SQL of the referenced tables like

create table cohort (cohort varchar(10)
                     , name text
                     , PRIMARY KEY (cohort));

create table sample_type (numeric_code varchar(2)
                          , sample_type varchar(8)
                          , definition text
                          , PRIMARY KEY (sample_type)
                          , UNIQUE(numeric_code))

Feature request: read only mode

As title
Thanks

Syntax highlighting

Hi, would you be interested in adding syntax highlighting for the SQL queries? Integrating something like codemirror is more or less trivial and the result looks pretty good IMHO. I.e. a query:

Or a table's structure:

UnicodeDecodeError trying to export data result set as CSV

See Stack Trace below. Happens when clicking the "Export CSV" button on a result set.

Traceback (most recent call last):
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/sqlite_web/sqlite_web.py", line 210, in inner
    return fn(table, *args, **kwargs)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/sqlite_web/sqlite_web.py", line 473, in table_query
    return export(table, sql, 'csv')
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/sqlite_web/sqlite_web.py", line 527, in export
    dataset.freeze(query, export_format, file_obj=buf, **kwargs)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/playhouse/dataset.py", line 152, in freeze
    exporter.export(file_obj, **kwargs)
  File "/Users/gmork/Library/Python/2.7/lib/python/site-packages/playhouse/dataset.py", line 338, in export
    writer.writerow(row)
UnicodeEncodeError: 'ascii' codec can't encode character u'\u20ac' in position 120: ordinal not in range(128)```

Forwarding issue

When i access the webinterface using the defined password, i get forwarded to the page path/favicon.ico/ .
After going back to the main page everything works fine.

pip install: dependencies are missing from setup.py

I expect pip install to install all of the dependencies needed to run the application.

from the README:

Installation

$ pip install sqlite-browser flask peewee

setup.py has support for dependencies via the parameter install_requires! Indeed this parameter is existing but empty in the project's setup.py

Changing it to

install_requires=[
    'Flask',
    'peewee',
],

would simplify the installation as well as the documentation.

Background info:
I found sqlite_browser via search on github.

When I was about installing it I glanced over the README, and saw OK, this is a standard python pip install instruction - obviously missing the extra packages.
So I simply created a new virtualenv and run

pip install sqlite-browser

which did not work (ImportError: No module named flask)

pip install flask

ImportError: No module named peewee

pip install peewee

and finally success.

Dependencies in setup.py also help installing via other tools, e.g. creating a PEX bundle with

$ pex -r sqlite_browser -e sqlite_browser.sqlite_browser:main -o sqlite_browser

instead of the longer

$ pex -r sqlite_browser -r flask -r peewee -e sqlite_browser.sqlite_browser:main -o sqlite_browser

Cannot recreate a table that has been deleted

sqlite_web empty.sqlite
Create table test.
Click Drop and confirm that you want to drop the table. The UI will say Table "test" dropped successfully.
Try to create table test again.
The URL changes to http://127.0.0.1:8080/test/import/ and the page says: Not Found. The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.

[Feature Request] Adding First and Last Navigation Buttons

This sqlite-web really is good! After using it for a while, I am thinking about having a simple additional feature: first and last navigation buttons, aside from previous and next buttons at the bottom. I find myself always have to look at the last page number and typing it into the address bar manually all the time when I wanted to see the data are actually stored. Thank you.

preserve ordering on "previous" and "next" table content links

When I order table content by a specific column in content view and rows span across multiple pages, the "previous" and "next" links at the bottom of the page don't preserve ordering arguments. The Next and Previous pages start with no ordering.

Adding the ordering for next and previous links in table_content.html seems to fix the issue for me

<         <a href="{{ url_for('table_content', table=table, page=previous_page) }}">&larr; Previous</a>

---
>         <a href="{{ url_for('table_content', table=table, page=previous_page, ordering=ordering) }}">&larr; Previous</a>
60c60
<         <a href="{{ url_for('table_content', table=table, page=next_page) }}">Next &rarr;</a>

---
>         <a href="{{ url_for('table_content', table=table, page=next_page, ordering=ordering) }}">Next &rarr;</a>

OperationalError: unable to open database file

Hello there!

When trying to open already opened database I got this.
Is there any way to add flag which would ignore other connections?

$ sqlite_web db.sqlite -H 0.0.0.0
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3688, in _create_connection
    return self._connect(self.database, **self.connect_kwargs)
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3939, in _connect
    conn = sqlite3.connect(database, **kwargs)
sqlite3.OperationalError: unable to open database file

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/sqlite_web", line 11, in <module>
    load_entry_point('sqlite-web==0.1.6', 'console_scripts', 'sqlite_web')()
  File "/usr/local/lib/python3.5/dist-packages/sqlite_web/sqlite_web.py", line 678, in main
    dataset = SqliteDataSet('sqlite:///%s' % db_file)
  File "/usr/local/lib/python3.5/dist-packages/playhouse/dataset.py", line 31, in __init__
    self._database.connect()
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3660, in connect
    self._local.conn = self._create_connection()
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3688, in _create_connection
    return self._connect(self.database, **self.connect_kwargs)
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3578, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3688, in _create_connection
    return self._connect(self.database, **self.connect_kwargs)
  File "/usr/local/lib/python3.5/dist-packages/peewee.py", line 3939, in _connect
    conn = sqlite3.connect(database, **kwargs)
peewee.OperationalError: unable to open database file

would it be possible to publish also a wheel on pypi ?

Creating Table in sqlite-web browser and BOOL data type is returning INTEGER

When creating tables in sqlite_web browser, I am running into trouble when trying to set the data type of a given field to BOOL; it instead returns INTEGER. The bottom three key-value pairs are supposed to be a BOOL (see image):

When I go to the "Create Column" page and name a field and set its data type to BOOL, it is properly displaying BOOL from the dropdown box. However, after hitting the "Add Column" button and returning to the structure page, both the SQL and the columns sections show the data type as INTEGER.

Am I missing something here? Is this normal behavior? Is INTEGER functioning as a BOOL in a C-like manner where the field value 0 = false and 1 = true?

Table names with special characters cause crash when viewing content tab

Test case:

import sqlite3
conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute('''CREATE TABLE 'example.com' (visitors INTEGER, unique_users INTEGER)''')
c.execute("INSERT INTO 'example.com' VALUES (20, 5)")
c.execute("INSERT INTO 'example.com' VALUES (10, 2)")

c.execute('''CREATE TABLE 'hello!!world' (visitors INTEGER, unique_users INTEGER)''')
c.execute("INSERT INTO 'hello!!world' VALUES (20, 5)")
c.execute("INSERT INTO 'hello!!world' VALUES (10, 2)")

conn.commit()
conn.close()

Execute the above script, then run: sqlite_web example.db

Click on either "example.com" or "hello!!world" on the sidebar, then click on the Content tab. The web app will crash, or return a 500 Internal Server Error.

Looking at the crash output, it looks like the special symbols are being replaced with an underscore. Here's the traceback:

(venv-3.5.2) $ ~/.pyenv/versions/venv-3.5.2/bin/sqlite_web example.db 
 * Running on http://127.0.0.1:8080/ (Press CTRL+C to quit)
127.0.0.1 - - [28/Aug/2017 22:17:16] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [28/Aug/2017 22:17:19] "GET /hello%21%21world/ HTTP/1.1" 200 -
127.0.0.1 - - [28/Aug/2017 22:17:19] "GET /static/fonts/glyphicons-halflings-regular.woff2 HTTP/1.1" 404 -
[2017-08-28 22:17:20,335] ERROR in app: Exception on /hello!!world/content/ [GET]
Traceback (most recent call last):
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: no such table: hello_world

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/sqlite_web/sqlite_web.py", line 185, in inner
    return fn(table, *args, **kwargs)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/sqlite_web/sqlite_web.py", line 393, in table_content
    total_rows = ds_table.all().count()
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3161, in count
    return self.aggregate(convert=False) or 0
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3154, in aggregate
    return self._aggregate(aggregation).scalar(convert=convert)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 2921, in scalar
    row = self._execute().fetchone()
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 2912, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3775, in execute_sql
    self.commit()
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3598, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/home/albert/.pyenv/versions/3.5.2/envs/venv-3.5.2/lib/python3.5/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: no such table: hello_world
127.0.0.1 - - [28/Aug/2017 22:17:20] "GET /hello%21%21world/content/ HTTP/1.1" 500 -

I have a feeling that this might be a peewee issue, since it doesn't look like you're attempting to do any sort of replacement in your code.

Note that selecting the query tab and running the prefilled query works. It correctly quotes the name of the table, and allows the data to be correctly fetched.

Content tab throws 500 Internal Service error

Happening on every table. The structure and query tabs work though.

Feature request: URL highlighting, file preview

I am using sqlite to store info on a photo collection, I insert the file path as a TEXT field and can view the url via sqlite-web. I can view the file by copy-paste into a browser tab, but what would be even nicer would be:

URL highlighing, so I could just click the URL to preview the photo in another tab
Even nicer, a quickview of the file

This possible?
Cheers

Error when exporting CSV

Error

When trying to export table as JSON or CSV using the corresponding buttons in the Query section of the web interface I get the following error message:

Internal Server Error

The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

Traceback

[2018-02-21 01:21:04,466] ERROR in app: Exception on /EOS_BTC_binance/query/ [POST]
Traceback (most recent call last):
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlite_web/sqlite_web.py", line 185, in inner
    return fn(table, *args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlite_web/sqlite_web.py", line 452, in table_query
    return export(table, sql, 'csv')
  File "/home/ubuntu/.local/lib/python3.5/site-packages/sqlite_web/sqlite_web.py", line 506, in export
    dataset.freeze(query, export_format, file_obj=buf, **kwargs)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/playhouse/dataset.py", line 131, in freeze
    exporter.export(file_obj, **kwargs)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/playhouse/dataset.py", line 301, in export
    for row in self.query.tuples():
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 1533, in __iter__
    self._ensure_execution()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 1530, in _ensure_execution
    self.execute()
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 1447, in inner
    return method(self, database, *args, **kwargs)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 1518, in execute
    return self._execute(database)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 1571, in _execute
    self._cursor_wrapper = self._get_cursor_wrapper(cursor)
  File "/home/ubuntu/.local/lib/python3.5/site-packages/peewee.py", line 5409, in _get_cursor_wrapper
    return ModelTupleCursorWrapper(cursor, self.model, self._returning)
AttributeError: 'ModelRaw' object has no attribute '_returning'
64.229.97.140 - - [21/Feb/2018 01:21:04] "POST /EOS_BTC_binance/query/ HTTP/1.1" 500 -

System Info

Ubuntu 16.04
Python 3.5.2
sqlite-web (0.2.0)

How to make database readonly?

Is it possible to configure and run this, such that no modification can be made to the database? This way the database is readonly, the end user could only query it. Alternatively, adding support for authentication for making changes to the database will be nice.

add option for command line password

hi
can you add an option to give a password via command line, NOT interactively? This way it can be automatized...
or a way so a password can be given and saved somewhere, so if you start again the http server there's no need to put it in again
thanks

peewee.OperationalError: Connection already open

Hello,

I already did pip install and now I'm entering '$ sqlite_web my/path' and it keeps saying 'peewee.OperationalError: Connection already open' I'm not sure how to get around this. Any ideas as to why it's throwing that error?

Thanks in advance!

How to specify web password when using docker ?

Would be cool to pass the password to the app via docker.

coleifer / sqlite-web Goto Github PK

sqlite-web's Introduction

Installation

Usage

Features

Screenshots

Command-line options

Using docker

sqlite-web's People

Contributors

Stargazers

Watchers

Forkers

sqlite-web's Issues

Running - but

192.168.1.31 - - [18/Sep/2018 14:14:31] "GET /timeseries/content/ HTTP/1.1" 200 -

Installation

Error

Traceback

Recommend Projects

Recommend Topics

Recommend Org