Tutorial¶
The example app used by this tutorial is available at examples 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))
__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 us _created and _updated, but you are not forced to used
them:
class People(CommonColumns):
__tablename__ = 'people'
id = Column(Integer, primary_key=True, autoincrement=True)
firstname = Column(String(80))
lastname = Column(String(120))
Eve settings¶
All standard Eve settings will work with SQLAlchemy support. However, you
need manually decide which SQLAlchemy declarative classes you wish to
register. You can do it using registerSchema:
from eve_sqlalchemy.decorators import registerSchema
from .tables import Invoices, People
# SQLAlchemy. This is useful while debugging and in development, but is turned
# off by default.
# --------
# SQLALCHEMY_ECHO = True
# SQLALCHEMY_RECORD_QUERIES = True
As you noticed the schema will be stored inside _eve_schema class attribute so it can be easily used. You can of course extend the autogenerate schema with your custom options:
DEBUG = True
# The default schema is generated by the decorator
DOMAIN = {
'people': People._eve_schema['people'],
'invoices': Invoices._eve_schema['invoices']
}
# but you can always customize it:
DOMAIN['people'].update({
'item_title': 'person',
'additional_lookup': {
'url': 'regex("[0-9]+")',
'field': 'id'
},
'cache_control': 'max-age=10,must-revalidate',
'cache_expires': 10,
'resource_methods': ['GET', 'POST', 'DELETE']
})
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 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 generate_salt(self):
return ''.join(random.sample(string.letters, 12))
def encrypt(self, password):
"""Encrypt password using hashlib and current salt.
"""
return str(hashlib.sha1(password + str(self.salt))\
.hexdigest())
@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.
"""
self.salt = self.generate_salt()
return self.encrypt(value)
def check_password(self, password):
if not self.password:
return False
return self.encrypt(password) == self.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:
from eve import Eve
from eve_sqlalchemy import SQL
from eve_sqlalchemy.validation import ValidatorSQL
from .tables import Base, People
app = Eve(validator=ValidatorSQL, data=SQL)
# bind SQLAlchemy
db = app.data.driver
Now you can run Eve:
app.run(debug=True)
and start it:
$ python sqla_example.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.
Using those expresssion is straightforward (you can use them only with dictionary where filter):
http://127.0.0.1:5000/people?where={"lastname":"like(\"Smi%\")"}
which produces where closure:
people.lastname LIKE "Smi%"
Another examples using in:
http://127.0.0.1:5000/people?where={"firstname":"in(\"(\'John\',\'Fred\'\"))"}
which produces where closure:
people.firstname IN ("John", "Fred")
Another examples using similar to:
http://127.0.0.1:5000/people?where={"firstname":"similar to(\"(\'%ohn\'|\'%acob\'\"))"}
which produces where closure:
people.firstname SIMILAR TO '("%ohn"|"%acob")'
and if you have postgresql ARRAY column you can use any:
http://127.0.0.1:5000/documents?where={"keywords":"any(\"critical\")"}
which produces where closure:
"critical" = ANY(documents.keywords)
and if you want to query using NULL:
http://127.0.0.1:5000/documents?where={"keywords":"!=null"}
which produces where closure:
documents.keywords IS NOT NULL
SQLAlchemy 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:
http://127.0.0.1:5000/people?sort=[("lastname", -1, "nullslast")]
which produces order by expression:
people.lastname DESC NULLS LAST
You can also support the following python-Eve syntax:
http://127.0.0.1:5000/people?sort=lastname,-created_at
How to adjust the primary column name¶
Eve use the _id column as primary id field. This is the default value of the MongoDB database. In SQL, it much more common to call this column just id. You can do that with the following change in your settings.py:
from eve.utils import config
ID_FIELD = 'id'
ITEM_LOOKUP_FIELD = ID_FIELD
config.ID_FIELD = ID_FIELD
config.ITEM_LOOKUP_FIELD = ID_FIELD
registerSchema('people')(People)
DOMAIN = {
'people': People._eve_schema['people'],
}
Embedded resources¶
Eve-SQLAlchemy support the embedded keyword of python-eve (Eve Embedded Resource Serialization).
http://127.0.0.1:5000/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.
