SQLAlchemy Column default not working with python functions

[AT-SyT]

Hello,

I have the following problem using eveSqlAlchemy 0.3.4 with eve 0.5.3:

I specified a python function (get_user_id) as default value for a column in my Database to fill in the current User ID when I insert the row. The problem that occurs now is that when I POST a new entry to my endpoint it raises the following error:

'TypeError: <function get_user_id at 0x1069ae5f0> is not JSON serializable'

From debugging the program I found out that the error occurs when eve is trying to generate the ETAG for the row.

Now I wanted to ask if there is any fix or workaround for this problem. I thought about specifying the default function in the eve Schema but I found no way to use a callable as default value, or is this somehow possible?

Best regards from Austria

Sebastian

[mandarvaze]

From the error, it doesn't look like it is related to "default" function, as you indicated in the title. Are your user IDs, of type UUID ? (Cause that can lead to 'not JSON serializable' error) Can you provide relevant code/model definition here ?

[AT-SyT]

No my userIDs are just normal integers for now. I think the problem is that eve doesn't call the given default function but tries to serialize the function itself which is not serializable.

In my model I have the following:

creator_id = Column(Integer, ForeignKey('user.id'), default=get_user_id)

The get_user_id looks like the following:

from flask.ext.login import current_user

def get_user_id():
    return current_user.id

[dkellner]

I think for now the best way to address this would be using events: http://python-eve.org/features.html#event-hooks.

Since Cerberus 1.0 supports a default_setter rule, maybe this will be another way to tackle use-cases like this in the future.

[hectorcanto]

I'm using a default function and having the same issue with several types of fields, not only UUIDs but also DateTime or String.

I'm using Eve==0.7.10, Eve-SQLAlchemy==0.7.0 and SQLAlchemy==1.2.13

I believe the problem is not the type at all, I've extended the BaseSerializer to accept UUID and DateTime among others.

from eve import Eve
from eve.io.base import BaseJSONEncoder
from eve_sqlalchemy import SQL, validation
from sqlalchemy.dialects.postgresql import UUID

class CustomJSONEncoder(BaseJSONEncoder):
    def default(self, obj):
        if isinstance(obj, UUID):
            return str(obj)
        elif isinstance(obj, datetime):
                return obj.timestamp()
        return super(CustomJSONEncoder, self).default(obj)

app = Eve(settings=settings, json_encoder=CustomJSONEncoder,
              validator=validation.ValidatorSQL, data=SQL)

The problem is that the default function defined in the model is arriving to the serializer unresolved, as the error says:

TypeError: Object of type function is not JSON serializable

I think eve-sqlalchemy is getting in the way of sqlalchemy somehow.

Curiously enough, I defined the typical Eve _created default's to default=datetime.datetime.utcnow and it is resolving properly.

I will be very thankful to have any direction on where the issue comes from, so I could investigate more and provide a better analysis or even a solution.

[dkellner]

@hectorcanto Any help is highly appreciated!

At first you could write a new integration test for using the SQLAlchemy default function - maybe even for different types of fields. Since this issue was opened we've included some integration tests already and they should provide a good starting point. This would both provide a minimal example and a way to prevent this bug from occuring again in the future.

For the fix I'd look at _get_field_default in ColumnFieldConfig first.

[iamleson98]

omg, is your problem solved ? i'm facing a similar problem the param default in DateTime field of sqlalchemy does not work for me i tried adding this in__init__.py file:

from sqlalchemy import func
from datetime import datetime

func.systime = lambda: str(datetime.utcnow())

and then in model definition:

from sqlalchemy import func, Column

class Table(Base):
      __tablename__: str = 'users'
      username = Column(String(50), nullable=False)
      created_at = Column(Datetime, nullable=False, server_default=func.systime())

but it does not work for me still.

[mandarvaze]

@leminhson2398 You are not supposed to call the function using () - See the documentation

Try the following instead :

created_at = Column(Datetime, nullable=False, server_default=func.systime)

Notice () not present after func.systime

[iamleson98]

@mandarvaze . Oh, server_default requires param value has to be string type. I changed the code to func.systime and it does not work

[mandarvaze]

@leminhson2398 You are correct. The URL I gave earlier was for default not server_default. My mistake.

Can you try func.now() - which is a built-in function, instead of defining your own func.systime ?

There are also func.localtime() and func.current_timestamp()

[iamleson98]

Mr @mandarvaze, my postgresql version is 11 my sqlalchemy is 1.3.12 i tried running this script:

from sqlalchemy import *
from datetime import datetime

DB_URL: str = 'postgresql://postgres:anhyeuem98@localhost/a_db'

engine = create_engine(DB_URL)

metadata = MetaData()

notes = Table(
    'notes',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('text', String, nullable=False),
    Column('created_at', DateTime(timezone=True), default=datetime.utcnow),
    Column('updated_at', DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)
)

if not metadata.is_bound():
    metadata.create_all(engine)

and this is sql code generated:

-- Table: public.notes

-- DROP TABLE public.notes;

CREATE TABLE public.notes
(
    id integer NOT NULL DEFAULT nextval('notes_id_seq'::regclass),
    text character varying COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    CONSTRAINT notes_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.notes
    OWNER to postgres;

as you can see, the created_at, updated_at fields still has no DEFAULT constraint

[iamleson98]

mr @mandarvaze , just let you to know that i fixed it just do it like this:

from sqlalchemy import TIMESTAMP, func, ...

Column('created_at', TIMESTAMP(timezone=True), server_default=func.now())

That's it