cs91chris / flask_autocrud

Automatically generate a RESTful API service for CRUD operation on database and advanced search

Flask-AutoCRUD

Inspired by: sandman2

based on: sqlalchemy-filters Flask-ResponseBuilder Flask-ErrorsHandler

Automatically generate a RESTful APIs for CRUD operation and advanced search on a database. If a list of Model is not provided, all tables are affected, otherwise you can customize:

• resource name
• fields name
• resource url
• allowed methods
• hidden fields

Features

• HATEOAS support
• conditional requests via ETag header
• full range of CRUD operations
• filtering, sorting and pagination
• customizable responses via query string
• custom FETCH method for advanced search
• content negotiation based on Accept header
• export to csv available
• meta resource description
• cli tool to run autocrud on a database

Quickstart

Install flask_autocrud using pip:

$ pip install Flask-AutoCRUD

Example usage

from flask import Flask

from flask_autocrud import AutoCrud
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite+pysqlite:///db.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['AUTOCRUD_METADATA_ENABLED'] = True

db = SQLAlchemy(app)
AutoCrud(app, db)

app.run(debug=True)

Go to http://127.0.0.1:5000/resources and see all available resources with its endpoint. NOTE: you must set SQLALCHEMY_DATABASE_URI with your database.

If you want to see an example use with Flask-Admin see in example folder.

Filtering and Sorting

Add filters as query string parameters, they are applied in AND, OR operator not supported.

You can use entity fields as parameter with the following placeholders in the value:

• null value: null
• in operator: list separated by ;
• not operator: ! means: not equal, not null, not in
• comparators: __gt__ (grater), __lt__ (lesser), __gte__ (grater-equal), __lte__ (lesser-equal)
• like operator: % for example: %%test%, %test% or %%test NOTE first % are not used in expression, it only indicated that value must be used with like operator.

Other parameters, note that all starts with _:

• Use _fields parameter to get only the fields listed as value, separated by ;.
• Use _limit and _page parameters for pagination.
• Sorting is implemented with _sort parameter. The value is a list of field separated by ; You can prepend - to reverse order.
• Use _export parameter to export data into csv format with file name passed as value or leave empty for default. You can also use Accept:text/csv header, but it has a different behavior because the transformation is applied at the end of response.
• Use _related in order to fetch data of related resources listed as value separated by ; or leave empty if you want all. Added in 2.2.0 in previous release use _extended with no filters.
• Use _as_table in order to flatten nested dict useful if you want render response as table in combination with response in html format or simply if you do not want nested json (no value required).
• With _no_links links of related data and pages are filtered (no value required).

Example requests:

• /invoice?InvoiceId=(35;344)
• /invoice?Total=__lte__10&_sort=Total
• /invoice?_fields=BillingCountry;Total;InvoiceId&InvoiceId=!355;344&_sort=-InvoiceId
• /invoice?_fields=Total;InvoiceId&BillingPostalCode=!null&BillingCountry=%%ermany
• /invoice?_fields=Total;InvoiceDate;InvoiceId;CustomerId&_page=2&_limit=10
• /invoice?InvoiceDate=(2009-01-01;2009-02-01 00:00:00)
• /track?_related=Album;Genre

Custom method FETCH

FETCH request is like a GET collection resources with a body that represents the filters to apply. It differs from filters in query string because there are used to reduce the response (filters are in AND), here are used to produce a search response, in fact you can request and filter data of combined related resources (like sql JOIN) and use OR operator with a simple syntax.

See: sqlalchemy-filters documentation for filters explanation and more examples.

If you are unable to use FETCH, you can use POST method with header: X-HTTP-Method-Override: FETCH. If you want only headers and not response use header: X-HTTP-Method-Override: HEAD.

The following is an example of body request on /customer:

