NOTE: This program has been superseded by JDBClient, a cross-platform Java program that is better in every way.

Up front — this program only runs under Linux. Sorry.

For more depth about databases and SQL, be sure to visit my MySQL tutorial.

DBClient is a Python 2.7 program that runs on Linux and other Unices. It is released under the GPL, it is Copyright © 2012, P. Lutus, and it's free.

DBClient requires:

• access to a MySQL server,
• Python 2.7 ("yum install python"),
• MySQLdb ("yum install MySQL-python")
• and GTK3 ("yum install gtk3")

These packages provide the base language, MySQL interfacing, and the GUI respectively.

Here are the download packages:

• The DBClient TAR archive containing DBClient.py and DBClient_gui.glade (both need to be present for DBCLient to run).
• The Python DBCLient source file in plain-text form, just for browsing, not running.

To run DBClient, put the two files of the TAR archive into a single directory and, from that directory:

$ ./DBClient.py

The above can be placed in a shell script, but remember that the working directory must be set to the directory containing the two program files DBClient.py and DBCLient_gui.glade.

If run with no command-line arguments, DBClient shows its setup and login page with its editing cursor focused on the password field. In most cases DBCLient can get most of the information it needs from the system — a default server (localhost) and a default user (the current user) — and it then tries to list available MySQL databases. If it succeeds without a password, then there's either something wrong with your MySQL configuration or you don't have databases of interest to anyone. :)

To provide a password automatically (witout entering it into a dialog) or to specify a server, user, database or table in advance of running DBClient, just launch DBClient with one of these command-line arguments:

Usage: DBClient.py [options]

Options:
  -h, --help            show this help message and exit
  -s SERVER, --server=SERVER
                        specify a server
  -u USER, --user=USER  specify a user
  -p PASSWORD, --password=PASSWORD
                        specify a password (a dialog will appear if one is not
                        provided)
  -d DATABASE, --db=DATABASE
                        specify a database
  -t TABLE, --table=TABLE
                        specify a table
  -r, --read            Read the specified table (like pressing "Query")
  -e, --edit            Enable record editing (off by default, available as a
                        checkbox)
  -i, --instant         Set instant query mode (perform query on each
                        selection or control change)
            

There are security issues with putting a password on the command line (the password appears in the system log as well as in the output of "ps"), so it's not recommended unless you actually have sole control over your computer.

Typically the user will provide a password in one of the above ways, after which DBClient will read a database list, the table list for the first listed database, then will load the first table on the list. At this point the database and table drop-down lists should be populated, so changing databases and tables becomes easy.

Before we delve into DBCLient's features, here's a bit of database terminology.

In broad outline:

• DBClient talks to MySQL, a database engine.
• MySQL communicates with databases, collections of information in table form.
• Databases contain tables like this:

  Name	Age	Favorite Color
  Bruce Callow	13	I haven't decided yet.
  Frank Wright	37	Red .. no, wait ...
  Seymour Hawthorne	82	None of your business.

• Tables contain records, and records contain fields:

  	Field 1	Field 2	Field 3
  Field Names	Name	Age	Favorite Color
  Record 1	Bruce Callow	13	I haven't decided yet.
  Record 2	Frank Wright	37	Red .. no, wait ...
  Record 3	Seymour Hawthorne	82	None of your business.

More exotic databases link tables together relationally, so that specific values that might appear in hundreds of tables only need to be defined in one. But the above is the basic layout of a database, even if a real database might have millions of records. Now we move on to what one can do with the tables in a database.

To read more about databases, visit my MySQL Tutorial.

Overview

Here are some usage notes:

• The normal DBClient operational sequence is:

  • Enter a username and password, which allows access to your databases.
  • Select a database at the upper left.
  • Select a table at the upper right.
  • Optionally, select the "Query" tab and enter search criteria. If no criteria are entered, all records will be read.
  • Press the "Query" button at the lower right to execute the query and display the records that meet the criteria.

• If you want to use the queried records directly:

  • On the "Query" tab, click one of the "Copy TSV", "Copy HTML" or "Browser" buttons to export records to different environments:

    • The "Copy TSV" option makes a copy of the queried records onto the system clipboard, ready for pasting into a spreadsheet program. Most modern spreadsheet programs will readily accept tabbed clipboard content and turn it into columnar data.
    • The "Copy HTML" button creates a properly formatted HTML table enclosed in an HTML page, complete with CSS designed to make the table look good (example on this page). Simply paste the result into your favorite HTML editor.
    • The "Browser" button launches the system browser with a copy of the queried data formatted as an HTML page. This option speeds up the process of reviewing query results, and the HTML page can be saved from the browser.

