Acces to db made easy

- Prev: Mailing list
- Next: Sqledit - the standalone program to browse and edit data

# Sqledit Tutorial

## Intended audience

This is meant as a tutorial for the sqledit command that is part of sqlkit. It’s intended audience is anybody who is interested in editing data in a database (as opposed to editing the structure of the database).

No programming skill is required, but if you are supposed to install it yourself, you may need to understand at least a little bit of your operating system (but that may be as simple as a double click if you use bundles).

## Installation

According to you operating system and setup you may find the very easy way for you. You may not event need to know which are the dependancies that are explained below for the curious ones.

### Installing under Debian/Ubuntu

On Ubuntu lucid (10.04) and probably also others >= 9.10 you can prepare dependencies:

    sudo add-apt-repository ppa:toobaz/sqlkit

On Debian:

    echo deb http://ppa.launchpad.net/toobaz/sqlkit/ubuntu lucid main | sudo tee /etc/apt/sources.list.d/sqlkit.list
    sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 39012CF8

and install it as follows (this installs python drivers for PostgreSQL and Mysql as well):

    sudo apt-get update
    sudo apt-get install python-sqlkit python-psycopg2 python-mysqldb

I’ll try to keep this updated as the official package

### Installing under Windows

The easiest way is to use Windows Package Manager that handles dependencies (that can turn usefull for other software too). The installation process becomes as easy as:

> - download and install Windows Package Manager
> - start it, select sqlkit and install (you may want to select/inistall also the database drivers for PostgreSql and MySQL)

If you want to go the “hard” way you can install all peaces separately but still use the Pygtk all-in-one installer (thanks to Dieter Verfaillie):

> - download and install Python

> - download and install Pygtk-all-in-one

> - download sqlkit-0.9.4.zip unzip and install by doing:

>       python setup.py install

>   Note that python may not be in the PATH so you may need to write it explicetly.

### Installig under MacOS

You can install and run Sqlkit under MacOS also. You can use the all-in-one boundle (thanks to Anders F. Björklund) or install from MacPorts.

### Dependencies

Sqlkit depends on the following packages:

Python: | sqledit it’s a Python script. It should work with python 2.4 and following. But if you used 2.4 be sure to add the driver for sqlite that was added to the main distribution only starting from 2.5.

PyGTK:

any Linux desktop distribution has it already installed, in case it does not have it, it should be trivial to do. In Debian-derived systems (e.g.: Ubuntu), you simply run:

    apt-get install python-gtk2

In a Windows system it used to be a difficult task but now it’s very simple. Please follow the instructions on the pygtk site

sqlalchemy: | this is the core of the sql staff. It’s a layer that builds SQL statements and invokes the backend drivers. It takes care of inspecting the database and so on. You need at least release 0.5 but 0.5.4 would be much better.
python-dateutil:
| needed for computation on dates, used in filters (e.g: date >= ‘m-1’, means date >= starting of last month), see Date filters.
babel: | needed for localization of numbers and dates
setuptools: | needed for the installation and to check version of sqlalchemy
drivers: | don’t forget to add the driver for the backend of your choice, the only driver included is for `sqlite`, that is the database of the demo and is included in the python distribution since python 2.5.

If you have setuptools installed in your system, you can install whatever you need simply with:

    easy_install sqlkit

and probably you’d have better results using `pip`:

    easy_install pip
    pip install sqlkit

remember to install the backend driver, these are the examples for postgresql and mysql:

    pip install psycopg2
    pip install MySQL-python

## sqledit/sqlkit

Now you should have a working setup. The command we are going to familiarize with is `sqledit` that is based on a library named `sqlkit`. If you are a programmer and are interested in the sqlkit package you can find extensive documentation in the web site

Programming with sqlkit is a pretty simple experience that allows you to use many more features than available with `sqledit`, nevertheless you can do a lot of simple tasks by using `sqledit` alone.

New in version 9.1.

Sqledit has a flexible configuration system that allows you to add many code snippets w/o writing a true program, so that even if you decide to start with sqledit due to it’s simplicity you can add more configurations as far as you needs them. I personally started using that as my preferred way.

Sqledit can be used:

- from command line, possibly adding arguments and options
- from a menu entry interactively writing the URL of the database you want to edit.

If you start it with no arguments you are presented a dialog with an entry and 3 buttons:

- you can write the url of a database of yours in the entry, e.g.:

      postgres://localhost/dbname     # sqlalchemy 5
      postgresql://localhost/dbname   # sqlalchemy 6+
      sqlite:///db.sqlite
      mysql://name:pass@host/dbname

  Note

  the URL for a sqlite database has 3 ‘/’if the database is in your current directory, 4 if you need to pass a file starting with ‘/’.

- start the demo tour

## Sqledit table listing

The demo tour is meant for developers, so that it shows source code as well, but it’s also suitable for our introduction and is a living database, so we will use that in this tutorial.

The demo presents you some examples on the left. Let’s start with... the last one! We start with the last one because it’s the window you will see when you start sqledit with an address of a real database (the demo one in this case).

### The table listing

The table listing of the database is shown above: clicking on a table name pops a menu that lets you choose between:

- table view: representation of the table in a spreadsheet fashion
- mask view: a form with each field is displayed
- table reflection: sqledit reads the definition for that table

## Tables

Let’s choose a table view:

each field of the table is represented in a column, each type has different representations:

text: | a simple cell will render the text
numbers: | each number is adjusted to the right
dates: | dates are represented in you preferred locale that is argued from LANG variable or from locale module information
boolean: | a checkbox is used. It the NULL value is accepted, clicking the checkbox will loop between True, False and undefined
intervals: | intervals are really poorly rendered at the moment...

foreign keys:

