pyeve/eve-sqlalchemy

SQLAlchemy data layer for Eve-powered RESTful APIs

Eve-SQLAlchemy extension

Powered by Eve, SQLAlchemy and good intentions this extension allows to effortlessly build and deploy highly customizable, fully featured RESTful Web Services with SQL-based backends.

Eve-SQLAlchemy is simple

The following code blocks are excerpts of examples/one_to_many and should give you an idea of how Eve-SQLAlchemy is used. A complete working example can be found there. If you are not familiar with Eve and SQLAlchemy, it is recommended to read up on them first.

For this example, we declare two SQLAlchemy mappings (from domain.py):

class Parent(BaseModel):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(BaseModel):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

As for Eve, a settings.py is used to configure our API. Eve-SQLAlchemy, having access to a lot of metadata from your models, can automatically generate a great deal of the DOMAIN dictionary for you:

DEBUG = True
SQLALCHEMY_DATABASE_URI = 'sqlite://'
SQLALCHEMY_TRACK_MODIFICATIONS = False
RESOURCE_METHODS = ['GET', 'POST']

DOMAIN = DomainConfig({
    'parents': ResourceConfig(Parent),
    'children': ResourceConfig(Child)
}).render()

Finally, running our application server is easy (from app.py):

app = Eve(validator=ValidatorSQL, data=SQL)

db = app.data.driver
Base.metadata.bind = db.engine
db.Model = Base

# create database schema on startup and populate some example data
db.create_all()
db.session.add_all([Parent(children=[Child() for k in range(n)])
                    for n in range(10)])
db.session.commit()

# using reloader will destroy the in-memory sqlite db
app.run(debug=True, use_reloader=False)

The API is now live, ready to be consumed:

$ curl -s http://localhost:5000/parents | python -m json.tool

{
    "_items": [
        {
            "_created": "Sun, 22 Oct 2017 07:58:28 GMT",
            "_etag": "f56d7cb013bf3d8449e11e8e1f0213f5efd0f07d",
            "_links": {
                "self": {
                    "href": "parents/1",
                    "title": "Parent"
                }
            },
            "_updated": "Sun, 22 Oct 2017 07:58:28 GMT",
            "children": [],
            "id": 1
        },
        {
            "_created": "Sun, 22 Oct 2017 07:58:28 GMT",
            "_etag": "dd1698161cb6beef04f564b2e18804d4a7c4330d",
            "_links": {
                "self": {
                    "href": "parents/2",
                    "title": "Parent"
                }
            },
            "_updated": "Sun, 22 Oct 2017 07:58:28 GMT",
            "children": [
                1
            ],
            "id": 2
        },
        "..."
    ],
    "_links": {
        "parent": {
            "href": "/",
            "title": "home"
        },
        "self": {
            "href": "parents",
            "title": "parents"
        }
    },
    "_meta": {
        "max_results": 25,
        "page": 1,
        "total": 10
    }
}

All you need to bring your API online is a database, a configuration file (defaults to settings.py) and a launch script. Overall, you will find that configuring and fine-tuning your API is a very simple process.

Eve-SQLAlchemy is thoroughly tested under Python 2.7-3.7 and PyPy.

Documentation

The offical project documentation can be accessed at eve-sqlalchemy.readthedocs.org. For full working examples, especially regarding different relationship types, see the examples directory in this repository.

eve-sqlalchemy's Issues

eve_sqlalchemy does not work with sqlalchemy v0.8

The line

from eve_sqlalchemy.decorators import registerSchema

raises an exception:

  File "/home/kalombo/.virtualenvs/dev/local/lib/python2.7/site-packages/eve_sqlalchemy/decorators.py", line 30, in <module>
    sqlalchemy.dialects.postgresql.JSON: 'json'}
AttributeError: 'module' object has no attribute 'JSON'

because sqlalchemy v0.8 does not support JSON for postgresql yet. Tested on SQLAlchemy==0.8.3

document_etag fails in delete

I Have a simple User model that contains a relationship with a Role model.

roles_users = db.Table('roles_users',
                       db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                       db.Column('role_id', db.Integer, db.ForeignKey('role.id')))

class Role(EveModel):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    description = db.Column(db.String(255))

class BaseIdentity(EveModel, UserMixin):
    __tablename__ = 'base_identity'
    id = db.Column(db.Integer, primary_key=True)
    id_type = db.Column(db.String(32), nullable=False)
    username = db.Column(db.String(255), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)

    __mapper_args__ = {
        'polymorphic_identity': 'base_identity',
        'polymorphic_on': id_type
    }
class User(BaseIdentity):
    __tablename__ = 'user'
    id = db.Column(db.Integer, db.ForeignKey('base_identity.id'), primary_key=True, nullable=False)
    firstname = db.Column(db.String(255), nullable=False)
    lastname = db.Column(db.String(255), nullable=False)
    email = db.Column(db.String(255), nullable=False)

    roles = db.relationship('Role',
                            secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))
    __mapper_args__ = {
        'polymorphic_identity': 'user',
    }

EveModel implements the jsonify method from the examples.
HATEOS is disabled.

when i try to delete a user using:

curl -v -H "If-Match: 771a2d531414a0819b45f7432b1e1334701e4ad5" -X DELETE --user admin http://localhost:5000/api/v1/user/3

i get the following exception:

