CODEWITHSUNDEEP
pythonsql-serversqlalchemy
rachekalmir
rachekalmir

Reputation: 614

Sqlalchemy Does Not Emit Correct SQL for MSSQL GETDATE() server default

I have the following declarative SQL class:

class Test(Base):
    ID = Column(Integer, primary_key=True)
    SYSTEM_TIMESTAMP = Column(DateTime, nullable=False, server_default='GETDATE()')

But using Base.metadata.create_all(engine) causes the following SQL to be emitted for the SYSTEM_TIMESTAMP:

[SYSTEM_TIMESTAMP] DATETIME NOT NULL DEFAULT 'GETDATE()',

An example for how GETDATE should be emitted from http://www.w3schools.com/sql/func_getdate.asp:

OrderDate datetime NOT NULL DEFAULT GETDATE()

I have tried using func.getdate instead of 'GETDATE()' as the server_default, but get the following error:

ArgumentError: Argument 'arg' is expected to be one of type '<type 'basestring'>' or
 '<class 'sqlalchemy.sql.expression.  ClauseElement'>' or
 '<class 'sqlalchemy.sql.expression.TextClause'>',
  got '<class 'sqlalchemy.sql.expression._FunctionGenerator'>'

SQLAlchemy version: 0.9.6 Pyodbc version: 3.0.5 Python version: 2.7

How do I get SQLAlchemy to emit to correct SQL to set the server default?

Upvotes: 2

Views: 1020

Answers (1)

rachekalmir
rachekalmir

Reputation: 614

The solution is to define the server default using the text() function of SQLAlchemy:

import sqlalchemy as sa
...
SYSTEM_TIMESTAMP = Column(DateTime, nullable=False, server_default=sa.text("GETDATE()"))

This will then correctly emit the server default:

[SYSTEM_TIMESTAMP] DATETIME NOT NULL DEFAULT GETDATE(),

This is documented here: http://docs.sqlalchemy.org/en/latest/core/defaults.html#server-side-defaults

Upvotes: 5

Related Questions