Implicitly combining columns message

[Larry Martell]

I have these 2 models:

class Server(Base):
__tablename__ = 'Server'

serverName = Column(String(50, 'SQL_Latin1_General_CP1_CI_AS'),
primary_key=True)
serverTypeEnumID = Column(Integer)
serverComponentEnumID = Column(ForeignKey('Enumeration.enumID'))
serverEnvironmentEnumID = Column(ForeignKey('Enumeration.enumID'),
nullable=False)
serverLifecycleEnumID = Column(ForeignKey('Enumeration.enumID'))
processorType = Column(String(50, 'SQL_Latin1_General_CP1_CI_AS'))
processorSpeedInGhz = Column(DECIMAL(9, 2))
physicalProcessorCount = Column(Integer)
corePerProcessorCount = Column(Integer)
coreCount = Column(Integer)
logicalProcessorCount = Column(Integer)
memoryInGB = Column(DECIMAL(19, 2))
serverIsVirtual = Column(String(1, 'SQL_Latin1_General_CP1_CI_AS'))
serverEndOfLifeDate = Column(DateTime)
locationCode = Column(ForeignKey('Location.locationCode'))
serverOSProductID = Column(ForeignKey('ProductVersion.productVersionID'))
updatedDate = Column(DateTime, nullable=False,
server_default=text("(getdate())"))

Location = relationship('Location')
Enumeration = relationship('Enumeration',
primaryjoin='Server.serverComponentEnumID == Enumeration.enumID')
Enumeration1 = relationship('Enumeration',
primaryjoin='Server.serverEnvironmentEnumID == Enumeration.enumID')
Enumeration2 = relationship('Enumeration',
primaryjoin='Server.serverLifecycleEnumID == Enumeration.enumID')
ProductVersion = relationship('ProductVersion')
Storage = relationship('Storage', secondary=lambda: ServerStorage.__table__)

class ServerUtilization(Server):
__tablename__ = 'ServerUtilization'

serverName = Column(ForeignKey('Server.serverName'), primary_key=True)
utilizationProfile = Column(String(50,
'SQL_Latin1_General_CP1_CI_AS'), nullable=False)
utilizationCpuPercent = Column(DECIMAL(9, 2))
utilizationMemoryPercent = Column(DECIMAL(9, 2))
utilizationStoragePercent = Column(DECIMAL(9, 2))
updatedDate = Column(DateTime, nullable=False,
server_default=text("(getdate())"))

And I get this message:

/Users/lmartell/Data-Exchange-Consolidation/scripts/venv/lib/python3.8/site-packages/sqlalchemy/orm/mapper.py:1899:
SAWarning: Implicitly combining column Server.updatedDate with column
ServerUtilization.updatedDate under attribute 'updatedDate'. Please
configure one or more attributes for these same-named columns
explicitly.

I read this: https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y

But it's not clear to me why I am getting the message in this case,
nor how to fix it.

[Mike Bayer]

this error means that your Server class has a column attribute which you would access as Server.updatedDate.   Your ServerUtilization subclass has another column attribute which you would also access as ServerUtilization.updatedDate.

the problem arises in that ServerUtilization refers to the combination of two rows, one inthe "Server" table and the other in "ServerUtilization" .

If you had rows in these two tables where updatedDate were different, the ServerUtilization entity would be unable to refer to these two separate values as there is no mapped attribute that separates them.

This means that if you were to say,   some_server_utilization.updatedDate = <somedate> , that date value would be *copied* to both the serverutilization and server tables, even though you have not given instructions to the ORM that these two columns should always be identical.

the ORM wants explicit instructions on how to handle these two different columns.  the two examples given in the FAQ entry can apply directly to your model as either separating them:

class ServerUtilization(Server):

   # ...

   utilization_updatedAt = Column("updatedAt", ...)

or combining them:

class ServerUtilization(Server):

   # ...

   updatedAt = column_property(Column("updatedAt", ...), Server.updatedAt)

-- 

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/CACwCsY7W0uHskGdoMconkCVJSy8sw21U%3DkSfE%2BkYU0mN8zKDsw%40mail.gmail.com.

[Larry Martell]

So because ServerUtilization has a FK relation to Server they cannot
have any columns with the same name?

[Mike Bayer]

it's important to distinguish between "Python attributes" and "database table columns", these are two separate things.

Both tables in your database may have a column named "updatedDate".   There is no restriction on database table structure.

Your ServerUtilization class, which refers to *both* columns, must make a choice if these two columns are to be accessed *indivudally* or *together*.   

That is, suppose  we have one row in each of "server" and "server_utilization":

server:   serverName = "server1"    updatedDate="January 12, 2007"

server_utilization:   serverName="server1"  updatedDate="March 8, 2009"

Now load ServerUtilization(serverName="server1") from the database:

su = session.query(ServerUtilization).filter(ServerUtilization.serverName == "server1").one()

now , what happens if I access updated date?

print(su.updatedDate)   # <--- what does this print?

it should print "March 8, 2009".

But note that this means the "January 12, 2007" date is not accessible from "su".

Now what if i update it:

su.updatedDate = "December 22, 2020"

Which columns should be updated?    *Only* server__utilization.updatedDate (use choice 1)?   Or *both* server_utilization.updatedDate *and* server.updatedDate?   (use choice 2)

again there is no restriction on database columns, this only refers towards how you would prefer to access these two distinct values in your python code.

-- 

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/CACwCsY6x4NOdtQqfSi43j9%3D9gg4wR88DN2H9u6bwYzj%2BPUDG-A%40mail.gmail.com.

[Larry Martell]

I would think:

su = session.query(ServerUtilization).filter(ServerUtilization.serverName
== "server1").one()

would use the data from ServerUtilization only. Does SQLA implicitly
join the 2 tables even if no columns from Server are referenced?

[Mike Bayer]

On Tue, Dec 22, 2020, at 2:15 PM, Larry Martell wrote:

I would think:

su = session.query(ServerUtilization).filter(ServerUtilization.serverName

== "server1").one()

would use the data from ServerUtilization only. Does SQLA implicitly

join the 2 tables even if no columns from Server are referenced?

Your ServerUtilization entity is the set of all columns on both tables since you are using joined table inheritance.   so yes a ServerUtilization object is the JOIN of those two tables.  if you wanted it to only refer to "ServerUtiilzation" then you would set the concrete=True parameter on the mapping.

the three styles of table inheritance mapping including joined, single and concrete are each described at https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/inheritance.html?highlight=concrete# .

-- 

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/CACwCsY4ZsVqiNb4cs_JBUp1O4w8dSPUhYvGO_h6mf937xGiY1Q%40mail.gmail.com.

[Larry Martell]

On Tue, Dec 22, 2020 at 2:53 PM Mike Bayer <mik...@zzzcomputing.com> wrote:
>
>
>
> On Tue, Dec 22, 2020, at 2:15 PM, Larry Martell wrote:
>
> I would think:
>
> su = session.query(ServerUtilization).filter(ServerUtilization.serverName
> == "server1").one()
>
> would use the data from ServerUtilization only. Does SQLA implicitly
> join the 2 tables even if no columns from Server are referenced?
>
>
>
> Your ServerUtilization entity is the set of all columns on both tables since you are using joined table inheritance. so yes a ServerUtilization object is the JOIN of those two tables. if you wanted it to only refer to "ServerUtiilzation" then you would set the concrete=True parameter on the mapping.
>
> the three styles of table inheritance mapping including joined, single and concrete are each described at https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/inheritance.html?highlight=concrete# .

Thanks!