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

[mgarfias]

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.

[amleczko]

Can you check your settings against newest 0.3 version of eve-sqlalchemy?

[mgarfias]

$ pip freeze | grep Eve-SQL Eve-SQLAlchemy==0.3

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

(body_id has changed due to auto_increments/testing other stuff).

[blint587]

I also encountered the same issue. I was not able to POST a new document when there was a foreign key constrain in the Table definition, without no problem.

[vlasy]

This works perfectly for me - the resource is created. Is this still a problem?

[dkellner]

@vlasy Hopefully not. Which version of Eve SQLAlchemy did you try?

[ralphsmith80]

I haven't tried POSTing recently, but it worked on my forked repo. Forked from version 0.4.2.dev0. However, I did have to add support for polymorphic inserts.

[vlasy]

@dkellner Eve-SQLAlchemy==0.4.2.dev0

[dkellner]

OK, so I will close this as this should be fixed by now.