foreign keys are represented via the value they point to in the remote table. At present only simple (not compound keys) are allowed. To help you detect that that’s a ForeignKey it’s drawn in blue. Just to be pedantic: you won’t see the real value (that may happen to be an id, normally not very interesting), you will rather see the value it points to...

As you can realize there is not real value where is points. An id points to a record of a table (e.g.: director id 1 may point to the record in director table where `last_name` is Fellini), but Fellini is not the value of the id: it’s rather a representation of the record that in many circumstances may be enough (and in many other is not).

So I introduced a rule: I represent it with the value of the first character field of the line. Clearly this rules is doomed to fail in some cases and you can correct it forcing a representation of the line we will call a format field. You can go in the main window of sqledit, select databases and ‘edit sqlkit field’ and you will be presented a mask to edit the value you prefer.

### filtering

you may have a lot of data and what sqlkit will help you at is to filter in a simple way. Each column has a clickable header that pops a menu entry. The first menu entry pops a filter widget:

in the image we have clicked on three column’s header: the filter on each column is composed of 4 parts: the label with the name, the operator for the filter, the checkbox to disable the filter and the entry for a value.

Some operators have pretty intuitive operators (‘>’ as bigger than or later that for dates) text have also regular expression (normally much more useful so that it’s the default) or `like`.

Note

you can select more filter for column, click on the label in the filter panel. You can for example say that you want all the films produced between 2000 and 2005, that means having 2 filter on the field year.

Pressing `Enter` on a field or the reload button will run the query and present the selected records in the TableView.

Dates are special in that you often have to filter with dates relative to the moment you do the query (today, this month,...) so that i added some shortcuts to accomplish this task (e.g.: ‘m’ means the beginning of the month). You can read more on this feature in Date filters.

### totals

One more feature of sqlkit that comes very handy is the ability to make totals in the fashion of a spreadsheet. This only works on numbers of course, and you can trigger this feature from the column menu. Since our test database does not have numbers other than for year of production, in the example I joked and computed the total on the column of the year of production. In real cases you will do sum with more interesting data...

Subtotals are a very useful feature of any total, so you can ask sqlkit to create subtotals when some value change (e.g: date, month, year, director...).

### completions

When you enter data in a text entry or in a foreign key, you may find yourself typing something that is already in the database. In this cases you can have sqledit to search that text for you. Really that’s a must for Foreign Keys where you can only pick the data among those proposed.

Since the possible values may be a lot and we don’t want to wast time waiting to retrieve data that would only confuse us, we will require sqledit to show possible values pressing enter in the entry. In this case the text that we may have already entered will be used to filter the possible values and to be more precise:

Shift Enter: | will trigger a search using the text at the beginning of the field
Control Enter: | will trigger a search using a regexp. If you don’t know what a regexp is, consider that as a minimum it will do a search of the string in any position, but can do much more and really also depends on the database backend.
Control Shift: | will disregard what you have already written and do a search on all possible values, thus emulating an `enum` field.

You can find complete information on how to configure Completion in the docs.

### changing view

When in a table view, you may want to jump on a mask view or even keep the two open simultaneously. That can be simply done by clicking with right button in a row: the menu that appears lets you edit the row with a mask. If that’s a ForeignKey column you can even edit the value the foreign key points to.

## Mask

The other view we can use is the mask view. The records are presented by default in a form with the labels on the right and the forms on the left.

Note

This is just a default and the only one possible at the moment, but programmatically you can choose any fancy layout you want, but I won’t digress as I want to limit the information for non developers in his context.

### completion

In this mask you can see that foreign keys use a combo with a completion element popdown. Same shortcut as for the table one are used to complete. A double click on the arrow let you use it as an enum field.

### filters

Filters can be activated clicking on the label. the filter panel will be presented as usual.

The difference is that when the query is issued the result is presented in a tab of the filter panel and you browse the results clicking in the output tab or clicking the forward and backward arrows of the mask.

### layout

If the table has many fields, you may get a layout that is not very usable. This is a limit of the interfaces at the moment, not of the sqlkit package that can handle any fancy layout as you can see looking at the examples of the demo.

The library also allows you to edit related tables (i.e.: director and movies) with no effort, in order to do this you need at least a minimum of programming, namely:

> - defining the model (as per SqlAlchemy)
> - defining the layout (this is very easy and demo has plenty of examples)

These 2 definitions can be written in the configuration for the a nick of sqledit, please read sqledit manual for details on nick configuration.

## The Demo

The demo is a pretty simple way to be introduced to more advanced features that you would only have with a little of programming. I hope it will encourage you to do it and possibly to approach Python.

The very important thing to understand when reading the snippets of the demo is that each time you write the table as a string (e.g: table=’movies’) you will trigger an inspection of the database, but no assumption is made on the relationships between tables. When you pass a mapper or a class (e.g. class_=model.Movie) you are passing possibly more information.

The model in fact (you can go and see in `demo/sql/model/movies.py`) has lines as:

    class Director(Base):
        __tablename__ = 'director'
        id             = Column(Integer, primary_key=True)
        last_name   = Column(String(60), nullable=False)
        first_name  = Column(String(60))
        nation      = Column(String(6))

        movies      = relation('movie', backref='director', cascade='all, delete-orphan',)

where the last line instructs sqlalchemy of the relation existent between the tables, and more: it adds an attribute on the class `Director` that holds all the movies produces by that director (and vice verse thanks to the argument `backref`).

Adding these information makes it possible to used the layout in a mask to produce a mask with director and all the movies, if you are interested in this part... let me know and I will add more info. For the moment I suggest you to go and read more about Relationships

## Feedback

I hope you found this tutorial useful.

If you like this piece of software, have suggestion on how to improve it or improve the tutorial I’d be happy to know

cheers sandro *:-)