marshmallow-code / marshmallow-sqlalchemy Goto Github PK
View Code? Open in Web Editor NEWSQLAlchemy integration with marshmallow
Home Page: https://marshmallow-sqlalchemy.readthedocs.io
License: MIT License
SQLAlchemy integration with marshmallow
Home Page: https://marshmallow-sqlalchemy.readthedocs.io
License: MIT License
Is there anyway of using marshmallow-sqlalchemy to validate json which contains data for multiple tables?
Example:
Given 2 tables Question and InputQuestion where InputQuestion is an extension of Question,
the following json is posted
{
"question": {
"question_type_id": 1,
"client_date_created": "17:25:12.517000",
"company_id": 74
},
"input_question": {
"input_question_type_id": 1,
"require_input_if_positive": false,
"device_input_type_id": 1,
"require_photo_if_positive": false
}
}
My naive and unsuccessful attempt to validate this was to setup a schema, with nested instances of
ModelSchema classes.
class Linked_Checklist_Input_Question_Schema(Schema):
question = fields.Nested(Checklist_Question_Schema)
input_question = fields.Nested(Checklist_Input_Question)
#test link schema
schema = Linked_Checklist_Input_Question_Schema()
#load example json
result = schema.load(data)
#which gives us as result.errors
{u'_schema': [u'Invalid type.']}
`
Given a schema hierarchy of album->track->genres, if track.genres
is a Related
field, tracks.genres.model
returns the model of the top level schema (Album) rather than the model of the Related
field's parent (Track). This causes an attribute error, as Related.model
tries to find the genres
attribute on the Album model rather than the Track model.
I can't seem to come up with any justification or advantage to using field.root
rather than field.parent
in get_schema_for_field
. Obviously I can override this problem pretty easily, and am happy to do so, but wanted to try and get some insight as to if there was a specific reason for using field.root
.
In my application, I have an API endpoint like so:
/api/activities/
In my database, I have several different types of Activity models, all inheriting from a common class. I made a schema like so:
class ActivitySchema(ModelSchema):
class Meta:
model = models.Activity
And then have flask-restful handle the api endpoint itself, querying the database and passing the objects to marshmallow-alchemy.
However, marshmallow-sqlalchemy serializes the objects as if they were all of the base Activity type. It would be nice if there was some support like this:
class ActivitySchema(ModelSchema):
class Meta:
model = models.Activity
generate_polymorphic_schemas = True
Which would use sqlalchemy's inspection to view all polymorpic subclasses of Meta.model
, store them on ActivitySchema
, and then when serializing/deserializing use the appropriate class.
Thoughts?
If this is a desirable feature, I can try to make a PR implementing such functionality.
For example I have a JSONB field on the model which is currently mapped to Raw
. I would like to make it map to Dict
. Ideally I could write:
content = field_for(MyModel, 'content', klass=fields.Dict)
After upgrading to 0.6.0 I get an "Invalid Syntax" exception on line 133 in schema.py:
filters = {
column.key: data.get(column.key)
for column in columns
}
"for column in columns" is not a valid syntax.
http://marshmallow-sqlalchemy.readthedocs.io/en/latest/recipes.html#base-schema-ii say that BaseOpts should use SchemaOpts, but with that I get:
AttributeError: 'BaseOpts' object has no attribute 'model_converter'
If I change it to ModelSchemaOpts it works though.
map_field
takes an id and field, gets and converts an object to dict using marshmallow, and takes out the specified field.
In [59]: map_field('foo', 30)
Out[59]: '2015-08-05T06:49:57.347259+00:00'
In [60]: map_field('foo', 40)
Out[60]: datetime.datetime(2015, 8, 4, 11, 56, 41, 97946)
I have no clue why this is happening, but it occurs only with dates.
Hi,
author = Author(name='Chuck Paluhniuk')
book = Book(title='Fight Club', author=author)
session.add(author)
session.add(book)
session.commit()
author_schema.dump(author).data
output model book:
# {'books': [123], 'id': 321, 'name': 'Chuck Paluhniuk'}
how to access to model books if i set list model like ,
lst = ses.query(author ).all()
author_schema.dump(lst).data
output :
# {'books': [123], 'id': 321, 'name': 'Chuck Paluhniuk'}
but i need access to model book?
marshmallow-sqlalchemy breaks if a column with for a foreign key is NULL/None.
Use the main example from project's description and insert a book without an author and try to serialize it.
>>> book = Book(title="Fight Club")
>>> session.add(book)
>>> session.commit()
>>> book_schema = BookSchema()
>>> book_schema.dump(book).data
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/schema.py", line 564, in dump
**kwargs
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/marshalling.py", line 137, in serialize
index=(index if index_errors else None)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/marshalling.py", line 56, in call_and_store
value = getter_func(data)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/marshalling.py", line 131, in <lambda>
getter = lambda d: field_obj.serialize(attr_name, d, accessor=accessor)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/fields.py", line 221, in serialize
return self._serialize(value, attr, obj)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow/fields.py", line 1180, in _serialize
value = self.keygetter(value)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/marshmallow_sqlalchemy/convert.py", line 18, in get_pk_from_identity
_, key = identity_key(instance=obj)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/sqlalchemy/orm/util.py", line 275, in identity_key
mapper = object_mapper(instance)
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/sqlalchemy/orm/base.py", line 266, in object_mapper
return object_state(instance).mapper
File "/home/shreyder/.virtualenvs/mtest/lib/python3.4/site-packages/sqlalchemy/orm/base.py", line 288, in object_state
raise exc.UnmappedInstanceError(instance)
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.NoneType' is not mapped
Using SQLAlchemy to process MySQL tables for an API I'm building, and one of the columns in my table happens to be type 'BIT'. This apparently isn't supported Marshmallow, but is a valid column type for SQLAlchemy's MySQL dialect. Any chance of a fix, or is there an easier work around in the Marshmallow-SQLAlchemy connector that I'm missing?
This is basically the example from here: http://marshmallow-sqlalchemy.readthedocs.org/en/latest/
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship
from sqlalchemy import event
from sqlalchemy.orm import mapper
engine = sa.create_engine('sqlite:///:memory:')
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()
class Author(Base):
__tablename__ = 'authors'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
def __repr__(self):
return '<Author(name={self.name!r})>'.format(self=self)
class Book(Base):
__tablename__ = 'books'
id = sa.Column(sa.Integer, primary_key=True)
title = sa.Column(sa.String)
author_id = sa.Column(sa.Integer, sa.ForeignKey('authors.id'))
author = relationship("Author", backref='books')
def __repr__(self):
return '<Book(title={self.title!r})>'.format(self=self)
Base.metadata.create_all(engine)
author = Author(name='Chuck Paluhniuk')
session.add(author)
book = Book(title='Fight Club', author=author)
session.add(book)
session.commit()
from marshmallow_sqlalchemy import ModelSchema
class BookSchema(ModelSchema):
class Meta:
model = Book
sqla_session = session
class AuthorSchema(ModelSchema):
class Meta:
model = Author
sqla_session = session
author_schema = AuthorSchema()
book_schema = BookSchema()
# Print the author to show that it's definitely there
print book.author
dump_data = author_schema.dump(author).data
print dump_data
# {'books': [123], 'id': 321, 'name': 'Chuck Paluhniuk'}
print author_schema.load(dump_data).data
# Everything seems fine until:
print book_schema.dump(book).data
#{'title': u'Fight Club', 'id': 1, 'author': 1}
Result of the last print:
{'title': u'Fight Club', 'id': 1, 'author': None}
The author should not be None
, it should be 1
To determine the field class to be used for a SQLAlchemy column, we sometimes check its python_type
. But some columns, e.g. Postgres TSVECTOR
columns, don't have a python_type
and raise a NotImplementedError
on trying to access. Some possible fixes:
TSVECTOR
to SQLA_TYPE_MAPPING
. This would kind of fix the immediate problem I'm running into, but what's the right marshmallow type to use? We could use Str
, but that's not exactly right. Also, this doesn't help if other columns are missing python_type
, which might or might not be the case.Meta.exclude
option in marshmallow. This is more flexible but requires developers to understand that they can / should exclude columns that don't convert to fields.strict
option to fields_for_model
, such that ModelConversionErrors
are only raised when strict
is true. This is also flexible, but allows developers to silence potentially heterogeneous kinds of exceptions.I'm submitting a patch for the second option, mostly so I can get some unrelated work done, but I think the third also makes sense, and it might not be bad to use both. What do you think @sloria?
This is admittedly a very narrow use case, but users may occasionally want to create schemas for abstract models. Since by definition abstract models don't have mappers, the logic used to list columns and relationships won't work. This could be handled easily enough by listing model attributes without using the mapper, although it would be less elegant:
for key in dir(model):
attr = getattr(model, key)
if isinstance(attr, (Column, MapperProperty)):
# ingest column
...
The main plausible use case is a user who wants to define an ABC with several concrete subclasses, but only wants to serialize columns defined on the ABC. @sloria is this too narrow to support?
class Choices(enum.Enum):
a = 'a'
b = 'b'
c = 'c'
class MyModel(db.Model):
# ...
selected_choice = db.Column(db.Enum(Choices), nullable=False)
class MyModelSchema(ModelSchema):
class Meta:
model = MyModel
fields = ['selected_choice']
The MyModelSchema
end up having
selected_choice.validate[0].choices == (<enum 'ChoicesEnum'>,)
(notice a tuple), because MyModel.selected_choice.type.enums
stores a tuple of an enum for some reason...
BTW, constructing a marshmallow field with oneOf(ChoicesEnum)
(omit the unnecessary tuple wrapping) doesn't help much:
>>> f = fields.Str(validate=[
validate.OneOf(ChoicesEnum),
])
>>> f.serialize('a', ChoicesEnum.a)
'ChoicesEnum.a'
And I couldn't make it to deserialize.
A related discussion that I have found: #2
Hello,
With roughly the following models:
class Tariff(rod.model.db.Model, rod.model.PersistentMixin):
id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, primary_key=True)
title = sqlalchemy.schema.Column(sqlalchemy.types.String())
# Course this tariff belongs to
course_id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, sqlalchemy.schema.ForeignKey('course.id'))
course = sqlalchemy.orm.relationship(
'Course',
back_populates='tariffs'
)
# Price of this payment plan
price = sqlalchemy.schema.Column(sqlalchemy.types.Integer)
class Course(rod.model.db.Model, rod.model.PersistentMixin):
id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, primary_key=True)
title = sqlalchemy.schema.Column(sqlalchemy.types.String())
levels = sqlalchemy.orm.relationship(
'Level',
back_populates='course'
)
tariffs = sqlalchemy.orm.relationship(
'Tariff',
back_populates='course'
)
And the following schemas:
class CourseSchema(rod.model.BaseSchema):
class Meta(rod.model.BaseSchema.Meta):
model = rod.model.course.Course
tariffs = marshmallow.fields.Nested('TariffSchema', many=True, exclude=('course',))
class TariffSchema(rod.model.BaseSchema):
class Meta(rod.model.BaseSchema.Meta):
model = rod.model.tariff.Tariff
course = marshmallow.fields.Nested(CourseSchema)
I'm POSTing the following body:
{"course":{"id":1},"course_id":1,"title":"uu","price":"222"}
To my handler:
tariff_obj = rod.model.schemas.TariffSchema().load(flask.request.json).data
rod.model.db.session.add(tariff_obj)
rod.model.db.session.commit()
return flask.jsonify(rod.model.schemas.TariffSchema().dump(tariff_obj).data)
However, for some reason after load()
ing the JSON dump, my tariff_obj.course_id
is None
, and so it tariff_obj.course.id
, even though the rest of the tariff_obj.course
looks like a proper (albeit new) Course class instance.
Is this a bug?
Thanks.
I can't work out how to expose model properties created using @Property. E.g. as from your examples:
@property
def url(self):
return url_for('author', id=self.id)
I might want to include this property when returning data. However, the best help I could find from the API reference is using property2field, but in trying to use:
url = property2field(Author.__mapper__.get_property('url')
I get the following error:
sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|Post|posts' has no property 'url'
It seems like this ought to be possible, but I can't work it out. Any help appreciated.
Hi,
I'm currently building a module to integrate marshmallow with mongoengine (see: https://github.com/touilleMan/marshmallow-mongoengine). To do so I started from the codebase of marshmallow-sqlachemy.
Thus, I was wondering what is the use of inspect.getmro
in the schema.get_declared_field
function (see https://github.com/marshmallow-code/marshmallow-sqlalchemy/blob/dev/marshmallow_sqlalchemy/schema.py#L39) given the loop make use of opst = klass.opts
(i.e. the real class options) instead of the current motherclass's ones (so something like opts = base.opts
)
Is it an error or am I missing something ?
Another idea that is straight from ColanderAlchemy.
I believe this could be implemented relatively easily, but there are numerous different ways one could go about it, so I think perhaps some discussion is more appropriate than forging ahead with a PR which might turn out not to be the best way at all.
I would be really nice to be able to do something like this:
id = Column(Integer,
primary_key=True,
info={
'marshmallow_sqlalchemy': {
'dump_only': False
}
})
These would then be processed when constructing the schemas to override the settings. Potentially anything that marshmallow can do could be declared there.
I hacked this in quickly:
def _add_column_kwargs(self, kwargs, column):
"""Add keyword arguments to kwargs (in-place) based on the passed in
`Column <sqlalchemy.schema.Column>`.
"""
if column.nullable:
kwargs['allow_none'] = True
if hasattr(column.type, 'enums'):
kwargs['validate'].append(validate.OneOf(choices=column.type.enums))
# Add a length validator if a max length is set on the column
if hasattr(column.type, 'length'):
kwargs['validate'].append(validate.Length(max=column.type.length))
if hasattr(column.type, 'scale'):
kwargs['places'] = getattr(column.type, 'scale', None)
# Primary keys are dump_only ("read-only")
if getattr(column, 'primary_key', False):
kwargs['dump_only'] = True
if 'marshmallow_sqlalchemy' in column.info:
settings = column.info['marshmallow_sqlalchemy']
if 'dump_only' in settings:
kwargs['dump_only'] = settings['dump_only']
if 'load_only' in settings:
kwargs['load_only'] = settings['load_only']
and it worked quite well. It didn't support inheritance though which we definitely should I think. That can be achieved relatively easily as well I think as we have for each property, the list of applicable columns, so we just need to iterate over them in order from super to sub and apply the settings found in info to provide ordinary inheritance override behaviour. I think the same thing could be applied to relationships as well.
Thoughts?
Let me start by saying this might not be a bug.
Looks like kelvinhammond@12da838 breaks compatibility with sqlalchemy-utils (particularly PhoneNumberType
). It seems like a bug in sqlalchemy-utils where it has a python_type
of method
(rather than a class).
I have a pull request which should fix the issue:
kvesteri/sqlalchemy-utils#248
I think this is a bug in sqlalchemy-utils, but it might also be reasonable to be a little more forgiving by gracefully handling cases where issubclass
will fail. Tough call - on the one hand it is good to be accommodating but on the other hand we should trust that people are adhering to what appears to be a pretty definitive interface definition:
http://docs.sqlalchemy.org/en/latest/core/type_api.html#sqlalchemy.types.TypeEngine.python_type
Here is the relevant stack trace:
File "marshmallow_sqlalchemy/convert.py", line 219, in _add_column_kwargs
if not python_type or not issubclass(python_type, uuid.UUID):
TypeError: issubclass() arg 1 must be a class
(Pdb) python_type
<bound method PhoneNumberType.python_type of PhoneNumberType(length=20)>
Hi,
My SQL Alchemy models are totally decoupled from ways to access them (so from both an engine and a session). My session objects are created depending on the python process that makes use of the models (for instance, I am using a scoped_session
for my web app, a plain Session
object for one-off scripts).
This makes it impossible to use this library. Why is it a requirement to pass the session to the model declaration?
Hi,
I've created an Association Table as described in the SQLAlchemy manual, however when I try to load on a POST request I get this response:
RuntimeError: Model <class 'app.products.models.SoldProducts'> has multiple primary keys
Which is true, as the association table creates a compound key. How should I proceed?
Thanks!
I'm developing an api in Flask,
In my update functions I would like to restrict the fields that can be updated e.g. I don't want users to be able to change their email at the moment.
To achieve this I have set up a schema (UserSchema) with its fields restricted by a tuple (UserSchemaTypes.UPDATE_FIELDS).The tuple does not include email.
The problem I am having is that email is a required field for User rows in my database.
So when I create a User model object using the schema (users_schema.load(user_json) ) an illegal object is added to the sqlalchemy session.
#schema to validate the posted fields against
users_schema = UserSchema(only=UserSchemaTypes.UPDATE_FIELDS)
#attempt to deserialize the posted json to a User model object using the schema
user_data = users_schema.load(user_json)
if not user_data.errors:#update data passed validation
user_update_obj = user_data.data
User.update(user_id,vars(user_update_obj))
In my update function itself I then have to remove this illegal object from the session via db.session.expunge_all() as if I do not I receive an OperationalError.
@staticmethod
def update(p_id,data):
db.session.expunge_all()#hack I want to remove
user = User.query.get(p_id)
for k, v in data.iteritems():
setattr(user, k, v)
db.session.commit()
OperationalError received when db.session.expunge_all() is removed:
OperationalError: (raised as a result of Query-invoked autoflush; consider
using a session.no_autoflush block if this flush is occurring prematurely)
(_mysql_exceptions.OperationalError) (1048, "Column 'email' cannot be null") [SQL: u'INSERT INTO user (email, password, active, phone, current_login_at, last_login_at, current_login_ip, last_login_ip, login_count) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: (None, None, 1, '0444', None, None, None, None, None)]
I'm using json field from sqlalchemy_utils (https://sqlalchemy-utils.readthedocs.org/en/latest/data_types.html#module-sqlalchemy_utils.types.json) and after recent upgrade (from 0.2 to 0.8, wow) marshmallow_sqlalchemy throws ModelConversionError
traceback
File "./manager.py", line 2, in <module>
from dashboard.tasks import manager
File "/Users/zz/Dropbox/Workspace/python/trackoji_db/dashboard/tasks.py", line 7, in <module>
app = create_app()
File "/Users/zz/Dropbox/Workspace/python/trackoji_db/dashboard/__init__.py", line 38, in create_app
register_resources(api)
File "/Users/zz/Dropbox/Workspace/python/trackoji_db/dashboard/__init__.py", line 66, in register_resources
from .resources.widget import WidgetResource
File "/Users/zz/Dropbox/Workspace/python/trackoji_db/dashboard/resources/widget.py", line 11, in <module>
class WidgetSchema(ma.ModelSchema):
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow/schema.py", line 115, in __new__
dict_cls=dict_cls
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/schema.py", line 57, in get_declared_fields
declared_fields = mcs.get_fields(converter, opts)
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/schema.py", line 90, in get_fields
include_fk=opts.include_fk,
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 100, in fields_for_model
field = self.property2field(prop)
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 118, in property2field
field_class = self._get_field_class_for_property(prop)
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 177, in _get_field_class_for_property
field_cls = self._get_field_class_for_column(column)
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 146, in _get_field_class_for_column
return self._get_field_class_for_data_type(column.type)
File "/Users/zz/.virtualenvs/trackoji_db/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 169, in _get_field_class_for_data_type
'Could not find field column of type {0}.'.format(types[0]))
marshmallow_sqlalchemy.exceptions.ModelConversionError: Could not find field column of type <class 'sqlalchemy_utils.types.json.JSONType'>.
model:
class Widget(db.Model, BaseModel):
id = db.Column(db.Integer, primary_key=True)
params = db.Column(JSONType)
versions:
flask-marshmallow==0.6.2
marshmallow==2.6.0
marshmallow-sqlalchemy==0.8.0
sqlalchemy-utils==0.31.6
I would like to be able to do something like this:
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String),
)
class UserSchema(TableSchema):
class Meta:
table = users
Because sometimes you want to use SQLAlchemy, but you don't want an ORM.
After declaring a relationship field as dump_only
, the data is still loaded back into the object.
Model and schema:
class Course(rod.model.db.Model):
__tablename__ = 'course'
id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, primary_key=True)
title = sqlalchemy.schema.Column(sqlalchemy.types.String())
# Levels of this course
levels = sqlalchemy.orm.relationship(
'Level',
back_populates='course'
)
class CourseSchema(rod.model.BaseSchema):
class Meta(rod.model.BaseSchema.Meta):
model = rod.model.course.Course
levels = marshmallow_sqlalchemy.field_for(rod.model.course.Course, 'levels', dump_only=True)
Loading the object:
data = {
'title': 'Test Course',
'levels': [1, 2, 3] # Should be ignored
}
course = CourseSchema().load(data).data
print course.levels
Expected:
None
Actual:
{InstrumentedList} [<Level object>, <Level object>, <Level object>]
I am extending the Schema
class from marshmallow for my schemas and extending the TYPE_MAPPING
attribute, but because of these lines here, the ModelConverter
doesn't pick up my changes, because it hardcodes marshmallow's Schema
class.
from datetime import datetime
from marshmallow import Schema
[...]
class BaseSchema(Schema):
TYPE_MAPPING = Schema.TYPE_MAPPING.copy()
TYPE_MAPPING.update({
datetime: APIDateTime,
})
def __init__(self, *args, **kwargs):
if 'strict' not in kwargs:
kwargs['strict'] = True
super(BaseSchema, self).__init__(*args, **kwargs)
How do I override the mapping in marshmallow-sqlalchemy for datetime => my own custom field?
Not sure if this is intended functionality, however the code below throws ValueError: Deserialization requires a session
when trying to load data into a nested object.
I would expect passing a session would propagate to nested fields, however this doesn't seem to be the case. Is this by design?
If i remove followup
(the nested field) from the only=
property, the data loads fine from the non-nested field.
class FollowUp(ModelSchema):
reasonCode =fields.String(attribute='reason_code')
comment = fields.String()
class Occurence(ModelSchema):
#... other fields before ...
followup = fields.Nested(FollowUp())
status = fields.String()
# Inside my request handler:
obj = DBOccurence.query.get_or_404(occ_id)
res = Occurence(only=['status', 'followup']).load(
request.json,
instance=obj,
session=current_app.db.session
)
Hello,
Imagine a trivial use case in which we:
Lets take a sample model:
class Group(rod.model.db.Model):
__tablename__ = 'group'
id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, primary_key=True)
title = sqlalchemy.schema.Column(sqlalchemy.types.String(100))
teacher_id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, sqlalchemy.schema.ForeignKey('staff.id'))
teacher = sqlalchemy.orm.relationship(
'Staff', # References the Staff class below
back_populates='groups'
)
class Staff(rod.model.db.Model, rod.model.PersistentMixin):
__tablename__ = 'staff'
id = sqlalchemy.schema.Column(sqlalchemy.types.Integer, primary_key=True)
# Personal Information
name = sqlalchemy.schema.Column(sqlalchemy.types.String)
groups = sqlalchemy.orm.relationship(
'Group',
back_populates='teacher'
)
Since marshmallow-sqlalchemy converts relation fields to IDs between serialization and deserialization, the JSON string {"teacher": 1}
when deserialized on Group becomes Group.teacher: Object<Teacher>
, which looks like a new Teacher object, although it may be existing in the database.
The result, when you try to simply save the Group instance, is this:
FlushError: New instance <Group at 0x1088fd5d0> with identity key (<class 'rod.model.group.Group'>, (26,)) conflicts with persistent instance <Group at 0x1089c74d0>
I going to deserialize json model
Models:
class Users(Base):
__tablename__ = 'users'
ID = Column(Integer, primary_key=True,autoincrement=True)
Username = Column(String(50),nullable=False,unique=True)
Email = Column(String(200),nullable=False,unique=True)
Name = Column(String(20))
Password = Column(String(50))
Date = Column(Date)
class Logs(UserBase):
__tablename__ = 'logs'
ID = Column(Integer, primary_key=True,autoincrement=True)
UserLog = Column(String(50))
log_id = Column(Integer, ForeignKey('users.ID'))
log = relationship("Users",lazy="joined", backref=backref('logs'))
Schemas:
class LogsSchema(ModelSchema):
class Meta:
model = Logs
sqla_session = Session
class UsersSchema(ModelSchema):
logs = fields.Nested(LogsSchema,exclude=('log', ))
class Meta:
model = Users
sqla_session = Session
json model request
<QueryDict: {u'Username': [u'ramin world'], u'logs': [u'[UserLog=test]'], u'Date': [u'null'], u'Password': [u'1234'], u'Email': [u'[email protected]'], u'Name': [u'Farajpour']}>
load json request:
{'Username': u'ramin world', 'Password': u'1234', 'Name': u'Farajpour', 'Email': u'[email protected]'}
you see model u'logs': [u'[UserLog=test]']
is not on result.
I'm still trying to understand the benefits of using marshmallow-sqlalchemy above what marshmallow already gives me.
The obvious one is auto-generation of fields, but for my schemas, most of the fields require additional arguments such as dump_only
or required
, so this doesn't add much for me.
I checked the docs, but couldn't find much. However, I was just reading the code and noticed that a length validator is automatically included when sqlalchemy has a length constraint on the underlying db column. Similarly, #47 (comment) mentions that fields that aren't allowed to be null have marshmallow required
added.
Both are clever optimizations--and I think it'd be worth mentioning in the docs.
Ultimately, be nice if there was a clear set of 'here's the benefits of this extension over and above vanilla marshmallow' as well as 'here's what using field_for(column)
provides over and above the typical fields.str()
or fields.Integer()
'
Hi ๐
This is my first visit to this fine repo, but it seems you have been working hard to keep all dependencies updated so far.
Once you have closed this issue, I'll create seperate pull requests for every update as soon as I find one.
That's it for now!
Happy merging! ๐ค
I often use fields.Nested to handle relationships (fields.Related doesn't do what I need). This idea for fields.Relationships creates a fields.Nested, where the schema is based on the parent schema, and the sqlalchemy class for the relation. It does "many" automatically, and also excludes the foreign key to the parent (which is redundant). Example usage:
class OrderSchema(ModelSchema):
class Meta:
model = db.Order
sqla_session = db.session
items = Relationship()
This is the code I'm using:
class Relationship(fields.Nested):
def __init__(self, **kwargs):
super(Relationship, self).__init__(None, **kwargs)
@property
def schema(self):
if not hasattr(self, '_schema'):
property = getattr(self.root.Meta.model, self.attribute or self.name).property
self.many = property.uselist
class InnerSchema(ModelSchema):
class Meta(self.root.Meta):
model = property.mapper.class_
exclude = [c.name for c in property.remote_side]
self._schema = InnerSchema()
return self._schema
What I would really like to do - and I'm not sure how at the minute - is have this span multiple levels of relationship.
It appears that schemas for models with custom column types, which have a python_type
of uuid.UUID
, have a Length validator set on them. This fails when in validator.Length.__call__
, len()
is called on the UUID.
The column type is basically the example from the SQLAlchemy docs, with the addition of a python_type
property that returns uuid.UUID
.
I could just use the sqlalchemy.dialects.postgresql.UUID
, but I don't want to tie myself to just postgresql. Is there a recommended way for handling custom columns with this library?
Stack trace:
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow_sqlalchemy/schema.py:188: in validate
return super(ModelSchema, self).validate(data, *args, **kwargs)
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/schema.py:574: in validate
_, errors = self._do_load(data, many, postprocess=False)
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/schema.py:603: in _do_load
index_errors=self.opts.index_errors,
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/marshalling.py:283: in deserialize
index=(index if index_errors else None)
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/marshalling.py:65: in call_and_store
value = getter_func(data)
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/marshalling.py:276: in <lambda>
data
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/fields.py:263: in deserialize
self._validate(output)
../../../../.virtualenvs/server/lib/python3.5/site-packages/marshmallow/fields.py:195: in _validate
if validator(value) is False:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <Length(min=None, max=None, error=None)>, value = UUID('a6f4f8ed-c20d-4f69-b335-88f2292d21e9')
def __call__(self, value):
> length = len(value)
E TypeError: object of type 'UUID' has no len()```
Back in 0.1, we had a basic interface for customizing the behavior of relationship fields via a keygetter
passed to QuerySelect
or QuerySelectList
. That wound up being a less than ideal way to model relationships, which is why we introduced the Related
field. Still, it would still be helpful to expose some interface for customizing related fields without overriding every Related
by hand. For example, users may want to use something like StringRelatedField
from DRF or Relationship
from marshmallow-jsonapi.
At the moment, it seems like you'd subclass ModelConverter
and override some combination of _get_field_class_for_property
and _add_relationship_kwargs
to do this. Which works, but seems kind of complicated, and requires users to know a lot about private APIs. What about adding options to ModelSchemaOpts
for setting the field class for relationships, defaulting to Related
? Or adding an optional related_factory
that's invoked on converting relationships?
The current behaviour of ModelSchema is not that useful to me. Where I have, say, an "order" many-to-one relationship, by default it dumps a field "order" with the order_id, and this serialised data does not then load. It would be more helpful to dump this as field "order_id". one-to-many relationships are similarly not that useful.
Right now I am using a lot of excludes and manually defined fields, but this is unnecessary. In fact, marshmallow-sqlalchemy already has much more helpful default logic, on TableSchema, especially with include_fk=True. I can't directly use TableSchema as it only supports dump and I also need to load.
However, a one line change to schema.py really helps:
return converter.fields_for_model(opts.model, fields=opts.fields, exclude=opts.exclude)
-->
return converter.fields_for_table(opts.model.__table__, include_fk=True, fields=opts.fields, exclude=opts.exclude)
I realise this would break compatibility, but it seems a much more helpful default to me.
I use sqlalchemy_utils which provide ChoiceType field (and many others).
I'm able to create a new Marshmallow field (Field) to handle this new field with proper _serialize/_deserialize
functions but instead to override the attributes one by one in each schema I defined I want to update the list type_mapping in ModelConverter.
I didn't find how to access it properly, ModelConverter accepts schema_cls
attribute at init
https://github.com/marshmallow-code/marshmallow-sqlalchemy/blob/dev/marshmallow_sqlalchemy/convert.py#L76
but I'm not able to figure out how to use it properly with all these meta classes ;)
So for now I rely on a monkey patch:
ModelConverter.SQLA_TYPE_MAPPING.update({
ChoiceType: ChoiceTypeSchema
})
PS: I use flask-marshmallow in my app.
Is there a way to avoid inserting the data into session while using Marshmallow - sqlalchemy .load()
Because we tried to manage the objects by ourself (adding them into session if required) . Will add into the session if required , but for only validation I need to use load () method which is provided by marshmallow-sqlalchemy
as in ModelSchema.load
self.instance = instance or self.instance
because I instantiation schema class in module level, reuse them in multiple places and across requests,
the .instance
value firstly set will persists in following load()
calls, regardless load(instance=None)
, this will trigger nasty bug as its value depends on request order.
is there a good reason for this behavior and not just set self.instance
from parameter?
or should I instantiation a new schema object before any load
or dump
call?
As of today 12/9 loading a dictionary to a model that has default fields is no longer using the alchemy default fields and throwing errors.
Example below, Python 3
from datetime import datetime
from app import db
from app.models.base import Base as SQLBASE
from app.schemas import Base
__all__ = ["TestModel"]
class TestModel(SQLBASE):
__tablename__ = 'test_table'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Unicode(255), nullable=False, unique=True)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
class TestModelSchema(Base):
class Meta(Base.Meta):
dateformat = "%Y-%m-%d"
model = TestModel
test_schema = TestModelSchema()
class TestSchemaFunctions:
def test_no_defaults(self):
dict_model = {"name": "TestName"}
results = test_schema.load(dict_model)
print(results.errors, results.data)
# (Pdb) results.errors
# {'updated_at': ['Missing data for required field.'], 'created_at': ['Missing data for required field.'], 'id': ['Missing data for required field.']}
# (Pdb) results.data
# {'name': 'TestName'}
Currently when querying and serializing against a polymorphic model, the returned attributes are only the ones defined in the parent schema. And not of any of the child Schema, which could include specific validations or other post_/pre_ steps that need to be followed. We've implemented our own implementation with our own models, which is essentially a new custom field on the abstract model that chooses it's current polymorphic target; however, this is specific to our codebase. I and generalize it out and provide an pull request.
Some of my API's let people create new objects, and some of the fields are optional foreign keys. Currently when a foreign key gets passed in, I have some custom validators that check the database to verify that the FK object actually exists. I could catch the exception, but rather check it on initial POST/PATCH.
This strikes me as something that would be useful as a built-in validator for marshmallow-sqlalchemy
I think the implementation would be fairly straightforward, although it's an open question in my mind whether to use session.query(foreign object).options(load_only(pk_field)).get(pk id)
or something a little more fancy like:
if not db.session.query(db.exists([GearCategory.id]).where(GearCategory.id == category_id)).scalar():
raise Exception("Gear Category ID [%s] does not exist" % category_id)
The get()
will be far faster when the object is already in the session, but slower than the EXISTS
if the query has to actually hit the DB.
It looks like marshmallow-sqlalchemy makes no use of lazy='joined' loaded objects?
I'm using a schema dump with many=True to create a list of all objects and I'm loading the only relationship (list of url's) joined to not produce a query for every row but creates a select anyways.
Installing [email protected] results in schemas generated via ModelSchema
not having their fields set, thus dumping empty dicts. It looks like get_declared_fields
was added in marshmallow-code/marshmallow@f993b89, which is currently in the beta build. This commit fixes the issue.
Just a heads-up while we wait for the next marshmallow release. :P
I'm using Flask-SQLalchemy, Flask-Marshmallow, Marshmallow-SQLAlchemy and Marshmallow.
A couple of my SQLAlchemy models have @hybrid_properties
.
Here's my field:
count_direct_child_items = field_for(GearCategory, 'count_direct_child_items', dump_only=True)
When it tries to fetch the hybrid property, it fails because the hybrid property is not in the mapper.
all_orm_descriptors
may be useful for fixing this, as mentioned in this SQLAlchemy mailing list issue.
Here's the actual traceback:
Traceback (most recent call last):
File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py", line 1804, in get_property
return self._props[key]
KeyError: 'count_recursive_child_items'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "manage.py", line 15, in <module>
from app.serializers.gear_serializers import (GearCategorySchema, GearItemSchema,
File "/Users/jeffwidman/Code/rc/api_rc_flask/app/serializers/gear_serializers.py", line 9, in <module>
class GearCategorySchema(marshmallow.ModelSchema): # TODO switch to HyperlinkModelSchema
File "/Users/jeffwidman/Code/rc/api_rc_flask/app/serializers/gear_serializers.py", line 25, in GearCategorySchema
count_recursive_child_items = field_for(GearCategory, 'count_recursive_child_items', dump_only=True)
File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/marshmallow_sqlalchemy/convert.py", line 117, in field_for
prop = model.__mapper__.get_property(property_name)
File "/Users/jeffwidman/.virtualenvs/api_rc_flask/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py", line 1807, in get_property
"Mapper '%s' has no property '%s'" % (self, key))
sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|GearCategory|gear_category' has no property 'count_recursive_child_items'
Say you have a model:
class Author(Base):
__tablename__ = 'Author'
author_id = Column("AuthorId", Integer, primary_key=True)
name = Column("Name", Unicode(160), nullable=False)
get_primary_key will return AuthorId as the primary key, but the attribute that's actually desired is author_id. When I go go to serialize a BookSchema (with a many relationship on authors), the list of Author primary keys incorrectly gets filled with None. Using the below snippet instead solves the issue for me:
from sqlalchemy.orm import ColumnProperty, class_mapper
def get_primary_columns(model):
"""Get primary key columns for a SQLAlchemy model.
:param model: SQLAlchemy model class
"""
primary_keys = []
for primary_key in class_mapper(model).primary_key:
for prop in class_mapper(model).iterate_properties:
if isinstance(prop, ColumnProperty):
if prop.columns:
if prop.columns[0].compare(primary_key):
primary_keys.append(prop)
break
return primary_keys
Would love some input here on whether this all makes sense.
When I use ModelSchema
, and set ordered=True
in class Meta
, the output isn't the same order as my schema fields:
Demo:
import marshmallow_sqlalchemy as mas
class GearReviewSchema(mas.ModelSchema):
class Meta:
ordered = True
# schema fields...
gear_review_schema = GearReviewSchema()
review = db.session.query(GearReview).get(3)
gear_review_schema.dump(review)
# BUG: generates an OrderedDict, but ordering is different than schema fields
However, when I use the normal Schema, it works as expected:
import marshmallow as ma
class GearReviewSchema(ma.Schema):
class Meta:
ordered = True
# schema fields...
gear_review_schema = GearReviewSchema()
review = db.session.query(GearReview).get(3)
gear_review_schema.dump(review)
# generates an OrderedDict, with ordering same as schema fields
I first noticed this issue using Flask-Marshmallow alongside Marshmallow-SQLAlchemy, but after playing around with it, it looks like the problem is located in Marshmallow-SQLAlchemy.
The convert.py file, at line 58 says:
postgresql.JSONB: fields.Raw
But postgresql.JSONB is not supported until SQLAlchemy 0.9.7. Yet, the setup.py lists SQLAlchemy>=0.7. So, any version between 0.7 and 0.9.6 is not really supported.
Hi,
I have just a small question, is it actually possible to check unique constraint during validation ?
In my current application during the load of the json data everything is going well, and when I'm trying to persist the data I obtain an Integrity error since the unique filed value is already taken by another row.
ps: Thank you for this module that is really useful
Hi,
First off, apologies if this is documented and I've missed it. Could an example be provided of how to serialize a SQLAlchemy query result, where join has been used to return multiple Model objects?
If you use sqlalchemy 1.1 (b2 in my case) with the new JSON field type, you'll get a type conversion error:
marshmallow_sqlalchemy.exceptions.ModelConversionError: Could not find field column of type <class 'sqlalchemy.sql.sqltypes.JSON'>.
In Convert.py, I added ",
sa.JSON: fields.Raw" to the end of SQLA_TYPE_MAPPING in class ModelConverter, and that seems to do the trick (at least so far)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.