pg_request.py

Perform command line SQL queries on a PostgreSQL database.

Overview

This is port of the old db_request.py tool to python 3. It uses psycopg2 to access a postgreSQL database.

Features:

Predefined profiles of database connection parameters
Predefined SQL queries for inspecting the database
A number of output formats, among them CSV, JSON and Python.
Data from requests can be cached. The program can be used so it tries to read the cache first, before it contacts the database.

The COMMAND

COMMAND must be one of these:

[any SQL statement]
tables : List all tables.
columns TABLESCHEMA.TABLENAME : List columns of a table.
primary-key TABLESCHEMA.TABLENAME : Show primary keys of a table.
dump TABLE [TABLE...] : Dump tables in a single JSON structure

If no COMMAND is given, the program expects to read an SQL statement from standard input.

The connection profile

The database connection arguments can be specified with a profile (option --profile) or separately with command line options --user, --password, --instance, --server and --port. Missing connection arguments are requested interactively on the command line. Command line options take precedence over the specifed profile.

Output formats

Several output formats are supported:

default : Print row elements as raw (python) strings separated with spaces.
table : Print a nice table with aligned columns.
python : Print result as a python structure which is list of tuples.
json : Print result as a JSON structure.
json-full : Print result as a full key-value JSON structure.
csv : Print comma separated values with minimal quoting.
csv-quoted : Print comma separated values, everything quoted.

Cache

The program can cache data from sql requests in files in a cache directory or in single separate cache files.

Cache directory

When a cache directory shall be used, option '--cache' or environment variable PG_REQUEST_CACHE must be set to an existing directory.

The cache directory contains a file "directory.json", which is a JSON file that maps a connection data string to a dictionary. This dictionary maps sql statement to files. The data files are named HASH1-HASH2.json where HASH1 is an MD5 hash on the connection data string and HASH2 is an MD5 hash on the sql query string.

Cache file

When a cache file shall be used, option '--cachefile' or environment variable PG_REQUEST_CACHEFILE must be set.

In this case the result of an SQL query is stored in a single JSON file.

Cache mode

The cache mode must be specified with option '--cachemode' or environment variable PG_REQUEST_CACHEMODE.

The following cache modes are defined:

off: Do not use the cache.
offline: Only read data from the cache, do never query the database. An error is returned when the data for a query is not the cache.
cache: Try to read the cache first. If data is missing, query the database and add the data to the cache.
update: Always query the database and put all data in the cache.

Command line options

usage: pg_request.py [OPTIONS] COMMAND

options:

`-h, --help`	show this help message and exit
`--doc`	Create online help in restructured text format. Use "pg_request.py --doc \| rst2html" for creation of html help.
`--version`	Display program version.
`--man`	Display a man page
`-u, --user USERNAME`
	Set the USERNAME.
`--password PASSWORD`
	Set the PASSWORD.
`-s, --server SERVER`
	Set database SERVER.
`-p, --port SERVERPORT`
	Set database SERVERPORT.
`-i, --instance INSTANCE`
	Set name of database INSTANCE.
`-x, --profile PROFILE`
	Use a PROFILE with predefined database connection parameters, one of 'devices2', 'devices2015', 'devices2015_sqlite'. 'devices2015' is the default if this option is not given.
`-X, --no-profile`
	Do not use the default profile. You must specify the connection parameters by separate command line options or interactively in this case.
`-f, --file FILE`
	Set the filename of an sqlite database. When file is 'DATABASE.db', database schemas are loaded from files named DATABASE.SCHEMA.db when they are found in the same directory as FILE.
`-o, --format FORMAT`
	Define the output FORMAT, known: 'csv', 'csv-quoted', 'default', 'json', 'json-full', 'python', 'raw', 'table'.
`--header`	Add column header.
`--profiles`	List properties of known connection profiles.
`--csv`	Define output format to csv.
`--csv-quoted`	Define output format to csv-quoted.
`--python`	Define output format to python.
`--table`	Define output format to table.
`--json`	Define output format to json.
`--json-full`	Define output format to json with a full key-value structure.
`-c, --cache CACHEDIRECTORY`
	Define a CACHEDIRECTORY
`-C, --cachefile CACHEFILE`
	Define a CACHEFILE
`--cachemode CACHEMODE`
	Define the CACHEMODE, allowed : off, offline, cache, update.
`-v, --verbose`	Print some diagnostic to stderr.
`--exception`	do not catch exceptions (for debugging).