Questions?

Subscribe to out mailing list Google group:

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

Bundles

The only possibly difficult part of installation may be the installation of the dependencies. To bypass this I also provide binaries that bundle together any required software for Linux or Windows (you can download it from the download page). Drivers for PostgreSQL, MySQL and sqlite are included. Note that there bundles are not updated very frequently.

The hard way

If you prefer to do it the hard way, or if you meet problems with the bundle, or you need more backend drivers, you need to do the following steps.

Dependencies

Sqlkit depends on several 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.

If you use python 2.6 you may have some warning but should just work.

PyGTK:

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

apt-get install python-gtk2

In a Windows system it’s a much more difficult task. 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. SqlKit has not yet been ported to sqlalchemy 0.6.

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. The bundle has drivers for postgresql and mysql as well.

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

Installing under Debian/Ubuntu

If you have a Debian like distro I suggest you install sqlalchemy from source or via easy_install or pip (preferred) and then install the Debian package:

sudo wget http://apt.argolinux.org/sqlkit.list --output-document=/etc/apt/sources.list.d/sqlkit.list
sudo wget -O-  http://apt.argolinux.org/dists/lenny/public.key| sudo apt-key add -
sudo apt-get update

and install it via:

apt-get install python-sqlkit

This as well as the bundles is not as updated as python packages

sqledit/sqlkit

../_images/sqledit.png

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
    sqledit:///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:

../_images/table.png

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.

../_images/sqledit_config.png

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:

../_images/filter-panel.png

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

../_images/totals.png

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

../_images/mask.png

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 *:-)