SQL command line interpreter for astronomical surveys
Clone or download
Latest commit d57bca5 Oct 4, 2018
Permalink
Failed to load latest commit information.
bin change mode executable Apr 6, 2017
config/conda update conda files Oct 4, 2018
data add new gif Oct 4, 2018
easyaccess version 1.4.5 Oct 4, 2018
paper add fermilab pub number Oct 2, 2018
tests add desdr to test connections Oct 3, 2018
.gitignore update .gitignore Oct 2, 2018
CHANGES.md update Changes Oct 4, 2018
LICENSE.txt fix License Jun 15, 2018
MANIFEST.in pre-relase 1.3.0-rc0 May 2, 2016
README.md Update Readme Oct 4, 2018
TODO.md update TODO Apr 11, 2017
setup.py version 1.4.0 rc1 Apr 11, 2017

README.md

easyaccess latest release License pypi version

help_screen

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.5

For DES public data release, you can start easyaccess with:

easyaccess -s desdr

To create an account click here.

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)

Some nice features

  • Nice output format (using pandas)
  • Very flexible configuration
  • Smart tab autocompletion for commands, table names, column names, and file paths
  • Write output results to CSV, TAB, FITS, or HDF5 files
  • Load tables from CSV, FITS or HDF5 files directly into DB (memory friendly by using number of rows or memory limit)
  • Intrinsic DB commands to describe tables, schema, quota, and more
  • Easyaccess can be imported as module from Python with a complete Python API
  • Run commands directly from command line
  • Load SQL queries from a file and/or from the editor
  • Show the execution plan of a query if needed
  • Python functions can be run in a inline query

Conda installation

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

conda install easyaccess==1.4.5 -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.5

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