ketozhang / astroapiserver Goto Github PK

astroAPIServer only supports database interfaces specified by Python's DB-API v2.0 (PEP 249). Such python interfaces includes the following python packages:

• sqlite3
• pymysql
• psycopg

Design Choices

• astroAPIServer relies solely on Cursor objects and it is up to the users to establish the Connection object.
• astroAPIServer will not support any connection methods beyond what's specified in DB-API v2.0.

Issues

• astroAPIServer will need to deal with various placeholder syntaxes. Two options are possible
  1. User defines the placeholder attributes.
  2. astroAPIServer defines placeholder for at least the most popular DB-API packages and placeholders.

Usage

For example, let's using sqlite3. We instantiate the cursor object by some function get_database,

# database_handler.py
def get_database(database):
    db_path = "/path/to/database.db"
    con = sqlite3.connect(db_path)
    cursor = con.cursor()
    return cursor

astroAPIServer is built with both the Flask object and get_database.

from flask import Flask
from database_handler import get_database

app = Flask(__name__)
api = API(app, get_database=get_database)

Extends SQL API endpoint to support natural SQL clauses.

Supports and Restrictions

The following clauses and keywords should be supported:

• SELECT
  Limit to only column names.
• FROM
  Limit to only table names.
• ORDER BY
• DESC
• LIMIT

The following clauses and keywords will be left unsupported due to implementation difficulty or simply not recommended for this project:

• SELECT Operators
  (e.g., SELECT COUNT(*) FROM table)
  Many DBMS uses various select operator syntax. This wide variety cannot be currently supported.
• WHERE
  This would require support of SQL injection prevention (e.g., by placeholders) for various SQL interface python packages which is possible. This would also require support for various operators (e.g., =, >, <, <>, IN) which is very difficult to support along with SQL injection prevention by placeholders.
• JOIN
  The possibly nested syntax of join clauses will be very difficult to support the wide variety of queries possible. Furthermore, common practices set joint tables queries as views. It is best to leave it to the API developer to build views in their DBMS or simply assign a joint table query to an endpoint.
• GROUP BY
  Since SELECT operator is not supported. Recall all GROUP BY requires a aggregation operator/function in the SELECT clause.
• HAVING
  Since GROUP BY is not supported.

Usage

• Enable from execute_query function:

  ...
  execute_query(database, query, use_global_parameters=True) 

• API endpoint query parameter names should be standardized and documented: