[sqlalchemy] Re: orm internal question - querying multiple classes
thanks, Mike! -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Polymorphic Query All regardless of subclass
On Thu, Feb 28, 2019 at 1:34 PM Andrew Martin wrote:
>
> Hi All,
>
> Trying something out here for an analytics app here, and I'm not sure any of
> this is a good idea. I'm trying to create a data model that's flexible enough
> to handle a lot of different types of analysis. From plain CSVs to time
> series to survey questions with complex hierarchies. My approach is to have a
> pretty plain model and query as needed to construct dataframes that will end
> up in pandas and sci-kit learn.
>
> The basic idea is that there's a dataset that has variables associated with
> it. Variables have values associated. I want to retain type information on
> the values. If I'm reading a file and loading it, I want to store an int as
> an int or a datetime as a datetime and not stuff everything into a string and
> have to guess what things are based on variable metadata. That's where I'm
> kind of stuck. I think I have the multi-table inheritance set up correctly to
> store different types of value. I'm totally unsure how to query without
> having to know the subclass. I'd like to be able to extract values for pandas
> processing with something like this:
>
> vals = Values.value.filter_by(variable_id=123456).all()
>
> without having to know per variable which subclass to choose. I.e., I don't
> want to have to do
> vals = IntValue.value.filter_by(variable_id=123456).all()
>
so "Values.value.filter_by(variable_id=123456).all()" is not
meaningful to me because I don't know what "Values.value" is, is that
a dynamic relationship hence you're getting a Query ? What SQL do
you see this rendering?
>
> The second part of my question is only relevant if this whole design isn't
> hosed, which I'm fine if you tell me that it is and I need to go back to the
> drawing board.
>
> The second part is how to extract an entire dataset by ID in a way that would
> be comfortable in pandas. If I were doing this in raw SQL, I'd use a CTE to
> get the variables by dataset id and use a pivot to create the
> data-frame-shaped table. I'm confounded by how to do this in alchemy,
> especially with the polymorphic setup.
can you show me that ?
FTR we usually reduce the JOINs by putting all the "value" columns in
one table but otherwise similar approach, see:
https://docs.sqlalchemy.org/en/latest/_modules/examples/vertical/dictlike-polymorphic.html
>
>
> Here are my current model definitions:
>
>
> from sqlalchemy import Column, Index, Integer, Text, DateTime, Float,
> ForeignKey
> from sqlalchemy.orm import relationship
>
> from .meta import Base
>
>
> class DataSet(Base):
> __tablename__ = 'datasets'
> id = Column(Integer, primary_key=True)
> name = Column(Text, nullable=False)
> description = Column(Text, nullable=True)
>
>
>
> class Variable(Base):
> __tablename__ = 'variables'
> id = Column(Integer, primary_key=True)
> dataset_id = Column(Integer, ForeignKey('datasets.id'), Nullable=False)
> name = Column(Text, nullable=False)
> description = Column(Text, nullable=True)
> group_var_col_id = Column(Integer, ForeignKey('variables.id'),
> nullable=True)
> group_var_row_id = Column(Integer, ForeignKey('variables.id'),
> nullable=True)
>
> value_map = Column(Text, nullable=True) #change to JSONB when move from
> SQLite to Postgres
>
> dataset = relationship('DataSet', backref='variables')
>
>
> class Value(Base):
> __tablename__ = 'values'
> id = Column(Integer, primary_key=True)
> variable_id = Column(Integer, ForeignKey('variables.id'), Nullable=False)
> observation_id = Column(Text, nullable=False)
> value_type = Column(Text, nullable=False)
>
> variable = relationship('Variable', backref='values')
>
> __mapper_args__ = {'polymorphic_on': value_type}
>
>
> class IntValue(Value):
> __tablename__ = 'int_values'
> id = Column(None, ForeignKey('values.id'), primary_key=True)
> value = Column(Integer, nullable=True)
>
> __mapper_args__ = {'polymorphic_identity': 'int'}
>
>
> class StringValue(Value):
> __tablename__ = 'string_values'
> id = Column(None, ForeignKey('values.id'), primary_key=True)
> value = Column(Text, nullable=True)
>
> __mapper_args__ = {'polymorphic_identity': 'string'}
>
>
> class FloatValue(Value):
> __tablename__ = 'float_values'
> id = Column(None, ForeignKey('values.id'), primary_key=True)
> value = Column(Float, nullable=True)
>
> __mapper_args__ = {'polymorphic_identity': 'float'}
>
>
> class DateTimeValue(Value):
> __tablename__ = 'datetime_values'
> id = Column(None, ForeignKey('values.id'), primary_key=True)
> value = Column(DateTime, nullable=True)
>
> __mapper_args__ = {'polymorphic_identity': 'datetime'}
>
> Thanks in advance for any suggestions you might have!
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
>
Re: [sqlalchemy] orm internal question - querying multiple classes
On Thu, Feb 28, 2019 at 1:32 PM Jonathan Vanasco wrote: > > I couldn't find an answer to this in the docs. > > I'm auditing a handful of queries that are in a form similar to this... > > query = session.query(Foo, Bar).join(Bar, Foo.id == Bar.foo_id) > > I think SqlAlchemy is interpreting this as: select from the leftmost entity > from `query` (Foo), joining things to it for the rest of the construct, then > populate the rest of the objects in `query` based on their appearance in the > row from the join (Bar). Is that about right? sure, though you're giving it the ON clause directly so it doesn't even have to do much, but yes it sees Bar and pulls that from the FROM list to be the right side of the join. > > I'm getting the results I expect and the sql I want, I just want to confirm > that I'm using SqlAlchemy correctly. I want to make sure that I am not > invoking things incorrectly, yet getting the right results. that's happened > before! > > > > > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Polymorphic Query All regardless of subclass
Hi All,
Trying something out here for an analytics app here, and I'm not sure any
of this is a good idea. I'm trying to create a data model that's flexible
enough to handle a lot of different types of analysis. From plain CSVs to
time series to survey questions with complex hierarchies. My approach is to
have a pretty plain model and query as needed to construct dataframes that
will end up in pandas and sci-kit learn.
The basic idea is that there's a dataset that has variables associated with
it. Variables have values associated. I want to retain type information on
the values. If I'm reading a file and loading it, I want to store an int as
an int or a datetime as a datetime and not stuff everything into a string
and have to guess what things are based on variable metadata. That's where
I'm kind of stuck. I think I have the multi-table inheritance set up
correctly to store different types of value. I'm totally unsure how to
query without having to know the subclass. I'd like to be able to extract
values for pandas processing with something like this:
vals = Values.value.filter_by(variable_id=123456).all()
without having to know per variable which subclass to choose. I.e., I don't
want to have to do
vals = IntValue.value.filter_by(variable_id=123456).all()
The second part of my question is only relevant if this whole design isn't
hosed, which I'm fine if you tell me that it is and I need to go back to
the drawing board.
The second part is how to extract an entire dataset by ID in a way that
would be comfortable in pandas. If I were doing this in raw SQL, I'd use a
CTE to get the variables by dataset id and use a pivot to create the
data-frame-shaped table. I'm confounded by how to do this in alchemy,
especially with the polymorphic setup.
Here are my current model definitions:
from sqlalchemy import Column, Index, Integer, Text, DateTime, Float,
ForeignKey
from sqlalchemy.orm import relationship
from .meta import Base
class DataSet(Base):
__tablename__ = 'datasets'
id = Column(Integer, primary_key=True)
name = Column(Text, nullable=False)
description = Column(Text, nullable=True)
class Variable(Base):
__tablename__ = 'variables'
id = Column(Integer, primary_key=True)
dataset_id = Column(Integer, ForeignKey('datasets.id'), Nullable=False)
name = Column(Text, nullable=False)
description = Column(Text, nullable=True)
group_var_col_id = Column(Integer, ForeignKey('variables.id'), nullable=
True)
group_var_row_id = Column(Integer, ForeignKey('variables.id'), nullable=
True)
value_map = Column(Text, nullable=True) #change to JSONB when move from
SQLite to Postgres
dataset = relationship('DataSet', backref='variables')
class Value(Base):
__tablename__ = 'values'
id = Column(Integer, primary_key=True)
variable_id = Column(Integer, ForeignKey('variables.id'), Nullable=False
)
observation_id = Column(Text, nullable=False)
value_type = Column(Text, nullable=False)
variable = relationship('Variable', backref='values')
__mapper_args__ = {'polymorphic_on': value_type}
class IntValue(Value):
__tablename__ = 'int_values'
id = Column(None, ForeignKey('values.id'), primary_key=True)
value = Column(Integer, nullable=True)
__mapper_args__ = {'polymorphic_identity': 'int'}
class StringValue(Value):
__tablename__ = 'string_values'
id = Column(None, ForeignKey('values.id'), primary_key=True)
value = Column(Text, nullable=True)
__mapper_args__ = {'polymorphic_identity': 'string'}
class FloatValue(Value):
__tablename__ = 'float_values'
id = Column(None, ForeignKey('values.id'), primary_key=True)
value = Column(Float, nullable=True)
__mapper_args__ = {'polymorphic_identity': 'float'}
class DateTimeValue(Value):
__tablename__ = 'datetime_values'
id = Column(None, ForeignKey('values.id'), primary_key=True)
value = Column(DateTime, nullable=True)
__mapper_args__ = {'polymorphic_identity': 'datetime'}
Thanks in advance for any suggestions you might have!
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] orm internal question - querying multiple classes
I couldn't find an answer to this in the docs. I'm auditing a handful of queries that are in a form similar to this... query = session.query(Foo, Bar).join(Bar, Foo.id == Bar.foo_id) I think SqlAlchemy is interpreting this as: select from the leftmost entity from `query` (Foo), joining things to it for the rest of the construct, then populate the rest of the objects in `query` based on their appearance in the row from the join (Bar). Is that about right? I'm getting the results I expect and the sql I want, I just want to confirm that I'm using SqlAlchemy correctly. I want to make sure that I am not invoking things incorrectly, yet getting the right results. that's happened before! -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Using primary_join with back_populates
On Wed, Feb 27, 2019 at 11:38 PM Pavel Pristupa wrote:
>
> Yes, just a typo.
> The actual problem is the following:
>
> When I try to add back_populates('user') to User.billing_addresses and
> User.shipping_addresses relationships, I get the error:
> User.billing_addresses and back-reference Address.user are both of the same
> direction . Did you mean to set remote_side on the
> many-to-one side?
once I correct for the syntactical issues I can't reproduce this
error. Please provide a proper MCVE, thanks! see below
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, configure_mappers, Session
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
billing_addresses = relationship(
"Address",
primaryjoin="and_(User.id==Address.user_id,
Address.is_billing.is_(True))",
back_populates="user",
uselist=True,
)
shipping_addresses = relationship(
"Address",
primaryjoin="and_(User.id==Address.user_id,
Address.is_billing.is_(False))",
back_populates="user",
uselist=True,
)
class Address(Base):
__tablename__ = 'address'
id = sa.Column(sa.Integer, primary_key=True)
is_billing = sa.Column(
sa.Boolean
) # Let it be a discriminator for whether it's a billing or shipping
user_id = sa.Column(sa.Integer, sa.ForeignKey("user.id"), nullable=False)
user = relationship(User)
configure_mappers()
e = sa.create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
a1, a2 = Address(), Address()
u1 = User(billing_addresses=[a1], shipping_addresses=[a2])
assert a1.user is u1
assert a2.user is u1
s.add(u1)
s.commit()
output
$ python test.py
2019-02-28 08:19:19,174 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-02-28 08:19:19,174 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,175 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-02-28 08:19:19,175 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,176 INFO sqlalchemy.engine.base.Engine PRAGMA
table_info("user")
2019-02-28 08:19:19,176 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,176 INFO sqlalchemy.engine.base.Engine PRAGMA
table_info("address")
2019-02-28 08:19:19,177 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,177 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2019-02-28 08:19:19,177 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,178 INFO sqlalchemy.engine.base.Engine COMMIT
2019-02-28 08:19:19,178 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL,
is_billing BOOLEAN,
user_id INTEGER NOT NULL,
PRIMARY KEY (id),
CHECK (is_billing IN (0, 1)),
FOREIGN KEY(user_id) REFERENCES user (id)
)
2019-02-28 08:19:19,179 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,179 INFO sqlalchemy.engine.base.Engine COMMIT
2019-02-28 08:19:19,180 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-02-28 08:19:19,181 INFO sqlalchemy.engine.base.Engine INSERT INTO
user DEFAULT VALUES
2019-02-28 08:19:19,181 INFO sqlalchemy.engine.base.Engine ()
2019-02-28 08:19:19,182 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (is_billing, user_id) VALUES (?, ?)
2019-02-28 08:19:19,182 INFO sqlalchemy.engine.base.Engine (None, 1)
2019-02-28 08:19:19,182 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (is_billing, user_id) VALUES (?, ?)
2019-02-28 08:19:19,183 INFO sqlalchemy.engine.base.Engine (None, 1)
2019-02-28 08:19:19,183 INFO sqlalchemy.engine.base.Engine COMMIT
>
> среда, 27 февраля 2019 г., 1:03:40 UTC+7 пользователь Mike Bayer написал:
>>
>> you are missing and_():
>>
>> billing_addresses = relationship('Address',
>> primary_join='and_(User.id==Address.id,
>> Address.is_billing.is_(True))', uselist=True)
>>
>>
>> On Tue, Feb 26, 2019 at 5:44 AM Pavel Pristupa wrote:
>> >
>> > Hi everybody!
>> >
>> > Is there a way to use primary_join with back_populates in the following
>> > case?
>> >
>> > I have two entities (sorry, I may be wrong with the exact syntax):
>> >
>> >
>> > class User(Base):
>> > id = sa.Column(sa.Integer, primary_key=True)
>> > billing_addresses = relationship('Address',
>> > primary_join='User.id==Address.id, Address.is_billing.is_(True)',
>> > uselist=True)
>> > shipping_addresses = relationship('Address',
>> > primary_join='User.id==Address.id, Address.is_billing.is_(False)',
>> > uselist=True)
>> >
>> >
>> > class Address(Base):
>> > id = sa.Column(sa.Integer, primary_key=True)
>> > is_billing = sa.Column(sa.Boolean) # Let it be a discriminator for
>> > whether it's a billing or shipping
>> > user_id = sa.Column(sa.Integer, sa.ForeignKey('User.id'), nullable=False)
>> > user =
