Alembic: server_default not working with python datetime

[Ahtii]

I am trying to use server_default instead of default because of the reasons mentioned in this post (second answer) but I get the following error:

sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type '<class 'str'>' or '<class 'sqlalchemy.sql.elements.ClauseElement'>' or '<class 'sqlalchemy.sql.elements.TextClause'>', got '<class 'datetime.datetime'>'

with this code:

created_date = Column(DateTime, server_default=datetime.utcnow())

[zzzeek]

hey there -

docs for server_default are at https://docs.sqlalchemy.org/en/13/core/defaults.html#server-defaults .

you can't send a Python function for server_default, you need to use a database-side function derived from the func namespace, like "func.now()".

[Ahtii]

@zzzeek but i want a utc time which is not possible with func.now() but still i tried it by replacing datetime.utcnow() with func.now() and now i get Null value in the database. Then i tried replacing server_default with default and it works there. How would i use utc with server_default ?

[zzzeek]

@zzzeek but i want a utc time which is not possible with func.now() but still i tried it by replacing datetime.utcnow() with func.now() and now i get Null value in the database.

the database table / column has to be created with this setting. if you are adding it after the fact, you'd need to run a migration that will alter the default value of the column. however it's easier if you are just in development to drop the table and recreate it with the server default function you are looking for.

Then i tried replacing server_default with default and it works there. How would i use utc with server_default ?

what database are you targeting? each database has a different syntax for this. if you are looking to create a DB-agnostic platform then you'd want to look into a recipe like https://docs.sqlalchemy.org/en/13/core/compiler.html#utc-timestamp-function

[Ahtii]

Okay i recreated the table and now server_default works thanks :)

The database I am using is MySql and i already checked that link but it doesn't mention MySql

Did the developer of sqlalchemy forgot to put the same for MySql ?

[zzzeek]

MySQL only supports "TIMESTAMP" as a datatype, so you would craete your table using the TIMESTAMP data type. The timestamp is stored in UTC. You should read https://dev.mysql.com/doc/refman/8.0/en/datetime.html and https://dba.stackexchange.com/a/24904 .

[Ahtii]

@zzzeek Just playing around with:

 from sqlalchemy.sql import expression
 from sqlalchemy.ext.compiler import compiles
 from sqlalchemy.types import DateTime

 class utcnow(expression.FunctionElement):
     type = DateTime()

 @compiles(utcnow, 'mysql')
 def my_utcnow(element, compiler, **kw):
     return "UTC_TIMESTAMP()" 

and column:

created_date = Column(TIMESTAMP, default=utcnow())

and I get utc datetime as result :)

I moved to default again from server_default as it seems I will have to recreate the table everytime to make server_default work

[Ahtii]

@zzzeek I also wanna know if I can contribute to Sqlalchemy or any open source project you have please give me one chance i will work hard.

[zzzeek]

hey there -

feel free to hang out on gitter where the devs are: https://gitter.im/sqlalchemy/devel

contribution is self-motivated so feel free to look through open issues, more in github/sqlalchemy/sqlalchemy where there should be some tagged as "easy".