Re: [sqlalchemy] sqlalchemy.orm.exc.FlushError on subclass
if you can't correct this model to apply the persistence details to the
concrete class you wish to persist and query, then you'd do the suggested
"enable_typechecks=False". There is no attribute in SQLAlchemy named "meta"
and no stack trace is given here so I dont know to what that refers.
Overall I'm not sure how this API_Person class is useful because you can't
query for them. I would think that if you cant change the original model
then you'd have this API_Person as a series of helper functions that accept a
Person as their argument.
On Wed, Sep 6, 2023, at 1:56 PM, 'Luna Lucadou' via sqlalchemy wrote:
> The project I am working on is split up into several modules. Previously,
> each module had its own ORM classes.
> However, due to several bugs arising from forgetting to update each module's
> ORM classes in lock step when adding new functionality, we have decided it
> would be best to extract the ORM classes which interact with our DB into
> their own module and make that available to the other modules via pip.
>
> One of these modules, which monitors for changes in an upstream DB and
> applies them to ours, has some methods which are not present in the other
> modules and which cannot be easily extracted out due to its dependencies on
> upstream DB functionality.
>
> As such, in this module, we must subclass the ORM models which interact with
> our DB:
>
> models.apimodels.db.person.py:
> #...
> @dataclass(init=False, eq=True, unsafe_hash=True)
> class Person(Base):
> __tablename__ = "person"
>
> id: Mapped[int] = mapped_column(primary_key=True)
> first_name: Mapped[str]
> last_name: Mapped[str]
> email_address: Mapped[str]
> office_address: Mapped[str]
> office_phone_number: Mapped[str]
>
> # ...
>
> etl.models.api_db.api_person.py:
> #...
> from apimodels.db.person import Person as PersonBase
> # ...
> class API_Person(PersonBase):
> __tablename__ = "person"
> __table_args__ = {"keep_existing": True}
>
> def get_pvi(self):
> # ...
>
> def get_historical_pvis(self) -> list[str]:
> # ...
>
> def __eq__(self):
> # ...
>
> def __hash__(self):
> # ...
>
> @staticmethod
> def from_upstream_hub_person(
> uh_person: Optional[UH_Person],
> ) -> Optional["API_Person"]:
> # ...
>
> Of note is that this subclass does not add any new attributes or modify
> existing ones, it merely adds some helper methods related to identifying
> primary key changes in the upstream DB. (This is also why we override the eq
> and hash methods provided by dataclasses - incoming changesets have to be
> matched against existing records, even when primary keys change upstream.)
>
> This is effectively single-table inheritance, but it is not a good fit for
> polymorphic_identity since it is not a distinct class, merely adding
> module-specific helper methods, and if I am reading the documentation
> correctly, using polymorphic_identity would mean any records touched by the
> API_Person subclass (which is all of them) would no longer be usable by other
> modules, which do not extend any of the models.
>
> From what I have read, it seems like the keep_existing param should be of use
> here, but neither it nor extend_existing set to True help with the errors I
> am seeing when attempting to interact with this subclass in any way:
>
> sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type 'model.api_db.api_person.API_Person'> as a member of collection
> "Identifier.person". Expected an object of type 'apimodels.db.person.Person'> or a polymorphic subclass of this type. If
> is a subclass of 'apimodels.db.person.Person'>, configure mapper "Mapper[Person(person)]" to
> load this subtype polymorphically, or set enable_typechecks=False to allow
> any subtype to be accepted for flush.
>
> I did try setting enable_typechecks to False, but this results in a different
> error when attempting to use getattr on the subclass:
>
> AttributeError: 'Person' object has no attribute 'meta'
>
> Is there a better way of doing 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/bff6d34f-ea35-4aba-ba94-5ae1f29154fan%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and
[sqlalchemy] sqlalchemy.orm.exc.FlushError on subclass
The project I am working on is split up into several modules. Previously,
each module had its own ORM classes.
However, due to several bugs arising from forgetting to update each
module's ORM classes in lock step when adding new functionality, we have
decided it would be best to extract the ORM classes which interact with our
DB into their own module and make that available to the other modules via
pip.
One of these modules, which monitors for changes in an upstream DB and
applies them to ours, has some methods which are not present in the other
modules and which cannot be easily extracted out due to its dependencies on
upstream DB functionality.
As such, in this module, we must subclass the ORM models which interact
with our DB:
models.apimodels.db.person.py:
#...
@dataclass(init=False, eq=True, unsafe_hash=True)
class Person(Base):
__tablename__ = "person"
id: Mapped[int] = mapped_column(primary_key=True)
first_name: Mapped[str]
last_name: Mapped[str]
email_address: Mapped[str]
office_address: Mapped[str]
office_phone_number: Mapped[str]
# ...
etl.models.api_db.api_person.py:
#...
from apimodels.db.person import Person as PersonBase
# ...
class API_Person(PersonBase):
__tablename__ = "person"
__table_args__ = {"keep_existing": True}
def get_pvi(self):
# ...
def get_historical_pvis(self) -> list[str]:
# ...
def __eq__(self):
# ...
def __hash__(self):
# ...
@staticmethod
def from_upstream_hub_person(
uh_person: Optional[UH_Person],
) -> Optional["API_Person"]:
# ...
Of note is that this subclass does not add any new attributes or modify
existing ones, it merely adds some helper methods related to identifying
primary key changes in the upstream DB. (This is also why we override the
eq and hash methods provided by dataclasses - incoming changesets have to
be matched against existing records, even when primary keys change
upstream.)
This is effectively single-table inheritance, but it is not a good fit for
polymorphic_identity since it is not a distinct class, merely adding
module-specific helper methods, and if I am reading the documentation
correctly, using polymorphic_identity would mean any records touched by the
API_Person subclass (which is all of them) would no longer be usable by
other modules, which do not extend any of the models.
>From what I have read, it seems like the keep_existing param should be of
use here, but neither it nor extend_existing set to True help with the
errors I am seeing when attempting to interact with this subclass in any
way:
sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type as a member of collection
"Identifier.person". Expected an object of type or a polymorphic subclass of this type. If
is a subclass of , configure mapper "Mapper[Person(person)]" to
load this subtype polymorphically, or set enable_typechecks=False to allow
any subtype to be accepted for flush.
I did try setting enable_typechecks to False, but this results in a
different error when attempting to use getattr on the subclass:
AttributeError: 'Person' object has no attribute 'meta'
Is there a better way of doing 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/bff6d34f-ea35-4aba-ba94-5ae1f29154fan%40googlegroups.com.
Re: [sqlalchemy] sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA' expected to update 1 row(s); 0 were matched.
it can be related to the driver or the "cloud" database you're using, we have
lots of reports of SQL Server drivers not reporting on matched rows correctly
On Wed, Sep 6, 2023, at 7:40 AM, Leandro Lázaro wrote:
> I updated the driver to the latest version and removed the line
> 'self.engine.dialect.supports_sane_rowcount = False,' and the same error
> occurred again. Could this driver also have an unknown bug?
>
> TCLOUD_DATABASE_DRIVER_V18 =
> "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1"
>
> SQLALCHEMY_TCLOUD_DATABASE_URI =
> 'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER_V18+'=yes=yes'
>
> Em ter., 5 de set. de 2023 às 22:03, Leandro Lázaro
> escreveu:
>> It worked!!! Thank you very much, it solved my problem. I was already going
>> crazy haha. I will try to update the driver to solve the problem right at
>> the root. Just a doubt: Changing to false does not generate any side effects?
>>
>> Em ter., 5 de set. de 2023 às 21:16, Mike Bayer
>> escreveu:
>>> __
>>> we will work around what is likely some kind of driver related error:
>>>
>>> engine = create_engine(" your engine string normally .. ")
>>> engine.dialect.supports_sane_rowcount = False
>>>
>>>
>>> then run the program normally. the UPDATE will succeed. however, after
>>> the program runs, look in the database and make sure the UPDATE took effect
>>> correctly.if so, the driver, either pyodbc, or the ODBC driver in use,
>>> has an unknown bug. try updating drivers, both pyodbc and the ODBC driver
>>> in use.
>>>
>>>
>>> On Tue, Sep 5, 2023, at 6:50 PM, Leandro Lázaro wrote:
Hello, apologies for the delay.
I'm using SQL Server. Updates using the code below work normally. However,
when I try to change the object directly and apply commit, I receive the
mentioned error.
stmt = update(PPESSOA).where(PPESSOA.CODIGO ==
ppessoa.CODIGO).values(EMAILPESSOAL=data['EMAIL'])
session.execute(stmt)
session.commit()
The connection string is:
SQLALCHEMY_TCLOUD_DATABASE_URI =
'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER
Driver is:
libmsodbcsql-17.10.so.4.1
Thank you for all!
Em terça-feira, 22 de agosto de 2023 às 15:51:47 UTC-3, Mike Bayer
escreveu:
> __
> if it's fully reproducible every time with both statements, then this
> suggests something is happening with the database server itself, such as
> some kind of issue with triggers getting involved or something. In
> particular if this is MS SQL Server and there are triggers involved, the
> updated rowcount might not be working correctly and extra steps might
> need to be taken to turn off SQLAlchemy's rowcount facilities.
>
> you want to turn on `echo=True` and see that the expected UPDATE
> statement takes place. Then you might want to try running those UPDATE
> statements directly on the database and see that they work.
>
> What database backend, what database driver, and is the issue only
> reproducible in a single environment or can it be reproduced elsewhere?
>
>
> On Tue, Aug 22, 2023, at 2:42 PM, Leandro Lázaro wrote:
>> Hello
>>
>> First, thanks for building this library. God bless you
>>
>>
>> I've been trying to understand what's going on for days.
>>
>> This code works fine:
>>
>> https://pastebin.com/fLTnB8jy
>>
>> But if I try to modify any other parameter like PERSONAL EMAIL I get the
>> error:
>>
>> sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA'
>> expected to update 1 row(s); 0 were matched
>>
>> Example: https://pastebin.com/EwdwLRcp
>>
>> Tank you
>>
>>
>> --
>> 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/c2939c34-4d95-4fe8-b697-afc705b47a11n%40googlegroups.com
>>
>> .
>
Re: [sqlalchemy] sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA' expected to update 1 row(s); 0 were matched.
changing it to false means if an UPDATE statement that expects to update on
primary key in the ORM does not actually match any rows, the program continues
and this may be a silent failure, leading to more confusing problems later if
the row was in fact deleted or is otherwise non-existent.
On Tue, Sep 5, 2023, at 9:03 PM, Leandro Lázaro wrote:
> It worked!!! Thank you very much, it solved my problem. I was already going
> crazy haha. I will try to update the driver to solve the problem right at the
> root. Just a doubt: Changing to false does not generate any side effects?
>
> Em ter., 5 de set. de 2023 às 21:16, Mike Bayer
> escreveu:
>> __
>> we will work around what is likely some kind of driver related error:
>>
>> engine = create_engine(" your engine string normally .. ")
>> engine.dialect.supports_sane_rowcount = False
>>
>>
>> then run the program normally. the UPDATE will succeed. however, after the
>> program runs, look in the database and make sure the UPDATE took effect
>> correctly.if so, the driver, either pyodbc, or the ODBC driver in use,
>> has an unknown bug. try updating drivers, both pyodbc and the ODBC driver
>> in use.
>>
>>
>> On Tue, Sep 5, 2023, at 6:50 PM, Leandro Lázaro wrote:
>>> Hello, apologies for the delay.
>>>
>>> I'm using SQL Server. Updates using the code below work normally. However,
>>> when I try to change the object directly and apply commit, I receive the
>>> mentioned error.
>>>
>>>
>>> stmt = update(PPESSOA).where(PPESSOA.CODIGO ==
>>> ppessoa.CODIGO).values(EMAILPESSOAL=data['EMAIL'])
>>> session.execute(stmt)
>>> session.commit()
>>>
>>> The connection string is:
>>> SQLALCHEMY_TCLOUD_DATABASE_URI =
>>> 'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER
>>>
>>> Driver is:
>>>
>>> libmsodbcsql-17.10.so.4.1
>>>
>>> Thank you for all!
>>>
>>>
>>>
>>> Em terça-feira, 22 de agosto de 2023 às 15:51:47 UTC-3, Mike Bayer escreveu:
__
if it's fully reproducible every time with both statements, then this
suggests something is happening with the database server itself, such as
some kind of issue with triggers getting involved or something. In
particular if this is MS SQL Server and there are triggers involved, the
updated rowcount might not be working correctly and extra steps might need
to be taken to turn off SQLAlchemy's rowcount facilities.
you want to turn on `echo=True` and see that the expected UPDATE statement
takes place. Then you might want to try running those UPDATE statements
directly on the database and see that they work.
What database backend, what database driver, and is the issue only
reproducible in a single environment or can it be reproduced elsewhere?
On Tue, Aug 22, 2023, at 2:42 PM, Leandro Lázaro wrote:
> Hello
>
> First, thanks for building this library. God bless you
>
>
> I've been trying to understand what's going on for days.
>
> This code works fine:
>
> https://pastebin.com/fLTnB8jy
>
> But if I try to modify any other parameter like PERSONAL EMAIL I get the
> error:
>
> sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA'
> expected to update 1 row(s); 0 were matched
>
> Example: https://pastebin.com/EwdwLRcp
>
> Tank you
>
>
> --
> 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/c2939c34-4d95-4fe8-b697-afc705b47a11n%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
Re: [sqlalchemy] sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA' expected to update 1 row(s); 0 were matched.
I updated the driver to the latest version and removed the line
'self.engine.dialect.supports_sane_rowcount = False,' and the same error
occurred again. Could this driver also have an unknown bug?
TCLOUD_DATABASE_DRIVER_V18 =
"/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1"
SQLALCHEMY_TCLOUD_DATABASE_URI =
'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@
'+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER_V18+'=yes=yes'
Em ter., 5 de set. de 2023 às 22:03, Leandro Lázaro <
leandrolazaro5...@gmail.com> escreveu:
> It worked!!! Thank you very much, it solved my problem. I was already
> going crazy haha. I will try to update the driver to solve the problem
> right at the root. Just a doubt: Changing to false does not generate any
> side effects?
>
> Em ter., 5 de set. de 2023 às 21:16, Mike Bayer <
> mike_not_on_goo...@zzzcomputing.com> escreveu:
>
>> we will work around what is likely some kind of driver related error:
>>
>> engine = create_engine(" your engine string normally .. ")
>> engine.dialect.supports_sane_rowcount = False
>>
>>
>> then run the program normally. the UPDATE will succeed. however, after
>> the program runs, look in the database and make sure the UPDATE took effect
>> correctly.if so, the driver, either pyodbc, or the ODBC driver in use,
>> has an unknown bug. try updating drivers, both pyodbc and the ODBC driver
>> in use.
>>
>>
>> On Tue, Sep 5, 2023, at 6:50 PM, Leandro Lázaro wrote:
>>
>> Hello, apologies for the delay.
>>
>> I'm using SQL Server. Updates using the code below work normally.
>> However, when I try to change the object directly and apply commit, I
>> receive the mentioned error.
>>
>> stmt = update(PPESSOA).where(PPESSOA.CODIGO ==
>> ppessoa.CODIGO).values(EMAILPESSOAL=data['EMAIL'])
>> session.execute(stmt)
>> session.commit()
>>
>> The connection string is:
>> SQLALCHEMY_TCLOUD_DATABASE_URI =
>> 'mssql://'+TCLOUD_DATABASE_USERNAME+':'+TCLOUD_DATABASE_PASSWORD_UPDATED+'@
>> '+TCLOUD_DATABASE_HOST+':'+TCLOUD_DATABASE_PORT+'/'+TCLOUD_DATABASE_NAME+'?driver='+TCLOUD_DATABASE_DRIVER
>>
>> Driver is:
>>
>> libmsodbcsql-17.10.so.4.1
>>
>> Thank you for all!
>>
>>
>> Em terça-feira, 22 de agosto de 2023 às 15:51:47 UTC-3, Mike Bayer
>> escreveu:
>>
>>
>> if it's fully reproducible every time with both statements, then this
>> suggests something is happening with the database server itself, such as
>> some kind of issue with triggers getting involved or something. In
>> particular if this is MS SQL Server and there are triggers involved, the
>> updated rowcount might not be working correctly and extra steps might need
>> to be taken to turn off SQLAlchemy's rowcount facilities.
>>
>> you want to turn on `echo=True` and see that the expected UPDATE
>> statement takes place. Then you might want to try running those UPDATE
>> statements directly on the database and see that they work.
>>
>> What database backend, what database driver, and is the issue only
>> reproducible in a single environment or can it be reproduced elsewhere?
>>
>>
>> On Tue, Aug 22, 2023, at 2:42 PM, Leandro Lázaro wrote:
>>
>> Hello
>>
>> First, thanks for building this library. God bless you
>>
>>
>> I've been trying to understand what's going on for days.
>>
>> This code works fine:
>>
>> https://pastebin.com/fLTnB8jy
>>
>> But if I try to modify any other parameter like PERSONAL EMAIL I get the
>> error:
>>
>> sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'PPESSOA'
>> expected to update 1 row(s); 0 were matched
>>
>> Example: https://pastebin.com/EwdwLRcp
>>
>> Tank you
>>
>>
>> --
>> 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/c2939c34-4d95-4fe8-b697-afc705b47a11n%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
