How to insert into a system versioned table with sqlalchemy?-CodePudding

I'm getting classes for the tables in the DB as follows:

import sqlalchemy as sa
import sqlalchemy.ext.automap

eng = sa.create_engine(CONNECTION_URL)
Base = sa.ext.automap.automap_base()
Base.prepare(eng, reflect=True)
Session = sa.orm.sessionmaker(bind=eng)

Table1 = Base.classes.Table1

In my case Table1 is system versioned which I understand sqlalchemy doesn't explicitly support.

When running the following code:

t = Table1(field1=1, field2=3)
with Session() as session:
    session.add(t)
    session.commit()

I get the following error:

[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value into a GENERATED ALWAYS column in table 'DBName.dbo.Table1'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column. (13536) (SQLExecDirectW);

I understand this probably has to do with the ValidTo and ValidFrom columns

Table1.__table__.columns.keys()
# Column('ValidFrom', DATETIME2(), table=<Table1>, nullable=False)
# Column('ValidTo', DATETIME2(), table=<Table1>, nullable=False)

How do I tell sqlalchemy to ignore those columns during the insert statement?

EDIT

I'm guessing the below is the relevant part of the create statement?

CREATE TABLE [dbo].[Table1]
    [TableID] [int] NOT NULL IDENTITY,
    ...
    [ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL

CodePudding user response：

I've got this code below working using a sqlalchemy.

CREATE TABLE dbo.Customer2
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START 
               NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END
              NOT NULL ,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CustomerHistory2))

If the StartTime / EndTime columns are hidden (which these arent't) then a value isn't needed in the insert statement you can add just the required. However the date columns in my table are required, so using default.

sql = "INSERT INTO dbo.Customer2 VALUES (2,'Someone else', default,default)"

print(sql)

with engine.connect() as con:
    rs = con.execute(sql)