[sqlalchemy] help writing a query with conditions based on two tables/models using ORM

[Corey Boyle]

I have something like the following...

class TimestampMixin(object):
created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
modified_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, 
onupdate=datetime.datetime.utcnow)


class Memo(db.Model, TimestampMixin):
__tablename__ = "memos"
pk = db.Column(db.Integer, primary_key=True)
note = db.Column(db.Text)
date = db.Column(db.Date)
purpose = db.Column(db.Text)
product = db.Column(db.Text)
location = db.Column(db.Text)
private = db.Column(db.Boolean, default=False, nullable=True)
in_person = db.Column(db.Boolean, default=False, nullable=True)
rr = db.Column(db.Boolean, default=False, nullable=True)

customer_pk = db.Column(db.Integer, db.ForeignKey("customers.pk"))
author_pk = db.Column(db.Integer, db.ForeignKey("users.pk"))
contact_pk = db.Column(db.Integer, db.ForeignKey("contacts.pk"))
r_by_pk = db.Column(db.Integer, db.ForeignKey("users.pk"))

recipients = db.relationship("MemoRecipient", backref="memo", 
cascade="save-update, 
merge, delete",)
comments = db.relationship("MemoComment", backref="memo", 
cascade="save-update, 
merge, delete",)
reminders = db.relationship("MemoReminder", backref="memo", 
cascade="save-update, 
merge, delete",)


class MemoRecipient(db.Model):
__tablename__ = "memorecipients"
pk = db.Column(db.Integer, primary_key=True)

memo_pk = db.Column(db.Integer, db.ForeignKey("memos.pk"))
user_pk = db.Column(db.Integer, db.ForeignKey("users.pk"))



Whenever a Memo is created, it has an author and optionally gets tagged 
with other users using the MemoRecipient table.

I am trying to figure out how to write a query that will give me all the 
Memos that were authored by (Memo.author_pk) or sent to 
(MemoRecipient.user_pk) a specific user. I also need to order the results 
by Memo.modified_at and paginate the results. I'm thinking I need some type 
of JOIN, I'm just not sure which.

Any advice?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e46936a6-84e7-43df-bbf8-8d86385ed8b7o%40googlegroups.com.

[sqlalchemy] Re: Am I doing this query of lots of columns correctly?

['Jonathan Vanasco' via sqlalchemy]

that should be `loaded_columns_as_dict()` , unless you decorate the method 
with @property.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b912b1d6-8f17-4232-8b2b-1cebe8c90d9do%40googlegroups.com.

[sqlalchemy] Re: Am I doing this query of lots of columns correctly?

['Jonathan Vanasco' via sqlalchemy]

I use a mixin class to handle this stuff. Example below.

> So, my question: is it generally better practice to name every column 
that you want to pull, even if it's a long list? 
Not really.  There is a "bundle" api here that might be better for you- 
https://docs.sqlalchemy.org/en/13/orm/loading_columns.html

> Also, why does using just RecoveryLogEntries instead of naming each 
column yield a different result?
One is querying a "table", the other is querying a list of items. The 
queried items could be a "table", "table column", "subquery column" or 
several other things.

> It seems weird because in the SQL world, I could do a "SELECT *" or 
"SELECT id, ..." and the output is still in the same format regardless of 
whether I explicitly name name each column or use * to select all columns.
A lot of people approach SqlAclhemy like they are writing SQL. IMHO, a 
better approach is to remember that SqlAlchemy lets you write Python that 
generates Sql for multiple dialects.

> It just seems like it's a whole bunch of typing which could be 
error-prone.  I'll do it if I need to, but what I'm really asking is what 
is the most appropriate/accepted/standard way to do this.

The more standard ways are to expect/inspect the types of objects that are 
queried and act upon it.  The results are not text, but objects. If you 
inspect them, you can pull out the relevant information.

anyways, using a generic mixin (far below), I use the following code.  I 
also sometimes have methods on my objects to return json that only has 
specific fields (such as `as_json_v1`, `as_json_v2`, etc)


class RecoveryLogEntries(Base, UtilityObjectMixin):
# ... your class ...

# then...

results = session.query(RecoveryLogEntries).limit(record_count)
as_json = [r.loaded_columns_as_dict for r in results]



class UtilityObjectMixin(object):
 """see 
https://github.com/jvanasco/pyramid_sqlassist/blob/master/pyramid_sqlassist/objects.py#L127-L165"";


def columns_as_dict(self):
"""
Beware- this function will trigger a load of attributes if they 
have not been loaded yet.
"""
return dict((col.name, getattr(self, col.name))
for col
in sa_class_mapper(self.__class__).mapped_table.c
)


def loaded_columns_as_dict(self):
"""
This function will only return the loaded columns as a dict.
See Also: ``loaded_columns_as_list``
"""
_dict = self.__dict__
return {col.name: _dict[col.name]
for col in sa_class_mapper(self.__class__).mapped_table.c
if col.name in _dict
}


def loaded_columns_as_list(self, with_values=False):
"""
This function will only return the loaded columns as a list.
By default this returns a list of the keys(columns) only.
Passing in the argument `with_values=True` will return a list of 
key(column)/value tuples, which could be blessed into a dict.
See Also: ``loaded_columns_as_dict``
"""
_dict = self.__dict__
if with_values:
return [(col.name, _dict[col.name], )
for col in sa_class_mapper(self.__class__).mapped_table.
c
if col.name in _dict
]
return [col.name
for col in sa_class_mapper(self.__class__).mapped_table.c
if col.name in _dict
]

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/874a7299-9fa9-4be9-bb54-3aa186180269o%40googlegroups.com.

Re: [sqlalchemy] MSSQL with PyODBC: StaleDataError on UPDATE statement

[Mike Bayer]

triggers, ah. Maybe? that seems inconvenient. it would be good if you could 
adjust the triggers to not affect the rowcount of the original statement, 
otherwise you'd have to set supports_sane_rowcount=False on your engine:

engine.dialect.supports_sane_rowcount =False


On Fri, Jun 26, 2020, at 12:49 PM, jue...@gmail.com wrote:
> I think ID is the only primary key of the Measurements table, but I can 
> double check this on Monday. I used sqlacodegen to create the initial models, 
> so if this tool correctly detects primary keys, the Measurement model should 
> match the table definition.
> 
> I also tried to execute the follwing statement using engine.execute:
> 
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
> 
> The result also indicated that 5051 rows where affected, but I don't get it 
> why... There is clearly only one Measurement with ID 6248... I know that 
> certain triggers are executed if IsCompleted is changed, so maybe this has an 
> effect on the reported rows?
> 
> Mike Bayer schrieb am Freitag, 26. Juni 2020 um 18:40:11 UTC+2:
>> __
>> hi -
>> 
>> does your Measurement table have a primary key constraint present, and does 
>> this primary key constraint consist of exactly the "ID" column only and no 
>> other columns? it would appear you have not mapped the primary key correctly.
>> 
>> 
>> 
>> On Fri, Jun 26, 2020, at 9:57 AM, jue...@gmail.com wrote:
>>> I'm currently working with sqlalchemy (Version 1.3.18) to access a 
>>> Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc (Version 
>>> 4.0.30)
>>> 
>>> When I try to update a single object and try to update and commit the 
>>> object, I run into a StaleDataError:
>>> 
>>> StaleDataError: UPDATE statement on table 'Measurement' expected to update 
>>> 1 row(s); 5051 were matched. 
>>> 
>>> The update statement looks perfectly fine:
>>> 
>>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
>>> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
>>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
>>> 
>>> Any Idea what's wrong with this.
>>> 

>>> --
>>> SQLAlchemy - 
>>> The Python SQL Toolkit and Object Relational Mapper
>>> 
>>> http://www.sqlalchemy.org/
>>> 
>>> To post example code, please provide an MCVE: Minimal, Complete, and 
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>>> description.
>>> --- 
>>> You received this message because you are subscribed to the Google Groups 
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an 
>>> email to sqlalchemy+...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/d607c30c-2bc5-4680-9e7f-842da8f28792n%40googlegroups.com
>>>  
>>> .
>> 
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/811cc2da-18fc-4679-914f-e7d672c4bdcfn%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/98db1064-e207-4cfb-bd12-81b264016b4c%40www.fastmail.com.

Re: [sqlalchemy] MSSQL with PyODBC: StaleDataError on UPDATE statement

[jue...@gmail.com]

I think ID is the only primary key of the Measurements table, but I can 
double check this on Monday. I used sqlacodegen to create the initial 
models, so if this tool correctly detects primary keys, the Measurement 
model should match the table definition.

I also tried to execute the follwing statement using engine.execute:

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)

The result also indicated that 5051 rows where affected, but I don't get it 
why... There is clearly only one Measurement with ID 6248... I know that 
certain triggers are executed if IsCompleted is changed, so maybe this has 
an effect on the reported rows?


Mike Bayer schrieb am Freitag, 26. Juni 2020 um 18:40:11 UTC+2:

> hi -
>
> does your Measurement table have a primary key constraint present, and 
> does this primary key constraint consist of exactly the "ID" column only 
> and no other columns?  it would appear you have not mapped the primary key 
> correctly.
>
>
>
> On Fri, Jun 26, 2020, at 9:57 AM, jue...@gmail.com wrote:
>
> I'm currently working with sqlalchemy (Version 1.3.18) to access a 
> Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc 
> (Version 4.0.30)
>
> When I try to update a single object and try to update and commit the 
> object, I run into a StaleDataError:
>
> StaleDataError: UPDATE statement on table 'Measurement' expected to update 
> 1 row(s); 5051 were matched. 
>
> The update statement looks perfectly fine:
>
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
>
> Any Idea what's wrong with this.
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d607c30c-2bc5-4680-9e7f-842da8f28792n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/811cc2da-18fc-4679-914f-e7d672c4bdcfn%40googlegroups.com.

Re: [sqlalchemy] Re: MSSQL with PyODBC: StaleDataError on UPDATE statement

[Mike Bayer]

yeah that is just disabling the check, the "5000 rows matched" is a critical 
malfunction referring to non-working primary key.

On Fri, Jun 26, 2020, at 10:39 AM, jue...@gmail.com wrote:
> Strangely, if I add the following mapper_args, it is working with a warning 
> (SAWarning: Dialect mssql+pyodbc does not support updated rowcount - 
> versioning cannot be verified.) but not with an exception.
> 
>  __mapper_args__ = {
>  'version_id_col': Created,
>  'version_id_generator': False,
>  }
> 
> jue...@gmail.com schrieb am Freitag, 26. Juni 2020 um 16:01:07 UTC+2:
>> Here is the code I used (simplified):
>> 
>> class Measurement(Base):
>>  __tablename__ = 'Measurement'
>>  __table_args__ = (
>>  {'implicit_returning': False}, # Required because of some triggers on this 
>> table
>>  )
>> 
>>  ID = Column(Integer, primary_key=True)
>>  Created = Column(DATETIME2, nullable=False, default=datetime.datetime.now)
>>  IsCompleted = Column(BIT)
>>  Completed = Column(DATETIME2)
>> 
>> measurement = Measurement(Part=motor_unit, 
>> TestProcedureVersion=test_procedure)
>> session.add(measurement)
>> session.commit()
>> 
>> # And Later:
>> measurement.IsCompleted = True
>> session.commit() # --> This raises the StaleData Exception
>> 
>> 
>> 
>> jue...@gmail.com schrieb am Freitag, 26. Juni 2020 um 15:57:32 UTC+2:
>>> I'm currently working with sqlalchemy (Version 1.3.18) to access a 
>>> Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc (Version 
>>> 4.0.30)
>>> 
>>> When I try to update a single object and try to update and commit the 
>>> object, I run into a StaleDataError:
>>> 
>>> StaleDataError: UPDATE statement on table 'Measurement' expected to update 
>>> 1 row(s); 5051 were matched. 
>>> 
>>> The update statement looks perfectly fine:
>>> 
>>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
>>> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
>>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
>>> 
>>> Any Idea what's wrong with this.
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d71580a6-07a4-4495-af60-cd8164fcc899n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e7b12a84-1282-4407-97bd-5a75f676df63%40www.fastmail.com.

Re: [sqlalchemy] MSSQL with PyODBC: StaleDataError on UPDATE statement

[Mike Bayer]

hi -

does your Measurement table have a primary key constraint present, and does 
this primary key constraint consist of exactly the "ID" column only and no 
other columns? it would appear you have not mapped the primary key correctly.



On Fri, Jun 26, 2020, at 9:57 AM, jue...@gmail.com wrote:
> I'm currently working with sqlalchemy (Version 1.3.18) to access a Microsoft 
> Server SQL Database (Version: 14.00.3223). I use pyodbc (Version 4.0.30)
> 
> When I try to update a single object and try to update and commit the object, 
> I run into a StaleDataError:
> 
> StaleDataError: UPDATE statement on table 'Measurement' expected to update 1 
> row(s); 5051 were matched. 
> 
> The update statement looks perfectly fine:
> 
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
> 
> Any Idea what's wrong with this.
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d607c30c-2bc5-4680-9e7f-842da8f28792n%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/49798e71-dbae-48b7-b7da-922c1f94c413%40www.fastmail.com.

[sqlalchemy] Re: MSSQL with PyODBC: StaleDataError on UPDATE statement

[jue...@gmail.com]

Strangely, if I add the following mapper_args, it is working with a warning 
(SAWarning: Dialect mssql+pyodbc does not support updated rowcount - 
versioning cannot be verified.) but not with an exception.

__mapper_args__ = {
'version_id_col': Created,
'version_id_generator': False,
}

jue...@gmail.com schrieb am Freitag, 26. Juni 2020 um 16:01:07 UTC+2:

> Here is the code I used (simplified):
>
> class Measurement(Base):
> __tablename__ = 'Measurement'
> __table_args__ = (
> {'implicit_returning': False},  # Required because of some 
> triggers on this table
> )
>
> ID = Column(Integer, primary_key=True)
> Created = Column(DATETIME2, nullable=False, 
> default=datetime.datetime.now)
> IsCompleted = Column(BIT)
> Completed = Column(DATETIME2)
> 
> measurement = Measurement(Part=motor_unit, 
> TestProcedureVersion=test_procedure)
> session.add(measurement)
> session.commit()
>
> # And Later:
> measurement.IsCompleted = True
> session.commit()  # --> This raises the StaleData Exception
>
>
>
> jue...@gmail.com schrieb am Freitag, 26. Juni 2020 um 15:57:32 UTC+2:
>
>> I'm currently working with sqlalchemy (Version 1.3.18) to access a 
>> Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc 
>> (Version 4.0.30)
>>
>> When I try to update a single object and try to update and commit the 
>> object, I run into a StaleDataError:
>>
>> StaleDataError: UPDATE statement on table 'Measurement' expected to 
>> update 1 row(s); 5051 were matched. 
>>
>> The update statement looks perfectly fine:
>>
>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
>> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
>> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
>>
>> Any Idea what's wrong with this.
>>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d71580a6-07a4-4495-af60-cd8164fcc899n%40googlegroups.com.

[sqlalchemy] Re: MSSQL with PyODBC: StaleDataError on UPDATE statement

[jue...@gmail.com]

Here is the code I used (simplified):

class Measurement(Base):
__tablename__ = 'Measurement'
__table_args__ = (
{'implicit_returning': False},  # Required because of some triggers 
on this table
)

ID = Column(Integer, primary_key=True)
Created = Column(DATETIME2, nullable=False, 
default=datetime.datetime.now)
IsCompleted = Column(BIT)
Completed = Column(DATETIME2)

measurement = Measurement(Part=motor_unit, 
TestProcedureVersion=test_procedure)
session.add(measurement)
session.commit()

# And Later:
measurement.IsCompleted = True
session.commit()  # --> This raises the StaleData Exception



jue...@gmail.com schrieb am Freitag, 26. Juni 2020 um 15:57:32 UTC+2:

> I'm currently working with sqlalchemy (Version 1.3.18) to access a 
> Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc 
> (Version 4.0.30)
>
> When I try to update a single object and try to update and commit the 
> object, I run into a StaleDataError:
>
> StaleDataError: UPDATE statement on table 'Measurement' expected to update 
> 1 row(s); 5051 were matched. 
>
> The update statement looks perfectly fine:
>
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
> tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
> 2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)
>
> Any Idea what's wrong with this.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/72ff50d9-e707-487f-a19e-a1fb45c16e49n%40googlegroups.com.

[sqlalchemy] MSSQL with PyODBC: StaleDataError on UPDATE statement

[jue...@gmail.com]

I'm currently working with sqlalchemy (Version 1.3.18) to access a 
Microsoft Server SQL Database (Version: 14.00.3223). I use pyodbc 
(Version 4.0.30)

When I try to update a single object and try to update and commit the 
object, I run into a StaleDataError:

StaleDataError: UPDATE statement on table 'Measurement' expected to update 
1 row(s); 5051 were matched. 

The update statement looks perfectly fine:

2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine UPDATE 
tbm.[Measurement] SET [IsCompleted]=? WHERE tbm.[Measurement].[ID] = ?
2020-06-26 15:50:59,872 INFO sqlalchemy.engine.base.Engine (True, 6248)

Any Idea what's wrong with this.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d607c30c-2bc5-4680-9e7f-842da8f28792n%40googlegroups.com.