Re: Accessing / connecting to web2py database with GUI

[Niphlod]

if you're using sqlite, by default the file is stored under the databases/ directory of your app.

On Thursday, December 10, 2015 at 11:41:27 AM UTC+1, Sam Heather wrote:

Hi there,

Is it possible to access the Web2Py database remotely using a GUI tool? Web2Py doesn't seem to use the normal instance of sqlite (which isn't installed on the server) - how can I find that instance and connect to it via an SSH tunnel using the SQL GUIs on my Mac? I don't want to have to write queries for the tiniest of maintenance changes...

Thanks!

Sam

[Dave S]

On Thursday, December 10, 2015 at 3:42:09 AM UTC-8, Niphlod wrote:

if you're using sqlite, by default the file is stored under the databases/ directory of your app.

And for a shell running on the webserver machine, cd to that directory allows you to use the standard SQLite3 command line tool.  I've not tried GUI tools, but I can vouch that SQLite3 seems happy (Fedora 16, Centos 65, and Windows 7 so far).

/dps

[Dave S]

On Thursday, December 10, 2015 at 2:38:23 PM UTC-8, Sam Heather wrote:

Thanks for the tip. I'm specifically looking for a GUI, but as far as I can tell you have to install one on the local server - it can't be done remotely without downloading the db, editing it, and re-uploading it :(

Kinda a shame - doesn't seem a very scalable way of making small tweaks.

ᐧ

What sort of small tweaks are you interested in? If you're adding/deleting records or editing them, the appadmin page can provide that, and with the right https settings you can do that remotely.  If you're changing the field properties, then you'd be editing the model files and using the migrate settings.

/dps

[Dave S]

On Thursday, December 10, 2015 at 3:03:23 PM UTC-8, Sam Heather wrote:

Just the kind of tweaks where I might want to edit a value. I'm trying to allow that kind of admin access to non-techies who don't know SQL, so the kind of GUI that allows them to view the database like a spreadsheet (as is available for MySQL) would ideal. I read I can actually switch Web2PY to use proper SQL instead of SQLite here .... is that correct?  

Web2Py works with several databases, and the common relational databases with SQL interfaces (MySQL, Postgres, Oracle, and the Windders one) are frequently used underneath Web2Py.  SQLite3 is just the one shipped "in the box".  Postgres seems to be the one most favored for production databases, if you have a choice.  For such databases, the actual storage will probably not be in your application directory.

SQLite3 is a fairly robustly featured DB, but is best suited for small databases and development work.  All of my current efforts fit into that description; my only use of Postgres was where I didn't have to set up the server.

But before you go running around looking for Big Iron to set your DB up on, note that the AppAdmin interface allows values to be exported and imported as CSV files which your users can edit in a spreadsheet; worksfor all backends.  If this sort of maintenance is frequent, though, it might be just as well to provide a form (like using SQLFORM and SQLFORM.grid) that make it easy for them to find and edit the values of interest.  Such a form would work with any of the backends.

/dps

ᐧ

 

[Dave S]

On Thursday, December 10, 2015 at 3:39:07 PM UTC-8, Dave S wrote:

[...] 

SQLite3 is a fairly robustly featured DB, but is best suited for small databases and development work. 

Note that this has to do with scaling, not features.  It's built to use a conventional filesystem, and uses a more global locking scheme than the DBs built for heavy parallel access.

/dps