• If you want to edit records:

  • Perform the steps above until you have a table of records on display.
  • In the table display at the top, click a record of interest — this will open the editing tab.
  • Click the "Allow Edits" checkbox at the lower right. (This feature prevents accidental editing.)
  • Edit the record. Each edited field turns red to indicate that it has unsaved content.
  • When you are satisfied with the result, press "Commit" to save your changes to the database.

To expand on the above, if your goal is extract records from a database, then you can use DBase's SQL Query Tab, described below, to create your query and retrieve the results. But if you want to edit records, how you proceed depends on the size of the tables in your database:

• If your tables are relatively small, then you should be able to:

  1. Enter a password to allow database access.
  2. Select a database at the upper left.
  3. Select a table at the upper right.
  4. Press "Query" at the lower right to read the table.
  5. Click one of the records in the table display to commence editing.

• But if your tables are large, and they take too long to fully load, then:

  • Before step (4) above, click the "Query" tab and enter search criteria to limit the number of records that will be read during editing.
  • Press the "Query" button at the lower right to retrieve the selected records.
  • Proceed as above.

The reason for these steps is that, after each edit, DBClient must read the records from the database to accurately reflect the outcome of the editorial changes. If database tables were simple text and no dynamic content, this step would not be needed, but a modern database can have built-in triggers that update all the fields of a record if any of the fields are edited. Because of this, it's necessary to re-execute the query after each editorial commit.

The SQL Query Tab

Programs like DBClient read and write tables and databases in concert with a database server program like MySQL. Because a table might contain tens of thousands of records, a program like DBClient is able to apply a filter called a "query" to choose records of interest. For example, I might want to know how many U.S. towns are named "Ashland," a town I once lived in. In the old days of hand entries at a terminal, I might enter this SQL (Structured Query Language) query:

SELECT * FROM zipdb.ziptable WHERE city = "ashland"

And I would get this result:

zip	city	state	latitude	longitude	timezone	dst
01721	Ashland	MA	42.257956	-71.45886	-5	1
03217	Ashland	NH	43.705061	-71.62778	-5	1
04732	Ashland	ME	46.64005	-68.42804	-5	1
04752	Ashland	ME	46.602477	-68.410131	-5	1
12407	Ashland	NY	42.321453	-74.33926	-5	1
17921	Ashland	PA	40.763568	-76.34824	-5	1
23005	Ashland	VA	37.754254	-77.47465	-5	1
36251	Ashland	AL	33.243964	-85.84503	-6	1
38603	Ashland	MS	34.839417	-89.15818	-6	1
41101	Ashland	KY	38.473961	-82.64532	-5	1
41102	Ashland	KY	38.439526	-82.6932	-5	1
41105	Ashland	KY	38.370285	-82.694757	-5	1
41114	Ashland	KY	38.370285	-82.694757	-5	1
44805	Ashland	OH	40.867016	-82.31514	-5	1
54806	Ashland	WI	46.577191	-90.89707	-6	1
59003	Ashland	MT	45.486308	-106.33169	-7	1
59004	Ashland	MT	46.017965	-106.99199	-7	1
62612	Ashland	IL	39.891861	-90.03125	-6	1
65010	Ashland	MO	38.784123	-92.24395	-6	1
67831	Ashland	KS	37.196075	-99.77662	-6	1
68003	Ashland	NE	41.068476	-96.38189	-6	1
71002	Ashland	LA	32.129489	-93.08273	-6	1
97520	Ashland	OR	42.169751	-122.61772	-8	1

Even though the above could be gotten at a text-only terminal, the same result is available from DBCLient where stating a query is much easier — it's mostly a matter of pressing buttons and only a bit of typing. In fact, I created the above query result in DBCLient, clicked the DBClient "Copy HTML" button to create and copy an HTML-table version of the query result onto the system clipboard, and pasted it into my Web editor Arachnophilia while designing this page. The entire operation took five minutes.

Exotic queries can be easily entered into DBClient using the query dialog (see Figure 1 above). One can enter an expression for each field in the original table, then link the search terms together using logical AND and OR specifiers, mostly by clicking selectors rather than typing. One can also specify which fields to include in the result.

Query Syntax Notes

Unfortunately, MySQL doesn't provide contextual error messages -- it will only say that something went wrong. This means the user has to try to sort out what went wrong. Here are some hints:

