SQL database introspection and methods in Python to access existing astronomy databases.


Keywords
astronomy, astrophysics, database, orm, python-api, sql
License
Apache-2.0
Install
pip install astrosql==1.0.1

Documentation

astroSQL

SQL database introspection and methods in Python for existing astronomy databases.

Features

  • astroSQL Python API:
    • Uses Peewee SQL ORM.
    • Workflow: connects to database -> get by table name -> query by various methods.
    • Smartly inspects database to gather table/schema removing the need to create database models.
    • Collection of simple SQL read and write methods.

System Dependencies

  • Python 3.6.1+
  • MySQL server

Setup

  1. Use pip to install from pypa source or git clone source

    # Pypa
    pip install astrosql
    # Github
    git clone git@github.com:ketozhang/astroSQL.git
    cd astroSQL/
    pip install .

    But there is no setup.py? pyproject.yaml is the setup file, using a new convention PEP 517

  2. Edit the configuration file as necessary in ~/.astrosql/config.yml:

    # Uncomment 'forward' if you want to place config.yml elsewhere, specify the file path (maybe `~/.astrosql/config.yml` ?)
    # forward: '/path/to/config.yml'
    
    # Comment out any unecessary lines, empty will be read
    mysql:
      host: "localhost"
      user: "username"
      password: ""
      database: "database_name"

    WARNING: Keep this file secure if password is written

Usage

  1. Before you get started, make sure your SQL database is up and running. For instance on Linux run,

    sudo service mysql status
  2. Test out your connection

    from astrosql import AstroSQL
    
    # This will use your credentials at ~/.my.cnf
    db = AstroSQL("DATABASE_NAME")
    
    # You may specify the path to CNF
    db = AstroSQL("DATABASE_NAME", read_default_file="/path/to/my.cnf")
    
    # Otherwise manually enter it yourself
    # Check https://pymysql.readthedocs.io/en/latest/modules/connections.html for more options
    db = AstroSQL("DATABASE_NAME", user="USERNAME", password="PASSWORD", host="localhost")
    
    # Print all avaialable tables
    print(db.tables.values())
  3. Take a look at the API Referfence section for commands.

API Reference

AstroSQL

class astrosql.AstroSQL(database, **kwargs)

Assume the following was run correctly db = AstroSQL(...),

  • get_table

    db.get_table("example")
  • array2sql

    # Schema assumed to have columns id TINYINT, name VARCHAR, mag decimal(3, 10)
    data = [
        [1, "SN1987A", 2.9]
        [2, "SN1993J", 10.8]
    ]
    
    db.array2sql("example", data)
    
  • dict2sql

    data = [
      {"id": 1, "name": "SN1987A", "mag": 2.9}
      {"id": 2, "name": "SN1993J", "mag": 10.8}
    ]
    
    db.dict2sql("example", data)
  • text2sql

    # data.txt
    1  SN1987A  2.9
    2  SN1993J  10.8
    
    db.text2sql("example", "/path/to/data.txt")
  • get

    DEPRECATING

  • get_by_basename Returns a list of data matching the base file name.

    # Assuming the schema has a column basename of some string datatype
    
    db.get_by_basename("example", "BASENAME")
  • get_by_object Returns a list of data matching the object name.

    # Assuming the schema has a column objname of some string  datatype
    
    db.get_by_object("example", "OBJNAME")
  • get_by_radec Returns a list of data queried by circle of some radius centered at some RA and Dec.

    # Assuming the schema has ra and dec columns in degrees
    ra, dec, radius = 0, 0, 1
    
    db.get_by_radec("example", ra, dec, radius)
  • get_by_sql Returns a list of data queried by SQL query.

    query = "SELECT * FROM example;"
    db.get_by_sql(query)

Abandoned Features

  • astroSQL Shell Command
    • query SQL database to text-based file
    • update SQL database with text-based file

References

Filippenko Group - Project Team

The program was built for the Filippenko Group, astronomy researchers led by Alex Filippenko for analyzing data from the Lick Observatory and Keck Observatory.

Project team led by Keto Zhang and Weikang Zheng.

Source Code and Inspiration:

Some parts of the program was provided by and inspired from Issac Shiver and Thomas Tu from the FlipperPhoto repository.