[sqlalchemy] How to combine class hierarchy and foreign keys
I am working on a simple note taking application. There are Items which are
just short pieces of text with a creation date. And there are Connections
that link two Items together.
The twist is that Connections are themselves Items, so, they have a text
and creation date and can be connected with each other as well as with
plain Items. This functionality is modelled by defining the class
Connection as a subclass of Item.
Connection has (among other things) two attributes of type Item: left and
right. Unfortunately, it turns out that mapping these classes on the
database is too tricky for me. I hope that someone can help me with this.
I am using sqlalchemy version 1.3.1 and an SQLight back end, version 3.26.0.
First, I tried Joined Table Inheritance:
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
type = Column(String(20))
creation_date = Column(Date, nullable=False)
contents = Column(String(500), nullable=False)
__mapper_args__ = {
'polymorphic_identity':'items',
'polymorphic_on':type
}
class Connection(Item):
__tablename__ = 'connections'
id = Column(Integer, ForeignKey('items.id'), primary_key=True)
left_id = Column(Integer, ForeignKey('items.id'))
left = relationship('Item', foreign_keys=[left_id])
right_id = Column(Integer, ForeignKey('items.id'))
right = relationship('Item', foreign_keys=[right_id])
__mapper_args__ = {'polymorphic_identity':'connections'}
There are multiple foreign keys from Connection to Item. I resolved the
conflicts for left and right by specifying foreign_keys in the relationship
but there is no way to do that for the id which is used for the class
hierarchy join. Running this code yields the error:
Can't determine join between 'items' and 'connections'; tables have more
than one foreign key constraint relationship between them. Please specify
the 'onclause' of this join explicitly.
I have no idea how to do that.
I also tried Single Table Inheritance, to avoid the hierarchy foreign key:
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
type = Column(String(20))
creation_date = Column(Date, nullable=False)
contents = Column(String(500), nullable=False)
__mapper_args__ = {
'polymorphic_identity':'items',
'polymorphic_on':type
}
class Connection(Item):
left_id = Column(Integer, ForeignKey('items.id'))
left = relationship('Item', foreign_keys=[left_id])
right_id = Column(Integer, ForeignKey('items.id'))
right = relationship('Item', foreign_keys=[right_id])
__mapper_args__ = {'polymorphic_identity':'connections'}
Alas, this also results in an error:
Incompatible collection type: Item is not list-like.
I do not understand this at all, as there are no back_populates from
Connection to Item, so why should it be list like?
Please, help me!
Thank you very much,
Huub
--
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] user-defined simple fields in a multi-tenant flask app with ORM
Hi Jonathan, i would do it like this: - add in your global model tables named attributs, attributs_value and values, - any table (let's call it XTable) that will eventually has need extra column per client will be linked to attributs_value via a table XTable_Attributs (For maximum flexibility). Hope that helps, Best regards Ibrahima GAYE Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco a écrit : > > > On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote: >> >> >> > Simpler solutions would be just using hstore or JSON types, but I would >> be loosing the goodies of SQLAlchemy / Postgres schemas and consistency. >> >> this is totally how I'd want to do it unless your clients are >> given access to program in SQL and SQLAlchemy. >> > > wile I would handle this as JSON data too, there is also a database > pattern for doing this in multi tenant applications where you use a table > to allocate and store the allowable keys for each tenant , and another > table to store the key values for the tenants objects. but i would do this > in JSON. > > > -- > 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.
Re: [sqlalchemy] Re: Using operators with multiple InstrumentedAttribute instances
On Thursday, March 28, 2019 at 6:27:52 PM UTC-4, Mike Bayer wrote: > > > is this some standard thing you're both doing? I didn't see anything > about joins or query analyzing.you often have answers for > questions where I don't understand what theyre asking! > Well his question and the example code look a lot of some things i've encountered before... so I'm fairly confident I know what he's intending to do on a higher level (vs what he's actually doing in this code). I've had to do similar things where you allow "website users, usually marketing / audience / analytics" select a handful of metrics to generate a custom report. in his example, it's clear to me these 3 'metric' items respond to the table/columns... formula = '"metric:123" + "metric:456" + "metric:789"' SELECT post.id + campaign.id + asset.id FROM post, campaign, asset so then there's got to be a way to join the 3 tables together. you actually helped me on something similar a long time ago... and I think there's still an open ticket or two in the SqlAlchemy backlog regarding this. I was trying to inspect dynamically build queries to figure out if a table was joined yet or not or if a column was already queried. -- 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] Re: Using operators with multiple InstrumentedAttribute instances
On Thu, Mar 28, 2019 at 5:33 PM Jonathan Vanasco wrote: > > I gave up on attempts to do something similar a while back, because it became > to problematic to examine all the SqlAlchemy objects – and the existing query > – in an effort to construct the joins and query correctly. > > I would up using a two-phase approach. phase 1 analyzes the 'requested > metrics' to figure out which tables and columns are needed, and raises an > error if things look bad. phase 2 generates the query. I use a python dict > to store metadata about the query as it is analyzed, using the tables as keys > and building an array of the columns - this way i only join the table once. > based on what tables are needed in the dict, or other data on the metrics I > pre-calculate, i know how to structure the joins. this approach is somewhat > restricting, but works very well, is quick to deploy and easy to maintain. is this some standard thing you're both doing? I didn't see anything about joins or query analyzing.you often have answers for questions where I don't understand what theyre asking! > > -- > 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.
Re: [sqlalchemy] Using operators with multiple InstrumentedAttribute instances
On Thu, Mar 28, 2019 at 4:43 PM Ian Miller wrote: > > Hello all, > > I am in the process of trying to create a dynamic expression query engine in > an application I'm working on. > > So there is a formula that gets defined like so: > > formula = '"metric:123" + "metric:456" + "metric:789"' > > Each metric maps to a column in the database tables - long story short, I'm > able to retrieve the metric by ID, and instantiate an InstrumentedAttribute > object that has the SQLAlchemy metadata for the associated column. What I'm > trying to achieve is to be able to iterate through the formula, and > dynamically build a SQLALchemy query that maps to the formula. this seems a bit vague, "instantiate an InstrumentedAttribute" is not exactly a public SQLAlchemy API, these are created internally as part of the mapping process, so I wouldn't know exactly how you're going about "instantiating" these, not like it isn't possible but normally if youre generating dynamic SQL you'd just be building with column() objects. > > For example, the formula defined above would look something like this in SQL: > > SELECT post.id + campaign.id + asset.id > FROM post, campaign, asset > WHERE ..; > > The idea is to translate the above to something like: > > session.query( 0x7ff9269f92b0> + 0x7ff9269c5990> + 0x7ff926896048>).all() this also puzzles me. you're saying you have a list of InstrumentedAttribute objects. so...the above would be, "session.query(sum(my_list_of_attributes[1:], my_list_of_attributes[0])).all()" . or something similar just applying the + operator to the items. is that what you're looking for ? > > I've tried a couple of approaches of dynamically generating the SQLAlchemy > ORM query, but I haven't been able to find anything that works. Would anyone > have any idea or tips on how to accomplish this? > > Thank 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+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] Re: Using operators with multiple InstrumentedAttribute instances
I gave up on attempts to do something similar a while back, because it became to problematic to examine all the SqlAlchemy objects – and the existing query – in an effort to construct the joins and query correctly. I would up using a two-phase approach. phase 1 analyzes the 'requested metrics' to figure out which tables and columns are needed, and raises an error if things look bad. phase 2 generates the query. I use a python dict to store metadata about the query as it is analyzed, using the tables as keys and building an array of the columns - this way i only join the table once. based on what tables are needed in the dict, or other data on the metrics I pre-calculate, i know how to structure the joins. this approach is somewhat restricting, but works very well, is quick to deploy and easy to maintain. -- 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] Using operators with multiple InstrumentedAttribute instances
Hello all, I am in the process of trying to create a dynamic expression query engine in an application I'm working on. So there is a formula that gets defined like so: formula = '"metric:123" + "metric:456" + "metric:789"' Each metric maps to a column in the database tables - long story short, I'm able to retrieve the metric by ID, and instantiate an InstrumentedAttribute object that has the SQLAlchemy metadata for the associated column. What I'm trying to achieve is to be able to iterate through the formula, and dynamically build a SQLALchemy query that maps to the formula. For example, the formula defined above would look something like this in SQL: SELECT post.id + campaign.id + asset.id FROM post, campaign, asset WHERE ..; The idea is to translate the above to something like: session.query( + + ).all() I've tried a couple of approaches of dynamically generating the SQLAlchemy ORM query, but I haven't been able to find anything that works. Would anyone have any idea or tips on how to accomplish this? Thank 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+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] user-defined simple fields in a multi-tenant flask app with ORM
On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote: > > > > Simpler solutions would be just using hstore or JSON types, but I would > be loosing the goodies of SQLAlchemy / Postgres schemas and consistency. > > this is totally how I'd want to do it unless your clients are given access > to program in SQL and SQLAlchemy. > wile I would handle this as JSON data too, there is also a database pattern for doing this in multi tenant applications where you use a table to allocate and store the allowable keys for each tenant , and another table to store the key values for the tenants objects. but i would do this in JSON. -- 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] user-defined simple fields in a multi-tenant flask app with ORM
On Thu, Mar 28, 2019 at 8:27 AM Xavier Bustamante Talavera wrote: > > Hello, > > Thanks in advance for the help. > > I am using the ORM with SQLAlchemy in a flask app that is multi-tenant > (multi-client): I have several running Flask apps, one per client. Each flask > app connects to a different Postgres schema. I use one declarative base for > all clients, as they share the same ORM classes, and then every time flask > creates a new session it connects to the client schema. I create the tables > using the ORM in python, in a way based in this issue: > https://github.com/sqlalchemy/sqlalchemy/issues/3914 > > A new requirement is client-defined custom fields. So, our ORM classes will > have their regularly defined fields in sqlalchemy ORM manner in python, plus > some client-defined fields. The client-defined fields are regular types > without difficult stuff: like strings, numbers... As every client is in its > own schema, an example of approach could be creating those fields in the > table in the db directly, and then reflect them on the ORM at the beginning > of each session (as we need to know the schema). I think this would work really poorly. Reflection is slow, mapping configuration is slow, tearing down of ORM models is slow and not optimized for any cases outside of test suites, all of this would add mulit-second latency to every request, and you would need to prevent all concurrency from each process as you are attempting to use the same ORM model in the process. options here include some kind of module-level trickery where each client gets their own SQLAlchemy model into a private module namespace in the process that is copied from the main one, which would be extremely difficult to get right, or to just give each client their own flask application process,which wont scale, depends on how many tenants you are talking about. > Simpler solutions would be just using hstore or JSON types, but I would be > loosing the goodies of SQLAlchemy / Postgres schemas and consistency. this is totally how I'd want to do it unless your clients are given access to program in SQL and SQLAlchemy. What is a real-world scenario where you have given a tenant three additional columns on one of the database tables and the tenants need to use that data ? what would that look like and what would you be doing that is any different from pulling those values from an hstore ? > > This could be done in different ways in SQLAlchemy, so I am asking you to > orient me to a solution, guide, tutorial, or advice to avoid known pitfalls > when doing this. For example some steps in how to build this reflection, if > you think it is a good solution. > > A future requirement will be creating client-defined tables that inherit from > our regular ORM ones. If for example, we have "Computer" ORM class, they > would want to define types of Computers, like "Desktop". These classes would > only be collections of client-defined fields. How would this impact the above > solution? again this sounds like your application just needs to have a Python package per client where each one has its own version of the ORM model. > > And finally, I will learn database migrations with Alembic, so any piece of > advice or link to manage this with Alembic would be appreciated. all the same, separate alembic migration directories per client. sorry > > This is for open-source software, so if we manage to do it we are happy to > write a blog post anywhere (we have a small blog site for example), if you > find it can be useful for others. > > Thank you for your time (and this amazing software)! :-) > > -- > 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://gr
[sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM
Hello, Thanks in advance for the help. I am using the ORM with SQLAlchemy in a flask app that is multi-tenant (multi-client): I have several running Flask apps, one per client. Each flask app connects to a different Postgres schema. I use one declarative base for all clients, as they share the same ORM classes, and then every time flask creates a new session it connects to the client schema. I create the tables using the ORM in python, in a way based in this issue: https://github.com/sqlalchemy/sqlalchemy/issues/3914 A new requirement is client-defined custom fields. So, our ORM classes will have their regularly defined fields in sqlalchemy ORM manner in python, plus some client-defined fields. The client-defined fields are regular types without difficult stuff: like strings, numbers... As every client is in its own schema, an example of approach could be creating those fields in the table in the db directly, and then reflect them on the ORM at the beginning of each session (as we need to know the schema). Simpler solutions would be just using hstore or JSON types, but I would be loosing the goodies of SQLAlchemy / Postgres schemas and consistency. This could be done in different ways in SQLAlchemy, so I am asking you to orient me to a solution, guide, tutorial, or advice to avoid known pitfalls when doing this. For example some steps in how to build this reflection, if you think it is a good solution. A future requirement will be creating client-defined tables that inherit from our regular ORM ones. If for example, we have "Computer" ORM class, they would want to define types of Computers, like "Desktop". These classes would only be collections of client-defined fields. How would this impact the above solution? And finally, I will learn database migrations with Alembic, so any piece of advice or link to manage this with Alembic would be appreciated. This is for open-source software, so if we manage to do it we are happy to write a blog post anywhere (we have a small blog site for example), if you find it can be useful for others. Thank you for your time (and this amazing software)! :-) -- 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.
