Re: [sqlalchemy] Issue with "complex" many 2 many delete, FK violation

[Mark Aquino]

Hi Mike,

Sorry about the indentations.

I'm not sure I understand the changes you made to the script after delete
as it removes all test_chains, test_var_regions, and test_const regions
that are still referenced by the other test_molecules.   The only way I've
been able to get the delete to work properly is to manually delete
test_var_regions and test_const_regions first and then delete the
test_molecules, but the ideal outcome I'm trying to achieve is that when a
test_molecule is deleted:

   1.  that the system checks if the chains connected to it are removed if
   they are not linked to other test_molecules.
   2. if a test_chain is going to be deleted then
  1. the test_mol_sequence associated with it is deleted if it is no
  longer associated with any other test_chains
  2. any test_var_regions and test_const_regions are deleted if they
  are not associated with any other test_chains
   3. and finally if a test_mol_sequence is deleted that any
   test_mol_sequence_features are deleted if they are not associated with any
   other test_mol_sequences.


To make things a little easier to explain, if we just are dealing with
molecule1 and molecule4 there are only 3 unique test_chains: heavy_chain_1,
light_chain_1, and heavy_chain_2
if I deleted molecule1, I would want to retain heavy_chain_2 and
light_chain_1 and delete heavy_chain_1 as it was no longer associated with
any test_molecule. Ideally, then I would remove any test_const_regions and
test_var_regions that are no longer associated with any test_chains.
Because heavy_chain_1 shares the same test_var_region as heavy_chain_2,
that test_var_region would remain in the system but the test_const_region
unique to heavy_chain_1 would be deleted along with the test_mol_sequence
and test_mol_sequence_features associated with it.

molecule1.chains.add(heavy_chain_1)
molecule1.chains.add(light_chain_1)
#molecule2.chains.add(heavy_chain_2)
#molecule2.chains.add(light_chain_2)
#molecule3.chains.add(heavy_chain_1)
#molecule3.chains.add(light_chain_2)
molecule4.chains.add(heavy_chain_2)
molecule4.chains.add(light_chain_1)


light_chain_1_sequence = TestMolSequence(content="taglconst1VAR1")
heavy_chain_1_sequence = TestMolSequence(content="tagheavyconstant1VAR2")
heavy_chain_2_sequence = TestMolSequence(content="tagheavyconstant2VAR2")
light_chain_2_sequence = TestMolSequence(content="taglconst1VAR3")


Does that make sense?



On Tue, Apr 13, 2021 at 4:50 PM Mike Bayer  wrote:

> Hi there -
>
> I would ask that you try to make sure your formatting is maintained when
> posting examples especially such long ones as I had to re-indent it in
> order to run this.
>
> The delete at the end is failing because of incomplete cascade rules.
> The DELETE against "test_mol_sequence" seeks to CASCADE as configured to
> the other three tables,, which then fail because there are non-cascading
> FKs in the association tables.   We can see this via the message:
>
> update or delete on table "test_var_region" violates foreign key
> constraint "test_chain_var_region_var_region_id_fkey" on table
> "test_chain_var_region"
> DETAIL:  Key (id)=(1) is still referenced from table
> "test_chain_var_region".
>
> [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id = %(id)s]
>
>
> it's DELETEing from test_mol_sequence but the error is against a totally
> different table.  That's postgresql's cascade.
>
> if you want these CASCADEs to remain in place then you'd need to ensure
> that those linked rows can be deleted without any rows being present in the
> association tables.
>
> I can modify your test at the end to fully DELETE these rows without
> qualifying for those that have empty collections only and the script then
> passes, because now it's deleting those rows that would otherwise be
> dependent on by "test_var_region" and therefore "test_mol_sequence",  so
> there's nothing unexpected going on.Easiest solution here would be to
> add CASCADE rules to the association tables also.  If you want that to be
> prevented as you mention, and instead expect the script to explicitly
> delete those depending rows, then your script is already achieving that.
> the "business logic" so to speak in this case would be as below:
>
> orphan_chains = (
> session.query(TestChain).
> #filter(~TestChain.molecules.any()).
> all()
> )
> for chain in orphan_chains:
> session.delete(chain)
>
> orphan_vrs = (
> session.query(TestVarRegion)
> #.filter(~TestVarRegion.chains.any())
> .all()
> )
> for orphan_vr in orphan_vrs:
> session.delete(orphan_vr)
> orphan_crs = (
> session.query(TestConstRegion)
> #   .filter(~TestConstRegion.chains.any())
> .all()
> )
> for orphan_cr in orphan_crs:
> session.delete(orphan_cr)
> orphan_sequences = (

Re: [sqlalchemy] Issue with "complex" many 2 many delete, FK violation

[Mike Bayer]

Hi there -

I would ask that you try to make sure your formatting is maintained when 
posting examples especially such long ones as I had to re-indent it in order to 
run this.

The delete at the end is failing because of incomplete cascade rules.   The 
DELETE against "test_mol_sequence" seeks to CASCADE as configured to the other 
three tables,, which then fail because there are non-cascading FKs in the 
association tables.   We can see this via the message:

update or delete on table "test_var_region" violates foreign key constraint 
"test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region" 
DETAIL:  Key (id)=(1) is still referenced from table "test_chain_var_region".

[SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id = %(id)s]


it's DELETEing from test_mol_sequence but the error is against a totally 
different table.  That's postgresql's cascade.

if you want these CASCADEs to remain in place then you'd need to ensure that 
those linked rows can be deleted without any rows being present in the 
association tables.

I can modify your test at the end to fully DELETE these rows without qualifying 
for those that have empty collections only and the script then passes, because 
now it's deleting those rows that would otherwise be dependent on by 
"test_var_region" and therefore "test_mol_sequence",  so there's nothing 
unexpected going on.Easiest solution here would be to add CASCADE rules to 
the association tables also.  If you want that to be prevented as you mention, 
and instead expect the script to explicitly delete those depending rows, then 
your script is already achieving that.   the "business logic" so to speak in 
this case would be as below:

orphan_chains = ( 
session.query(TestChain).
#filter(~TestChain.molecules.any()).
all()
)
for chain in orphan_chains:
session.delete(chain)

orphan_vrs = (
session.query(TestVarRegion)
#.filter(~TestVarRegion.chains.any())
.all()
)
for orphan_vr in orphan_vrs:
session.delete(orphan_vr)
orphan_crs = (
session.query(TestConstRegion)
#   .filter(~TestConstRegion.chains.any())
.all()
)
for orphan_cr in orphan_crs:
session.delete(orphan_cr)
orphan_sequences = (
session.query(TestMolSequence)
#   .filter(~TestMolSequence.chains.any())
.all()
)








On Tue, Apr 13, 2021, at 10:03 AM, maqui...@gmail.com 
 wrote:
> I need to delete the association table rows for many to many relationships 
> when I delete one, but the default behavior (to remove those rows) does not 
> seem to work in my case.
> 
> I have multiple levels of many to many relationships, as you can see in the 
> example I'll provide below and when I delete a "parent" afterwards I try to 
> clean up any children left behind that have no other parents.  However, these 
> children are in many to many relationships with other children and that's 
> when the ORM fails to attempt to remove those children from their related 
> association tables (at least, in a way that I expect).
> 
> The issue is error is:
> def do_executemany(self, cursor, statement, parameters, context=None):
> if self.executemany_mode is EXECUTEMANY_DEFAULT:
> >   cursor.executemany(statement, parameters)
> E   sqlalchemy.exc.IntegrityError: 
> (psycopg2.errors.ForeignKeyViolation) update or delete on table 
> "test_var_region" violates foreign key constraint 
> "test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
> E   DETAIL:  Key (id)=(1) is still referenced from table 
> "test_chain_var_region".
> E   
> E   [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id = 
> %(id)s]
> E   [parameters: ({'id': 5}, {'id': 6}, {'id': 7}, {'id': 8}, {'id': 
> 9}, {'id': 10})]
> E   (Background on this error at: http://sqlalche.me/e/gkpj)
> 
> The desired effect, of course, is that the rows in test_chain_var_region that 
> reference the deleted chains removed.  I've tried several strategies to do 
> this but with no change in this behavior.
> 
> Cascades could be an issue, and I would rather handle removal of any "orphan" 
> rows in the model tables via business logic than have the database cascade 
> deletes and potentially remove rows that are associated with other objects.
> 
> import pytest
> from sqlalchemy import (
> Table,
> Column,
> Integer,
> String,
> ForeignKey,
> create_engine,
> )
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy.orm import relationship, Session
> from sqlalchemy.util import OrderedSet
> 
> 
> Base: DeclarativeMeta = declarative_base()
> engine = create_engine(
> "postgresql://postgres:postgres@localhost:5432/espresso", echo=True
> )
> 

Re: [sqlalchemy] Issue with "complex" many 2 many delete, FK violation

[Mark Aquino]

I just wanted to clarify, the desire would be for the "test_var_region" and
"test_const_region" entities that are linked to other entities to remain
untouched and only to have their associations removed from the deleted
items. The output from the ORM indicates that the system is actually
attempting to delete them in some sort of cascade event a.) although that
is unspecified in any cascade option and b.) that is undesired in the first
place.

