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
• Prefefined 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 a valid SQL statement or one these strings:

• tables : List all tables.
• columns TABLESCHEMA.TABLENAME : List columns of a table.

If no SQL-statement is given, the program expects to read a 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, which can be specifed by the command line options --cache and --cachemode or envorinment variables PG_REQUEST_CACHE and PG_REQUEST_CACHEMODE.

The cache directory contains a file "directory.json", which is a JSON file than maps a connection data string and a sql statement to a file and a number of json data files, one for each sql query. 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.

There are three cachemodes:

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.
--man	Display a man page
-u USERNAME, --user USERNAME
	Set the USERNAME.
--password PASSWORD
	Set the PASSWORD.
-s SERVER, --server SERVER
	Set database SERVER.
-p SERVERPORT, --port SERVERPORT
	Set database SERVERPORT.
-i INSTANCE, --instance INSTANCE
	Set name of database INSTANCE.
-x PROFILE, --profile PROFILE
	Use a PROFILE with predefined database connection parameters, one of 'devices2', 'devices2015'. '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.
-o FORMAT, --format FORMAT
	Define the output FORMAT, known: 'csv', 'csv-quoted', 'default', 'json', 'json-full', 'python', '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 CACHEDIRECTORY, --cache CACHEDIRECTORY
	Define a CACHEDIRECTORY
--cachemode CACHEMODE
	Define the CACHEMODE, allowed : 'offline', 'cache' (default) or 'update'
-v, --verbose	Print some diagnostic to stderr.