Notes on the use of db2fiat.py
Created:           April 2, 2009
Updated:          June 17, 2010


db2fiat.py

 

This is a simple tool for extracting flat files from a database.
It accepts as an argument either a filename as a command line argument:

      db2fiat.py query.sql

or a the literal text of the query in the form

      db2fiat.py --QUERY="text of query string"

Other options:

      -h, --help
      -v, --VERBOSE
      -p parameterfile        Name of a parameter file
      --PARAM=                Override of any PARAM in parameter file

The default parameter file db2fiat.param contains the following PARAM keys:

            QUERY,HOST,USER,PASSWD,DATABASE

Parameter files:

There is a copy of the allowed parameters in db2fiat.param (in the same directory as db2fiat.py.  This file establishes the set of PARAM options allowed.  An override of any of the defaults can optionally be provided by:

      1.  A .db2fiat.param in your ~/ directory

      2.  A db2fiat.param in ./ for a cwd override

      3.  Any file specified using the -p option.

The override mechanism is not hierarchal.  The program chooses the highest level listed above.

Connection information

The parameter file contains the connection information for the database.  For the DLS database, you must use HOST=theta, USER=DLS, PASSWD=XXXXXX, DATABASE=DLS2009a.

DLS  has select and execute privileges on DLS2009a.  If you want to write tables, please ask for an account with those privileges.

Installation:

 

There is a copy of db2fiat.py and the python modules it calls in /afs/soft/bin.  This should be in your path.

You must also have python installed, as well as the MySQLdb database driver for python programs.  You can install this driver using:

      apt-get install python-mysqldb

Specifying a Query:

See http://matilda/dls_schema.notes for information about the tables and view mentioned here.

You can type your query into the command line:

      db2fiat.py --QUERY="Select alpha,delta,mag_isor from PhotoObj limit 100"

You are also allow to specify the query as a file.  Unfortunately, it must at present be in the following multi-line format:

            SELECT alpha,delta,mag_isor
            FROM PhotoObj
            WHERE subfield='F2p23'

because I haven't gotten around to making the parser smart enough to break the query apart.

You can also make a list out of your columns and tables, like:

            SELECT
                  alpha
                  delta
                  mag_isor
            FROM
                  PhotoObj
            WHERE
                  subfield='F2p23' limit 10


You can also append a format to the end of you column name if you want to get Python to format the data:


            SELECT
                  alpha:%.6f
                  delta:%6f
                  mag_isor:%2f
            FROM
                  PhotoObj
            WHERE
                  subfield='F2p23' limit 10

Sorry, no decimal to sexegesimal conversion at the moment.

 

NULL VALUES:

Db2fiat.py allows you to set a default output string for null values on the command line or in the default parameter file:

Db2fiat.py –NULLVALUE=0

Converts all null values into zeros.