• You can apply mathematical operators to DateTime fields, but only if you're careful:

  • No good: > '06-03-2005' (wrong date format)
  • No good: > 2005-06-03 (no quotes around the date)
  • Good: > '2005-06-03'

• Most string arguments need to be quoted:

  • Bad: = Albany (no quotes around string argument)
  • Good: = 'Albany'
  • Good: = "Albany" (equivalent in most cases)

• Embedded special characters:

  • Bad: = 'That's too bad' (unescaped apostrophe in string)
  • Good: = 'That''s too bad' (escaped apostrophe)
  • Good: = "That's too bad" (alternate quoting character)

When using the internal MySQL query search functions, remember that MySQL search queries are case-insensitive, so "alaska", "Alaska" and "ALASKA" are equivalent. But the "External Regex" option, if enabled, is case-sensitive unless your entry is accompanied by "(?i)".

Exporting Results

Having acquired the desired query result, one can then use DBClient's "Copy TSV", "Copy HTML" or "Browser" buttons to copy query results, for example into a spreadsheet program for further processing or printing, or for incorporation into a Web page. Spreadsheet programs know how to read a tab-separated-value (TSV) table from the system clipboard, so that's an easy export route.

One can also launch the system browser with the query table's contents on display by clicking the "Browser" button. This step also makes an HTML file copy of the displayed content in a subdirectory of the user's home directory named ".DBCLient/web_pages". If the reader makes extensive use of the "Browser" function, it might be a good idea to clean out this directory from time to time (all generated HTML pages are retained).

A subtle point: To get an HTML table without text-wrapped content (that therefore in some cases may extend beyond the right border of your browser window), and before clicking the browser button, unselect the "Ellipsize" checkbox on the table display. On the other hand, to get text-wrapped page content, select "Ellipsize" (the default).

One DBClient query feature merits a detailed comment. The "External Regex" button on the "Query" tab redirects one's query entries to an external regular expression engine that is part of in Python, rather than using the regular expression engine built into MySQL. The advantage is thet, unlike MySQL, Python's regular expression processor understands some POSIX and Perl extensions, which can sometimes increase the descriptive power of a query. The drawback to using this feature is that it is much slower than running a pure MySQL query, because the entire table must be read and then submitted to an external regular expression processor.

Another useful query feature is "Instant", handy for interactively designing queries. "Instant", enabled by checking the query tab's "Instant" checkbox, executes the query after each control change. This feature is obviously meant for relatively small tables because it re-reads the table after each control change.

The Record Editing Tab

The other primary database activity is entering and editing records, and DBclient does this very well. Here's a picture of DBClient in its editor mode:

The field with the red characters has been changed but not yet committed to the database.

Typically one creates a query to find the records of interest (especially important for large tables), then edits them within DBClient. After executing a query, to begin editing a record, select it by clicking the desired row in the table listing at the top of the display (the blue row in the above graphic). This action opens the editing panel (se Figure 2 above) and one can begin editing. The editing panel has options for creating new records within a given table, or copying existing records, in order to efficiently create many records that differ in small ways.

A note about copying records: If you would like to save time and copy an existing record for editing into a new form, it turns out this is only safe for a database in which each record has a unique primary key. The reason is that MySQL cannot recognize a copy of a record as unique unless it has a unique primary key. In fact, without a primary key, MySQL will treat all copies of a given record as the same record, with scary consequences. If you make editorial changes to one copy, the changes will be applied simultaneously to all copies. And worse, if you try to delete one of the copies, all the copies will be deleted at once.

The solution to this problem is to add a primary key field to your table. Adding a primary key to a table is quite easy:

ALTER TABLE dbname.tablename ADD COLUMN pk integer NOT NULL AUTO_INCREMENT PRIMARY KEY

Because of its importance, the above SQL command sequence is provided as a button-press on the table description tab, and it is highly recommended for a number reasons beyond simply being able to make copies of records.

The Table Description Tab

The table description tab lists the present table's field names and particulars. This is an important resource for analyzing databases and tables, and may alert the user to the fact that a particular field may not have the correct data type.

I had considered including the ability to edit the field data types (apart from using the MySQL mini-terminal described below), but I decided that would be too risky for everyday use. It's not difficult to create or modify a table or change a field's data type (see SQL Examples list for the syntax), but this isn't something one wants to undertake lightly.

The MySQL Mini-terminal Tab

The "MySQL Mini-terminal" tab presents an open-ended communications channel with your selected MySQL server. I emphasize about this feature that (a) it allows you to do many sorts of things that move beyond simple queries and record editing, but (b) it can wipe out your databases with a few ill-considered keystrokes.

