Re: [sqlalchemy] Doesnt execute any thing at new pc
On 9/12/23 12:31 AM, FURKAN bilgin wrote: Hi, I updated my PC and downloaded my old codes. However, the SQLAlchemy execute function is not working. but it works on Ubuntu. The code is basically like this: My first suggestion is to make sure the directory path points to something that actually exists. Your "username" may have changed. -- Richard Damon -- 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/12f40a81-3c37-4c9a-a807-8a2f5167be98%40Damon-Family.org.
Re: [sqlalchemy] Many-to-many cascade delete
Fundamentally the cascade applies to doing something to the 'many' side when something happens on the 'one' side. A many-to-many association can't do this sort of things, and in fact, you can't just 'create' a many-to-many relationship in a relational database. To implement a many-to-many relationship, you need an intermediary table that is many-to-one with each of the two sides to build that many-to-many relationship. You could use a cascade to update this interconnection table, to delete the cross connection terms when you delete one side of a relationship (and if you don't you can't delete the items without first removing all the relationships). By itself, this doesn't help you, but you could create a trigger on deleting this crossing element, and have it check if after it is gone if the secret it was connected to is now no longer referenced, and if so delete the secret. On 11/2/21 4:49 PM, Anders Buch wrote: Hello All, Suppose I have a table of people, a table of secrets, and a many-to-many association table between them. A person can exist without knowing any secrets, but a secret without any associated people might as well be deleted. My reading of the documentation is that it is not possible to set cascade options to obtain this behavior? (Because a cascade delete option would cause a secret to be deleted if any person forgets about it?) If correct, is there another elegant way to achieve this? Thanks in advance! Anders Buch -- 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/b5e4faee-5cd6-4b9b-936b-0d80458f55e4n%40googlegroups.com <https://groups.google.com/d/msgid/sqlalchemy/b5e4faee-5cd6-4b9b-936b-0d80458f55e4n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- Richard Damon -- 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/35a2e6f8-d375-2054-fd9a-758fdf2f227d%40Damon-Family.org.
Re: [sqlalchemy] Changing Type of an ORM record
On 5/2/21 6:34 PM, Mike Bayer wrote: > > > On Sun, May 2, 2021, at 4:44 PM, Richard Damon wrote: >> I asked this a bit ago, but never got an answer, so trying again wording >> a bit different to see if I can get help. > > sorry if this got missed. > >> >> The question is, given an existing record for an 'Employee', how to I >> change it from an Employee to say an Engineer. I don't want to make a >> 'new' record with a new ID number, as the id number is referenced in >> other tables. >> >> >> Not using ORM, it would be a simple matter of writing the data into the >> Engineer table with an INSERT, forcing the ID to match the ID of the >> employee, and then change the value of the type field in the Employee >> table with an UPDATE. The question is, is there a more "ORM' way to >> do this? > > this request comes up from time to time however the Core method you > refer towards is the best way to do this. This kind of operation is > unusual enough that it's simpler for users to write the Core routine > they want for their needs rather than adding a complex generalized > feature to the ORM that would not be used often and would be difficult > to develop and support. Ok, understand. Maybe my application is a bit unusual, but it seems I will be doing this a LOT. In my case nothing will REALLY be the base class, but many of the sub-classes will have relations referencing the id number of in the base class, and when importing a change set from an external source, those id numbers might not match, so the base class includes a UUID to match things up, and to avoid foreign key errors in some of the derived objects, it seems I want to first make a pass to create all the new nodes as just the base class so they don't create broken relationships, and then upgrade them to their final type so I can fill in the relationships. Since many of the relationships will by cycles, I can't start at the base and build up. I presume that after changing the base record I should tell SQLAlchemy to flush the old record out of its cache so it will re-read it with its new identity. -- Richard Damon -- 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/d7b66178-52bd-60cc-b8bd-655d46e535af%40Damon-Family.org.
[sqlalchemy] Changing Type of an ORM record
I asked this a bit ago, but never got an answer, so trying again wording
a bit different to see if I can get help.
Going to use the example from the documentation, as hopefully that will
give me the hints needed to handle my more complicate case.
Using SQLAlchemy 1.4 ORM and Joined Table Inheritance
# Define Base Class for Inheritance
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
# Define some Sub-classes
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'engineer',
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'manager',
}
The question is, given an existing record for an 'Employee', how to I
change it from an Employee to say an Engineer. I don't want to make a
'new' record with a new ID number, as the id number is referenced in
other tables.
Not using ORM, it would be a simple matter of writing the data into the
Engineer table with an INSERT, forcing the ID to match the ID of the
employee, and then change the value of the type field in the Employee
table with an UPDATE. The question is, is there a more "ORM' way to do this?
--
Richard Damon
--
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/c7b852a4-8d94-6341-c1ad-376051244059%40Damon-Family.org.
[sqlalchemy] Upgrading an ORM object from a base class to a derived class.
Using SQLAlchemy 1.4 latest, intending to keep upgrading and moving to 2.x Using ORM with Joined Table Inheritance. Have an object in the database as an instance of the base class, which has a lot of data in it, and want to upgrade it to be an instance of derived class. So in the examples, promote an plain Employee to be an Engineer or a Manager. If not using ORM and willing to just duplicate code, I would just write the new data to the associated derived table and then change the type field in the base table, but this seem 'crude'. Is there a better way to let me use the ORM structures to actually do some of the work? I don't think I can just create a new derived object with the original primary key, as that will get rejected as a duplicate key, not replace the object. -- Richard Damon -- 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/b3392a90-5e70-33c3-3955-8bc8f1299916%40Damon-Family.org.
Re: [sqlalchemy] How to refer to columns whose names begin with a number when autoloading?
On 4/12/21 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
>
> SELECTTOP 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
Have you tried using getattr? That might work (if SQLAlchemy isn't doing
something to quote the name to make a valid version.
getattr(self, '1st_period') would be the equivalent of self.1st_period,
but not have the name parsed by Python.
--
Richard Damon
--
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/40956a6a-5de8-67d9-a42a-fdd45d173b6d%40Damon-Family.org.
Re: [sqlalchemy] Using Abstract Base Classes with ORM Table Classes
First, just recently switched my developement form 1.3 to 1.4 based on comments of time line, since I suspect I won't be done till next year, will likely be releasing with 2.0. (I set the migration warnings described in the porting guide). Not sure I can use the class decorator, as I am using a recipie that adds a declare_attr for __tablename__ to declarative_base to default to the class name. Looking on that page, it does look like from "Creating an Explicit Base Non-Dynamically" that I could add a __abstract__ = True to the class to get around the issue of declarative_base trying to make a real table out of the intermediate class to provide the common code. (Looks like this was in 1.3 too, but didn't see it). On 3/15/21 8:16 AM, Mike Bayer wrote: > you no longer have to use DeclarativeMeta at all, you can use a class > decorator: > > https://docs.sqlalchemy.org/en/14/orm/mapping_styles.html#declarative-mapping-using-a-decorator-no-declarative-base > <https://docs.sqlalchemy.org/en/14/orm/mapping_styles.html#declarative-mapping-using-a-decorator-no-declarative-base> > > > if you are on 1.3, there's a way to get the same effect in 1.3 using > the instrument_declarative function: > https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html?highlight=instrument_declarative#sqlalchemy.ext.declarative.instrument_declarative > <https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html?highlight=instrument_declarative#sqlalchemy.ext.declarative.instrument_declarative> > > > which can be turned into an equivalent decorator. > > that said I have not yet experimented with mapping classes that are > also extending ABCMeta so I'm not sure if there are other issues. > > > > On Sun, Mar 14, 2021, at 8:53 PM, Richard Damon wrote: >> I have a lot of tables that have some similar functionality that I would >> like to factor out into a base mix-in class that provides some common >> methods. Some of these methods will want to use a method that must be >> defined in the actual table ORM class, and would be an abstract method >> in the base. If you just blindly do this you get the Python error of >> multiple metaclasses, so I need to define a metaclass that inherets from >> both ABCMeta and DeclarativeMeta, and then the Table ORM classes need to >> mention declarative_base, the mixin and metaclass=mymeta. This works but >> looks wordy. >> >> If instead I try to put the mix-in between declarative_base and the >> table class in the heirarchy, SQLAlchemy complains that it is missing >> information for it to be a table (which is correct). If I put it as a >> base to declarative_base the SQLAlchemy gets errors that it needs to >> implement the abstract methods (and I of course can only do this once). >> >> I could make the mixin not use the ABCMeta as its metaclass, but then if >> I forget to define the abstract method in the table, I get no >> complaints, at best I could catch the call to the abstract method >> because it wasn't overridden. >> >> Is this the way it is supposed to work, or am I missing some other trick? >> >> Side question, when doing this sort of mix-in, does the order of the >> mix-in and declarative_base matter, or is there a real preference? >> >> -- >> Richard Damon >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve >> <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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To view this discussion on the web >> visit >> https://groups.google.com/d/msgid/sqlalchemy/e8d4c401-95fe-957f-7d65-3e37cd5150c6%40Damon-Family.org >> <https://groups.google.com/d/msgid/sqlalchemy/e8d4c401-95fe-957f-7d65-3e37cd5150c6%40Damon-Family.org>. >> > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <http://stackoverflow.com/help/mcve> for a full description. > --- > You re
[sqlalchemy] Using Abstract Base Classes with ORM Table Classes
I have a lot of tables that have some similar functionality that I would like to factor out into a base mix-in class that provides some common methods. Some of these methods will want to use a method that must be defined in the actual table ORM class, and would be an abstract method in the base. If you just blindly do this you get the Python error of multiple metaclasses, so I need to define a metaclass that inherets from both ABCMeta and DeclarativeMeta, and then the Table ORM classes need to mention declarative_base, the mixin and metaclass=mymeta. This works but looks wordy. If instead I try to put the mix-in between declarative_base and the table class in the heirarchy, SQLAlchemy complains that it is missing information for it to be a table (which is correct). If I put it as a base to declarative_base the SQLAlchemy gets errors that it needs to implement the abstract methods (and I of course can only do this once). I could make the mixin not use the ABCMeta as its metaclass, but then if I forget to define the abstract method in the table, I get no complaints, at best I could catch the call to the abstract method because it wasn't overridden. Is this the way it is supposed to work, or am I missing some other trick? Side question, when doing this sort of mix-in, does the order of the mix-in and declarative_base matter, or is there a real preference? -- Richard Damon -- 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/e8d4c401-95fe-957f-7d65-3e37cd5150c6%40Damon-Family.org.
Re: [sqlalchemy] ORM and objects with properties that need conversion to write to database
Thank you, that looks like what I was looking for but didn't know what it was called. On 10/8/20 4:55 AM, Simon King wrote: > On Thu, Oct 8, 2020 at 3:38 AM Richard Damon wrote: >> I am working on a app using SQLAlchemy's ORM layer to interface to the >> database, but I am running into an issue that if an object has >> 'complicated' property, like a UUID, that SQLAlchemy doesn't know how to >> handle. One option would be to make the propery actually only hold a >> database suitable representation, and I see an option to add a >> 'reconstructor' to convert a value read from the database into an >> object. Is there a similar way that when writing the object, to indicate >> how to convert the object into a format that can be put into the database? >> > The usual pattern is to create a custom datatype using > sqlalchemy.types.TypeDecorator. You would override the > process_bind_baram and process_result_value methods to convert objects > between the python and database representations: > > https://docs.sqlalchemy.org/en/13/core/custom_types.html#augmenting-existing-types > > For the specific case of UUIDs, there is an example in the docs that > will use Postgres' UUID type against postgres, and CHAR against other > databases: > > https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type > > Hope that helps, > > Simon > -- Richard Damon -- 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/9a984aed-26d1-5c13-d1b5-05529e58b2db%40Damon-Family.org.
[sqlalchemy] ORM and objects with properties that need conversion to write to database
I am working on a app using SQLAlchemy's ORM layer to interface to the database, but I am running into an issue that if an object has 'complicated' property, like a UUID, that SQLAlchemy doesn't know how to handle. One option would be to make the propery actually only hold a database suitable representation, and I see an option to add a 'reconstructor' to convert a value read from the database into an object. Is there a similar way that when writing the object, to indicate how to convert the object into a format that can be put into the database? -- Richard Damon -- 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/a67e28b7-cd9e-174d-2f6e-cd38c9122f7d%40Damon-Family.org.
Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
On 9/8/20 8:02 PM, Mark Aquino wrote: > I’m not using that FK for inheritance though. I’m just relating one > type of tracked entity to another (it’s parent, basically). After I > did this it actually broke my code so it didn’t really work (it just > temporarily got rid of one error and caused a more complicated one) > I think you need to post the basics of the code. See the link below about making it a MCVE There is obviously something you aren't describing, or we need to see to point to you what you are missing. -- Richard Damon -- 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/414307fa-8779-5be1-244c-57c1cf9d7a02%40Damon-Family.org.
Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
The key point is that the derived class needs a pointer to its base
class for the inheritance, and if it has another one to represent object
linkage, then the ORM module doesn't know which one is which, in my case
even though they were all called node_id, the fact one of the classes
had another reference it didn't know which to use, thus you need to put
a __mapper_args__ with an entry for "inherit_condition" to indicate
which on to use for inheritance. Once you resolve the inheritance
problem, the rest can be used for object relationships.
On 9/8/20 4:50 PM, maqui...@gmail.com wrote:
> I'm having the same problem,
> I have a base class called TrackedEntity that has child classes like
> Request and others that inherit from it
>
> on Request I wanted to put a reference to the id of the TrackedEntity
> that created the Request
> class Request(TrackedEntity, TrackedEntityContainer, VisibleIdMixin):
> parent_tracked_entity_id = Column(UUID,
> ForeignKey("tracked_entity.id"))
>
> and I get the same error as above. Adding that inherit condition
> makes the runtime error stop, but it doesn't make sense to me. Why
> can't I just have a foreign key to that table? It's a simple many to one
>
>
> @Richard: you can use @declared_attr.cascading to cascade the
> mapper_args to your child classes.
> On Friday, August 28, 2020 at 2:56:02 PM UTC-4 Richard Damon wrote:
>
> Thank you, so that go into each subclass that would have the problem.
>
> 8/28/20 2:37 PM, Mike Bayer wrote:
> > the argument you're looking for is inherit_condition:
> >
> >
>
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
>
> >
> >
> > class Foo(...):
> > __mapper_args__ = {
> > "inherit_condition": node_id == Node.node_id
> > }
> >
>
> --
> Richard Damon
>
--
Richard Damon
--
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/8f002a3f-fb68-5f8b-c921-2dddaceb1119%40Damon-Family.org.
Re: [sqlalchemy] ORM 3 level hieracrchy
It looks like I can keep the @declared_attr by using
"inherit_condition": cls.node_id == cls.__mro__[1].node_id
The __mro__[1] gets the leftmost (or only) parent class. As long as I
make sure that it the inheritance tree (and I wasn't planning on
multiple inheritance here) I should be ok.
On 9/3/20 10:25 AM, Mike Bayer wrote:
> yup that was the idea
>
>
> On Thu, Sep 3, 2020, at 10:24 AM, Richard Damon wrote:
>> I have a large number (around a dozen or more, and likely to grow) of
>> derived classes, so I was hoping to cut down repetition with the
>> @declared_attr.
>> So, 3rd (or farther) derived classes need the inherit_condition to point
>> to their immediate base. That does seem to remove the warning.
>>
>> On 9/3/20 9:58 AM, Mike Bayer wrote:
>> > you might be able to use the declared_attr __mapper__ but you would
>> > need to omit that erroneous inherit condition if the class is
>> > "Name". IMO it would be easier to follow and understand by simply
>> > using explicit __mapper_args__ on each class but this depends on what
>> > you're doing.
>> >
>> >
>> >
>> > On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
>> >> I've tried taking my code and changing the ForeignKey to be to
>> Node, and
>> >> that doesn't change the Warning.
>> >> Is the problem trying to DRY with the @declared_attr __mapper__?
>> >>
>> >> On 9/2/20 11:29 PM, Mike Bayer wrote:
>> >> > well you are giving Name an inherit condition that conflicts
>> with how
>> >> > you set up the foreign key.
>> >> >
>> >> > Name.node_id FKs to Property.node_id
>> >> >
>> >> > but then inherit condition is Name.node_id -> Node.node_id
>> >> >
>> >> > There seems to be a little unsmoothness to actually being able to
>> >> > configure it that way, that is, skipping over Property.node_id,
>> but in
>> >> > this case your FKs make it clear what you're going for which is the
>> >> > "normal' setup of Name->Property->Node so you can remove "inherit
>> >> > condition" from Name and it works fine:
>> >> >
>> >> > class Name(Property):
>> >> > __tablename__ = 'Name'
>> >> >
>> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> >> > primary_key=True)
>> >> >
>> >> > __mapper_args__ = {
>> >> > "polymorphic_identity": "Name",
>> >> > }
>> >> >
>> >> >
>> >> > or set it:
>> >> >
>> >> > class Name(Property):
>> >> > __tablename__ = 'Name'
>> >> >
>> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> >> > primary_key=True)
>> >> >
>> >> > __mapper_args__ = {
>> >> > "polymorphic_identity": "Name",
>> >> > "inherit_condition": node_id == Property.node_id,
>> >> > }
>> >> >
>> >> >
>> >> >
>> >> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> >> >> Here is the code, note in all cases node_id are foreign
>> >> key/primary_key
>> >> >> to a primary_key down the chain:
>> >> >>
>> >> >>
>> >> >> class Base:
>> >> >> """Base Class for SQLAlchemy ORM Classes"""
>> >> >> @declared_attr
>> >> >> def __tablename__(cls):
>> >> >> """Default the Table Name to the Class Name"""
>> >> >> return cls.__name__
>> >> >>
>> >> >> Base = declarative_base(cls=Base)
>> >> >>
>> >> >> class Node(Base):
>> >> >> """Class repesents the base of the User Data types."""
>> >> >> node_id = Column(Integer, primary_key=True)
>> >> >> type_name = Column(String(255), nullable=False) # todo should
>> >> come
>> >> >> from type_id
>> >> >>
>> >> >> @declared_attr
>> >> >> def __mapper_args__(cls
Re: [sqlalchemy] ORM 3 level hieracrchy
I have a large number (around a dozen or more, and likely to grow) of
derived classes, so I was hoping to cut down repetition with the
@declared_attr.
So, 3rd (or farther) derived classes need the inherit_condition to point
to their immediate base. That does seem to remove the warning.
On 9/3/20 9:58 AM, Mike Bayer wrote:
> you might be able to use the declared_attr __mapper__ but you would
> need to omit that erroneous inherit condition if the class is
> "Name". IMO it would be easier to follow and understand by simply
> using explicit __mapper_args__ on each class but this depends on what
> you're doing.
>
>
>
> On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
>> I've tried taking my code and changing the ForeignKey to be to Node, and
>> that doesn't change the Warning.
>> Is the problem trying to DRY with the @declared_attr __mapper__?
>>
>> On 9/2/20 11:29 PM, Mike Bayer wrote:
>> > well you are giving Name an inherit condition that conflicts with how
>> > you set up the foreign key.
>> >
>> > Name.node_id FKs to Property.node_id
>> >
>> > but then inherit condition is Name.node_id -> Node.node_id
>> >
>> > There seems to be a little unsmoothness to actually being able to
>> > configure it that way, that is, skipping over Property.node_id, but in
>> > this case your FKs make it clear what you're going for which is the
>> > "normal' setup of Name->Property->Node so you can remove "inherit
>> > condition" from Name and it works fine:
>> >
>> > class Name(Property):
>> > __tablename__ = 'Name'
>> >
>> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> > primary_key=True)
>> >
>> > __mapper_args__ = {
>> > "polymorphic_identity": "Name",
>> > }
>> >
>> >
>> > or set it:
>> >
>> > class Name(Property):
>> > __tablename__ = 'Name'
>> >
>> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> > primary_key=True)
>> >
>> > __mapper_args__ = {
>> > "polymorphic_identity": "Name",
>> > "inherit_condition": node_id == Property.node_id,
>> > }
>> >
>> >
>> >
>> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> >> Here is the code, note in all cases node_id are foreign
>> key/primary_key
>> >> to a primary_key down the chain:
>> >>
>> >>
>> >> class Base:
>> >> """Base Class for SQLAlchemy ORM Classes"""
>> >> @declared_attr
>> >> def __tablename__(cls):
>> >> """Default the Table Name to the Class Name"""
>> >> return cls.__name__
>> >>
>> >> Base = declarative_base(cls=Base)
>> >>
>> >> class Node(Base):
>> >> """Class repesents the base of the User Data types."""
>> >> node_id = Column(Integer, primary_key=True)
>> >> type_name = Column(String(255), nullable=False) # todo should
>> come
>> >> from type_id
>> >>
>> >> @declared_attr
>> >> def __mapper_args__(cls):
>> >> if cls.__name__ == 'Node' :
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': 'Node',
>> >> 'polymorphic_on': cls.type_name,
>> >> }
>> >> else:
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': cls.__tablename__,
>> >> "inherit_condition": cls.node_id == Node.node_id
>> >> }
>> >> return __mapper_args__
>> >>
>> >> class Property(Node):
>> >> node_id = Column(Integer, ForeignKey('Node.node_id'),
>> >> primary_key=True)
>> >> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>> >>
>> >> class Name(Property):
>> >> node_id = Column(Integer, ForeignKey('Property.node_id'),
>> >> primary_key=True)
>> >>
>> >>
>> >> On 9/2/20 9:39 PM, Mike Bayer wrote:
>> >> > there'
Re: [sqlalchemy] ORM 3 level hieracrchy
I've tried taking my code and changing the ForeignKey to be to Node, and
that doesn't change the Warning.
Is the problem trying to DRY with the @declared_attr __mapper__?
On 9/2/20 11:29 PM, Mike Bayer wrote:
> well you are giving Name an inherit condition that conflicts with how
> you set up the foreign key.
>
> Name.node_id FKs to Property.node_id
>
> but then inherit condition is Name.node_id -> Node.node_id
>
> There seems to be a little unsmoothness to actually being able to
> configure it that way, that is, skipping over Property.node_id, but in
> this case your FKs make it clear what you're going for which is the
> "normal' setup of Name->Property->Node so you can remove "inherit
> condition" from Name and it works fine:
>
> class Name(Property):
> __tablename__ = 'Name'
>
> node_id = Column(Integer, ForeignKey("Property.node_id"),
> primary_key=True)
>
> __mapper_args__ = {
> "polymorphic_identity": "Name",
> }
>
>
> or set it:
>
> class Name(Property):
> __tablename__ = 'Name'
>
> node_id = Column(Integer, ForeignKey("Property.node_id"),
> primary_key=True)
>
> __mapper_args__ = {
> "polymorphic_identity": "Name",
> "inherit_condition": node_id == Property.node_id,
> }
>
>
>
> On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> Here is the code, note in all cases node_id are foreign key/primary_key
>> to a primary_key down the chain:
>>
>>
>> class Base:
>> """Base Class for SQLAlchemy ORM Classes"""
>> @declared_attr
>> def __tablename__(cls):
>> """Default the Table Name to the Class Name"""
>> return cls.__name__
>>
>> Base = declarative_base(cls=Base)
>>
>> class Node(Base):
>> """Class repesents the base of the User Data types."""
>> node_id = Column(Integer, primary_key=True)
>> type_name = Column(String(255), nullable=False) # todo should come
>> from type_id
>>
>> @declared_attr
>> def __mapper_args__(cls):
>> if cls.__name__ == 'Node' :
>> __mapper_args__ = {
>> 'polymorphic_identity': 'Node',
>> 'polymorphic_on': cls.type_name,
>> }
>> else:
>> __mapper_args__ = {
>> 'polymorphic_identity': cls.__tablename__,
>> "inherit_condition": cls.node_id == Node.node_id
>> }
>> return __mapper_args__
>>
>> class Property(Node):
>> node_id = Column(Integer, ForeignKey('Node.node_id'),
>> primary_key=True)
>> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>>
>> class Name(Property):
>> node_id = Column(Integer, ForeignKey('Property.node_id'),
>> primary_key=True)
>>
>>
>> On 9/2/20 9:39 PM, Mike Bayer wrote:
>> > there's an FAQ entry, a little bit dated but the general idea is still
>> > there, at:
>> >
>> >
>> >https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
>> >
>> > for joined table inheritance, where Name(Node) -> node_id are FK ->
>> > PK, the warning isn't emitted. so please share the mapping if it is
>> > doing this when it shouldnt.
>> >
>> >
>> > On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
>> >> I am getting the following error:
>> >>
>> >> SAWarning: Implicitly combining column Node.node_id with column
>> >> Name.node_id under attribute 'node_id'. Please configure one or more
>> >> attributes for these same-named columns explicitly.
>> >>
>> >> In my case I am using poymorphic classes by joining with the ORM. Node
>> >> is the base of the hierarchy (derived from declarative_base), then I
>> >> have a class Property derived from it, and a class Name derived from
>> >> Property. Each class has a primary_key named node_id, with a
>> foreign key
>> >> constraint one step done the hierarchy. I have also tried making
>> all the
>> >> Foreign key constraints point to Node.node_id and it doesn't make a
>> >> difference.
>> >
Re: [sqlalchemy] ORM 3 level hieracrchy
Here is the code, note in all cases node_id are foreign key/primary_key
to a primary_key down the chain:
class Base:
"""Base Class for SQLAlchemy ORM Classes"""
@declared_attr
def __tablename__(cls):
"""Default the Table Name to the Class Name"""
return cls.__name__
Base = declarative_base(cls=Base)
class Node(Base):
"""Class repesents the base of the User Data types."""
node_id = Column(Integer, primary_key=True)
type_name = Column(String(255), nullable=False) # todo should come
from type_id
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'Node' :
__mapper_args__ = {
'polymorphic_identity': 'Node',
'polymorphic_on': cls.type_name,
}
else:
__mapper_args__ = {
'polymorphic_identity': cls.__tablename__,
"inherit_condition": cls.node_id == Node.node_id
}
return __mapper_args__
class Property(Node):
node_id = Column(Integer, ForeignKey('Node.node_id'), primary_key=True)
ref_id = Column(Integer, ForeignKey('Node.node_id'))
class Name(Property):
node_id = Column(Integer, ForeignKey('Property.node_id'),
primary_key=True)
On 9/2/20 9:39 PM, Mike Bayer wrote:
> there's an FAQ entry, a little bit dated but the general idea is still
> there, at:
>
> https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
>
> for joined table inheritance, where Name(Node) -> node_id are FK ->
> PK, the warning isn't emitted. so please share the mapping if it is
> doing this when it shouldnt.
>
>
> On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
>> I am getting the following error:
>>
>> SAWarning: Implicitly combining column Node.node_id with column
>> Name.node_id under attribute 'node_id'. Please configure one or more
>> attributes for these same-named columns explicitly.
>>
>> In my case I am using poymorphic classes by joining with the ORM. Node
>> is the base of the hierarchy (derived from declarative_base), then I
>> have a class Property derived from it, and a class Name derived from
>> Property. Each class has a primary_key named node_id, with a foreign key
>> constraint one step done the hierarchy. I have also tried making all the
>> Foreign key constraints point to Node.node_id and it doesn't make a
>> difference.
>>
>> It is just a warning, and the lookup does seem to make the double join,
>> so it doesn't seem to be a big problem, but it seems it want me to
>> configure something for these, but I am not sure what.
>>
>> Two level inheritance is working just fine, it is just where it hits the
>> 3rd level that it seems to want something explicit.
>>
>> --
>> Richard Damon
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To view this discussion on the web
>> visit
>> https://groups.google.com/d/msgid/sqlalchemy/8fa8e94d-cc6f-5bf5-efeb-dbdbae0d7663%40Damon-Family.org.
>>
>
> --
> 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
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e07cc259-2216-4ece-baf9-daabebf4ac00%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/e07cc259-2216-4ece-baf9-daabebf4ac00%40www.fastmail.com?utm_medium=email&utm_source=footer>.
--
[sqlalchemy] ORM 3 level hieracrchy
I am getting the following error: SAWarning: Implicitly combining column Node.node_id with column Name.node_id under attribute 'node_id'. Please configure one or more attributes for these same-named columns explicitly. In my case I am using poymorphic classes by joining with the ORM. Node is the base of the hierarchy (derived from declarative_base), then I have a class Property derived from it, and a class Name derived from Property. Each class has a primary_key named node_id, with a foreign key constraint one step done the hierarchy. I have also tried making all the Foreign key constraints point to Node.node_id and it doesn't make a difference. It is just a warning, and the lookup does seem to make the double join, so it doesn't seem to be a big problem, but it seems it want me to configure something for these, but I am not sure what. Two level inheritance is working just fine, it is just where it hits the 3rd level that it seems to want something explicit. -- Richard Damon -- 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/8fa8e94d-cc6f-5bf5-efeb-dbdbae0d7663%40Damon-Family.org.
Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
Thank you, so that go into each subclass that would have the problem.
8/28/20 2:37 PM, Mike Bayer wrote:
> the argument you're looking for is inherit_condition:
>
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
>
>
> class Foo(...):
> __mapper_args__ = {
> "inherit_condition": node_id == Node.node_id
> }
>
--
Richard Damon
--
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/a529b33e-4675-e94f-e937-c39a651a6e0a%40Damon-Family.org.
[sqlalchemy] ORM AmbiguousForeignKeysErro
Following code and error I am getting:
class Base:
"""Base Class for SQLAlchemy ORM Classes"""
@declared_attr
def __tablename__(cls):
"""Default the Table Name to the Class Name"""
return cls.__name__
Base = declarative_base(cls=Base)
class Node(Base):
"""Class represents the base of the User Data types."""
node_id = Column(Integer, primary_key=True)
type_name = Column(String(255), nullable=False) # todo should come
from type_id
__mapper_args__ = {
'polymorphic_identity': 'Node',
'polymorphic_on': type_name,
}
class Property(Node):
"""Class represents a Node providing information about another Node"""
node_id = Column(Integer, ForeignKey('Node.node_id'), primary_key=True)
ref_id = Column(Integer, ForeignKey('Node.node_id'))
__mapper_args__ = {
'polymorphic_identity': 'Property',
}
Error:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'Node' and 'Property'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Other classes driving from node don't have this issue, but then they
don't have that second foreign key back to node. Can't figure out how to
sepecify the onclause, since I don't explicitly give the join., and my
searching-foo isn't finding anything on this. I suspect somewhere,
likely in the __mapper_args__ I need to specify the field to join on,
but can't find it.
On a somewhat related note, for purposes of DRY, since all of the
subclasses have the same node_id declaration, and basically the same
__mapper__ is there a way I can add this to Node to push this into the
subclasses? Would I use a @declared_attr, which would need to check if
the type was Node since it is different?
(Background, long time programmer, but somewhat new to python, looking
to learn how with reasons, not just rote recipes to follow)
--
Richard Damon
--
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/bd6dcf30-f6ee-308d-284f-14aeef4505ae%40Damon-Family.org.
Re: [sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert
On 8/22/20 12:09 PM, Vitaly Kruglikov wrote: > Hi Richard. I wish it was that simple, but it's not. Here is an > example of how using a builtin name breaks: > > ``` > In [3]: unique = object() > ...: class TestId: > ...: id = 'something else' > ...: unique_id = id(unique) > ...: > ...: > Which would be the expected problem with hiding global names, but you could do unique = object() real_id = id class TestId: id = 'something else' unique_id = real_id(unique) The other option might be to put the column definitions into the table_args for the table (but that loses the column object) -- Richard Damon -- 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/9b0e37af-68b9-1569-64cd-4c0e1a185d4a%40Damon-Family.org.
Re: [sqlalchemy] Re: ArgumentError: Only one Column may be marked autoincrement=True, found both id and id.` when I run the following insert
On 8/22/20 10:46 AM, Vitaly Kruglikov wrote: > I suspect this has something to do with the combination of the > explicit definition of the `id_` column and reflection, but don't know > how to fix. I really need to keep the explicit `id_` descriptor and > shouldn't rename it to `id` because that's a reserved python word. > I would note that 'id' is NOT a reserved word (aka key-word) in Python, but the name of a built-in. As such id(xx) [which uses the built in] and obj.id [which can reference the id member of that object] are not incompatible. Don't use it as a variable name, as that would cause issues, but in an explicit scope like a class it works. -- Richard Damon -- 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/be9c0312-0fff-c543-1434-4550c00ed7a1%40Damon-Family.org.
Re: [sqlalchemy] Attaching a second database to a connection
Ok, I guess I knew you could execute explicit SQL but wasn't thinking
about it or coming across it in my searches. In my case I wouldn't want
to automatically connect, as it will be done at a specific time for a
specific operation, so I could do the ATTACH specifically.
Will I need to explicitly recreate all the schema for the tables? This
second database will have an identical schema to the main (in fact, it
will be generated by the program, as this is a way to bring in updates),
though maybe I won't bother setting up a full ORM model and be using
more explicit SQL to get the data.
On 7/7/20 12:46 AM, Mike Bayer wrote:
>
>
> On Mon, Jul 6, 2020, at 11:19 PM, Richard Damon wrote:
>> SQLite allows a program to attach multiple databases to a single
>> connection, and you are able to reference tables in these additional
>> databases with things like schema.table as the name of a table.
>>
>> Is there a way to do this in SQLAlchemy?
>
> sure, you use SQLite's ATTACH DATABASE command, usually using an event
> so it occurs for all connections automatically, here is code from our
> test suite:
>
> from sqlalchemy import event
>
> engine = create_engine("sqlite://")
>
> @event.listens_for(engine, "connect")
> def connect(dbapi_connection, connection_record):
> dbapi_connection.execute(
> 'ATTACH DATABASE "test_schema.db" AS test_schema'
> )
>
> then you reference the attached database as a schema, Table(...,
> schema="test_schema")
>
>>
>> I am working on an application that will want to import data from
>> another database (that uses basically the same schema, maybe just a
>> subset of the schema of the main database), and get the updates needed
>> to perform by using a join on unique keys (that aren't necessarily the
>> primary key).
>>
>> After finishing the update, and pulling the information in (remapping
>> rowid/primary keys <-> foreign keys that didn't match between the
>> databases) I would then detach this database (which ideally I opened as
>> a read only connection).
>>
>> I can see how to establish multiple engines and sessions, but then I
>> can't do the join between the databases which would let me do a lot of
>> the work down in the database engine. I also have found being able to
>> bind different sets of tables into different engines, but in my case the
>> database will have the same set of tables, so this doesn't look to work.
>>
>> --
>> Richard Damon
>>
--
Richard Damon
--
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/037e7fb9-e6f1-df4f-6749-2d218d58dd5a%40Damon-Family.org.
[sqlalchemy] Attaching a second database to a connection
SQLite allows a program to attach multiple databases to a single connection, and you are able to reference tables in these additional databases with things like schema.table as the name of a table. Is there a way to do this in SQLAlchemy? I am working on an application that will want to import data from another database (that uses basically the same schema, maybe just a subset of the schema of the main database), and get the updates needed to perform by using a join on unique keys (that aren't necessarily the primary key). After finishing the update, and pulling the information in (remapping rowid/primary keys <-> foreign keys that didn't match between the databases) I would then detach this database (which ideally I opened as a read only connection). I can see how to establish multiple engines and sessions, but then I can't do the join between the databases which would let me do a lot of the work down in the database engine. I also have found being able to bind different sets of tables into different engines, but in my case the database will have the same set of tables, so this doesn't look to work. -- Richard Damon -- 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/93932507-f4b8-d378-8db6-28636d7a0825%40Damon-Family.org.
Re: [sqlalchemy] Getting SQLite INTEGER PRIMARY KEY for a column
On 7/1/20 10:12 PM, Mike Bayer wrote: > > I think you might be reading that phrase (not sure where you read it) > too literally. per https://www.sqlite.org/lang_createtable.html#rowid > > > With one exception noted below, if a rowid table has a primary key > that consists of a single column and the declared type of that column > is "INTEGER" in any mixture of upper and lower case, then the column > becomes an alias for the rowid. Such a column is usually referred to > as an "integer primary key". > > > The exception mentioned above is that if the declaration of a column > with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it > does not become an alias for the rowid and is not classified as an > integer primary key. > > the primary key above is not DESC so it's covered as a synonym for > ROWID, and will generate incrementing integer values automatically. > as long as you are getting those incrementing keys, and you arent > using the AUTOINCREMENT keyword, you are using the rowid. > > Looks like you may be right. I took the restriction to mean that the PRIMARY KEY designation had to be on the row definition, and not as a separate constraint, and DBeaver was showing a unique index being defined for the primary key column, but the CLI for sqlite3 doesn't show that index, and when manually inserting data such that the ROWID would be different if it wasn't an alias shows it following the inserted data, so I guess it is becoming the named alias for the ROWID. I think I came across a different section wording that restriction that wasn't as clear about it, or it was long enough ago that they have updated that wording to be clearer. -- Richard Damon -- 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/e7f9d160-27d0-64dd-7ebb-ef3ff8ccab99%40Damon-Family.org.
[sqlalchemy] Getting SQLite INTEGER PRIMARY KEY for a column
I am using SQLAlchemy ORM with a SQLite database, and many of my tables will have a simple integer primary key, with lots of foreign keys referencing them. It should improve efficiency if that integer primary key was the alias for the ROWID that you get by defining the column as INTEGER PRIMARY KEY, but it seems that with a definition of: class Language(Base): """Define Language Table.""" __tablename__ = "Language" lang_id = Column(Integer, primary_key=True) lang_code = Column(String(20), unique=True) I get as the DDL: CREATE TABLE "Language" ( lang_id INTEGER NOT NULL, lang_code VARCHAR(20), CONSTRAINT "pk_Language" PRIMARY KEY (lang_id), CONSTRAINT "uq_Language_lang_code" UNIQUE (lang_code) ) which does not (at least appear to) create the needed primary key that is an alias for the ROWID. I can't seem to find anything documented to do to make this happen. I would think this would be a commonly wanted optimization. Is there something I can do to get this? I would like to be able to use the ORM. -- Richard Damon -- 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/c4b955a6-1dda-7fab-49a0-c84f2603822c%40Damon-Family.org.
Re: [sqlalchemy] Handling multiple very similar tables
Thank you for the pointers. The main usage of these will be in the UI to map the name of items from/into the users native language. One routine would from the items class call into this translation layer and lookup the names as desired in the specified language (with fall back code if such a translation wasn't provided), and a second routine would provide a list of items (using the specified language) and let the user choose one of them. Of course, the first application of this will be for the language system, which will need a modified language selection routine that lists the languages in their own language so if the system, so if the system is in an incomprehensible language, you can find the language you know. On 6/6/20 12:24 AM, Mike Bayer wrote: > What's important here is how you would use these classes. that is, > if you want to have them all laid out explicitly, and your code will > do things like " session.query(Table157).all() ", that is, refer to > them explicitly, versus these tables are all part of some kind of > dynamic data structure, like "session.query(tables[table_id])" or > something like that. > > the general pattern for making lots of classes of the same structure, > when you are also using the ORM, is to either use mixins, or to just > create the classes dynamically. > > There's a wiki recipe called "EntityName" that shows this, and I've > just updated / reformatted it to be more or less current: > > https://github.com/sqlalchemy/sqlalchemy/wiki/EntityName > > note the second example that shows declarative with a mixin, and shows > how you can use the type() function to generate new classes > dynamically. Creating new classes in Python is quite open ended. > > > On Fri, Jun 5, 2020, at 10:21 PM, Richard Damon wrote: >> I am working on a project where I am using (and learning) SQLAlchemy (to >> an SQLite database if that matters) where I am seeing the need for a >> number of very similar tables, and would like to follow the DRY (Don't >> Repeat Yourself) principle if possible. >> >> Each of these tables will have 3 fields (actually, a few more, but these >> are the key ones that are of interest) >> >> An Integer Field that is a Foreign Key to another Table (and which table >> it is varies on each of the tables being generated) >> >> A Second Integer Field that is also a Foreign Key, but for all the >> tables this is to the same table (it is in fact specifying a language) >> >> A Third field, which will be a text field. (The name of the item >> specified by the First Field, in the language specified by the second) >> >> >> The first two fields together will be the Primary Key for the table. The >> second and third together will have a unique constraint on them. >> >> I am somewhat new to Python (but an experienced programmer and tend to >> learn fast), and trying to figure out the best way to do this while >> trying to minimize repeated code. >> >> One thought that comes to mind is a base class, but then I have only 1 >> class directly derived from declarative_base, but that class itself >> shouldn't generate a table, doesn't have all the information itself to >> make a table, and I am not sure how to pass down to it the information >> about the variable foreign key. >> >> A second thought that looks promising would be a class decorator for the >> class (looks like it should work, but a bit of work to learn how to >> build that). >> >> Something else I didn't think of ? >> >> There are going to be a collection of routines to do some of the >> generalized work for these tables, taking some sort of description of >> which table to work with, which could be put in the base class, or the >> decorator could just duck type the needed hooks into the class. >> >> -- >> Richard Damon >> -- Richard Damon -- 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/29d616fe-0144-68f7-81c9-eca5f8707332%40Damon-Family.org.
[sqlalchemy] Handling multiple very similar tables
I am working on a project where I am using (and learning) SQLAlchemy (to an SQLite database if that matters) where I am seeing the need for a number of very similar tables, and would like to follow the DRY (Don't Repeat Yourself) principle if possible. Each of these tables will have 3 fields (actually, a few more, but these are the key ones that are of interest) An Integer Field that is a Foreign Key to another Table (and which table it is varies on each of the tables being generated) A Second Integer Field that is also a Foreign Key, but for all the tables this is to the same table (it is in fact specifying a language) A Third field, which will be a text field. (The name of the item specified by the First Field, in the language specified by the second) The first two fields together will be the Primary Key for the table. The second and third together will have a unique constraint on them. I am somewhat new to Python (but an experienced programmer and tend to learn fast), and trying to figure out the best way to do this while trying to minimize repeated code. One thought that comes to mind is a base class, but then I have only 1 class directly derived from declarative_base, but that class itself shouldn't generate a table, doesn't have all the information itself to make a table, and I am not sure how to pass down to it the information about the variable foreign key. A second thought that looks promising would be a class decorator for the class (looks like it should work, but a bit of work to learn how to build that). Something else I didn't think of ? There are going to be a collection of routines to do some of the generalized work for these tables, taking some sort of description of which table to work with, which could be put in the base class, or the decorator could just duck type the needed hooks into the class. -- Richard Damon -- 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/5771fcb2-6730-e2b8-6555-d27a39db6505%40Damon-Family.org.
Re: [sqlalchemy] SQLAlchemy Access to Native Bulk Loaders
On 4/19/20 4:44 PM, Benjamin Taub wrote: > Thanks for taking the time to respond, Richard. I may be thinking > about the command line option that you mentioned. However, I do see > that MySQL has a LOAD DATA statement > (https://dev.mysql.com/doc/refman/8.0/en/load-data.html) that, I > think, does what I'm thinking about. Similarly, Postgres has COPY > (https://www.postgresql.org/docs/9.2/sql-copy.html) and SQL Server has > BULK INSERT > (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15). > > These seem to be embedded in the related SQL implementations but are > clearly not ANSI standard. I'm not sure if that makes it disqualifying > for a SQLAlchemy feature request, or if anyone else could even use it, > but functionality like this is something that, at least for me, would > make my implementation more DB independent. > > Anyhow, thanks again for your note and your work on SQLAlchemy. I > appreciate it. > > Ben I will admit that wasn't a command I was familiar with, but being DB Specific it would be something I tend to try to minimize the use of. -- Richard Damon -- 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/18784b2d-85c7-80ab-c268-a9fdd0b21d4b%40Damon-Family.org.
Re: [sqlalchemy] SQLAlchemy Access to Native Bulk Loaders
On 4/19/20 3:01 PM, Ben wrote: > Hi, James, thank you for the info. I'll give this a try. Still, it > does seem to require that the data be read into Python and then > written out to the DB, albeit quickly. What I'd prefer is a way to > issue a call to the DB to have it 'suck in' some csv file directly, > rather than having to read it into Python first. Most databases have a > bulk loader to handle such jobs so I would think it would be possible > to provide a vendor-neutral way to call this functionality but I could > be missing something. Perhaps I should look at adding this to the > SQLAlchemy github project but I'm not sure that the world would want > to rely on my code :) > > In any case, thank you so much for taking the time to reply. > > Ben I can't think of any SQL engines, where the engine itself can read a CSV file to load a database (In many cases, the actual SQL engine is off on another machine with the database, and has no direct link to the local file system). Like SQLite, many have a command line interface that can read the file and insert it into the database. Also, some higher end wrappers might provide such a feature, but that is NOT part of the base SQL language. -- Richard Damon -- 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/babc0ee4-d95d-af02-54cc-0691ab9ce6da%40Damon-Family.org.
Re: [sqlalchemy] Bidirectional many-to-many without foreign key
On 1/28/20 9:03 AM, 'Radoslaw Krzak' via sqlalchemy wrote: Hey Simon, *address_id* is *not* a primary key and is not unique across the *addresses* table. *One* /Company/ can reference *many* addresses rows (because there might be multiple rows with the same *address_id* value, hmm maybe I should have used a different name) and vice-versa, *one* /Address/ can be referenced by *many* companies, so it's many to many. The point is I am not linking by primary keys, but simply by the value of *address_id* column. This is a deliberate action and I would love to see how I could set up such a relationship using *primaryjoin, foreign_keys *and***remote_side (if necessary)* :) Radek On Tuesday, January 28, 2020 at 9:28:06 AM UTC, Simon King wrote: Company has an address_id column, which means each company only has a single address, doesn't it? ie. this is a many-to-one relationship, not a many-to-many? I suspect this design has some unintended consequence that you might not have thought about. If a company that has multiple locations shares one of its locations with another company, but not all of them, either you need to incorrectly imply that the second company is at all the first companies locations (give them the same address-id) or imply that they don't share that location (give them different address-id). This latter case requires that you might need to create multiple records for the exact same place (with different address-ids) which would be a violation of the normal form, and makes operations for creating these relationships very complicated. -- Richard Damon 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/569fdc31-883c-9c20-24fe-5f43223af522%40Damon-Family.org.
Re: [sqlalchemy] Error with polymorphic selectin and adding item to session.info: 'expanding' parameters can't be used with an empty list
Hey Mike,
Noticed 1.2.5 doesn't have a release date yet
on
http://docs.sqlalchemy.org/en/latest/changelog/changelog_12.html#change-1.2.2.
Was wondering if that could be released sometime soon - we're blocked from
switching over until it's released.
Damon
On Friday, February 23, 2018 at 12:47:13 PM UTC-8, da...@benchling.com
wrote:
>
> Awesome, thanks Mike! Looking forward to the release.
>
>
> Damon
>
> On Friday, February 23, 2018 at 11:20:33 AM UTC-8, Mike Bayer wrote:
>>
>> On Fri, Feb 23, 2018 at 1:38 PM, Mike Bayer
>> wrote:
>> > *perfect* test case, I'll get a bug report up and can fix this quickly,
>> thanks!
>>
>> here's the issue:
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4199/selectin-polymorphic-hitting-expanding-in
>>
>>
>> here's the patch:
>> https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/678
>>
>> thanks!
>>
>>
>> >
>> > On Fri, Feb 23, 2018 at 1:26 PM, Damon Doucet
>> wrote:
>> >> Hey all,
>> >>
>> >> Loving the new selectin stuff. I think we've hit a bug with
>> >> polymorphic_load=selectin. I've posted a small repro at the bottom.
>> >>
>> >> The crash we're seeing is:
>> >>
>> >> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError)
>> >> 'expanding' parameters can't be used with an empty list [SQL: u'SELECT
>> a1.id
>> >> AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id =
>> a1.id
>> >> \nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id']
>> [parameters:
>> >> [{'primary_keys': []}]]
>> >>
>> >> A few interesting points:
>> >>
>> >> - Uncommenting L2 => no crash
>> >> - Commenting L1 or L3 => no crash
>> >>
>> >> Let me know if there's anything more I can do to clarify/help.
>> >>
>> >>
>> >> Thanks!
>> >> Damon
>> >>
>> >>
>> >>
>> >> from sqlalchemy import *
>> >> from sqlalchemy.orm import *
>> >> from sqlalchemy.ext.declarative import declarative_base
>> >> from sqlalchemy import event
>> >>
>> >> Base = declarative_base()
>> >>
>> >>
>> >> class A(Base):
>> >> __tablename__ = 'a'
>> >> id = Column(Integer, primary_key=True)
>> >> type = Column(String)
>> >> b_id = Column(ForeignKey('b.id'))
>> >>
>> >> __mapper_args__ = {
>> >> 'polymorphic_on': type,
>> >> }
>> >>
>> >>
>> >> class A1(A):
>> >> __tablename__ = 'a1'
>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': 'a1',
>> >> 'polymorphic_load': 'selectin',
>> >> }
>> >>
>> >>
>> >> class A2(A):
>> >> __tablename__ = 'a2'
>> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': 'a2',
>> >> 'polymorphic_load': 'selectin',
>> >> }
>> >>
>> >>
>> >> class B(Base):
>> >> __tablename__ = 'b'
>> >> id = Column(Integer, primary_key=True)
>> >> a_list = relationship('A')
>> >>
>> >>
>> >> e = create_engine("sqlite://", echo=True)
>> >> Base.metadata.create_all(e)
>> >>
>> >> s = Session(e)
>> >>
>> >> b = B(a_list=[A1(), A2()])
>> >> s.add(b)
>> >> s.info['foo'] = b.a_list[0] # L1
>> >> # s.info['bar'] = b.a_list[1] # L2
>> >> s.commit() # L3
>> >>
>> >> print b.a_list # crashes
>> >>
>> >> --
>> >> 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 post to this group, send email to sqlal...@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] Error with polymorphic selectin and adding item to session.info: 'expanding' parameters can't be used with an empty list
Awesome, thanks Mike! Looking forward to the release.
Damon
On Friday, February 23, 2018 at 11:20:33 AM UTC-8, Mike Bayer wrote:
>
> On Fri, Feb 23, 2018 at 1:38 PM, Mike Bayer > wrote:
> > *perfect* test case, I'll get a bug report up and can fix this quickly,
> thanks!
>
> here's the issue:
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4199/selectin-polymorphic-hitting-expanding-in
>
>
> here's the patch:
> https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/678
>
> thanks!
>
>
> >
> > On Fri, Feb 23, 2018 at 1:26 PM, Damon Doucet > wrote:
> >> Hey all,
> >>
> >> Loving the new selectin stuff. I think we've hit a bug with
> >> polymorphic_load=selectin. I've posted a small repro at the bottom.
> >>
> >> The crash we're seeing is:
> >>
> >> sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError)
> >> 'expanding' parameters can't be used with an empty list [SQL: u'SELECT
> a1.id
> >> AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id =
> a1.id
> >> \nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id']
> [parameters:
> >> [{'primary_keys': []}]]
> >>
> >> A few interesting points:
> >>
> >> - Uncommenting L2 => no crash
> >> - Commenting L1 or L3 => no crash
> >>
> >> Let me know if there's anything more I can do to clarify/help.
> >>
> >>
> >> Thanks!
> >> Damon
> >>
> >>
> >>
> >> from sqlalchemy import *
> >> from sqlalchemy.orm import *
> >> from sqlalchemy.ext.declarative import declarative_base
> >> from sqlalchemy import event
> >>
> >> Base = declarative_base()
> >>
> >>
> >> class A(Base):
> >> __tablename__ = 'a'
> >> id = Column(Integer, primary_key=True)
> >> type = Column(String)
> >> b_id = Column(ForeignKey('b.id'))
> >>
> >> __mapper_args__ = {
> >> 'polymorphic_on': type,
> >> }
> >>
> >>
> >> class A1(A):
> >> __tablename__ = 'a1'
> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
> >> __mapper_args__ = {
> >> 'polymorphic_identity': 'a1',
> >> 'polymorphic_load': 'selectin',
> >> }
> >>
> >>
> >> class A2(A):
> >> __tablename__ = 'a2'
> >> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
> >> __mapper_args__ = {
> >> 'polymorphic_identity': 'a2',
> >> 'polymorphic_load': 'selectin',
> >> }
> >>
> >>
> >> class B(Base):
> >> __tablename__ = 'b'
> >> id = Column(Integer, primary_key=True)
> >> a_list = relationship('A')
> >>
> >>
> >> e = create_engine("sqlite://", echo=True)
> >> Base.metadata.create_all(e)
> >>
> >> s = Session(e)
> >>
> >> b = B(a_list=[A1(), A2()])
> >> s.add(b)
> >> s.info['foo'] = b.a_list[0] # L1
> >> # s.info['bar'] = b.a_list[1] # L2
> >> s.commit() # L3
> >>
> >> print b.a_list # crashes
> >>
> >> --
> >> 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 post to this group, send email to sqlal...@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] Error with polymorphic selectin and adding item to session.info: 'expanding' parameters can't be used with an empty list
Hey all,
Loving the new selectin stuff. I think we've hit a bug with
polymorphic_load=selectin. I've posted a small repro at the bottom.
The crash we're seeing is:
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError)
'expanding' parameters can't be used with an empty list [SQL: u'SELECT a1.id
AS a1_id, a.id AS a_id, a.type AS a_type \nFROM a JOIN a1 ON a.id = a1.id
\nWHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id'] [parameters:
[{'primary_keys': []}]]
A few interesting points:
- Uncommenting L2 => no crash
- Commenting L1 or L3 => no crash
Let me know if there's anything more I can do to clarify/help.
Thanks!
Damon
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
type = Column(String)
b_id = Column(ForeignKey('b.id'))
__mapper_args__ = {
'polymorphic_on': type,
}
class A1(A):
__tablename__ = 'a1'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'a1',
'polymorphic_load': 'selectin',
}
class A2(A):
__tablename__ = 'a2'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'a2',
'polymorphic_load': 'selectin',
}
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_list = relationship('A')
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
b = B(a_list=[A1(), A2()])
s.add(b)
s.info['foo'] = b.a_list[0] # L1
# s.info['bar'] = b.a_list[1] # L2
s.commit() # L3
print b.a_list # crashes
--
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] Large number of polymorphic subclasses
Hey Mike,
I just saw the new selectinload feature, and am super excited about it!
As far as I can tell, this is only for relationships, so I wanted to check
what the plans are for an inheritance loader using selectinload as well,
given that you initially mentioned them together and I can't find an issue
in the tracker for it.
> I've created an issue for both a new relationship loader and an
> inheritance loader at the same time, since they will use very similar
> paths, at https://bitbucket.org/zzzeek/sqlalchemy/issues/3944
> <https://www.google.com/url?q=https%3A%2F%2Fbitbucket.org%2Fzzzeek%2Fsqlalchemy%2Fissues%2F3944&sa=D&sntz=1&usg=AFQjCNHP34Q9-nl0O_qmczm7uZFo8daCAA>
> .
Thanks!
Damon
On Tuesday, April 4, 2017 at 8:24:07 PM UTC-7, Mike Bayer wrote:
>
> Reusing the original query is hard, and the case you have is due to
> mismatched entities that would have to be handled, probably by wrapping the
> original query in a subquery just like subquery eager loading does. The
> subquery eager loading feature took a few years to work out an enormous
> number of issues with this approach.
>
> The new feature which I'm now working on, several hours a day, in response
> to this thread solves the whole problem in a much better way, using a
> simple IN expression against the primary keys of all states loaded in the
> query. Betas of 1.2 will hopefully be available in a month or two.The
> feature can be replicated in older versions by just grabbing the list of
> states coming out of __iter__ and using their primary keys in an IN clause.
> Parallel development of this other approach as an interim throwaway is
> probably not worth it if it continues to become more complicated.
>
> On Apr 4, 2017 7:02 PM, > wrote:
>
> Hey Mike,
>
> Thanks for those -- seems to have helped those cases, though we're running
> into some pretty weird behavior with joins. Here's a simplified case that
> shows one of the issues we're running into (use _loader_from_cls from
> above):
>
>
> class W(Base):
> __tablename__ = 'w'
> id = Column(Integer, primary_key=True)
> type = Column(String)
>
> x_id = Column(Integer, ForeignKey('x.id'))
> x = relationship('X')
>
> __mapper_args__ = {'polymorphic_on': type}
>
>
> class W2(W):
> __tablename__ = 'w2'
> id = Column(Integer, ForeignKey('w.id'), primary_key=True)
> w2 = Column(String)
> __mapper_args__ = {'polymorphic_identity': 'w2'}
>
>
> class X(Base):
> __tablename__ = 'x'
> id = Column(Integer, primary_key=True)
>
>
> @event.listens_for(W, "load", propagate=True)
> def load_extra(target, context):
> key = ('loader_by_cls', type(target))
>
> if key not in context.attributes:
> context.attributes[key] = _loader_for_cls(target, context)
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
> s = Session(e)
>
> s.add(W2(x=X()))
> s.commit()
>
> s.query(W).join(W.x).first()
>
>
> One of the statements emitted by this is
>
> SELECT w2.id AS w2_id, w.id AS w_id, w.type AS w_type, w.x_id AS w_x_id,
> w2.w2 AS w2_w2
> FROM w2, w JOIN x ON x.id = w.x_id
> LIMIT ? OFFSET ?
>
> Specifically, the "FROM w2, w JOIN x" is not what we want here -- we'd
> just want "FROM w2"
>
> Replacing
>
> q = orig_query.with_entities(target_cls)
>
> with
>
> q = context.session.query(target_cls).join(orig_query.subquery())
>
> fixes the issue, though this feels less than ideal. Subclassing Query also
> seems less than ideal. Do you have any ideas here?
>
>
> Thanks,
> Damon
>
> On Tuesday, April 4, 2017 at 6:42:32 AM UTC-7, Mike Bayer wrote:
>
>> This repaste of the example contains two refinements to address each of
>> these issues distinctly. We want to avoid re-entrant invocation of
>> _loader_for_cls, so putting a flag into attributes handles that. Then
>> there's the issue of same class coming in that we are already querying,
>> we can look at the type being loaded in column_descriptions, and this
>> also solves the re-entrant problem though I think the flag is more
>> direct. The column_descriptions check likely needs more refinements,
>> but the general idea is that if the entity being loaded is already a B2,
>> then you wouldn't do any extra B2 queries (unless you have multiple
>> levels of inheritance / polymorphic loading in which case that probably
>> needs adjustment).
>>
>> The "
Re: [sqlalchemy] Large number of polymorphic subclasses
Hey Mike,
Thanks for those -- seems to have helped those cases, though we're running
into some pretty weird behavior with joins. Here's a simplified case that
shows one of the issues we're running into (use _loader_from_cls from
above):
class W(Base):
__tablename__ = 'w'
id = Column(Integer, primary_key=True)
type = Column(String)
x_id = Column(Integer, ForeignKey('x.id'))
x = relationship('X')
__mapper_args__ = {'polymorphic_on': type}
class W2(W):
__tablename__ = 'w2'
id = Column(Integer, ForeignKey('w.id'), primary_key=True)
w2 = Column(String)
__mapper_args__ = {'polymorphic_identity': 'w2'}
class X(Base):
__tablename__ = 'x'
id = Column(Integer, primary_key=True)
@event.listens_for(W, "load", propagate=True)
def load_extra(target, context):
key = ('loader_by_cls', type(target))
if key not in context.attributes:
context.attributes[key] = _loader_for_cls(target, context)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(W2(x=X()))
s.commit()
s.query(W).join(W.x).first()
One of the statements emitted by this is
SELECT w2.id AS w2_id, w.id AS w_id, w.type AS w_type, w.x_id AS w_x_id,
w2.w2 AS w2_w2
FROM w2, w JOIN x ON x.id = w.x_id
LIMIT ? OFFSET ?
Specifically, the "FROM w2, w JOIN x" is not what we want here -- we'd just
want "FROM w2"
Replacing
q = orig_query.with_entities(target_cls)
with
q = context.session.query(target_cls).join(orig_query.subquery())
fixes the issue, though this feels less than ideal. Subclassing Query also
seems less than ideal. Do you have any ideas here?
Thanks,
Damon
On Tuesday, April 4, 2017 at 6:42:32 AM UTC-7, Mike Bayer wrote:
>
> This repaste of the example contains two refinements to address each of
> these issues distinctly. We want to avoid re-entrant invocation of
> _loader_for_cls, so putting a flag into attributes handles that. Then
> there's the issue of same class coming in that we are already querying,
> we can look at the type being loaded in column_descriptions, and this
> also solves the re-entrant problem though I think the flag is more
> direct. The column_descriptions check likely needs more refinements,
> but the general idea is that if the entity being loaded is already a B2,
> then you wouldn't do any extra B2 queries (unless you have multiple
> levels of inheritance / polymorphic loading in which case that probably
> needs adjustment).
>
> The "strong reference" comment, you can try commenting that out in the
> test and see what happens. Because this loader trick loads all the
> subclasses up front upon seeing the first member of a particular
> subtype, it is loading for subsequent instances that aren't seen yet as
> well. If we don't make a strong reference to them, they get lost and
> the extra attribute loading fails.
>
> The feature I have in development works a lot better because all the
> additional loads are done *after* all the entities are loaded, and it
> does it using an IN query that only includes those entities that
> definitely need the load. I'm not sure if there are other negative side
> effects from our loading of the "subclass" entity in some cases ahead of
> where the primary query gets at the entity.The recipe here could be
> made to do all the extra loads after the primary query but theres no
> "after query" hook, you'd have to subclass Query and override __iter__
> to add this step.
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import event
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> a1 = Column(String)
> type = Column(String)
>
> __mapper_args__ = {'polymorphic_on': type}
>
>
> class B1(A):
> __tablename__ = 'b1'
> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
> b1 = Column(String)
> b_data = Column(String)
> __mapper_args__ = {'polymorphic_identity': 'b1'}
>
>
> class B2(A):
> __tablename__ = 'b2'
> id = Column(Integer, ForeignKey('a.id'), primary_key=True)
> b2 = Column(String)
> b_data = Column(String)
> cs = relationship("C", lazy='subquery')
>
> __mapper_args__ = {'polymorphic_identity': 'b2'}
>
>
> class C(Base):
> __tablename_
Re: [sqlalchemy] Large number of polymorphic subclasses
Hey Mike, Looks like I spoke too soon -- a few more questions: Using the example code you posted, we're actually seeing 4 additional queries (one per result model), rather than the expected 3 (one per result model type). If you print context.query inside load_extra, I think it's clear why: - the loader sequentially processes loaded models (https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/loading.py#L75) - on processing the first B2, it executes a query to load all B2's data - when processing the results of this new query, it now processes the second B2 record (under a new context) - load_extra() gets called for the new B2 with a different context than the first B2 - this now executes _loader_for_cls Note that if you don't call _loader_for_cls in load_extra, load_extra is called for each record using the correct context. A related issue is that if you query directly for B2, it'll redundantly execute _loader_for_cls. I think we could solve both of these issues by doing this instead: q = orig_query.with_entities(target_cls) if q == orig_query: return Unfortunately, the == operator doesn't work for comparing queries. Do you have a way to compare query equality, or alternatively have a solution to both of these issues? Could you also elaborate on what you meant by this comment? # store this strong reference so recs don't get lost while # iterating Thanks again for all your help, Damon On Monday, April 3, 2017 at 6:42:17 PM UTC-7, da...@benchling.com wrote: > > Thanks a ton for your help, Mike! > > We played around with it and are pretty happy with your solution using the > load() event, so we'll be using that moving forward. > > > Damon > > On Wednesday, March 29, 2017 at 2:40:39 PM UTC-7, Mike Bayer wrote: >> >> I have a working version of both loading relationships via IN as well as >> loading joined inheritance subclasses via IN, including your great idea >> that extra eager loaders should continue to work for the subclass >> loaders. >> >> I've only tested it with one scenario so far and both patches have a >> long way to go re: tests and documentation. >> >> the scratch test case is currently in the commit message for the second >> patch, which illustrates a base class + subclass load where both classes >> have an additional relationship. This is at >> https://gerrit.sqlalchemy.org/#/c/359/, note this builds upon the >> previous gerrit at https://gerrit.sqlalchemy.org/#/c/352/.The work >> at the moment will likely fall down with any kind of surprises but if >> you wanted to start running it and finding those cases, that is always >> helpful. >> >> So far this looks promising as something that can be in 1.2 with perhaps >> some "EXPERIMENTAL" warnings surrounding it, but overall 1.2 was lacking >> a "killer ORM feature" so these two would be it. >> >> >> >> On 03/23/2017 06:02 PM, mike bayer wrote: >> > >> > >> > On 03/23/2017 02:40 PM, mike bayer wrote: >> >> >> >> >> >> On 03/23/2017 12:53 PM, da...@benchling.com wrote: >> >>> Hey Mike, >> >>> >> >>> Thanks for the quick response! >> >>> >> >>> For developers that are pretty familiar with the SQLAlchemy API, but >> not >> >>> so much the internals, would implementing the subqueryloads to >> >>> contribute to SA be a reasonable endeavor? Could you ballpark how >> much >> >>> time how long it might take for us to do it? >> >> >> >> I haven't looked at what this would take, but it would be intricate >> and >> >> also need a lot of tests that are not easy to write.Like, if you >> >> worked on it, you could probably get something working, but then that >> >> probably wouldn't be how it really needs to be because all kinds of >> >> things that are simple for simple cases don't work with the vast >> amount >> >> of edge cases which we have. >> > >> > I've created an issue for both a new relationship loader and an >> > inheritance loader at the same time, since they will use very similar >> > paths, at https://bitbucket.org/zzzeek/sqlalchemy/issues/3944.A >> POC >> > for the relationship loader so far looks to be very simple (but then >> > again all the loaders start out very simple...) and is at >> > https://gerrit.sqlalchemy.org/352. The same infrastructure and >> > approach would also be used for the ma
Re: [sqlalchemy] Large number of polymorphic subclasses
Thanks a ton for your help, Mike! We played around with it and are pretty happy with your solution using the load() event, so we'll be using that moving forward. Damon On Wednesday, March 29, 2017 at 2:40:39 PM UTC-7, Mike Bayer wrote: > > I have a working version of both loading relationships via IN as well as > loading joined inheritance subclasses via IN, including your great idea > that extra eager loaders should continue to work for the subclass > loaders. > > I've only tested it with one scenario so far and both patches have a > long way to go re: tests and documentation. > > the scratch test case is currently in the commit message for the second > patch, which illustrates a base class + subclass load where both classes > have an additional relationship. This is at > https://gerrit.sqlalchemy.org/#/c/359/, note this builds upon the > previous gerrit at https://gerrit.sqlalchemy.org/#/c/352/.The work > at the moment will likely fall down with any kind of surprises but if > you wanted to start running it and finding those cases, that is always > helpful. > > So far this looks promising as something that can be in 1.2 with perhaps > some "EXPERIMENTAL" warnings surrounding it, but overall 1.2 was lacking > a "killer ORM feature" so these two would be it. > > > > On 03/23/2017 06:02 PM, mike bayer wrote: > > > > > > On 03/23/2017 02:40 PM, mike bayer wrote: > >> > >> > >> On 03/23/2017 12:53 PM, da...@benchling.com wrote: > >>> Hey Mike, > >>> > >>> Thanks for the quick response! > >>> > >>> For developers that are pretty familiar with the SQLAlchemy API, but > not > >>> so much the internals, would implementing the subqueryloads to > >>> contribute to SA be a reasonable endeavor? Could you ballpark how much > >>> time how long it might take for us to do it? > >> > >> I haven't looked at what this would take, but it would be intricate and > >> also need a lot of tests that are not easy to write.Like, if you > >> worked on it, you could probably get something working, but then that > >> probably wouldn't be how it really needs to be because all kinds of > >> things that are simple for simple cases don't work with the vast amount > >> of edge cases which we have. > > > > I've created an issue for both a new relationship loader and an > > inheritance loader at the same time, since they will use very similar > > paths, at https://bitbucket.org/zzzeek/sqlalchemy/issues/3944.A POC > > for the relationship loader so far looks to be very simple (but then > > again all the loaders start out very simple...) and is at > > https://gerrit.sqlalchemy.org/352. The same infrastructure and > > approach would also be used for the mapper inheritance loader, which > > would be enabled via a mapper()-level flag, as well as per-query using a > > new query option. > > > > I can't yet guarantee this will be a 1.2 thing, I'd have to get some > > more momentum going w/ test cases and all that. In a 1.2 release it > > would also be a little bit experimental as new loading styles usually > > have small issues coming up for months or years, as people try more use > > cases. > > > > > > > > > > > > > >> > >> The routine that's loading the additional columns just for one object > at > >> a time is here: > >> > >> > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/loading.py#L635 > > >> > >> and then here for the bulk of it: > >> > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/mapper.py#L2588 > > >> > >> > >> > >> But the much harder part would be how to work this step into the > loading > >> infrastructure, which would be somewhere in > >> > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/loading.py#L273, > > > >> > >> which is a very intricate function with over a decade of constant > >> refactorings behind it, and I'd have to think pretty deeply for awhile > >> how best to do this. > >> > >> Not to mention that there's more than one way to do this query, there's > >> either re-using the criteria from the original query, or there's > >> injecting the primary key ids of the whole list of objects into an IN > >> clause after the fact.
Re: [sqlalchemy] Large number of polymorphic subclasses
Hey Mike, Thanks for the quick response! For developers that are pretty familiar with the SQLAlchemy API, but not so much the internals, would implementing the subqueryloads to contribute to SA be a reasonable endeavor? Could you ballpark how much time how long it might take for us to do it? Regarding STI and relationships, is there any way to do that but still get the benefits of JTI? e.g. is there an easy way to resolve my_base_class_inst.subclass_prop as a proxy to the subclass? We could roll our own using __getitem__ but it seems a little hairy. Thanks again, Damon On Wednesday, March 22, 2017 at 3:59:45 PM UTC-7, Mike Bayer wrote: > > > > On 03/22/2017 02:17 PM, da...@benchling.com wrote: > > Hey all, > > > > We were wondering if you had any advice on having a large (~10) number > > of polymorphic subclasses for a single base class. Using > > with_polymorphic: '*' causes SQLAlchemy to joinedload all subclasses > > like this: > > > > SELECT ... > > FROM base_table > > LEFT OUTER JOIN sub_table_1 ON base_table.id = sub_table_1.id > > LEFT OUTER JOIN sub_table_2 ON base_table.id = sub_table_2.id > > ... > > > > Postgres buckles under too many joins, and these queries start taking a > > really long time. > > > > One other note is that for most of our queries, only a few of these > > sub-tables are actually needed, so most of the joins are wasted. > > Unfortunately, ahead of time, we don't know which tables will be needed > > -- we're relying on the discriminator. > > > > Ideally, we'd be able to specify that the ORM should subqueryload the > > subclasses (and only execute subqueries on the types that are present). > > This would have to happen both when querying the base table, but also > > when accessing relationships. We'd want it to execute a query on the > > base table, then execute one query for each present subclass. > > > > Another solution might be to use some kind of hook that > > > > - is executed after a query returns with results (or after a list of > > models are added to the session?) > > - groups the models by type and runs its own subqueries to load the data > > > > Any help here is greatly appreciated! > > > The purpose of with_polymorphic is more about being able to filter on > multiple classes at the same time, which is why it uses joins, but these > don't scale to many subclasses.Adding a subquery load for the > related tables would be something that the ORM can someday have as a > feature, but it would need a lot of tests to ensure it's working as > advertised. > > There's a lot of ways to get those other tables loaded but none of them > look that great. Turning off with_polymorphic(), one approach is to > collect all the distinct types and identifiers from your query result; > then do a separate query for each subtype: > > result = session.query(BaseClass).filter(...).all() > > types = sorted([(type(obj), obj.id) for obj in result], > key=lambda t: t[0]) > > for type, ids in itertools.groupby(types, key=lambda t: t[0]): > session.query(type).filter(type.id.in_(ids)).all() > > That will emit a query with an INNER JOIN for each class and will > populate the remaining records in the identity map. The columns that > are already loaded are not re-accessed, though the DBAPI will still send > them over the network to the cursor. You can try limiting the columns > you query for in each statement as well by using the defer() option. > > Another way is to use with_polymorphic() but to provide a different kind > of SQL statement, like a polymorphic_union(). This would be a UNION of > statements that each have an inner join. the resulting SQL is a beast > but it at least isn't using those left outer joins. I think you can > probably use sqlalchemy.orm.util.polymorphic_union() directly to get > this UNION statement built up automatically. > > Still another way is to reorganize the mappings to use single-table > inheritance and relationship() to link out to the related table, then > the normal "subqueryload" feature to load them as relationships. Even > though this way is ugly, I might use this (short of implementing the > related table subqueryload feature) just to make things simple. > > > Definitely a feature that should be added but that's not an immediate > solution. > > > > > > > Thanks, > > Damon > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > &
[sqlalchemy] Large number of polymorphic subclasses
Hey all, We were wondering if you had any advice on having a large (~10) number of polymorphic subclasses for a single base class. Using with_polymorphic: '*' causes SQLAlchemy to joinedload all subclasses like this: SELECT ... FROM base_table LEFT OUTER JOIN sub_table_1 ON base_table.id = sub_table_1.id LEFT OUTER JOIN sub_table_2 ON base_table.id = sub_table_2.id ... Postgres buckles under too many joins, and these queries start taking a really long time. One other note is that for most of our queries, only a few of these sub-tables are actually needed, so most of the joins are wasted. Unfortunately, ahead of time, we don't know which tables will be needed -- we're relying on the discriminator. Ideally, we'd be able to specify that the ORM should subqueryload the subclasses (and only execute subqueries on the types that are present). This would have to happen both when querying the base table, but also when accessing relationships. We'd want it to execute a query on the base table, then execute one query for each present subclass. Another solution might be to use some kind of hook that - is executed after a query returns with results (or after a list of models are added to the session?) - groups the models by type and runs its own subqueries to load the data Any help here is greatly appreciated! Thanks, Damon -- 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: Inferring joins from table A to table C via table B
Thank you very much for the explanation. It is what I feared was the case. One of the great features we love about SA is the mappers, allowing us to define table relationships in such a way that we can decide what table(s) around which to "pivot", giving us different ways of returning data even when processed from the same query. It seemed to us that if the mappers are able to traverse all the joins necessary to render the mapped objects -- we greatly admire SA's ability to construct all the outer joins required to do this in one fell swoop -- that it should also be possible to have SA follow similar logic to construct query objects as well -- in a completely analogous fasion -- when supplied with filters. Alas that this is not the case. :( --Damon On Sep 3, 12:29 pm, "Michael Bayer" wrote: > Damon wrote: > > >> > MUST we explicitly supply the join to such query objects? Or is there > >> > some way that SA can figure out that tbl_people_documents is in > >> > between tbl_people and tbl_documents on its own? Perhaps there is > >> > something we can add to the tbl_people/tbl_documents object > >> > definitions that clues SA in? > > >> join on the relation. > > >> query(A).join(A.relation_to_b).filter(B.foo == 'bar') > > > The problem with that, from what we're trying to build, is that we > > have to explicitly know that relation object and supply it. > > > We want SA to *infer* the relationship between any two tables based on > > the ORM relationships that we have already defined in our mapper > > objects. > > but you're asking for it to infer the join between *three* tables - i.e. > your association table. The current SQLA functionality is that ORM-level > joins, that is joins which occur due to the presence of a relation(), must > be expressed explicitly in terms of the relation between the two entity > classes. Right now only a SQL level join, that is joins which occur > due to the presence of a known foreign key between the two tables, is what > happens if you don't specify the relation() you'd like to join on. > > The proposed enhancement would require that we change the method used when > someone joins from A to B using query.join(), in that it would > specifically search for ORM-level relations, instead of relying upon > SQL-level joining which searches only for foreign keys between the two > tables. It would also throw an error if there were any ambiguity > involved. I'm not 100% sure but I think it's quite possible that we had > such a "assume the only relation() in use" feature a long time ago when > constructing joins, and it was removed in favor of explicitness, but I'd > have to dig through 0.3 functionality to see if that was the case. > > My initial take on this feature is -1 on this since I don't think being > explicit about an ORM relation is burdensome or a bad idea (plus we might > have already made this decision a long time ago). We might just need some > better error messages when a join can't be found between "A" and "B" to > suggest that its only looking for immediate foreign keys in that case, not > ORM relations. > > Alternatively, SQL-expression level join() would "search" for any number > of paths from table A to table B between any other tables that may create > a path between them. that would also find the association table between A > and B and create a longer series of joins without ORM involvement. I'm > strongly -1 on such a feature as the expression language shouldn't be > tasked with performing expensive graph traversals just to formulate a SQL > query, and table.join()'s contract is that it produces a JOIN between only > two tables, not a string of joins. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Inferring joins from table A to table C via table B
> > MUST we explicitly supply the join to such query objects? Or is there > > some way that SA can figure out that tbl_people_documents is in > > between tbl_people and tbl_documents on its own? Perhaps there is > > something we can add to the tbl_people/tbl_documents object > > definitions that clues SA in? > > join on the relation. > > query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. Again, my example was not the joining between two adjacent tables, which we can get SA to handle on its own without our help. It was between two tables that are adjacent only through an intermediary table that touches both of them. --Damon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Inferring joins from table A to table C via table B
Hi, Relatively new to SA and unable to find the answer in this group's archives. (Possibly my search-fu is weak, in which case I apologize but hope you'll point me in right direction.) We're building a generic DB query engine on SA using the ORM and mappers, but we can't seem to get SA to naturally figure out the join required to render a query that filters on data found in two tables that are related through a 3rd-party table. For example, given two tables: tbl_people idpeople (primary key) ... tbl_documents iddoc (primary key) ... We have a 3rd-party table that relates the two of them together thusly: tbl_people_documents idpeople (foreign key to tbl_people.idpeople; primary key) iddoc (foreign key to tbl_documents.iddoc; primary key) ... Building a mapper object with the proper relations is no problem. But building a query object that filters on data in just tbl_people and tbl_documents fails because SA can't seem to infer the join between tbl_people and tbl_documents despite the foreign keys present in tbl_people_documents. MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? Silly example: Say we want to find all the people records that have last names of "SMITH" and document body content of "SPAM". We would write the SQL like this: SELECT p.* FROM tbl_people p, tbl_documents d, tbl_people_documents pd WHERE p.idpeople = pd.idpeople AND pd.iddoc = d.iddoc AND p.lastname = 'SMITH' AND d.body = 'SPAM' ; I understand how to build the equivalent SA query object by explicitly supplying the query object the join criteria. But we want SA to *know* how to infer that join criteria itself. Is this possible? Thanks in advance, Damon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