.... cropped for brevity ......
 File "/home/user/git/simple_site/venv/lib/python2.7/site-packages/Eve-0.5-py2.7.egg/eve/methods/common.py", line 794, in decorated
    r = f(resource, **combined_args)
  File "/home/user/git/simple_site/venv/lib/python2.7/site-packages/Eve-0.5-py2.7.egg/eve/methods/delete.py", line 33, in deleteitem
    return deleteitem_internal(resource, concurrency_check=True, **lookup)
  File "/home/user/git/simple_site/venv/lib/python2.7/site-packages/Eve-0.5-py2.7.egg/eve/methods/delete.py", line 76, in deleteitem_internal
    original = get_document(resource, concurrency_check, **lookup)
  File "/home/user/git/simple_site/venv/lib/python2.7/site-packages/Eve-0.5-py2.7.egg/eve/methods/common.py", line 64, in get_document
    etag = document.get(config.ETAG, document_etag(document))
  File "/home/user/git/simple_site/venv/lib/python2.7/site-packages/Eve-0.5-py2.7.egg/eve/utils.py", line 277, in document_etag
    h.update(dumps(value, sort_keys=True).encode('utf-8'))
  File "build/bdist.linux-x86_64/egg/bson/json_util.py", line 125, in dumps
    return json.dumps(_json_convert(obj), *args, **kwargs)
  File "/usr/lib/python2.7/json/__init__.py", line 250, in dumps
    sort_keys=sort_keys, **kw).encode(obj)
  File "/usr/lib/python2.7/json/encoder.py", line 209, in encode
    chunks = list(chunks)
  File "/usr/lib/python2.7/json/encoder.py", line 434, in _iterencode
    for chunk in _iterencode_dict(o, _current_indent_level):
  File "/usr/lib/python2.7/json/encoder.py", line 408, in _iterencode_dict
    for chunk in chunks:
  File "/usr/lib/python2.7/json/encoder.py", line 332, in _iterencode_list
    for chunk in chunks:
  File "/usr/lib/python2.7/json/encoder.py", line 442, in _iterencode
    o = _default(o)
  File "/usr/lib/python2.7/json/encoder.py", line 184, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: <simple_site.models.user.Role object at 0x7fc1146c9e90> is not JSON serializable

shouldn't the model be parsed by the DataLayer's parser?

Internal SQLAlchemy object intialized without Flask app.

Here's my very rough understanding of what may or may not be a problem - all learned while (not yet successfully) setting up an eve-sqlalchemy API.

Eve is an instance of a Flask app. Eve's constructor takes a parameter,

data = SQL

The SQL object constructs sets a default db, to a new instance of

flask_sqlalchemy.SQLAlchemy()

The constructor documentation for flask_sqlalchemy.SQLAlchemy has this to say about what should be built when:

From https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py:

There are two usage modes which work very similarly. One is binding
the instance to a very specific Flask application::
    app = Flask(__name__)
    db = SQLAlchemy(app)
The second possibility is to create the object once and configure the
application later to support it::
    db = SQLAlchemy()
    def create_app():
    app = Flask(__name__)
    db.init_app(app)
    return app
The difference between the two is that in the first case methods like
:meth:`create_all` and :meth:`drop_all` will work all the time but in
the second case a :meth:`flask.Flask.app_context` has to exist.

So to get the best case instantiation of flask_sqlalchemy.SQLAlchemy, we need to be able to pass the Flask App object to it's constructor - in this case, the Eve subclass object, which has already instantiated the SQLAlchemy instance internally, without being able to do so.

I don't have a recommendation or solution here, just the suggestion that, to my admittedly very new to the situation analysis, this looks like it could be painful down the road.

I'm adding this here in the hopes that it will make an obvious improvement jump out to someone who better understands the overall architecture. :)

multi-column primary key

In the docs of SQLAlchemy, it says:

Multiple columns may be assigned the primary_key=True flag which denotes a multi-column primary key, known as a composite primary key.

But in eve-sqlalchemy and cerberus, we validate the unique constraint in the scope of single column.

Any suggestions on this issue?

Error: TypeError: Decimal('0.00') is not JSON serializable

I get an error when trying to PATCH an object which has a decimal field like this:

CREATE TABLE package (
id int(11) NOT NULL AUTO_INCREMENT,
created_ts timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
updated_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
etag varchar(45) DEFAULT NULL,
length decimal(20,2) DEFAULT '0.00',
width decimal(20,2) DEFAULT '0.00',
height decimal(20,2) DEFAULT '0.00',

class Package(CommonColumns):
tablename = 'package'
length = Column(Numeric(20, 2), default=0.00)
width = Column(Numeric(20, 2), default=0.00)
height = Column(Numeric(20, 2), default=0.00)

File "C:\Python27\Lib\json\encoder.py", line 184, in default
raise TypeError(repr(o) + " is not JSON serializable")

TypeError: Decimal('0.00') is not JSON serializable

How to define a custom filter and bind it to a resource endpoint

I'm using Python EVE, EVE-SQLALchemy, Flask, Flask-SQLALchemy, SQLALchemy and a Postgres database.

I have my API endpoints all setup and they are working fine out the box.

Now i'm trying to filter the items returned by the some_model resource so that it only returns items (GET) that belong to a certain user (the user doing the API request) or have a certain name (prefix). The SQlAlcehmy code to perform this is below:

session.query(SomeModel).filter(or_(SomeModel.name.like('SOMETHING_%'), SomeModel.account_id==1)).all()

I've looked at pre and post request hooks, User-Restricted Resource Access and everything else I could find, but nowhere seems to describe how to apply custom filters to (all) GET requests of a certain endpoint/resource.

Kind of like the URL filter functionality, but always on and filtering at the database level, not the serialization/response level.

Does SCHEMA_ENDPOINT works with eve-sqlalchemy?

I'm trying to use the SCHEMA_ENDPOINT feature but it seems not to work with eve-sqlalchemy.

Features

Hi guys!

Great Work!!! I'm very interested in this project but I'm in doubt about if this sqlalchemy-eve is full feature compared with eve/mongodb. Has this project any known drawback at the moment? Something that can't do?

Thanks a lot!

TypeError: datetime.time(0, 15) is not JSON serializable

I'm having this error when trying to get some objects from a Firebird Database. Is it related to DATE_FORMAT or it should serialize any datetime object?

Following the traceback:
https://gist.github.com/anonymous/fafe8dc7b8d8da14dd24

DOC: Better documentation for using flask-sqlalchemy

The example from the docs doesn't make sense

from eve_sqlalchemy import SQL as _SQL
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(app)  # app is not defined yet

class SQL(_SQL):
   driver = db

app = Eve(validator=ValidatorSQL, data=SQL)

When we create app, it requires SQL class, which requires db which in turn requires app which hasn't been defined yet.

There is also the "circular imports" issue. This caused my settings.py file to be bad, but the problem reported itself as DOMAIN dictionary missing or wrong which is misleading since I did have a settings.py file(magic filename) and it it did have DOMAIN dict defined as per the example.

Full text search support?

Hey guys,

I was just wondering if there is a recommended an approach for implementing full text search. Would be nice if someone could guide me in the right direction.

That would help a lot!

best regards
Hannes

question about the new operator parser (possible regression)

Hi @amleczko ,

Since your commit 8b7d4aa, our tests are broken (cf. https://travis-ci.org/kiniou/talaos-inventory/jobs/60720548#L242) and it appears the culprit is the method attr.op() you use to call the requested operator.

In substance, when I pass some query argument like:

/asset?where={'name':'startswith(\"computer\")}`

Now (0.3.1 release) sqlalchemy translate this query to:

asset.name startswith "computer"

instead of (pre 0.3.1 release)

asset.name like "computer%"

I may have missed something but it seems the attr.op() is not behaving like you want it to. And I don't know why since i'm not really used to the inner working of SQLAlchemy.

I managed to find the tests about the parse_dictionnary() method but none of those parsed operators are tested in tests/get.py (or maybe i'm too tired to find those tests ... ).

Cheers

eve-sqlalchemy vs sqlalchemy branch in eve

How does this project relate to the sqlalchemy branch inside eve? and more importantly which one should be used?

Missing CommonColumns import in documentation example.

I guess this is mostly a documentation question.

On this page:
http://eve-sqlalchemy.readthedocs.org/

Is CommonColumns just a design pattern, or something provided as part of SQLAlchemy?
If it something that can be imported, the import could be added.

If it is something custom, it would help to declare CommonColumns inline in the example, and simply mention that it is not strictly necessary - rather than having it in a separate code block later.

In PUT method on element in collection error in creation of new object causes record to be deleted

Seems like a bug. I'm using version 0.3.4, eve 0.5.3, using schema from tutorial http://eve-sqlalchemy.readthedocs.org/en/stable/tutorial.html
ID_FIELD field is set to "id":
ID_FIELD = 'id'
ITEM_LOOKUP_FIELD = ID_FIELD
config.ID_FIELD = ID_FIELD
config.ITEM_LOOKUP_FIELD = ID_FIELD

schema:

DOMAIN = {
'people': People._eve_schema['people'],
'invoices': Invoices._eve_schema['invoices']
}

DOMAIN['people'].update({
'item_title': 'person',
'additional_lookup': {
'url': '[0-9]+',
'field': 'id'
},
'cache_control': 'max-age=10,must-revalidate',
'cache_expires': 10,
'resource_methods': ['GET', 'POST', 'DELETE'],
'item_methods': ['GET', 'PATCH', 'PUT', 'DELETE']
})

Steps to reproduce:

1. POST /people
   {"lastname": "Jonh", "Doe"}
   Response id: 1
2. PUT /people/1
   {"lastname": "Jonh", "Doe"}
   Response: 400 "Can's set attribute".

I looked into the debugger, exception is raised on line 231 in init.py:

model_instance._id = id_

It seems like id field name is used incorrectly. I'm new to eve and eve-sqlalchemy, so I'm not sure how to fix it properly (e.g. where is the name of the field is stored)

AND EVEN MORE IMPORTANTLY:
This bug causes incorrect behavior. Put request failed, and I expect that nothing changes. But the record is deleted from the database, because it is done in separate transaction!
And any error in model creation would lead to this behavior

PATCH works as expected though.

SQLAlchemy Column default not working with python functions

Hello,

I have the following problem using eveSqlAlchemy 0.3.4 with eve 0.5.3:

I specified a python function (get_user_id) as default value for a column in my Database to fill in the current User ID when I insert the row.
The problem that occurs now is that when I POST a new entry to my endpoint it raises the following error:

'TypeError: <function get_user_id at 0x1069ae5f0> is not JSON serializable'

From debugging the program I found out that the error occurs when eve is trying to generate the ETAG for the row.

Now I wanted to ask if there is any fix or workaround for this problem. I thought about specifying the default function in the eve Schema but I found no way to use a callable as default value, or is this somehow possible?

Best regards from Austria

Sebastian

eagerloading embedded resources

Hi,

I have a simple one-to-many Parent-Child relationship. I populate my database with 2 parents, each of which has 3 children.

When I request /api/child?embedded={"parent":1}, the amount of queries hitting the database range from 7 to 9 queries (excluding the count query), depending on how I configure eager loading at the relationship level. There's 1 query per child to get its parent, plus some overhead.

This is != scalable. I think SQLAlchemy's Relationship Loading Techniques can be used to great extend here to create efficient queries. The amount of queries required to retrieve the data of the simple request above, regardless of the amount of entries in the database (and assuming no pagination) should be 1.

Simplify setting for ITEM_LOOKUP_FIELD

This relates to issue #52 but does not fix it. It's an enhancement. You should not have to override the eve config object to pick up values in settings.py i.e. config.ITEM_LOOKUP_FIELD = ID_FIELD.

Pull request coming.

projections not working

Problem

As soon as I try to put datasource['projections'] for a resource, I get a huge stack trace that doesn't give a clue as to why this is failing.

This error is seen for GET as well as for POST

Here is the snippet of the code:

DOMAIN['people'].update({
    'datasource': {
        'projection': {'lastname': 0}
    }
})

people is same as defined in the documentation. Nothing fancy.
I really want to hide (encrypted) password field from the GET response to user resource. lastname is just any column used for testing

What works

When I comment the above code, there is no stack trace, but then lastname field is returned in the GET response.

projections work when used as query argument. e.g Using httpie module http command as follows works well. i.e. lastname is supressed from the GET response

http http://127.0.0.1:5000/people\?projection\=\{\"lastname\":0\}

Setup

SQLite DB
Python 3.4 on OSX

Module Versions:
Eve==0.5.3
Eve-SQLAlchemy==0.3.4

Others modules that might be interacting with eve-sqlalchemy
Flask==0.10.1
Flask-SQLAlchemy==2.0
SQLAlchemy==1.0.6
SQLAlchemy-Utils==0.30.12

Examples from documentation not working (and some missing ones)

I have tried the sqla_example.py from the /examples, but could not replicate some of the examples from the documentation (and similar):

Works:

http://127.0.0.1:5000/people/1
http://127.0.0.1:5000/people?where={"id":"1"}

both return 1 record

http://localhost:5000/people?where={"firstname":"like(\"john\")"}

returns 4 records

http://127.0.0.1:5000/people?where={"lastname":"like(\"oba%\")"}

returns 1 record

Does not work:

http://127.0.0.1:5000/people?where={"lastname":"like(\"%bama\")"}

returns 0 records

http://127.0.0.1:5000/people?where={"firstname":"similar to(\"%ohn\"|\"%acob\")"}

returns 0 records

http://127.0.0.1:5000/people?where={"firstname":"similar to(\"(\'%ohn\'|\'%acob\'\"))"}

returns code 500 and error message simplejson.scanner.JSONDecodeError: Invalid \X escape sequence '': line 1 column 29 (char 28) // Werkzeug Debugger

http://127.0.0.1:5000/people?where={"firstname":"in(\"(\'John\',\'Fred\'\"))"}

returns code 500 and error message simplejson.scanner.JSONDecodeError: Invalid \X escape sequence '': line 1 column 29 (char 28) // Werkzeug Debugger

http://127.0.0.1:5000/people?sort=[("lastname", -1, "nullslast")]

returns code 500 and error message _sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "NULLS": syntax error [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT people.firstname || ? || people.lastname AS anon_2, people._created AS people__created, people._updated AS people__updated, people.etag AS people__etag, people.id AS people_id, people.firstname AS people_firstname, people.lastname AS people_lastname \nFROM people ORDER BY people.lastname DESC NULLS LAST) AS anon_1'] [parameters: (' ',)] // Werkzeug Debugger

Missing examples
It would be great to add examples:

• how to use basic operators as AND and OR(e.g., WHERE lastname='Obama' OR firstname='John')
• basic python requests example
  as this does not work, returns code 500:

import requests
p = {
    "where": {
        "id": "1"
    }
}
url = "http://127.0.0.1:5000/people"
r = requests.get(url, params = p)

Query a relationship

Is possible to filter by fields in a collection?. I require to filter the following response:

https://gist.github.com/samtux/bcf4783f450aa3ca905f

I tried the following queries according to the documentation python-evis:

respuesta?where={"pregunta.pregunta.formulario.idgrupotrabajo":540544}
respuesta?where={"pregunta":{"formulario":{"idgrupotrabajo":540544}}}

But I have not been able to perform the filter.

Thanks.

Not honoring ITEM_URL

Needing to support non-integer ID's, I configured Eve with a custom ITEM_URL but this line has it hard-coded - should be config.ITEM_URL. I can override another way but it may trip up others.

Error: datetime.timedelta is not JSON-serializable

Hi, I just started using eve-sqlalchemy but I get an error when trying to access an object which has a timedelta field like this:

from sqlalchemy.types import Interval
...
token_gen_interval = Column(
    Interval,
    CheckConstraint("token_gen_interval > '0 seconds'"),
    nullable=False,
    default=timedelta(minutes=30))
...

The error I get is:

TypeError: datetime.timedelta(0, 1800) is not JSON serializable

PostgreSQL Example

Are there any PostgreSQL examples available?

How to properly get related objects?

I couldn't find any example how to load objects like in a ManyToMany relationship, whether a list of ids or embedded objects.
Am I missing something?

Unicode problem in python 3

After #61 we have this problem:

(...)
    r = f(resource, **combined_args)
  File "/home/goneri/git_repos/eve/eve/methods/get.py", line 105, in get
    build_response_document(document, resource, embedded_fields)
  File "/home/goneri/git_repos/eve/eve/methods/common.py", line 506, in build_response_document
    resolve_embedded_documents(document, resource, embedded_fields)
  File "/home/goneri/git_repos/eve/eve/methods/common.py", line 692, in resolve_embedded_documents
    subdocument[last_field]))
  File "/home/goneri/git_repos/eve/eve/methods/common.py", line 684, in <lambda>
    getter = lambda ref: embedded_document(ref, data_relation, field)  # noqa
  File "/home/goneri/git_repos/eve/eve/methods/common.py", line 623, in embedded_document
    **{config.ID_FIELD: reference})
  File "/home/goneri/git_repos/eve-sqlalchemy/eve_sqlalchemy/__init__.py", line 174, in find_one
    parse_dictionary(lookup, model))
  File "/home/goneri/git_repos/eve-sqlalchemy/eve_sqlalchemy/parser.py", line 45, in parse_dictionary
    if(type(v) is unicode):
NameError: name 'unicode' is not defined

Nullable columns with a default set should not be "required" by registerSchema

A column of the form is_public = Column(Boolean, default=False, nullable=False) produces a schema with required=True, although the intention of the default argument is explicitly to not require a user to provide a value. The culprit is the registerSchema decorator.

I propose changing that line to something like:

                schema['required'] = not (col.nullable or col.default is None) \
                    if not col.primary_key else False

Using own Flask-SQLAlchemy object does not work as described

Section "Using Flask-SQLAlchemy" of the documentation states that the way to re-use my existing Flask-SQLAlchemy object is to subclass the SQL data layer object and provide it to Eve. This does not work as described; instead, your own Flask-SQLAlchemy object is used when following the docs.

models.py:

from eve_sqlalchemy import SQL as _SQL
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class SQL(_SQL):
    driver = db

app.py:

from eve import Eve
from . import models

app = Eve(settings={'DOMAIN': {}}, data=models.SQL)

testing:

>>> import models, app
>>> app.data.driver is models.db
False
>>> app.data.driver is models._SQL.db
True

As far as I can see SQL.init_app is responsible for that. Eve itself initialises the data layer by first setting the instance-level driver to None (as the comment in your source already acknowledges), then calling init_app on the data layer, which reverts SQL.driver to the module-level SQL.db. My own driver set on the class-level as proposed by the docs is never ever used.

