0

I have a table on the Microsoft SQL Server 2014 Express and I want to interact with this table using a flask application.

I have a problem that after a COMMIT command a UPDATE is non-deterministically invoked, sometimes occurs but more often not.

Below I am attaching a minimal example, where the problem occurs

#!/usr/bin/env python3
import sys
import time

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from config import Config
# PYODBC_URI = "DRIVER={ODBC Driver 17 for SQL Server};(...others parameters...)"
# params = urllib.parse.quote_plus(PYODBC_URI)
# SQLALCHEMY_EXTERNAL_DATABASE_URI = "mssql+pyodbc:///?odbc_connect=%s" % params
# SQLALCHEMY_BINDS = {
#    'db': SQLALCHEMY_EXTERNAL_DATABASE_URI
# }

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)

class SQLRamki(db.Model):
    __tablename__ = 'sqlramki'
    __bind_key__ = 'db'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    id_ramki_zlecenia = db.Column(db.Integer)

iters = 3
while iters:
    iters = iters - 1

    a = SQLRamki.query.filter_by(id=96).first()
    print(a.brief())

    a.id_ramki_zlecenia = 100000 - iters
    print(db.session.dirty)

    time.sleep(2)
    db.session.commit()

After the first call, I am getting

<SQLRamki id: 96 id_ramki_zlecenia: 89600>
IdentitySet([])
<SQLRamki id: 96 id_ramki_zlecenia: 99998>
IdentitySet([])
<SQLRamki id: 96 id_ramki_zlecenia: 99999>
IdentitySet([])

and if I invoke this script again, I am getting the same output

<SQLRamki id: 96 id_ramki_zlecenia: 89600>
IdentitySet([])
<SQLRamki id: 96 id_ramki_zlecenia: 99998>
IdentitySet([])
<SQLRamki id: 96 id_ramki_zlecenia: 99999>
IdentitySet([])

so, there wasn't invoked UPDATE on the DB (I had checked by setting a SQLALCHEMY_ECHO to True)

What am I doing wrong?

abrzozowski
  • 389
  • 1
  • 3
  • 12
  • You're sure you have update privilege for this database? – Tim Roberts Jan 31 '22 at 21:50
  • 1
    If `session.dirty` isn't being set then the object is not being tracked by the session. Are you sure you actually add the object to the session? – snakecharmerb Feb 01 '22 at 07:30
  • @TimRoberts I think so, due to i can add rows to the db @snakecharmerb I'm not sure. But probably not, because if I invoke `db.session.add(a)` below `a = SQLRamki.query.fil...` i'm getting `is already attached to session '1' (this is '2')` thanks for pointing that me out. Can I force a flask to work only in one session or how to properly manage sessions? ... probably I should dive into documentation – abrzozowski Feb 01 '22 at 09:52
  • You can call `session.expunge(obj)` to remove it from a session and then `session.add(obj)` to add it to another. But the question is probably _why_ you are dealing with two sessions? – snakecharmerb Feb 01 '22 at 11:17
  • Yes, I wonder why and when is creating a new session. At this moment I have a workaround, thanks, changing `SQLRamki.query.filter_by(id=96).first()` to `db.session.query(SQLRamki).filter_by(id=96).first()` which works like i expect – abrzozowski Feb 01 '22 at 12:52
  • I've found a similar problem https://stackoverflow.com/questions/21803278 and https://stackoverflow.com/questions/40020388/flask-sqlalchemy-db-session-querymodel-vs-model-query – abrzozowski Feb 01 '22 at 12:59

0 Answers0