{
    "fields": [
        "Address",
        "City"
    ],
    "related": {
        "Employee": [
            "FirstName",
            "LastName"
        ],
        "Invoice": ["*"]
    },
    "filters": [
        {
            "model": "Customer",
            "field": "SupportRepId",
            "op": "==",
            "value": 5
        },
        {
            "model": "Invoice",
            "field": "Total",
            "op": ">",
            "value": 6
        }
    ],
    "sorting": [
        {
            "model": "Invoice",
            "field": "Total",
            "direction": "asc"
        },
        {
            "model": "Customer",
            "field": "Address",
            "direction": "desc"
        }
    ]
}

AutoCRUD cli

You can use autocrud as a standalone application configurable via yaml file. Some options could be given via cli see: autocrud --help.

From release 2.2.0 multiple wsgi server can be used, instead in previous release only gunicorn or waitress can be used; in addition cli options are changed.

Configuration file contains 2 principal macro section:

• app: every configuration under it will be passed to Flask config object
• wsgi: every configuration under it will be passed to the chosen wsgi server

For example:

app:
  SQLALCHEMY_DATABASE_URI: sqlite+pysqlite:///examples/db.sqlite3
  SQLALCHEMY_TRACK_MODIFICATIONS: false
wsgi:
  bind: localhost:5000
  workers: 1
  threads: 1

Configuration