As for a solution, I think it's desireable to be able to provide your own Flask-SQLAlchemy driver to eve-sqlalchemy. To solve the issue I replaced self.driver = db on line 45 with self.driver = self.__class__.driver (pull request incoming), which does the same thing when using the default data layer, but allowing to provide a custom Flask-SQLAlchemy driver.

Response Eve-SQLAlchemy not as expected

Hi,
Thank you for your good work.
I just installed and the application seems to work for the most. http://localhost:5000/ and http://localhost:5000/ give responses.
However:
1: I get an error on http://localhost:5000/account/1.
2: http://localhost:5000/account?id=1 gives me the response underneath. The underscores do not seem to comply to your documentation, nor standard json (according to jsonapi.org).

So, what do I do wrong not to get both http://localhost:5000/account/1 and the response right?

Thanks in advance and have a nice day,

Evert

{
"_links": {
"self": {
"href": "account",
"title": "account"
},
"parent": {
"href": "/",
"title": "home"
}
},
"_items": [
{
"accountname": "eve",
"salt": "eve",
"_etag": null,
"_updated": "Tue, 21 Apr 2015 09:36:25 GMT",
"password": "550e8400-e29b-41d4-a716-446655440000",
"id": 1,
"_created": null
}
],
"_meta": {
"page": 1,
"total": 1,
"max_results": 25
}
}

Examples not working

Both the examples in the example dir are not working, in particular when running trivial.py and surfing to http://127.0.0.1:5000/people, I get the following error:

sqlalchemy.exc.OperationalError

OperationalError: (sqlite3.OperationalError) no such column: People [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT People) AS anon_1']

Am I missing something?

Documentation needs trivial example

An additional example is needed in the documentation of the simplest possible API:

This example should include:

• A trivial 'no authentication' auth setup
• Just one or two database tables

ValidatorSQL class doesn't handle/pass allow_unknown

Hi, just discovered that the abovementioned class doesn't notice when allow_unknown is set to True.

The key lines missing/needed from Eve are here (in __init__):

        if resource:
            self.allow_unknown = config.DOMAIN[resource]['allow_unknown']

I've worked around this (and it works fine) by subclassing and adding those two lines.

Eve 0.6 compatibility

I'm currently working on compatibility for Eve 0.6 in https://github.com/dkellner/eve-sqlalchemy/tree/eve-0.6, but there is still some work to do and a lot of rough edges to clean. There will be a pull request as soon as it is ready.

There are at least two noticeable changes:

1. Refactoring of Eve integration tests so we don't copy them anymore (if not necessary), but use multiple inheritance to run all of Eve method tests by default (concerns get.py, post.py, put.py, patch.py and delete.py). This involved mimicing the resources (models) Eve uses for its integration tests and not inventing new ones. This should make it easier to keep up with Eve development in the future.
2. Handling of related IDs and embedded objects with just one field in the payload, as Eve does.

SQL object creates a blank local flask_sqlalchemy.SQLAlchemy object.

Is there a particular reason that the eve_sqlalchemy.SQL object initializes with an internally created instance of flask_sqlalchemy.SQLAlchemy, rather than taking one as a constructor option?

https://github.com/RedTurtle/eve-sqlalchemy/blob/master/eve_sqlalchemy/__init__.py#L27

db = flask_sqlalchemy.SQLAlchemy()

https://github.com/RedTurtle/eve-sqlalchemy/blob/master/eve_sqlalchemy/__init__.py#L63

def init_app(self, app):

It seems like the class would be much simpler to use if the constructor was able to take a previously built instance of flask_sqlalchemy.SQLAlchemy.

def init_app(self, app, db=None):

Psedocode example that is currently impossible:

# Make exactly the instance of flask_sqlalchemy.SQLAlchemy needed.
flask_app = app()
flask_sql = flask_sqlalchemy.SQLAlchemy(**kwargs)
eve_sql = eve_sqlalchemy.SQL(app=flask_app, db=flask_sql)

This change would seem to allow some of the currently enforced retroactive injection to be replaced by declaration and combination, resulting in more readable client code.

I could be missing some critical nuance of the architecture here, of course.

Delete ORM objects created in other request

This is probably something that is staring me in the face somewhere in a doc, but it seems not possible for me to delete objects in a custom view. (and I need that view because I cannot find a way to expose sqlalchemy functions through Eve).

Now I understand things get complicated with deletes when you are working with requests, so I appreciate there may be no simple answer. Things are even more complicated because the core of this function needs to happen in an another spawned thread as it takes up quite some time ( I allow consumers to poll an endpoint for progress updates).

I have tried the following:

1. deleting in the view before the functions gets called. This results in the session going haywire. One
   of the parent objects throws errors when accessed. Probably stuff does not get finished before my thread is spawned.
2. deleting within the spawned thread results in "not attached to this session" errors
3. setting ID to 'None' and deleting objects from the parent in the ORM only (setting the collection
   empty) . Does not seem to have any impact at all, the objects retain there IDs and the parent
   collection remains filled.

I hope there is something obvious that I have missed! Any wisdom that you might be able to share would be highly appreciated!

Thanks,
Jeroen

POST inserting data into sqlalchemy backed table with foreign key constraints fails

Error is:

{"_status": "ERR", "_issues": {"body_id": "value '1' cannot be converted to a ObjectId"}, "_error": {"message": "Insertion failure: 1 document(s) contain(s) error(s)", "code": 422}}

Relevant tables:

class Bodies(CommonColumns):
    __tablename__ = 'bodies'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String,unique=True)
    abrv = Column(String,unique=True)
    url  = Column(String)

class Titles(CommonColumns):
    __tablename__ = 'titles'
    id = Column(Integer, primary_key=True, autoincrement=True)
    body_id = Column(Integer, ForeignKey('bodies.id',use_alter=True,name="bodies_fk"))
    body = relationship(Bodies, uselist=False,post_update=True)
    name = Column(String)
    abrv = Column(String)
    desc = Column(String)
    fix = Column("fix",Enum("pre", "suf", name="fix"))

Bits from settings.py:

DOMAIN['bodies'].update({
    # this resource item endpoint (/invoices/<id>) will match a UUID regex.
    'item_title': 'body',
    'additional_lookup': {
      'url': 'regex("[\d]+")',
      'field': 'id'
    },
    'cache_control': 'max-age=10,must-revalidate',
    'cache_expires': 10,
    'resource_methods': ['GET', 'POST', 'DELETE']
    })

DOMAIN['titles'].update({
    'item_title': 'title',
    'additional_lookup': {
      'url': 'regex("[\w]+")',
      'field': 'abrv'
    },
    'cache_control': 'max-age=10,must-revalidate',
    'cache_expires': 10,
    'resource_methods': ['GET', 'POST', 'DELETE']
    })

Body of the POST:

{ "body_id": 1, "abrv": "CA", "name": "Coursing Aptitude"}

GET Request for http://localhost:5000/bodies/1 results in:

{
  "_updated": "Fri, 27 Mar 2015 00:30:45 GMT",
  "abrv": "UKC",
  "name": "United Kennel Club",
  "url": "http://ukcdogs.com",
  "_etag": "32daca4ef54b9323a93cc0810cbf2a70bfdba1d7",
  "_links": {
    "self": {
      "href": "bodies/1",
      "title": "body"
    },
    "collection": {
      "href": "bodies",
      "title": "bodies"
    },
    "parent": {
      "href": "/",
      "title": "home"
    }
  },
  "_created": "Fri, 27 Mar 2015 00:30:45 GMT",
  "_id": 1,
  "id": 1
}

I feel like I'm missing something obvious here, but none of the eve-sqlalchemy examples even mentions an insert via POST, only using sqlalchemy to do the inserts for the example data.

Request for new functionality - relationship in sqlalchemy orm to REST output

First of all, thanks a lot for the fantastic work on Eve & its sqlalchemy extension!

Would it not be great to have clients to the Eve endpoints also easily consume relationships without resorting to special views? The whole idea of Eve is to create the interface from the database definition isn't it?

Let's assume we have a client table with a relationship to projects, i.e. one-to-many. There are a couple of possibilities.

1. Ideally one would have an endpoint like /clients/10/projects for client with id==10 and that would drill down to /clients/10/projects/1 etc. Vice versa the endpoint projects/1/clients/10. This is already implemented somewhat in Eve itself, possibly only for the dict engine specified in DOMAIN with the url syntax. Perhaps hooking it there would be straightforward.
2. Another approach where the related links would be exposed through an HATEOAS type of approach would also work for me. When the result is a single element, like after /clients/10, links to child projects, and the parent client, could be added as a list to an HATEOAS element.
3. Something that is already somewhat implemented: a simple list of identifiers. I have seen that the backref link from an orm is made available as its identifier (like "parent_id": 10,) in the GET response. It makes sense because the backref is defined as a real column in the reflection engine. The other the other direction is left out. This might actually be the easiest way?

Of course functionality should be configureable as I understand that the orm approach can be slow and resourceful.

Thanks a lot for your consideration!

Best regards,
Jeroen

eve-sqlalchemy breaks sqlalchemy default.

We've been using eve-sqlalchemy to provide a REST API as part of a larger project, and have found that it breaks the behaviour of setting a default on a column.

