Eve-SQLAlchemy¶
Use Eve with SQLAlchemy instead of MongoDB. Re-use your existing SQL data model and expose it via a RESTful Web Service with no hassle.
Documentation¶
Installation¶
This part of the documentation covers the installation of Eve-SQLAlchemy. The first step to using any software package is getting it properly installed.
Installing Eve-SQLAlchemy is simple with pip:
$ pip install eve-sqlalchemy
Development Version¶
Eve-SQLAlchemy is actively developed on GitHub, where the code is always available. If you want to work with the development version of Eve-SQLAlchemy, there are two ways: you can either let pip pull in the development version, or you can tell it to operate on a git checkout. Either way, virtualenv is recommended.
Get the git checkout in a new virtualenv and run in development mode.
$ git clone https://github.com/pyeve/eve-sqlalchemy.git
Cloning into 'eve-sqlalchemy'...
...
$ cd eve-sqlalchemy
$ virtualenv venv
...
Installing setuptools, pip, wheel...
done.
$ . venv/bin/activate
$ pip install .
...
Successfully installed ...
This will pull in the dependencies and activate the git head as the current
version inside the virtualenv. Then all you have to do is run git pull
origin
to update to the latest version.
To just get the development version without git, do this instead:
$ mkdir eve-sqlalchemy
$ cd eve-sqlalchemy
$ virtualenv venv
$ . venv/bin/activate
$ pip install git+https://github.com/pyeve/eve-sqlalchemy.git
...
Successfully installed ...
And you’re done!
Tutorial¶
The example app used by this tutorial is available at examples/simple
inside
the Eve-SQLAlchemy repository.
Schema registration¶
The main goal of the SQLAlchemy integration in Eve is to separate dependencies and keep model registration depend only on sqlalchemy library. This means that you can simply use something like that:
from sqlalchemy import Column, DateTime, ForeignKey, Integer, String, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property, relationship
Base = declarative_base()
class CommonColumns(Base):
__abstract__ = True
_created = Column(DateTime, default=func.now())
_updated = Column(DateTime, default=func.now(), onupdate=func.now())
_etag = Column(String(40))
class People(CommonColumns):
__tablename__ = 'people'
id = Column(Integer, primary_key=True, autoincrement=True)
firstname = Column(String(80))
lastname = Column(String(120))
fullname = column_property(firstname + " " + lastname)
class Invoices(CommonColumns):
__tablename__ = 'invoices'
id = Column(Integer, primary_key=True, autoincrement=True)
number = Column(Integer)
people_id = Column(Integer, ForeignKey('people.id'))
people = relationship(People, uselist=False)
We have used CommonColumns
abstract class to provide attributes used by
Eve, such as _created
and _updated
. These are not needed if you are only
reading from the database. However, if your API is also writing to the database,
then you need to include them.
Eve settings¶
All standard Eve settings will work with SQLAlchemy support. However, you
need to manually decide which SQLAlchemy declarative classes you wish to
register. You can do so using DomainConfig
and ResourceConfig
, which
will give you a default schema (DOMAIN
dictionary) derived from your
SQLAlchemy models. This is intended as a starting point and to save you
from redundant configuration, but there’s nothing wrong with customizing this
dictionary if you need to!
from eve_sqlalchemy.config import DomainConfig, ResourceConfig
from eve_sqlalchemy.examples.simple.tables import Invoices, People
DEBUG = True
SQLALCHEMY_DATABASE_URI = 'sqlite://'
SQLALCHEMY_TRACK_MODIFICATIONS = False
RESOURCE_METHODS = ['GET', 'POST']
# The following two lines will output the SQL statements executed by
# SQLAlchemy. This is useful while debugging and in development, but is turned
# off by default.
# --------
# SQLALCHEMY_ECHO = True
# SQLALCHEMY_RECORD_QUERIES = True
# The default schema is generated using DomainConfig:
DOMAIN = DomainConfig({
'people': ResourceConfig(People),
'invoices': ResourceConfig(Invoices)
}).render()
# But you can always customize it:
DOMAIN['people'].update({
'item_title': 'person',
'cache_control': 'max-age=10,must-revalidate',
'cache_expires': 10,
'resource_methods': ['GET', 'POST', 'DELETE']
})
# Even adding custom validations just for the REST-layer is possible:
DOMAIN['invoices']['schema']['number'].update({
'min': 10000
})
A note about using update
¶
A common mistake is to use update
to try to update values in a nested
dictionary. This will overwrite the entire dictionary and probably cause
KeyError
s.
# Instead of this...
DOMAIN['foo'].update({
'datasource': { # 'datasource' will only contain 'default_sort'!
'default_sort': [('id', -1)]
}
})
# ... do this:
DOMAIN['foo']['datasource']['default_sort'] = [('id', -1)]
Authentication example¶
This example is based on the Token-Based tutorial from Eve Authentication. First we need to create eve-side authentication:
"""
Auth-Token
~~~~~~~~~~
Securing an Eve-powered API with Token based Authentication and
SQLAlchemy.
This snippet by Andrew Mleczko can be used freely for anything
you like. Consider it public domain.
"""
from eve import Eve
from eve.auth import TokenAuth
from .models import User
from .views import register_views
class TokenAuth(TokenAuth):
def check_auth(self, token, allowed_roles, resource, method):
"""First we are verifying if the token is valid. Next
we are checking if user is authorized for given roles.
"""
login = User.verify_auth_token(token)
if login and allowed_roles:
user = app.data.driver.session.query(User).get(login)
return user.isAuthorized(allowed_roles)
else:
return False
if __name__ == '__main__':
app = Eve(auth=TokenAuth)
register_views(app)
app.run()
Next step is the User SQLAlchemy model:
"""
Auth-Token
~~~~~~~~~~
Securing an Eve-powered API with Token based Authentication and
SQLAlchemy.
This snippet by Andrew Mleczko can be used freely for anything
you like. Consider it public domain.
"""
import hashlib
import string
import random
from itsdangerous import TimedJSONWebSignatureSerializer \
as Serializer
from itsdangerous import SignatureExpired, BadSignature
from werkzeug.security import generate_password_hash, \
check_password_hash
from sqlalchemy.orm import validates
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
SECRET_KEY = 'this-is-my-super-secret-key'
class User(Base):
__tablename__ = 'users'
login = Column(String, primary_key=True)
password = Column(String)
roles = relationship("Role", backref="users")
def generate_auth_token(self, expiration=24*60*60):
"""Generates token for given expiration
and user login."""
s = Serializer(SECRET_KEY, expires_in=expiration)
return s.dumps({'login': self.login })
@staticmethod
def verify_auth_token(token):
"""Verifies token and eventually returns
user login.
"""
s = Serializer(SECRET_KEY)
try:
data = s.loads(token)
except SignatureExpired:
return None # valid token, but expired
except BadSignature:
return None # invalid token
return data['login']
def isAuthorized(self, role_names):
"""Checks if user is related to given role_names.
"""
allowed_roles = set([r.id for r in self.roles])\
.intersection(set(role_names))
return len(allowed_roles) > 0
def encrypt(self, password):
"""Encrypt password using werkzeug security module.
"""
return generate_password_hash(password)
@validates('password')
def _set_password(self, key, value):
"""Using SQLAlchemy validation makes sure each
time password is changed it will get encrypted
before flushing to db.
"""
return self.encrypt(value)
def check_password(self, password):
if not self.password:
return False
return check_password_hash(self.password, password)
And finally a flask login view:
"""
Auth-Token
~~~~~~~~~~
Securing an Eve-powered API with Token based Authentication and
SQLAlchemy.
This snippet by Andrew Mleczko can be used freely for anything
you like. Consider it public domain.
"""
import json
import base64
from flask import request, jsonify
from werkzeug.exceptions import Unauthorized
from .models import User
def register_views(app):
@app.route('/login', methods=['POST'])
def login(**kwargs):
"""Simple login view that expect to have username
and password in the request POST. If the username and
password matches - token is being generated and return.
"""
data = request.get_json()
login = data.get('username')
password = data.get('password')
if not login or not password:
raise Unauthorized('Wrong username and/or password.')
else:
user = app.data.driver.session.query(User).get(login)
if user and user.check_password(password):
token = user.generate_auth_token()
return jsonify({'token': token.decode('ascii')})
raise Unauthorized('Wrong username and/or password.')
Start Eve¶
That’s almost everything. Before you can start Eve you need to bind SQLAlchemy from the Eve data driver:
app = Eve(validator=ValidatorSQL, data=SQL)
db = app.data.driver
Base.metadata.bind = db.engine
db.Model = Base
Now you can run Eve:
app.run(debug=True)
and start it:
$ python app.py
* Running on http://127.0.0.1:5000/
and check that everything is working like expected, by trying requesting people:
$ curl http://127.0.0.1:5000/people/1
{
"id": 1,
"fullname": "George Washington",
"firstname": "George",
"lastname": "Washington",
"_etag": "31a6c47afe9feb118b80a5f0004dd04ee2ae7442",
"_created": "Thu, 21 Aug 2014 11:18:24 GMT",
"_updated": "Thu, 21 Aug 2014 11:18:24 GMT",
"_links": {
"self": {
"href":"/people/1",
"title":"person"
},
"parent": {
"href": "",
"title": "home"
},
"collection": {
"href": "/people",
"title": "people"
}
},
}
Using Flask-SQLAlchemy¶
If you are using Flask-SQLAlchemy, you can use your existing db
object in the SQL
class driver, rather than the empty one it creates.
You can do this by subclassing SQL
and overriding the driver.
from eve_sqlalchemy import SQL as _SQL
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
class SQL(_SQL):
driver = db
app = Eve(validator=ValidatorSQL, data=SQL)
SQLAlchemy expressions¶
With this version of Eve you can use SQLAlchemy expressions such as: like, in, any, etc. For more examples please check SQLAlchemy internals.
Query strings are supported, allowing for filtering and sorting. Both native Mongo queries and Python conditional expressions are supported. For more examples please check SQLAlchemy filtering.
Filtering¶
Generating ‘exact’ matches
Here we are asking for all people where lastname value is Smith:
/people?where={"lastname":"Smith"}
which produces where closure:
people.lastname = "Smith"
Generating multiple ‘exact’ matches
Here we are asking for all people where age value is between 50 and 60:
/people?where=age>50 and age<60
which produces where closure:
people.age > 50 AND people.age < 60
Generating ‘like’ matches
Here we are asking for all people where lastname value contains Smi:
/people?where={"lastname":"like(\"Smi%\")"}
which produces where closure:
people.lastname LIKE "Smi%"
Generating ‘in’ matches
Here we are asking for all people where firstname value is John or Fred:
/people?where={"firstname":"in(\"(\'John\',\'Fred\')\")"}
or you can also use the other syntax query
/people?where={"firstname":['John','Fred']}
which produces where closure:
people.firstname IN ("John", "Fred")
Generating ‘similar to’ matches
/people?where={"firstname":"similar to(\"(\'%ohn\'|\'%acob\')\")"}
which produces where closure:
people.firstname SIMILAR TO '("%ohn"|"%acob")'
Generating ‘any’ matches
If you have postgresql ARRAY column you can use any:
/documents?where={"keywords":"any(\"critical\")"}
which produces where closure:
"critical" = ANY(documents.keywords)
Generating ‘not null’ matches
/documents?where={"keywords":"!=null"}
which produces where closure:
documents.keywords IS NOT NULL
Generating ‘datetime’ matches
Here we are asking for all documents that where _created after Mon, 17 Oct 2019 03:00:00 GMT:
/documents?where=_created> \"Mon, 17 Oct 2019 03:00:00 GMT\"
which produces where closure:
documents._created > 2019-10-17 03:00:00
Sorting¶
Starting from version 0.2 you can use SQLAlchemy ORDER BY expressions such as: nullsfirst, nullslast, etc.
Using those expresssion is straightforward, just pass it as 3 argument to sorting:
/people?sort=[("lastname", -1, "nullslast")]
which produces order by expression:
people.lastname DESC NULLS LAST
You can also use the following python-Eve syntax:
/people?sort=lastname,-created_at
FAQ¶
cURL
Keep in mind that every browser or cURL generator can implement its own encoder, and not all produce the same result. So, adding –data-urlencode to the curl query should work.
curl -iG --data-urlencode where='_created> "Thu, 22 Nov 2018 09:00:00 GMT"' localhost:5000/people
Embedded resources¶
Eve-SQLAlchemy support the embedded keyword of python-eve (Eve Embedded Resource Serialization).
/people?embedded={"address":1}
For example, the following request will list the people and embedded their addresses.
Starting from version 0.4.0a, only the fields that have the projection (Eve Projections) enabled are included in the associated resource. This was necessary to avoid endless loops when relationship between resources were referring each other.
Simple example¶
Create a file, called trivial.py, and include the following:
''' Trivial Eve-SQLAlchemy example. '''
from eve import Eve
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import column_property
from eve_sqlalchemy import SQL
from eve_sqlalchemy.config import DomainConfig, ResourceConfig
from eve_sqlalchemy.validation import ValidatorSQL
Base = declarative_base()
class People(Base):
__tablename__ = 'people'
id = Column(Integer, primary_key=True, autoincrement=True)
firstname = Column(String(80))
lastname = Column(String(120))
fullname = column_property(firstname + " " + lastname)
SETTINGS = {
'DEBUG': True,
'SQLALCHEMY_DATABASE_URI': 'sqlite://',
'SQLALCHEMY_TRACK_MODIFICATIONS': False,
'DOMAIN': DomainConfig({
'people': ResourceConfig(People)
}).render()
}
app = Eve(auth=None, settings=SETTINGS, validator=ValidatorSQL, data=SQL)
# bind SQLAlchemy
db = app.data.driver
Base.metadata.bind = db.engine
db.Model = Base
db.create_all()
# Insert some example data in the db
if not db.session.query(People).count():
db.session.add_all([
People(firstname=u'George', lastname=u'Washington'),
People(firstname=u'John', lastname=u'Adams'),
People(firstname=u'Thomas', lastname=u'Jefferson')])
db.session.commit()
# using reloader will destroy in-memory sqlite db
app.run(debug=True, use_reloader=False)
Run this command to start the server:
python trivial.py
Open the following in your browser to confirm that the server is serving:
http://127.0.0.1:5000/
You will see something like this:
<resource>
<link rel="child" href="people" title="people"/>
</resource>
Now try the people URL:
http://127.0.0.1:5000/people
You will see the three records we preloaded.
<resource href="people" title="people">
<link rel="parent" href="/" title="home"/>
<_meta>
<max_results>25</max_results>
<page>1</page>
<total>3</total>
</_meta>
<_updated>Sun, 22 Feb 2015 16:28:00 GMT</_updated>
<firstname>George</firstname>
<fullname>George Washington</fullname>
<id>1</id>
<lastname>Washington</lastname>
</resource>
Upgrading¶
Upgrading from 0.6.0 to 0.7.0¶
Eve-SQLAlchemy is now based on Eve 0.7, which introduces potentially breaking changes:
- The ETag format was changed to comply with RFC 7232-2.3. Be aware the ETag header values are now enclosed with double-quotes.
- Eve now returns a 428 Precondition Required instead of a generic 403 Forbidden when the If-Match request header is missing.
For a comprehensive list of changes refer to the official changelog.
Upgrading from 0.5.0 to 0.6.0¶
There is one potentially breaking change in 0.6.0: Due to a regression 0.5.0 did not return None/null values anymore (as Eve does and 0.4.1 did). That means your API might return slightly different responses after upgrading to 0.6.0 than it did before. If it’s really a breaking change for you depends on your API specification and your clients.
Upgrading from 0.4.1 to 0.5.0¶
There are two breaking changes in 0.5.0:
- Eve-SQLAlchemy now handles related IDs and embedded objects with just one field in the payload, just as Eve does. This will most likely affect your consumers, too!
- We introduced a new way to register your SQLAlchemy models with Eve. So far
there is no backward compatible wrapper for the former
registerSchema
decorator.
Let’s look at the needed changes in more detail. To illustrate both changes, we will look at the following models (the full code is in the examples directory):
class People(CommonColumns):
__tablename__ = 'people'
id = Column(Integer, primary_key=True, autoincrement=True)
firstname = Column(String(80))
lastname = Column(String(120))
fullname = column_property(firstname + " " + lastname)
class Invoices(CommonColumns):
__tablename__ = 'invoices'
id = Column(Integer, primary_key=True, autoincrement=True)
number = Column(Integer)
people_id = Column(Integer, ForeignKey('people.id'))
people = relationship(People, uselist=False)
2. Registering of SQLAlchemy models¶
In 0.4.1, you were most likely doing something along the following lines in your settings.py:
ID_FIELD = 'id'
config.ID_FIELD = ID_FIELD
registerSchema('people')(People)
registerSchema('invoices')(Invoices)
DOMAIN = {
'people': People._eve_schema['people'],
'invoices': Invoices._eve_schema['invoices']
}
There are good news: manually (and globally) setting ID_FIELD
, including
the workaround of setting config.ID_FIELD
, is not required anymore. The
same applies to ITEM_LOOKUP_FIELD
and ITEM_URL
. While you can still
override them, they are now preconfigured at the resource level depending on
your models’ primary keys.
The required configuration for the models above simplifies to:
from eve_sqlalchemy.config import DomainConfig, ResourceConfig
DOMAIN = DomainConfig({
'people': ResourceConfig(People),
'invoices': ResourceConfig(Invoices)
}).render()
Note: If you’ve modified DATE_CREATED
, LAST_UPDATED
or ETAG
, you
have to pass their value to DomainConfig.render()
. They are needed during
rendering the final DOMAIN
configuration.
DomainConfig(domain_dict).render(date_created=DATE_CREATED,
last_updated=LAST_UPDATED,
etag=ETAG)
How to Contribute¶
Contributions are welcome! Not familiar with the codebase yet? No problem! There are many ways to contribute to open source projects: reporting bugs, helping with the documentation, spreading the word and of course, adding new features and patches.
Getting Started¶
Making Changes¶
- Fork the repository on GitHub.
- Create a topic branch from where you want to base your work.
- This is usually the
master
branch. - Please avoid working directly on the
master
branch. - Make commits of logical units (if needed rebase your feature branch before submitting it).
- Check for unnecessary whitespace with
git diff --check
before committing. - Make sure your commit messages are in the proper format.
- If your commit fixes an open issue, reference it in the commit message (#15).
- Make sure your code conforms to PEP8 (we’re using flake8 for PEP8 and extra checks).
- Make sure you have added the necessary tests for your changes.
- Run all the tests to assure nothing else was accidentally broken.
- Run again the entire suite via tox to check your changes against multiple
python versions.
pip install tox; tox
- Don’t forget to add yourself to AUTHORS.
These guidelines also apply when helping with documentation (actually, for typos and minor additions you might choose to fork and edit).
Submitting Changes¶
- Push your changes to a topic branch in your fork of the repository.
- Submit a Pull Request.
- Wait for maintainer feedback.
Join us on IRC¶
If you’re interested in contributing to the Eve-SQLAlchemy project or have any questions about it, come join us in Eve’s #python-eve channel on irc.freenode.net.
First time contributor?¶
It’s alright. We’ve all been there. See next chapter.
Don’t know where to start?¶
There are usually several TODO comments scattered around the codebase, maybe
check them out and see if you have ideas, or can help with them. Also, check
the open issues in case there’s something that sparks your interest. There’s
also a special contributor-friendly
label flagging some interesting feature
requests and issues that will easily get you started - even without knowing the
codebase yet. If you’re fluent in English (or notice any typo and/or mistake),
feel free to improve the documentation. In any case, other than GitHub help
pages, you might want to check this excellent Effective Guide to Pull
Requests
Changelog¶
0.7.2 (unreleased)¶
- Nothing changed yet.
0.7.1 (2019-08-10)¶
- Updated Tutorial to use werkzeug.security module (#196) [Mandar Vaze]
- Require Flask-SQLAlchemy >= 2.4 and SQLAlchemy >= 1.3 due to security issues [Dominik Kellner]
- Support filtering on embedded document fields / across relations (#186) [Dominik Kellner]
- Fix sorting across relations [Dominik Kellner]
- Add Python 3.7 and PyPy3 to supported (and tested) versions [Dominik Kellner]
- Pin SQLAlchemy version due to warnings in Flask-SQLAlchemy [Dominik Kellner]
- Improve documentation (#187, #189) [Marc Vila]
0.7.0 (2018-10-08)¶
- Eve 0.7 support (#178) [Nicola Iarocci]
0.6.0 (2018-08-15)¶
- Fix querying of list relations using where [Dominik Kellner]
- Update Tutorial (#177) [Nicola Iarocci]
- Return None-values again (#155) [Cuong Manh Le]
- Allow to supply own Flask-SQLAlchemy driver (#86) [fubu]
- Support columns with server_default (#160) [Asif Mahmud Shimon]
0.5.0 (2017-10-22)¶
- Add DomainConfig and ResourceConfig to ease configuration (#152) [Dominik Kellner]
- Fixes in documentation (#151) [Alessandro De Angelis]
- Fix deprecated import warning (#142) [Cuong Manh Le]
- Configure zest.releaser for release management (#137) [Dominik Kellner, Øystein S. Haaland]
- Leverage further automated syntax and formatting checks (#138) [Dominik Kellner]
- Clean up specification of dependencies [Dominik Kellner]
- Added ‘Contributing’ section to docs (#129) [Mario Kralj]
- Fix trivial app output in documentation (#131) [Michal Vlasák]
- Added dialect-specific PostgreSQL JSON type (#133) [Mario Kralj]
- Fix url field in documentation about additional lookup (#110) [Killian Kemps]
- Compatibility with Eve 0.6.4 and refactoring of tests (#92) [Dominik Kellner]
0.4.1 (2015-12-16)¶
- improve query with null values [amleczko]
0.4.0a3 (2015-10-20)¶
- hybrid_properties are now readonly in Eve schema [amleczko]
0.4.0a2 (2015-09-17)¶
- PUT drops/recreates item in the same transaction [goneri]
0.4.0a1 (2015-06-18)¶
- support the Python-Eve generic sorting syntax [Goneri Le Bouder]
- add support for and_ and or_ conjunctions in sqla expressions [toxsick]
- embedded table: use DOMAIN to look up the resource fields [Goneri Le Bouder]
0.3.4 (2015-05-18)¶
- fix setup.py metadata
- fix how embedded documents are resolved [amleczko]
0.3.3 (2015-05-13)¶
- added support of SA association proxy [Kevin Roy]
- make sure relationships are generated properly [amleczko]
0.3.2 (2015-05-01)¶
- add fallback on attr.op if the operator doesn’t exists in the ColumnProperty [Kevin Roy]
- add support for PostgreSQL JSON type [Goneri Le Bouder]
0.3.1 (2015-04-29)¶
- more flexible handling sqlalchemy operators [amleczko]
0.3 (2015-04-17)¶
- return everything as dicts instead of SQLAResult, remove SQLAResult [Leonidaz0r]
- fix update function, this closes #22 [David Durieux]
- fixed replaced method, we are compatible with Eve>=0.5.1 [Kevin Roy]
- fixed jsonify function [Leonidaz0r]
- update documentation [Alex Kerney]
- use id_field column from the config [Goneri Le Bouder]
- add flake8 in tox [Goneri Le Bouder]
0.2.1 (2015-02-25)¶
- always wrap embedded documents [amleczko]
0.2 (2015-01-27)¶
- various bugfixing [Arie Brosztein, toxsick]
- refactor sorting parser, add sql order by expresssions; please check https://eve-sqlalchemy.readthedocs.org/#sqlalchemy-sorting for more details [amleczko]
0.1 (2015-01-13)¶
- First public preview release. [amleczko]