Sqledit - the standalone program to browse and edit data

Sqledit is an application that can be used by anybody without any programming skill. Basically is just needs a target database that can be fed throught a GUI or on the command line.

Sqledit is a data editor/browser. At startup it will present a list of all tables that can be selected for editing (choosing SqlMask or SqlTable) or for introspection.

When run from command line it offers several options:

usage: %prog [options] [[url|nick] table]
   -u, --url = URL: an url to open (eg postgres://user:pass@host/dbname)
   -n, --nick = nick: a nick in ~/.sqledit/nicks
   -t, --table = table: open table 'table'
   -m, --mask: open a SqlMask (default is SqlTable)
   -b, --browser: open the table browser reguardless of nick configuration
   -T, --sqltable: open a SqlTable (default when -t is used)
   -d, --dev: open in 'dev' mode
   -D, --debug: print debug
   -g, --gtk-debug: use LogTheMethods
   -S, --sql=statement: execute statement (requires -t)
   -a, --all-tables: read all table on startup (very slow)
   -f, --field_list=fields: comma (or space) separated field list
   -o, --order_by=fields: comma separated field list
   -c, --configure: open SqlMasq on _sqlkit_table or create it
   -v, --version: prin version and exit
   -L, --load: load data when opening a table (if no table is directly
         opened, set LoadData)
   -l, --limit=LIMIT: limit to LIMIT rows
   -i, --ipython: if ipython is present it opne an ipython shell
   -C, --create-templates=mode: create in one of these modes: layout,
         hooks, programm, models, all
   , --create-tables: Create all tables in models defined in metadata (models.metadata or Base.metadata)

If called without argument it will present a connection dialog:

If sqledit can find a demo in your systems, it will give you the chance to start it. If you want to type the URL of your db you can directly attempt a connection or disable it via a checkbox on the right of the entry.

The autoconnect mode is really nice but you may experiment hangs till a connection timeout if you write a wrong host name.

Since database urls are not nice to write, you can store data in a configuration file and call nick names instead.

.sqledit

You can write configurations in a file in your home called .sqledit/nicks and start sqledit on that configuration using a nickname:

[invitati]
URL = postgres://sandro:xxx@my_host/2giugno
table = partecipazioni_invitato
dev = True
field_list = nome, cognome, and all other fields
order_by = cognome, nome

[brasile]
copy = invitati
field_list = nome, cognome, email, ludo

Valid option are URL and any other option for sqledit. The special option copy force sqledit to read the other definition first and then overwrite. In this case ‘brasile’ shares all options with ‘2giugno’ but overwrites field_list.

Even if you don’t want to program in Python you may want to add configuration in a more rich way that allowed in .sqledit/nicks. That can add for example layout information or information on the relations between tables so that a Mask can present a ecord and data related to it.

Let’s say that that’s a gentle introduction to programming with sqlkit...

Nick extended configuration

Sqledit configuration may be extended beyond .sqledit/nicks file. You can add configurations for a nick in a subdirectory of .sqledit named as the nick itself. Hooks, layout, or a program can be placed directly hereq. As an example if you have a nick ‘film’ defined in .sqledit/nicks:

[film]
url = 'postgres://localhost/film

you can add customizations w/o creating a real program. Currently you can add:

layout.py:

the layout that SqlMask should use when creating the GUI. You can create an empty template for this with sqledit --create-template layout nick. That’s the place where you would register your layout.

This is autoloaded if no program.py is present

hooks.py:

the Hooks that will be used as default. You can create an empty template for this with sqledit --create-template hooks nick. That’s the place where you would register yout hooks.

This is autoloaded if no program.py is present

models.py:

this file is for definition of the classes. If you define classes here you can set Relationships between them so that a mask can display parent/child records as director/movies. Many examples in the demo.

This is autoloaded if no program.py is present

You can create an empty template for this with sqledit --create-template models nick

program.py:

a program that will be executed instead of normal sqledit. There is no particular advantage running this instead of creating a real standalone program but sometimes it just grows step by step (you start with a simple nick, than you add a layout, maybe a hook and you need more customization).

You will call the program as sqledit film and sqledit will:

• cd to ~/.sqledit/film
• execfile(‘program.py’, {‘opts’ : opts})
• no other file will be autoloaded in this case

where opts is a class that holds the options you set in the nick definition (among which opts.url). You can create an empty template for this with sqledit --create-template hooks nick.

A second reason is that many times a program that uses sqlkit is a very short script and I found myself spending more time deciding a proper name and place, this already makes these decisions for you!

There is no need to start gtk.main() since this is no longer the main program, sqledit will run it before this moment

schema browser

Introspection of the database will give you the possibility to see all fields of a table showing all fields, with type, primary keys, foreign keys and indexes. If you configured a nick to jump directly on a table or any other configuration allowed by sqledit customization, you’ll need the -b (–browser) option to get to the schema browser.

Options

Calling sqledit from a command line under a Linux system with bash completion you can benefit from the completion that will look for completion in the .sqledit/nicks file and will suggest some common url (postgresql://, sqlite://...)

When primary keys are numeric you probably don’t want/need to see them, you can switch off the visualization with the primary key toggle button

The Load toggle button determines if you want to load data when opening a table.

Blank toggle button determines if you want to cast blank string fields to NULL values. When you decide to cast it you may be prompted several times if you want to save changes that you are not even aware of.

Configuring sqlkit

Sqlkit looks for possible configuration options in some tables, that may or not be present: _sqlkit_table, and _sqlkit_field.

These tables can be edited directly from the database menu, or via <Ctrl-e> shortcut.

Completion will help yo configure the fields. Here is the meaning:

table’s field

name:

the table’s name

search field:

this is the string field that will be used when searching via foreign key. Suppose you are editing a table of movies, and you must fill in the director’s field. You write some letters and trigger a completion, that means you want sqlkit to use that text (e.g. “Fel”), select which directors are present that has that string in... well you surely want to search in the last name, but you need to tell sqlkit.

search_field is here for that.

format:

ok, you get back from completion a list of directors you still need to show them in a nice way (e.g. first_name, last_name). Here you are supposed to used the syntax “%(field_name)s”.

attributes’fields

name:the field_name description:the label you want to be used. (Note that when using related tables you may indicate relation.field_name) help_text:this is the tooltip that will be added to the entry autostart:you can set an Autocompletion value for the completion