HTML/JS as interface to local SQLite database
Solution 1
This is what I've ended up doing:
As referred to here, you can use Python to create a local web server. This tutorial gives a basic infrastructure for the server handler. I had to deal with some issues, possibly caused by Python 3 or by using Chrome to access my local page.
My GET handler function ended up looking like this:
def do_GET(self):
try:
self.send_response(200)
self.send_header('Content-type', 'text/html')
self.send_header('Access-Control-Allow-Origin', '*')
self.end_headers()
try:
fn = GETHANDLERS[self.path[1:]]
self.wfile.write(fn().encode("utf-8"))
except KeyError:
self.wfile.write(self.path.encode("utf-8"))
return
except:
self.send_error(404, 'File Not Found: {0}'.format(self.path))
Where GETHANDLERS
is a dictionary mapping URLs to functions - for example, if you visit http://localhost/my_func
, the GETHANDLERS['my_func']
function is called. If no function exists for the URL, the URL is just echoed back.
I've implemented functions that manipulate a local SQLite database with the sqlite3
module. Here's an example of the query to list everything in the Categories
table:
import sqlite3
def get_categories():
con = sqlite3.connect('my.db')
c = con.cursor()
c.execute('''SELECT * FROM Categories;''')
return [cat[0] for cat in c.fetchall()]
GETHANDLERS["categories"] = get_categories
To use the local app, it's necessary for the user to install Python, then run the server script before opening the webpage. I think this is pretty acceptable!
Solution 2
If you really want just a client side application, you should really consider using HTML5 tehnologies(WebSQL, Indexed DB).A good reading is here: html5 doctor. You will need to export the database somewhere, or let the user export the database and when the user reloads the page the database should reconnect. example: export websql to a csv-file.
Solution 3
You could use the sqlite3 connector and connect to a local database file from a HTA Application.
Here is the connector (one has to register the dll as described on the page)
https://www.assembla.com/spaces/litex/documents
Check the HTA Demo App in the zip file (DLL is in \litex\bin folder, hta is in the script folder..)
Related videos on Youtube
Daniel Buckmaster
Startup founder, renewables advocate, reluctant technophile.
Updated on June 04, 2022Comments
-
Daniel Buckmaster almost 2 years
I'm writing a pretty simple database application, which I want to be locally stored (as opposed to looking up a remote database). I like HTML/Javascript for designing interfaces, and they're cross-platform (everybody has a browser!), so I'd really like to write a webpage as a frontend. No client/server interaction should be involved - I just want the users to be able to interact with the database using a browser, instead of a native program.
However, the only way I can see to access databases from a browser is using something like WebSQL or IndexedDB. Both of these, however, abstract away the process of managing the database file itself, and store it away in user settings somewhere. I want to distribute the database file itself along with the app.
In short: is there a way to use HTML/Javascript to modify a local SQLite database file? Or is HTML not the tool I should be using for this sort of application?
EDIT: possibly relevant
-
Daniel Buckmaster about 12 yearsI found lots of documentation on how to export a database, but is there a good way to import from CSV? Then, for example, the first time the app loads I could import all the data from local CSV into the browser's database.
-
Placinta Salaru Alin about 12 yearshmm you could try using this parser purbayubudi.wordpress.com/2008/11/09/… , but i would recommend exporting and importing your database using xml because is simpler w3schools.com/ajax/ajax_xmlfile.asp