On Tue, Apr 13, 2021 at 10:04 AM maqui...@gmail.com 
wrote:

> I need to delete the association table rows for many to many relationships
> when I delete one, but the default behavior (to remove those rows) does not
> seem to work in my case.
>
> I have multiple levels of many to many relationships, as you can see in
> the example I'll provide below and when I delete a "parent" afterwards I
> try to clean up any children left behind that have no other parents.
> However, these children are in many to many relationships with other
> children and that's when the ORM fails to attempt to remove those children
> from their related association tables (at least, in a way that I expect).
>
> The issue is error is:
> def do_executemany(self, cursor, statement, parameters, context=None):
> if self.executemany_mode is EXECUTEMANY_DEFAULT:
> >   cursor.executemany(statement, parameters)
> E   sqlalchemy.exc.IntegrityError:
> (psycopg2.errors.ForeignKeyViolation) update or delete on table
> "test_var_region" violates foreign key constraint
> "test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
> E   DETAIL:  Key (id)=(1) is still referenced from table
> "test_chain_var_region".
> E
> E   [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id
> = %(id)s]
> E   [parameters: ({'id': 5}, {'id': 6}, {'id': 7}, {'id': 8},
> {'id': 9}, {'id': 10})]
> E   (Background on this error at: http://sqlalche.me/e/gkpj)
>
> The desired effect, of course, is that the rows in test_chain_var_region
> that reference the deleted chains removed.  I've tried several strategies
> to do this but with no change in this behavior.
>
> Cascades could be an issue, and I would rather handle removal of any
> "orphan" rows in the model tables via business logic than have the database
> cascade deletes and potentially remove rows that are associated with other
> objects.
>
> import pytest
> from sqlalchemy import (
> Table,
> Column,
> Integer,
> String,
> ForeignKey,
> create_engine,
> )
> from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
> from sqlalchemy.orm import relationship, Session
> from sqlalchemy.util import OrderedSet
>
>
> Base: DeclarativeMeta = declarative_base()
> engine = create_engine(
> "postgresql://postgres:postgres@localhost:5432/espresso", echo=True
> )
> test_chain_const_region = Table(
> "test_chain_const_region",
> Base.metadata,
> Column("chain_id", Integer, ForeignKey("test_chain.id")),
> Column("const_region_id", Integer, ForeignKey("test_const_region.id")),
> )
> test_chain_var_region = Table(
> "test_chain_var_region",
> Base.metadata,
> Column("chain_id", Integer, ForeignKey("test_chain.id")),
> Column("var_region_id", Integer, ForeignKey("test_var_region.id")),
> )
> test_molecule_chain = Table(
> "test_molecule_chain",
> Base.metadata,
> Column("molecule_id", Integer, ForeignKey("test_molecule.id")),
> Column("chain_id", Integer, ForeignKey("test_chain.id")),
> )
>
> test_mol_sequence_feat_mol_sequence = Table(
> "test_mol_sequence_feat_mol_sequence",
> Base.metadata,
> Column("mol_sequence_feat_id", Integer, ForeignKey("
> test_mol_sequence_feat.id")),
> Column("mol_sequence_id", Integer, ForeignKey("test_mol_sequence.id")),
> )
>
>
> class TestMolecule(Base):
> __tablename__ = "test_molecule"
> id = Column(Integer, primary_key=True)
> label = Column(String)
> chains = relationship(
> "TestChain",
> secondary=test_molecule_chain,
> collection_class=OrderedSet,
> back_populates="molecules",
> )
>
>
> class TestMolSequence(Base):
> __tablename__ = "test_mol_sequence"
>
> id = Column(Integer, primary_key=True)
> content = Column(String, nullable=False, unique=True)
> parent_features = relationship(
> "TestMolSequenceFeat",
> secondary=test_mol_sequence_feat_mol_sequence,
> collection_class=OrderedSet,
> back_populates="feature_sequences",
> single_parent=True,
> )
> chains = relationship(
> "TestChain", back_populates="mol_sequence", collection_class=OrderedSet
> )
>
>
> class TestMolSequenceFeat(Base):
> __tablename__ = "test_mol_sequence_feat"
>
> id = Column(Integer, primary_key=True)
> molecule_sequence_id = Column(
> Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
> )
> molecule_sequence = relationship("TestMolSequence",)
> start = Column(Integer)
> stop = Column(Integer)
> feature_sequences = relationship(
> "TestMolSequence",
> secondary=test_mol_sequence_feat_mol_sequence,
> collection_class=OrderedSet,
> back_populates="parent_features",
> # 

[sqlalchemy] Issue with "complex" many 2 many delete, FK violation

[maqui...@gmail.com]

I need to delete the association table rows for many to many relationships 
when I delete one, but the default behavior (to remove those rows) does not 
seem to work in my case.

I have multiple levels of many to many relationships, as you can see in the 
example I'll provide below and when I delete a "parent" afterwards I try to 
clean up any children left behind that have no other parents.  However, 
these children are in many to many relationships with other children and 
that's when the ORM fails to attempt to remove those children from their 
related association tables (at least, in a way that I expect).

The issue is error is:
def do_executemany(self, cursor, statement, parameters, context=None):
if self.executemany_mode is EXECUTEMANY_DEFAULT:
>   cursor.executemany(statement, parameters)
E   sqlalchemy.exc.IntegrityError: 
(psycopg2.errors.ForeignKeyViolation) update or delete on table 
"test_var_region" violates foreign key constraint 
"test_chain_var_region_var_region_id_fkey" on table "test_chain_var_region"
E   DETAIL:  Key (id)=(1) is still referenced from table 
"test_chain_var_region".
E   
E   [SQL: DELETE FROM test_mol_sequence WHERE test_mol_sequence.id 
= %(id)s]
E   [parameters: ({'id': 5}, {'id': 6}, {'id': 7}, {'id': 8}, 
{'id': 9}, {'id': 10})]
E   (Background on this error at: http://sqlalche.me/e/gkpj)

The desired effect, of course, is that the rows in test_chain_var_region 
that reference the deleted chains removed.  I've tried several strategies 
to do this but with no change in this behavior.

Cascades could be an issue, and I would rather handle removal of any 
"orphan" rows in the model tables via business logic than have the database 
cascade deletes and potentially remove rows that are associated with other 
objects.

import pytest
from sqlalchemy import (
Table,
Column,
Integer,
String,
ForeignKey,
create_engine,
)
from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta
from sqlalchemy.orm import relationship, Session
from sqlalchemy.util import OrderedSet


Base: DeclarativeMeta = declarative_base()
engine = create_engine(
"postgresql://postgres:postgres@localhost:5432/espresso", echo=True
)
test_chain_const_region = Table(
"test_chain_const_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("const_region_id", Integer, ForeignKey("test_const_region.id")),
)
test_chain_var_region = Table(
"test_chain_var_region",
Base.metadata,
Column("chain_id", Integer, ForeignKey("test_chain.id")),
Column("var_region_id", Integer, ForeignKey("test_var_region.id")),
)
test_molecule_chain = Table(
"test_molecule_chain",
Base.metadata,
Column("molecule_id", Integer, ForeignKey("test_molecule.id")),
Column("chain_id", Integer, ForeignKey("test_chain.id")),
)

test_mol_sequence_feat_mol_sequence = Table(
"test_mol_sequence_feat_mol_sequence",
Base.metadata,
Column("mol_sequence_feat_id", Integer, 
ForeignKey("test_mol_sequence_feat.id")),
Column("mol_sequence_id", Integer, ForeignKey("test_mol_sequence.id")),
)


class TestMolecule(Base):
__tablename__ = "test_molecule"
id = Column(Integer, primary_key=True)
label = Column(String)
chains = relationship(
"TestChain",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="molecules",
)


class TestMolSequence(Base):
__tablename__ = "test_mol_sequence"

id = Column(Integer, primary_key=True)
content = Column(String, nullable=False, unique=True)
parent_features = relationship(
"TestMolSequenceFeat",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="feature_sequences",
single_parent=True,
)
chains = relationship(
"TestChain", back_populates="mol_sequence", collection_class=OrderedSet
)


class TestMolSequenceFeat(Base):
__tablename__ = "test_mol_sequence_feat"

id = Column(Integer, primary_key=True)
molecule_sequence_id = Column(
Integer, ForeignKey("test_mol_sequence.id", ondelete="CASCADE"),
)
molecule_sequence = relationship("TestMolSequence",)
start = Column(Integer)
stop = Column(Integer)
feature_sequences = relationship(
"TestMolSequence",
secondary=test_mol_sequence_feat_mol_sequence,
collection_class=OrderedSet,
back_populates="parent_features",
# single_parent=True,
)


class TestChain(Base):
__tablename__ = "test_chain"

id = Column(Integer, primary_key=True)
label = Column(String)
chain_type = Column(String)
mol_sequence_id = Column(Integer, ForeignKey("test_mol_sequence.id"))
mol_sequence = relationship("TestMolSequence", back_populates="chains")
molecules = relationship(
"TestMolecule",
secondary=test_molecule_chain,
collection_class=OrderedSet,
back_populates="chains",
)
var_regions = relationship(
"TestVarRegion",
secondary=test_chain_var_region,
collection_class=OrderedSet,
back_populates="chains",
)
const_regions = relationship(
"TestConstRegion",
secondary=test_chain_const_region,
collection_class=OrderedSet,
back_populates="chains",
)


class 

Re: [sqlalchemy] How to refer to columns whose names begin with a number when autoloading?

[Mike Bayer]

the next section at 
https://docs.sqlalchemy.org/en/14/orm/mapping_columns.html#automating-column-naming-schemes-from-reflected-tables
 shows how to automate intercepting of reflected columns, so you could do this:

from sqlalchemy import event 

@event.listens_for(metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
entries = {
"1st": "first",
"2nd": "second",
"3rd": "third",
"4th": "fourth"
}
for prefix in entries:
if prefix in column["name"]:
column["key"] = column["name"].replace(prefix, entries[prefix])
break


class Student(Model):
__table__ = Table("Students", metadata, autoload=True, autoload_with=engine)




On Tue, Apr 13, 2021, at 8:48 AM, r...@rosenfeld.to 
 wrote:
> Thanks for the documentation.  Sorry, but I'm not certain how to apply that 
> in my case.  Since I am mapping to an existing table, how could I reference 
> the object attribute with an illegal name in Python?   Do I combine getattr 
> with the documentation as below?
> 
> class Student(Model):
> __table__ = Table("Students", metadata, autoload=True, 
> autoload_with=engine)
> first_period = getattr(__table__.c, "1st_period")
> 
> Thanks,
> Rob
> On Monday, April 12, 2021 at 11:17:59 PM UTC-5 Mike Bayer wrote:
>> __
>> besides the idea of using getattr(), as these are object attributes it's 
>> probably a good idea to name them differently from those columns.  See the 
>> docs at 
>> https://docs.sqlalchemy.org/en/14/orm/mapping_columns.html#naming-columns-distinctly-from-attribute-names
>>  for strategies on how to achieve this.
>> 
>> 
>> On Mon, Apr 12, 2021, at 12:29 AM, Rob Rosenfeld wrote:
>>> Hi All,
>>> 
>>> I'm using SQLAlchemy to access a legacy MSSQL database.   I'm using the 
>>> autoload feature to load the schema from the database.
>>> 
>>> In this example I'd like to read data out of the column named "1st_period" 
>>> in the database.   The following query shows the SQL I'd need.  But trying 
>>> to access a property named "1st_period" yields a SyntaxError
>>> 
>>> Thanks,
>>> Rob
>>> 
>>> SELECT TOP 10 [1st_period] FROM Students;
>>> 
>>> class Student(Model):
>>> __table__ = Table("Students", metadata, autoload=True, autoload_with=engine)
>>> 
>>> 
>>> @property
>>> def first_period(self):
>>> return self.1st_period
>>> 
>>> 
>>> 
>>> 

>>> -- 
>>> 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/CAHUdipkSOfZhBGfXpiOu5nV1XKtbw8ML8%3DSQ40EbXO97oyoR2w%40mail.gmail.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/ac1c30c1-6cdc-46df-af84-6219beb521d7n%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/1b88c4ed-7889-4b0f-8e9c-17e4b981ab86%40www.fastmail.com.

Re: [sqlalchemy] How to refer to columns whose names begin with a number when autoloading?

[r...@rosenfeld.to]

Thanks for the documentation.  Sorry, but I'm not certain how to apply that 
in my case.  Since I am mapping to an existing table, how could I reference 
the object attribute with an illegal name in Python?   Do I combine getattr 
with the documentation as below?

class Student(Model):
__table__ = Table("Students", metadata, autoload=True, 
autoload_with=engine)
first_period = getattr(__table__.c, "1st_period")

Thanks,
Rob

On Monday, April 12, 2021 at 11:17:59 PM UTC-5 Mike Bayer wrote:

> besides the idea of using getattr(), as these are object attributes it's 
> probably a good idea to name them differently from those columns.  See the 
> docs at 
> https://docs.sqlalchemy.org/en/14/orm/mapping_columns.html#naming-columns-distinctly-from-attribute-names
>  
> for strategies on how to achieve this.
>
>
> On Mon, Apr 12, 2021, at 12:29 AM, Rob Rosenfeld wrote:
>
> Hi All,
>
> I'm using SQLAlchemy to access a legacy MSSQL database.   I'm using the 
> autoload feature to load the schema from the database.
>
> In this example I'd like to read data out of the column named "1st_period" 
> in the database.   The following query shows the SQL I'd need.  But trying 
> to access a property named "1st_period" yields a SyntaxError
>
> Thanks,
> Rob
>
> SELECT TOP 10 [1st_period] FROM Students;
>
> class Student(Model):
> __table__ = Table("Students", metadata, autoload=True, 
> autoload_with=engine)
>
>
> @property
> def first_period(self):
> return self.1st_period
>
> 
>
>
> -- 
> 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/CAHUdipkSOfZhBGfXpiOu5nV1XKtbw8ML8%3DSQ40EbXO97oyoR2w%40mail.gmail.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/ac1c30c1-6cdc-46df-af84-6219beb521d7n%40googlegroups.com.

Re: Re[4]: [sqlalchemy] Invertinace mapped type_id to fix value for each child class

[Simon King]

I probably wouldn't use this:

if test_type == ChildClass1().typ_id:

...simply because creating an instance of the object just to get
access to the typ_id seems like a waste of effort. If you really need
to check integer typ_id values, the staticmethod approach seems fine.

Simon

On Mon, Apr 12, 2021 at 8:58 PM 'Sören Textor' via sqlalchemy
 wrote:
>
> Hi Simon
> Again you really helped me out. I don't know what point I missed, but
> now it works. As usual it's not as simpe le or lets say there are a lot
> more code pieces to change before I can really test it in my code. But I
> got it.
>
> just one more thing:
> I often have to check if a given tpye t the class type. Therefore I
> usally use the statci method.
>
> Thus what would you do:
>
>  if test_type == ChildClass1().typ_id:
> or
>  if test_type==ChildClass.TypID():
>
> and to ensure only TypId exists fpr that type:
>  __mapper_args__ = {
>  "polymorphic_identity": ChildClass.TypID(),
>  }
>
> And as I said: Thanks a lot!
>
> SirAnn
>
>
> -- Originalnachricht --
> Von: "Simon King" 
> An: sqlalchemy@googlegroups.com
> Gesendet: 12.04.2021 20:26:48
> Betreff: Re: Re[2]: [sqlalchemy] Invertinace mapped type_id to fix value
> for each child class
>
> >Here's a standalone working example:
> >
> >import sqlalchemy as sa
> >import sqlalchemy.orm as saorm
> >from sqlalchemy.ext.declarative import declarative_base
> >
> >
> >Base = declarative_base()
> >
> >
> >class Objekt(Base):
> > __tablename__ = "objekt"
> > id = sa.Column(sa.Integer, primary_key=True)
> > typ_id = sa.Column(sa.Integer, sa.ForeignKey("objekt_typ.id"))
> > typ = saorm.relationship("ObjektTyp")
> > name = sa.Column(sa.String(100))
> >
> > __mapper_args__ = {
> > "polymorphic_on": typ_id,
> > }
> >
> >
> >class ObjektTyp(Base):
> > __tablename__ = "objekt_typ"
> > id = sa.Column(sa.Integer, primary_key=True)
> > name = sa.Column(sa.String(100))
> >
> >
> >class ChildObjekt1(Objekt):
> > __tablename__ = "child_objekt1"
> > id = sa.Column(sa.Integer, sa.ForeignKey(Objekt.id), primary_key=True)
> > text = sa.Column(sa.String(255))
> >
> > __mapper_args__ = {
> > "polymorphic_identity": 1,
> > }
> >
> >
> >class ChildObjekt2(Objekt):
> > __tablename__ = "child_objekt2"
> > id = sa.Column(sa.Integer, sa.ForeignKey(Objekt.id), primary_key=True)
> > text = sa.Column(sa.String(255))
> >
> > __mapper_args__ = {
> > "polymorphic_identity": 2,
> > }
> >
> >
> >if __name__ == "__main__":
> > engine = sa.create_engine("sqlite://")
> > Base.metadata.create_all(bind=engine)
> > Session = saorm.sessionmaker(bind=engine)
> >
> > session = Session()
> > child1type = ObjektTyp(id=1, name="child1")
> > child2type = ObjektTyp(id=2, name="child1")
> >
> > child1 = ChildObjekt1(text="child 1 text")
> > child2 = ChildObjekt2(text="child 2 text")
> >
> > session.add_all([child1type, child2type, child1, child2])
> > session.flush()
> >
> > for obj in session.query(Objekt):
> > print(obj)
> >
> >
> >Simon
> >
> >On Mon, Apr 12, 2021 at 6:40 PM 'Sören Textor' via sqlalchemy
> > wrote:
> >>
> >>  class Objekt(db.Model):
> >>   __tablename__ = 'objekt'
> >>
> >>   def __init__(self,**kwargs):
> >>   super().__init__(**kwargs)
> >>
> >>   id = db.Column(db.Integer, primary_key=True)
> >>   typ_id = db.Column(db.Integer, db.ForeignKey('objekt_typ.id'))
> >>   typ= db.relationship("ObjektTyp")
> >>   name   = db.Column(db.String(100))
> >>
> >>   __mapper_args__ = {
> >>   'polymorphic_on': typ_id
> >>   }
> >>
> >>  class ChildObjekt1(Objekt):
> >>   __versioned__ = {}
> >>   __tablename__ = 'child_objekt1'
> >>
> >>   @staticmethod
> >>   def TypId():
> >>   return 7
> >>
> >>   # User fields
> >>   def __init__(self,**kwargs):
> >>   super().__init__(**kwargs)
> >>   #super().__init__(typ_id=ChildObjekt1.TypId(), **kwargs)
> >>
> >>   ###
> >>   id db.Column(db.Integer, db.ForeignKey('objekt.id'),
> >>  primary_key=True)
> >>   text = db.Column(db.String(255 ), default='')
> >>
> >>   __mapper_args__ = {
> >>   'polymorphic_identity': 7,
> >>   }
> >>
> >>
> >>  leads to:
> >>  venv\lib\site-packages\sqlalchemy\orm\mapper.py", line 1542, in
> >>  _configure_polymorphic_setter
> >>   self.polymorphic_on = self._props[self.polymorphic_on]
> >>  KeyError: 'typ_id'
> >>
> >>  raise exception
> >>  sqlalchemy.exc.ArgumentError: Can't determine polymorphic_on value
> >>  'typ_id' - no attribute is mapped to this name.
> >>
> >>  maybe i do something totally worg.. I am also using sql continuum
> >>
> >>  -- Originalnachricht --
> >>  Von: "Simon King" 
> >>  An: sqlalchemy@googlegroups.com
> >>  Gesendet: 12.04.2021 19:06:11
> >>  Betreff: Re: [sqlalchemy] Invertinace mapped