Among the things the mini-terminal can do are:

• Create, modify and delete databases, tables and views.
• Examine the structure and relations between tables and/or views.
• View tables and views, and their descriptions, in an internal, undecorated form for purposes of analysis and troubleshooting.

In other words, the mini-terminal allows you to do most everything you can do in a MySQL shell session, but with the advantage that you can copy the result of the transactions to the system clipboard for use elsewhere.

I have decided not to write a comprehensive description of each DBClient feature and command — over the years I have come to realize that most people don't read instructions, and in any case, if my program's features and their uses aren't self-evident, then I have failed as a programmer. And I don't think thast's true :).

Instead I will say explore DBClient — most of its features are self-explanatory, and for someone familiar with database activities it shouldn't require more than a few minutes to become productive. And if this isn't the case, I would like to hear from you.

The MySQL organization offers an interesting, free sample relational database, relatively sophisticated and consisting of make-believe movie data, that the reader can use for harmless experimentation. I say "harmless" because if the user inadvertently deletes or changes something that prevents the databse from working, it's a matter of seconds to restore it from source. Here are the steps to get the practice database installed locally:

• Download the MySQL organization's sample database archive sakila-db.tar.gz from here.
• Unpack the archive:
  • tar -xzf sakila-db.tar.gz
• The above command will produce a new directory (sakila-db) containing three files:
  • sakila-db/sakila-data.sql
  • sakila-schema.sql
  • sakila.mwb
• In that new directory, run the mysql command-line interpreter:
  • mysql -p (password)
• Now issue these instructions (green text to the right of the mysql prompt):
  • mysql> source sakila-schema.sql
  • mysql> source sakila-data.sql

If all the above instructions are carried out correctly, a new database named "sakila" will be present in your MySQL database list. The database contains a number of tables and views ("views" are described above in "MySQL Command Summary") typical of a modern database.

The reader should feel free to experiment with this database, be willing to make mistakes, because the database can be easily restored from its source by repeating the above procedure.

DBClient is Copyright © 2012, P. Lutus, it is released under the GPL, and it is free.

(Reverse chronological order)

• 10-24-2012 Version 2.4. Solved some Unicode processing issues.
• 10-05-2012 Version 2.3. Discovered and corrected a bug where, during an edit, the program would update all fields, including unchanged ones. In some relational contexts this doesn't work.
• 10-01-2012 Version 2.2. Fixed some border cases in the string processing code that didn't escape some arguments correctly.
• 09-30-2012 Version 2.1. Now that I have tables with triggers that change content after an edit, I've had to rewrite the editing routines to always re-read the table after each edit (i.e. the description of version 1.9 below is now the default behavior). Users with large tables who want to carry out edits and who don't want long delays on updates, are encouraged to first submit a query that limits the number of listed records. I also increased the number of query input fields (two per field), connected with logical AND/OR selectors.
• 09-27-2012 Version 2.0. Improved control function and layout, tuned the HTML export routine and browser launcher.

• 09-26-2012 Version 1.9. Added a Commit/Refresh button to the editing dialog. Commit/Refresh commits any editorial changes, then re-queries the table. This is for use with tables that have triggers and functions that are executed with any data change, so the editing display remains current.

  This feature is only practical with small tables. In other circumstances, the normal "Commit" button is preferred because it doesn't re-read the table on each commit.

• 09-25-2012 Version 1.8. After accidentally editing a few records I didn't mean to, I've added a checkbox that must be selected to enable editing.
• 09-24-2012 Version 1.7. Fixed some bugs having to do with correctly escaping quotes and backlslashes.
• 09-23-2012 Version 1.6. Changed to not automatically read a table when it's selected. This prevents slow operation on databases with large tables. The original behavior can be restored by selecting "instant mode" at the query tab or by setting command-line option "-i". Otherwise, the first time a table is read is when a query is executed, which allows selection of a subset of records.
• 09-22-2012 Version 1.5. Cleaned up more small bugs, added the ability to easily insert a primary key into a table.
• 09-21-2012 Version 1.4. Cleaned up some bugs having to do with NULL values and table names with spaces, added a browser launch feature for table display.
• 09-21-2012 Version 1.3. Changed the HTML table formatter to accommodate the fact that Microsoft Explorer doesn't understand modern CSS.
• 09-20-2012 Version 1.2. Added table description tab.
• 09-13-2012 Version 1.1. Initial Public Release.