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 (To be completed) check here (Using des credentials)
Current version = 1.4.0
-
Oracle Client > 11g.2 (External library, no python) Check here for instructions on how to install these libraries
-
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
-
PyTables (optional, for hdf5 output)
-
future (for python 2/3 compatibility)
-
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
- 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, extense 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
Now easyaccess can be installed using conda out of the box!
conda install easyaccess==1.4.0 -c mgckind
Assuming that easyaccess
is in your path, you can enter the interactive interpreter by calling easyaccess
without any command line arguments:
easyaccess
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.
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.
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 " > "
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
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