0

I am using SQLAlchemy (1.2.5), pyodbc(4.0.22). I have created a table using python / SQLAlchemy as follows:

class Persons(Base):
    __tablename__ = 'Persons'
    ID_Person = Column(Integer(),primary_key = True)
    Affiliate_Name = Column(VARCHAR(200), unique=True, nullable = False) 
    time_created = Column(DateTime(timezone=True), server_default=func.now())
    time_updated = Column(DateTime(timezone=True), server_onupdate=func.now())

I can then go into MS SQLServer (2014) and see the table created.

I execute the following commands in SQL Server:

USE [TestDB]

INSERT INTO [dbo].[Persons] (Affiliate_Name) values ('RANDOM NAME')

UPDATE [dbo].[Persons] 
SET Affiliate_Name = 'RANDOM NAME2'
WHERE Affiliate_Name = 'RANDOM NAME'

When I look at the data, the time_updated column is still NULL.

EDIT:

I changed my table definition to the following:

class Persons(Base):
    __tablename__ = 'Persons'
    ID_Person = Column(Integer(),primary_key = True)
    Affiliate_Name = Column(VARCHAR(200), unique=True, nullable = False) 
    time_created = Column(DateTime(timezone=True), server_default=func.now())
    time_updated = Column(DateTime(timezone=True), onupdate=datetime.now)

Now, the time_updated column is updated when SQLAlchemy updates data, but will need a trigger to change within MSSQL

chris dorn
  • 817
  • 8
  • 13
  • 1
    Who created the table? Was it you or was it SQLAlchemy? – DavidG Mar 23 '18 at 14:02
  • @DavidG SQLAlchemy – chris dorn Mar 23 '18 at 14:04
  • 1
    Does SQL Server support a form of `ON UPDATE` or something? A quick glance at the output of a `CreateTable` compiled for mssql dialect simply omits the server on update. The documentation hints that the primary use case of `server_onupdate` is to signal to SQLAlchemy, that some trigger or some such creates a server generated value. This seems somewhat related: https://bitbucket.org/zzzeek/sqlalchemy/issues/3444/sqlalchemy-does-not-emit-server_onupdate – Ilja Everilä Mar 23 '18 at 14:12
  • 1
    It'd seem mssql does not have such feature out of the box: https://stackoverflow.com/questions/21493178/need-a-datetime-field-in-ms-sql-that-automatically-updates-when-the-record-is-mo. You'll have to write a trigger and replace your column definition with `Column(..., server_onupdate=FetchedValue())`: http://docs.sqlalchemy.org/en/latest/core/defaults.html#triggered-columns – Ilja Everilä Mar 23 '18 at 14:21
  • 1
    Can you post the CREATE TABLE statement? the behavior suggests that no DEFAULT constraint is set for the time columns. – user1443098 Mar 23 '18 at 14:22
  • @IljaEverilä You are correct. Changing the time_updated Column definition to onupdate=datetime.now meant that SQLAlchemy would update the time_updated column upon insert. Thanks for the clarification. If you want to add an answer, I can accept that. – chris dorn Mar 23 '18 at 14:28

0 Answers0