2

I am using SQLalchemy with flask to interact with sql server. I have enabled system versioning for the tables. On executing first query to insert or update the table after the app runs , sysstarttime gets updated, but in next subsequent insert or update operation the sysstarttime doesn't update .. it is still showing the older transaction time meanwhile if some other running flask app tries to update the rows it throws an error "data modification failed for the system versioned table"

query that i have used for creating tables:

CREATE TABLE dbo.table(
    ScanSubscriptionID NOT NULL PRIMARY KEY,
    UserName varchar(20) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.table));

Here's the code of my app:

import urllib
from flask import Flask
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
connection_string =\
    urllib.parse.quote_plus('Driver={ODBC Driver 17 for SQL Server};Server=localhost\MSSQLSERVER01;Database=db_Data;Trusted_Connection=yes;')
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string
app.config['SQLALCHEMY_DATABASE_URI'] = connection_string
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
session_option = {
    'autocommit': True
}
db = SQLAlchemy(app, session_options=session_option)
db.init_app(app)
session1 = db.session
@app.route('/')
def testdb():
    try:
        session1.begin()
        session1.execute("update [table] set UserName = 'Ksing' where ScanSubscriptionID = 1600336614")
        session1.commit()
        return '<h1>It works.</h1>'
    except Exception as e:
        raise e
    finally:
        print(f'Time is  :{datetime.utcnow()}')
        #session1.close()


if __name__ == '__main__':
    app.run(debug=True)
Nikolay Shebanov
  • 1,363
  • 19
  • 33
kanwar singh
  • 21
  • 2
  • Hey Kanwar, it seems you have forgotten to add sample code, could you update your question? Without the code, my first guess would be that you provided a default column value as `datetime.now()` instead of `datetime.now` or `func.now`. In that case, the column value gets memorized on the first run of the code. – Nikolay Shebanov Nov 10 '20 at 11:14
  • Hey Nick placed the code on github:https://github.com/kanwar28/testrepo/blob/master/app2.py – kanwar singh Nov 10 '20 at 11:44
  • 1
    Hey Nick, have not used datetime.now() option for creating the columns. As the table is system versioned, so SysStartTime and SysEndTime columns are the one which gets updated on each CURD operation. – kanwar singh Nov 10 '20 at 11:48
  • Great, thanks for updating the question! Have you looked into the discussion here: https://stackoverflow.com/questions/40672573/ms-sql-temporal-table-update-failure? – Nikolay Shebanov Nov 10 '20 at 11:54
  • Yeah Nick have gone through that link as well, even i thought that the issue is with concurrency but the same logic works fine on using PYODBC driver. With sqlalchemy first transaction update the rows perfectly but on subsequent transaction the value updated in SysStartTime column is the value of the previous transaction. – kanwar singh Nov 10 '20 at 12:00
  • 1
    Lets say,first transaction at : 10.02.30 and time updated is 10.02.30, Second transaction at 10.03.40 and time updated is 10.02.30,Third transaction at 10.04.08 and time updated is 10.03.40. Meanwhile if some other app tries to update the same in between 10.02.30-10.03.40 throws data modification error. – kanwar singh Nov 10 '20 at 12:08

0 Answers0