Inserting into table with ForeignKey

[duane-dibbley]

Hello! I have a problem which is probably trivial but I cannot figure out what is troubling me. I am trying to insert some data into table which has defined foreign key to some other table:

class Users(CommonColumns):
    __tablename__ = 'vb_users'
    __table_args__ = {'schema': 'test'}

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_name = Column(Text, nullable=False, unique=True)
    api_key = Column(Text, nullable=False, unique=True)
    api_secret = Column(Text, nullable=False, unique=True)

class DataIn(CommonColumns):
    __tablename__ = 'vb_data_in'
    __table_args__ = {'schema': 'test'}

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_id = Column(ForeignKey('test.vb_users.id', ondelete='RESTRICT',
                                onupdate='CASCADE'), nullable=False)
    user_data = Column(JSON, nullable=False)
    request_session = Column(Text)

    user = relationship(Users)

Relevant part of my settings.py:

ID_FIELD = 'id'
ITEM_LOOKUP_FIELD = ID_FIELD
config.ID_FIELD = ID_FIELD
config.ITEM_LOOKUP_FIELD = ID_FIELD

# Need to be called *after* setting config.ID_FIELD
registerSchema('users')(Users)
registerSchema('datain')(DataIn)

DOMAIN = {
    'users': Users._eve_schema['users'],
    'datain': DataIn._eve_schema['datain'],
}

DOMAIN['users'].update({
    'description': 'Get user info.',
    'id_field': 'id',
    'item_lookup_field': 'id',
})

DOMAIN['datain'].update({
    'description': 'Endpoint for inserting data.',
    'id_field': 'id',
    'item_lookup_field': 'id',
    'resource_methods': ['POST'],
})

I am also using HMAC authentication class where I select user from database and return True if everything went fine.

When I try to insert something in my DataIn table, I get this error:

{
  "_error": {
    "code": 422,
    "message": "Insertion failure: 1 document(s) contain(s) error(s)"
  },
  "_issues": {
    "user": "required field"
  },
  "_status": "ERR"
}

This is not the only problem I have. There is also column request_session which should be inserted after some calculations in my code, not by user data.

My questions are:

• how to properly handle this simple relation? Should I assign Users object to Flask app after successful authentication or something similar?
• how can I insert request_session column, which should be calculated after each request? Can I also somehow assign it to my app so that Eve-SQLAlchemy becomes aware of it?

Thanks a lot for all your effort with this library!!! ;)

[dkellner]

I don't have experience with this kind of authentication scheme, but the error message you're getting is because your payload is missing the user id. I guess it is related to http://python-eve.org/authentication.html#user-restricted-resource-access .

Btw, which version of Eve-SQLAlchemy are you using? 0.4.1 or the current master branch?

[duane-dibbley]

@dkellner Thank you very very much for helping, I wasn't aware of set_request_auth_value() method... I got some project, almost a year old and this was never implemented there.

To answer your question, I am using Eve-SQLAlchemy from master branch, so it is 0.5.0.dev0.

And finally, just to clarify one more thing... I have a column (called request_session) which should carry the value assigned after each request is initiated. This is the code I have in my Flask blueprint which is creating that value:

@app_requests.before_app_request
def before():
    app.request_session = uuid.uuid4().hex
    # rest of the code...

How to associate this value to some models which should store the value? Example table:

class DataIn(CommonColumns):
    __tablename__ = 'vb_data_in'
    __table_args__ = {'schema': 'test'}

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_id = Column(ForeignKey('test.vb_users.id', ondelete='RESTRICT',
                                onupdate='CASCADE'), nullable=False)
    user_data = Column(JSON, nullable=False)
    request_session = Column(Text)  # <----- how to tell this column to use app.request_session value?

    user = relationship(Users)

Thank you one more time and keep up the good work!!! ;)

[dkellner]

I did not have time yet to try it myself, but here are two ideas to play around with:

1. Maybe you should use the g object for this (see Flask docs on application context).
2. Using default setters (see Cerberus docs on default values) with a function like this should work:

   def get_request_session():
      from flask import g
      return getattr(g, 'request_session', None)

[duane-dibbley]

@dkellner Thanks a lot for all the help and tips, it is extremely appreciated! Closing this now and going back to coding ;)

[duane-dibbley]

Uh, I'm really sorry for reopening this, but I am still a bit troubled with understanding my problems :(

So, let's say I have tables like this:

class Users(CommonColumns):
    __tablename__ = 'vb_users'
    __table_args__ = {'schema': 'test'}

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_name = Column(Text, nullable=False, unique=True)
    api_key = Column(Text, nullable=False, unique=True)
    api_secret = Column(Text, nullable=False, unique=True)

class DataIn(CommonColumns):
    __tablename__ = 'vb_data_in'
    __table_args__ = {'schema': 'test'}

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    user_id = Column(ForeignKey('test.vb_users.id', ondelete='RESTRICT',
                                onupdate='CASCADE'), nullable=False)
    user_data = Column(JSON, nullable=False)
    request_session = Column(Text)

    # user = relationship(Users)

and I set

AUTH_FIELD = 'user_id'

in my Eve settings, along with auth value in my authentication method:

# fetch user from database and perform some checks
user = app.data.driver.session.query(Users).filter(Users.api_key == userid).one()
# set request auth value
self.set_request_auth_value(user.id)  # user is an sqlalchemy class

At this point, everything will work fine - user_id will be correctly inserted into DataIn table. However, if I uncomment user = relationship(Users) line in my models and try to insert something, I will get 422 with a description "user": "required field". So, after a bit of digging through both Eve and Eve-SQLAlchemy documentation, I came up with conclusion that I should probably extend my schema, something like:

DOMAIN['datain']['schema']['user']['data_relation']['resource'] = 'users'

Is this the proper approach/setup?

And regarding my other problem (column with some "arbitrary" value), @dkellner I tried to follow your instructions and read about it but I still don't know how to assign this value to my column in the end. I tried with extending my schema again:

DOMAIN['datain']['schema']['request_session']['default']= get_request_session()

and defining the function which should get the value:

from flask import current_app as app
from flask import g

@app.after_request
def get_request_session():
    return getattr(g, 'request_session', None)

but I'm getting Flask RuntimeError: working outside of application context issue :( I know this is a strictly Flask-related problem, but I just cannot figure out how to properly put this function in "context" (in my blueprint or anywhere else in my code) so I can use it in my schema.

I apologize one more time for bugging you with these basics, but I'm fairly new in this whole Flask/SQLAlchemy/Eve world, so I hope you will understand my troubles :)

Thanks for all the tips in advance!

[dkellner]

Don't worry about reopening. Could just happen that people are busy some days and you'll have to wait for an answer ;).

Your first problem occurs because we hide the foreign key in the api if a relationship is defined. The reason is to remain consistency with the way Eve itself handles related documents and embedding. Try setting the AUTH_FIELD to 'user', maybe that resolves your problem already.

For the second problem I thought of using default setters. Something along these lines:

DOMAIN['datain']['schema']['request_session']['default_setter'] = get_request_session

[duane-dibbley]

@dkellner I perfectly understand that people are not waiting for me only and my silly questions, so no matter when the answers appear I am happy :) I tried to set my AUTH_FIELD to user but I still got 422, so I followed the example of extending schema with data_relation and it seems that it worked. I just wonder if that is a good way to go?

About the other problem, I tried with default_setter also but the problem is again the Flask working outside of application context, no matter where do I define my get_request_session function :/ Going to read a bit more about Flask application context, maybe it will finally click in my head :)

[dkellner]

I wonder... what is the value of DOMAIN['datain']['schema']['user']['data_relation']['resource'] before your modification?

[dkellner]

OK, using default_setter will not work, as this was introduced in Cerberus 1.0 und we're currently still using 0.9.2 - my bad :).

It should work using Eve's event hooks:

def pre_datain_post_request(request):
    g.request_session = 'fancy calculation'

def before_insert_datain(items):
    for item in items:
        item['request_session'] = g.get('request_session')

app.on_pre_POST_datain += pre_datain_post_request
app.on_insert_datain += before_insert_datain

[duane-dibbley]

@dkellner I didn't have anything defined in DOMAIN['datain']['schema']['user']['data_relation']['resource'] before I "manually" set it to user, I thought that just defining relationships with SQLAlchemy classes would result in Eve-SQLAlchemy figuring out the schema :)

I've been reading about my other problem and event hook was one of the things that I thought might solve my problems. Trying to implement it and following your tips resulted in what I expected to see :)

Thank you one more time for all the effort and explanations, it was a huge help!!!

[bomb-on]

This might be a good opportunity to update examples in docs, explaining how to handle relations, isn't it so @dkellner? :)

[dkellner]

@bomb-on Agree - and actually I've prepared one example per basic relationship pattern in SQLAlchemy already (http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html). It will still need some time to land though, as it's kind of connected to a bigger rewrite I did in the way we generate the configuration from the SQLAlchemy model structure (to solve several of our current issues).

@duane-dibbley Yes actually it should figure out that by itself (and it did for the minimal example i've created for your code snippets above). Can you provide a minimal failing example in this case?

[bomb-on]

@dkellner Ah, alright then... I was thinking to offer my help regarding the example(s) but since you have it under control, I'll leave it to you :)