1. AUTOCRUD_METADATA_ENABLED: (default: True) enable metadata endpoint for a resource
2. AUTOCRUD_METADATA_URL: (default: '/meta) added at the end of url resource
3. AUTOCRUD_READ_ONLY: (default: False) enable only http GET method
4. AUTOCRUD_BASE_URL: (default: '') prefix url for resources
5. AUTOCRUD_RESOURCES_URL: (default: '/resources') url for all available resources
6. AUTOCRUD_RESOURCES_URL_ENABLED: (default: True) enable route for resources list
7. AUTOCRUD_SUBDOMAIN: (default: None) bind autocrud endpoints to a subdomain
8. AUTOCRUD_MAX_QUERY_LIMIT: (default 1000) max query limit, 0 means no limit
9. AUTOCRUD_FETCH_ENABLED: (default True) enable or disable FETCH method
10. AUTOCRUD_QUERY_STRING_FILTERS_ENABLED: (default True) enable or disable filters in querystring
11. AUTOCRUD_EXPORT_ENABLED: (default True) enable or disable export to csv
12. AUTOCRUD_DATABASE_SCHEMA: (default None) database schema to consider
13. AUTOCRUD_CONDITIONAL_REQUEST_ENABLED: (default True) allow conditional request

TODO

• automatic swagger ui or alternative api docs

Feedback and contributions are welcome.

License MIT

flask_autocrud's Issues

error 500 when using links on many to many relation

Considering the following DB schema

CREATE TABLE IF NOT EXISTS "Genres" (
	"genreId"	TEXT,
	"genreName"	TEXT,
	"genreDesc"	TEXT,
	PRIMARY KEY("genreId")
);

CREATE TABLE IF NOT EXISTS "Artists" (
	"artistId"	TEXT,
	"artistName"	TEXT,
	"artistPhoto"	TEXT,
	"genreRef"	TEXT,
	PRIMARY KEY("artistId"),
	FOREIGN KEY("genreRef") REFERENCES "Genres"("genreId")
);

CREATE TABLE IF NOT EXISTS "SimilarTo" (
	"artist1Ref"	TEXT,
	"artist2Ref"	TEXT,
	PRIMARY KEY("artist1Ref","artist2Ref"),
	FOREIGN KEY("artist1Ref") REFERENCES "Artists"("artistId"),
	FOREIGN KEY("artist2Ref") REFERENCES "Artists"("artistId")
);

CREATE TABLE IF NOT EXISTS "Labels" (
	"labelId"	TEXT,
	"labelName"	TEXT,
	PRIMARY KEY("labelId")
);

CREATE TABLE IF NOT EXISTS "Songs" (
	"songId"	TEXT,
	"songTitle"	TEXT,
	"songLength"	NUMERIC,
	PRIMARY KEY("songId")
);

CREATE TABLE IF NOT EXISTS "AppearsOn" (
	"albumRef"	TEXT,
	"songRef"	TEXT,
	PRIMARY KEY("albumRef","songRef"),
	FOREIGN KEY("albumRef") REFERENCES "Albums"("albumId"),
	FOREIGN KEY("songRef") REFERENCES "Songs"("songId")
);

CREATE TABLE IF NOT EXISTS "ParticipateTo" (
	"artistRef"	TEXT,
	"albumRef"	TEXT,
	 FOREIGN KEY("artistRef") REFERENCES "Artists"("artistId"),
	FOREIGN KEY("albumRef") REFERENCES "Albums"("albumId")
);

CREATE TABLE IF NOT EXISTS "Albums" (
	"albumId"	TEXT,
	"albumYear"	INTEGER,
	"albumTitle"	TEXT,
	"albumImage"	TEXT,
	"labelRef"	TEXT,
	"genreRef"	TEXT,
	PRIMARY KEY("albumId"),
	FOREIGN KEY("labelRef") REFERENCES "Labels"("labelId"),
	FOREIGN KEY("genreRef") REFERENCES "Genres"("genreId")
);

Trying to access /artists/id_of_artist/albums raises a 500 error. The exact error is sqlalchemy filter complaining about Artistsnot belonging to itsmodelparameter. The problem seems to come fromqs2sqla.pybut it is hard to find exactly why without taking time to understandflask_autocrud` inner working :-(.

sometime meta not works

works on validation

(.venv) C:\Users\xxxxxx\Documents\gits\fid-forward>curl -i  http://127.0.0.1:5000/api/v1/validation/meta
HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 763
Server: Werkzeug/1.0.1 Python/3.7.0
Date: Mon, 25 May 2020 12:32:57 GMT

{"description": null, "fields": [{"description": null, "key": true, "name": "id", "nullable": false, "type": "int", "unique": null}, {"description": null, "key": false, "name": "validation_date",
"nullable": true, "type": "datetime", "unique": null}, {"description": null, "key": false, "name": "comment", "nullable": true, "type": "str", "unique": null}, {"description": null, "key": false,
"name": "status", "nullable": true, "type": "str", "unique": null}, {"description": null, "key": false, "name": "id_user", "nullable": true, "type": "int", "unique": null}], "methods": ["HEAD", "F
ETCH", "DELETE", "PATCH", "POST", "GET", "PUT", "OPTIONS"], "name": "validation", "related": {"demand": "/api/v1/demand", "user": "/api/v1/user"}, "url": "/api/v1/validation"}

not works on application

(.venv) C:\Users\xxxxx\Documents\gits\fid-forward>curl -i  http://127.0.0.1:5000/api/v1/application/meta
HTTP/1.0 500 INTERNAL SERVER ERROR
Content-Type: application/problem+json
Content-Length: 1092
Server: Werkzeug/1.0.1 Python/3.7.0
Date: Mon, 25 May 2020 12:33:12 GMT

{"detail": "Traceback (most recent call last):\n  File \"C:\\Users\\PCDxxxx1\\programme\\py37\\lib\\site-packages\\flask\\app.py\", line 1950, in full_dispatch_request\n    rv = self.dispatch_req
uest()\n  File \"C:\\Users\\Pxxx1\\programme\\py37\\lib\\site-packages\\flask\\app.py\", line 1936, in dispatch_request\n    return self.view_functions[rule.endpoint](**req.view_args)\n  File
\"C:\\Users\\PCDTxxx1\\programme\\py37\\lib\\site-packages\\flask\\views.py\", line 89, in view\n    return self.dispatch_request(*args, **kwargs)\n  File \"C:\\Users\\PCDxxx81\\programme\\py37\
\lib\\site-packages\\flask_autocrud\\service.py\", line 45, in dispatch_request\n    return controller(*args, **kwargs)\n  File \"C:\\Users\\Pxxxx1\\programme\\py37\\lib\\site-packages\\flask_a
utocrud\\service.py\", line 161, in get\n    return self._response.build_response(builder, model.description())\nTypeError: 'InstrumentedAttribute' object is not callable\n", "instance": "about:bl
ank", "response": null, "status": 500, "title": "Internal Server Error", "type": "https://httpstatuses.com/500"}

Logging/trace has raw ANSI/garbage characters under Microsoft Windows

Extract:

127.0.0.1 - - [08/Feb/2024 18:38:56] "?[33mGET / HTTP/1.1?[0m" 404 -
127.0.0.1 - - [08/Feb/2024 18:38:56] "?[33mGET /favicon.ico HTTP/1.1?[0m" 404 -
127.0.0.1 - - [08/Feb/2024 18:39:02] "?[33mGET /admin HTTP/1.1?[0m" 404 -
127.0.0.1 - - [08/Feb/2024 18:39:11] "?[33mGET /resources HTTP/1.1?[0m" 404 -

include related fields in response other than custom FETCH

Hi,

first of all, thanks for the project!
Is it possible to include related fields in GET requests as well?

I want to use Apollo GraphQL on top but can't implement your custom FETCH method there.

My model is a company that has multiple arrays of id's (users, addresses, invoices, etc). These id's are on other microservices. With FETCH I can easily query them as related ressources.

Or am I missing another way here?

Best regards

models.py

class Company(BaseModel):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(150), nullable=False, unique=True)
    addresses = relationship("Address", foreign_keys="Address.company_id", back_populates='company')

class Address(BaseModel):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    address_id = Column(Integer)
    company_id = Column(Integer, ForeignKey('company.id'))
    company = relationship("Company", foreign_keys=company_id)

curl --request FETCH call
{ "fields": [ "name" ], "related": { "addresses": [ "address_id" ] } }

Missing examples from README

1. autocrud for any database (with config file pointing to example database)
2. examples with example database

autocrud demo

autocrud -c examples/configuration.yaml

sample app

python examples/run.py

NOTE has a warning, [2024-02-08 19:37:19,141] WARNING in handler: invalid handler: 'None'

Try to run but no template found exception

py37\lib\site-packages\jinja2\environment.py", line 907, in select_template

message=u"Tried to select from an empty list " u"of templates."

Failure to run after install

setup.py requirements are too greedy and pull in version that are incompatible with current code:

AttributeError: module 'flask.json' has no attribute 'JSONEncoder'

ImportError: cannot import name 'url_quote' from 'werkzeug.urls' 

ImportError: cannot import name 'escape' from 'jinja2' 

ImportError: cannot import name 'json' from 'itsdangerous'

AttributeError: module 'sqlalchemy' has no attribute '__all__'. Did you mean: '__file__'?

SQLalchemy error is likely another manifestation of #6 (i.e. incompatible with SQLalchemy version 2.x).

AttributeError: 'Query' object has no attribute '_join_entities'

Hi @cs91chris,
First of all, thank you for this awesome project!

I am getting the above error, when I try to run a request against any of the model endpoints,

import os
import pyodbc
from dotenv import load_dotenv

from flask import Flask

from flask_autocrud import AutoCrud, Model
from flask_sqlalchemy import SQLAlchemy

load_dotenv()


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = f"{os.environ.get('DB_CONN_STR')}?driver={pyodbc.drivers()[-1]}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['AUTOCRUD_METADATA_ENABLED'] = True
app.config['AUTOCRUD_DATABASE_SCHEMA'] = os.environ.get('DB_TABLE_SCHEMA')

db = SQLAlchemy(app)

with app.app_context():
    AutoCrud(app, db)

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

When I run a requests against the /resources endpoint I see all of the tables in my schema though.

Am I doing something wrong here?

Note: My SQLAlchemy version is 2.0.6.