Introduction

What is this about ?

This project is a Python library that you can add to one of your projects, to have an autogenerated API to interact with your data from a MySQL Database. The syntax is Mongo like, and the API easy to connect to any web application.

What Python version ?

This only works with Python 2 (for now, designed to run with Google AppEngine).

How does it work ?

Starting from a normalized SQL database, the library will read the definition of the tables in your database, and expose web services corresponding.

What Database can I use ?

Currently, this project has been produced to work in a Google Cloud Platform environment, then the only database I care is MySQL. But because SQL is now a standard, it should not be very hard to create alternative versions for PostgreSQL or Oracle.

With what web framework ?

The classes in charge of the SQL transactions do not depend on any web framework, but I supply a flask blueprint in the library.

How to use

Warning

This is a personnal project I've created for my personnal use.

Be aware than everything is not perfect, and that I basicly implement a functionnality only when I need it. Then, please consider this project like a version 0.1, far from a final version.

How Can I use it in my project ?

First, you need to install it with pip. :

pip install https://github.com/duckswitch/py-db-api.git@master

Then add the blueprint to your flask application :

# Import flask
from flask import Flask
# Import lib dependencies
from db_api.blueprint import construct_db_api_blueprint
from db_api.db_connection import DBConnection
# Import SQL Driver
import MySQLdb

app = Flask(__name__)
# register the supplied blueprint
app.register_blueprint(
    construct_db_api_blueprint(
        db_driver=MySQLdb,
        db_host=u"127.0.0.1",
        db_user=u"username",
        db_passwd=u"myusernamepassword",
        db_name=u"mydatabase"
    ), 
    url_prefix=u'/api'
)

if __name__ == u"__main__":
    app.run(debug=True)

Use the web service

You can now do requests on the relative url : /api/db/[table-name]/

• GET for a select
• POST for an insert
• DELETE for a delete
• PUT for an update

Some Examples for this schema :

List data (GET)

getting client "Dummy1 or Dummy2"

GET http://localhost:5000/api/db/client?filters=FILTERS&first=0&nb=100

With FILTERS having this value :

{
    "$or": [
        {
            "name": "Dummy1"
        },
        {
            "name": "Dummy2"
        }
    ]
}

The API will return :

{
  "first": 0,
  "items": [
    {
      "id": 1,
      "name": "Dummy1"
    },
    {
      "id": 2,
      "name": "Dummy2"
    }
  ],
  "nb": 100
}

More informations on the filter syntax here

Update data (PUT)

PUT http://localhost:5000/api/db/client?filters=FILTERS

This endpoint takes a filter parameter to select what you want to update (see the GET section).

The payload of the request must look like this :

{
    "$set": {
        "name": "new name",
        "firstname": "new first name"
    }
}

Be aware that you can't update a data related to a table from the foreign one.

Insert data (POST)

POST http://localhost:5000/api/db/client

A JSON which looks like an item returned from the GET endpoint, but do not forget all required fields.

Example to insert a project, depending on the client Dummy1 :

{
    "name": "Awesome project",
    "client": {
        "id": 1
    },
    "provisioned_hours": 999,
    "started_at": 1451602800
}

Delete data (DELETE)

No payload required.

DELETE http://localhost:5000/api/db/client?filters=FILTERS

This endpoint takes a filter parameter to select what you want to delete (see the GET section).

How to contribute

Setting up the project

Install Python2 & virtualenv. Then, just run the setup.sh

./setup.sh

If the blueprint in the main.py is well configured to work with your local dateabase, just do

source venv/bin/activate
python2 main.py

Running the unit tests :

source venv/bin/activate
python2 -m pytest db-api