easyaccess

Enhanced command line SQL interpreter client for astronomical databases.

Python Command Line Interpreter to access Oracle DES DB using cx_Oracle

For a short tutorial check here

Current version = 1.4.3

Requirements

• Oracle Client > 11g.2 (External library, no python) Check here for instructions on how to install these libraries

• cx_Oracle

  Note that cx_Oracle needs libaio on some Linux systems (e.g., #98)

  Note that cx_Oracle needs libbz2 on some Linux systems

• fitsio >= 0.9.6

• pandas >= 0.14

• termcolor

• PyTables (optional, for hdf5 output)

• future (for python 2/3 compatibility)

• requests

• gnureadline (optional, for better console behavior in OS X)

• importlib (This is only needed if running python 2.6)

• Note that you need to install python-future for python2/3 compatibility

Some nice features

• Nice output format (using pandas)
• Very flexible configuration
• Smart tab completion for commands, table names, column names and file paths accordingly
• write output results to csv, tab, fits files or HDF5 files
• load tables from csv, fits or hdf5 directly into DB (memory friendly by using number of rows or memory limit)
• intrinsic db commands to describe tables, own schema, quota and more
• It can be imported as module from python, complete python API
• Can run command directly from command line
• Load sql query from file and/or from editor
• Show the execution plan of a query if required
• Can run python functions inline query
• Many more

Conda installation

Now easyaccess can be installed using conda out of the box!

conda install easyaccess==1.4.3 -c mgckind

Pip installation

easyaccess can also be installed using pip but it'd require the installation of the oracle instant client first

pip install easyaccess==1.4.3

Interactive interpreter

Assuming that easyaccess is in your path, you can enter the interactive interpreter by calling easyaccess without any command line arguments:

    easyaccess

Running SQL commands

Once inside the interpreter run SQL queries by adding a ";" at the end::

    DESDB ~> select ... from ... where ... ;

To save the results into a table add ">" after the end of the query (after ";") and namefile at the end of line

    DESDB ~> select ... from ... where ... ; > test.fits

The file types supported so far are: .csv, .tab, .fits, and .h5. Any other extension is ignored.

Load tables

To load a table it needs to be in a csv format with columns names in the first row the name of the table is taken from filename or with optional argument --tablename

    DESDB ~> load_table <filename> --tablename <mytable> --chunksize <number of rows to read/upload> --memsize <memory in MB to read at a time>

The --chunsize and --memsize are optional arguments to facilitate uploading big files.

Load SQL queries

To load SQL queries just run:

    DESDB ~> loadsql <filename.sql>

or

    DESDB ~> @filename.sql

The query format is the same as the interpreter, SQL statement must end with ";" and to write output files the query must be followed by " > "

Configuration

The configuration file is located at $HOME/.easyaccess/config.ini but everything can be configured from inside easyaccess type:

    DESDB ~> help config

to see the meanings of all the options, and:

    DESDB ~> config all show

to see the current values, to modify one value, e.g., the prefetch value

    DESDB ~> config prefetch set 50000

and to see any particular option (e.g., timeout):

    DESDB ~> config timeout show

Command-line usage

Much of the functionality provided through the interpreter is also available directly from the command line. To see a list of command-line options, use the --help option

    easyaccess --help