Pretty sure it's this code that's to blame: https://github.com/RedTurtle/eve-sqlalchemy/blob/056b6ca836def3fb396ea3a015a04a016d601db2/eve_sqlalchemy/decorators.py#L150-L152

ITEM_LOOKUP_FIELD does not properly inherit its value from ID_FIELD

The ITEM_LOOKUP_FIELD value is retrieved during the registerSchema call from the Eve defaults, instead of the eve settings you actually pass to the application.

Obviously there is something wrong with the way this is implemented: registerSchema apparently needs to be called before the eve application is created and its output is used to fill-in the app settings, so there is no way for it to access the eve application settings which only exist after the app is created (...). The fact that registerSchema even tries to access the app configuration is wrong.

Also, I am not sure why you need to retrieve the value of ITEM_LOOKUP_FIELD on schema registration in the first place.

This is also mentioned in issue #42.

PATCH updates the database multiple times, broken hooks

I found a very weird behaviour when using the SQLAlchemy extension. When I register a function to the on_updated hook, it should receive two arguments, updates and original. However original has the updates applied already. The hook is not helpful for me because of this behaviour, so I traced the cause of the issue.
My analysis showed the following:
The on_updated event hook is called from eve/methods/patch.py. The variable original is also there in that function and has the correct original data when it is assigned in line 126:

original = get_document(resource, concurrency_check, **lookup)

In line 172 a copy of it is created:

updated = original.copy()

In line 179 it is then updated:

updated.update(updates)

In line 186 then the database is updated:

app.data.update(resource, object_id, updates)

However the copy does not seem to work. Printing dict(original) before and after line 179 shows that its values, which is the database's content, has changed. As these changes are buffered by SQLAlchemy there might not be many side effects, however it still breaks the hooks.

There is an even bigger problem. The copy does not work, because SQLAResult.copy only creates a shallow copy of the object. I patched that function to

class SQLAResult(collections.MutableMapping):
    def copy(self):             
        return dict(self).copy()

Now the database is not updated in Line 179 anymore. However 'original' still refers to what is actually in the database, which is changed by the SQL update in line 186. Therefore the hooks still don't work. Eve exspects the return value of get_document to be detached from the database, which eve_sqlalchemy does not.

I created a basic proof of concept application, which clearly shows the misbehaviour.
You can download it here(Can I upload files here?): http://people.ee.ethz.ch/~bconrad/eve-sqlalchemy-bug-poc.tar.gz

New release (0.3) anytime soon?

Any chance of a new release (0.3?) any time soon? The new (working) serialization for object arrays (when embedding) is a necessity in my project and an official release would be just fantastic! Thanks

Error with Oracle connection

I have configured the REST API with a connection to Oracle:

https://gist.github.com/samtux/e7f85eb6bad8e3ea5b7d

When I request all records as:

curl http://localhost:5000/pregunta
curl http://localhost:5000/tercero

Returns all records correctly. But when I ask a single record returns an error, such as:

curl http://localhost:5000/pregunta/25

<!--

Traceback (most recent call last):
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/eve/endpoints.py", line 90, in item_endpoint
    response = getitem(resource, **lookup)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/eve/methods/common.py", line 239, in rate_limited
    return f(*args, **kwargs)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/eve/auth.py", line 68, in decorated
    return f(*args, **kwargs)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/eve/methods/common.py", line 820, in decorated
    r = f(resource, **combined_args)
  File "/home/visorgeografico/.virtualenvs/ontrackVirtual/lib/python2.7/site-packages/eve/methods/get.py", line 295, in getitem
    response[config.ID_FIELD])
KeyError: '_id'

-->

What am I doing wrong with my validation?

I have this code and I've got 0.3.4 installed:

root@a784638346e1:/# python -m pip freeze
Cerberus==0.8.1
Eve==0.5.3
Eve-SQLAlchemy==0.3.4
Events==0.2.1
Flask==0.10.1
Flask-PyMongo==0.3.1
Flask-SQLAlchemy==2.1
itsdangerous==0.24
Jinja2==2.8
MarkupSafe==0.23
pymongo==2.9
simplejson==3.8.1
SQLAlchemy==1.0.9
Werkzeug==0.10.4

But when I try to post some data:

⚘ curl -X POST http://localdocker:8080/people -d '{"lastname": "Dude"}'                                                                                                 
{"_error": {"message": "Insertion failure: 1 document(s) contain(s) error(s)", "code": 422}, "_issues": {"{\"lastname\": \"Dude\"}": "unknown field"}, "_status": "ERR"}

Um... Excuse me? Unknown field? lastname is not an unknown field.

Though I wonder about it now... I just changed my validator to this guy:

class PretendValidator(ValidatorSQL):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

    def validate(self, *args, **kwargs):
        return True

And when I run that it barfs and triggers the debugger which shows me TypeError: '_etag' is an invalid keyword argument for People. Is it just that eve is trying to inject fields? Or what? I've been at this for entirely too long and I think I might be going crazy :P

PATCH + update integer field

When I try to update an integer field with curl + json data, I have errors.

See the 2 cases:

First case (with " for the integer value)

curl -H "If-Match: 93f85bf839f34164e062e8907baaf5352f72000a" -X PATCH -i http://127.0.0.1/hit/10 -d '{"hits": "44"}' -H 'Content-Type: application/json'

and the error:
{"_status": "ERR", "_issues": {"hits": "must be of integer type"}}

second case (without " for the integer value)

curl -H "If-Match: 93f85bf839f34164e062e8907baaf5352f72000a" -X PATCH -i http://127.0.0.1/hit/10 -d '{"hits": 44}' -H 'Content-Type: application/json'

and the error:
{"_error": {"message": "The browser (or proxy) sent a request that this server could not understand.", "code": 400}, "_status": "ERR"}

Perhaps check if the value is a number and convert it into integer, but perhaps there is a better method ;)

DOMAIN not connected to EVE in example.

At this page, http://eve-sqlalchemy.readthedocs.org/

the example code provided does not connect DOMAIN into the settings dictionary, resulting in an error message.

raise ConfigException('DOMAIN dictionary missing or wrong.')
eve.exceptions.ConfigException: DOMAIN dictionary missing or wrong.

A solution to this is to declare a simple SETTINGS dictionary, with the DOMAIN settings in it, and pass it into the Eve constructor:

SETTINGS = {
      'DOMAIN': DOMAIN,
  }
app = Eve(settings=SETTINGS, validator=ValidatorSQL, data=SQL)

simple example as tutorial

I have tried to use sqlalchemy-eve to build some REST service around https://github.com/sdementen/piecash SA models.

I could setup the app correctly and got some results using simple GET on the collections.
I noticed however some quirks:

• the _id field can't be renamed through the schema (I have tried field_id but it is not used...). I could use ID_FIELD but then all models should have the same name for the key ==> could be replaced by the primary_key of the class.
• I could not see embedded objects (ie relations) in queries

Would it be possible to build as a tutorial/doc a simple example with the classical Items / Orders SA model example to see how a minimal example can work ?

How to create a data relation with list type

Hey guys,

I have a problem concerning lists. I want to attach a list of accessories to a car. In my understanding I have to do this with a lookup table. Here are my tables:

car_has_accessories = Table(
    'car_has_accessories', CommonColumns.metadata,
    Column('car_id', Integer, ForeignKey('cars.id')),
    Column('accessory_id', Integer, ForeignKey('accessories.id'))
)

class Accessories(CommonColumns):
    __tablename__ = 'accessories'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(80))

class Cars(CommonColumns):
    __tablename__ = 'cars'
    id = Column(Integer, primary_key=True, autoincrement=True)
    accessories = relationship(
        "Accessories", secondary=car_has_accessories)

Now I'm not sure how to specify the data_relation for eve. With these tables the schema for the cars endpoint looks like this:

{'_id': {'required': False, 'type': 'string', 'unique': False},
 'accessories': {'data_relation': {'embeddable': True,
                                   'resource': 'accessories'},
                 'type': 'objectid'},
 'id': {'required': False, 'type': 'integer', 'unique': True}
}

I want the cars endpoint to return accessories as a list of ids, but if I query the endpoint the payload doesn't contain any accessories.

curl http://127.0.0.1:5000/cars/1
{
"_updated": "Mon, 19 Jan 2015 10:58:56 GMT",
"_links": {
    "self": {
        "href": "cars/1",
        "title": "Car"
    },
    "collection": {
        "href": "cars",
        "title": "cars"
    },
    "parent": {
        "href": "/",
        "title": "home"
     }
},
"_etag": null,
"_created": "Mon, 19 Jan 2015 10:58:56 GMT",
"_id": 1,
"id": 1
}

Am I on the right path here?

regards
Hannes

UnicodeEncodeError when filtering unicode string

http://127.0.0.1:5000/api/v1/user_profiles?where={"name":"like(\"%25林%\")"}

File "C:\Users\SiuCheongTerry\OneDrive\Projects\to-do\tochange.com.hk\server\venv\lib\site-packages\eve_sqlalchemy\parser.py", line 43, in parse_dictionary
conditions += parse('{0}{1}'.format(k, v), model)
UnicodeEncodeError: 'ascii' codec can't encode character u'\u6797' in position 7: ordinal not in range(128)

parser.py
line 42-47
try:
conditions += parse('{0}{1}'.format(k, v), model)
except ParseError:
pass
else:
continue

to...

    try:
        if(type(v) is unicode):
            conditions += parse('{0}{1}'.format(k, v.encode('utf-8')), model)
        else:
            conditions += parse('{0}{1}'.format(k, v), model)
    except ParseError:
        pass
    else:
        continue

PUT to sub-resource fails in upcoming eve

It seem that the - as of yet - unreleased eve version specified by tox, makes PUT to sub-resource fail, as can also be seen in travis logs when clicking the build badge.

Looking at the difference of:

py.test eve_sqlalchemy/tests/put.py::TestPutSQL::test_put_subresource

and

tox -e py33 -- eve_sqlalchemy/tests/put.py::TestPutSQL::test_put_subresource

The first one installed with python setup.py develop inside a virtualenv and the second run as is, I see that the document argument to eve_sqlalchemy.replace differs and contains respectively:

{
    'number': 5,
    '_etag': '734feae487343ac9ba703fa1cdb13cf1b32e18c3',
    '_updated': datetime.datetime(2015, 7, 21, 13, 16, 41),
    '_created': datetime.datetime(2015, 7, 21, 15, 16, 41, 521290),
    '_id': 2
}

and

{
    'number': 5,
    '_etag': '41b6372bf938270b3d8e2db9d2bbc9dde7bd2a35',
    '_updated': datetime.datetime(2015, 7, 21, 13, 16, 38),
    '_created': datetime.datetime(2015, 7, 21, 15, 16, 38, 502411),
    'people': '102',
    '_id': 2
}

That is, people, a relationship from Invoives to People is now included in document, while that does not seem to have been the case before. And it seems this breaks the creation of the new model instance:

model_instance = model(**document)

Leading to a 'str' object has no attribute '_sa_instance_state' that is eventualy eaten by an Exception catch in eve.methods.put.put_internal

Documentation needs Sqlite3 example

An example using SQLite3 instead of Mongo is needed, as this is a primary use case for proof-of-concept apps.