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.
