GUI interface for sqlite data entry in Python
Question: I am making a simple sqlite database for storing some non-sensitive client
information. I am very familiar with python+sqlite and would prefer to stick
with this combo on this project. I would like to create an simple GUI
interface for data entry and searching of the database... something very
similar to what MS Access provides. I want my wife to be able to enter/search
data easily, so PHPmyadmin style things are out of the question.

I understand I could just give in and get MS Access, but if reasonbly possible
would rather just write the code myself so it will run on my computers (*nix)
and is flexible (so I can later integrate it with a web application and our
smart phones.)

Can you developers recommend any interfaces/packages/etc (preferably pythonic)
that can accomplish this with reasonable ease?

Thanks!


Answer: Since you're interested in future integration with a web application, you
might consider using a Python web framework and running the app locally on
your machine, using your web browser as the interface. In that case, one easy
option would be [web2py](http://www.web2py.com). Just
[download](http://www.web2py.com/examples/default/download), unzip, and run,
and you can use the web-based IDE
([demo](http://www.web2py.com/demo_admin/default/site)) to create a simple
CRUD app very quickly (if you really want to keep it simple, you can even use
the "New application wizard"
([demo](http://www.web2py.com/demo_admin/wizard/index)) to build the app). It
includes its own server, so you can run your app locally, just like a desktop
app.

You can use the web2py [DAL](http://web2py.com/book/default/chapter/06)
(database abstraction layer) to define and create your SQLite database and
tables (without writing any SQL). For example:

    
    
    db = DAL('sqlite://storage.db')
    
    db.define_table('customer',
        Field('name', requires=IS_NOT_IN_DB(db, 'customer.name')),
        Field('address'),
        Field('email', requires=IS_EMAIL()))
    

The above code will create a SQLite database called storage.db and create a
table called 'customer'. It also specifies form validators for the 'name' and
'email' fields, so whenever those fields are filled via a form, the entries
will be validated ('name' cannot already be in the DB, and 'email' must be a
valid email address format) -- if validation fails, the form will display
appropriate error messages (which can be customized).

The DAL will also handle schema
[migrations](http://web2py.com/book/default/chapter/06#Migrations)
automatically, so if you change your table definitions, the database schema
will be updated (if necessary, you can turn off migrations completely or on a
per table basis).

Once you have your data models defined, you can use web2py's
[CRUD](http://web2py.com/book/default/chapter/07#CRUD) system to handle all
the data entry and searching. Just include these two lines (actually, they're
already included in the 'welcome' scaffolding application):

    
    
    from gluon.tools import Crud
    crud = Crud(db)
    

And in a controller, define the following action:

    
    
    def data():
        return dict(form=crud())
    

That will expose a set of pre-defined URLs that will enable you to create,
list, search, view, update, and delete records in any table.

Of course, if you don't like some of the default behavior, there are lots of
ways to customize the CRUD forms/displays, or you can use some of web2py's
other [forms functionality](http://web2py.com/book/default/chapter/07) to
build a completely custom interface. And web2py is a full-stack framework, so
it will be easy to add functionality to your app as your needs expand (e.g.,
access control, notifications, etc.).

Note, web2py requires no installation or configuration and has no
dependencies, so it's very easy to distribute your app to other machines --
just zip up the entire web2py folder (which will include your app folder) and
unzip it on another machine. It will run on *nix, Mac, and Windows (on
Windows, you will either need to install Python or download the web2py Windows
binary instead of the source version -- the Windows binary includes its own
Python interpreter).

If you have any questions, there's a very helpful and responsive [mailing
list](https://groups.google.com/forum/?fromgroups#!forum/web2py). You might
also get some ideas from some existing [web2py
applications](http://web2py.com/appliances).

