SQL Data in List Dialogs

SQL command-line utilities can output SQL queries to a Zenity list dialog. Like the earlier CSV examples, the SQL output needs to reformatted to a sequential list. The SQL output from the command-line tools will vary by database; for example, MySQL uses tabs between the fields, whereas SQLite uses a vertical bar (|).

For my testing, I used an SQLite3 database (someuser.db) with a table (users) of fields containing first and last names, age, and job. To output a SELECT query, I entered:

$ sqlite3 someuser.db "select fname,lname,age,job from users"
Brooke|Metcalfe|18|Student
Leah|Metcalfe|18|Co-op
Pete|Metcalfe|100|Old dude
...
Willy|Coyote|99|Evil genius

The SQLite query output can be modified with the tr command and shown in a Zenity list dialog (Figure 8):

$ sqlite3 someuser.db "select fname,lname,age,job from users" | tr '|' '\n' | zenity --list --title="My Database" --column="first name" --column="last name" --column=age --column=job

Figure 8: List dialog showing SQL query.

The Zenity list dialog supports a number of useful options, such as radio buttons and checkboxes. The lists are editable, and the selected fields or rows can be used in further scripting.

Insert SQL Data in a Zenity Form

Zenity forms allows for the creation of basic data entry dialogs. In about eight lines of Bash code (Listing 3), I created a Zenity form (Figure 9) I can use to insert data into my SQLite users table.

01 #!/bin/bash
02 row=$(zenity --forms --title="Create user" --text="Add new user" --add-entry="First Name" --add-entry="Last Name" --add-entry="Age" --add-entry="Job" --separator="','")
03 if [[ -n $row ]] # Some data found
04 then
05   cmd="sqlite3 someuser.db  \"INSERT INTO users (Fname,Lname,Age,Job) VALUES ('$row')\""
06   eval $cmd
07   echo "Added data: '$row'"
08 fi

Figure 9: SQL input form.

The OK button will pass the user-entered data as a string, whereas the Cancel button will not pass any data. An if statement checks to see whether any data has been entered.

The SQL INSERT statement needs VALUES to be in the format

("value1","value2,"value3,"value4")

The formatting can be done by setting the Zenity --separator option to a comma, defined with single quotes within double quotes (line 2).

This example is quite basic, so the next step would be to add data validation.

Final Comments

For simple dialogs, Zenity works amazingly well. I found that as the requirements started to get more complicated, a Python solution appeared to be cleaner and simpler. I was able to control a Raspberry Pi rover in about 20 lines of Bash and Zenity code, but it only took 15 lines of Python and Tkinter code.

There is a Python library (Zenity 2.0) that emulates Zenity, so if you're feeling comfortable with the Zenity dialogs and you don't need complex dialogs, this might be something to consider.

If you are looking for a more complete command-line GUI tool, try YAD [3].