Matias Carrasco Kind

Easyaccess tutorial, DES collaboration meeting, May 11-15, 2015

DES Database

A brief tutorial

DES users

~ 300 DB users

Only few use quota

DB Acess tool

trivialAccess (ad-hoc command line)
Python API (uses cx_Oracle python module and Oracle libs)
sqlplus (Oracle own command line from Oracle libs)
others paid and free... (Toad, SQLalchemy, etc... )

easyaccess (replacing trivialAcess)
Web client (experimental)

trivialAccess

jython and other java tools

Installation and learning curve

No longer supported

easyaccess

https://github.com/mgckind/easyaccess/

EasyAccess confluence page

Some "neat" features

Refactorization of trivialAccess
History of commands
Completely open source and written in python
Smart auto-completion of commands, columns, users, tables and path
Load sql queries directly from a file
Load table directly in to DB in one step
Write tables in csv, tab, fits and hdf5(h5) formats
Can be imported as python module!
Many more...

Installation

Clone it from github
Using pip or easy_install (see requirements)
( e.g., 'pip install easyaccess')
Using EUPS (what's EUPS?)
eups distrib install easyaccess 1.1.0+1
More info here
To run it : "easyaccess". For help : "easyaccess -h"

Thanks to: Alex, Nacho, Felipe, Don, Eli, Liz, Keith, Chris, Lorne and others...

Important files

.desservices.ini : usually at $HOME or defined in $DES_SERVICES

Automatically created (if not found) by easyaccess (read/write by user only)

Check example content below (scroll down)


;
; DES services configuration
; Please modify the passwords accordingly
;

[db-dessci]
user = mcarras2
passwd = ****
name = dessci
server = leovip148.ncsa.uiuc.edu
port = 1521

[db-destest]
user = mcarras2
passwd = ****
name = destest
server = leovip148.ncsa.uiuc.edu
port = 1521

[db-desoper]
user = mcarras2
passwd = ****
name = desoper
server = leovip148.ncsa.uiuc.edu
port = 1521

Important files

config.ini : usually at $HOME/.easyaccess/config.ini

Automatically created (if not found) by easyaccess

Configuration file for easyaccess (can be modified manually or from inside easyaccess)


#config.ini
#
# Easyaccess default parameters
#
# database       : Default is dessci, change to desoper, destest and others
#                  Make sure the db-"database" section is in the .desservices.ini
# editor         : Default editor to open from inside easyaccess if $EDITOR is not set
# prefetch       : Prefetch number of rows to get from oracle (not the number of total rows)
#                : This determine the number of trips to the DB to get all results from query
# histcache      : The number of line in the history cache (when possible)
# timeout        : The time in seconds before closing a connection for a query to print on screen
#                  If the results are redirected to a file there is not a timeout
# nullvalue      : The value used to replace null or empty entries when printing into a file
# outfile_max_mb : Max size of each fits file in MB (default 1GB)
# autocommit     : Auto commit changes in DB (default yes)

[easyaccess]
database = dessci
editor = nano
prefetch = 10000
histcache = 5000
timeout = 900
nullvalue = -9999
outfile_max_mb = 1000
autocommit = yes

[display]
color_terminal = yes
loading_bar = yes
max_rows = 1500
width = 1000
max_columns = 50

Basic usage

DESDM ~> SELECT ... FROM ... WHERE ... ;

bash$ easyaccess -c "SELECT ... FROM ... WHERE ... ;"

(Don't forget the ";" after the query)


_________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11; < check

Checking statement...
Ok!

_________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11;

    |☆             |  Press Ctrl-C to abort    Rows : 10, Avg time (rows/sec): 73.7


10 rows in 0.14 seconds

    COADD_OBJECTS_ID         RA        DEC  MAG_AUTO_G      TILENAME
1         3120938977   9.553670 -52.858189     99.0000  DES0037-5248
2         3120944503   9.999752 -52.754332     22.4878  DES0037-5248
3         3120954907   9.114992 -52.555158     25.3244  DES0037-5248
4         3120936831   8.994403 -52.899365     24.9927  DES0037-5248
5         3120952757   9.427335 -52.595066     23.6036  DES0037-5248
6         3120045269  10.008397 -42.256504     24.9008  DES0041-4206
7         3120047663  10.084474 -47.183702     24.6363  DES0041-4706
8         3120047896  10.556631 -47.180639     23.4764  DES0041-4706
9         3120055956  10.197952 -47.100950     26.3588  DES0041-4706
10        3120066754  10.863236 -46.987299     21.7274  DES0041-4706

Inside easyaccess

(Press down arrow for more and get insight from commands)

help (or ?)

type help {command} for more info. e.g. DESDM ~> help prefetch


_________
DESDB ~> help

     \‾‾‾‾‾‾\
  // / .    .\
 // /   .    _\
// /  .     / //
\\ \     . / //
 \\ \_____/ //
  \\_______//    DARK ENERGY SURVEY
   `‾‾‾‾‾‾‾´     DATA MANAGEMENT

 *General Commands* (type help ):
===================================================
clear  config  edit  exit  help  history  prefetch  shell


 *DB Commands*      (type help ):
====================================================
add_comment              find_user   mytables                show_index
describe_table           load_table  refresh_metadata_cache  user_tables
find_tables              loadsql     set_password            whoami
find_tables_with_column  myquota     show_db

 *Default Input*
===================================================
* To run SQL queries just add ; at the end of query
* To write to a file  : select ... from ... where ... ; > filename
* Supported file formats (.csv, .tab., .fits, .h5)
* To check SQL syntax : select ... from ... where ... ; < check

* To access an online tutorial type: online_tutorial
_________
DESDB ~>

config

e.g. to disable loading bar : DESDM ~> config loading_bar set no


_________
DESDB ~> config

        Change parameters from config file (config.ini). Smart autocompletion enabled

        Usage:
            - config  show : Shows current value for parameter in config file
                e.g.
            - config  set  : Sets parameter to given value
            - config all show: Shows all parameters and their values
            - config filepath: Prints the path to the config file

        Parameters:
            database          : Default DB to connect to
            editor            : Editor for editing sql queries, see --> help edit
            prefetch          : Number of rows prefetched by Oracle, see --> help prefetch
            histcache         : Length of the history of commands
            timeout           : Timeout for a query to be printed on the screen. Doesn't apply to output files
            nullvalue         : value to replace Null entries when writing a file (default = -9999)
            outfile_max_mb    : Max size of each fits file in MB
            max_rows          : Max number of rows to display on the screen. Doesn't apply to output files
            width             : Width of the output format on the screen
            max_columns       : Max number of columns to display on the screen. Doesn't apply to output files
            color_terminal    : yes/no toggles the color for terminal std output. Need to restart easyaccess
            loading_bar       : yes/no toggles the loading bar. Useful for background jobs
            autocommit        : yes/no toggles the autocommit for DB changes (default is yes)

clear (or .)

Clears the screen
edit

Opens up an editor window to write query
exit (or quit, or ^D)

Exits easyaccess and save changes and history
history

Prints the history of the commands prompted
shell (or !{command})

Access the shell, e.g. DESDM ~> shell pwd, or DESDM ~> !pwd
prefetch

Shows, sets or sets to default the number of prefetch rows from Oracle

describe_table


_________
DESDB ~> describe_table MINI_Y1A1

   Rows : 10, Avg time (rows/sec): 159.5

Description of MINI_Y1A1 commented as: '"small random subsample from Y1A1"'

          COLUMN_NAME DATA_TYPE DATA_FORMAT COMMENTS
1    COADD_OBJECTS_ID    NUMBER     22,11,0     Null
2                  RA    NUMBER      22,9,6     Null
3                 DEC    NUMBER      22,9,6     Null
4          MAG_AUTO_G    NUMBER      22,6,4     Null
5          MAG_AUTO_R    NUMBER      22,6,4     Null
6          MAG_AUTO_I    NUMBER      22,6,4     Null
7            TILENAME  VARCHAR2        12,,     Null
8   MAGERR_DETMODEL_I  VARCHAR2        30,,     Null
9             FLAGS_I    NUMBER      22,3,0     Null
10      XCORR_SFD98_G    NUMBER     22,10,6     Null

_________
DESDB ~>

find_tables


_________
DESDB ~> find_tables %Y1A1%

   Rows : 23, Avg time (rows/sec): 334.8


23 rows in 0.07 seconds

                  TABLE_NAME
1                Y1A1_RUNTAG
2               Y1A1_MOLYGON
3            Y1A1_MANGLE_RUN
4              Y1A1_FINALCUT
5          Y1A1_COADD_PHOTOZ
6                Y1A1_CCDGON
7                 Y1A1_COADD
8                Y1A1_PSF_QA
9               Y1A1_OBJECTS
10            Y1A1_COADD_SPT
11       Y1A1_MOLYGON_CCDGON
12    Y1A1_COADD_OBJECTS_D04
13              Y1A1_CATALOG
14            Y1A1_COADD_SRC
15        Y1A1_COADD_OBJECTS
16  Y1A1_COADD_OBJECTS_DFULL
17                 MINI_Y1A1
18          Y1A1_EXPOSURETAG
19             Y1A1_EXPOSURE
20       Y1A1_COADD_STRIPE82
21    Y1A1_COADD_OBJECTS_D10
22        Y1A1_FIRSTCUT_EVAL
23                Y1A1_IMAGE

find_tables_with_column


_________
DESDB ~> find_tables_with_column %TPZ%

    |            ☆ |  Press Ctrl-C to abort    Rows : 26, Avg time (rows/sec): 18.7


26 rows in 1.40 seconds

                          TABLE_NAME   COLUMN_NAME
1                   SVA1_GOLD_PHOTOZ     TPZ_ZCONF
2                   SVA1_GOLD_PHOTOZ        TPZ_ZP
3                   SVA1_GOLD_PHOTOZ     TPZ_ZPERR
4               SVA1_GOLD_PHOTOZ_PDF           TPZ
5            SVA1_GOLD_PHOTOZ_SPARSE           TPZ
6                      TPZ_SVA1_GOLD  TPZ_SG_CLASS
7                      TPZ_SVA1_GOLD     TPZ_ZCONF
8                      TPZ_SVA1_GOLD      TPZ_ZERR
9                      TPZ_SVA1_GOLD     TPZ_ZPHOT
10                 Y1A1_COADD_PHOTOZ     TPZ_ZCONF
11                 Y1A1_COADD_PHOTOZ        TPZ_ZP
12                 Y1A1_COADD_PHOTOZ     TPZ_ZPERR
13                             ZBINS     TPZ_ZVALS
14         mcarras2.SVA1_GOLD_PHOTOZ     TPZ_ZCONF
15         mcarras2.SVA1_GOLD_PHOTOZ        TPZ_ZP
16         mcarras2.SVA1_GOLD_PHOTOZ     TPZ_ZPERR
17     mcarras2.SVA1_GOLD_PHOTOZ_PDF           TPZ
18  mcarras2.SVA1_GOLD_PHOTOZ_SPARSE           TPZ
19            mcarras2.TPZ_SVA1_GOLD  TPZ_SG_CLASS
20            mcarras2.TPZ_SVA1_GOLD     TPZ_ZCONF
21            mcarras2.TPZ_SVA1_GOLD      TPZ_ZERR
22            mcarras2.TPZ_SVA1_GOLD     TPZ_ZPHOT
23        mcarras2.Y1A1_COADD_PHOTOZ     TPZ_ZCONF
24        mcarras2.Y1A1_COADD_PHOTOZ        TPZ_ZP
25        mcarras2.Y1A1_COADD_PHOTOZ     TPZ_ZPERR
26                    mcarras2.ZBINS     TPZ_ZVALS

find_user, user_tables


_________
DESDB ~> find_user sevilla

    | ☆            |  Press Ctrl-C to abort    Rows : 1, Avg time (rows/sec): 3.6


1 rows in 0.28 seconds

   USERNAME               EMAIL FIRSTNAME LASTNAME
1  nsevilla  nsevilla@gmail.com   Ignacio  Sevilla

_________
DESDB ~> user_tables nsevilla

Public tables from NSEVILLA
                          0
0             SVA1_GOLD_REP
1  Y1A1_STRIPE82_MATCHES_V1
2  Y1A1_STRIPE82_MATCHES_V2
_________
DESDB ~>

whoami, myquota, mytables


_________
DESDB ~> whoami

   Rows : 1, Avg time (rows/sec): 16.0

   USERNAME                       EMAIL FIRSTNAME LASTNAME
1  mcarras2  mcarras2@ncsa.illinois.edu    Matias     Kind

_________
DESDB ~> myquota

    |     ☆        |  Press Ctrl-C to abort    Rows : 1, Avg time (rows/sec): 1.6

  TABLESPACE_NAME  GBYTES_USED  GBYTES_LEFT
1           USERS    34.363403   215.636597

_________
DESDB ~> mytables

    |   ☆          |  Press Ctrl-C to abort    Rows : 11, Avg time (rows/sec): 22.1

List of my tables

                 TABLE_NAME
1                     ZBINS
2   SVA1_GOLD_PHOTOZ_SPARSE
3          SVA1_GOLD_PHOTOZ
4                 MINI_Y1A1
5           FGOTTENMETADATA
6         Y1A1_COADD_PHOTOZ
7               SPARSE_DICT
8                  MY_TABLE
9              SPEC_MATCHED
10     SVA1_GOLD_PHOTOZ_PDF
11            TPZ_SVA1_GOLD

_________
DESDB ~>

show_db, set_password


_________
DESDB ~> show_db

user: MCARRAS2
db  : DESSCI
host: LEOVIP148.NCSA.UIUC.EDU

Personal links:

   OWNER  DB_LINK  USERNAME  HOST  CREATED
No rows selected

_________
DESDB ~> set_password

Enter new password:
Re-Enter new password:
Password changed in DESSCI

Password changed in DESOPER

_________
DESDB ~>

refresh_metadata_cache

This refresh the cache with tables, columns and users for autocompletion


_________
DESDB ~> refresh_metadata_cache
Updated 11.33 hours ago

Re-creating metadata table ...

    | ☆            |  Press Ctrl-C to abort
FGOTTENMETADATA table Created!

Loading metadata into cache...
_________
DESDB ~>

show_index

Describes the indices in a TABLE as column_name, oracle_Type, date_length, comments


_________
DESDB ~> show_index Y1A1_COADD_PHOTOZ

    |   ☆          |  Press Ctrl-C to abort    Rows : 1, Avg time (rows/sec): 2.3


1 rows in 0.44 seconds

          TABLE_NAME       COLUMN_NAME INDEX_TYPE      INDEX_NAME ITYP_NAME
1  Y1A1_COADD_PHOTOZ  COADD_OBJECTS_ID     NORMAL  SYS_C001624313      Null

Special commands

To download a table from DB, to upload tables from files, to add comments

(Press down arrow)

Save results to a file

DESDM ~> SELECT ... FROM ... WHERE ... ; > file.csv

Supported output files (.csv, .fits, .tab, .h5)


            _________
DESDB ~> select COADD_OBJECTS_ID, RA, DEC, MAG_AUTO_G, TILENAME from MINI_Y1A1 where rownum < 11; > test.tab

Fetching data and saving it to test.tab ...


    |         ☆    |  Press Ctrl-C to abort    Rows : 10, Avg time (rows/sec): 9.3

 Written 10 rows to test.tab in 1.08 seconds and 1 trips


_________
DESDB ~> shell more test.tab
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
3120938977 9.55367000 -52.85818900 99.00000000 DES0037-5248
3120944503 9.99975200 -52.75433200 22.48780060 DES0037-5248
3120954907 9.11499200 -52.55515800 25.32439995 DES0037-5248
3120936831 8.99440300 -52.89936500 24.99270058 DES0037-5248
3120952757 9.42733500 -52.59506600 23.60359955 DES0037-5248
3120045269 10.00839700 -42.25650400 24.90080070 DES0041-4206
3120047663 10.08447400 -47.18370200 24.63629913 DES0041-4706
3120047896 10.55663100 -47.18063900 23.47640038 DES0041-4706
3120055956 10.19795200 -47.10095000 26.35880089 DES0041-4706
3120066754 10.86323600 -46.98729900 21.72739983 DES0041-4706

_________
DESDB ~> !head -n 2 test.tab
COADD_OBJECTS_ID RA DEC MAG_AUTO_G TILENAME
3120938977 9.55367000 -52.85818900 99.00000000 DES0037-5248

Upload a table from a file

Supported formats: csv, fits
Table name taken from filename, to drop a table: DESDM ~> DROP TABLE {table};
DESDM ~> load_table {filename}
To share table: DESDM ~> grant select on {tablename} to DES_READER;


_________
DESDB ~> !more michigan.csv
COADD_OBJECTS_ID,RA,DEC,MAG_AUTO_G,TILENAME
3120938977,9.55367000,-52.85818900,99.00000000,DES0037-5248
3120944503,9.99975200,-52.75433200,22.48780060,DES0037-5248
3120954907,9.11499200,-52.55515800,25.32439995,DES0037-5248
3120936831,8.99440300,-52.89936500,24.99270058,DES0037-5248
3120952757,9.42733500,-52.59506600,23.60359955,DES0037-5248
3120045269,10.00839700,-42.25650400,24.90080070,DES0041-4206
3120047663,10.08447400,-47.18370200,24.63629913,DES0041-4706
3120047896,10.55663100,-47.18063900,23.47640038,DES0041-4706
3120055956,10.19795200,-47.10095000,26.35880089,DES0041-4706
3120066754,10.86323600,-46.98729900,21.72739983,DES0041-4706
_________
DESDB ~> load_table michigan.csv

  Table MICHIGAN created successfully with 10 rows and 5 columns in 0.35 seconds

 You might want to refresh the metadata (refresh_metadata_cache)
 so your new table appears during autocompletion

To make this table public run:

   grant select on MICHIGAN to DES_READER;

_________
DESDB ~> describe_table michigan

    |    ☆         |  Press Ctrl-C to abort    Rows : 5, Avg time (rows/sec): 9.0

Description of MICHIGAN commented as: 'None'

        COLUMN_NAME      DATA_TYPE DATA_FORMAT COMMENTS
1  COADD_OBJECTS_ID         NUMBER       22,,0     Null
2                RA  BINARY_DOUBLE         8,,     Null
3               DEC  BINARY_DOUBLE         8,,     Null
4        MAG_AUTO_G  BINARY_DOUBLE         8,,     Null
5          TILENAME       VARCHAR2        12,,     Null

_________
DESDB ~> select * from michigan

Invalid command or missing ; at the end of query.
Type help or ? to list commands

_________
DESDB ~> select * from michigan;

    | ☆            |  Press Ctrl-C to abort    Rows : 10, Avg time (rows/sec): 38.6


10 rows in 0.26 seconds

    COADD_OBJECTS_ID         RA        DEC  MAG_AUTO_G      TILENAME
1         3120938977   9.553670 -52.858189   99.000000  DES0037-5248
2         3120944503   9.999752 -52.754332   22.487801  DES0037-5248
3         3120954907   9.114992 -52.555158   25.324400  DES0037-5248
4         3120936831   8.994403 -52.899365   24.992701  DES0037-5248
5         3120952757   9.427335 -52.595066   23.603600  DES0037-5248
6         3120045269  10.008397 -42.256504   24.900801  DES0041-4206
7         3120047663  10.084474 -47.183702   24.636299  DES0041-4706
8         3120047896  10.556631 -47.180639   23.476400  DES0041-4706
9         3120055956  10.197952 -47.100950   26.358801  DES0041-4706
10        3120066754  10.863236 -46.987299   21.727400  DES0041-4706

Add comments or descriptions

Add comments to columns in table created

Add comments to table itself


_________
DESDB ~> describe_table michigan

    |             ☆|  Press Ctrl-C to abort    Rows : 5, Avg time (rows/sec): 3.3

Description of MICHIGAN commented as: 'None'

        COLUMN_NAME      DATA_TYPE DATA_FORMAT COMMENTS
1  COADD_OBJECTS_ID         NUMBER       22,,0     Null
2                RA  BINARY_DOUBLE         8,,     Null
3               DEC  BINARY_DOUBLE         8,,     Null
4        MAG_AUTO_G  BINARY_DOUBLE         8,,     Null
5          TILENAME       VARCHAR2        12,,     Null

_________
DESDB ~> add_comment column MICHIGAN.COADD_OBJECTS_ID id

    |            ☆ |  Press Ctrl-C to abort
Comment added to column: COADD_OBJECTS_ID in table MICHIGAN

_________
DESDB ~> add_comment column MICHIGAN.ra position in the sky -- ra

    |   ☆          |  Press Ctrl-C to abort
Comment added to column: ra in table MICHIGAN

_________
DESDB ~> add_comment column MICHIGAN.dec position in the sky -- dec

    | ☆            |  Press Ctrl-C to abort
Comment added to column: dec in table MICHIGAN

_________
DESDB ~> add_comment column MICHIGAN.tilename This is the number of the tile

    | ☆            |  Press Ctrl-C to abort
Comment added to column: tilename in table MICHIGAN

_________
DESDB ~> add_comment table MICHIGAN example table

    |☆             |  Press Ctrl-C to abort
Comment added to table: MICHIGAN

_________
DESDB ~> describe_table MICHIGAN

    |  ☆           |  Press Ctrl-C to abort    Rows : 5, Avg time (rows/sec): 13.1

Description of MICHIGAN commented as: 'example table'

        COLUMN_NAME      DATA_TYPE DATA_FORMAT                        COMMENTS
1  COADD_OBJECTS_ID         NUMBER       22,,0                              id
2                RA  BINARY_DOUBLE         8,,       position in the sky -- ra
3               DEC  BINARY_DOUBLE         8,,      position in the sky -- dec
4        MAG_AUTO_G  BINARY_DOUBLE         8,,                            Null
5          TILENAME       VARCHAR2        12,,  This is the number of the tile

_________
DESDB ~>

Load sql statements from a file

Write your favorite sql query, then load it from the file
Comments are supported
DESDM ~> loadsql {file.sql}
DESDM ~> @{file.sql}


-- sql query example
-- These are comments
SELECT 
T1.COADD_OBJECTS_ID, --id
T1.RA, --ra
T1.DEC, --dec
T1.MAG_AUTO_G-T1.XCORR_SFD98_G G, --extinction corrected
T1.MAGERR_DETMODEL_I, --I no corrected
T2.DESDM_ZP,--photoz
T2.DESDM_ZPERR --photoz-err
FROM
MINI_Y1A1 T1, --sub sample from y1a1
Y1A1_COADD_PHOTOZ  T2 -- photoz table
WHERE
T2.COADD_OBJECTS_ID = T1.COADD_OBJECTS_ID; --matching by ids


_________
DESDB ~> loadsql test.sql

 SELECT  T1.COADD_OBJECTS_ID,  T1.RA,  T1.DEC,  T1.MAG_AUTO_G-T1.XCORR_SFD98_G G,  T1.MAGERR_DETMODEL_I,  T2.DESDM_ZP, T2.DESDM_ZPERR  FROM MINI_Y1A1 T1,  Y1A1_COADD_PHOTOZ  T2  WHERE T2.COADD_OBJECTS_ID = T1.COADD_OBJECTS_ID;

submit query? (Y/N): y

    |    ☆         |  Press Ctrl-C to abort    Rows : 18, Avg time (rows/sec): 1.4


18 rows in 12.93 seconds

    COADD_OBJECTS_ID         RA        DEC          G            MAGERR_DETMODEL_I  DESDM_ZP  DESDM_ZPERR
1         3120938977   9.553670 -52.858189  98.936168  20141118000041_DES0037-5248  0.053050      0.15650
2         3120944503   9.999752 -52.754332  22.431785  20141118000041_DES0037-5248  0.267907      0.11793
3         3120954907   9.114992 -52.555158  25.263235  20141118000041_DES0037-5248  0.682050      0.06274
4         3120936831   8.994403 -52.899365  24.932943  20141118000041_DES0037-5248  0.562024      0.13328
5         3120952757   9.427335 -52.595066  23.547770  20141118000041_DES0037-5248  1.004921      0.08233
6         3120045269  10.008397 -42.256504  24.859159  20141118000041_DES0041-4206  0.985158      0.15018
7         3120047663  10.084474 -47.183702  24.589065  20141118000041_DES0041-4706  0.894567      0.08141
8         3120047896  10.556631 -47.180639  23.428831  20141118000041_DES0041-4706  0.550385      0.05724
9         3120055956  10.197952 -47.100950  26.315677  20141118000041_DES0041-4706  0.434088      0.12292
10        3120066754  10.863236 -46.987299  21.691686  20141118000041_DES0041-4706  0.378991      0.11451
11        3120060168  10.673803 -47.056676  23.599043  20141118000041_DES0041-4706  0.481716      0.08525
12        3120064061  10.250745 -47.011011  24.605948  20141118000041_DES0041-4706  0.804396      0.09282
13        3121043108   9.145812 -41.983799  22.939673  20141118000041_DES0037-4206  0.486377      0.10291
14        3121052639   9.156004 -41.756442  20.893839  20141118000041_DES0037-4206  0.144946      0.05946
15        3121214820   9.573569 -41.374574  23.683248  20141118000041_DES0036-4123  0.630103      0.03789
16        3121235477   9.589115 -41.156993  22.892964  20141118000041_DES0036-4123  0.945554      0.06161
17        3121191613   8.979183 -41.611909  25.117038  20141118000041_DES0036-4123  0.854403      0.11231
18        3121189323   9.657756 -41.648061  24.081666  20141118000041_DES0036-4123  0.982048      0.11320

easyaccess python API

On how to run easyaccess commands from inside a python script




# # easyaccess python API

import easyaccess as ea
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

connection=ea.connect()


# ###Using cursor 

cursor=connection.cursor() ##create a cursor object to handle the DB

query='select * from mini_y1a1' ##query

QQ=cursor.execute(query) #execute query

QQ.description

header = [item[0] for item in cursor.description]
rows = cursor.fetchall()  ## Bring the data
cols = np.array(zip(*rows))



# ### Using easyaccess pre-defined


DF=connection.query_to_pandas(query) #one line!

pd.set_option('display.max_rows',12) #Display only

sns.set_style("whitegrid")
temp=sns.JointGrid("MAG_AUTO_G", "MAG_AUTO_R",DF,xlim=[20,27],ylim=[20,27])
temp.plot_joint(sns.kdeplot,cmap='jet',clip=[(20,27),(20,27)])
temp.plot_joint(plt.scatter, s= .3,color='black')
temp.plot_marginals(sns.distplot, color='green',kde=False)


sns.distplot(DF.MAG_AUTO_I[(DF.MAG_AUTO_I>15) & (DF.MAG_AUTO_I<28)],color='red')


# ## Save to a file


connection.query_and_save(query,'mytable.csv') #same formatting


# ## Upload table


connection.load_table('mytable.csv',name='test_api')

connection.mytables()

# ## Load SQL

query2=connection.loadsql('test.sql')

print query2

# ## ping the DB
connection.ping()
cursor.execute('drop table test_api')

connection.mytables()

# ## Running easyaccess commands

connection.onecmd('help')
connection.onecmd('whoami')

connection.close()

connection.ping()

Conclusions

Easyaccess: Son of trivialAccess
Interactive command line access to the DB with lots of "sugar" features
Can be imported as module in pytohn
Web interface in development, stay tuned
Feedback is important!

Useful links

EasyAccess web interface (Experimental)
Github repository
Confluence wiki (where you can ask questions)
Data Access FAQ
Data releases
cx_Oracle module background engine of easyaccess
Oracle tutorial

DES Database

A brief tutorial

DES users

DB Acess tool

trivialAccess

easyaccess

Some "neat" features

Installation

Important files

.desservices.ini : usually at $HOME or defined in $DES_SERVICES

Important files

config.ini : usually at $HOME/.easyaccess/config.ini

Basic usage

Inside easyaccess

help (or ?)

config

clear (or .)

edit

exit (or quit, or ^D)

history

shell (or !{command})

prefetch

describe_table

find_tables

find_tables_with_column

find_user, user_tables

whoami, myquota, mytables

show_db, set_password

refresh_metadata_cache

show_index

Special commands

Save results to a file

Upload a table from a file

Add comments or descriptions

Load sql statements from a file

easyaccess python API

Conclusions

Useful links