easyaccess


Nameeasyaccess JSON
Version 1.4.12 PyPI version JSON
download
home_pagehttps://github.com/des-labs/easyaccess
SummaryEasy access to the DES DB. Enhanced command line SQL interpreter client for DES
upload_time2023-04-11 13:37:22
maintainer
docs_urlNone
authorMatias Carrasco Kind
requires_python
licenseLICENSE.txt
keywords
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # easyaccess 

**easyaccess**: an enhanced command line SQL interpreter client for astronomical surveys.

**Jump to:**

- [Installation](#installation)
- [Usage](#usage)
- [Configuration](#configuration)
- [FAQ](#faq)
       
<img src="data/help.gif" alt="help_screen" style="width: 70%; margin: auto;">

## Description

`easyaccess` is an enhanced command line interpreter and Python package created to facilitate access to astronomical catalogs stored in SQL Databases. It provides a custom interface with custom commands and was specifically designed to access data from the Dark Energy Survey Oracle database, including autocompletion of tables, columns, users and commands, simple ways to upload and download tables using csv, fits and HDF5 formats, iterators, search and description of tables among others. It can easily be extended to another surveys or SQL databases. The package was completely written in Python and support customized addition of commands and functionalities.

For a short tutorial check [here](https://des.ncsa.illinois.edu/desaccess/docs/easyaccess).

## DES DR1/DR2 access quickstart

To access the DES public data releases, you first need an account, which you can register yourself [here](https://des.ncsa.illinois.edu/desaccess). Once you have login credentials for the public DES data server, you can start `easyaccess` with:

```bash
easyaccess -s desdr
```

## 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

## FAQ

We have a running list of [FAQ](FAQ.md) which we will constantly update, please check [here](FAQ.md).

## Contributing

Please take a look at our [Code of Conduct](CODE_OF_CONDUCT.md) and our [contribution guide](CONTRIBUTING.md).

## Citation

If you use `easyaccess` in your research, we encourage you to use this reference [https://arxiv.org/abs/1810.02721](https://arxiv.org/abs/1810.02721) or copy/paste this BibTeX:

```bibtex
@ARTICLE{2018arXiv181002721C,
       author = {{Carrasco Kind}, M. and {Drlica-Wagner}, A. and {Koziol}, A.~M.~G. and
        {Petravick}, D.},
        title = "{easyaccess: Enhanced SQL command line interpreter for astronomical surveys}",
      journal = {arXiv e-prints},
     keywords = {Astrophysics - Instrumentation and Methods for Astrophysics},
         year = 2018,
        month = Oct,
          eid = {arXiv:1810.02721},
        pages = {arXiv:1810.02721},
archivePrefix = {arXiv},
       eprint = {1810.02721},
 primaryClass = {astro-ph.IM},
       adsurl = {https://ui.adsabs.harvard.edu/\#abs/2018arXiv181002721C},
      adsnote = {Provided by the SAO/NASA Astrophysics Data System}
}
```

See also: Carrasco Kind et al., (2019). *easyaccess: Enhanced SQL command line interpreter for astronomical surveys*. **Journal of Open Source Software**, 4(33), 1022, https://doi.org/10.21105/joss.01022

## Installation

Installing `easyaccess` can be a little bit tricky given the external libraries required, in particular the Oracle libraries which are free to use. If you are primarily interested in *using* the `easyaccess` client, we recommend running the Docker image as described below.

### Docker

Running `easyaccess` in Docker is easy. Execute the command below to download and run our published image.

```bash
$ docker run -it --rm \
    registry.gitlab.com/des-labs/kubernetes/easyaccess:latest \
    easyaccess -s desdr

Enter username : 
Enter password : 
Connecting to DB ** desdr ** ...
Loading metadata into cache...
     _______      
     \      \      
  // / .    .\    
 // /   .    _\   
// /  .     / // 
\\ \     . / //  
 \\ \_____/ //   
  \\_______//    DARK ENERGY SURVEY
   `-------`     DATA MANAGEMENT

easyaccess 1.4.12. The DESDM Database shell.
_________
DESDR ~> SELECT RA, DEC, MAG_AUTO_G, TILENAME FROM DR2_MAIN sample(0.001) FETCH FIRST 5 ROWS ONLY ;

         RA        DEC  MAG_AUTO_G      TILENAME
1  8.236249 -24.021460   24.450422  DES0032-2415
2  8.084798 -25.715401   26.279263  DES0033-2541
3  8.142266 -35.854926   26.509785  DES0032-3540
4  8.197418 -48.274010   25.243387  DES0030-4831
5  8.107404 -26.313876   24.758778  DES0032-2623
```

Alternatively, you may build and run the image yourself using the included Dockerfile with the commands:

```bash
docker build -t des-easyaccess .
docker run -it --rm des-easyaccess easyaccess -s desdr
```

### Source Installation

`easyaccess` is based heavily on the Oracle python client `cx_Oracle`, you can follow the installation instructions from [here](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#quick-start-cx-oracle-installation). For `cx_Oracle` to work, you will need the Oracle Instant Client packages which can be obtained from [here](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html).

Make sure you have these libraries installed before proceeding to the installation of easyaccess, you can try by opening a Python interpreter and type:

```python
import cx_Oracle
```

If you have issues, please check the [Troubleshooting page](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#troubleshooting) or our [FAQ page](FAQ.md).

You can clone this repository and install `easyaccess` with:

```bash
python setup.py install
```

#### Requirements

- [Oracle Client](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html) > 11g.2 (External library, no python)
  Check [here](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html) for instructions on how to install these libraries
- [cx_Oracle](https://cx-oracle.readthedocs.io/en/latest/index.html)
  - Note that cx_Oracle needs libaio on some Linux systems
  - Note that cx_Oracle needs libbz2 on some Linux systems
- See the `setup.py` file for additional requirements.

## Usage

For a short tutorial and documentation see [here](https://des.ncsa.illinois.edu/desaccess/docs/easyaccess). Note that not all the features are available for public database use.

### Interactive interpreter

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

```bash
easyaccess
```

### 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

```bash
easyaccess --help
```

### Running SQL commands

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

```bash
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

```bash
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

```bash
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:

```bash
DESDB ~> loadsql <filename.sql>
```
or

```bash
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 a standard output redirect `command > outfile`

## Configuration

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

```bash
DESDB ~> help config
```

to see the meanings of all the options, and:

```bash
DESDB ~> config all show
```

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

```bash
DESDB ~> config prefetch set 50000
```

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

```bash
DESDB ~> config timeout show
```

## Architecture

We have included a simplified UML diagram describing the architecture and dependencies of `easyaccess` which shows only the different methods for a given class and the name of the file hosting a given class. The main class, `easy_or()`, inherits all methods from all different subclasses, making this model flexible and extendable to other surveys or databases. These methods are then converted to command line commands and functions that can be called inside `easyaccess`. Given that there are some DES specific functions, we have moved DES methods into a separate class `DesActions()`.

![`easyaccess` architecture diagram](paper/classes_simple.png)

            

Raw data

            {
    "_id": null,
    "home_page": "https://github.com/des-labs/easyaccess",
    "name": "easyaccess",
    "maintainer": "",
    "docs_url": null,
    "requires_python": "",
    "maintainer_email": "",
    "keywords": "",
    "author": "Matias Carrasco Kind",
    "author_email": "mcarras2@illinois.edu",
    "download_url": "https://files.pythonhosted.org/packages/55/33/8912501b6d656785fcc2f426785f2bda4e7d9c37a53dbadf49e1c25d561e/easyaccess-1.4.12.tar.gz",
    "platform": null,
    "description": "# easyaccess \n\n**easyaccess**: an enhanced command line SQL interpreter client for astronomical surveys.\n\n**Jump to:**\n\n- [Installation](#installation)\n- [Usage](#usage)\n- [Configuration](#configuration)\n- [FAQ](#faq)\n       \n<img src=\"data/help.gif\" alt=\"help_screen\" style=\"width: 70%; margin: auto;\">\n\n## Description\n\n`easyaccess` is an enhanced command line interpreter and Python package created to facilitate access to astronomical catalogs stored in SQL Databases. It provides a custom interface with custom commands and was specifically designed to access data from the Dark Energy Survey Oracle database, including autocompletion of tables, columns, users and commands, simple ways to upload and download tables using csv, fits and HDF5 formats, iterators, search and description of tables among others. It can easily be extended to another surveys or SQL databases. The package was completely written in Python and support customized addition of commands and functionalities.\n\nFor a short tutorial check [here](https://des.ncsa.illinois.edu/desaccess/docs/easyaccess).\n\n## DES DR1/DR2 access quickstart\n\nTo access the DES public data releases, you first need an account, which you can register yourself [here](https://des.ncsa.illinois.edu/desaccess). Once you have login credentials for the public DES data server, you can start `easyaccess` with:\n\n```bash\neasyaccess -s desdr\n```\n\n## Features\n\n- Nice output format (using pandas)\n- Very flexible configuration\n- Smart tab autocompletion for commands, table names, column names, and file paths\n- Write output results to CSV, TAB, FITS, or HDF5 files\n- Load tables from CSV, FITS or HDF5 files directly into DB (memory friendly by using number of rows or memory limit)\n- Intrinsic DB commands to describe tables, schema, quota, and more\n- easyaccess can be imported as module from Python with a complete Python API\n- Run commands directly from command line\n- Load SQL queries from a file and/or from the editor\n- Show the execution plan of a query if needed\n- Python functions can be run in a inline query\n\n## FAQ\n\nWe have a running list of [FAQ](FAQ.md) which we will constantly update, please check [here](FAQ.md).\n\n## Contributing\n\nPlease take a look at our [Code of Conduct](CODE_OF_CONDUCT.md) and our [contribution guide](CONTRIBUTING.md).\n\n## Citation\n\nIf you use `easyaccess` in your research, we encourage you to use this reference [https://arxiv.org/abs/1810.02721](https://arxiv.org/abs/1810.02721) or copy/paste this BibTeX:\n\n```bibtex\n@ARTICLE{2018arXiv181002721C,\n       author = {{Carrasco Kind}, M. and {Drlica-Wagner}, A. and {Koziol}, A.~M.~G. and\n        {Petravick}, D.},\n        title = \"{easyaccess: Enhanced SQL command line interpreter for astronomical surveys}\",\n      journal = {arXiv e-prints},\n     keywords = {Astrophysics - Instrumentation and Methods for Astrophysics},\n         year = 2018,\n        month = Oct,\n          eid = {arXiv:1810.02721},\n        pages = {arXiv:1810.02721},\narchivePrefix = {arXiv},\n       eprint = {1810.02721},\n primaryClass = {astro-ph.IM},\n       adsurl = {https://ui.adsabs.harvard.edu/\\#abs/2018arXiv181002721C},\n      adsnote = {Provided by the SAO/NASA Astrophysics Data System}\n}\n```\n\nSee also: Carrasco Kind et al., (2019). *easyaccess: Enhanced SQL command line interpreter for astronomical surveys*. **Journal of Open Source Software**, 4(33), 1022, https://doi.org/10.21105/joss.01022\n\n## Installation\n\nInstalling `easyaccess` can be a little bit tricky given the external libraries required, in particular the Oracle libraries which are free to use. If you are primarily interested in *using* the `easyaccess` client, we recommend running the Docker image as described below.\n\n### Docker\n\nRunning `easyaccess` in Docker is easy. Execute the command below to download and run our published image.\n\n```bash\n$ docker run -it --rm \\\n    registry.gitlab.com/des-labs/kubernetes/easyaccess:latest \\\n    easyaccess -s desdr\n\nEnter username : \nEnter password : \nConnecting to DB ** desdr ** ...\nLoading metadata into cache...\n     _______      \n     \\      \\      \n  // / .    .\\    \n // /   .    _\\   \n// /  .     / // \n\\\\ \\     . / //  \n \\\\ \\_____/ //   \n  \\\\_______//    DARK ENERGY SURVEY\n   `-------`     DATA MANAGEMENT\n\neasyaccess 1.4.12. The DESDM Database shell.\n_________\nDESDR ~> SELECT RA, DEC, MAG_AUTO_G, TILENAME FROM DR2_MAIN sample(0.001) FETCH FIRST 5 ROWS ONLY ;\n\n         RA        DEC  MAG_AUTO_G      TILENAME\n1  8.236249 -24.021460   24.450422  DES0032-2415\n2  8.084798 -25.715401   26.279263  DES0033-2541\n3  8.142266 -35.854926   26.509785  DES0032-3540\n4  8.197418 -48.274010   25.243387  DES0030-4831\n5  8.107404 -26.313876   24.758778  DES0032-2623\n```\n\nAlternatively, you may build and run the image yourself using the included Dockerfile with the commands:\n\n```bash\ndocker build -t des-easyaccess .\ndocker run -it --rm des-easyaccess easyaccess -s desdr\n```\n\n### Source Installation\n\n`easyaccess` is based heavily on the Oracle python client `cx_Oracle`, you can follow the installation instructions from [here](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#quick-start-cx-oracle-installation). For `cx_Oracle` to work, you will need the Oracle Instant Client packages which can be obtained from [here](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html).\n\nMake sure you have these libraries installed before proceeding to the installation of easyaccess, you can try by opening a Python interpreter and type:\n\n```python\nimport cx_Oracle\n```\n\nIf you have issues, please check the [Troubleshooting page](https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#troubleshooting) or our [FAQ page](FAQ.md).\n\nYou can clone this repository and install `easyaccess` with:\n\n```bash\npython setup.py install\n```\n\n#### Requirements\n\n- [Oracle Client](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html) > 11g.2 (External library, no python)\n  Check [here](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html) for instructions on how to install these libraries\n- [cx_Oracle](https://cx-oracle.readthedocs.io/en/latest/index.html)\n  - Note that cx_Oracle needs libaio on some Linux systems\n  - Note that cx_Oracle needs libbz2 on some Linux systems\n- See the `setup.py` file for additional requirements.\n\n## Usage\n\nFor a short tutorial and documentation see [here](https://des.ncsa.illinois.edu/desaccess/docs/easyaccess). Note that not all the features are available for public database use.\n\n### Interactive interpreter\n\nAssuming that ```easyaccess``` is in your path, you can enter the interactive interpreter by calling ```easyaccess``` without any command line arguments:\n\n```bash\neasyaccess\n```\n\n### Command line usage\n\nMuch 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\n\n```bash\neasyaccess --help\n```\n\n### Running SQL commands\n\nOnce inside the interpreter run SQL queries by adding a \";\" at the end::\n\n```bash\nDESDB ~> select ... from ... where ... ;\n```\n\nTo save the results into a table add \">\" after the end of the query (after \";\") and namefile at the end of line\n\n```bash\nDESDB ~> select ... from ... where ... ; > test.fits\n```\n\nThe file types supported so far are: .csv, .tab, .fits, and .h5. Any other extension is ignored.\n\n### Load tables\n\nTo load a table it needs to be in a csv format with columns names in the first row\nthe name of the table is taken from filename or with optional argument --tablename\n\n```bash\nDESDB ~> load_table <filename> --tablename <mytable> --chunksize <number of rows to read/upload> --memsize <memory in MB to read at a time>\n```\n\nThe `--chunsize` and `--memsize` are optional arguments to facilitate uploading big files.\n\n### Load SQL queries\n\nTo load SQL queries just run:\n\n```bash\nDESDB ~> loadsql <filename.sql>\n```\nor\n\n```bash\nDESDB ~> @filename.sql\n```\n\nThe query format is the same as the interpreter, SQL statement must end with `;` and to write output files the query must be followed by a standard output redirect `command > outfile`\n\n## Configuration\n\nThe configuration file is located at ```$HOME/.easyaccess/config.ini``` but everything can be configured from inside easyaccess type:\n\n```bash\nDESDB ~> help config\n```\n\nto see the meanings of all the options, and:\n\n```bash\nDESDB ~> config all show\n```\n\nto see the current values, to modify one value, e.g., the prefetch value\n\n```bash\nDESDB ~> config prefetch set 50000\n```\n\nand to see any particular option (e.g., timeout):\n\n```bash\nDESDB ~> config timeout show\n```\n\n## Architecture\n\nWe have included a simplified UML diagram describing the architecture and dependencies of `easyaccess` which shows only the different methods for a given class and the name of the file hosting a given class. The main class, `easy_or()`, inherits all methods from all different subclasses, making this model flexible and extendable to other surveys or databases. These methods are then converted to command line commands and functions that can be called inside `easyaccess`. Given that there are some DES specific functions, we have moved DES methods into a separate class `DesActions()`.\n\n![`easyaccess` architecture diagram](paper/classes_simple.png)\n",
    "bugtrack_url": null,
    "license": "LICENSE.txt",
    "summary": "Easy access to the DES DB. Enhanced command line SQL interpreter client for DES",
    "version": "1.4.12",
    "split_keywords": [],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "7032e1cc422605c348493cb78d6d07d0c2e94a08188745923c63fada836fe3e4",
                "md5": "673e43ad594116cb689a73f27d2d0896",
                "sha256": "74cf575104ee4cac7ea9adbce9d5d15f41797df4af83c7516d6deb6054da344f"
            },
            "downloads": -1,
            "filename": "easyaccess-1.4.12-py2.py3-none-any.whl",
            "has_sig": false,
            "md5_digest": "673e43ad594116cb689a73f27d2d0896",
            "packagetype": "bdist_wheel",
            "python_version": "py2.py3",
            "requires_python": null,
            "size": 57822,
            "upload_time": "2023-04-11T13:37:20",
            "upload_time_iso_8601": "2023-04-11T13:37:20.292738Z",
            "url": "https://files.pythonhosted.org/packages/70/32/e1cc422605c348493cb78d6d07d0c2e94a08188745923c63fada836fe3e4/easyaccess-1.4.12-py2.py3-none-any.whl",
            "yanked": false,
            "yanked_reason": null
        },
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "55338912501b6d656785fcc2f426785f2bda4e7d9c37a53dbadf49e1c25d561e",
                "md5": "c5219f581aa61b8b35a526aa2a1e9aa8",
                "sha256": "2103f53718bc271bb4b311286d75d9cf3fa0966391953a17d9f76e27dd3874dc"
            },
            "downloads": -1,
            "filename": "easyaccess-1.4.12.tar.gz",
            "has_sig": false,
            "md5_digest": "c5219f581aa61b8b35a526aa2a1e9aa8",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 1383803,
            "upload_time": "2023-04-11T13:37:22",
            "upload_time_iso_8601": "2023-04-11T13:37:22.664776Z",
            "url": "https://files.pythonhosted.org/packages/55/33/8912501b6d656785fcc2f426785f2bda4e7d9c37a53dbadf49e1c25d561e/easyaccess-1.4.12.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2023-04-11 13:37:22",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "github_user": "des-labs",
    "github_project": "easyaccess",
    "travis_ci": false,
    "coveralls": false,
    "github_actions": true,
    "lcname": "easyaccess"
}
        
Elapsed time: 0.05242s